文章目录
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
- char(M):
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.