[SQL][学习笔记]mooc学习（数据类型、主键外键、表、索引）

SQL对大小写不敏感 ：SELECT 与 select 是相同的

常见数据类型

字符型

	定长字符类型	变长字符类型	文本型
①	char(n1)	varchar(n3)	text
②	nchar(n2)	nvarchar(n4)	ntext
③	字符串长度固定， 适用长度固定的数据	字符串长度不固定， 适用长度不固定的数据	字符串长度不固定， 适用于长文本数据
④	n1≤8000；n2≤4000	n3≤8000；n4≤4000	＜231-1
⑤	实际值少于定义长度长度时 用空格填充	实际值少于定义长度长度时 不用空格填充	存储实际字符， 但空间至少为2k

表中①行为定义ASCII字符集组成的字符串，②行为定义Unicode字符集组成的字符串

数值型

1. 整数型（用于存储整型数据，即不带小数）

类型	占用存储空间	表示范围
tinyint	1字节	0~255
smallint	2字节	-32768~32767
int / integer	4字节	-2147483648~2147483647
bigint	8字节	-9223372036854775808~9223372036854775807

2. 精确数据型（用于存储带小数的完整的十进制数）

类型	百度摘要
numeric(p,s)	使用标准、可变长度的内部格式来存储数字
decimal(p,s)	存储为 96 位（12 个字节）无符号的整型形式， 并除以一个 10 的幂数。

p表示精度，s表示小数位数且 s≤整数位数
说明：精确数值所占的存储空间随精度的增加而增加

3. 浮点型（近似数值型，不能精确地表示数据精度）

类型	占用存储空间	表示范围
float(n) 1≤n≤24	4字节	-3.4E+38~3.4E+38
float(n) 25≤n≤53	8字节	-1.79E+308~1.79E+308
real	4字节	-.3.4E+38~3.4E+38

4. 货币型
   在数据的第一数字前加一个货币符号，整数位超过三位自动加分隔符
   类型：smallmoney(4字节) , money(8字节）
5. 位型
   表示真假，相当于其他语言中的额逻辑型数据。
   ① 在计算机中占一个字节
   ②一般用 0 / false 表示假，用 非0 / true 表示真

日期时间型

类型	占用存储空间	表示范围
smalldatetime	4字节	1/1/1950~6/6/2049
datetime	8字节	1/1/1753~12/31/9999

二进制型
存储图像数据、有格式的文本数据（eg.Word、Excel文件）、程序文件数据等。
1.Binary[(n)]：存储长度n+4字节，多余内容截断。
2. Varbinary[(n)]：存储长度可变
3. Image：长度为2³¹-1字节

其他数据类型
时间戳型、唯一标识符型、游标型、变体型、通用型、XML型

主键和外键

主键

称 “ 主关键字 ”，是被挑选出来，用作表中唯一表示行的字段或字段组合。

• 一个表只有一个主关键字，分别成为单字段主键或多字段主键
• 主键的值不可重复，也不可为空（NULL）
• 主键不是必须的，但最好为每个表都设置一个主键

作用

• 保证实体的完整性
• 加快数据库的操作速度
• 实现表之间的关联

外键

若某个字段在当前表中不是主键字段，但在当前表之外的表中是主键字段，则该字段就称为 “ 外键 ” 字段，简称 “ 外键”。

• 外键的存在是为了在表之间建立关系；关系的建立是为了在表之间正确引用数据。

表之间的关系

一般在两个表之间创建关联。发起创建关系的为 “ 主表 ”，接收关联的表为 “ 从表 ”。

• 一对一（1：1） ：在主表的主键和从表的主键上建立关联。
• 一对多（1：n） ：在主表的主键和从表的外键上建立关联。
• 多对多（m：n）

参与建立关系的字段一般存放相同性质、相同类型的数据，但字段名可以不同。
在主键和外键以外的其他键上建立关系无实际意义。

数据表的创建与操作

创建

create table <表名>(
<列名><数据类型> [<列约束>], <列名><数据类型> [<列约束>]
.....
 [<表约束>]
 ）

栗子：

create table 系
(系编号 char(4),
系名称 char(14),
教室个数 smallint,
班级个数 smallint,
学院编号 char(1),
primary key(系编号),//主键
foreign key(学院编号) references 学院(学院编号)//外键
);

操作

一、对表的操作

1. 添加
   alter table <表名> add <列名><数据类型>
2. 修改
   alter table <表名> alter <列名> <修改后的数据类型>
3. 删除
   alter table <表名> drop <列名>

二、对表中记录的操作

1. 插入（将新纪录插入指定表中）
   insert into <表名> [(<列1>[,<列2>...)] values(<常量1> [,<常量2>]....)
   栗子：

     //将完整的记录插入到表中
     insert into 学院 values ('A','软件','大傻子'，'012-34567890123','A123'）
     //将一个不完整的记录插入到表中
     insert into 学院（学院编号，学院名称，地址） values ('A','软件','A123'）
   

2. 更新记录(修改指定表中满足where条件的记录）
   update <表名> set <列名1>=更新的值 [ where <更新条件>]
   栗子：

     //将学生表中学号为G12345的出生年月改为2019-01-29，籍贯改为魔仙堡
     update 学生 set 出生年月=‘2019-01-29’，籍贯=‘魔仙堡’ where 学号=‘G12345’
   

3. 删除记录（删除指定表中满足where条件的记录）删除的记录不可恢复
   delete from <表名> [where <条件>]
   栗子：

    //删除学生表中姓名为“胡萝卜”的记录
    delete from <学生表> where 姓名=‘胡萝卜'
    //删除学生表中所有记录行
    delete from <学生表>
   

   先删除从表中的记录，然后才能删除主表中的记录，以保证数据引用的完整性。

索引

分类

• 聚簇索引：索引的顺序与记录存储的物理顺序一致；一个数据表中只有一类聚簇索引。
• 非聚簇索引：索引的顺序与实际记录存储的物理顺序独立；一个数据表中可有多种。
• 唯一索引：索引的这一列的值没有相同的，包括空值。

创建

create [unique] [clustered] [nonclustered] //分别为 唯一索引，聚簇索引，非聚簇索引
index <索引名> on <表名>（<列1><,列2>....）

栗子：

//按学生表中的姓名和性别列创建聚簇索引
create clustered index id_xmxb on 学生（姓名，性别）
//按学生表中的姓名列创建聚簇索引
create clustered index id_xm on 学生（姓名）

查看

sp_helpindex <索引名>

删除

drop index <索引名>
栗子：

//删除当前数据库 “学生表”中以“班级编号”列创建的索引
drop index 学生.学生_班级编号