mysql基础

oragnelove

已于 2023-01-11 08:44:43 修改

阅读量3.3k

点赞数

文章标签： mysql

于 2022-11-15 16:09:43 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_55387802/article/details/127867812

版权

文章目录

Linux安装mysql数据库

[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@localhost ~]# yum install mariadb mariadb-server
[root@localhost ~]# systemctl enable mariadb.service  --now #开启服务
[root@localhost ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since 日 2022-08-07 16:54:46 CST; 16s ago
  Process: 2475 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 2392 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 2474 (mysqld_safe)
...

mysql基础知识

– 单⾏注释：#注释⽂字
– 多⾏注释：/* 注释⽂字 */

DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段）
DML: 数据操作语言，用来对数据库表中的数据进行增删改
DQL: 数据查询语言，用来查询数据库中表的记录
DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限

数据类型

整数类型：bit、bool、tinyint、smallint、mediumint、int、bigint
浮点数类型：float、double、decimal
字符串类型：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext
⽇期类型：Date、DateTime、TimeStamp、Time、Year

整形

类型名称	取值范围	大小
TINYINT	-128〜127	1个字节
SMALLINT	-32768〜32767	2个宇节
MEDIUMINT	-8388608〜8388607	3个字节
INT (INTEGHR)	-2147483648〜2147483647	4个字节
BIGINT	-9223372036854775808〜9223372036854775807	8个字节

特点：
①都可以设置无符号和有符号，默认有符号，无符号在数据类型后加 unsigned 关键字。

②如果超出了范围，会报out or range异常，插入临界值
③长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

浮点型

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL (M, D)，DEC	压缩的“严格”定点数	M+2 个字节

说明

定点数：decimal(M,D)
浮点数:
float(M,D) 4
double(M,D) 8

特点：
①M代表整数部位+小数部位的个数，D代表小数部位
②如果超出范围，则报out or range异常，并且插入临界值
③M和D都可以省略，但对于定点数，M默认为10，D默认为0

④如果精度要求较高，则优先考虑使用定点数

字符串

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或多个SET成员	1、2、3、4或8个字节，取决于集合成员的数量（最多64个成员）

char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略

日期和时间

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

DCL管理用户

进入mysql数据库，安装完默认无密码

[root@localhost ~]# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

显示数据库列表

MariaDB [(none)]> show databases;

使用数据库

MariaDB [(none)]> use mysql;

显示库中的数据表

MariaDB [mysql]> show tables;

查看用户mysql中的用户

MariaDB [(none)]> use mysql;
MariaDB [mysql]> select user,host from user;

创建用户

MariaDB [mysql]> create user lll;

create user 'test2'@'localhost' identified by '123';
#test2的主机为localhost表⽰本机，此⽤户只能登陆本机的mysql
create user 'test3'@% identified by '123';
#test3可以从任何机器连接到mysql服务器
create user 'test4'@'192.168.11.%' identified by '123';
#test4可以从192.168.11段的机器连接mysql

说明：

主机名默认值为%，表⽰这个⽤户可以从任何主机连接mysql服务器

密码可以省略，表⽰⽆密码登录

修改用户密码,需要通过管理员账户修改

MariaDB [(none)]> set password for 'lll' = password('1');

SET PASSWORD FOR '⽤户名'@'主机' = PASSWORD('密码');

或者通过修改mysql.user表修改密码

use mysql;
update user set authentication_string = password('321') where user = 'test1' and host = '%';
flush privileges;

说明：通过表的⽅式修改之后，需要执⾏flush privileges;才能对⽤户⽣效。authentication_string字段表⽰密码，⽼的⼀些版本中密码字段是password。

删除用户

MariaDB [mysql]> drop user 'test'@'%';

DROP USER '用户名'@'主机名';

也可以通过删除mysql.user表数据的⽅式删除，如下：

delete from user where user='⽤户名' and host='主机';
flush privileges;

注意通过表的⽅式删除的，需要调⽤flush privileges;刷新权限信息（权限启动的时候在内存中保存着，通过表的⽅式修改之后需要刷新⼀下）。

给用户授权

MariaDB [mysql]> grant all on *.* to lll; #赋予lll所有权限
Query OK, 0 rows affected (0.00 sec)

查看用户权限

MariaDB [mysql]> show grants for lll;

说明：

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

查询权限：

SHOW GRANTS FOR 用户名@;

授予权限：

GRANT 权限列表 ON 数据库名.表名 TO 用户名;

撤销权限：

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名;

多个权限用逗号分隔
授权时，数据库名和表名可以用 * 进行通配，代表所有

权限⽣效时间

⽤户及权限信息放在库名为mysql的库中，mysql启动时，这些内容被读进内存并且从此时⽣效，所以如果通过直接操作这些表来修改⽤户及权限信息的，需要重启mysql或者执⾏flush privileges;才可以⽣效。
⽤户登录之后，mysql会和当前⽤户之间创建⼀个连接，此时⽤户相关的权限信息都保存在这个连接中，存放在内存中，此时如果有其他地⽅修改了当前⽤户的权限，这些变更的权限会在下⼀次登录时才会⽣效。

授权原则说明

只授予能满⾜需要的最⼩权限，防⽌⽤户⼲坏事，⽐如⽤户只是需要查询，那就只给select权限就可以了，不要给⽤户赋予update、insert或者delete权限
创建⽤户的时候限制⽤户的登录主机，⼀般是限制成指定IP或者内⽹IP段
初始化数据库的时候删除没有密码的⽤户，安装完数据库的时候会⾃动创建⼀些⽤户，这些⽤户默认没有密码
为每个⽤户设置满⾜密码复杂度的密码
定期清理不需要的⽤户，回收权限或者删除⽤户

总结：

通过命令的⽅式操作⽤户和权限不需要刷新，下次登录⾃动⽣效
通过操作mysql库中表的⽅式修改、⽤户信息，需要调⽤flush privileges;刷新⼀下，下次登录⾃动⽣效
mysql识别⽤户⾝份的⽅式是：⽤户名+主机
⼀些指令中带主机的，主机都可以省略，默认值为%，表⽰所有机器，
mysql中⽤户和权限的信息在库名为mysql的库中

DDL（数据定义语言）

DDL：Data Define Language数据定义语⾔，主要⽤来对数据库、表进⾏⼀些管理操作。
如：建库、删库、建表、修改表、删除表、对列的增删改等等。

库的管理

查询所有数据库：
SHOW DATABASES;
查询当前数据库：
SELECT DATABASE();

创建库

create database [if not exists] 库名;
create database 新库名;
MariaDB [mysql]> create database test;

创建库的完整写法,default charset utf8 默认字符utf8，collate utf8_general_ci大小写不敏感。

MariaDB [(none)]> create database if not exists test default charset utf8 collate utf8_general_ci;

查看数据库的创建语句

MariaDB [(none)]> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

删除库

drop databases [if exists] 库名;
drop database if exists 旧库名;
MariaDB [(none)]>  drop database if exists test;

使用数据库：

USE 数据库名;

表的管理

创建表

CREATE TABLE [if not exists] 表名(
	字段名1 类型  [约束] [COMMENT 字段1注释],
	字段名2 类型  [约束] [COMMENT 字段2注释],
	字段名3 类型  [约束] [COMMENT 字段3注释],
	...
	字段名n 类型  [约束] [COMMENT 字段n注释]
)[ COMMENT 表注释 ];

在同⼀张表中，字段名不能相同
宽度和约束条件为可选参数，字段名和类型是必须的
最后⼀个字段后不能加逗号
类型是⽤来限制字段必须以何种数据类型来存储记录
类型其实也是对字段的约束(约束字段下的记录必须为XX类型)
类型后写的约束条件是在类型之外的额外添加的约束

约束说明

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.1版本后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张图的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

not null：标识该字段不能为空

创建一个表，字段a，类型为int，约束为非空

MariaDB [test]> create table test1(a int not null comment '字段a');

插入空数据会报错

MariaDB [test]> insert into test1 values(null);
ERROR 1048 (23000): Column 'a' cannot be null

正常插入数据

MariaDB [test]> insert into test1 values(1);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select *from test1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

default value：为该字段设置默认值，默认值为value

MariaDB [test]> create table test2 (
    -> a int not null comment '字段a',
    -> b int not null default 0 comment '字段b'
    -> );
Query OK, 0 rows affected (0.01 sec)
#创建表，字段b非空，默认数值为0
MariaDB [test]> insert into test2(a) values(1);
Query OK, 1 row affected (0.00 sec)
#插入数据字段a值为1，此时字段b未给值，默认为0
MariaDB [test]> insert into test2(a,b) values(2,2);
Query OK, 1 row affected (0.00 sec)
#插入数据字段a值为2，字段b值为2
MariaDB [test]> select * from test2;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)

primary key：标识该字段为该表的主键，可以唯.的标识记录，插入重复的会报错

两种写法，如下：

第一种：跟在列后

