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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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)