MySQL基础(上)

侯静川

已于 2022-08-04 10:44:27 修改

阅读量884

点赞数 2

分类专栏： MySQL基础文章标签： mysql 数据库 oracle

于 2022-03-14 22:24:25 首次发布

本文链接：https://blog.csdn.net/houjingchuan/article/details/123490335

版权

MySQL基础专栏收录该内容

4 篇文章 0 订阅

订阅专栏

4.1 SELECT ... FROM ...

一、数据库概述

1.名词解释

DB : 数据库 database 存储数据的仓库，本质是一个文件系统，保存了一系列有组织的数据

DBMS ：数据库管理系统 Database Management System 是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制

SQL ：结构化查询语言 strcutured Query Language 专门用来与数据库通信的语言

2.DB与DBMS的关系

用DBMS使用 sql 文件相当于用 WPS使用 word 文件

举例：用户在手机上想查看某个商品的价格点击以后通过web服务器来访问DBMS，DBMS使用查询语句查找数据库中的信息，之后返回给DBMS，从而使用户拿到数据。

3.MySQL介绍

MySQL 是开源的关系型数据库管理系统（RDBMS）软件，已经是Oracle公司下的了

MySQL 是一种关联数据库管理系统，将数据保存在不同的表中，而不是将数据放在一个大仓库中，增加了速度并提高了灵活性。

一般网站的数据库会选择MySQL来管理，像跨国企业和银行会使用Oracle的数据库管理系统

4.RDBMS 和非 RDBMS

关系型数据库：

是最古老的数据库类型

实质是把复杂的数据结构归结为简单的二元关系，即二维表格形式

关系型数据库以行和列的形式存储数据，以便于用户理解。这一系列的行和列被称为表（table），一组表就组成了一个库（database）。

表与表之间的数据记录有关系。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库就是建立在关系模型基础上的数据库

SQL就是关系型数据库的查询语言

非关系型数据库：

可看作传统的关系型数据库的阉割版本，基于键值对存储数据，不需要通过SQL层的解析，性能非常高。同时减少不常用的功能来提高性能

类别： ( 1 ) 键-值型数据库（Key-Value）：

通过键值的方式来存储数据，其中Key和Value的对象可以是简单的对象也可以是复杂的对象。Key作为唯一的标识符，优点是查询速度快，缺点是无法像关系型数据库一样使用条件过滤，若不知道去哪里找数据，就要遍历所有的键。

键值型数据库典型的使用场景是作为 内存缓存， Redis是最流行的键值型数据库。

( 2 ) 文档型数据库：

此类数据库可以存放和获取文档，文档数据库中所存放的文档就相当于键值型数据库中的值。MongonDB是最流行的文档型数据库。

( 3 ) 搜索引擎数据库

( 4 ) 列式数据库

( 5 ) 图形数据库

NoSQL是非关系型数据库的查询语言，对SQL做了很好的补充。

5.关系型数据库的设计规则

关系型数据库的典型数据结构就是数据表，这些数据表的组成都是结构化的。

将数据放到表中，表再放到库中。

一个数据库可以有多个表，每个表都有自己的名字，表明具有唯一性。

表具有一些特性，这些特性定义了数据在表中如何存储，类似 Java中类的设计。

ORM思想（Object Relational Mapping） 对象关系映射
数据库中的一个表 <-----> Java 中的一个类
表中的一条数据、一条记录 <-----> 类的一个对象（或实体）
表中的一个列 <-----> 类中的一个字段、属性（filed）

E-R模型（entity-relationship，实体-联系）中有三个主要概念是：实体集，属性，联系集。

一个实体集对应数据库中的一张表，一个实体对应一条记录，一个属性对应表中的一列，也称为一个字段

表的关联关系：

表与表之间的数据记录有关系。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。

四种：一对一关联，一对多关联，多对多关联，自我引用。 一对一关联（one to one）：一个表A中的一条记录对应与一个表B中的一条记录。

在实际开发中用的不多，因为一对一可以创建成一张表。

一张表，根据字段的常用和不常用可以拆分为两张表，建立一对一的关系。

一对多关联（one to many）：一个表A中的一条记录对应表B中的多个记录

举例，部门表和员工表

部门表：编号、名称、简介...

员工表：编号、姓名、所属部门

一对多建表原则：在从表（多的一方）创建一个字段，字段作为外键指向主表（一方）的主键

多对多关联（many to many）：

要表示多对多关联，必须创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。

举例：学生-课程

学生信息表：一行代表一个学生的信息

课程信息表：一行代表一个课程的信息

