CRUD：增删改啥
1.Create
create table students(
id int unsigned primary key auto_increment,
sn int not null unique comment '学号',
name varchar(20) not null,
qq varchar(20)
);
1.1 单行数据+全列插入
使用insert into [table] values(all col);
- values里面的值必须要和表的列一一对应（数量和顺序一致）。
mysql> insert into students values(100, 10000, '张三', NULL);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(101, 10001, '李四', '16542');
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+-----+-------+--------+-------+
| id | sn | name | qq |
+-----+-------+--------+-------+
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
+-----+-------+--------+-------+
2 rows in set (0.00 sec)
1.2 多行数据+指定列插入
使用insert into students(id, sn, name) values (102, 20001, ‘小明’), (104, 20002, ‘小红’);
- 插入两条数据，values数量必须和指定列数量一致。
mysql> insert into students(id, sn, name) values (102, 20001, '小明'), (104, 20002, '小红');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from students;
+-----+-------+--------+-----------+
| id | sn | name | qq |
+-----+-------+--------+-----------+
| 1 | 10003 | 王五 | 123123123 |
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
| 102 | 20001 | 小明 | NULL |
| 104 | 20002 | 小红 | NULL |
+-----+-------+--------+-----------+
5 rows in set (0.00 sec)
1.3 插入否则更新
如果主键（唯一键）冲突，使用duplicate可以进行更新。
mysql> select * from students;
+-----+-------+--------+-----------+
| id | sn | name | qq |
+-----+-------+--------+-----------+
| 1 | 10003 | 王五 | 123123123 |
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
| 102 | 20001 | 小明 | NULL |
| 104 | 20002 | 小红 | NULL |
+-----+-------+--------+-----------+
5 rows in set (0.00 sec)
#存在插入失败
mysql> insert into students(id, sn, name) values(1, 10005, 'marry');
ERROR 1062 (23000): Duplicate entry '1' for key 'students.PRIMARY'
mysql> insert into students(id, sn, name) values(1, 10005, 'marry')
> on duplicate key update sn = 10005,name = 'marry';
Query OK, 2 rows affected (0.01 sec)
-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新
mysql> select * from students;
+-----+-------+--------+-----------+
| id | sn | name | qq |
+-----+-------+--------+-----------+
| 1 | 10005 | marry | 123123123 |
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
| 102 | 20001 | 小明 | NULL |
| 104 | 20002 | 小红 | NULL |
+-----+-------+--------+-----------+
5 rows in set (0.00 sec)
可以发现，如果直接插入，则会报主键(唯一键也可以)1重复，那么需要加上on duplicate key update 这条命令，表示若id键存在，则更新duplicate后面的值。
1.4 替换
使用replace可以替换。
- 主键或者唯一键没有冲突，则直接插入；
- 主键或者唯一键如果冲突，则删除后再插入
mysql> select * from students;
+-----+-------+--------+-----------+
| id | sn | name | qq |
+-----+-------+--------+-----------+
| 1 | 10006 | marry | 123123123 |
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
| 102 | 20001 | 小明 | NULL |
| 104 | 20002 | 小红 | NULL |
+-----+-------+--------+-----------+
5 rows in set (0.00 sec)
mysql> replace into students(sn, name) values(20001, '小黑');
Query OK, 2 rows affected (0.01 sec)
mysql> select * from students;
+-----+-------+--------+-----------+
| id | sn | name | qq |
+-----+-------+--------+-----------+
| 1 | 10006 | marry | 123123123 |
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
| 104 | 20002 | 小红 | NULL |
| 105 | 20001 | 小黑 | NULL |
+-----+-------+--------+-----------+
5 rows in set (0.00 sec)
mysql> replace into students(sn, name) values(20008, '小白');
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+-----+-------+--------+-----------+
| id | sn | name | qq |
+-----+-------+--------+-----------+
| 1 | 10006 | marry | 123123123 |
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
| 104 | 20002 | 小红 | NULL |
| 105 | 20001 | 小黑 | NULL |
| 106 | 20008 | 小白 | NULL |
+-----+-------+--------+-----------+
6 rows in set (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入
2. Retrieve(检索)
先创建一个表，插入几个数据。
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.03 sec)
mysql> desc exam_result;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| chinese | float | YES | | 0 | |
| math | float | YES | | 0 | |
| english | float | YES | | 0 | |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 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.01 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.02 sec)
2.1 select全列查询
select * from [table name]
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.02 sec)
- 通常情况下不建议使用 * 进行全列查询
-
- 查询的列越多，意味着需要传输的数据量越大；
-
- 可能会影响到索引的使用。
2.2 select指定列查询
只检索除了id，name，chinese列。
mysql> select id,name, chinese from exam_result;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 1 | 唐三藏 | 67 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 4 | 曹孟德 | 82 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 70 |
| 7 | 宋公明 | 75 |
+----+-----------+---------+
7 rows in set (0.00 sec)
- 指定列的顺序不需要按定义表的顺序来。
2.3 select查询字段为表达式
- 表达式不包含字段
mysql> select id,name,10 from exam_result;
+----+-----------+----+
| id | name | 10 |
+----+-----------+----+
| 1 | 唐三藏 | 10 |
| 2 | 孙悟空 | 10 |
| 3 | 猪悟能 | 10 |
| 4 | 曹孟德 | 10 |
| 5 | 刘玄德 | 10 |
| 6 | 孙权 | 10 |
| 7 | 宋公明 | 10 |
+----+-----------+----+
7 rows in set (0.00 sec)
- 表达式包含一个字段
mysql> select id,name,chinese+10 from exam_result;
+----+-----------+------------+
| id | name | chinese+10 |
+----+-----------+------------+
| 1 | 唐三藏 | 77 |
| 2 | 孙悟空 | 97 |
| 3 | 猪悟能 | 98 |
| 4 | 曹孟德 | 92 |
| 5 | 刘玄德 | 65 |
| 6 | 孙权 | 80 |
| 7 | 宋公明 | 85 |
+----+-----------+------------+
7 rows in set (0.07 sec)
- 表达式包含多个字段
mysql> select id,name,chinese+10+math from exam_result;
+----+-----------+-----------------+
| id | name | chinese+10+math |
+----+-----------+-----------------+
| 1 | 唐三藏 | 175 |
| 2 | 孙悟空 | 175 |
| 3 | 猪悟能 | 196 |
| 4 | 曹孟德 | 176 |
| 5 | 刘玄德 | 150 |
| 6 | 孙权 | 153 |
| 7 | 宋公明 | 150 |
+----+-----------+-----------------+
7 rows in set (0.00 sec)
2.4 为查询结果指定别名
- as 可省略可不省略
mysql> select id, name, chinese+math+english as 总分 from exam_result;
+----+-----------+--------+
| id | name | 总分 |
+----+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------+
7 rows in set (0.00 sec)
mysql> select id, name as 名字, chinese+math+english as 总分 from exam_result;
+----+-----------+--------+
| id | 名字 | 总分 |
+----+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------+
7 rows in set (0.00 sec)
mysql> select id, name as 名字, chinese+math+english as 总分 from exam_result;
2.5 结果去重（distinct）
- 98重了，使用distinct去重
mysql> select math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
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.02 sec)
2.6 WHERE 条件查询
- 比较运算符
- 逻辑运算符
ADN OR NOT
2.6.1 英语不及格的同学(<60)
mysql> select name,english from exam_result where english<60;
+-----------+---------+
| name | english |
+-----------+---------+
| 唐三藏 | 56 |
| 刘玄德 | 45 |
| 宋公明 | 30 |
+-----------+---------+
3 rows in set (0.01 sec)
2.6.2 语文成绩在 [80, 90] 分的同学及语文成绩
- and 连接
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)
- 使用between。。。and。。。
mysql> select name,chinese from exam_result where chinese between 80 and 90;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
3 rows in set (0.00 sec)
2.6.3 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
- 使用or
mysql> select name,math from exam_result where
-> math=58 or
-> math=59 or
-> math=98 or
-> math=99;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
2 rows in set (0.00 sec)
- 使用in
mysql> select name,math from exam_result where math in (58, 59, 98, 99);
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
2 rows in set (0.00 sec)
2.6.4 姓孙的同学 及 孙某同学
- % 匹配任意多个（包括 0 个）任意字符
mysql> select name from exam_result where name like '孙%';
+-----------+
| name |
+-----------+
| 孙悟空 |
| 孙权 |
+-----------+
2 rows in set (0.00 sec)
- _ 匹配严格的一个任意字符
mysql> select name from exam_result where name like '孙_';
+--------+
| name |
+--------+
| 孙权 |
+--------+
1 row in set (0.00 sec)
2.6.5 语文成绩好于英语成绩的同学
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)
2.6.6 总分在 200 分以下的同学
mysql> select name,chinese+english+math as 总分 from exam_result where chinese+english+math<200;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+--------+
2 rows in set (0.00 sec)
2.6.7 语文成绩 > 80 并且不姓孙的同学
name not lise ‘孙%’
mysql> select name,chinese as 总分 from exam_result where chinese>80 and name not like '孙%';
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+--------+
2 rows in set (0.00 sec)
2.6.8 孙某同学，否则要求总成绩>200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select name,chinese,math,english,chinese+math+english as 总分 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)
2.6.9 NULL查询
- student表
mysql> select * from students;
+-----+-------+--------+-----------+
| id | sn | name | qq |
+-----+-------+--------+-----------+
| 1 | 10006 | marry | 123123123 |
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
| 104 | 20002 | 小红 | NULL |
| 105 | 20001 | 小黑 | NULL |
| 106 | 20008 | 小白 | NULL |
+-----+-------+--------+-----------+
6 rows in set (0.00 sec)
- 查询QQ号已知的同学
mysql> select name,qq from students
-> where qq is not NULL;
+--------+-----------+
| name | qq |
+--------+-----------+
| marry | 123123123 |
| 李四 | 16542 |
+--------+-----------+
2 rows in set (0.00 sec)
- NULL和NULL比较 =和<=>区别(NULL比较要用<=>)
mysql> select NULL=NULL,NULL=1,NULL=0;
+-----------+--------+--------+
| NULL=NULL | NULL=1 | NULL=0 |
+-----------+--------+--------+
| NULL | NULL | NULL |
+-----------+--------+--------+
1 row in set (0.00 sec)
mysql> select NULL<=>NULL,NULL<=>1,NULL<=>0;
+-------------+----------+----------+
| NULL<=>NULL | NULL<=>1 | NULL<=>0 |
+-------------+----------+----------+
| 1 | 0 | 0 |
+-------------+----------+----------+
1 row in set (0.00 sec)
2.7 结果排序
- 没有 order by 的sql句子，返回的顺序是未定义的，永远不要相信这个顺序，
语法：
2.7.1 同学及数学成绩，按数学成绩升序/j降序显示
mysql> select name,math from exam_result
-> order by math;
+-----------+------+
| name | math |
+-----------+------+
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
7 rows in set (0.00 sec)
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)
2.7.2 同学及qq号，按qq号排序显示
- NULL比任何数都小。
mysql> select name,qq from students order by qq;
+--------+-----------+
| name | qq |
+--------+-----------+
| 张三 | NULL |
| 小红 | NULL |
| 小黑 | NULL |
| 小白 | NULL |
| marry | 123123123 |
| 李四 | 16542 |
+--------+-----------+
6 rows in set (0.00 sec)
mysql> select name,qq from students order by qq desc;
+--------+-----------+
| name | qq |
+--------+-----------+
| 李四 | 16542 |
| marry | 123123123 |
| 张三 | NULL |
| 小红 | NULL |
| 小黑 | NULL |
| 小白 | NULL |
+--------+-----------+
6 rows in set (0.00 sec)
2.7.3 查询同学各门成绩，以此按数学降序，英语升序，语文升序的方式显示
- 多字段排序，排序的优先级随书写顺序
mysql> select name,math,english,chinese from exam_result
-> order by math desc,english,chinese;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏 | 98 | 56 | 67 |
| 猪悟能 | 98 | 90 | 88 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
2.7.4 查询同学及总分，由高到低
mysql> select name,math,english,chinese,chinese+math+english as 总分
-> from exam_result
-> order by chinese+math+english desc;
+-----------+------+---------+---------+--------+
| name | math | english | chinese | 总分 |
+-----------+------+---------+---------+--------+
| 猪悟能 | 98 | 90 | 88 | 276 |
| 孙悟空 | 78 | 77 | 87 | 242 |
| 曹孟德 | 84 | 67 | 82 | 233 |
| 唐三藏 | 98 | 56 | 67 | 221 |
| 孙权 | 73 | 78 | 70 | 221 |
| 刘玄德 | 85 | 45 | 55 | 185 |
| 宋公明 | 65 | 30 | 75 | 170 |
+-----------+------+---------+---------+--------+
7 rows in set (0.00 sec)
- order by中可以使用别名
mysql> select name,math,english,chinese,chinese+math+english as 总分 from exam_result order by 总分 desc;
+-----------+------+---------+---------+--------+
| name | math | english | chinese | 总分 |
+-----------+------+---------+---------+--------+
| 猪悟能 | 98 | 90 | 88 | 276 |
| 孙悟空 | 78 | 77 | 87 | 242 |
| 曹孟德 | 84 | 67 | 82 | 233 |
| 唐三藏 | 98 | 56 | 67 | 221 |
| 孙权 | 73 | 78 | 70 | 221 |
| 刘玄德 | 85 | 45 | 55 | 185 |
| 宋公明 | 65 | 30 | 75 | 170 |
+-----------+------+---------+---------+--------+
7 rows in set (0.00 sec)
2.7.5 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩从高到低
mysql> select name,math from exam_result
where name like '孙%' or name like '曹%' order by math desc;
+-----------+------+
| name | math |
+-----------+------+
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
+-----------+------+
3 rows in set (0.00 sec)
2.7.6 筛选分页结果
对未知表进行查询时，最好加一条LIMIT1 ，避免因为表中数据过大，查询全表数据导致数据库卡死。
按id进行分页，每页三条记录，分别显示第1、2、3页：
mysql> select name,chinese,math,english from exam_result
-> limit 3 offset 0;
+-----------+---------+------+---------+
| name | chinese | math | english |
+-----------+---------+------+---------+
| 唐三藏 | 67 | 98 | 56 |
| 孙悟空 | 87 | 78 | 77 |
| 猪悟能 | 88 | 98 | 90 |
+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select name,chinese,math,english from exam_result limit 3 offset 3;
+-----------+---------+------+---------+
| name | chinese | math | english |
+-----------+---------+------+---------+
| 曹孟德 | 82 | 84 | 67 |
| 刘玄德 | 55 | 85 | 45 |
| 孙权 | 70 | 73 | 78 |
+-----------+---------+------+---------+
3 rows in set (0.00 sec)
第三页不足三个，有几个输出几个
mysql> select name,chinese,math,english from exam_result limit 3 offset 6;
+-----------+---------+------+---------+
| name | chinese | math | english |
+-----------+---------+------+---------+
| 宋公明 | 75 | 65 | 30 |
+-----------+---------+------+---------+
1 row in set (0.00 sec)
3. Update
语法：
3.1 将查询到的结果进行列值更新
3.1.1 将孙悟空同学的数学成绩变更为80分
mysql> select name,math from exam_result
-> where name='孙悟空';
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 78 |
+-----------+------+
1 row in set (0.00 sec)
mysql> update exam_result set math=80
-> where name='孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select name,math from exam_result where name='孙悟空';
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 80 |
+-----------+------+
1 row in set (0.00 sec)
3.1.2 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
mysql> select name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name | math | chinese |
+-----------+------+---------+
| 曹孟德 | 84 | 82 |
+-----------+------+---------+
1 row in set (0.00 sec)
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 name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name | math | chinese |
+-----------+------+---------+
| 曹孟德 | 60 | 70 |
+-----------+------+---------+
1 row in set (0.00 sec)
3.1.3 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
mysql> select name,math,chinese+math+english 总分 from exam_result order by 总分;
+-----------+------+--------+
| name | math | 总分 |
+-----------+------+--------+
| 宋公明 | 65 | 170 |
| 刘玄德 | 85 | 185 |
| 曹孟德 | 60 | 197 |
| 唐三藏 | 98 | 221 |
| 孙权 | 73 | 221 |
| 孙悟空 | 80 | 244 |
| 猪悟能 | 98 | 276 |
+-----------+------+--------+
7 rows in set (0.00 sec)
mysql> select name,math,chinese+math+english 总分 from exam_result order by 总分 limit 3 offset 0;
+-----------+------+--------+
| name | math | 总分 |
+-----------+------+--------+
| 宋公明 | 65 | 170 |
| 刘玄德 | 85 | 185 |
| 曹孟德 | 60 | 197 |
+-----------+------+--------+
3 rows in set (0.00 sec)
- 更新，但是不能使用查总分前3获取了，因为前三可能不是这3个人了，因此要用in的命令。
mysql> update exam_result set math=math+30
-> order by chinese+english+math
-> limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select name,math,chinese+math+english 总分 from exam_result order by 总分 limit 3 offset 0;
+-----------+------+--------+
| name | math | 总分 |
+-----------+------+--------+
| 宋公明 | 95 | 200 |
| 刘玄德 | 115 | 215 |
| 唐三藏 | 98 | 221 |
+-----------+------+--------+
3 rows in set (0.00 sec)
mysql> select name,math,chinese+math+english 总分 from exam_result where name in('宋公明','刘玄德','曹孟德');
+-----------+------+--------+
| name | math | 总分 |
+-----------+------+--------+
| 曹孟德 | 90 | 227 |
| 刘玄德 | 115 | 215 |
| 宋公明 | 95 | 200 |
+-----------+------+--------+
3 rows in set (0.00 sec)
3.1.4 将所有同学的语文成绩更新为原来的 2 倍
- 更新全表的语句慎用
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 90 | 67 |
| 5 | 刘玄德 | 55 | 115 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 95 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.01 sec)
mysql> update exam_result set chinese=chinese*2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 2 | 孙悟空 | 174 | 80 | 77 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
4. Delete
语法：
4.1 删除数据
4.1.1 删除孙悟空同学的考试成绩
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 2 | 孙悟空 | 174 | 80 | 77 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.09 sec)
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
6 rows in set (0.00 sec)
4.1.2 删除整张表的数据
- 删除child
- 自增id再删除了之后，仍然继续增长（从4开始，不是从1开始）;
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| child |
| exam_result |
| myclass |
| people |
| students |
| ttt |
| ttt1 |
+-------------------+
7 rows in set (0.00 sec)
mysql> select * from child;
+------+------------+--------------+
| id | birth | phone |
+------+------------+--------------+
| 1 | 1999-04-02 | 15685954545 |
| 2 | 2022-03-05 | 352454514521 |
+------+------------+--------------+
2 rows in set (0.00 sec)
mysql> delete from child;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from child;
Empty set (0.00 sec)
4.2 截断表
语法：
慎用：
- 只能对整张表操作，不能像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.03 sec)
mysql> INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
---截断整个数据表，注意影响行数是0，所以实际上没有对数据真正操作
mysql> SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)
--再插入一条数据，数据重新增长
mysql> INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
+----+------+
1 row in set (0.00 sec)
5. 插入查询结果
语法：
- 删除表中的重复记录，重复的数据只能有一份
先创建一个表：
mysql> create table test_table (id int, name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test_table values
-> (100, 'aaa'),
-> (100, 'aaa'),
-> (200,'bbb'),
-> (200, 'bbb'),
-> (200, 'bbb'),
-> (300, 'ccc');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
- 创建一张空表tese1_table， 结构和tese_table一样，通过重命名的方式去重。
mysql> create table test1_table like test_table;
Query OK, 0 rows affected (0.04 sec)
--将去重的数据插入新表
mysql> insert into test1_table select distinct * from test_table;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
--将新表重命名为老表
mysql> rename table test_table to old_test_table,
-> test1_table to test_table;
Query OK, 0 rows affected (0.04 sec)
-- 再查老表，就去重了
mysql> select * from test_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.01 sec)
6.聚合函数
6.1 统计班级共有多少个同学
- 表格数据
mysql> select * from students;
+-----+-------+--------+-----------+
| id | sn | name | qq |
+-----+-------+--------+-----------+
| 1 | 10006 | marry | 123123123 |
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | 16542 |
| 104 | 20002 | 小红 | NULL |
| 105 | 20001 | 小黑 | NULL |
| 106 | 20008 | 小白 | NULL |
+-----+-------+--------+-----------+
6 rows in set (0.00 sec)
- 使用 * 做统计，不受NULL影响。
mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.03 sec)
- 使用表达式做统计
mysql> select count(1) from students;
+----------+
| count(1) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
6.2 统计班收集的qq号有多少
- NULL不会计入结果
mysql> select qq from students;
+-----------+
| qq |
+-----------+
| 123123123 |
| NULL |
| 16542 |
| NULL |
| NULL |
| NULL |
+-----------+
6 rows in set (0.00 sec)
mysql> select count(qq) from students;
+-----------+
| count(qq) |
+-----------+
| 2 |
+-----------+
1 row in set (0.04 sec)
6.3 统计本次考试的数学成绩分数个数
- distinct 去重符号应该紧贴列
mysql> select math from exam_result;
+------+
| math |
+------+
| 98 |
| 98 |
| 90 |
| 115 |
| 73 |
| 95 |
+------+
6 rows in set (0.00 sec)
mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
mysql> select count( distinct math) from exam_result;
+-----------------------+
| count( distinct math) |
+-----------------------+
| 5 |
+-----------------------+
1 row in set (0.00 sec)
6.4 统计数学成绩总分
mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
| 569 |
+-----------+
1 row in set (0.00 sec)
- 统计数学不及格（<60）的总分，没有结果，返回NULL
mysql> select sum(math) from exam_result where math<60;
+-----------+
| sum(math) |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
6.5 统计总分的平均分
mysql> select avg(chinese+english+math) 平均总分 from exam_result;
+--------------+
| 平均总分 |
+--------------+
| 297.5 |
+--------------+
1 row in set (0.00 sec)
6.6 返回英语最高分
mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
| 90 |
+--------------+
1 row in set (0.00 sec)
6.7 返回 > 70 分以上的数学最低分
mysql> select min(math) from exam_result where math>70;
+-----------+
| min(math) |
+-----------+
| 73 |
+-----------+
1 row in set (0.00 sec)
7. group by子句的使用
- 表准备：
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| salgrade |
+-----------------+
3 rows in set (0.00 sec)
mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO | | NULL | |
| dname | varchar(14) | YES | | NULL | |
| loc | varchar(13) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno | int(6) unsigned zerofill | NO | | NULL | |
| ename | varchar(10) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | int(4) unsigned zerofill | YES | | NULL | |
| hiredate | datetime | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| comm | decimal(7,2) | YES | | NULL | |
| deptno | int(2) unsigned zerofill | YES | | NULL | |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
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.11 sec)
mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO | | NULL | |
| dname | varchar(14) | YES | | NULL | |
| loc | varchar(13) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| grade | int | YES | | NULL | |
| losal | int | YES | | NULL | |
| hisal | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
7.1 操作
-显示每个部门的平均工资和最高工资
mysql> select deptno, avg(sal),max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal) | max(sal) |
+--------+-------------+----------+
| 20 | 2175.000000 | 3000.00 |
| 30 | 1566.666667 | 2850.00 |
| 10 | 2916.666667 | 5000.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)
- 显示每个部门的每个岗位
mysql> select avg(sal),max(sal),job,deptno from emp group by deptno,job;
+-------------+----------+-----------+--------+
| avg(sal) | max(sal) | job | deptno |
+-------------+----------+-----------+--------+
| 950.000000 | 1100.00 | CLERK | 20 |
| 1400.000000 | 1600.00 | SALESMAN | 30 |
| 2975.000000 | 2975.00 | MANAGER | 20 |
| 2850.000000 | 2850.00 | MANAGER | 30 |
| 2450.000000 | 2450.00 | MANAGER | 10 |
| 3000.000000 | 3000.00 | ANALYST | 20 |
| 5000.000000 | 5000.00 | PRESIDENT | 10 |
| 950.000000 | 950.00 | CLERK | 30 |
| 1300.000000 | 1300.00 | CLERK | 10 |
+-------------+----------+-----------+--------+
9 rows in set (0.00 sec)
-显示平均工资低于2000的部门和它的平均工资
-统计各个部门的平均工资
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
- having和group by配合使用，对group by结果进行过滤
mysql> select deptno,avg(sal) as my_avg from emp group by deptno having my_avg<2000;
+--------+-------------+
| deptno | my_avg |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)
--- --having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。
8 oracle9i经典测试表
DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_c i;
USE `scott`;
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) DEFAULT NULL COMMENT '等级',
`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
