———————————— 高级查询
— as 起别名
select name as 名字 from studnets;
— 消除重复的行 — 查看有哪几种xxx
select distinct gender from students; — 查看有哪几种性别
select distinct name,gender from students; — 查看有哪几种(性别，姓名)组合
—— where 运算符
—- 比较
— 等于: =
— 大于: >
— 大于等于: >=
— 小于: <
— 小于等于: <=
— 不等于: != 或 <>
select * from students where id <= 4;
—- 逻辑
— and or not
select * from students where id < 4 or is_delete=0;
——– 模糊查询
— like
— %表示任意多个任意字符
— _表示一个任意字符
select * from students where name like ‘M%‘;
select * from students where name like ‘Mik_‘;
——– 范围查询
— in表示在一个非连续的范围内
select * from students where id in (1, 3);
— between … and …表示在一个连续的范围内
select * from students where cls_id between 1 and 3; — 1，2，3
——— 空判断
select * from students where cls_id is not null;
——————————————————–排序
select * from students order by name asc默认/desc;
select * from students [where age in (10, 11)] order by name; — 先拿到数据集再排序
——————————————————–聚合函数
select count(*) from students;
— select max min avg
select sum(age) from students;
——————————————————– 分组
select gender from students group by gender;
— +——–+
— | gender |
— +——–+
— | 男 |
— | 女 |
— | 中性 |
— | 保密 |
— +——–+
——————– group by + group_concat()
select gender, group_concat(name) from students group by gender;
— +——–+———————————————————–+
— | gender | group_concat(name) |
— +——–+———————————————————–+
— | 男 | 彭于晏,刘德华,周杰伦,程坤,郭靖 |
— | 女 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰 |
— | 中性 | 金星 |
— | 保密 | 凤姐 |
— +——–+———————————————————–+
——————– group by + 集合函数
— 分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender;
——————– group by + having 过滤
— 平均年龄大于10的性别
select gender, avg(age) from students group by gender having avg(age) > 10;
— 人数大于1的性别和人数
select gender, count(*) from students group by gender having count(*) > 1;
——————– group by + with rollup 汇总
—- with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
select gender,count(*) from students group by gender with rollup;
—————————————-分页
select * from students limit 3; — 取前3条，相当于0,3
—- select * from 表名 limit start,count
select * from students limit 2, 4; — 跳过2条 从第3条开始取4条
—————————————- 链接查询
select * from classes as c [inner] join students as s on c.id = s.cls_id;
— +—-+———–+—-+——+——+——–+——–+——–+
— | id | name | id | name | age | height | gender | cls_id |
— +—-+———–+—-+——+——+——–+——–+——–+
— | 1 | 精英班 | 1 | Mike | 10 | 170.00 | 男 | 1 |
— | 1 | 精英班 | 2 | John | 11 | 180.00 | 男 | 1 |
— +—-+———–+—-+——+——+——–+——–+——–+
select * from classes as c left join students as s on c.id = s.cls_id;
— +—-+———–+——+——+——+——–+——–+——–+
— | id | name | id | name | age | height | gender | cls_id |
— +—-+———–+——+——+——+——–+——–+——–+
— | 1 | 精英班 | 1 | Mike | 10 | 170.00 | 男 | 1 |
— | 1 | 精英班 | 2 | John | 11 | 180.00 | 男 | 1 |
— +—-+———–+——+——+——+——–+——–+——–+
select * from classes as c right join students as s on c.id=s.cls_id;
— +——+———–+—-+——+——+——–+——–+——–+
— | id | name | id | name | age | height | gender | cls_id |
— +——+———–+—-+——+——+——–+——–+——–+
— | 1 | 精英班 | 1 | Mike | 10 | 170.00 | 男 | 1 |
— | 1 | 精英班 | 2 | John | 11 | 180.00 | 男 | 1 |
— +——+———–+—-+——+——+——–+——–+——–+
—————————————自关联
— 通常大分类有小分类这种形式的数据放到一个表中，并且pid指向表的id
CREATE TABLE `areainfo` (
`id` int(10) unsigned NOT NULL,
`name` varchar(32) DEFAULT NULL,
`pid` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
)
— 查询广东省下的所有地级市
select c.name from areainfo as p inner join areainfo as c
on c.pid=p.id where p.name=‘广东省‘;
— 查询山东省下的所有地级市和县区
— 注意： [a join b on 条件] 结果是一个`表`********************
select city.name, county.name from areainfo as county join
(areainfo as city join areainfo as province
on city.pid=province.id and province.pid is null)
on city.id=county.pid
where province.name=‘山东省‘;
select city.name, county.name from (areainfo as county join
(areainfo as city join areainfo as province
on city.pid=province.id and province.pid is null)
on city.id=county.pid)
where province.name=‘山东省‘;
———————————- 子查询
— 每个SQL包含两部分 主查询 和 子查询
— 子查询有三种类型
— 标量子查询: 子查询返回的结果是一个数据(一行一列)
— 列子查询: 返回的结果是一列(一列多行)
— 行子查询: 返回的结果是一行(一行多列)
— 标量子查询： 将子查询的结果当成一个值
— 查询大于平均年龄的学生
select * from students where age > (select avg(age) from students);
— 列子查询： 将子查询的结果当成同一属性(列)多个值的的集合
— 查询班级还存在的学生的名字
select name from students where cls_id in (select id from classes);
— 行子查询： 将多列数据看成一条数据
— 查找班级年龄最大,身高最高的学生
select * from students where (height, age)=
(select max(height), max(age) from students);
— 只有在最大身高、最大年龄刚好是一个人的时候才能查找到数据。