Myqsl语句经典例题

2014-09-28 约 2289 字预计阅读 5 分钟

这里是mysql例题导入代码

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138


------------创建数据库---------------
create database data charset=utf8;

------------ 建表语句-----------------
# 学生表 Student：

create table Student(

SId varchar(10) ,

Sname varchar(10),

Sage datetime,

Ssex varchar(10));




# 教师表 Teacher

create table Teacher(

TId varchar(10),

Tname varchar(10)); 



# 科目表 Course

create table Course(

CId varchar(10),

Cname nvarchar(10),

TId varchar(10)); 

# 成绩表 SC

create table SC(

SId varchar(10),

CId varchar(10),

score decimal(18,1)); 


------------ 插入数据语句-----------------

# 学生表 Student：

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('09' , '张三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2017-12-30' , '女');

insert into Student values('12' , '赵六' , '2017-01-01' , '女');

insert into Student values('13' , '孙七' , '2018-01-01' , '女');



# 科目表 Course

insert into Course values('01' , '语文' , '02'); 

insert into Course values('02' , '数学' , '01'); 

insert into Course values('03' , '英语' , '03'); 



# 教师表 Teacher

insert into Teacher values('01' , '张三');
 
insert into Teacher values('02' , '李四'); 

insert into Teacher values('03' , '王五'); 



# 成绩表 SC

insert into SC values('01' , '01' , 80); 

insert into SC values('01' , '02' , 90); 

insert into SC values('01' , '03' , 99); 

insert into SC values('02' , '01' , 70); 

insert into SC values('02' , '02' , 60); 

insert into SC values('02' , '03' , 80); 

insert into SC values('03' , '01' , 80); 

insert into SC values('03' , '02' , 80); 

insert into SC values('03' , '03' , 80); 

insert into SC values('04' , '01' , 50); 

insert into SC values('04' , '02' , 30); 

insert into SC values('04' , '03' , 20); 

insert into SC values('05' , '01' , 76); 

insert into SC values('05' , '02' , 87); 

insert into SC values('06' , '01' , 31); 

insert into SC values('06' , '03' , 34); 

insert into SC values('07' , '02' , 89); 

insert into SC values('07' , '03' , 98); 

###例题列表

1:查询课程01比课程02成绩高的学生信息及课程分数

解题代码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


#1.查询01课程比02课程成绩高的学生信息和成绩分数
#学生信息在student这个表中
#成绩分数在sc表中
#目的:01课程比02课程成绩高的学生,01课程和02课程用cid体现,cid只在sc中有
select * from student a
INNER JOIN sc b
on a.SId=b.SId #这里看成第一个大表

INNER JOIN sc c
on b.SId=c.SId and b.CId="01" AND c.cid="02" #这里看成第二个大表
WHERE b.score>c.score #进行分数比较

2:查询同时存在01课程和02课程的情况

解题代码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


#第一种写法
#查询同时存在01课程和02课程的情况
#01课程和02课程的成绩表都在sc表之中
SELECT *
FROM
(SELECT * FROM sc where cid='01') a
INNER join
(select * from sc where cid='02') b
on a.sid=b.sid
#第二种写法
#这个解决方案相当于,把where条件改加到最下面了
SELECT *
FROM
sc a
INNER join
sc b
on a.sid=b.sid
WHERE a.CId='01' AND b.cid='02'

3:查询存在01课程但可能不存在02课程的情况(不存在时显示为null)

解题代码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


#第一种写法
#查询存在01课程但可能不存在02课程的情况(不存在时显示为null)
SELECT *
FROM
(select * from sc WHERE cid='01') a
LEFT JOIN (SELECT * FROM sc where CId='02') b
on a.sid=b.sid
#第二种写法
SELECT *
FROM
sc a
LEFT JOIN sc b
on a.sid=b.sid and b.cid='02'
WHERE a.cid='01';

4:查询不存在01课程,但存在02课程的情况

解题代码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


#第一种写法
SELECT
*
FROM
(SELECT *
FROM sc WHERE sid NOT IN
(SELECT SId FROM sc WHERE CId='01')) A
INNER JOIN SC B
ON A.SID=B.SId AND B.CID='02';
#第二种写法
select *
FROM sc a
WHERE SId not in (SELECT SId from sc where cid='01')#用not in 进行了排除
AND cid='02';git status

5:查询不存在01课程,但存在02课程的情况

解题代码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


#第一种写法
SELECT
*
FROM
(SELECT *
FROM sc WHERE sid NOT IN
(SELECT SId FROM sc WHERE CId='01')) A
INNER JOIN SC B
ON A.SID=B.SId AND B.CID='02';
#第二种写法
select *
FROM sc a
WHERE SId not in (SELECT SId from sc where cid='01')#用not in 进行了排除
AND cid='02';

6:查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

解题代码

#解题思路:先查询sid,然后用sid进行匹配;group by配合平均函数使用; SELECT b.sid,b.Sname,a.avg_score FROM (SELECT sid,AVG(score) as avg_score FROM sc GROUP BY SId HAVING avg_score>=60) a INNER JOIN student b ON a.sid=b.sid

7:查询在 SC(成绩表) 表存在成绩的学生信息;

解题代码

#查询在 SC 表存在成绩的学生信息,这题目主要巩固group by的括号内严谨写法,还有b.可作为查询条件. select b. FROM (SELECT SId from sc GROUP BY sid) a LEFT JOIN student b on a.Sid=b.SId;

8:查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

解题代码

#查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null) SELECT a.SId as 学生编号,a.Sname as 学生姓名,b.COUNT_CID as 选课总数,b.SUM_SCORE FROM student a LEFT JOIN (SELECT SId,SUM(score) AS SUM_SCORE,COUNT(CId) AS COUNT_CID FROM sc GROUP BY SId) b ON a.SId=b.SId

4.1:查询有成绩的学生信息;

解题代码

#这个案例是用in来实现的; SELECT a.* FROM student a WHERE a.SId IN (SELECT SId from sc GROUP BY SId); #上面用了 where in 在什么什么里面查找; #还可以用exist来判断存在; SELECT a.* FROM student a where EXISTS (SELECT * FROM sc B WHERE a.SId=b.SId) #用inner join实现 SELECT a.* FROM student a INNER JOIN (SELECT sid FROM sc GROUP BY sid) b ON a.sid=b.SId;

5:求交集,inner join

解题代码

#查询姓李的老师都有谁? SELECT * FROM teacher WHERE Tname LIKE ‘李%'; #查询数量的话 SELECT COUNT(1) as cons FROM teacher WHERE Tname LIKE ‘李%';

6:查询学过「张三」老师授课的同学的信息

解题代码

目录

Myqsl语句经典例题