20220816 第一组 于芮 数据库查询(第三十二天)()

# DQL数据库查询语言

重点,DQL是我们每天都要接触编写最多也是最难的SQL,该语言用来查询记录,不会修改数据库和表结构。

## 构建数据库

创建一张student表:

“`sqlDROP TABLE IF EXISTS student;CREATE TABLE student ( id INT(10) PRIMARY KEY, `name` VARCHAR(10), age INT(10) NOT NULL, gender VARCHAR(2));“`

构建一张course表:

“`sqlDROP TABLE IF EXISTS course;CREATE TABLE course( id INT(10) PRIMARY KEY, `name` VARCHAR(10), t_id INT(10));“`

构建一张teacher表:

“`sqlDROP TABLE IF EXISTS teacher;CREATE TABLE teacher( id INT(10) PRIMARY KEY, `name` VARCHAR(10));“`

构建一个score表:

“`sqlDROP TABLE IF EXISTS score;CREATE TABLE scores( s_id INT(10), score INT(10), c_id INT(10), PRIMARY KEY(s_id,c_id));“`

表格填充数据:

“`sqlinsert into student (id,name,age,gender)VALUES(1,’小明’,19,’男’),(2,’小红’,19,’男’),(3,’小刚’,24,’男’),(4,’小龙’,11,’男’),(5,’小丽’,18,’男’),(6,’小军’,18,’女’),(7,’小航’,16,’男’),(8,’小亮’,23,’男’),(9,’小杰’,22,’女’),(10,’小虎’,21,’男’);

insert into course (id,name,t_id)VALUES(1,’数学’,1),(2,’语文’,2),(3,’c++’,3),(4,’java’,4),(5,’php’,null);

insert into teacher (id,name)VALUES(1,’Tom’),(2,’Jerry’),(3,’Tony’),(4,’Jack’),(5,’Rose’);

insert into scores (s_id,score,c_id)VALUES(1,80,1);insert into scores (s_id,score,c_id)VALUES(1,56,2);insert into scores (s_id,score,c_id)VALUES(1,95,3);insert into scores (s_id,score,c_id)VALUES(1,30,4);insert into scores (s_id,score,c_id)VALUES(1,76,5);

insert into scores (s_id,score,c_id)VALUES(2,35,1);insert into scores (s_id,score,c_id)VALUES(2,86,2);insert into scores (s_id,score,c_id)VALUES(2,45,3);insert into scores (s_id,score,c_id)VALUES(2,94,4);insert into scores (s_id,score,c_id)VALUES(2,79,5);

insert into scores (s_id,score,c_id)VALUES(3,65,2);insert into scores (s_id,score,c_id)VALUES(3,85,3);insert into scores (s_id,score,c_id)VALUES(3,37,4);insert into scores (s_id,score,c_id)VALUES(3,79,5);

insert into scores (s_id,score,c_id)VALUES(4,66,1);insert into scores (s_id,score,c_id)VALUES(4,39,2);insert into scores (s_id,score,c_id)VALUES(4,85,3);

insert into scores (s_id,score,c_id)VALUES(5,66,2);insert into scores (s_id,score,c_id)VALUES(5,89,3);insert into scores (s_id,score,c_id)VALUES(5,74,4);

insert into scores (s_id,score,c_id)VALUES(6,80,1);insert into scores (s_id,score,c_id)VALUES(6,56,2);insert into scores (s_id,score,c_id)VALUES(6,95,3);insert into scores (s_id,score,c_id)VALUES(6,30,4);insert into scores (s_id,score,c_id)VALUES(6,76,5);

insert into scores (s_id,score,c_id)VALUES(7,35,1);insert into scores (s_id,score,c_id)VALUES(7,86,2);insert into scores (s_id,score,c_id)VALUES(7,45,3);insert into scores (s_id,score,c_id)VALUES(7,94,4);insert into scores (s_id,score,c_id)VALUES(7,79,5);

insert into scores (s_id,score,c_id)VALUES(8,65,2);insert into scores (s_id,score,c_id)VALUES(8,85,3);insert into scores (s_id,score,c_id)VALUES(8,37,4);insert into scores (s_id,score,c_id)VALUES(8,79,5);

insert into scores (s_id,score,c_id)VALUES(9,66,1);insert into scores (s_id,score,c_id)VALUES(9,39,2);insert into scores (s_id,score,c_id)VALUES(9,85,3);insert into scores (s_id,score,c_id)VALUES(9,79,5);

insert into scores (s_id,score,c_id)VALUES(10,66,2);insert into scores (s_id,score,c_id)VALUES(10,89,3);insert into scores (s_id,score,c_id)VALUES(10,74,4);insert into scores (s_id,score,c_id)VALUES(10,79,5);“`

