2. 数据库DMZ语句—增删改查

DML语句

增删改

DML: INSERT, DELETE, UPDATE
INSERT：
一次插入一行或多行数据
语法
INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE 如果重复更新之
col_name=expr
[, col_name=expr] ... ]
简化写法：
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

1. INSERT

INSERT有3种 语法：
最常见的增方法1
MariaDB [testdb]> desc custom;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| id     | smallint(5) unsigned | NO   | PRI | NULL    |       |
| name   | char(30)             | YES  |     | NULL    |       |
| gender | char(1)              | YES  |     | m       |       |
| phone  | char(11)             | YES  |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [testdb]> insert into employee (id,name,gender,phone) values(1,'a','m','10086');

MariaDB [testdb]> select * from employee;
+----+------+--------+-------+
| id | name | gender | phone |
+----+------+--------+-------+
|  1 | a    | m      | 10086 |
+----+------+--------+-------+

# 也可以多值；
MariaDB [testdb]> insert into employee (id,name,gender,phone) values(3,'c','m','10016'),(2,'b', 'n','10000');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select * from employee;
+----+------+--------+-------+
| id | name | gender | phone |
+----+------+--------+-------+
|  1 | a    | m      | 10086 |
|  2 | b    | n      | 10000 |
|  3 | c    | m      | 10016 |
+----+------+--------+-------+

增方法2：
MariaDB [testdb]> insert into employee set id=7,name='wang',gender='m',phone=1111111111;
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> select * from employee;
+----+------+--------+------------+
| id | name | gender | phone      |
+----+------+--------+------------+
|  1 | a    | m      | 10086      |
|  2 | b    | n      | 10000      |
|  3 | c    | m      | 10016      |
|  7 | wang | m      | 1111111111 |
+----+------+--------+------------+

合并employee到student

MariaDB [testdb]> insert student select * from employee where id>1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb]> desc student;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| id     | smallint(5) unsigned | NO   | PRI | NULL    |       |
| name   | char(30)             | YES  |     | NULL    |       |
| gender | char(1)              | YES  |     | m       |       |
| phone  | char(11)             | YES  |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [testdb]> select * from student;
+----+------+--------+------------+
| id | name | gender | phone      |
+----+------+--------+------------+
|  2 | b    | n      | 10000      |
|  3 | c    | m      | 10016      |
|  7 | wang | m      | 1111111111 |
+----+------+--------+------------+

2. UPDATE

UPDATE：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
注意：一定要有限制条件，否则将修改所有行的指定字段
限制条件：
WHERE
LIMIT
Mysql 选项：-U|--safe-updates| --i-am-a-dummy

MariaDB [testdb]> update student set name='zhang' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> select * from student;
+----+-------+--------+------------+
| id | name  | gender | phone      |
+----+-------+--------+------------+
|  2 | b     | n      | 10000      |
|  3 | zhang | m      | 10016      |
|  7 | wang  | m      | 1111111111 |
+----+-------+--------+------------+

MariaDB [testdb]> update student set name='zhang' ;   # 修改所有的name=zhang;

为了防止这种错误； 加安全更新；

# [root@Centos7 ~]# mysql --safe-updates
[root@Centos7 ~]# nano /etc/my.cnf.d/mysql-clients.cnf
[mysql]
safe-updates
[mysql_upgrade]

MariaDB [testdb]> update student set name='zhang';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

3. DELETE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
可先排序再指定删除的行数
注意：一定要有限制条件，否则将清空表中的所有数据
限制条件：
WHERE
LIMIT
TRUNCATE TABLE tbl_name; 清空表

部分删除：
MariaDB [testdb]> delete from student where id=7;
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> select * from student;
+----+-------+--------+-------+
| id | name  | gender | phone |
+----+-------+--------+-------+
|  2 | zhang | n      | 10000 |
|  3 | zhang | m      | 10016 |
+----+-------+--------+-------+

全部删除 truncate 效率更高；不记录日志；
MariaDB [testdb]> truncate table student;

