sql基础语句02

8、显示文章标题,发帖人,最后回复时间

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,'第一条','张三','1988-10-10 12:32:32',NULL),
(NULL,'第二条','张三','1988-10-10 12:34:32', NULL),
(NULL,'第二条回复1','张三','1988-10-10 12:36:32',1),
(NULL,'第二条回复2','李四','1988-10-10 12:37:32',1),
(NULL,'第一条回复1','张三','1988-10-10 12:39:32',1);

SELECT a.title,a.postuser,
    (SELECT MAX(postdate) FROM articles WHERE parentid = a.id)reply
    FROM articles a WHERE a.parentid IS NULL;

9、删除除了ID号不同，其他否相同的学生冗余信息

CREATE TABLE student2(id INT AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(20),NAME VARCHAR(20));
INSERT INTO student2 VALUES
(NULL,'2005001','张三'),
(NULL,'2005002','李四'),
(NULL,'2005001','张三');

SELECT * FROM student2 WHERE id NOT IN(SELECT MIN(id) FROM student2 GROUP BY NAME);

把分组的结果做出虚表，然后从虚表中选出结果，最后将结果作为删除的条件数据

DELETE FROM student2 WHERE id NOT IN( SELECT mids FROM (SELECT MIN(id) mids FROM student2 GROUP BY NAME)AS t);

mysql报错，删除依赖后面的语句，而删除有导致统计语句结果不一致

DELETE FROM student2 WHERE id NOT IN(SELECT MIN(id) mids FROM student2 GROUP BY NAME);

9、航空网的航班

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 flight VALUES
(NULL,1,2,'9:37:23'),
(NULL,1,3,'9:37:23'),
(NULL,1,2,'10:37:23'),
(NULL,2,3,'10:37:23');

INSERT INTO city VALUES
(NULL,'北京'),
(NULL,'上海'),
(NULL,'广州');

查询起飞城市是北京的所有航班，按到达城市的名字排序

SELECT * FROM flight f, city c
    WHERE f.endCityID = c.cityID AND StartCityID =
    (SELECT c1.cityID FROM city c1 WHERE c1.cityName = "北京")
    ORDER BY c.cityName ASC;

SELECT * FROM flight f, city c
    WHERE f.endCityID = c.cityID AND f.StartCityID =
    (SELECT cityID FROM city WHERE cityName = "北京")
    ORDER BY c.cityName ASC;

查询北京到上海的所有航班记录（起飞城市，到达城市，起飞时间，航班号）

SELECT c1.cityName,c2.cityname, f.startcityid,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 = "上海"

10、求出小于45岁的各个老师所带的大于12岁的学生人数

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,"dd",45),(2,"f",25),
(3,"t",26),(4,"tg",27);

INSERT INTO student VALUES
(1,"ww",11),(2,"s",25),
(3,"te",26),(4,"mxc",27);

INSERT INTO tea_stu VALUES
(1,1),(1,2),(1,3),(2,2),(2,3),(2,4),(3,3),
(3,4),(3,1),(4,4),(4,1),(4,2),(4,3);

SELECT teaid,COUNT(*) FROM tea_stu GROUP BY teaid;

找到学生年龄大于12岁的学生以老师ID进行分组

SELECT tea_stu.teaID,COUNT(*) total FROM student, tea_stu
    WHERE student.stuID = tea_stu.stuID AND student.age > 12 GROUP BY tea_stu.teaID

将上面的查询结果作为一个虚表tea_stu2，然后查询老师的ID，姓名和符合要求的学生

SELECT teacher.teaID, teacher.name, total FROM teacher, 
    (
    SELECT tea_stu.teaID,COUNT(*) total FROM student, tea_stu
    WHERE student.stuID = tea_stu.stuID AND student.age > 12 GROUP BY tea_stu.teaID
    )AS tea_stu2 
    WHERE teacher.teaID = tea_stu2.teaid AND teacher.age<45;

11、查出比经理薪水还高的员工信息

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

INSERT INTO employees VALUES
(NULL,"log",10000,NULL),
(NULL,"dd",15000,1),
(NULL,"da",10000,2),
(NULL,"fs",10000,3);

SELECT e.* FROM employees e,employees m WHERE e.managerid = m.id AND
    e.salary>m.salary;