MySql-05-DQL数据查询语言、单表查询、多表查询

一.DQL数据查询语言

基本概念：DQL用于对数据库关系表中的数据进行查询（读取），其支持多种查询方式。

数据查询语言关键字：select

查询语句基本格式:select <属性名> from <[数据库名.]表名>； 

在使用查询语句之前建立一个简单数据表：

## DQL数据查询语言

# 创建数据库
create database db_2;

use db_2;

# 创建学生、班级、教师的数据表
-- 教师表
create table teacher(
t_id int primary key auto_increment,
t_name varchar(20) not null,
t_sex varchar(4) check(t_sex = '男' || t_sex = '女'),
t_age int check(t_age >= 20 && t_age <= 60)
)auto_increment = 100;

insert into teacher(t_name,t_sex,t_age) values("张三","男",28),("李四","女",20),
("王五","女",23),("布鲁斯","男",27),("龙卷风","男",30),("桃子","女",21),
    ("robert","男",40),("可达鸭","男",28),("荔枝","女",22),("教主","男",29);

-- 班级表
create table class(
c_id int primary key auto_increment,
c_name varchar(30) not null,
c_tid int, -- 班级的任课老师/班主任老师
c_stunum int default 0, -- 班级学生人数
constraint for_CT foreign key(c_tid) references teacher(t_id)
)auto_increment = 100;

insert into class(c_name,c_tid) values("C语言",101),
("C++",103),("数据结构",106),("数据库",100),("Win32",NULL),
    ("游戏开发",107),("QT",NULL),("Linux服务器",NULL);

-- 学生表
create table student(
s_id int primary key auto_increment,
s_name varchar(20) not null,
s_cid int, -- 学生属于哪个班级
s_sex varchar(4) check(s_sex = '男'||s_sex = '女'),
s_age int check(s_age >= 18 && s_age <= 40),
constraint for_SC foreign key(s_cid) references class(c_id)
)auto_increment = 1000;

insert into student(s_name,s_cid,s_sex,s_age) values("陈小皮",100,'男',20),
("吉利服",103,'男',20),("张益达",100,'男',20),("萌萌",103,'女',20),
("晓东",101,'男',20),("小蔡",null,'男',21),("小玉",102,'女',21),
("阿雪",103,'女',21),("如花",null,'女',25),("似玉",null,'女',24),
("小桂子",null,'男',22),("小强子",101,'男',23),("小吴",102,'男',24),
    ("小花",100,'女',23),("小丸子",null,'女',20),("小红",101,'女',21);

查询语言：

1.查询所有字段：select * from <表名>;   

2.查询指定字段:select <属性名1,属性名2······> from <表名>;

# 一、数据查询语言的基本格式select
-- 1、查询所有班级的所有信息
select * from db_2.class;
-- '*'代表所有字段（属性）

-- 2、查询所有教师的指定数据
select t_id,t_name from teacher;
-- 根据对应的属性名来筛选列

二.单表查询

1.条件查询：

基本格式：select <属性名> from <[数据库名.]表名> where 查询条件;

条件查询的基本运算符：

①、条件运算符：>、<、=、!=(<>)、>=、<=（注意不存在'==','!='与'<>'作用相同）

②、逻辑运算符：&&（and\AND）、||(or\OR)，用于连接两个条件

# 二、条件查询where

# 1、关系运算符（>、<、>=、<=、=、!=、<>）
-- 查询学号大于1003号的学生信息
select * from student where s_id > 1003;
-- 根据对应的查询条件来筛选行

-- 查询学号不等于1009号的学生信息
select * from student where s_id <> 1009;
-- <>是不等于符号（相当于!=）

# 2、逻辑运算符（&&(and/AND)、||(or/OR)）
-- 查询学号大于等于1003号并且小于等于1013号的学生信息
select * from student where s_id >= 1003 && s_id <= 1013;

-- 查询学号小于1003号并且大于1013号的学生信息
select * from student where s_id < 1003 || s_id > 1013;

2.范围查询：

①.在······范围内查找：

between······and······

    在······范围外查找：

not between······and······

②.在集合范围内查找：

in(·······)

    在集合范围外查找：

not in(·····)

#由于in的内部处理没有where好，一般很少用in进行条件查询

# 3、范围查询（适用于连续范围内：数字、字母）
-- between……and……：在……之间
-- 查询学号大于等于1003号并且小于等于1013号的学生信息
select * from student where s_id between 1003 and 1013;

