app开发的价格清单_万网和慧聪网_美国疫情最新数据消息_互联网推广

简介

MySQL是一个免费、开源的数据库管理系统，主要负责管理关系型数据，属于Oracle公司。MySQL分为社区版和企业版，社区版是免费的，企业版提供了完整的技术支持。

MySQL是客户端-服务器架构的数据库管理系统。

官网：https://www.mysql.com/cn/

数据库和数据库管理系统

数据库：Database，按照特定的规则存储数据的仓库

数据库管理系统：Database Management System，操作和管理数据库的软件

数据库管理系统可分为两类：

基于共享文件系统的数据库管理系统，比如Microsoft Access，主要用于桌面用途
基于客户端-服务器的数据库管理系统，比如MySQL、Oracle

关系型数据库

Relational Database，采用表来存储结构化数据，使用SQL来操作数据。

关系型数据库采用关系模型来存储数据

关系模型：关系模型就是表，用二维表的形式表示实体和实体间联系的数据模型。

关系模型中的基本概念：

关系：一个关系对应着一个二维表，表名就是关系名
元组：在二维表中的一行，称为一个元组
属性：二维表中的字段名称

一维表和二维表：一维表只有行这一个维度，二维表有行和列两个维度，一维表和二维表在外观上完全一样，只是概念上有所不同

依据数据是否有结构来对数据进行分类

结构化数据：由二维表格来逻辑表达和实现的数据，数据以行为单位，一行数据表示一个实体，每一行数据的属性是相同的
半结构化数据：虽然不符合二维表这种数据模型，但是包含相关标记，用来区分字段和记录，常见的半结构化数据有XML和JSON
非结构化数据：没有任何预定义的数据模型

安装和卸载

在很多地方都安装和卸载过mysql，这里进行一个全面的总结。

Windows

安装MySQL

在Windows上有以安装包进行安装和直接解压二进制文件进行安装两种方式，这里选择的第二种。

页面链接：https://www.oracle.com/mysql/technologies/mysql-enterprise-edition-downloads.html

安装步骤：

解压二进制文件后，配置环境变量
以管理员的身份运行cmd，执行命令：mysqld --initialize-insecure --user=mysql ，在mysql根目录下生成data文件夹
执行命令 mysqld -install
执行命令 net start MySQL
执行命令 mysqladmin -uroot -p password root，root 密码一开始为空，所以不用写。
如果想要持久化的定义一些配置，在根目录下新建 my.ini 文件，写入配置信息即可。

my.ini文件中的配置：

[mysqld]
port=3306
basedir=D:\mysql-8.0.19-winx64
datadir=D:\mysql-8.0.19-winx64\data
max_connections=200
max_connect_errors=10
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
default-time-zone='+8:00'
secure_file_priv=''
character-set-server=utf8mb4[mysql]
default-character-set=utf8mb4[client]
port=3306
default-character-set=utf8mb4

Windows系统中MySQL根目录下的文件夹和文件：

bin：存放可执行文件，如mysql、mysqld
data：存放日志文件和数据库，MySQL中存储的数据全部都保存在data目录下。在data目录下，一个数据库中的数据会被存放在一个子文件夹中
docs：存放文档
include：MySQL是用C和C++写成的，这个文件夹下存放C语言的头文件
lib：存放库文件
share：存放字符集、语言等信息
my.ini文件：在Windows中，MySQL的配置文件是my.ini，在Linux中MySQL的配置文件是my.cnf。一般情况下，只要修改这个配置文件中的内容，就可以对MySQL进行配置。

my.ini中的配置详解：

配置文件的结构：

[模块名称]
键=值
....
[模块名称]
...

模块名称：

[mysqld]：MySQL服务器端的配置
[mysql]、[client]：MySQL客户端的配置

MySQL服务器的端的配置：

port：服务器的端口
basedir：MySQL的安装路径
datadir：MySQL的数据存储路径
default-storage-engine：默认的存储引擎
sql-mode：SQL模式的参数，通过这个参数可以设置检验SQL语句的严格程度
max_connections：允许同时访问MySQL服务器的最大连接数，其中一个连接是保留的，留给管理员专用的
max_connect_errors：允许连接失败的次数
query_cache_size：查询时的缓存大小，缓存中可以存储以前通过select语句查询过的信息，再次查询时就可以直接从缓存中拿出信息
table_cache：所有进程打开表的总数
tmp_table_size：内存中临时表的总数
character-set-server：服务器端的字符集
default-time-zone：服务器端的默认时区

MySQL客户端的配置：

default-character-set：客户端默认的字符集

Windows上登录MySQL

启动MySQL服务：

第一种方式：右击我的电脑 - 管理 - 服务，找到MySQL服务，右击，在弹出的菜单中点击启动，启动MySQL程序的服务器
第二种方式：在命令行执行命令mysqld

进入MySQL界面：进入命令行，输入mysql -u 用户名 -p，回车，输入密码，进入MySQL客户端。在安装MySQL时会默认创建root用户，第一次登录使用root用户登录

卸载MySQL

进入计算机管理，停止MySQL服务，
在控制面板卸载掉mysql的相关程序，
将MySQL的安装目录下的MySQL删除，
将 C:\ProgramData\MySQL 删除，
进入注册表，删除：
- HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL文件夹，
- HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL文件夹。
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL的文件夹。
如果mysql已经删除，但服务仍然在后台运行，以管理员的身份运行cmd ，输入命令 sc delete mysql , 删除服务

Linux

安装MySQL

首先需要删除Linux上默认安装的mysql
- 执行命令rpm -qa | grep mysql，查看是否有mysql的安装包，如果有，使用rpm -e ${安装包名}删除掉
下载mysql的压缩包：
- 下载链接：https://downloads.mysql.com/archives/community/
- 下载结果：mysql-8.1.0-linux-glibc2.17-x86_64.tar.xz，大小400M左右

在这里插入图片描述

解压压缩包：tar -xvf mysql-8.1.0-linux-glibc2.17-x86_64.tar.xz
重命名解压出的结果：mv mysql-8.1.0-linux-glibc2.17-x86_64 mysql-8.1.0
配置环境变量：vi /etc/profile

在这里插入图片描述

添加mysql用户组合mysql用户：这样做的好处是，在mysql启动的时候，mysqld进程的属主就是mysql，保证了mysql服务的独立性
- 查看是否有名为mysql的用户组：groups mysql
- 添加mysql用户组和mysql用户： groupadd mysql && useradd -r -g mysql mysql
  - useradd的参数-g，是指定mysql用户所属的用户组
  - useradd的参数-r，是创建一个系统账户
在mysql的根目录下新建文件夹，名为data，存储将来mysql运行时产生的数据
修改mysql文件夹所属的用户：chown mysql:mysql -R mysql-8.1.0，在这里，将整个mysql目录的所属用户变为刚才新建的用户
新建/etc/my.cnf 文件，添加如下配置：

