MySQL常用语句之查询（一）

MySQL数据查询在工作中使用的是最多的，本篇专门记录数据库查询的各种招式和技巧。

• 基本查询
• 条件查询
• 查询排序
• 聚合函数
• 分组查询
• 分页查询
• 连接查询
• 子查询
• 自关联

基本查询

1. 查询所有字段（生成环境慎用）
	select * from student;


2. 查询指定字段
	select name, age from student;
	select student.name, student.age from student;


3. 使用`as`给字段设置别名	
	select name as "名字", age as "年龄" from student;


4. 使用`as`给表设置别名
	select s.name, s.age from student as s;


5. 使用`distinct`消除重复行
	select distinct name from student;

条件查询

1. 比较运算符（`>`、`>=`、`<`、`<=`、 `= `、`!=`）

	-- 查询大于18岁的信息
	select * from student where age > 18;
	
	-- 查询大于等于18岁的信息
	select * from student where age >= 18;
	
	-- 查询 id 小于50的信息
	select * from student where id < 50;
	
	-- 查询 id 小于等于50的信息
	select * from student where id <= 50;
	
	-- 查询 id = 10 的信息
	select id, name, age from student where id = 10;
	
	-- 查询名字不等于 “张三” 的学生信息
	select id, name, age from student where name != '张三';



2. 逻辑运算符（`与:and`、`或:or`、`非:not`）

	-- 查询 18岁 到 25岁 之间的员工信息
	select * from staff where age > 18 and age < 25;
	
	-- 查询 18岁以上男员工的信息
	select * from staff where age > 18 and sex = "男";
	
	-- 查询 20岁以上 或 工资6000（包含）以上的员工信息
	select * from staff where age > 20 or salary >= 6000;
	
	-- 查询 不大于50岁 的女员工信息 "()"控制优先级
	select * from staff where not (age > 50 and sex = "女");
	
	-- 查询 年龄不大于20岁 工资大于6000的员工
	select * from staff where (not age > 20) and salary > 6000;

	

3. 模糊查询（`like`、`%`、`_`、`rlike`）

	-- 查询 名字中以 “王” 开头的信息
	select * from student where name like "王%";
	
	-- 查询 名字中以 “平” 结尾的信息
	select * from student where name like "%平";
	
	-- 查询 名字中有 “王” 的信息
	select * from student where name like "%王%";
	
	-- 查询 两个字的名字信息
	select * from student where name like "__";
	
	-- 查询 至少两个字的名字信息
	select * from student where name like "__%";

	-- 查询 以“王”开始的名字
	select name from student where name rlike "^王.*";
	
	-- 查询 以“王”开头，“平”结尾的名字
	select name from student where name rlike "^王.*平$";


 
4. 范围查询（）

`in` ：表示在一个非连续的范围内
`not in` ：不非连续的范围内
`between...and...` ：表示在一个连续的范围内
`not between...and...` ：表示在一个连续的范围内

	-- 查询 年龄为18、30的学生
	select * from student where age = 18 or age = 30;
	select * from student where age in (18,30);
	
	-- 查询 年龄不是18、30的学生
	select * from student where age not in (18,30);
	
	-- 查询 年龄是18到30之间的学生
	select * from student where age between 18 and 30;
	
	-- 查询 年龄不是18到30之间的学生
	select * from student where age not between 18 and 30;



5. 判断空值

is null: 等于空值
is not null: 不等于空值

	-- 查询 content为空的信息
	select * from heroes where content is null;
	-- select * from heroes where content = null 无结果;
	
	-- 查询 content不为空的
	select * from heroes where content is not null;
	-- select * from heroes where content != null 无结果;
	

查询排序

order by 字段
asc 升序(默认值)
desc 降序
order by 多个字段

	-- 查询 年龄在18-22之间的女学生，按照年龄升序排列
	select * from student where (age between 18 and 22) and sex = "女" order by age; 
	select * from student where (age between 18 and 22) and sex = "女" order by age desc; 

	-- 查询 年龄在16-20之间的男生，按身高降序排列，如相同，按年龄升序排列
	select * from student (age between 16 and 20) and sex = "男" order by height desc,age asc;

	-- 查询 所有学生，按年龄小到大排列，身高从高到低
	select * from student order by age, height desc;

聚合函数

count(*) -- 总数
max() 	 -- 最大值
sum()    -- 求和
avg()    -- 平均值

	-- 查询 所有学生总数
	select count(*) as "学生总数" from student;

	-- 查询 最大的年龄
	select max(age) from student;

	-- 查询 男生最高分数
	select max(score) as "最高分数" from student where sex = "男";

	-- 查询 所有人分数总和
	select sum(score) from student;

	-- 查询 男生平均分数
	select avg(score) from student where sex="男";
	select sum(score)/count(*) from student where sex="男";

分组查询

group by: 分组
group_concat():  会计算哪些行属于同一组，将属于同一组的列显示出来，配合group by使用
having：过滤分组
with rollup: 实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
coalesce: 来设置一个可以取代 NUll 的名称

	-- 按照性别分组，查询所有的性别
	select sex from student group by sex;
 
 	-- 查询每种性别各多少人
 	select sex, count(*) as '数量' from student group by sex;

	-- 查询每组中的最高分数、平均分数
	select sex, max(socre) from student group by sex;
	select sex, avg(socre) from student group by sex;

	-- 查询每组中的包含分数信息
	select sex, GROUP_CONCAT(socre) from student group by sex;

	-- 计算男生的人数
	select sex, count(*) from student where sex="男" group by sex;

	-- 计算男生的人数,并查看男生名字
	select sex, count(*), group_concat(name) from student where sex="男" group by sex;
	
	-- 计算男生的人数,并查看男生名字,以及每个人的分数: 名字__90
	select sex, count(*), group_concat(name, score) from student where sex="男" group by sex;
	select sex, count(*), group_concat(name,'__',score) from student where sex="男" group by sex;

	-- 查询人数多于2的性别
	select sex, group_concat(name) as '名字',count(*) from student group by sex having count(*) > 2;
	
	-- 分别计算男、女平均分，再计算总人数的平均分	
	select sex, avg(score) from student group by sex with rollup;
	-- 上面的结果总平均数 KEY值是：NULL，使用coalesce 替换 'NULL' 为 '总平均数'
	select coalesce(sex, '总平均数'), avg(score) from student group by sex with rollup;

分页查询

• limit start(起始), count(个数)

-- 限制查询 10个学生
select * from student limit 10;

-- 查询前 20个学生
select * from student limit 0, 20;

-- 查询所有男生，按分数降序排列，只显示前 10名
select * from student where sex = '男' order by score desc limit 10;

连接查询

• inner join ... on 内连接：取多个表的交集
• left join ... on 左连接：取左表所有记录，即使右表没有对应匹配的记录
• right join ... on右连接：取右表所有记录，即使左表没有对应匹配的记录

-- 查询 表1中与表2中名字相同名字的记录（ inner 可以省略）
select a.name, a.age, a.sex from table1 a join table2 b on a.name = b.name;

-- 查询 表1中所有的记录
select a.name, a.age, a.sex from table1 a left join table2 b on a.name = b.name;

-- 查询 表2中所有的记录
select a.name, a.age, a.sex from table1 a right join table2 b on a.name = b.name;

子查询

• slelect 内嵌 select

-- 查询最高分的学生的信息
select * from student where score = (select max(score) from student);

自关联

• 一个表通过更改别名，当做两个表使用

select * from 表1 as 表A inner join 表1 as 表B on 表A.xxx=表B.yyy having 条件;

THE END ！