数据定义（DDL）

4.数据定义（DDL）

4.1创建与管理数据库

Mysql安装以后，系统会自动创建information_schema和mysql数据库，performance_schema是关于系统监控的一个库，存放的都是运维相关的表，information_schema存放的是元数据，也就是数据字典。

当然，必须首先创建数据库，然后才能创建数据库的数据对象。MySQL可以采用命令行的方式，也可以通过图形管理工具方式创建、操作数据库和数据对象。

4.1.1创建数据库

创建数据库：

CREATE DATABASE/SCHEMA IF NOT EXISTS 数据库名
DEFAULT CHARACTER SET 字符集名
DEFAULT COLLATE 校对规则名

查看当前(默认)数据库：

select database();

查看指定数据库：

SHOW DATABASES 
LIKE '';

字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。字符集：常用的是latin1，中文一般是UTF8和GBK

mysql校对规则命名约定：以相关的字符集名开始，包括一个语言名，以ci(大小写不敏感)、cs（大小写敏感）或_bin（二元）结束。

查看数据支持的字符集和他们的默认校对规则：

show CHARACTER SET;

数据库的字符集和校对规则如果没有指定就使用默认的。

查看字符集：

SHOW VARIABLES LIKE 'CHARACTER%';

查看校对规则：

SHOW VARIABLES LIKE 'collation_%';

4.1.2管理数据库

打开数据库：

USE 数据库名

用来从一个数据库“跳转”到另一个数据库，在用CREATE DATABASE语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。

修改数据库：

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

查看是否修改：

show create database 数据库名;
或
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation_%';

删除数据库：

DROP DATABASE IF EXISTS 数据库名;

显示全部·数据库：

SHOW DATABASES;

退出mysql：

quit;
或
exit;
CTRL+z

4.2创建与管理数据库表

4,2,1创建数据库表

首先要确定表字段类型，常用类型如下：

1.数据类型

1. 数值类型

   MySQL支持所有标准SQL数据类型，包括严格数值数据类型（INTEGER、SMALLINT、DECIMAL和NUMERIC）以及近似数值数据类型（FLOAT、REAL和DOUBLE PRECISION）。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

MySQL允许使用FLOAT（M，D）或REAL（M，D）或DOUBLE PRECISION（M，D）格式，“（M，D）”表示该值一共显示M位有效数字，其中D位位于小数点后面。

2.字符串类型

字符串类型的数据主要是由字母、汉字、数字符号、特殊符号构成的数据对象。按照字符个数多少的不同，字符串类型可分为以下几类。

①CHAR和VARCHAR类型：CHAR和VARCHAR类似，但它们保存和检索的方式不同，它们的最大长度和是否尾部空格被保留等方面也不同，在存储或检索过程中不进行大小写转换。CHAR和VARCHAR类型声明的长度表示用户想要保存的最大字符数。例如，CHAR（30）可以占用30个字符。CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65，535之间的值。（VARCHAR的最大有效长度由最大行大小和使用的字符集确定，整体最大长度是65，533字节）。同CHAR对比，VARCHAR值只保存需要的字符数，另加一个字节来记录长度（如果列声明的长度超过255，则使用两个字节）。VARCHAR值保存时不进行填充。当值保存和检索时，尾部的空格仍保留，符合标准SQL。

在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。

BLOB和TEXT列不能有默认值。

BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。通过更改max_allowed_packet变量的值可以更改消息缓存区的大小，但必须同时修改服务器和客户端程序。

3.日期和时间类型

日期时间类型的数据是具有特定格式的数据，专用于表示日期、时间，包括以下几个类型。

DATE类型：表示日期，输入数据的格式是yyyy-mm-dd,支持的范围是'1000-01-01'到'9999-12-31'。

TIME类型：表示时间，输入数据的格式是hh:mm:ss，TIME的范围是'-838:59:59'到'838:59:59'。TIME类型不仅可以用于表示一天的时间（必须小于24h），还可能为某个事件过去的时间或两个事件之间的时间间隔（可以大于24h，甚至为负）。

③DATETIME类型：表示日期时间，格式是yyyy-mm-dd hh：mm：ss，支持的范围为'1000-01-01 00：00：00'到'9999-12-31 23：59：59'。

timestamp 一般长度为10位，加毫秒是3位，表示的是1970年1月1日后的秒数或毫秒数

2.创建表

使用CREATE TABLE命令可以创建表。

语法格式：

CREATE TABLE [IF NOT EXISTS] 表名
(列名  数据类型  [NOT NULL|NULL][DEFAULT 列默认值]...)
ENGING=存储引擎

MySQL支持的存储引擎有MyISAM管理非事务表。它是默认的存储引擎，提供高速存储和检索，以及全文搜索能力。也可以配置使用另外一个引擎。5.6版本后支持InnoDB,支持事务，多版本并发控制等，后 台系统一般使用这个引擎。

