目录
一、基础概述
1. 标识符命名规则
- 数据库名、表名不得超过30个字符,变量名限制为29个
- 必须只能包含 A–Z, a–z, 0–9, _共63个字符
- 数据库名、表名、字段名等对象名中间不要包含空格
- 同一个MySQL软件中,数据库不能同名;同一个库中,表不能重名;同一个表中,字段不能重名
- 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用,请在SQL语句中使用 ``(着重号)引起来
- 保持字段名和类型的一致性:在命名字段并为其指定数据类型的时候一定要保证一致性,假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了
2. MySQL中的数据类型
其中,常用的几类类型介绍如下:
二、创建和管理数据库
1. 创建数据库
- 方式1:创建数据库
CREATE DATABASE 数据库名;
- 方式2:创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
- 方式3:判断数据库是否已经存在,不存在则创建数据库( 推荐 )
CREATE DATABASE IF NOT EXISTS 数据库名;
# 如果MySQL中已经存在相关的数据库,则忽略创建语句,不再创建数据库。
注意:DATABASE 不能改名。一些可视化工具可以改名,它是建新库,把所有表复制到新库,再删旧库完成的。
2. 使用数据库
- 查看当前所有的数据库
SHOW DATABASES; # 有一个S,代表多个数据库
- 查看当前正在使用的数据库
SELECT DATABASE(); # 使用的一个 mysql 中的全局函数
- 查看指定库下所有的表
SHOW TABLES FROM 数据库名;
- 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
或者:
SHOW CREATE DATABASE 数据库名\G
- 使用/切换数据库
USE 数据库名;
注意:要操作表格和数据之前必须先说明是对哪个数据库进行操作,否则就要对所有对象加上“
数据库名.
”。
3. 修改数据库
- 更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; # 比如:gbk、utf8等
4. 删除数据库
- 方式1:删除指定的数据库
DROP DATABASE 数据库名;
- 方式2:删除指定的数据库( 推荐 )
DROP DATABASE IF EXISTS 数据库名;
5. 临时数据库的创建与使用
三、创建和管理表
1. 创建表
- 必备条件:
- CREATE TABLE权限
- 存储空间
- 方式1:"白手起家"的方式
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
# 加上了IF NOT EXISTS关键字,则表示:如果当前数据库中不存在要创建的数据表,则创建数据表;
# 如果当前数据库中已经存在要创建的数据表,则忽略建表语句,不再创建数据表。
举例
mysql> # 方式1:"白手起家"的方式 需要用户具备创建表的权限。
mysql> CREATE TABLE IF NOT EXISTS myemp1(
-> id INT,
-> emp_name VARCHAR(15), #使用VARCHAR来定义字符串,必须在使用VARCHAR时指明其长度。
-> hire_date DATE
-> );
Query OK, 0 rows affected (0.58 sec)
mysql> # 查看表结构
mysql> DESC myemp1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| emp_name | varchar(15) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> # 查看创建表的语句结构
mysql> # 如果创建表时没有指明使用的字符集,则默认使用表所在的数据库的字符集。
mysql> SHOW CREATE TABLE myemp1;
+--------+--------------------------------------+
| Table | Create Table
|
+--------+--------------------------------------+
| myemp1 | CREATE TABLE `myemp1` (
`id` int(11) DEFAULT NULL,
`emp_name` varchar(15) DEFAULT NULL,
`hire_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------+
1 row in set (0.00 sec)
- 方式2:基于现有的表,同时导入数据
CREATE TABLE 表名
AS
subquery
# 使用 AS subquery 选项,将创建表和插入数据结合起来
举例
# 方式2:基于现有的表,同时导入数据
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;
# 说明1:查询语句中字段的别名,可以作为新创建的表的字段的名称。
# 说明2:此时的查询语句可以结构比较丰富,使用前面章节讲过的各种SELECT
CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
# 需求1:创建一个表employees_copy,实现对employees表的复制,包括表数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;
# 需求2:创建一个表employees_blank,实现对employees表的复制,不包括表数据
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
# 创建一个不可能条件
WHERE 1 = 2;
2. 修改表
- 修改表指的是修改数据库中已经存在的数据表的结构。
- 使用
ALTER TABLE
语句可以实现:- 向已有的表中添加列
- 修改现有表中的列
- 删除现有表中的列
- 重命名现有表中的列
(1)添加字段
- 语法格式
ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];
举例
mysql> #3. 修改表 --> ALTER TABLE
mysql> DESC myemp1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| emp_name | varchar(15) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> # 添加一个字段
mysql> # 默认添加到表中的最后一个字段的位置
mysql> ALTER TABLE myemp1
-> ADD salary DOUBLE(10,2);
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC myemp1;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| emp_name | varchar(15) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| salary | double(10,2) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> # 添加到表中的第一个字段
mysql> ALTER TABLE myemp1
-> ADD phone_number VARCHAR(20) FIRST;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC myemp1;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| phone_number | varchar(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| emp_name | varchar(15) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| salary | double(10,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> # 添加到表中emp_name后
mysql> ALTER TABLE myemp1
-> ADD email VARCHAR(45) AFTER emp_name;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC myemp1;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| phone_number | varchar(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| emp_name | varchar(15) | YES | | NULL | |
| email | varchar(45) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| salary | double(10,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
(2)修改字段
- 可以修改列的数据类型,长度、默认值和位置
- 修改字段数据类型、长度、默认值、位置的语法格式如下:
ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名2];
举例
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';
(3)重命名字段
- 使用
CHANGE old_column new_column dataType
子句重命名列。语法格式如下:
ALTER TABLE 表名 CHANGE [column] 列名 新列名 新数据类型;
举例
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);
ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);
(4)删除字段
- 删除表中某个字段的语法格式如下
ALTER TABLE 表名 DROP [COLUMN] 字段名
举例
ALTER TABLE myemp1
DROP COLUMN my_email;
3. 重命名表
- 方式一:使用
RENAME
RENAME TABLE emp
TO myemp;
举例
RENAME TABLE myemp1
TO myemp11;
- 方式二:
ALTER table dept
RENAME [TO] detail_dept;
举例
ALTER TABLE myemp2
RENAME TO myemp12;
4. 删除表
- 在MySQL中,当一张数据表没有与其他任何数据表形成关联关系时,可以将当前数据表直接删除。
- 数据和结构都被删除
- 所有正在运行的相关事务被提交
- 所有相关索引被删除
- 语法格式:
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
IF EXISTS
的含义为:如果当前数据库中存在相应的数据表,则删除数据表;如果当前数据库中不存在相应的数据表,则忽略删除语句,不再执行删除数据表的操作。
举例
# 不光将表结构删除掉,同时表中的数据也删除掉,释放表空间
DROP TABLE IF EXISTS myemp2;
DROP TABLE
语句不能回滚DROP TABLE
语句不能回滚DROP TABLE
语句不能回滚
5. 清空表
TRUNCATE TABLE
语句:- 删除表中所有的数据
- 释放表的存储空间
举例
TRUNCATE TABLE employees_copy;
或者
DELETE FROM employees_copy;
-
TRUNCATE
语句不能回滚,而使用DELETE
语句删除数据,可以回滚 -
TRUNCATE
语句 比DELETE
速度快,且使用的系统和事务日志资源少,但TRUNCATE
无事务且不触发TRIGGER
,有可能造成事故,故不建议在开发代码中使用此语句。 -
说明:
TRUNCATE
在功能上与不带WHERE
子句的DELETE
语句相同。
关于 TRUNCATE 和 DELETE 的对比可见 DCL中 COMMIT 与 ROLLBACK 的使用
6. 临时表
-
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
-
临时表在MySQL 3.23版本中添加,如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
-
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然你也可以手动销毁。
-
当你使用
SHOW TABLES
命令显示数据表列表时,你将无法看到临时表。
(1)创建
这与普通表的格式是大致差不多的,只需要在CREATE TABLE
中添加一个TEMPORARY
即可。同样,他也有两种方式,“白手起家”以及“基于现有的表,同时导入数据”
- 语法格式
CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
CREATE TEMPORARY TABLE 临时表名
AS
subquery
(2)删除
DROP TABLE 临时表名;
四、删表需谨慎
1. 谨慎谨慎
表删除操作将把表的定义和表中的数据一起删除,并且MySQL在执行删除操作时,不会有任何的确认信息提示,因此执行删除操时应当慎重。在删除表前,最好对表中的数据进行 备份 ,这样当操作失误时可以对数据进行恢复,以免造成无法挽回的后果。
同样的,在使用 ALTER TABLE
进行表的基本修改操作时,在执行操作过程之前,也应该确保对数据进行完整的备份,因为数据库的改变是无法撤销的,如果添加了一个不需要的字段,可以将其删除;相同的,如果删除了一个需要的列,该列下面的所有数据都将会丢失。
2. MySQL8新特性—DDL的原子化
在MySQL 8.0版本中,InnoDB表的DDL支持事务完整性,即 DDL操作要么成功要么回滚 。DDL操作回滚日志写入到data dictionary数据字典mysql.innodb_ddl_log(该表是隐藏的表,通过show tables无法看到)中,用于回滚操作。通过设置参数,可将DDL操作日志打印输出到MySQL错误日志中。
分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表,结果如下:
CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);
SHOW TABLES;
- 在MySQL 5.7版本中,测试步骤如下: 删除数据表book1和数据表book2,结果如下:
mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'
mysql># 再次查询数据库中的数据表名称,结果如下:
mysql> SHOW TABLES;
Empty set (0.00 sec)
# 从结果可以看出,虽然删除操作时报错了,但是仍然删除了数据表book1。
- 在MySQL 8.0版本中,测试步骤如下: 删除数据表book1和数据表book2,结果如下:
mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'
mysql># 再次查询数据库中的数据表名称,结果如下:
mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| book1 |
+------------------+
1 row in set (0.00 sec)
# 从结果可以看出,数据表book1并没有被删除。