Mysql基础 上

---

1. SQL语句分类

SQL（Structured Query Language）语句可以按其功能和用途进行分类。以下是常见的SQL语句分类：

1.数据定义语言 (DDL): DDL语句用于定义和管理数据库中的结构，如表、视图、索引等。常见的DDL语句包括：

• CREATE：创建数据库、表、视图等对象。
• ALTER：修改数据库结构，如添加、修改或删除列、约束、索引等。
• DROP：删除数据库、表、视图等对象。
• TRUNCATE：清空表中的数据。

2.数据操作语言 (DML): DML语句用于对数据库中的数据进行操作，如查询、添加、修改、删除等。常见的DML语句包括：

• SELECT：从数据库表中检索数据。
• INSERT：向数据库表中插入新数据。
• UPDATE：更新数据库表中的数据。
• DELETE：从数据库表中删除数据。

3 .数据控制语言 (DCL): DCL语句用于定义用户访问数据库的权限和权限控制。常见的DCL语句包括：

• GRANT：给用户或用户组授予特定的权限。
• REVOKE：撤销用户或用户组的特定权限。

4.事务控制语言 (TCL): TCL语句用于管理数据库中的事务，包括提交或回滚事务。常见的TCL语句包括：

• COMMIT：将事务的操作结果永久保存到数据库。
• ROLLBACK：撤销事务中的操作，回滚到之前的状态。
• SAVEPOINT：在事务中设置保存点，以便在需要时回滚到特定的点。

这些是SQL语句的一些常见分类。每种分类都有不同的语句，用于不同的目的和操作。在实际应用中，根据需要选择合适的SQL语句来管理数据和操作数据库。

2. SQL语言的规则与规范

• SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
• 每条命令以 ; 或 \g 或 \G 结束
• 关键字不能被缩写也不能分行
• 关于标点符号
  • 必须保证所有的()、单引号、双引号是成对结束的
  • 必须使用英文状态下的半角输入方式
  • 字符串型和日期时间类型的数据可以使用单引号（’ '）表示
  • 列的别名，尽量使用双引号（" "），而且不建议省略as
• MySQL 在 Windows 环境下是大小写不敏感的
• MySQL 在 Linux 环境下是大小写敏感的
  • 数据库名、表名、表的别名、变量名是严格区分大小写的
  • 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
• 推荐采用统一的书写规范：
  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL 关键字、函数名、绑定变量等都大写

命名规则

数据命名规范，可以参考阿里开发规约(嵩山版)，基本规则如下：

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

3. MySQL

MySQL是一种流行的开源关系型数据库管理系统（RDBMS），广泛用于Web应用程序和其他应用程序中。它使用结构化查询语言（SQL）进行数据管理和操作。

MySQL具有以下特点：

1.开源: MySQL是开源软件，可以免费使用和修改，有一个活跃的开源社区。
2.跨平台: MySQL可在各种操作系统上运行，包括Windows、Linux、macOS等。
3.高性能: MySQL具有出色的性能和快速的查询处理能力，能够处理大量数据并支持高并发访问。
4.可扩展性: MySQL支持水平和垂直扩展，可以通过集群和复制等技术实现高可用性和负载均衡。
5.安全性: MySQL提供了丰富的安全功能，包括用户认证、访问控制、加密传输等，以保护数据的机密性和完整性。
6.丰富的功能: MySQL支持多种数据类型、索引、事务处理、存储过程、触发器、视图等高级功能，适用于各种复杂的数据管理需求。
7.可定制性: MySQL允许根据需要进行配置和优化，以满足特定应用程序的要求。
MySQL被广泛应用于各种Web应用程序、企业应用程序、数据仓库等场景，是一个可靠、稳定且功能强大的数据库管理系统。

请注意，MySQL是一种数据库管理系统，而不是查询语言。查询语言是通过执行SQL（结构化查询语言）语句进行与数据库的交互和操作。

