数据库学习笔记2

1、结构化查询语言

SQL（Structured Query Language）的组成

• 数据定义语言DDL（Data Definition Language）：用来定义和管理数据对象，如数据库、表等。

			create 、 drop 、alter

• 数据操作语言DML（Data Manipulation Language）：用来操作数据库对象中包含的数据。

			insert 、 update 、delete

• 数据查询语言DQL（Data Query Language）：用来对数据库中的数据进行查询。

			select

• 数据控制语言DCL（Data Control Language）：用来管理数据库，包括管理权限和数据更改。

			grant 、commit 、 rollback

2、MySQL的列（字段）类型

类型

数据类型	字节数	取值范围	说明
tinyint	1字节	有符号值：-2 ^ 7 ~ 2 ^ 7 - 1 ; 无符号值：0 ~ 2 ^ 8 - 1	非常小的数据
smallint	2字节	有符号值：-2 ^ 15 ~ 2 ^ 15 - 1 ; 无符号值：0 ~ 2 ^ 16 - 1	较小的数据
mediumint	3字节	有符号值：-2 ^ 23 ~ 2 ^ 23 - 1 ; 无符号值：0 ~ 2 ^ 24 - 1	中等大小的数据
int	4字节	有符号值：-2 ^ 31 ~ 2 ^ 31 - 1 ; 无符号值：0 ~ 2 ^ 32 - 1	标准整数
bigint	8字节	有符号值：-2 ^ 63 ~ 2 ^ 63 - 1 ; 无符号值：0 ~ 2 ^ 64 - 1	较大的整数
float	4字节	± 1.1754351E - 38	单精度的浮点数
double	8字节	± 2.2250738585072014E - 308	双精度的浮点数
decimal	对decimal(m,d)，如果m>d，为m+2否则为d+2	有符号值：-2 ^ 7 ~ 2 ^ 7 - 1 ; 无符号值：0 ~ 2 ^ 8 - 1	字符串形式的浮点数

2、字符串类型

类型	说明
char[ m ]	定长字符串，m为0~255之间的整数
varchar[ m ]	变长字符串，m为0~65535之间的整数
tinyblob	允许长度0~255字节，值的长度+1个字节
blob	允许长度0~65535字节，值的长度+2个字节
tinytext	允许长度0~255字节，值的长度+2个字节
text	允许长度0~65535字节，值的长度+2个字节
varbinary[ m ]	允许长度0~m个字节的变长字符串，值的长度+1个字节
binary[ m ]	允许长度0~m个字节的定长字符串

3、日期和时间类型

类型	字节数	取值范围	说明
date	3字节	1000-01-01 ~ 9999-12-31	日期，格式：YYYY-MM-DD
time	3字节	-838:59:59 ~ 838:59:59	时间，格式：HH:MM:SS
datetime	8字节	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	格式：YYYY-MM-DD HH:MM:SS
timestamp	4字节	19700101000000 ~ 2037年的某个时刻	时间戳，格式：YYYMMDDHHMMSS
year	1字节	1901 ~ 2155	年份，格式：YYYY

注：
1、每个时间和日期列类型都有一个零值，当插入非法数值时就用零值来添加
2、时间戳列类型主要用于记录更改或创建某个记录

4、NULL值

null表示空值

• 参与算数运算符时，结果仍为null
• 0或null都为假，1为真。

3、创建表

基本语法

create table [if not exists] `表名`(
	`字段名1` 列类型 [属性] [索引] [注释],
	`字段名2` 列类型 [属性] [索引] [注释],
		...
	`字段名n` 列类型 [属性] [索引] [注释]
)[engine=存储引擎][charset=字符集][注释];

数据字段属性

• unsigned （无符号的）
  • 声明该列不允许负值
• zerofill （0填充）
  • 不足位数的用0来填充
  • 如某个字段类型int(4)，赋值为50，那么查询显示结果为 0050
• auto_increment
  • 自增长，每添加一条数据，值自动 +1
  • 通常为主键，整数类型
  • 可以定义起始值和步长
• null 和 not null
  • 默认为null，表示没有插入任何值
  • not null 表示非空
• default
  • 默认值
  • 部分字段可以默认为 ‘男’ ，‘这个家伙很懒什么都没留下’ 等
