【MySQL】表的基本操作
- 表的增删改查
- Insert
- 单行数据 + 指定列插入 + 全列插入
- 多行数据 + 指定列插入 + 全列插入
- 插入否则更新(duplicate key)
- 替换(replace)
- select
- 全队列查询
- 指定列查询
- 查询字段可以是表达式
- 为查询结果
- 结果去重
- where条件
- 排序(order by)
- 筛选分页结果
- Updata
- Delete
- 截断表
- 插入查询结果
- 聚合函数
- group by分组字句的使用
- having
表的增删改查
CRUD : Create(创建), Retrieve(读取),Update(更新),Delete(删除)
Insert
语法:
insert [into] table_name [(column [, column] ...)] values (value_list) [, (value_list)] ...value_list: value, [, value] ...
创建一个学生表:
mysql> create table student(-> id int unsigned primary key auto_increment,-> name varchar(20) not null,-> mail varchar(20) unqiue-> );mysql> insert into student (id,name,mail) values(1,'小乔','123');
Query OK, 1 row affected (0.01 sec)
单行数据 + 指定列插入 + 全列插入
在上面的insert中的中括号里的是可以进省略的。
- 指定列插入
mysql> insert into student (id,name,mail) values(1,'小乔','123');
Query OK, 1 row affected (0.01 sec)
- 全列插入
当省略指定插入的列的时候,默认就是全部字段都要进行插入,这个就叫做全列插入
mysql> insert into student values(4,'周瑜','654');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+------+
| id | name | mail |
+----+--------+------+
| 1 | 小乔 | 123 |
| 2 | 大桥 | 321 |
| 4 | 周瑜 | 654 |
+----+--------+------+
3 rows in set (0.00 sec)
多行数据 + 指定列插入 + 全列插入
当进行多行插入时,只需要使用逗号隔开即可。
- 指定列插入
mysql> insert into student (name,mail) values('赵云','1234'),('张飞','45623'),('马超','59632');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
- 全列插入
mysql> insert into student values(10,'关羽','12394'),(11,'刘备','458623'),(12,'刘邦','519632');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+--------+
| id | name | mail |
+----+--------+--------+
| 1 | 小乔 | 123 |
| 2 | 大桥 | 321 |
| 4 | 周瑜 | 654 |
| 5 | 赵云 | 1234 |
| 6 | 张飞 | 45623 |
| 7 | 马超 | 59632 |
| 10 | 关羽 | 12394 |
| 11 | 刘备 | 458623 |
| 12 | 刘邦 | 519632 |
+----+--------+--------+
9 rows in set (0.00 sec)
插入否则更新(duplicate key)
有时候我们插入数据时候,可能受到了主键或者唯一键的约束,但是我们就是想插入这个新的值。所以就可以使用duplicate,如果插入的数据不冲突直接插入,如果插入的数据冲突冲突了就进行更新。
语法:
insert... on duplicate key dpdate column = value [, column = value] ...
示例:
mysql> insert into student values (2,'大乔','321') on duplicate key update name='大乔',mail=96532;
Query OK, 2 rows affected (0.00 sec)mysql> select * from student;
+----+--------+--------+
| id | name | mail |
+----+--------+--------+
| 1 | 小乔 | 123 |
| 2 | 大乔 | 96532 |
| 4 | 周瑜 | 654 |
| 5 | 赵云 | 1234 |
| 6 | 张飞 | 45623 |
| 7 | 马超 | 59632 |
| 10 | 关羽 | 12394 |
| 11 | 刘备 | 458623 |
| 12 | 刘邦 | 519632 |
+----+--------+--------+
9 rows in set (0.00 sec)
mysql> insert into student values (2,'大乔','321') on duplicate key update name='大乔',mail=96532;
Query OK, 0 rows affected (0.00 sec)mysql> insert into student values (13,'吕布','22322') on duplicate key update name='大乔',mail=96532;
Query OK, 1 row affected (0.01 sec)mysql> insert into student values (2,'大乔','321') on duplicate key update name='大乔',mail=96532;
Query OK, 2 rows affected (0.00 sec)-- 0 row affected: 表中有冲突数据,但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据,数据被插入
-- 2 row affected: 表中有冲突数据,并且数据已经被更新
替换(replace)
上面的duplicate的只要有冲突了,就直接在当前的所属插入的地方直接进行更新,但是替换就不一样了。替换是只要遇到冲突了,就是直接把冲突的数据删除,然后在插入新的数据,如果没有冲突了效果和inset是一样的。
语法:
replace的语法和insert的语法是一样的,只是把insert换成了replacereplace [into] table_name [(column [, column] ...)] values (value_list) [, (value_list)] ...
示例:
mysql> select * from student;
+----+--------+--------+
| id | name | mail |
+----+--------+--------+
| 1 | 小乔 | 123 |
| 2 | 大乔 | 96532 |
| 4 | 周瑜 | 654 |
| 5 | 赵云 | 1234 |
| 6 | 张飞 | 45623 |
| 7 | 马超 | 59632 |
| 10 | 关羽 | 12394 |
| 11 | 刘备 | 458623 |
| 12 | 刘邦 | 519632 |
| 13 | 吕布 | 22322 |
+----+--------+--------+
10 rows in set (0.00 sec)mysql> replace into student values (14,'吕布',22322);
Query OK, 2 rows affected (0.01 sec)mysql> select * from student;
+----+--------+--------+
| id | name | mail |
+----+--------+--------+
| 1 | 小乔 | 123 |
| 2 | 大乔 | 96532 |
| 4 | 周瑜 | 654 |
| 5 | 赵云 | 1234 |
| 6 | 张飞 | 45623 |
| 7 | 马超 | 59632 |
| 10 | 关羽 | 12394 |
| 11 | 刘备 | 458623 |
| 12 | 刘邦 | 519632 |
| 14 | 吕布 | 22322 |
+----+--------+--------+
10 rows in set (0.00 sec)
mysql> replace into student values (15,'貂蝉',22255);
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+----+--------+--------+
| id | name | mail |
+----+--------+--------+
| 1 | 小乔 | 123 |
| 2 | 大乔 | 96532 |
| 4 | 周瑜 | 654 |
| 5 | 赵云 | 1234 |
| 6 | 张飞 | 45623 |
| 7 | 马超 | 59632 |
| 10 | 关羽 | 12394 |
| 11 | 刘备 | 458623 |
| 12 | 刘邦 | 519632 |
| 14 | 吕布 | 22322 |
| 15 | 貂蝉 | 22255 |
+----+--------+--------+
11 rows in set (0.00 sec)
-- 1 row affected: 表中没有冲突数据,数据被插入
-- 2 row affected: 表中有冲突数据,删除后重新插入
select
语法:
SELECT [DISTINCT] {* | {column [, column] ...} [FROM table_name] [WHERE ...] [ORDER BY column [ASC | DESC], ...] LIMIT ...
案例:
----创建表结构
mysql> insert into exam (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);----插入数据
mysql> 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)
全队列查询
语法:
select * from 表名
select 列举全部 from 表名
通常情况下不建议使用 * 进行全队列查询
因为查询的列越多,就意味着要传输的数据量越大,可能会影响到索引的使用。
mysql> 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)
指定列查询
指定列查询就是指定需要查询的字段即可
mysql> select name,math from exam;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 孙悟空 | 78 |
| 猪悟能 | 98 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
查询字段可以是表达式
mysql> select 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
mysql> select name,math,math+chinese from exam;
+-----------+------+--------------+
| name | math | math+chinese |
+-----------+------+--------------+
| 唐三藏 | 98 | 165 |
| 孙悟空 | 78 | 165 |
| 猪悟能 | 98 | 186 |
| 曹孟德 | 84 | 166 |
| 刘玄德 | 85 | 140 |
| 孙权 | 73 | 143 |
| 宋公明 | 65 | 140 |
+-----------+------+--------------+
7 rows in set (0.01 sec)
为查询结果
语法:
select column [as] alias_name [...] from table_name;
# as 可以省略
mysql> select name,math,math+chinese+english as total from exam;
+-----------+------+-------+
| name | math | total |
+-----------+------+-------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+------+-------+
7 rows in set (0.00 sec)
结果去重
语法:
select distinct ……
mysql> select math from exam;
+------+
| math |
+------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
7 rows in set (0.00 sec)mysql> select distinct math from exam;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
6 rows in set (0.00 sec)
where条件
比较运算符
运算符 | 说明 |
---|---|
.>, >=, <, <= | 大于,大于等于,小于,小于等于 |
= | 等于,NULL 不安全,例如 NULL = NULL 的结果是 NULL |
<=> | 等于,NULL 安全,例如 NULL <=> NULL 的结果是 TRUE(1) |
!= | , <> 不等于 |
BETWEEN a0 AND a1 | 范围匹配,[a0, a1],如果 a0 <= value <= a1,返回 TRUE(1) |
IN (option, …) | 如果是 option 中的任意一个,返回 TRUE(1) |
IS NULL | 是 NULL |
IS NOT NULL | 不是 NULL |
LIKE | 模糊匹配。% 表示任意多个(包括 0 个)任意字符;_ 表示任意一个字符 |
逻辑运算符
运算符 | 说明 |
---|---|
AND | 多个条件必须都为 TRUE(1),结果才是 TRUE(1) |
OR | 任意一个条件为 TRUE(1), 结果为 TRUE(1) |
NOT | 条件为 TRUE(1),结果为 FALSE(0) |
- 查找语文不及格了人
mysql> select name,english from exam where english<60;
+-----------+---------+
| name | english |
+-----------+---------+
| 唐三藏 | 56 |
| 刘玄德 | 45 |
| 宋公明 | 30 |
+-----------+---------+
3 rows in set (0.00 sec)
- 查看语文成绩在【60,90】的人
使用andmysql> select name,chinese from exam where chinese>=60 and chinese<=90;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
| 孙权 | 70 |
| 宋公明 | 75 |
+-----------+---------+
6 rows in set (0.00 sec)
使用betweenmysql> select name,chinese from exam where chinese between 60 and 90;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
| 孙权 | 70 |
| 宋公明 | 75 |
+-----------+---------+
6 rows in set (0.01 sec)
- 查看数学成绩是 65 或者 73或者 98 或者 99 分的同学及数学成绩
使用or进行条件连接mysql> select name,math from exam where math=65 or math=73 or math=98 or math=99;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
4 rows in set (0.01 sec)
使用 in 条件mysql> select name,math from exam where math in(65,73,98,99);
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
4 rows in set (0.00 sec)
- 模糊查询
查看所有性孙的
-- % 匹配任意多个(包括 0 个)任意字符 mysql> select name from exam where name like '孙%';
+-----------+
| name |
+-----------+
| 孙悟空 |
| 孙权 |
+-----------+
2 rows in set (0.00 sec)
查看所有性孙某的,两个姓名的
-- _ 匹配严格的一个任意字符mysql> select name from exam where name like '孙_';
+--------+
| name |
+--------+
| 孙权 |
+--------+
1 row in set (0.00 sec)
- 查看语文成绩比英语成绩好的
mysql> select name,chinese,english from exam where chinese>english;
+-----------+---------+---------+
| name | chinese | english |
+-----------+---------+---------+
| 唐三藏 | 67 | 56 |
| 孙悟空 | 87 | 77 |
| 曹孟德 | 82 | 67 |
| 刘玄德 | 55 | 45 |
| 宋公明 | 75 | 30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)
- 总分在200分以下的
-- where 条件中使用表达式
-- 别名不能用在 where 条件中mysql> select name,math+chinese+english as total from exam where math+chinese+english<200;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------+
2 rows in set (0.00 sec)# a.这里看似是可以的,但是这里的指向顺序是先执行from,在执行where,随后在执行select的,所有这里一定不能写成total<200;
mysql> select name,math+chinese+english as total from exam where total<200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
- 文成绩 > 80 并且不姓孙的同学
mysql> select name,chinese from exam where chinese<80 and name not like '孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 刘玄德 | 55 |
| 宋公明 | 75 |
+-----------+---------+
3 rows in set (0.00 sec)
- 孙某同学,否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select name,chinese,math,english,chinese+math+english total from exam where name like '孙_' or(chinese+math+english>200 and chinese<math and english>8
80);
+-----------+---------+------+---------+-------+
| name | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 猪悟能 | 88 | 98 | 90 | 276 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+-------+
2 rows in set (0.00 sec)
排序(order by)
语法:
-- ASC 为升序(从小到大)
-- DESC 为降序(从大到小)
-- 默认为 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];
注意:没有 ORDER BY 子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序
- 根据order by进行对语文成绩进行升序和降序
-- 升序操作mysql> select name,chinese from exam order by chinese asc;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 刘玄德 | 55 |
| 唐三藏 | 67 |
| 孙权 | 70 |
| 宋公明 | 75 |
| 曹孟德 | 82 |
| 孙悟空 | 87 |
| 猪悟能 | 88 |
+-----------+---------+
7 rows in set (0.00 sec)
-- 降序操作mysql> select name,chinese from exam order by chinese desc;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 猪悟能 | 88 |
| 孙悟空 | 87 |
| 曹孟德 | 82 |
| 宋公明 | 75 |
| 孙权 | 70 |
| 唐三藏 | 67 |
| 刘玄德 | 55 |
+-----------+---------+
7 rows in set (0.00 sec)
- 查询同学各门成绩,依次按 数学降序,英语升序,语文升序的方式显示
mysql> select name,chinese,math,english from exam order by math desc,chinese asc,english asc;
+-----------+---------+------+---------+
| name | chinese | math | english |
+-----------+---------+------+---------+
| 唐三藏 | 67 | 98 | 56 |
| 猪悟能 | 88 | 98 | 90 |
| 刘玄德 | 55 | 85 | 45 |
| 曹孟德 | 82 | 84 | 67 |
| 孙悟空 | 87 | 78 | 77 |
| 孙权 | 70 | 73 | 78 |
| 宋公明 | 75 | 65 | 30 |
+-----------+---------+------+---------+
7 rows in set (0.00 sec)
- 查询同学及总分,由高到低
mysql> select name,chinese+math+english from exam order by chinese+math+english desc;
+-----------+----------------------+
| name | chinese+math+english |
+-----------+----------------------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+----------------------+
7 rows in set (0.00 sec)
这里可以起别名进行排序
原因是这里的执行顺序是先执行的select,后执行的order by
mysql> select name,chinese+math+english as total from exam order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
筛选分页结果
语法:
-- 起始下标为 0 -- 从 0 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n; -- 从 s 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n; -- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
mysql> select * from exam limit 3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
3 rows in set (0.01 sec)mysql> select * from exam limit 2,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)mysql> select * from exam limit 2 offset 3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
Updata
对查询到的列值做更新。
语法:
upadat 表名称 set column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]
- 将孙悟空同学的数学成绩变更为 80 分
---原来数据
mysql> select name,math from exam where name='孙悟空';
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 78 |
+-----------+------+---进行数据更新
mysql> update exam set math=80 where name='孙悟空';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0---更新后数据
mysql> select name,math from exam where name='孙悟空';
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 80 |
+-----------+------+
1 row in set (0.00 sec)
- 将曹孟德同学的数学成绩变更为 60 分,语文成绩变更为 70 分
---原来数据
mysql> select name,math,chinese from exam where name='曹孟德';
+-----------+------+---------+
| name | math | chinese |
+-----------+------+---------+
| 曹孟德 | 84 | 82 |
+-----------+------+---------+
1 row in set (0.00 sec)---对数据进行更新
mysql> update exam 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 where name='曹孟德';
+-----------+------+---------+
| name | math | chinese |
+-----------+------+---------+
| 曹孟德 | 60 | 70 |
+-----------+------+---------+
1 row in set (0.00 sec)
- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
mysql> select name,math,math+chinese+english total from exam order by total asc limit 3;
+-----------+------+-------+
| name | math | total |
+-----------+------+-------+
| 宋公明 | 65 | 170 |
| 刘玄德 | 85 | 185 |
| 曹孟德 | 60 | 197 |
+-----------+------+-------+
3 rows in set (0.00 sec)# a.注意mysql中不支持maht += 30这种语法,只能是math = math + 30;
mysql> update exam set math=math+30 order by chinese+math+english asc limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select name,math,math+chinese+english total from exam order by total asc limit 3;
+-----------+------+-------+
| name | math | total |
+-----------+------+-------+
| 宋公明 | 95 | 200 |
| 刘玄德 | 115 | 215 |
| 唐三藏 | 98 | 221 |
+-----------+------+-------+
3 rows in set (0.00 sec)
- 将所有同学的语文成绩更新为原来的 2 倍
mysql> select name,chinese from exam;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 70 |
| 刘玄德 | 55 |
| 孙权 | 70 |
| 宋公明 | 75 |
+-----------+---------+
7 rows in set (0.00 sec)mysql> update exam set chinese=chinese*2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0mysql> select name,chinese from exam;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 134 |
| 孙悟空 | 174 |
| 猪悟能 | 176 |
| 曹孟德 | 140 |
| 刘玄德 | 110 |
| 孙权 | 140 |
| 宋公明 | 150 |
+-----------+---------+
7 rows in set (0.00 sec)
Delete
语法:
delete from 表名称 [WHERE ...] [ORDER BY ...] [LIMIT ...]
- 删除孙悟空同学的考试成绩
mysql> select * from exam where name='孙悟空';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 174 | 80 | 77 |
+----+-----------+---------+------+---------+
1 row in set (0.01 sec)mysql> delete from exam where name='孙悟空';
Query OK, 1 row affected (0.00 sec)mysql> select * from exam;
+----+-----------+---------+------+---------+
| 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)
- 删除考试成绩在最后一名的学生
mysql> select name,math,chinese,english,math+chinese+english total from exam order by total asc;
+-----------+------+---------+---------+-------+
| name | math | chinese | english | total |
+-----------+------+---------+---------+-------+
| 刘玄德 | 115 | 110 | 45 | 270 |
| 宋公明 | 95 | 150 | 30 | 275 |
| 唐三藏 | 98 | 134 | 56 | 288 |
| 孙权 | 73 | 140 | 78 | 291 |
| 曹孟德 | 90 | 140 | 67 | 297 |
| 猪悟能 | 98 | 176 | 90 | 364 |
+-----------+------+---------+---------+-------+
6 rows in set (0.01 sec)mysql> delete from exam order by chinese+math+english asc limit 1;
Query OK, 1 row affected (0.01 sec)mysql> select name,math,chinese,english,math+chinese+english total from exam order by total asc;
+-----------+------+---------+---------+-------+
| name | math | chinese | english | total |
+-----------+------+---------+---------+-------+
| 宋公明 | 95 | 150 | 30 | 275 |
| 唐三藏 | 98 | 134 | 56 | 288 |
| 孙权 | 73 | 140 | 78 | 291 |
| 曹孟德 | 90 | 140 | 67 | 297 |
| 猪悟能 | 98 | 176 | 90 | 364 |
+-----------+------+---------+---------+-------+
5 rows in set (0.00 sec)
- 删除整张表中的数据
语法:
delete from 表名称;
---测试表
mysql> create table for_delete(-> id int primary key auto_increment,-> name varchar(20)-> );
Query OK, 0 rows affected (0.04 sec)---向表中插入数据
mysql> insert into for_delete (name) values ('a'),('b'),('c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)---查看表结构,并注意auto_increment的值
mysql> show create table for_delete\G
*************************** 1. row ***************************Table: for_delete
Create Table: CREATE TABLE `for_delete` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)---删除整张表的数据
mysql> delete from for_delete;
Query OK, 3 rows affected (0.01 sec)mysql> select * from for_delete;
Empty set (0.00 sec)---再次插入一条数据
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 4 | d |
+----+------+
1 row in set (0.01 sec)
所以上面的测试我们可以发现,delete只是删除了表的内容,并不会对计数器进行清空。
截断表
这个也是用于来删除整张表的数据的。
注意:这个操作慎用
- 只能对整表操作,不能像 DELETE 一样针对部分数据操作;
- 实际上 MySQL 不对数据操作,所以比 DELETE 更快,但是TRUNCATE在删除数据的时候,并不经过真正的事物,所以无法回滚
- 会重置 AUTO_INCREMENT 项
语法:
truncate [TABLE] 表名称;
---创建新表,并插入数据
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.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)---查看表结构
mysql> show create table for_truncate\G
*************************** 1. row ***************************Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # 这里AUTO_INCREMENT=4
1 row in set (0.00 sec)---使用truncate删除数据,并再次查看表结构
mysql> truncate table for_truncate;
Query OK, 0 rows affected (0.03 sec)mysql> show create table for_truncate\G
*************************** 1. row ***************************Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci# 这里AUTO_INCREMENT=default了
1 row in set (0.00 sec)---再次添加数据
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | d |
+----+------+
1 row in set (0.00 sec)
插入查询结果
其实就是insert和select的一个结合操作
语法:
INSERT INTO table_name [(column [, column ...])] SELECT ...
示例:删除表中的的重复复记录,重复的数据只能有一份
# 创建新表并插入数据
mysql> create table duplicate_table(-> id int,-> name varchar(20)-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into duplicate_table values
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> 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)# 创建一个和duplicate_table一样的新表
mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.03 sec)mysql> desc duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> desc no_duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)# 使用插入查询结构
mysql> insert into no_duplicate_table select distinct * from duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> 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 new_duplicate_table, no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.04 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) | 返回查询到的数据的 最小值,不是数字没有意义 |
- 统计班级共有多少同学
-- 使用 * 做统计,不受 NULL 影响
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 se
- 统计本次考试数学成绩的个数
---可以进行重命名
mysql> select count(math) as count from exam;
+-------+
| count |
+-------+
| 5 |
+-------+
1 row in set (0.01 sec)---去重操作
mysql> select math from exam;
+------+
| math |
+------+
| 98 |
| 98 |
| 90 |
| 73 |
| 95 |
+------+
5 rows in set (0.00 sec)mysql> select count(distinct math) from exam;
+----------------------+
| count(distinct math) |
+----------------------+
| 4 |
+----------------------+
1 row in set (0.00 sec)
- 统计数学成绩的总分
mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
| 454 |
+-----------+
1 row in set (0.00 sec)
- 统计数学分数小于60的总分
--- 如果有返回结果,如果没有返回NULL
mysql> select sum(math) from exam where math<60;
+-----------+
| sum(math) |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
- 统计平均分
mysql> select avg(chinese+math+english) average from exam;
+---------+
| average |
+---------+
| 303 |
+---------+
1 row in set (0.00 sec)
- 返回语文最高分和最低分
mysql> select max(chinese),min(chinese) from exam;
+--------------+--------------+
| max(chinese) | min(chinese) |
+--------------+--------------+
| 176 | 134 |
+--------------+--------------+
1 row in set (0.00 sec)
- 返回 > 70数学的最高分和最低分
mysql> select max(math),min(math) from exam where math > 70;
+-----------+-----------+
| max(math) | min(math) |
+-----------+-----------+
| 98 | 73 |
+-----------+-----------+
1 row in set (0.00 sec)# 这里要注意了,因为这里select 查找name是整张表的,而max和min只有一个,不匹配,所以要使用分组查询
mysql> select name,max(math),min(math) from exam where math > 70;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test_db.exam.name'; this is incompatible with sql_mode=only_full_group_by
group by分组字句的使用
语法:
select column1, column2, .. from table group by column;
案例:
- 准备工作,创建一个雇员信息表(来自oracle 9i的经典测试表)
EMP员工表
DEPT部门表
SALGRADE工资等级表
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);
- 如何显示每个部门的平均工资和最高工资
mysql> select max(sal) 最高,avg(sal) 平均 from emp group by deptno;
+---------+-------------+
| 最高 | 平均 |
+---------+-------------+
| 3000.00 | 2175.000000 |
| 2850.00 | 1566.666667 |
| 5000.00 | 2916.666667 |
+---------+-------------+
3 rows in set (0.01 sec)
- 显示每个部门的每种岗位的平均工资和最低工资
mysql> select deptno,job, avg(sal) ,min(sal) from emp group by deptno,job order by deptno asc;
+--------+-----------+-------------+----------+
| deptno | job | avg(sal) | min(sal) |
+--------+-----------+-------------+----------+
| 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)
having
having是对聚合后的数据进行条件筛选。有点类似与where,但是group by不能是where搭配,group by是跟having一起搭配使用的。
- having与where的区别
MySQL中的HAVING和WHERE子句的主要区别在于它们的作用时机、使用位置、以及可以使用的表达式类型。
作用时机和位置:
- WHERE子句在数据聚合之前应用,用于过滤查询结果中的行。它在FROM子句之后、GROUP BY和HAVING子句之前执
- 行。WHERE子句中的条件表达式可以是列名,不能包含聚合函数。
- HAVING子句在数据聚合之后应用,用于过滤聚合后的结果集。它通常与GROUP BY子句一起使用,出现在GROUP BY子句之后。HAVING子句可以用聚合函数(如SUM、COUNT、AVG、MAX和MIN),而WHERE子句不可以。
使用场景:
- WHERE子句用于过滤行级别的条件,确保只有满足特定条件的行被包括在查询结果中。
- HAVING子句用于过滤聚合后的组,例如,当你想要找出订单数量超过一定数量的产品时,你会使用HAVING子句来过滤这些组。
小结:
- WHERE是查询返回结果之前进行过滤的,用于过滤行级别的数据。
- HAVING是查询返回结果之后,对结果进行过滤的,用于过滤聚合后的数据。
综上所述,选择使用WHERE还是HAVING取决于你的查询需求:如果你需要在数据聚合之前过滤特定的行,应使用WHERE;如果你需要对聚合后的数据进行过滤,例如使用聚合函数并过滤出满足特定条件的组,则应使用HAVING子句
------来自百度百科
- 显示平均工资低于2000的部门和它的平均工资
- 统计各个部门的平均工资
mysql> select deptno,avg(sal) deptavg from emp group by deptno order by deptno asc;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
- having和group by配合使用,对group by结果进行过滤
mysql> select deptno, avg(sal) deptavg from emp group by deptno having deptavg<2000 order by deptno asc;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)--having经常和group by搭配使用,作用是对分组进行筛选,作用有些像where。
- 显示平均工资低于2000的部门和它的平均工资除了SMITH
mysql> select deptno,job,avg(sal) myavg from emp where ename!='SMITH' group by deptno,job having myavg<2000 order by deptno asc;
+--------+----------+-------------+
| 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)
- 执行顺序