选课信息表：一个学生可选多门课，一门课可以被多个学生选择

这样学生和课程就相当于形成了多对多的关系

自我引用（Self reference）

在一个员工表中有员工编号和员工的主管编号两个字段，主管编号就是那个员工的员工编号这样就是自我引用。

二、MySQL使用演示

1.MySQL的登陆

服务的启动与停止：

MySQL安装完毕以后需要启动服务器进程不然客户端无法连接数据库

在配置过程中已经将MySQL自启动勾选当Windows启动与停止时，MySQL也启动和停止。

手动启动服务器方法 1 ：右键我的电脑，打开管理，启动服务器。

手动启动服务器方法 2 ：使用命令行工具（cmd）以管理员身份运行

#启动 MySQL 服务命令
net start MySQL服务名
#停止 MySQL 服务命令
net stop MySQL服务名

登录MySQL ：

在cmd中输入命令

mysql -u root -p 回车在下一行输入密码 (访问本机8.0版本的MySQL)

mysql -u root -P端口号 -p 回车在下一行输入密码

8.0 端口为 3306

5.7 端口为 13306

不加端口号登录的是8.0的版本因为环境变量配置的是8.0版本的

完整命令为 :

mysql -u root -P端口号 -h ip -p

例： mysql -u root -P3306 -h localhost -p

mysql -u root -P3306 -h 127.0.0.1 -p
使用MySQL自带命令行工具

直接输入密码

选择的什么版本的命令行工具就会进入哪个版本的MySQL

登录以后查看版本命令： mysql > select version( ) ;

退出登录命令：mysql > exit 或者 quit

2.MySQL的演示使用

查看所有的数据库
```
show databases ;
```

创建自己的数据库

create database 数据库名 ；

#创建的数据库名不能重名

create datebase dbetest1 ；

使用自己的数据库
```
use 数据库名 ;

use dbetest1 ;
```
注：如果没有使用use语句，直接对数据库进行操作，会报错。 No database selected （没有选择数据库）

在数据库中创建表

create table 表名 (表中的属性)；

#创建员工表

create table employees ( id int, name varchar(15) ) ;

向表中添加数据

insert into employees values ( 各个属性 ) ;

#向员工表里添加数据

insert into employees values ( 1001, 'Tom' ) ;

查看某个表格

select * from 表名 ；

#查看员工表的表格

select * from employees ；

删除数据库
```
drop database 数据库名 ；
```

在 MySQL 5.7 版本，当输入的记录是中文时，会报错。

ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'name' at row 1

输入 show create table employees；

会出现此表的信息，会发现 CHARSET （字符集）一行

CHARSET=latin1

这是因为在5.7版本中没有中文的字符集

latin1 是欧洲所用的码，不包含汉字，汉字的字符集有 UTF-8，世界上主流语言的字符集都在UTF-8字符集上包含。

输入 show create database dbtest1；

会出现数据库的信息，会有下面的信息

 dbtest1  | CREATE DATABASE `dbtest1` /*!40100 DEFAULT CHARACTER SET latin1 */

我们所创建的数据库也是拉丁的字符集

所以在5.7版本，我们不说明创建的数据库和表的字符集的话，就会使用默认的字符集格式 latin1

解决5.7版本中文乱码问题：

查看编码命令：

show variables like 'character_%';
show variables like 'collation_%';
//排序规则（Collation）是指对指定字符集下不同字符的比较规则。

修改MySQL数据目录下的my.ini配置文件

在大概63行左右添加 （mysql下面）
default-character-set = utf8；#默认字符集
在大概76行左右添加 （mysqld下面）
character-set-server=utf8
collation-server=utf8_general_ci

修改完成以后，在我的电脑管理上重启mysql57，重新进入mysql57，这时候的默认字符集就改为了utf8了。

注： 5.7版本的修改后 utf8 是mb3 格式的即三个字节一个字符

8.0版本的 utf8mb4 是四个字节一个字符 8.0版本的默认字符集不用修改直接用就行

3.MySQL图形化管理工具

MySQL图形化管理工具

工具1. Workbench

MySQL官方提供的图形化管理工具MySQL Workbench

工具2.Navicat

Navicat MySQL 是一个强大的MySQL数据库服务器管理和开发工具。

在登录8.0版本的时候，由于新的加密机制，导致登录不上。

可以在命令行输入命令解决。

4.MySQL目录结构与源码

主要目录结构

