全面解析MySQL(2)——CRUD基础

1.Create

Create(创建)：添加新数据到数据库中

#基础语法
insert into table_name (column1,column2,column3, ...) 
values (value1,value2,value3, ...);

1.1 单行全列插入

value中值的数量和顺序必须和column⼀致

describe demo1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#插入(id=1,name='张三',age=10)的记录
mysql> insert into demo1 values (1,'张三',10);
Query OK, 1 row affected (0.01 sec)
#插入结果如下
mysql> select * from demo1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
+------+------+------+
1 row in set (0.00 sec)

1.2 单行指定列插入

demo1:指定(id,name,age)三列插入，相当于全列插入

insert into demo1 (id,name,age) values (2,'李四',11);
Query OK, 1 row affected (0.00 sec)mysql> select * from demo1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
|    2 | 李四 |   11 |
+------+------+------+
2 rows in set (0.00 sec)

demo2:指定(id,name)两列插入

insert into demo1 (id,name) values (3,'王五');
Query OK, 1 row affected (0.00 sec)mysql> select * from demo1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
|    2 | 李四 |   11 |
|    3 | 王五 | NULL |
+------+------+------+
3 rows in set (0.00 sec)

1.3 多行插入

在⼀条insert语句中也可以一次插入多行数据

insert into demo1 values (4,'赵六',12),(5,'田七',13);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from demo1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
|    2 | 李四 |   11 |
|    3 | 王五 | NULL |
|    4 | 赵六 |   12 |
|    5 | 田七 |   13 |
+------+------+------+
5 rows in set (0.00 sec)

2.Read

Read(读取)：查询或获取现有数据

#基础语法
select 通配符/列名 from 表名

2.1 全列查询

#(*)通配符
#使⽤(*)可以查询表中(所有列)的值
select * from demo1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
|    2 | 李四 |   11 |
|    3 | 王五 | NULL |
|    4 | 赵六 |   12 |
|    5 | 田七 |   13 |
+------+------+------+
5 rows in set (0.00 sec)

2.1 指定列查询

指定(id,name)两列查询

select id,name from demo1;
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 李四 |
|    3 | 王五 |
|    4 | 赵六 |
|    5 | 田七 |
+------+------+
5 rows in set (0.00 sec)
#注意1：可以指定多列查询，也可以指定单列查询
#注意2：查询的顺序和指定的顺序有关
select name,id from demo1;
+------+------+
| name | id   |
+------+------+
| 张三 |    1 |
| 李四 |    2 |
| 王五 |    3 |
| 赵六 |    4 |
| 田七 |    5 |
+------+------+
5 rows in set (0.00 sec)

2.3 表达式作为查询条件

select * from exam;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    5 | 刘玄德 |      55 |   85 |      45 |
|    6 | 孙权   |      70 |   73 |      78 |
|    7 | 宋公明 |      75 |   65 |      30 |
+------+--------+---------+------+---------+
7 rows in set (0.00 sec)
#将表达式(english + 10)作为查询条件
mysql> select id,name,chinese,math,(english+10) from exam;
+------+--------+---------+------+--------------+
| id   | name   | chinese | math | (english+10) |
+------+--------+---------+------+--------------+
|    1 | 唐三藏 |      67 |   98 |           66 |
|    2 | 孙悟空 |      87 |   78 |           87 |
|    3 | 猪悟能 |      88 |   98 |          100 |
|    4 | 曹孟德 |      82 |   84 |           77 |
|    5 | 刘玄德 |      55 |   85 |           55 |
|    6 | 孙权   |      70 |   73 |           88 |
|    7 | 宋公明 |      75 |   65 |           40 |
+------+--------+---------+------+--------------+
7 rows in set (0.00 sec)

2.4 为查询结果指定别名

关键字：as