MariaDB [test]> create table if exist test3(
    -> a int not null comment '字段a' primary key,
    -> b int not null default 2  comment '字段b'
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'exist test3(
a int not null comment '字段a' primary key,
b int not null defaul' at line 1
MariaDB [test]> create table  test3( a int not null comment '字段a' primary key, b int not null default 2  comment '字段b'  );
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into test3(a) values(1);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into test3(a) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' #重复插入报错
MariaDB [test]> select * from test3;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

第二种：在所有列定义之后定义

MariaDB [test]> drop table if exists test3;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> create table  test3( a int not null comment '字段a' , b int not null default 2  comment '字段b',primary key(a));
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into test3(a,b) values(1,4);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into test3(a,b) values(2,4);
Query OK, 1 row affected (0.01 sec)               #字段b非主键，可以重复插入

MariaDB [test]> insert into test3(a,b) values(2,3);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'  #重复插入报错
MariaDB [test]> select * from test3;
+---+---+
| a | b |
+---+---+
| 1 | 4 |
| 2 | 4 |
+---+---+
2 rows in set (0.00 sec)

第二种⽀持多字段作为主键，多个之间⽤逗号隔开，语法：primary key(字段1,字段2,字段n)，

mysql> create table test3(
-> a int not null comment '字段a',
-> b int not null comment '字段b',
-> PRIMARY KEY (a,b)
-> );

foreign key：为表中的字段设置外键

语法：foreign key(当前表的列名) references 引⽤的外键表(外键表中字段名称)

desc查看表的结构

MariaDB [test]> desc test3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | PRI | NULL    |       |
| b     | int(11) | NO   |     | 2       |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

MariaDB [test]> select * from test3;
+---+---+
| a | b |
+---+---+
| 1 | 4 |
| 2 | 4 |
+---+---+
2 rows in set (0.00 sec)
MariaDB [test]> create table test4 (a int not null ,b int not null default 5,foreign key(a) references test3(a) );#test3为外键表
Query OK, 0 rows affected (0.02 sec)
MariaDB [test]> insert into test4(a,b) values(2,3);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into test4(a,test4.b) values(1,4);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from test4;
+---+---+
| a | b |
+---+---+
| 2 | 3 |
| 1 | 4 |
+---+---+
2 rows in set (0.00 sec)
MariaDB [test]> insert into test4(a,test4.b) values(4,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`test4`, CONSTRAINT `test4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test3` (`a`))

说明：

表⽰test4中a字段的值来源于表test3中的字段a。

两张表中需要建⽴外键关系的字段类型需要⼀致
要设置外键的字段不能为主键
被引⽤的字段需要为主键
被插⼊的值在外键表必须存在，如上⾯向test4中插⼊a为4的时候报错了，原因：4的值在test3表中不存在

unique key(uq)：标识该字段的值是唯⼀的

⽀持⼀个到多个字段，插⼊重复的值会报违反唯⼀约束，会插⼊失败。

⽅式1：跟在字段后

MariaDB [test]> create table test5(a int not null comment '字段a' unique key);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into test5(a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into test5(a) VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'
MariaDB [test]>

⽅式2：所有列定义之后定

MariaDB [test]> drop table if exists test5;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> create table test5(a int not null comment '字段a' ,unique key(a));
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into test5(a) VALUES (1);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into test5(a) VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'

⽅式2⽀持多字段，多个之间⽤逗号隔开，语法：unique key(字段1,字段2,字段n)

mysql> create table test5(
-> a int not null comment '字段a',
-> b int not null comment '字段b',
-> unique key(a,b)
-> );

auto_increment：标识该字段的值⾃动增长（整数类型，⽽且为主键）

MariaDB [test]> create table test6(
    -> a int not null auto_increment primary key comment '字段a',
    -> b int not null comment '字段b'
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into test6(b) VALUES (10);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into test6(b) VALUES (11);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into test6(b) VALUES (12);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from test6;
+---+----+
| a | b  |
+---+----+
| 1 | 10 |
| 2 | 11 |
| 3 | 12 |
+---+----+
3 rows in set (0.00 sec)
#未设置a的值，但是a的值会自动增长，字段a为⾃动增长，默认值从1开始，每次+1

MariaDB [test]> delete from test6;
Query OK, 3 rows affected (0.00 sec)

MariaDB [test]> insert into test6(b) VALUES (20);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from test6;
+---+----+
| a | b  |
+---+----+
| 4 | 20 |
+---+----+
1 row in set (0.00 sec)

⾃增长列当前值存储在内存中，清空数据库的table表，⾃增列的值不会从初始值（1）开始，清空数据库的table表并重启数据库。

[root@localhost ~]# systemctl restart mariadb.service

show create table 表名，查看表的创建语法

MariaDB [test]> show create table ceshi2 \G
*************************** 1. row ***************************
       Table: ceshi2
Create Table: CREATE TABLE `ceshi2` (
  `a` int(11) NOT NULL COMMENT '字段a',
  `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试2'
1 row in set (0.01 sec)

删除表

drop table [if exists] 表名;

MariaDB [test]> drop table if exists test1;
Query OK, 0 rows affected (0.00 sec)

修改表名

alter table 表名 rename [to] 新表名;

MariaDB [test]> alter table test2 rename to ceshi2;
Query OK, 0 rows affected (0.01 sec)

表设置备注

alter table 表名 comment ‘备注信息’;

MariaDB [test]> alter table ceshi2 comment '测试2';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [test]> show create table ceshi2 \G
*************************** 1. row ***************************
       Table: ceshi2
Create Table: CREATE TABLE `ceshi2` (
  `a` int(11) NOT NULL COMMENT '字段a',
  `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试2'
1 row in set (0.01 sec)

只复制表的结构

create table 表名 like 被复制的表名;


MariaDB [test]> create table ceshi3 like ceshi2;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from ceshi3;
Empty set (0.00 sec)

MariaDB [test]> show create table ceshi3 \G
*************************** 1. row ***************************
       Table: ceshi3
Create Table: CREATE TABLE `ceshi3` (
  `a` int(11) NOT NULL COMMENT '字段a',
  `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试2'
1 row in set (0.00 sec)

复制表结构+数据

create table 表名 [as] select 字段,... from 被复制的表 [where 条件];

MariaDB [test]> create table ceshi4 as select * from ceshi2;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from ceshi4;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)
MariaDB [test]> show create table ceshi4 \G
*************************** 1. row ***************************
       Table: ceshi4
Create Table: CREATE TABLE `ceshi4` (
  `a` int(11) NOT NULL COMMENT '字段a',
  `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表中列（字段）的管理

添加列（添加字段）

在末尾添加字段

alter table 表名 add column 列名 类型 [列约束];

在开头添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

在中间添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;


MariaDB [test]> select * from ceshi4;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)

MariaDB [test]> alter table ceshi4 add column c int not null default 100 comment '字段c' ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> insert into ceshi4(a,b,c) values(7,8,9);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from ceshi4;
+---+---+-----+
| a | b | c   |
+---+---+-----+
| 1 | 0 | 100 |
| 2 | 2 | 100 |
| 7 | 8 |   9 |
+---+---+-----+
3 rows in set (0.00 sec)

修改列（修改字段）

alter table 表名 modify column 列名 新类型 [约束];

alter table 表名 change column 列名 新列名 新类型 [约束];

modify不能修改列名，change可以修改列名

MariaDB [test]> show create table ceshi4 \G
*************************** 1. row ***************************
       Table: ceshi4
Create Table: CREATE TABLE `ceshi4` (
  `a` int(11) NOT NULL COMMENT '字段a',
  `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
  `c` int(11) NOT NULL DEFAULT '100' COMMENT '字段c'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [test]> alter table ceshi4 change column c d varchar(20) not null default '你好' comment '字段d'  ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> show create table ceshi4 \G
*************************** 1. row ***************************
       Table: ceshi4
Create Table: CREATE TABLE `ceshi4` (
  `a` int(11) NOT NULL COMMENT '字段a',
  `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
  `d` varchar(20) NOT NULL DEFAULT '你好' COMMENT '字段d'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除列（删除字段）

alter table 表名 drop column 列名;

MariaDB [test]> select * from ceshi4;
+---+---+-----+
| a | b | d   |
+---+---+-----+
| 1 | 0 | 100 |
| 2 | 2 | 100 |
| 7 | 8 | 9   |
+---+---+-----+
3 rows in set (0.00 sec)

MariaDB [test]> show create table ceshi4 \G
*************************** 1. row ***************************
       Table: ceshi4
Create Table: CREATE TABLE `ceshi4` (
  `a` int(11) NOT NULL COMMENT '字段a',
  `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
  `d` varchar(20) NOT NULL DEFAULT '你好' COMMENT '字段d'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [test]> alter table ceshi4 drop column d;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> show create table ceshi4 \G
*************************** 1. row ***************************
       Table: ceshi4
Create Table: CREATE TABLE `ceshi4` (
  `a` int(11) NOT NULL COMMENT '字段a',
  `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [test]> select * from ceshi4;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
| 2 | 2 |
| 7 | 8 |
+---+---+
3 rows in set (0.00 sec)

DML（数据操作语言）

插⼊操作（插入数据）

指定字段：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

全部字段：
INSERT INTO 表名 VALUES (值1, 值2, ...);

值和字段需要⼀⼀对应
如果是字符型或⽇期类型，值需要⽤单引号引起来；如果是数值类型，不需要⽤单引号
字段和值的个数必须⼀致，位置对应
字段如果不能为空，则必须插⼊值
可以为空的字段可以不⽤插⼊值，但需要注意：字段和值都不写；或字段写上，值⽤null代替
表名后⾯的字段可以省略不写，此时表⽰所有字段，顺序和表中字段顺序⼀致。

insert into 表名 set 字段 = 值,字段 = 值;#不常见

批量添加数据：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

– 删除test1
drop table if exists test1;
– 创建test1
create table test1(a int,b int);
– 删除test2
drop table if exists test2;
– 创建test2
create table test2(c1 int,c2 int,c3 int);
– 向test2中插⼊数据
insert into test2 values (100,101,102),(200,201,202),(300,301,302),
(400,401,402);
– 向test1中插⼊数据
insert into test1 (a,b) select 1,1 union all select 2,2 union all
select 2,2;
– 向test1插⼊数据，数据来源于test2表
insert into test1 (a,b) select c2,c3 from test2 where c1>=200;

select返回的结果和插⼊数据的字段数量、顺序、类型需要⼀致。

数据更新

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

update 表名 [[as] 别名] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件];

有些表名可能名称⽐较长，为了⽅便操作，可以给这个表名起个简单的别名，更⽅便操作⼀些。如果⽆别名的时候，表名就是别名。

MariaDB [test]> select * from ceshi4;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
| 2 | 2 |
| 7 | 8 |
+---+---+
3 rows in set (0.01 sec)
MariaDB [test]> update ceshi4 set a=10,b=11 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from ceshi4;
+----+----+
| a  | b  |
+----+----+
| 10 | 11 |
|  2 |  2 |
|  7 |  8 |
+----+----+
3 rows in set (0.00 sec)
MariaDB [test]> update ceshi4 set a=111 ;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [test]> select * from ceshi4;
+-----+----+
| a   | b  |
+-----+----+
| 111 | 11 |
| 111 |  2 |
| 111 |  8 |
+-----+----+
3 rows in set (0.00 sec)

多表更新

update 表1 [[as] 别名1],表名2 [[as] 别名2] set [别名.]字段 = 值,[别名.]字段 = 值[where条件];

删除数据：

DELETE FROM 表名 [ WHERE 条件 ];

MariaDB [test]> select * from ceshi4;
+----+----+
| a  | b  |
+----+----+
| 23 | 25 |
| 23 | 25 |
| 23 | 25 |
+----+----+
3 rows in set (0.00 sec)
MariaDB [test]> delete from ceshi4 where ceshi4.a=23;
Query OK, 3 rows affected (0.00 sec)

MariaDB [test]> select * from ceshi4;
Empty set (0.00 sec)

drop，truncate，delete区别

drop (删除表)：删除内容和定义，释放空间，简单来说就是把整个表去掉，以后要新增数据是不可能的，除⾮新增⼀个表。

drop语句将删除表的结构被依赖的约束（constrain），触发器（trigger）索（index），依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

如果要删除表定义及其数据，请使⽤ drop table 语句
truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)，与drop不同的是，只是清空表数据⽽已。truncate table;

注意：truncate不能删除具体⾏数据，要删就要把整个表清空了。
delete (删除表中的数据)：delete 语句⽤于删除表中的⾏。delete语句执⾏删除的过程是每次从表中删除⼀⾏，并且同时将该⾏的删除操作作为事务记录在⽇志中保存，以便进⾏进⾏回滚操作。

DQL（数据查询语言）

SELECT 字段列表 FROM 表名字段 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后的条件列表 ORDER BY 排序字段列表 LIMIT 分页参数

select查下基础篇

基本语法

select 查询的列 from 表名;

select语句中不区分⼤⼩写，SELECT和select、FROM和from效果⼀样。

查询常量

select 常量值1,常量值2,常量值3;

MariaDB [test]> select 1,2,3,'nihao','nihao';
+---+---+---+-------+-------+
| 1 | 2 | 3 | nihao | nihao |
+---+---+---+-------+-------+
| 1 | 2 | 3 | nihao | nihao |
+---+---+---+-------+-------+
1 row in set (0.00 sec)

查询表达式

select 表达式;

MariaDB [test]> select 1*2,3/6;
+-----+--------+
| 1*2 | 3/6    |
+-----+--------+
|   2 | 0.5000 |
+-----+--------+
1 row in set (0.00 sec)

查询函数

select 函数;

MariaDB [test]> SELECT CONCAT('Hello', 'World');
+--------------------------+
| CONCAT('Hello', 'World') |
+--------------------------+
| HelloWorld               |
+--------------------------+
1 row in set (0.00 sec)
#CONCAT(s1, s2, ..., sn)字符串拼接，将s1, s2, ..., sn拼接成一个字符串

查询指定的字段

select 字段1,字段2,字段3 from 表名;

列别名

select 列 [as] 别名 from 表;

MariaDB [test]> select * from test4;
+---+---+
| a | b |
+---+---+
| 2 | 3 |
| 1 | 4 |
+---+---+
2 rows in set (0.00 sec)
MariaDB [test]> select a as "a的值",b as "b的值" from test4;
+---------+---------+
| a的值   | b的值   |
+---------+---------+
|       2 |       3 |
|       1 |       4 |
+---------+---------+
2 rows in set (0.00 sec)

select a "列1",b "列2" from test1;
select a '列1',b '列2' from test1;;
select a 列1,b 列2 from test1;#别名中有特殊符号的，⽐如空格，此时别名必须⽤引号引来。

表别名

select 别名.字段,别名.* from 表名 [as] 别名;

MariaDB [test]> select  t.a as '列1',t.b as '列2' from test4 as t;
+------+------+
| 列1  | 列2  |
+------+------+
|    2 |    3 |
|    1 |    4 |
+------+------+
2 rows in set (0.01 sec)

建议别名前⾯跟上as关键字
查询数据的时候，避免使⽤select *，建议需要什么字段写什么字段

select条件查询

select 列名 from 表名 where 列 运算符 值;
SELECT 字段列表 FROM 表名 WHERE 条件列表;

注意关键字where，where后⾯跟上⼀个或者多个条件，条件是对前⾯数据的过滤，只有满⾜where后⾯条件的数据才会被返回。

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围内（含最小、最大值）
IN(…)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是NULL

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或 \|\|	或者（多个条件任意一个成立）
NOT 或 !	非，不是

MariaDB [TEST]> select * from J_STUDENT;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
|   4 | 莉莉   | 1983-03-23 00:00:00 | 女   |
|   5 | 王弼   | 1982-06-21 00:00:00 | 男   |
|   6 | 王丽   | 1984-10-10 00:00:00 | 女   |
|   7 | 刘香   | 1980-12-22 00:00:00 | 女   |
|   8 | 蔡蔡   | 2000-02-01 13:23:34 | 男   |
+-----+--------+---------------------+------+
8 rows in set (0.00 sec)

MariaDB [TEST]> SELECT * FROM J_STUDENT WHERE SNAME = '蔡蔡';
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   8 | 蔡蔡   | 2000-02-01 13:23:34 | 男   |
+-----+--------+---------------------+------+
1 row in set (0.01 sec)

MariaDB [TEST]> SELECT sage FROM J_STUDENT WHERE SNAME = '蔡蔡';
+---------------------+
| sage                |
+---------------------+
| 2000-02-01 13:23:34 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [TEST]> SELECT * FROM J_STUDENT WHERE ssex = '男';
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
|   5 | 王弼   | 1982-06-21 00:00:00 | 男   |
|   8 | 蔡蔡   | 2000-02-01 13:23:34 | 男   |
+-----+--------+---------------------+------+
5 rows in set (0.00 sec)

MariaDB [TEST]> SELECT * FROM J_STUDENT WHERE sno <= 5;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
|   4 | 莉莉   | 1983-03-23 00:00:00 | 女   |
|   5 | 王弼   | 1982-06-21 00:00:00 | 男   |
+-----+--------+---------------------+------+
5 rows in set (0.00 sec)
MariaDB [TEST]> SELECT * FROM J_STUDENT WHERE sno <= 5 AND ssex = '女';
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   4 | 莉莉   | 1983-03-23 00:00:00 | 女   |
+-----+--------+---------------------+------+
1 row in set (0.00 sec)
MariaDB [TEST]> SELECT * FROM J_STUDENT WHERE sno <= 5 AND ssex != '男';
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   4 | 莉莉   | 1983-03-23 00:00:00 | 女   |
+-----+--------+---------------------+------+
1 row in set (0.00 sec)

MariaDB [TEST]> SELECT * FROM J_STUDENT WHERE sname = '张三' or sname = '李四';
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
+-----+--------+---------------------+------+
2 rows in set (0.00 sec)

like 模糊查询

select 列名	from 表名	where 列	like pattern;

说明：

pattern中可以包含通配符，有以下通配符：
%：表⽰匹配任意⼀个或多个字符
_：表⽰匹配任意⼀个字符。

MariaDB [TEST]> select * from J_STUDENT where sname like '张%';
+-----+-----------+---------------------+------+
| sno | sname     | sage                | ssex |
+-----+-----------+---------------------+------+
|   1 | 张三      | 1980-01-23 00:00:00 | 男   |
|   3 | 张飒      | 1981-09-09 00:00:00 | 男   |
|   9 | 张三丰    | 1799-12-01 13:23:34 | 男   |
+-----+-----------+---------------------+------+
3 rows in set (0.00 sec)

MariaDB [TEST]> select * from J_STUDENT where sname like '张_';
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
+-----+--------+---------------------+------+
2 rows in set (0.00 sec)

between and (区间查询)

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围，这些值可以是数值、文本或者⽇期，属于⼀个闭区间查询。

selec 列名	from 表名	where 列名	between 值1 and 值2;

返回对应的列的值在[值1,值2]区间中的记录
使⽤between and可以提⾼语句的简洁度
两个临界值不要调换位置，只能是⼤于等于左边的值，并且⼩于等于右边的值。

MariaDB [TEST]> select * from J_STUDENT where sno between 1 and 3;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
+-----+--------+---------------------+------+
3 rows in set (0.00 sec)
MariaDB [TEST]> select * from J_STUDENT where sno >=1 and sno <=3;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
+-----+--------+---------------------+------+
3 rows in set (0.00 sec)

in查询

IN 操作符允许我们在 WHERE ⼦句中规定多个值。

select 列名 from 表名 where 字段 in (值1,值2,值3,值4);

in 后⾯括号中可以包含多个值，对应记录的字段满⾜in中任意⼀个都会被返回
in列表的值类型必须⼀致或兼容
in列表中不⽀持通配符。

MariaDB [TEST]> select * from J_STUDENT where sno = 1 or sno = 3 or sno =7 or sno =9;
+-----+-----------+---------------------+------+
| sno | sname     | sage                | ssex |
+-----+-----------+---------------------+------+
|   1 | 张三      | 1980-01-23 00:00:00 | 男   |
|   3 | 张飒      | 1981-09-09 00:00:00 | 男   |
|   7 | 刘香      | 1980-12-22 00:00:00 | 女   |
|   9 | 张三丰    | 1799-12-01 13:23:34 | 男   |
+-----+-----------+---------------------+------+
4 rows in set (0.00 sec)

MariaDB [TEST]> select * from J_STUDENT where sno in(1,3,7,9 );
+-----+-----------+---------------------+------+
| sno | sname     | sage                | ssex |
+-----+-----------+---------------------+------+
|   1 | 张三      | 1980-01-23 00:00:00 | 男   |
|   3 | 张飒      | 1981-09-09 00:00:00 | 男   |
|   7 | 刘香      | 1980-12-22 00:00:00 | 女   |
|   9 | 张三丰    | 1799-12-01 13:23:34 | 男   |
+-----+-----------+---------------------+------+
4 rows in set (0.00 sec)

比用or更加的简洁。

not in 查询

not in 与in刚好相反，in是列表中被匹配的都会被返回，NOT IN是和列表中都不匹配的会被返回。

MariaDB [TEST]> select * from J_STUDENT where sno not in(1,3,7,9 );
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
|   4 | 莉莉   | 1983-03-23 00:00:00 | 女   |
|   5 | 王弼   | 1982-06-21 00:00:00 | 男   |
|   6 | 王丽   | 1984-10-10 00:00:00 | 女   |
|   8 | 蔡蔡   | 2000-02-01 13:23:34 | 男   |
+-----+--------+---------------------+------+
5 rows in set (0.00 sec)

null值查询

当IN和NULL⽐较时，⽆法查询出为NULL的记录。

MariaDB [TEST]> select * from test1;
+------+------+
| a    | b    |
+------+------+
|   22 |   23 |
| NULL |   34 |
|   34 | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.00 sec)

MariaDB [TEST]> select * from test1 where a in (null);
Empty set (0.00 sec)

MariaDB [TEST]> select * from test1 where a in (null,22);
+------+------+
| a    | b    |
+------+------+
|   22 |   23 |
+------+------+
1 row in set (0.00 sec)

MariaDB [TEST]> select * from test1 where a in (null,34);
+------+------+
| a    | b    |
+------+------+
|   34 | NULL |
+------+------+
1 row in set (0.00 sec)

当NOT IN 后⾯有NULL值时，不论什么情况下，整个sql的查询结果都为空。

MariaDB [TEST]> select * from test1 where a not in (null);
Empty set (0.00 sec)

MariaDB [TEST]> select * from test1 where a not in (22);
+------+------+
| a    | b    |
+------+------+
|   34 | NULL |
+------+------+
1 row in set (0.00 sec)

MariaDB [TEST]> select * from test1 where a not in (22,null);
Empty set (0.00 sec)

MariaDB [TEST]>

查询运算符、like、between and、in、not in对NULL值查询不起效。
判断NULL只能⽤IS NULL、IS NOT NULL

MariaDB [TEST]> select * from test1 where a = null;
Empty set (0.00 sec)

MariaDB [TEST]> select * from test1 where a is null;
+------+------+
| a    | b    |
+------+------+
| NULL |   34 |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)

MariaDB [TEST]> select * from test1 where a is not  null;
+------+------+
| a    | b    |
+------+------+
|   22 |   23 |
|   34 | NULL |
+------+------+
2 rows in set (0.00 sec)

<=>（安全等于）

既可以判断NULL值，又可以判断普通的数值，可读性较低，⽤得较少

MariaDB [TEST]> select * from test1 where a <=> null;
+------+------+
| a    | b    |
+------+------+
| NULL |   34 |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)

MariaDB [TEST]> select * from test1 where a <=> 22;
+------+------+
| a    | b    |
+------+------+
|   22 |   23 |
+------+------+
1 row in set (0.00 sec)

条件查询一个问题

select * from test1;
select * from test1 where a like '%';

两条语句输出结果一致吗？看情况。

条件查询字段中包含null值，输出不一致

MariaDB [TEST]> select * from test1;
+------+------+
| a    | b    |
+------+------+
|   22 |   23 |
| NULL |   34 |
|   34 | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.00 sec)
MariaDB [TEST]> select * from test1 where a like '%';
+------+------+
| a    | b    |
+------+------+
|   22 |   23 |
|   34 | NULL |
+------+------+
2 rows in set (0.00 sec)

