MySQL第九章(创建管理表)

一.基础知识

1.1一条数据的存储过程

1.2标识符命名规则

1.表名和库名不能超过30个字符
2.必须包含A-Z, a-z, 0-9, 和下划线 共63个字符
3.表名, 库名, 字段名等对象名中间不要包含空格
4.同一个MySQL软件中, 数据库不能同名, 同一个库中, 表不能同名, 同一个表中, 字段不能重名, 如果坚持要用就要用``着重号引起来
5.保持字段名和类型的一致性: 在命名字段并为其指定数据类型的时候一定要保持一致性, 假如数据类型在一个表里是整数, 在另一个表里可就别变成字符型了

二.创建和管理数据库

2.1创建数据库

#方式一:
CREATE DATABASE myemp1
#方式二: 可以指定要创建数据库的字符集
CREATE DATABASE myemp2 CHARACTER SET 'gbk'
#方式三(推荐):数据库如果已存在,则不会报错, 不存在则创建
CREATE DATABASE IF NOT EXISTS myemp3 CHARACTER SET 'utf8'
#建议后面都加'utf8'

2.2使用数据库

#查看当前连接中的数据库有哪些
SHOW DATABASES 

#切换数据库
USE +数据库名

#查看当前数据库中保存的数据表
SHOW tables 

#查看当前使用的数据库
SELECT DATABASE()
FROM DUAL 

#查看指定数据库下保存的数据表
SHOW TABLES FROM +数据库名

2.3修改数据库

#虽说有这个功能, 但很少去改, 有也只是修改字符集
ALTER DATABASE +数据库名 CHARACTER SET 'utf8'

2.4删除数据库

#方式1
DROP DATABASE +数据库名

#方式2, 若存在则删除, 不存在则不会报错
DROP DATABASE IF EXISTS +数据库名

三.创建表

3.1创建方式一(白手起家)

#需要用户具备创建表的权限
举例:
CREATE TABLE IF NOT EXISTS mytable(
id INT,
emp_name VARCHAR(15), #使用varchar来定义字符串,必须指明长度
hire_date DATE
)

如果创建表时没有指明使用的字符集, 则默认使用表所在的数据库的字符集

3.2创建方式二(富二代)

基于现有的表, 同时导入数据

#例题1
CREATE TABLE myemp2
AS 
SELECT e.employee_id, e.last_name, e.salary 
FROM employees e 

#例题2
CREATE TABLE myemp22
AS
SELECT e.employee_id AS emp_id, e.last_name AS lname, d.department_name 
FROM employees e JOIN departments d 
ON e.department_id = d.department_name 
#说明1:查询语句中字段的别名, 可以作为新创建的表的字段的名称
#说明2:此时的查询语句结构比较丰富, 使用前面章节的各种SELECT


#例题3: 复制employees表里所有的数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees e 


#例题4: 只复制dpartment表的字段
CREATE TABLE departments_copy
AS 
SELECT *
FROM employees e 
WHERE 0

3.3管理表

#查看表结构
DESC +表名

#查看创建表的语句结构
SHOW CREATE TABLE +表名

#查看表数据
SELECT * FROM +表名

四.修改表

4.1追加一个列

ALTER TABLE myemp3 
ADD salary double(10,2); #默认添加到表中的最后一个字段的位置
#这里的10,表示共10位, 2表示精确到两位小数


ALTER TABLE myemp3 
ADD phone_number varchar(20) FIRST #添加到第一个位置


ALTER TABLE myemp3 
ADD email varchar(45) AFTER emp_name #添加到emp_name的后面

4.2修改一个列

#可以修改数据类型, 但一般很少修改数据类型, 大都修改长度
ALTER TABLE myemp3 
MODIFY emp_name varchar(25)

4.3重命名一个列

ALTER TABLE myemp3 
CHANGE salary monthly_salary double(10,2)

ALTER TABLE myemp3 
CHANGE email my_email varchar(50)
#也能实现修改数据类型的长度

4.4删除一个列

ALTER TABLE myemp3 
DROP COLUMN my_email

五.重命名表

RENAME TABLE myemp11 
TO myemp1

六.删除表(谨慎操作,回滚不了)

DROP TABLE IF EXISTS myemp1

七.清空表

TRUNCATE TABLE myemp1

八.内容拓展

拓展一: 阿里巴巴《JAVA开发手册》之MySQL字段命名

拓展二:

九.删除表数据与清空表数据的区别

9.1 DCL中的COMMIT 和 ROLLBACK

COMMIT: 提交数据, 一旦执行COMMIT, 则数据就被永久的保存在数据库中, 意味着数据不可以回滚
ROLLBACK: 回滚数据, 执行ROLLBACK, 则可以实现数据的回滚, 回滚到最近一次COMMIT之后

9.2 对比TRUNCATE TABLE 和 DELETE FROM

相同点: 都可以实现对表中所有数据的删除, 同时保留表结构
不同点:
TRUNCATE TABLE: 一旦执行, 表数据全部清除,并且数据不能回滚
DELETE FROM: 执行此操作, 表数据可以全部清除(不带WHERE), 数据是可以实现回滚的

9.3 DDL 和 DML的说明

(1)DDL一旦执行, 就不可回滚, 指令SET autocommit = FALSE 对DDL失效
(2)DML一旦执行, 默认情况下也是不能回滚, 但是, 在执行DML之前, 执行了SET autocommit = FALSE, 则执行的DML操作就可以实现回滚

#不能回滚
COMMIT 

SELECT *
FROM myemp3 

SET autocommit = FALSE

TRUNCATE TABLE myemp3

SELECT *
FROM myemp3

ROLLBACK

SELECT *
FROM myemp3

可以回滚
COMMIT 

SELECT *
FROM myemp3 

SET autocommit = FALSE

DELETE FROM myemp3

SELECT *
FROM myemp3

ROLLBACK

SELECT *
FROM myemp3

十. MySQL8.0的新特性: DDL的原子化

若myemp1存在, myemp2不存在

DROP TABLE myemp1,myemp2
#在MySQL8.0中myemp1删除不成功
#在MySQL5.7中myemp1删除成功