学习笔记2021 . 12 . 9续2

2021.12.9

子查询

相关子查询

如果子查询的执行依赖于外部查询，这通常是因为子查询中的表使用外部表并且是有条件关联的。因此，每次执行外部查询时，都必须重新计算子查询。这样的子查询称为关联子查询。

相关的子查询逐行执行，主查询的每一行执行一次。

典型应用场景：查询薪资高于部门平均薪资的员工的姓氏、薪资、部门id。

选择姓氏、工资、部门标识

来自员工e1

哪里工资(

选择AVG(工资)

来自员工e2

其中E2 . department _ id=E1 . ` department _ id `

);

从例子自己的理解来看：

子查询的关键是内部查询需要使用外部表中的数据。这里的处理也可以和上面的情况一样，以平均部门的结果为表实现子查询。

由此，我们可以结合前面的顺序，简单地总结一下查询的位置安排。

exist和not exist

关联子查询通常与EXISTS运算符一起使用，以检查子查询中是否有满足条件的行。

如果在子查询中不存在满足条件的行：

条件返回假

继续在子查询中搜索。

如果在子查询中存在满足条件的行：

不要继续在子查询中搜索。

条件返回真

NOT EXISTS关键字表示如果某个条件不存在，则返回TRUE，否则返回FALSE。

例如：在“部门”表中查询“员工”表中不存在的部门的department_id和department_name。

选择d .部门_id，d .部门_名称

来自d部门

不存在的地方

（

选择*

来自员工e

WHERE d . ` department _ id `=e . ` department _ id `

);

理解这个关键字可以理解为子查询中的一种特殊用法。其实很容易理解，可以通过其他方式实现。

思考：

问题：谁的工资比亚伯高？

解答：

#模式1:自连接

SELECT e2 .姓氏，e2 .工资

来自员工e1、员工e2

其中e1.last_name='Abel '

和e1.“工资”e2.“工资”

#模式2:子查询

选择姓氏、工资

来自员工

哪里工资(

选择工资

来自员工

其中姓='亚伯'

);

问题：?有什么好的或坏的方法吗

解答：有很好的自我连接模式！

您可以在主题中使用子查询或自连接。一般来说，建议您使用自连接，因为在许多DBMS进程中，自连接的处理速度比子查询快得多。

可以理解，子查询实际上是通过未知表查询后的条件判断，而自连接是通过已知的自数据表进行的条件判断，因此自连接处理在大多数DBMS中都是优化的。

略去了这一章的习题练习，

但从逻辑上讲，这里的练习应该是最难的。如果有需要，在这里刷题，锻炼思维。

创建和管理表

存储数据是数据处理的第一步。只有正确地存储数据，我们才能有效地处理和分析数据。否则只能是一塌糊涂，无从下手。

那么，如何才能有序高效地存储用户的各种与业务相关的复杂数据呢？在MySQL中，一个完整的数据存储过程包括四个步骤，即创建数据库、确认字段、创建数据表和插入数据。

我们应该先创建数据库，而不是直接创建数据表吗？

因为从系统架构的层面来说，MySQL数据库系统是由大到小的数据库服务器。

、数据库、数据表、数据表的行与列。

数据库名的规范要求：

• 数据库名、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

创建和管理数据库

创建数据库

• 方式1：创建数据库

CREATE DATABASE 数据库名; 

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

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

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

CREATE DATABASE IF NOT EXISTS 数据库名; 

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

示例：

CREATE DATABASE IF NOT EXISTS mytest1;
SHOW DATABASES;

数据库的使用：

• 查看当前所有的数据库

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

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

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

• 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

• 查看数据库的创建信息

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

• 使用/切换数据库

USE 数据库名;

数据库的删除：

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

DROP DATABASE 数据库名;

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

DROP DATABASE IF EXISTS 数据库名;

要删除的不存在则默默推出，不会报错。

创建表

