DML 与 DQL

DML 操作-数据的增删改

添加数据

格式 insert into 表名[(字段列表)] values(值列表);

-- 标准添加（指定所有字段，给定所有的值）
mysql> insert into users(id,name,age,gender) values(4,'duan',23,'女');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
+----+--------+------+--------+
4 rows in set (0.00 sec)

-- 指定部分字段添加值
mysql> insert into users(name,age) values('zach',23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  5 | zach   |   23 | NULL   |
+----+--------+------+--------+
5 rows in set (0.00 sec)

-- 不指定字段值添加
mysql> insert into users values(null,'user',18,'男');
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  5 | zach   |   23 | NULL   |
|  6 | user   |   18 | 男     |
+----+--------+------+--------+
6 rows in set (0.00 sec)

-- 批量添加值
mysql> insert into users values
    -> (null,'stu1',16,'女'),
    -> (null,'stu2',16,'男'),
    -> (null,'stu3',16,'女');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  5 | zach   |   23 | NULL   |
|  6 | user   |   18 | 男     |
|  7 | stu1   |   16 | 女     |
|  8 | stu2   |   16 | 男     |
|  9 | stu3   |   16 | 女     |
+----+--------+------+--------+
9 rows in set (0.00 sec)

修改数据

格式 update 表名 set 字段 1=值 1，字段 2=值 2 (where 条件); 不加条件默认修改所有的

-- 将id为6的age修改为25，gender改为女
mysql> update users set age=25,gender='女' where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  5 | zach   |   23 | NULL   |
|  6 | user   |   25 | 女     |
|  7 | stu1   |   16 | 女     |
|  8 | stu2   |   16 | 男     |
|  9 | stu3   |   16 | 女     |
+----+--------+------+--------+
9 rows in set (0.00 sec)

-- 将id为7,8,9的数据年龄+1
mysql> update users set age=age+1 where id=7 or id=8 or id=9;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  5 | zach   |   23 | NULL   |
|  6 | user   |   25 | 女     |
|  7 | stu1   |   17 | 女     |
|  8 | stu2   |   17 | 男     |
|  9 | stu3   |   17 | 女     |
+----+--------+------+--------+
9 rows in set (0.00 sec)

删除数据

格式 delete from 表名 (where 条件); 不写条件就会删除表中所有数据

-- 删除users表中gender为空的数据
mysql> delete from users where gender is NULL;
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  6 | user   |   25 | 女     |
|  7 | stu1   |   17 | 女     |
|  8 | stu2   |   17 | 男     |
|  9 | stu3   |   17 | 女     |
+----+--------+------+--------+
8 rows in set (0.00 sec)

-- 删除users表中年龄在15到20之间的数据
mysql> delete from users where age between 15 and 20;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  6 | user   |   25 | 女     |
+----+--------+------+--------+
5 rows in set (0.00 sec)

DQL-MySQL 数据查询 SQL

语法格式： select 字段列表|* from 表名**[where 条件]**[group by 分组字段 [having 分组条件]]**[order by 排序字段 排序规则]**[limit 分页参数]

基础查询

-- 查询表中所有的列 所有数据
mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  6 | user   |   25 | 女     |
+----+--------+------+--------+
5 rows in set (0.00 sec)

-- 查询表中的指定字段
mysql> select name,age from users;
+--------+------+
| name   | age  |
+--------+------+
| 段段   |   24 |
| 黄黄   |   23 |
| 图图   |    1 |
| duan   |   23 |
| user   |   25 |
+--------+------+
5 rows in set (0.00 sec)

where 条件查询

• 可以在 where 子句中指定任何条件
• 可以使用 and or 等指定多个条件
• where 条件也可以应用在 update 和 delete 语句的后面
• where 子句类似程序语言中 if 条件，根据 MySQL 表中的字段值来进行数据的过滤

-- 查询users表中 age>20的数据
mysql> select * from users where age > 20;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  4 | duan   |   23 | 女     |
|  6 | user   |   25 | 女     |
+----+--------+------+--------+
4 rows in set (0.00 sec)

-- 查询users表中，name是段段的数据
mysql> select * from users where name = '段段';
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
+----+--------+------+--------+
1 row in set (0.00 sec)

-- 查询在users表中 年龄在22-24的数据
mysql> select * from users where age between 22 and 24;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  4 | duan   |   23 | 女     |
+----+--------+------+--------+
3 rows in set (0.00 sec)

-- 查询在users表中 年龄在22-24的女生数据
mysql> select * from users where gender = '女' and age between 22 and 24;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  4 | duan   |   23 | 女     |
+----+--------+------+--------+
2 rows in set (0.01 sec)

and 和 or 使用注意

举个例子

-- 查询在users表中 年龄为22或23的女生信息
mysql> select * from users where gender = '女' and age = 22 or age =23;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  2 | 黄黄   |   23 | 男     |
|  4 | duan   |   23 | 女     |
+----+--------+------+--------+
2 rows in set (0.00 sec)

可以看出 实际查询结果并不符合要求，这时的结果是：查询年龄为 22 的人，或者年龄为 23 的女生

• and 与 or 同时存在时，sql 语句执行的顺序是 and > or
• 解决办法：实用小括号

mysql> select * from users where gender = '女' and (age = 22 or age =23);
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  4 | duan |   23 | 女     |
+----+------+------+--------+
1 row in set (0.00 sec)

like 子句

可以使用 like 语句进行某个字段的模糊搜索 like 可以等价于 = 可以使用 % 进行模糊匹配，**%可以代表任意字符** 可以使用 _ 表示一个任意字符

-- 查询姓名是duan的数据
mysql> select * from users where name like 'duan';
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  4 | duan |   23 | 女     |
+----+------+------+--------+
1 row in set (0.00 sec)

-- 查询姓名中 包含n字符的数据
mysql> select * from users where name like '%n%';
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  4 | duan |   23 | 女     |
+----+------+------+--------+
1 row in set (0.00 sec)

-- 查询姓名中姓段的数据
mysql> select * from users where name like '段%';
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
+----+--------+------+--------+
1 row in set (0.00 sec)

-- 查询姓名中最后一个字为黄的
mysql> select * from users where name like '%黄';
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  2 | 黄黄   |   23 | 男     |
+----+--------+------+--------+
1 row in set (0.00 sec)

-- 查询姓名中 第二个字符为 n 的
mysql> select * from users where name like '_u%';
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  4 | duan |   23 | 女     |
+----+------+------+--------+
1 row in set (0.00 sec)

-- 查询姓名是 两个字符的数据
mysql> select * from users where name like '__';
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
+----+--------+------+--------+
3 rows in set (0.00 sec)

注意：where 子句中的 like 的_或者% 进行模糊搜索时，效率不高

• 尽可能不去使用_或%
• 如果需要使用，也尽可能不要把通配符放在开头处

MySQL 中的聚合函数（统计函数）

• max()
• min()
• count()
• sum()
• avg()

-- 计算users表中 最大年龄，最小年龄，年龄和，平均年龄
mysql> select max(age),min(age),sum(age),avg(age) from users;
+----------+----------+----------+----------+
| max(age) | min(age) | sum(age) | avg(age) |
+----------+----------+----------+----------+
|       25 |        1 |       96 |  19.2000 |
+----------+----------+----------+----------+
1 row in set (0.01 sec)

-- 美化字段名
mysql> select max(age) as max_age , min(age) min_age,sum(age) as sum_age,avg(age
) avg_age from users;
+---------+---------+---------+---------+
| max_age | min_age | sum_age | avg_age |
+---------+---------+---------+---------+
|      25 |       1 |      96 | 19.2000 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

-- 统计users表中数据量
mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.05 sec)

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

