MySQL基础篇3——DDL、DML、DCL使用篇

此笔记来自b站尚硅谷课程，仅做复习使用。

一、创建和管理库

  一条数据存储的过程
  存储数据是处理数据的第一步。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。

  那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？ 在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

  我们要先创建一个数据库，而不是直接创建数据表呢？

  因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。

  标识符命名规则
  数据库名、表名不得超过30个字符，变量名限制为29个

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

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

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

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

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

  MySQL中的数据类型

  其中，常用的几类类型介绍如下：

1、创建数据库（CREATE DATABASE）

  方式1：创建数据库

#方式1：
CREATE DATABASE mytest1; # 创建的此数据库使用的是默认的字符集
#查看创建数据库的结构
SHOW CREATE DATABASE mytest1;

  方式2：创建数据库并指定字符集

#方式2：显式了指名了要创建的数据库的字符集
CREATE DATABASE mytest2 CHARACTER SET 'gbk';
SHOW CREATE DATABASE mytest2;

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

#方式3（推荐）：如果要创建的数据库已经存在，则创建不成功，但不会报错。
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'utf8';
SHOW CREATE DATABASE mytest2;

#如果要创建的数据库不存在，则创建成功
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';

  如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

  注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

   使用数据库
  查看当前所有的数据库

SHOW DATABASES;  #有一个S，代表多个数据库

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

SELECT DATABASE();  #使用的一个 mysql 中的全局函数

  查看指定库下所有的表

SHOW TABLES FROM 数据库名;

  查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
#或者：
SHOW CREATE DATABASE 数据库名\G

  使用/切换数据库

USE 数据库名;

  注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

2、修改数据库（ALTER DATABASE）

  更改数据库字符集

ALTER DATABASE mytest2 CHARACTER SET 'utf8';

3、删除数据库（DROP DATABASE）

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

#方式1：如果要删除的数据库存在，则删除成功。如果不存在，则报错
DROP DATABASE mytest1;

SHOW DATABASES;

  方式2：删除指定的数据库（ 推荐 ）

#方式2：推荐。 如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错。
DROP DATABASE IF EXISTS mytest1;

DROP DATABASE IF EXISTS mytest2;

SHOW DATABASES;

二、创建和管理表

1、创建表（CREATE TABLE）

  创建方式1
    必须具备：
    CREATE TABLE权限
    存储空间

  语法格式：

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

  加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；

  如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

  必须指定：
    表名
    列名(或字段名)，数据类型，长度

  可选指定：
    约束条件
    默认值

  创建表举例：

#方式1："白手起家"的方式
CREATE TABLE IF NOT EXISTS myemp1(   #需要用户具备创建表的权限。
id INT,
emp_name VARCHAR(15)    #使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
hire_date DATE
);

#查看表结构
DESC myemp1;

#查看创建表的语句结构
SHOW CREATE TABLE myemp1; #如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。

#查看表数据
SELECT * FROM myemp1;

  创建方式2
  使用 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

#练习2：创建一个表employees_blank，实现对employees表的复制，不包括表数据
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
#WHERE department_id > 10000;
WHERE 1 = 2;

  查看数据表结构
  在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用DESCRIBE/DESC语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。

  语法格式如下：

#查看创建表的语句结构
SHOW CREATE TABLE myemp1; 

2、修改表（ ALTER TABLE）

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

  使用 ALTER TABLE 语句可以实现：
    向已有的表中添加列

    修改现有表中的列

    删除现有表中的列

    重命名现有表中的列

  追加一个列（ADD）

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

# 3.1 添加一个字段
ALTER TABLE myemp1
ADD salary DOUBLE(10,2);    #默认添加到表中的最后一个字段的位置

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;   #添加到表中的第一个字段的位置

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;  #添加到表中emp_name字段的后面的位置

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

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

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

# 3.2 修改一个字段：数据类型、长度、默认值（略）
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25);

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) DEFAULT 'aaa';#修改emp_name时添加默认值'aaa'

  对默认值的修改只影响今后对表的修改

  此外，还可以通过此种方式修改列的约束。这里暂先不讲。

  重命名一个列（CHANGE）
  使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

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

# 3.3 重命名一个字段
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);  #重命名时也可以修改字段长度

  删除一个列（DROP）
  删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

