数据库--查询语句

查询语句

mysql中要学习的知识：多表关系，查询语句，索引

添加数据补充

将一个查询结果插入到另一张表中

create table student(name char(10), gender int);
insert into student values('nalituo', 1);
insert into student values('sasigi', 0);

create table student_man(name char(10), gender int);
# 快速将student表中性别为1的记录插入student_man表中
insert into student_man select * from student where gender = 1;

所有select关键字

select [distinct] * from table_name
	where
	group by
	having
	order by
	limit a,b


必须存在的 select
* 可以换成任意一个或者多个字段，可以重复，但是*必须写最前面
# 注意：关键字的顺序是固定的不能随意变化
# 注意：执行顺序distinct函数在having后面，其余从select到limit都和定义顺序相同

简单查询

*表示通配符，显示所有字段
可以指定任意个字段名
可以对字段的数据进行四则运算
聚合函数，如下

取别名

当字段名太长获取时不容易理解时，可以使用as来取别名，也可以不写as，直接空格加别名

举例（下面查询的都是在这张表的基础上）：where 条件

# 数据创建
create table stu(
	id int primary key auto_increment,
	name char(10),
    math float,
    english float
    );
insert into stu values(null,"赵云",90,30);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"大乔",10,70);
insert into stu values(null,"李清照",100,100);
insert into stu values(null,"铁拐李",20,55);
insert into stu values(null,"小李子",20,55);

# 查看所有数据
select * from stu;
# 查看英语成绩
select english from stu;
# 查看每个人的总分
select english+math as 总分 from stu;
# 为每个人的数学都减10分
select math-10 数学 from stu;
# 不需要加引号，包括中文

1.where

select * from table_name
where 后接
1.比较运算符  > < >= <= = !=
2.成员运算符  in   not in  后面接一个set
3.逻辑运算符  and or not
4.模糊查询	like 
    % 表示0-任意个数的任意字符
    _ 表示1个任意字符
    %李%	表示查询所有带有李字的记录
5.between and 两者之间 两者都能取到值
6.is null 是否为空
7.exists 子查询语句

# 注意：int类型，查询的时候加上''也可以查到

举例：

# 查询姓大的，数学小于80分，并且英语大于20分的人的数学成绩
select name,math from stu where name like '大%' and math < 80 and english > 20;

# 查询英语及格的人的平均分
select name, (english+math)/2 as '平均分' from stu where english >= 60;

2.distinct 去除重复记录

# 仅在查询结果中所有字段全部相同时，才算重复的记录
select distinct * from stu;

# 名字相同就重复
select distinct name from stu;

3.group by

分组，即将一个整体按照某个特征或依据来分为不同的部分

为什么要分组，分组是为了统计，例如统计男性有几个，女性有几个

数据准备：

create table emp(
	id int primary key auto_increment,
	name char(10),
	sex char(10),
	dept char(10),
	job char(10),
	salary float
	);
insert into emp values
(1,"刘备","男","市场","总监",5800),
(2,"张飞","男","市场","员工",3000),
(3,"关羽","男","市场","员工",4000),
(4,"孙权","男","行政","总监",6000),
(5,"周瑜","男","行政","员工",5000),
(6,"小乔","女","行政","员工",4000),
(7,"曹操","男","财务","总监",10000),
(8,"司马懿","男","财务","员工",6000);

# 语法：
select xxx from table_name group by 字段名;


# 需求：按照性别进行分组
select * from emp group by sex;
# 这样做只会显示该分组的第一行数据，这是因为emp有很多行，而分组聚合后的sex只有两行，两者的行数不匹配，逻辑是错误的。


# mysql 5.6下，查询的结果是name仅显示为该分组的第一个
# mysql 5.7以上则会直接报错，5.6也可以手动开启这个功能
# 把 ONLY_FULL_GROUP_BY 添加到服务器的配置文件中

统计函数

也称之为聚合函数
将一堆数据经过计算得出一个结果
sum avg count max min
可以用在，字段的位置，或是分组的后面

求和：sum(字段名)
平均数  avg(字段名)
最大值  max(字段名)
最小值  min(字段名)
个数    count(字段名)    # 字段名称可以使用*代替,因为如果只查一个字段，那么当该字段为空时，是不计数的
 
# 例如：查找所有人的工资总和
select sum(salary) from emp;
  
# 例如：查询所有人的平均工资
select avg(salary) from emp;