条件查询字段中不包含null值，输出结果一致

MariaDB [TEST]> select * from J_STUDENT;
+-----+-----------+---------------------+------+
| sno | sname     | sage                | ssex |
+-----+-----------+---------------------+------+
|   1 | 张三      | 1980-01-23 00:00:00 | 男   |
|   2 | 李四      | 1982-12-12 00:00:00 | 男   |
|   3 | 张飒      | 1981-09-09 00:00:00 | 男   |
|   4 | 莉莉      | 1983-03-23 00:00:00 | 女   |
|   5 | 王弼      | 1982-06-21 00:00:00 | 男   |
|   6 | 王丽      | 1984-10-10 00:00:00 | 女   |
|   7 | 刘香      | 1980-12-22 00:00:00 | 女   |
|   8 | 蔡蔡      | 2000-02-01 13:23:34 | 男   |
|   9 | 张三丰    | 1799-12-01 13:23:34 | 男   |
+-----+-----------+---------------------+------+
9 rows in set (0.00 sec)

MariaDB [TEST]> select * from J_STUDENT where sno like '%' ;
+-----+-----------+---------------------+------+
| sno | sname     | sage                | ssex |
+-----+-----------+---------------------+------+
|   1 | 张三      | 1980-01-23 00:00:00 | 男   |
|   2 | 李四      | 1982-12-12 00:00:00 | 男   |
|   3 | 张飒      | 1981-09-09 00:00:00 | 男   |
|   4 | 莉莉      | 1983-03-23 00:00:00 | 女   |
|   5 | 王弼      | 1982-06-21 00:00:00 | 男   |
|   6 | 王丽      | 1984-10-10 00:00:00 | 女   |
|   7 | 刘香      | 1980-12-22 00:00:00 | 女   |
|   8 | 蔡蔡      | 2000-02-01 13:23:34 | 男   |
|   9 | 张三丰    | 1799-12-01 13:23:34 | 男   |
+-----+-----------+---------------------+------+
9 rows in set (0.00 sec)

排序与分页

排序查询（order by）

select 字段名	from 表名	order by 字段1 [asc|desc],字段2 [asc|desc];

需要排序的字段跟在order by之后；

asc|desc表⽰排序的规则，asc：升序，desc：降序，默认为asc；

⽀持多个字段进⾏排序，多字段排序之间⽤逗号隔开。如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

MariaDB [TEST]> select * from J_STUDENT order by sage  asc;
+-----+-----------+---------------------+------+
| sno | sname     | sage                | ssex |
+-----+-----------+---------------------+------+
|   9 | 张三丰    | 1799-12-01 13:23:34 | 男   |
|   1 | 张三      | 1980-01-23 00:00:00 | 男   |
|   7 | 刘香      | 1980-12-22 00:00:00 | 女   |
|   3 | 张飒      | 1981-09-09 00:00:00 | 男   |
|   5 | 王弼      | 1982-06-21 00:00:00 | 男   |
|   2 | 李四      | 1982-12-12 00:00:00 | 男   |
|   4 | 莉莉      | 1983-03-23 00:00:00 | 女   |
|   6 | 王丽      | 1984-10-10 00:00:00 | 女   |
|   8 | 蔡蔡      | 2000-02-01 13:23:34 | 男   |
+-----+-----------+---------------------+------+
9 rows in set (0.00 sec)

