#转载请联系
为了更好的了解下面的知识点，我们先创建两张表并插入数据。
# 学生表 +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | | 9 | 程坤 | 27 | 181.00 | 男 | 2 | | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | | 11 | 金星 | 33 | 162.00 | 中性 | 3 | | | 12 | 静香 | 12 | 180.00 | 女 | 4 | | | 13 | 郭靖 | 12 | 170.00 | 男 | 4 | | | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | +----+-----------+------+--------+--------+--------+-----------+ #班级表 +----+------+ | id | name | +----+------+ | 1 | 1班 | | 2 | 2班 | +----+------+
- as关键字
as关键字可以给表和字段起别名，可以让表更加易读，或者写sql语句时更加方便。
# as 关键字给字段起别名，让查询到的表更加直观 mysql> select name as '名字',age as '年龄' from students; +-----------+--------+ | 名字 | 年龄 | +-----------+--------+ | 小明 | 18 | | 小月月 | 18 | | 彭于晏 | 29 | | 刘德华 | 59 | | 黄蓉 | 38 | | 凤姐 | 28 | | 王祖贤 | 18 | | 周杰伦 | 36 | | 程坤 | 27 | | 刘亦菲 | 25 | | 金星 | 33 | | 静香 | 12 | | 郭靖 | 12 | | 周杰 | 34 | +-----------+--------+
# as 关键词给表起别名 mysql> select s.name,s.age from students as s; +-----------+------+ | name | age | +-----------+------+ | 小明 | 18 | | 小月月 | 18 | | 彭于晏 | 29 | | 刘德华 | 59 | | 黄蓉 | 38 | | 凤姐 | 28 | | 王祖贤 | 18 | | 周杰伦 | 36 | | 程坤 | 27 | | 刘亦菲 | 25 | | 金星 | 33 | | 静香 | 12 | | 郭靖 | 12 | | 周杰 | 34 | +-----------+------+
# 上面两个例子的用法结合使用 mysql> select s.name as '名字',s.age as '年龄' from students as s; +-----------+--------+ | 名字 | 年龄 | +-----------+--------+ | 小明 | 18 | | 小月月 | 18 | | 彭于晏 | 29 | | 刘德华 | 59 | | 黄蓉 | 38 | | 凤姐 | 28 | | 王祖贤 | 18 | | 周杰伦 | 36 | | 程坤 | 27 | | 刘亦菲 | 25 | | 金星 | 33 | | 静香 | 12 | | 郭靖 | 12 | | 周杰 | 34 | +-----------+--------+
- distinct关键词
distinct关键词可以消除重复行。
mysql> select distinct gender from students; +--------+ | gender | +--------+ | 女 | | 男 | | 保密 | | 中性 | +--------+
- 比较运算符
- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>
mysql> select * from students where age>18; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | | 9 | 程坤 | 27 | 181.00 | 男 | 2 | | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | | 11 | 金星 | 33 | 162.00 | 中性 | 3 | | | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | +----+-----------+------+--------+--------+--------+-----------+
- 逻辑运算符
- and
- or
- not
优先级：小括号 > 算术运算符 > 比较运算符 > not > and > or
# 找出除了18岁以上女性的全部人信息 mysql> select * from students where not (age>18 and gender='女'); +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | | 9 | 程坤 | 27 | 181.00 | 男 | 2 | | | 11 | 金星 | 33 | 162.00 | 中性 | 3 | | | 12 | 静香 | 12 | 180.00 | 女 | 4 | | | 13 | 郭靖 | 12 | 170.00 | 男 | 4 | | +----+-----------+------+--------+--------+--------+-----------+
- 模糊查询
- like
- %表示任意多个任意字符
- _表示一个任意字符
# 查找姓周的全部同学 mysql> select * from students where name like '周%'; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | +----+-----------+------+--------+--------+--------+-----------+
# 查找姓周，且名字只有一个字的同学 mysql> select * from students where name like '周_'; +----+--------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+--------+------+--------+--------+--------+-----------+ | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | +----+--------+------+--------+--------+--------+-----------+
# 查找姓名中 包含 "杰" 字的所有名字 mysql> select * from students where name like '%杰%'; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | +----+-----------+------+--------+--------+--------+-----------+
- 范围查询
- in用在一个非连续的范围内
- between ... and ...表示在一个连续的范围内
# 查找id是1.3.5.7的同学 mysql> select * from students where id in (1,3,5,7); +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | +----+-----------+------+--------+--------+--------+-----------+
# 查找id为1-8的同学 mysql> select * from students where id between 1 and 8; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | +----+-----------+------+--------+--------+--------+-----------+
- 空判断
- 判空 is null
- 判非空 is not null
# 查找身高为空的同学 mysql> select * from students where height is null; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | +----+-----------+------+--------+--------+--------+-----------+
# 查找填了身高信息的女性 mysql> select * from students where height is not null and gender=2; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | | 12 | 静香 | 12 | 180.00 | 女 | 4 | | | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | +----+-----------+------+--------+--------+--------+-----------+
- 排序
- 将行数据按照列1进行排序，如果某些行 列1 的值相同时，则按照 列2 排序，以此类推
- asc从小到大排列，即升序
- desc从大到小排序，即降序
- 默认按照列值从小到大排列（即asc关键字）
# 所有学生信息先按照年龄从大到小排序，当年龄相同时 按照身高从高到矮排序 mysql> select * from students order by age desc,height desc; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | | 11 | 金星 | 33 | 162.00 | 中性 | 3 | | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | | 9 | 程坤 | 27 | 181.00 | 男 | 2 | | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | | 1 | 小明 | 18 | 180.00 | 女 | 1 | | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | | 12 | 静香 | 12 | 180.00 | 女 | 4 | | | 13 | 郭靖 | 12 | 170.00 | 男 | 4 | | +----+-----------+------+--------+--------+--------+-----------+
- 分页
select * from students limit start=0,count # start=可省略
- 从start开始，获取count条数据
- start默认值为0
# 年龄从小到大，前6个作为第一页 mysql> select * from students order by age asc limit 0,6; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 12 | 静香 | 12 | 180.00 | 女 | 4 | | | 13 | 郭靖 | 12 | 170.00 | 男 | 4 | | | 1 | 小明 | 18 | 180.00 | 女 | 1 | | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | +----+-----------+------+--------+--------+--------+-----------+
# 注意：在sql语句中limit后不可以直接加公式- 聚合函数
- 聚合函数，
aggregation function，又称为组函数。默认情况下 聚合函数会对当前所在表当做一个组进行统计。 - count(*) 计算总行数
- max(列) 表示求此列的最大值
- min(列)表示求此列的最小值
- sum(列)表示求此列的和
- avg(列)表示求此列的平均值
# 计算出全班同学的平均年龄 mysql> select avg(age) as '平均年龄' from students; +--------------+ | 平均年龄 | +--------------+ | 27.6429 | +--------------+
- 分组 （第一part）
- 所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行更精细化的处理。
- group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
- group by可用于单个字段分组，也可用于多个字段分组
# 性别分组 mysql> select gender from students group by gender; +--------+ | gender | +--------+ | 男 | | 女 | | 中性 | | 保密 | +--------+
# 查询各个性别的平均年龄和平均身高，并保持两位小数。 mysql> select gender,round(avg(age),2),round(avg(height),2) from students group by gender; +--------+-------------------+----------------------+ | gender | round(avg(age),2) | round(avg(height),2) | +--------+-------------------+----------------------+ | 男 | 32.60 | 177.75 | | 女 | 23.29 | 173.43 | | 中性 | 33.00 | 162.00 | | 保密 | 28.00 | 150.00 | +--------+-------------------+----------------------+
- 分组 （第二part）
- group by + group concat()：group_concat(字段名)根据分组结果，使用group_concat()来放置每一个分组中某字段的集合
- group by + having：having 条件表达式用来过滤分组结果。having作用和where类似，但having只能用于group by，而where是用来过滤表数据的。
# 查找各个性别分组里有哪些同学 mysql> select gender,group_concat(name) from students group by gender; +--------+-----------------------------------------------------------+ | gender | group_concat(name) | +--------+-----------------------------------------------------------+ | 男 | 彭于晏,刘德华,周杰伦,程坤,郭靖 | | 女 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰 | | 中性 | 金星 | | 保密 | 凤姐 | +--------+-----------------------------------------------------------+
# 按性别分组，分别统计出平均年龄超过30岁的组的性别以及姓名 mysql> select gender,avg(age),group_concat(name) from students group by gender having avg(age)>30; +--------+----------+---------------------------------------------+ | gender | avg(age) | group_concat(name) | +--------+----------+---------------------------------------------+ | 男 | 32.6000 | 彭于晏,刘德华,周杰伦,程坤,郭靖 | | 中性 | 33.0000 | 金星 | +--------+----------+---------------------------------------------+
- 子查询
在一个 select 语句中，嵌入了另外一个 select 语句，那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询。
- 标量子查询: 子查询返回的结果是一个数据(一行一列)
- 列子查询: 子查询返回的结果是一列(一列多行)
- 行子查询: 子查询返回的结果是一行(一行多列)
- 表子查询: 子查询返回的结果是一个临时表(多行多列)
# 标量子查询，子查询返回的结果是一个数据
# 查找大于班级平均年龄的同学的信息 mysql> select * from students where age>(select avg(age) from students); +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | | 11 | 金星 | 33 | 162.00 | 中性 | 3 | | | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | +----+-----------+------+--------+--------+--------+-----------+
# 列子查询 子查询返回的结果是一列 # 查找有对应班级的学生的全部信息 mysql> select * from students where cls_id in (select id from classes); +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | | 9 | 程坤 | 27 | 181.00 | 男 | 2 | | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | +----+-----------+------+--------+--------+--------+-----------+
# 行子查询 子查询返回的结果是一行
# 查找出年龄最大，而且身高最高的那个人。 mysql> select * from students where (age,height)=(select max(age),max(height) from students); Empty set (0.00 sec)
# 表子查询 子查询返回的是一个表 # 查找学号小于6的男同学 mysql> select * from (select * from students where id<6) as a where a.gender=1; +----+-----------+------+--------+--------+--------+-----------+ | id | name | age | height | gender | cls_id | is_delete | +----+-----------+------+--------+--------+--------+-----------+ | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | +----+-----------+------+--------+--------+--------+-----------+ # 注意：子查询返回的表是一个临时表，所以一定要用as关键词起个别名
- 连接与自连接（参考我的另一篇博文：https://www.cnblogs.com/chichung/p/9588187.html）
- 附录 SQL的完整格式
SELECT select_expr [,select_expr,...] [
FROM tb_name
[JOIN 表名]
[ON 连接条件]
[WHERE 条件判断]
[GROUP BY {col_name | postion} [ASC | DESC], ...]
[HAVING WHERE 条件判断]
[ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
[ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]
# 精简版
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count
#执行顺序
from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count
end~~~