MySQL详细学习

一、SQL简述

1 sql简介

Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。

1.SQL的概述

1、简单易学，具有很强的操作性
2、绝大多数重要的数据库管理系统均支持SQL
3、高度非过程化；用SQL操作数据库时大部分的工作由DBMS自动完成

2.SQL的优点

1、简单易学，具有很强的操作性
2、绝大多数重要的数据库管理系统均支持SQL
3、高度非过程化；用SQL操作数据库时大部分的工作由DBMS自动完成

3.SQL的分类

1、DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等； 常用语句：CREATE、 ALTER、DROP
2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别； 常用语句：GRANT、DENY
4、DQL(Data Query Language) 数据查询语言，用来查询数据 常用语句：SELECT

二、数据库的三大范式

1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。
2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。
3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。

三、数据库的数据类型

使用MySQL数据库存储数据时，不同的数据类型决定了 MySQL存储数据方式的不同。为此，MySQL数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点 数类型、日期和时间类型、字符串类型、二进制…等等数据类型。

1.整数类型

根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的，

2.浮点数类型和定点数类型

在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。下图列举了 MySQL中浮点数和定点数类型所对应的字节大小及其取值范围：

从上图中可以看出：DECIMAL类型的取值范围与DOUBLE类型相同。但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长 度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的数据6.5243 插人数据库后显示的结果为6.52

3.字符串类型

在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。
当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1

4.字符串类型

文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为如下4种：

5.日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是 ：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值：

5.1 YEAR类型

YEAR类型用于表示年份，在MySQL中，可以使用以下三种格式指定YEAR类型 的值。
1、使用4位字符串或数字表示，范围为’1901’—'2155’或1901—2155。例如，输人 ‘2019’或2019插人到数据库中的值均为2019。
2、使用两位字符串表示，范围为’00’—‘99’。其中，‘00’—'69’范围的值会被转换为 2000—2069范围的YEAR值，‘70’—'99’范围的值会被转换为1970—1999范围的YEAR 值。例如，输人’19’插人到数据库中的值为2019。
3、使用两位数字表示，范围为1—99。其中，1—69范围的值会被转换为2001— 2069范围的YEAR值，70—99范围的值会被转换为1970—1999范围的YEAR值。例 如，输人19插入到数据库中的值为2019。
请注意：当使用YEAR类型时，一定要区分’0’和0。因为字符串格式的’0’表示的YEAR值是2000而数字格式的0表示的YEAR值是0000。

5.2 TIME类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中，HH表示小时， MM表示分,SS表示秒。在MySQL中，可以使用以下3种格式指定TIME类型的值。
1、以’D HH:MM:SS’字符串格式表示。其中，D表示日可取0—34之间的值, 插人数据时，小时的值等于(DX24+HH)。例如，输入’2 11:30:50’插人数据库中的日期为59:30:50。
2、以’HHMMSS’字符串格式或者HHMMSS数字格式表示。 例如，输人’115454’或115454,插入数据库中的日期为11:54:54
3、使用CURRENT_TIME或NOW()输人当前系统时间。

5.3 DATETIME类型

DATETIME类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH: MM:SS’，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS 表示秒。在MySQL中，可以使用以下4种格式指定DATETIME类型的值。
以’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式表示的日期和时间，取值范围为’1000-01-01 00:00:00’—‘9999-12-3 23:59:59’。例如，输人’2019-01-22 09:01:23’或 ‘20140122_0_90123’插人数据库中的 DATETIME 值都为 2019-01-22 09:01:23。
1、以’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’字符串格式表示的日期和时间，其中YY表示年，取值范围为’00’—‘99’。与DATE类型中的YY相同，‘00’— '69’范围的值会被转换为2000—2069范围的值，‘70’—'99’范围的值会被转换为1970—1999范围的值。
2、以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期 和时间。例如，插入20190122090123或者190122090123,插人数据库中的DATETIME值都 为 2019-01-22 09:01:23。
3、使用NOW来输人当前系统的日期和时间。

5.4 TIMESTAMP类型

