数据库之数据查询

DQL复杂查询

简单查询

where子句

作用:在删除、修改及查询的语句后都可以添加where子句（条件），用于筛选满足特定的添加的数据进行删除、修改和查询操作。
如:
    delete from 表名 where 条件;
    update 表名 set ... where 条件;
    select ... from 表名 where 条件;
计算列

运算符

关系运算符

= 等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between and 区间查询
	between v1 and v2;在v1和v2之间

逻辑运算符

and 并且:多个条件同时满足
or 或者:多个条件至少满足一个
not 取反

Null查询

-- 是null
is null
	select * from student where 
s_phone is null;
-- 不是null
is not null
	select * from student where s_phone is not null;

模糊查询like

like子句

在where子句的条件中，我们可以使用like关键字来实现模糊查询
语法：
select * from 表名 where 列名 like 'reg';
在like关键字后的reg表达式中
    % 表示任意多个字符
    _ 表示任意单个字符

like子句举例

# 查询学生姓名包含字母o的学生信息
select * from stus where stu_name like '%o%';

# 查询学生姓名第一个字为`张`的学生信息
select * from stus where stu_name like '张%';

# 查询学生姓名最后一个字母为o的学生信息
select * from stus where stu_name like '%o';

# 查询学生姓名中第二个字母为o的学生信息
select * from stus where stu_name like '_o%';

计算列

对从数据表中查询的记录的列进行一定的运算之后显示出来
+,加
-,减
*,乘
/,乘
%,取余
	举例:
        # 出生年份 = 当前年份 - 年龄
        select s_name,2021-s_age from student;

别名

作用:我们可以为查询结果的列名 去一个语义性更强的别名 (如下案例中 AS 关键字也可以省略)
	举例:
        # 出生年份 = 当前年份 - 年龄
        select s_name AS '姓名',2022-s_age AS '出生年份' from student;

distinct : 消除重复行

作用:在查询结果中消除重复行
	也就是将结果中的相同数据消除

eg:
	-- 查询student中所有年龄
	select s_age from student;
	--消除掉相同年龄
	select distinct s_age from student;

分支结构查询

语法:
    CASE
        WHEN 条件1 THEN 结果1
        WHEN 条件2 THEN 结果2
        WHEN 条件3 THEN 结果3
        ELSE 结果
    END
注意：通过使用CASE END进行条件判断，每条数据对应生成一个值。

	

分支结构查询举例

SELECT employee_id , first_name , salary ,department_id ,
    CASE
        WHEN salary>=10000 THEN 'A'
        WHEN salary>=8000 AND salary<10000 THEN 'B'
        WHEN salary>=6000 AND salary<8000 THEN 'C'
        WHEN salary>=4000 AND salary<6000 THEN 'D'
        ELSE 'E'
    END as "LEVEL"
FROM t_employees;

order by : 排序

将查询到的满足条件的记录按照指定的列的值升序/降序排列
语法：
	select * from 表名 where 条件 order by 列名 asc|desc;
        asc:升序(默认)
        desc:降序;
# 单字段排序        
举例:
	将所有男生按照成绩由高到低显示
  	select * from student where s_sex = '男' order by s_score desc;
  	
# 多字段排序 ： 先满足第一个排序规则，当第一个排序的列的值相同时再按照第二个列的规则排序 
举例:
	将所有男生按照成绩由高到低显示,成绩相同时,按照年龄由小到大显示
	select * from student where s_sex = '男' order by s_score desc,s_age asc;

聚合函数

SQL中提供了一些可以对查询的记录的列进行计算的函数
count:数量
max:最大值
min:最小值
sum:求和
avg:求平均数
count() 统计函数，统计满足条件的指定字段值的个数（记录数）
    1.count:统计个数
        select count(s_id) from student;
    2,max获取最大值
        select max(s_age) from student;
    3,max获取最大值
        select min(s_age) from student;
    4,求总和
        select sum(s_score) from student;
    5.求平均数
        select avg(s_score) from student;

日期函数与字符串函数

日期函数

当我们向日期类型的列添加数据时，可以通过字符串类型赋值（字符串格式必须为：yyyy-MM-dd- hh:mm:ss)我们可以通过以下函数获取当前系统时间：
now():获取当前时间
	select now();
sysdate():获取当前时间
	select sysdate();
1,通过字符串类型给日期类型的列赋值
	insert into student (s_enterence) values('2020-09-12 12:13:34');

字符串函数

通过SQL指令对字符串进行处理
函数:
concat(字段名或字符串1,字段名或字符串2,...):拼接
upper(字段名) 将字段的值转换成大写
lower(字段名) 将指定列的值转换成小写
substring(字段名,开始位置,长度) 从指定列中截取部分显示 start从1开始
insert 
1, concat(colnum1,colunm2,...) 拼接多列
	select concat(s_name,'-',s_score) from student;
2,upper(column) 将字段的值转换成大写
	select upper(s_name) from student;
3,lower(column) 将字段的值转换成小写
	select upper(s_name) from student;
4,substring(column,start,len) 从指定列中截取部分显示 start从1开始
	-- 显示电话号码后四位
	select stu_name,substring(s_tel,8,4) from student;

group by 分组

