数据库基本操作

如有不同观点或发现错误，请告知，感谢。

表结构的创建、修改、查看

表的创建：

CREATE TABLE SC(
Sno char(6),
Cno char(4) ,
//decimal浮点数
Grade decimal(12,1),
//将Sno，Cno设置为名为PK_SC的联合主键
CONSTRAINT PK_SC PRIMARY KEY (Sno,Cno),
//设置参照student表Sno属性的外键
FOREIGN KEY(Sno) REFERENCES student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno)
)

CREATE TABLE Course(
//将Cno设置为主键
Cno char(4) PRIMARY KEY,
Cname varchar(20),
Cpno char(4),
//设置一个约束：学分要大于0且小于6
Ccredit tinyint check(Ccredit>-1 and Ccredit<6),
//设置一个约束：先修课的课程编号和当前的课程编号不同。
check(Cpno != Cno)
)

CREATE TABLE Student(
Sno char(6) PRIMARY KEY,
//设置为唯一的，非空。
Sname varchar(8) UNIQUE NOT NULL,
//设置一个约束：Sex只能是男or女且非空。
Sex char(2) check(Sex='男' or Sex='女') NOT NULL,
Sage smallint check(Sage > 16),
//默认值为：JSJ
Sdept varchar(15) DEFAULT ('JSJ')
)

表结构的修改：

属性的修改：

向student表中添加两行属性 ：address varchar(60), inDate date

AlTER TABLE student ADD address varchar(60), inDate date;

将student表中的属性address的长度修改为50

ALTER TABLE student ALTER column address varchar(50);

将student表中的属性inDate删除

ALTER TABLE student DROP COLUMN inDate;

约束的修改：

在SC表中将Sno，Cno设置为名为PK_SC的联合主键。

ALTER TABLE SC ADD CONSTRAINT PK_SC PRIMARY KEY (Sno,Cno);

在SC表中：设置参照Course表中Cno属性的外键FK_SC

ALTER TABLE SC ADD CONSTRAINT FK_SC FOREIGN KEY(Cno) REFERENCES Course(Cno);

在SC表中设置一个名为CK_SC的约束：Grade在[0-100]区间里。

ALTER TABLE SC ADD CONSTRAINT CK_SC_Grade CHECK(Grade>=0 and Grade <= 100);

将SC表中的PK_SC约束删除

ALTER TABLE SC DROP CONSTRAINT PK_SC;

索引的修改：

为SC.Cno创建一个名为Ix_sc_cno的索引

CREATE INDEX Ix_sc_cno ON SC(Cno);

为Course.Cname创建一个唯一性的索引：Ix_course_cname

CREATE UNIQUE INDEX Ix_course_cname ON Course(Cname);

删除名为Course.Ix_course_cname的索引

DROP INDEX Course.Ix_course_cname;

表结构的查看

查看学生表的结构

EXEC sp_help N'student';

查看SC表的约束

EXEC sp_helpconstraint  SC；

查看SC表的索引

EXEC sp_helpindex SC

表数据的修改，查看

表数据的修改

增：

向 student 表中插入一条数据（按照表属性的顺序）

INSERT INTO student VALUES (3001,'赵达','男',20,'SX');

向 Course 表中插入一条数据（按照自己设定的顺序和需要的属性）
其他属性为默认值（如果设默认值了）

INSERT INTO Course (Cno,Cname,Ccredit) VALUES (1081,'电子商务',4);

向 Course 表中插入多条数据

INSERT INTO  Course VALUES(1088,'Java',NULL,5),(1089,'数学',NULL,3);

把 SX 系学生的 Sno, Sname, Ssex, Cno , Grade 插入到表 SC_name 中
其中 Sno 在 student 表和 SC 表中，Sname 和 Sex 在 student 表中，Cno 和 Grade 在 SC 表中
详情请看 表的创建 中创建的表
DISTINCT 意味着：不重复

INSERT INTO SC_name SELECT DISTINCT Student.Sno,Sname,Sex,Cno,Grade 
FROM Student,SC WHERE Student.Sdept = 'SX' AND Student.Sno = SC.Sno;

删：

删掉 student 表中 Sno 为 4001 和 4002 的数据

DELETE FROM student WHERE Sno = 4001 or Sno = 4002;

删除所有 JSJ 系得男同学

DELETE FROM Student WHERE Sdept = 'JSJ' AND Sex = '男';