两个统计的区别

• count(*) 按照表中所有的列进行数据统计，只要其中一列上有数据，就可以计算
• count(id) 是按照指定的 id 字段进行统计，也可以使用别的字段进行统计，但是注意，如果指定的列上出现了 NULL 值，那么为 NULL 的这个数据不会被统计

mysql> update users set age = NULL where id = 6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  6 | user   | NULL | 女     |
+----+--------+------+--------+
5 rows in set (0.00 sec)

mysql> select count(age) from users;
+------------+
| count(age) |
+------------+
|          4 |
+------------+
1 row in set (0.01 sec)

Group BY 分组

group by 语句根据一个或者多个列对结果集进行分组 一般情况下，用于数据的统计或计算，配合聚合函数使用

mysql> update users set age = 28 where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 段段   |   24 | 女     |
|  2 | 黄黄   |   23 | 男     |
|  3 | 图图   |    1 | 女     |
|  4 | duan   |   23 | 女     |
|  6 | user   |   28 | 女     |
+----+--------+------+--------+
5 rows in set (0.00 sec)

-- 统计 users 表中 男生女生人数 使用分组统计
mysql> select gender,count(*) from users group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女     |        4 |
| 男     |        1 |
+--------+----------+
2 rows in set (0.00 sec)

-- 分别统计每个班级的男女生人数
mysql> select class_id,gender,count(*) from users group by class_id,gender;
+----------+--------+----------+
| class_id | gender | count(*) |
+----------+--------+----------+
|        1 | 女     |        1 |
|        1 | 男     |        1 |
|        2 | 女     |        2 |
|        2 | 男     |        1 |
+----------+--------+----------+
4 rows in set (0.00 sec)