MariaDB [TEST]> select sname 姓名,sage 出生日期,ssex 性别 from J_STUDENT order by sage  asc;
+-----------+---------------------+--------+
| 姓名      | 出生日期            | 性别   |
+-----------+---------------------+--------+
| 张三丰    | 1799-12-01 13:23:34 | 男     |
| 张三      | 1980-01-23 00:00:00 | 男     |
| 刘香      | 1980-12-22 00:00:00 | 女     |
| 张飒      | 1981-09-09 00:00:00 | 男     |
| 王弼      | 1982-06-21 00:00:00 | 男     |
| 李四      | 1982-12-12 00:00:00 | 男     |
| 莉莉      | 1983-03-23 00:00:00 | 女     |
| 王丽      | 1984-10-10 00:00:00 | 女     |
| 蔡蔡      | 2000-02-01 13:23:34 | 男     |
+-----------+---------------------+--------+
9 rows in set (0.00 sec)

如果第一个字段值相同时，我们可以根据第二个字段进行排序

MariaDB [TEST]> select sno id,sname 姓名,sage 出生日期,ssex 性别 from J_STUDENT order by sno desc,sage asc;+----+-----------+---------------------+--------+
| id | 姓名      | 出生日期            | 性别   |
+----+-----------+---------------------+--------+
|  9 | 张三丰    | 1799-12-01 13:23:34 | 男     |
|  8 | 蔡蔡      | 2000-02-01 13:23:34 | 男     |
|  7 | 刘香      | 1980-12-22 00:00:00 | 女     |
|  6 | 王丽      | 1984-10-10 00:00:00 | 女     |
|  5 | 王弼      | 1982-06-21 00:00:00 | 男     |
|  4 | 莉莉      | 1983-03-23 00:00:00 | 女     |
|  3 | 张飒      | 1981-09-09 00:00:00 | 男     |
|  2 | 李四      | 1982-12-12 00:00:00 | 男     |
|  1 | 张三      | 1980-01-23 00:00:00 | 男     |
+----+-----------+---------------------+--------+

where之后进行排序

MariaDB [TEST]> select sno id,sname 姓名,sage 出生日期,ssex 性别 from J_STUDENT where sno < 5 order by sno desc,sage asc;
+----+--------+---------------------+--------+
| id | 姓名   | 出生日期            | 性别   |
+----+--------+---------------------+--------+
|  4 | 莉莉   | 1983-03-23 00:00:00 | 女     |
|  3 | 张飒   | 1981-09-09 00:00:00 | 男     |
|  2 | 李四   | 1982-12-12 00:00:00 | 男     |
|  1 | 张三   | 1980-01-23 00:00:00 | 男     |
+----+--------+----------

limit

limit⽤来限制select查询返回的⾏数，常⽤于分页等操作。

select 列 from 表	limit [offset,] count;

offset：表⽰偏移量，通俗点讲就是跳过多少⾏，offset可以省略，默认为0，表⽰跳过0⾏；范围：[0,+∞)。
count：跳过offset⾏之后开始取数据，取count⾏记录；范围：[0,+∞)。
limit中offset和count的值不能⽤表达式。

获取前n行的记录

select 列	from 表	limit 0,n;	
select 列	from 表	limit n;

MariaDB [TEST]> select * from J_STUDENT limit 3;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
+-----+--------+---------------------+------+
3 rows in set (0.00 sec)

获取出生日期最小的一条记录

MariaDB [TEST]> select * from J_STUDENT order by sage desc limit 1;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   8 | 蔡蔡   | 2000-02-01 13:23:34 | 男   |
+-----+--------+---------------------+------+
1 row in set (0.00 sec)

获取排名第n到m的记录

select 列	from 表	limit n-1,m-n+1;

#获取3到4的记录
MariaDB [TEST]> select * from J_STUDENT limit 2,2;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
|   4 | 莉莉   | 1983-03-23 00:00:00 | 女   |
+-----+--------+---------------------+------+
2 rows in set (0.00 sec)

分页查询

分页我们经常使⽤，分页⼀般有2个参数： page：表⽰第⼏页，从1开始，范围[1,+∞) pageSize：每页显⽰多少条记录，范围[1,+∞)

使⽤limit实现分页，语法如下：

select 列 from 表名 limit (page - 1) * pageSize,pageSize;

两条记录为一页

MariaDB [TEST]> select * from J_STUDENT limit 0,2;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   1 | 张三   | 1980-01-23 00:00:00 | 男   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
+-----+--------+---------------------+------+
2 rows in set (0.00 sec)

MariaDB [TEST]> select * from J_STUDENT limit 2,2;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   3 | 张飒   | 1981-09-09 00:00:00 | 男   |
|   4 | 莉莉   | 1983-03-23 00:00:00 | 女   |
+-----+--------+---------------------+------+
2 rows in set (0.00 sec)

MariaDB [TEST]> select * from J_STUDENT limit 4,2;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   5 | 王弼   | 1982-06-21 00:00:00 | 男   |
|   6 | 王丽   | 1984-10-10 00:00:00 | 女   |
+-----+--------+---------------------+------+
2 rows in set (0.00 sec)

MariaDB [TEST]> select * from J_STUDENT limit 6,2;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   7 | 刘香   | 1980-12-22 00:00:00 | 女   |
|   8 | 蔡蔡   | 2000-02-01 13:23:34 | 男   |
+-----+--------+---------------------+------+
2 rows in set (0.00 sec)

MariaDB [TEST]> select * from J_STUDENT limit 8,2;
+-----+-----------+---------------------+------+
| sno | sname     | sage                | ssex |
+-----+-----------+---------------------+------+
|   9 | 张三丰    | 1799-12-01 13:23:34 | 男   |
+-----+-----------+---------------------+------+
1 row in set (0.00 sec)

也可以中间添加where条件，或者order by语句。

MariaDB [TEST]> select * from J_STUDENT order by sage desc limit 0,5;
+-----+--------+---------------------+------+
| sno | sname  | sage                | ssex |
+-----+--------+---------------------+------+
|   8 | 蔡蔡   | 2000-02-01 13:23:34 | 男   |
|   6 | 王丽   | 1984-10-10 00:00:00 | 女   |
|   4 | 莉莉   | 1983-03-23 00:00:00 | 女   |
|   2 | 李四   | 1982-12-12 00:00:00 | 男   |
|   5 | 王弼   | 1982-06-21 00:00:00 | 男   |
+-----+--------+---------------------+------+
5 rows in set (0.00 sec)

分页排序时，排序不要有⼆义性，⼆义性情况下可能会导致分页结果乱序
order by 语句排序中单字段存在相同的值时，需要再指定⼀个排序规则，通过多种排序规则才能保证不存在⼆义性

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];

SELECT column, group_function,… FROM table [WHERE condition] GROUP BY group_by_expression [HAVING group_condition];

group_function：聚合函数。

max 查询指定列的最⼤值

min 查询指定列的最⼩值

count 统计查询结果的⾏数

sum 求和，返回指定列的总和

avg 求平均值，返回指定列数据的平均值

groupbyexpression：分组表达式，多个之间⽤逗号隔开。

group_condition：分组之后对数据进⾏过滤。

分组中，select后⾯只能有两种类型的列： 1. 出现在group by后的列 2. 或者使⽤聚合函数的列

MariaDB [TEST]> select * from t_order;
+----+---------+-----------+-------+----------+
| id | user_id | user_name | price | the_year |
+----+---------+-----------+-------+----------+
|  1 |    1001 | 黎明      | 11.11 |     2017 |
|  2 |    1001 | 黎明      | 22.22 |     2018 |
|  3 |    1001 | 黎明      | 88.88 |     2018 |
|  4 |    1002 | 刘德华    | 33.33 |     2018 |
|  5 |    1002 | 刘德华    | 12.22 |     2018 |
|  6 |    1002 | 刘德华    | 16.66 |     2018 |
|  7 |    1002 | 刘德华    | 44.44 |     2019 |
|  8 |    1003 | 张学友    | 55.55 |     2018 |
|  9 |    1003 | 张学友    | 66.66 |     2019 |
+----+---------+-----------+-------+----------+
9 rows in set (0.00 sec)
#以user name分组，统计每一条用户有几条记录
MariaDB [TEST]> select user_name 姓名,count(*) 记录 from t_order group by user_name;
+-----------+--------+
| 姓名      | 记录   |
+-----------+--------+
| 刘德华    |      4 |
| 张学友    |      2 |
| 黎明      |      3 |
+-----------+--------+
3 rows in set (0.00 sec)
#以user name分组，统计每组price的平均值
MariaDB [TEST]> select user_name 姓名,avg(price) from t_order group by user_name;
+-----------+------------+
| 姓名      | avg(price) |
+-----------+------------+
| 刘德华    |  26.662500 |
| 张学友    |  61.105000 |
| 黎明      |  40.736667 |
+-----------+------------+
3 rows in set (0.00 sec)

分组前对数据进⾏筛选，使⽤where关键字

查询2018年，各个用户的记录

MariaDB [TEST]> select user_name 姓名,count(*) 记录 from t_order t  where t.the_year=2018 group by user_name;
+-----------+--------+
| 姓名      | 记录   |
+-----------+--------+
| 刘德华    |      3 |
| 张学友    |      1 |
| 黎明      |      2 |
+-----------+--------+
3 rows in set (0.00 sec)

分组后对数据筛选，使⽤having关键字

查询2018年记录⼤于1的⽤户

MariaDB [TEST]> select user_name 姓名,count(*) 记录 from t_order t  where t.the_year=2018 group by user_name having 记录 >=2;
+-----------+--------+
| 姓名      | 记录   |
+-----------+--------+
| 刘德华    |      3 |
| 黎明      |      2 |
+-----------+--------+
2 rows in set (0.00 sec)

where和having的区别

执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

可以把having理解为两级查询，即含having的查询操作先获得不含having⼦句时的sql查询结果表，然后在z这个结果表上使⽤having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后⾯的聚集函数相同。

分组后排序

获取每个⽤户最⼤⾦额,排序

MariaDB [TEST]> select user_name 姓名,max(price) 最大金额 from t_order group by user_name order by 最大金额;
+-----------+--------------+
| 姓名      | 最大金额     |
+-----------+--------------+
| 刘德华    |        44.44 |
| 张学友    |        66.66 |
| 黎明      |        88.88 |
+-----------+--------------+
3 rows in set (0.00 sec)

MariaDB [TEST]> select user_name 姓名,max(price) 最大金额 from t_order group by user_name order by 最大金额 desc;
+-----------+--------------+
| 姓名      | 最大金额     |
+-----------+--------------+
| 黎明      |        88.88 |
| 张学友    |        66.66 |
| 刘德华    |        44.44 |
+-----------+--------------+
3 rows in set (0.00 sec)

where、group by、having、order by、limit这些关键字⼀起使⽤时，先后顺序有明确的限制，必须按照顺序来写，语法如下：select 列 from

表名

where [查询条件]

group by [分组表达式]

having [分组过滤条件]

order by [排序条件]

limit [offset,] count;

查询出2018年，记录数量⼤于等于2的，按照记录数量降序排序，最后只输出最多的。

MariaDB [TEST]> select user_name 用户,count(*) 记录 from t_order t where t.the_year=2018  group by user_name order by 记录 desc limit 1;
+-----------+--------+
| 用户      | 记录   |
+-----------+--------+
| 刘德华    |      3 |
+-----------+--------+
1 row in set (0.00 sec)

函数

数值函数

abs:求绝对值

函数 ABS(x) 返回x的绝对值。正数的绝对值是其本⾝，负数的绝对值为其相反数，0的绝对值是0。

MariaDB [TEST]> select abs(5),abs(-2.4),abs(-23),abs(0);
+--------+-----------+----------+--------+
| abs(5) | abs(-2.4) | abs(-23) | abs(0) |
+--------+-----------+----------+--------+
|      5 |       2.4 |       23 |      0 |
+--------+-----------+----------+--------+
1 row in set (0.00 sec)

sqrt:求⼆次⽅根（开⽅）

函数 SQRT(x) 返回⾮负数x的⼆次⽅根。负数没有平⽅根，返回结果为NULL。

MariaDB [TEST]> select sqrt(25),sqrt(0),sqrt(-56);
+----------+---------+-----------+
| sqrt(25) | sqrt(0) | sqrt(-56) |
+----------+---------+-----------+
|        5 |       0 |      NULL |
+----------+---------+-----------+
1 row in set (0.00 sec)

mod:求余数

函数 MOD(x,y) 返回x被y除后的余数，MOD()对于带有⼩数部分的数值也起作⽤，它返回除法运算后的余数.

