mysql基础_07_数据定义语言(DDL)

大纲

库和表的管理

• 库的管理
  • 创建
  • 修改
  • 删除
• 表的管理
  • 创建
  • 修改
  • 删除

创建： create
修改： alter
删除： drop

一、库的管理

---

1. 库的创建

---

• 语法

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

• 注意： 如果库已经存在，再建库就会报错，所以要加上库不存在的条件

2. 库的修改

---

• 注意： 数据库一般不修改，一旦修改会出现各种各样的问题，比如关联，NOT NULL等

2.1 库名的修改

• sql

  RENAME DATABASE oldName TO newName ;
  

• 注意: 此语句非常不安全，可能会导致数据的丢失，强烈不建议使用。需要修改库名时，可以先停止MySQL的服务，然后直接修改MySQL系统中文件夹的名字即可

2.2 修改数据库字符集

• sql

  ALTER DATABASE 库名 CHARACTER SET 字符集；
  

3. 库的删除

---

• 语法

DROP DATABASE [IF EXISTS] 库名；

• 注意： 和创建相同，应该如果存在则删除。

二、 表的管理

---

1. 表的创建

---

• 语法

  CREATE TABLE 表名（
  	列名  列的类型 【（长度） 约束】，
  	列名  列的类型 【（长度） 约束】，
  	...
  	列名  列的类型 【（长度） 约束】
  ）；
  

2. 表的修改

---

2.1 修改列名

• 语法

  ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新列的数据类型 ；
  

• 注意： 修改列名时必须要加上列的数据类型，同时也可以修改数据类型。
• COLUMN 可以省略

2.2 修改列的类型或约束

• 语法

  ALTER TABLE 表名 MODIFY COLUMN 列名  新列的数据类型 ；
  

2.3 添加新列

• 语法

  ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [约束]；
  

2.4 删除列

• 语法

  ALTER TABLE 表名 DROP COLUMN 列名 ；
  

2.5 修改表名

• 语法

  	ALTER TABLE 表名 RENAME TO 新表名;
  

3. 表的删除

• 语法

  DROP TABLE IF EXISTS 表名 ;
  

4. 表的复制

---

4.1 仅仅复制表的结构

CREATE TABLE 表名 LIKE 被复制表的表名 ；

4.2 复制表结构及其数据

CREATE TABLE 表名 SELECT * FROM 被复制表名；

三、列的数据类型

---

• 数值
  • 整数
  • 小数
    • 定点数
    • 浮点数
• 字符型
  • 较短的文本
  • 较长的文本

1. 数值

---

1.1 整型

数据类型	字节	范围
Tinyint	1 B	有符号： -128~127 无符号：0～255
Smallint	2 B	有符号：-32768~32767 无符号：0～65535
Mediumint	3 B	…
Int/Integer	４Ｂ	…
Bigint	8B	…

• 特点：
  ① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
  ② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
  ③ 如果不设置长度，会有默认的长度,长度代表了显示的最大宽度
  ④ 使用 ZEROFILL ,可 长度不够会用0在左边填充

CREATE TABLE tablename (
	l1 INT(7) ZEROFILL         #长度为7 不够7位左边补零
);

1.2 小数

浮点型

浮点数	字节	范围
float	4 B	…
double	8 B	…

定点数浮点型

定点数类型	字节	范围
DEC(M,D) DECIMAL(M,D )	M+2	最大取值范围与double相同，给定decimal的有效取值范围有M和D决定

备注：

• M: 整数和小数两部分的总长度，超过则改为临界值
• D: 小数部分

2. 字符型

---

字符串类型	最多字符数	备注
char(M)	M	固定长度 M为 0~255之间
varchar(M)	M	可变长度 M为 0~255之间
text

3. 二进制

---

• binary
• varbinary
• blob

4. 枚举

---

• ENUM
• sql

	c1 ENUM('a','b','c');

5. 日期

---

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101B0001	2038年的某个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

四、 约束

• 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
• 分类：六大约束
  • NOT NULL：非空，用于保证该字段的值不能为空
  • DEFAULT:默认，用于保证该字段有默认值
  • PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
  • UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
  • CHECK:检查约束【mysql中不支持】,可以设置条件，不满足条件不予插入
  • FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束，用于引用主表中某列的值
• 约束的添加分类：
  • 列级约束：六大约束语法上都支持，但外键约束没有效果
  • 表级约束：除了非空、默认，其他的都支持
• 演示

	CREATE TABLE stuinfo(
		id INT PRIMARY KEY,#主键
		stuName VARCHAR(20) NOT NULL UNIQUE,#非空
		gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查  mysql 无效但不报错
		seat INT UNIQUE,#唯一
		age INT DEFAULT  18,#默认约束
		majorId INT ；
		【CONSTRAINT 外健名】 FOREIGN KEY(majorid) REFERENCES major(id)#外键
	);
	
	CREATE TABLE major(
		id INT PRIMARY KEY,
		majorName VARCHAR(20)
	);

SHOW INDEX FROM 表名；  # 查看表的索引  对表的主键、外健、唯一健自动添加索引。

---

1. 主键和唯一健的区别（面试）

	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	√	×	至多有1个	√，但不推荐
唯一 健	√	√	可以有多个	√，但不推荐

2. 外健

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

3. 修改约束

修改表时添加约束

	/*
	1、添加列级约束
	alter table 表名 modify column 字段名 字段类型 新约束;
	
	2、添加表级约束
	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.删除唯一
	ALTER TABLE stuinfo DROP INDEX seat;
	
	#5.删除外键
	ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

四、 标识列（自增长 AUTO_INCREMENT）

• 又称为自增长列
• 含义：可以不用手动的插入值，系统提供默认的序列值
• 特点：
  • 标识列必须和主键搭配吗？不一定，但要求是一个key
  • 一个表可以有几个标识列？至多一个！
  • 标识列的类型只能是数值型
  • 标识列可以通过 SET auto_increment_increment=3;设置步长
  • 可以通过 手动插入值，设置起始值 （mysql不支持）

#一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT  ,
	NAME FLOAT UNIQUE AUTO_INCREMENT,
	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_increment=3;