MySQL的目录结构	说明
bin目录	所有MySQL的可执行文件.exe
MySQLInstanceConfig.exe	数据库的配置向导，在安装的时候出现的内容
data目录	系统数据库所在的目录
my.ini文件	MySQL的主要配置文件
C:\ProgramData\MySQL\MySQL Server 8.0\Data	用户创建的数据库所在的目录

源码在资料源码文件里。

三、基本的SELECT语句

1.SQL概述

1974年，IBM研究员发表一篇论文《SEQUEL：一门结构化的英语查询语言》，直到今天这门结构化的查询语言没有太大的变化。

不同的数据库生产厂商都支持SQL语句，但都有特有内容。

2.SQL分类

SQL语言在功能上分为以下三大类：

DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。

主要的语句包括 CREATE、DROP、ALTER等。
DML（Data Manipulation Language、数据操作语言），用于增删改查数据库记录，并检查数据库完整性，INSERT、DELETE、UPDATE、SELECT

SELECT是SQL语言基础，最为重要，可以单独一类为DQL（数据查询语言）
DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。COMMIT、ROLLBACK、SAVEPOINT、GRANT、REVOKE

3.SQL的语言规范

3.1基本规则

SQL可以写在一行和多行。为提高可读性，各子句分行写，必要时使用缩进
每条命令以； \g \G结束。
关键字不能缩写也不能分行。
标点符号：括号、单引号、双引号成对结束，必须使用英文的输入法，字符串型和日期时间类型的数据可以使用单引号表示，列的别名，尽量使用双引号（" "）,而且不建议省略as。

3.2SQL大小写规范

MySQL在Windows环境下是大小写不敏感的。

MySQL在Linux环境下是大小写敏感的，数据库名、表名、表的别名、变量名是严格区分大小写的，关键字、函数名、列名、列的别名是忽略大小写的。

统一书写规范：数据库名、表名、表别名、字段名、字段别名等都小写。

SQL关键字、函数名、绑定变量等都大写。

3.3注释

可以使用如下格式的注释结构：

单行注释：#注释文字（MySQL特有方式）
单行注释：-- 注释文字（-- 后面必须包含一个空格）
多行注释：/*注释文字*/

3.4命名规则

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

只能包含 a~z、A~Z、0~9、_ 共63个字符。

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

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

3.5数据导入指令

两种方式：

方式一、基于具体的图形化界面工具导入数据

比如在 SQLyog中选择工具，在其中选择执行SQL脚本，选中要导入的sql文件

方式二、source + 文件的全部路径名

在命令行客户端输入命令，使用source指令导入：

mysql > source d : \ mysqldb.sql

4.基本的SELECT语句

4.0 SELECT ...

SELECT 1;#没有任何子句
SELECT 2/9;#没有任何语句
-- 相当于 ：
SELECT 1 FROM DUAL;# DUAL 伪表

4.1 SELECT ... FROM ...

SELECT 字段1,字段2..... FROM 表名

SELECT * FROM employees;#在employees表中查询全部字段  * ：表中所有的字段，所有的列
SELECT email FROM employees;#在employees表中查询email字段的数据

不同的列中间用逗号隔开。

4.2列的别名

重命名一个列
便于计算
紧跟列名中间以空格间隔，也可以 在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符，以及区分大小写。

SELECT employee_id, last_name, salary
FROM employees;

SELECT employee_id id, last_name name, salary 
FROM employees;

SELECT employee_id AS id, last_name AS name, salary 
FROM employees;#重命名一个列 使用AS关键字

SELECT employee_id AS "id", last_name AS "name", salary 
FROM employees;#别名用双引号引起来，以便于加入特殊字符空格等

SELECT employee_id AS "id", last_name AS "name", salary * 12 AS "annual salary"
FROM employees;

# AS ：alias （别名），可以省略
#列的别名可以使用一对双引号引起来，不能用单引号

查询以后得到的结果叫做结果集（result set）

4.3去除重复行

#查询员工表中有哪些部门id
SELECT department_id
FROM employees;#全部列出来了

SELECT DISTINCT department_id
FROM employees;#去除了重复的
#DISTINCT ：去除重复行

SELECT DISTINCT department_id, salary
FROM employees;#联合去重，两个字段都一样才会被去掉。

4.4空值参与运算

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

#1.空值 ：null 
#2.null不等同于 0,'','null',只是不知道
#3.空值参与运算：结果一定也为 null
SELECT employee_id, salary "月工资", salary * (1 + commission_pct) * 12 "年工资"
FROM employees;#结果集的年工资中一堆null
#实际解决方案：引入流程控制 IFNULL(commision, 0)...

4.5着重号 ``

