一、数据库


1. 新建

CREATE DATABASE [IF NOT EXISTS] 库名;

2. 修改

更改名称:

# 5.7版本已经不可用了
RENAME DATABASE 老名称 TO 新名称;

更改字符集:

ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

3. 删除

DROP DATABASE [IF EXITSTS] 数据库名称;

二、数据表


1. 新建

CREATE TABLE [IF NOT EXISTS] 表名(
    列名1 类型 [约束] [非空] [默认值] [描述]
    列名2 类型 [约束] [非空] [默认值] [描述]
    列名3 类型 [约束] [非空] [默认值] [描述]
    ...
    列名4 类型 [约束] [非空] [默认值] [描述]
)

2. 修改

修改列名:

ALTER TABLE <表名> CHANGE [COLUMN] <老字段名称> <新字段名称> <新字段类型>;

修改类型和约束:

ALTER TABLE <表名> MODIFY COLUMN <字段名称> <字段类型>;

添加新列:

ALTER TABLE <表名> ADD COLUMN <列名> <类型>;

添加新列到指定位置:

# 添加列
ALTER TABLE <表名> ADD COLUMN <列名> <类型> [FIRST | AFTER 已存在列名]

删除列:

ALTER TABLE <表名> DROP COLUMN <列名>;

修改表名:

ALTER TABLE  <表名> RENAME TO <新的表名>;

3. 删除

DROP TABLE [IF EXISTS] <表名>;

4. 复制

仅仅复制表的结构:

CREATE TABLE <表名> LIKE <已存在的表>;

复制表的数据 + 结构:

CREATE TABLE <表名>
SELECT * FROM <已存在的表名>;

复制表的结构+部分数据:

CREATE TABLE <表名>
SELECT [* | 列名1, 列名2, ...]
FROM <已存在的表>
WHERE 对已存在的表的筛选条件;

仅仅复制部分字段:

CREATE TABLE <表名>
SELECT <字段1, 字段2, ...>
FROM <已存在的表>
WHERE <不等式>; # 如 0 | 1=2

三、数据类型


(一)分类

类型描述
数值型整数、小数(定点数、浮点数)
字符型短文本(char, varchar)、长文本(text, blob)
日期型....

(二)整型

1. 分类:

整数类型字节范围(有符号)范围(无符号)
TINYINT1-128-1270-255
SMALLINT2-32768-327670-65535
MEDIUMINT2-8388608-83886070-16777215
INT INTEGER4-2147483648 - 21474836470-4294967295
BIGINT8-2^63到 2^63-10-2^128-1

2. 注意事项:

  1. 默认是有符号的,如果需要无符号的类型需类型后面加上UNSIGNED.
  2. 类型后面的长度代表的是显示的宽度,不是值的大小,如果不够会用0来填充,但是必须搭配ZEROFILL关键字。
  3. 在类型的后面加上ZEROFILL会根据设置的长度来显示值,不够的位数用0填充。
  4. 如果插入的数值超出了整型的范围,会报出out of range异常,并且插入临界值。
  5. 如果不设置长度,会有默认的长度。

3. 示例 (ZEROFILL):

CREATE TABLE IF NOT EXISTS TEST(
    ID INT AUTO INCREMENT;
    PID INT(6) ZEROFILL NOT NULL;
    NAME VARCAHR NOT NULL;
)

根据上述SQL为例,可以看到PID这个字段后面我加上了ZEROFILL关键字,如果我此时执行INSERT INTO TEST(PID, NAME)VALUES(123, 'tt')往表格中插入一条数据;接着我执行SELECT * FROM TEST;你会看到我们刚刚插入的123这个值显示的时候变成了000123,也就是0*3+123,什么意思呢?也就是我们建表的时候指定了这个字段的显示位数为6位,现在我们的值为123也就是3位,那么剩下的3位就会用0来填充。

(三)小数

1. 分类:

