MySQL_DDL

三、DDL

DDL（Data Definition Language）：数据定义语言； 可以通过DDL语句对数据库或者表进行：创建、删除、修改等操作；

1.数据库操作

(1)创建数据库

• 方式1：创建数据库

CREATE DATABASE 数据库名;

• 方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

• 方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

CREATE DATABASE IF NOT EXISTS 数据库名;

(2)使用数据库

• 查看当前所有的数据库

SHOW DATABASES;

• 查看当前正在使用的数据库(使用全局函数 DATABASE() )

SELECT DATABASE();

• 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

• 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;

• 使用/切换数据库

USE 数据库名;

(3)修改数据库

• 更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集;

• 删除指定的数据库（ 推荐 ）

DROP DATABASE IF EXISTS 数据库名;

2.表操作

(1)创建表

• 方式一：

# 格式：
CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

# 举例1：
CREATE TABLE emp (
	-- int类型
	emp_id INT,
	-- 最多保存20个中英文字符
	emp_name VARCHAR(20),
	-- 总位数不超过15位
	salary DOUBLE,
	-- 日期类型
	birthday DATE
);
# 举例2：
CREATE TABLE dept(
	-- int类型，自增
	deptno INT(2) AUTO_INCREMENT,
	dname VARCHAR(14),
	loc VARCHAR(13),
	-- 主键
	PRIMARY KEY (deptno)
);

• 方式二：

使用 AS，将创建表和插入数据结合起来;

# 相当于复制employees表并取名为emp1
CREATE TABLE emp1 AS SELECT * FROM employees;
# 创建具有emp2表相相同字段的空表
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; 
# 创建一个具有某个表某些数据的表
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

(2)查看表结构

SHOW CREATE TABLE 表名\G

(3)修改表

• 追加一个列

# 语法格式
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
# 举例：
ALTER TABLE dept80 ADD job_id varchar(15);

• 修改一个列

# 格式
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名
2】;
# 举例：
ALTER TABLE dept80 MODIFY last_name VARCHAR(30);
ALTER TABLE dept80 MODIFY salary double(9,2) default 1000;
# 注：对默认值的修改只影响今后对表的修改

• 重命名一个列

# 格式
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
# 举例：
ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

• 删除一个列

# 格式
ALTER TABLE 表名 DROP 【COLUMN】字段名;
# 举例：
ALTER TABLE dept80 DROP COLUMN job_id;

• 重命名表

# 方式一：
RENAME TABLE emp TO myemp;
# 方式二：
ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略

• 删除表

# 格式
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
# 举例：
DROP TABLE dept80;

• 清空表

# TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
TRUNCATE TABLE detail_dept;
# 对比：
SET autocommit = FALSE;
DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

3.插入数据

(1)VALUES的方式插入数据

• 为表的所有字段按默认顺序插入数据

INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);

• 为表的指定字段插入数据

INSERT INTO departments(department_id, department_name) VALUES (80, 'IT');

• 同时插入多条记录

INSERT INTO emp(emp_id,emp_name)
	VALUES (1001,'shkstart'),
	(1002,'atguigu'),
	(1003,'Tom');

(2)将查询结果插入到表中

• 格式：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

• 举例：

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

4.更新数据

• 格式：

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

• 使用 WHERE 子句指定需要更新的数据

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

• 省略 WHERE 子句，则表中的所有数据都将被更新

UPDATE copy_emp SET department_id = 110;

5.删除数据

• 格式：

DELETE FROM table_name [WHERE <condition>];

• 使用 WHERE 子句删除指定的记录

DELETE FROM departments
WHERE department_name = 'Finance';

• 省略 WHERE 子句，则表中的全部数据将被删除

DELETE FROM copy_emp;