SELECT * FROM ORDER;#ORDER 也是关键字
SELECT * FROM `order`;#加着重号以后就可以正常运行了

4.6查询常数

SELECT "尚硅谷", employee_id, last_name
FROM employees;#每一行都会匹配上常数

5.显示表结构

DESCRIBE employees;#显示表中字段的详细信息

6.过滤数据

有条件的 SELECT

#查询 90 号部门员工信息
SELECT * 
FROM employees
WHERE department_id = 90;
#过滤条件声明在FROM结构的后面

#查询 last_name 为 King 的人的信息
SELECT *
FROM employees
WHERE last_name = 'King';#字符串中尽量区分大小写，MySQL不严谨所以不区分，在Oracle区分大小写

第三章练习题答案：

#第一题：
SELECT employee_id, last_name, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS "ANNUAL SALARY"
FROM employees;
#第二题：
SELECT DISTINCT job_id
FROM employees;
#第三题：
SELECT last_name, salary
FROM employees
WHERE salary > 12000;
#第四题：
SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;
#第五题
DESC departments;
SELECT * 
FROM departments;

四、运算符

1.算术运算符

算术运算符：+ - * / div % mod

加减运算符：

SELECT 100, 100 + 1, 100 - 1, 100 + 35.5, 100 - 35.5
FROM DUAL;

SELECT 100 + '1'
FROM DUAL;#会将字符串隐式转换为数值，结果是101

SELECT 100 + 'a'
FROM DUAL;#将a看做 0 处理，结果是100

SELECT 100 + NULL
FROM DUAL;#NULL参与的运算，结果为NULL

乘除运算符：

SELECT 100 * 2, 100 / 2, 100 DIV 2，100 * 1.0， 100 / 1.0
FROM DUAL; #除法会默认保留小数位数

SELECT 100 div 0
FROM DUAL;#0做分母，结果为NULL

取模运算符：

SELECT 12 % 3, 12 MOD 3
FROM DUAL;

2.比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，结果为真返回 1 ，假返回 0 ，其他情况返回NULL。

比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果集。

常规比较运算符：

= < <= >= > 运算符
<=> 安全等于运算符，安全的判断是否相等。
!= <> 不等于运算符

#等于 =
SELECT 1 = 2, 1 != 2, 1 = '1', 1 = 'a', 0 = 'a'
FROM DUAL;#字符串存在隐式转换，如果转换数值不成功，则会看做 0 
#result : 0 1 1 0 1

SELECT 'a' = 'a', 'ab' = 'ab', 'a' = 'b'
FROM DUAL;#当纯粹的字符串比较的时候就不会出现隐式转换,比较的是ANSI编码是否相等
#result : 1 1 0

SELECT NULL = NULL, NULL != NULL, 1 = NULL
FROM DUAL;#当运算符两边出现了NULL，结果就一定会是NULL

SELECT last_name, salary
FROM employees 
#WHERE salary = 6000 
WHERE commission_pct = NULL;#NULL参与比较都是所有数据返回的都是NULL，所以取不到数据
#result : 此时执行不会有任何的结果

/*
安全等于 <=> （为NULL而生）： 与等于作用一样，区别是可以判断NULL，在两个操作数均为NULL时，返回值为1，而不为NULL
当一个操作数为NULL时，返回值是0，而不为NULL。
*/

SELECT 1 <=> NULL, NULL <=> NULL
FROM DUAL;
#result : 0 1

SELECT last_name, salary, commission_pct
FROM employees 
WHERE commission_pct <=> NULL;#查询commission_pct中为NULL的字段有哪些

/*
不等于 <> != 
*/

SELECT 3 <> 2, NULL != 3, NULL <> NULL
FROM DUAL;
#result : 1 null null

关键字比较运算符：

/*IS NULL, IS NOT NULL, ISNULL：判断是否为NULL或者是否不为NULL*/
#为NULL的数据:
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NULL;
#或
SELECT last_name, salary, commission_pct
FROM employees
WHERE ISNULL(commission_pct);#将ISNULL当做函数使用

#不为NULL的数据:
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#或
SELECT last_name, salary, commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;#相当于在前面加了个 非

/*LEAST(), GREATEST():函数查询最大和最小*/
SELECT LEAST('g','b','t','m'), GREATEST('g','b','t','m')
FROM DUAL;

SELECT LEAST('first_name','last_name')
FROM DUAL;#从前往后一个一个的比较ASCILL码，谁的大就是谁

SELECT LEAST(first_name,last_name)
FROM employees;#表中的每一对数据一个一个比较

SELECT LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;#加一个LENGTH()函数，比较的是字符串的长度