MariaDB [testdb]> drop table student;   # 可能会卡住；

删除的小技巧：
先建立软链接，相当于文件有两个名称，数据库中就可以直接删除了‘

导入数据库

[root@Centos7 ~]# mysql -uroot -p < hellodb_innodb.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+

MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

4. SELECT 查询

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]

字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL

MariaDB [hellodb]> select stuid,name,gender,classid,teacherid from students;
+-------+---------------+--------+---------+-----------+
| stuid | name          | gender | classid | teacherid |
+-------+---------------+--------+---------+-----------+
|     1 | Shi Zhongyu   | M      |       2 |         3 |
|     2 | Shi Potian    | M      |       1 |         7 |
|     3 | Xie Yanke     | M      |       2 |        16 |

# 别名
MariaDB [hellodb]> select stuid,name as 姓名,gender,classid,teacherid from students;
+---------------+--------+---------+-----------+
| 姓名          | gender | classid | teacherid |
+---------------+--------+---------+-----------+
| Shi Zhongyu   | M      |       2 |         3 |
| Shi Potian    | M      |       1 |         7 |

MariaDB [hellodb]> select name,gender, age+10 as age from students;   # 运算
+---------------+--------+-----+
| name          | gender | age |
+---------------+--------+-----+
| Shi Zhongyu   | M      |  32 |
| Shi Potian    | M      |  32 |

MariaDB [hellodb]> select * from students where gender='M';
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |

# null的写法特殊is
MariaDB [hellodb]> select * from students where classid is not null;
MariaDB [hellodb]> select * from students where classid is null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    24 | Xu Xian     |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+

# BETWEEN   之间’  小前大后；
MariaDB [hellodb]> select * from students where age between 20 and 30;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |

# IN (element1, element2, ...)
MariaDB [hellodb]> select * from students where classid in (1,3,6);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

DISTINCT 去除重复列

SELECT DISTINCT gender FROM students;
LIKE: （通配符）
% 任意长度的任意字符
_ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：
NOT
AND
OR
XOR

# 查询班级号码 去重后的；
MariaDB [hellodb]> select distinct classid from students;
+---------+
| classid |
+---------+
|       2 |
|       1 |
|       4 |
|       3 |
|       5 |
|       7 |
|       6 |

# 通配符（推荐使用）
MariaDB [hellodb]> select * from students where name like '%an';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    20 | Diao Chan    |  19 | F      |       7 |      NULL |
|    24 | Xu Xian      |  27 | M      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+

# 正则查询： s开头的
MariaDB [hellodb]> select * from students where name rlike '^s';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     6 | Shi Qing    |  46 | M      |       5 |      NULL |
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |

分组查询

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读

DESC students;
 INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
 INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
 SELECT * FROM students WHERE id < 3;
 SELECT * FROM students WHERE gender='m';
 SELECT * FROM students WHERE gender IS NULL;
 SELECT * FROM students WHERE gender IS NOT NULL;
 SELECT * FROM students ORDER BY name DESC LIMIT 2;
 SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
 SELECT * FROM students WHERE id >=2 and id <=4
 SELECT * FROM students WHERE BETWEEN 2 AND 4
 SELECT * FROM students WHERE name LIKE ‘t%’
 SELECT * FROM students WHERE name RLIKE '.[lo].';
 SELECT id stuid,name as stuname FROM students

MariaDB [hellodb]> select count(*) from students;
+----------+
| count(*) |
+----------+
|       25 |
+----------+

MariaDB [hellodb]> select count(stuid) from students;

MariaDB [hellodb]> select classid,max(age) from students group by classid;
+---------+----------+
| classid | max(age) |
+---------+----------+
|    NULL |      100 |
|       1 |       22 |
|       2 |       53 |
|       3 |       26 |
|       4 |       32 |
|       5 |       46 |
|       6 |       23 |
|       7 |       23 |
+---------+----------+

# 分组统计；
MariaDB [hellodb]> select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F      |  19.0000 |
| M      |  33.0000 |
+--------+----------+
     