TIMESTAMP类型用于表示日期和时间，它的显示形式与DATETIME相同但取值范围比DATETIME小。在此，介绍几种TIMESTAMP类型与DATATIME类型不同的形式：
1、使用CURRENT_TIMESTAMP输人系统当前日期和时间。
2、输人NULL时系统会输人系统当前日期和时间。
3、无任何输人时系统会输入系统当前日期和时间。

6.二进制类型

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。BLOB类型分为如下四种：

7.mysql中的取值

7.1 round(x,d)，四舍五入。round(x) ,其实就是round(x,0),也就是默认d为0

-- 109.46

select round(109.456,2);

7.2 TRUNCATE(x,d)，直接截取需要保留的小数位

--  109.45

select TRUNCATE(109.456,2);

7.3 FORMAT（x,d），四舍五入，保留d位小数，返回string类型

-- 109.46

select FORMAT(109.456,2);

7.4 CONVERT(x, DECIMAL(10,d)); 转型，会四舍五入,保留d位小数

-- 109.46

select CONVERT(109.456, DECIMAL(10,2));

7.5 CEILING(x); 直接取整，个位+1

-- 101

select CEILING(100.56);

7.6 FLOOR(x); 直接取整

-- 100

select FLOOR(100.56);

8.mysql的CONVERT函数

8.1 将值转换为DATE数据类型

-- 2022-05-25
SELECT CONVERT('2022-05-25', DATE);
-- 2022-05-25 17:58:48
SELECT NOW();
-- 2022-05-25
SELECT CONVERT(NOW(), DATE);

8.2 将值转换为DATETIME数据类型

-- 2022-05-25 00:00:00
SELECT CONVERT('2022-05-25', DATETIME);

8.3 将值转换为TIME数据类型

-- 14:06:10
SELECT CONVERT('14:06:10', TIME);
-- 2022-05-25 17:25:12
SELECT NOW();
-- 17:25:12
SELECT CONVERT(NOW(), TIME);

8.4 将值转换为CHAR数据类型

-- '150'
SELECT CONVERT(150, CHAR);
-- 出错Error
SELECT CONCAT('Hello World',437));
-- 'Hello World437'
SELECT CONCAT('Hello World',CONVERT(437, CHAR));

8.5 将值转换为SIGNED数据类型

-- 5
SELECT CONVERT('5.0', SIGNED);
-- 2
SELECT (1 + CONVERT('3', SIGNED))/2;
-- -5
SELECT CONVERT(5-10, SIGNED);
-- 6
SELECT CONVERT(6.4, SIGNED);
-- -6
SELECT CONVERT(-6.4, SIGNED);
-- 7
SELECT CONVERT(6.5, SIGNED);
-- -7
SELECT CONVERT(-6.5, SIGNED);

8.6 将值转换为UNSIGNED数据类型

-- 5
SELECT CONVERT('5.0', UNSIGNED);
-- 6
SELECT CONVERT(6.4, UNSIGNED);
-- 0
SELECT CONVERT(-6.4, UNSIGNED);
-- 7
SELECT CONVERT(6.5, UNSIGNED);
-- 0
SELECT CONVERT(-6.5, UNSIGNED);

8.7 将值转换为DECIMAL数据类型

-- 9
SELECT CONVERT('9.0', DECIMAL);
 
-- DECIMAL(数值精度，小数点保留长度)
-- DECIMAL(10,2)可以存储最多具有8位整数和2位小数的数字
-- 精度与小数位数分别为10与2
-- 精度是总的数字位数，包括小数点左边和右边位数的总和
-- 小数位数是小数点右边的位数
-- 9.50
SELECT CONVERT('9.5', DECIMAL(10,2));
-- 99999999.99
SELECT CONVERT('1234567890.123', DECIMAL(10,2));
-- 220.232
SELECT CONVERT('220.23211231', DECIMAL(10,3));
-- 220.232
SELECT CONVERT(220.23211231, DECIMAL(10,3));

9.mysql中的utf8与utf8mb4

utf8和utf8mb4别用错

