库的管理
①创建: CREATE DATABASE [IF NOT EXISTS] 库名
②查看:SHOW DATABASES
③修改:库名可以直接修改相对应的存储文件名
ALTER DATABASE 库名+
CHARACTER SET 编码方式
COLLATE 检验方式 设置检验方式(防止出现中文乱码)
Tips:show character set; 查看mysql所支持的字符及校验规则
④删除:DROP DATABASE IF EXISTS 库名
表的管理
PS:所有的表都存储在information_schema.TABLES表中,可根据TABLE_SCHEMA(数据库名)和TABLE_NAME(表名)来查看相关表信息。
①创建:CREATE TABLE [IF NOT EXISTS] 表名(列名,列的类型(长度)[约束]), … )
TIPS:在创建表时,可在每一字段后加 COMMENT ‘注释’作解释
②查看:
所有表:SHOW TABLES
表结构:DESC 表名 || SHOW CREATE TABLE 表名[\G] #可以查看具体创建过程
③修改 :(ADD 、MODIFY、 CHANGE 、DROP 、RENAME TO)
效用 | 表示方式 |
---|---|
添加列或添加列级约束 | ALTER TABLE 表名 ADD COLUMN (列名 列的类型(长度)[约束]) … ) [添加位置 first/after 列名]; Ps:可添加表级约束(ADD [CONSTRAINT pk/uq/ck(约束名)] 约束类型(列名)); |
修改列类型或列级约束 | ALTER TABLE 表名 MODIFY COLUMN 列名 类型 [约束] [修改位置 first/after 列名]; |
修改列名 | ALTER TABLE 表名 CHANGE COLUMN 旧名 新名 类型; |
删除列 | ALTER TABLE 表名 DROP COLUMN 列名; |
修改表名称 | ALTER TABLE 表名 RENAME TO 新名; |
④表的删除:DROP TABLE IF EXISTS 表名;
通常流程:先删除,后新建
⑤表的复制: 可以跨库操作:库名.表名
格式 | 结果 |
---|---|
CREATE TABLE 新表名 LIKE 所复制的表名 | 复制表的格式(包含key和自增长),不复制表中的数据 |
CREATE TABLE 新表名 SELECT 字段 FROM 所复制的表名 | 可以复制部分的字段和数据(但不会复制表中的key和自增长列的标识) |
tips:若需要只复制部分结构,不复制数据,可以使用一个永假的筛选条件
常见约束
约束:限制表中数据,保证数据的一致性。
修改:在创建表的时候和修改表的时候进行设置,数据插入之前。
六大约束 | 效用 |
---|---|
①NOT NULL | 非空 |
②DEFAULT | 默认,使用update进行修改时,也可使得 set *** = deafult |
③PRIMARY KEY | 主键,保证字段的值具有唯一性,并且一定非空 |
④UNIQUE KEY | 唯一性,但可以为空 |
⑤CHECK | 检查约束(mysql不支持),例,CHECK(列名=’ ’ OR 列名=’ ')对插入数据进行限制 |
⑥FOREIGN KEY | 外键(从表添加) |
1)、创建表时添加约束:
列级约束:六大约束都支持,外键约束无效,直接在字段和类型后进行追加
表级约束:除了①②都支持
语法:[CONSTRAINT pk/uq/ck(约束名)] 约束类型 (具体约束列)(可以起别名)
外键:FOREIGN KEY(列名) REFERENCES 主表(列名))[on update 等级 on delete 等级]
tips:外键约束名一般为fk_当前表_主表,外键约束的等级:
等级名 | 效用 |
---|---|
Cascade(级联) | 主动权在主表,主表修改/删除,从表对应进行修改/删除 |
Set null | 主动权在主表,主表修改/删除,从表对应外键设为null(外键子段不能有非空约束) |
No action | 主表不作为,同Restrict |
Restrict | 严格,主表对该字段的修改和删除完全被限制,必须先处理从表对应的值 |
Set default | Mysql Innodb引擎不支持 |
2)、修改表时添加约束:
①使用MODIFY关键字添加列级约束
②使用ADD关键字添加表级约束
3)、删除约束:
非空和默认可用MODIFY修改列,不加约束,会进行覆盖
对于主键、唯一(INDEX)、外键可以使用:ALTER TABLE 表名 DROP 约束类型 列名(主键不需要提供列名)
PS:所有约束存储在information_schema数据库的table_consraints表中
也可使用:SHOW INDEX FROM 表名 #查看表中所有的键(索引)
4)、三个key的特点
key | 特点 |
---|---|
主键 | 保证唯一性,不可为空,一个表至多一个,可以组合(复合主键,不推荐) |
唯一 | 保证唯一性,可为空(只能允许一个null),一表可有多个,可以组合(不推荐) |
外键 | 限制两个表的关系,也可以是一个表中的两个字段 保证该字段的值必须来自于主表关联列(必须是一个键,一般为主键或唯一键)的值,和主表中的类型一致或兼容 插入数据时,先插入主表,再插入从表,删除数据时,先删除从表,再删除主表 |
PS:一般项目用表都不会设置外键,因为外键所在表插入字段时必须要求主表存在,每次都会查看,数据过多时,影响效率,并且在某种特殊需求下,会要求删除主表信息而保留从表信息,有很大影响。
标识列/自增长列(不用手动插入值,系统提供默认的序列值)
①创建表时设置标识列:直接跟在主/唯一/外键(即跟key搭配,数据类型为数值型,且一个表只能有一个)约束之后,AUTO_INCREMENT
ps:SHOW VARIABLES LIKE ‘%auto_increment%’
标识列的起始值 :auto_increment_offset
步长:auto_increment_increment
TIPS:mysql中不支持对起始值进行修改,但可以在插入数值时指定值,此后数据便会根据此值增长
②修改表时设置标识列:通过MODIFY关键字设置标识列
③修改表时删除标识列:通过MODIFY关键字删除标识列(不设置,进行覆盖)