删掉 SC 表中课程名为 Java 的课程
（ SC 表中只有 Cno ---- 课程号，Course 表中有 Cno 和对应的 Cname ---- 课程名）

DELETE FROM SC WHERE Cno = (SELECT Cno FROM Course WHERE Cname = 'Java');

改：

修改 Student 表中 Sno 为 4001 学生的系科为: JSJ

UPDATE Student SET Sdept = 'JSJ' WHERE Sno = 4001;

在 Student 表中把赵茵的年龄加1岁，性别改为女。

UPDATE Student SET Sex = '女',Sage = Sage + 1 WHERE Sname = '赵茵';

修改杨华的1088课程的成绩为93分
(SC表中只有Sno — 学号，Student 表中有 Sno 以及对应的 Sname — 姓名)

UPDATE SC SET Grade = 93 WHERE Cno = 1088 AND 
Sno = (SELECT Sno FROM Student WHERE Sname = '杨华');

把“数学”课的成绩减去1分
(SC表中只有Cno — 课程号，Course 表中有 Cno 以及对应的 Cname — 课程名)

UPDATE SC SET Grade = Grade - 1 WHERE 
Cno = (SELECT Cno FROM Course WHERE Cname = '数学');

表数据的查看

有关 CASE WHEN THEN ELSE 的用法的博客
查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。

SELECT Sno,Sname,Sage FROM Student WHERE Sex = '女' AND 
Sage BETWEEN 19 AND 21 ORDER BY Sage DESC;

查询姓名中第2个字为“明”字的学生学号、性别

SELECT Sno,Sex FROM Student WHERE Sname LIKE '_明%';

查询 1001课程没有成绩的学生学号、课程号

SELECT Sno,Cno FROM SC WHERE Cno = 1001 AND Grade IS NULL;

查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名，结果按系及学号排列

SELECT Sno,Sname FROM Student WHERE Sdept IN ('JSJ','SX','WL') 
AND Sage > 25 ORDER BY Sdept,Sno;

按10分制查询学生的SNO,CNO,10分制成绩
(1-10分为1 ，11-20分为2 ，30-39分为3，……，90-100为10)

SELECT Sno,Cno,ceiling(Grade / 10) AS NewGrade FROM SC;

查询 STUDENT 表中的学生共分布在那几个系中。(DISTINCT)

SELECT DISTINCT Sdept FROM Student;

查询0001号学生1001，1002课程的成绩。

SELECT Grade FROM SC WHERE Cno IN (1001,1002) AND Sno = 0001;

查询姓名中有“明”字的学生人数。

SELECT SUM(CASE WHEN Sname LIKE '%明%' THEN 1 ELSE 0 END) AS '人数' FROM Student;

计算‘JSJ’系的平均年龄及最大年龄。

SELECT AVG(Sage) AS '平均年龄',MAX(Sage) AS '最大年龄' FROM Student;

–3计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列

SELECT Cno AS '课程号',SUM(Grade) AS '总分',AVG(Grade) AS '平均分',MAX(Grade)
 AS '最高分',MIN(Grade) AS '最低分'FROM SC GROUP BY Cno ORDER BY '平均分' DESC;

计算 1001,1002 课程的平均分。

SELECT Cno AS '课程号',AVG(Grade) AS '平均分' FROM SC 
WHERE Cno IN (1001,1002) GROUP BY Cno;

查询平均分大于80分的学生学号及平均分

SELECT Sno AS '学生学号',AVG(Grade) AS '平均分' 
FROM SC GROUP BY Sno HAVING AVG(Grade)> 80;

统计选修课程超过 2 门的学生学号

SELECT Sno AS '学生学号' FROM SC GROUP BY Sno HAVING COUNT(1) > 2;

统计正好有10位成绩大于85分以上的课程号。

SELECT Cno AS '课程号' FROM SC WHERE Grade > 85 
GROUP BY Cno HAVING COUNT(1) = 10;

统计平均分不及格的学生学号

SELECT Sno AS '学生学号' FROM SC GROUP BY Sno HAVING AVG(Grade) < 60;

统计有大于两门课不及格的学生学号

SELECT Sno AS '学生学号' FROM SC WHERE Grade < 60 
GROUP BY Sno HAVING COUNT(1) > 2;

查询 JSJ 系的学生选修的课程号

SELECT DISTINCT Cno FROM Student INNER JOIN SC 
ON Student.Sno = SC.Sno WHERE Sdept = 'JSJ';

