MySQL学习笔记（三）之单表查询
一、创建测试数据表
create table students(
id int unsigned not null auto_increment,
name varchar(12) not null,
class_id int unsigned null,
gender tinyint(1) unsigned not null,
age tinyint(2) unsigned not null,
score tinyint(3) null,
primary key(`id`)
)engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci;
# 插入测试数据
insert into students(name,class_id,gender,age,score) values ('包不同',3,1,8,87),
('郁光标',1,1,7,67),('幽草',6,2,7,95),('融智大师',2,2,7,null),('老贾',5,2,7,44),
('完颜阿骨打',1,1,8,60),('辛双清',5,2,7,91),('单伯山',1,2,7,54),('孟师叔',6,1,6,95),
('宋长老',6,2,8,64),('刀白凤',2,1,7,56),('于洞主',4,2,8,74),('智光大师',3,1,6,54),
('苏辙',6,2,7,47),('风波恶',6,1,6,null),('无崖子',5,1,6,0),('天山童姥',6,2,6,null),
('司马林',5,1,6,null),('芙蓉仙子崔绿华',3,1,8,100),('康广陵',3,2,6,63),('龚光杰',2,2,8,94),
('葛光佩',6,1,6,48),('竹剑',1,2,6,85),('耶律重元',6,2,7,86),('许卓诚',5,1,6,62),
('梦姑',3,1,7,69),('诸保昆',1,2,6,63),('秦伯起',1,2,6,72),('徐长老',1,1,8,60),
('海风子',6,2,6,93),('阿洪',2,1,6,78),('祁六',1,1,8,96),('易大彪',2,2,7,78),
('范百龄',3,2,7,76),('玄生',6,2,6,null),('玄慈',6,1,6,0),('颇拉苏',5,1,6,null),
('慕容复',2,1,8,0),('章虚道长',4,2,6,null),('玄难',5,2,7,83),('古笃诚',2,2,8,93),
('黎夫人',3,2,7,84);
二、MySQL查询
1. 基础查询：
语法：select 字段列表[,常量,表达式,函数等] from [table_name];
a）查询表中的字段
mysql> select name from students; #查询students表中的name字段
b）查询多个字段，字段列表使用逗号（,）分隔，查询所有字段可以使用*
mysql> select name,class_id,age from students; #查询部分字段
mysql> select * from students; #查询所有字段
c）去重查询，使用distinct关键字
mysql> select distinct class_id from students;
d）给字段取别名，使用 as 或空格，**当别名中有空格或其他特殊字符时，必须使用引号引起来，否则将会报错**
mysql> select name [as] 姓名 from student ;
e）拼接字段，使用concat函数，不能使用‘+’进行连接，在MySQL中‘+’号只是用于计算数值的和，若加号两边不为数值类型，MySQL将强制转换成数值类型，进行加法计算，若强制转换失败，则将字符串转换成0，null和任何数字相加结果均为null。
mysql> select name+score from students;
+------------+
| name+score |
+------------+
| 87 |
| 67 |
| 95 |
| NULL |
mysql> select concat(name,' ',score) as 姓名和分数 from students;
+---------------------------+
| 姓名和分数 |
+---------------------------+
| 包不同 87 |
| 郁光标 67 |
| 幽草 95 |
| NULL |
f）查询常量
mysql> select 1111;
mysql> select 'abc';
g）查询表达式
mysql> select 6568+256;
h）查询函数
mysql> select version();
2. 条件查询
语法：select 字段 from table_name where 筛选条件
a）关系运算符：
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 不等于
!= 不等于（与<>等同）
<=> 安全等于
b）逻辑运算符：
and 与
or 或
not 非
&& 与（等同于and）
c）模糊查询：
like
not like like取反
d）范围查询：
in(值1,值2,...) 在in里面
not int(值1,值2) in取反
between and 在between和and之间
not between and between and 取反
e）空值(null)查询：
is null 为null的列
is not null 不为null的列
# 1. 查询60分以上(包括)的学生的姓名，班级ID，分数
mysql> select name,class_id,score from students where score >= 60;
# 2. 查询出班级ID不为1的学生姓名，班级ID
mysql> select name,class_id from students where class_id <> 1;#或
mysql> select name,class_id from students where class_id != 1;
# 3. 查询出班级ID为2并且分数大于60的学生姓名，分数
mysql> select name,score from students where class_id=2 and score>60;
# 4. 查询出成绩小于60和大于90的学生姓名，班级ID和分数
mysql> select name,class_id,score from students where score<60 or score>90;
或：mysql> select name,class_id,score from students where not (score>=60 and score <=90);
#5. 查询出学生姓名中有‘大’的学生名字
mysql> select name from students where name like '%大%';
#6. 查询出学生姓名中第三个字符为‘大’的学生名字
mysql> select name from students where name like '__大%';
#7. 查询出学生姓名中第二个字符为'_'的学生姓名
mysql> select name from students where name like '_\_%';
**说明：**
1）%：匹配多个字符
2）_：匹配一个字符
3）\：转义字符
4）使用escape关键字可以自定义转义字符：如
mysql> select name from students where name like '_$_%' escape '$';
# 8. 查询出学生class_id是2,3,6的学生姓名，班级ID
mysql> select name,class_id from students where class_id in(2,3,6);
# 9.查询出学生成绩在65~90之间的学生姓名，成绩
mysql> select name,score from students where score between 60 and 90;
# 10. 查询出学生成绩为null的学生姓名，班级ID，成绩
mysql> select name,class_id,score from students where score is null;
说明：
1）与score=null条件不一样
2）可以使用score<=>null查询
3）<=>：安全等于，既可以查询出值为null的行，也可以查询其他值的行，is null只能查询出为null的行。
# 11. 查询出学生成绩不为null的学生姓名，班级ID，成绩
mysql> select name,class_id,score from students where score is not null;
3）排序查询
语法：select 查询列表 from table_name [where 筛选条件] order by 字段 [排序规则];
排序规则：
asc：正序
desc：倒序
排序规则省略默认为asc（正序），有null和0时，asc排序时null在前，多字段排序：field1 asc,field2 desc
# 1.查询出学生的姓名，成绩，并按照正序排列
mysql> select name,score from students order by score;
# 2. 查询出学生姓名，班级ID，成绩，并按照班级ID正序，成绩倒序排序
mysql> select name,class_id,score from students order by class_id asc,score desc;
#3. 查询出班级ID为3的学生姓名，班级ID，成绩，并按照成绩倒序排序
mysql> select name,class_id,score from students where class_id=3 order by score desc;
4）分组查询
语法：select 查询列表 from table_name [where 筛选条件] group by 分组依据 [having 过滤条件] [order by field 排序规则]
mysql> select * from students group by class_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdb1.students.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
**报错了？？？**
分析报错信息发现原来是sql_mode=only_full_group_by惹的祸，关于sql_mode信息请查阅[MySQL学习笔记之sql_mode](https://www.cnblogs.com/huige185/p/13948910.html)
取消sql_mode的only_full_group_by项：
mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from students group by class_id;
+----+--------------+----------+--------+-----+-------+
| id | name | class_id | gender | age | score |
+----+--------------+----------+--------+-----+-------+
| 1 | 包不同 | 3 | 1 | 8 | 87 |
| 2 | 郁光标 | 1 | 1 | 7 | 67 |
| 3 | 幽草 | 6 | 2 | 7 | 95 |
| 4 | 融智大师 | 2 | 2 | 7 | NULL |
| 5 | 老贾 | 5 | 2 | 7 | 44 |
| 12 | 于洞主 | 4 | 2 | 8 | 74 |
+----+--------------+----------+--------+-----+-------+
6 rows in set (0.00 sec)
设置sql_mode='only_full_group_by'可以使用聚合函数：
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
# 1. 查询每个班级的总人数
mysql> select count(*) 每个班级总人数,class_id from students group by class_id;
+-----------------------+----------+
| 每个班级总人数 | class_id |
+-----------------------+----------+
| 7 | 3 |
| 8 | 1 |
| 11 | 6 |
| 7 | 2 |
| 7 | 5 |
| 2 | 4 |
+-----------------------+----------+
6 rows in set (0.00 sec)
# 2. 查询每个班级男生总人数
mysql> select count(*),class_id from students where gender = 1 group by class_id order by class_id;
+----------+----------+
| count(*) | class_id |
+----------+----------+
| 4 | 1 |
| 3 | 2 |
| 4 | 3 |
| 4 | 5 |
| 4 | 6 |
+----------+----------+
5 rows in set (0.00 sec)
# 3. 查询班级ID大于3的每个班级女生人数，并按照女生人数倒序排序
mysql> select count(*) 女生人数,class_id from students where gender = 2 group by class_id having class_id > 3 order by 女生人数 desc;
+--------------+----------+
| 女生人数 | class_id |
+--------------+----------+
| 7 | 6 |
| 3 | 5 |
| 2 | 4 |
+--------------+----------+
3 rows in set (0.00 sec)
# 4. 查询出每个班级的最高分数
mysql> select class_id,max(score) from students group by class_id order by class_id;
+----------+------------+
| class_id | max(score) |
+----------+------------+
| 1 | 96 |
| 2 | 94 |
| 3 | 100 |
| 4 | 74 |
| 5 | 91 |
| 6 | 95 |
+----------+------------+
6 rows in set (0.00 sec)
# 5. 查询出每个班级有成绩的前三名的学生全部信息
mysql> select a.* from students a where (select count(*) from students where class_id=a.class_id and score>a.score) < 3 and a.score is not null order by a.class_id,a.score desc;
+----+-----------------------+----------+--------+-----+-------+
| id | name | class_id | gender | age | score |
+----+-----------------------+----------+--------+-----+-------+
| 32 | 祁六 | 1 | 1 | 8 | 96 |
| 23 | 竹剑 | 1 | 2 | 6 | 85 |
| 28 | 秦伯起 | 1 | 2 | 6 | 72 |
| 21 | 龚光杰 | 2 | 2 | 8 | 94 |
| 41 | 古笃诚 | 2 | 2 | 8 | 93 |
| 31 | 阿洪 | 2 | 1 | 6 | 78 |
| 33 | 易大彪 | 2 | 2 | 7 | 78 |
| 19 | 芙蓉仙子崔绿华 | 3 | 1 | 8 | 100 |
| 1 | 包不同 | 3 | 1 | 8 | 87 |
| 42 | 黎夫人 | 3 | 2 | 7 | 84 |
| 12 | 于洞主 | 4 | 2 | 8 | 74 |
| 7 | 辛双清 | 5 | 2 | 7 | 91 |
| 40 | 玄难 | 5 | 2 | 7 | 83 |
| 25 | 许卓诚 | 5 | 1 | 6 | 62 |
| 9 | 孟师叔 | 6 | 1 | 6 | 95 |
| 3 | 幽草 | 6 | 2 | 7 | 95 |
| 30 | 海风子 | 6 | 2 | 6 | 93 |
+----+-----------------------+----------+--------+-----+-------+
17 rows in set (0.00 sec)
5）分页查询：
select 查询列表 from table_name where 筛选条件 group by 分组依据 having 过滤条件 order by 排序规则 limit 起始条数,每页显示条数;
三、SQL的执行顺序
select 查询列表 from table_name where 筛选条件 group by 分组依据 having 过滤条件 order by 排序规则 limit 起始条数,每页显示条数;
执行顺序
1. from 子句
2. where 子句
3. group by 子句
4. having 子句
5. select 子句
6. order by 子句
7. limit 子句