类型大小范围(有符号)范围(无符号)描述
FLOAT (浮点数)4(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度、浮点数值
DOUBLE (浮点数)8(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度、浮点数值
DECIMAL(M, D) 或 DEC(M, D)M+2依赖于M和D的值依赖于M和D的值小数值

2. 注意事项:

  1. DEC类型的M代表:整数位+小数位,D代表:小数位,如果超出范围会插入临界值。
  2. 如果不指定长度,FLOAT和DUBLE会默认自增长而DEC则不会,DEC超出范围就会报错。
  3. 如果不指定长度,M默认为10,D默认为0。
  4. 定点型的精确度较高,如果要求插入的精确度较高的话如货币运算则考虑使用DEC类型。

3. 基础原则:

  1. 所选择的类型越简单越好,能保存数值的类型越小越好。

(四)字符型

类型长度空间消耗效率特点
char(M)M: 为0-255 (可省略,默认为1)不可变类型
varchar(M)M: 0-65535 (不可省略)可变类型
Enum(元素1,元素2, ...).........只能选择一个
SET(元素1, 元素2, ...).........可以选择多个
binary.........不可变较短二进制
varbinary.........可变较短二进制

注意事项:

  1. char(M),当M为10时,即便你插入的数据只有2个字符大小,也会为你开辟10个字符的空间。
  2. varchar(M), 当M为10,你插入的数据只有2个字符的大小,他就会给你开辟2个字符的空间。
  3. 当使用Enum()类型的时候,如果插入的数据有大小写的区别,则会被插入元素中的对应值。

(五)日期类型

1. 分类:

类型大小( bytes)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳

2. 注意事项:

类型字节范围时区影响
DATETIME81000-9999不受
TIMESTAMP41970-2038

四、常见约束


(一)分类

名称含义描述
NOT NULL非空用于保证该字段不为空
DEFAULT默认用于保证该字段的默认值
PRIMARY KEY主键用于保证该字段的唯一性,并且非空
UNIQUE唯一用户保证该字段的唯一性,可以为空
CHECK检查...
FOREIGN KEY外键用于限制两个表的关系,保证该字段的值必须来自主表关联列的值;在从表添加外键约束,用于引用主表的某列值

(二)添加约束的时机

  1. 创建表时。
  2. 修改表时。

(三)按位置分类

位置支持约束备注
列级约束六大约束都支持 (外键和检查不起作用)...
表级约束除了非空和默认都支持...

(四)外键约束

  1. 要求要在从表设置关联关系。
  2. 从表的列类型必须和主表关联列的类型保持一致或者兼容,名称并无要求。
  3. 主表的关联列必须是唯一约束或者主键的列
  4. 插入数据时,先插入主表再插入从表。
  5. 删除数据时,先删除从表再删除主表。
  6. 每一列的约束可以为多个,但是这个还是要注意你的业务场景。

(五)创建表时添加约束

1. 列级约束语法

CREATE TABLE IF NOT EXISTS <表名>(
    字段名 <类型> [约束类型],
    ...
    字段名 <类型> [约束类型]
)

2. 表级约束语法:

# 非空和默认不支持
CREATE TABLE [IF NOT EXISTS] <表名>(
    字段名 <类型>,
    ...
    字段名 <类型>,
    CONSTRAINT 别名 约束类型(字段名)
)

# 主键约束示例
CONSTRAINT 别名 PRIMARY KEY(字段名)

# 唯一约束示例
CONSTRAINT 别名 UNIQUE(字段名)

# 外键约束示例
CONSTRAINT 别名 FOREIGN KEY(字段名) REFERENCES <主表(主表字段名)>

3. 主键约束和唯一约束的区别:

名称唯一性是否允许为空是否允许组合备注
主键约束支持,但不推荐...
唯一约束支持,但不推荐虽说可以为空,但是记录中只能出现一条为空的记录(5.5后支持多个)

4. 注意事项:

  1. 在你添加表级约束的时候可以不加CONSTRAINT 别名,这样除了主键之外索引名称是当前约束中字段的名称。

(六)修改表时添加约束

# 列级语法
ALTER TABLE <表名> MODIFY COLUMN <字段名> <字段类型> <约束类型>;

# 表级语法
ALTER TABLE <表名> MODIFY ADD [CONSTRAINT 别名] <约束类型>(字段名);

# 示例
ALTER TABLE `INFO` MODIFY COLUMN `stuname` VARCHAR(20) UNIQUE;

注意事项:

  1. 表级约束支持两种写法

    # 列级写法
    ALTER TABLE <表名> MODIFY COLUMN <字段名> <字段类型> PRIMARY KEY;
    
    # 表级写法
    ALTER TABLE <表名> ADD PRIMARY KEY(字段名);

(七)修改表时删除约束

1. 删除非空约束:

ALTER TABLE <表名> MODIFY COLUMN <字段名> <字段类型> NULL; # 或者不写 默认是NULL

2. 删除默认约束:

ALTER TABLE <表名> MODIFY COLUMN <字段名> <字段类型>; 后面的默认约束去掉就行了

3. 删除主键约束:

ALTER TABLE <表名> DROP PRIMARY KEY;

4. 删除唯一约束:

ALTER TABLE <表名> DROP INDEX <唯一约束的名称>;

5. 删除外键约束:

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束的名称>;

(八)标识列

1. 含义:

又称为自增长列,可以不用手动插入值,系统提供默认的序列值。

2. 语法:

CREATR TABLE IF NOT EXISTS <表名>(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    ...
)

3. 特点:

  1. 标识列不一定要和主键搭配,但是字段必须是一个约束key。
  2. 一张表最多只能有一个标识列。
  3. 标识列的类型必须是数值型。

4. 如何查看步长:

SHOW VARIABLES LIKE '%auto_increment%';

5. 如何修改步长:

SET auto_increment_increment=<size>

6. 修改表时添加标识列:

ALTER TABLE <表名> MODIFY COLUMN 字段名 INT PRIMARY KEY AUTO_INCREMENT;

7. 修改表时删除标识列:

ALTER TABLE <表名> MODIFY COLUMN 字段名 INT PRIMARY KEY;
最后修改:2021 年 02 月 01 日 11 : 01 PM
如果觉得我的文章对你有用,请随意赞赏