/*BETWEEN ... AND ... : 判断一个值是否在两个值之间*/
#查询工资在6000 - 8000之间的员工信息
SELECT employee_id, last_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;#闭区间
#或
SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= 6000 && salary <= 8000;

SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= 8000 && salary <= 6000;
#result : 无结果，必须严格遵守上下限规定

#查询不在6000 - 8000内的数据
SELECT employee_id, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#或
SELECT employee_id, last_name, salary
FROM employees
WHERE salary < 6000 || salary > 8000;
#或
SELECT employee_id, last_name, salary
FROM employees
WHERE salary < 6000 OR salary > 8000;

/*IN (SET) \ NOT IN (SET)：属于\不属于运算符，判断一个值是否为列表中的一个值,离散的查找*/
#查询部门为10,20，30号的员工信息
SELECT last_name, salary, department_id
FROM employees
WHERE department_id IN(10, 20, 30);
#或
SELECT last_name, salary, department_id
FROM employees
WHERE department_id = 10 OR department_id = 20 OR department_id = 30;
#不能写成 department_id = 10 OR 20 OR 30,这样的意思是永远是 1 即真的，不会过滤数据

#查询工资不是6000，7000,8000的员工信息
SELECT last_name, salary 
FROM employees
WHERE salary NOT IN(6000, 7000, 8000);

/*LIKE : 模糊匹配运算符，判断一个值是否符合模糊匹配规则*/
#查询 last_name 中包含字符 'a' 的员工信息
# % : 代表不确定个数的字符(可以是 0 个，1 个，多个)
SELECT last_name
FROM employees
WHERE last_name LIKE '%ki%';#忽略了大小写

SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';#以a开头的

SELECT last_name
FROM employees
WHERE last_name LIKE '%a';#以a结尾的

#查询 last_name 中包含字符 'a' 且包含 'e' 的员工信息
#写法1：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' && last_name LIKE '%e%';
#写法2：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' || last_name LIKE '%e%a%';

# _ : 一个下划线代表一个不确定的字符

#查询第二个字符是 'a' 的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';

#查询第二个字符是 '_', 第三个字符是 'a' 的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_/_a%';#加转义字符 
#或者 将别的字符看做转义的意思(了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';

/*REGEXP\RLIKE : 正则表达式运算符，判断一个值是否符合正则表达式规则*/
/*
正则表达式部分规则 ：
'^':匹配以该字符后面的字符开头的字符串
'$':匹配以该字符前面的字符结尾的字符串
'.':匹配任何一个单字符
'[]':匹配在括号内的任何字符，任何一个匹配上了都返回1，[0-9]: - 表示范围
'*':匹配0个或多个在他前面的字符
*/
SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;

SELECT 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]', 'atguigu' REGEXP '[a-b]'
FROM DUAL;

3.逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回值为 1 ，0 或者NULL。

MySQL中支持的四种逻辑运算符：

运算符	作用	示例
NOT 或！	逻辑非	SELECT NOT A
AND 或 &&	逻辑与	SELECT A AND B
OR 或 \|\|	逻辑或	SELECT A OR B
XOR	逻辑异或	SELECT A XOR B

AND 的优先级要高于 OR：

( ) and ( ) or ( ) and ( ) 先算and再算or

章节练习题：

#运算符练习题
#1.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary < 5000 || salary > 12000;
#或
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

#2.选择在20或50号部门工作的员工信息
SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50

SELECT last_name, department_id
FROM employees
WHERE department_id IN(20, 50);

#3.选择公司中没有管理者的员工姓名及job_id
SELECT last_name, job_id
FROM employees
#WHERE ISNULL(manager_id);
#WHERE manager_id <=> NULL;
WHERE manager_id IS NULL;

#4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name, salary, commission_pct
FROM employees 
WHERE commission_pct IS NOT NULL;

#5.选择员工姓名中第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

#6.选择姓名中有a和k的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';

SELECT last_name
FROM employees
WHERE last_name REGEXP 'a' AND last_name REGEXP 'k';

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%k%';

#7.显示出employees表中以e结尾first_name的员工信息
SELECT *
FROM employees
WHERE first_name LIKE '%e';

#8.显示出employees表中部门编号在80 - 100 之间的姓名、工种
SELECT last_name, job_id, department_id
FROM employees
WHERE department_id REGEXP '[80-100]';

SELECT last_name, job_id, department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

#9.显示出employees表中manager_id是100,101,110的员工姓名、工资、管理者id
SELECT last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 110);

五、排序与分页

1.排序数据

