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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
| create table student
(
stuID char(10) not null primary key,
stuName char(8),
stuSex char(2) default'男',
Birthday date
);
create table course
(
courseID char(3) not null primary key,
courseName varchar(20) not null
);
create table sc
(
stuID char(10) not null,
courseID char(3) not null,
Grade decimal(4,1),
primary key(stuID,courseID),
CONSTRAINT fk_sc_stuID foreign key(stuID) references student(stuID),
CONSTRAINT fk_sc_courseID foreign key(courseID) references course(courseID)
);
create table teacher
(
teacherID char(3) not null primary key,
teacherName char(8) not null,
teacherSex char(2)
);
create table teachering
(
teacherID char(3) not null,
courseID char(3) not null,
primary key(teacherID,courseID),
CONSTRAINT fk_teachering_teacherID foreign key(teacherID) references teacher(teacherID),
CONSTRAINT fk_teachering_courseID foreign key(courseID) references course(courseID)
);
insert into student
values( '2014030701','张三','男','1987-01-12'),
( '2014020701','王宁','男','1988-03-20'),
( '2014030702','王芳','女','1987-11-15'),
( '2014020704','李立','男','1986-12-30'),
( '2014030703','田甜','女','1987-09-10');
insert into course
values('C01','大学英语'),
('C02','数据库原理及应用'),
('C03','操作系统');
insert into sc
values('2014030701','C02','98'),
('2014030701','C03','86'),
('2014030703','C01','79'),
('2014030703','C02','94'),
('2014030703','C03','55');
insert into teacher
values('t01','李勇','男'),
('t02','钱军','男'),
('t03','王旺','男'),
('t04','张成','男'),
('t05','李丽','女');
insert into teachering
values('t01','C01'),
('t02','C02'),
('t04','C03');
1
select stuID as 学号，stuName as 姓名,Birthday as 出生日期
from student
where stuName like '王%' and 男的 懒得写了;
2
select *
from sc
limit 3;
3
SELECT course.courseID 课程号, courseName 课程号, COUNT(*)人数, MAX(Grade) 最高分
FROM sc,course
WHERE SC.courseID = course.courseID
GROUP BY courseName
4
select student .stuName, student.stuID
from sc as a join student as b on a.stuID = b.stuID
join student on student.stuID = A.stuID
视图
CREATE VIEW stu_sc_teacher
SELECT student.stuID 学号, student.stuName 姓名 ,sc.courseID 课程号,course.courseName 课程名 ,sc.Grade 成绩 , teachering.teacherID 教师编号 , teacher.teacherName 教师名称
FROM student,sc,course,teachering,teacher
WHERE student.stuID = sc.stuID and sc.courseID = course.courseID and sc.courseID = teachering.courseID and teachering.teacherID = teacher.teacherID
ORDER BY student.stuID
SELECT student.stuName,student.stuID,sc.courseID,course.courseName,sc.Grade, teachering.teacherID
FROM student,sc,course,teachering
WHERE student.stuID = sc.stuID and
sc.courseID = course.courseID and
sc.courseID = teachering.courseID and
ORDER BY student.stuID
存储1
delimiter //
CREATE PROCEDURE proc_1()
BEGIN
SELECT student.stuID 学号, student.stuName 姓名 ,student.stuSex 性别,course.courseID 课程号,course.courseName 课程名,sc.Grade 成绩
FROM student,course,sc
WHERE student.stuID = sc.stuID and course.courseID = sc.courseID and sc.Grade < 60
ORDER BY student.stuID;
END;//
delimiter;
delimiter //
CREATE PROCEDURE proc_1()
BEGIN
SELECT student.stuID , student.stuName ,student.stuSex ,course.courseID,course.courseName,sc.Grade
FROM student,course,sc
WHERE student.stuID = sc.stuID and course.courseID = sc.courseID and sc.Grade < 60
ORDER BY student.stuID;
END;//
delimiter;
存储2
delimiter //
CREATE PROCEDURE proc_2(in xh char(10))
BEGIN
SELECT *
FROM student
WHERE stuID = xh;
END;//
delimiter;
存储3
delimiter //
CREATE PROCEDURE proc_3(in cid_tmp char(3) , OUT sum_grade decimal(4,1))
BEGIN
SELECT SUM(Grade) INTO sum_grade
FROM sc
WHERE courseID = cid_tmp;
END;//
delimiter;
set @grade=0;
call proc_3('C03',@grade);
SELECT @grade 成绩
delimiter //
CREATE TRIGGER student_sno
AFTER UPDATE
ON student
FOR EACH ROW
BEGIN
if (
select stuID
from sc
where old.stuID != new.stuID
) is NULL THEN
UPDATE sc
SET stuID = new.stuID
WHERE stuID = old.stuID;
END;//
delimiter;
|