mysql学习03

https://www.cnblogs.com/wupeiqi/articles/5729934.html

https://www.cnblogs.com/wupeiqi/articles/5748496.html

MySQL测试题

一、表关系

请创建如下表，并创建相关约束

Microsoft Windows [版本 10.0.14393]
(c) 2016 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>mysql -u root -P3306 -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

C:\Users\Administrator>mysql -u root -P3306 -h 127.0.0.1 -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaige              |
| mysql              |
| performance_schema |
| runbs              |
| sdhz               |
| sunck              |
| test               |
+--------------------+
8 rows in set (0.17 sec)

mysql> create database sql_test
    -> ;
Query OK, 1 row affected (0.07 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaige              |
| mysql              |
| performance_schema |
| runbs              |
| sdhz               |
| sql_test           |
| sunck              |
| test               |
+--------------------+
9 rows in set (0.00 sec)

mysql> use sql_test;
Database changed
mysql> drop database sql_test;
Query OK, 0 rows affected (0.21 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaige              |
| mysql              |
| performance_schema |
| runbs              |
| sdhz               |
| sunck              |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql> create database sql_test default charset utf-8 collate utf8_general_ci;
ERROR 1115 (42000): Unknown character set: 'utf'
mysql> create database sql_test default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaige              |
| mysql              |
| performance_schema |
| runbs              |
| sdhz               |
| sql_test           |
| sunck              |
| test               |
+--------------------+
9 rows in set (0.00 sec)

mysql> use sql_test;
Database changed
mysql> show tables;
Empty set (0.05 sec)

mysql> create table class(
    -> cid int not null auto_increment primary key,
    -> caption varchar(25)
    -> )engine=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

mysql> show tables;
+--------------------+
| Tables_in_sql_test |
+--------------------+
| class              |
+--------------------+
1 row in set (0.00 sec)

mysql> desr class
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desr class' at line 1
mysql> desc class;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| cid     | int(11)     | NO   | PRI | NULL    | auto_increment |
| caption | varchar(25) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.09 sec)

mysql> create table teacher(
    -> tid int auto_increment primary key not null,
    -> tname varchar(25),
    -> )engine=InnoDB default charset=utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')engine=InnoDB default charset=utf8' at line 4
mysql> create table teacher(
    -> tid int auto_increment primary key not null,
    -> tname varchar(25)
    -> )engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> desc teacher
    -> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| tid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| tname | varchar(25) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> create table student(
    -> sid int auto_increment primary key not null,
    -> sname varchar(25),
    -> gender varchar(2),
    -> class_id int
    -> )engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> alter table class add constraint FK_class_student foreign key class.cid references student.class_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'references student.class_id' at line 1
mysql> show tables;
+--------------------+
| Tables_in_sql_test |
+--------------------+
| class              |
| student            |
| teacher            |
+--------------------+
3 rows in set (0.00 sec)

mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| sid      | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname    | varchar(25) | YES  |     | NULL    |                |
| gender   | varchar(2)  | YES  |     | NULL    |                |
| class_id | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> desc class;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| cid     | int(11)     | NO   | PRI | NULL    | auto_increment |
| caption | varchar(25) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> alter table student add constraint fk_stu_cls foreign key student(class_id) references class(cid);
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| sid      | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname    | varchar(25) | YES  |     | NULL    |                |
| gender   | varchar(2)  | YES  |     | NULL    |                |
| class_id | int(11)     | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> create table student(
    -> sid int not null primary key auto_increment,
    -> sname varchar(25),
    -> gender varchar(25),
    -> class_id;;;;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
ERROR:
No query specified

ERROR:
No query specified

ERROR:
No query specified

mysql> create table course9
    -> create table course9；；
    ->
    -> ‘
    -> ；
    ->
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table course9????

??
??

)' at line 2
mysql> create table course(
    -> cid int not null primary key auto_increment,
    -> cname varchar(25),
    -> tearch_id int
    -> )engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> alter table course add constraint fk_cour_teach foreign key course(tearch_id) references teacher(tid);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table score(
    -> sid int not null primary key auto_increment,
    -> student_id int not null,
    -> corse_id int not null,
    -> number int not null
    -> )engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.13 sec)

mysql> alter table score add constraint score_to_stu foreign key score(student_id) references student(sid);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table score add constraint score_to_corse foreign key score(corse_id) references course(cid);
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

创建语句