## 单表查询

### 基本查询

#### 基本语法

查询所有列:

“`sqlselect * from 表名;select * from student;“`

查询指定的列:

“`sqlselect id,`name`,age,gender from student;select id,`name`,age from student;“`

> 补充:开发中,严禁使用`select *`。

如果表中有完全重复的记录只显示一次,在查询的列之前加上`distinct`。

“`sqlselect DISTINCT `name` from book;“`

#### 列运算

“`sqlselect id,`name`,age/10 from student;“`

> 注意:我们写的所有的查询语句,最终执行的结果,都是生成一张虚拟表。

“`sqlselect id,`name`,sal+1000 from employee;“`

> 注意:>> 1. null值和任何值做计算都为null,需要用到函数`ifnull()`函数。“select IFNULL(sal,0) + 1000 from employee;“如果薪资是空,则为0。> 2. 将字符串做加减乘除运算,会把字符串当0处理。

#### 别名

我们可以给列起【别名】,因为我们在查询过程中,列名很可能重复,可能名字不够简洁,或者列的名字不能满足我们的要求。

“`sqlselect id `编号`,`name` `姓名`,age `年龄`,gender `性别` from student;select id as `编号`,`name` as `姓名`,age as `年龄`,gender as `性别` from student;“`

#### 条件控制

条件查询:在后面添加`where`指定条件

“`sqlselect * from student where id = 3;select * from student where id in (1,3,5);select * from student where id > 2;select * from student where id BETWEEN 3 and 5;select * from student where id BETWEEN 6 and 7 or age > 20;“`

模糊查询:我想查询所有姓张的。

“`sqlselect * from student where `name` like ‘张%’;select * from student where `name` like ‘张_’;select * from student where `name` like ‘%明%’;select * from student where `name` like ‘_明_’;“`

> 通配符:`_`下划线代表一个字符,`%`百分号代表任意个字符。

### 排序

– 升序

“`sql select * from student ORDER BY age ASC; — ASC是可以省略 “`

– 降序

“`sql select * from student ORDER BY age DESC; “`

– 使用多列作为排序条件:当第一个排序条件相同时,根据第二列排序条件进行排序(第二列如果还相同,…..)

“`sql select * from student ORDER BY age asc,id desc; “`

> 举例:>> 创建一张用户表,id,username,password。>> 几乎所有的表都会有两个字段,create_time,update_time。>> 几乎所有的查询都会按照update_time降序排列。

### 聚合函数

#### count

查询满足条件的记录行数,后边可以跟where条件。

如果满足条件的列值为空,不会进行统计。

如果我们要统计真实有效的记录数,最好不要用可以为空列。

– count(*)– count(主键)(推荐)– count(1)(不推荐)

“`sqlselect count(列名) from 表名;“`

“`sqlselect count(id) from student where gender=’男’;“`

#### max

查询满足条件的记录中的最大值,后面可以跟where条件。

“`sqlselect max(age) from student where gender=’女’;“`

#### min

查询满足条件的记录中的最小值,后面可以跟where条件。

“`sqlselect MIN(age) from student where gender=’男’;“`

#### sum

查询满足条件的记录的和,后面可以跟where条件。

“`sqlselect sum(age) from student where gender=’男’;“`

#### avg

查询满足条件的记录的平均数,后面可以跟where条件。

“`sqlselect avg(score) from scores where c_id = 3;“`

### 分组查询

顾名思义:分组查询就是将原有数据进行分组统计。

> 举例:>> 将班级的同学按照性别分组,统计男生和女生的平均年龄。

“`sqlselect 分组列名,聚合函数1,聚合函数2… from 表名 group by 该分组列名;“`

分组要使用关键词`group by`,后面可以是一列,也可以是多个列,分组后查询的列只能是分组的列,或者是使用了聚合函数的其他的列,剩余列不能单独使用。

“`sql— 根据性别分组,查看每一组的平均年龄和最大年龄select gender,avg(age),max(age) from student group by gender;— 根据专业号分组,查看每一个专业的平均分select c_id,avg(score) from scores group by c_id;“`