#为(chinese+math+english)指定别名为(总分)
select id,name,(chinese+math+english) as '总分' from exam;
+------+--------+------+
| id   | name   | 总分 |
+------+--------+------+
|    1 | 唐三藏 |  221 |
|    2 | 孙悟空 |  242 |
|    3 | 猪悟能 |  276 |
|    4 | 曹孟德 |  233 |
|    5 | 刘玄德 |  185 |
|    6 | 孙权   |  221 |
|    7 | 宋公明 |  170 |
+------+--------+------+
7 rows in set (0.00 sec)

2.5 去重查询

关键字：distinct

2.5.1 去重查询(单列)

select * from exam;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    5 | 刘玄德 |      55 |   85 |      45 |
|    6 | 孙权   |      70 |   73 |      78 |
|    7 | 宋公明 |      75 |   65 |      30 |
|    8 | 关云长 |      70 |   80 |      90 |
+------+--------+---------+------+---------+
8 rows in set (0.00 sec)mysql> select english from exam;
+---------+
| english |
+---------+
|      56 |
|      77 |
|      90 |#第一个90分
|      67 |
|      45 |
|      78 |
|      30 |
|      90 |#第二个90分
+---------+
8 rows in set (0.00 sec)
#对(english)这一列进行去重
mysql> select distinct english from exam;
+---------+
| english |
+---------+
|      56 |
|      77 |
|      90 |#第一个90分
|      67 |
|      45 |
|      78 |
|      30 |
+---------+
7 rows in set (0.00 sec)

注意：

2.5.2 去重查询(多列)

select * from exam;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    5 | 刘玄德 |      55 |   85 |      45 |
|    6 | 孙权   |      70 |   73 |      78 |
|    7 | 宋公明 |      75 |   65 |      30 |
|    8 | 关云长 |      70 |   80 |      90 |
|    8 | 张翼德 |      70 |   80 |      90 |
+------+--------+---------+------+---------+
9 rows in set (0.00 sec)mysql> select math,english from exam;
+------+---------+
| math | english |
+------+---------+
|   98 |      56 |
|   78 |      77 |
|   98 |      90 |
|   84 |      67 |
|   85 |      45 |
|   73 |      78 |
|   65 |      30 |
|   80 |      90 |#(1)math=80,english=90
|   80 |      90 |#(2)math=80,english=90
+------+---------+
9 rows in set (0.00 sec)
#同时对(math,english)两列同时进行去重查询
#要保证两行中math和english的分数要分别相同
mysql> select distinct math,english from exam;
+------+---------+
| math | english |
+------+---------+
|   98 |      56 |
|   78 |      77 |
|   98 |      90 |
|   84 |      67 |
|   85 |      45 |
|   73 |      78 |
|   65 |      30 |
|   80 |      90 |
+------+---------+
8 rows in set (0.00 sec)

注意：

2.6 条件查询

关键字：where

#基础语法
select 通配符/列名 from 表名 where (条件)

2.6.1 比较运算符

运算符	说明
>,<,>=,<=	大于，小于，大于等于，小于等于
=	等于(MySQL中不存在==)
<=>	用于null的比较。例如：null <=> null 的结果是true，null = null 的结果还是null
!=,<>	不等于
value between A and B	范围匹配，如果value在[A,B]之间返回true
value not between A and B	范围匹配，如果(value < A并且 value > B)返回true
value in (option1,option2…)	如果value与某一option匹配则返回true，not in表示取反
is null/is not null	是null/不是null
_	模糊匹配，表示(一个)任意字符
like	模糊匹配，表示(任意个)任意字符

2.6.2 逻辑运算符

运算符	说明
and	逻辑与，全true为true，有false为false
or	逻辑或，全false为false，有true为true
not	逻辑非，条件为true，结果为false

2.6.3 比较条件查询

demo1:查询语文成绩比数学高的记录

select * from exam where chinese > math;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 孙悟空 |      87 |   78 |      77 |
|    7 | 宋公明 |      75 |   65 |      30 |
+------+--------+---------+------+---------+
2 rows in set (0.01 sec)

demo2:查询英语大于60的记录