# 例如：查询工资最高的人的姓名
select name,max(salary) from emp;
# 不是想要的效果，因为默认显示的是第一个name，因为name有很多行，而max(salary)只有一行

select name from emp where salary = max(salary);
# 报错，原因：伪代码
def where(条件):
    for line in file:
        if salary = max(salary) 
# 分析  where 读取满足条件的一行  ,max()先要拿到所有数据 才能求最大值。
# 但这里由于读取没有完成所有无法求出最大值
# 结论  where 后面不能使用聚合函数

分组+统计

# 需求 查询每个性别有几个人
select sex, count(*) from emp group by sex;

# 需求：查询每个性别有几个人，并且显示名字
select name,sex,count(*) from emp group by sex;
# 报错

# 我们可以用group_concat 将分组之外的字段 做一个拼接 ,但是这是没有意义
# 如果要查询某个性别下的所有信息，直接使用where即可
# 结论，只有出现在了group by 后面得字段才能出现在select的后面

4.having

用于过滤，但是与where不同的是，having使用在分组之后

案例：

# 求出平均工资大于5000的部门信息
select dept,avg(salary) from emp group by dept having avg(salary) > 5000;

# 查询部门人数少于3的部门名称 人员名称 人员个数
select dept, group_concat(name), count(*) from emp group by dept having count(*) < 3;

5.order by

根据某个字段排序

# 语法
select * from table_name order by 字段名称;
# 默认升序，使用desc降序，asc升序

# 改为降序
select * from table_name order by 字段名称 desc;

# 多字段，先按照第一个排序，第一个相同，再按照第二个
select * from table_name order by 字段名称1 desc, 字段名称2 asc;

# 案例：工资升序，id降序
select * from emp order by salary asc, id desc；

6.limit

用于限制要显示的记录数量

# 语法1
select * from table_name limit 个数;
# 语法2
select * from table_name limit 起始位置,个数;
# 语法3
select * from table_name limit 个数 offset 过滤个数;

# 案例：查询前三条
select * from emp limit 3;

# 从第3条开始，查询3条
select * from emp limit 2, 3;

# 从第4条开始，查询2条
select * from emp limit 2 offset 3;

# 注意：起始位置，从0开始
# 经典的使用场景：分页显示
1.每一页显示的条数 a = 3
2.明确当前页数 b = 2
3.计算起始位置 c = (b-1) * a

select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;

# django中会提供一个分页功能，但是它是先查询所有数据，丢到列表中，再取出数据，这样如果数据量太大可能会有问题

子查询

将一条语句的结果作为另一条语句的条件或者是数据来源

当我们一次性查不到想要的数据时就需要使用子查询

in 关键字子查询

当内层查询(括号内的)结果会有多个结果时，不能使用=必须使用in，另外in查询只能包含一个字段，也就是一列数据

需求：找出平均年龄大于25的部门名称

准备数据：

drop table if exists emp;
create table emp (id int primary key ,name char(10),sex char,age int,dept_id int,job char(10),salary double);

insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张飞","男",24,1,"员工",3000),
(3,"关羽","男",30,1,"员工",4000),
(4,"孙权","男",25,2,"总监",6000),
(5,"周瑜","男",22,2,"员工",5000),
(6,"小乔","女",31,2,"员工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司马懿","男",24,3,"员工",6000);

create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"行政"),(3,"财务");

# 1. 查询出平均年龄大于25的部门编号
select dept_id from emp group by dept_id having avg(age) > 25;

#2. 根据编号查询部门的名称
select * from dept where id in (1, 2);

# 子查询合并
select * from dept where id in (select  dept_id from emp  group by  dept_id  having avg(age) > 25);

子查询的思路:
1.要分析 查到最终的数据 到底有哪些步骤 
2.根据步骤写出对应的sql语句
3.把上一个步骤的sql语句丢到下一个sql语句中作为条件 

# 注意，如果用子查询，那么内部的语句查询的字段必须指定，而不能为*，否则会报错，因为外部得到的结果会乱掉

exist关键字子查询

当内层查询有结果时，外层才会执行

# 案例
select * from dept where exists (select * from dept where id = 1);

# 由于内层查询产生了结果，所以执行了外层查询dept的所有数据

多表查询

笛卡尔积查询

# 案例：
select * from table1,table2,...

# 笛卡尔积查询在数据关联关系错误时，结果会出现大量的错误数据，
# 这时我们需要添加过滤条件，从表外键值等于主表的主键值
select * from emp, dept where dept_id=dept.id;


