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;