-- not between……and……：不在……之间
-- 查询学号大于等于1003号并且小于等于1013号的学生信息
select * from student where s_id not between 1003 and 1013;

# 4、集合范围查询（适用于不连续范围，自定义范围）
-- in：在集合范围内查询
select * from student where s_age in(18,20,30,23,24,26);
select * from student where s_name in("陈小皮","张三","张益达","李四","王五");

-- not in：在集合范围外查询
select * from student where s_age not in(18,20,30,23,24,26);

3.模糊匹配

关键字：like

可用于根据关键字搜索。

模糊匹配的通配符：

‘%’：匹配任意0个字符、1个或多个字符

‘-’：匹配任意1个字符

# 5、字符串模糊匹配like（只适用于字符串或者文本类型）
-- like：像……一样，类似于……
-- 通配符'%'：匹配任意多个字符
-- 查询姓名以‘小’字开头以任意多个字符结尾的学生信息
select * from student where s_name like "小%";

-- 通配符'_'：匹配任意一个字符
-- 查询姓名以‘小’字开头以任意一个字符结尾的学生信息
select * from student where s_name like "小_";

-- 通配符可以组合使用
-- 查询姓名以‘小’字开头以任意两个字符结尾的学生信息
select * from student where s_name like "小__"; -- 有两个下划线

-- 查询姓名中包含‘小’字的学 生信息
select * from student where s_name like "%小%";

-- 查询姓名以‘小’字开头并且名字至少要有3个字符的学生信息
select * from student where s_name like "小__%"; -- 有两个下划线

4.分页查询

格式：limit [偏移量n,] 记录条数m

分页查询可以有两个参数，也可以只有一个参数，第一个参数为偏移量n，第二个参数为记录条数m，意思是查询从第n+1条记录开始的后m条数据，可以不给的偏移量，也就是只有一个参数时偏移量n默认为0.

#思考：查询语句查询出的结果形成的表格是真实存在的数据表吗？

# 6、分页查询limit
-- 查询学生表中前5条数据
select * from student limit 5;
-- 相当于是：select * from student limit 0,5;

-- 查询学生表中第3条数据开始的后6条数据
select * from student limit 2,6;

5.查询结果排序

有时候我们需要对查询结果进行升序或降序，这就需要用到查询结果的排序了。

关键字：order by (默认升序)，

一般格式：select <属性名> from <表名> order by <属性>；

如：

select s_id,s_name from students order by s_id;

# order by 属性名 desc;(desc 降序排列)

select s_id,s_name from students order by s_id desc;

# 7、查询结果排序order by（默认升序排列）
-- 查询学生信息，按照年龄从小到大排序
select * from student order by s_age;

-- 查询学生信息，按照年龄从大到小排序
select * from student order by s_age desc; -- desc 降序排列

三.多表查询

在现实生活中的查询往往需要查找多个表中的数据，要求所查询的数据表存在联系，这就涉及到多表查询了。

1.交叉连接

基本格式：

select <[数据库1.]表1.属性1······[数据表n.]表n.属性n> from <[数据库1.]表1.属性1······[数据表n.]表n.属性n>

这里有涉及到一个笛卡尔积的概念，也就是多个表中所有数据都相互匹配一次

# 三、DQL多表查询
# 1、交叉连接
# ① 交叉连接的基本格式
-- 查询学生所在班级的班级名称
select student.s_id,student.s_name,class.c_name from student,class;

# ② 笛卡尔积
-- 查询学生所在班级的班级名称
select student.*,class.* from student,class;
/*
笛卡尔积：连接查询的多个数据表中的所有数据都相互匹配一次，形成一个包含所有可能情况的临时数据表
-- 笛卡尔积中会产生大量的无效数据，非常影响查询性能，会降低查询效率
-- 要避免产生笛卡尔积
*/
-- 交叉连接会产生笛卡尔积

# ③ 带条件的交叉连接
-- 查询学生所在班级的班级名称
select student.*,class.* from student,class
where student.s_cid = class.c_id; -- 等值连接：外键值 = 主键值

2.内连接

内连接先判断连接条件，根据表中匹配的字段，列出与连接条件匹配的数据，也就是说只有相匹配的结果才能查询出来。

关键字：inner join

基本格式：

select <[数据库1.]表1.属性1······[数据表n.]表n.属性n> from [数据库1.]表1 inner join [数据库2.]表2 on 条件表达式1 inner join ··· inner join [数据库n.]表n on 条件表达式n;

