sql语句

来源：互联网发布：淘宝客招代理话术编辑：程序博客网时间：2024/06/06 05:36

目录(?)[-]
题目一有关内连接左右连接最大最小值
题目二关于排序和模糊查询
单表多表查询
重复数据问题
海量数据库优化方法

题目一：有关内连接、左、右连接、最大最小值

有两张表：部门表department 部门编号dept_id 部门名称dept_name
员工表employee 员工编号emp_id 员工姓名emp_name 部门编号dept_id 工资emp_wage
根据下列题目写出sql：

1、列出工资大于5000的员工所属的部门名、员工id和员工工资； 2、列出员工表中的部门id对应的名称和员工id（左连接） 3、列出员工大于等于2人的部门编号 4、列出工资最高的员工姓名 5、求各部门的平均工资 6、求各部门的员工工资总额 7、求每个部门中的最大工资值和最小工资值，并且它的最小值小于5000，最大值大于10000 8、假如现在在库中有一个和员工表结构相同的空表employee2,请用一条sql语句将employee表中的所以记录插入到employee2表中。

answer:

1:列出工资大于5000的员工所属的部门名、员工id和员工工资；

select emp_id,emp_wage,dept_name from employee as e inner join department as d on e.dept_id=d.dept_id where e.emp_wage>5000 group by e.emp_id;

2:列出员工表中的部门id对应的名称和员工id（左连接）

select dept_name,emp_id from department d left join employee e on e.dept_id=d.dept_id group by e.emp_id;

+------------+--------+
| dept_name | emp_id |
+------------+--------+
| 咨询部     | NULL   |
| 软件开发部 |   1001 |
| 市场策划部 |   1002 |
| 销售部     |   1003 |
| HR         |   1004 |
| HR         |   1005 |
| HR         |   1006 |
| 软件开发部 |   1007 |
+------------+--------+

3：列出员工大于等于2人的部门编号

select dept_name from department d [inner] join employee e on d.dept_id=e.dept_id group by dept_name
having count(e.dept_id) >=2;

4:列出工资最高的员工姓名

select * from employee
where emp_wage =(select max(emp_wage) from employee);

+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1007 | ad | 1 | 12000 |
+--------+----------+---------+----------+

5: 求各部门的平均工资

select dept_name,AVG(emp_wage) as '平均工资' from employee e join department d on e
.dept_id=d.dept_id Group by dept_name;

+------------+------------+
| dept_name | 平均工资   |
+------------+------------+
| HR         | 7500.0000 |
| 市场策划部 | 2500.0000 |
| 软件开发部 | 10000.0000 |
| 销售部     | 3200.0000 |
+------------+------------+

如果用右连接：

select dept_name,AVG(emp_wage) as '平均工资' from employee e right
join department d on e.dept_id=d.dept_id Group by dept_name;

+------------+------------+
| dept_name | 平均工资   |
+------------+------------+
| HR         | 7500.0000 |
| 咨询部     | NULL       |
| 市场策划部 | 2500.0000 |
| 软件开发部 | 10000.0000 |
| 销售部     | 3200.0000 |
+------------+------------+

6：求各部门的员工工资总额

select dept_name,SUM(emp_wage) as '部门工资总额' from employee e join department d on e.dept_id=d.dept_id
Group by dept_name;

7：求每个部门中的最大工资值和最小工资值，并且它的最小值小于5000，最大值大于10000

select dept_name ,max(emp_wage)from employee e inner join department d on e.dept_id=d.dept_id
where emp_wage>=10000
group by dept_name
union all
select dept_name ,
min(emp_wage) from employee e inner join department d on e.dept_id=d.dept_id
where emp_wage<=5000
group by dept_name;

如果是求每个部门中的最大工资值和最小工资值-->

select dept_name ,max(emp_wage),min(emp_wage) from employee e inner join department d on e.dept_id=d.dept_id group by dept_name;

8：假如现在在库中有一个和员工表结构相同的空表employee2,请用一条sql语句将employee表中的所以记录插入到employee2表中。

insert into employee2 select * from employee;

题目二：关于排序和模糊查询

一、给定一张表1，根据要求进行排序：

+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
|   1001 | toni     |       1 |    32000 |
|   1002 | lMcy     |       3 |     2500 |
|   1003 | toM      |       4 |     3200 |
|   1004 | mamy     |       2 |     9500 |
+--------+----------+---------+----------+

1.根据emp_wage，员工工资升序排序：MySQL> select * from employee order by emp_wage ASC; （注：ASC=ascending升序）