我们可以这样理解:一旦发生了分组,我们查询的结果只能是所有男生的年龄平均值、最大值,而不能是某一个男生的数据。

分组查询前,可以通过关键字【where】先把满足条件的人分出来,再分组。

“`sqlselect 分组列,聚合函数1… from 表名 where 条件 group by 分组列;“`

“`sqlselect c_id,avg(score) from scores where c_id in (1,2,3) group by c_id;“`

分组查询后,也可以通过关键字【having】把组信息中满足条件的组再细分出来。

“`sqlselect 分组列,聚合函数1… from 表名 where 条件 group by 分组列 having 聚合函数或列名(条件);“`

“`sqlselect gender,avg(age),sum(age) `sum_age` from student GROUP BY gender HAVING `sum_age` > 50;“`

> 面试题:where和having的区别?>> 1. where是写在group by之前的筛选,在分组前筛选;having是写在group by之后,分组后再筛选。> 2. where只能使用分组的列作为筛选条件;having既可以使用分组的列,也可以使用聚合函数列作为筛选条件。

### 分页查询

`limit`字句,用来限定查询结果的起始行,以及总行数。

**limit是`mysql`独有的语法。**

“`sqlselect * from student limit 4,3;select * from student limit 4;“`

– 如果只有一个参数,说明从起始位置查找4条记录。

– 如果两个参数,说明从第4行下一行,向后查找3条记录。

> 面试题:>> – MySQL:limit> – Oracle:rownum> – SqlServer:top

> 分析:>> student表中有10条数据,如果每页显示4条,分几页?3页>> 3页怎么来的?(int)(Math.ceil(10 / 4));>> 显示第一页的数据:select * from student limit 0,4;>> 第二页:select * from student limit 4,4;>> 第三页:select * from student limit 8,4;

一个问题:我想要判断在student表中有没有叫”小红”的这个人?

1.0版本

“`sqlselect * from student where name = ‘小红’;select id from student where name = ‘小红’;“`

2.0版本

“`sqlselect count(id) from student where name = ‘小红’;“`

3.0版本

“`sqlselect id from student where name = ‘小红’ limit 1;“`

> 注意:Limit字句永远是在整个的sql语句的最后。

## 多表查询

### 笛卡尔积

“`sqlselect * from student,teacher;“`

如果两个表没有任何关联关系,我们也不会连接这两张表。

在一个`select * from 表名1,表名2;`,就会出现笛卡尔乘积,会生成一张虚拟表,这张虚拟表的数据就是表1和表2两张表数据的乘积。

**注意:开发中,一定要避免出现笛卡尔积。**

多表连接的方式有四种:

– 内连接– 外连接**– 全连接– 子查询

### SQL92语法

1992年的语法。

“`sql— 查询学号,姓名,年龄,分数,通过多表连接查询,student和scores通过id和s_id连接SELECT stu.id 学号, stu.name 姓名, stu.age 年龄, sc.score 分数 FROM student stu, scores sc WHERE stu.id = sc.s_id;“`

“`sql— 查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,courseSELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目FROM student stu, scores sc, course cWHERE stu.id = sc.s_idAND c.id = sc.c_id;“`

“`sql— 查询学号,姓名,年龄,分数,科目名称,老师名称,通过多表查询,student和scores,course,teacherSELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目, t.`name` 老师FROM student stu, scores sc, course c, teacher tWHERE stu.id = sc.s_idAND c.id = sc.c_idAND c.t_id = t.id;“`

“`sql— 查询老师的信息以及对应教的课程SELECT t.id 教师号, t.NAME 教师姓名, c.NAME 科目名FROM teacher t, course c WHERE t.id = c.t_id;“`

> SQL92语法,多表查询,如果有数据为null,会过滤掉。

“`sql— 查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course— 在查询的基础上,进一步筛选,筛选小红和张小军的成绩SELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目FROM student stu, scores sc, course cWHERE stu.id = sc.s_idAND c.id = sc.c_idAND stu.`name` in (‘小红’,’张小军’);“`

“`sql— 查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course— 在查询的基础上,进一步筛选,筛选小红和张小军的成绩— 在小红和张小军成绩的基础上进一步再筛选,筛选他们的java成绩SELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目FROM student stu, scores sc, course cWHERE stu.id = sc.s_idAND c.id = sc.c_idAND stu.`name` in (‘小红’,’张小军’)AND c.`name` = ‘java’;“`