4. Oracle vs MySQL

• Oracle 更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要求。
• MySQL 由于其体积小、速度快、总体拥有成本低，可处理上千万条记录的大型数据库，尤其是开放源码这一特点，使得很多互联网公司、中小型网站选择了MySQL作为网站数据库（Facebook，Twitter，YouTube，阿里巴巴/蚂蚁金服，去哪儿，美团外卖，腾讯）

5. MySQL8.0

MySQL从5.7版本直接跳跃发布了8.0版本 ，可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强，开发者对MySQL的源代码进行了重构，最突出的一点是多MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。

• 开放源代码，使用成本低。
• 性能卓越，服务稳定。
• 软件体积小，使用简单，并且易于维护。
• 历史悠久，社区用户非常活跃，遇到问题可以寻求帮助。
• 许多互联网公司在用，经过了时间的验证。

6. MySQL的命令

1. 数据导入命令

source /path/to/your/sql/file.sql

2.连接和退出命令：

mysql -u <用户名> -p：以指定的用户名连接到MySQL服务器，并提示输入密码。
exit 或 \q：退出MySQL客户端。

3. 数据库操作命令：

CREATE DATABASE <数据库名>：创建一个新的数据库。
DROP DATABASE <数据库名>：删除指定的数据库及其所有内容。
USE <数据库名>：选择要使用的数据库。

4.表操作命令：

CREATE TABLE <表名> (<列定义>)：创建一个新的表。
DROP TABLE <表名>：删除指定的表。
ALTER TABLE <表名> ADD COLUMN <列定义>：向表中添加新的列。
DESCRIBE <表名> 或 SHOW COLUMNS FROM <表名>：显示表的结构和列信息。
TRUNCATE TABLE <表名>：清空表中的所有数据。

5.数据操作命令：

SELECT <列名> FROM <表名> [WHERE <条件>]：从表中检索数据。
INSERT INTO <表名> (<列名>) VALUES (<值>)：向表中插入新数据。
UPDATE <表名> SET <列名>=<值> [WHERE <条件>]：更新表中的数据。
DELETE FROM <表名> [WHERE <条件>]：从表中删除数据。

6.权限管理命令：

GRANT <权限> ON <数据库名>.<表名> TO <用户名>@<主机> IDENTIFIED BY '<密码>'：授予用户指定的权限。
REVOKE <权限> ON <数据库名>.<表名> FROM <用户名>@<主机>：撤销用户的指定权限。

7.其他常用命令：

SHOW DATABASES：显示所有数据库的列表。
SHOW TABLES：显示指定数据库中的所有表。
SHOW PROCESSLIST：显示当前正在运行的MySQL进程列表。
SET NAMES <字符集>：设置客户端与服务器之间的字符集。

7. 基本的select语句

1.selct … from

1. 语法

SELECT 标识选择哪些列
FROM 标识从哪个表中选择

2. 不带from子句

select 1;
select 9/2;
select 1 from dual;
select null = null; # null，所以规定使用is null判断Null
SELECT * FROM employees WHERE manager_id IS NULL;

3. 选择全部的列

select * from departments;

4. 选择特定的列

SELECT department_id, location_id FROM departments;

2.列的别名

1.重命名一个列
2.紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
3.AS 可以省略

SELECT last_name AS name, commission_pct comm FROM employees;

SELECT last_name "name", salary*12 "annual_salary" FROM employees; 1

3. 去除重复行

默认情况下，查询会返回全部行，包括重复行。

SELECT department_id FROM employees;

在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT department_id FROM employees;

多列去重

SELECT DISTINCT department_id,salary FROM employees;

这里有两点需要注意：

• DISTINCT 需要放到所有列名的前面，如果写成 SELECT salary, DISTINCT department_id FROMemployees 会报错。
• DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需要写 DISTINCT department_id 即可，后面不需要再加其他的列名了。

4.空值参与计算

所有运算符或列值遇到null值，运算的结果都为null