注意啦！一般在使用 group by 语句的时候，除了聚合函数，其他在 select 后面出现的字段列都需要出现在 group by 后面

having 的用法 在分组聚合计算之后，对结果再一次进行过滤，类似于 where where 过滤的是行数据，having 过滤的是分组数据

-- 统计班级人数，并且找出班级超过2人的班级
mysql> select class_id,count(*) from users group by class_id having count(*)>2;
+----------+----------+
| class_id | count(*) |
+----------+----------+
|        2 |        3 |
+----------+----------+
1 row in set (0.00 sec)

order by 排序

在 MySQL 中使用 select 的语句查询的数据结果是根据数据在底层文件的结构来排序的 首先不要依赖默认的排序，另外在需要排序时，要使用 order by 对返回的结果进行排序

• asc 升序
• desc 降序

-- 按照年龄对结果进行排序 从大到小
mysql> select * from users order by age desc;
+----+--------+------+--------+----------+
| id | name   | age  | gender | class_id |
+----+--------+------+--------+----------+
|  6 | user   |   28 | 男     |        2 |
|  1 | 段段   |   24 | 女     |        1 |
|  2 | 黄黄   |   23 | 男     |        1 |
|  4 | duan   |   23 | 女     |        2 |
|  3 | 图图   |    1 | 女     |        2 |
+----+--------+------+--------+----------+
5 rows in set (0.01 sec)

-- 按照年龄对结果进行排序 从小到大
mysql> select * from users order by age;
+----+--------+------+--------+----------+
| id | name   | age  | gender | class_id |
+----+--------+------+--------+----------+
|  3 | 图图   |    1 | 女     |        2 |
|  2 | 黄黄   |   23 | 男     |        1 |
|  4 | duan   |   23 | 女     |        2 |
|  1 | 段段   |   24 | 女     |        1 |
|  6 | user   |   28 | 男     |        2 |
+----+--------+------+--------+----------+
5 rows in set (0.00 sec)

-- 多个字段的排序，先按照age字段从小到大排序，age相同的情况下，按照id排序 从小到大
mysql> select * from users order by age,id;
+----+--------+------+--------+----------+
| id | name   | age  | gender | class_id |
+----+--------+------+--------+----------+
|  3 | 图图   |    1 | 女     |        2 |
|  2 | 黄黄   |   23 | 男     |        1 |
|  4 | duan   |   23 | 女     |        2 |
|  1 | 段段   |   24 | 女     |        1 |
|  6 | user   |   28 | 男     |        2 |
+----+--------+------+--------+----------+
5 rows in set (0.00 sec)

-- 多个字段的排序，按照age从小到大排序，在age相同的情况下 按照id从大到小排序
mysql> select * from users order by age,id desc;
+----+--------+------+--------+----------+
| id | name   | age  | gender | class_id |
+----+--------+------+--------+----------+
|  3 | 图图   |    1 | 女     |        2 |
|  4 | duan   |   23 | 女     |        2 |
|  2 | 黄黄   |   23 | 男     |        1 |
|  1 | 段段   |   24 | 女     |        1 |
|  6 | user   |   28 | 男     |        2 |
+----+--------+------+--------+----------+
5 rows in set (0.00 sec)

limit 数据分页

• limit n 提取 n 条数据
• limit m,n 跳过 m 条数据 提取 n 条数据

-- 查询users表中的前三条数据
mysql> select * from users limit 3;
+----+--------+------+--------+----------+
| id | name   | age  | gender | class_id |
+----+--------+------+--------+----------+
|  1 | 段段   |   24 | 女     |        1 |
|  2 | 黄黄   |   23 | 男     |        1 |
|  3 | 图图   |    1 | 女     |        2 |
+----+--------+------+--------+----------+
3 rows in set (0.00 sec)

