SQL语句加强

这里是我总结的SQL语句练习

第一个突破点

表准备
CREATE TABLE employee (
    id INT PRIMARY KEY auto_increament, -- 主键
    name VARCHAR (50), -- 名字
    salary BIGINT,  -- 薪水
    depid INT  -- 部门id
)

用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。

SELECT * FROM employee ORDER BY depId DESC, salary ASC

列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序

 方法一
 SELECT COUNT(*),depId FROM
  ( SELECT * FROM employee e1   WHERE ( SELECT AVG(e2.salary) FROM employee e2
 WHERE e2.depId = e1.depId ) < e1.salary ORDER BY e1.depId ) e3 GROUP BY depId

方法二（关联查询）
SELECT COUNT(*),e1.depId FROM employee e1 LEFT JOIN ( SELECT AVG(e2.salary) avge, e2.depId FROM
 employee e2 GROUP BY e2.depId ) e3 ON e1.depId = e3.depId WHERE e1.salary > e3.avge GROUP BY e1.depId;

第二个突破点

表准备
CREATE TABLE score (
    id INT auto_increment PRIMARY KEY,
    name VARCHAR(10),
    cource VARCHAR (10),
    score int(3)
)
INSERT INTO score(id,name,cource,score) VALUES (null,'张三','语文',81),(null,'张三','数学',75),
(null,'李四','语文',76),(null,'李四','数学',90),
(null,'王五','语文',81),(null,'王五','数学',100),
(null,'王五 ','英语',90);
我发现一个奇怪的地方，在mysql数据库插入语句的时候，可以有into关键字，也可以不要。

用一条SQL语句 查询出每门课都大于80分的学生姓名
反向查询，但是效率不是很高 
SELECT name FROM score sc WHERE sc.name not in (SELECT s.name FROM score s WHERE s.score<=80) GROUP BY sc.name
正向查询
SELECT name,min(sc.score) msc FROM score sc  GROUP BY name HAVING msc>80;

第三个突破点

一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.
SELECT a.name,b.name FROM department a,department b WHERE a.name<b.name

第四个突破点

表准备
DROP TABLE if EXISTS testDb;
CREATE TABLE testDb(
    id int  PRIMARY KEY AUTO_INCREMENT,
    accId VARCHAR(10),
    occMonth DATE,
    debitOccur BIGINT
)
INSERT INTO testDb VALUES(NULL,'101','1988-1-1',100),
(null,'101','1988-2-1',110),
(null,'101','1988-3-1',120),
(null,'101','1988-4-1',100),
(null,'101','1988-5-1',100),
(null,'101','1988-6-1',100),
(null,'101','1988-7-1',100),
(null,'101','1988-8-1',100);
我发现PRIMARY KEY和auto_increment可以交换位置的，以前都没有注意这种细节
第一种
SELECT a.accId FROM testDb a,(SELECT * FROM testDb c WHERE c.accId='101') b  WHERE a.debitOccur >b.debitOccur
  AND a.occMonth=b.occMonth GROUP BY a.accId
第二种
SELECT a.accId FROM testDb a,testDb b  WHERE a.debitOccur >b.debitOccur AND b.accId='101'
  AND a.occMonth=b.occMonth GROUP BY a.accId

我不知道那种效率要快一点，还有group by 和distint 这两种那个要快点，好像是group by吧，如果谁知道告诉我一下，就非常感谢了。

第五个突破点

year  month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1  m2  m3  m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

SELECT (SELECT a1.amount FROM salary AS a1 WHERE a1.years=a.years AND a1.months='1'),
       (SELECT a2.amount FROM salary AS a2 WHERE a2.years=a.years AND a2.months='2'),
            (SELECT a3.amount FROM salary AS a3 WHERE a3.years=a.years AND a3.months='3'),
            (SELECT a4.amount FROM salary AS a4 WHERE a4.years=a.years AND a4.months='4')
FROM salary a GROUP BY a.years  ORDER BY a.years 

第六个突破点

DROP TABLE if EXISTS articles;
CREATE TABLE articles (
    id INT auto_increment PRIMARY KEY,
    title VARCHAR (50),
    postuser VARCHAR (10),
    postdate datetime,
    parentid INT REFERENCES articles (id)
)
insert into articles values
(null,'第一条','张三','1998-10-10 12:32:32',null),
(null,'第二条','张三','1998-10-10 12:34:32',null),
(null,'第一条回复1','李四','1998-10-10 12:35:32',1),
(null,'第二条回复1','李四','1998-10-10 12:36:32',2),
(null,'第一条回复2','王五','1998-10-10 12:37:32',1),
(null,'第一条回复3','李四','1998-10-10 12:38:32',1),
(null,'第二条回复2','李四','1998-10-10 12:39:32',2),
(null,'第一条回复4','王五','1998-10-10 12:39:40',1);

SELECT title,postuser,(SELECT MAX(a.postdate) FROM articles a WHERE a.parentid=b.id ) FROM articles b  WHERE 
b.parentid is null;

第七个突破点

学生表 如下:
id号   学号   姓名 课程编号 课程名称 分数
1        2005001  张三  0001      数学    69
2        2005002  李四  0001      数学    89
3        2005001  张三  0001      数学    69

删除除了id号不同,其他都相同的学生冗余信息
DELETE FROM  t_student  WHERE id not in (SELECT id FROM (SELECT min(t.id) AS id FROM t_student t 
GROUP BY t.num,t.tname,t.tcode,t.course,t.score) as re
)

第八个突破点

CREATE TABLE city (
    cityID INT auto_increment PRIMARY KEY,
    cityName VARCHAR (20)
);