MariaDB [TEST]> select mod(10,9),mod(10.5,9),mod(12,6);
+-----------+-------------+-----------+
| mod(10,9) | mod(10.5,9) | mod(12,6) |
+-----------+-------------+-----------+
|         1 |         1.5 |         0 |
+-----------+-------------+-----------+

ceil和ceiling:向上取整

函数CEIL(x)和CEILING(x)的意义相同，返回不⼩于x的最⼩整数值，返回值转化为⼀个BIGINT。

MariaDB [TEST]> select ceil(0),ceil(2),ceiling(-2.1),ceil(2.1);
+---------+---------+---------------+-----------+
| ceil(0) | ceil(2) | ceiling(-2.1) | ceil(2.1) |
+---------+---------+---------------+-----------+
|       0 |       2 |            -2 |         3 |
+---------+---------+---------------+-----------+
1 row in set (0.00 sec)

ﬂoor:向下取整

floor(x)函数返回不大于x的最⼤整数值。

MariaDB [TEST]> select floor(3),floor(4.1),floor(-3),floor(-5.6);
+----------+------------+-----------+-------------+
| floor(3) | floor(4.1) | floor(-3) | floor(-5.6) |
+----------+------------+-----------+-------------+
|        3 |          4 |        -3 |          -6 |
+----------+------------+-----------+-------------+

rand:⽣成⼀个随机数

⽣成⼀个0~1之间的随机数，传⼊整数参数是，⽤来产⽣重复序列

MariaDB [TEST]> select rand(),rand(),rand();
+--------------------+--------------------+---------------------+
| rand()             | rand()             | rand()              |
+--------------------+--------------------+---------------------+
| 0.8368902176962143 | 0.6017424926177994 | 0.49804184073399926 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select rand(1),rand(2),rand(1);
+---------------------+--------------------+---------------------+
| rand(1)             | rand(2)            | rand(1)             |
+---------------------+--------------------+---------------------+
| 0.40540353712197724 | 0.6555866465490187 | 0.40540353712197724 |
+---------------------+--------------------+---------------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select rand(2),rand(1),rand(2);
+--------------------+---------------------+--------------------+
| rand(2)            | rand(1)             | rand(2)            |
+--------------------+---------------------+--------------------+
| 0.6555866465490187 | 0.40540353712197724 | 0.6555866465490187 |
+--------------------+---------------------+--------------------+

round:四舍五⼊函数

返回最接近于参数x的整数；ROUND(x,y)函数对参数x进⾏四舍五⼊的操作，返回值保留⼩数点后⾯指定的y位。

MariaDB [TEST]> select round(4.321),round(4.321,0),round(4.321,1),round(4.321,2);
+--------------+----------------+----------------+----------------+
| round(4.321) | round(4.321,0) | round(4.321,1) | round(4.321,2) |
+--------------+----------------+----------------+----------------+
|            4 |              4 |            4.3 |           4.32 |
+--------------+----------------+----------------+----------------+

sign:返回参数的符号

返回参数的符号，x的值为负、零和正时返回结果依次为 -1、0 和 1。

MariaDB [TEST]> select sign(0),sign(-2),sign(2);
+---------+----------+---------+
| sign(0) | sign(-2) | sign(2) |
+---------+----------+---------+
|       0 |       -1 |       1 |
+---------+----------+---------+

pow 和 power:次⽅函数

POW(x,y)函数和POWER(x,y)函数⽤于计算x的y次⽅。


MariaDB [TEST]> select power(5,2),pow(2,-1),pow(0.5,-2),pow(0,5),pow(-4,3);
+------------+-----------+-------------+----------+-----------+
| power(5,2) | pow(2,-1) | pow(0.5,-2) | pow(0,5) | pow(-4,3) |
+------------+-----------+-------------+----------+-----------+
|         25 |       0.5 |           4 |        0 |       -64 |
+------------+-----------+-------------+----------+-----------+
1 row in set (0.00 sec)

sin正弦函数

SIN(x) 返回x的正弦值，其中x为弧度值。

MariaDB [TEST]> select sin(180),sin(0),pi();
+---------------------+--------+----------+
| sin(180)            | sin(0) | pi()     |
+---------------------+--------+----------+
| -0.8011526357338304 |      0 | 3.141593 |
+---------------------+--------+----------+
1 row in set (0.00 sec)

字符串函数

length:返回字符串直接⻓度

⼀个汉字是3个字节，⼀个数字或字母是⼀个字节

MariaDB [TEST]> select length("123456789"),length("你好"),length("abcdefg");
+---------------------+------------------+-------------------+
| length("123456789") | length("你好")   | length("abcdefg") |
+---------------------+------------------+-------------------+
|                   9 |                6 |                 7 |
+---------------------+------------------+-------------------+
1 row in set (0.00 sec)

concat:合并字符串

CONCAT(sl，s2，…) 函数返回结果为连接参数产⽣的字符串，或许有⼀个或多个参数。

若有任何⼀个参数为 NULL，则返回值为 NULL。若所有参数均为⾮⼆进制字符串，则结果为⾮⼆进制字符串。若⾃变量中含有任⼀⼆进制字符串，则结果为⼀个⼆进制字符串。

MariaDB [TEST]> select concat("我爱","黎明"),concat("我爱","黎明","null"),concat("我爱","黎明",null);
+---------------------------+----------------------------------+--------------------------------+
| concat("我爱","黎明")     | concat("我爱","黎明","null")     | concat("我爱","黎明",null)     |
+---------------------------+----------------------------------+--------------------------------+
| 我爱黎明                  | 我爱黎明null                     | NULL                           |
+---------------------------+----------------------------------+--------------------------------+
1 row in set (0.00 sec)

insert:替换字符串

INSERT(s1，x，len，s2) 返回字符串 s1，⼦字符串起始于 x 位置，并且⽤ len 个
字符长的字符串代替 s2。
x的值从1开始，第⼀个字符的x=1，若 x 超过字符串长度，则返回值为原始字符
串。

假如 len 的长度⼤于其他字符串的长度，则从位置 x 开始替换。

若任何⼀个参数为 NULL，则返回值为 NULL。

MariaDB [TEST]> select insert("wo ai li ming",7,7,"liu de hua");
+------------------------------------------+
| insert("wo ai li ming",7,7,"liu de hua") |
+------------------------------------------+
| wo ai liu de hua                         |
+------------------------------------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select insert("我爱黎明",3,2,"刘德华");
+----------------------------------------+
| insert("我爱黎明",3,2,"刘德华")        |
+----------------------------------------+
| 我爱刘德华                             |
+----------------------------------------+
1 row in set (0.00 sec)

lower:将字⺟转换成⼩写


MariaDB [TEST]> select lower("WO AI LI MING");
+------------------------+
| lower("WO AI LI MING") |
+------------------------+
| wo ai li ming          |
+------------------------+
1 row in set (0.00 sec)

upper:将字⺟转换成⼤写

MariaDB [TEST]> select upper("wo ai li ming ") as "我爱黎明" ;
+----------------+
| 我爱黎明       |
+----------------+
| WO AI LI MING  |
+----------------+
1 row in set (0.01 sec)

left:从左侧截取字符串

LEFT(s，n) 函数返回字符串 s 最左边的 n 个字符，s=1表⽰第⼀个字符。

MariaDB [TEST]> select left("wo ai li ming",5 );
+--------------------------+
| left("wo ai li ming",5 ) |
+--------------------------+
| wo ai                    |
+--------------------------+
1 row in set (0.00 sec)

right:从右侧截取字符串

RIGHT(s，n)函数返回字符串s最右边的n个字符。

MariaDB [TEST]> select right("wo ai li ming",7);
+--------------------------+
| right("wo ai li ming",7) |
+--------------------------+
| li ming                  |
+--------------------------+
1 row in set (0.00 sec)

trim:删除字符串两侧空格

TRIM(s)删除字符串s两侧的空格。


MariaDB [TEST]> select "               我爱黎明            ",trim("                       我爱黎明                         ");
+-----------------------------------------+----------------------------------------------------------------------+
| 我爱黎明                                | trim("                       我爱黎明                         ")     |
+-----------------------------------------+----------------------------------------------------------------------+
|                我爱黎明                 | 我爱黎明                                                             |
+-----------------------------------------+----------------------------------------------------------------------+

replace:字符串替换

REPLACE(s，s1，s2) 使⽤字符串 s2 替换字符串 s 中所有的字符串 s1。

#使用b代替字符串中所有的a
MariaDB [TEST]> select replace("ababababab","a","b");
+-------------------------------+
| replace("ababababab","a","b") |
+-------------------------------+
| bbbbbbbbbb                    |
+-------------------------------+
1 row in set (0.00 sec)

substr 和 substring:截取字符串

substr()是substring()的同义词。

substr(str,pos)

第三个字符之后的⼦字符串：inese 	
SELECT substring('chinese', 3);
倒数第三个字符之后的⼦字符串：ese
SELECT substring('chinese', -3);

substr(str from pos)

第三个字符之后的⼦字符串：inese 
SELECT substring('chinese' FROM 3);
倒数第三个字符之后的⼦字符串：ese 
SELECT substring('chinese' FROM -3);

substr(str,pos,len)

第三个字符之后的两个字符：in 
SELECT substring('chinese', 3, 2);	
倒数第三个字符之后的两个字符：es 
SELECT substring('chinese', -3, 2);

substr(str from pos for len)

第三个字符之后的两个字符：in 
SELECT substring('chinese' FROM 3 FOR 2);	
倒数第三个字符之后的两个字符：es 
SELECT substring('chinese' FROM -3 FOR 2);

reverse:反转字符串

REVERSE(s) 可以将字符串 s 反转，返回的字符串的顺序和 s 字符串的顺序相反。

MariaDB [TEST]> select reverse("我爱黎明");
+-------------------------+
| reverse("我爱黎明")     |
+-------------------------+
| 明黎爱我                |
+-------------------------+
1 row in set (0.00 sec)

日期函数

curdate 和 current_date:两个函数作⽤相同，返回当前系统的⽇期值

CURDATE() 和 CURRENT_DATE() 函数的作⽤相同，将当前⽇期按照“YYYY-MMDD”或“YYYYMMDD”格式的值返回，具体格式根据函数⽤在字符串或数字语境中⽽定，返回的date类型。

MariaDB [TEST]> select curdate(),current_date(),curdate()+1;
+------------+----------------+-------------+
| curdate()  | current_date() | curdate()+1 |
+------------+----------------+-------------+
| 2022-11-01 | 2022-11-01     |    20221102 |
+------------+----------------+-------------+
1 row in set (0.00 sec)

curdme 和 current_dme:获取系统当前时间

CURTIME() 和 CURRENT_TIME() 函数的作⽤相同，将当前时间以“HH：MM：SS”或“HHMMSS”格式返回，具体格式根据函数⽤在字符串或数字语境中⽽定，返回time类型。

MariaDB [TEST]> select curtime(),current_time(),curtime()+1;
+-----------+----------------+-------------+
| curtime() | current_time() | curtime()+1 |
+-----------+----------------+-------------+
| 17:17:54  | 17:17:54       |      171755 |
+-----------+----------------+-------------+
1 row in set (0.00 sec)

now 和 sysdate:获取当前时间⽇期

NOW() 和 SYSDATE() 函数的作⽤相同，都是返回当前⽇期和时间值，格式为“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具体格式根据函数⽤在字符串或数字语境中⽽定，返回datetime类型。

MariaDB [TEST]> select now(),sysdate(),now()+1;
+---------------------+---------------------+----------------+
| now()               | sysdate()           | now()+1        |
+---------------------+---------------------+----------------+
| 2022-11-01 17:23:02 | 2022-11-01 17:23:02 | 20221101172303 |
+---------------------+---------------------+----------------+
1 row in set (0.00 sec)

unix_timestamp:获取UNIX时间戳

UNIX_TIMESTAMP(date) 若⽆参数调⽤，返回⼀个⽆符号整数类型的 UNIX 时间戳

