三、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;