MySQL之DDL（数据定义语言）

DDL：数据定义语言

主要用于数据库和表的管理和操作

库的管理

创建数据库

CREATE DATABASE IF NOT EXISTS stuDB

删除数据库

drop databses if exists stuDB;

表的管理

创建表:

create table if not exists 表名(

字段名 字段类型 【字段约束】,

字段名 字段类型 【字段约束】,

字段名 字段类型 【字段约束】

);

数据类型：

• 整形：tinyint、samllint、int、bigint

• 浮点型：float、double、decimal

• 字符型：char、varchar、tinytext、text

• 日期型：date（YYYY-MM-DD）、time(HH:mm:ss)、datetime、timestamp(时间戳)

• 二进制型：blob(图片)

常见六大约束：

名称	类型	说明
非空约束	NOT NULL	用于限制该字段为必填项
默认约束	DEFAULT	用于限制该字段没有填写参数值，则为默认值
主键约束	PRIMARY KEY	用于限制该字段的值不能重复、默认不为空
唯一约束	UNIQUE	用于限制该字段的值不能重复
检查约束	CHECK	用于限制该字段的值必须满足指定条件（mysql不支持语法不报错）
外键约束	FOREIGN	用于限制两个表的关系 ，要求外键的值必须来自主表的关联列

CREATE TABLE IF NOT EXISTS stuinfo(
​	stuid INT	NOT NULL PRIMARY KEY COMMENT '主键',		
​	stuname VARCHAR(20) UNIQUE	NOT NULL COMMENT '姓名',		#唯一、非空
​	studender	CHAR(1)	DEFAULT '男' COMMENT '性别',			#默认
​	email VARCHAR(20) NOT NULL COMMENT '邮箱',					#非空
​	age INT CHECK(age BETWEEN 0 AND 200) COMMENT '年龄',	#检查
​	classid INT COMMENT '班级id',
​	CONSTRAINT fk_stuinfo_class FOREIGN KEY (classid) REFERENCES	class(id)	#外键
)ENGINE=INNODB DEFAULT CHARSET=utf8;

修改表:

语法： ALTER TABLE 表名 （ADD|MODIFY|CHANGE|DROP） COLUMN 字段名 字段类型 【字段约束】

修改表名：
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  数据库.`表名`  //即复制结构又复制数据

数据库建表规范

• 【强制】表达式与否概念的字段，必须使用is_xxx的命名方式，数据类型是unsigned tinyint(1) 例：is_delete

• 【强制】表名、字段名必须使用小写字母+数字，禁止出现数字开头，进制两个下划线中间只有数字

• 【强制】表名不使用复数名词

• 【强制】禁用保留字，如desc、range、match、delayed等

• 【强制】主键索引名为pk_字段名，唯一索引名为uk_字段名,普通索引名为idx_字段名

• 【强制】小数类型为decimal，禁止使用float、double

• 【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型

• 【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000

• 【强制】表必备三个字段：id、gmt_create、gmt_modified

• 【推荐】表的命名最好是业务名称_表的作用

• 【推荐】库名与应用名尽量一致

• 【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释

• 【推荐】字段允许适当的冗余，以提高查询性能，但必须考虑数据的一致性

  ​ ​ 冗余字段应遵循：

  1. 不是频繁修改的字段
  2. 不是varchar超长字段，更不能使text字段

• 【推荐】单表行数超过500万行或者单表容量超过2GB,才推荐分库分表

• 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度

数据库表设计三大范式

第一范式：要求数据库表都应该有主键，并且每一列都是不可再分割的原子数据项

第二范式：所有非主键字段完全依赖主键，不能产生部分依赖

​ 多对多，三张表，关系表两个外键

第三范式：所有非主键字段，直接依赖主键，不能产生传递依赖

一对多，两张表，多的表加外键

一对一两种方案：

  														1. 主键共享
                         														2. 外键唯一

提醒：在实际开发中，以满足客户需求为主，有时候会拿冗余换执行速度