mysql> select
    -> *
    -> from
    -> score
    -> left join student on score.student_id=student.sid
    -> left join course on score.corse_id=course.cid;
+-----+------------+----------+--------+------+-------+--------+----------+------+---------+-----------+
| sid | student_id | corse_id | number | sid  | sname | gender | class_id | cid  | cname   | tearch_id |
+-----+------------+----------+--------+------+-------+--------+----------+------+---------+-----------+
|   1 |          1 |        1 |     60 |    1 | may   | nv     |        1 |    1 | shengwu |         1 |
|   2 |          1 |        2 |     59 |    1 | may   | nv     |        1 |    2 | tiyu    |         1 |
|   3 |          2 |        2 |    100 |    2 | tom   | na     |        1 |    2 | tiyu    |         1 |
+-----+------------+----------+--------+------+-------+--------+----------+------+---------+-----------+
3 rows in set (0.06 sec)

mysql> select
    -> score.sid,
    -> score.student_id,
    -> student.sname,
    -> score.corse_id,
    -> course.cname,
    -> score.name
    -> from
    -> left join student on score.student_id=student.sid
    -> left join course on score.corse_id=course.cid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join student on score.student_id=student.sid
left join course on score.cors' at line 9
mysql> select
    -> score.sid,
    -> score.student_id,
    -> student.sname,
    -> score.corse_id,
    -> course.cname,
    -> score.number
    -> from
    -> left join student on score.student_id=student.sid
    -> left join course on score.corse_id=course.cid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join student on score.student_id=student.sid
left join course on score.cors' at line 9
mysql> select score.sid,score.student_id,student.sname,score.corse_id,course.cname,score.number from
    -> left join student on score.student_id=student.sid leftjoin course on course.cid=score.corse_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join student on score.student_id=student.sid leftjoin course on course.cid=' at line 2
mysql> select * from
    -> left join student on score.student_id=student.sid leftjoin course on course.cid=score.corse_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join student on score.student_id=student.sid leftjoin course on course.cid=' at line 2
mysql>

View Code

mysql> select * from
    -> score
    -> left join student on score.student_id=student.sid
    -> left join course on score.corse_id=course.cid;
+-----+------------+----------+--------+------+-------+--------+----------+------+---------+-----------+
| sid | student_id | corse_id | number | sid  | sname | gender | class_id | cid  | cname   | tearch_id |
+-----+------------+----------+--------+------+-------+--------+----------+------+---------+-----------+
|   1 |          1 |        1 |     60 |    1 | may   | nv     |        1 |    1 | shengwu |         1 |
|   2 |          1 |        2 |     59 |    1 | may   | nv     |        1 |    2 | tiyu    |         1 |
|   3 |          2 |        2 |    100 |    2 | tom   | na     |        1 |    2 | tiyu    |         1 |
+-----+------------+----------+--------+------+-------+--------+----------+------+---------+-----------+
3 rows in set (0.00 sec)

mysql> select
    -> score.sid,
    -> score.student_id,
    -> student.sname,
    -> score.corse_id,
    -> course.cname,
    -> score.number
    -> from score
    -> left join student on score.student_id=student.sid
    -> left join course on score.corse_id=course.cid;
+-----+------------+-------+----------+---------+--------+
| sid | student_id | sname | corse_id | cname   | number |
+-----+------------+-------+----------+---------+--------+
|   1 |          1 | may   |        1 | shengwu |     60 |
|   2 |          1 | may   |        2 | tiyu    |     59 |
|   3 |          2 | tom   |        2 | tiyu    |    100 |
+-----+------------+-------+----------+---------+--------+
3 rows in set (0.00 sec)

mysql>

View Code

二、操作表

1、自行创建测试数据

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

3、查询平均成绩大于60分的同学的学号和平均成绩；

-- SELECT MAX(cid) FROM class;
-- SELECT sum(cid) FROM class;
-- SELECT AVG(cid) FROM class;
-- SELECT COUNT(cid) FROM class;
-- SELECT sum(cid)/COUNT(cid) FROM class;
-- 
-- SELECT student_id,AVG(num) FROM score GROUP BY student_id HAVING avg(num)>60;

SELECT student_id,student.sname,AVG(num) FROM score left JOIN student ON student.sid=score.student_id GROUP BY student_id HAVING avg(num)>60 ;

View Code