utf8mb4是 MySQL 的特有概念：5.5.3 以前，Unicode 收录的字符不够全面，最大 3 个字节/字符已足够使用，此时的utf8的别名即为utf8mb3，有“UTF8, Most Bytes 4”之意。后来，Unicode 收录了更多字符，扩张到最大 4 个字节/字符，因此在 5.5.3 版本后增加了utf8mb4的编码

四、数据库、数据表的基本操作

1.数据库的基本操作

MySQL安装完成后，要想将数据存储到数据库的表中，首先要创建一个数据库。创 建数据库就是在数据库系统中划分一块空间存储数据，语法如下：

create database 数据库名称;

创建一个叫db1的数据库MySQL命令：

-- 创建一个叫db1的数据库
show create database db1;

创建数据库后查看该数据库基本信息MySQL命令：

show create database db1;

删除数据库MySQL命令：

drop database db1;

查询出MySQL中所有的数据库MySQL命令

show databases;

将数据库的字符集修改为gbk MySQL命令：

alter database db1 character set gbk;

切换数据库 MySQL命令：

use db1;

查看当前使用的数据库 MySQL命令：

select database();

2.数据表的基本操作

数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意：在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作，否则会抛出“No database selected”错误。
语法如下：

 create table 表名(
         字段1 字段类型,
         字段2 字段类型,
         …
         字段n 字段类型
);

2.1 创建数据表

示例：创建学生表 MySQL命令：

 create table student(
 id int,
 name varchar(20),
 gender varchar(10),
 birthday date
 );

2.2 查看数据表

示例：查看当前数据库中所有表 MySQL命令：

show tables;

示例：查表的基本信息 MySQL命令：

show create table student;

示例：查看表的字段信息 MySQL命令：

desc student;

2.3 修改数据表

有时，希望对表中的某些信息进行修改，例如：修改表名、修改字段名、修改字段 数据类型…等等。在MySQL中使用alter table修改数据表.
示例：修改表名 MySQL命令：

alter table student rename to stu;

示例：修改字段名 MySQL命令

alter table stu change name sname varchar(10);

示例：增加字段 MySQL命令：

alter table stu add address varchar(50);

示例：删除字段 MySQL命令：

alter table stu drop address;

2.4 删除数据表

语法：

drop table 表名;

示例：删除数据表 MySQL命令：

drop table stu;

五、数据表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：

以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。

1.主键约束

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。
主键约束基本语法：

字段名 数据类型 primary key;

设置主键约束(primary key)的第一种方式
示例：MySQL命令：

create table student(
id int primary key,
name varchar(20)
);

设置主键约束(primary key)的第二·种方式
示例：MySQL命令：

create table student01(
id int
name varchar(20),
primary key(id)
);

2.非空约束

非空约束即 NOT NULL指的是字段的值不能为空，基本的语法格式如下所示：

字段名 数据类型 NOT NULL;

示例：MySQL命令：

create table student02(
id int
name varchar(20) not null
);

3.默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；其基本的语法格式如下所示：

字段名 数据类型 DEFAULT 默认值；

示例：MySQL命令：

create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

5.唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现，其基本的语法格式如下所示：

字段名 数据类型 UNIQUE;

示例：MySQL命令：

create table student04(
id int,
name varchar(20) unique
);

6.外键约束

外键约束即FOREIGN KEY常用于多张表之间的约束。基本语法如下：

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

示例：创建一个学生表 MySQL命令：

create table student05(
id int primary key,
name varchar(20)
);

示例：创建一个班级表 MySQL命令：

create table class(
classid int primary key,
studentid int
);

示例：学生表作为主表，班级表作为副表设置外键， MySQL命令：

alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);

6.1 数据一致性概念

大家知道：建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。

6.2 删除外键

语法如下：

alter table 从表名 drop foreign key 外键名；

示例：删除外键 MySQL命令：

alter table class drop foreign key fk_class_studentid;

6.3 关于外键约束需要注意的细节

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

六、数据表插入数据