USE bookstore;
CREATE TABLE bookstore.book (
book_id CHAR (10) NOT NULL PRIMARY KEY COMMENT '图书编号', -- 字段类型后的书写顺序可以自由切换
book_type VARCHAR (20) NOT NULL DEFAULT '计算机' COMMENT '类型',
name VARCHAR (40) NOT NULL COMMENT '书名',
author CHAR (10) NOT NULL COMMENT '作者',
publish_name VARCHAR (20) NOT NULL COMMENT '出版社',
publish_time DATE NOT NULL COMMENT '出版日期',
unitprice FLOAT (5,0) NOT NULL COMMENT '单价',
qty INT (5) COMMENT '数量',
account FLOAT (3,1) COMMENT '折扣',
head_page BLOB COMMENT '封面图片'
) COMMENT ='书籍明细' ENGINE = INNODB;

4.2.2管理数据库表

1.修改表结构

ALTER TABLE命令可以用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的注释和表的类型。

语法格式：

ALTER [IGNORE] TABLE 表名
ADD [COLUMN] 列名 [FIRST|AFTER 列名]            /*添加列*/
|ALTER [COLUMN] 列名 {SET DEFAULT 默认值|DROP DEFAULT} /*修改默认值*/
|CHANGE [COLUMN] 旧列名 列定义[FIRST|AFTER 列名]/*对列重命名*/
|MODIFY [COLUMN] 列定义 [FIRST|AFTER 列名]     /*修改列类型*/
|DROP [COLUMN] 列名                            /*删除列*/
|RENAME [TO] 新表名                           /*重命名该表*/

例如：

ALTER TABLE book ADD COLUMN a TINYINT;  /*新增a列*/
ALTER TABLE book ADD COLUMN d TINYINT AFTER a; /*a后新增d*/
ALTER TABLE book ADD COLUMN b TINYINT FIRST ; /*表头增加b*/
ALTER TABLE book ALTER COLUMN a SET DEFAULT 1;/*修改指定列默认值*/
ALTER TABLE book CHANGE a b integer;  /*修改列的名称*/
ALTER TABLE book MODIFY b VARCHAR(20);  /*修改指定列的类型、注释*/
ALTER TABLE book
MODIFY COLUMN `b` int COMMENT '学号' FIRST,
MODIFY COLUMN `c` varchar(20) COMMENT '姓名' AFTER `b`;
ALTER TABLE book DROP COLUMN b;  /*从表中删除列，默认值*/
ALTER TABLE book ALTER COLUMN e drop DEFAULT ;
ALTER TABLE book ALTER book_type DROP DEFAULT ;
ALTER TABLE book RENAME TO book1;  /*修改表名*/
RENAME TABLE book TO book1;/*修改表名*/
ALTER TABLE book ADD scan_cnt INT NULL COMMENT '浏览次数',
DROP COLUMN name;   /*增加浏览次数，删除书名*/

2.复制表

当需要建立的数据库表与已有的数据库表结构相同时，可以通过复制表的结构和数据的方法实现。

语法格式：

CREATE TABLE [IF NOT EXISTS] 新表名 [LIKE 参照表名]|[AS(elect 语句)]                          /*复制表*/
CREATE TABLE IF NOT EXISTS book2 LIKE book ;
CREATE TABLE IF NOT EXISTS book4 AS select * from book;

语法说明：

LIKE：使用LIKE关键字创建一个与参照表名结构相同的新表，列名、数据类型、空指定和主（外）键索引、主键、分区也将复制，但是表的内容、参照性约束不会复制。因此创建的新表名是一个空表。

AS：使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。select语句表示一个表达式，可以是一条SELECT语句。

3.删除表

需要删除一个表时，可以使用DROP TABLE语句。

语法格式：

DROP TABLE [IF EXISTS] 表名1 [,表名2]···
DROP TABLE IF EXISTS book2,book3,book4;   /*删除多个表*/

语法说明：

IFEXISTS：避免要删除的表不存在时出现错误信息。这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。（如果A表的主键被别的表参照作为外键，同时在别的表中设置了restrict模式，drop A表会报错）

4.显示数据表信息

(1)显示数据表文件名

SHOW TABLES命令用于显示已经建立的数据表文件。

语法格式：

SHOW TABLES

(2)显示数据表结构

DESCRIBE语句用于显示表中各列的信息。

{DESCRIBE|DESC} 表名[列名|通配符]
DESC book;
DESC book book_id;
DESC book 'book%';
​

语法说明：

DESCRIBE|DESC：DESC是DESCRIBE的简写，二者用法相同。

列名|通配符：可以是一个列名，或一个包含‘%’和‘_’通配符的字符串，用于获得对于带有与字符串相匹配的名称的各列的输出。没有必要在引号中包含字符串，除非其中包含空格或其他特殊字符

(3)查看建表语句

SHOW CREATE TABLE 表名

