表的创建
常见的数据类型
数值型：
整型 tinyint、smallint、mediumint、int/integer、bigint
特点：
1.如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
2.如果插入的数值超出了正型的范围，会报out of range异常，并且插入临界值
3.如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
小数型
浮点型：float(M,D) double(M,D) 定点型：decimal(M,D)
特点：
1.M：位数(整数部位+小数部位) D：(小数部位) 如果超过范围，则插入临界值
2.M和D都可以省略 如果是decimal，则M默认是10，D默认是0 如果是float和double，则会根据插入的数值的精度来决定精度
3.定点型的精度较高，如果要求插入的数值精度要求高如货币运算等则考虑使用
字符型
char varchar text blob(较大的二进制) enum(用于保存枚举) set(用于保存集合)
特点
| 写法 | M的意思 | 特点 | 空间的耗费 | 效率 | |
|---|---|---|---|---|---|
| char | char(M) | 最大的字符数，可以省略，默认为1 | 固定长度的字符 | 比较耗费 | 高 |
| varchar | varchar(M) | 最大的字符数，不可以省略 | 可变长度的字符 | 比较节省 | 低 |
日期型
date 只保存日期 time 只保存时间 year 只保存年
datetime 保存日期+时间(不受时区影响) timestamp 保存日期+时间(受时区影响)
常见约束
NOT NULL 非空 DEFAULT 默认值 PRIMARY KEY 主键 UNIQUE 唯一 FOREIGN KEY 外键 check
列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空，默认，其他都支持
主键约束和唯一约束区别：
| 唯一性 | 是否允许为空 | 可以有多个NULL | 是否允许组合 | |
|---|---|---|---|---|
| PRIMARY KEY | √ | × | 最多有1个 | √，但不推荐 |
| UNIQUE | √ | √ | 可以有多个 | √，但不推荐 |
外键特点：
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3.主表的关联列必须是一个key(一般是主键或唯一)
4.插入数据时，先插入主表，后插入从表；删除数据时，先删除从表，后删除主表
#可以通过下面两种方式删除主表的记录
#方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON SET NULL;
标识列：
标识列又称自增长列
特点：
1.标识列必须和主键搭配吗？不一定，但要求是一个key
2.一个表可以有几个标识列？至多一个
3.标识列的类型只能是数值型
4.标识列可以通过
show variables like '%auto_increment%'; 查看标识列的设置值
set auto_increment_increment=3; 设置步长 1 4 7 10
create table if not exists 表名(
列名 列的类型(长度) 约束,
列名 列的类型(长度) 约束,
列名 列的类型(长度) 约束,
.
.
.
列名 列的类型(长度) 约束
);
CREATE TABLE students(
id INT PRIMARY KEY AUTO_INCREMENT, #主键 自增
stuName VARCHAR(20) NOT NULL, #非空
sex CHAR(1),
age INT DEFAULT 18, #默认
seat INT UNIQUE, #唯一
majorid INT,
FOREIGN KEY(majorid) REFERENCES major(id) #外键
);
表的修改
#修改表名
alter table 表名 rename to 新表名;
#添加新列
alter table 表名 add column 列名 类型;
#修改列名
alter table 表名 change column 旧列名 新列名 类型;
#修改列的类型或约束
alter table 表名 modify column 列名 类型;
#删除列
alter table 表名 drop column 列名;
表的删除
drop table [if exists] 表名;
表的复制
#仅仅复制表的结构
create table 新表名 like 来源表;
#复制表的结构和数据
create table 新表名 select * from 来源表;
#只复制部分数据
create table 新表名 select id,name from 来源表 where id>5;
#仅仅复制某些字段
create table 新表名 select id,name from 来源表 where 0;
修改表时添加约束
/*
添加列级约束
alter table 表名 modify column 列名 类型 约束;
添加表级约束
alter table 表名 add 【constraint 约束别名】 约束类型(字段名) 【外键的引用】;
*/
#添加非空约束
ALTER TABLE students MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#添加默认约束
ALTER TABLE students MODIFY COLUMN age INT DEFAULT 18;
#添加主键
#1.列级约束
ALTER TABLE students MODIFY COLUMN id INT PRIMARY KEY;
#2.表级约束
ALTER TABLE students ADD PRIMARY KEY(id)
#添加唯一约束
#1.列级约束
ALTER TABLE students MODIFY COLUMN seat INT UNIQUE;
#2.表级约束
ALTER TABLE students ADD UNIQUE(seat)
#添加外键约束
ALTER TABLE students ADD CONSTRAINT major_id FOREIGN KEY(majorid) REFERENCES major(id);
修改表时删除约束
#删除非空约束
ALTER TABLE students MODIFY COLUMN stuname VARCHAR(20) NULL;
#删除默认约束
ALTER TABLE students MODIFY COLUMN age INT;
#删除主键
ALTER TABLE students DROP PRIMARY KEY;
#删除唯一
ALTER TABLE students DROP INDEX seat;
#删除外键约束
ALTER TABLE students DROP FOREIGN KEY 约束别名;