1.1排序规则

使用 ORDER BY 子句排序
- ASC （ascend）：升序
- DESC （descend）：降序
ORDER BY 子句在SELECT语句的结尾

1.2单列排序

#排序
/*
每次执行SELECT语句，显示的结果集的顺序不变
那是因为数据库有默认的顺序，就是往数据库添加的先后顺序
如果没有使用排序操作，默认情况下查询返回的数据是按照添加数据的顺序显示的
*/

#使用ORDER BY进行排序操作
#升序：ASC
#降序：DESC

/*练习 ：按照salary从高到低的顺序显示员工信息*/
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;#如果不指明排序规则，那么默认升序排列

#我们可以使用列的别名进行排序
SELECT employee_id, salary, salary * 12 AS "annual_sal"
FROM employees
ORDER BY annual_sal DESC;

#但是对于过滤操作不可以使用列的别名
SELECT employee_id, salary, salary * 12 AS "annual_sal"
FROM employees
WHERE annual_sal > 80000;#错误的

/*举例子解释：*/
#强调 ：WHERE必须在FROM后面紧贴
SELECT employee_id, salary
FROM employees
WHERE department_id IN (50, 60, 70)
ORDER BY department_id DESC;
/*
语句执行的顺序 ：
首先从FROM中找出employees表，再看过滤条件WHERE语句。
再看查询什么SELECT取出需要的字段，然后看是否起别名，进行转换。
最后才执行ORDER BY排序操作。
所以，ORDER BY可以用列的别名，WHERE不能用列的别名。
*/

1.3多列排序

#二级排序
/*练习 ：按照department_id 降序，再按salary升序排列*/
SELECT employee_id, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC;

SELECT employee_id, salary, department_id
FROM employees
ORDER BY department_id DESC, salary;#省略升序关键字

2.分页

2.1背景

查询返回的记录太多了，查看起来不方便，怎么能够实现分页查询？
表里有 4 条数据，我们只想要第 2 3 条数据，怎么做？

2.2实现规则

/*分页*/

/*
mysql使用LIMIT实现数据的分页显示
LIMIT 格式：严格来说 LIMIT 位置偏移量, 条目数
LIMIT 0, 条目数 结构等价于 LIMIT 条目数
*/

#需求1.每页显示20条记录，此时显示第一页
SELECT employee_id, last_name
FROM employees
LIMIT 0, 20;
#LIMIT 偏移量(从哪开始),记录条数

#需求2.每页显示20条记录，此时显示第二页
SELECT employee_id, last_name
FROM employees
LIMIT 20, 20;

#需求3.每页显示20条记录，此时显示第三页
SELECT employee_id, last_name
FROM employees
LIMIT 40, 20;

#需求：每页显示pagesize条记录，此时显示第pageNo页
#公式：LIMIT pagesize * (pageNo - 1), pagesize;
SELECT employee_id, last_name
FROM employees
LIMIT pagesize * (pageNo - 1), pagesize;

#将 WHERE \ORDER BY\ LIMIT 放一起如何声明，声明顺序如下
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
LIMIT 0, 10;

#需求：表里有四条数据,只想要 二 三条数据
SELECT employee_id, last_name, salary
FROM employees
LIMIT 1, 2;

#MySQL 8.0 新特性 LIMIT...OFFSET... 新特性的后面是偏移量 前面是条目数
SELECT employee_id, last_name
FROM employees
LIMIT 2 OFFSET 1;

/*LIMIT 子句要放到SELECT的最后*/

#练习：查询员工表中工资最高的员工信息
SELECT last_name, employee_id, salary
FROM employees
ORDER BY salary DESC#工资倒叙排
LIMIT 1;#只选第一个

#LIMIT 可以使用在MySQL、PGSQL、MariaDB、SQLite 等数据库中使用，表示分页，别的数据库不行

章节练习：

#排序与分页章节练习
#1.查询员工的姓名和部门号和年薪，按年薪降序，按名字升序显示
SELECT last_name, department_id, salary * 12
AS "annual_salary"
FROM employees
ORDER BY annual_salary DESC, last_name;

#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序，显示第21到40位置的数据
SELECT last_name, salary
FROM employees
#WHERE salary >= 17000 || salary <= 8000
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20, 20;#前面是偏移量，后面是记录数

#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT employee_id, last_name, email, department_id
FROM employees
#WHERE email LIKE '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC, department_id;

六、多表查询

多表查询，也叫做关联查询，指两个或者多个表一起完成查询操作。

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

1.一个案例引发的多表链接