MariaDB [TEST]> select unix_timestamp(),unix_timestamp(now()),now(),unix_timestamp("2022-11-01 00-00-00");
+------------------+-----------------------+---------------------+---------------------------------------+
| unix_timestamp() | unix_timestamp(now()) | now()               | unix_timestamp("2022-11-01 00-00-00") |
+------------------+-----------------------+---------------------+---------------------------------------+
|       1667294846 |            1667294846 | 2022-11-01 17:27:26 |                            1667232000 |
+------------------+-----------------------+---------------------+---------------------------------------+
1 row in set (0.00 sec)

from_unixtime:时间戳转⽇期

FROM_UNIXTIME(unix_timestamp[,format])函数把UNIX时间戳转换为普通格式的⽇期时间值，与UNIX_TIMESTAMP () 函数互为反函数。

unix_timestamp：时间戳（秒）

format：要转化的格式⽐如“”%Y-%m-%d“” 这样格式化之后的时间就是2017-11-30

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

MariaDB [TEST]> select from_unixtime(unix_timestamp(now())),unix_timestamp(now()),now();
+--------------------------------------+-----------------------+---------------------+
| from_unixtime(unix_timestamp(now())) | unix_timestamp(now()) | now()               |
+--------------------------------------+-----------------------+---------------------+
| 2022-11-04 16:12:39                  |            1667549559 | 2022-11-04 16:12:39 |
+--------------------------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

month与monthname:获取指定⽇期的⽉份

MONTH(date) 函数返回指定 date 对应的⽉份，范围为 1～12。

MONTHNAME(date) 函数返回⽇期 date 对应⽉份的英⽂全名。


MariaDB [TEST]> select month(now()),monthname(now()),monthname('2022-11-4');
+--------------+------------------+------------------------+
| month(now()) | monthname(now()) | monthname('2022-11-4') |
+--------------+------------------+------------------------+
|           11 | November         | November               |
+--------------+------------------+------------------------+

dayname:获取指定⽇期的星期名称

DAYNAME(date) 函数返回 date 对应的⼯作⽇英⽂名称，例如 Sunday、Monday

等。


MariaDB [TEST]> select dayname(now()),now();
+----------------+---------------------+
| dayname(now()) | now()               |
+----------------+---------------------+
| Friday         | 2022-11-04 16:35:12 |
+----------------+---------------------+
1 row in set (0.00 sec)

dayofweek:获取⽇期对应的周索引

DAYOFWEEK(d) 函数返回 d 对应的⼀周中的索引（位置）。1 表⽰周⽇，2 表⽰周⼀，……，7 表⽰周六。这些索引值对应于ODBC标准。

MariaDB [TEST]> select dayofweek(now()),now();
+------------------+---------------------+
| dayofweek(now()) | now()               |
+------------------+---------------------+
|                6 | 2022-11-04 16:36:36 |
+------------------+---------------------+
1 row in set (0.00 sec)

week:获取指定⽇期是⼀年中的第⼏周

WEEK(date[,mode])函数计算⽇期date是⼀年中的第⼏周。WEEK(date,mode)函数允许指定星期是否起始于周⽇或周⼀，以及返回值的范围是否为0～52或0～53。

WEEK函数接受两个参数：date是要获取周数的⽇期。mode是⼀个可选参数，⽤于确定周数计算的逻辑。它允许您指定本周是从星期⼀还是星期⽇开始，返回的周数应在0到52之间或0到53之间。

如果忽略mode参数，默认情况下WEEK函数将使⽤default_week_format系统变量的值。

要获取default_week_format变量的当前值，请使⽤SHOW VARIABLES语句如下：

MariaDB [TEST]> show variables like 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set (0.00 sec)

模式	一周的第一天	范围	第一周是怎么计算的
0	星期日	0-53	从本年的第一个星期日开始，是第一周。前面的计算为第0周
1	星期一	0-53	假如1月1日到第一个周一的天数超过3天，则计算为本年的第一周。否则为第0周
2	星期日	1-53	从本年的第一个星期日开始，是第一周。前面的计算为上年度的第5x周
3	星期一	1-53	假如1月1日到第一个周日天数超过3天，则计算为本年的第一周。否则为上年度的第5x周
4	星期日	0-53	假如1月1日到第一个周日的天数超过3天，则计算为本年的第一周。否则为第0周
5	星期一	0-53	从本年的第一个星期一开始，是第一周。前面的计算为第0周。
6	星期日	1-53	假如1月1日到第一个周日的天数超过3天，则计算为本年的第一周。否则为上年度的第5x周
7	星期一	1-53	从本年的第一个星期一开始，是第一周。前面的计算为上年度的第5x周

MariaDB [TEST]> select now(),week(now());
+---------------------+-------------+
| now()               | week(now()) |
+---------------------+-------------+
| 2022-11-04 17:11:43 |          44 |
+---------------------+-------------+
1 row in set (0.00 sec)

dayofyear:获取指定⽇期在⼀年中的位置

DAYOFYEAR(d) 函数返回 d 是⼀年中的第⼏天，范围为 1～366。

MariaDB [TEST]> select now(),dayofyear(now()),dayofyear('2022-01-01');
+---------------------+------------------+-------------------------+
| now()               | dayofyear(now()) | dayofyear('2022-01-01') |
+---------------------+------------------+-------------------------+
| 2022-11-04 17:13:52 |              308 |                       1 |
+---------------------+------------------+-------------------------+
1 row in set (0.00 sec)

dayofmonth:获取指定⽇期在⼀个⽉的位置

DAYOFMONTH(d) 函数返回 d 是⼀个⽉中的第⼏天，范围为 1～31。

MariaDB [TEST]> select now(),dayofmonth(now()),dayofmonth('2022-01-01');
+---------------------+-------------------+--------------------------+
| now()               | dayofmonth(now()) | dayofmonth('2022-01-01') |
+---------------------+-------------------+--------------------------+
| 2022-11-04 17:14:44 |                 4 |                        1 |
+---------------------+-------------------+--------------------------+
1 row in set (0.00 sec)

year:获取年份

YEAR() 函数可以从指定⽇期值中来获取年份值。

MariaDB [TEST]> select now(),year(now()),year('2022-01-01');
+---------------------+-------------+--------------------+
| now()               | year(now()) | year('2022-01-01') |
+---------------------+-------------+--------------------+
| 2022-11-04 17:15:47 |        2022 |               2022 |
+---------------------+-------------+--------------------+
1 row in set (0.00 sec)

