MySQL--学习记录4

目录

1.什么是内连接查询； 如何使用内连接查询。

2.什么是外连接查询； 如何使用外连接查询。

 3.如何编写复合条件连接查询语句

4.什么叫子查询， 比较运算符结合标量子查询的使用。

5.如何在子查询中使用关键字进行查询。

1.什么是内连接查询； 如何使用内连接查询。

编程要求

在右侧编辑器补充代码，查询数据表中学生姓名以及对应的班级名称，将其对应的列名分别另命名为studentName和className。

我们为你提供了两张表，内容如下：

tb_student表数据：

id

name

class_id

1

Emma

2

2

Mary

4

3

Allen

(null)

4

Kevin

1

5

Rose

2

6

James

1

tb_class表数据：

id

name

1

软件1631

2

软件1632

3

测试1631

4

测试1632

仅将两个表中满足连接条件的行组合起来作为结果集，称为内连接；

关键字：[inner] join ... on。

语法：

  1. 表1 [inner] join 表2 on 表1.字段=表2.字段

语法解释：

  1. 从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示连接条件：条件字段就是代表相同的业务含义（如下面两张表中的 employee.dept_id 和 department.id），大多数情况下为两张表中的主外键关系。
create table tb_student
(
id int,
name varchar(25),
class_id int
);

insert into tb_student (id,name,class_id)
values (1,'Emma',2),
(2,'Mary',4),
(3,'Allen',null),
(4,'Kevin',1),
(5,'Rose',2),
(6,'James',1);

create table tb_class
(
id int,
name varchar(25)
);

insert into tb_class (id,name)
values (1,'软件1631'),
(2,'软件1632'),
(3,'测试1631'),
(4,'测试1632');



select tb_stu.name as studentName ,tb_cla.name as className from tb_class as tb_cla join tb_student as tb_stu on tb_stu.class_id=tb_cla.id;

 

2.什么是外连接查询； 如何使用外连接查询。

编程要求

在右侧编辑器补充代码，分别使用左外连接和右外连接查询数据表中所有学生姓名和对应的班级名称，查询结果列分别另命名为studentName和className。

我们为你提供了两张表，内容如下：

tb_student表数据：

id

name

class_id

1

Emma

2

2

Mary

4

3

Allen

(null)

4

Kevin

1

5

Rose

2

6

James

1

tb_class表数据：

id

name

1

软件1631

2

软件1632

3

测试1631

4

测试1632

外连接查询

以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配，正确保留；不能匹配，其它表的字段都置空（null），称为外连接。

外连接查询分为左外连接查询和右外连接查询；

关键字：left/right [outer] join ... on。

语法：

  1. 表1 left/right [outer]  join 表2 on 表1.字段=表2.字段
select tb_stu.name as studentName ,
tb_cla.name as className
from tb_class as tb_cla left 
join tb_student as tb_stu on tb_stu.class_id=tb_cla.id;


select tb_stu.name as studentName ,
tb_cla.name as className
 from tb_class as tb_cla right 
join tb_student as tb_stu on tb_stu.class_id=tb_cla.id;

 3.如何编写复合条件连接查询语句

编程要求

在右侧编辑器补充代码，查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级，其中学生的姓名和学生所在班级分别另命名为studentName和className。

我们为你提供了两张表，内容如下：

tb_student表数据：

id

name

class_id

score

1

Emma

2

89

2

Mary

4

92

4

Kevin

1

76

5

Rose

3

68

6

James

1

99

tb_class表数据：

id

name

1

软件1631

2

软件1632

3

测试1631

4

测试1632

复合条件连接查询

  • 复合条件连接查询，就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加精确。
create table tb_student2
(
id int,
name varchar(25),
class_id int,
id_score int
);


insert into tb_student2 (id,name,class_id,id_score)
values (1,'Emma',2,89),
(2,'Mary',4,92),
(4,'Kevin',1,76),
(5,'Rose',2,68),
(6,'James',1,99);