-- SELECT MAX(cid) FROM class;
-- SELECT sum(cid) FROM class;
-- SELECT AVG(cid) FROM class;
-- SELECT COUNT(cid) FROM class;
-- SELECT sum(cid)/COUNT(cid) FROM class;
-- 
-- SELECT student_id,AVG(num) FROM score GROUP BY student_id HAVING avg(num)>60;

-- SELECT student_id,student.sname,AVG(num) FROM score left JOIN student ON student.sid=score.student_id GROUP BY student_id HAVING avg(num)>60 ;

SELECT t.student_id,t.a,student.sname from (SELECT student_id,AVG(num)as a FROM score GROUP BY student_id HAVING avg(num)>60 )as t LEFT JOIN student on t.student_id=student.sid;

View Code

4、查询所有同学的学号、姓名、选课数、总成绩；

5、查询姓“李”的老师的个数；

-- SELECT * from teacher WHERE tname like "李%";
-- SELECT COUNT(tid) from teacher WHERE tname like "李%";
-- SELECT tid,tname,"ok" from teacher WHERE tname like "李%";
SELECT COUNT("ok") FROM (SELECT tid,tname,"ok" from teacher WHERE tname like "李%") as v

View Code

6、查询没学过“叶平”老师课的同学的学号、姓名；

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

-- SELECT * from score WHERE score.course_id=1 or score.course_id=2;
-- SELECT B.student_id,COUNT(B.student_id) FROM (SELECT student_id,course_id from score WHERE score.course_id=1 or score.course_id=2)as B GROUP BY B.student_id HAVING COUNT(B.student_id)>1;
SELECT BB.student_id,student.sname FROM(SELECT B.student_id,COUNT(B.student_id) FROM (SELECT student_id,course_id from score WHERE score.course_id=1 or score.course_id=2)as B GROUP BY B.student_id HAVING COUNT(B.student_id)>1) as BB JOIN student ON student.sid=BB.student_id;

View Code

8、查询学过“李平”老师所教的所有课的同学的学号、姓名；

-- 拿到李平老师教的所有课程
-- SELECT * from course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname="李平老师";
-- SELECT * FROM (SELECT student_id FROM score WHERE course_id in (
-- SELECT cid from course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname="李平老师"
-- ))as TT  LEFT JOIN student on TT.student_id=student.sid GROUP BY TT.student_id; 
SELECT student_id FROM score WHERE course_id in (
SELECT cid from course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname="李平老师"
)

View Code

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

10、查询有课程成绩小于60分的同学的学号、姓名；

-- 查询有课程成绩小于60分的同学的学号、姓名

-- SELECT score.student_id from score WHERE num<60 GROUP BY score.student_id

-- SELECT student.sid,student.sname FROM (SELECT score.student_id from score WHERE num<60 GROUP BY score.student_id
-- )as S LEFT JOIN student ON student.sid=S.student_id

View Code

11、查询没有学全所有课的同学的学号、姓名；

-- 去重
-- SELECT DISTINCT student_id FROM score WHERE num<60;

-- 查询没有学全所有课的同学的学号、姓名；
-- SELECT student_id,COUNT(1) from score GROUP BY student_id HAVING COUNT(student_id)<(SELECT COUNT(1)as c FROM course)
-- SELECT COUNT(1)as c FROM course

SELECT A.student_id,student.sname from (SELECT student_id,COUNT(1) from score GROUP BY student_id HAVING COUNT(student_id)<(SELECT COUNT(1)as c FROM course))as A LEFT JOIN student ON student.sid=A.student_id;

View Code

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

-- 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
SELECT * FROM student WHERE student.sid IN 
(SELECT student_id from score WHERE student_id!=1 AND course_id in (
SELECT course_id FROM score WHERE score.student_id=1
)GROUP BY student_id)

View Code

13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

-- 查询和“001”号的同学学习的课程完全相同的其他同学学号和姓名；
SELECT student_id,COUNT(student_id) FROM score WHERE course_id IN 
(SELECT course_id from score WHERE score.student_id=1) GROUP BY student_id
HAVING COUNT(student_id)=(SELECT COUNT(1) from score WHERE score.student_id=1)

View Code

15、删除学习“叶平”老师课的SC表记录；

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；

17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分；