常见的变量类型

具体的介绍有需要再去仔细了解。

创建数据表：

• 必须具备：
  • CREATE TABLE权限
  • 存储空间
• 语法格式：

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

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

• 必须指定：
  • 表名
  • 列名(或字段名)，数据类型，长度
• 可选指定：
  • 约束条件
  • 默认值

示例：

CREATE TABLE IF NOT EXISTS mytest
(
	id INT,
	m_name VARCHAR(15),
	h_date DATE
);

这里使用varchar定义字符串的时候必须指明其长度。

创建方法2:

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

• 指定的列和子查询中的列要一一对应

• 通过列名和默认值定义列

这种创建方式的更多的细节：

• 使用这种方法创建表后，原始的数据也是跟着保留的。
• 查询语句中字段的别名，可以作为新创建的表的字段的名称。
• 创建空表的话就可以在where筛选条件中写一个不可能条件，比如说1=2即可。

具体示例：

CREATE TABLE myemp1
AS
SELECT employee_id,last_name,salary
FROM employees;

管理表

修改表

添加一个字段

• 向已有的表中添加列

语法格式如下：

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

修改一个字段

• 可以修改列的数据类型，长度、默认值和位置
• 但一般也不会去改变数据类型。更多的是去改长度。
• 修改字段数据类型、长度、默认值、位置的语法格式如下：

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

示例：

ALTER TABLE	dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE	dept80
MODIFY salary double(9,2) default 1000;

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

重命名一个字段

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

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

举例：

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15); 

删除一个字段

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

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例：

ALTER TABLE  dept80
DROP COLUMN  job_id; 

重命名表

• 使用RENAME

RENAME TABLE emp
TO myemp;

删除表

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

• 数据和结构都被删除

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

• 所有相关索引被删除

• 语法格式：

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

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

• 举例：

DROP TABLE dept80;

• DROP TABLE 语句不能回滚

清空表

• TRUNCATE TABLE语句：

  • 删除表中所有的数据
  • 释放表的存储空间
  • 但是清空过后表的结构还是存在的。

• 举例：

TRUNCATE TABLE detail_dept;

清空数据和删除数据的区别

• TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
• TRUNCATE是DDL的代表。DDL的操作一旦执行就无法回滚。因为在执行完DDL操作后，一定会执行commit操作。DML的操作默认情况下一旦执行也是没法回滚的，但是可以修改参数，则此时的操作可以回滚。
• 对比：

SET autocommit = FALSE;
  
DELETE FROM emp2; 
#TRUNCATE TABLE emp2;
  
SELECT * FROM emp2;
  
ROLLBACK;
  
SELECT * FROM emp2;

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

DML之增删改

插入数据

方式1：VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。

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

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

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

举例：

INSERT INTO emp1
VALUES (70, 'Pub', '2000-10-11', 1700); 

此时即没有指明添加的字段，必须按照声明的字段按顺序的去添加。

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

INSERT INTO emp1(id,hire_date,salary,`name`) 
VALUES(2,'1999-08-08',6545,'yiyi');

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

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

此时如果上面的字段多于下面的值，那么当下面的值个数少的时候，即没有赋值的则直接赋值为NULL。

情况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 emp(emp_id,emp_name)
 VALUES (1001,'shkstart'),
(1002,'atguigu'),
(1003,'Tom');

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：
●　Records：表明插入的记录条数。
●　Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
●　Warnings：表明有问题的数据值，例如发生数据类型转换。

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

小结：

• VALUES也可以写成VALUE，但是VALUES是标准写法。

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

方式2：将查询结果插入到表中

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 子句中的列名对应。且必须一一对应。
• 且被添加的表中的字段长度不能小于复制对象的表中的字段的长度。

举例：

INSERT INTO emp1(id,`name`,salary,hire_date)
SELECT employee_id,last_name,salary,hire_date
FROM employees
WHERE department_id IN (60,70);