# 3.4 删除一个字段
ALTER TABLE myemp1
drop COLUMN my_email;

3、重命名表（RENAME TABLE）

  方式一：使用RENAME

#4. 重命名表
#方式1：
RENAME TABLE myemp1
TO myemp11;

DESC myemp11;

  方式二：

#方式2：
ALTER TABLE myemp2
RENAME TO myemp12;

DESC myemp12;

4、删除表（DROP TABLE）

  在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

  数据和结构都被删除

  所有正在运行的相关事务被提交

  所有相关索引被删除

  语法格式：

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

  IF EXISTS的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

  DROP TABLE 语句不能回滚

#5. 删除表
#不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS myemp12;

5、清空表（TRUNCATE TABLE）

  TRUNCATE TABLE语句：
    删除表中所有的数据

    释放表的存储空间

TRUNCATE TABLE myemp11;

  TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

  对比：TRUNCATE TABLE和DELETE FROM

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

  不同点：
      TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。

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

6. DCL 中 COMMIT 和 ROLLBACK

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

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

三、数据处理之增删改

1、添加(INSERT INTO)

  方式1：VALUES的方式添加(VALUES)
  使用这种语法一次只能向表中插入一条数据。

  情况1：为表的所有字段按默认顺序插入数据

INSERT INTO 表名
VALUES (value1,value2,....)

  值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

  举例：

INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400); #注意：一定要按照声明的字段的先后顺序添加

  情况2：为表的指定字段插入数据

  为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

  在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,…valuen需要与column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

  举例：

# ② 指明要添加的字段 （推荐）
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-09',4000,'Jerry');


# 说明：没有进行赋值的hire_date 的值为 null
INSERT INTO emp1(id,salary,`name`)
VALUES(3,4500,'shk');

  情况3：同时插入多条记录
  INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

  或者

INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

# ③ 同时插入多条记录 （推荐）
INSERT INTO emp1(id,`name`,salary)
VALUES
(4,'Jim',5000),
(5,'张俊杰',5500);

  使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：
    ●　Records：表明插入的记录条数。

    ●　Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。

    ●　Warnings：表明有问题的数据值，例如发生数据类型转换。

  一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

  总结：
  VALUES也可以写成VALUE，但是VALUES是标准写法。

  字符和日期型数据应包含在单引号中。

  方式2：将查询结果插入到表中(SELECT)
  INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

  基本语法格式如下：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

  在 INSERT 语句中加入子查询。

  不必书写 VALUES 子句。

  子查询中的值列表应与 INSERT 子句中的列名对应。

#方式2：将查询结果插入到表中
INSERT INTO emp1(id,`name`,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date # 查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN (60,70);

2、更新(UPDATE····SET···WHERE)

  使用 UPDATE 语句更新数据。

  可以一次更新多条数据。

  如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

#2. 更新数据 （或修改数据）
# UPDATE .... SET .... WHERE ...
# 可以实现批量修改数据的。

UPDATE emp1
SET hire_date = CURRENT_DATE()
WHERE id = 5;

SELECT * FROM emp1;

#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date = CURRENT_DATE(),salary = 6000
WHERE id = 4;

3、删除(DELETE FROM … WHERE)

DELETE FROM table_name [WHERE <condition>];

  table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

  使用 WHERE 子句删除指定的记录。

#3. 删除数据 DELETE FROM .... WHERE....
DELETE FROM emp1
WHERE id = 1;

  如果省略 WHERE 子句，则表中的全部数据将被删除

DELETE FROM emp1;

  删除中的数据完整性错误

#在删除数据时，也有可能因为约束的影响，导致删除失败
DELETE FROM departments
WHERE department_id = 50;

4、计算列（新特性）

  什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

  在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

  举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。 首先创建测试表tb1，语句如下：

#4. MySQL8的新特性：计算列
USE atguigudb;

CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL  #字段c即为计算列
);

INSERT INTO test1(a,b)
VALUES(10,20);

SELECT * FROM test1;

UPDATE test1
SET a = 100;

四、MYSQL数据类型精讲

  字符集设置

#1.关于属性：character set name

SHOW VARIABLES LIKE 'character_%';

#创建数据库时指名字符集
CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';

SHOW CREATE DATABASE dbtest12

#创建表的时候，指名表的字符集
CREATE TABLE temp<