分组：就是将数据表中的记录按指定的列进行分组
语法:
	select 分组字段/聚合函数 
	from 表名
	[where 条件]
	group by 分组列名 [having 条件]
	[order by 排序字段]

groupby举例

--根据班级分组
 select class, count(s_id) from student group by class;
-- 按照性别分组 
select s_sex ,count(s_id) n from student group by s_sex order by n desc; 

having 条件查询

having 条件
语法
    select 分组字段/聚合函数
    from 表名
    [where 条件]
    group by 分组列名 [having 条件]
    [order by 排序字段]

limit 分页

分页:当数据表中的记录比较多的时候，如果一次性全部查询出来显示给用户，用户的可读性/体
验性就不太好，因此我们可以将这些数据分页进行展示。
语法:
	select 查询的字段
	from 表名
	where 条件
	limit param1(起步索引),param2;(查询条数)
注意:
-- param1：表示获取查询语句的结果中的第一条数据的索引（索引从0开始）
-- param2：表示获取的查询记录的条数（如果剩下的数据条数<param2，则返回剩下的所有记录）

limit分页举例

-- 查询第一页数据
select * from student  limit 0,2;
-- 查询第二页数据
select * from student  limit 2,2;
-- 查询第三页数据
select * from student  limit 4,2;

分页通用语句

-- 在一张数据表中
-- pageNum 表示要查询的页码
-- pageSize表示每页显示条数
#分页通用语句
select  * from student limit (pageNum-1)*pageSize,pageSize;

表关联关系

数据与数据之间的关系;
    1,一对一关系
        如:一人一个身份证
        1,主键直接连接
        2,在A表中添加一个字段,存储对应数据在B表中
    2,一对多关系
        如:一个学生上多门课
        方案:在多的表中,添加一个字段记录A表中中
    3,多对多关系
        如:一个学生上多门课,一门课多个学生上;
        方案:在添加一张表,记录其数据

表关联

foreign key外键约束

-- 创建方式1:
	-- 创建表时
create table 表名 (
	字段名 数据类型 [约束],
	字段名 数据类型 [约束],
    ...
    constraint 外键名称 foreign key (外键列名) references 主表 (主表列名)
);
-- 创建方式2
	-- 创建表后,创建外键
	alter table 表名 add constraint 外键名称 foreign key(外键列名) references 主表名(主表列表);
-- 删除外键
	alter table 表名 drop foreign key 外键名称;
	
	
	

外键优点:

优点:让数据产生关联
缺点:数据不能联动

级联

解决外键缺点
    -- 级联更新
    on update cascade
    -- 级联删除
    on delete cascade
    
-- 创建级联方式2
	-- 创建表后,创建外键
	alter table 表名 add constraint 外键名称 foreign key(外键列名) references 主表名(主表列表) on update cascade on delete cascade;    

连接查询

笛卡尔积

笛卡尔积：使用A中的每个记录一次关联B中每个记录，笛卡尔积的总数=A总数*B总数;
如果直接执行 select ... from 表1 inner join 表2; 会获取两种数据表中的数据集合的笛卡尔积（依次使用表1中的每一条记录去匹配表2的每条数据）
如:
	select * from classes inner join students;

inner join 内连接

-- 获取交集
使用 on 设置两张表连接查询的匹配条件，查询结果只获取两种表中匹配条件成立的数据，任何一张表在另一种表如果没有找到对应匹配则不会出现在查询结果中
语法:
	select ... from 表1 inner join 表2 on 匹配条件 [where 筛选条件];

select * from stu inner join classes on stu.c_id = classes.c_id;

left join 左连接

左连接：显示左表中的所有数据，如果在有右表中存在与左表记录满足匹配条件的数据，则进行匹配；如果右表中不存在匹配数据，则显示为null
-- 语法
select * from leftTabel LEFT JOIN rightTable ON 匹配条件 [where 条件];
如:
select * from classes left join stu on classes.c_id = stu.c_id;

right join 右连接

右连接：显示右表中的所有数据，如果在有左表中存在与右表记录满足匹配条件的数据，则进行匹配；如果左表中不存在匹配数据，则显示为null
select * from leftTabel LEFT JOIN rightTable ON 匹配条件 [where 条件];
如:
select * from classes right join stu on classes.c_id = stu.c_id;

子查询

子查询:先进行一次查询，第一次查询的结果作为第二次查询的源/条件（第二次查询是基于第一次的查询结果来进行的）.
分类:
	单行单列
	多行单列
	多行多列
在查询的基础上,将查询的值作为条件,值,或表,在进行查询.;
如:
-- 查询班级名称为'Java2110'班级中的学生信息
# 如果子查询返回的结果是一个值（单列单行），条件可以直接使用关系运算符（= !=....）
select * from students where cid = (select class_id from classes whereclass_name='Java2110');

-- 查询所有Java班级中的学生信息
# 如果子查询返回的结果是多个值（单列多行），条件使用IN / NOT IN
select * from students where cid IN (select class_id from classes where class_name LIKE 'Java%');

-- 查询cid=1的班级中性别为男的学生信息
# 先查询cid=1班级中的所有学生信息，将这些信息作为一个整体虚拟表(多行多列)
# 再基于这个虚拟表查询性别为男的学生信息（‘虚拟表’需要别名）
select * from (select * from students where cid=1) t where t.stu_gender='男'