SQL Server 初次使用

之前习惯使用mysql ＋ Navicat 进行数据库操作，并没有使用过SQL Server。这次介绍如何使用SQL Server。

一. 创建数据库

打开 Microsoft SQL Server Management Studio

右击数据库，创建新数据库，命名为“教务系统”

右击我们新建的数据库“教务系统”，点击新建查询。本次实验操作都基于SQL语句。

二. SQL 语句操作

1. 创建表

设计学生表（Student）,课程表（Course），学生选课表（SC）
（1）学生表（Student）

CREATE TABLE Student
(
	Sno CHAR(9) PRIMARY KEY,	/* 列级完整性约束条件，Sno 为主键 */
	Sname CHAR(20) NOT NULL,	/* 列级完整性约束条件，Sname 不为空 */
	Ssex CHAR(2),
	Sage SMALLINT,				/* SMALLINT 的范围为 0-255 */
	Sdept CHAR(20)
);

（2）课程表（Course）

CREATE TABLE Course
(
	Cno CHAR(4) PRIMARY KEY,	/* 列级完整性约束条件，Cno 为主键*/
	Cname CHAR(40) NOT NULL,	/* 列级完整性约束条件，Cname 不为空*/
	Cpno CHAR(4),
	Credit TINYINT,
	FOREIGN KEY(Cpno) REFERENCES Course(Cno)	/*表级完整性约束条件，Cpno 是外键，被参照表是 Course, 被参照列是 Cno*/
);

（3）学生选课表（SC）

CREATE TABLE SC
(
	Sno CHAR(9),
	Cno CHAR(4),
	Grade TINYINT,
	PRIMARY KEY (Sno, Cno),						/* 主码由两个属性构成，必须作为表级完整性进行定义 */
	FOREIGN KEY (Sno) REFERENCES Student(Sno),	/* 表级完整性约束条件，Sno 是外码，被参照表是 Student，被参照列是Sno */
	FOREIGN KEY (Cno) REFERENCES Course(Cno),	/* 表级完整性约束条件，Sno 是外码，被参照表是 Course，被参照列是Cno */
);

2. 增加数据（INSERT）

（1）添加学生表（Student）数据

INSERT INTO 
Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES
('201215121','李勇','男',20,'CS'),
('201215122','刘晨','女',19,'CS'),
('201215123','王敏','女',18,'MA'),
('201215125','张立','男',19,'IS')

（2）添加课程表(Course)数据

INSERT INTO
Course(Cno, Cname, Cpno, Credit)
VALUES
('6','数据处理',NULL,2),
('1','数据库','5',4),
('2','数学',NULL,2),
('3','信息系统','1',4),
('4','操作系统','6',3),
('5','数据结构','7',4),
('7','PASCAL','6',4)

（3）添加学生选课表(SC)数据

INSERT INTO
SC(Sno, Cno, Grade)
VALUES
('201215121','1',92),
('201215121','2',85),
('201215121','3',88),
('201215122','2',90),
('201215122','3',80)

3. 查看数据（SELECT）

（1）单表查询
查询所有学生信息。

SELECT * FROM SC;

单独查询学生学号和所修课程号。

SELECT
	Sno, Cno
FROM 
	SC;

查询学生信息，出生年份，所在院系。

SELECT
	Sname NAME, 2014-Sage BIRTHYEAR, LOWER(Sdept) DEPARTMENT
FROM	
	Student

（2）连接查询
查询学生姓名，性别，所在院系，选修课程号及对应成绩。

SELECT 
	Student.Sno SNO, Sname NAME, Ssex SEX, Sdept DEPARTMENT, Cno CNO, Grade GRADE
FROM
	Student, SC
WHERE
	Student.Sno = SC.Sno;

查询课程的先修课程。

SELECT 
	First.Cno '课程号', First.Cname '课程名',Second.Cno '先修课程号', Second.Cname '先修课'
FROM
	Course First, Course Second
WHERE
	First.Cpno = Second.Cno

查询学生学号，姓名，所在院系，所修课程的课程号，课程名称和对应成绩。

SELECT
	Student.Sno Sno, Sname, Sdept, SC.Cno, Cname, Grade
FROM
	Student LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno), Course
WHERE
	SC.Cno = Course.Cno

查询学生学号，姓名，所在院系，所修课程的课程号，课程名称和对应成绩。

SELECT 
	Student.Sno Sno, Sname, Sdept, SC.Cno, Cname, Grade
FROM
	Student, Course, SC
WHERE
	Student.Sno = SC.Sno AND SC.Cno = Course.Cno

（3）嵌套查询
查询同时选修了信息系统的同学名称和院系。

SELECT 
	Sno, Sname, Sdept
FROM
	Student
WHERE
	Student.Sno IN
	(
		SELECT 
			Sno
		FROM
			SC
		WHERE SC.Cno IN
		(
			SELECT
				Cno
			FROM
				Course
			WHERE
				Cname = '信息系统'
		)	
	)

查询年龄最大的学生的姓名和年龄。

SELECT 
	Sname, Sage
FROM
	Student
WHERE
	Sage = 
	(SELECT 
		MAX(Sage)
	FROM 
		Student
	)

（4）集合查询
查询同时选修课程号为1和课程号为2课程的学生。

SELECT 
	Sno, Sname
FROM
	Student
WHERE
	Sno IN
	(
		SELECT 
			Sno 
		FROM 
			SC
		WHERE 
			Cno = '1'
		INTERSECT
		SELECT 
			Sno 
		FROM 
			SC
		WHERE 
			Cno = '2'
	)

（5）基于派生表的查询
找出每个学生超过他自己选修课程平均成绩的课程号。

SELECT 
	Sno, Cno
FROM
	SC, (
			SELECT
				Sno, AVG(Grade)
			FROM
				SC
			GROUP BY
				Sno
		) AS Avg_sc(avg_sno, avg_grade)
WHERE
	SC.Sno = Avg_sc.avg_sno AND SC.Grade >= Avg_sc.avg_grade

3. 修改数据（UPDATE）

将学号为 201215122 的学生 的年龄修改为10岁。

UPDATE
	Student
SET
	Sage = 10
WHERE
	Sno='201215122'

将所有学生的年龄增加1岁。

UPDATE
	Student
SET
	Sage = Sage + 1

4. 删除数据

删除学号为 201215125 的学生信息。

DELETE
FROM
	Student
WHERE
	Sno = '201215125'

	SELECT 
	*
FROM
	Student