[大数据]MySQL(5)：SQL之DDL

一、SQL

? SQL（Structured Query Language）结构化查询语言，用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。

分类

1. DDL：数据定义语言：create、alter、drop、truncate
2. DML：数据操作语言：insert、update、delete
3. DQL：数据查询语言：select、from、where
4. DCL：数据控制语言：grant、revoke
5. TCL：事务控制语言：commit、rollback

拼写

1. SQL 语句要以分号;结尾
2. SQL 语句不区分大小写，关键字建议使用大写
3. 可以用倒引号``把表名和列名括起来。
4. 可以使用空格和缩进来增强语句的可读性。

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

注释

? 单行注释：-- 注释内容 或 #注释内容（mysql特有）

? 多行注释：/* 注释 */

二、DDL

? DDL：数据定义语言：create、alter、drop、truncate

（1）库的管理

1、创建数据库（create）

语法：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

例子：

-- eg:
CREATE DATABASE school;

CREATE DATABASE IF NOT EXISTS school
DEFAULT CHARACTER SET utf8		-- 指定默认字符集为utf8
DEFAULT COLLATE utf8_chinese_ci; -- 默认校对规则为utf8_chinese_ci（简体中文，不区分大小写）

2、查看数据库（show）

语法：

SHOW DATABASES [LIKE '数据库名'] ;

SHOW CREATE DATABASE 数据库名;-- 查看创建数据库的语句

例子：

-- eg:
SHOW DATABASES; -- 查看所有数据库

SHOW DATABASES LIKE 'school'; -- 查看具体的数据库

SHOW DATABASES LIKE '%wo%';  -- like模糊查询

3、使用数据库（use）

语法：

USE 库名;

4、修改数据库（alter）

语法：

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名> }

例子：

-- eg:
ALTER DATABASE school
DEFAULT CHARACTER SET gb2312 -- 指定字符集修改为 gb2312
DEFAULT COLLATE gb2312_chinese_ci; -- 默认校对规则修改为 gb2312_unicode_ci

-- 修改数据库名
RENAME DATABASE 旧库名 TO 新库名; 

5、删除数据库（drop）

语法：

DROP DATABASE [IF NOT EXISTS] 库名;

例子：

DROP DATABASE IF NOT EXISTS `school`;

（2）表的管理

1、创建表（create）

语法：

CREATE TABLE [IF NOT EXISTS] `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]  -- 最后一行不加逗号
)[表的类型][字符集设置][注释]

例子：

-- eg：创建学生表
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` int(1) DEFAULT NULL COMMENT '性别 0:女,1:男',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='学生表';

2、修改表（alter）

• 修改表名

ALTER TABLE student RENAME AS students;

• 修改表字符集(eg:字符集修改为 gb2312，校对规则修改为 gb2312_chinese_ci。)

ALTER TABLE student CHARACTER SET gb2312  DEFAULT COLLATE gb2312_chinese_ci;

• 新增字段(常用)

ALTER TABLE student ADD COLUMN age INT(4) DEFAULT NULL COMMENT '年龄'; -- 末尾添加字段(常用)
ALTER TABLE student ADD COLUMN stuId INT(4) FIRST; -- 开头添加
ALTER TABLE student ADD COLUMN stuno INT(11) AFTER name; -- 某个字段后面添加

• 修改字段类型和约束(常用)

ALTER TABLE student MODIFY COLUMN `name` VARCHAR(10) DEFAULT '匿名' COMMENT '姓名';

注意：修改表中的字段时，也要写名字，数据类型，约束等

• 修改字段名

ALTER TABLE student CHANGE COLUMN sex gender INT(1); -- age 改为 gender

注意：修改字段名时，在给定新名称时，要指定列的类型和约束

• 删除字段

ALTER TABLE student DROP COLUMN birthday; 

COLUMN关键字可以省略；

字段名可以用 `` 括起来，避免字段名与关键字相同带了的困扰。

3、删除表（drop || truncate）

