SQL基础3—DDL数据定义语言

关键字：CREATE\DROP DATEBASE 、CREATE\DROP\ALTER TABLE

引言

Data Definition Language 数据定义语言，用来定义数据库对象：库、表、列等。

关键字：

• 数据库的创建、删除
• 表的创建、删除、修改(表结构)

1.基本操作

---

1）查看所有数据库名称：

SHOW DATABASES；　

2）切换数据库：

USE 数据库名;   

2.操作数据库

---

2.1 创建数据库：CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] 数据库名；

有无[IF NOT EXISTS]的区别：

• 1）创建一个名为mydb1的数据库：如果这个数据已经存在，那么会报错。

  CREATE DATABASE mydb1;

• 2）在名为mydb1的数据库不存在时创建该库，这样可以避免报错。

  CREATE DATABASE IF NOT EXISTS mydb1;

2.2 删除数据库：DROP DATABASE

DROP DATABASE [IF EXISTS] 数据库名；

1.删除名为mydb1的数据库：如果这个数据库不存在，那么会报错。

DROP DATABASE mydb1;

2.就算mydb1不存在，也不会的报错。

DROP DATABASE IF EXISTS mydb1;

3.SQL数据类型

---

MySQL与Java一样，也有数据类型。MySQL中数据类型主要应用在列上。

3.1 常用类型：

1.整型：

• Int(n)：整型——n表示数字的显示长度，可以省略
• TINYINT
• SMALLINT
• BIGINT

2.浮点型：

• double(m,n)
• float(m,n)
• decimal(m,n)：在货币运算方面使用该类型，因为不会出现精度缺失问题；

说明：

• m 代表 整体显示长度 = 整数部分长度 + 小数部分长度 ；
• n 代表 小数部分最大显示长度；
• m和n可以省略，数据库根据输入值进行判断

例如：

double(5,2) ： 
	表示最多5位，其中必须有2位小数，即-999.99 至 999.99；

3.字符型：

• char(m)：固定长度字符串类型——char(4) 范围是0-255
• varchar(m)：可变长度字符串类型；
• text：字符串类型——用于存储较长文本

4.日期型：

• date：日期类型（只能保存日期），字节空间，格式为：yyyy-MM-dd；
• time：时间类型（只能保存时间），格式为：hh：mm：ss
• datetime：时间戳类型——可以保持日期+时间，格式为：yyyyMMdd hhmmss
• timestamp：时间戳类型——可以保持日期+时间

5.二进制型：

• blob：字节类型——存储如：jpg mp3 avi

3.2 Char与Varchar的区别

意思	格式	n的解释	特点	效率
Char([n])	固定长度字符	最大的字符个数n,可选/默认为 1	不管实际存储，开辟的空间都是n个字符	高
Varchar(n)	可变长度字符	最大的字符个数n,必选	根据实际存储长度，决定开辟的空间	低

3.3 Datetime 与 Timestamp

类型	保存范围	所占字节
Datetime	1900-1-1~xxxx年	8
Timestamp	1970-1-1~2038-12-31	4

3.4 BLOB (binary large object)

BLOB (binary large object)：二进制大对象。

BLOB常常是数据库中用来存储二进制文件的字段类型。

在JAVA中插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。

MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)：

类型	大小(字节)
TinyBlob	最大 255
Blob	最大 65K
MediumBlob	最大 16M
LongBlob	最大 4G

注意：

• 1）实际使用中根据需要存入的数据大小定义不同的BLOB类型。

• 2）如果存储的文件过大，数据库的性能会下降。

• 3）数据库使用过程中如果报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数：

  max_allowed_packet=16M

• 注意：修改了my.ini文件，一定要重新启动服务

4.操作数据表

4.1 创建表:CREATE TABLE

1.基本语法

CREATE TABLE [IF NOT EXISTS] 表名(
  字段名 字段类型 【字段约束】,
  字段名 字段类型 【字段约束】,
  ......
  字段名 字段类型 【字段约束】,
  CONSTRAINT 约束名 FOREIGN KEY(majorid) REFERENCES major(id) 
);

模板：

DROP TABLE IF EXISTS stus;
CREATE TABLE stus(
	sid	    CHAR(6) PRIMARY KEY auto_increament,
	sname	VARCHAR(20) NOT NULL,
	age		INT  CHECK(age BETWEEN 12 AND 120),
	majorid INT,
	seat    INT UNIQUE,
	gender	VARCHAR(10)  DEFAULT '男',

	CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id) 	
);

再例如：

CREATE TABLE emps(
	eid		CHAR(6),
	ename	VARCHAR(50),
	age		INT,
	gender	VARCHAR(6),
	birthday	DATE,
	hiredate	DATE,
	salary	DECIMAL(7,2),
	resume	VARCHAR(1000)
);

3.数据类型

详见3

2.字段约束（详见完整性约束）

4.2 查看表：SHOW

1.查看当前数据库中所有表名称：

SHOW TABLES;

SHOW TABLES FROM 数据库名;

2.查看指定表的创建语句：

SHOW CREATE TABLE 表名;

3.查看表结构：

方式1：
SHOW COLUMNS FROM 表名;

方式2：
DESC 表名;

4.3 删除表：DROP TABLE

DROP TABLE [IF EXISTS] 表名;

4.4 修改表结构：ALTER TABLE

1.表名修改

ALTER TABLE 表名 RENAME TO 新表名;

修改表名称：修改stu表名称为student：

ALTER TABLE stus RENAME TO student;

2.表中列的修改

ALTER TABLE 表名 ADD|MODIFY|CHANGE|DROP COLUMN 字段名 字段类型 [字段约束];

1.ADD添加列：给stus表添加classname列：

ALTER TABLE stu ADD COLUMN classname varchar(100);

2.MODIFY 修改列类型：修改stu表的gender列类型为CHAR(2)：

ALTER TABLE stus MODIFY COLUMN gender CHAR(2);

3.CHANGE 修改列名：修改stu表的gender列名为sex：

ALTER TABLE stus CHANGE COLUMN gender sex CHAR(2);

4.DROP 删除列：删除stsu表的classname列：

ALTER TABLE stus DROP COLUMN classname;

4.5 表的复制

1.仅复制表结构

CREATE TABLE 新表名 LIKE [其他数据库.]原表名;

2.复制表结构+记录

CREATE TABLE 新表名 SELECT 字段列表 FROM [其他数据库.]原表名 [WHERE 条件];

思考题： 复制departments，仅仅复制department_id和department_name两列，但不要内容

CREATE TABLE empcopy4 
SELECT department_id,department_name 
FROM myemployees.departments 
LIMIT 0;