#案例引入 ：查询Abel员工在哪一个城市工作
#通过employees查询department_id
SELECT * 
FROM employees
WHERE last_name <=> 'Abel';
#通过departments查询location_id 
SELECT *
FROM departments
WHERE department_id <=> 80;
#通过locations查询Abel员工的地址
SELECT * 
FROM locations
WHERE location_id <=> 2500;

为什么不将其放到一个表中？

冗余字段增加，重复数据增加。
磁盘与内存的IO交互效率低。
一个人查询表，表会被锁定，其他人不能查询其他数据，效率低。
大表不方便维护

案例：

查询 employee_id, last_name, department_name。

#案例引入 ：查询Abel员工在哪一个城市工作
#通过employees查询department_id
SELECT * 
FROM employees
WHERE last_name <=> 'Abel';
#通过departments查询location_id 
SELECT *
FROM departments
WHERE department_id <=> 80;
#通过locations查询Abel员工的地址
SELECT * 
FROM locations
WHERE location_id <=> 2500;

#多表查询如何实现？

#错误的实现方式1：每个员工都与每个部门匹配一遍，出现笛卡尔积（交叉连接）的错误。
SELECT employee_id, last_name, department_name
FROM employees,departments;#查询出2889条记录

SELECT 27 * 107 FROM DUAL;#employees表的记录数与departments表的记录数乘积
#错误的原因：缺少了多表的连接条件

#错误的实现方式2：CROSS JOIN 交叉连接的错误
SELECT employee_id, last_name, department_name
FROM employees CROSS JOIN departments;#查询出2889条记录

SELECT 27 * 107 FROM DUAL;#employees表的记录数与departments表的记录数乘积
#错误的原因：缺少了多表的连接条件

#正确方式：
SELECT employee_id, department_name
FROM employees, departments
#两个表的连接条件 （关联查询）：
WHERE employees.department_id = departments.department_id;

#案例：查询两个表共有字段
#错误示例：
SELECT employee_id, department_id, department_name
FROM employees, departments
WHERE employees.employee_id = departments.department_id;
#[Err] 1052 - Column 'department_id' in field list is ambiguous 模棱两可

#正确示例：如果出现共有字段，必须指明哪个表
SELECT employees.employee_id, employees.department_id, departments.department_name
FROM employees, departments
WHERE employees.employee_id = departments.department_id;

#建议：从SQL优化角度，建议多表查询时，每个字段前都指明所在的表

#可以给表起别名，在SELECT与WHERE中使用表的别名
SELECT emp.employee_id, emp.department_id, dept.department_name
FROM employees emp, departments dept#空格给表起别名
WHERE emp.employee_id = dept.department_id;
#注意：一旦给表起了别名，那么在SELECT与WHERE使用的时候就必须要用别名，不能使用原名。

#练习 ： 查询员工的employee_id, last_name, department_name, city。
SELECT emp.employee_id, emp.last_name, dept.department_name, loc.city
FROM employees emp, departments dept, locations loc
WHERE emp.department_id = dept.department_id && dept.location_id = loc.location_id;
#如果n个表实现多表查询，则至少需要n-1个连接条件

2.多表查询的分类

角度1 ：等值连接 vs 非等值连接

角度2 ：自连接 vs 非自连接

角度3 ：内连接 vs 外连接

/*
演绎式：提出问题1-->解决问题1-->提出问题2-->解决问题2...

归纳式：总--分
*/

#多表连接的分类
/*
角度1 ：等值连接 vs 非等值连接

角度2 ：自连接 vs 非自连接

角度3 ： 内连接 vs 外连接
*/

#角度1 ：等值连接 vs 非等值连接
#等值连接的例子：以上的例子都是等值连接
#非等值连接的例子：查询员工的薪资等级信息
SELECT * 
FROM job_grades;#薪资等级表

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

#角度2 ：自连接 vs 非自连接
#自连接的例子：表中记录自我连接
SELECT * FROM employees;

#查询员工id, 员工姓名及其管理者的id和姓名
SELECT emp.employee_id, emp.last_name, mar.employee_id, mar.last_name AS "manager_name"
FROM employees emp, employees mar
WHERE emp.manager_id = mar.employee_id; 


#角度3 ： 内连接 vs 外连接
#内连接的例子：

SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;#只有106条记录
/*
只是把左表和右表当中满足连接条件的数据查出来了，其他的数据没有要
这样的多表连接叫做 内连接

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
*/

/*
外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外
还查询到了左表或右表中不匹配的行。

外连接的分类：左外连接 与 右外连接 与 满外连接

左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行
这种连接方式叫左外连接。

*/