在MySQL通过INSERT语句向数据表中插入数据。在此，我们先准备一张学生表，代码如下：

 create table student(
 id int,
 name varchar(30),
 age int,
 gender varchar(30)
 );

1. 为表中所有字段插入数据

每个字段与其值是严格一一对应的。也就是说：每个值、值的顺序、值的类型必须与对应的字段相匹配。但是，各字段也无须与其在表中定义的顺序一致，它们只要与 VALUES中值的顺序一致即可。
语法如下：

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

示例：向学生表中插入一条学生信息 MySQL命令：

insert into student (id,name,age,gender) values (1,'bob',16,'male');

2. 为表中指定字段插入数据

语法如下：

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

插入数据的方法基本和为表中所有字段插入数据，一样，只是需要插入的字段由你自己指定

3. 同时插入多条记录
   语法如下：

INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;

在该方式中：(字段名1,字段名2,…)是可选的，它用于指定插人的字段名；(值 1,值 2,…),(值 1,值 2,…)表示要插人的记录，该记录可有多条并且每条记录之间用逗号隔开。
示例：向学生表中插入多条学生信息 MySQL命令：

insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male'),(4,'tom',18,'male');

七、更新数据

在MySQL通过UPDATE语句更新数据表中的数据。在此，我们将就用六中的student学生表

1. UPDATE基本语法

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

在该语法中：字段名1、字段名2…用于指定要更新的字段名称；值1、值 2…用于表示字段的新数据；WHERE 条件表达式 是可选的，它用于指定更新数据需要满足的条件

2. UPDATE更新部分数据

示例：将name为tom的记录的age设置为20并将其gender设置为female MySQL命令：

update student set age=20,gender='female' where name='tom';

3. UPDATE更新全部数据

示例：将所有记录的age设置为18 MySQL命令：

update student set age=18;

八、删除数据

在MySQL通过DELETE语句删除数据表中的数据。在此，我们先准备一张数据表，代码如下：

-- 创建学生表
 create table student(
 id int,
 name varchar(30),
 age int,
 gender varchar(30)
 );
 -- 插入数据
 insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male'),(4,'tom',18,'male'),(5,'sal',19,'female'),(6,'sun',20,'male')
,(7,'sad',13,'female'),(8,'sam',14,'male');

1. DELETE基本语法

在该语法中：表名用于指定要执行删除操作的表；[WHERE 条件表达式]为可选参数用于指定删除的条件。

DELETE FROM 表名 [WHERE 条件表达式];

2. DELETE删除部分数据

示例：删除age等于14的所有记录 MySQL命令：

delete from student where age=14;

3. DELETE删除全部数据

示例：删除student表中的所有记录 MySQL命令：

delete from student;

4. TRUNCATE和DETELE的区别

TRUNCATE和DETELE都能实现删除表中的所有数据的功能，但两者也是有区别的：
1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。
2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1
3、DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句

九、MySQL数据表简单查询(接下来使用的全是scott数据库)

数据库链接link

1.简单查询概述

简单查询即不含where的select语句。在此，我们讲解简单查询中最常用的两种查询：查询所有字段和查询指定字段。

2.查询所有字段（方法不唯一只是举例）

查询所有字段 MySQL命令：

select * from emp;

3.查询指定字段（empno、ename）

查询指定字段（empno,ename） MySQL命令：

select empno,ename from emp;

4.常数的查询

在SELECT中除了书写列名，还可以书写常数。可以用于标记
常数的查询日期标记 MySQL命令：

select EMPNO,ENAME,'2022-4-8' from emp;

5.从查询结果中过滤重复数据

在使用DISTINCT 时需要注意：
在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。

#从查询结果中过滤重复数据
select distinct DEPTNO from emp;

6.算术运算符（举例加运算符）

在SELECT查询语句中还可以使用加减乘除运算符。

#.算术运算符
select EMPNO,SAL+10000,'2022-4-8' from emp;

十、函数

1.聚合函数

