mysql数据定义语言DDL

库的管理

创建 create
语法:create database 库名 【character set 字符集】

# 案例:创建库
create database if not exists books;

修改 alter
更改库的字符集

ALTER DATABASE books character set gbk;

删除 drop

drop database if exists books;

表的管理

创建 create

语法: create table 表名(
列名 列的类型(【长度】) 【约束】,
列名 列的类型(【长度】) 【约束】,
列名 列的类型(【长度】) 【约束】
);

# 案例:创建表Book

CREATE TABLE book(
	id INT,# 编号
	bName VARCHAR(20),# mysql字符类型使用VARCHAR
	price DOUBLE,# 价格
	authorId INT,# 作者,作者表
	publishDate DATETIME #出版日期
);

DESC book; # 查询表

# 创建author
CREATE TABLE author(
	id INT,
	auName VARCHAR(20),
	nation VARCHAR(10)
);
DESC author;

修改 alter

语法:alter table 表名 add|drop|change|modify COLUMN 列名 【列类型 约束】

# 修改列名 
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

# 修改列的类型 约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP 【约束】;
# 添加新列 
ALTER TABLE author ADD COLUMN annual DOUBLE;
# 删除列
ALTER TABLE author DROP COLUMN annual;
# 修改表名
ALTER TABLE author RENAME TO bookAuthor;
DESC book;
DESC author;

添加列
语法:alter table 表名 add column 列名 类型 【first|after 字段名】
add是默认添加在字段的最后,如果需要定义顺序在最后修改。

删除 drop

语法:DROP TABLE 表名

表的复制

INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国');

# 1.仅仅复制表的结构,不复制数据
CREATE TABLE copy LIKE author;

# 2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;

# 3.复制部分数据
CREATE TABLE copy3
SELECT id,auName 
FROM author
WHERE nation='中国';

# 4.仅仅复制部分结构，不需要数据
CREATE TABLE copy4
SELECT id,auName 
FROM 库名.author # 复制不同库的表
WHERE 0; # 设置谁也不满足就不会返回数据了

数据类型

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

数值型:
整型:tinyint、smallint、mediumint、int/integer、bigint
小数:定点数、浮点数，
字符型:
较短的文本:char、varchar
较长的文本:text、blob(较长的二进制数据)
日期型: datetime、timestamp

数值型

整型:tinyint、smallint、mediumint、int/integer、bigint
说明:
1.如何设置无符号和有符号
默认有符号

create table tab_int(
    t1 INT ZEROFILL,# 加上就默认变成无符号
    t2 INT unsigned
);

2.如果超出整型范围，会报out of range异常，并插入临界值
3.如果不设置长度有默认的长度，这里的长度代表显示结果的宽度，不足补0(默认不补,设置关键字zerofill,设置后默认无符号) -- 建议不适用

浮点型:float(M,D)、double(M,D)
定点型(最大取值范围与double相同,更精确):DEC/DECIMAL(M,D)
说明:
1.M和D可以省略(dec有默认值M=10,D=0),D小数点后的位数，M=整数的位数+小数的位数(D)
2.如果超出范围，会报out of range异常，并插入临界值
3.定点型精确度高

字符型

较短的文本:
char(M)、varchar(M)
binary/varbinary(二进制)
ENUM('枚举类型1','枚举类型2','仅可以从中选忽略大小写')
Set类似enum一次性可以选择多个插入('a,b')
说明:
1.M最多的字符数(汉字也算一个字符)
2.

说明	char	varchar
最大长度不能超过M	可以省略,默认1	不可以省略
特点	开辟固定长度的空间	varchar根据字符长度开辟空间
空间耗费	较多	较少
效率	略高	略低

较长的文本:text、blob(较长的二进制数据)

日期型
date、datetime、timestamp(受时区、版本影响)、time、year

常见约束

用于限制表中的数据，为了保证表中数据的准确性和可靠性
添加约束的时机(添加数据前)：1.创建表时 2.修改表时
约束的添加分类:
1.列级约束 语法都支持,但外键约束没有效果
2.表级约束 除了非空、默认其余都支持

语法: create table 表名(
列名 列的类型(【长度】) 【约束】,
表级约束
)

六大约束
NOT NULL :非空
DEFAULT:保证该字段有默认值
PRIMARY KRY:主键，用于保证该字段的值具有唯一性且非空
UNIQUE:保证该字段具有唯一性,可以为空
CHECK:检查约束(mysql不支持)
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自另外一个表关联列的值