5.清空表

truncate table sc;--降低表的水位，HWM，实践当中经常truncate的是临时表或者事实表的表分区

4.3完整性约束

数据完整性指的是数据的一致性和正确性，完整性约束是指数据库的内容必须随时遵守的规则。如果定义了数据完整性约束，MySQL会负责数据的完整性，每次更新数据时，MySQL都会测试新的数据内容是否符合相关的完整性约束条件，只有符合完整性约束条件的更新才被接受。

例如，为保证数据的完整性，需要对输入的数据进行以下检查。

输入的类型是否正确？

——年龄必须是数字

输入的格式是否正确？

——Email必须包含@符号

是否在允许的范围内?

——性别只能是”男”或者”女”

是否存在重复输入？

——同一员工信息输入了两次

是否符合其他特定要求？

——信誉值大于5的客户才能够加入客户表

数据完整性约束分为实体完整性（Entity Integrity）、域完整性（Domain Integrity）、参照完整性（Referential Integrity）及用户定义的完整性（User-defined Integrity）其含义如图1所示。

数据约束与完整性之间的关系如表1所示。

4.3.1默认约束

用于约束该字段有默认值，约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值

CREATE TABLE TEST (ID INT DEFALUT NULL);  /*ID默认为空*/

4.3.2非空约束

约束字段值不能为空。

CREATE TABLE TEST (ID INT NOT NULL);

4.3.3主键约束

1.定义主键

#创建表book_copy，将书名定义为主键。
CREATE TABLE BOOK_COPY 
(book_id varchar(6) not null,
name varchar(20) primary key not null,--notnull可以忽略
pub_date date default null);
#创建course表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。其中学号、课程号和毕业日期构成复合主键。
CREATE TABLE COURSE
(STU VARCHAR(6),
NAME VARCHAR(20),
GRAD_DATE DATE,
CNOVAR CHAR(3),
CREDITS int,
PRIMARY KEY(STU,CNO,GRAD_DATE));
#增加一个name，作为上题复合主键的一部分，使用B树索引
ALTER TABLE `bookstore`.`course`
MODIFY COLUMN `NAME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL AFTER`STU`,
DROP PRIMARY KEY,
ADD PRIMARY KEY(`STU`,`CNO`,`GRAD_DATE`,`NAME`) USING BTREE;

2.定义主键的原则

原则上，任何列或者列的组合都可以充当一个主键，但是主键列必须遵守一些规则。这些规则源自于关系模型理论和MySQL所制定的规则。

(1)每个表必须定义一个主键

(2)唯一性规则

表中两个不同的行在主键上不能具有相同的值。

(3)最小化规则

复合主键不应该包含一个不必要的列。一个列名在一个主键的列表中只能出现一次。

3.删除主键

ALTER TABLE `demo`.`book` DROP PRIMARY KEY;

4.3.4替代键约束(唯一约束)

替代键（代理键）像主键一样，是表的一列或一组列，它们的值在任何时候都是唯一的。因为一个表只能有一个主键，当一个表有多个列需要建立唯一性约束时，替代键是没有被选做主键的候选键。定义替代键的关键字是UNIQUE。

关键字UNIQUE表示“书名”是一个替代键，其列值必须是唯一的。替代键也可以定义为表的完整性约束

在MySQL中，替代键和主键的区别主要有以下几点：

1、数量不同

一个数据表只能创建一个主键，但可以有若干个UNIQUE键，并且它们甚至可以重合。例如，在C1和C2 列定义了一个替代键，并且在C2和C3列定义了另一个替代键，这两个替代键在C2列上重合，MySQL允许这样。

2、NULL设置不同

主键字段的值不允许为NULL，字符类型可以为空，UNIQUE字段可取NULL

3、索引不同

创建PRIMARY KEY约束时，系统自动产生PRIMARY KEY索引。创建UNIQUE约束时，系统自动产生UNIQUE索引。

对于已经创建好的表，可以使用ALTER TABLE语句向表中添加约束。语法格式如下：

ALTER TABLE 表名
ADD PRIMARY KEY [索引方式 ]（列名，…）    /*添加主键*/
| ADD UNIQUE [索引名]（列名，…）          /*添加替代键约束*/
| DROP PRIMARY KEY                     /*删除主键*/
| DROP INDEX 索引名                     /*删除索引*/

MySQL可以同时创建多个约束。注意，使用PRIMARY KEY的列，必须是一个具有NOT NULL属性的列。

4.3.5参照性约束

1.概念

在数据库中，有很多规则是和表之间的关系有关的。例如，学生只有注册后才可以参与考试，才可以录入成绩。因此，在成绩表中的所有学生（由学号来标识）必须是学生注册表中的学生，也就是说存储在成绩表中的所有学号必须是学生注册表学号列中的学号。这种类型的关系就是参照完整性约束（referential integrity constraint），如下图所示：