MYSQL-创建表和管理表

创建数据库

1. 创建数据库

-- 直接创建
CREATE DATABASE mytest01;
-- 如果要创建的数据库不存在，则创建成功，不会报错
CREATE DATABASE IF NOT EXISTS mytest01;
-- 创建数据库并设置字符集
CREATE DATABASE IF NOT EXISTS mytest01 CHARACTER SET 'utf8';

2. 查看创建数据库的结构

SHOW CREATE DATABASE mytest01;

管理数据库

1. 查看当前连接中的数据库

SHOW DATABASES;

2. 查看当前数据库中的所有表

SHOW TABLES;

3. 查看当前使用的数据库

SELECT DATABASE() FROM DUAL;

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

SHOW TABLES FROM atguigudb;

修改数据库

1. 修改数据库的字符集

ALTER DATABASE mytest01 CHARACTER SET 'gbk';

删除数据库

-- 直接删除数据库，如果数据库不存在，则报错
DROP DATABASE mytest04;
-- 数据库如果存在则删除，不报错
DROP DATABASE IF EXISTS mytest01;

创建表

1. 直接创建

-- 直接创建
CREATE TABLE myemp01(
id INT,
empname VARCHAR(20),
birthday DATE;
);

2. 如果表不存在，则直接创建

-- 如果表不存在，则直接创建
CREATE TABLE IF NOT EXISTS myemp02(
id INT,
empname VARCHAR(20),
birthday DATE
);

3. 基于现有的表创建，并导入表数据

-- 基于现有的表创建，并导入表数据
CREATE TABLE
IF NOT EXISTS myemp03 AS SELECT
	*
FROM
	atguigudb.employees;

4. 基于现有的表创建，不导入表数据

-- 基于现有的表创建，不导入表数据
CREATE TABLE
IF NOT EXISTS myemp04 AS SELECT
	employee_id,
	last_name,
	email,
	hire_date
FROM
	atguigudb.employees
WHERE
	1 = 2;

5. 查询语句中字段的别名，可以作为新创建的表的字段的名称

-- 查询语句中字段的别名，可以作为新创建的表的字段的名称
CREATE TABLE myemp04 AS SELECT
	employee_id id,
	last_name AS emp_name,
	hire_date hire_time
FROM
	atguigudb.employees;

修改表

• ALTER TABLE

ADD：增加一列

• ADD后的字段要加字段类型

-- 增加一个字段sex
ALTER TABLE myemp01 ADD sex VARCHAR(1);
-- 在empname后增加一个字段hire_date
ALTER TABLE myemp01 ADD hire_date DATE AFTER empname;

MODIFY：修改字段（列）属性

• MODIFY后的字段要加字段类型
• 数据类型

ALTER TABLE myemp01 MODIFY sex INT;

• 长度

ALTER TABLE myemp01 MODIFY empname VARCHAR(50);

• 默认值

ALTER TABLE myemp01 MODIFY empname VARCHAR(50) DEFAULT 'REFINE';

• 修改位置

ALTER TABLE myemp01 MODIFY sex INT AFTER empname;

CHANGE：重命名一列

ALTER TABLE myemp01 CHANGE empname emp_name VARCHAR(45);

DROP COLUMN：删除一列

ALTER TABLE myemp01 DROP COLUMN birthday;

表重命名

REANME

ALTER TABLE myemp01 RENAME TO myemprefine01;
RENAME TABLE myemprefine01 TO myemp01;

删除表

DROP

• 删除表结构、表数据，释放表空间；

DROP TABLE myemp01;
DROP TABLE IF EXISTS emp;

• 删除后，查询该表会报错。

清空表

TRUNCATE

• 清空表数据，保留表结构，数据不可以回滚；

TRUNCATE TABLE myemp2;

DELETE

• 清空表数据，保留表结构，数据可以回滚；

DELETE FROM myemp1;

TRUNCATE和DELETE的异同

1. 相同点:

• 都可以情况表数据，并且保留表结构

2. 不同点

• DROP和TRUNCATE是DDL语言，数据定义语言，用于定义和管理 SQL 数据库中的所有对象的语言；
  DELETE是DML语言，数据操作语言，SQL中处理数据等操作统称为数据操纵语言；
• TRUNCATE：不可以回滚；
  DELETE：执行SET autocommit=FALSE;后，可以进行数据回滚操作。

COMMIT 和 ROLLBACK

• 这两个是DCL语言，数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等
• COMMIT：提交数据，意味着数据不可以回滚。
• ROLLBACK：回滚数据，回滚到最近的一次COMMIT之后。

-- myemp03 myemp04两个表中都有数据
SELECT * FROM myemp03;
SELECT * FROM myemp04;

-- 设置不自动提交数据
SET autocommit=FALSE;

-- 删除myemp03表的数据
DELETE FROM myemp03;
-- 回滚后，myemp03表中的数据还原
ROLLBACK;

-- 删除myemp03表的数据
DELETE FROM myemp04;
-- 提交数据
COMMIT;
-- myemp04中的数据无法还原，表数据为空
ROLLBACK;