系列文章:MySQL 基础:库表、约束与类型 的第 2 篇。
Mysql数据表操作
数据表,是数据库中重要的结构之一,是存储数据的基础。在关系型数据库中,实际上表就是一个二维的数据结构。
创建数据表
首先需要进入到某个数据库,上面的操作数据库中已经创建了一个库db1。
# 进入db1数据库
[root@localhost][(none)]> USE db1;
Database changed
[root@localhost][db1]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
进入后使用SELECT DATABASE(); 来查询当前进入的数据。
CRATE TABLE [IF NOT EXISTS] table_name(
column_name data_type
)
由于是二维表,所以column_name肯定是确认的,根据存储的数据类型在选择合适的data_type。
[root@localhost][db1]> CREATE TABLE emp(
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(8,2) UNSIGNED
-> );
Query OK, 0 rows affected (0.12 sec)
创建一张员工表,存储了名字、年龄、工资。其中年龄和工资采用了无符号。
查看数据表
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
- 查询当前数据库全部表:
SHOW TABLES ; - 查询其他数据库中表:
SHOW TABLES FROM mysql - 模糊查询当前数据库表:
SHOW TABLES LIKE 'pattern'- 这里的pattern是指匹配模式,
%是指匹配多个字符,_是指匹配单个字符。需要注意的是在匹配有带有_下划线的数据表名字时候,需要添加\反斜杠转义。
- 这里的pattern是指匹配模式,
# 查询当前数据库中全部表。
[root@localhost][db1]> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| emp |
+---------------+
1 row in set (0.00 sec)
# 查询其他数据库中全部表。
[root@localhost][db1]> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
# 使用 LIKE 做模糊查询
[root@localhost][db1]> SHOW TABLES LIKE 'e%'
-> ;
+--------------------+
| Tables_in_db1 (e%) |
+--------------------+
| emp |
+--------------------+
1 row in set (0.00 sec)
查看表结构
SHOW COLUMNS FROM table_name
[root@localhost][db1]> SHOW COLUMNS FROM emp;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
修改表结构
平时用IDE用多了,估计很少会去写SQL修改表结构。
修改表结构分为两种
- 修改表本身
- 修改表名
RENAME TBALE ol_table_name TO new_table_name - 修改表选项
ALTER TABLE table_name table_option [=] option
- 修改表名
- 修改表字段
- 新增字段
ALTER TABLE table_name ADD [colums_name] data_type [data_type] [location] - 修改字段
ALTER TABLE table_name MODIFY [columns_name] [data_type] [location] - 重命名字段
ALTER TABLE table_name CHANGE ol_columns_name new_columns_name data_type [columns_type] [location] - 删除字段
ALTER TABLE table_name DROP columns_name
- 新增字段
插入表数据(一)
INSERT [INTO] table_name [(columns_name , ... )] VALUES(value , ...)
# 插入一条模拟数据,默认全部字段都插入。也就没有指定columns的时候所有的字段都需要赋值。
[root@localhost][db1]> INSERT emp VALUE('Tom',23,8432.34);
Query OK, 1 row affected (0.01 sec)
# 插入一条数据,只为部分字段赋值。
[root@localhost][db1]> INSERT emp(username , salary) VALUE('Jon',5600.32);
Query OK, 1 row affected (0.01 sec)
实际上插入数据的时候是有约束的。
简单的查询:SELECT expr,.. FROM table_name
# 先做一个简单的演示,使用*标识所有的列。
[root@localhost][db1]> SELECT * FROM emp;
+----------+------+---------+
| username | age | salary |
+----------+------+---------+
| Tom | 23 | 8432.34 |
| Jon | NULL | 5600.32 |
+----------+------+---------+
2 rows in set (0.00 sec)
在插入
Jon的时候并没有指定age,实际上一般的业务上会出现数据不能为空的情况。比如说刚才创建emp所有的字段都是可以为空值。
列约束:NULL \ NOT NULL
NULL,字段值可以为空;NOT NULL字段值不可为空。
# 模拟一条数据,并指定age 为空。
[root@localhost][db1]> INSERT emp(username,age) VALUE('Zo',NULL);
Query OK, 1 row affected (0.00 sec)
# 看下存储的结果
[root@localhost][db1]> SELECT * FROM emp;
+----------+------+---------+
| username | age | salary |
+----------+------+---------+
| Tom | 23 | 8432.34 |
| Jon | NULL | 5600.32 |
| Zo | NULL | NULL |
+----------+------+---------+
在创建一张表,并指定部分字段不能为空。
# 创建表emp1 ,指定username 不能为空。
[root@localhost][db1]> CREATE TABLE emp1(
-> username VARCHAR(20) NOT NULL,
-> age TINYINT UNSIGNED NULL
-> );
Query OK, 0 rows affected (0.10 sec)
# 查看下emp1的数据表结构。
[root@localhost][db1]> SHOW COLUMNS FROM emp1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
从emp2结构中看到,此时的username 已经不能为NULL了。
# 模拟数据,尝试吧NULL赋值到username。
[root@localhost][db1]> INSERT emp1(username,age) VALUE(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null
列约束:AUTO_INCREMENT
AUTO_INCREMENT 自增必须定义成主键使用。
# 如果列约束 AUTO_INCREMENT 不配合主键使用会出现下列警告。
[root@localhost][db1]> CREATE TABLE emp2(
-> id SMALLINT UNSIGNED AUTO_INCREMENT,
-> username VARCHAR(20)
-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 修改创建表语句,并指定主键
[root@localhost][db1]> CREATE TABLE emp2(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.09 sec)
# 检查下emp2表结构。
[root@localhost][db1]> SHOW COLUMNS FROM emp2;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 插入一条数据试试。
[root@localhost][db1]> INSERT emp2(username) VALUE('Jon');
Query OK, 1 row affected (0.01 sec)
# 查看结果
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
| 1 | Jon |
+----+----------+
1 row in set (0.00 sec)
# 如果插入的时候指定id的值,看看会发生什么?
[root@localhost][db1]> INSERT emp2(id,username) VALUE(10,'Tom');
Query OK, 1 row affected (0.01 sec)
# 查看结果。
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
| 1 | Jon |
| 10 | Tom |
+----+----------+
2 rows in set (0.00 sec)
# 也就是说,如果我们指定插入的值,是可以插入成功的。在插入一条看看
[root@localhost][db1]> INSERT emp2(username) VALUE('King');
Query OK, 1 row affected (0.03 sec)
# 再查看
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
| 1 | Jon |
| 10 | Tom |
| 11 | King |
+----+----------+
3 rows in set (0.00 sec)
# 结果可以看到,指定主键后,在此插入若不指定会在最大的id上进行自增。
列约束:PRIMARY KEY
PRIMARY KEY 主键约束,上面我们使用了
AUTO_INCREMENT而它是必须配合PRIMARY KEY使用。但是PRIMARY KEY 可以单独使用。一张表也只能存在一个主键。
# 创建一个表,并指定PRIMARY KEY 但是不使用AUTO_INCRMENT。
[root@localhost][db1]> CREATE TABLE emp3(
id SMALLINT UNSIGNED PRIMARY KEY ,
username VARCHAR(20)
);
Query OK, 0 rows affected (0.09 sec)
主键有一个特性就是保证唯一性,所以不可以重复。
# 检查下emp3表结构,可以看到NULL列是NO。
[root@localhost][db1]> SHOW COLUMNS FROM emp3;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 我们来模拟一次主键重复的情况。
[root@localhost][db1]> INSERT emp3(id,username) VALUES(1,'Tom');
Query OK, 1 row affected (0.01 sec)
# 主键重复异常。
[root@localhost][db1]> INSERT emp3(id,username) VALUES(1,'Jon');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
列约束:UNIQUE KEY
UNIQUE KEY唯一约束,唯一的约束可以保证数据的唯一性,唯一约束的字段可以设置为NULL。每张表可以设置多个唯一约束,这个主键不同。
# 创建一张主键自增并且有唯一约束的表emp4。
[root@localhost][(none)]> CREATE TABLE emp4(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> age TINYINT UNSIGNED
-> );
Query OK, 0 rows affected (0.09 sec)
# 查看下emp4表结构情况。
[root@localhost][Demo]> SHOW COLUMNS FROM emp4;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
# 插入数据试试
[root@localhost][Demo]> INSERT emp4(username,age) VALUES('Tom',22);
Query OK, 1 row affected (0.01 sec)
# 重复插入Tom试试,这会提示错误。
[root@localhost][Demo]> INSERT emp4(username,age) VALUES('Tom',22);
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'
这里其实有一个疑问,为什么QNIQUE KYE可以为NULL,如果为NULL怎么表示唯一呢?
列约束:DEFAULT
DEFAULT 默认值,如果没有明确字段赋值,则自动赋值为默认值。
# 创建一张表,指定sex DEFAULT 值为 0,如果插入数据的时候不指定,那么这个字段就是0。
[root@localhost][Demo]> CREATE TABLE emp5(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> sex TINYINT DEFAULT 0
-> );
Query OK, 0 rows affected (0.11 sec)
#检查表emp5结构
[root@localhost][Demo]> SHOW COLUMNS FROM emp5;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | tinyint(4) | YES | | 0 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
# 插入模拟数据
[root@localhost][Demo]> INSERT emp5(username) VALUES('Tom');
Query OK, 1 row affected (0.02 sec)
[root@localhost][Demo]> INSERT emp5(username) VALUES('Jon');
Query OK, 1 row affected (0.01 sec)
# 查看数据,并没有指定sex的值,就会使用默认值。
[root@localhost][Demo]> SELECT * FROM emp5;
+----+----------+------+
| id | username | sex |
+----+----------+------+
| 1 | Tom | 0 |
| 2 | Jon | 0 |
+----+----------+------+
2 rows in set (0.00 sec)
列约束:小结
列的属性,除了数据类型的约束,就是上面几种列约束。
- 约束保证数据的完整性。
- 约束分为表级约束和列级别约束。
- 约束的类型包括
- NOT NULL
非空约束 - PRIMARY KEY
主键约束 - UNIQUE KEY
唯一约束 - DEFAULT
默认约束 - FOREIGN KEY
外键约束
- NOT NULL
插入数据需要注意
在插入数据的时候也是存在一些需要注意的地方,下面演示下。
# 创建测试表 users
[root@localhost][Demo]> CREATE TABLE users(
-> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(20) UNIQUE KEY,
-> pwd VARCHAR(30) NOT NULL,
-> age TINYINT UNSIGNED NOT NULL,
-> sex TINYINT UNSIGNED DEFAULT 0
-> );
Query OK, 0 rows affected (0.07 sec)
#看下表的结构。
[root@localhost][Demo]> SHOW COLUMNS FROM users;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | UNI | NULL | |
| pwd | varchar(30) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| sex | tinyint(3) unsigned | YES | | 0 | |
+-------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
# 插入数据模拟数据,不指定插入数据的位置。
[root@localhost][Demo]> INSERT users VALUES(NULL,'Tom','_23234awdasdwf',23,NULL);
Query OK, 1 row affected (0.03 sec)
# 再试一次,少插入一个位置的数据。如果省略了列名,就要需要将列数据补全,否则会造成插入失败的情况。
[root@localhost][Demo]> INSERT users VALUES(NULL,'Tom','_23234awdasdwf',23);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
插入时候使用DEFAULT关键字,同时可以使用表达式作为参数。
# 关键子DEFAULT也可以使用,sex字段就有DEFAULT,age 用到了表达式求值
[root@localhost][Demo]> INSERT users VALUES(NULL,'Rose','_23234awdasdwf', 2*3 + 11,DEFAULT);
Query OK, 1 row affected (0.00 sec)
# 一次性插入多条数据
[root@localhost][Demo]> INSERT users VALUES(NULL,'Atom','pwd',21,DEFAULT),(NULL,'Per','pwd',23,DEFAULT);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 23 | NULL |
| 3 | Jon | _23234awdasdwf | 17 | NULL |
| 4 | Rose | _23234awdasdwf | 17 | 0 |
| 5 | Atom | pwd | 21 | 0 |
| 6 | Per | pwd | 23 | 0 |
+----+------+----------------+-----+------+
插入表数据(二)
INSERT [INTO] table_name SET columns_name ={ expr | DEFAULT},...
- 与第一种插入方式不同的是,此方法可以使用子查询(SubQuery);而且这方式一次只能插入一条数据。
# 模拟一条测试数据。
[root@localhost][Demo]> INSERT users SET name = 'CoCo', pwd = 'pwd',age = 22;
Query OK, 1 row affected (0.01 sec)
# 查询结果。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 23 | NULL |
| 3 | Jon | _23234awdasdwf | 17 | NULL |
| 4 | Rose | _23234awdasdwf | 17 | 0 |
| 5 | Atom | pwd | 21 | 0 |
| 6 | Per | pwd | 23 | 0 |
| 7 | CoCo | pwd | 22 | 0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
插入表数据(三)
INSERT [INTO] tablle_name [(columns_name,...)] SELECT ...
- 这种方式,可以将查询结果插入到指定的数据表中。
# 创建一张测试表。只有两个字段,id 和 name。
[root@localhost][Demo]> CREATE TABLE test(
-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(30)
-> );
Query OK, 0 rows affected (0.07 sec)
# 尝试将users表中的数据插入到test中。
# 先看看当前users表中的数据。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 18 | 1 |
| 3 | Jon | _23234awdasdwf | 17 | 1 |
| 4 | Rose | _23234awdasdwf | 17 | 0 |
| 5 | Atom | pwd | 16 | 0 |
| 6 | Per | pwd | 18 | 0 |
| 8 | CoCo | agc | 17 | 1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
# 尝试将 age > 17 的name插入到test中。注意了,插入几个值和查询的值要匹配。这里提示2条成功
[root@localhost][Demo]> INSERT test(name) SELECT name FROM users WHERE age > 17;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
#检查结果,这里可以看到 users 表中 age > 17 的name值插入到test中。
[root@localhost][Demo]> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
| 2 | Per |
+----+------+
2 rows in set (0.00 sec)
更新表数据(一)
UPDATE [LOW_PRIORITY] [IGNORE] tabel_refernce SET columns_name1 = { expre1 | DEFAULT} , columns_name2 = { expre2 | DEFAULT}] ... [WHERE where_condition]
- 单表更新,这里需要注意的是,如果不添加where判断条件,会导致该表所有记录都被更新。
# 这里使用users表中的数据。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 23 | NULL |
| 3 | Jon | _23234awdasdwf | 17 | NULL |
| 4 | Rose | _23234awdasdwf | 17 | 0 |
| 5 | Atom | pwd | 21 | 0 |
| 6 | Per | pwd | 23 | 0 |
| 7 | CoCo | pwd | 22 | 0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
# 这条SQL语句表示让这个表中所有的age 都 + 5;
[root@localhost][Demo]> UPDATE users SET age = age + 5;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
# 查看结果,已经全部更新。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 28 | NULL |
| 3 | Jon | _23234awdasdwf | 22 | NULL |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
| 5 | Atom | pwd | 26 | 0 |
| 6 | Per | pwd | 28 | 0 |
| 7 | CoCo | pwd | 27 | 0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
# 添加where_condition。将age > 25 的记录 age - 5;
[root@localhost][Demo]> UPDATE users SET age = age - 5 WHERE age > 25;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
# 查看结果
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 23 | NULL |
| 3 | Jon | _23234awdasdwf | 22 | NULL |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
| 5 | Atom | pwd | 21 | 0 |
| 6 | Per | pwd | 23 | 0 |
| 7 | CoCo | pwd | 22 | 0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
更新表数据(二)
- 多表更新:
UPDATE table_reference SET columns_name = { expr1 | DEFAULT } [ columns_name2 = { expr2 | DEFAULT }]... [WHERE where_condition] - 但是呢多表更新实际上是需要使用要连接,这里不做过多的皆是,简单的演示一下。
# 创建一张user_names 表来存储用户的名字,其实没啥意义,就是演示下。
[root@localhost][Demo]> CREATE TABLE user_names(
-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(40) NOT NULL,
-> uid TINYINT UNSIGNED NOT NULL
-> );
Query OK, 0 rows affected (0.07 sec)
# 检查下表结构
[root@localhost][Demo]> SHOW COLUMNS FROM user_names;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(40) | NO | | NULL | |
| uid | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
# 我们先吧用户的名字和主键都插入进来。
[root@localhost][Demo]> INSERT INTO user_names(uid,name) SELECT id , name FROM users ;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
[root@localhost][Demo]> SELECT * FROM user_names;
+----+------+-----+
| id | name | uid |
+----+------+-----+
| 1 | Atom | 5 |
| 2 | CoCo | 8 |
| 3 | Jon | 3 |
| 4 | Per | 6 |
| 5 | Rose | 4 |
| 6 | Tom | 1 |
+----+------+-----+
6 rows in set (0.00 sec)
# 这个时候吧users表中某个用户的名字修改一次,在更新到user_names表中去。
[root@localhost][Demo]> UPDATE users SET name = 'Misa' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 最后演示多表更新
[root@localhost][Demo]> UPDATE user_names AS un INNER JOIN users AS u ON un.uid = u.id SET un.name = u.name;
Query OK, 1 row affected (0.00 sec)
Rows matched: 6 Changed: 1 Warnings: 0
# 这里可以看到uid 为 1 的名字已经发生了变化。
[root@localhost][Demo]> SELECT * FROM user_names;
+----+------+-----+
| id | name | uid |
+----+------+-----+
| 1 | Atom | 5 |
| 2 | CoCo | 8 |
| 3 | Jon | 3 |
| 4 | Per | 6 |
| 5 | Rose | 4 |
| 6 | Misa | 1 |
+----+------+-----+
6 rows in set (0.00 sec)
删除表数据(一)
DELETE FROM table_name [WHERE where_condition]
- 单条删除,如果省略where_condition将删除所有数据。
# 删除一条id为7的记录。
[root@localhost][Demo]> DELETE FROM users WHERE id = 7;
Query OK, 1 row affected (0.01 sec)
# 检查结果
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Misa | _23234awdasdwf | 23 | NULL |
| 3 | Jon | _23234awdasdwf | 22 | NULL |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
| 5 | Atom | pwd | 21 | 0 |
| 6 | Per | pwd | 23 | 0 |
+----+------+----------------+-----+------+
5 rows in set (0.00 sec)
这里需要注意的是,如果是自增类型主键,当被删除了最大的主键之后。再次插入数据并不会在现在的最大主键上自增。
# 在添加一条测试数据来验证下主键自增的规律。
[root@localhost][Demo]> INSERT users VALUES(NULL,'CoCo','agc',22,1);
Query OK, 1 row affected (0.05 sec)
# 结果发现直接从6到8,而7是刚才被删除的记录。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Misa | _23234awdasdwf | 23 | NULL |
| 3 | Jon | _23234awdasdwf | 22 | NULL |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
| 5 | Atom | pwd | 21 | 0 |
| 6 | Per | pwd | 23 | 0 |
| 8 | CoCo | agc | 22 | 1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
删除表数据(二)
DELETE table_name[.*] [, table_name[.*]] ... FROM table_referneces [WHERE where_condition]
# 这里使用一张表进行删除。这里吧重复密码的账号进行删除,但是只保留id最小的那一个。实际上这个操作没啥用。
[root@localhost][Demo]> DELETE t1 FROM users AS t1 LEFT JOIN (SELECT id , pwd FROM users GROUP BY pwd HAVING count(pwd) >= 2 ) AS t2 ON t1.pwd = t2.pwd WHERE t1.id > t2.id;
Query OK, 3 rows affected (0.02 sec)
# 在检查一下。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Misa | _23234awdasdwf | 18 | 1 |
| 5 | Atom | pwd | 16 | 0 |
| 8 | CoCo | agc | 17 | 1 |
| 10 | Pet | abc | 22 | 1 |
+----+------+----------------+-----+------+
查询表数据(一)
在通常情况下,大部分的SQL都是查询语句,所以查询最后说明。
SELECT select_expr[ select_expre ...]
[
FROM table_reference
[WHERE where_condition]
[GROUP BY {columns_name | position}] [ASC | DESC]
[HAVING where_condition]
[ORDER BY {columns_name | expre | position}] [ASC | DESC]
[LIMIT {[offset] row_count | row_count OFFSET}]
]
查询表达式 SELECT_EXPR
- 每个表达式标识想要的一列,必须至少有一列;多列之间使用逗号分割。* 号表示全部,table_name.* 表示该命名表的所有列。
- 查询表达式可以使用[AS] alisa_name 赋予别名。同时别名可用与GROUP BY ,ORDER BY 或者HAVING子句。字段顺序、字段的别名会影响结果集。
# 依然使用users来做测试数据。
# 只查询users表中id 和 name;并且使用使用alisa_name。
[root@localhost][Demo]> SELECT u.id AS uid , u.name AS uname FROM users AS u;
+-----+-------+
| uid | uname |
+-----+-------+
| 5 | Atom |
| 8 | CoCo |
| 3 | Jon |
| 6 | Per |
| 4 | Rose |
| 1 | Tom |
+-----+-------+
6 rows in set (0.00 sec)
# 使用陷阱,如果使用alisa_name 而没有使用AS 关键就会导致下面的问题。可能是少写了一个逗号,而结果确实一个结果集。
[root@localhost][Demo]> SELECT id name FROM users;
+------+
| name |
+------+
| 5 |
| 8 |
| 3 |
| 6 |
| 4 |
| 1 |
+------+
6 rows in set (0.00 sec)
条件表达式 GROUP BY
GROUP BY {columns_name | position} [ASC | DESC],...
- 如果没有指定WHERE子句,则显示全部数据。在WHERE表达式中,可以使用MYSQL内置函数或者是运算符。
- ASC 是升序、DESC是降序,若存在多个分组条件那就用逗号分割。
# 依然使用users表中的测试数据,但是要将sex 为NULL的数据进行修改。
[root@localhost][Demo]> UPDATE users SET sex = 1 WHERE sex IS NULL;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 23 | 1 |
| 3 | Jon | _23234awdasdwf | 22 | 1 |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
| 5 | Atom | pwd | 21 | 0 |
| 6 | Per | pwd | 23 | 0 |
| 8 | CoCo | agc | 22 | 1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
# 使用GROUP BY 来做分组。这里分组的结果就是0和1。
[root@localhost][Demo]> SELECT sex FROM users GROUP BY sex;
+------+
| sex |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.00 sec)
分组条件 HAVING
[HAVING where_condition]
- 使用HAVING只对一部分数据来分组。需要注意的是,如果使用HAVING语句,那么在查询结果中就存在对应的字段。并切与函数进行配合。
这里需改修改一处配置信息,由于本人使用的是MYSQL5.7版本,所以GROUP BY和以往不一样。
# 检查当前sql_mode;
select @@sql_mode;
5.7中默认设置了sql_mode=only_full_group_by
修改下my.cof。在[mysqld]下面添加
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
就是不要only_full_group_by这个。
简单解释下only_full_group_by是干嘛的。
使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行
# 回到 HAVING分组条件上。
[root@localhost][Demo]> SELECT sex , age FROM users GROUP BY sex HAVING age > 21;
+------+-----+
| sex | age |
+------+-----+
| 0 | 22 |
| 1 | 23 |
+------+-----+
2 rows in set (0.00 sec)
结果排序 ORDER BY
ORDER BY {columns_name | expr | position} [ASC | DESC],...
# 使用ORDER BY 来做排序 DESC ,由于默认是ASC升序;所以这里用DESC降序来演示。
[root@localhost][Demo]> SELECT * FROM users ORDER BY id DESC;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 8 | CoCo | agc | 22 | 1 |
| 6 | Per | pwd | 23 | 0 |
| 5 | Atom | pwd | 21 | 0 |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
| 3 | Jon | _23234awdasdwf | 22 | 1 |
| 1 | Tom | _23234awdasdwf | 23 | 1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
# 对多个字段进行排序。这里可以看到虽然CoCo 和 Roes 都是22age 但是id采用了降序。所以CoCo排在前面。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age , id DESC;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 5 | Atom | pwd | 21 | 0 |
| 8 | CoCo | agc | 22 | 1 |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
| 3 | Jon | _23234awdasdwf | 22 | 1 |
| 6 | Per | pwd | 23 | 0 |
| 1 | Tom | _23234awdasdwf | 23 | 1 |
+----+------+----------------+-----+------+
限制查询 LIMIT
LIMIT {offset,} row_count | row_count OFFSET offset
- 限制查询结果的返回集合。
# 这会返回第一和第二条数据。这个LIMIT和ID的排序没有一点关系的。
[root@localhost][Demo]> SELECT * FROM users LIMIT 2;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 23 | 1 |
| 3 | Jon | _23234awdasdwf | 22 | 1 |
+----+------+----------------+-----+------+
# 需要注意的是LIMIT限制查询和其他条件是没有关系的,它仅仅是在结果集中过滤。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC LIMIT 2;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 23 | 1 |
| 6 | Per | pwd | 23 | 0 |
+----+------+----------------+-----+------+
2 rows in set (0.00 sec)
# 还有一种情况就是范围。先看看全部数据。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 1 | Tom | _23234awdasdwf | 23 | 1 |
| 6 | Per | pwd | 23 | 0 |
| 3 | Jon | _23234awdasdwf | 22 | 1 |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
| 8 | CoCo | agc | 22 | 1 |
| 5 | Atom | pwd | 21 | 0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)
# 我们尝试获取结果集中第三条和第四条。这里需要注意的时候结果集的下标是从0开始的。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC LIMIT 2 , 2;
+----+------+----------------+-----+------+
| id | name | pwd | age | sex |
+----+------+----------------+-----+------+
| 3 | Jon | _23234awdasdwf | 22 | 1 |
| 4 | Rose | _23234awdasdwf | 22 | 0 |
+----+------+----------------+-----+------+
2 rows in set (0.00 sec)
数据库表操作小结
插入
INSERT [INTO] table_name [(columns_name , ... )] VALUES(value , ...)INSERT [INTO] table_name SET columns_name ={ expr | DEFAULT},...INSERT [INTO] tablle_name [(columns_name,...)] SELECT ...
更新
UPDATE [LOW_PRIORITY] [IGNORE] tabel_refernce SET columns_name1 = { expre1 | DEFAULT} , columns_name2 = { expre2 | DEFAULT}] ... [WHERE where_condition]
删除
DELETE FROM table_name [WHERE where_condition]
查询
SELECT select_expr[ select_expre ...][
FROM table_reference
[WHERE where_condition]
[GROUP BY {columns_name | position}] [ASC | DESC]
[HAVING where_condition]
[ORDER BY {columns_name | expre | position}] [ASC | DESC]
[LIMIT {[offset] row_count | row_count OFFSET}]]
小结
插入、更新、删除都要带明确的 WHERE 条件,查询按 SELECT、GROUP BY、HAVING、ORDER BY、LIMIT 组合;列约束在建表阶段就应确定。