主键与唯一

类型	保证唯一性	是否支持为空	一个表中的个数	是否允许字段组合
主键	√	×	最多一个	√,但不推荐
唯一	√	√(只能插入一个null)	可以有多个	√，但不推荐

CREATE TABLE stuinfo(
	id INT ,
	stuName CHAR(1) ,
	seat INT,
	age INT, 
	majorid INT,
	PRIMARY KEY(id,stuName),#两个字段组合成一个主键
    PRIMARY KEY(seat,age)
)

外键
1.要求在从表(stuinfo)设置外键类型
2.从表(stuinfo)的外键列的类型和主表(major)的关联列的类型要求一致或兼容
3.主表(major)的关联列必须是一个key(一般是主键或唯一键)
4.插入数据时先major再stuinfo,删除数据时先stuinfo再major

创建表时列级约束
show index from 表名
查看表中所有的索引:主键、唯一、外键、自定义

CREATE DATABASE students;
USE students;

CREATE TABLE stuinfo(
	id INT PRIMARY KEY NOT NULL,#主键 可以添加多个约束
	stuName VARCHAR(20) NOT NULL,# 非空
	seat INT UNIQUE,#唯一
	age INT DEFAULT 18 #默认约束
);

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

创建表时表级约束
语法:【CONSTRAINT 约束名(索引名、主键固定)】(有默认名字) 约束类型(字段名)

CREATE TABLE stuinfo(
	id INT ,
	stuName CHAR(1) ,
	seat INT,
	age INT, 
	majorid INT,
	# 表级约束  up xx 代表索引的名字，主键固定
	CONSTRAINT pk PRIMARY KEY(id),#id设置为主键
	CONSTRAINT up UNIQUE(seat),#seat设置为唯一键
	CONSTRAINT xx FOREIGN KEY(majorid) REFERENCES major(id)
);

通用写法

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL,# 非空
	seat INT UNIQUE,#唯一
	age INT DEFAULT 18, #默认约束
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

修改表时添加约束

列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束

表级约束
ALTER TABLE 表名 add 【CONSTRAINT 约束名】 约束类型(字段名)

# 列级约束...
# 添加非空约束
ALTER TABLE book MODIFY COLUMN stuname varchar(20) not null;
# 添加默认约束
ALTER TABLE book MODIFY COLUMN age int default 18;

# 表级约束写法
ALTER TABLE book add primary key(id)

ALTER TABLE book add CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

修改表时删除约束

删除主键,直接不写是删除不了的
1.主键不是标识列 - 直接drop删除
2.主键是标识列 - 先删去标识列再drop删除

# 删除非空约束,可以不写默认为null
ALTER TABLE book MODIFY COLUMN stuname varchar(20) 【NULL】;
# 删除默认
ALTER TABLE book MODIFY COLUMN age int;
# 删除主键,直接不写 不可以 删除不了
ALTER TABLE book MODIFY COLUMN id int;

# 表级约束写法
ALTER TABLE book drop primary key; # 只能这样写
# 删除唯一
ALTER TABLE book drop index seat(索引名);
ALTER TABLE book drop FOREIGN KEY fk_stuinfo_major;

类型	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持,外键没效果	不可以
表级约束	所有列的下面	默认和非空不支持,其他支持	可以,主键没效果

标识列(自增长列)

可以不用手动插入值,系统提供默认的序列值

说明:
1.两种写法
insert into 表名(列名...不写自增列) values(值1,....)
INSERT INTO tab_identity VALUES(NULL,'john') 用NULL占位置
2.使用范围(是一个key):唯一键、主键、外键、自定义
3.至多有一个标识列
4.标识列的类型只能是数值型

# 创建表时
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);

INSERT INTO tab_identity VALUES(NULL,'john'); #用NULL占位置
INSERT INTO tab_identity(name) VALUES('john'); 

# 可以手动插入值来修改起始值,从10开始
INSERT INTO tab_identity VALUES(10,'john'); 
INSERT INTO tab_identity VALUES(NULL,'john');

SHOW VARIABLES LIKE '%auto_increment%';
# auto_increment_increment 1 默认步长为1
# auto_increment_offset 1 默认从1开始
# mysql中不支持设置起始值
set auto_increment_increment=步长

# 修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id int  PRIMARY KEY AUTO_INCREMENT;
# 修改表时删除标识列,此时id还是主键
ALTER TABLE tab_identity MODIFY COLUMN id int;