在开发中，我们常常有类似的需求：统计某个字段的最大值、最小值、 平均值等等。为此，MySQL中提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。
聚合函数使用规则：
只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。
接下来，我们学习常用聚合函数。

1.1、count（）

统计表中数据的行数或者统计指定列其值不为NULL的数据个数
查询有多少该表中有多少人

#统计表中数据的行数或者统计指定列其值不为NULL的数据个数
select count(*) from emp;

1.2、max（）

计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算

#查询工资最高的员工
select max(SAL) from emp;

1.3、min（）

计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算

#查询工资最低的员工
select min(SAL) from emp ;

1.4、sum（）

计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0

#统计所以工资总和
select sum(SAL) from emp;

1.5、avg（）

计算指定列的平均值，如果指定列类型不是数值类型则计算结果为

#查询平均工资
select avg(SAL) from emp;

2.其他常用函数

这里我就不一一举例了，基本混个眼熟，以后用到再细说

2.1、时间函数

SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

2.2、字符串函数

--连接函数
SELECT CONCAT ()
--
SELECT INSTR ();
--统计长度
SELECT LENGTH();

2.3、数学函数

-- 绝对值
SELECT ABS(-136);
-- 向下取整
SELECT FLOOR(3.14);
-- 向上取整
SELECT CEILING(3.14);

十一、条件查询

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。

1.使用关系运算符查询

在WHERE中可使用关系运算符进行条件查询，常用的关系运算符如下所示：

#查询工资大于等于1500的员工信息
select * from emp where SAL>=1500;

2.使用IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

#查询员工编号为7369和7934的员工信息
select * from emp where EMPNO in ('7369','7934');

#查询员工编号为7369以外的员工信息
select * from emp where EMPNO  not in ('7369'); 

3.使用BETWEEN AND关键字查询

BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来

#查询员工工资1000到2000的员工信息
SELECT * FROM emp WHERE SAL BETWEEN  1000 AND 2000;

#查询不是员工工资1000到2000的员工信息
SELECT * FROM emp WHERE SAL not BETWEEN  1000 AND 2000;

4.使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。请注意：空值NULL不同于0，也不同于空字符串
由于student表没有空值就不演示查询空值的了

#查询comm不为空值的员工信息
SELECT * FROM emp WHERE COMM is not null;

5.使用AND关键字查询

在MySQL中可使用AND关键字可以连接两个或者多个查询条件。

#查询工资大于1500的售货员的信息
SELECT * FROM emp WHERE SAL>=1500 and JOB ='SALESMAN';

6.使用OR关键字查询

在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来

#查询工资大于1500或为售货员的信息
SELECT * FROM emp WHERE SAL>=1500 or JOB ='SALESMAN';

7.使用LIKE关键字查询

MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配

7.1 普通字符串

#查询job开头为'SALES%'的员工信息
SELECT * FROM emp WHERE JOB LIKE 'SALES%';

7.2 含有%通配的字符串

%用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串

#查询job开头为'SALES%'的员工信息
SELECT * FROM emp WHERE JOB LIKE 'SALES%';

#查询job以T结尾的员工信息
SELECT * FROM emp WHERE JOB LIKE '%T';

#查询job含有N的员工信息
SELECT * FROM emp WHERE JOB   LIKE  '%N%';

7.3 含有_通配的字符串

#查询工作名称为SALESMA开头并且长度为8的员工信息
SELECT * FROM emp WHERE JOB   LIKE  'SALESMA_';

#查询长度为8结尾为SMAN的员工信息
SELECT * FROM emp WHERE JOB   LIKE  '____SMAN';

8.使用LIMIT限制查询结果的数量

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条

#查询工资最低的3名员工
SELECT * FROM emp ORDER BY SAL asc LIMIT 3;

#查询工资最高的3名员工
SELECT * FROM emp ORDER BY SAL DESC LIMIT 3;

9.使用GROUP BY进行分组查询

GROUP BY 子句可像切蛋糕一样将表中的数据进行分组，再进行查询等操作。换言之，可通俗地理解为：通过GROUP BY将原来的表拆分成了几张小表。

9.1 GROUP BY和聚合函数一起使用

