数据准备工作：
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10),
age TINYINT,
city VARCHAR(10),
score TINYINT
); INSERT INTO student VALUES (NULL,"曹操",28,"洛阳",95);
INSERT INTO student VALUES (NULL,"刘备",27,"成都",80);
INSERT INTO student VALUES (NULL,"孙权",17,"建业",85);
INSERT INTO student VALUES (NULL,"孔明",21,"成都",98);
INSERT INTO student VALUES (NULL,"关羽",25,"成都",70);
INSERT INTO student VALUES (NULL,"张飞",24,"成都",50);
INSERT INTO student VALUES (NULL,"鲁肃",22,"建业",90);
INSERT INTO student VALUES (NULL,"周瑜",19,"建业",97);
INSERT INTO student VALUES (NULL,"郭嘉",23,"洛阳",98);
INSERT INTO student VALUES (NULL,"小乔",18,"建业",70);
INSERT INTO student VALUES (NULL,"貂蝉",26,NULL,65);
一、简单查询
语法：select select选项 字段列表 from 数据源;
select选项：系统该如何对待查询得到的结果
all：默认的，表示保存所有的记录
distinct：去重，去除重复的记录（所有的字段值都相同），只保留一条
字段列表：对查询结果显示哪些列名
*：全部列名
as：给列名起别名，解决从多张表获取数据存在列名冲突的问题
from数据源：只要是一个符合二维表结构的数据即可
单表数据：from 表名
多表数据：from 表名1，表名2...
动态数据：from (select select选项 字段列表 from 数据源) as 别名
1.1 查询所有学生
查询指令：select * from student;
mysql> select * from student;
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 1 | 曹操 | 28 | 洛阳 | 95 |
| 2 | 刘备 | 27 | 成都 | 80 |
| 3 | 孙权 | 17 | 建业 | 85 |
| 4 | 孔明 | 21 | 成都 | 98 |
| 5 | 关羽 | 25 | 成都 | 70 |
| 6 | 张飞 | 24 | 成都 | 50 |
| 7 | 鲁肃 | 22 | 建业 | 90 |
| 8 | 周瑜 | 19 | 建业 | 97 |
| 9 | 郭嘉 | 23 | 洛阳 | 98 |
| 10 | 小乔 | 18 | 建业 | 70 |
| 11 | 貂蝉 | 26 | NULL | 65 |
+-----+--------+------+--------+-------+
11 rows in set (0.03 sec)
1.2 查询学生姓名和年龄
查询指令：select sname,age from student;
mysql> select sname,age from student;
+--------+------+
| sname | age |
+--------+------+
| 曹操 | 28 |
| 刘备 | 27 |
| 孙权 | 17 |
| 孔明 | 21 |
| 关羽 | 25 |
| 张飞 | 24 |
| 鲁肃 | 22 |
| 周瑜 | 19 |
| 郭嘉 | 23 |
| 小乔 | 18 |
| 貂蝉 | 26 |
+--------+------+
11 rows in set (0.00 sec)
1.3 运算查询：将所有学生的年龄+10进行显示
查询指令：select sname,age+10 from student;
mysql> select sname,age+10 from student;
+--------+--------+
| sname | age+10 |
+--------+--------+
| 曹操 | 38 |
| 刘备 | 37 |
| 孙权 | 27 |
| 孔明 | 31 |
| 关羽 | 35 |
| 张飞 | 34 |
| 鲁肃 | 32 |
| 周瑜 | 29 |
| 郭嘉 | 33 |
| 小乔 | 28 |
| 貂蝉 | 36 |
+--------+--------+
11 rows in set (0.00 sec)
1.4 别名查询：将列名sname,age分别用中文显示
查询指令：select sname as 姓名,age as 年龄 from student;
mysql> select sname as 姓名,age as 年龄 from student;
+--------+--------+
| 姓名 | 年龄 |
+--------+--------+
| 曹操 | 28 |
| 刘备 | 27 |
| 孙权 | 17 |
| 孔明 | 21 |
| 关羽 | 25 |
| 张飞 | 24 |
| 鲁肃 | 22 |
| 周瑜 | 19 |
| 郭嘉 | 23 |
| 小乔 | 18 |
| 貂蝉 | 26 |
+--------+--------+
11 rows in set (0.00 sec)
二、高级查询
语法：select select选项 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 分页 ;
2.1 where 子句
作用：针对表去对应的磁盘处获取所有的记录，where的作用就是在拿到一条结果就开始进行判断，判断是否符合
条件，如果符合就获取放到内存中去，如果不符合条件就不获取。
2.1.1 查询年龄大于24的学生
查询指令：select * from student where age > 24;
mysql> select * from student where age > 24;
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 1 | 曹操 | 28 | 洛阳 | 95 |
| 2 | 刘备 | 27 | 成都 | 80 |
| 5 | 关羽 | 25 | 成都 | 70 |
| 11 | 貂蝉 | 26 | NULL | 65 |
+-----+--------+------+--------+-------+
4 rows in set (0.00 sec)
2.1.2 查询年龄是18到22的学生
查询指令：select * from student where age between 18 and 22;
mysql> select * from student where age between 18 and 22;
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 4 | 孔明 | 21 | 成都 | 98 |
| 7 | 鲁肃 | 22 | 建业 | 90 |
| 8 | 周瑜 | 19 | 建业 | 97 |
| 10 | 小乔 | 18 | 建业 | 70 |
+-----+--------+------+--------+-------+
4 rows in set (0.00 sec)
2.1.3 查询地址是成都或洛阳的学生
查询指令：select * from student where city in ("成都","洛阳");
mysql> select * from student where city in ("成都","洛阳");
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 1 | 曹操 | 28 | 洛阳 | 95 |
| 2 | 刘备 | 27 | 成都 | 80 |
| 4 | 孔明 | 21 | 成都 | 98 |
| 5 | 关羽 | 25 | 成都 | 70 |
| 6 | 张飞 | 24 | 成都 | 50 |
| 9 | 郭嘉 | 23 | 洛阳 | 98 |
+-----+--------+------+--------+-------+
6 rows in set (0.00 sec)
2.1.4 查询姓孙的学生
查询指令：select * from student where sname like "孙%";
mysql> select * from student where sname like "孙%";
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 3 | 孙权 | 17 | 建业 | 85 |
+-----+--------+------+--------+-------+
1 row in set (0.00 sec)
2.1.5 查询地址不为空的学生
查询指令：select * from student where city is not null;
mysql> select * from student where city is not null;
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 1 | 曹操 | 28 | 洛阳 | 95 |
| 2 | 刘备 | 27 | 成都 | 80 |
| 3 | 孙权 | 17 | 建业 | 85 |
| 4 | 孔明 | 21 | 成都 | 98 |
| 5 | 关羽 | 25 | 成都 | 70 |
| 6 | 张飞 | 24 | 成都 | 50 |
| 7 | 鲁肃 | 22 | 建业 | 90 |
| 8 | 周瑜 | 19 | 建业 | 97 |
| 9 | 郭嘉 | 23 | 洛阳 | 98 |
| 10 | 小乔 | 18 | 建业 | 70 |
+-----+--------+------+--------+-------+
10 rows in set (0.00 sec)
2.1.6 查询地址为成都或洛阳，且年龄大于25的学生
查询指令：select * from student where city in ("成都","洛阳") and age > 25;
mysql> select * from student where city in ("成都","洛阳") and age > 25;
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 1 | 曹操 | 28 | 洛阳 | 95 |
| 2 | 刘备 | 27 | 成都 | 80 |
+-----+--------+------+--------+-------+
2 rows in set (0.00 sec)
2.1.7 查询地址不是成都或洛阳的学生
查询指令：select * from student where city not in ("成都","洛阳");
mysql> select * from student where city not in ("成都","洛阳");
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 3 | 孙权 | 17 | 建业 | 85 |
| 7 | 鲁肃 | 22 | 建业 | 90 |
| 8 | 周瑜 | 19 | 建业 | 97 |
| 10 | 小乔 | 18 | 建业 | 70 |
+-----+--------+------+--------+-------+
4 rows in set (0.00 sec)
2.2 group by 子句
作用：根据指定的字段，将数据进行分组，分组的目标是为了统计。
聚合函数：对分组数据进行统计操作。
count()：统计每组中的数量，如果统计目标是字段，那么不统计为空NULL字段，如果为*，则统计记录数
avg()：求平均值
sum()：求和
max()：求最大值
min()：求最小值
2.2.1 统计各个地方的人数
查询指令：select city,count(*) from student group by city;
mysql> select city,count(*) from student group by city;
+--------+----------+
| city | count(*) |
+--------+----------+
| NULL | 1 |
| 建业 | 4 |
| 成都 | 4 |
| 洛阳 | 2 |
+--------+----------+
4 rows in set (0.02 sec)
2.2.2 统计各个地方的平均年龄
查询指令：select city,avg(age) from student group by city;
mysql> select city,avg(age) from student group by city;
+--------+----------+
| city | avg(age) |
+--------+----------+
| NULL | 26.0000 |
| 建业 | 19.0000 |
| 成都 | 24.2500 |
| 洛阳 | 25.5000 |
+--------+----------+
4 rows in set (0.01 sec)
2.2.3 统计各个地方的成绩总和
查询指令： select city,sum(score) from student group by city;
mysql> select city,sum(score) from student group by city;
+--------+------------+
| city | sum(score) |
+--------+------------+
| NULL | 65 |
| 建业 | 342 |
| 成都 | 298 |
| 洛阳 | 193 |
+--------+------------+
4 rows in set (0.00 sec)
2.2.4 统计各个地方的最大成绩与最小成绩
查询指令：select city,max(score),min(score) from student group by city;
mysql> select city,max(score),min(score) from student group by city;
+--------+------------+------------+
| city | max(score) | min(score) |
+--------+------------+------------+
| NULL | 65 | 65 |
| 建业 | 97 | 70 |
| 成都 | 98 | 50 |
| 洛阳 | 98 | 95 |
+--------+------------+------------+
4 rows in set (0.01 sec)
2.3 having 子句
作用：对分组后的数据进行筛选
having与where的区别：
1.having是在分组后对数据进行过滤，where是在分组前对数据进行过滤
2.having后面可以使用统计函数过滤数据，where后面不可以使用统计函数
2.3.1 统计各个地方的学生人数，获取人数大于2的数据
查询指令：select city,count(*) as num from student group by city having num > 2;
mysql> select city,count(*) as num from student group by city having num > 2;
+--------+-----+
| city | num |
+--------+-----+
| 建业 | 4 |
| 成都 | 4 |
+--------+-----+
2 rows in set (0.00 sec)
注意：having是在group by之后，group by是在where之后，where的时候表示将数据从磁盘拿到内存，
where之后的所有操作都是内存操作。
2.4 order by 子句
作用：对查询结果进行排序显示，默认按升序，可以对多个字段进行排序
asc：升序排序
desc：降序排序
2.4.1 把所有学生先按成绩进行降序排序，再按年龄进行升序排序
查询指令：select * from student order by score desc,age asc;
mysql> select * from student order by score desc,age asc;
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 4 | 孔明 | 21 | 成都 | 98 |
| 9 | 郭嘉 | 23 | 洛阳 | 98 |
| 8 | 周瑜 | 19 | 建业 | 97 |
| 1 | 曹操 | 28 | 洛阳 | 95 |
| 7 | 鲁肃 | 22 | 建业 | 90 |
| 3 | 孙权 | 17 | 建业 | 85 |
| 2 | 刘备 | 27 | 成都 | 80 |
| 10 | 小乔 | 18 | 建业 | 70 |
| 5 | 关羽 | 25 | 成都 | 70 |
| 11 | 貂蝉 | 26 | NULL | 65 |
| 6 | 张飞 | 24 | 成都 | 50 |
+-----+--------+------+--------+-------+
11 rows in set (0.00 sec)
2.5 limit 子句
作用：利用limit来限制获取指定区间的数据。
基本语法：limit offset,length;
offset：偏移量，从哪条记录开始，第一条记录为0
length：长度，获取多少条记录
2.5.1 对成绩进行排名，获取成绩是第6名到第10名的学生
查询指令：select * from student order by score desc limit 5,5;
mysql> select * from student order by score desc limit 5,5;
+-----+--------+------+--------+-------+
| sid | sname | age | city | score |
+-----+--------+------+--------+-------+
| 3 | 孙权 | 17 | 建业 | 85 |
| 2 | 刘备 | 27 | 成都 | 80 |
| 5 | 关羽 | 25 | 成都 | 70 |
| 10 | 小乔 | 18 | 建业 | 70 |
| 11 | 貂蝉 | 26 | NULL | 65 |
+-----+--------+------+--------+-------+
5 rows in set (0.00 sec)