查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法)

1、SELECT Sname FROM Student WHERE 
Sno IN (SELECT Sno FROM SC WHERE Cno = 1002);

2、SELECT Sname FROM Student INNER JOIN SC 
ON Student.Sno = SC.Sno WHERE Cno = 1002;

查询数据库原理不及格的学生学号及成绩

SELECT Sno,Grade FROM SC INNER JOIN Course 
ON SC.Cno = Course.Cno WHERE Cname = '数据库原理' AND Grade < 60;

查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法)

1、SELECT Sname FROM SC INNER JOIN Course ON SC.Cno = Course.Cno 
INNER JOIN Student ON SC.Sno = Student.Sno 
WHERE Cname = '数据库原理' AND Grade > 80;

2、SELECT Sname FROM Student WHERE Sno IN 
(SELECT Sno FROM SC WHERE Grade > 80 AND Cno = (SELECT Cno FROM Course 
WHERE Cname = '数据库原理'));

查询平均分不及格的学生的学号，姓名,平均分。

SELECT Student.Sno,Sname,AVG(Grade) AS '平均分' FROM Student INNER JOIN SC 
ON Student.Sno = SC.Sno 
GROUP BY Student.Sno,Sname HAVING AVG(Grade) < 60;

查询女学生平均分高于75分的学生姓名。

SELECT Sname FROM Student INNER JOIN SC ON Student.Sno = SC.Sno 
WHERE Sex = '女' GROUP BY Student.Sno,Sname HAVING AVG(Grade) > 75;

查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)

SELECT Student.Sno,Sname,Cno,Grade FROM Student LEFT JOIN SC 
ON Student.Sno = SC.Sno WHERE Sex = '男';

查询平均分不及格的学生人数

SELECT COUNT(1) AS '不及格人数' FROM Student 
WHERE Sno IN (SELECT Sno FROM SC GROUP BY Sno HAVING AVG(Grade) < 60);

查询没有选修1002 课程的学生的学生姓名

SELECT Sname FROM Student WHERE 
Sno NOT IN (SELECT Sno FROM SC WHERE Cno = 1002);

查询平均分最高的学生学号及平均分(2种方法 TOP , all)

1、SELECT TOP 1 Sno,AVG(Grade) FROM SC GROUP BY Sno 
ORDER BY AVG(Grade) DESC ;

2、SELECT Sno,AVG(Grade) AS '平均分' FROM SC GROUP BY Sno 
HAVING AVG(Grade) >= ALL(SELECT AVG(Grade) FROM SC GROUP BY Sno);

查询没有选修1001，1002课程的学生姓名。

SELECT Sname FROM Student WHERE 
Sno NOT IN (SELECT Sno FROM SC WHERE Cno IN (1001,1002));

查询1002课程第一名的学生学号

SELECT TOP 1 Sno FROM SC WHERE Cno = 1002 ORDER BY Grade DESC;

查询平均分前三名的学生学号

SELECT TOP 3 Student.Sno FROM Student INNER JOIN SC 
ON Student.Sno = SC.Sno GROUP BY Student.Sno ORDER BY AVG(Grade) DESC;

查询 JSJ 系的学生与年龄不大于19岁的学生的差集

SELECT * FROM Student WHERE Sdept = 'JSJ' 
EXCEPT
SELECT * FROM Student WHERE Sage <= 19;

查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名

SELECT Student.Sno,Sname FROM Student INNER JOIN SC 
ON Student.Sno = SC.Sno WHERE (Cno = 1001 AND Grade > 90) OR 
Student.Sno IN(SELECT Sno FROM SC GROUP BY Sno HAVING AVG(Grade) > 85) ;

查询每门课程成绩都高于该门课程平均分的学生学号
OVER开窗函数

SELECT A.Sno FROM (SELECT Sno,Grade,AVG(Grade) OVER (PARTITION BY Cno) 
AS '学科平均分' FROM SC) AS A WHERE A.Grade > A.学科平均分
GROUP BY A.Sno HAVING COUNT(1) = 
(SELECT COUNT(Cno) FROM SC WHERE Sno = A.Sno GROUP BY Sno);

查询大于本系科平均年龄的学生姓名

SELECT Sname FROM (SELECT Sname,Sage,AVG(Sage) OVER (PARTITION BY Sdept) 
AS '平均年龄' FROM Student) AS AVGAGE WHERE Sage > 平均年龄