#各个部门的员工个数	
SELECT count(*),DEPTNO FROM emp GROUP BY DEPTNO;

#统计部门大于10的各个部门员工的个数
SELECT count(*),DEPTNO FROM emp WHERE DEPTNO>10 GROUP BY DEPTNO;

9.2 GROUP BY和聚合函数以及HAVING一起使用

#统计工资总和大于9000的部门
select sum(SAL),DEPTNO from emp group by DEPTNO having sum(SAL)>9000;

10.使用ORDER BY对查询结果排序

从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序
其语法格式如下所示：

SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

在该语法中：字段名1、字段名2是查询结果排序的依据；参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。通常情况下，ORDER BY子句位于整个SELECT语句的末尾。

#查询所以员工的工资按照由小到大升序排列
SELECT * FROM emp ORDER BY  SAL ASC;

#查询所以员工的工资按照由大到小排列
SELECT * FROM emp ORDER BY  SAL DESC;

11.UNION

UNION累加并且有去重的效果 加上All则查询全部的数据

#查询员工姓名和薪资 在部门10并且含有字母A的员工 和 在部门20薪资大于1000的员工 和在部门30薪资小于2000的员工
SELECT ENAME,SAL,DEPTNO  FROM emp  WHERE DEPTNO =10 and  ENAME LIKE '%A%'
#SELECT ENAME,SAL,DEPTNO  FROM emp  WHERE  ENAME LIKE '%A%'
UNION #ALL
SELECT ENAME,SAL,DEPTNO FROM emp WHERE DEPTNO = 20 AND SAL>1000
UNION #ALL 
SELECT  ENAME,SAL,DEPTNO FROM  emp WHERE DEPTNO =30 AND SAL<2000;

11.1使用UNION ALL实现递归

WITH recursive t1 AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM t1 WHERE n < 5 ) 
SELECT *  FROM t1

十二、别名设置

在査询数据时可为表和字段取別名，该别名代替表和字段的原名参与查询操作。
操作的表事先已准备

1.为表取别名

在查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称。语法格式如下所示

SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;

#为表取别名
SELECT * FROM emp as em;

#为字段取别名
SELECT ENAME  '姓名', JOB FROM emp;

十三、表的关联关系

在实际开发中数据表之间存在着各种关联关系。在此，介绍MySQL中数据表的三种关联关系。
多对一
多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中，应将外键建在多的一方否则会造成数据的冗余。
多对多
多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。
一对一
在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。

1.关联查询

#查询销售部所以员工
SELECT * FROM emp WHERE DEPTNO =(SELECT DEPTNO FROM dept WHERE DNMAE ='SALES')

2.关于关联关系的删除数据

#删除OPERATIONS
DELETE FROM emp WHERE DEPTNO =(SELECT DEPTNO FROM dept WHERE DNMAE ='OPERATIONS')
DELETE FROM dept WHERE DNMAE ='OPERATIONS';
SELECT * FROM dept;

十四、多表连接查询

1.交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接，其语法格式如下：

SELECT * FROM 表1 CROSS JOIN 表2;

#交叉连接查询
SELECT * FROM emp CROSS JOIN dept;
SELECT * FROM emp , dept;
SELECT * FROM emp,dept WHERE emp.deptno= dept.deptno;

#dept和salgrade
SELECT * FROM emp e,salgrade s WHERE e.SAL BETWEEN s.LOSAL and s.HISAL; 

#薪资大于2000的员工姓名和工作地点
SELECT  e.ENAME,e.SAL,d.LOC FROM emp e,dept d WHERE e.deptno=d.deptno HAVING SAL>2000;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。
由于这个交叉连接查询在实际运用中没有任何意义，所以只做为了解即可

2.内连接查询

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的 记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

#.内连接查询
SELECT * FROM emp e JOIN dept d on(e.DEPTNO = d.DEPTNO) WHERE SAL>2000;

SELECT * FROM emp e  JOIN salgrade s  on(e.SAL BETWEEN s.LOSAL AND s.HISAL);