# 2、内连接inner join
# ① 内连接的基本格式
-- 查询学生所在班级的班级名称
select student.*,class.* from student inner join class
on student.s_cid = class.c_id; -- 等值连接：外键值 = 主键值
-- 内连接之后的连接条件关键字用on，而不是where

# ② 不带条件的内连接
-- 查询学生所在班级的班级名称
select student.*,class.* from student inner join class;
-- 相同条件下，内连接和交叉连接的查询效果是一样的

# ③ 内连接和交叉连接的区别
-- 内连接不会产生笛卡尔积
    -- 内连接的查询效率比交叉连接要高很多

3.左外连接

外连接是对内连接进行筛选，又分为左外连接、右外连接和全外连接。

左外连接是以左表为基准，显示左表查询字段的所有记录，右表只显示与左表匹配的记录，没有匹配的用null代替。

关键字：left join

基本格式：select [数据库1.]表1.属性1,·······,select [数据库n.]表n.属性n from

[数据库1.]表1 left join [数据库2.]表2 on 条件表达式1 left join·····;

4.右外连接

与左连接刚好相反，以右表为基准，显示右表查询字段的所有记录，左表只显示和右表匹配的记录，没有匹配的用null代替。

关键字：right join

基本格式：select [数据库1.]表1.属性1,·······,select [数据库n.]表n.属性n from

[数据库1.]表1 right join [数据库2.]表2 on 条件表达式1 left join·····;

# 左右外连接可以相互转换，把两个表的顺序倒过来就行，没有特别的限制

# 3、外连接
-- 外连接是对内连接查询结果的补全（将查询结果完全展示，补全空数据）
-- 外连接会显示内连接中没有匹配的数据（没有匹配的数据用null代替）

# ① 左外连接left join（左连接）
-- 查询学生所在班级的班级名称
select student.*,class.* from student left join class
on student.s_cid = class.c_id; -- 等值连接：外键值 = 主键值

# ② 右外连接right join（右连接）
-- 查询学生所在班级的班级名称
select student.*,class.* from student right join class
on student.s_cid = class.c_id; -- 等值连接：外键值 = 主键值

-- 左右连接可以相互转换
-- 查询学生所在班级的班级名称
select student.*,class.* from class right join student
on student.s_cid = class.c_id; -- 等值连接：外键值 = 主键值

5.多表连接查询

DQL不仅可以连接查询2个表，也可以连接查询多个表中的数据（一般不超过4个表）

基本格式：select 表1.属性1，表2.属性2······ from 表1 连接(inner/left/right join) 表2 on 连接条件1

连接(inner/left/right join) 表3 on 连接条件2···；

（连接两个表之后写上连接条件，再连接另一个表）

#多表连接查询时应注意连接表之间的外键关系和连接条件，只有表中属性相互有关系的表才能进行连接。

 

# 4、多表连接查询
-- 查询学生所在班级的班级名称，以及对应班级的任课老师
select student.s_id,student.s_name,class.c_name,teacher.t_name 
from student left join class on student.s_cid = class.c_id
    left join teacher on class.c_tid = teacher.t_id;
-- 有外键联系的数据表连接查询才有意义
-- 多表连接查询最好不要涉及到4个以上表的连接（不包含4个表），否则查询效率非常低，是数据库设计的问题
-- 表的连接查询非常消耗查询性能

6.别名的使用

关键字：as

格式：select <属性名> as <属性名> from <表名> as <别名>;

# 5、别名的使用as
-- 在查询语句中可以使用关键字as给表名和属性名取别名
-- 基本格式：属性名 as 别名，表名 as 别名

-- 查询学生所在班级的班级名称，以及对应班级的任课老师
select S.s_id as 学号,S.s_name as 学生姓名,
C.c_name as 班级名称,T.t_name as 任课老师
from student as S left join class as C on S.s_cid = C.c_id
    left join teacher as T on C.c_tid = T.t_id;
-- 给数据表取别名可以减少表名的解析时间，从而提高处理效率

-- 取别名的关键字as可以省略
-- 查询学生所在班级的班级名称，以及对应班级的任课老师
select S.s_id 学号,S.s_name 学生姓名,
C.c_name 班级名称,T.t_name 任课老师
from student S left join class C on S.s_cid = C.c_id
    left join teacher T on C.c_tid = T.t_id;