sqlmysql
7. 尚硅谷MySQL创建和管理表.pdf
7. 尚硅谷MySQL数据类型 .pdf
8. 尚硅谷MySQL约束和分页.pdf
数据定义语言

库和表的管理

库的管理

创建库

**CREATE DATABASE [IF NOT EXISTS] 库名 [CHARACTER SET 字符集];**

修改库

RENAME DATABASE 旧库名 TO 新库名; ❌此句已作废，非要修改关闭MySQL，到data里修改文件名
**ALTER DATABASE 库名 CHARACTER SET 字符集;** 更改字符集

删除库

**DROP DATABASE [IF EXISTS] 库名;**

MySQL命名规则

数据库名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
不能在对象名的字符间留空格
必须不能和用户定义的其他对象重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了 ```sql
1、库的创建
案例：创建库Books
CREATE DATABASE IF NOT EXISTS books;

2、库的修改

RENAME DATABASE books TO 新库名;❌

更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

3、库的删除

DROP DATABASE IF EXISTS books;

<a name="fJKsg"></a>
### 表的管理
1. 创建表
`**USE 库名;**`<br />`**CREATE TABLE 表名(**`<br />`**列名 列的类型 [(长度) 约束],**`<br />`**...**`<br />`**列名 列的类型 [(长度) 约束]**`<br />`**);**`
`**DESC 表名;**`
2. 修改表 alter
`**ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 [约束 first|after 字段名]**`<br />可以设置新加字段所在表的位置<br />`**ALTER TABLE 表名 DROP COLUMN 字段名;**`<br />`**ALTER TABLE 表名 MODIFY COLUMN 字段名 新字段类型 新约束;**`<br />`**ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 字段类型 [约束];**`<br />`**ALTER TABLE 表名 RENAME TO 新表名;**`
3. 删除表
`**DROP TABLE [IF EXISTS] 表名;**`
4. 表的复制
`**CREATE TABLE 表名 LIKE 被复制表名**` 只复制表结构<br />`**CREATE TABLE 表名 查询语句;**` 复制表结构+数据
```sql
#二、表的管理
#1.表的创建 ★
#案例：创建表Book
CREATE TABLE book (
    id          INT,                    #编号
    bName       VARCHAR(20),    #图书名
    price       DOUBLE,                #价格
    authorId    INT,                    #作者编号
    publishDate DATETIME            #出版日期
);
DESC book;
#案例：创建表author
CREATE TABLE IF NOT EXISTS author (
    id      INT,
    au_name VARCHAR(20),
    nation  VARCHAR(10)
)
DESC author;
#2.表的修改
#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#③添加新列并设置位置
ALTER TABLE 表名 ADD COLUMN 字段名 类型 [first|after 字段名];
ALTER TABLE author ADD COLUMN annual DOUBLE;
#④删除列
ALTER TABLE book_author DROP COLUMN annual;
#⑤修改表名
ALTER TABLE author RENAME TO book_author;
DESC book;
#3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
#通用的写法：
DROP DATABASE IF EXISTS 库名;
CREATE DATABASE 库名;
DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名 (
  ...
);
#4.表的复制
INSERT INTO author
VALUES (1, '村上春树', '日本'),
       (2, '莫言', '中国'),
       (3, '冯唐', '中国'),
       (4, '金庸', '中国');
SELECT * FROM Author;
#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
#2.复制表的结构 + 数据
CREATE TABLE copy2
SELECT * FROM author;
#只复制部分数据
CREATE TABLE copy3
SELECT id, au_name
FROM author
WHERE nation = '中国';
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id, au_name
FROM author
WHERE 0;

常见类型

分类
数值型
整型
小数
定点数
浮点数
字符型
较短的文本：char、varchar
较长的文本：text、blob-较长的二进制数据
日期型

数值型

整型

分类

tinyint	smallint	mediumint	int/integer	bigint
1	2	3	4	8

特点

如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
如果不设置长度，会有默认的长度

长度代表了显示的最大宽度，并不是范围
如果不够会用0在左边填充，但必须搭配zerofill使用，并且默认变为无符号整型

#1.如何设置无符号和有符号
CREATE TABLE tab_int(
    t1 INT,
    t2 INT UNSIGNED
);
DESC tab_int;
INSERT INTO tab_int VALUES(-123456, 0);
INSERT INTO tab_int VALUES(-123456, -123456);    #-123456,0❌
INSERT INTO tab_int VALUES(2147483648, 4294967296);    #❌
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
    t1 INT(7) ZEROFILL,
    t2 INT(7) UNSIGNED ZEROFILL
);
INSERT INTO tab_int VALUES(123, 123);
SELECT * FROM tab_int;    # 0000123, 0000123

小数

分类

浮点型

**float(M, D)**
**double(M, D)**

定点型

**dec(M, D)**或**decimal(M, D)**
特点

M：整数部位+小数部位

D：小数部位
如果超过范围，MySQL5.7不执行

M和D都可以省略

如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

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

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

#测试M和D
DROP TABLE tab_float;
CREATE TABLE tab_float(
    f1 FLOAT(5, 2),
    f2 DOUBLE(5, 2),
    f3 DECIMAL(5, 2)
);
SELECT * FROM tab_float;
DESC tab_float;
INSERT INTO tab_float VALUES(123.4523, 123.4523, 123.4523);
INSERT INTO tab_float VALUES(123.456, 123.456, 123.456);    #均四舍五入
INSERT INTO tab_float VALUES(123.4, 123.4, 123.4);    #第三个后面补0
INSERT INTO tab_float VALUES(1523.4, 1523.4, 1523.4);    #三个都报错

