MySQL数据库之DDL操作

1、数据库管理系统的一些常用术语

学习数据库首先要清楚数据库的一些常用术语：

• 行：又叫做记录，每一行都是一组相关的数据。
• 列：又叫做字段，每一列都是一组数据类型相同数据。
• 主键：是唯一的，在一张数据表中只有一个主键，且不能为空。
• 外键：主要用于关联两个表。
• 复合键：将多个列组合起来形成一组键，通常用于索引，主键一般不采用符合键的形式，因为这样做会违反三大范式中的第二范式，所有非主键字段完全依赖主键，不能产生部分依赖，如果主键采用符合形式，可能会产生部分依赖。
• 索引：相当于书的目录，可以加快检索的速度。
• 表头：每一列的名称。
• 值：行的具体信息, 每个值必须与该列的数据类型相同。

2、操作语言类型

数据库的操作大致分为以下几类：

1. DQL(数据查询语言)：主要用来查询数据，select语句（最常用）
2. DML(数据操纵语言)：主要要来对数据中表的数据的增删改，insert、update 、delete
3. DDL(数据定义语言)：主要用来对数据库中表的增删改，create、drop、alter
4. DCL(数据控制语言)：grant授权、revoke撤销等。
5. TCL(事务控制语言)：主要用来控制事务，commint提交事务、rollback回滚事务

DDL	DML	DQL	DCL	TCL
数据定义语言	数据操作语言	数据查询语言，最常用	数据控制语言	事务控制语言
create alter drop	insert update delete	select	grant revoke	commit rollback

注：DML+DQL构成了CRUD,即增加(Create)、J检索(Retrieve)、更新(Update)和删除(Delete)

4、 DDL（数据定义语言）

​ DDL是数据定义语言，主要用来操作数据库和创建数据表，其中操作数据库主要包括创建数据库（create）、更新数据库（alter）以及删除数据库（drop）。注：在数据库中不区分大小写。

4.1 数据类型

​ 在mysql中数据类型主要被分成三类：

• 数值类型：主要用来表示一些数字的类型，比如int、float、bigint等。
• 日期/时间类型：主要用来表示时间和日期的类型，比如data、datatime等
• 字符串(字符)类型：主要用来表示一些字符的类型。比如char、varchar等。

数值类型：主要用来表示一些数字的类型，MySQL支持所有标准SQL数值数据类型。