SELECT 
student_id,
num,
(SELECT num from score as innerTb where innerTb.student_id=outerTb.student_id and course_id=1)as yw,
(SELECT num from score as innerTb where innerTb.student_id=outerTb.student_id and course_id=2)as wl,
(SELECT num from score as innerTb where innerTb.student_id=outerTb.student_id and course_id=3)as ty,
AVG(num),
COUNT(student_id)
from score as outerTb GROUP BY student_id;

View Code

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

-- 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

-- SELECT course.cid,
-- (SELECT MAX(num) FROM score WHERE score.course_id=course.cid),
-- (SELECT min(num) FROM score WHERE score.course_id=course.cid)
-- from course;

SELECT course_id ,MAX(num),MIN(num)from score GROUP BY course_id;

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

-- 按各科平均成绩从低到高和及格率的百分数从高到低顺序；
-- SELECT * from score;
-- SELECT AVG(num)  FROM score GROUP BY student_id desc ;
SELECT course_id,MAX(num),MIN(num),AVG(num),COUNT(course_id),SUM(CASE WHEN score.num>60 then 1 ELSE 0 END),SUM(CASE WHEN score.num>60 then 1 ELSE 0 END)/COUNT(course_id)*100 FROM score GROUP BY course_id;

View Code

20、课程平均分从高到低显示（显示任课老师）；

-- 课程平均分从高到低显示（现实任课老师）
SELECT course_id,AVG(num)as aa from score GROUP BY course_id ORDER BY aa DESC;

View Code

SELECT aa,ee.course_id,course.teacher_id,teacher.tname from (SELECT score.course_id,AVG(num)as aa  from score GROUP BY course_id  DESC ORDER BY aa desc)as ee 
LEFT JOIN course on course.teacher_id=ee.course_id
left JOIN teacher on teacher.tid=course.teacher_id;

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)

-- 查询各科成绩前三名的记录:(不考虑成绩并列情况) 
-- SELECT *,(SELECT num FROM score as s2 WHERE s2.course_id=s1.course_id ORDER BY num LIMIT 0,1 ) as fist_num FROM score as s1;
SELECT * FROM(
SELECT 
    *,
    (SELECT num from score as s2 where s2.course_id=s1.course_id ORDER BY num DESC LIMIT 0,1) as fist_num  ,
    (SELECT num from score as s2 where s2.course_id=s1.course_id ORDER BY num DESC LIMIT 2,1) as third_num 
    from 
    score as s1

)AS T
WHERE num>=third_num AND num<=fist_num;

View Code

22、查询每门课程被选修的学生数；

-- 查询每门课程被选修的学生数；
-- SELECT student_id,COUNT(course_id) FROM score GROUP BY student_id
SELECT score.course_id,COUNT(student_id) from score GROUP BY score.course_id

23、查询出只选修了一门课程的全部学生的学号和姓名；

-- 查询出只选修了一门课程的全部学生的学号和姓名；
-- SELECT * from (
-- SELECT student_id ,COUNT(1) as aa FROM score GROUP BY student_id 
-- ) as T 
-- LEFT JOIN student ON student.sid=T.student_id
-- WHERE T.aa=1;

SELECT student_id ,COUNT(1) as aa FROM score GROUP BY student_id HAVING aa=1

View Code

24、查询男生、女生的人数；

-- 查询男生、女生的人数；
-- SELECT gender ,COUNT(1),"男","女" from student GROUP BY gender
SELECT (SELECT COUNT(1) FROM student WHERE gender="男")as "男",
(SELECT COUNT(1) FROM student WHERE gender="女")as "女"

25、查询姓“张”的学生名单；

SELECT * FROM student WHERE sname LIKE "张%"

26、查询同名同姓学生名单，并统计同名人数；

1 SELECT sid,sname,COUNT(1)as "数量" FROM student GROUP BY sname;

27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

-- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
SELECT course_id,AVG(num)as aa FROM score GROUP BY course_id ORDER by aa ASC ,course_id DESC;

View Code

28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

31、求选了课程的学生人数

32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

33、查询各个课程及相应的选修人数；

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

35、查询每门课程成绩最好的前两名；

36、检索至少选修两门课程的学生学号；

37、查询全部学生都选修的课程的课程号和课程名；

38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；

39、查询两门以上不及格课程的同学的学号及其平均成绩；

40、检索“004”课程分数小于60，按分数降序排列的同学学号；

41、删除“002”同学的“001”课程的成绩；

