【MySQL数据库】DDL语言，库和表的管理

DDL语言：库和表的管理

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。

一、库的管理：

1、创建库

create database (if not exists) 库名
create database if not exists 库名;#如果当前库已存在，则不创建
create database 【if no exists】 库名 【character set 字符集名】

二、库的修改

# 更改库名【已经废弃了，不安全】
rename database books to 新库名;
# 更改库的字符集
alter database books characher set gbk;

三、删除库

drop database 库名

二、表的管理：

1、创建表

CREATE TABLE 【IF NOT EXISTS】 stuinfo(
	stuId INT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME
);
DESC studentinfo;

2、修改表

alter

# 语法：alter table 表名 add(添加)|drop(删除)|modify(修改)|change(改变名) column 列名 【列类型 约束】;
# 1)修改列名
ALTER TABLE studentinfo change column 旧列名 新列名 类型;
# 2)修改表名
ALTER TABLE stuinfo RENAME 【TO】  studentinfo;
# 3)修改列的类型或约束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;
ALTER TABLE studentinfo MODIFY COLUMN name varchar(50);
# 4)添加新列
ALTER TABLE studentinfo ADD COLUMN 列名 类型 【first|after 【列名2】】;#放在列名2的前面或者后面，不设置列名2则为最前或最后
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;#在最前面添加列
ALTER TABLE studentinfo ADD COLUMN QQ VARCHAR(20) after email;#在email列后面加上一列QQ

# 5)删除列
ALTER TABLE studentinfo DROP COLUMN email;

3、删除表

drop TABLE 【IF EXISTS】 studentinfo;

4、表的复制

# 1)仅仅复制表的结构
create table copy2 
like author;
# 2)复制表的结构+数据
create table copy2
select * from author;
# 3)只复制部分数据
create table copy3
select id,au_name
from author
where nation='中国';
# 4)只复制某些字段
create table copy4
select id,au_name
from author
where 0;# 没有数据满足条件

三、常见类型

1、整型：

• tinyint：1字节，

• smallint：2字节

• mediumint：3字节

• int、integer：4字节

• bigint：8字节

#特点：
# 1、默认为有符号整数，如果要设置无符号整数，则使用 unsigned 关键字
create table tab_int{
    ti int,
    t2 int unsigned
}
# 2、如果插入的数值超过了整形的范围，会报错out of range异常，并且插入临界值
# 3、对于整数，其大小只与其设置的类型有关，与字段宽度无关。如果加上zerofill约束，默认设置为无符号整数，且当整数宽度小于设置值时，左边用0填充。

2、小数：

• 浮点型

  • float(M,D)：4字节
  • double(M,D)：8字节

• 定点型

  • dec(M,D)：m+2个字节
  • decimal(M,D)：m+2个字节

#特点
# 1、M：整数部分+小数部分的总长度，超过则插入临界值999.99；D：小数点后的位数
create table tab_float{
	f1 float(5,2),
	f2 double(5,2),
	f3 decimal(5,2)
}
# 2、M和D可以省略
#如果是decimal，则M默认为10，D默认为0
#如果是float和double，则会根据插入的数值的精度来确认精度。

# 3、定点型的精确度较高，如果要求插入数值的精度较高，如货币运算等则考虑使用

原则：所选择的类型越简单越好，能保存数值的类型越小越好。

3、字符型/串数据：

• 较短的文本：char、varchar
  • char(M)：
    • M是最多的字符数，可以省略，默认为1
    • 固定长度的字符。
    • 比较耗费空间
    • 性能稍微高一点
    • 比如sex：男、女，长度固定的，用char。
  • varchar(M)：
    • M是最多的字符数，不可以省略
    • 可变长度的字符。
    • 比较节省空间
    • 性能稍微低一点
    • 字符串长度变化大，就可以使用varchar

4、较长的文本：

text系列、blob（较长的二进制数据）

5、日期型：

• date：4字节，最小值1000-01-01，最大值9999-12-31

