目录
区域范围 -> between A and B / >=A and <=B
IN(option...) 选择，只要是option的任意一个则返回
2.5.3 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
2.5.4 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
Limit m offset n 表示起始位置(下标从0开始)，m表示显示多少条信息
2.7.2 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
2.7.3 将总成绩倒数前三的 3 位同学的数学成绩加上 50 分
1. 整体学习的思维导图
2. 表的创建
2.1 Create(创建)
我们先创建一个表，需求如下：
-
Id 序号 非空 唯一 自增长
-
Name 姓名 非空
-
Age 年龄
-
Telphone 电话 非空 唯一
mysql> create table if not exists stu_from(
-> ID int unsigned not null primary key auto_increment,
-> Name varchar(20) not null comment '姓名',
-> Age tinyint comment '年龄',
-> Telphone varchar(25) not null unique comment '电话'
);
mysql> desc stu_from;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(20) | NO | | NULL | |
| Age | tinyint(4) | YES | | NULL | |
| Telphone | varchar(25) | NO | UNI | NULL | |
+----------+------------------+------+-----+---------+----------------+2.1.1 插入规则
mysql> insert into 表名(需要插入的字段,不写默认全插入) valuse(插入的数据需要和字段一一对应);-
单列插入：
mysql> insert into stu_from(Name,Age,Telphone) values('欧阳',18,'1234567890');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu_from(Name,Age,Telphone) values('牛马',19,'1234567891');
Query OK, 1 row affected (0.00 sec)-
多列插入：
mysql> insert into stu_from(Name,Age,Telphone) values('鬼哥',20,'1234567892'), ('张明东',19,'1234567893');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0全字段插入可以省略表名后面括号的内容，如果需要指定插入就需要在括号列出要插入的字段。需要注意的是values()括号内插入的数据顺序需要和stu_from()括号内的字段一一对应。
2.1.2 更新插入
这类插入的核心是：
-
如果插入的数据不存在那么插入。
-
如果插入的数据存在修改数据
mysql> insert into stu_from(Name,Age,Telphone) values('欧阳',18,'1234567890');
ERROR 1062 (23000): Duplicate entry '1234567890' for key 'Telphone'
-- replace 替换
mysql> replace into stu_from(Id, Name, Age, Telphone) values(1,'ouyang',18,'1234567894');
Query OK, 2 rows affected (0.01 sec)
mysql> select * from stu_from;
+----+-----------+------+------------+
| ID | Name | Age | Telphone |
+----+-----------+------+------------+
| 1 | ouyang | 18 | 1234567894 |
| 2 | 牛马 | 19 | 1234567891 |
| 3 | 鬼哥 | 20 | 1234567892 |
| 4 | 张明东 | 19 | 1234567893 |
+----+-----------+------+------------+
-- on duplicate key update
mysql> insert into stu_from(Name,Age,Telphone) values('ouyang',18,'1234567894') on duplicate key update Name='欧阳',Telphone='1234567890';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from stu_from;
+----+-----------+------+------------+
| ID | Name | Age | Telphone |
+----+-----------+------+------------+
| 1 | 欧阳 | 18 | 1234567890 |
| 2 | 牛马 | 19 | 1234567891 |
| 3 | 鬼哥 | 20 | 1234567892 |
| 4 | 张明东 | 19 | 1234567893 |
+----+-----------+------+------------+2.2 Retrieve(读取)
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ... 2.2.1 创建一个实例表
该表要求如下：
-
id 学号 唯一 自增长
-
name 姓名 非空
-
Chinese 语文成绩 非空
-
math 数学成绩 非空
-
english 英语成绩 非空
mysql> create table if not exists stu_SJ(
-> id int unsigned unique auto_increment comment '学号',
-> name varchar(20) not null comment '姓名',
-> chinese int not null comment '语文成绩',
-> math int not null comment '数学成绩',
-> english int not null comment '英语成绩'
-> );
mysql> desc stu_SJ;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| chinese | int(11) | NO | | NULL | |
| math | int(11) | NO | | NULL | |
| english | int(11) | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+ 我们插入以下数据进行测试：
-
('唐三藏', 67, 98, 56)
-
('孙悟空', 87, 78, 77)
-
('猪悟能', 88, 98, 90)
-
('曹孟德', 82, 84, 67)
-
('刘玄德', 55, 85, 45)
-
('孙权', 70, 73, 78)
-
('宋公明', 75, 65, 30)
mysql> insert into stu_SJ(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 stu_SJ;
+----+-----------+---------+------+---------+
| 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 |
+----+-----------+---------+------+---------+2.3 select使用
2.3.1 全表查询
select * from 表名; -- 尽量不要使用全表查询，如果表很大会带来资源浪费问题.mysql> select * from stu_SJ;
+----+-----------+---------+------+---------+
| 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 |
+----+-----------+---------+------+---------+2.3.2 指定序列查询
mysql> select name from stu_SJ;
+-----------+
| name |
+-----------+
| 唐三藏 |
| 孙悟空 |
| 猪悟能 |
| 曹孟德 |
| 刘玄德 |
| 孙权 |
| 宋公明 |
+-----------+2.3.3 查询表达式
mysql> select id, name, 10 from stu_SJ;
+----+-----------+----+
| id | name | 10 |
+----+-----------+----+
| 1 | 唐三藏 | 10 |
| 2 | 孙悟空 | 10 |
| 3 | 猪悟能 | 10 |
| 4 | 曹孟德 | 10 |
| 5 | 刘玄德 | 10 |
| 6 | 孙权 | 10 |
| 7 | 宋公明 | 10 |
+----+-----------+----+
-- select后面添加不同的字段类型，或者表达式都可以接在表格后面。-- 一个字段
mysql> select id, name, chinese+10 from stu_SJ;
+----+-----------+------------+
| id | name | chinese+10 |
+----+-----------+------------+
| 1 | 唐三藏 | 77 |
| 2 | 孙悟空 | 97 |
| 3 | 猪悟能 | 98 |
| 4 | 曹孟德 | 92 |
| 5 | 刘玄德 | 65 |
| 6 | 孙权 | 80 |
| 7 | 宋公明 | 85 |
+----+-----------+------------+
-- 多个字段
mysql> select id, name, chinese+math+english from stu_SJ;
+----+-----------+----------------------+
| id | name | chinese+math+english |
+----+-----------+----------------------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+----------------------+2.3.3.1 为查询表达式改名字
-- 我们可以将表达式改名字
mysql> select id, name, chinese+math+english as total from stu_SJ;
+----+-----------+-------+
| id | name | total |
+----+-----------+-------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+-------+
mysql> select id 学号, name 姓名, chinese+math+english as 总分 from stu_SJ;
+--------+-----------+--------+
| 学号 | 姓名 | 总分 |
+--------+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+--------+-----------+--------+2.3.4 查询去重
我们可以看到有些数学的成绩是相同的，如果我们需要去重怎么做呢？ 使用distinct
-- 去重前
mysql> select math 数学 from stu_SJ;
+--------+
| 数学 |
+--------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
-- 去重后
mysql> select distinct math 数学 from stu_SJ;
+--------+
| 数学 |
+--------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+2.4 where的使用
> , >=, <, <= 大于，大于等于，小于，小于等于
-
查询语文成绩 >60
-
查询英语成绩 >=80
-
查询数学成绩 <60
-
查询英语成绩 <=80
mysql> select name, chinese from stu_SJ where chinese > 60;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
| 孙权 | 70 |
| 宋公明 | 75 |
+-----------+---------+
mysql> select name, english from stu_SJ where english >= 80;
+-----------+---------+
| name | english |
+-----------+---------+
| 猪悟能 | 90 |
+-----------+---------+
mysql> select name, math from stu_SJ where math < 60;
Empty set (0.00 sec)
mysql> select name, english from stu_SJ where english <= 80;
+-----------+---------+
| name | english |
+-----------+---------+
| 唐三藏 | 56 |
| 孙悟空 | 77 |
| 曹孟德 | 67 |
| 刘玄德 | 45 |
| 孙权 | 78 |
| 宋公明 | 30 |
+-----------+---------+= / <=> 的区别
这个在计算NULL会体现出来：
| = | 等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL |
| <=> | 等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1) |
mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
| NULL |
+-------------+
mysql> select NULL <=> NULL;
+---------------+
| NULL <=> NULL |
+---------------+
| 1 |
+---------------+!= / <> 不等于
-
查找表中id不等于3的数据
-
查找表中name不等于孙悟空的数据
mysql> select * from stu_SJ where id!=3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
mysql> select * from stu_SJ where name<>'孙悟空';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+区域范围 -> between A and B / >=A and <=B
-
查找数学成绩在60~90之间的同学
-
查找id在2~6之间的同学
-
查找总分(c+m+e)成绩在150~200之间的同学
mysql> select name,math from stu_SJ where math between 60 and 90;
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
mysql> select id,name from stu_SJ where id >=2 and id <=6;
+----+-----------+
| id | name |
+----+-----------+
| 2 | 孙悟空 |
| 3 | 猪悟能 |
| 4 | 曹孟德 |
| 5 | 刘玄德 |
| 6 | 孙权 |
+----+-----------+
mysql> select id,name,chinese+math+english as total from stu_SJ where chinese+math+english between 150 and 200;
+----+-----------+-------+
| id | name | total |
+----+-----------+-------+
| 5 | 刘玄德 | 185 |
| 7 | 宋公明 | 170 |
+----+-----------+-------+
-- 注意total名称是查找完数据进行的名称修改是在查找之后，如果where total between 150 and 200会报错
mysql> select id,name,chinese+math+english as total from stu_SJ where total between 150 and 200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'IN(option...) 选择，只要是option的任意一个则返回
-
查询chinese 60 67 70 88
mysql> select * from stu_SJ where chinese in(60,67,70,80);
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+IS NULL / IS NOT NULL
查询是否为null
创建一个新表：
-
id 非空 唯一 自增
-
name 非空
-
QQ
mysql> create table if not exists from1(
-> id int unsigned not null primary key auto_increment comment '学号',
-> name varchar(10) not null comment '姓名',
-> QQ varchar(28)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc from1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| QQ | varchar(28) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+插入以下数据：
-
欧阳， 1111
-
牛马， NULL
-
张明东， 2222
-
鬼哥， NULL
mysql> insert from1(name,QQ) values('欧阳','1111');
mysql> insert from1(name,QQ) values('牛马','NULL');
mysql> insert from1(name,QQ) values('张明东','2222');
mysql> insert from1(name,QQ) values('鬼哥',NULL);
mysql> select * from from1;
+----+-----------+------+
| id | name | QQ |
+----+-----------+------+
| 1 | 欧阳 | 1111 |
| 2 | 牛马 | NULL |
| 3 | 张明东 | 2222 |
| 4 | 鬼哥 | NULL |
+----+-----------+------+
-
查找QQ为null
-
查找QQ非null
mysql> select * from from1 where QQ is null;
+----+--------+------+
| id | name | QQ |
+----+--------+------+
| 2 | 牛马 | NULL |
| 4 | 鬼哥 | NULL |
+----+--------+------+
mysql> select * from from1 where QQ is not null;
+----+-----------+------+
| id | name | QQ |
+----+-----------+------+
| 1 | 欧阳 | 1111 |
| 3 | 张明东 | 2222 |
+----+-----------+------+LIKE
模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符
-
查询姓孙的同学
-
查询孙某同学
mysql> select name from stu_SJ where name like '孙%';
+-----------+
| name |
+-----------+
| 孙悟空 |
| 孙权 |
+-----------+
mysql> select name from stu_SJ where name like '孙_';
+--------+
| name |
+--------+
| 孙权 |
+--------+or 或者
-
查询语文成绩在80~90 or 英语成绩 >80
-
查询要么是孙姓学生 or total(总分) > 220
mysql> select name,chinese,english from stu_SJ where (chinese between 80 and 90) or (english > 80);
+-----------+---------+---------+
| name | chinese | english |
+-----------+---------+---------+
| 孙悟空 | 87 | 77 |
| 猪悟能 | 88 | 90 |
| 曹孟德 | 82 | 67 |
+-----------+---------+---------+
mysql> select name, chinese+math+english as total from stu_SJ where name like '孙%' or chinese+math+english > 220;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 孙权 | 221 |
+-----------+-------+查找练习：
-
查询不姓孙的同学
-
查找语文成绩好于英语成绩的同学
-
语文成绩 80 并且不姓孙的同学
mysql> select name from stu_SJ where name not like'孙%';
+-----------+
| name |
+-----------+
| 唐三藏 |
| 猪悟能 |
| 曹孟德 |
| 刘玄德 |
| 宋公明 |
+-----------+
mysql> select name,chinese,english from stu_SJ where chinese>english;
+-----------+---------+---------+
| name | chinese | english |
+-----------+---------+---------+
| 唐三藏 | 67 | 56 |
| 孙悟空 | 87 | 77 |
| 曹孟德 | 82 | 67 |
| 刘玄德 | 55 | 45 |
| 宋公明 | 75 | 30 |
+-----------+---------+---------+
mysql> select name,chinese from stu_SJ where chinese > 80 and name not like'孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+2.5 order by 子句
-- ASC 为升序（从小到大） -- DESC 为降序（从大到小） -- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...]; 注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
2.5.1 同学及总分成绩，按总分成绩升序显示
mysql> select name,chinese+math+english as total from stu_SJ order by chinese+math+english asc;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 233 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
+-----------+-------+2.5.2 同学及qq 号，按 qq 号降序排序显示
mysql> select name,QQ from from1 order by QQ desc;
+-----------+------+
| name | QQ |
+-----------+------+
| 张明东 | 2222 |
| 欧阳 | 1111 |
| 牛马 | NULL |
| 鬼哥 | NULL |
+-----------+------+
-- NULL比任何数都小2.5.3 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
mysql> select name,math,english,chinese from stu_SJ order by math desc, english asc, chinese asc;
+-----------+------+---------+---------+
| 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 |
+-----------+------+---------+---------+2.5.4 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
mysql> select name,math from stu_SJ where name like'孙%' or name like'曹%' order by math desc;
+-----------+------+
| name | math |
+-----------+------+
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
+-----------+------+2.6 筛选分页操作 limit
-- 起始下标为 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; -
以N条为单位展示数据
mysql> select * from stu_SJ limit 3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
-
Limit m offset n 表示起始位置(下标从0开始)，m表示显示多少条信息
mysql> select * from stu_SJ limit 3 offset 0;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
mysql> select * from stu_SJ limit 3 offset 3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
mysql> select * from stu_SJ limit 3 offset 6;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+2.7 Update的使用
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...] 2.7.1 将刘玄德同学的语文成绩变更为 80 分
mysql> select * from stu_SJ limit 3 offset 0;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
mysql> select * from stu_SJ limit 3 offset 3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
mysql> select * from stu_SJ limit 3 offset 6;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
mysql> update stu_SJ set chinese=80 where name='刘玄德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select name,chinese from stu_SJ where name='刘玄德';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 刘玄德 | 80 |
+-----------+---------+2.7.2 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
mysql> select name,math,chinese from stu_SJ where name='曹孟德';
+-----------+------+---------+
| name | math | chinese |
+-----------+------+---------+
| 曹孟德 | 84 | 82 |
+-----------+------+---------+
mysql> update stu_SJ 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 stu_SJ where name='曹孟德';
+-----------+------+---------+
| name | math | chinese |
+-----------+------+---------+
| 曹孟德 | 60 | 70 |
+-----------+------+---------+2.7.3 将总成绩倒数前三的 3 位同学的数学成绩加上 50 分
-- 找出倒数前三位的成绩
mysql> select name,chinese+math+english as total from stu_SJ order by total asc limit 3;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 170 |
| 曹孟德 | 197 |
| 刘玄德 | 210 |
+-----------+-------+
mysql> update stu_SJ set math=math+30 order by chinese+math+english asc limit 3;
mysql> select name,chinese+math+english as total from stu_SJ order by total asc limit 3;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 200 |
| 唐三藏 | 221 |
| 孙权 | 221 |
+-----------+-------+2.7.4 将所有同学的总成绩更新为原来的 2 倍
mysql> select name,chinese+math+english total from stu_SJ order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 刘玄德 | 240 |
| 曹孟德 | 227 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 宋公明 | 200 |
+-----------+-------+
mysql> update stu_SJ set chinese=chinese*2,math=math*2,english=english*2;
mysql> select name,chinese+math+english total from stu_SJ order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 552 |
| 孙悟空 | 484 |
| 刘玄德 | 480 |
| 曹孟德 | 454 |
| 唐三藏 | 442 |
| 孙权 | 442 |
| 宋公明 | 400 |
+-----------+-------+2.8 Delete的使用
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...] 2.8.1 删除孙悟空同学的考试成绩
mysql> delete from stu_SJ where name='孙悟空';
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu_SJ;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 196 | 112 |
| 3 | 猪悟能 | 176 | 196 | 180 |
| 4 | 曹孟德 | 140 | 180 | 134 |
| 5 | 刘玄德 | 160 | 230 | 90 |
| 6 | 孙权 | 140 | 146 | 156 |
| 7 | 宋公明 | 150 | 190 | 60 |
+----+-----------+---------+------+---------+2.8.2 删除整张表
-- 谨慎使用
-- 我们先创建一个删除用例用于测试
mysql> create table if not exists Test_delete(
-> id int unsigned primary key auto_increment,
-> name char(5)
-> );
mysql> desc Test_delete;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(5) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
mysql> insert into Test_delete(name) values('A'),('B'),('C');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from Test_delete limit 3 offset 0;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
mysql> select * from Test_delete limit 3;
Empty set (0.00 sec)
mysql> show create table Test_delete \G;
*************************** 1. row ***************************
Table: Test_delete
Create Table: CREATE TABLE `Test_delete` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(5) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)2.8.3 截断表
TRUNCATE [TABLE] table_name 注意：这个操作慎用
-
只能对整表操作，不能像 DELETE 一样针对部分数据操作；
-
实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事
物，所以无法回滚
-
会重置 AUTO_INCREMENT 项
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
mysql> desc for_truncate;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
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> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
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)2.9 插入查询结果
INSERT INTO table_name [(column [, column ...])] SELECT ... 本次案例是将一个表中的重复数据去掉插入到另一个表中。
两个表分别为：Test_tb 和 TestCp_tb
-- 我们先来创建一个测试表
mysql> create table if not exists Test_tb( id int unsigned , name varchar(15) );
-- 插入一些数据
mysql> insert into Test_tb(id, name) values(100, 'aaa');
mysql> insert into Test_tb(id, name) values(100, 'aaa');
mysql> insert into Test_tb(id, name) values(200, 'bbb');
mysql> insert into Test_tb(id, name) values(200, 'bbb');
mysql> insert into Test_tb(id, name) values(300, 'bbb');
mysql> insert into Test_tb(id, name) values(300, 'ccc');
mysql> insert into Test_tb(id, name) values(400, 'ccc');
mysql> insert into Test_tb(id, name) values(400, 'ccc');
mysql> select * from Test_tb;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 100 | aaa |
| 200 | bbb |
| 200 | bbb |
| 300 | bbb |
| 300 | ccc |
| 400 | ccc |
| 400 | ccc |
+------+------+
-- 第一步了解怎么筛选去重 -- detinct
mysql> select distinct * from Test_tb;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | bbb |
| 300 | ccc |
| 400 | ccc |
+------+------+
-- 创建一个一模一样的表
mysql> create table if not exists TestCP_tb like Test_tb;
Query OK, 0 rows affected (0.01 sec)
mysql> desc TestCP_tb;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
-- 向表中插入筛选出的非重复数据
mysql> insert into TestCP_tb select distinct * from Test_tb;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from TestCP_tb;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | bbb |
| 300 | ccc |
| 400 | ccc |
+------+------+3.0 聚合函数
函数+说明
-
COUNT([DISTINCT] expr) 返回查询到的数据的 数量
-
SUM([DISTINCT] expr) 返回查询到的数据的 总和，不是数字没有意义
-
AVG([DISTINCT] expr) 返回查询到的数据的 平均值，不是数字没有意义
-
MAX([DISTINCT] expr) 返回查询到的数据的 最大值，不是数字没有意义
-
MIN([DISTINCT] expr) 返回查询到的数据的 最小值，不是数字没有意义
-
样例表
-
mysql> select * from stu_SJ;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 196 | 112 |
| 2 | 孙悟空 | 120 | 200 | 79 |
| 3 | 猪悟能 | 120 | 196 | 180 |
| 4 | 曹孟德 | 140 | 180 | 134 |
| 5 | 刘玄德 | 160 | 230 | 90 |
| 6 | 孙权 | 140 | 146 | 156 |
| 7 | 宋公明 | 150 | 190 | 60 |
+----+-----------+---------+------+---------+3.0.1 统计班内有多少同学
mysql> select count(*) from stu_SJ;
+----------+
| count(*) |
+----------+
| 7 |
+----------+3.0.2 班级内english>=100有多少人
mysql> select count(english) from stu_SJ where english>=100;
+----------------+
| count(english) |
+----------------+
| 4 |
+----------------+3.0.3 计算班级中语文无重复的个数有多少
mysql> select * from stu_SJ;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 196 | 112 |
| 2 | 孙悟空 | 120 | 200 | 79 |
| 3 | 猪悟能 | 120 | 196 | 180 |
| 4 | 曹孟德 | 140 | 180 | 134 |
| 5 | 刘玄德 | 160 | 230 | 90 |
| 6 | 孙权 | 140 | 146 | 156 |
| 7 | 宋公明 | 150 | 190 | 60 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select count(distinct chinese) from stu_SJ;
+-------------------------+
| count(distinct chinese) |
+-------------------------+
| 5 |
+-------------------------+3.0.4 统计数学的总分数
mysql> select sum(math) from stu_SJ;
+-----------+
| sum(math) |
+-----------+
| 1338 |
+-----------+3.0.6 返回英语最高分
-- max
mysql> select max(english) from stu_SJ;
+--------------+
| max(english) |
+--------------+
| 180 |
+--------------+
-- 找到math <= 150 的最高分
mysql> select max(math) from stu_SJ where math <= 150;
+-----------+
| max(math) |
+-----------+
| 146 |
+-----------+3.1 group by 子句
在select中使用group by 子句可以对指定列进行分组查询
select column1, column2, .. from table group by column; 案例准备：
-
准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
-
EMP员工表
-
DEPT部门表
-
SALGRADE工资等级表
-
mysql> use scott;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| salgrade |
+-----------------+
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
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 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+如何显示每个部门的平均工资和最高工资
-- 分成多个部门
mysql> select deptno from emp group by deptno;
+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
+--------+
mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal) | max(sal) |
+--------+-------------+----------+
| 10 | 2916.666667 | 5000.00 |
| 20 | 2175.000000 | 3000.00 |
| 30 | 1566.666667 | 2850.00 |
+--------+-------------+----------+-
显示每个部门的每种岗位的平均工资和最低工资
mysql> select job,avg(sal),min(sal) from emp group by job; +-----------+-------------+----------+ | job | avg(sal) | min(sal) | +-----------+-------------+----------+ | ANALYST | 3000.000000 | 3000.00 | | CLERK | 1037.500000 | 800.00 | | MANAGER | 2758.333333 | 2450.00 | | PRESIDENT | 5000.000000 | 5000.00 | | SALESMAN | 1400.000000 | 1250.00 | +-----------+-------------+----------+ -
显示平均工资低于2000的部门和它的平均工资
-
统计各个部门的平均工资
-
having和group by配合使用，对group by结果进行过滤
-
mysql> select avg(sal) from emp group by deptno having avg(sal) < 2000;
+-------------+
| avg(sal) |
+-------------+
| 1566.666667 |
+-------------+
--having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。 having和where的区别：
-
WHERE：用于在数据分组前过滤记录，不支持聚合函数，可以使用任意字段作为过滤条件。
-
HAVING：用于在数据分组后过滤分组，支持聚合函数，但不能包含既不在GROUP BY子句中也不在聚合函数中的字段，可以使用字段别名作为过滤条件。
面试题：SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