[mysqld]
# 设置3306端口
port=3306# 设置mysql的安装目录
basedir=/opt/mysql-8.1.0# 设置mysql数据库的数据的存放目录
datadir=/opt/mysql-8.1.0/data# 允许最大连接数
max_connections=10000# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10# 服务端使用的字符集默认为UTF8
character-set-server=UTF8MB4# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password[mysql]
# 设置mysql客户端默认字符集
default-character-set=UTF8MB4[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306default-character-set=UTF8MB4

安装MySQL需要的两个库，提前安装好：yum install -y libaio numactl
在MySQL根目录下执行命令：mysqld --initialize --user=mysql --basedir=/opt/mysql-8.1.0 --datadir=/opt/mysql-8.1.0/data，初始化生成随机密码，并打印在屏幕上，保存好

在这里插入图片描述

修改文件support-files/mysql.server，在其中设置basedir和datadir的值

在这里插入图片描述

执行命令：support-files/mysql.server start , 启动MySQL服务器，以便客户端的连接
设置MySQL开机自启：

cp support-files/mysql.server /etc/init.d/mysqld && \
chkconfig --add mysqld && \
chkconfig --level 345 mysqld on

查看设置结果：

[root@Linux4-CentOS7 mysql-8.1.0]# service mysqld statusSUCCESS! MySQL running (2188)

[root@Linux4-CentOS7 mysql-8.1.0]# chkconfig注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。要列出 systemd 服务，请执行 'systemctl list-unit-files'。查看在具体 target 启用的服务请执行'systemctl list-dependencies [target]'。mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关
netconsole      0:关    1:关    2:关    3:关    4:关    5:关    6:关
network         0:关    1:关    2:开    3:开    4:开    5:开    6:关

在mysqld --initialize 命令后会生成一个随机密码，以此密码进入数据库：mysql -u root -p，按下回车键，输入临时密码
- 更改密码：SET password for root@localhost = '123456';
- 刷新权限：FLUSH PRIVILEGES;
新建用户并赋权：
- 新建用户：CREATE USER 'blog_system'@'%' IDENTIFIED BY '123456';
- 赋权：GRANT ALL ON blog_system.* TO blog_system@'%';，这里的ALL表示所有权限，还可以是SELECT、INSERT、UPDATE、DELETE等，blog_system.*表示blog_system数据库下的所有表，注意，这里的用户名也是blog_system。

mac

我使用brew来安装mysql的，安装步骤：

安装：brew install mysql
执行安全脚本，设置root用户的密码：mysql_secure_installation，这是安全度比较低的密码
启动mysql服务：brew services start mysql，关闭是stop命令，重启是restart命令
查看mysql版本：mysql --version
进入mysql：mysql -u root -p qwe137549

数据库和数据表

它们是mysql中存储数据的单位。

数据库：存储和组织数据表。MySQL中的数据库分为系统数据库和自定义数据库。

系统数据库是MySQL自带的，用于存储MySQL自身信息的数据库。
自定义数据库：用户自己定义的数据库，存储用户自己的数据

数据表：存储和组织数据的基本单位，一个数据表对应现实中的一个模型，每一个字段都是一个属性，每一行数据都是一个实体。对比面向对象编程，数据表相当于类，数据表中的一行相当于一个对象。

mysql8.0 自带的数据库：

mysql：主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。
information_schema：信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。
performance_schema：主要用于收集数据库服务器性能参数，可用于监控服务器在一个较低级别的运行过程中的资源消耗、资源等待等情况
sys：库中所有的数据源来自performance_schema。目标是把performance_schema的把复杂度降低

进入mysql后查看当前有哪些数据库：show databases;

SQL语句

用户通过SQL语句来操作数据库

SQL：Structured Query Language ，结构化查询语言，用于对关系型数据库进行操作。符合标准的SQL可以在所有的关系型数据库中使用，但是每个数据库厂商都有自己特殊的语法。

SQL的种类：依据功能的不同，SQL分为4种：

数据控制语言：Data Control Language，简称DCL，定义用户的访问权限
数据定义语言：Data Difinition Language，简称DDL，执行建库、建表等
数据操作语言：Data Manipulation Language，简称DML，执行对数据的增删改查
数据查询语言：Data Query Language，简称DQL，查询表中的数据

SQL书写规范：

SQL语句要以分号结尾
SQL语句不区分大小写，但是通常，关键字大写，表名、字段名小写

注释

每一种语言都有自己的注释方式，代码量越多，代码注释的重要性就越高。

注释的分类：MySQL中的注释分为单行注释和多行注释。

单行注释：
- #：井号后的内容直到行尾都会被视为注释内容
- --：双横杠后面必须有空格，双横杠加空格后的内容，直到行尾，都会被视为注释
多行注释：/* */，这两个符号内的所有内容都会被视为注释

查看MySQL的帮助文档：在命令行输入HELP命令。

案例：

mysql> # 多行注释
mysql> -- 单行注释
mysql> /* 多行注释 */
mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jmeter_learn       |
| mybatis_learn      |
| mysql              |
| sys                |
| test               |
+--------------------+

在命令行使用mysql的小技巧

查询语句可以使用"\G"结尾，结果会以"字段: 值"的形式来显示，一个值一行，这种显示方式会让结果更加容易观看。

操作数据库的SQL语句

创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] <数据库名> 
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

语法讲解：

IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚步存在时才能执行操作，避免因数据库已存在而产生的错误
[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则。

MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。

字符集是用来定义MySQL存储字符串的方式
校对规则定义了比较字符串的方式

查看数据库

查看数据库的创建语句

SHOE CREATE DATABASE <数据库名称>;

查看有哪些数据库

show databasee;

使用LIKE语句查找指定的数据库

SHOW DATABASES LIKE <'LIKE子句的表达式'>;

LIKE是SQL语句中的关键字，表示模糊查询，在LIKE语句中提供一个表达式，然后LIKE语句返回名称符合这个表达式的所有数据库。
在LIKE语句的表达式中，百分号匹配多个字符，下横杠匹配单个字符

修改数据库

语法：

ALTER DATABASE [数据库名] 
{ [DEFAULT] CHARACTER SET <字符集名> | [DEFAULT] COLLATE <校对规则名>}

删除数据库

DROP DATABASE [IF EXISTS] 数据库名称;

IF EXISTS：如果存在，则执行后面的语句，用于防止当数据库不存在时发生错误

使用数据库

USE <数据库名称>;

案例

创建数据库learn_db：CREATE DATABASE learn_db;
查询数据库的创建语句：

CREATE DATABASE learn_db 
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ *
/!80016 DEFAULT ENCRYPTION='N' */

进入learn_db数据库：USE learn_db;
查看当前有哪些数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learn_deb          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

操作数据表的SQL语句

创建数据表

语法：

CREATE TABLE <表名> 
(<列名><类型> [PRIMARY KEY [AUTO_INCREMENT]] [UNIQUE] [NOT NULL] [DEFAULT <值>] [COMMENT <'备注'>],
....,
[CONSTRAINT <constraint_name> FOREIGN KEY(<字段>) REFERENCES<表名(<字段>)>]);  
[表选项][分区选项];

语法讲解：

表的基本结构是字段，每个字段都有自己的数据类型，某些特殊的字段还会有约束，用于限定字段内的数据

数据类型

数据表中的每一列都有自己的数据类型，不同的数据类型有不同的取值范围，占用的内存大小也是不同的。

mysql中的数据类型：可以分为整数型、小数型、字符串、日期时间、二进制类型五种。

整数型

TINYINT[(m)] [UNSIGNED]：1字节
SMALLINT[(m)] [UNSIGNED]：2字节
MEDIUMINT[(m)] [UNSIGNED]：3字节
INT[(m)] [UNSIGNED]：4字节，也可以写为Integer
BIGINT[(m)] [UNSIGNED]：8字节

UNSIGNED：数据类型关键字后如果加上UNSIGNED，表示无符号数据；

‘m’ ：表示数据在显示时的最小长度，而且在设置了zerofill之后才会有用，一般不推荐使用这种方式，因为在建表时会有默认的m值

小数型

FLOAT[(m,d)]：单精度浮点数，4字节。

DOUBLE[(m,d)]：双精度浮点数，8字节

DECIMAL(m,d)：定点数，m+2字节。在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候，如货币、科学数据，使用 DECIMAL 的类型

m称为精度，表示总位数；
d称为标度，表示小数的位数。
m的取值范围是1-255，d的是1-30，且不能大于m-2，如果不指定精度，默认为(10, 0)

案例：DECIMAL(10, 2)：10是数值的总长度，包括整数部分和小数部分，2是小数部分的长度。

FLOAT和DOUBLE 在不指定精度时，默认会按照实际的精度，这是由计算机硬件和操作系统决定。

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围，缺点是会引起精度问题。

字符串

CHAR(n)：定长字符串，n表示存储几位字符，1<=n<=255。存储占用n字节
VARCHAR(n)：变长字符串，n表示最多存储几位字符串，1<=n<=16383。存储占用n+1字节，需要有额外的一个字节来存储长度
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT：保存非二进制字符串，专为存储极大的字符串而设计的数据类型

日期时间

日期时间的值要加引号

DATE：日期类型，占用3个字节，日期的格式是：“YYYY-mm-dd”；
TIME：时间类型，占用3个字节，时间的格式是：“HH:mm:ss”，
DATETIME：日期时间类型，占用8个字节，日期时间类型的格式是：“YYYY-mm-dd HH:mm:ss”。
TIMESTAMP：时间戳类型

二进制类型

存储二进制类型的数据

BLOB：使用二进制的方式来存储字符串

其它类型

boolean类型：相当于tinyint(1)

clob类型：存储大量的字符数据

blob类型：存储大量的二进制数据

案例：

drop table test1;
create table test1(-- 整数类型a_tinyint TINYINT(10),a_smallint SMALLINT(11) comment "注释1",a_mediumint MEDIUMINT(12),a_int INT(13), a_integer integer(15),a_bigint BIGINT(14),a_boolean boolean,a_bit bit,-- 小数类型a_float float(10,2),a_double double(15, 2),a_decimal decimal(10, 5),-- 字符串类型a_char char(10),a_varchar varchar(50),a_tinytext tinytext,a_text text,a_mediumtext mediumtext,a_long_text longtext,-- 日期类型a_date date,a_time time,a_datetime datetime,a_timestamp timestamp,a_year year,-- 二进制类型a_blob blob
);

常量值

MySQL中默认true相当于1，false相当于0，NULL、null表示空值，字符串放在单引号或双引号中均可。注意，mysql中没有boolean类型，但是用户可以使用tinyint来模拟boolean类型

约束

约束：constraint，对表中数据的限制，约束施加在某个字段之上，那么这个字段的值必须符合条件，否则就会报错。约束可以保证表中数据的正确性和有效性。

约束的分类：依据功能，约束可以分为：

主键约束：PRIMARY KEY，被设置主键约束的字段，其值不可为null，不可重复。主键约束可以保证表的实体完整性
唯一约束：UNIQUE，一个表中可以有多个唯一约束，被设置唯一约束的字段，要求该列唯一，可以为null，但只能有一个null
默认约束：DEFAULT <值>，用来指定某列的默认值，插入数据时如果没有为该列赋值，那么使用默认值
非空约束：NOT NULL，被设置非空约束的字段，其值不可以为空
外键约束：[CONSTRAINT <constraint_name> FOREIGN KEY(<字段>) REFERENCES<表名(<字段>)>]，用来在两个表的数据之间建立链接，
- 外键对应的是参照完整性，一个表可以有多个外键，本表的外键必须指向另一个表的主键，且他们的数据类型必须数一致。
- 默认情况下，如果主键所关联的外键上有数据，那么主键不可以被删除但是外键可以被删除，外键不可以增加主键没有的字段，主键不可以修改
检查约束：检查值是否符合给定的表达式，MySQL会忽略检查约束

约束的语法：除了外键约束，其它约束都可以直接声明在字段名称之后，逗号之前，外键声明在表的最后，并且和其它字段用逗号隔开。

自增长

AUTO_INCREMENT，只有整数类型的、被PRIMAEY KEY或UNIQUE修饰的字段才可以设置自增长，当某个字段被设置为自增长之后，用户就不需要为这个字段赋值了，默认情况下，AUTO_INCREMENT 的初始值是1，每新增一条记录，字段值自动加1。如果用户手动设置了一个值，那么从用户设置的值开始自增。

案例

创建学生表：id字段是主键并且自增长

CREATE TABLE student (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50),date_of_birth datetime,class varchar(50)
);