DROP TABLE [IF EXISTS] 表名;   -- 删除表
TRUNCATE TABLE [IF EXISTS] 表名; -- 清空表数据

4、查看表

SHOW TABLES [LIKE '表名']; -- 查看有哪些表
SHOW CREATE TABLE 表名; -- 查看创建表的语句

DESCRIBE 表名; -- 查看表结构
DESC 表名; -- 简写

三、约束

? 为了保证表中的数据的准确和可靠性，对用户操作表的数据进行约束。

? 在 MySQL 中，主要支持以下 五种约束：主键约束、外键约束、唯一约束、非空约束、默认值约束。检查约束不支持

（1）主键约束：PRIMARY KEY

? 1. 一个表只能有一个主键，非空+唯一，不允许为空

? 2. 不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。

? 3. AUTO_INCREMENT：自增长，只支持整数类型（TINYINT、SMALLINT、INT、BIGINT

? 4. ZEROFILL ：用0填充，int(4) ->0001

CREATE TABLE test
(
    id INT(11) PRIMARY KEY , -- 1.定义字段的同时指定主键
    name VARCHAR(25)
);

CREATE TABLE test
(
    id INT(11),
    name VARCHAR(25),
    PRIMARY KEY(id) -- 2.定义完所有字段之后指定主键，
);

CREATE TABLE test
(
    id INT(11),
    name VARCHAR(25)
);

ALTER TABLE test ADD PRIMARY KEY(id);  -- 3.修改表时添加主键
ALTER TABLE test DROP PRIMARY KEY;     -- 4.删除主键

?

（2）外键约束：FOREIGN KEY

? 1. 约束两个表中数据的一致性和完整性，被约束的表是从表，约束别人的表是主表，外键是在从表

? 2. 插入数据，先插入主表；删除数据，先删除从表

? 3. 一般不使用物理外键，使用逻辑外键

CREATE TABLE tb_dept  -- 部门表
(
  id INT(11) PRIMARY KEY,
  name VARCHAR(22) NOT NULL
);

CREATE TABLE tb_emp  -- 员工表
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES tb_dept(id)  -- 创建表时设置外键约束
);

-- 修改表时添加外键
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

ALTER TABLE tb_emp ADD CONSTRAINT fk_tb_dept FOREIGN KEY(deptId) REFERENCES tb_dept(id);

-- 删除外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

ALTER TABLE tb_emp DROP FOREIGN KEY fk_tb_dept;

（3）唯一约束：UNIQUE KEY

? 对字段的值不能重复， 唯一字段可以插入多个null

CREATE TABLE tb_dept
(
	id INT(11) PRIMARY KEY,
 	name VARCHAR(22) UNIQUE
);

-- 在修改表时添加唯一约束
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

-- 删除唯一约束
ALTER TABLE <表名> DROP INDEX <唯一约束名>;

（4）非空约束：NOT NULL

? 字段的值不能为空，如果用户在添加数据时没有指定值，数据库系统就会报错。

CREATE TABLE tb_dept
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) NOT NULL,
    location VARCHAR(50)
);

-- 改变字段的非空约束
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;

（5）默认值约束：DEFAULT

? 在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。

CREATE TABLE tb_emp
(
 id INT(11) PRIMARY KEY,
 name VARCHAR(25),
 addr VARCHAR(25) DEFAULT '上海'
);

-- 修改字段的默认约束
ALTER TABLE <数据表名> MODIFY COLUMN <字段名> <数据类型> DEFAULT '北京' COMMENT '地址';

?

（6）检查约束：CHECK

? 在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。

? MySQL 的存储引擎不支持 CHECK 约束。

CREATE TABLE tb_emp
(
 id INT(11) PRIMARY KEY,
 name VARCHAR(25),
 salary decimal(5,2),
 CHECK(salary>0 AND salary<100)
);

-- 在创建表时设置检查约束
ALTER TABLE <表名> ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

-- 在修改表时添加检查约束
ALTER TABLE <表名> ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)