DDL语言(Data Definition Language)(数据定义语言)(数据库的操作)
/*
数据定义语言
数据库和表的管理
一,库的管理
创建,修改,删除
二,表的管理
创建,修改,删除
创建 create
修改 alter
删除 drop
*/
库和表的管理★
existe
/*
不存在
*/
if not exists
/*
存在
*/
if exists
库的管理
/*
语法:
create database 库名;
*/
create database【if not exists】books;
# 如果没有就创建 容错率
caeate datebase if not exists books;
库的修改
/*
库名的修改:
需要暂停mysql服务去直接mysql/data文件夹里面修改去库的名字
修改库的字符集:
语法:
alter database 库名 character set【gbk||utf8】
*/
alter database books character set gbk
库的删除
/*
语法:
drop database【if exists】库名;
*/
drop database books;
drop database if exists books;
表的创建
/*
语法:
create table【if exists】表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
*/
create table book(
id int , #编号
bName varchar(20), #图书名
price double, #价格
authorId int, #作者编号
publishDate datetime #出版日期
)
表的修改
/*
修改列名
修改列的类型或者约束
添加新的列
删除列
修改表名
alter table 表名【add|drop|modify|change column】列名【列类型 约束】;
*/
修改列名
/*语法:
alter table 表名 change【column】旧列名 新列名称 新列类型
*/
修改列的类型或约束
/*语法:
列级:
alter table 表名 modify column 列名称 新列类型 新列参数 新列声明
表级:
alter table 表名 add 【CONSTRAINT 约束名别名】 约束类型(列名,...,列名) #不建议组合
*/
添加新列
/*语法:
这样加的列在表的最后
alter table 表名 add column 列名称 列类型
在某列后面追加
alter table 表名 add column 列名称 列类型 列参数 列声明 [after 某列列明]
加在最前面
alter table 表名 add column 列名称 列类型 列参数 列声明 [first]
*/
alter table user add column name char(20);
删除列
/*
语法:
alter table 表名 drop column 列名
*/
修改表名
/*
语法:
alter table 表名 rename to 新表名
*/
表的删除
/*
语法:
drop table【if exists】表名;
*/
表的复制★
/*
当两个表不在同一个数据库中的时候 我们 可以在被复制的表前加上库的名字 from 数据库名字。表的名字
*/
/*
一,只复制表的全部结构
create table 新表名字 like 被复制的表的名字
*/
create table copy like user
/*
二,复制表的全部结构+全部数据
create table 新表名字
select * from 被复制的表的名字
*/
create table copy2
select * from user
/*
三,复制表的全部结构+部分数据
create table 新表名字
select * from 被复制的表的名字
where 筛选条件
*/
create table copy3
select * from user
where id > 3
/*
四,复制表的部分结构
create table 新表名字
select 你想要的那些字段
from 被复制的表的名字
where false || 不成立的表达式 表达式不成立就没有数据,就只有你想要的字段的表结构
*/
create table copy4
select id,name
from user
where false;
/*
五,跨库复制表
create table 新表名字
select *
from 数据库名字 . 表的名字
*/
create table copy5
select *
from test.user
类型和约束
数值类型
/*
常见的数据类型
数值型:
整形
小数:
定点数
浮点数
字符型:
较短的文本: char varchar
较长的文本: text blob(较长的二进制数据)
日期型:
*/
整形
/*
分类:
tinyint 1
smallint 2
mediumint 3
int/integer 4
bigint 8
特点:
一,如果不设置有无符号,默认是有符号。设置无符号需要加unsigned关键字
二,如果插入的数值唱、唱过了整形的范围,会报out of range异常,并且插入临界值
三,如果不设置长度,会有默认的长度
四,长度代表了显示的最大宽度,使用了zerofill关键字之后会在左边填充0。并且默认会变成无符号整型
*/
unisgned【无符号关键字】★
create table 表名(
id int unsigned
)
zerofifill【零填充关键字】★
/*
设定你id的长度为7 你插入了123 结果会显示为0000123
这个时候自动的变成了无符号了
*/
create table 表名(
id int(7) zerofill
)
小数
/*
分类:
一,浮点型
float(M,D)
double(M,D)
二,定点型
dec(M,D)
decimal(M,D)
特点:
一,M:整数位数+小数位数。 D:小数点后面的位数。 超出范围插入临界值
二,省略(M,D)
float和double,则会根据你插入的数值是多少精度来决定精度
dec和decimal M默认是10,D默认是0
三,定点型的精度较高,如果要求插入的数值的精度较高如货币运算就考虑使用。
语法:
create table 表名(
f1 float,
d2 double,
d3 decimal
)
*/
使用原则★
/*
所选择的类型越简单越好,能保存的数值的类型越小越好
*/
字符类型
/*
较短的文本:
char
varchar
较长的文本:
text
blob(较长的为二进制文本)
较短的二进制:
binary
varbinary
说明:类似char和varchar,不同的是它们保存二进制字符串而不包含非二进制字符串。
枚举:
Enum
说明:枚举类型,要求插入的值必须属于列表指定的值之一 【不区分大小写】
set类型:
set
说明:set和Enum的区别是:set可以选取多个成员,而Enum只能一个 【不区分大小写】
特点:
写法 M的意思 M省略 特点 空间的消耗 效率
char char(M) 最大的字符数 可 默认为1 固定长度的字符 比较消耗 高
varchar varchar(M) 最大的字符数 否 可变长度的字符 比较节省 低
*/
Enum枚举
/*
枚举类型,要求插入的值必须属于列表指定的值之一 不区分大小写
*/
create table user (
id ENUM('a','b','c')
)
insert into user values('A') #成功
insert into user values('a') #成功
insert into user values('b') #成功
insert into user values('c') #成功
insert into user values('g') #失败
Set类型
/*
set类型,set插入的值可以是指定列表中多个成员 不区分大小写
*/
create table tab_set (
id SET('a','b','c')
)
insert into tab_set values('A') #成功
insert into tab_set values('a') #成功
insert into tab_set values('a','b') #成功
insert into tab_set values('a','b','c') #成功
insert into tab_set values('g') #失败
日期类型
/*
timestamp和实际时区有关,更能反映实际的日期,而datetime则只能反映出插入的时的当地时区
timestamp的属性受mysql版本和sqlmode(语法模式)的影响很大
分类:
date:只保存日期
time:只保存时间
year:只保存年
datetime:保存日期+时间
timestamp:保存日期+时间
特点:
字节 范围 时区的影响
datetime: 8 1000~9999 不受
timestamp: 4 1970~2038 受
*/
常见的约束
/*
a: auto_increment:自增长值,用于整数类型,而且必须是一个“key”(就下面2个)
b: primary key : 设置为主键,就是通过该字段的值,可以唯一确定一行数据;并且默认不为空
c: unique key: 唯一键,设定该字段的值是唯一的,不可重复的,但可以为空。
d: not null:设定为不为空
e: default 默认值:设定默认值,则如果插入数据时,该字段没有给值,就使用它。
f: comment ‘字段的说明文字’。
g: engins后面的是mysql的引擎;和性能有关
*/
/*
含义:
一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
NOT NULL:非空,用于保证该字段的值不能为空
比如:姓名,学号等
DEFAULT:默认,用于保证该字段有默认值
比如:性别
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,【并且非空】
比如:学号 id
UNIQUE KEY:唯一,用于保证该字段的值具有唯一性,【可以为空】
比如:座位号
CHECK:检查约束【mysql中不支持】
比如:年龄,性别
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束,用于引用主表中的某列的值【只能用在表级】
比如:学生表(从表)的专业编号,连接专业表(主编)的id
REFERENCES:引用,在外键后用 引用后面跟的是主表的名字和字段
添加约束的时机:
1创建表时
2修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但【外键】和【检查】约束没有效果
表级约束:
除了非空,默认,其他都支持
*/
创建表时添加约束
添加列级约束
/*
语法:
直接在字段名和类型后面追加 约束类型即可
只支持:默认,非空,主键,唯一
*/
create table if not exists stuinfo(
id INT primary key comment'id 主键',
stuName VARCHAR(20) NOT NULL COMMENT'姓名 非空',
gender CHAR(1) CHECK(gender='男',gender='女') COMMENT'性别 检查', #【在mysql中不支持】
seat INT UNIQUE KEY COMMENT'座位号 唯一',
age INT DEFAULT 18 COMMENT'年龄 默认',
majorId INT REFERENCES major(id) COMMENT'专业编号 外键' #【在mysql中列级约束中不起作用】
)
create table if not exists major(
id INT primary key comment'id 专业编号',
majorName VARCHAR(20) not null comment'专业名字'
)
展示索引
/*
展示这个表中的索引
语法:
show index from 表名
*/
show index from stuinfo;
添加表级约束
/*
语法:在各个字段的最下面
create table【if exists】表名(
列名 列的类型,
...
列名 列的类型,
【CONSTRAINT 约束名别名】 约束类型(字段名),
... #字段名可根据情况写多个或者一个
【CONSTRAINT 约束名别名】 约束类型(字段名), #但是建议写一个
【CONSTRAINT 约束名别名】 FOREIGN KEY(从表中的字段) REFERENCES 主表的名字(主表中的字段名)
)
表级约束中不支持:
【默认】
【非空】
所以这个约束直接在表级层面添加即可
表级约束中的别名:
paimary key不能改别名 你改了也还是原来的名字
外键的别名约定俗成 fk_从表名_主表名
*/
create table if not exists stuinfo(
id INT comment'id 主键',
stuName VARCHAR(20) NOT NULL COMMENT'姓名 非空',
gender CHAR(1) COMMENT'性别 检查', #【在mysql中不支持】
seat INT COMMENT'座位号 唯一',
age INT DEFAULT 18 COMMENT'年龄 默认',
majorId INT COMMENT'专业编号 外键', #【在mysql中列级约束中不起作用】
CONSTRAINT pk primary key(id), #主键
CONSTRAINT uq UNIQUE KEY(seat), #唯一
CONSTRAINT ck CHECK(gender='男',gender='女') , #检查 【mysql不支持】
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id) #外键
)
create table if not exists major(
id INT primary key comment'id 专业编号',
majorName VARCHAR(20) not null comment'专业名字'
)
/*
通用的写完就是一般就外键在表级约束书写
其他的都在列级书写
*/
主键和唯一的区别【面试题】
/*
保证唯一性 是否允许为空 表中个数 null 允许组合
主键 √ × 最多一个 NOT NULL √,但不允许
唯一 √ √ 最多一个 可以有多个null √,但不允许
组合:
是在表级约束时 书写多个字段名
【CONSTRAINT 约束名别名】 约束类型(字段名,...,字段名)
效果:
*/
create table if not exists stuinfo(
id INT comment'id 主键',
stuName VARCHAR(20) NOT NULL COMMENT'姓名 非空',
CONSTRAINT pk primary key(id,stuName), #主键 这里书写组合键
)
insert into stuinfo values(1,'hu') #成功 值唯一
insert into stuinfo values(1,'chao') #成功 值唯一
insert into stuinfo values(2,'hu') #成功 值唯一
insert into stuinfo values(2,'chao') #成功 值唯一
insert into stuinfo values(1,'hu') #失败 值不唯一,与第一条重复
#说明: 当组合时就是主键包括的字段同时做唯一比较
外键
/*
1,要求在从表中设置外键关系
2,从表的外键列的类型要与主表中的关联列的类型一致或者兼容,名称无所谓
3,主表中的关联列必须是一个KEY(键,一般是主键,唯一)
4,创建的从表拥有外键是,应该先创建主表
5,插入数据时先插入主表的数据,才能插入从表的数据
6,删除数据时和插入相反,先删除从表的,在删除主表的数据
*/
修改表添加【列级】约束
/*
当表创建好是没有添加约束
支持
默认,非空,主键,唯一
*/
create table if not exists stuinfo(
id INT comment'id 主键',
stuName VARCHAR(20) COMMENT'姓名 非空',
gender CHAR(1) COMMENT'性别 检查', #【在mysql中不支持】
seat INT COMMENT'座位号 唯一',
age INT COMMENT'年龄 默认',
majorId INT COMMENT'专业编号 外键', #【在mysql中列级约束中不起作用】
)
/*
添加列级约束直接用修改表约束语句
alter table 表名 modify column 列名称 新列类型 新列参数 新列声明
*/
alter table stuinfo modify column id int primary key comment'主键'
修改表添加【表级】约束
/*
支持
主键,唯一.外键
语法:
alter table 表名 add [CONSTRAINT 约束名别名] 约束类型(列名,...,列名) #不建议用组合
alter table 表名 add [CONSTRAINT 约束名别名] FOREIGN KEY(从表列名) REFERENCES 主表表名(主表引用列名)
*/
alter table stuinfo add [CONSTRAINT pk] primary key(id)
alter table stuinfo add [CONSTRAINT fk_stuinfo_major] FOREIGN KEY(id) REFERENCES major(id)
修改表删除约束
# 对应列级的约束只能用【modify】修改删除 用表级的drop不会运行成功,也没有效果
/*
删除【非空】约束
alter table 表名 modify column 列名称 新列类型 【null】
*/
alter table stuinfo modify column stuName VARCHAR(20)
alter table stuinfo modify column stuName VARCHAR(20) null
/*
删除【默认】约束
alter table 表名 modify column 列名称 新列类型 【null】
*/
alter table stuinfo modify column age INT
alter table stuinfo modify column age INT null
##############################################################################################
# 对应表级的约束只能用 【drop】 删除 用列级的modify只会运行成功但是没有效果
/*
删除【主键】约束
show index from 表 # 查看表的索引名字
alter table 表名 drop primary key
*/
show index from stuinfo
LATER TABLE stuinfo DROP PRIMARY KEY
/*
删除【唯一】约束
show index from 表 # 查看表的索引名字
alter table 表名 drop index 约束名/别名
*/
show index from stuinfo
alter table stuinfo drop index uq #uq唯一键的别名
/*
删除【外键】约束
alter table 表名 drop FOREIGN key 约束名/别名
*/
alter table stuinfo drop FOREIGN KEY fk_stuinfo_major
在外键上加唯一约束
/*
在外键所在的列添加唯一索引要想删除唯一索引留下外键索引
只能先删除外键 再删除唯一 然后再添加外键
*/
标识列(自增长)[auto_increment]
/*
含义:自增长列
可以不用手动的插入值,系统默认提供序列值
特点:
标识列必须和KEY搭配 主键,唯一,外键。
一个表只能有一个标识列
标识列的类型只能是数值型类型
可以通过插入值来设置初始值
标识列的修改和删除
属于列级约束,所以【modify】就OK
*/
create table if not exists stuinfo(
id INT PRIMARY KEY auto_increment comment'id 主键',
stuName VARCHAR(20) COMMENT'姓名 非空',
)
/*
查看标识列的offset 和 increment 是开始量和增长量
*/
show variables like '%auto_increment%'
/*
修改 增长量 mysql 不支持修改offset
*/
set auto_increment_increment = 3
———-当你发现自己的才华撑不起野心时,就请安静下来学习吧!———-
版权声明:本文为qq_44472728原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。