MYSQL 案例积累

MYSQL 案例积累

---

本文为自学mysql案例记录，具体可参见：https://blog.csdn.net/xiejiachao/article/details/120711096?utm_source=app&app_version=4.12.0

以学生表、课程表、成绩表、教师表为例 进行练习

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

---

1. 查询“001”课程比“002”课程成绩高的所有学生的学号

   # 通过仅包含“001” 和 仅包含 “002”的表多表连接方式查询
   select a.S# 
   from (select s#,score from SC where C#='001') a,
         (select s#,score from SC where C#='002') b
   where a.score>b.score and a.s#=b.s#;
   

2. 查询平均成绩>60分的同学的学号和平均成绩

   select a.S#,avg(score)
   from SC as a
   group by a.S#
   having avg(score) > 60
   

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

   ====================
   #自己写的查询
   select S#,Sname,courseCnt,totalScore
   from Student as a
   join(
     select S#,count(C#) as courseCnt,sum(score) as totalScore
     from SC
     group by S#
   ) as b
   on a.S# = b.S#
   
   ==================
   # 参考
   select Student.S#,Student.Sname,count(SC.C#),sum(score)
   from Student left Outer join SC on Student.S#=SC.S#
   group by Student.S#,Sname
   

4. 查询姓“李”的老师的个数 (**需要巩固)

   select count(distinct(Tname))
   from Teacher
   where Tname like '李%'
   

   搜索数据库数据时，通配符可以替代一个或多个字符

   sql通配符必须和like运算符一起使用，sql中通常可以使用以下通配符：

   • %：代表0个或多个字符
   • _仅替代一个字符
   • [charlist] 字符列中任何单一字符
   • [^charlist] 或 [!charlist] 不在字符列中任何单一字符

   示例：

   Persons 表:

   Id	LastName	FirstName	Address	City
   1	Adams	John	Oxford Street	London
   2	Bush	George	Fifth Avenue	New York
   3	Carter	Thomas	Changan Street	Beijing

   # 选取居住在以 "Ne" 开始的城市里的人 返回id = 2的结果
   SELECT * FROM Persons
   WHERE City LIKE 'Ne%'
   
   # 从 "Persons" 表中选取居住在包含 "lond" 的城市里的人：
   SELECT * FROM Persons
   WHERE City LIKE '%lond%'
   
   # "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人：
   SELECT * FROM Persons
   WHERE FirstName LIKE '_eorge'
   
   #从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是一个任意字符，然后是 "er"：
   SELECT * FROM Persons
   WHERE LastName LIKE 'C_r_er'
   
   #从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：
   SELECT * FROM Persons
   WHERE City LIKE '[ALN]%'
   
   # 从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人：
   SELECT * FROM Persons
   WHERE City LIKE '[!ALN]%'
   

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

   Student(S#,Sname,Sage,Ssex) 学生表
   Course(C#,Cname,T#) 课程表
   SC(S#,C#,score) 成绩表
   Teacher(T#,Tname) 教师表
   -----------------------------
   # 自己写的查询
   select S#,Sname from Student where S# not in (
   select S# from SC where C# in(
   select C# from Course where T# in (
   select T# from Teacher where Tname = '叶平')))
   group by S#
   
   -----------------------------
   # 参考：
   select Student.S#,Student.Sname
   from Student
   where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');