+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
|   1002 | lMcy     |       3 |     2500 |
|   1003 | toM      |       4 |     3200 |
|   1004 | mamy     |       2 |     9500 |
|   1001 | toni     |       1 |    32000 |
+--------+----------+---------+----------+

2.根据emp_id降序排序：mysql> select * from employee order by emp_id DESC; （注：DESC=descending降序）

+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
|   1004 | mamy     |       2 |     9500 |
|   1003 | toM      |       4 |     3200 |
|   1002 | lMcy     |       3 |     2500 |
|   1001 | toni     |       1 |    32000 |
+--------+----------+---------+----------+

二、根据以上表1，根据要求进行模糊查询

1、不区分大小写查询员工名中含有m的员工资料：mysql> select * from employee where emp_name like '%m%';

2、查询员工名中含有大写M的员工资料：mysql> select * from employee where emp_name like binary('%M%');

3、查询员工名中以“t”打头以“i”结尾的员工资料：mysql> select * from employee where emp_name like 't%' and emp_name like '%i';

+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1001 | toni | 1 | 32000 |
+--------+----------+---------+----------+

4、查询员工名中第三位是大写“M”的员工资料：mysql> select * from employee where emp_name like binary('%_ _M%');

+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1003 | toM | 4 | 3200 |
+--------+----------+---------+----------+

单表、多表查询

一、给定一张学生成绩表tb_grade：学号，姓名，科目号，科目名，分数。（如下表）

（select * from tb_grade order by Sno,Cno;）
+------+-------+-----+-------+-------+
| Sno | Sname | Cno | Cname | score |
+------+-------+-----+-------+-------+
| 1001 | 李菲 | 1 | 语文 | 86 |
| 1001 | 李菲 | 2 | 数学 | 56 |
| 1001 | 李菲 | 3 | 物理 | 48 |
| 1001 | 李菲 | 4 | 化学 | 90 |
| 1001 | 李菲 | 5 | 英语 | 57 |
| 1002 | 王琪 | 1 | 语文 | 90 |
| 1002 | 王琪 | 2 | 数学 | 68 |
| 1002 | 王琪 | 3 | 物理 | 87 |
| 1002 | 王琪 | 4 | 化学 | 68 |
| 1002 | 王琪 | 5 | 英语 | 45 |
| 1003 | 杨阳 | 1 | 语文 | 86 |
| 1003 | 杨阳 | 2 | 数学 | 56 |
| 1003 | 杨阳 | 3 | 物理 | 48 |
| 1003 | 杨阳 | 4 | 化学 | 90 |
| 1003 | 杨阳 | 5 | 英语 | 52 |
| 1004 | 和树 | 1 | 语文 | 82 |
| 1004 | 和树 | 2 | 数学 | 56 |
| 1004 | 和树 | 3 | 物理 | 67 |
| 1004 | 和树 | 4 | 化学 | 30 |
| 1004 | 和树 | 5 | 英语 | 68 |
+------+-------+-----+-------+-------+

1、查询不及格科目数大于等于2的学生学号和不及格科目数量：

MySQL> select Sno,count(score) as '不及格科目数' from tb_grade where score<60 group by Sno having count(score)>=2;

+------+--------------+
| Sno | 不及格科目数 |
+------+--------------+
| 1001 | 3 |
| 1003 | 3 |
| 1004 | 2 |
+------+--------------+

2、查询不及格科目数大于等于2的学生学号和学生姓名：

MySQL> select Sno,Sname from tb_grade where score<60 group by Sno having count(score)>=2;

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

| Sno | Sname |
+------+-------+
| 1001 | 李菲 |
| 1003 | 杨阳 |
| 1004 | 和树 |
+------+-------+

3、查询不及格科目数大于等于2的学生学号、学生姓名、科目号、科目名称和分数，并按学号降序、科目号升序排序：

MySQL> select * from tb_grade where score<60 and Sno in( select Sno from tb_grade where score<60 group by Sno having count(score)>=2 ) order by Sno desc,Cno asc;

+------+-------+-----+-------+-------+
| Sno | Sname | Cno | Cname | score |
+------+-------+-----+-------+-------+
| 1004 | 和树 | 2 | 数学 | 56 |
| 1004 | 和树 | 4 | 化学 | 30 |
| 1003 | 杨阳 | 2 | 数学 | 56 |
| 1003 | 杨阳 | 3 | 物理 | 48 |
| 1003 | 杨阳 | 5 | 英语 | 52 |
| 1001 | 李菲 | 2 | 数学 | 56 |
| 1001 | 李菲 | 3 | 物理 | 48 |
| 1001 | 李菲 | 5 | 英语 | 57 |
+------+-------+-----+-------+-------+