• datetime：8字节，最小值1000-01-01 00:00:00

  • 只能反映出插入时的当地时区

• timestamp：4字节，最小值19700101080001，最大值2038年的某个时刻

  • 容易受到实际时区的影响，也受到MySQL版本和SQLMode的影响。

• time：3字节，最小值-838:59:59，最大值838:59:59：

• year：1字节，最小值1901，最大值2155

• # 查看时区
  show variables like 'time_zone';
  # 切换时区
  set time_zone='+9:00';
  

6、Blob类型：

7、其他:

• binary、varbinary，保存二进制

• 枚举类型：保存枚举

  • insert插入操作的时候不区分大小写
  • 

• set类型：保存set集合

  • insert的时候不区分大小写
  • 

四、常见约束

1、常见约束

NOT NULL：非空，用于保证该字段的值不能为空
DEFAULT：默认，用于保证该字段有默认值
UNIQUE：唯一，用于保证字段值具有唯一性，可以为空
CHECK：检查约束【mysql中不支持，语法不报错，无效果，做兼容，运行sqlserver的sql语句不报错】
PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空
FOREIGN KEY：外键，用于限制两个表的关系，用于保证改字段的值，必须来自主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。
	比如学生表的专业编号，员工表的部门编号和工种编号
	外键所绑定的字段必须为主键

多个约束通过空格隔开！

添加约束的时机：
	1、创建表时
	2、修改表时
约束的添加分类：
	1、列级约束
		六大约束语法上都支持，但外键约束没有效果
	2、表级约束
		除了非空、默认，其他的都支持

2、【实战】创建表时添加约束

create database students;
use students;
create table stuinfo(
	id int primary key,#主键
	stuName varchar(20) not null,#非空
	gender char(1) check(gender='男' or gender='女'),#检查约束，没效果
	seat int unique,# 唯一
	age int default 18,#默认约束
	majorid int foreign key references major(id)#外键连接major的id属性
)
create table major(
	id int primary key,
	majorNmae varchar(20)
)

show index from stuinfo;#查看所有的索引，包括主键、外键、唯一

3、【实战】添加表级约束

# 在各个字段的最下面，【constraint 约束名】 约束类型(字段名)
create table stuinfo(
	id int,
    stuname varchar(20),
    gender char(1),
    seart int,
    age int,
    majorid int,
    constraint pk primary key(id),#为id添加主键
    constraint uq unique(seat),#唯一键
    constraint ck check(gender='男' or gender='女'),#检查
    constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键
    #添加外键的时候，在末尾上加上on delete cascade：级联删除
    #cascade：级联
    #on delete set null：删除时，设置为null
)
create table major(
	id int primary key,
	majorNmae varchar(20)
)

4、【实战】通用

create table stuinfo(
	id int primary key,#主键
	stuName varchar(20) not null,#非空
	gender char(1),
	seat int unique,# 唯一，可以存在多个
	age int default 18,#默认约束
	majorid int ,
    constraint fk_stuinfo_major foreign key(majorid) references major(id)#表级约束外键连接major的id属性
)
create table major(
	id int primary key,
	majorNmae varchar(20)
)

组合主键，在表级约束中添加主键，里面的多个字段通过逗号隔开，当插入数据的组合主键完全相同，就会报错。允许但不推荐！主键最好只有一个字段！

5、【实战】添加约束

#添加列级约束
alter table stuinfo modify column stuname varchar(20) not null;
#添加表级约束
alter table stuinfo add primary key(id);

#添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

6、【实战】删除约束

# 直接改列就行了，不给约束，或者替换约束

7、标识列：自增

auto_increment#自增，和约束使用方法一致，一个表最多有一个自增列
unique auto_increment#组合使用，非空自增！
show variables like '%auto_increment%';#查看系统默认步长和起始值
#起始值自定义后无效，步长可以设置为3，当前数据库下所有的步长都为3
set auto_increment_increment=3;
#第一次插入的值有效，比如第一行数据插入自增字段为10，则初始值为10.