MariaDB [hellodb]> select classid,avg(age) from students group by classid having classid is not null order by classid limit 3;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|       1 |  20.5000 |
|       2 |  36.0000 |
|       3 |  20.2500 |
+---------+----------+

练习
导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
(2) 以ClassID为分组依据，显示每组的平均年龄
(3) 显示第2题中平均年龄大于30的分组及平均年龄
(4) 显示以L开头的名字的同学的信息
(5) 显示TeacherID非空的同学的相关信息
(6) 以年龄排序后，显示年龄最大的前10位同学的信息
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息

5.多表查询

5.1 union 相加，去重

SQL UNION 运算符
UNION中的每个SELECT语句必须具有相同的列数

这些列也必须具有相似的数据类型

每个SELECT语句中的列也必须以相同的顺序排列

SQL UNION 实例
以下SQL语句从 "Customers" 和"Suppliers" 表中选择所有不同的城市（只有不同的值）：

下面的 SQL 语句从 "Customers" 和 "Suppliers" 表中选取所有不同的城市（只有不同的值）：

实例
SELECT 城市 FROM Customers
UNION
SELECT 城市 FROM Suppliers
ORDER BY 城市;

注释：不能用 UNION 来列出两个表中的所有城市。如果一些客户和供应商来自同一个城市，每个城市将只被列入一个列表。UNION将只选择不同的值。请使用UNION ALL选择重复值!

SQL UNION ALL 实例
以下SQL语句使用 UNION ALL 从 "Customers"和"Suppliers" 表中选择所有城市（也是重复的值）：

实例
SELECT 城市 FROM Customers
UNION ALL
SELECT 城市 FROM Suppliers
ORDER BY 城市;

带有 WHERE 的 SQL UNION ALL
以下SQL语句使用UNIONALL从"Customers"和 "Suppliers" 表中选择所有上海市的城市（也是重复数值）：

实例
SELECT 城市, 省份 FROM Customers
WHERE 省份='上海市'
UNION ALL
SELECT 城市, 省份 FROM Suppliers
WHERE 省份='上海市'

ORDER BY 城市;

SQL UNION与WHERE
以下SQL语句从“客户”和“供应商”中选择所有不同的上海城市（只有不同的值）：

SELECT 城市, 省份 FROM Customers
WHERE 省份='上海市'
UNION 
SELECT 城市, 省份 FROM Suppliers
WHERE 省份='上海市'
ORDER BY 城市;
另一个UNION示例
以下SQL语句列出了所有客户和供应商：

SELECT '客户姓名' As Type,城市, 省份 
FROM Customers
UNION 
SELECT '供应商名称',城市, 省份 
FROM Suppliers;

MariaDB [hellodb]> select stuid,name,age,gender from students union select * from teachers;
+-------+---------------+-----+--------+
| stuid | name          | age | gender |
+-------+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |
|     2 | Shi Potian    |  22 | M      |
|     3 | Xie Yanke     |  53 | M      |
|     4 | Ding Dian     |  32 | M      |
|     5 | Yu Yutong     |  26 | M      |
|     6 | Shi Qing      |  46 | M      |
|     7 | Xi Ren        |  19 | F      |
|     8 | Lin Daiyu     |  17 | F      |
|     9 | Ren Yingying  |  20 | F      |
|    10 | Yue Lingshan  |  19 | F      |
|    11 | Yuan Chengzhi |  23 | M      |
|    12 | Wen Qingqing  |  19 | F      |
|    13 | Tian Boguang  |  33 | M      |

5.2 JOIN 交叉

参考：SQL的各种连接Join详解，都需要熟练掌握！

MariaDB [hellodb]> select * from students cross join teachers;;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  94 | M      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   4 | Lin Chaoying  |  93 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   1 | Song Jiang    |  45 | M      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   2 | Zhang Sanfeng |  94 | M      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   3 | Miejue Shitai |  77 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   4 | Lin Chaoying  |  93 | F      |