mysql 查询(详细版)

2019独角兽企业重金招聘Python工程师标准>>>

1. 基本查询

• 查询所有字段

select * from students;

• 查询指定字段

select name,age,gender from students;   -- 只有一张表
select s.name,s.age,s.gender from students as s;    -- 有多张表，将sudents表指定为s

• 消除重复行

select distinct name from student;        -- distinct可以过滤掉重复的数据

2. 条件查询

• where 后面支持多种运算符，进行条件的处理

-- 比较运算符和逻辑运算符
select * from students where id > 5 and gender = 1;    -- 查询id>5的男同学
select * from students where name != '张三' or id <= 3;    -- 查询id小于等于3或者名字不等于3的学生

-- 模糊查询（使用like）
select * from students where name like '张%' or name like '%三';    -- 查询名字以张开头的学生或者以三结尾的学生(%表示一个或多个字符)
select * from students where name like '张_' or name like '_三'    -- 查询名字以张开头或者以三结尾的两个字的学生(_表示一个字符)

-- 范围查询(between and ,in)
select * from students where id between 1 and 3;    -- 查询id在1-3之间的学生
select * from students where id in (3, 6, 9);        -- 查询id是3，6，9的学生

3. 排序和分组

• 排序(order by)

select * from students where gender = 1 and is_deleted = 0 order by id desc;
-- 将没有被删除的男生按降序排序
select * from students where name like '张%' and gender = 1 order by id;
-- 将姓张的男生按升序排序

• 分组(group by ... having)

-- 分组是对数据表某一个字段执行一次排序操作，然后总结出分类数量。
select gender,count(*) from students group by gender;    -- 统计男女生数量
select gender, any_value(name) from students group by gender;  -- 获取分组后分类的默认值，用any_value,但不能同时获取分组数量
select gender,count(*) from sudents group by gender having gender =2; -- 统计班上女生的数量(使用having做判断)
-- where和group by区别：where是对from后面指定的表进行数据筛选,属于对原始数据的筛选;having是对group by的结果进行筛选

4. 聚合函数和分页

• 聚合函数(max,min,avg,count,sum)

select max(height) from students where gender = 1;    -- 统计身高最高的男生
select avg(height) from students;        -- 统计平均身高(取的是非空值)

• 分页(limit start,end)

-- pageNum, pageSize
select * from students limit 0,5;    -- 查询前5条数据
select * from students limit pageSize*(pageNum-1),pageSize; -- 分页公式

5. 连接查询(inner A join B on a = b)

• 内连接查询

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.cls_id;
-- 查询班上学生所在的班级
select A.name,B.name from A inner join B on A.cls_id = B.cls_id;   -- 连接查询的公式
-- 内连接省略写法(inner join 可以写成join 或者cross join)

• 外连接查询(left join和right join)

select * from students as s left join classes as c on s.cls_id = c.cls_id; -- 左连接，查出来的数据以左边的表为主表，右边的为附表
select * from students as s right join classes as c on s.cls_id = c.cls_id; -- 右连接，以右边的表为主表，所有内容都显示，左边的显示一部分，空余的部分用null填充。
-- 外连接省略写法(left join 可以写成 outer join)

6. 自关联查询

• 常见的省市关联

create table areas(
aid int primary key,
atitle varchar(20),
pid int
);     -- 创建areas表
select p.atitle,s.atitle from areas as p inner join areas as s on s.pid = p.aid where pid='湖北省';    
-- 通过内联查询，找出父子表之间的关系(查询湖北省的所有市)

• 类似的也常见于二级菜单，树形结构等

7. 子查询(select .... select ....)

• 分类：标量，行级，列级，表级

--  查询班上大于平均身高的学生
-- 充当数据源
select * from (select * from students) as t;   -- 必须要有别名
-- 充当条件
select * from students where height > (select avg(height) from students); --子查询必须要有括号
-- 列级子查询的应用
select classes.name from classes where cls_id in (select cls_id from students);     -- 查询有学生的班级
-- 行级子查询应用(构建行元素)
-- 关键字（in 范围，any/some 任意一个，all 所有）
select * from students where (age, height) = (select max(age),max(height) from students);     -- 查询班级年龄最大，身高最高的学生(如果最大不在一个人身上，返回空)
-- 表级子查询一般效率较低，并且一般都可以用连接查询实现。

转载于:https://my.oschina.net/chinahufei/blog/1583801