CREATE TABLE flight (
    flightID INT auto_increment PRIMARY KEY,
    StartCityID INT REFERENCES city (cityID),
    endCityID INT REFERENCES city (cityID),
    StartTime TIMESTAMP
);

insert into city values(null,'北京'),(null,'上海'),(null,'广州');
insert into flight values(null,1,3,'2002-11-14 9:37:23'),(null,1,2,'2002-11-14 10:37:23')
,(null,2,3,'2002-11-14 10:37:23');


查询起飞城市是北京的所有航班，按到达城市的名字排序
SELECT * FROM flight f,city c WHERE  f.endcityid = c.cityid AND f.startcityid =(
 SELECT c1.cityid FROM city c1 WHERE c1.cityname = "北京"
) ORDER BY c.cityname asc;

查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）
select c1.CityName,c2.CityName,f.StartTime,f.flightID
from city c1,city c2,flight f
where f.StartCityID=c1.cityID 
and f.endCityID=c2.cityID
and c1.cityName='北京'
and c2.cityName='上海'

第九个突破点

CREATE TABLE employees (
    id INT PRIMARY KEY auto_increment,
    NAME VARCHAR (50),
    salary INT,
    managerid INT REFERENCES employees (id)
)

insert into employees values (null,' lhm',10000,null), (null,' zxx',15000,1
),(null,'flx',9000,1),(null,'tg',10000,2),(null,'wzg',10000,3);

查出比经理薪水还高的员工信息
SELECT
    e1.*
FROM
    employees e1
INNER JOIN employees e2 ON e1.managerid = e2.id
WHERE
    e1.salary > e2.salary

第十个突破点

drop table if exists tea_stu;
    drop table if exists teacher;
    drop table if exists student;
      create table teacher(teaID int primary key,name varchar(50),age int);
      create table student(stuID int primary key,name varchar(50),age int);
      create table tea_stu(teaID int references teacher(teaID),stuID int references student(stuID));
insert into teacher values(1,'zxx',45), (2,'lhm',25) , (3,'wzg',26) , (4,'tg',27);
insert into student values(1,'wy',11), (2,'dh',25) , (3,'ysq',26) , (4,'mxc',27);
insert into tea_stu values(1,1), (1,2), (1,3);
insert into tea_stu values(2,2), (2,3), (2,4);
 insert into tea_stu values(3,3), (3,4), (3,1);
insert into tea_stu values(4,4), (4,1), (4,2) , (4,3);

第一种方法
SELECT t.name,t.age,COUNT(t.name) FROM teacher t LEFT JOIN tea_stu ts ON t.teaId=ts.teaId
   LEFT JOIN student s ON ts.stuId = s.stuID WHERE t.age <45 AND s.age >12 GROUP BY t.name

第二种方法
SELECT t.NAME, t.age, ( SELECT COUNT(s.stuID) FROM tea_stu ts LEFT JOIN student s ON ts.stuId = s.stuID
WHERE s.age > 12 AND t.teaId=ts.teaId ) total FROM teacher t WHERE t.age < 45 GROUP BY t.name

第十一个突破点

求出发帖最多的人
  SELECT postuser, COUNT(*) AS total FROM articles GROUP BY
    postuser HAVING total = ( SELECT MAX(total2) FROM (SELECT
 count(*) total2 FROM articles  GROUP BY postuser) AS t )

第十一个突破点

一个用户表中有一个积分字段，假如数据库中有100多万个用户，若要在每年第一天凌晨将积分清零，你将考虑什么，你将想什么办法解决?
//ALTER TABLE articles ADD COLUMN parentid INT;
第一种
ALTER TABLE articles DROP COLUMN parentid;
第二种
update user set score=0; 

第十二个突破点

一个用户具有多个角色，请查询出该表中具有该用户的所有角色的其他用户。
先是求出这个用户所有角色的数据，再通过分组，求总数等于这个用户
select count(*) as num,tb.id 
from 
 tb,
 (select role from tb where id=xxx) as t1
where
 tb.role = t1.role and tb.id != t1.id
group by tb.id 
having 
    num = select count(role) from tb where id=xxx;

第十三个突破点

create Table EMPLOYEES （
EMPLOYEE_ID      NUMBER        Primary Key,
FIRST_NAME       VARCHAR2(25),
LAST_NAME       VARCHAR2(25),
Salary number(8,2),
HiredDate DATE,
Departmentid number(2)
）

 create Table Departments(
Departmentid number(2)        Primary Key,
DepartmentName  VARCHAR2(25)
)

基于上述EMPLOYEES表写出查询：写出雇用日期在今年的，或者工资在[1000,2000]之间的，或者员工姓名（last_name）以’Obama’打头的所有员工，列出这些员工的全部个人信息。
select * from employees 
where Year(hiredDate) = Year(date()) 
    or (salary between 1000 and 200)
    or left(last_name,3)='abc';

基于上述EMPLOYEES表写出查询：查出部门平均工资大于1800元的部门的所有员工，列出这些员工的全部个人信息。
select id,name,salary,deptid did from employee1 where (select avg(salary)
 from employee1 where deptid = did) > 1800;

基于上述EMPLOYEES表写出查询：查出个人工资高于其所在部门平均工资的员工，列出这些员工的全部个人信息及该员工工资高出部门平均工资百分比。
select employee1.*,(employee1.salary-t.avgSalary)*100/employee1.salary 
from employee1,
    (select deptid,avg(salary) avgSalary from employee1 group by deptid) as t
where employee1.deptid = t.deptid and employee1.salary>t.avgSalary;