select null + 1; # null
select null >=1; # null;
select null = nul; #null
SELECT commission_pct FROM employees WHERE commission_pct = NULL; #错误
SELECT commission_pct FROM employees WHERE commission_pct is NULL; #
SELECT employee_id,salary,commission_pct, 12 * salary * (1 + commission_pct)
"annual_sal" FROM employees;

要注意一点 ：NULL在存储空间上不占用实际空间，而空字符串会占用特定的存储空间，取决于列定义的最大长度。因此，在存储空间和内存占用方面，使用NULL可以节省空间，特别是对于大型表或广泛使用的列

5.着重号

我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对"``"（着重号）引起来。

6.过滤条件

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件

举例

SELECT employee_id, last_name, job_id, department_id FROM employees WHERE
department_id = 90;

在员工中筛选在90号部门工作的所有员工的信息

7.显示表结构

DESCRIBE employees;
或 DESC employees

其中，各个字段的含义分别解释如下：

• Field：表示字段名称。
• Type：表示字段类型，这里 barcode、- goodsname 是文本型的，price 是整数类型的。
• Null：表示该列是否可以存储NULL值。
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的
  一部分；MUL表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有，那么值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

8. 排序与分页

1.排序

使用ORDER BY 子句排序

• ASC（ascend）: 升序
• DESC（descend）:降序

ORDER BY 子句在SELECT语句的结尾。

2.分页

背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢

语法：
MySQL中使用 LIMIT 实现分页

LIMIT [位置偏移量,] 行数

例子

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10

*分页显式公式：（当前页数-1）每页条数，每页条数

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

使用这个公式要去定义PageNo和PageSize

9.多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进
行关联。

1.笛卡尔积（交叉连接）

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素
个数的乘积数。

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

2.多表关联的分类

1.等值连接

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

也就是where中确定具体的值

2.非等值连接

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

也就是where中是一个范围区间

3. 自连接

SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

也就是同一个表中不同列的操作

4. 非自连接

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

也就是不同表中的操作

5. 内连接与外连接

内连接（Inner Join）和外连接（Outer Join）是关系型数据库中常用的连接操作。它们用于将两个或多个表中的数据联合在一起，以获取满足特定条件的记录集。下面是内连接和外连接的详细说明：

内连接（Inner Join）：

• 内连接返回两个表中满足连接条件的记录集。
• 只有在连接条件匹配的情况下，才会返回结果。
• 内连接使用 JOIN 关键字，也可以使用 INNER JOIN 进行显示。

外连接（Outer Join）：

• 外连接根据连接条件返回两个表的匹配和非匹配记录集。
• 左外连接（Left Outer Join）返回左表的所有记录以及与之匹配的右表记录。则连接条件中左边的表也称为 主表 ，右边的表称为 从表
• 右外连接（Right Outer Join）返回右表的所有记录以及与之匹配的左表记录则连接条件中右边的表也称为 主表 ，左边的表称为 从表。
• 全外连接（Full Outer Join）返回左右表的所有记录以及非匹配的记录。
• 外连接使用 LEFT JOIN、RIGHT JOIN 或 FULL JOIN 关键字进行表示。

内连接和外连接的应用场景：

• 内连接常用于需要基于两个或多个表中的共同字段进行查询和联接的情况，例如根据员工表和部门表中的部门ID进行联接。
• 外连接常用于需要获取匹配记录以及非匹配记录的情况，例如需要获取所有的订单记录，包括没有对应客户的订单（左外连接）或没有对应订单的客户信息（右外连接）。

需要注意的是，根据具体的数据库管理系统（如Oracle、MySQL等），内连接和外连接的语法可能有所不同。建议在实际使用时查阅相应的文档和语法规范。

1.内连接

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

2.左外连接

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

3. 右外连接

SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

3.满外连接

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或
UNION ALL关键字分隔。
语法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

1.UNION
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
2.UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重