二、有三张表：class、student、score

班级表 class：

+-------+---------+---------+
| classID | className |
+---------+-----------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
+---------+-----------+

学生表 student：

+-------+---------+---------+
| stuID | classID | stuName |
+-------+---------+---------+
| 1001 | 1 | 张三 |
| 1002 | 1 | 李丽 |
| 1003 | 1 | 钱封 |
| 1004 | 2 | 杨国 |
| 1005 | 2 | 小样 |
| 1006 | 2 | 区天 |
| 1007 | 3 | 李三宅 |
| 1008 | 3 | 黄武 |
| 1009 | 3 | 赵六 |
+-------+---------+---------+

分数表 score：

+----------+-------+--------+-------+
| courseID | stuID | course | score |
+----------+-------+--------+-------+
| 2 | 1001 | 数学 | 73 |
| 3 | 1001 | 英语 | 79 |
| 1 | 1001 | 语文 | 81 |
| 3 | 1002 | 英语 | 87 |
| 2 | 1002 | 数学 | 83 |
| 1 | 1002 | 语文 | 79 |
| 1 | 1003 | 语文 | 65 |
| 3 | 1003 | 英语 | 65 |
| 2 | 1003 | 数学 | 97 |
| 1 | 1004 | 语文 | 78 |
| 3 | 1004 | 英语 | 78 |
| 2 | 1004 | 数学 | 86 |
| 1 | 1005 | 语文 | 67 |
| 3 | 1005 | 英语 | 88 |
| 2 | 1005 | 数学 | 89 |
| 2 | 1006 | 数学 | 90 |
| 3 | 1006 | 英语 | 92 |
| 1 | 1006 | 语文 | 98 |
| 1 | 1007 | 语文 | 85 |
| 2 | 1007 | 数学 | 78 |
| 3 | 1007 | 英语 | 72 |
| 1 | 1008 | 语文 | 78 |
| 3 | 1008 | 英语 | 77 |
| 2 | 1008 | 数学 | 85 |
| 3 | 1009 | 英语 | 94 |
| 2 | 1009 | 数学 | 91 |
| 1 | 1009 | 语文 | 68 |
+----------+-------+--------+-------+

1、查询各班各科分数最高的学生学号，姓名，班级名称，科目名称，分数：

MySQL>

select stu.stuID,stu.stuName,c.classID,c.className, sc.score from class c,student stu,
score sc where c.classID=stu.classID and stu.stuID=sc.stuID order by c.classID,stu.stuID;

重复数据问题

题一:表stu

+----+------+-----+-------+
| id | sno | cno | score |
+----+------+-----+-------+
| 1 | 1001 | 1 | 89 |
| 3 | 1001 | 1 | 89 |
| 2 | 1002 | 1 | 87 |
| 4 | 1002 | 1 | 90 |
| 5 | 1003 | 1 | 86 |
+----+------+-----+-------+

1、查询出sno,cno重复的数据：

> select * from rs where (sno,cno) in (select sno,cno from rs group by sno,cno having count(*)>1);

2、查询sno,cno,score都重复的数据，且除id最小的一条数据外：

> select * from rs where (sno,cno,score) in(select sno,cno,score from rs group by sno,cno,score having count(*)>1)and id not in (select min(id) from rs group by sno,cno,score having count(*)>1);

3、删除sno,cno,score都重复的数据，保留id最小的一条数据：

>（SQL Server未验证）delete from rs where (sno,cno,score) in(select sno,cno,score from rs group by sno,cno,score having count(*)>1)and id not in (select min(id) from rs group by sno,cno,score having count(*)>1);

>（MySQL）

create table temp as select * from rs where (sno,cno,score) in(select sno,cno,score from rs group by sno,cno,score having count(*)>1)and id not in (select min(id) from rs group by sno,cno,score having count(*)>1);

delete from rs where id in(select id from temp);

drop table temp;

海量数据库优化方法？

现在有一张正在使用的表，数据量为1000W，你如何做优化？

1、在查询概率高的字段上建立索引；

2、将表分区，如按月份分12个区；

3、优化SQL语句；

4、控制查询条件；

5、定期备份数据库，将过去一段时间内的数据清除。

目录(?)[-]
题目一有关内连接左右连接最大最小值
题目二关于排序和模糊查询
单表多表查询
重复数据问题
海量数据库优化方法