#查询员工的部门名称和薪资等级
SELECT e.ENAME,d.DNMAE,s.GRADE FROM emp e JOIN dept d using(DEPTNO) JOIN salgrade s on(e.SAL BETWEEN s.LOSAL AND s.HISAL)

3.外连接查询

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

由此可见，外连接的语法格式和内连接非常相似，只不过使用的是LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN关键字。其中，关键字左边的表被称为左表，关键字右边的表被称为右表；OUTER可以省略。
在使用左(外)连接和右(外)连接查询时，查询结果是不一致的，具体如下：
1、LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
2、RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

3.1 左（外）连接查询

左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。

#查询每个员工属于的部门
SELECT * FROM  dept d left JOIN emp e using(DEPTNO);

3.2 右（外）连接查询

右(外)连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。

#查询出所有的部门
SELECT * FROM emp e RIGHT JOIN dept d using(DEPTNO);

#查询部门名称和每一个部门分别有多少员工
SELECT d.DEPTNO,d.DNMAE,count(e.EMPNO) FROM emp e RIGHT JOIN dept d using(DEPTNO) GROUP BY d.DEPTNO,d.DNMAE;

4.伪表查询

#查询员工信息  高于自己部门的平均薪资的员工
SELECT DEPTNO,AVG(SAL) avgsal FROM emp GROUP BY DEPTNO;

SELECT * FROM emp e JOIN (SELECT DEPTNO,AVG(SAL) avgsal FROM emp GROUP BY DEPTNO) da on(e.DEPTNO=da.DEPTNO and e.SAL>da.avgsal) ORDER BY e.DEPTNO;

十五、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

1.带比较运算符的子查询

比较运算符前面我们提到过得，就是>、<、=、>=、<=、!=等

#员工薪资大于平均薪资的人
SELECT * FROM emp WHERE SAL > (SELECT AVG(SAL)FROM emp );

2.带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询， 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会 执行

SELECT* FROM dept WHERE EXISTS	(SELECT * FROM emp WHERE ENAME='KING');

3.带ANY关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

#等同于小于等于30部门的最高工资
SELECT * FROM emp WHERE SAL <  any(SELECT SAL FROM emp WHERE DEPTNO =30);

4.带ALL关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。

#查询谁的薪资低于30部门所有的人薪资
SELECT * FROM emp WHERE SAL <  all(SELECT SAL FROM emp WHERE DEPTNO =30);

十六、视图

1.视图语法

CREATE VIEW 视图名 as 查询语句

#等同于小于30部门的最高工资
CREATE VIEW da as (SELECT * FROM emp WHERE SAL <  any(SELECT SAL FROM emp WHERE DEPTNO =30));

SELECT * FROM da;

2.视图修改

ALTER VIEW 视图名 as 查询语句；

#查询谁的薪资低于30部门所有的人薪资
ALTER VIEW da as SELECT * FROM emp WHERE SAL <  all(SELECT SAL FROM emp WHERE DEPTNO =30);

SELECT * FROM da;

3.视图删除

ALTER VIEW 视图名 as 查询语句；

#删除V_de视图
DROP VIEW v_de;

十七、SQL大数据量查询的优化

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

总结

重要（从关键字分析）：
查询语句的书写顺序和执行顺序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序
from ===> where ===> group by ===> having ===> select ===> order by ===> limit

#查询员工部门在（10，20）且在4月份入职 平均工资大于1500的平均工资和部门所在及什么工作
SELECT AVG(SAL),DEPTNO,JOB FROM emp WHERE DEPTNO in (10, 20) AND EXTRACT(MONTH FROM HIREDATE) = 4 GROUP BY DEPTNO,JOB HAVING AVG( SAL)>1500;

参考资料

csdn mysql基础学习资料https://blog.csdn.net/weixin_45851945/article/details/114287877?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164932450016780366586682%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=164932450016780366586682&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_alltop_positive~default-1-114287877.142^v7pc_search_result_control_group,157^v4control&utm_term=mysql&spm=1018.2226.3001.4187