select * from exam where english > 60;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    6 | 孙权   |      70 |   73 |      78 |
|    8 | 关云长 |      70 |   80 |      90 |
|    8 | 张翼德 |      70 |   80 |      90 |
+------+--------+---------+------+---------+
6 rows in set (0.00 sec)

demo3:查询总分大于200的记录

select id,name,(chinese + math + english) as '总分' from exam where (chinese + math + english) > 200;
+------+--------+------+
| id   | name   | 总分 |
+------+--------+------+
|    1 | 唐三藏 |  221 |
|    2 | 孙悟空 |  242 |
|    3 | 猪悟能 |  276 |
|    4 | 曹孟德 |  233 |
|    6 | 孙权   |  221 |
|    8 | 关云长 |  240 |
|    8 | 张翼德 |  240 |
+------+--------+------+
7 rows in set (0.00 sec)

注意：不能在where条件中进行取别名的操作

2.6.4 逻辑条件查询

demo1:查询语文成绩大于80分并且数学成绩大于80分的同学

select * from exam where chinese > 80 and math > 80;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      82 |   84 |      67 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)

demo2:查询语文成绩大于80或者数学成绩大于80分的同学

select * from exam where chinese > 80 or math > 80;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    5 | 刘玄德 |      55 |   85 |      45 |
+------+--------+---------+------+---------+
5 rows in set (0.00 sec)

注意：and的优先级大于or

2.6.5 范围查询

demo1:查询语文成绩在[80,90]分的记录

select * from exam where chinese between 80 and 90;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      82 |   84 |      67 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)

demo2:查询数学成绩是78或者79或者98或者99分的记录

#使用or实现
select * from exam where math = 78 or math = 79 or math = 98 or math = 99;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)
#使用in实现
mysql> select * from exam where math in (78,79,98,99);
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)

2.6.6 模糊查询

demo1:查询姓孙的记录

select * from exam where name like '孙%';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 孙悟空 |      87 |   78 |      77 |
|    6 | 孙权   |      70 |   73 |      78 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)

demo2:查询姓孙并且名字只有两个字的记录

select * from exam where name like '孙_';
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    6 | 孙权 |      70 |   73 |      78 |
+------+------+---------+------+---------+
1 row in set (0.00 sec)

2.6.7 null(空)值查询

demo1:查询英语成绩为null的记录

select * from exam where english is null;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|   11 | 黄汉升 |      70 |   85 |    NULL |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)mysql> select * from exam where english <=> null;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|   11 | 黄汉升 |      70 |   85 |    NULL |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)

demo2:查询英语成绩不为null的记录

select * from exam where english != null;
Empty set (0.00 sec)mysql> select * from exam where english <> null;
Empty set (0.00 sec)mysql> select * from exam where english is not null;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    5 | 刘玄德 |      55 |   85 |      45 |
|    6 | 孙权   |      70 |   73 |      78 |
|    7 | 宋公明 |      75 |   65 |      30 |
|    8 | 关云长 |      70 |   80 |      90 |
|    8 | 张翼德 |      70 |   80 |      90 |
|   10 | 赵子龙 |      70 |   85 |      85 |
+------+--------+---------+------+---------+
10 rows in set (0.00 sec)

2.7 排序

• asc：升序
• desc：降序，不是查看表结构的desc(describe)

#基础语法
#默认asc
select 通配符/列名 from 表名 (where...) order by 列名 (asc/desc);

demo1:按照语文成绩升序

select * from exam order by chinese asc;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    5 | 刘玄德 |      55 |   85 |      45 |
|    1 | 唐三藏 |      67 |   98 |      56 |
|    6 | 孙权   |      70 |   73 |      78 |
|    8 | 关云长 |      70 |   80 |      90 |
|    8 | 张翼德 |      70 |   80 |      90 |
|   10 | 赵子龙 |      70 |   85 |      85 |
|   11 | 黄汉升 |      70 |   85 |    NULL |
|    7 | 宋公明 |      75 |   65 |      30 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
+------+--------+---------+------+---------+
11 rows in set (0.00 sec)

demo2:按照语文成绩降序