“`sql— 查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course— 找出最低分和最高分,按照科目分组,每一科SELECT sc.c_id, max( score ), min( score ), c.`name` FROM scores sc, course c WHERE sc.c_id = c.id GROUP BY sc.c_id;“`

### SQL99语法

1999年的语法。

#### 内连接

在我们刚才的sql当中,使用逗号分隔两张表进行查询,mysql进行优化默认就等效于内连接。

使用【join】关键字,使用【on】来确定连接条件。【where】只做筛选条件。

“`sqlSELECT t.*, c.* , sc.*FROM teacher t INNER JOIN course c ON c.t_id = t.id INNER JOIN scores sc ON sc.c_id = c.id;“`

#### 外连接(常用)

内连接和外连接的区别:

– 对于【内连接】的两个表,如果【驱动表】在【被驱动表】找不到与之匹配的记录,则最终的记录不会出现在结果集中。– 对于【外连接】中的两个表,即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录,也要将该记录加入到最后的结果集中。针对不同的【驱动表】的位置,有分为【左外连接】和【右外连接】。

> – 对于左连接,左边的表为主,左边的表的记录会完整的出现在结果集里。> – 对于右连接,右边的表为主,左边的表的记录会完整的出现在结果集里。

外连接的关键字【outter join】,也可以省略outter,连接条件同样使用【on】关键字。

##### 左连接

“`sqlSELECT t.*, c.* FROM teacher t LEFT JOIN course c ON t.id = c.t_id;“`

##### 右连接

“`sqlSELECT t.*, c.* FROM course c RIGHT JOIN teacher t ON t.id = c.t_id;“`

#### 全连接

mysql不支持全连接。oracle支持全连接。

“`sqlSELECT * FROM teacher t FULL JOIN course c ON c.t_id = t.id;“`

我们可以通过一些手段来实现全连接的效果

“`sqlSELECT t.*, c.* FROM teacher t LEFT JOIN course c ON t.id = c.t_idUNIONSELECT t.*, c.* FROM teacher t RIGHT JOIN course c ON t.id = c.t_id

   今天的内容就是这些了,知识很多,需要我们更加努力地去学习,加油!

————————

# DQL数据库查询语言

重点,DQL是我们每天都要接触编写最多也是最难的SQL,该语言用来查询记录,不会修改数据库和表结构。

## 构建数据库

创建一张student表:

“`sqlDROP TABLE IF EXISTS student;CREATE TABLE student ( id INT(10) PRIMARY KEY, `name` VARCHAR(10), age INT(10) NOT NULL, gender VARCHAR(2));“`

构建一张course表:

“`sqlDROP TABLE IF EXISTS course;CREATE TABLE course( id INT(10) PRIMARY KEY, `name` VARCHAR(10), t_id INT(10));“`

构建一张teacher表:

“`sqlDROP TABLE IF EXISTS teacher;CREATE TABLE teacher( id INT(10) PRIMARY KEY, `name` VARCHAR(10));“`

构建一个score表:

“`sqlDROP TABLE IF EXISTS score;CREATE TABLE scores( s_id INT(10), score INT(10), c_id INT(10), PRIMARY KEY(s_id,c_id));“`

表格填充数据:

“`sqlinsert into student (id,name,age,gender)VALUES(1,’小明’,19,’男’),(2,’小红’,19,’男’),(3,’小刚’,24,’男’),(4,’小龙’,11,’男’),(5,’小丽’,18,’男’),(6,’小军’,18,’女’),(7,’小航’,16,’男’),(8,’小亮’,23,’男’),(9,’小杰’,22,’女’),(10,’小虎’,21,’男’);

insert into course (id,name,t_id)VALUES(1,’数学’,1),(2,’语文’,2),(3,’c++’,3),(4,’java’,4),(5,’php’,null);

insert into teacher (id,name)VALUES(1,’Tom’),(2,’Jerry’),(3,’Tony’),(4,’Jack’),(5,’Rose’);

insert into scores (s_id,score,c_id)VALUES(1,80,1);insert into scores (s_id,score,c_id)VALUES(1,56,2);insert into scores (s_id,score,c_id)VALUES(1,95,3);insert into scores (s_id,score,c_id)VALUES(1,30,4);insert into scores (s_id,score,c_id)VALUES(1,76,5);

