#(1)查询SC表中的所有数据,成绩信息为原成绩加上5分后的成绩(不要修改表中的数据)。select*,grade+5from SC;/*(2) 查询STUDENT表中的所在系(消去重复的系别)*/selectdistinct Sdept
from STUDENT;/*(3) 查询SC表,显示学号、课程号和成绩*/select SNO,CNO,grade,casewhen grade>=90then'优秀'when grade>=80then'良'when grade>=70then'中'when grade>=60then'及格'else'不及格'end 等级
from SC;#差, between and 等级 else#(4)查询Student学生表中姓李且单名的学生信息。select*from Student
where SNAME like'李_';#%#(5)查询学号为201215121、 201215122、 201215123学生的姓名、性别、年龄。select SNAME,ssex,sage
from Student
where Sno in('201215121','201215122','201215123');#not like '201215125' between and#(6)查询Student学生表中第二个字不为’立’的学生信息。select*from Student
where SNAME notlike'_立%';#%#(7)查询SC表中课程号为2的课程成绩大于等于90分的学号。select Sno
from SC
where Cno=2and grade>=90;#(8)查询Course表中没有先行课的课程的信息。select*from Course
where cpno='';#0 null '0'#(9)在Student表中统计男女生人数,使查询结果具有可读性。select SSEx 性别,count(*) 每种性别人数
from Student
groupby SSEX;#(9)在Student表中统计男女生人数,使查询结果具有可读性。不对的# Select count(‘男’) 男生人数, count(‘女’) 女生人数#from Student#group by SSEX limit 1;#(10)查询平均分在75分以上同学的平均成绩。select Sno,avg(grade) 平均分
from SC groupby Sno
havingavg(grade)>=75;#(11)查询平均分最高同学的信息。select*,avg(grade)from SC groupby Sno
orderbyavg(grade)descLIMIT1;#having select*from Student
where Sno in(select Sno
from SC groupby Sno
orderbyavg(grade)desc)LIMIT1;
连接查询
/*1. 查询选修了1号课程或2课程的学生的姓名,成绩。*/select SNAME,grade
from STUDENT,SC
where STUDENT.SNO=SC.SNO
and CNO in('1','2');#*2. 查询选修了课程名为’信息系统’的成绩在85分以上的学生的姓名和成绩。select SNAME,cname,grade
from STUDENT,SC,COURSE
where STUDENT.SNO=SC.SNO and COURSE.CNO=SC.CNO
and CNAME='信息系统'and grade>=85;/*3. 查询姓名为’李勇’的学生信息以及他的选修信息(显示课程名)。*/select*from STUDENT,SC,COURSE
where STUDENT.SNO=SC.SNO and COURSE.CNO=SC.CNO
and SNAME='李勇';/*4. 查询系名为’CS’的学生信息和课程成绩情况*/select*from STUDENT,SC
where STUDENT.SNO=SC.SNO and STUDENT.sdept='cs';/*5. 查询性别为’女’的学生信息、所修课程成绩及学分*/select*from STUDENT,SC,COURSE
where STUDENT.SNO=SC.SNO and COURSE.CNO=SC.CNO
and STUDENT.ssex='女';/*6. 查询所有的学生信息以及他们的选课情况,如果该学生没有选课,也需要包含该学生的信息
解题分析:可以使用外连接,查询结果是那些本来不能连接起来的学生记录,
因为这些学生没有选课,所有他们对应的课程编号为空*/select*from STUDENT leftjoin SC on STUDENT.SNO=SC.SNO;#与COURSE无关 /*7. 查询所有未被选修的课程情况*/select*from SC rightjoin Course on SC.CNO=Course.CNO;#只显示未选修的课程select*from Course where CNO notin(select cno from sc groupby CNO);#(1)本题最好用子查询 连接查询不是很完美 (2)有的用左连接 (3)不少缺group by CNO 但结果也对 此处数据量少倒也无所谓 #(4)有的用where SC.CNO=Course.CNO is null 不对的
子查询
/*1. 在SC表中查询选修了2号课程或3课程的学生学号。*/select SNO,cno
from SC
where CNO in('2','3');#1号和2号/*2. 查询选修了2号课程或3课程的学生学号、姓名、性别等情况。
解题分析:先在SC表中查询出选修了2号课程或3课程的学生的学号,然后在STUDENT表中查询以上学号对应的学生的情况*/select*from STUDENT
where SNO in(select SNO
from SC
where CNO in('2','3'));/*3. 查询选修了先行课为“5”的学生学号和姓名*/SELECT Sno,Sname FROM Student
WHERE Sno IN(SELECT Sno FROM SC
WHERE Cno IN(SELECT Cno FROM Course WHERE Cpno='5'));/*4. 找出每门课程学分大于所有课程平均学分的课程号、课程名。*/SELECT Cno,cname FROM Course
WHERE ccredit>(SELECTAVG(ccredit)FROM Course);/*5. 查询3号课程成绩在80分到90分之间的学生姓名、性别、所在系。*/select SNAME,SSEX,sdept from STUDENT
where SNO in(select SNO
from SC
where CNO ='3'and grade between80and90);#缺CNO ='3'/*6. 查询选修了“数据库”的学生的情况。
解题分析:先在Course表中查询出数据库对应的课程号,然后在SC表中查询该课程号对应选修的学号,
最后在STUDENT表中查询这些学号对应的学生情况。*/select*from STUDENT
where SNO in(select SNO from SC
where CNO in(select CNO from Course
where CNAME='数据库'));#查询条件是CNAME='数据库' 有的不仔细/*7. 查询与李勇在同一个系学习的学生选修课程情况。
解题分析:先在STUDENT表中查询姓名为李勇的同学所在的系别,然后在STUDENT表查询同一系别的学生学号,
最后在SC表中查询同一系别的学生选修课程情况。*/select*from SC
where sno in(select sno from STUDENT
where sdept in(select sdept from STUDENT
where SNAME='李勇')and sname<>'李勇');
#and sname<>'李勇'要也可以 不要也可以
/*8.将姓名为李勇的同学的所有成绩加上5分进行显示(原数据不动)。*/select sno,grade+5,grade from SC
where SNO in(select SNO
from STUDENT
where SNAME='李勇');/*9. 查询计算机科学系所有学生大于90分的成绩记录。*/select sno,grade from SC
where grade>90and SNO in(select SNO from STUDENT
where sdept='CS');#是并列条件 有的将grade>90 放在子查询里面/*10.查询SC表中非201215122的同学的成绩比201215122同学所有课程成绩都好的学生姓名和所在系
解题分析:只要非201215122的同学有一门课程成绩比201215122的最高分高就行*/SELECT Sname,Sdept FROM Student
WHERE sno in(select sno from sc where grade >ALL(SELECT grade FROM Sc WHERE Sno='201215122')and Sno<>'201215122');#有的用any 不少差and Sno<> '201215122'/*11.查询选修课程数比学分为3的课程数多的学生姓名
学生的选课信息存在于SC表中,现要比较某个学生选修课程数与学分为3的课程数:
(1)首先需要统计学分为3的课程数(2)其次,要扫描SC全表,统计出每个学生的选修课程数
(3)最后,在STUDENT表中根据学号输出姓名。*/select Sname from student
where Sno IN(select Sno from SC
groupby Sno havingcount(*)>(selectcount(*)from course where ccredit='3'));
更新数据
# 给SC表插入王丽丽同学(学号为201215126)学习计算机网络(课程号为8)课程的成绩(89分)。INSERTINTO Student VALUES('201215126','王丽丽',null,null,null);INSERTINTO course VALUES('8','计算机网络',null,null);INSERTINTO SC VALUES('201215126','8',89);# 班上新来一名同学,请将他的信息插入学生表Student中。学号:201215127,姓名:王序,性别:男,年龄:19:系别:IS。INSERTINTO Student VALUES('201215127','王序','男','19','is');#由于管理要求,需要用格式为:S_AVG(Sno,AVG_GRADE)的临时表,暂存平均成绩大于等于80分的女同学的学号和平均成绩。CREATETABLE S_AVG
(Sno CHAR(9)PRIMARYKEY,
AVG_GRADE int);INSERTINTO S_AVG ( Sno, AVG_GRADE)SELECT Student.Sno,AVG(GRADE)FROM Student, SC
WHERE Student.Sno=SC.Sno
AND SSEX ='女'GROUPBY SC.Sno
HAVINGAVG(GRADE)>=80;#或者使用子查询 CREATETABLE S_AVG (Sno CHAR(9)PRIMARYKEY, AVG_GRADE int);INSERTINTO S_AVG ( Sno, AVG_GRADE)SELECT Student.Sno,AVG(GRADE)FROM Student,sc
WHERE SSEX ='女'and Student.sno in(select Student.sno from sc
GROUPBY SC.Sno
HAVINGAVG(GRADE)>=80);# 将学生表Student中的学生名字“王丽丽”改为“王小丽”。UPDATE Student
SET Sname='王小丽'WHERE Sname='王丽丽';# 把学习成绩表SC中“信息系统”课成绩小于等于80的成绩全部改为85分。UPDATE SC
SET grade=85WHERE grade<=80AND Cno IN(SELECT Cno
FROM Course
WHERE cname='信息系统');# 或者连接UPDATE SC,course
SET grade=85WHERE grade<=80and course.cname='信息系统'and Course.cno=sc.cno;# 在学习成绩表SC中修改“数学”课程的成绩,若成绩低于该课程的平均成绩时,则将其成绩改为该平均成绩。#第一步:定义局部变量,将“数学”课程的平均成绩赋给它#第二步:对sc的grade进行判断和修改。selectavg(grade)from sc where Cno IN(SELECT Cno FROM course WHERE cname='数学')INTO@avgs;update SC set GRADE =@avgswhere GRADE <@avgsand Cno IN(SELECT Cno FROM course WHERE cname='数学');#从学生表Student中删除学号为201215127的学生的信息。DELETEFROM Student
WHERE Sno ='201215127';#从学习成绩表SC中删除李勇同学的所有课程成绩的记录。DELETEFROM SC WHERE sno in(SELECT sno FROM Student WHERE SNAME='李勇');#从学生表Student中删除系别为“CS”的所有学生的记录,先设置外键检测为0。set foreign_key_checks=0;DELETEFROM Student
WHERE sdept ='cs';#将sc表中学生号为”201215122”的学生的成绩改为空值。UPDATE Sc
SET grade =NULLWHERE Sno='201215122';#选出选修2号课程的不及格的学生以及缺考的学生。SELECT Sno
FROM SC
WHERE Grade <60AND Cno='2'UNIONSELECT Sno
FROM SC
WHERE Grade ISNULLAND Cno='2';
视图操作
#(1) s-t数据库中,创建学生平均成绩视图:A_GRADE(Sno,SNAME,AVG_GRADE)。 #方法1:CREATEVIEW A_GRADE(Sno,SNAME,AVG_GRADE)ASSELECT Student.Sno,SNAME,AVG(GRADE)FROM Student,SC
WHERE Student.Sno = SC.Sno
GROUPBY Sc.Sno;#(2)设计学习成绩视图GRADE_T,其中属性包括:学号、姓名、课程号、课程名、学分、成绩。CREATEVIEW GRADE_T(Sno,Sname,cno,cname,ccredit,GRADE)ASSELECT Student.Sno,Sname,sc.cno,cname,ccredit,GRADE
FROM Student,sc,course
WHERE Student.Sno=sc.Sno and sc.cno= course.cno ;#(3)创建视图S_SC,要求显示成绩大于等于85分的男学生的学号、姓名、课程号与该课程的成绩,并按成绩降序排列。CREATEVIEW S_SC(SNO,sname,cno,grade)ASSELECT STUDENT.SNO,sname,cno,grade
FROM STUDENT,SC
WHERE STUDENT.SNO = SC.sno and ssex ='男'AND grade>=85ORDERBY SC.grade DESC;#(4)在A_GRADE视图中查询平均成绩大于85分的学生学号、姓名和平均成绩SELECT sno,sname,AVG_GRADE
FROM A_GRADE
WHERE AVG_GRADE>=85;#(5)建立计算机系学生的视图CS_Student。CREATEVIEW CS_Student
ASSELECT Sno,Sname,Ssex,Sage
FROM Student
WHERE Sdept='cs';#(6)将计算机系学生视图CS_Student中学号”201215122”的学生姓名改为”艾学习”。UPDATE CS_Student
SET Sname='艾学习'WHERE Sno='201215122';#(7)向计算机系学生视图CS_Studen中插入一个新的学生记录,其中学号为”201215128”,姓名为”尤所成”,年龄为19岁INSERTINTO CS_Student
VALUES('201215128','尤所成',null,19);#(8)删除计算机系学生视图CS_Studen中学号为”201215122”的记录 set foreign_key_checks=0;DELETEFROM CS_Student
WHERE Sno='201215122';set foreign_key_checks=1;
#1、用户的创建#(1)CREATE USER语句创建用户user1、user2、user3,密码均为’123456’。CREATEUSER'user1'@'localhost' IDENTIFIED BY'123456';CREATEUSER'user2'@'localhost' IDENTIFIED BY'123456';CREATEUSER'user3'@'localhost' IDENTIFIED BY'123456';#(2)INSERT INTO语句向USER表创建用户user4,密码均为’123456’。INSERTINTO mysql.user(HOST,USER,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES('localhost','user4',md5('123456'),'','','');#(3)GRANT语句创建用户user5,密码均为’123456’,且为全局级用户。GRANTALLPRIVILEGESON*.*TO'user5'@'localhost' IDENTIFIED BY'123456';#2、用户授权(利用GRANT语句)#(1)授予user1用户对s-t数据库拥有所有权GRANTALLPRIVILEGESON`s-t`.*TO'user1'@'localhost';#(2)授予user2用户对s-t数据库的student表拥有select,create,drop权GRANTselect,create,dropon`s-t`.student TO'user2'@'localhost';#(3)授予user3用户对s-t数据库的student表拥有列级select,update权GRANTselect,update(sname)on`s-t`.student TO'user3'@'localhost';#3、权限收回(利用revoke语句)#(1)收回user1用户对s-t数据库拥有所有权revokeALLPRIVILEGESON`s-t`.*from'user1'@'localhost';#(2)收回user3用户对s-t数据库的student表拥有列级update权revokeupdate(sname)on`s-t`.student from'user3'@'localhost';#4、删除用户(利用drop语句,演示时去掉#)#drop user 'user1'@'localhost';#drop user 'user2'@'localhost';#drop user 'user3'@'localhost';#drop user 'user4'@'localhost';#drop user 'user5'@'localhost';
触发器
#在student表中创建触发器,限制系别的输入,只允许输入“cs”、“is”和“ma”,createtrigger trigger_limitSex
before inserton student for each rowbeginif NEW.sdept='cs'or NEW.sdept='is'or NEW.sdept='ma'thenset@mesg='更改成功';elseset@mesg='更改不成功';insertINTO xxxx value(1);endif;End;#在course表中创建触发器,只允许课程表的编号是4位数字,createtrigger trigger_limitcno
before inserton course for each rowbeginif NEW.cno regexp'[0-9]{4}'thenset@mesg='插入成功';elseset@mesg='插入不成功';insertINTO xxxx value(1);endif;End;#在student中创建触发器,只允许修改年龄介于15-45之间,createtrigger trigger_limitage
BEFORE UPDATEON student FOR EACH ROWBEGINif NEW.sage<15or NEW.sage>45thenset@mesg='修改不成功';insertINTO xxxx value(1);elseset@mesg='修改成功';endif;END;#在course中创建触发器,实现在删除课程表一门课程时,级联删除sc中的相关记录,createtrigger trigger_delete
before deleteon course for each rowbeginSET@oldcno=old.cno;DELETEFROM sc WHERE sno=@oldcno;END;