• comment
  • 为字段、表添加注释
  • 方便查看了解字段信息等

数据表的存储引擎

MySQL中数据表的存储引擎

• InnoDB、MyISAM、HEAP、BOB、CSV等
• 数据表以文件方式存放包括表文件、数据文件和数据库选项文件；位置：MySQL安装目录下的Data目录中

名称	MyISAM	InnoDB
事务处理	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约两倍

InnoDB：

• 安全性高，支持事务处理及多用户操作
• .frm文件及上级目录的ibdata1文件

MyISAM：

• 节约空间，检索速度快
• .frm：主要存放表的元数据（meta），包括表结构定义的信息等
• .MYD：数据文件
• .MYI：主要存放索引信息

设置字符集

数据库、数据表、数据列都可以设置不同的字符集

charset = utf8

修改默认字符集
1、修改MySQL的my.ini文件（需要重启服务）

default-character-set = utf8
character_set_server = utf8

2、使用MySQL命令

4、修改表

alter table

#重命名
alter table 新表名 rename as 旧表名;
#添加字段
alter table 表名 add 新字段 [新字段的属性];
#修改字段（针对于属性的修改）
alter table 表名 modify column 修改字段名 修改类型 [修改属性];
#修改字段（x修改字段名及类型）
alter table 表名 change 原字段名 新字段名 修改类型 [修改属性];
#删除字段
alter table drop 删除字段名;

5、删除表

基本语法

drop table [if exists] 删除表名;

6、数据完整性

实体完整性

• 主键约束、唯一约束、标识列

域完整性

• 检查约束、数据类型、默认值、非空约束、外键约束

引用完整性

• 外键约束

自定义完整性

• 存储过程、触发器

7、创建约束

使用 constraint 关键字来创建约束。
创建数据表时，可以直接为字段绑定约束；

# 主键约束（创建字段时）
`字段名` 列类型 primary key [属性];
primary(`添加主键字段名`);
# 外键约束
constraint foreign key 自定义约束名(`本表中需要绑定外键的字段名`) references 绑定的表名(`绑定的字段名`);
# 唯一标识约束
`字段名` 列类型 unique [属性];
unique(`添加唯一标识约束字段名`);

也可以在数据表创建后，为字段绑定相应的约束。

# 添加主键
alter table 添加主键表名 modify column `添加主键的字段` 列类型 primary key [属性];
alter table 添加主键表名 add constraint 自定义约束名 primary key (`添加主键的字段`); 
alter table 添加主键表名 modify column `添加主键的字段` 列类型 [属性] , add primary key (`添加主键的字段`);
# 添加外键
alter table 需要添加外键的表名 add constraint 自定义约束名 foreign key (`需要绑定外键的字段名`) references 绑定表名(`绑定的字段名`);
# 添加唯一标识约束
alter table 添加唯一约束表名 modify column `添加唯一标识的字段` 列类型 unique [属性];
alter table 添加唯一约束表名 constraint 自定义约束名 unique (`添加唯一标识约束字段`);

primary key 约束

• 约束唯一标识数据库中的每条记录。
• 主键必须包含唯一的值。
• 主键列不能包含null值（not null）。
• 每个表都应该有一个主键，并且每个表只能有一个主键。
• 联合主键与复合主键：
  • 联合主键体现在多表,复合主键体现在一个表的多个字段。

create table course(
id mediumint  auto_increment comment '主键id',
name varchar(30) comment '课程名称',
primary key(id)
)
engine = myisam charset = utf8 comment = '课程'

create table IF NOT EXISTS stu_cour(
id mediumint  auto_increment comment '主键id',
stu_id mediumint comment '学生表id',
cour_id mediumint comment '课程表id',
primary key(id)
)
engine = myisam charset = utf8 comment = '学生课程表'

• 其中 id 就表示联合主键。

create table student(
name varchar(30) comment '姓名',
age smallint comment '年龄',
sex varchar(2) comment '性别',
primary key(name,age)
)
engine = myisam charset = utf8 comment = '学生'

• 其中 name age 就表示复合主键。

unique 约束

• unique约束唯一标识数据库中的每条记录。
• unique 和 primary key 约束均列或列集合提供唯一性的保证。
• primary key 拥有自动定义的 unique 约束。
• 每个表可以有多个unique约束，但是每个表只能有一个primary key约束。