insert into scores (s_id,score,c_id)VALUES(2,35,1);insert into scores (s_id,score,c_id)VALUES(2,86,2);insert into scores (s_id,score,c_id)VALUES(2,45,3);insert into scores (s_id,score,c_id)VALUES(2,94,4);insert into scores (s_id,score,c_id)VALUES(2,79,5);

insert into scores (s_id,score,c_id)VALUES(3,65,2);insert into scores (s_id,score,c_id)VALUES(3,85,3);insert into scores (s_id,score,c_id)VALUES(3,37,4);insert into scores (s_id,score,c_id)VALUES(3,79,5);

insert into scores (s_id,score,c_id)VALUES(4,66,1);insert into scores (s_id,score,c_id)VALUES(4,39,2);insert into scores (s_id,score,c_id)VALUES(4,85,3);

insert into scores (s_id,score,c_id)VALUES(5,66,2);insert into scores (s_id,score,c_id)VALUES(5,89,3);insert into scores (s_id,score,c_id)VALUES(5,74,4);

insert into scores (s_id,score,c_id)VALUES(6,80,1);insert into scores (s_id,score,c_id)VALUES(6,56,2);insert into scores (s_id,score,c_id)VALUES(6,95,3);insert into scores (s_id,score,c_id)VALUES(6,30,4);insert into scores (s_id,score,c_id)VALUES(6,76,5);

insert into scores (s_id,score,c_id)VALUES(7,35,1);insert into scores (s_id,score,c_id)VALUES(7,86,2);insert into scores (s_id,score,c_id)VALUES(7,45,3);insert into scores (s_id,score,c_id)VALUES(7,94,4);insert into scores (s_id,score,c_id)VALUES(7,79,5);

insert into scores (s_id,score,c_id)VALUES(8,65,2);insert into scores (s_id,score,c_id)VALUES(8,85,3);insert into scores (s_id,score,c_id)VALUES(8,37,4);insert into scores (s_id,score,c_id)VALUES(8,79,5);

insert into scores (s_id,score,c_id)VALUES(9,66,1);insert into scores (s_id,score,c_id)VALUES(9,39,2);insert into scores (s_id,score,c_id)VALUES(9,85,3);insert into scores (s_id,score,c_id)VALUES(9,79,5);

