SQL SERVER数据库总结

概述

数据库优点：相互关联的数据集合、较少的数据冗余、程序与数据相互独立、保证数据安全可靠、最大限度保证数据正确性、数据可以共享并能保证数据的一致性

数据独立性：应用程序不会因数据的物理表示方式和访问计数的改变而改变，即应用程序不依赖于任何特定的物理表示方式和访问计数

物理独立性：数据的存储位置或存储结构发生变化时，不影响程序的特性

逻辑独立性：表达信息发生变化，如增删等，不影响应用程序特性

数据模型三要素：数据的基本结构（静态特征）、数据的约束条件（静态特征）、定义在数据上的操作（动态特征）

数据模型三个要求：能够真实的模拟世界、容易被人们理解、能够方便的在计算机上实现

概念层数据模型：抽象现实系统中有应用价值的元素与关联，面向用户、面向世界，与DBMS无关

实体联系模型：

实体：职工、学生、教师等

属性：学号、姓名、性别等

联系：学生与系（实体之间联系）、部门经理号与职工号（实体内部联系）等

包含一对一联系（1：1）、一对多联系（1：n）、多对多联系（m：n）

组织层数据模型：数据组织形式的角度来描述信息，有层次模型、网状模型、关系模型、面向对象模型、对象关系模型。

三级模式结构：

外模式：用户能够看见和使用的

模式：所有用户的公共视图。数据库中的全部信息

内模式：存储结构存储方式

均不涉及物理存储方式

两级映像：

外模式/模式映像：程序与数据的逻辑独立性

模式/内模式映像：数据与程序的物理独立性

关系数据库

元组：如（011，李勇，21，男，计算机系）

候选键：能够唯一标识一个关系的元组又不包含其他多余属性，如学号、身份证号

主键：候选键中选一个作为主键

主属性：包含在任意候选键中的属性

实体完整性：必须有主键且主键不能重复

参照完整性：主键用下划线标识，如学生（学号，姓名，性别，专业，年龄）选课（学号，课程号，成绩）

关系代数：

举例：查询信息管理系选了C004课程的学生姓名与成绩：

πSname,Grade(σCno=‘C004’⋀Sdept=‘信息管理系’（SC⋈Student）)

SQL SERVER基础

数值

Bigint 整数 8字节（64）

Int 整数 4字节（32）

Smallint 整数 2字节（16）

Tinyint 整数 1字节（8）

numeric(p,q)或decimal(p,q) p为精度即位数，q为小数位数，q默认0 最多17字节

float 浮点 4或8字节

real 浮点 4字节

字符

char(n) 固定，n取值1-8000 n字节

varchar(n) 可变，n为最大，1-8000 字符数，1汉字算2字符

text 存储超过8000字节的字符数，1汉字算2字符

varchar(max) 存储超过8000字节的字符数，1汉字算2字符

nchar(n) Unicode,1-4000 2*n字节

nvchar(n) Unicode,n为最大 2*n字节，1汉字算2字符

ntext Unicode

nvarchar(max)

binary(n) 1-8000,二进制 n字节

varbinary(n)

image 大容量二进制

varbinary(max)

Unicode即统一字符编码

日期

date YYYY-MM-DD 3字节

time[(n)] 24H制，n为秒的小数位数，默认100 3-5字节

datetime YYYY-MM-DD hh:mm:ss.nnn 8字节

smalldatetime YYYY-MM-DD hh:mm:00精确到分钟 4字节

定义表：

例如：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