time_to_sec(:将时间转换为秒值

time_to_sec((time) 函数返回将参数time转换为秒数的时间值，转换公式为“⼩时×3600+分钟×60+秒”。

MariaDB [TEST]> select now(),time_to_sec(now()),time_to_sec('01:01:01');
+---------------------+--------------------+-------------------------+
| now()               | time_to_sec(now()) | time_to_sec('01:01:01') |
+---------------------+--------------------+-------------------------+
| 2022-11-04 17:18:08 |              62288 |                    3661 |
+---------------------+--------------------+-------------------------+

sec_to_time:将秒值转换为时间格式

sec_to_time(seconds) 函数返回将参数 seconds 转换为⼩时、分钟和秒数的时间值。

MariaDB [TEST]> select sec_to_time(3661),sec_to_time(62288);
+-------------------+--------------------+
| sec_to_time(3661) | sec_to_time(62288) |
+-------------------+--------------------+
| 01:01:01          | 17:18:08           |
+-------------------+--------------------+
1 row in set (0.00 sec)

date_add和adddate:向⽇期添加指定时间间隔

DATE_ADD(date,INTERVAL expr type)

date：参数是合法的⽇期表达式。

expr type参数是您希望添加的时间间隔。

type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

MariaDB [TEST]> select date_add(now(),interval 10 day),date_add(now(),interval 10 year),date_add(now(),interval 10 hour),now();
+---------------------------------+----------------------------------+----------------------------------+---------------------+
| date_add(now(),interval 10 day) | date_add(now(),interval 10 year) | date_add(now(),interval 10 hour) | now()               |
+---------------------------------+----------------------------------+----------------------------------+---------------------+
| 2022-11-14 17:26:13             | 2032-11-04 17:26:13              | 2022-11-05 03:26:13              | 2022-11-04 17:26:13 |
+---------------------------------+----------------------------------+----------------------------------+---------------------+
1 row in set (0.00 sec)

date_sub和subdate:⽇期减法运算

DATE_SUB(date,INTERVAL expr type)

date：参数是合法的⽇期表达式。

expr 参数是您希望添加的时间间隔。

type的类型和date_add中的type⼀样。

MariaDB [TEST]> select date_sub(now(),interval 10 day),date_sub(now(),interval 10 year),date_sub(now(),interval 10 hour),now();
+---------------------------------+----------------------------------+----------------------------------+---------------------+
| date_sub(now(),interval 10 day) | date_sub(now(),interval 10 year) | date_sub(now(),interval 10 hour) | now()               |
+---------------------------------+----------------------------------+----------------------------------+---------------------+
| 2022-10-25 17:28:50             | 2012-11-04 17:28:50              | 2022-11-04 07:28:50              | 2022-11-04 17:28:50 |
+---------------------------------+----------------------------------+----------------------------------+---------------------+
1 row in set (0.00 sec)

addtime:时间加法运算

ADDTIME(time,expr) 函数⽤于执⾏时间的加法运算。添加 expr 到 time 并返回结果。

其中：time 是⼀个时间或⽇期时间表达式，expr 是⼀个时间表达式。

MariaDB [TEST]> select addtime(now(),'1:1:1'),now();
+------------------------+---------------------+
| addtime(now(),'1:1:1') | now()               |
+------------------------+---------------------+
| 2022-11-04 18:32:11    | 2022-11-04 17:31:10 |
+------------------------+---------------------+
1 row in set (0.00 sec)

subtime:时间减法运算

SUBTIME(time,expr) 函数⽤于执⾏时间的减法运算。

函数返回time。expr表⽰的值和格式time相同。time是⼀个时间或⽇期时间表达式，expr是⼀个时间。

MariaDB [TEST]> select subtime(now(),'1:1:1'),now();
+------------------------+---------------------+
| subtime(now(),'1:1:1') | now()               |
+------------------------+---------------------+
| 2022-11-07 07:35:49    | 2022-11-07 08:36:50 |
+------------------------+---------------------+
1 row in set (0.00 sec)

datediﬀ:获取两个⽇期的时间间隔

DATEDIFF(date1，date2)返回起始时间date1和结束时间date2之间的天数。

date1和date2为⽇期或date-and-time表达式。计算时只⽤到这些值的⽇期部分。

MariaDB [TEST]> select datediff('2022-11-7','2000-1-1');
+----------------------------------+
| datediff('2022-11-7','2000-1-1') |
+----------------------------------+
|                             8346 |
+----------------------------------+
1 row in set (0.00 sec)

date_format:格式化指定的⽇期

DATE_FORMAT(date，format) 函数是根据 format 指定的格式显⽰ date 值。 DATE_FORMAT() 函数接受两个参数： date：是要格式化的有效⽇期值format：是由预定义的说明符组成的格式字符串，每个说明符前⾯都有⼀个百分⽐字符(%)。 format：格式和上⾯的函数from_unixtime中的format⼀样

MariaDB [TEST]> select date_format('2022-11-7','%Y-%M-%D-%W');
+----------------------------------------+
| date_format('2022-11-7','%Y-%M-%D-%W') |
+----------------------------------------+
| 2022-November-7th-Monday               |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select date_format('2022-11-7','%Y-%m-%d-%a');
+----------------------------------------+
| date_format('2022-11-7','%Y-%m-%d-%a') |
+----------------------------------------+
| 2022-11-07-Mon                         |
+----------------------------------------+
1 row in set (0.00 sec)

weekday:获取指定⽇期在⼀周内的索引位置

WEEKDAY(date)返回date的星期索引(0=星期⼀，1=星期⼆,……6=星期天)

MariaDB [TEST]> select now(),week(now()),dayname(now()),weekday(now());
+---------------------+-------------+----------------+----------------+
| now()               | week(now()) | dayname(now()) | weekday(now()) |
+---------------------+-------------+----------------+----------------+
| 2022-11-07 09:08:36 |          45 | Monday         |              0 |
+---------------------+-------------+----------------+----------------+
1 row in set (0.00 sec)

MySQL 聚合函数

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。

count(col): 表示求指定列的总行数

max(col): 表示求指定列的最大值

min(col): 表示求指定列的最小值

sum(col): 表示求指定列的和

avg(col): 表示求指定列的平均值

MariaDB [TEST]> select * from t_order;
+----+---------+-----------+-------+----------+
| id | user_id | user_name | price | the_year |
+----+---------+-----------+-------+----------+
|  1 |    1001 | 黎明      | 11.11 |     2017 |
|  2 |    1001 | 黎明      | 22.22 |     2018 |
|  3 |    1001 | 黎明      | 88.88 |     2018 |
|  4 |    1002 | 刘德华    | 33.33 |     2018 |
|  5 |    1002 | 刘德华    | 12.22 |     2018 |
|  6 |    1002 | 刘德华    | 16.66 |     2018 |
|  7 |    1002 | 刘德华    | 44.44 |     2019 |
|  8 |    1003 | 张学友    | 55.55 |     2018 |
|  9 |    1003 | 张学友    | 66.66 |     2019 |
+----+---------+-----------+-------+----------+
9 rows in set (0.00 sec)
MariaDB [TEST]> select count(the_year) from t_order where the_year=2017;
+-----------------+
| count(the_year) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select user_name,max(price) from t_order;
+-----------+------------+
| user_name | max(price) |
+-----------+------------+
| 黎明      |      88.88 |
+-----------+------------+
1 row in set (0.00 sec)
MariaDB [TEST]> select user_name,min(price) from t_order;
+-----------+------------+
| user_name | min(price) |
+-----------+------------+
| 黎明      |      11.11 |
+-----------+------------+
1 row in set (0.00 sec)
MariaDB [TEST]> select sum(id) from t_order;
+---------+
| sum(id) |
+---------+
|      45 |
+---------+
1 row in set (0.00 sec)
MariaDB [TEST]> select avg(id) from t_order;
+---------+
| avg(id) |
+---------+
|  5.0000 |
+---------+
1 row in set (0.00 sec)

MySQL 流程控制函数

if判断,流程控制

IF(expr,v1,v2)

当expr为真是返回v1的值，否则返回v2

MariaDB [TEST]> select if(0,1,3),if(1,1,3);
+-----------+-----------+
| if(0,1,3) | if(1,1,3) |
+-----------+-----------+
|         3 |         1 |
+-----------+-----------+
1 row in set (0.00 sec)

MariaDB [TEST]> select if(1>3,1,3),if(1<3,1,3);
+-------------+-------------+
| if(1>3,1,3) | if(1<3,1,3) |
+-------------+-------------+
|           3 |           1 |
+-------------+-------------+
1 row in set (0.00 sec)

ifnull:判断是否为空

IFNULL(v1,v2)：v1为空返回v2，否则返回v1。

MariaDB [TEST]> select ifnull(1,2),ifnull(null,1);
+-------------+----------------+
| ifnull(1,2) | ifnull(null,1) |
+-------------+----------------+
|           1 |              1 |
+-------------+----------------+
1 row in set (0.00 sec)

case:搜索语句

格式

CASE <表达式>	
 WHEN <值1> THEN <操作>	
 WHEN <值2> THEN <操作>	
 ...	
 ELSE <操作>	
END CASE;

CASE	
 WHEN <条件1> THEN <命令>	
 WHEN <条件2> THEN <命令>	
 ...	
 ELSE commands	
END CASE;

MariaDB [TEST]> select * from J_COURSE;
+-----+--------------+-----+
| cno | cname        | tno |
+-----+--------------+-----+
|   1 | 企业管理     |   3 |
|   2 | 马克思       |   1 |
|   3 | UML          |   2 |
|   4 | 数据库       |   5 |
|   5 | 物理         |   8 |
+-----+--------------+-----+
5 rows in set (0.00 sec)

MariaDB [TEST]> select cname 课程,(case when tno>=8 then '优' when 8>tno and tno>=5 then '良' else '差' end ) 评价 from J_COURSE;
+--------------+--------+
| 课程         | 评价   |
+--------------+--------+
| 企业管理     | 差     |
| 马克思       | 差     |
| UML          | 差     |
| 数据库       | 良     |
| 物理         | 优     |
+--------------+--------+
5 rows in set (0.00 sec)

MariaDB [TEST]> select cno 序号,cname 课程,(case when cno=2 then '优' when  cno=4 then '良' else '差' end ) 评价 from J_COURSE;     +--------+--------------+--------+
| 序号   | 课程         | 评价   |
+--------+--------------+--------+
|      1 | 企业管理     | 差     |
|      2 | 马克思       | 优     |
|      3 | UML          | 差     |
|      4 | 数据库       | 良     |
|      5 | 物理         | 差     |
+--------+--------------+--------+
5 rows in set (0.00 sec)

其他函数

version 数据库版本号

database 当前的数据库

user 当前连接⽤户

password 返回字符串密码形式

m d 5 返回字符串的m d 5数据

MariaDB [TEST]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.68-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select database();
+------------+
| database() |
+------------+
| TEST       |
+------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)

连接查询及原理

笛卡尔积

笛卡尔积：有两个集合A和B，笛卡尔积表⽰A集合中的元素和B集合中的元素任意相互关联产⽣的所有可能的结果。假如A中有m个元素，B中有n个元素，A、B笛卡尔积产⽣的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

sql中笛卡尔积语法

select 字段	from 表1,表2[,表N];	
或者	
select 字段	from 表1 join 表2 [join 表N];

MariaDB [TEST]> select * from t_team;
+----+-----------+
| id | team_name |
+----+-----------+
|  1 | lol       |
|  2 | csgo      |
|  3 | dota      |
|  4 | dnf       |
+----+-----------+
4 rows in set (0.00 sec)

MariaDB [TEST]> select * from t_name;
+----+--------+
| id | name   |
+----+--------+
|  1 | 珍珍   |
|  2 | 莲莲   |
|  3 | 艾艾   |
+----+--------+
3 rows in set (0.00 sec)

MariaDB [TEST]> select * from t_team ,t_name;
+----+-----------+----+--------+
| id | team_name | id | name   |
+----+-----------+----+--------+
|  1 | lol       |  1 | 珍珍   |
|  1 | lol       |  2 | 莲莲   |
|  1 | lol       |  3 | 艾艾   |
|  2 | csgo      |  1 | 珍珍   |
|  2 | csgo      |  2 | 莲莲   |
|  2 | csgo      |  3 | 艾艾   |
|  3 | dota      |  1 | 珍珍   |
|  3 | dota      |  2 | 莲莲   |
|  3 | dota      |  3 | 艾艾   |
|  4 | dnf       |  1 | 珍珍   |
|  4 | dnf       |  2 | 莲莲   |
|  4 | dnf       |  3 | 艾艾   |
+----+-----------+----+--------+
12 rows in set (0.00 sec)

内连接

语法

select 字段	from 表1 inner join 表2 on 连接条件;	
或	
select 字段	from 表1 join 表2 on 连接条件;	
或	
select 字段	from 表1, 表2 [where 关联条件];

内连接相当于在笛卡尔积的基础上加上了连接的条件。

当没有连接条件的时候，内连接上升为笛卡尔积。

MariaDB [TEST]> select * from t_team ,t_name where t_name.name='珍珍';
+----+-----------+----+--------+
| id | team_name | id | name   |
+----+-----------+----+--------+
|  1 | lol       |  1 | 珍珍   |
|  2 | csgo      |  1 | 珍珍   |
|  3 | dota      |  1 | 珍珍   |
|  4 | dnf       |  1 | 珍珍   |
+----+-----------+----+--------+
4 rows in set (0.00 sec)
MariaDB [TEST]> select * from t_team join  t_name on t_team.team_name='dnf' and t_name.name='珍珍';
+----+-----------+----+--------+
| id | team_name | id | name   |
+----+-----------+----+--------+
|  4 | dnf       |  1 | 珍珍   |
+----+-----------+----+--------+
1 row in set (0.00 sec)
MariaDB [TEST]> select * from t_team join  t_name on t_team.id=t_name.id;
+----+-----------+----+--------+
| id | team_name | id | name   |
+----+-----------+----+--------+
|  1 | lol       |  1 | 珍珍   |
|  2 | csgo      |  2 | 莲莲   |
|  3 | dota      |  3 | 艾艾   |
+----+-----------+----+--------+
3 rows in set (0.00 sec)

外连接

外连接涉及到2个表，分为：主表和从表，要查询的信息主要来⾃于哪个表，谁就是主表。

外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显⽰匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显⽰null。

最终：外连接查询结果 = 内连接的结果 + 主表中有的⽽内连接结果中没有的记录。

外连接分为2种：

左外链接：使⽤left join关键字，left join左边的是主表。

右外连接：使⽤right join关键字，right join右边的是主表。

左连接

语法 select 列 from 主表 left join 从表 on 连接条件;

MariaDB [TEST]> select * from t_team left join  t_name on t_team.id=t_name.id;
+----+-----------+------+--------+
| id | team_name | id   | name   |
+----+-----------+------+--------+
|  1 | lol       |    1 | 珍珍   |
|  2 | csgo      |    2 | 莲莲   |
|  3 | dota      |    3 | 艾艾   |
|  4 | dnf       | NULL | NULL   |
+----+-----------+------+--------+
4 rows in set (0.00 sec)

右连接

语法

select 列 from 从表 right join 主表 on 连接条件;

MariaDB [TEST]> select * from t_team right join  t_name on t_team.id=t_name.id;
+------+-----------+----+--------+
| id   | team_name | id | name   |
+------+-----------+----+--------+
|    1 | lol       |  1 | 珍珍   |
|    2 | csgo      |  2 | 莲莲   |
|    3 | dota      |  3 | 艾艾   |
+------+-----------+----+--------+
3 rows in set (0.00 sec)

MariaDB [TEST]> select * from t_name right join  t_team on t_team.id=t_name.id;
+------+--------+----+-----------+
| id   | name   | id | team_name |
+------+--------+----+-----------+
|    1 | 珍珍   |  1 | lol       |
|    2 | 莲莲   |  2 | csgo      |
|    3 | 艾艾   |  3 | dota      |
| NULL | NULL   |  4 | dnf       |
+------+--------+----+-----------+
4 rows in set (0.00 sec)

子查询

出现在select语句中的select语句，称为⼦查询或内查询。

外部的select查询语句，称为主查询或外查询。

分类

按结果集的⾏列数不同分

标量⼦查询（结果集只有⼀⾏⼀列）

列⼦查询（结果集只有⼀列多⾏）

⾏⼦查询（结果集有⼀⾏多列）

表⼦查询（结果集⼀般为多⾏多列）

按⼦查询出现在主查询中的不同位置分

select后⾯：仅仅⽀持标量⼦查询。

from后⾯：⽀持表⼦查询。

where或having后⾯：⽀持标量⼦查询（单列单⾏）、列⼦查询（单列多⾏）、⾏⼦查询（多列多⾏）

exists后⾯（即相关⼦查询）：表⼦查询（多⾏、多列）

CREATE TABLE `dt` (
  `dt_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '游戏id',
  `dt_name` varchar(32) NOT NULL DEFAULT '' COMMENT '游戏名称',
  PRIMARY KEY (`dt_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='游戏组表'；  
insert into dt values(1,'lol'),(2,'dota'),(3'csgo'),(4'dnf');

CREATE TABLE `dtemp` (
  `dt_id` int(11) ,
  `dtemp_name` varchar(32) ,
  `level` int(11) 
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8  
insert into dtemp values(1,'张三',2),(2,'李四',3), (3,'喜洋洋',2),(4,'灰太狼',2),(3,'虹猫',8),(1,'蓝兔',7),(2,'黑小虎',9),(4,'迪迦',10),(3,'盖亚',10),(1,'加坦杰厄',10),(1,'大古',5),(1,'熊大',1),(1,'熊二','1) ;

select后面的子查询

查询各游戏的人数

MariaDB [TEST]> select * ,(select count(*) from dtemp where dt.dt_id=dtemp.dt_id) as 人数 from dt;
+-------+---------+--------+
| dt_id | dt_name | 人数   |
+-------+---------+--------+
|     1 | lol     |      6 |
|     2 | dota    |      2 |
|     3 | csgo    |      3 |
|     4 | dnf     |      2 |
+-------+---------+--------+
4 rows in set (0.00 sec)

查询加坦杰厄玩的游戏

MariaDB [TEST]>  select (select dt_name  from dt,dtemp where dt.dt_id=dtemp.dt_id and dtemp.dtemp_name='加坦杰厄') as  游戏名;
+-----------+
| 游戏名    |
+-----------+
| lol       |
+-----------+
1 row in set (0.00 sec)

from后面的子查询

将子查询的结果集充当一张表，要求必须起别名，否者这个表找不到。

然后将真实的表和子查询结果表进行连接查询。

输出玩家平均水平最高的游戏

MariaDB [TEST]> select b.dt_id,b.a,c.dt_name from (select dtemp.dt_id,avg(level) a from dtemp group by dtemp.dt_id order by a desc limit 1) b,dt c where b.dt_id=c.dt_id;
+-------+--------+---------+
| dt_id | a      | dt_name |
+-------+--------+---------+
|     3 | 6.6667 | csgo    |
+-------+--------+---------+
1 row in set (0.01 sec)

where和having后⾯的⼦查询

标量子查询（单行单列行子查询）
列子查询（单列多行子查询）
行子查询（一行多列）

特点：
子查询放在小括号内。
子查询一般放在条件的右侧。
标量子查询，一般搭配着单行单列操作符使用 >、<、>=、<=、=、<>、!=
列子查询，一般搭配着多行操作符使用

in(not in)：列表中的“任意一个”

any或者some：和子查询返回的“某一个值”比较，比如a>some(10,20,30)，a大于子查询中任意一个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。

all：和子查询返回的“所有值”比较，比如a>all(10,20,30)，a大于子查询中所有值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30);

5.子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。

mysql中的in、any、some、all

in，any，some，all分别是子查询关键词之一。

in：in常用于where表达式中，其作用是查询某个范围内的数据

any和some一样：可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

all：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

下文中会经常用到这些关键字。

标量子查询

找到游戏水平高于游戏平均值，并且输出对应的玩家和游戏。

MariaDB [TEST]> select a.*,b.dt_name from dtemp a ,dt b where a.level > (select avg(level) from dtemp ) and a.dt_id=b.dt_id ;
+-------+--------------+-------+---------+
| dt_id | dtemp_name   | level | dt_name |
+-------+--------------+-------+---------+
|     1 | 蓝兔         |     7 | lol     |
|     1 | 加坦杰厄     |    10 | lol     |
|     2 | 黑小虎       |     9 | dota    |
|     3 | 虹猫         |     8 | csgo    |
|     3 | 盖亚         |    10 | csgo    |
|     4 | 迪迦         |    10 | dnf     |
+-------+--------------+-------+---------+
6 rows in set (0.00 sec)

MariaDB [TEST]> select * from dtemp where level > (select level from dtemp where dtemp_name='虹猫' );
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
+-------+--------------+-------+
4 rows in set (0.00 sec)

子查询+分组函数

各游戏平均水平大于总的平均水平

MariaDB [TEST]> select dt_id,avg(dtemp.level)  from dtemp group by dt_id   having avg(dtemp.level) > (select avg(level) from dtemp);
+-------+------------------+
| dt_id | avg(dtemp.level) |
+-------+------------------+
|     2 |           6.0000 |
|     3 |           6.6667 |
|     4 |           6.0000 |
+-------+------------------+
3 rows in set (0.00 sec)

列子查询(子查询结果集一列多行)

列子查询需要搭配多行操作符使用：in(not in)、any/some、all。

MariaDB [TEST]> select * from dtemp where dt_id=2;
+-------+------------+-------+
| dt_id | dtemp_name | level |
+-------+------------+-------+
|     2 | 李四       |     3 |
|     2 | 黑小虎     |     9 |
+-------+------------+-------+
2 rows in set (0.00 sec)

MariaDB [TEST]> select * from dtemp where level > (select level from dtemp where dt_id=2 );
ERROR 1242 (21000): Subquery returns more than 1 row
MariaDB [TEST]> select * from dtemp where level > all(select level from dtemp where dt_id=2 );
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
+-------+--------------+-------+
3 rows in set (0.00 sec)

MariaDB [TEST]> select * from dtemp where level > any(select level from dtemp where dt_id=2 );
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
+-------+--------------+-------+
7 rows in set (0.00 sec)

MariaDB [TEST]> select * from dtemp where level > some(select level from dtemp where dt_id=2 );
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
+-------+--------------+-------+
7 rows in set (0.00 sec)
MariaDB [TEST]> select * from dtemp where level in  (select level from dtemp where dt_id=1 );
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     1 | 蓝兔         |     7 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
+-------+--------------+-------+
10 rows in set (0.00 sec)
MariaDB [TEST]> select * from dtemp where level in  (select level from dtemp where dt_id=1 ) and dt_id<>1;
+-------+------------+-------+
| dt_id | dtemp_name | level |
+-------+------------+-------+
|     3 | 喜洋洋     |     2 |
|     4 | 灰太狼     |     2 |
|     4 | 迪迦       |    10 |
|     3 | 盖亚       |    10 |
+-------+------------+-------+
4 rows in set (0.00 sec)

行子查询（子查询结果集一行多列）

输出游戏水平最高并且玩lol的玩家

MariaDB [TEST]> select dtemp_name 峡谷第一 from dtemp where dtemp.dt_id=(select dt_id from dt where dt_name='lol') and dtemp.level =(select max(level) from dtemp);
+--------------+
| 峡谷第一     |
+--------------+
| 加坦杰厄     |
+--------------+
1 row in set (0.00 sec)

exists后面（也叫做相关子查询）

语法：exists(完整的查询语句)。
exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。
一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。
和前面的查询不同，这先执行主查询，然后主查询查询的结果，在根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。

MariaDB [TEST]> select exists (select * from dtemp where level=11);
+---------------------------------------------+
| exists (select * from dtemp where level=11) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select exists (select * from dtemp where level=1);
+--------------------------------------------+
| exists (select * from dtemp where level=1) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [TEST]> select * from dtemp where dtemp.level in (11);
Empty set (0.00 sec)

关于事务

说明：数据库中的事务是指对数据库执⾏⼀批操作，这些操作最终要么全部执⾏成功，要么全部失败，不会存在部分成功的情况。

四大特性

原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态，⼀个事务必须使数据库从⼀个⼀致性状态变换到另⼀个⼀致性状态。
隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

隐式事务和显式事务

mysql中事务默认是隐式事务，执⾏insert、update、delete操作的时候，数据库⾃动开启事务、提交或回滚事务。

是否开启隐式事务是由变量autocommit控制的。所以事务分为隐式事务和显式事务。

隐式事务：事务⾃动开启、提交或回滚，⽐如insert、update、delete语句，事务的开启、提交或回滚由mysql内部⾃动控制的。

查看方式：autocommit为ON表⽰开启了⾃动提交。

MariaDB [TEST]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

自动提交下，必须手动开启事务才能回滚。

MariaDB [TEST]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [TEST]> select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
+-------+--------------+-------+
13 rows in set (0.00 sec)

MariaDB [TEST]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]>  insert into dtemp values(1,"ppd",8);
Query OK, 1 row affected (0.00 sec)

MariaDB [TEST]>  select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
|     1 | ppd          |     8 |
+-------+--------------+-------+
14 rows in set (0.00 sec)

MariaDB [TEST]> rollback
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]>  select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
+-------+--------------+-------+
13 rows in set (0.00 sec)

MariaDB [TEST]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]>  insert into dtemp values(1,"ppd",8);
Query OK, 1 row affected (0.00 sec)