# 并且会产生重复的字段信息，例如员工里的部门编号和部门表里的id字段，所以在select 后指出需要查询的字段名称
# 案例：
select dept.name as 部门, dept.id 部门编号, emp.name 姓名, emp.id 员工编号, sex from emp, dept where dept.id=dept_id;

内连接查询语法

本质上就是笛卡尔积查询

# 语法
select * from table1 inner join table2;

# 案例
select * from emp inner join dept where dept_id = dept.id
# 其中inner可以省略，emp和dept的顺序也可以调换
# 这里的where可以用on替代

外连接查询

包括没有匹配关系的数据，不常用，因为一般都会有外键约束对应关系

举例：

# 不存在外键关联的两张表
# 存在一些不正确的部门id
drop table if exists emp;
create table emp(id int primary key, name char(10), sex char(10), dept_id int );
insert into emp values(1,"大黄","m",1);
insert into emp values(2,"老王","m",2);
insert into emp values(3,"老李","w",30);

drop table if exists dept;
create table dept(id int primary key, name char(10));

insert into dept values(1, '市场');
insert into dept values(2, '财务');
insert into dept values(3, '行政');

左外连接查询

左边的表无论是否能够匹配都要完整显示，右边的仅展示匹配上的记录

# 需求：要查询所有员工以及其所属的部门信息
select * from emp left join dept on dept_id = dept.id;
# 注意，在外连接查询中不能够使用where关键字，必须使用on关键字专门来做表的对应关系

右外连接查询

右边的表无论是否能够匹配都要完整显示，左边的仅展示匹配上的记录

# 需求：要查询所有部门以及其所属的员工信息
select * from emp right join dept on dept.id = dept_id;

# 注意，哪个表放前面就先展示哪个表

全外连接查询

无论是否匹配成功，两边表的数据都要全部显示

# 需求：查询所有员工与所有部门的对应关系
select * from emp full join dept on dept.id = dept_id;
# 虽然不会报错，但也不是我们想要的结果

# mysql不支持全外连接

# 我们可以将左外连接查询的结果，和右外连接查询的结果，做一个合并

select * from emp left join dept on dept.id = dept_id
union
select * from emp right join dept on dept.id = dept_id;


# union的用法
select * from emp
union all
select * from emp;

# union将自动去除重复的记录
# union all 不去除重复

select sex,name from emp
union
select * from dept;
# 注意 union 必须保证两个查询结果列数相同，一般用在多个结果结构完全一致时

总结：外连接查询，查到的是没有对应关系的记录，但是这样的数据原本就是有问题的，所以最常用的还是内连接查询

内连接表示只显示匹配成功的记录

外连接表示没有匹配成功的也要显示

多表查询案例：

create table stu(id int primary key auto_increment, name char(10));

create table tea(id int primary key auto_increment, name char(10));

create table tsr(
    id int primary key auto_increment, 
    t_id int, 
    s_id int, 
    foreign key(t_id) references tea(id), 
    foreign key(s_id) references stu(id)
	);

insert into stu values(null, '张三'), (null, '李四');
insert into tea values(null, 'nick'), (null, 'jerry');
insert into tsr values(null,1,1),(null,1,2),(null,2,2);

# nick老师教过哪些人？
select tea.name, stu.name from tea join tsr join stu on tea.id = t_id and stu.id = s_id where tea.name = 'nick';

注意:

在查询数据中,应该将上述知识合并使用,特别是子查询和内连接查询,如果面临多表联合查询,应该先理清楚步骤,一步一步实现,可以将一部分的查询结果搭建成一个临时表,再联合其他表进行查询.

例如:

# 查询平均成绩大于80分的同学的姓名以及平均成绩
# 1. 根据学生id分组,查出所有平均成绩大于80的学生id
select student_id, avg(num) from score group by student_id having avg(num) > 80;
#2. 将查询结果作为一个临时表,如果要使用临时表的数据,就可以写上as a 然后后面就用 a.字段 去使用
(select student_id, avg(num) from score group by student_id having avg(num) > 80) as a ;
#3. 将临时表数据与学生表连接
select * from student join (select student_id, avg(num) from score group by student_id having avg(num) > 80) as a ;
#4. 进行一些筛选
select * from student join (select student_id, avg(num) from score group by student_id having avg(num) > 80) as a on sid = a.student_id;