-- 查询users表中跳过前两条，取三条数据
mysql> select * from users limit 2,3;
+----+--------+------+--------+----------+
| id | name   | age  | gender | class_id |
+----+--------+------+--------+----------+
|  3 | 图图   |    1 | 女     |        2 |
|  4 | duan   |   23 | 女     |        2 |
|  6 | user   |   28 | 男     |        2 |
+----+--------+------+--------+----------+
3 rows in set (0.00 sec)

-- limit的应用，
-- 每页显示10条数据
limit 0,10
limit 10,10
limit 20,10
limit 30,10

-- 提取 users表中年龄最大的三个用户数据
mysql> select * from users order by age desc limit 3;
+----+--------+------+--------+----------+
| id | name   | age  | gender | class_id |
+----+--------+------+--------+----------+
|  6 | user   |   28 | 男     |        2 |
|  1 | 段段   |   24 | 女     |        1 |
|  2 | 黄黄   |   23 | 男     |        1 |
+----+--------+------+--------+----------+
3 rows in set (0.00 sec)

课后练习题

1. class_id 为 2 的男女生人数
2. 获取每个 class 平均年龄，并按照平均年龄从大到小排序
3. 统计每个班级的人数 按照从大到小排序
4. 获取班级人数最多的，班级 id 信息

-- 1
mysql> select class_id,gender,count(*) from users where class_id = 2 group by ge
nder;
+----------+--------+----------+
| class_id | gender | count(*) |
+----------+--------+----------+
|        2 | 女     |        2 |
|        2 | 男     |        1 |
+----------+--------+----------+
2 rows in set (0.00 sec)

-- 2
mysql> select class_id,avg(age) from users group by class_id order by avg(age) desc;
+----------+----------+
| class_id | avg(age) |
+----------+----------+
|        1 |  23.5000 |
|        2 |  17.3333 |
+----------+----------+
2 rows in set (0.00 sec)

-- 3
mysql> select class_id,count(*) from users group by class_id order by count(*) desc;
+----------+----------+
| class_id | count(*) |
+----------+----------+
|        2 |        3 |
|        1 |        2 |
+----------+----------+
2 rows in set (0.00 sec)

-- 4
mysql> select class_id,count(*) from users group by class_id order by count(*) desc limit 1;
+----------+----------+
| class_id | count(*) |
+----------+----------+
|        2 |        3 |
+----------+----------+
1 row in set (0.00 sec)

MySQL 数据库导入导出和授权 (CCL)

数据的导出

1.数据库的导出

语法格式： mysqldump -u root -p 数据库名 > 要保存的路径/tlxy.sql

zachary@MacBook-Pro ~ % mysqldump -u root -p tlxy > /Users/zachary/Desktop/tlxy.sql
Enter password:
zachary@MacBook-Pro ~ %

2.数据表的导出

语法格式： mysqldump -u root -p 数据库名 数据表名 > 要保存的路径/tlxy.sql

zachary@MacBook-Pro ~ % mysqldump -u root -p tlxy users > /Users/zachary/Desktop/tlxy.sql
Enter password:
zachary@MacBook-Pro ~ %

数据的导入

1.数据库的导入

将导出的数据库的所有的表，导入到本地的一个数据库内 语法格式： mysql -u root -p 新数据库 < 要导入的数据库路径

2.数据表的导入

将导出的一张数据包，导入到某个已存在的数据库中 mysql -u root -p 数据库 < 要导入的数据表

权限管理

mysql 中的 root 用户是数据库中权限最高的用户，千万不要用在项目中 可以给不同的用户，或者项目，创建不同的 MySQL 用户，并适当的授权，完成数据库的相关操作 这样就一定程度上保证了数据库的安全

• 查看数据库中所有的用户

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | zachary          |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

创建用户的语法格式： grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’

-- 在mysql中 创建一个 zachary 用户， 授权可以对tlxy这个库的所有表进行添加和查询的权限
grant select,insert on tlxy.* to zachary@'%' identified by '123456';

-- 给duan授权对tlxy这个库的所有表的所有权限
grant all on tlxy.* to duan@'%' identified by '123456';

-- 删除用户
drop user 'zachary'@'%';

更新: 2024-01-23 06:39:32
原文: https://www.yuque.com/zacharyblock/cx2om6/lidc7icq01wycq94