创建学生分数表：分数表引用学生表中的id作为外键。

CREATE TABLE score (id int PRIMARY KEY AUTO_INCREMENT,student_id int,examination varchar(50),course varchar(50),score double,CONSTRAINT c1 FOREIGN KEY(student_id) REFERENCES student(id)
);

查看数据表的结构

查看建表语句

语法：SHOW CREATE TABLE 表名;

案例1：show create table score;

| score | CREATE TABLE score (id int NOT NULL AUTO_INCREMENT,student_id int DEFAULT NULL,examination varchar(50) DEFAULT NULL,course varchar(50) DEFAULT NULL,score double DEFAULT NULL,PRIMARY KEY (id),KEY c1 (student_id),CONSTRAINT c1 FOREIGN KEY (student_id) REFERENCES student (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

查看表结构

语法：{DESC | DESCRIBE} <表名>;

案例1：查看score表的结构

mysql> DESC score;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int         | NO   | PRI | NULL    | auto_increment |
| student_id  | int         | YES  | MUL | NULL    |                |
| examination | varchar(50) | YES  |     | NULL    |                |
| course      | varchar(50) | YES  |     | NULL    |                |
| score       | double      | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

向表中添加数据

语法：INSERT INTO <表名> [(字段, ...)] VALUES (值,...) , ...

如果是向表中的所有字段赋值，指定字段的子句可以省略。

案例1：向student表中添加数据

-- 一次性向数据库中插入3条语句，在这里不考虑id字段的值，因为它是自增的
-- datatime类型的数据，在赋值时，使用“yyyy-MM-dd HH:mm:ss”格式的字符串即可，不是这个格式的字符串会报错
INSERT INTO student (name, date_of_birth, class) VALUES ('张三', '2018-05-04 08:30:00', '1班'),('李四', '2018-05-07 08:30:00', '1班'), ('王五', '2018-06-01 18:01:00', '1班');

查看添加结果：

mysql> select * from student;
+----+--------+---------------------+-------+
| id | name   | date_of_birth       | class |
+----+--------+---------------------+-------+
|  1 | 张三   | 2018-05-04 08:30:00 | 1班   |
|  2 | 李四   | 2018-05-07 08:30:00 | 1班   |
|  3 | 王五   | 2018-06-01 18:01:00 | 1班   |
+----+--------+---------------------+-------+

查询表中的数据

在之前的案例中简单接触了 select * from 表名 的语法，在这里正式学习从数据表中查询数据，这是sql语句中最复杂的部分。

查询表中数据的语法：

[EXPLAIN]
SELECT [DISTINCT] <字段 , ...>
FROM <表名>[[INNER | LEFT | RIGHT] JOIN] <表名> ON <条件>
[WHERE <条件>]
[GROUP BY <字段, ...>]
[HAVING <条件>]
[ORDER BY <字段 [ASC | DESC], ....>]
[LIMIT [<偏移量,>] 行数];

接下来学习语法中涉及到的关键字

SELECT 、FROM

SELECT：指定最终的结果中要展示哪些字段，可以是一个星号，表示所有字段.

FROM：从哪张表读取数据

案例：

SELECT id, name, date_of_birth, class 
FROM student;

+----+--------+---------------------+-------+
| id | name   | date_of_birth       | class |
+----+--------+---------------------+-------+
|  1 | 张三   | 2018-05-04 08:30:00 | 1班   |
|  2 | 李四   | 2018-05-07 08:30:00 | 1班   |
|  3 | 王五   | 2018-06-01 18:01:00 | 1班   |
+----+--------+---------------------+-------+
3 rows in set (0.00 sec)

WHERE

WHERE 关键字后会声明一个条件表达式，依据这个条件，对表中的行进行过滤，WHERE子句中的字段要和实体表中的字段名称一致，因为WHERE从FROM后的表中选择字段

条件表达式中的可以使用的运算符：

算术运算符：+、-、*、/、%
比较运算符：
- =：一般用作比较运算符，用在set语句中是赋值运算符，要注意sql语句中没有 ==
- <>、!=：不等于
- <=>：安全等于运算符，当两个表达式彼此相等或都等于空值时，比较结果为true，若其中一个为空时则为false，因为在 ‘=’ 比较运算符中如果某个比较的值为空，那么结果为空；
- >、>=、<、<=：
- 字段 [not] BETWEEN x AND y：范围匹配
逻辑运算符：and、or、not
位运算符：&、|、^、!、<<、>>
判断是否为null：IS NULL、IS NOT NULL
模糊查询：[NOT] LIKE，使用特定格式的字符串来匹配字段的值，一定要全部匹配才算匹配成功，LIKE可以用作对查询结果的过滤，包括show databases、show tables等语句。
- LIKE条件中的特殊字符
  - %：匹配多个任意字符
  - _ ：匹配一个任意字符
正则表达式：[NOT] REGEXP 、[NOT] RLIKE，MySQL的正则表达式不区分大小写并且无需全部匹配，只需要匹配一部分即视为匹配，RLIKE和REGEXP必须使用在WHERE或HAVING之后

案例1：比较运算符，查询student表中学生姓名等于“张三的数据”

SELECT * 
FROM student 
WHERE name = "张三";

+----+--------+---------------------+-------+
| id | name   | date_of_birth       | class |
+----+--------+---------------------+-------+
|  1 | 张三   | 2018-05-04 08:30:00 | 1班   |
+----+--------+---------------------+-------+
1 row in set (0.00 sec)

案例2：模糊查询，查询student表中学生姓名中包含“王”这个字符的数据。

SELECT * 
FROM student 
WHERE name LIKE "%王%";

+----+--------+---------------------+-------+
| id | name   | date_of_birth       | class |
+----+--------+---------------------+-------+
|  3 | 王五   | 2018-06-01 18:01:00 | 1班   |
+----+--------+---------------------+-------+
1 row in set (0.00 sec)

案例3：正则表达式，查询student表中学生姓名是两个字符的数据

SELECT * 
FROM student 
WHERE name REGEXP '[\\w]{2}';

+----+--------+---------------------+-------+
| id | name   | date_of_birth       | class |
+----+--------+---------------------+-------+
|  1 | 张三   | 2018-05-04 08:30:00 | 1班   |
|  2 | 李四   | 2018-05-07 08:30:00 | 1班   |
|  3 | 王五   | 2018-06-01 18:01:00 | 1班   |
+----+--------+---------------------+-------+
3 rows in set (0.00 sec)

GROUP BY

分组聚合，GROUP BY 子句会指定某些字段，然后配合聚合函数，以这些字段为分组条件，对其它字段进行聚合计算

GROUP BY子句中的字段要和实体表中的字段名称一致。
GROUP BY子句通常和聚合函数一起使用，来对表中的数据作分组聚合计算。

和GROUP BY子句一起使用的聚合函数：

求和：sum([DISTINCT] 列名)
求平均值：avg([DISTINCT] 列名)
求最大值：max([DISTINCT] 列名)
求最小值：min([DISTINCT] 列名)
计数：count([DISTINCT] 列名 [, 列名 ... ])

这5个聚合函数都可以和DISTINCT连用，它表示对先对数据进行去重，然后再聚合。

如果查询语句中出现了GROUP BY子句，那么在SELECT子句中，只可以出现GROUP BY子句后的字段和聚合函数。如果没有指定GROUP BY子句，那么聚合函数默认把全表作为一个组。

案例：求每科成绩的平均分

SELECT course, avg(score) avg_score
FROM score
GROUP BY course;

+--------+-----------+
| course | avg_score |
+--------+-----------+
| 语文   | 85.000000 |
| 数学   | 89.333333 |
| 科学   | 76.666667 |
+--------+-----------+
3 rows in set (0.00 sec)

这里计算结果默认保留6位小数，随后使用自定义函数来处理小数点后的数据

字段别名

为字段或表设置别名 : 表名或字段名后加 ‘AS <别名>’，可以为表名或字段名设置别名，AS可以省略。

在之前GROUP BY章节的案例中，就使用到了字段别名

HAVING

依据条件对表中的数据进行过滤，HAVING从SELECT和WHERE后面选择字段，是一个过滤声明

案例：求平均分大于80的课程

SELECT course, avg(score) avg_score
FROM score
GROUP BY course
HAVING avg_score > 80;

+--------+-----------+
| course | avg_score |
+--------+-----------+
| 语文   | 85.000000 |
| 数学   | 89.333333 |
+--------+-----------+
2 rows in set (0.00 sec)

WHERE和HAVING的区别

WHERE和HAVING都是从查询结果中过滤数据，它们之间的区别在于：

WHERE从实体表中选择字段，是一个约束声明
HAVING从SELECT子句中选择字段，是一个过滤声明

ORDER BY

依据指定字段对数据进行排序，ASC表示升序，DESC表示降序，如果不指定，默认是升序排序

案例：求每科成绩的平均分并且从大到小排序

SELECT course, avg(score) avg_score
FROM score
GROUP BY course
ORDER BY avg_score DESC;

+--------+-----------+
| course | avg_score |
+--------+-----------+
| 数学   | 89.333333 |
| 语文   | 85.000000 |
| 科学   | 76.666667 |
+--------+-----------+
3 rows in set (0.00 sec)

LIMIT

限制查询结果的记录数

语法：LIMIT 偏移量,记录数

偏移量可以省略，默认从0开始
记录数是指要查询几条记录

案例：查看第3页数据，假设每页2条数据，pageNo = 3, pageSize = 2，偏移量 = (pageNo - 1) * pageSize = 4

SELECT id, name, date_of_birth, class
FROM student
LIMIT 4, 2;

+----+-----------+---------------------+-------+
| id | name      | date_of_birth       | class |
+----+-----------+---------------------+-------+
|  5 | 田七      | 2018-04-04 08:34:00 | 1班   |
|  6 | 张三      | 2018-05-04 08:30:00 | 1班   |
+----+-----------+---------------------+-------+
2 rows in set (0.00 sec)

通常使用LIMIT来实现分页查询，这里做个演示。

DISTINCT

对最终结果进行去重，DISTINCT可以使用在SELECT子句后和聚合函数中。

案例1：在SELECT子句中使用DISTINCT

没有使用DISCTINT的结果

SELECT class
FROM student;

+-------+
| class |
+-------+
| 1班   |
| 1班   |
| 1班   |
+-------+
3 rows in set (0.00 sec)

使用DISCTINT的结果

SELECT DISTINCT class
FROM student;

+-------+
| class |
+-------+
| 1班   |
+-------+
1 row in set (0.00 sec)

案例2：在聚合函数中使用DISTINCT。

没有使用DISCTINT的结果：统计student表中的class字段有多少个值，

SELECT COUNT(class) co 
FROM student;

+----+
| co |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

使用DISCTINT的结果：统计student表中的class字段，在相同值只计算一次的情况下有多少个值

SELECT COUNT(DISTINCT class) co 
FROM student;

+----+
| co |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

结果解析：使用了DISTINCT，相同的值会被去重，所以只会被计算1次

JOIN

多表联合查询：在实际的生产中，通常会把数据拆分到多张表中，减少单张表的数据量，在查询时，在把多张表的数据联合在一起。

表连接的语法：

SELECT 
FROM 表1 JOIN 表2 ON 条件[...]
WHERE 条件

表连接的分类：

内连接：[INNER] JOIN，求两个表的交集，ON子句条件成立的记录
左连接：LEFT [OUTER] JOIN：，ON子句条件成立的记录再加上左表剩余的记录，
右连接：RIGHT [OUTER] JOIN：，ON子句添加成立的记录再加上右表剩余的记录
外连接：OUTER JOIN：左连接 + 右连接 = 外连接

mysql 不支持外连接

左连接和右连接互为逆操作，但是在实际生产中，很少用到右连接，因为左连接更加符合人的思维、

案例1：内连接

要求查询每个学生的成绩，结果中包括学生id、学生姓名、科目、成绩

SELECT st.id, st.name, sc.examination, sc.course, sc.score
FROM student stJOIN score scON st.id = sc.student_id;

+----+--------+-----------------------+--------+-------+
| id | name   | examination           | course | score |
+----+--------+-----------------------+--------+-------+
|  1 | 张三   | 一年级期末考试        | 语文   |  98.5 |
|  1 | 张三   | 一年级期末考试        | 数学   |  90.3 |
|  1 | 张三   | 一年级期末考试        | 科学   |  85.7 |
|  2 | 李四   | 一年级期末考试        | 语文   |  80.4 |
|  2 | 李四   | 一年级期末考试        | 数学   |  80.3 |
|  2 | 李四   | 一年级期末考试        | 科学   |  76.7 |
|  3 | 王五   | 一年级期末考试        | 语文   |  79.5 |
|  3 | 王五   | 一年级期末考试        | 数学   |  98.3 |
|  3 | 王五   | 一年级期末考试        | 科学   |  67.1 |
+----+--------+-----------------------+--------+-------+
9 rows in set (0.00 sec)

案例2：左连接

要求查询每个学生的成绩，结果中包括学生id、学生姓名、科目、成绩

查询数据：

SELECT st.id, st.name, sc.examination, sc.course, sc.score
FROM student stLEFT JOIN score scON st.id = sc.student_id;

结果：


+----+--------+-----------------------+--------+-------+
| id | name   | examination           | course | score |
+----+--------+-----------------------+--------+-------+
|  1 | 张三   | 一年级期末考试        | 语文   |  98.5 |
|  1 | 张三   | 一年级期末考试        | 数学   |  90.3 |
|  1 | 张三   | 一年级期末考试        | 科学   |  85.7 |
|  2 | 李四   | 一年级期末考试        | 语文   |  80.4 |
|  2 | 李四   | 一年级期末考试        | 数学   |  80.3 |
|  2 | 李四   | 一年级期末考试        | 科学   |  76.7 |
|  3 | 王五   | 一年级期末考试        | 语文   |  79.5 |
|  3 | 王五   | 一年级期末考试        | 数学   |  98.3 |
|  3 | 王五   | 一年级期末考试        | 科学   |  67.1 |
|  4 | 赵六   | NULL                  | NULL   |  NULL |
+----+--------+-----------------------+--------+-------+
10 rows in set (0.00 sec)

可以看到，在左连接中，左表有而右表没有的数据，右表显示为NULL

案例3：右连接

在这里，仅仅将左连接颠倒一下，作为演示

SELECT sc.examination, sc.course, sc.score, st.id, st.name
FROM score scRIGHT JOIN student stON st.id = sc.student_id;

+-----------------------+--------+-------+----+--------+
| examination           | course | score | id | name   |
+-----------------------+--------+-------+----+--------+
| 一年级期末考试        | 语文   |  98.5 |  1 | 张三   |
| 一年级期末考试        | 数学   |  90.3 |  1 | 张三   |
| 一年级期末考试        | 科学   |  85.7 |  1 | 张三   |
| 一年级期末考试        | 语文   |  80.4 |  2 | 李四   |
| 一年级期末考试        | 数学   |  80.3 |  2 | 李四   |
| 一年级期末考试        | 科学   |  76.7 |  2 | 李四   |
| 一年级期末考试        | 语文   |  79.5 |  3 | 王五   |
| 一年级期末考试        | 数学   |  98.3 |  3 | 王五   |
| 一年级期末考试        | 科学   |  67.1 |  3 | 王五   |
| NULL                  | NULL   |  NULL |  4 | 赵六   |
+-----------------------+--------+-------+----+--------+
10 rows in set (0.00 sec)

结果解析：在右连接中，右表有而左表没有的数据，左表为NULL

两表join后的数据量

如果表A中的一个key如果和表B中的多个key相匹配，那么表A中的这一条数据在结果表中会变成多条。两表join后的数据量，取决于join字段的值有无重复，如果参与join的字段有重复值，那么结果中的条数就是 原条数 * 重复值

笛卡尔积

笛卡尔乘积其实是数学领域的概念，就是对两个集合做乘法，数据库中也引入笛卡尔乘积的概念，笛卡尔乘积对两表关联时，不要求指定关联属性。如果左表有m行，右表有n行，则两表的笛卡尔乘积将会有 m ∗ n`行

在实际生产中，用户在join数据前必须要知道join key是否是一对一的

USING

语法：JOIN <表名> USING(<字段>)

USING的功能相当于ON，用来指定连接条件，
USING子句指定的字段必须同时存在于两表，并且在两张表中的数据类型必须一致
USING子句相当于表A.字段 = 表B.字段

子查询

又叫嵌套查询，在SQL语句中使用"()"括起来的查询语句，子查询是一个完整的查询语句，它会返回自己的查询结果，有了子查询，用户可以在同一条SQL语句中作更多复杂的计算

案例：计算每个学生的平均分，在对平均分进行排序

SELECT id, name, examination, avg_score
FROM (SELECT st.id, st.name, sc.examination, avg(sc.score) avg_scoreFROM student stLEFT JOIN score scON st.id = sc.student_idWHERE score IS NOT NULLGROUP BY st.id, st.name, sc.examination
) t1
ORDER BY avg_score DESC;

+----+--------+-----------------------+-----------+
| id | name   | examination           | avg_score |
+----+--------+-----------------------+-----------+
|  1 | 张三   | 一年级期末考试        | 91.666667 |
|  3 | 王五   | 一年级期末考试        | 81.666667 |
|  5 | 田七   | 一年级期末考试        | 81.000000 |
|  2 | 李四   | 一年级期末考试        | 79.000000 |
+----+--------+-----------------------+-----------+
4 rows in set (0.20 sec)

子查询和某些关键字连用，可以作为WHERE子句后的条件表达式

可以和子查询连用的关键字：

IN：WHERE <字段> [not] IN (<子查询>)，子查询返回一个一列多行的集合，判断字段是否在这个集合之中，如果在，则 true，不在，则为false。
EXISTS：WHERE [NOT] EXISTS(子查询)，将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果集非空，则EXISTS子句返回TRUE。EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
ANY：WHERE <字段> <比较运算符> ANY(子查询)，对于子查询中返回的任何一个值，如果比较结果为 true，则为 true，否则为 false。
SOME：ANY的别名，用法相同。
ALL：WHERE <字段> <比较运算符> ALL(子查询)，对于子查询中返回的所有值，如果比较结果为 true，则为 true，否则为 false，它通常和比较运算符一起使用

案例1：IN运算符，查询参加过“一年级期末考试”的所有学生

SELECT id, name, class
FROM student
WHERE id IN (SELECT DISTINCT student_idFROM scoreWHERE examination = '一年级期末考试');

+----+--------+-------+
| id | name   | class |
+----+--------+-------+
|  1 | 张三   | 1班   |
|  2 | 李四   | 1班   |
|  3 | 王五   | 1班   |
+----+--------+-------+
3 rows in set (0.00 sec)

案例2：EXISTS运算符，查询一年级期末考试每一门课程都没有考的学生

SELECT id, name, class
FROM student st
WHERE NOT EXISTS (SELECT 1FROM score scWHERE examination = '一年级期末考试'AND st.id = sc.student_id);

+----+--------+-------+
| id | name   | class |
+----+--------+-------+
|  4 | 赵六   | 1班   |
+----+--------+-------+
1 row in set (0.00 sec)

案例3：EXISTS运算符，查询一年级期末考试至少考了一门课程的学生

SELECT id, name, class
FROM student st
WHERE EXISTS (SELECT 1FROM score scWHERE examination = '一年级期末考试'AND st.id = sc.student_id);

+----+--------+-------+
| id | name   | class |
+----+--------+-------+
|  1 | 张三   | 1班   |
|  2 | 李四   | 1班   |
|  3 | 王五   | 1班   |
|  5 | 田七   | 1班   |
+----+--------+-------+
4 rows in set (0.00 sec)

解析：案例3的sql和案例2的sql正好相反，对于子查询中的数据，如果没有结果集，则一门课都没有考，如果有结果集，则至少考了一门课。

案例：EXISTS运算符，一个比较复杂的案例

这是在学习过程中遇到的一个比较有难度的案例，所以特地记录在这里

造表：

-- 学生表
CREATE TABLE student (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50),date_of_birth datetime,class varchar(50)
);-- 课程表
CREATE TABLE course (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50)
);-- 考试成绩表，本表中的student_id对应student表中的id字段，course_id对于course表中的id字段，
-- 相当于一张中间表
CREATE TABLE score (id int PRIMARY KEY AUTO_INCREMENT,student_id int,course_id int,examination varchar(50),score double,CONSTRAINT c1 FOREIGN KEY(student_id) REFERENCES student(id)
);

造数据：

INSERT INTO student (name, date_of_birth, class) VALUES ('张三', '2018-05-04 08:30:00', '1班'),('李四', '2018-05-07 08:30:00', '1班'), ('王五', '2018-06-01 18:01:00', '1班'),('赵六', '2018-07-04 06:30:00', '1班'),('田七', '2018-04-04 08:34:00', '1班');INSERT INTO course (name) VALUES ('语文'), ('数学'), ('科学');INSERT INTO score (student_id, course_id, examination, score) VALUES (1, 1, '一年级期末考试', 98.5),(1, 2, '一年级期末考试', 90.3),(1, 3, '一年级期末考试', 85.7),(2, 1, '一年级期末考试', 80.4),(2, 2, '一年级期末考试', 80.3),(2, 3, '一年级期末考试', 76.7),(3, 1, '一年级期末考试', 79.5),(3, 2, '一年级期末考试', 98.3),(3, 3, '一年级期末考试', 67.1),(5, 1, '一年级期末考试', 80.5);

题目：查询一年级期末考试，每一门考试都有参加的学生

题目解析：查询每一名课程都有考的学生，可以转换为没有一门课没有被该学生考的学生

SELECT id, name
FROM student st 
-- 如果不存在当前学生没有考过的课，返回true
WHERE NOT EXISTS (-- 这里的结果是当前学生没有参加过考试的课程SELECT 1FROM course coWHERE NOT EXISTS (-- 判断：当前学生是否参加了当前考试SELECT 1FROM score scWHERE st.id = sc.student_idAND co.id = sc.course_id));

+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+
3 rows in set (0.00 sec)

sql解析：这个sql相当于一个双重for循环，

最外层的student表是第一层for循环，每一行student表中的数据都对应整个course表
第二层的course表是第二层for循环，每一行course表中的数据都对应整个score表
在第三层，根据score表中的数据，判断当前学生是否参加了当前课程
整个sql的结构，如果不存在当前学生没有参加的课程，那么当前学生就是考了全部的课程

UNION

把多个查询结果纵向连接在一起，有UNION和UNION ALL两种语句，要求多个查询结果的字段数量和字段类型必须一致。

UNION和UNION ALL的区别：

重复行的处理
- UNION操作符会删除结果集中的重复行
- UNION ALL操作符会保留所有的行，包括重复行
排序的处理
- UNION操作符在合并结果集后会按照默认的排序规则对结果进行排序
- UNION ALL只是简单地将两个结果集合并后返回，不会对结果进行排序

案例1：UNION会对于联合结果进行去重

SELECT 1 co
UNION
SELECT 1 co;

+----+
| co |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

案例2：UNION ALL不会对于联合结果进行去重

SELECT 1 co
UNION ALL
SELECT 1 co;

+----+
| co |
+----+
|  1 |
|  1 |
+----+
2 rows in set (0.00 sec)

注意： union 执行的排序是全量排序，即对select语句所包含的所有字段进行排序

我这里使用的是mysql，mysql中的union不会对合并结果进行排序

案例：

select 1 id ,'a1' val ,'b1' text
union
select 1 ,'a1' ,'b3'
union
select 1 ,'a1' ,'b2';

+----+-----+------+
| id | val | text |
+----+-----+------+
|  1 | a1  | b1   |
|  1 | a1  | b3   |
|  1 | a1  | b2   |
+----+-----+------+
3 rows in set (0.00 sec)

案例解析：在这个案例中，第一列和第二列的数据相同，但是第三列的数据却被排好序了，证明union是对select语句所包含的所有字段进行排序

注意：UNION 的执行顺序在ORDER BY之前，所以UNION之前不能有ORDER BY语句，会报错，

错误情况的案例：

SELECT id, student_id, examination, course, score
FROM score
WHERE course = '语文'
ORDER BY id  -- 在这里会报错
UNION ALL
SELECT id, student_id, examination, course, score
FROM score
WHERE course = '数学'
ORDER BY id;

报错记录：

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 
'UNION ALL 
SELECT id, student_id, examination, course, score
FROM score
WHERE cou' at line 5

开窗函数

开窗函数：开窗函数和聚合函数一样，对行进行聚合计算，和聚合函数不同的是，聚合函数为每一组返回一个值，开窗函数为每一组的每一行都返回一个值。聚合函数或排名函数等加OVER关键字，它们一起构成了开窗函数。开窗函数不可以用于HAVING或WHERE之后。

语法：

聚合函数或排名函数(字段) OVER(PARTITION BY 字段 ORDER BY {字段 [ASC | DESC], .. }{ROWS | RANGE} BETWEEN {UNBOUNDED PRECEDING | x PRECEDING | CURRENT ROW} AND {UNBOUNDED FOLLOWING | x FOLLOWING | CURRENT ROW})

语法解析：

OVER关键字表示把函数当成开窗函数。OVER关键字后的括号中可以添加选项来改变聚合计算的窗口范围，如果选项为空，则会对结果集中的所有行进行运算。
PARTITION BY：指定开窗函数按照哪些字段进行分区，相当于GROUP BY
ORDER BY：指定开窗函数按照哪些字段进行排序，相当于ORDER BY
ROWS表示按照行进行晒选，
RANGE表示按照值进行筛选。
UNBOUNDED：无界限
PRECEDING：表示前面的行
FOLLOWING：表示后面的行
CURRENT ROW：当前行，偏移量为0

可以和over关键字一起使用的函数：

聚合函数：
- sum([DISTINCT] 列名)
- avg([DISTINCT] 列名)
- max([DISTINCT] 列名)
- min([DISTINCT] 列名)
- count([DISTINCT] 列名 [, 列名 ... ])
排名函数：针对哪一列进行操作由over关键字指定
- rank()：n个相同值，它们的名次相同，下一个值的名次向后跳 n - 1 位
- dense_rank()：n个相同值，它们的名次相同，下一个值的名次紧接着上一个名次
- row_number()：每行返回一个值，值是递增的

MySQL不支持的可以和over关键字一起使用的函数：

first_value(列名)：取分组后本组内直到当前行的第一个值，
last_value(列名)：取分组后本组内的直到当前行的最后一个值
lag(列名, n, 默认值)：取分组内当前行向前第n行的值，如果没有值，则得到一个 “默认值”
lead(列名, n, 默认值)：取分组内当前行向后第n行的值，如果没有值，则得到一个 “默认值”

这些函数在其它数据库中是被支持的。

开窗函数的执行在LIMIT之前，因为LIMIT不是标准的WHERE从句后的子句。所有的查询都是从FROM开始执行的，每一个步骤会为下一个步骤生成一张虚拟表，作为下一个步骤的输入。

案例1：为每一行生成一个独特的编号

SELECT id, name, row_number()over()
FROM student;

+----+--------+--------------------+
| id | name   | row_number()over() |
+----+--------+--------------------+
|  1 | 张三   |                  1 |
|  2 | 李四   |                  2 |
|  3 | 王五   |                  3 |
|  4 | 赵六   |                  4 |
|  5 | 田七   |                  5 |
+----+--------+--------------------+
5 rows in set (0.01 sec)

案例解析：这是最简单的开窗函数的应用，如果over函数中没有指定PARTITION BY，那么默认整张表都是一组

案例2：查询每门课的前两名学生

SELECT course, name, examination, score
FROM (SELECT st.id, st.name, sc.examination, sc.course, sc.score, row_number() over(PARTITION BY course ORDER BY score DESC) roFROM student stLEFT JOIN score scON st.id = sc.student_idWHERE sc.score IS NOT NULL
) t1
WHERE ro <= 2;

+--------+--------+-----------------------+-------+
| course | name   | examination           | score |
+--------+--------+-----------------------+-------+
| 数学   | 王五   | 一年级期末考试        |  98.3 |
| 数学   | 张三   | 一年级期末考试        |  90.3 |
| 科学   | 张三   | 一年级期末考试        |  85.7 |
| 科学   | 李四   | 一年级期末考试        |  76.7 |
| 语文   | 张三   | 一年级期末考试        |  98.5 |
| 语文   | 田七   | 一年级期末考试        |  80.5 |
+--------+--------+-----------------------+-------+
6 rows in set (0.00 sec)

SQL语句中关键字的执行顺序

FROM、JOIN ON、WHERE、GROUP BY和聚合函数、SELECT、HAVING、DISTINCT、UNION、ORDER BY、开窗函数、LIMIT。

修改表中的数据

修改表中数据的语法：UPDATE <表名> SET <字段=值> WHERE <字段=值>;，对符合WHERE子句的行执行SET子句

UPDATE多表联合更新

根据一张表中的值，来更新另一张表中的值

格式一：

UPDATE 表1 JOIN 表2 ON 条件
SET 表1.字段 = 表2.字段

格式二：

UPDATE 表1, 表2
SET 表1.字段1 = 表2.字段1
WHERE 表1.字段2 = 表2.字段2 -- WHERE子句后的字段相当于格式一中ON子句后的字段

案例：

1、造数据：

CREATE TABLE student(id int,name varchar(20),chinese int,english int,math int
);insert into student (id, name) values (110, 'zhangsan');
insert into student (id, name) values (111, 'lisi');
insert into student (id, name) values (112, 'wangwu');
insert into student (id, name) values (113, 'zhaoliu');
insert into student (id, name) values (114, 'tianqi');CREATE TABLE chinese_score(id int,chinese int
);INSERT INTO chinese_score VALUES (114, 78);
INSERT INTO chinese_score VALUES (110, 79);
INSERT INTO chinese_score VALUES (111, 80);
INSERT INTO chinese_score VALUES (112, 77);
INSERT INTO chinese_score VALUES (113, 61);
INSERT INTO chinese_score VALUES (115, 83);
INSERT INTO chinese_score VALUES (116, 90);CREATE TABLE math_score(
id int,
math int);insert into math_score values (110, 78);
insert into math_score values (111, 79);
insert into math_score values (112, 77);
insert into math_score values (113, 61);
insert into math_score values (114, 99);
insert into math_score values (115, 83);
insert into math_score values (116, 90);

2、级联更新：

UPDATE student s, chinese_score sc
SET s.chinese = sc.chinese
WHERE s.id = sc.id;UPDATE student s JOIN math_score sc ON s.id = sc.id
SET s.math = sc.math;

删除表中的数据

删除数据的两种方式：

DELETE FROM 表名 WHERE 字段=值;：删除符合WHERE子句的行
TRUNCATE TABLE 表名：删除表中数据，不能回滚，truncate会重置表的自增值，truncate不会激活与表有关的触发器

修改数据表的结构

增加列：ALTER TABLE <表名> ADD COLUMN <列名><类型>;
修改列：ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列名> <新列的数据类型>;
修改列的数据类型：ALTER TABLE <表名> MODIFY <原列名> <新数据类型>; // 没有COLUMN关键字
删除列：ALTER TABLE <表名> DROP COLUMN <列名>;
添加主键约束：ALTER TABLE <表名> ADD PRIMARY KEY(<列名>);
添加唯一约束：ALTER TABLE <表名> ADD CONSTRAINT <约束名> UNIQUE(<列名>);
添加或删除默认约束：ALTER TABLE <表名> {SET DEFAULT <默认值> | DROP DEFAULT};
添加外键约束：

ALTER TABLE <表名> 
ADD CONSTRAINT  <约束名> FOREIGN KEY <(外键字段)> REFERENCES <表名>(<字段名>)
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT | ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}];

删除外键约束：ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
重命名表：ALTER TABLE <表名> RENAME TO <新表名>;

删除数据表

DROP TABLE [IF EXISTS] <表名>, ... ;

批量向表中添加数据

方式1：从查询结果中创建表：CREATE TABLE 表名 AS 查询语句：会新建一张表，并把查询结果存放到表中

方式2：把查询结果插入到已存在的表中：INSERT INTO 表名 [(字段, ... )] 查询语句：要求表必须提前存在，表中的字段和查询语句的字段必须一一对应。

mysql特有的方式：从外部文件中导入数据：LAOD DATA [LOCAL] INFILE '<路径>' INTO TABLE <表名> [FIELDS TERMINATED BY '字符'] [LINES TERMINATED BY '字符']：

从外部文件中导入数据，导入数据后，即使删除外部文件，表中的数据也不会被删除。
有两个参数需要设置：
- 在MySQL的配置文件my.ini中，在[mysqld]模块下，加入参数secure_file_priv=''，表示可以从任意目录下导入文件，然后重启服务器，因为这个变量是只读变量，所以不可以从命令行修改。
- 在进入MySQL客户端时，加入参数 --infile_path=1，就可以从本地文件中导入数据，否则，在LOAD语句中不可以使用LOCAL关键字。
MySQL对于文件格式有着严格的要求，包括：文件编码、字段数量等，有一点错误，就会导致导入失败。向MySQL中导入数据的时候，首先要对数据进行清洗。MySQL不适合大数据。

MySQL自带的函数

系统函数

返回MySQL的版本：version()
返回当前的数据库名称：database()、schema()
返回当前的用户名和主机：user()

条件判断函数

if函数：IF(expr1,expr2,expr3)：如果expr1不为0或者NULL,则返回expr2的值，否则返回expr3的值
case when语句：
- 格式1：CASE 变量 WHEN 值1 THEN 结果1 [WHEN 值2 THEN 结果2 ...] [ELSE 默认结果 ] END：当compare_value=value时返回result
- 格式2：CASE WHEN 条件表达式1 THEN 结果1 [WHEN 条件表达式2 THEN 结果2 ...] [ELSE 默认结果] END：当condition为TRUE时返回result

字符串函数

字符串长度

返回字符串的字符数：char_length(str)
返回字符串的字节数：length(str)

字符串拼接

把参数拼接在一起并返回：concat(s1, s1, …)
使用指定的分隔符把参数拼接在一起并返回：concat_ws(x, s1, s2, …)

查找子字符串

查找字符串中某个子字符串的位置：instr(字符串, 子字符串)；

mysql> SELECT instr('abc', 'c') po;
+----+
| po |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

截取子字符串

截取字符串左边的n位字符：left(str, n)：
截取字符串右边的n位字符：rigth(str, n)：
从指定的位置开始，截取字符串的n位字符：mid(str, pos, n)

正则表达式函数

regexp_replace(str, reg, sub); ：使用正则表达式去匹配字符串，用第三个参数去替换匹配到的子字符串
regexp_instr(str, reg)：返回匹配正则表达式的子字符串的下标

日期、时间函数

获取当前日期和时间

now()、current_timestamp()、localtime()、localtimestamp()，效果相同，建议只使用now()；
sysdate()：和now()不同之处在于，now() 在执行开始时值就得到了，sysdate() 在函数执行时动态得到值。如 select now(), sleep(3), sysdate();
获取当前日期：curdate()、current_date()
获取当前时间：curtime()、current_time()
获取当前utc时间：utc_date()、utc_time()、utc_timestamp()

提取日期中某个部分

date(date)
time(date_time)
year(date)
month(date)
week(date)
day(date)
hour(time)
minute(time)
second(time)
microsecond(time)
提取日期中的某部分，如年、月：extract(<time_unit> from <date>)

返回月份中的最后一天

last_day()

求某天是星期几

date_format(date, “%w”)，0是星期日

日期和字符串之间的转换函数

把字符串转换为时间：str_to_date(str, format)：format由格式指定符组成：如 %Y、%m、%d，format和str必须完全匹配
把时间转换为字符串：date_format(date, format)，time_format(time, format)，把时间转换为特定格式。

格式字符串

%w，星期，0是星期日
%Y，年；
%m，月；
%d，日期；
%H，小时；
%i，分钟；
%s，秒；
%T，时间

日期的计算

日期的运算：DATE类的值 {+ | -} INTERVAL <数字> {YEAR | MONTH | DAY | WEEK}
日期加时间的运算：DATETIME类型的值 {+ | -} INTERVAL <数字> {YEAR | MONTH | DAY | WEEK | HOUR | MINUTE | SECOND}

时间计算函数

date_add(date, INTERVAL expr unit)：这个方法执行日期的运算
- date实参指定开始的date或datetime的值
- expr 是一个表达式，和date实参相加，它也可以是一个负数
- unit是一个关键字，表示时间单位：hour、minute、second、year、month、day
date_sub(date, INTERVAL expr unit)：同 date_add，只不过本方法对时间执行相减操作
datadiff(expr1, expr2)
timediff(expr1, expr2)

列转行、行转列

列转行和行转列是sql语句的重要功能，在日常开发过程中经常用到的重量级功能，在这里重点学习

列转行

将多列数据转换到一行。通过聚合函数和CASE WHEN语句来实现列转行的功能

案例：这是现在score表中的数据，

mysql> SELECT * FROM score;
+----+------------+-----------------------+--------+-------+
| id | student_id | examination           | course | score |
+----+------------+-----------------------+--------+-------+
|  1 |          1 | 一年级期末考试        | 语文   |  98.5 |
|  2 |          1 | 一年级期末考试        | 数学   |  90.3 |
|  3 |          1 | 一年级期末考试        | 科学   |  85.7 |
|  4 |          2 | 一年级期末考试        | 语文   |  80.4 |
|  5 |          2 | 一年级期末考试        | 数学   |  80.3 |
|  6 |          2 | 一年级期末考试        | 科学   |  76.7 |
|  7 |          3 | 一年级期末考试        | 语文   |  79.5 |
|  8 |          3 | 一年级期末考试        | 数学   |  98.3 |
|  9 |          3 | 一年级期末考试        | 科学   |  67.1 |
| 10 |          5 | 一年级期末考试        | 语文   |  80.5 |
+----+------------+-----------------------+--------+-------+
10 rows in set (0.00 sec)

需求：现在希望把语文、数学、科学的成绩展示在一行

代码实现：

SELECT examination, student_id, max(CASE courseWHEN '语文' THEN scoreELSE NULL END) as 语文, max(CASE courseWHEN '数学' THEN scoreELSE NULL END) as 数学, max(CASE courseWHEN '科学' THEN scoreELSE NULL END) as 科学
FROM score
GROUP BY examination, student_id;

+-----------------------+------------+--------+--------+--------+
| examination           | student_id | 语文   | 数学   | 科学   |
+-----------------------+------------+--------+--------+--------+
| 一年级期末考试        |          1 |   98.5 |   90.3 |   85.7 |
| 一年级期末考试        |          2 |   80.4 |   80.3 |   76.7 |
| 一年级期末考试        |          3 |   79.5 |   98.3 |   67.1 |
| 一年级期末考试        |          5 |   80.5 |   NULL |   NULL |
+-----------------------+------------+--------+--------+--------+
4 rows in set (0.00 sec)

行转列

将一行数据转换为多列。使用UNION和UNION ALL来实现

视图

view，视图是从某个查询结果中生成的一张虚拟表，它的存在是为了更好的管理数据，如果需要设置某些用户只可以读取数据，但是不可以修改数据，可以把这些数据封装到一个视图中。视图不包含数据，每次使用视图时，必须执行视图对应的SELECT语句。

视图的增删改查

创建视图：create view <视图名> as <select 语句>;
查看视图中的数据：SELECT <字段>, ... FROM <视图名>;
修改视图：alter view <视图名> as <select 语句>;
删除视图：drop view <视图名>;
查询当前数据库中的视图：show table status where comment='view';

其它操作

操作用户

MySQL的用户：在安装MySQL时，MySQL会默认创建root用户，使用root用户，可以创建和管理其它用户。

关于用户信息保存在mysql.user表中。

操作用户的语句：例如，创建用户user1，可以在任何主机登录，赋予查看test库下所有表的权限，密码是123456：

创建用户：CREATE USER 'user1'@'%' IDENTIFIED BY '123456';
授权：GRANT ALL PRIVILEGES ON test.* TO 'user1'@'%';
刷新权限：FLUSH PRIVILEGES;
展示用户的权限：SHOW GRANTS for 'user1'@'%' ;
删除某个用户的权限权限：REVOKE ALL PRIVILEDGES ON test.* FROM ‘user1’@‘%’;
删除用户：DROP USER user1;

案例：允许root用户在任何主机登录：

CREATE USER 'root'@'%' IDENTIFIED BY  '123456';
GRANT ALL PRIVILEGES ON *.*  TO 'root'@'%';

FLUSH命令

格式：FLUSH <flush_option> [, … ]。使用FLUSH命令清除一些MySQL使用内部缓存。

为了执行FLUSH，必须有reload权限。

flush_option：

hosts：主要是用来清空主机缓存表。如果某些主机改变IP数字，或如果 mysql 给出得到错误消息 “Host ‘ip’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’”，此时应该清空主机表。当在连接MySQL服务器时，对一台给定的主机有多于最大错误数(max_connect_errors)个错误连续不断地发生，MySQL为了安全的需要将会阻止该主机进一步的连接请求。清空主机表允许主机再尝试连接。
privileges：每当重新赋权后，为了以防万一，让新权限立即生效，一般都执行一次，目地是从数据库授权表中重新装载权限到缓存中。

数据库备份

数据库的备份：mysqldump -uroot -proot 数据库名 > F:\db.sq // 我在尝试的时候发现我没有C盘的访问权限

备份的恢复：在一个数据库下执行命令：source sql文件的绝对路径

使用经验

sql语句

比例类的指标：同比和环比

同比和环比：

同比：是与上一个相同周期为基础相比较，例如，今年第n月与去年第n月的比较，
环比：是与上一个相邻周期为基础相比较，表明统计指标逐期的发展变化

它们的计算公式都是一样的：增长率 =（本期数－上一周期数）／上一周期数 × 100%。

作用：同比和环比是反映增长速度最基础、最核心的数据指标，同比和环比通常是同比变化率和环比变化率的简称，用于表示某一事物在对比期内发展变化的方向和程度

注意事项：比例类的数据，不能直接聚合，只能找到分子和分母，对分子和分母进行聚合，然后再次计算比例。因为比例值取决于分子和分母，两个比例值的分子和分母不同，它们不能直接相加减。

案例1：一道数学题，今年是30个，去年是10个，今年比去年的同比是多少，怎么算？

计算今年比去年的同比增长率的公式如下：(今年的数值 - 去年的数值) / 去年的数值 * 100。百分数，分母是100的数，在这里乘以100，相当于分子乘以100，然后取分子，然后拼接一个百分号。

  (30 - 10) / 10 * 100 
= 20 / 10 * 100 
= 2 * 100 
= 200，结果是200%，200拼接一个百分号即可。

某个字段中的值按照特定的顺序排序

首先要求字段中的值必须是只有几个固定的枚举值，然后把这些固定的值使用case when语句转换为数字，然后对数字进行排序

INSERT … UPDATE 语句

插入数据的使用同时更新数据，一种比较复杂的sql语法。

案例：f1字段是主键，这里是mybatis框架中的sql标签

insert into t1 (f1, f2) values
<foreach collection="list" item="item" separator=",">
(#{item.f1,jdbcType=INTEGER}, #{item.f2,jdbcType=VARCHAR})
</foreach>
ON DUPLICATE KEY
UPDATE
f1 = values(f1),
f2 = values(f2),
</insert>

案例讲解：

ON DUPLICATE key update：在insert数据时使用，使用这种方式可以处理新增数据和原有数据重复的情况。它是根据索引字段是否重复来判断是否执行，如果重复则执行update，否则则执行insert。
索引优先级：主键>唯一索引，当主键重复时则执行update，当主键不重复，唯一索引重复时也执行update，当主键和唯一索引值都不重复才执行insert
VALUES() 函数只在 INSERT … UPDATE 语句中有意义，而在其它情况下只会返回 NULL，它用于访问原来的值。

WITH AS语句

又称公共表表达式（Common Table Expressions，CTE），一种不常用的sql语法，用于子查询的复用。

语法：

WITH t1 AS (-- 查询语句
)
SELECT * FROM t1;

如果同一个sql中有多个相同的子查询，可以把子查询放到WITH AS中，避免同一个子查询执行多次

sql语句执行后显示warning

show warnings，查看告警信息即可，它不影响sql的执行

在一条sql中读取原表的数据并且向原表写入数据

案例：

INSERT INTO your_table (column1, column2, column3)
SELECT column1, column2, CONCAT(column3, '_modified')
FROM your_table

我更多的是想知道，在一条sql中，读取表中的数据，再向原表中写入数据，这种操作是否支持。是支持的。

实现相邻两行数据之间的计算

方案1：两行数据进行join
方案2：lag函数

lag函数

lag函数：用于访问当前行之前的某行，

语法：LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
- scalar_expression：字段名称，要根据指定偏移量返回的值，这是一个返回单个（标量）值的任何类型的表达式。scalar_expression 不能为分析函数。
- offset：当前行（从中获得取值）前的行数。如果未指定，则默认值为 1，可以是负数
- default：默认值，如果返回值为NULL，使用默认值
- OVER为开窗函数，LAG函数必须与开窗函数一起使用。

案例：

WITH T AS (SELECT 1 AS ID, 10 AS NUMUNION ALLSELECT 1, 20UNION ALLSELECT 1, 30UNION ALLSELECT 2, 40UNION ALLSELECT 2, 50UNION ALLSELECT 2, 60)
SELECT ID, NUM, LAG(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs, LAG(NUM, 1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs, LAG(NUM, 2, 0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs
FROM T

执行结果：

id	num	oneargs	towargs	thressargs
1	10	\N	\N	0
1	20	10	10	0
1	30	20	20	10
2	40	\N	\N	0
2	50	40	40	0
2	60	50	50	40

操作JSON数据

判断json数组中是否包含某个值？

JSON_CONTAINS(json_column, '["value2"]')
以下三种语法类似：
- JSON_CONTAINS(property_values, JSON_ARRAY(#{propertyValue}))
- JSON_CONTAINS(property_values, concat('["', #{propertyValue}, '"]'))
- JSON_CONTAINS(property_values, concat("[\"", #{propertyValue}, "\"]"))

判断json对象中是否包含某个值：

JSON_CONTAINS(JSON_EXTRACT(json_column, '$.*'), JSON_ARRAY(#{propertyValue})); ， JSON_EXTRACT是把对象中的所有值提取为一个数组

日期计算

把时间戳转换为时间

mysql中的时间戳只有10位的，如果是13位的，需要进行截取。

使用from_unixtime函数来进行转换：date_format(from_unixtime(时间戳 / 1000), 'yyyy-MM-dd HH:mm:ss') AS event_date

批量更新

根据同一张表中的一部分内容更新另一部分内容

需求：表中有3个字段，id、name、version_id，不同的version_id下，id、name需要是相同的，现在，表中有两个version_id，dev_v1、dev_v2，要求，把dev_v1下的id赋值给dev_v2下的id，如果dev_v1下的name 等于 dev_v2下的name

sql：

UPDATE script v2
JOIN (SELECT id, nameFROM scriptWHERE version_id = 'dev_v1') v1
ON v2.name = v1.name
SET v2.id = v1.id
WHERE v2.version_id = 'dev_v2';

运维

估算数据表的大小

把表中每个字段的数据类型的大小加起来，乘以行数，就是表所占的字节数。

mysql中提供了查询数据库大小的sql，但是这种sql只是估算数据库的大小，有时候会失效。

查看数据库容量大小：

SELECT table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
FROM information_schema.tables
WHERE table_schema = 'learn_db'  -- 库名
GROUP BY table_schema
ORDER BY sum(data_length) desc, sum(index_length) desc;

查看所有数据库各表容量大小：

SELECT table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)'
FROM information_schema.tables
WHERE table_schema = 'learn_db'  -- 库名
ORDER BY data_length DESC, index_length DESC;

Windows上mysql服务器启动错误

Windows上如果mysql服务器启动错误，查看安装目录下后缀为.err的错误日志

Windows上mysql服务出现问题

mysql出现了问题，注册表中的数据全部被删除了，导致启动服务时找不到路径。解决方案：

删除mysql服务
以管理员的身份打开cmd：按win + r，输入cmd，按 ctrl + shift + enter
执行命令：mysqld，启动mysql服务

mysql中关于时区的操作

查看时区：show variables like "%time_zone%";

在配置文件中修改时区：

[mysqld]
default-time-zone = '+8:00'

查看 mysql 状态

status

show variables like ‘%data%’;

show status like ‘%thread%’;

查看自增ID的相关信息

查询自增信息的sql：

-- 查询当前主键的自增值，查询出的结果相当于当前表中下一个ID的值
SELECT auto_increment
FROM information_schema.TABLES
WHERE Table_Schema= 'myp_demo1'
AND table_name= 'user'-- 修改表的自增值
alter table myp_demo1.user auto_increment = 6;

注意事项

这里是一些我使用mysql的过程中，发现自己比较容易忽视的点，做个记录

limit子句偏移量从0开始

limit 偏移量, 数据条数，偏移量从0开始

使用not in运算符时，注意null值的问题

使用not in运算符时，注意null值的问题，null和一切进行运算，结果都是null

除以一个数之前，首先判断它是不是为零

除以一个数之前，首先判断它是不是为零，如果除数为零，结果是null。