select * from exam order by chinese desc;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    3 | 猪悟能 |      88 |   98 |      90 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    7 | 宋公明 |      75 |   65 |      30 |
|    6 | 孙权   |      70 |   73 |      78 |
|    8 | 关云长 |      70 |   80 |      90 |
|    8 | 张翼德 |      70 |   80 |      90 |
|   10 | 赵子龙 |      70 |   85 |      85 |
|   11 | 黄汉升 |      70 |   85 |    NULL |
|    1 | 唐三藏 |      67 |   98 |      56 |
|    5 | 刘玄德 |      55 |   85 |      45 |
+------+--------+---------+------+---------+
11 rows in set (0.00 sec)

demo3:按照数学降序，英语升序，语⽂升序

select * from exam order by math desc,english asc,chinese asc;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|   11 | 黄汉升 |      70 |   85 |    NULL |
|    5 | 刘玄德 |      55 |   85 |      45 |
|   10 | 赵子龙 |      70 |   85 |      85 |
|    4 | 曹孟德 |      82 |   84 |      67 |
|    8 | 关云长 |      70 |   80 |      90 |
|    8 | 张翼德 |      70 |   80 |      90 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    6 | 孙权   |      70 |   73 |      78 |
|    7 | 宋公明 |      75 |   65 |      30 |
+------+--------+---------+------+---------+
11 rows in set (0.00 sec)

2.8 分页查询

#基础语法1:默认从0开始，筛选num条记录
select 通配符/列名 from 表名 (where...) (order by ...) limit num;
#基础语法2:从start开始，筛选num条记录
select 通配符/列名 from 表名 (where...) (order by ...) limit start,num;
#基础语法3(建议):从start开始，筛选num条记录
select 通配符/列名 from 表名 (where...) (order by ...) limit num offset start;

demo1:假设一页有三条记录，查询第一页的记录

#建议搭配(order by)使用
select * from exam order by id asc limit 3 offset 0;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   78 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)

demo2:假设一页有三条记录，查询第二页的记录

select * from exam order by id asc limit 3 offset 3;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    4 | 曹孟德 |      82 |   84 |      67 |
|    5 | 刘玄德 |      55 |   85 |      45 |
|    6 | 孙权   |      70 |   73 |      78 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)

3.Update

Update(更新)：修改数据库或系统中已存在的记录

#基础语法
update 表名 (要修改的数据) (where...) (order by...) (limit...);

demo1:将孙悟空的数学成绩变更为80分

update exam set math = 80 where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from exam where name = '孙悟空';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 孙悟空 |      87 |   80 |      77 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)

demo2:将曹孟德的数学成绩变更为60分，语文成绩变更为70

update exam set math = 60,chinese = 70 where name = '曹孟德';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from exam where name = '曹孟德';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    4 | 曹孟德 |      70 |   60 |      67 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)

注意：当update语句缺少where条件时，全表的记录都将被更新

4.Delete

Delete(删除)：从数据库或系统中移除记录

#基础语法
delete from 表名 (where...) (order by...) (limit...);

demo1:删除姓名为黄汉升的记录

delete from exam where name = '黄汉升';
Query OK, 1 row affected (0.01 sec)mysql> select * from exam;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |      67 |   98 |      56 |
|    2 | 孙悟空 |      87 |   80 |      77 |
|    3 | 猪悟能 |      88 |   98 |      90 |
|    4 | 曹孟德 |      70 |   60 |      67 |
|    5 | 刘玄德 |      55 |   85 |      45 |
|    6 | 孙权   |      70 |   73 |      78 |
|    7 | 宋公明 |      75 |   65 |      30 |
|    8 | 关云长 |      70 |   80 |      90 |
|    8 | 张翼德 |      70 |   80 |      90 |
|   10 | 赵子龙 |      70 |   85 |      85 |
+------+--------+---------+------+---------+
10 rows in set (0.00 sec)

demo2:删除整张表的记录

delete from exam;
Query OK, 10 rows affected (0.01 sec)mysql> select * from exam;
Empty set (0.00 sec)