Welcome

A blog by moplord

欢迎来到我的博客一起分享学习

3-Minute Read

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字节


定义表:

例如:

CREATE TABLE Jobs(`

	`Jid  char(6)   PRIMARY KEY,`

	`Descp   nchar(20)   NOT NULL,`

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

	`MinSalary int,`

	`MaxSalary int,`

	`CHECK(MaxSalary >= MinSalary)`

`)`

CREATE TABLE 架构名.表名(

	列名   数据类型   列级约束

	表级完整性约束

)

约束:

NOT NULL:非空

PRIMARY KEY:主键

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

UNIQUE:唯一值约束,不能重复

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


修改表:

ALTER TABLE 架构名.表名{

	ALTER COLUMN 列名 新数据类型

  |ADD 列名 数据类型 约束

  | DROP COLUMN 列名                //删除列

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

}

删除表

DROP TABLE 表名

查询语句

查询指定列

SELECT Sno FROME Student

查询全部列

SELECT * FROME Student

计算与列别名

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

去除重复行

SELECT DISTINCT Sno FROM SC

满足条件

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

IN 与 NOT IN

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

通配符

_匹配任意一个字符

%匹配0到多个字符

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

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

例如查张李六姓的学生

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

查询叫X某X的学生

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

ESCAPE 转义字符

查询包含30%

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

NULL

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

可以用

WHERE Grade IS NULL
WHERE Grade IS NOT NULL

AND 与WHERE

用于多重查询


排序ORDER BY

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

聚合函数

COUNT(*):列值个数

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

SUM(列名):列和

AVG(列名):列平均值

MAX(列名):列最大值

MIN(列名):列最小值

举例:

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

分组统计

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

统计每个系女生

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

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

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

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

WHERE对FROM筛选

GOUP BY 对WHERE后分组

HAVING对分组后筛选


内连接

FROM 表1 INNER JOIN JOIN 表2 ON 条件

例如

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

自连接(一张表当两张表用)

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

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

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

外连接(输出所有连接,包括不满足条件的)

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

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


TOP n 前几个

TOP n percent 前百分之n

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

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

CASE

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

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

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

查询结果保存表中

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

子查询

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

EXISTS

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

插入

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

更新

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

删除

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

索引与视图

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

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

B树:平衡树

未完待续…

Recent Posts

CSS3

Categories

About

没有名字的博客