MariaDB [TEST]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]>  select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
|     1 | ppd          |     8 |
+-------+--------------+-------+
14 rows in set (0.00 sec)

显式事务：事务需要⼿动开启、提交或回滚，由开发者⾃⼰控制。事务手动提交后就不能在回滚了；

设置不自动提交事务

set autocommit=0;

START TRANSACTION 或 BEGIN TRANSACTION;//开启事务
commit;//提交事务

rollback;//回滚事务

MariaDB [TEST]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [TEST]> select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
+-------+--------------+-------+
13 rows in set (0.00 sec)

MariaDB [TEST]> insert into dtemp values(1,"55k",8);
Query OK, 1 row affected (0.00 sec)

MariaDB [TEST]> select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
|     1 | 55k          |     8 |
+-------+--------------+-------+
14 rows in set (0.00 sec)

MariaDB [TEST]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]> select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
+-------+--------------+-------+
13 rows in set (0.00 sec)

MariaDB [TEST]> insert into dtemp values(1,"55k",8);
Query OK, 1 row affected (0.00 sec)

MariaDB [TEST]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]> select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
|     1 | 55k          |     8 |
+-------+--------------+-------+
14 rows in set (0.00 sec)

savepoint关键字

在事务中我们执⾏了⼀⼤批操作，可能我们只想回滚部分数据，可以使⽤savepoin来实现。

MariaDB [TEST]>  select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
|     1 | ppd          |     8 |
+-------+--------------+-------+
14 rows in set (0.00 sec)

MariaDB [TEST]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]> delete from dtemp where dtemp_name="ppd";
Query OK, 1 row affected (0.00 sec)

MariaDB [TEST]> savepoint part1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]> insert into dtemp values (1,"55k",8);
Query OK, 1 row affected (0.00 sec)

MariaDB [TEST]> rollback to part1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [TEST]>  select * from dtemp;
+-------+--------------+-------+
| dt_id | dtemp_name   | level |
+-------+--------------+-------+
|     1 | 张三         |     2 |
|     2 | 李四         |     3 |
|     3 | 喜洋洋       |     2 |
|     4 | 灰太狼       |     2 |
|     3 | 虹猫         |     8 |
|     1 | 蓝兔         |     7 |
|     2 | 黑小虎       |     9 |
|     4 | 迪迦         |    10 |
|     3 | 盖亚         |    10 |
|     1 | 加坦杰厄     |    10 |
|     1 | 大古         |     5 |
|     1 | 熊大         |     1 |
|     1 | 熊二         |     1 |
+-------+--------------+-------+
13 rows in set (0.00 sec)

savepoint需要结合rollback to sp1⼀起使⽤，可以将保存点sp1到rollback to之间的操作回滚掉。

只读事务

表⽰在事务中执⾏的是⼀些只读操作，如查询，但是不会做insert、update、delete操作，数据库内部对只读事务可能会有⼀些性能上的优化。

start transaction read only;	//只读事务

start transaction read only;
//开启只读事务
//之后若使用insert、update、delete操作，将会报错。
select * from test;
//查询操作可以正常执行
commit;
//事务提交

事务中的⼀些问题

脏读

⼀个事务在执⾏的过程中读取到了其他事务还没有提交的数据。

读已提交

从字⾯上我们就可以理解，即⼀个事务操作过程中可以读取到其他事务已经提交的数据。

可重复读

⼀个事务操作中对于⼀个读取操作不管多少次，读取到的结果都是⼀样的。

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同

幻读

幻读在可重复读的模式下才会出现,一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在.

事务中后⾯的操作需要的前面的读取操作提供⽀持，但读取操作却不能⽀持下⾯的操作时产⽣的错误，就像发⽣了幻觉⼀样。

事务的隔离级别

当多个事务同时进⾏的时候，如何确保当前事务中数据的正确性，⽐如A、B两个事物同时进⾏的时候，A是否可以看到B已提交的数据或者B未提交的数据，这个需要依靠事务的隔离级别来保证，不同的隔离级别中所产⽣的效果是不⼀样的。

事务隔离级别主要是解决了上⾯多个事务之间数据可见性及数据正确性的问题。

隔离级别	脏读	不可重复读	幻读
Read uncommitted 读未提交	√	√	×
Read committed 读已提交	×	√	×
Repeatable Read(默认) 可重复读	×	×	√
Serializable 串⾏	×	×	×

SERIALIZABLE会让并发的事务串⾏执⾏（多个事务之间读写、写读、写写会产⽣互斥，效果就是串⾏执⾏，多个事务之间的读读不会产⽣互斥）。

读写互斥：事务A中先读取操作，事务B发起写⼊操作，事务A中的读取会导致事务B中的写⼊处于等待状态，直到A事务完成为⽌。

表⽰我开启⼀个事务，为了保证事务中不会出现上⾯说的问题（脏读、不可重复读、读已提交、幻读），那么我读取的时候，其他事务有修改数据的操作需要排队等待，等待我读取完成之后，他们才可以继续。

写读、写写也是互斥的，读写互斥类似。

上⾯4中隔离级别越来越强，会导致数据库的并发性也越来越低。

查看隔离级别

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.02 sec)