字符型

较短的文本
**char**
**varchar**
其他
binary 和 varbinary 用于保存较短的二进制
enum 用于保存枚举，只能n选1
set 用于保存集合，与enum的不同是可以多选
较长的文本
**text**
**blob** 较大的二进制
特点

	写法	M的意思	特点	空间的耗费	效率
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

CREATE TABLE tab_char(
    c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');    #报错不执行
INSERT INTO tab_char VALUES('A');    #a 不区分大小写
CREATE TABLE tab_set(
    s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');    #a,b
INSERT INTO tab_set VALUES('a,c,d');

日期型

分类
**date** 只保存日期
**time** 只保存时间
**year** 只保存年
**datetime** 保存日期+时间
**timestamp** 保存日期+时间
特点

	字节	范围	时区等的影响
datetime	8	1000—9999	不受
timestamp	4	1970—2038	受

CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(), NOW());
SHOW VARIABLES LIKE 'time_zone';
SET time_zone='+9:00';    #🔴设置时区
SELECT * FROM tab_date;    #timestamp的时间会随着时区改变

常见约束

含义
一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类
六大约束

**NOT NULL** 非空，用于保证该字段的值不能为空。比如姓名、学号等
**DEFAULT** 默认，用于保证该字段有默认值。比如性别
**PRIMARY KEY** 主键，用于保证该字段的值非空唯一。比如学号、员工编号等
**UNIQUE** 唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
**CHECK** 检查约束【mysql中不支持】。比如年龄、性别
**FOREIGN KEY** 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号

添加约束的时机

创建表时

**CREATE TABLE 表名(**
**字段名字段类型列级约束,**
**字段名字段类型,**
**表级约束**
**);**
约束的添加分类
列级约束
语法
直接在字段名和类型后面追加约束类型即可
只支持：默认、非空、主键、唯一，外键没效果

CREATE DATABASE students;
USE students;
DROP TABLE IF EXISTS stuinfo;
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 REFERENCES major (id)                                                #外键❌
);
CREATE TABLE major(
        id        INT PRIMARY KEY,
    majorName VARCHAR(20)
);
#🔴查看stuinfo中的所有索引，包括主键、外键、唯一会生成索引
SHOW INDEX FROM stuinfo;    #查看表中所有的索引

表级约束
支持：主键、唯一、外键
语法
在各个字段的最下面
**[constraint 约束名] 约束类型(字段名)**

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo (
    id      INT,
    stuname VARCHAR(20),
    gender  CHAR(1),
    seat    INT,
    age     INT,
    majorid INT,
    CONSTRAINT pk PRIMARY KEY (id),        #主键
    CONSTRAINT uq UNIQUE (seat),            #唯一键
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)    #外键
);
SHOW INDEX FROM stuinfo;
#通用的写法：🔴
CREATE TABLE IF NOT EXISTS stuinfo (
    id      INT PRIMARY KEY,
    stuname VARCHAR(20),
    sex     CHAR(1),
    age     INT DEFAULT 18,
    seat    INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)
);

主键和唯一的对比

	保证唯一性	是否允许为空	可以有多少个	是否允许组合
主键	✔️	❌	至多有1个主键	✔️但不推荐
唯一	✔️	✔️	可以有多个唯一	✔️但不推荐

外键

要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
主表的关联列必须是一个key（一般是主键或唯一）
插入数据时，先插入主表，再插入从表

删除数据时，先删除从表，再删除主表
但是，

  - 级联删除，alter table语句添加外键结尾追加 `**ON DELETE CASCADE**`，则可直接删除主表，从表相应行同时被删除
  - 级联制空，alter table语句添加外键结尾追加 `**ON DELETE SET NULL**`，则可直接删除主表，从表相应行外键同时被置null

修改表时

添加列级约束

**alter table 表名 modify column 字段名字段类型新约束;**

添加表级约束

**alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];**

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo (
    id      INT,
    stuname VARCHAR(20),
    gender  CHAR(1),
    seat    INT,
    age     INT,
    majorid INT
);
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY (id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE (seat);
#5.添加外键
ALTER TABLE stuinfo
    ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id);

修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一，这里seat是唯一键名
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键，这里fk_stuinfo_major是外键名
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;

标识列

又称为自增长列
含义
可以不用手动的插入值，系统提供默认的序列值
注意

标识列必须和主键搭配吗？不一定，但要求是一个key
一个表至多一个标识列
标识列的类型只能是数值型
标识列可以通过

**SET auto_increment_offset = 100;** 设置起始序号
**SET auto_increment_increment = 3;** 设置步长
也可以通过手动插入值，设置起始值
创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) UNIQUE,
    seat INT
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(id, NAME) VALUES (NULL, 'john');
INSERT INTO tab_identity(NAME) VALUES ('lucy');
SELECT * FROM tab_identity;
SHOW VARIABLES LIKE '%auto_increment%';
set auto_increment_offset  = 100;
INSERT INTO tab_identity(id, NAME) VALUES (100, 'john');    #也可以在插入首个数据时写上
SET auto_increment_increment = 3;

修改表是设置标识列

#修改
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#删除
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;

DDL