类型	大小	范围	说明	用途
tinyint	1 byte	有符号：(-128，127) 无符号：(0，255)	非常小的数据	小整数值
smallint	2 bytes	有符号：(-32 768，32 767) 无符号：(0, 65535)	较小的数据	大整数值
mediumint	3 bytes	有符号：(-8 388 608，8 388 607) 无符号：(0，16 777 215)	中等数据	大整数值
int	4 bytes	有符号：(-2 147 483 648，2 147 483 647) 无符号：(0，4 294 967 295)	标准整数	大整数值（常用）
bigint	8 bytes	有符号：(-9,223,372,036,854,775,808，9 223 372 036 854 775 807) 无符号：(0，18 446 744 073 709 551 615)	较大的整数	极大整数值
float	4 bytes	有符号：(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 无符号：0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数	单精度 浮点数值（常用）
double	8 bytes	有符号：(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 无符号：0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数	双精度 浮点数值
decimal	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	字符串形式的浮点数	小数值

日期/时间类型

类型	大小 ( bytes)	范围	格式	用途
data	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值（常用）
time	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间（常用）
year	1	1901/2155	YYYY	年份值（常用）
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值（常用）
timestamp	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
char	0-255 bytes	定长字符串（常用）
varchar	0-65535 bytes	变长字符串（常用）
tinyblob	0-255 bytes	不超过 255 个字符的二进制字符串
tinytext	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
text	0-65 535 bytes	长文本数据

char和varchar虽然都便是字符串，但是它们之间是有区别的：

• 长度不一样，char类型的长度是固定的，而varchar类型：varchar类型的长度是可变的。

• 效率不同

  1、char类型：char类型每次修改的数据长度相同，效率更高。

  2、varchar类型：varchar类型每次修改的数据长度不同，效率更低。

• 存储不同

  1、char类型：char类型存储的时候是初始预计字符串再加上一个记录字符串长度的字节，占用空间较大。

  2、varchar类型：varchar类型存储的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小。

4.2 基本语句

当要创建数据库时可以使用create语句进行创建，具体语法：create database [if no exixts] 数据库名：

CREATE DATABASE book              直接创建，如果存在会报错
或者：
CREATE DATABASE IF NO EXISTS book      判断这个数据库是否存在，不存在就创建

当要删除数据库时可以使用drop语句进行删除，切记慎用，具体语法：drop database [if no exixts] 数据库名；

DROP DATABASE book       直接删除数据库

DROP DATABASE IF EXISTS book     判断这个数据库是否存在，存在就删除

当要选择现在使用的数据库（切换数据库）时可以使用use语句进行数据库的选择，具体语法：use 数据库名；

USE book    使用当前定义的数据库

当要查看所有数据库时可以使用show语句进行查看，具体语法:show 数据库名；

SHOW DATABASES         查询所有数据库

---

4.3 创建数据表

创建数据表的前提是创建了一个数据库，在创建数据表时要注意是否有与之关联的数据表，如果没有可以直接创建，否则要先创建父表再创建子表，在创建数据库时要使用到三个字段：字段名 数据类型 [ 数据长度 ] [ 约束 ]

具体语法：create table [ if no exixts ] 表名（ 字段名 数据类型 [ 数据长度 ] [ 约束 ] ）数据引擎 字符集；即：

create  table  表明名（

  字段名1  数据类型，

  字段名2  数据类型，

  字段名3  数据类型，

......................

  字段名n 数据类型

）ENGINE=INNODB DEFAULT CHARSET=utf8；

---

------------- >> 案例：创建一个text数据库，然后在数据库中创建一张EMP 表，并且要求使用默认的数据引擎，字符使用utf8，规定EMPNO字段的值不能为空且为主键，HIREDATE字段的值默认为空，

建表语句：

CREATE DATABASE TEXT;              建立数据库
USE TEXT;                          使用数据库
DROP TABLE IF EXISTS EMP;          判断EMP表是否存在，存在就删除
CREATE TABLE EMP(                  创建数据表EMP
    EMPNO INT(4)  NOT NULL ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	PRIMARY KEY (EMPNO),
	DEPTNO INT(2) 
	)ENGINE=INNODB DEFAULT CHARSET=utf8;    使用默认的数据引擎，字符使用utf8
	

可以使用desc 数据表名 查看创建的表的结构

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  | MUL | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set

---

4.4 删除数据表以及数据库

删除数据表（库）的操作比较简单可以使用drop语句进行删除，切记不要盲目删除，在删除数据库表的时候一定要注意是否有与之相关联的表，如果没有便可以直接删除，此时数据便会从内存或者硬盘中移除，否则就要考虑该表与关联的表之间的关系，假设该表是子表，则可以直接删除，如果该表是父表，则不能直接删除，要先删除子表才能删除父表。具体语法：drop table if exists 数据表名;

建议最好别使用drop truncate删除数据库和数据表

---

----------->>案例：删除上面创建的EMP表（假设没有其他表与其关联）

删表语句：

USE TEXT;                           使用数据库
DROP TABLE IF EXISTS EMP;           删除EMP表

删除数据库：在删除数据库时一般会有两种删除 drop truncate

原始数据库：
mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| ssm_hotel           |
| ssm_library         |
| ssm_sterngym        |
+---------------------+
3 rows in set


创建一个数据库
mysql> create database if not exists shi;
Query OK, 1 row affected

mysql> show databases
    -> ;
+---------------------+
| Database            |
+---------------------+
| shi                 |
| ssm_hotel           |
| ssm_library         |
| ssm_sterngym        |
+---------------------+
4 rows in set


mysql> drop database if exists shi;
Query OK, 0 rows affected

mysql> show database;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| ssm_hotel           |
| ssm_library         |
| ssm_sterngym        |
+---------------------+
3 rows in set

建议最好别使用drop truncate删除数据库和数据表

---

4.5、更新表和字段结构

1. 当表中的结构需要改变使，即需要增加某个字段时，可以使用alter语句进行添加，具体语法：

alter table 表名 add（字段名 数据类型（数据长度）约束）

---

---------->> 案列;需要在数据表emp中增加一个age字段，数据类型为int，数据长度为10。

mysql> ALTER TABLE emp ADD(age INT(10));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  |     | NULL    |       |
| age      | int(10)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
9 rows in set

2.表中某个字段的结构需要改变使，即需要增加某个字段时，可以使用alter语句进行添加，具体语法：

alter table 表名 nodify 字段名 更改的数据类型（数据长度）

alter table 表名 change 字段名 更改的字段名 更改的数据类型（数据长度）

​

---------->>案列;需要在数据表emp中更改age字段，数据类型为varchar，数据长度为100。

mysql> ALTER TABLE emp MODIFY age VARCHAR(100) ;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EMPNO    | int(4)       | NO   | PRI | NULL    |       |
| ENAME    | varchar(10)  | YES  |     | NULL    |       |
| JOB      | varchar(9)   | YES  |     | NULL    |       |
| MGR      | int(4)       | YES  |     | NULL    |       |
| HIREDATE | date         | YES  |     | NULL    |       |
| SAL      | double(7,2)  | YES  |     | NULL    |       |
| COMM     | double(7,2)  | YES  |     | NULL    |       |
| DEPTNO   | int(2)       | YES  |     | NULL    |       |
| age      | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
9 rows in set

---

---

------------------------------------------------------end--------------------------------------------------

---

---

MySQL初始链接：------------------------------------------>>>>>>>>>前往加载<<<<<<<<<-------------------------------------