insert into scores (s_id,score,c_id)VALUES(10,66,2);insert into scores (s_id,score,c_id)VALUES(10,89,3);insert into scores (s_id,score,c_id)VALUES(10,74,4);insert into scores (s_id,score,c_id)VALUES(10,79,5);“`

## 单表查询

### 基本查询

#### 基本语法

查询所有列:

“`sqlselect * from 表名;select * from student;“`

查询指定的列:

“`sqlselect id,`name`,age,gender from student;select id,`name`,age from student;“`

> 补充:开发中,严禁使用`select *`。

如果表中有完全重复的记录只显示一次,在查询的列之前加上`distinct`。

“`sqlselect DISTINCT `name` from book;“`

#### 列运算

“`sqlselect id,`name`,age/10 from student;“`

> 注意:我们写的所有的查询语句,最终执行的结果,都是生成一张虚拟表。

“`sqlselect id,`name`,sal+1000 from employee;“`

> 注意:>> 1. null值和任何值做计算都为null,需要用到函数`ifnull()`函数。“select IFNULL(sal,0) + 1000 from employee;“如果薪资是空,则为0。> 2. 将字符串做加减乘除运算,会把字符串当0处理。

#### 别名

我们可以给列起【别名】,因为我们在查询过程中,列名很可能重复,可能名字不够简洁,或者列的名字不能满足我们的要求。

“`sqlselect id `编号`,`name` `姓名`,age `年龄`,gender `性别` from student;select id as `编号`,`name` as `姓名`,age as `年龄`,gender as `性别` from student;“`

#### 条件控制

条件查询:在后面添加`where`指定条件

“`sqlselect * from student where id = 3;select * from student where id in (1,3,5);select * from student where id > 2;select * from student where id BETWEEN 3 and 5;select * from student where id BETWEEN 6 and 7 or age > 20;“`

模糊查询:我想查询所有姓张的。

“`sqlselect * from student where `name` like ‘张%’;select * from student where `name` like ‘张_’;select * from student where `name` like ‘%明%’;select * from student where `name` like ‘_明_’;“`

> 通配符:`_`下划线代表一个字符,`%`百分号代表任意个字符。

### 排序

– 升序

“`sql select * from student ORDER BY age ASC; — ASC是可以省略 “`

– 降序

“`sql select * from student ORDER BY age DESC; “`

– 使用多列作为排序条件:当第一个排序条件相同时,根据第二列排序条件进行排序(第二列如果还相同,…..)

“`sql select * from student ORDER BY age asc,id desc; “`

> 举例:>> 创建一张用户表,id,username,password。>> 几乎所有的表都会有两个字段,create_time,update_time。>> 几乎所有的查询都会按照update_time降序排列。

### 聚合函数

#### count

查询满足条件的记录行数,后边可以跟where条件。

如果满足条件的列值为空,不会进行统计。

如果我们要统计真实有效的记录数,最好不要用可以为空列。

– count(*)– count(主键)(推荐)– count(1)(不推荐)

“`sqlselect count(列名) from 表名;“`

“`sqlselect count(id) from student where gender=’男’;“`

#### max

查询满足条件的记录中的最大值,后面可以跟where条件。

“`sqlselect max(age) from student where gender=’女’;“`

#### min

查询满足条件的记录中的最小值,后面可以跟where条件。

“`sqlselect MIN(age) from student where gender=’男’;“`

#### sum

查询满足条件的记录的和,后面可以跟where条件。

“`sqlselect sum(age) from student where gender=’男’;“`

#### avg

查询满足条件的记录的平均数,后面可以跟where条件。

“`sqlselect avg(score) from scores where c_id = 3;“`

### 分组查询

顾名思义:分组查询就是将原有数据进行分组统计。

> 举例:>> 将班级的同学按照性别分组,统计男生和女生的平均年龄。

“`sqlselect 分组列名,聚合函数1,聚合函数2… from 表名 group by 该分组列名;“`

分组要使用关键词`group by`,后面可以是一列,也可以是多个列,分组后查询的列只能是分组的列,或者是使用了聚合函数的其他的列,剩余列不能单独使用。

“`sql— 根据性别分组,查看每一组的平均年龄和最大年龄select gender,avg(age),max(age) from student group by gender;— 根据专业号分组,查看每一个专业的平均分select c_id,avg(score) from scores group by c_id;“`

我们可以这样理解:一旦发生了分组,我们查询的结果只能是所有男生的年龄平均值、最大值,而不能是某一个男生的数据。

分组查询前,可以通过关键字【where】先把满足条件的人分出来,再分组。

“`sqlselect 分组列,聚合函数1… from 表名 where 条件 group by 分组列;“`

“`sqlselect c_id,avg(score) from scores where c_id in (1,2,3) group by c_id;“`

分组查询后,也可以通过关键字【having】把组信息中满足条件的组再细分出来。

“`sqlselect 分组列,聚合函数1… from 表名 where 条件 group by 分组列 having 聚合函数或列名(条件);“`

“`sqlselect gender,avg(age),sum(age) `sum_age` from student GROUP BY gender HAVING `sum_age` > 50;“`

> 面试题:where和having的区别?>> 1. where是写在group by之前的筛选,在分组前筛选;having是写在group by之后,分组后再筛选。> 2. where只能使用分组的列作为筛选条件;having既可以使用分组的列,也可以使用聚合函数列作为筛选条件。

### 分页查询

`limit`字句,用来限定查询结果的起始行,以及总行数。

**limit是`mysql`独有的语法。**

“`sqlselect * from student limit 4,3;select * from student limit 4;“`

– 如果只有一个参数,说明从起始位置查找4条记录。

– 如果两个参数,说明从第4行下一行,向后查找3条记录。

> 面试题:>> – MySQL:limit> – Oracle:rownum> – SqlServer:top

> 分析:>> student表中有10条数据,如果每页显示4条,分几页?3页>> 3页怎么来的?(int)(Math.ceil(10 / 4));>> 显示第一页的数据:select * from student limit 0,4;>> 第二页:select * from student limit 4,4;>> 第三页:select * from student limit 8,4;

一个问题:我想要判断在student表中有没有叫”小红”的这个人?

1.0版本

“`sqlselect * from student where name = ‘小红’;select id from student where name = ‘小红’;“`

2.0版本

“`sqlselect count(id) from student where name = ‘小红’;“`

3.0版本

“`sqlselect id from student where name = ‘小红’ limit 1;“`

> 注意:Limit字句永远是在整个的sql语句的最后。

## 多表查询

### 笛卡尔积

“`sqlselect * from student,teacher;“`

如果两个表没有任何关联关系,我们也不会连接这两张表。

在一个`select * from 表名1,表名2;`,就会出现笛卡尔乘积,会生成一张虚拟表,这张虚拟表的数据就是表1和表2两张表数据的乘积。

**注意:开发中,一定要避免出现笛卡尔积。**

多表连接的方式有四种:

– 内连接– 外连接**– 全连接– 子查询

### SQL92语法

1992年的语法。

“`sql— 查询学号,姓名,年龄,分数,通过多表连接查询,student和scores通过id和s_id连接SELECT stu.id 学号, stu.name 姓名, stu.age 年龄, sc.score 分数 FROM student stu, scores sc WHERE stu.id = sc.s_id;“`

“`sql— 查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,courseSELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目FROM student stu, scores sc, course cWHERE stu.id = sc.s_idAND c.id = sc.c_id;“`

“`sql— 查询学号,姓名,年龄,分数,科目名称,老师名称,通过多表查询,student和scores,course,teacherSELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目, t.`name` 老师FROM student stu, scores sc, course c, teacher tWHERE stu.id = sc.s_idAND c.id = sc.c_idAND c.t_id = t.id;“`

“`sql— 查询老师的信息以及对应教的课程SELECT t.id 教师号, t.NAME 教师姓名, c.NAME 科目名FROM teacher t, course c WHERE t.id = c.t_id;“`

> SQL92语法,多表查询,如果有数据为null,会过滤掉。

“`sql— 查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course— 在查询的基础上,进一步筛选,筛选小红和张小军的成绩SELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目FROM student stu, scores sc, course cWHERE stu.id = sc.s_idAND c.id = sc.c_idAND stu.`name` in (‘小红’,’张小军’);“`

“`sql— 查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course— 在查询的基础上,进一步筛选,筛选小红和张小军的成绩— 在小红和张小军成绩的基础上进一步再筛选,筛选他们的java成绩SELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目FROM student stu, scores sc, course cWHERE stu.id = sc.s_idAND c.id = sc.c_idAND stu.`name` in (‘小红’,’张小军’)AND c.`name` = ‘java’;“`

“`sql— 查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course— 找出最低分和最高分,按照科目分组,每一科SELECT sc.c_id, max( score ), min( score ), c.`name` FROM scores sc, course c WHERE sc.c_id = c.id GROUP BY sc.c_id;“`

### SQL99语法

1999年的语法。

#### 内连接

在我们刚才的sql当中,使用逗号分隔两张表进行查询,mysql进行优化默认就等效于内连接。

使用【join】关键字,使用【on】来确定连接条件。【where】只做筛选条件。

“`sqlSELECT t.*, c.* , sc.*FROM teacher t INNER JOIN course c ON c.t_id = t.id INNER JOIN scores sc ON sc.c_id = c.id;“`

#### 外连接(常用)

内连接和外连接的区别:

– 对于【内连接】的两个表,如果【驱动表】在【被驱动表】找不到与之匹配的记录,则最终的记录不会出现在结果集中。– 对于【外连接】中的两个表,即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录,也要将该记录加入到最后的结果集中。针对不同的【驱动表】的位置,有分为【左外连接】和【右外连接】。

> – 对于左连接,左边的表为主,左边的表的记录会完整的出现在结果集里。> – 对于右连接,右边的表为主,左边的表的记录会完整的出现在结果集里。

外连接的关键字【outter join】,也可以省略outter,连接条件同样使用【on】关键字。

##### 左连接

“`sqlSELECT t.*, c.* FROM teacher t LEFT JOIN course c ON t.id = c.t_id;“`

##### 右连接

“`sqlSELECT t.*, c.* FROM course c RIGHT JOIN teacher t ON t.id = c.t_id;“`

#### 全连接

mysql不支持全连接。oracle支持全连接。

“`sqlSELECT * FROM teacher t FULL JOIN course c ON c.t_id = t.id;“`

我们可以通过一些手段来实现全连接的效果

“`sqlSELECT t.*, c.* FROM teacher t LEFT JOIN course c ON t.id = c.t_idUNIONSELECT t.*, c.* FROM teacher t RIGHT JOIN course c ON t.id = c.t_id

   今天的内容就是这些了,知识很多,需要我们更加努力地去学习,加油!