数据库约束练习（2）

1.已知表T1中有2行数据，T2中有3行数据，执行SQL语句,“select a.* from T1 a,T2 b”后，返回的行数为

A.2

B.3

C.5

D.6

答案：D

 解析：关联查询没有任何条件时，是返回笛卡尔积的结果，行数为乘积，列数为两张表的列相加 

2.Mysql查询时，只有满足联接条件的记录才包含在查询结果，这种联接是

A.左联接

B.右联接

C.内联接

D.全联接

答案：C

解析：只有内联接始终会按照联接条件进行过滤，外联接当外表中的数据不满足联接条件时，也会返

3.数据库做join操作时，至少要涉及到几张表？

A.3

B.1

C.2

D.4

答案：B

解析：当使用自联接的时候，也可以join关联，就只有1张表

4.有两个表分别如下：

表A(varchar(32) name， int grade)

数据：zhangshan 80， lisi 60， wangwu 84

表B(varchar(32) name， int age)

数据：zhangshan 26， lisi 24， wangwu 26， wutian 26

写SQL语句得到如下查询结果：

| NAME   | GRADE | AGE |

| --------- | ----- | ---- |

| zhangshan | 80  | 26  |

| lisi   | 60  | 24  |

| wangwu  | 84  | 26  |

| wutian  | null | 26  |

--创建表，写入数据
create table A(name varchar(32) primary key, grade int);
 
create table B(
	name varchar(32) primary key,
	age int
);
 
insert into
	A(name, grade)
values
('zhangshan', 80),
('lisi', 60),
('wangwu', 84);
 
 
insert into
	B(name, age)
values
('zhangshan', 26),
('lisi', 24),
('wangwu', 26),
('wutian', 26);
 

select
	B.name ,
	A.grade ,
	B.age 
from
	A right join B on A.name = B.name;

说明：主要考察使用关联查询时需要使用内联还是外联，这里wutian再A表中没有记录，但还是需要返回结果，所以应该将B表作为外表进行外连接查询 

5.现在有员工表、部门表和薪资表。部门表depart的字段有depart_id， name；员工表 staff 的字段有 staff_id， name， age， depart_id；薪资表salary 的字段有 salary_id，staff_id，salary，month。

（问题a）：求每个部门'2016-09'月份的部门薪水总额

（问题b）：求每个部门的部门人数，要求输出部门名称和人数

（问题c）：求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数

--创建表，写入数据
create table depart(
	depart_id int primary key auto_increment,
	name varchar(20)
);
 
create table staff(
	staff_id int primary key auto_increment,
	name varchar(20),
	age int,
	depart_id int,
	foreign key (depart_id) references depart (depart_id)
);
 
create table salary(
	salary_id int primary key auto_increment,
	staff_id int,
	salary int,
	month timestamp,
	foreign key (staff_id) references staff (staff_id)
);
 
insert into depart (name) values
('运营部'),
('财务部'),
('人力资源部'),
('策划部');
 
insert into staff (name,age,depart_id) values
('张一',19,1),
('李一',26,1),
('赵一',29,1),
('张二',19,2),
('李二',26,2),
('赵二',29,2),
('张三',19,3),
('李三',22,3),
('赵三',29,3),
('李四',25,4),
('赵四',24,4),
('王四',23,4);
 
insert into salary (staff_id,salary,month) values
(1,5900,'2016-9-1'),
(1,6900,'2016-8-1'),
(2,13000,'2016-9-1'),
(2,9000,'2016-8-1'),
(3,9000,'2016-9-1'),
(3,8000,'2016-8-1'),
(4,10200,'2016-9-1'),
(4,15200,'2016-8-1'),
(5,10500,'2016-9-1'),
(5,9500,'2016-8-1'),
(6,11000,'2016-9-1'),
(6,15000,'2016-8-1'),
(7,9000,'2016-9-1'),
(7,8300,'2016-8-1'),
(8,9200,'2016-9-1'),
(8,12000,'2016-8-1'),
(9,12500,'2016-9-1'),
(9,16000,'2016-8-1'),
(10,8500,'2016-9-1'),
(10,9800,'2016-8-1'),
(11,10000,'2016-9-1'),
(11,9000,'2016-8-1'),
(12,10000,'2016-9-1'),
(12,11000,'2016-8-1');

问题a：求每个部门'2016-09'月份的部门薪水总额 

SELECT

	dep.NAME,

	sum( sal.salary ) 

FROM

	salary sal

	JOIN staff sta ON sal.staff_id = sta.staff_id

	JOIN depart dep ON sta.depart_id = dep.depart_id 

WHERE

	YEAR ( sal.MONTH ) = 2016 

	AND MONTH ( sal.MONTH ) = 9 

GROUP BY

	dep.depart_id

说明：

mysql中年和月的函数分别是year(字段)，month(字段)

查询要求的是每个部门的信息，所以要按照部门进行分组，部门和员工为1:m，员工与薪水为1:m，查询要求有部门，有薪水，所以必须关联3张表查询

问题b：求每个部门的部门人数，要求输出部门名称和人数

SELECT

	dep.NAME,

	count( sta.staff_id ) 

FROM

	staff sta

	JOIN depart dep ON dep.depart_id = sta.depart_id 

GROUP BY

	sta.depart_id

 说明：查询要求的信息有部门，有人数，人数只能从员工表中获取，所以关联表为部门表/员工表。按照部门id分组查询员工id的行数

（问题c）：求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数

SELECT

	dep.NAME,

	sal.MONTH,

	sum( sal.salary ) 

FROM

	depart dep

	JOIN staff sta ON dep.depart_id = sta.depart_id

	JOIN salary sal ON sta.staff_id = sal.staff_id 

GROUP BY

	dep.depart_id,

	sal.MONTH

 说明：按照题目要求，查询信息有部门/月/每个部门每月薪资总额，其中薪水信息再薪水表中，每个员工可以有多条薪资记录（不同时间）。所以需要按照部门分组，再按照月份分组，查询分组的部门在不同月份下的总薪水

6.编写一个SQL查询，获取Employee表中第二高的薪水（Salary）【陌陌科技2020届校招笔试题】

+-----+-------+

| Id | Salary|

+-----+-------+

| 1  | 100 |

+-----+-------+

| 2  | 200 |

+-----+-------+

| 3  | 300 |

+-----+-------+

例如上述Employee表，SQL查询应该返回200作为第二高的薪水。如果不存在第二高的薪水，那么查询应该返回null。

+---------------------+

| SecondHighestSalary |

+---------------------+

|    200     |

+---------------------+

select ifnull((select  Salary  from Employee order by Salary desc limit 1,1),null ) as SecondHighestSalary;

 说明：先查询最高的薪水，然后适用ifnull，如果没有就返回null

7.已知T1和T2的字段定义完全相同，T1有5条不同数据，T2有5条不同数据，其中T1有2条数据存在表T2中，使用UNION语句查询这些数据，要求重复数据不出现

SELECT * FROM T1 UNION SELECT * FROM T2；

 说明：union不能出现重复，union all会出现重复数据。根据题目要求使用union