假设有一个“职工”表，表结构如下
| 职工号 | 姓名 | 年龄 | 月工资 | 部门号 | 电话 | 办公室 |
|---|---|---|---|---|---|---|
| 1 | 张三 | 25 | 2500 | 1 | 1123 | 101 |
| 2 | 李四 | 26 | 1500 | 1 | 1234 | 102 |
| 3 | 王五 | 22 | 900 | 2 | 4567 | 103 |
| 4 | 刘秋 | 21 | 2000 | 2 | 34598 | 103 |
创建表格：
//创建表格
mysql> create table workes (
-> id int not null auto_increment,
-> name varchar(100) not null,
-> age varchar(5) not null,
-> salary int(11) not null,
-> department int not null,
-> telephone varchar(13) not null,
-> office varchar(5) not null,
-> primary key(id)
-> )default charset=utf8;
//插入数据
mysql> insert into workers (name,age,salary,department,telephone,office) values ("张三","25","2500",1,"1234567","101");
mysql> insert into workers (name,age,salary,department,telephone,office) values ("李四","26","1500",1,"5678990","102"),("王五","22","900",2,"7657875","103");
mysql> insert into workers (name,age,salary,department,telephone,office) values ("刘秋","21","2000",2,"8799765","103"),("井七","27","1900",1,"98765454","102");
//查看数据内容
mysql> select * from workers;
+----+--------+-----+--------+------------+-----------+--------+
| id | name | age | salary | department | telephone | office |
+----+--------+-----+--------+------------+-----------+--------+
| 1 | 张三 | 25 | 2500 | 1 | 1234567 | 101 |
| 2 | 李四 | 26 | 1500 | 1 | 5678990 | 102 |
| 3 | 王五 | 22 | 900 | 2 | 7657875 | 103 |
| 4 | 刘秋 | 21 | 2000 | 2 | 8799765 | 103 |
| 5 | 井七 | 27 | 1900 | 1 | 98765454 | 102 |
+----+--------+-----+--------+------------+-----------+--------+
5 rows in set (0.00 sec)
一、问题：
查询每个部门中月工资最高的“职工号”, SQL查询语句如下：
SELECT 职工号 FROM 职工 AS A
WHERE职工.月工资 ＝ （SELECT MAX(月工资) FROM 职工 AS B WHERE A.部门号 ＝ B.部门号）;
语句查询：
mysql> select id from workers AS A
-> where A.salary = (select MAX(salary) from workers AS B
-> where A.department = B.department);
+----+
| id |
+----+
| 1 |
| 4 |
+----+
2 rows in set (0.00 sec)1:请简要的说明该查询语句对查询效率的影响(6分)
2:对该查询语句进行修改，使它既可以完成相同的功能，又可以提高查询效率。(8分)
回答：
1. 效率低。对于外层职工关系A 中的每一个记录，都要对内层职工关系B进行检索，所有效率不高。
2.
方法 1）使用临时表
SELECT MAX(月工资) as 最高工资，部门号 INTO temp FROM 职工 GROUP BY 部门号;
SELECT 职工号 FROM 职工，temp WHERE 月工资＝最高工资 AND 职工.部门号 ＝ temp.部门号；
//创建临时表
mysql> create TEMPORARY table temp (select MAX(salary) AS Most_Salary,department from workers group by department);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+-------------+------------+
| Most_Salary | department |
+-------------+------------+
| 2500 | 1 |
| 2000 | 2 |
+-------------+------------+
2 rows in set (0.00 sec)
//查询
mysql> select id from workers,temp where salary=Most_Salary and workers.department=temp.department;
+----+
| id |
+----+
| 1 |
| 4 |
+----+
2 rows in set (0.01 sec)注意：在mysql 中不支持select MAX(salary) as Most_Salary,department into temp from workers GROUP BY department;可更改为create table temp (select MAX(salary) AS Most_Salary,department from workers group by department);
方法 2)
SELECT 职工号 FROM 职工,（SELECT MAX(月工资) as 最高工资,部门号 FROM 职工 GROUP BY 部门号） as DEPMAX
WHERE 月工资 ＝ 最高工资 AND 职工.部门号 ＝ DEPMAX.部门号;
mysql> select id from workers,(select MAX(salary) AS Most_Salary,department from workers group by department ) AS DEPMAX where salary=Most_Salary and workers.department=DEPMAX.department;
+----+
| id |
+----+
| 1 |
| 4 |
+----+
2 rows in set (0.00 sec)
//列出多个属性
mysql> select id,workers.salary,workers.department from workers,(select Max(salary) as max_salary,department from workers group by department) as limittmp where workers.salary=limittmp.max_salary and workers.department=limittmp.department;
+----+--------+------------+
| id | salary | department |
+----+--------+------------+
| 1 | 2500 | 1 |
| 4 | 2000 | 2 |
+----+--------+------------+
2 rows in set (0.01 sec)
二、问题：
假设在“职工”表中的“年龄”和“月工资”字段上创建了索引，下列的查询语句可能不会促使查询优化器使用索引，从而降低了效率，请写出可以完成相同功能又可以提高查询效率的SQL语句。
select 姓名,年龄,月工资 from 职工 where 年龄 > 45 or 月工资 < 1000;
回答：
SELECT 姓名,年龄,月工资 FROM 职工
WHERE 年龄 > 45
UNION
SELECT 姓名，年龄，月工资 FROM 职工
WHERE月工资 < 1000;
使用UNION就可以促使 查询优化器使用索引。
mysql> select name,age,salary from workers where age>45 or salary <1000;
+--------+-----+--------+
| name | age | salary |
+--------+-----+--------+
| 王五 | 22 | 900 |
+--------+-----+--------+
1 row in set (0.01 sec)
mysql> select name,age,salary from workers where age>45 UNION select name,age,salary from workers where salary <1000;
+--------+-----+--------+
| name | age | salary |
+--------+-----+--------+
| 王五 | 22 | 900 |
+--------+-----+--------+
1 row in set (0.00 sec)
三、问题
设有关系EMP（ENO，ENAME，SALARY，DNO），其中各属性的含义依次为职工号、姓名、工资和所在部门号，
以及关系DEPT（DNO，DNAME，MANAGER），其中各属性含义依次为部门号、部门名称、部门经理的职工号。（回答下列题目）(20分)
1. 列出各部门中工资不低于600元的职工的平均工资。(10分)
2. 请用SQL语句将“销售部”的那些工资数额低于600的职工的工资上调10%。(10分)
//创建表格
mysql> create table EMP (
-> ENO int not null auto_increment,
-> ENAME varchar(100) not null,
-> SALARY int not null,
-> DNO int not null,
-> primary key(EMO)
-> )default charset=utf8;
//添加数据
mysql> insert into EMP (ENAME,SALARY,DNO) values ("李四","1500","1"),("王五","400","2");
mysql> insert into EMP (ENAME,SALARY,DNO) values ("张三","800","2"),("刘秋","900","1");
// 查看数据内容
mysql> select * from EMP;
+-----+--------+--------+-----+
| ENO | ENAME | SALARY | DNO |
+-----+--------+--------+-----+
| 1 | 李四 | 1500 | 1 |
| 2 | 王五 | 400 | 2 |
| 3 | 张三 | 800 | 2 |
| 4 | 刘秋 | 900 | 1 |
+-----+--------+--------+-----+
4 rows in set (0.01 sec)
//创建表格
mysql> create table DEPT (
-> DNO int not null,
-> DNAME varchar(100) not null,
-> MANAGER int not null,
-> primary key(DNO)
-> )default charset=utf8;
//添加数据
mysql> insert into DEPT (DNO,DNAME,MANAGER) values (2,"销售部","3");
//查看数据内容
mysql> select * from DEPT;
+-----+-----------+---------+
| DNO | DNAME | MANAGER |
+-----+-----------+---------+
| 2 | 销售部 | 3 |
+-----+-----------+---------+
1 row in set (0.00 sec)回答：
1）select DNO,avg(SALARY) from EMP where SALARY>=600 group by DNO;
2）摘抄的答案不能执行，验证的答案在代码框里面。
UPDATE EMP SET SALARY=SALARY*1.1 WHERE ENO IN
(SELECT ENO FROM EMP, DEPT
WHERE EMP.DNO=DEPT.DNO AND DNAME='销售部'AND SALARY<600);
或者
UPDATE EMP SET SALERY=SALERY*1.1 FROM EMP A,DEPT B
WHERE A.DNO = B.DNO AND B.DNAME="销售部" AND B.SALARY<600;
参考的链接：
https://blog.youkuaiyun.com/Ck_Max/article/details/86502848
https://blog.youkuaiyun.com/longgeaisisi/article/details/90694573
https://blog.youkuaiyun.com/elangsun/article/details/90709870 （有用）
嵌套公式：
UPDATE 表X SET A = 1, B = 2 WHERE C IN (SELECT * FROM (SELECT C FROM 表Y WHERE D = 3) ALLINFO) AND E = 5; 标蓝处再添加一层可将里边的查询提前执行,避免N*N次这样的慢查询.
//语句一
mysql> UPDATE EMP SET SALARY=SALARY*1.1 WHERE ENO IN
-> (SELECT * from
-> (SELECT ENO FROM EMP,DEPT WHERE DEPT.DNO=EMP.DNO AND DNAME="销售部"
-> AND SALARY<600) ALLINFO);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from EMP;
+-----+--------+--------+-----+
| ENO | ENAME | SALARY | DNO |
+-----+--------+--------+-----+
| 1 | 李四 | 1500 | 1 |
| 2 | 王五 | 484 | 2 |
| 3 | 张三 | 800 | 2 |
| 4 | 刘秋 | 900 | 1 |
+-----+--------+--------+-----+
4 rows in set (0.00 sec)
//语句二
mysql> UPDATE EMP A,DEPT B SET SALARY=SALARY*1.1
-> WHERE A.DNO = B.DNO AND B.DNAME="销售部" AND A.SALARY<600;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp;
+-----+--------+--------+-----+
| ENO | ENAME | SALARY | DNO |
+-----+--------+--------+-----+
| 1 | 李四 | 1500 | 1 |
| 2 | 王五 | 532 | 2 |
| 3 | 张三 | 800 | 2 |
| 4 | 刘秋 | 900 | 1 |
+-----+--------+--------+-----+
4 rows in set (0.00 sec)
四、设职工---社团数据库有三个基本表：(20分)
职工（职工号，姓名，年龄，性别）；
社会团体（编号，名称，负责人，活动地点）；
参加（职工号，编号，参加日期）；
其中：
1）职工表的主键为职工号。
2）社会团体表的主键为编号；外码为负责人，被参照表为职工表，对应属性为职工号。
3）参加表的职工号和编号为主键；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。
试用SQL语句表达下列操作：
1）定义职工表、社会团体表和参加表，并说明其主键和参照关系。
2）建立下列两个视图。
社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；
参加人情况（职工号，姓名，社团编号，社团名称，参加日期）
3）查找参加唱歌队或篮球队的职工号和姓名。
4）求参加人数超过100人的社会团体的名称和负责人。
答：
1）定义职工表、社会团体表和参加表，并说明其主键和参照关系。
//1）职工表的主键为职工号。
mysql> CREATE TABLE 职工 (职工号 CHAR(10) NOT NULL,
-> 姓名 CHAR(8) NOT NULL,
-> 年龄 SMALLINT,
-> 性别 CHAR(2),
-> CONSTRAINT C1_PK PRIMARY KEY (职工号)
-> );
Query OK, 0 rows affected (0.01 sec)
//2）社会团体表的主键为编号；外码为负责人，被参照表为职工表，对应属性为职工号。
mysql> CREATE TABLE 社会团体(
-> 编号 CHAR(8) NOT NULL,
-> 名称 CHAR(12) NOT NULL,
-> 负责人 CHAR(8),
-> 活动地点 VARCHAR(50),
-> CONSTRAINT C2_PK PRIMARY KEY (编号),
-> CONSTRAINT C2_FK FOREIGN KEY (负责人) REFERENCES 职工(职工号)
-> );
Query OK, 0 rows affected (0.02 sec)
//3）参加表的职工号和编号为主键；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。
mysql> CREATE TABLE 参加 (
-> 职工号 CHAR(8),
-> 编号 CHAR(8),
-> 参加日期 DATE,
-> CONSTRAINT C3_PK PRIMARY KEY(职工号,编号),
-> CONSTRAINT C3_FK_Z FOREIGN KEY(职工号) REFERENCES 职工(职工号),
-> CONSTRAINT C3_FK_B FOREIGN KEY(编号) REFERENCES 社会团体(编号)
-> );
Query OK, 0 rows affected (0.02 sec)
2）建立下列两个视图。
//社团负责人（编号,名称,负责人职工号,负责人姓名,负责人性别);
mysql> CREATE VIEW 社团负责人 (编号,名称,负责人职工号,负责人姓名,负责人性别)
-> AS SELECT 编号,名称,负责人,姓名,性别
-> FROM 社会团体,职工
-> WHERE 社会团体.负责人=职工.职工号;
Query OK, 0 rows affected (0.02 sec)
//参加人情况（职工号,姓名,社团编号,社团名称,参加日期);
mysql> CREATE VIEW 参加人情况 (职工号,姓名,社团编号,社团名称,参加日期)
-> AS SELECT 参加.职工号,姓名,社会团体.编号,名称,参加日期
-> FROM 职工,社会团体,参加
-> WHERE 职工.职工号=参加.职工号 AND 参加.编号=社会团体.编号;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+--------------------------+
| Tables_in_cre_data_mysql |
+--------------------------+
| 参加 |
| 参加人情况 |
| 社会团体 |
| 社团负责人 |
| 职工 |
| DEPT |
| EMP |
| alter_tbl |
| clone_import_vegetables |
| import_vegetables |
| mysql_vegetables |
| workers |
+--------------------------+
12 rows in set (0.00 sec)
3）查找参加唱歌队或篮球队的职工号和姓名。
4）求参加人数超过100人的社会团体的名称和负责人。
扫一扫
1512
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
