创建数据库
CREATE DATABASE IF NOT EXISTS jxdb CHARACTER SET UTF8MB4;
USE jxdb;
创建表格
CREATE TABLE Major
(
Mno CHAR(4) comment '专业编号',
Mname VARCHAR (15) NOT NULL comment '专业名称',
Dno CHAR(2) comment '学院编号',
PRIMARY KEY (Mno)
);
Show CREATE TABLE Major;
CREATE TABLE Teachers(
Tno CHAR(4) primary key comment '教师编号',
Tname varchar(4) not null comment '教师姓名',
Tgender ENUM('男','女')comment '性别',
Tbirth DATE comment '出生日期',
Dno CHAR(2) comment '学院编号'
);
show create table teachers;
修改基本表
添加 ALTER TABLE Courses ADD Pre_Cno CHAR(8) after Cname; DESCRIBE Courses; 修改 ALTER TABLE students CHANGE Sage Sbirth DATE comment'出生日期';
索引
普通索引
1)使用CREATE INDEX语句,在学生表的Mno列上按降序创建普通索引,索引名为IDX_Stu_Mno #答案提交:(1)按要求写出创建索引的SQL语句;(2)使用'SHOW INDEX FROM 基本表名;'命令查看基本表的索引信息 DROP INDEX IDX_Stu_Mno ON students; CREATE INDEX IDX_Stu_Mno ON Students(Mno DESC ); SHOW INDEX FROM students;
唯一索引
CREATE UNIQUE INDEX IDX_Cou_Cname ON Courses(Cname ASC ) ;
删除索引
DROP INDEX IDX_Tea_Tprof on teachers;
外键声明
ALTER TABLE Major
ADD CONSTRAINT FK_Maj_Dno FOREIGN KEY (Dno) REFERENCES Department(Dno);
Alter table reports
add constraint FK_Sno FOREIGN KEY (Sno) REFERENCES students(Sno);
ALTER修改表
(1)ALTER TABLE Student ADD college char(20) after sbirth
(2)
ALTER TABLE tutors ADD Tutid INT PRIMARY KEY AUTO-INCREMENT FIRST
select 语句
LIMIT 6 可用于查询前六位学生 SUBTRING(SCLASS,1,4) LEFT(Sno,4) LIMIT 9,5 表示第10条开始的后5条 SUBSTRING(Snow,5,2) 20141122104 取到11 DISTINCT 去重 FORMAT(AVG(Grade,1)保留小数点一位数 YEAR(Sbirth)出生年每月,取年份 CAST(SelNum AS CHAR) SelNum这个列或值转换为字符型数据类型 ASC升,DESC 降序
count 统计某一列个数 Group by HAVING
#请写出下列查询操作的SQL语句:“查询各门课程(Cno)的选课情况,结果显示课程编号、选课人数、平均分、最高分和最低分,查询结果按课程编号降序排序, # 并用中文显示属性列名,平均分保留小数点后1位” #FORMAT(AVG(Grade),1) SELECT Cno'课程编号',count(*) '选课人数',FORMAT(AVG(Grade),1)'平均分',MAX(Grade)'最高分',MIN(Grade)'最低分' FROM Reports GROUP BY Cno DESC ;
SELECT Cno'课程编号',count(*)'选修人数',FORMAT(avg(Grade),1)'平均成绩' FROM reports WHERE SUBSTRING(Sno,5,2)='11' GROUP BY Cno HAVING COUNT(*)>=5;
ORDER BY 默认升序
“查询2017学年至少上了两门课程的教师信息, # 结果显示教师编号、学年、所上课程门数,按教师编号升序排序,并用中文显示属性列名” SELECT Tno'教师编号',Tacademicyear'学年',count(DISTINCT Cno) '所上课程门数' FROM Tutors where Tacademicyear=2017 GROUP BY Tno, Tacademicyear HAVING count(DISTINCT Cno)>=2 ORDER BY Tno ASC ;
请写出下列查询操作的SQL语句:“查询所有选修了课程号为112p0024并且有成绩的学生的考试情况, # 结果要求显示学生的学号、课程号和成绩等级('优'、'良'、'中、'及格'和'不及格),并按成绩降序排序(提示:CASE WHEN)”
SELECT Sno,Cno,
CASE
WHEN Grade>90 THEN '优秀'
WHEN Grade>80 THEN '良'
WHEN Grade>70 THEN '中'
WHEN Grade>=60 THEN '及格'
ELSE '不及格'
END AS '成绩等级'
FROM reports
where Cno='112p0024' and Grade is not null
GROUP BY Grade,Sno
ORDER BY Grade DESC;
#请写出下列查询操作的SQL语句:“查询课程名中包含了'计算机'或'原理'的课程的所有信息” SELECT * FROM Courses #where Cname like '%计算机%' or '%原理%'; 错误 WHERE Cname LIKE '%计算机%' OR Cname LIKE '%原理%'; #请写出下列查询操作的SQL语句:“查询名字中第二个字为“一”的男学生的学号、姓名和出生年份,查询结果按学号降序排序” SELECT Sno,Sname,YEAR(Sbirth) AS "BornYear" FROM students #where Sname like '%一%' and Sgender='男',错误 WHERE Sname LIKE '_一%' AND Sgender='男' ORDER BY Sno DESC ;
请写出下列查询操作的SQL语句:"使用谓词IN嵌套查询,查询有课程成绩不及格的学生的信息,结果显示学号(Sno)、姓名(Sname)及所在班级(Sclass)" SELECT DISTINCT Students.Sno,Sname,Sclass FROM Students inner join reports r on Students.Sno = r.Sno where Grade in (SELECT Grade FROM Reports where Reports.Grade <60);
#请写出下列查询操作的SQL语句:"使用嵌套查询,查询总工资高于所有学院的平均总工资的教师信息,结果显示教师编号(Tno)、 # # 教师姓名(Tname)、学院名称(Dname)、职称(Tprof)及总工资,并用中文显示属性列名"
SELECT Tno AS "教师编号", Tname AS "教师姓名", Dname AS "学院名称", Tprof AS "职称", Tsal+Tcomm AS "总工资" FROM Teachers T, Department D WHERE T.Dno=D.Dno AND (Tsal+Tcomm)> ALL ( SELECT AVG(Tsal+Tcomm) FROM Teachers GROUP BY Dno);
#请写出下列查询操作的SQL语句:"使用EXISTS谓语查询至少选修了学生2014112104选修的全部课程的学生的学号(Sno)、姓名(Sname)"
SELECT S.Sno, Sname FROM Students S WHERE S.Sno<>'2014112104' AND NOT EXISTS (SELECT * FROM Reports Rep WHERE Sno='2014112104' AND NOT EXISTS (SELECT * FROM Reports WHERE S.Sno=Sno AND Rep.Cno=Cno) );
插入
INSERT INTO tutors(tno, cno, tacademicyear, tterm, sclass) VALUES ( (select tno from teachers where tname='许永军'), (SELECT cno from Courses where Cname='计算机网络'), 2018,1,20171151 ); INSERT INTO reports(Sno, Cno, Racademicyear, Rterm)
INSERT INTO TeaQuantity(Tno,Tacademicyear,Tquantity) (SELECT Tut.Tno,Tacademicyear,SUM(Chours) FROM Tutors Tut,Courses C, Teachers T WHERE Tut.Cno=C.Cno AND T.Tno=Tut.Tno AND Tname='王平' GROUP BY Tno,Tacademicyear);
更新
UPDATE TeaQuantity, (SELECT Tut.Tno,Tacademicyear,SUM(Chours) SumofQuantity FROM Tutors Tut,Courses C, Teachers T WHERE Tut.Cno=C.Cno AND T.Tno=Tut.Tno AND Tname='王平' GROUP BY Tno,Tacademicyear) TQ SET TeaQuantity.Tquantity= TQ.SumofQuantity WHERE TeaQuantity.Tno=TQ.Tno AND TeaQuantity.Tacademicyear=TQ.Tacademicyear;
UPDATE Department set Dheader = ( select Tno from teachers where Tname='李力' ) where Dno=( select Dno from teachers where Tname='李力' ); #弊端,只能针对一个人的 UPDATE Department D, ( SELECT Dno, Tno FROM Teachers WHERE Tname IN ('李力', '张兰', '张雪') ) DH SET Dheader=DH.Tno WHERE D.Dno=DH.Dno;
删除
delete from 表名 where 条件