MySQL基础篇创建和管理表总结

创建和管理表总结目录

1. 基础知识储备 1.1 一条数据存储的过程

存储数据是处理数据的第一步 。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

从系统架构的层次上看MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数据表的 行与列 ,所以从数据库的创建和管理开始记录。

1.2 标识符命名规则

①数据库名 、表名 不得超过30 个字符，变量名 限制为29 个

②必须只能包含 A–Z, a–z, 0–9, _共63个字符

③数据库名、表名、字段名等对象名中间不要包含空格

④同一个MySQL软件中，数据库不能同名 ；同一个库中，表不能重名；同一个表中，字段不能重名

⑤必须保证你的字段没有和保留字、数据库系统或常用方法冲突 。如果坚持使用创建数据库表，请在SQL语句中使用`（着重号）引起来

⑥保持字段名和类型的一致性 ：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

1.3 MySQL中的数据类型

【说明】

这里只是罗列出常用的，多了的话看着头晕，不实用

类型关键字

整数类型

INT(或INTEGER）

浮点类型

FLOAT、DOUBLE

定点数类型

DECIMAL

位类型

BIT

日期时间类型

DATE

文本字符串类型

VARCHAR

2. 创建和管理数据库 2.1 创建建数据库

①创建数据库

CREATE DATABASE 数据库名;

②创建数据库并指定字符集

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

③判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

CREATE DATABASE IF NOT EXISTS 数据库名;

备注：第三种方法如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库，另外DATABASE 不能改名。

2.2 操作数据库

①查看当前所有的数据库

SHOW DATABASES;

②查看当前正在使用的数据库

SELECT DATABASE();

③查看指定库下所有的表

show tables;

④查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;

⑤ 使用 / 切换 数据库

USE 数据库名 

2.3 修改和删除数据库

①更改数据库字符集

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

②删除数据库

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式二:

DROP DATABASE IF EXISTS 数据库名;

3. 创建和管理表 3.1 创建表

创建方式1

语法格式：

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

例如：

CREATE TABLE IF NOT EXISTS myempl(
id INT,
emp_name VARCHAR(15),
hire_date DATE 
);

创建方式2

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

①创建表的同时添加部分数据，注意此时添加的数据和AS 后面SELECT查询的内容是一致的。

使用样例

CREATE TABLE myemp3
AS 
SELECT e.employee_id emp_id, e.last_name lname, d.department_name dept_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

②创建表的同时，不要查询的数据，也就是说只是单纯的想要表结构

，在SELECT的判断条件入手即可

CREATE TABLE myemp4
AS 
SELECT e.employee_id emp_id, e.last_name lname, d.department_name dept_name
FROM employees e JOIN departments d
ON 1 = 2;

运行结果：

3.2 修改表

概述

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现

①向已有的表中添加列

②修改现有表中的列

③删除现有表中的列

④重命名现有表中的列

（1）追加一个列

使用格式

 ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例

ALTER TABLE myempl
ADD salary DOUBLE(10,2);#默认会添加在最后一行

ALTER TABLE myempl
ADD phone VARCHAR(20) FIRST;#指明添加在第一列

（2）修改一个列

可以修改列的数据类型，长度、默认值和位置

修改字段数据类型、长度、默认值、位置的语法格式如下：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;

举例：

ALTER TABLE myempl
MODIFY emp_name VARCHAR(25);

ALTER TABLE myempl
MODIFY emp_name VARCHAR(20) DEFAULT 'aaaa';

（3）重命名一个列

下方有另一种格式

语法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

ALTER TABLE myempl
CHANGE salary month_salary DOUBLE (10,2);

（4）删除一个列

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例：

ALTER TABLE myempl
DROP COLUMN emp_name;

3.3 删除和清空表

删除表

语法格式：

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

举例

DROP TABLE IF EXISTS empl;

清空表

使用格式

TRUNCATE TABLE 表名;

使用举例：

TRUNCATE TABLE detail_dept;

3.4 COMMOIT 和 ROLLBACK

（1）概念描述

COMMIT ：提交数据，一旦执行COMMIT，则数据就会被永久保存在数据库中，意味着数据不可以回滚。

ROLLBACK： 回滚数据，一旦执行ROLLBACK ，则可以实现数据回滚。 回滚到最近一次COMMIT之后。

（2）删除表数据对比：TRUNCATE TABLE和DELETEFROM

相同点：都可以实现对表的所有数据的删除，同时保留表结构

不同点：

TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。在执行过一次之后会默认执行一次COMMIT,导致数据不可以实现回滚

DELETEFROM：一旦执行此操作，表数据可以全部清除。（不带WHERE）。同时，数据是可以实现回滚的

（3）DDL和DML的说明

DDL的操作一旦执行，就不可回滚。

DML的操作默认情况，一旦执行，也是可以不回滚的。但是，如果在执行DML之前，执行了SET autocommit = FALSE ，则执行的DML 操作就可以实现回滚。

实现两种方式比较

演示：DELETE FROM ，可以实现回滚

#提交数据
COMMIT;
#查询表的数据
SELECT *
FROM myemp3;
#设置可以实现回滚条件
SET autocommit = FALSE;
#删除表
DELETE FROM myemp3;
#查询表的数据，表的数据被清除
SELECT *
FROM myemp3;
#回滚
ROLLBACK;
#查询表的数据，表的数据再现
SELECT *
FROM myemp3;

演示：TRUNCATE TABLE，即使设置，也不会实现回滚，因为在使用TRUNCATE TABLE 表名，之后会自动执行COMMIT

#提交数据
COMMIT;
#查询表的数据
SELECT *
FROM myemp3;
#设置可以实现回滚条件
SET autocommit = FALSE;
#删除表
TRUNCATE TABLE myemp3;
#查询表的数据，表的数据被清除
SELECT *
FROM myemp3;
#回滚
ROLLBACK;
#查询表的数据，表的数据不会再现
SELECT *
FROM myemp3;

（编辑：沈阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!