杂七杂八知识点

创建数据库

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 条件