/*查询所有班级里分数在90分以上的学生的姓名和学
生的成绩以及学生所在的班级，其中学生的姓名和学生
所在班级分别另命名为studentName和className。*/

select tb_stu2.name as studentName ,
tb_stu2.id_score,tb_cla.name as className 
from tb_class as tb_cla
 join tb_student2 as tb_stu2 on tb_stu2.class_id=tb_cla.id and tb_stu2.id_score>=90;

4.什么叫子查询， 比较运算符结合标量子查询的使用。

编程要求

在右侧编辑器补充代码，查询大于所有平均年龄的员工姓名与年龄。

我们为你提供了tb_emp表，数据如下：

id

name

age

1

Mary

23

2

Allen

21

3

kevin

25

4

Tom

33

5

Nancy

28

 

子查询

子查询是指出现在其他SQL语句内的SELECT子句。

例如：

  1. SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);

子查询指嵌套在查询内部，且必须始终出现在圆括号内，子查询可以分为四类：

标量子查询：返回单一值的标量，最简单的形式；

列子查询：返回的结果集是 N 行一列；

行子查询：返回的结果集是一行 N 列；

表子查询：返回的结果集是 N 行 N 列。

带比较运算符的子查询

运算符

说明

>

大于

>=

大于或等于

=

等于

!= 或 <>

不等于

<

小于

<=

小于或等于


带有比较运算符的子查询是指父查询和子查询使用比较运算符连接的嵌套查询；

使用场景：当用户能够确切的知道内层查询返回的是单个值时,可以使用比较运算符。

 

create table tb_emp
(
id int,
name varchar(25),
age int
);



insert into tb_emp (id,name,age)
values (1,'Mary',23),
(2,'Allen',21),
(3,'kevin',25),
(4,'Tom',33),
(5,'Nancy',28);

/*查询大于所有平均年龄的员工姓名与年龄。*/


select name,age
 from tb_emp 
where age>(select avg(age) from tb_emp);

5.如何在子查询中使用关键字进行查询。

编程要求

我们为你提供了如下数据表： tb_salary表数据：

id

position

salary

1

Java

8000

2

Java

8400

3

Java

9000

4

Python

6500

5

Python

10000

根据提供的数据，在右侧编辑器中补充代码：

查询薪资表中比Java最高工资高的所有员工职位名称和薪资；

查询薪资表中比Java最低工资高的所有员工职位名称和薪资；

查询薪资表中职位为Java的所有员工职位名称和薪资。

由于列子查询返回的结果集是 N 行一列，因此不能直接使用 = 、>、<、>=、<=、<>这些比较标量结果的操作符。在列子查询中可以使用 ALL、ANY、SOME 和 IN关键字操作符。

ALL关键字

ALL必须接在一个比较运算符的后面，表示与子查询返回的所有值比较都为 TRUE则返回TRUE。

ANY和SOME关键字

ANY与比较操作符联合使用，表示与子查询返回的任何值比较为TRUE ，则返回TRUE。 SOME是ANY的别名，一般用的比较少。

IN关键字

IN的意思就是指定的一个值是否在这个集合中，如果在就返回TRUE；否则就返回FALSE了，同IN(项1，项2，…)；

IN 是 = ANY 的别名，二者相同，但 NOT IN 的别名却不是 <> ANY 而是 <> SOME。

 

create table tb_salary
(
id int,
position varchar(25),
salary int
);

insert into tb_salary (id,position,salary)
values 
(1,'Java',8000),
(2,'Java',8400),
(3,'Java',9000),
(4,'Python',6500),
(5,'Python',10000);


/*查询薪资表中比Java最高工资高的所有员工职位名称和薪资；
查询薪资表中比Java最低工资高的所有员工职位名称和薪资；
查询薪资表中职位为Java的所有员工职位名称和薪资。*/

select position,salary
 from tb_salary
 where salary > any(select max(salary) from tb_salary where position='java');


select position,salary
 from tb_salary 
where salary > any(select min(salary) from tb_salary where position='java');



select position,salary
 from tb_salary 
where position='java';