计算机二级MySQL数据库设计学习笔记（2）

一、MySQL语言组成

1. 数据定义语言（DDL）:CREATE, ALTER, DROP
2. 数据操纵语言（DML）:SELECT, INSERT, UPDATE, DELETE
3. 数据控制语言（DCL）:GRANT, REVOKE

二、MySQL常用函数

1. 聚合函数
   count(), sum(), avg(), max(), min()

2. 数学函数
   ABS(), floor(), rand(), sort(), truncate()

3. 字符串函数
   upper()和ucase(), left(s,n), substring(s,n,len)

4. 日期函数
   curdate(), curtime(), now()

5. 其他函数
   if（条件，v1,v2), ifnull(v1,v2)

三、数据库操作

1. 创建数据库
   create database丨schema db_name if not exsists db_name
   default character set charset_name --指定数据库字符集
   default collate collation_name; --指定字符集的校对规则

MySQL中的默认字符集为latin1，默认校对规则为latin1_swedish_ci
简体中文字符集名称为gb2312，校对规则为gb2312_chinese_ci

2. 选择数据库
   use db_name;

3. 查看数据库
   show databases丨schemas;

4. 修改数据库
   alter database丨schema [db_name]
   default character set charset_name --指定数据库字符集
   default collate collation_name; --指定字符集的校对规则

db_name可省略，代表修改当前默认数据库

5. 删除数据库
   drop database丨schema db_name

四、表操作

1. MySQL中常用的数据类型

   数值类型：bit(), int(), decimal(m,d)

decimal()中的两个参数，m代表总位数，d代表小数点后的位数，如果m被省略，默认是10；如果d被省略，默认是0.

日期和时间类型：date, time, year, timestamp
字符串类型：char(), varchar(), text

2.创建表

create table tb_name
(
字段名1 数据类型 默认值，
字段名2 数据类型 默认值，
…
) [engine=引擎类型]；

auto_increment:
只有整型列，且该列是主键或者唯一性约束时，才能设置自增属性
初始值是1
可以使用insert语句更改初始值

关于引擎
oracle和SQL server等数据库管理系统只有一种存储引擎，MySQL有多种存储引擎，也可以自己编写存储引擎，可以使用show engines语句来查看存储引擎。

3.查看表

show tables from 丨in db_name；
该语句可以显示当前数据库中数据表的名称

4.查看数据表的基本结构

show columns from tb_name；
describe tb_name；
上述两条语句可以查看表结构

5.修改表

添加字段：
alter table tb_name
add column 新字段名 数据类型 [约束条件]
[first/after 已有字段名];

例： alter table db_school.tb_student2
add column id int not null unique auto_increment first;

例：alter table db_school.tb_student
add column de-partment varchar(16) default’信息学院’ after nation;

修改字段
alter table 语句提供三种修改字段的语句,分别如下：

change column;
alter table tb_name change column 原字段 新字段 数据类型[约束条件]；
例：alter db_school.tb_student change column birthday age TINYINT null default 18;

该子句可以同时修改表中指定列的名称和数据类型。alter table语句中可以同时添加多个change column子句，只需彼此间用逗号隔开。

alter column;
alter table tb_name alter column 字段名 set/drop default;
例：alter table tb_name alter column department drop default;
alter table tb_name alter column set default ‘经济学院’；

该子句可以修改或删除表中指定列的默认值。

modify column;
alter table tb_name modify column 字段名 数据类型 [约束条件] [first / after 已有字段]
例：alter table db_school.tb_name modify column department varchar(20) not null after studentname;

与change column 子句不同的是，该子句只会修改指定列的数据类型，而不会干涉它的列名。另外，该子句还可以通过after或first关键字修改指定列在表中的位置。

删除字段
alter table tb_name drop column 字段名；
alter table db_school.tb_student2 drop column id;

6.重命名表

alter table 原表名 rename to 新表名；
rename table 原表名 to 新表名；
例：alter db_school.tb_student rename to db_school.backup_tb_student;
rename db_school.backup_tb_student to db_school.tb_student;

7.删除表

drop table [if exists] 表1，表2…;

五、约束

1.主键约束

constraint PK_name primary key(字段名)；

在create table或alter table语句中使用关键字primary key来实现，其方式有两种：

列级完整性约束：只需在某个字段定义后加上关键字primary key.
表级完整性约束：需要在表中所有字段定义后添加一条primary key

 注：如果主键仅仅由一个表中的某一列所构成，上述两种方法均可以定义主键约束；如果主键是由表中的多个列构成的组合，则只能用表级完整性约束方法定义主键约束。

3、候选键约束（唯一约束）

constraint UK_约束名 unique（字段名）；

primary key与unique之间的区别：
	一个表只能创建一个primary key，但可以定义若干个unique。
	定义为primary key 的列不允许有空值，但定义为unique的字段允许空值的存在。
	定义primary key约束时。系统会自动产生primary key索引，而定义unique约束时，系统 自动产生unique索引。

4、外键约束

constraint FK_约束名 foreign key（字段名）references tb_被参照关系（主键字段）；

外键只能引用主键和候选键。所以，被参照关系必须定义主键和候选键。
外键只可以在使用InnoDB存储引擎创建的表中，其他存储引擎不支持。

5、check 约束

constraint CK_name check(约束条件)；

列级check约束定义单个字段需要满足的条件。
表级check约束定义多个字段之间满足的条件。

六、删除约束

1、删除外键约束

alter table 表名 drop foreign key 外键约束名；

当要删除无命名的外键约束时，可先使用show create table语句查看系统给外键约束指定的名称，然后在删除该约束名。

2、删除主键约束

alter table 表名 drop primary key;

3、删除候选键约束

删除候选键约束时，MySQL实际删除的是唯一性索引，应使用drop index子句删除。如果没有给约束命名，drop index子句删除的是定义候选字段的字段名；如果使用了constraint子句给候选键命名，drop index 子句删除的是约束名。

七、添加约束

1、添加主键约束

alter table 表名 add constraint 约束名 primary key（主键字段）；

2、添加外键约束

alter table 表名 add constraint 约束名 foreign key(外键字段) references 被参照表（主键字段）；

3、添加候选键约束

alter table 表名 add constraint 约束名 unique key（字段名）；