数据库命名规范--通用
数据库的基本操作
创建数据库:
MySQL安装完成后，要想将数据存储到数据表中，首先得先创建一个数据库。创建数据库就是在数据库系统中划分一块存储数据的空间。
在MySQL中，创建数据库的基本语法格式如下所示。
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option];CREATE {DATABASE | SCHEMA}：在MySQL中，DATABASE 和SCHEMA都代表数据库，可以使用 CREATE DATABASE或者CREATE SCHEMA创建指定名称的数据库。
IF NOT EXISTS：可选项，用于判断当前创建的数据库是否存在，如果不存在，才会执行创建数据库的操作。
db_name：创建数据库的名称，不能和当前已经存在的数据库的名称重名。
create_option：可选项，用于设置所创建的数据库的特征，如下所示
[DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
}说明：
1、 CHARACTER SET [=] charset_name ：设置数据库的默认字符集， show character set; 查看可用的字符集。
2、COLLATE [=] collation_name ： 设置数据库的默认校对集，为字符集指定比较和排序规则，show collation; 查看可用的校对集。
3、ENCRYPTION [=] {'Y' | 'N'} ：MySQL8.0.16引入的选项，设置是否要对数据库加密。
示例：创建数据库itcast，设置字符集为gbk，校对集为gbk_bin
create database if not exists itcast character set gbk collate gbk_bin;结果：Query OK, 1 row affected (0.00 sec)
Query OK：执行SQL语句成功。
1 row affected：执行后影响了数据库中的一条记录。
(0.00 sec)：执行所花时间。
关于名称大小写的问题
windows平台下，数据库名、表名和字段名都不区分大小写；
Linux平台下，数据库名和表名严格区分大小写，字段名不区分。
语法格式的说明
（1）中括号（[]）中的内容为可选项。
（2）[,...]表示，前面的内容可以重复。
（3）大括号（{}）和竖线（|）表示选择项，在选择项中仅需选择其中一项。
查看数据库
语法：SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr];
说明：
1、LIKE 'pattern'：like子句，查询出匹配该模式的数据库，"%"匹配任意长度的字符串，"_"匹配一个字符。
2、WHERE expr：WHERE子句，匹配指定条件的数据库。
示例： 查看所有数据库
show databases;
MySQL自动创建的数据库如下：
- information_schema:主要存储系统中的一些数据对象信息，如用户表信息、列信息、字符集信息等
- mysql：存储系统的用户权限信息。
- performance_schema：用于存储系统性能相关的动态参数。
- sys：基于information_schema和performance_schema，封装了一层更易于调优和诊断的系统视图。
查看数据库创建语句的语法
SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;
示例：
mysql> SHOW CREATE DATABASE itcast;
+----------+-----------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------+
| itcast | CREATE DATABASE `itcast`
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */
/*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)/*!...*/这样的注释语句，是mysql用于保存兼容的语句。如果在/*!后添加了版本号，则仅当MySQL的版本等于或者高于指定的版本号时才会执行注释中的语句。
选择数据库
USE <数据库名>
例如：选择数据库itcast作为后续操作的默认数据库
USE itcast执行后输出Database changed。
查看当前选择的是哪个数据库
SELECT DATABASE();
修改提示符
prompt 当前数据库:\d 当前主机:\h 当前用户:\u> 修改数据库特征
语法：
ALTER {DATABASE | SCHEMA} [db_name] alter_option;
说明：db_name可选项，是数据库名称，alter_option为要修改的特征
alter_option:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
| READ ONLY [=] {DEFAULT | 0 | 1}说明：
示例：将数据库itcast的字符集修改为gbk。
ALTER DATABASE itcast DEFAULT CHARACTER SET gbk;删除数据库
当数据库不再使用时应该将其删除，以确保数据库存储空间中存放的是有效数据。删除数据库是将已经创建的数据库从磁盘空间上清除，数据库清除之后，数据库中的所有数据也将一同被删除。
语法：DROP DATABASE IF EXISTS 数据库名称;
注意：删除数据库后，数据库中的所有数据都将被清除，原来分配的空间也将被回收！
数据类型
使用MySQL数据库存储数据时，不同的数据类型决定了MySQL存储数据方式的不同。
数值类型
在数据库中，经常需要存储一些数值，例如员工的工资、员工的工号、员工的年龄等，适合用数字类型来保存，数值类型包括整数类型、浮点类型、定点类型、BIT类型等。
整数类型
根据整数的取值范围和存储方式的不同，将整数类型分为5种。分别是TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。不同整数类型的取值范围可以根据字节数计算出来，例如，TINYINT类型的整数占用1个字节，1个字节是8位，那么，TINYINT类型无符号数的最大值就是2的8次方-1，即255。同理可以算出其它不同整数类型的取值范围。
浮点数类型和定点数类型
定点数类型（DECIMAL）表示精度确定的小数类型，适合用来于对精度要求比较高的数据。
浮点数类型分为两种，分别是单精度浮点数类型（FLOAT）和双精度浮点数类型（DOUBLE）。
BIT（位）类型
BIT（位）类型的字段通常用于存储bit值。
日期与时间类型
YEAR
YEAR类型用于存储年份数据。只需要记录年份时，使用YEAR更为方便和节省空间。
DATE
DATE类型用于存储日期数据。如果数据要用来记录年月日，通常使用DATE类型。
TIME
TIME类型用于存储时间数据。如果数据要用来记录时分秒，通常使用TIME类型。
DATETIME
DATETIME类型用于存储日期和时间的数据。如果数据要用来记录年月日时分秒，可以使用DATETIME类型。
TIMESTAMP
TIME类型TIMESTAMP类型用于表示日期和时间，它的显示形式和DATETIME类似。
字符串和二进制类型
为了存储字符串、图片和声音等数据，MySQL提供了字符串和二进制类型。
字符串类型：
| 数据类型 | 类型说明 |
| CHAR | 固定长度的字符串 |
| VARCHAR | 可变长度的字符串 |
| BINARY | 固定长度的二进制数据 |
| VARBINARY | 可变长度的二进制数据 |
| BOLB | 二进制大数据 |
| TEXT | 大文本数据 |
| ENUM | 枚举类型值 |
| SET | 字符串对象，可以有零或多个值 |
二进制类型：BIT类型
数据表的基本操作
创建数据表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(字段名1 数据类型1 [列级约束1]
[字段名2 数据类型2 [列级约束2]][,...]
[,表级约束(字段名3[,字段名4][,...])] [,...])
[table_options] [partition_options];TEMPORARY：表示临时表，仅在当前会话中可见，并且在会话关闭时自动删除。
约束：用于保证数据的完整性和有效性的规则。
partition_options：表示分区选项，用于设置数据表分区的内容。
查看数据表
使用SHOW TABLES语句查看数据表：
SHOW TABLES [LIKE 'pattern' | WHERE expr];用SHOW CREATE TABLE查看数据表创建语句和表的字符编码：
SHOW CREATE TABLE 表名;使用DESCRIBE语句查看数据表结构信息，其中包括字段名、字段类型等信息：
DESCRIBE 表名;mysql> DESC tb_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int | YES | | NULL | |
| dname | varchar(14) | YES | | NULL | |
| loc | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)Field：表示数据表中字段的名称，即列的名称。
Type：表示数据表中字段对应的数据类型。
Null：表示该字段是否可以存储NULL值。
Key：表示该字段是否已经建立索引。
Default：表示该字段是否有默认值，如果有，将显示对应的默认值。
Extra：表示与字段相关的附加信息。
纵向结构显示
使用语句结束符“\G”
修改数据表结构
语法：ALTER TABLE tbl_name [alter_option [, alter_option] ...] [partition_options]
修改表名
ALTER TABLE 旧表名 RENAME [TO] 新表名;
修改数据表的字符集
ALTER TABLE dept CHARACTER SET=gbk;
修改字段名（两种方式）
RENAME COLUMN仅可以修改字段名，CHANGE子句不仅可以修改字段名称，还可以重新定义字段的数据类型、约束、排列位置。
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
注意 ：新数据类型不能为空。
修改字段的数据类型（两种方式）
MODIFY仅可以对字段的数据类型和排列位置重新定义，而CHANGE不仅可以对字段的数据类型和排列位置重新定义，还可以修改字段名称。
ALTER TABLE 表名 MODIFY 字段名 数据类型;
修改字段的排列位置（两种方式）
ALTER TABLE dept CHANGE local_name local_name CHAR(20) FIRST;
ALTER TABLE表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2
“字段名1”指的是修改位置的字段
添加字段
ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件][FIRST|AFTER 已存在字段名]
“FIRST” 是可选参数，用于将新添加的字段设置为表的第一个字段。
“AFTER”是可选参数，用于将新添加的字段添加到指定的“已存在字段名”的后面。
删除字段
ALTER TABLE 表名 DROP 字段名;
删除数据表
语法：DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE];
注意：创建数据表时，表和表之间可能会存在关联，要删除这些被其它表关联的表比较复杂，将在后面的章节进行讲解，本节讲解的是删除没有关联关系的数据表。
表的约束
为了防止数据表中插入错误的数据，MySQL定义了一些规则维护数据库中数据的完整性和有效性，这些规则即表的约束。常见的约束有非空约束、唯一约束、主键约束、外键约束和默认值约束。
| 约束条件 | 说明 |
| PRIMARY KEY | 主键约束，用于唯一标识对应的记录 |
| FOREIGN KEY | 外键约束 |
| NOT NULL | 非空约束 |
| UNIQUE | 唯一性约束 |
| DEFAULT | 默认值约束，用于设置字段的默认值 |
表中列举的约束条件均是针对表中字段进行的限制，从而保证表中数据的正确性和唯一性。
非空约束
非空约束指的是字段的值不能为NULL ，必须是NULL值之外的其他的具体值。
1、设置非空约束
（1）创建数据表时设置非空约束 在字段的数据类型后面追加NOT NULL。
（2）修改数据表时添加非空约束 在ALTER TABLE语句中通过MODIFY或CHANGE重新定义字段的方式添加非空约束。
2、删除非空约束
非空约束的删除，可以通过ALTER TABLE语句中的MODIFY或CHANGE重新定义字段的方式实现。
3、案例：创建数据表时设置非空约束。
例如，在数据库中创建一个用于存放部门信息的数据表tb_dept01，部门信息中部门地址不允许为NULL值，数据表tb_dept01的表结构信息如下所示：
| 字段名称 | 数据类型 | 约束 | 备注说明 |
| deptno | INT | 部门编号 | |
| dname | VARCHAR(14) | 部门名称 | |
| local | VARCHAR(13) | 非空约束 | 部门地址 |
具体SQL语句如下所示：
CREATE TABLE tb_dept01(
deptno INT,
dname VARCHAR(14),
local VARCHAR(13) NOT NULL
);4、案例：修改数据表时使用MODIFY添加非空约束
例如，将数据表tb_dept01中的dname字段添加非空约束
ALTER TABLE tb_dept01 MODIFY dname VARCHAR(14) NOT NULL;5、案例：修改数据表时使用CHANGE删除非空约束
例如，删除tb_dept01数据表中dname字段的非空约束。
ALTER TABLE tb_dept01 CHANGE COLUMN dname dname varchar(14);唯一约束
唯一约束用于保证数据表中字段的唯一性，即表中字段的值不能重复出现：
字段名 数据类型 UNIQUE;
设置唯一约束
（1）创建数据表时设置唯一约束方式有两种，一种是设置列级约束，一种是设置表级约束。 （2）修改数据表时添加唯一约束在ALTER TABLE语句中通过MODIFY或CHANGE重新定义字段的方式添加，也可以通过ALTER TABLE语句中的ADD添加。
删除唯一约束
默认情况下所创建的索引名称和字段名称一致，如果想要删除字段中已有的唯一约束，可以通过ALTER TABLE语句中的“DROP 索引名”实现。
通过案例演示创建数据表时设置唯一约束。 例如，在数据库中创建一个用于存放员工信息的员工表tb_emp01，员工表中员工工号不能重复；相同部门中不能包含相同的员工姓名；员工职位不能为NULL值。员工表的表结构信息如下所示。
| 字段名称 | 数据类型 | 约束 | 说明 |
| deptname | VARCHAR(16) | 联合唯一约束 | 部门名称 |
| empno | INT | 唯一约束 | 员工工号 |
| ename | VARCHAR(16) | 联合唯一约束 | 员工姓名 |
| job | VARCHAR(16) | 非空约束 | 员工职位 |
| | VARCHAR(30) | 员工邮箱 |
创建数据表tb_emp01，具体SQL语句如下所示。
CREATE TABLE tb_emp01 (
deptname VARCHAR ( 16 ),
empno INT UNIQUE,
ename VARCHAR ( 16 ),
job VARCHAR ( 16 ) NOT NULL,
email VARCHAR ( 30 ),
UNIQUE ( deptname, ename )
);通过案例演示修改数据表时使用ADD添加唯一约束。 例如，为数据表tb_emp01中的email字段添加唯一约束，具体SQL语句及执行结果如下所示。
ALTER TABLE tb_emp01 ADD UNIQUE(email);通过案例演示使用DROP删除唯一约束。 例如，将数据表tb_emp01中的empno字段唯一约束删除，具体SQL语句及执行结果如下所示。
ALTER TABLE tb_emp01 DROP index empno;主键约束
在MySQL中，为了快速查找到表中的某条记录，可以通过设置主键约束来实现，主键约束相当于非空约束和唯一约束的组合，要求被约束字段中的值不允许重复，也不允许出现NULL值。
单字段主键指的是由一个字段构成的主键：
字段名 数据类型 PRIMARY KEY
多字段主键指的是由多个字段组合而成的主键：
PRIMARY KEY (字段名1,……)
设置主键约束
（1）创建数据表时设置主键约束 列级只能对单字段设置主键约束，表级可以对单字段或者多字段设置主键约束。
（2）修改数据表时添加主键约束 在ALTER TABLE语句中通过MODIFY或CHANGE重新定义字段的方式添加，也可以通过ALTER TABLE语句中的ADD添加。
删除主键约束
对于设置错误或者不再需要的主键约束，可以通过ALTER TABLE语句的DROP将主键约束删除。由于主键约束在数据表中只能有一个，因此不需要指定主键约束对应的字段名称，直接删除即可。
通过案例演示创建数据表时设置主键约束。 例如，在数据库中创建一个用于存放部门信息的数据表tb_dept02，部门信息中id编号使用最频繁，并且id字段的值不能为NULL值也不能重复；部门名称的值不能重复，部门地址的值不能为NULL值。数据表tb_dept02的表结构信息如下所示。
| 字段名称 | 数据类型 | 约束 | 备注说明 |
| id | INT | 主键约束 | id编号 |
| dname | VARCHAR(14) | 唯一约束 | 部门名称 |
| local | VARCHAR(13) | 非空约束 | 部门地址 |
创建数据表tb_dept02，具体SQL语句及执行结果如下所示。
CREATE TABLE tb_dept02(
id INT PRIMARY KEY,
dname VARCHAR(14) UNIQUE,
local VARCHAR(13) NOT NULL
);通过案例演示修改数据表时使用ADD给字段添加主键约束。 例如，为数据表tb_dept01中的deptno字段添加主键约束，具体SQL语句及执行结果如下所示。
ALTER TABLE tb_dept01 ADD PRIMARY KEY(deptno);使用DROP删除数据表tb_dept01中的主键约束，具体SQL语句及执行结果如下所示。
ALTER TABLE tb_dept01 DROP PRIMARY KEY;默认约束
默认约束用于给表中字段指定默认值，即当在表中插入一条新记录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。
设置默认值约束
（1）创建数据表时设置默认值约束 创建数据表时，给字段设置默认值约束，只需在定义字段时使用如下格式即可。
字段名 数据类型 DEFAULT 默认值;
（2）修改数据表时添加默认值约束 在ALTER TABLE语句中通过MODIFY或CHANGE重新定义字段的方式添加默认值约束。
删除默认值约束
删除默认约束是通过ALTER TABLE语句中MODIFY或CHANGE重新定义字段的方式实现。
通过案例演示创建数据表时设置默认值约束。 例如，创建一个存放员工信息的员工表tb_emp02，员工表中id编号查询频率很高，并且不允许为NULL值及重复值；员工姓名不能有重复值；员工状态的值默认为1。员工表tb_emp02的表结构信息如下所示。
| 字段名称 | 数据类型 | 约束 | 默认值 | 备注说明 |
| id | INT | 主键约束 | id编号 | |
| ename | VARCHAR(16) | 唯一约束 | 部门名称 | |
| sal | DECIMAL(7,2) | 部门地址 | ||
| status | INT | 默认值约束 | 1 |
创建数据表tb_emp02，具体SQL语句及执行结果如下所示。
CREATE TABLE tb_emp02(
id INT PRIMARY KEY,
ename VARCHAR(16) UNIQUE,
sal DECIMAL(7,2),
status VARCHAR(13) DEFAULT 1
);通过案例演示修改数据表时使用MODIFY添加默认值约束。 例如，修改数据表tb_emp02时为字段sal添加默认值约束，默认值为0.00，具体SQL语句及执行结果如下所示。
ALTER TABLE tb_emp02 MODIFY sal DECIMAL(7,2) DEFAULT 0.00;通过案例演示使用CHANGE删除默认值约束。 例如，修改数据表tb_emp02时删除字段sal的默认值约束，具体SQL语句及执行结果如下所示。
ALTER TABLE tb_emp02 CHANGE sal sal DECIMAL(7,2);设置表的字段值自动增加
在数据表中，若想为表中插入的新记录自动生成唯一的ID，可以使用AUTO_INCREMENT约束来实现。
语法：字段名 数据类型 AUTO_INCREMENT;
使用AUTO_INCREMENT时，需要注意以下4点。
（1）一个数据表中只能有一个字段设置AUTO_INCREMENT，设置AUTO_INCREMENT字段的数据类型可以是整数或浮点类型，并且该字段必须定义为键。
（2）如果为自动增长字段插入NULL，则该字段会自动增长值；如果插入的是一个具体的值，则不会自动增长值。
（3）默认情况下，设置AUTO_INCREMENT的字段的值是从1开始自增。如果插入了一个大于自动增长的具体值，则下次自动增长的值为字段中最大值加1。
（4）使用DELETE删除记录时，自动增长值不会减少或者填补空缺。
通过案例演示AUTO_INCREMENT的使用。 例如，在数据库中创建用于存储员工信息的员工表tb_emp03，员工表中员工工号需设置主键约束，并希望插入的员工数据中员工工号能够自动增长。员工表tb_emp03的表结构信息如下所示。
| 字段名称 | 数据类型 | 约束 | 是否自增 | 备注说明 |
| empno | INT | 主键约束 | 是 | 员工工号 |
| deptname | VARCHAR(14) | 非空约束 | 否 | 部门名称 |
| sal | VARCHAR(13) | 否 | 员工职位 |
创建数据表tb_emp03，具体SQL语句及执行结果如下所示。
CREATE TABLE tb_emp03(
empno INT PRIMARY KEY AUTO_INCREMENT,
deptname VARCHAR(14) NOT NULL,
job VARCHAR(13)
);上机实践：图书管理系统的数据库及相关数据表的创建
实践需求1：创建名称为bms的数据库，并且指定数据库的字符集为utf8mb4。
动手实践1：使用CREATE DATABASE语句创建字符集为utf8mb4的数据库bms，具体SQL语句及执行结果如下所示。
CREATE DATABASE bms CHARACTER SET utf8mb4;实践需求2：在数据库bms中创建用户表（user）、图书表（book）和记录表（record）用于表示存储用户信息、图书信息和借阅记录，这3张数据表的结构如下所示。
| 字段名称 | 数据类型 | 约束 | 默认值 | 是否自增 | 说明 |
| id | INT | 主键约束 | 是 | 用户编号 | |
| name | VARCHAR（20） | 非空约束 唯一约束 | 否 | 用户名称 | |
| state | CHAR(1) | 非空约束 | '0' | 否 | 用户状态 （0表示启用， 1表示禁用） |
| 字段名称 | 数据类型 | 约束 | 默认值 | 是否自增 | 说明 |
| id | INT | 主键约束 | 是 | 图书编号 | |
| name | VARCHAR（20） | 非空约束 唯一约束 | 否 | 图书名称 | |
| price | DECIMAL(6,2) | 非空约束 | 否 | 图书价格 | |
| upload_time | DATETIME | 非空约束 | 否 | 上架时间 | |
| borrower_id | INT | 否 | 借阅人编号 | ||
| borrow_time | DATETIME | 否 | 借阅时间 | ||
| state | CHAR(1) | 非空约束 | '0' | 否 | 图书状态 （0表示可借阅， 1表示已借阅， 2表示已下架） |
| 字段名称 | 数据类型 | 约束 | 默认值 | 是否自增 | 说明 |
| id | INT | 主键约束 | 是 | 借阅记录编号 | |
| book_id | INT | 非空约束 | 否 | 图书编号 | |
| borrower_id | INT | 非空约束 | 否 | 借阅人编号 | |
| borrow_time | DATETIME | 非空约束 | 否 | 借阅时间 | |
| remand_time | DATETIME | 非空约束 | 否 | 归还时间 |
动手实践2：
（1）选择数据库bms，并在数据库bms中创建数据表user，具体SQL语句及执行结果如下所示。
# 选择数据库
USE bms
# 创建创建数据表user
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL UNIQUE,
state CHAR(1) NOT NULL DEFAULT 0
);（2）在数据库bms中创建数据表book，具体SQL语句及执行结果如下所示。
# 创建数据表book
CREATE TABLE book (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL UNIQUE,
price DECIMAL(6, 2) NOT NULL,
upload_time DATETIME NOT NULL,
borrower_id INT ,
borrow_time DATETIME,
state CHAR(1) NOT NULL DEFAULT 0
);（3）在数据库bms中创建数据表record，具体SQL语句及执行结果如下所示。
# 创建数据表record
CREATE TABLE record (
id INT PRIMARY KEY AUTO_INCREMENT,
book_id INT NOT NULL,
borrower_id INT NOT NULL,
borrow_time DATETIME NOT NULL,
remand_time DATETIME NOT NULL
);实践需求3：查看数据表book的表结构信息。
动手实践3：使用DESC语句查看book的表结构信息，具体SQL语句及执行结果如下所示。
mysql> DESC book;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| price | decimal(6,2) | NO | | NULL | |
| upload_time | datetime | NO | | NULL | |
| borrower_id | int | YES | | NULL | |
| borrow_time | datetime | YES | | NULL | |
| state | char(1) | NO | | 0 | |
+-------------+--------------+------+-----+---------+----------------+
7 rows in set (0.03 sec)
1700
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