3.外连接 JOIN...ON...

在外连接的语法中，SQL92的规定和SQL99的规定不同，两种实现形式：

#练习：查询所有员工的last_name, department_name
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;#需要使用左外连接

#SQL92语法实现内连接：见上，略
#SQL92语法实现外连接：使用 + -----MySQL不支持SQL92语法中的外连接写法！

SELECT last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);#不能执行

/*
SQL99语法中使用JOIN...ON 的方式实现多表的查询
这种方式也能解决外连接的问题
*/

#SQL99语法实现内连接：
#两个表：
SELECT last_name, department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;#INEER可以省略
#三个表：
SELECT last_name, department_name, city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations loc 
ON d.location_id = loc.location_id;


#SQL99语法实现外连接：
SELECT last_name, department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;#左外连接，OUTER也可以省略

SELECT last_name, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;#右外连接

SELECT last_name, department_name
FROM employees e FULL JOIN departments d
ON e.department_id = d.department_id;#满外连接 MySQL不支持FULL JOIN，不能执行

在MySQL中不支持FULL JOIN 满外连接的语法，所以我们先引入UNION

4.UNION的使用

合并查询结果

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

7种JOIN的SQL实现：

#UNION和UNION ALL的使用
/*
UNION 是将两个表联合起来，去除重复的数据
UNION ALL 是不去除重复的数据
在实际开发中，我们尽量使用UNION ALL 效率高
我们可以想一些办法规避重复的数据

结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要进行去重操作
则尽量使用UNION ALL语句，以提高数据查询的效率
*/

# 7 种JOIN的实现：见Markdown文件中的七个UNION集合图片
#练习：查询员工id与部门名
#中图：内连接
SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

#左上图：左外连接
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

#右上图：右外连接
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

#左中图：
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

#右中图：
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

#在左外连接和右外连接的基础上过滤
#左中：筛选出不能匹配右表的数据就剩下左边的了
#右中：同上


#左下图：满外连接
#方式1：左上图 UNION ALL 右中图
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;#要求两个列的字段都一样才能UNION

#方式2：左中图 UNION ALL 右上图
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

#右下图：
#左中图 UNION ALL 右中图
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
UNION ALL
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

SQL99语法新特性：

#SQL99语法新特性1：自然连接
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;

SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;#自然等值连接
#自动查询两张表中所有相同的字段进行等值连接

#SQL99语法新特性2：USING的使用 适用于同名的字段
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
USING (department_id);#相当于替换的连接条件

#USING不适用于自连接的例子：
#查询员工id,姓名，管理者id及其姓名
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e JOIN employees m
ON e.manager_id = m.department_id;

章节练习：

#第06章：多表查询的课后练习

#第一题：显示所有员工的姓名，部门号和部门名称
SELECT emp.last_name, emp.department_id, dept.department_name
FROM employees emp LEFT JOIN departments dept
USING (department_id);

#第二题：查询90号部门员工的job_id, 和90号部门的location_id
SELECT e.job_id, d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;

#第三题：选择所有有奖金的员工的last_name, department_name, location_id, city
SELECT e.last_name, d.department_name, d.location_id, l.city
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id 
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL; #注意两个都是左连接。

#第四题：选择city在Toronto工作的员工的last_name, job_id, department_id, department_name 
SELECT e.last_name, e.job_id, d.department_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE l.city = 'Toronto';

#SQL92语法：
SELECT e.last_name, e.job_id, d.department_id, d.department_name
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id && d.location_id = l.location_id#连接条件
AND l.city = 'Toronto';

#第五题：查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为
#Executive
SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_name = 'Executive';

#第六题：选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号
SELECT e.last_name AS "employees", e.employee_id AS "Emp#", m.last_name AS "mannager", m.employee_id AS "Mgr#"
FROM employees e LEFT JOIN employees m
ON e.manager_id = m.employee_id;

#第七题：查询哪些部门没有员工
SELECT d.department_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
#或
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

#本题也可以使用子查询

#第八题：查询哪些城市没有部门
SELECT l.city
FROM departments d RIGHT JOIN locations l
ON d.location_id = l.location_id
WHERE d.location_id IS NULL;
#或
SELECT l.city
FROM locations l LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.location_id IS NULL;
#确定主表以后一直用LEFT JOIN

#第九题：查询部门为Sales或IT的员工信息
SELECT e.employee_id, e.last_name, e.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
#WHERE d.department_name = 'Sales' OR d.department_name = 'IT';
WHERE d.department_name IN ('Sales', 'IT');