sql server学习

基本表

-- 新建database
USE master
GO
CREATE DATABASE student_Mis /* 数据库名称*/
ON /* 以下部分称PRIMARY，即主文件的信息，用于定义数据库的数据文件，若缺省则为列出的第一个文件*/
( 
	NAME = student_Data,
	FILENAME = ‘...’, 
	SIZE = 10, 
	MAXSIZE = 50,
	FILEGROWTH = 5 
)
LOG ON /* 描述日志文件的信息，指定存储日志的文件清单，不指定默认25%用于创建日志 */
( 
	NAME = ‘student_Log’, /* 执行sql语句时引用的逻辑文件名 */
	FILENAME = ‘...’, /* 操作系统下数据库文件存储的名字 */
	SIZE = 10MB, /* 最初数据库存储空间大小 */
	MAXSIZE = 25MB, /* 最大存储空间 */
	FILEGROWTH = 5MB /* 指定数据库增长的数量，默认256KB，最小64KB，0则不增长 */
)
GO

-- 新建表
CREATE TABLE Courses
(
	Cno CHAR(6) PRIMARY KEY, /* 主键 */
	Cname CHAR(20),
	Pre_Cno CHAR(6),
	Credits INT
)
CREATE TABLE Students
(
	Sno CHAR(5) PRIMARY KEY, /* 主键 */
	Sname CHAR(20) NOT NULL, /* 不能为空 */
	/*...*/
)
CREATE TABLE Reports 
(
	Sno CHAR(5),
	Cno CHAR(6),
	Grade INT,
	PRIMARY KEY(Sno, Cno), -- 主键
	/* 
	CONSTRAINT：限制；FOREIGN KEY：外键
	这个表的主键由两个外键组成
	reports通过两个外键与其他两个表产生联系
	*/
	CONSTRAINT Student_Report FOREIGN KEY(Sno) REFERENCES Students,
	CONSTRAINT Report_Course FOREIGN KEY(Cno) REFERENCES Courses
)

-- 插入
INSERT INTO Students
	VALUES(‘S01’, ‘糖醋鱼’, ‘男’, ‘1995-10-12’, ‘D01’)
INSERT INTO Reports
	VALUES(‘S01’, ‘C01’, 92) /* 若Courses中没有C01或Students中没有S01，则报错 */
/*	
由此可见，含有外键的表新增元组需要先在其联动表中新建元组。 （问题类型：解释为什么这个地方会发生错误）
*/

-- 修改基本表
ALTER TABLE Student /* 选定表名 */
/*do sth.*/

/*do sth.部分常用语句：
	ADD
	DROP
	ALTER COLUMN
*/

/*1. 向基本表student中增加“入学时间”属性列，属性名Sentrancedate ，数据类型为DATETIME型*/
ALTER TABLE STUDENTS
	ADD Sentrancedate DATETIME
	
/*2. 删除基本表Student中Sname必须取唯一值的约束*/
ALTER TABLE Student DROP CONSTRAINT Sname

/*3. 删除基本表Student中Sage列*/
ALTER TABLE Student DROP COLUMN Sage

/*4. 修改基本表Student中Sage列的属性为DATETIME*/
ALTER TABLE Student ALTER COLUMN Sage DATETIME

/* 课后查询 */

-- 删除表
DROP TABLE Student
/*
并不能成功！
因为Reports中有Student的外键
要么把Reports清空（可以吗），要么删掉Reports
*/

索引

CREATE INDEX tmp /* tmp: table name */
	ON Student(Sname DESC); /* Sname: column name, DESC: descending */
CREATE UNIQUE INDEX tmp2
	ON Student(Sname ASC); /* ASC: ascending */

CREATE CLUSTERED INDEX tmp3
	ON Student(Sname ASC); /* ASC: ascending */
	
/*
UNIQUE表示对于索引值相同的只引用一次
CLUSTERED表示索引和物理存储一致，即对原表重排列。所以一个表只能有一个聚簇索引。
（如果先创建别的索引然后再创建聚簇索引呢？
排序ASC/DESC缺省为ASC
*/

查询

-- 无条件查询

SELECT

/* 1. 查询全体学生的姓名(Sname)，出生年份及学号(Sno) */
SELECT Sno, SName, YEAR(Birthday) BirthYear
	FROM Students;
/*	YEAR(Birthday) indicates a sql query, only selecting year in 	DATETIME
	BirthYear indicates the name of the new column after query*/

/*2. 将所有人的学号字母都变成小写字母*/
SELECT Sname,
	‘Birth:’ Title, /* new column named Birth, initially all “Title” */
	YEAR(Birthday) BirthYear,
	LOWER(Sno) Lsno
	
/*3. 查询选修了课程的学生学号*/
SELECT Sno
	FROM Reports;
SELECT DISTINCT Sno /* unique */
	FROM Reports;
	
-- 带条件查询

WHERE 	/*+条件表达式 */
			/*1. 比较条件*/
				WHERE Sdept = ‘x’;
				WHERE Sage > 16 AND Sage <= 22;
			/*2. 谓词条件*/
			/*3. LIKE/NOT LIKE
				1. a%b 以a开头以b结尾
				2. a_b 以a开头以b结尾长度为3*/
			/*4. 转义字符：ESCAPE*/
				WHERE Cname LIKE ‘DB\_设计’ ESCAPE ‘\’
				/*(获得“DB_设计”的课程)*/
			/*5. NULL*/
				WHERE Grade IS NULL;
				/*既不是零也不是长度为零，所以不能用=，只能用is(xx is NULL)*/ 

-- 查询结果排序

SELECT *
	FROM Students
	ORDER BY Dno,
	DATENAME(yyyy, GETDATE()) - YEAR(Birthday)
	DESC;

-- 集函数

SELECT AVG(Grade) /* 求平均成绩 */
	FROM Reports
	WHERE Cno = ‘C01’;

-- 查询结果分组

/* 查询选修了三门以及以上课程的学生学号(Sno) */

SELECT Sno
	FROM Reports
	GROUP BY Sno /* 按照某一列或多列的值分组，使列相等的为一组（一个元组） */
	HAVING COUNT(Cno) >= 3 /* 筛选满足条件的部分 */

/*
出现在SELECT语句中但是没有被聚集的属性只能出现在GROUP BY 子句中的那些属性
任何出现在HAVING 子句中，但是没有被聚集的属性必须出现在GROUP BY 子句中
*/

-- 连接查询
	-- 1. 不同表间的连接查询
		SELECT Students.*, Reports.* /* 如果用Students.Sno, Sname, Cname, Grade, Dname，就可以去除重复项 */
			FROM Students, Reports
			WHERE Students.Sno = Reports.Sno;
	-- 2. 自身连接查询
		需要给表起别名已做出区分，其他与1一样
		
-- 外连接
	/* 以一个表为基准，另一个表即使没有对应数据也要展示（值为NULL），相当于两个表的合并（个人理解） */
	/* 比如：查询所有学生的选修课程选课情况，这时即使没有选修课也要输出NULL */

-- 嵌套查询