CREATE TABLE Jobs(`

​	`Jid  char(6)   PRIMARY KEY,`

​	`Descp   nchar(20)   NOT NULL,`

​    `EduReq   nchar(6)   DEFAULT'本科',`

​	`MinSalary int,`

​	`MaxSalary int,`

​	`CHECK(MaxSalary >= MinSalary)`

`)`

即

1
2
3
4
5
6
7


CREATE TABLE 架构名.表名(

​	列名   数据类型   列级约束

​	表级完整性约束

)

约束：

NOT NULL：非空

PRIMARY KEY：主键

FOREIGN KEY：外键，即另一个表的主键，如ROREIGN KEY(Jid) REFERENCES Jobs(Jid)

UNIQUE：唯一值约束，不能重复

CHECK：且标志范围约束，如CHECK(Sex=‘男’ OR Sex=‘女’)

修改表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


ALTER TABLE 架构名.表名{

​	ALTER COLUMN 列名 新数据类型

  |ADD 列名 数据类型 约束

  | DROP COLUMN 列名                //删除列

  |ADD  CONSTRAINT 约束名 约束定义          添加约束

}

删除表

1

DROP TABLE 表名

查询语句

查询指定列

1

SELECT Sno FROME Student

查询全部列

1

SELECT * FROME Student

计算与列别名

1
2


SELECT Sname 姓名, 2015 - Sage 出生年份
	FROM Student

去除重复行

1

SELECT DISTINCT Sno FROM SC

满足条件

1
2
3


SELECT Sname FROME Student
	WHERE Sage BETWEEN 20 AND 23
	//等同于 WHERE Sage Sage < 20 OR Sage >23

IN 与 NOT IN

1
2


……
	WHERE Sdept IN('信息管理系'，‘通信工程系’,'计算机系')

通配符

_匹配任意一个字符

%匹配0到多个字符

[]匹配[]里的任意一个字符

[^]不匹配里的任意字符

例如查张李六姓的学生

1
2


SELECT * FROM Student
	WHERE Sname LIKE '[张李刘]%'

查询叫X某X的学生

1
2


SELECT * FROM Student
	WHERE Sname LIKE '_某_'

ESCAPE 转义字符

查询包含30%

1

WHERE fidld1 LIKE '%30!%%' ESCAPE '!'

NULL

NULL为未知的值，在查询语句中不会返回有NULL的值

可以用

1
2


WHERE Grade IS NULL
WHERE Grade IS NOT NULL

AND 与WHERE

用于多重查询

排序ORDER BY

1
2


WHERE Cno = 'COO2' ORDER BY Grade  \\升序，后面可添加ASC，默认省略
WHERE Cno = 'COO2' ORDER BY Grade DESC \\降序

聚合函数

COUNT(*)：列值个数

COUNT(DISTINCT 列名)：列值去重个数

SUM(列名)：列和

AVG(列名)：列平均值

MAX(列名)：列最大值

MIN(列名)：列最小值

举例：

1

SELECT AVG(Grade) FROM SC WHERE Sno = '0831103'

分组统计

1
2


GROUP BY 分组依据
HAVING 组提取条件

统计每个系女生

1
2
3
4


SELECT Sdept, Count(*) 女生人数 FROM Student
	WHERE Ssex = '女'
	GROUP BY Sdept
	//若有排序加最后

HAVING可以用聚合函数，WHERE不可以

例如统计选课门数超过三门

1
2


SELECT Sno, COUNT(*) 选课门数 FROM SC
	GROUP BY Sno HAVING COUNT(*) > 3

WHERE对FROM筛选

GOUP BY 对WHERE后分组

HAVING对分组后筛选

内连接

1

FROM 表1 INNER JOIN JOIN 表2 ON 条件

例如

1
2
3


SELECT * FROM Student INNER JOIN SC 
	ON Student.Sno = SC.Sno
	//后面是WHERE

自连接（一张表当两张表用）

1
2
3


FROM 表1 AS T1
JOIN 表1 AS T2
ON 表1.列名 = 表2.列名    //必须取别名

可以用于例如查询与李某同一系的学生

1
2
3
4
5


SELECT S2.Sname, S2.Sdept
	FROM Student S1 JOIN S2.Sdept
	ON S1.Sdept = S2.Sdept
	WHERE S1.Sname = '李某'
	AND S2.Sname != '李某'

外连接（输出所有连接，包括不满足条件的）

1
2


FROM 表1 LEFT OUTER JON 表2 ON 连接条件    //左外连接
FROM 表1 RIGHT OUTER JON 表2 ON 连接条件    //右外连接

例如查询选课情况，包含选课了的学生与没选课的学生

TOP n 前几个

TOP n percent 前百分之n

WITH TIES 包括并列结果，并列第几

1
2


SELECT TOP 3 .... FROM .... ORDER BY ...
SELECT TOP 10 WITH TIES .... FROM .... ORDER BY ...

CASE

1
2
3
4
5


CASE 测试表达式即列名
	WHEN 表达式1 THEN 结果1
	WHEN 表达式2 THEN 结果2
	ELSE 结果n
END

例如查询计算机系结果改为CS……

1
2
3
4
5
6
7
8


SELECT s.Sno 学号,Sname 姓名,
	CASE Sdept
		WHEN '计算机系' THEN 'CS'
		WHEN '信息管理系' THEN 'IM'
		WHEN '通讯工程系' THEN 'COM'
	END AS 所在系, Grade 成绩
	……
	//CASE 后面也可为空，可写为 WHEN Sdept = '计算机系' THEN 'CS'

查询结果保存表中

1
2
3


SELECT 查询列 INTO 新表名
	FROM 数据源
	……

子查询

1

SELECT 列名 WHERE ... NOT IN (子查询即SELCT FROM……)

EXISTS

1

WHERE NOT EXISTS(子查询)  \\子查询里判断真假，返回主查询SELECT的数值

插入

1
2


INSERT INTO 表名 VALUES(表值)
INSERT INTO 表名 SELECT  \\多行插入

更新

1
2
3


UPDATE 表名 SET 列名 = 表达式或NULL值等
FROM 条件表明
WHERE 条件更新

删除

1
2
3


DELETE FROM 表名
FROM 条件表名
WHERE 删除条件

索引与视图

聚集索引：按关键字进行物理排序

非聚集索引：只对关键字排序

B树：平衡树

未完待续…