-- SELECT MAX(cid) FROM class;
-- SELECT sum(cid) FROM class;
-- SELECT AVG(cid) FROM class;
-- SELECT COUNT(cid) FROM class;
-- SELECT sum(cid)/COUNT(cid) FROM class;
-- 
-- SELECT student_id,AVG(num) FROM score GROUP BY student_id HAVING avg(num)>60;

-- SELECT student_id,student.sname,AVG(num) FROM score left JOIN student ON student.sid=score.student_id GROUP BY student_id HAVING avg(num)>60 ;

-- SELECT t.student_id,t.a,student.sname from (SELECT student_id,AVG(num)as a FROM score GROUP BY student_id HAVING avg(num)>60 )as t LEFT JOIN student on t.student_id=student.sid;

-- SELECT * from teacher WHERE tname like "李%";
-- SELECT COUNT(tid) from teacher WHERE tname like "李%";
-- SELECT tid,tname,"ok" from teacher WHERE tname like "李%";
-- SELECT COUNT("ok") FROM (SELECT tid,tname,"ok" from teacher WHERE tname like "李%") as v

-- SELECT * from score WHERE score.course_id=1 or score.course_id=2;
-- SELECT B.student_id,COUNT(B.student_id) FROM (SELECT student_id,course_id from score WHERE score.course_id=1 or score.course_id=2)as B GROUP BY B.student_id HAVING COUNT(B.student_id)>1;
-- SELECT BB.student_id,student.sname FROM(SELECT B.student_id,COUNT(B.student_id) FROM (SELECT student_id,course_id from score WHERE score.course_id=1 or score.course_id=2)as B GROUP BY B.student_id HAVING COUNT(B.student_id)>1)
--        as BB JOIN student ON student.sid=BB.student_id;

-- 拿到李平老师教的所有课程
-- SELECT * from course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname="李平老师";
-- SELECT * FROM (SELECT student_id FROM score WHERE course_id in (
-- SELECT cid from course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname="李平老师"
-- ))as TT  LEFT JOIN student on TT.student_id=student.sid GROUP BY TT.student_id; 
-- SELECT student_id FROM score WHERE course_id in (
-- SELECT cid from course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname="李平老师"
-- )GROUP BY student_id

-- 查询有课程成绩小于60分的同学的学号、姓名

-- SELECT score.student_id from score WHERE num<60 GROUP BY score.student_id

-- SELECT student.sid,student.sname FROM (SELECT score.student_id from score WHERE num<60 GROUP BY score.student_id
-- )as S LEFT JOIN student ON student.sid=S.student_id

-- 去重
-- SELECT DISTINCT student_id FROM score WHERE num<60;

-- 查询没有学全所有课的同学的学号、姓名；
-- SELECT student_id,COUNT(1) from score GROUP BY student_id HAVING COUNT(student_id)<(SELECT COUNT(1)as c FROM course)
-- SELECT COUNT(1)as c FROM course

-- SELECT A.student_id,student.sname from (SELECT student_id,COUNT(1) from score GROUP BY student_id HAVING COUNT(student_id)<(SELECT COUNT(1)as c FROM course))as A LEFT JOIN student ON student.sid=A.student_id;
-- 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
-- SELECT * FROM student WHERE student.sid IN 
-- (SELECT student_id from score WHERE student_id!=1 AND course_id in (
-- SELECT course_id FROM score WHERE score.student_id=1
-- )GROUP BY student_id)
-- 查询和“001”号的同学学习的课程完全相同的其他同学学号和姓名；
-- SELECT student_id,COUNT(student_id) FROM score WHERE course_id IN 
-- (SELECT course_id from score WHERE score.student_id=1) GROUP BY student_id
-- HAVING COUNT(student_id)=(SELECT COUNT(1) from score WHERE score.student_id=1)
--
-- 与2号学生选择个数一样的同学
-- SELECT student_id,COUNT(student_id) from score GROUP BY score.student_id 
-- HAVING COUNT(student_id)=(
--  SELECT COUNT(1) as a from score WHERE student_id=2
-- )

SELECT student_id,COUNT(student_id) from score GROUP BY score.student_id 
HAVING COUNT(student_id)=(
 SELECT COUNT(1) as a from score WHERE student_id=2
)

View Code

posted @ 2018-10-03 18:55 巨兽~墨菲特阅读(218) 评论(0) 编辑收藏举报

刷新页面返回顶部

独孤_败天

mysql学习03

https://www.cnblogs.com/wupeiqi/articles/5729934.html

MySQL测试题

公告