MySQL——管理库和表

一、管理库

1.1 创建数据库

• 方式1：创建的此数据库使用的是默认的字符集UTF8

CREATE DATABASE mytest1;	

• 方式2：指定了要创建的数据库的字符集

CREATE DATABASE mytest3 CHARACTER SET 'gbk';

• 方式3：创建的数据库若存在，则无法创建，但不会保存，并且可以指定数据库的字符集**(推荐)**

CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';

1.2 管理数据库

• 查看当前连接中的数据库有哪些

SHOW DATABASES;

• 切换数据库

USE mytest2;

• 查看当前数据库中保存的数据表

SHOW TABLES;

• 查看当前使用的数据库

SELECT DATABASE() FROM DUAL;

• 查看指定数据库下保存的数据表

SHOW TABLES FROM mysql;

1.3 修改数据库（一般不改动！）

• 修改字符集gbk、utf8等

ALTER DATABASE mytest 2 CHARACTER SET 'gbk';
# 数据库不能改名，可视化工具中是建新库

1.4 删除数据库（一般不用！）

# 方式1
DROP DATABASE mytest1;
# 方式2
DROP DATABASE IF EXISTS mytest1;

二、管理表

写在前面

• 若创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集
• 对原表中数据或复制表中的数据进行删除，不会相互影响
• 创建一个表，实现对另一个表的复制，但不包括表数据，在where后添加一个恒不成立的条件即可
• 库、表删除后无法撤销

2.1 创建表

• 方式1：直接创建

CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),
hire_date DATE
);
# 使用varchar来定义字符串，必须在使用varchar时指明长度

CREATE TABLE
[IF NOT EXISTS] tb_name 	-- 不存在才创建，存在就跳过
(column_name1 data_type1	 -- 列名和类型必选
  [ PRIMARY KEY				 -- 可选的约束，主键
   | FOREIGN KEY 				-- 外键，引用其他表的键值
   | AUTO_INCREMENT 			-- 自增ID
   | COMMENT comment 			-- 列注释（评论）
   | DEFAULT default_value		 -- 默认值
   | UNIQUE 					-- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL 					-- 该列非空
  ], ...
) [CHARACTER SET charset] 		-- 字符集编码
[COLLATE collate_value] 		-- 列排序和比较时的规则（是否区分大小写等）

• 查看表结构

DESC myemp1;

• 查看创建表的语句结构

SHOW CREATE TABLE myemp1;

• 方式2：基于现有的表创建表,同时导入数据

CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

• 将查询的结果放入一张新表中。查询语句中字段的别名，可以作为新创建的表的字段的名称

CREATE TABLE IF NOT EXISTS myemp4
AS
SELECT employee_id emp_id,last_name lname,salary sal
FROM employees e JOIN departments d
ON e. department_id = d.department_id;

2.2 修改表（alter table）

{ ADD COLUMN <列名> <类型>  -- 增加列
 | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
 | MODIFY COLUMN <列名> <类型> -- 修改列类型
 | DROP COLUMN <列名> -- 删除列
 | RENAME TO <新表名> -- 修改表名
 | CHARACTER SET <字符集名> -- 修改字符集
 | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

• 添加一个字段(默认添加到表中最后一个字段的位置)

ALTER TABLE myemp1
ADD salary DOUBLE(10,2);
# 一共十位数，小数点后有两位

• 指定添加字段的位置，第一位

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

• 放在指定字段位置的后面

ALTER TABLE myemp1
ADD email VARCHAR(50) AFTER emp_name;

• 可修改字段的数据类型、长度、默认值
  • 修改长度

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) DEFAULT 'aaa';

• 重命名一个字段

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

• 在修改名称时，可修改字段长度

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(55);

• 删除一个字段

ALTER TABLE myemp1
DROP COLUMN my_email;

2.3 重命名表

• 方式1

RENAME TABLE myemp1
TO myemp11;

• 方式2

ALTER TABLE myemp2
RENAME TO myemp22;

2.4 删除表

• 删除表结构以及表中数据

DROP TABLE IF EXISTS myemp22;

2.5 清空表

• 清空表中数据，保留表结构

SELECT * FROM employee_copy;
TRUNCATE TABLE employee_copy;

三、DCL中 commit 和 rollback

• commit：提交数据。一旦执行commit，则数据就被永久的保存在数据库中，意味着数据不可以回滚
• rollback：回滚数据。一旦执行rollback，则可以实现数据的回滚。回滚到最近的一次commit之后

四、对比 truncate table 和 delecte from

• 相同点：都可以实现对表中所有数据的删除，同时保留表结构
• 不同点：
  • truncate table：一旦执行此操作，表数据全部清除。同时，数据不可以回滚
  • delecte from：一旦执行此操作，表数据可以全部清除（不带where）。同时，数据是可以实现回滚
  • truncate 的速度快，效率高，不能 where 筛选条件。一句清空表中所有的数据
  • truncate 删除后再插入值时自增长列从1开始，而 delete 是从断点开始
  • truncate 删除时不会返回受影响行数，delete 会返回受影响行数

五、DDL 和 DML 的说明

• DDL的操作一旦执行，就不可回滚
  • 指令 ‘set autocommit = false’ 对DDL操作失效。因为在执行完DDL操作之后，一定会执行一次commit，而此commit操作不受 set autocommit = false 的影响
• DML的操作默认情况，一旦执行，也是不可回滚
  • 若在执行sql之前，执行了 ‘set autocommit = false’ ，则执行的DML操作就可以实现回滚

六、SQL8.0的新特性：DDL的原子化

• 要么成功，要么回滚

参考：尚硅谷-宋红康老师的视频及课件。