全面解析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 比较运算符
2.6.2 逻辑运算符
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)
#基础语法#默认ascselect 通配符/列名 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)
