基本查询【MySQL】
文章目录
- 基本查询
- 插入时是否更新
- 替换
- 查询
- 指定列查询
- 查询字段为表达式
- 为查询结果指定别名
- 结果去重
- where条件
- NULL 的查询
- 结果排序
- 筛选分页结果
- Update
- Delete
- 截断表
- 聚合函数
- 分组(group by)
- having && where
基本查询
建表
mysql> create table Student (
-> id int unsigned primary key auto_increment,
-> sn int unsigned unique key comment '学号',
-> name varchar(20) not null ,
-> qq varchar(20) unique key
-> );
指定列单行插入
mysql> insert into Student (sn,name,qq) values (123,'苏雪卿','4567890' ) ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
+----+------+-----------+---------+
1 row in set (0.00 sec)
全列插入
mysql> insert into Student values (10,222,'林游星','12346' ) ;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
+----+------+-----------+---------+
2 rows in set (0.00 sec)
into可以省略
mysql> insert Student values (11,333,'林斩令','1254546' ) ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
+----+------+-----------+---------+
3 rows in set (0.00 sec)
多行插入
mysql> insert into Student values (12,127,'曹操','3515545') ,(14,128,'许褚','545445') ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
+----+------+-----------+---------+
5 rows in set (0.00 sec)
mysql> insert into Student (sn,name,qq) values (5151,'诸葛亮','35155') ,(545,'单沙禹','545') ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
| 15 | 5151 | 诸葛亮 | 35155 |
| 16 | 545 | 单沙禹 | 545 |
+----+------+-----------+---------+
7 rows in set (0.00 sec)
插入时是否更新
如果发生键值冲突 ,需要修改数据
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
| 15 | 5151 | 诸葛亮 | 35155 |
| 16 | 545 | 单沙禹 | 545 |
+----+------+-----------+---------+
7 rows in set (0.00 sec)
mysql> insert into Student values (15,20,'xuchu','8888') on duplicate key update sn=20,name='xuchu',qq=''8888';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
| 15 | 20 | xuchu | 8888 |
| 16 | 545 | 单沙禹 | 545 |
+----+------+-----------+---------+
7 rows in set (0.00 sec)
有冲突：Query OK, 2 rows affected (0.00 sec)
没有冲突：Query OK, 1 rows affected (0.00 sec)
要更新的数据和之前的数据一样，就不更新了：Query OK, 0 rows affected (0.00 sec)
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
| 15 | 20 | xuchu | 8888 |
| 16 | 545 | 单沙禹 | 545 |
+----+------+-----------+---------+
7 rows in set (0.00 sec)
mysql> insert into Student values (17,22,'雪如之','888') on duplicate key update sn=22,name='雪如之',qq=='888';
Query OK, 1 row affected (0.01 sec)
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
| 15 | 20 | xuchu | 8888 |
| 16 | 545 | 单沙禹 | 545 |
| 17 | 22 | 雪如之 | 888 |
+----+------+-----------+---------+
8 rows in set (0.00 sec)
mysql> insert into Student values (17,22,'雪如之','888') on duplicate key update sn=22,name='雪如之',qq='888';
Query OK, 0 rows affected (0.00 sec)
替换
1 row affected ,表中没有冲突数据，数据被插入
主键或者唯一键没有冲突，则直接插入
mysql> replace into Student (sn,name,qq) values (140,'许攸', '2222') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
| 15 | 20 | xuchu | 8888 |
| 16 | 545 | 单沙禹 | 545 |
| 17 | 22 | 雪如之 | 888 |
| 18 | 140 | 许攸 | 2222 |
+----+------+-----------+---------+
9 rows in set (0.00 sec)
2 rows affected ,表中有冲突数据，删除后重新插入
主键或者唯一键如果冲突，则删除后再插入
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
| 15 | 20 | xuchu | 8888 |
| 16 | 545 | 单沙禹 | 545 |
| 17 | 22 | 雪如之 | 888 |
| 18 | 140 | 许攸 | 2222 |
+----+------+-----------+---------+
9 rows in set (0.00 sec)
mysql> replace into Student (sn,name,qq) values (140,'许攸1', '2222') ;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from Student;
+----+------+-----------+---------+
| id | sn | name | qq |
+----+------+-----------+---------+
| 1 | 123 | 苏雪卿 | 4567890 |
| 10 | 222 | 林游星 | 12346 |
| 11 | 333 | 林斩令 | 1254546 |
| 12 | 127 | 曹操 | 3515545 |
| 14 | 128 | 许褚 | 545445 |
| 15 | 20 | xuchu | 8888 |
| 16 | 545 | 单沙禹 | 545 |
| 17 | 22 | 雪如之 | 888 |
| 19 | 140 | 许攸1 | 2222 |
+----+------+-----------+---------+
9 rows in set (0.00 sec)
查询
全列查询
mysql> CREATE TABLE exam_result (
-> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL COMMENT '同学姓名',
-> chinese float DEFAULT 0.0 COMMENT '语文成绩',
-> math float DEFAULT 0.0 COMMENT '数学成绩',
-> english float DEFAULT 0.0 COMMENT '英语成绩'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO exam_result (name, chinese, math, english) VALUES
-> ('唐三藏', 67, 98, 56),
-> ('孙悟空', 87, 78, 77),
-> ('猪悟能', 88, 98, 90),
-> ('曹孟德', 82, 84, 67),
-> ('刘玄德', 55, 85, 45),
-> ('孙权', 70, 73, 78),
-> ('宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from exam_result ;
+----+-----------+---------+------+---------+
| 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)
指定列查询
mysql> select id from exam_result ;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00 sec)
mysql> select id ,math from exam_result ;
+----+------+
| id | math |
+----+------+
| 1 | 98 |
| 2 | 78 |
| 3 | 98 |
| 4 | 84 |
| 5 | 85 |
| 6 | 73 |
| 7 | 65 |
+----+------+
7 rows in set (0.00 sec)
查询字段为表达式
mysql> select name,math ,math+chinese+english from exam_result ;
+-----------+------+----------------------+
| name | math | math+chinese+english |
+-----------+------+----------------------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+------+----------------------+
7 rows in set (0.00 sec)
为查询结果指定别名
mysql> select name,math ,math+chinese+english as total from exam_result ;
+-----------+------+-------+
| name | math | total |
+-----------+------+-------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+------+-------+
7 rows in set (0.00 sec)
as可以省略
mysql> select name 姓名,math 数学 ,math+chinese+english 总分 from exam_result ;
+-----------+--------+--------+
| 姓名 | 数学 | 总分 |
+-----------+--------+--------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+--------+--------+
7 rows in set (0.00 sec)
mysql> select name 姓名,math 数学 ,math+10 from exam_result ;
+-----------+--------+---------+
| 姓名 | 数学 | math+10 |
+-----------+--------+---------+
| 唐三藏 | 98 | 108 |
| 孙悟空 | 78 | 88 |
| 猪悟能 | 98 | 108 |
| 曹孟德 | 84 | 94 |
| 刘玄德 | 85 | 95 |
| 孙权 | 73 | 83 |
| 宋公明 | 65 | 75 |
+-----------+--------+---------+
7 rows in set (0.00 sec)
结果去重
mysql> select distinct math from exam_result ;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
6 rows in set (0.00 sec)
where条件
比较运算符:
逻辑运算符:
mysql> select name ,english from exam_result where english<60 ;
+-----------+---------+
| name | english |
+-----------+---------+
| 唐三藏 | 56 |
| 刘玄德 | 45 |
| 宋公明 | 30 |
+-----------+---------+
3 rows in set (0.00 sec)
mysql> select name, chinese from exam_result where chinese>=80 and chinese<=90;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
3 rows in set (0.00 sec)
select name, chinese from exam_result where chinese>=80 and chinese<=90;
select name, chinese from exam_result where chinese between 80 and 90;
这两行SQL是一样的
mysql> select name, chinese from exam_result where math=58 or math=59 or math=98 or math=99;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 猪悟能 | 88 |
+-----------+---------+
2 rows in set (0.00 sec)
mysql> select name, chinese from exam_result where math in (58,59,98,99);
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 猪悟能 | 88 |
+-----------+---------+
2 rows in set (0.00 sec)
select name, chinese from exam_result where math=58 or math=59 or math=98 or math=99;
select name, chinese from exam_result where math in (58,59,98,99);
这两行SQL是一样的
% 匹配任意多个（包括 0 个）任意字符
例如：姓孙
mysql> select name, chinese from exam_result where name like '孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 孙权 | 70 |
+-----------+---------+
2 rows in set (0.00 sec)
_ 匹配严格的一个任意字符
例如：孙某
mysql> select name, chinese from exam_result where name like '孙_';
+--------+---------+
| name | chinese |
+--------+---------+
| 孙权 | 70 |
+--------+---------+
1 row in set (0.00 sec)
查询英语成绩比语文成绩好的同学
mysql> select name, chinese ,english from exam_result where chinese >english;
+-----------+---------+---------+
| name | chinese | english |
+-----------+---------+---------+
| 唐三藏 | 67 | 56 |
| 孙悟空 | 87 | 77 |
| 曹孟德 | 82 | 67 |
| 刘玄德 | 55 | 45 |
| 宋公明 | 75 | 30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)
mysql> select name ,chinese+english+math total from exam result where total <200 ;
ERROR 1146 (42S02): Table 'test_db.exam' doesn't exist
上述写法是错误的
SQL语句执行顺序：
例如：查询英语成绩+语文成绩+数学成绩 <200的同学
mysql> select name ,chinese+english+math as total from exam_result where chinese+english+math <200 ;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------+
2 rows in set (0.00 sec)
语文成绩 > 80 并且不姓孙的同学
mysql> select name ,chinese from exam_result where chinese>80 and name not like '孙%' ;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
2 rows in set (0.00 sec)
孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select name, chinese, math, english, chinese + math + english 总分 from exam_result where name like '孙_' or ( chinese + math + english > 200 and chinese < math and english > 80 ) ;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 猪悟能 | 88 | 98 | 90 | 276 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+--------+
2 rows in set (0.00 sec)
NULL 的查询
mysql> create table test(
-> id int ,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test (id ,name) values(1,'张三') ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id ,name) values(null,'张三') ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id ,name) values(1,null) ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id ,name) values(null,null) ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id ,name) values(1,'') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test ;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | NULL |
| NULL | NULL |
| 1 | |
+------+--------+
5 rows in set (0.00 sec)
查询表中name为null
mysql> select * from test where name is null ;
+------+------+
| id | name |
+------+------+
| 1 | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
查询表中name为空串
mysql> select * from test where name ='' ;
+------+------+
| id | name |
+------+------+
| 1 | |
+------+------+
1 row in set (0.00 sec)
查询表中name不为空
mysql> select * from test where name is not null;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | |
+------+--------+
3 rows in set (0.00 sec)
结果排序
desc，降序
asc，升序
mysql> select name ,math from exam_result order by math desc ;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
| 刘玄德 | 85 |
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
mysql> select name ,math from exam_result order by math asc ;
+-----------+------+
| name | math |
+-----------+------+
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
7 rows in set (0.00 sec)
NULL值比任何值都要小
mysql> select name from test order by name desc ;
+--------+
| name |
+--------+
| 张三 |
| 张三 |
| |
| NULL |
| NULL |
+--------+
5 rows in set (0.00 sec)
mysql> select name from test order by name asc;
+--------+
| name |
+--------+
| NULL |
| NULL |
| |
| 张三 |
| 张三 |
+--------+
5 rows in set (0.00 sec)
查询同学各门成绩，依次按 数学降序，英语降序，语文升序的方式
mysql> select name, math , english ,chinese from exam_result order by math desc , english desc ,chinese asc ;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 猪悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
order by 默认升序
查询同学及总分，由高到低
mysql> select name, math+chinese+english as total from exam_result order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
需要有数据才排序
查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
mysql> select name, math from exam_result where name like '孙%' or name like '曹%' order by math des
sc;
+-----------+------+
| name | math |
+-----------+------+
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
+-----------+------+
3 rows in set (0.00 sec)
筛选分页结果
mysql> select * from exam_result limit 3 ;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 2,4;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)
mysql> select * from exam_result 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)
Update
将孙悟空同学的数学成绩变更为 80
mysql> select * from exam_result ;
+----+-----------+---------+------+---------+
| 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)
mysql> update exam_result set math=80 where name='孙悟空' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam_result ;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 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.01 sec)
将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
mysql> update exam_result set math=60 ,chinese=70 where name='曹孟德' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam_result ;
+----+-----------+---------+------+---------+
| 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 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
mysql> select name , math+chinese+english as total from exam_result order by total asc limit 3 ;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 曹孟德 | 197 |
+-----------+-------+
3 rows in set (0.00 sec)
mysql> update exam_result set math=math+30 order by math+chinese+english asc limit 3 ;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select name, math+chinese+english total from exam_result order by total limit 3 ;
Display all 909 possibilities? (y or n)
mysql> select name, math+chinese+english total from exam_result order by total asc limit 3 ;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 200 |
| 刘玄德 | 215 |
| 唐三藏 | 221 |
+-----------+-------+
3 rows in set (0.00 sec)
mysql> select name , math+chinese+english from exam_result order by math+chinese+english asc ;
+-----------+----------------------+
| name | math+chinese+english |
+-----------+----------------------+
| 宋公明 | 200 |
| 刘玄德 | 215 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 227 |
| 孙悟空 | 244 |
| 猪悟能 | 276 |
+-----------+----------------------+
7 rows in set (0.00 sec)
Delete
删除孙悟空同学的考试成绩
mysql> select * from exam_result where name='孙悟空';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 87 | 80 | 77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
mysql> delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.01 sec)
找到倒数第一同学的考试成绩，并删除
mysql> select name , math+chinese+english as total from exam_result order by total asc limit 1;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 200 |
+-----------+-------+
1 row in set (0.00 sec)
mysql> delete from exam_result order by math+chinese+english asc limit 1 ;
Query OK, 1 row affected (0.00 sec)
mysql> select name , math+chinese+english as total from exam_result order by total asc limit 1;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 215 |
+-----------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE for_delete (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table for_delete \G ;
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table for_delete \G ;
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from for_delete ;
Empty set (0.00 sec)
删除表后，自增还是4
截断表
TRUNCATE
只能对整表操作，不能像 DELETE 一样针对部分数据操作；
实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事
物，所以无法回滚
会重置 AUTO_INCREMENT 项
mysql> CREATE TABLE for_truncate (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table for_truncate \G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> truncate for_truncate ;
Query OK, 0 rows affected (0.14 sec)
mysql> select * from for_truncate ;
Empty set (0.00 sec)
mysql> show create table for_truncate \G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
删除表中的的重复复记录，重复的数据只能有一份
mysql> select * from duplicate_table ;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 100 | aaa |
| 200 | bbb |
| 200 | bbb |
| 200 | bbb |
| 300 | ccc |
+------+------+
6 rows in set (0.00 sec)
mysql> select distinct * from duplicate_table ;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
--创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
mysql> create table no_duplicate_table like duplicate_table;
Query OK，0 rows affected (0.04 sec)
-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
mysql> insert into no_duplicate_table select distinct * from duplicate_table ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from no_duplicate_table ;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
-- 重命名表
mysql> RENAME TABLE duplicate_table TO old_duplicate_table,
-> no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
聚合函数
COUNT([DISTINCT] expr) 返回查询到的数据的 数量
SUM([DISTINCT] expr) 返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr) 返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr) 返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr) 返回查询到的数据的 最小值，不是数字没有意义
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 90 | 67 |
| 5 | 刘玄德 | 55 | 115 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)
mysql> select count(*) from exam_result ;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) 总数 from exam_result ;
+--------+
| 总数 |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)
mysql> select count(1) 总数 from exam_result ;
+--------+
| 总数 |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)
mysql> select count(2) 总数 from exam_result ;
+--------+
| 总数 |
+--------+
| 5 |
+--------+
1 row in set (0.01 sec)
mysql> select count(math) from exam_result ;
+-------------+
| count(math) |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
统计本次考试，不重复的数学成绩分数个数
mysql> select count(distinct math) as res from exam_result ;
+-----+
| res |
+-----+
| 4 |
+-----+
1 row in set (0.00 sec)
统计数学成绩总分
mysql> select sum(math) from exam_result ;
+-----------+
| sum(math) |
+-----------+
| 474 |
+-----------+
1 row in set (0.01 sec)
统计平均分
mysql> select sum(math)/count(*) from exam_result ;
+--------------------+
| sum(math)/count(*) |
+--------------------+
| 94.8 |
+--------------------+
1 row in set (0.00 sec)
mysql> select avg(math) from exam_result ;
+-----------+
| avg(math) |
+-----------+
| 94.8 |
+-----------+
1 row in set (0.00 sec)
返回英语最高分
mysql> select max(english) from exam_result ;
+--------------+
| max(english) |
+--------------+
| 90 |
+--------------+
1 row in set (0.00 sec)
返回 > 70 分以上的数学最低分
mysql> select min(math) from exam_result where math>70 ;
+-----------+
| min(math) |
+-----------+
| 73 |
+-----------+
1 row in set (0.00 sec)
分组(group by)
分组的目的是为了进行分组之后，方便进行聚合统计
显示每个部门的平均工资和最高工资
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select deptno, max(sal) 最高, avg(sal) 平均 from emp group by deptno ;
+--------+---------+-------------+
| deptno | 最高 | 平均 |
+--------+---------+-------------+
| 10 | 5000.00 | 2916.666667 |
| 20 | 3000.00 | 2175.000000 |
| 30 | 2850.00 | 1566.666667 |
+--------+---------+-------------+
3 rows in set (0.00 sec)
显示每个部门的每种岗位的平均工资和最低工资
mysql> select deptno,job ,avg(sal) 平均, min(sal) 最低 from emp group by deptno,job ;
+--------+-----------+-------------+---------+
| deptno | job | 平均 | 最低 |
+--------+-----------+-------------+---------+
| 10 | CLERK | 1300.000000 | 1300.00 |
| 10 | MANAGER | 2450.000000 | 2450.00 |
| 10 | PRESIDENT | 5000.000000 | 5000.00 |
| 20 | ANALYST | 3000.000000 | 3000.00 |
| 20 | CLERK | 950.000000 | 800.00 |
| 20 | MANAGER | 2975.000000 | 2975.00 |
| 30 | CLERK | 950.000000 | 950.00 |
| 30 | MANAGER | 2850.000000 | 2850.00 |
| 30 | SALESMAN | 1400.000000 | 1250.00 |
+--------+-----------+-------------+---------+
9 rows in set (0.00 sec)
指定列名，实际分组，是用该列的不同的行数据来进行分组的
分组的条件deptno，组内一定是相同的 ，相同就意味着可以被聚合压缩
分组，就是把一组按照特定条件拆成了多个组，进行各自组内的统计
将分组理解为分表，就是把一张表按照特定条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计
显示平均工资低于2000的部门和它的平均工资
ysql> select deptno , avg(sal) deptavg from emp group by deptno having deptavg < 2000 ;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)
having是对聚合后的统计数据，条件筛选
having && where
having 和where 都能条件筛选
mysql> select * from emp ;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select deptno,job,avg(sal) myavg from emp where ename !='SMITH' group by deptno , job having myavg<2000 ;
+--------+----------+-------------+
| deptno | job | myavg |
+--------+----------+-------------+
| 10 | CLERK | 1300.000000 |
| 20 | CLERK | 1100.000000 |
| 30 | CLERK | 950.000000 |
| 30 | SALESMAN | 1400.000000 |
+--------+----------+-------------+
4 rows in set (0.00 sec)
having和 where ， 条件筛选的阶段是不同的