数据库详解（DB，DataBase）
- 概念：数据仓库，软件，安装在操作系统之上；SQL，可以存储大量的数据，500万以下，500万以上要做索引优化
- 作用：存储数据，管理数据
- 数据库分类
- 关系型数据库：行、列（SQL）
- MySQL、Oracle、Sql Server、DB2、SQLLite
- 通过表和表之间，行和列之间的关系进行数据的存储
- 非关系型数据库：{key：value} （NoSQL：Not Only SQL）
- Redis、MongoDB
- 对象存储，通过对象的自身属性来决定
- 关系型数据库：行、列（SQL）
- DBMS（Database Management System）
- 数据库的管理软件，科学有效地管理我们的数据。维护和获取数据
- 常用DBMS排名：DB-Engines Ranking - popularity ranking of database management systems
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图：
1、第一层负责连接管理、授权认证、安全等等。
每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名和密码的方式进行认证，也可以通过SSL证书进行认证。登录认证通过后，服务器还会验证该客户端是否有执行某个查询的权限。
2、第二层负责解析查询
编译SQL，并对其进行优化(如调整表的读取顺序，选择合适的索引等)。对于SELECT语句，在解析查询前，服务器会先检查查询缓存，如果能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果。存储过程、触发器、视图等都在这一层实现。
3、第三层是存储引擎
存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。
注意：mysql默认安装地址《C:\Program Files\MySQL\MySQL Server 8.0\》
数据库语言分类
- 数据定义语言 DDL：定义数据库表、视图、索引
- CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE
- 数据操作语言 DML：添加、删除、修改、查询数据
- INSERT \ DELETE \ UPDATE \SELECT
- 数据控制语言 DCL：用于定义数据库、表、字段、用户的权限和安全级别
- COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE
操作数据库 ——>操作数据库中的表——>操作数据库中表的数据
注意：mysql关键字不区分大小写
操作数据库
创建数据库
CREATE DATABASE mytest1; --创建数据库
CREATE DATABASE mytest2 CHARACTER SET ''; --创建数据库，并且设置字符集
CREATE DATABASE IF NOT EXISTS mytest2 ; --创建数据库，并且设置字符集查看数据库信息
show CREATE DATABASE hzx;--查看指定数据库和创建语句
show DATABASES; --查看当前连接中的多少个数据库
select DATABASE() from DUAL;--查看当前使用的数据库切换数据库
USE hzx; --使用数据库修改数据库（不建议经常操作）
ALTER DATABASE 数据库名 CHARACTER SET 字符集；--修改数据库字符集删除数据库
DROP DATABASE 数据库名;--删除数据库不存在会报错
DROP DATABASE IF EXISTS 数据库名 ;--如果存在删除数据库数据库的列类型
- 数值
- int 标准的整数 4个字节 常用的
- float 单精度浮点数 4个字节
- double 双精度浮点数 8个字节 （精度问题！）
- decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal
- 字符串
- char 字符串固定大小的 0~255
- varchar 可变字符串 0~65535 常用的变量 String
- text 文本串 2^16-1
- 时间日期
java.util.Date
data YYYY-MM-DD 日期格式
time HH:mm:ss 时间格式
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳 1970.1.1到现在的毫秒数 也较为常用
year 年份表示
注意：不要使用NULL进行运算，结果一定为NULL
- 常见数据类型
| 数据类型 | 大小 | 说明 | Java类型 |
| Bit[M] | M指定位数，默认未1 | 二进制，M从1到64 | 常用Boolean |
| TinYint | 1字节 | Byte | |
| Smallint | 2字节 | Short | |
| int | 4字节 | Integer | |
| Bigint | 8字节 | long | |
| Float(M,D) | 4字节 | 单精度，M长度，D小数位数，会发生精度丢失 | Float |
| Double(M,D) | 8字节 | Double | |
| Decimal(M,D) | M/D最大值+2 | 双精度，M为长度，D小数位数，精确数值 | BigDecimal |
| Numeric(M,D) | M/Dz最大值+2 | 和BigDecmal一致 | BigDecimal |
数据库的字段属性
- Unsigned
- 无符号的整数
- 不能声明为负数
- zerofill
- 0填充的
- 不足的位数使用0填充 int(3) 5——>005
- 自增
- 自动在上一条记录的基础上加一
- 通常用来设置唯一的主键 ~index，必须是整数类型
- 可以自定义设置主键自增的起始值和步长
- 非空 NULL not null
- 假设设置为not null ，如果不给它赋值就会报错！
- NULL ，如果不填写值，默认为NULL
- 默认
- 设置默认的值！
- sex，默认值为男，如果不指定该列的值则会有默认的值
- 每一个表必须要的五个字段，表示一个项目存在的意义：
- id 主键
- ‘version’ 乐观锁
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
操作数据库表
创建数据表
-- 目标：创建一个school数据库
-- 创建学生表（列、字段） 使用SQL创建
-- 学号int 登录密码varchar(20) 姓名、性别varchar(20) 出生日期(datetime) 家庭地址 email
/*注意点：
1、使用英文()
2、表的名称和字段尽量使用 `` (Tab键上面的那个键：“飘”)括起来
3、AUTO_INCREMENT：自增
4、字符串使用单引号（'':英文状态）括起来
5、所有的语句后面加 ，(英文的) ，最后一个字段不加
*/
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
--基于现有的表查创建表，并且会将查出的select数据插入进去
CREATE TABLE dbtest
AS
SELECT * from student格式总结
CREATE TABLE IF NOT EXISTS `表名` (
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释],
PRIMARY KEY (`字段名`)
)[表类型][字符集设置]
修改表
关键字：ALTER TABLE
##新增字段
ALTER TABLE base_cache_area
ADD cache_level int COMMENT '缓存区等级 1-5' DEFAULT 1;--添加一个字段,增加备注并且设置默认值
ALTER TABLE dbtest
ADD salary1 DOUBLE(10,2);--添加一个字段,默认放到最后
ALTER TABLE dbtest
ADD salary2 DOUBLE(10,2) FIRST;--添加一个字段,放到表开始
ALTER TABLE dbtest
ADD salary3 DOUBLE(10,2) AFTER id;--添加一个字段,放到id字段后面
##修改字段属性，默认值，长度
ALTER TABLE dbtest
MODIFY `name `DOUBLE(10,2) DEFAULT 'YANG';
##重命名一个字段
ALTER TABLE dbtest
CHANGE `name ` 新字段名 VARCHAR(200);
##删除一个字段
ALTER TABLE dbtest
DROP COLUMN `name`;
##重命名表
RENAME TABLE dbtest TO test; --方式1
ALTER TABLE dbtest
RENAME TO test; --方式2查看表
show TABLES;--查看表列表
SHOW TABLES FROM mysql;--查看指定数据库下保存的数据库表
DESC dbtest;--查看数据库详细数据
SHOW CREATE TABLE dbtest;--查看数据表创建语句show TABLES;--查看表列表 SHOW TABLES FROM mysql;--查看指定数据库下保存的数据库表 DESC dbtest;--查看数据库详细数据 SHOW CREATE TABLE dbtest;--查看数据表创建语句
数据库表的引擎类型
--关于数据库引擎
/* INNODB 默认使用~
MYISAM 早些年使用的 */
| MYISAM | INNODB | |
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 （只支持表锁） | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小（占的内存） | 较小 | 较大（约为MYISAM的两倍） |
事务：如果你两个SQL同时运行，要么同时成功，要么同时失败；如果一个成功，一个失败，就会提交不上去，就说明你这段代码失败了
表锁：假设有两个SQL要去查同一个表，它会要求你先把这个表锁住，它锁表的时候第二次进去查的时候就会进行排队等待
行锁：它只锁这一行记录，这样效率就会高一点
常规使用操作
MYISAM 节约空间，速度较快
INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
本质还是文件的存储！
MySQL引擎在物理文件上的区别：
- InnoDB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件，删了表就废了，一般不会动这个文件夹
- *.MYD：MyData 数据文件（data）
- *.MYI：MyIndex 索引文件（index）
对比InnoDB与MyISAM
1、 存储结构
MyISAM：每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
2、 存储空间
MyISAM： MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。
InnoDB： 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
3、 可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
4、 事务支持
MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
5、 AUTO_INCREMENT
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
6、 表锁差异
MyISAM： 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB： 支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
7、 全文索引
MyISAM：支持 FULLTEXT类型的全文索引
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
8、表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
9、表的具体行数
MyISAM： 保存有表的总行数，如果select count() from table;会直接取出出该值。
InnoDB： 没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
10、CRUD操作
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。
11、 外键
MyISAM：不支持
InnoDB：支持
数据库表的约束
保证了数据的完整性。
实体完整性：同一张表，不能存在完全相同的记录
域完整性：一张表控制年龄范围必须在0-20岁
引用完整性：员工表对应的部门id，在部门表中要能找到这个部门
用户自定义完整性：用户名唯一，密码不能为空
约束分类（不同角度分类，但是约束还是那些）
-
角度一
- 单列约束、多列约束
-
角度二
- 列级约束、表级约束
-
角度三
- not null [非空约束]
- unique [唯一约束]
- primary [主键约束]
- foregin [外键约束]
- check [检查约束]
- default [默认值约束]
操作约束
添加约束
a.CREATE TABLE 时添加约束
b.ALTER TABLE 时增加约束，或者删除约束
约束类型
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。
NULL约束
default '0' 或者加个default 其他字符，也能保证不是null
创建表时，可以指定某列不为空
drop table if exists student;
create table student(
id int not NULL,
sn int,
name varchar(20),
qq_mail varchar(10)
);UNIQUE：唯一约束
指定sn列为唯一的、不重复的：
drop table if exists student;
create table student(
id int not NULL,
sn int unique,
name varchar(20),
qq_mail varchar(10)
);DEFAULT：默认值约束
指定插入数据时，name列为空，默认值unkown：
drop table if exists student;
create table student(
id int not NULL,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(10)
);PRIMARY KEY：主键约束
指定id列为主键：
drop table if exists student;
create table student(
id int not NULL primary key,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(10)
);对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大 值+1。
-- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL id int primary key auto_increment,
FOREIGN KEY：外键约束
外键用于关联其他表的主键或唯一键，语法：
foreign key (字段名) references 主表(列)创建班级表classes，id为主键：
-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
drop table if exists classes;
create table classes(
id int primary key auto_increment,
name varchar(20),
`desc` varchar(100)
);创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键， classes_id为外键，关联班级表id
drop table if exists student;
create table student(
id int primary key auto_increment,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(20),
classes_id int,
foreign key (classes_id) references classes(id)
);CHECK约束（了解）
MySQL使用时不报错，但忽略该约束：
drop table if exists test_user;
create table test_user(
id int,
name varchar(20),
sex varchar(1),
check(sex = '男' or sex = '女')
);删除主键约束
ALTER TABLE your_table_name DROP PRIMARY KEY;
删除外键约束
ALTER TABLE your_table_name DROP FOREIGN KEY constraint_name;删除唯一约束
ALTER TABLE your_table_name DROP INDEX constraint_name;
删除检查约束
ALTER TABLE your_table_name DROP CHECK constraint_name;
删除默认约束
MySQL 中并不直接提供删除默认约束的功能，但可以通过修改列来达到删除默认值的效果：
ALTER TABLE your_table_name ALTER COLUMN column_name DROP DEFAULT;
数据库视图【虚拟表】
对同一张表，针对不同的用户，展示不同的信息表，这个表是一张虚拟表。
同一张表，针对不同的用户，展示不同的信息表，这个表是一张虚拟表。
视图的理解
因为是一张虚拟的表，占的内存很小，可以理解为存储起来的SELECT语句。
视图建立在已有表的基础上，这些存在的表也被称为基表
删除和新增视图不会影响基表；
但是新增，修改，删除视图中的数据，基表的数据也会发生变化。
操作视图
创建视图
CREATE VIEW 视图名称
AS
查询语句查看视图数据和视图明细
select * from 视图名称;
DESCRIBE A_cache；--查看表结构
SHOW CREATE VIEW A_cache ; --查看视图创建的详细信息
SHOW TABLE STATUS LIKE 'A_cache';--查看视图属性信息更新视图数据
就相当于更新表，删除表和新增表.
更新数据最好操作基表的数据。
--比如更新视图数据，就是UPDATE
UPDATE 视图名称
SET 列名
WHERE [条件]删除视图
DROP VIEW 视图名称存储过程
对一组SQL的封装。
执行过程
存储过程预先存储在Mysql服务器上，需要执行的时候，客户只需要向服务器发出调用存储过程的命令，然后运行一系列SQL
好处
简化操作，提高SQL重用行
减少失误
减少SQL语句在网络上的传输
减少SQL语句暴露的危险
和视图的对比
视图是操作虚拟表，存储过程是直接操作底层数据
都安全清晰，提高sql的重用性，和减少SQL的网络传输
存储过程的参数类型
没有参数(无参数无返回)
仅仅带in(有参数无返回)
仅仅带out(无参数有返回)
既带in又带out(有参数有返回)
带inout(有参数有返回)
DELIMITER $ --$这个符号开始
CREATE PROCEDURE 存储过程名(in|out|inout 参数名 参数类型...)
BEGIN
END $ --这个符号结束
DELIMITER ; --初始化开始符号存储函数
除了有系统函数还有自定义函数
语法
CREATE FUNCTION 函数名(参数名 参数类型...)
RETURNS 返回值类型
BEGIN
函数体 --在函数体中RETURN
END触发器
据库由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。
如果用户触发了某种时间，就会激发触发器的相应操作。
通常用作当新增/修改/删除等逻辑时，自动执行其他的一些逻辑。
语法
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} 表名
FOR EACH ROW
触发器执行的语句块;
--当操作某个表时 自动触发相应的语句块查看删除触发器
SHOW TRIGGERS; --查询所有的触发器
SHOW CREATE TRIGGER 触发器名; --查询触发器的创建信息
SELECT * from information_schema.TRIGGERS; --查询所有的触发器
DROP TRIGGER IF EXISTS 触发器名称；--删除触发器名称MySQL的数据管理
外键（了解即可）
方式一 在创建表的时候，增加约束（麻烦，比较复杂）
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生的 grade 列 引用 年级表的 id 列 （约束）
/*
1、定义外键key KEY `FK_gradeid`(`gradeid`)
2、给这个外键添加约束（执行引用 REFERENCES） CONSTRAINT `FK_gradeid`
*/
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` varchar(100) DEFAULT NULL COMMENT '家庭地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gradeid`(`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
注意：删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）
方式二 创建表成功后，添加外键约束
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
DROP TABLE student
-- 学生的 grade 列 引用 年级表的 id 列 （约束）
/*
1、定义外键key KEY `FK_gradeid`(`gradeid`)
2、给这个外键添加约束（执行引用 REFERENCES） CONSTRAINT `FK_gradeid`
*/
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` varchar(100) DEFAULT NULL COMMENT '家庭地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 创建表的时候，没有外键关系
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 引用外键的表(被引用的字段)
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰，这里了解即可）
最佳实践：
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 我们想使用多张表的数据，想使用外键（程序去实现）
DML语言（全部记住）也是不可回滚的。
但是如果在执行DML之前，执行了SET autocommit=FALSE ,则执行的DML操作就可以实现回滚。
数据库的意义：数据存储，数据管理
DML语言：数据操作语言
- insert
- update
- delete
添加
-- 插入语句（添加）
-- INSERT INTO 表名([字段名1，字段名2，字段名3]) VALUES('值1'),('值2'),)'值3')...
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 由于主键自增，我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade` VALUES('大四')
-- 一般写插入语句，我们一定要数据和字段一一对应！
-- 插入多个字段
INSERT INTO `grade`(`gradename`)
VALUES('大一'),('大二')
INSERT INTO `student`(`name`) VALUES('小舒')
INSERT INTO `student`(`name`,`pwd`,`sex`,`birthday`,`address`,`email`)
VALUES('小雨','111','男','2000-8-10','云南','1795386527@qq.com'),('小蓝','111','女','2000-8-10','云南','1795386527@qq.com')
语法：INSERT INTO 表名([字段名1，字段名2，字段名3]) VALUES(‘值1’),(‘值2’),)‘值3’)…
注意：
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的，但是后面的值必须要一一对应，不能少
- 可以同时插入多条数据，VALUES后面的值需要使用英文逗号隔开 (),()
修改
-- 修改学员的名字,带了条件
UPDATE `student` SET `name`='xiaoshu' WHERE id=1
-- UPDATE `student` SET `name`='xiaoshu' WHERE id>=2 2及以后的都会受到影响
-- 不指定条件，会改动所有的表
UPDATE `student` SET `name`='xiaoyu'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='xiaoshu',`email`='1795386527@qq.com' WHERE id=1
语法：UPDATE 表名 SET column_name = value,[column_name = value … ] WHERE [条件]
条件：where 子句 运算符 id等于某个值，大于某个值，在某个区间内修改
注意：
- column_name是数据库的列，尽量带上``
- 条件，筛选的条件，如果没有指定则会修改所有的列
- value，是一个具体的值，也可以是一个变量
- 多个设置的属性之间，使用英文逗号隔开
删除
DELETE命令
语法：delete from 表名 [where 条件]
-- 删除数据(避免这样写，会全部删除)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id = 1TRUNCATE 命令
作用：完全清空一个数据库表，表的结构和索引约束不会变
-- 清空 student 表
TRUNCATE `student`DELETE 和 TRUNCATE 的区别
- 相同点：都能删除数据，都不会删除表结构
- 不同：
- TRUNCATE 重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
了解即可：
delete删除的问题，重启数据库，现象
- INNODB 自增列会从1开始（存在内存中的，断电即失）
- MYISAM 继续从上一个自增量开始（存在文件中的，不会丢失）
DQL查询数据（重点）
DQL（Data Query Language）：数据查询语言
所有的查询操作都用它 SELECT。简单的查询，复杂的查询它都能做，数据库中最核心的语言，最重要的语句,使用频率最高的语句
select 完整语法(顺序不能变！)
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
注意：
[]括号代表可选的，{}括号代表必选的
select小结
- select 去重 要查询的字段 from 表 （注意：表和字段可以取别名）
- XXX join 要连接的表 on 等值判断
- where （具体的值，子查询语句）
- group by （通过哪个字段来分组）
- having （过滤分组后的信息，条件和where是一样的，位置不同）
- order by （通过哪个字段排序，ASC、DESC）
- limit startIndex，pageSize
查询：跨表、跨数据库 ——》业务层面
测试数据
DROP DATABASE IF EXISTS `school`;
-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
-- 使用school数据库
USE `school`;
-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`student_no` INT(4) NOT NULL COMMENT '学号',
`login_pwd` VARCHAR(20) DEFAULT NULL,
`student_name` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`grade_id` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话',
`address` VARCHAR(255) NOT NULL COMMENT '地址',
`born_date` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号',
`identity_card` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`student_no`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`grade_id`)
) ENGINE=INNODB DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subject_no`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subject_name` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`class_hour` INT(4) DEFAULT NULL COMMENT '学时',
`grade_id` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subject_no`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`student_no` INT(4) NOT NULL COMMENT '学号',
`subject_no` INT(4) NOT NULL COMMENT '课程编号',
`exam_date` DATETIME NOT NULL COMMENT '考试日期',
`student_result` INT (4) NOT NULL COMMENT '考试成绩'
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`student_no`,`login_pwd`,`student_name`,`sex`,`grade_id`,`phone`,`address`,`born_date`,`email`,`identity_card`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1002,'123456','张小雨',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
-- 插入学生数据
INSERT INTO `student` (`student_no`,`login_pwd`,`student_name`,`sex`,`grade_id`,`phone`,`address`,`born_date`,`email`,`identity_card`)
VALUES
(1002,'123456','张小雨',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234');
-- 插入年级数据
INSERT INTO `grade` (`grade_id`,`grade_name`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 插入科目数据
INSERT INTO `subject`(`subject_no`,`subject_name`,`class_hour`,`grade_id`)
VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
-- 插入成绩数据 这里仅插入了一组，其余自行添加
INSERT INTO `result`(`student_no`,`subject_no`,`exam_date`,`student_result`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58),
(1001,6,'2013-11-11 16:00:00',85);
语法：SELECT 字段… FROM 表
有的时候，列名字不是那么的见名知意。我们一般会起别名 AS 字段名 或 as 别名 或 表名 as 别名
去重 distinct
作用：去除SELECT语句查询出来的结果中重复的数据，重复的数据只显示一条
-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `student_no` FROM result -- 查询一下有哪些同学参加了考试
SELECT DISTINCT `student_no` FROM result -- 发现重复数据，去重
数据库的列（表达式）
SELECT VERSION() -- 查看系统的版本（函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）
-- 学员考试成绩 +1分 查看
SELECT student_no AS '学生编号',student_result+1 AS '提分后' FROM result数据库中的表达式：文本值，列，Null ，函数，计算表达式，系统变量
指定查询字段
-- 查询全部学生 SELECT 字段 FROM 表
SELECT * FROM student
SELECT * FROM result
-- 查询指定字段
SELECT `student_no`,`student_name` FROM student
-- 别名，给查询结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `student_no` AS 学号,`student_name` AS 学生姓名 FROM student AS s
-- 函数 CONCAT(a,b) 拼接字符串
SELECT CONCAT('姓名：',student_name) AS 学生名单 FROM student
where 条件子句
作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成！结果都是
-
逻辑运算符 尽量使用英文字母
| 运算符 | 语法 | 描述 |
| and && | a and b a && b | 逻辑与 |
| or || | a or b a || b | 逻辑或 |
| not ！ | not a !a | 逻辑非 |
-- WHERE
SELECT `student_no`,`student_result` FROM result
-- AND &&
-- 查询考试成绩在 95~100分之间
SELECT `student_no`,`student_result` FROM result
WHERE `student_result` >= 95 AND `student_result` <= 100
-- WHERE `student_result` BETWEEN 95 AND 100
SELECT `student_no`,`student_result` FROM result
WHERE `student_result` >= 95 && `student_result` <= 100
-- 模糊查询（区间）
SELECT `student_no`,`student_result` FROM result
WHERE `student_result` BETWEEN 95 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT `student_no`,`student_result` FROM result
WHERE `student_no`!=1000;
-- NOT
SELECT `student_no`,`student_result` FROM result
WHERE NOT `student_no`=1000;
-
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
| IS NULL | a is null | 如果操作符为NULL，结果为真 |
| IS NOT NULL | a is not null | 如果操作符不为NULL，结果为真 |
| BETWEEN | a between b and c | 若a在b和c之间，则结果为真 |
| like | a like b | SQL匹配，如果a匹配b，则结果为真（ 小红 like 小XX） |
| in | a in （a1，a2，a3…） | 假设a在a1，或者a2…其中的某一个值中，那结果为真 |
-- 模糊查询
-- 查询姓张的同学
-- like 结合 %(代表0到任意个字符) _(代表一个字符)
SELECT `student_no`,`student_name` FROM student
WHERE `student_name` LIKE '张%'
-- 查询姓张的同学，名字后面只有一个字的
SELECT `student_no`,`student_name` FROM student
WHERE `student_name` LIKE '张_'
-- 查询姓张的同学，名字后面有两个字的
SELECT `student_no`,`student_name` FROM student
WHERE `student_name` LIKE '张__'
-- 查询名字中有小字的同学
SELECT `student_no`,`student_name` FROM student
WHERE `student_name` LIKE '%小%'
-- IN 具体的一个或者多个值
-- 查询1001号、1002号学员
SELECT `student_no`,`student_name` FROM student
WHERE `student_no` IN(1001,1002)
-- 查询在北京朝阳的学生
SELECT `student_no`,`student_name` FROM student
WHERE `address` IN('北京朝阳')
-- 查询多个地区的学生
SELECT `student_no`,`student_name` FROM student
WHERE `address` IN('北京朝阳','广东深圳')
-- null not null
-- 地址为空的学生 NULL ''
SELECT `student_no`,`student_name` FROM student
WHERE `address`='' OR `address` IS NULL
-- 查询有出生日期的同学 不为空
SELECT `student_no`,`student_name` FROM student
WHERE `born_date` IS NOT NULL
-- 查询没有出生日期的同学 为空
SELECT `student_no`,`student_name` FROM student
WHERE `born_date` IS NULL
联表查询
-- 联表查询 JOIN 一般正常查询都用INNER JOIN
-- JOIN(连接的表) ON(判断条件) 连接查询 语法
-- JOIN where 等值查询
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*
思路：
1、分析需求，分析查询的字段来自哪些表（连接查询）
2、确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中的 student_no = 成绩表中的 student_no
*/
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
INNER JOIN result AS r
ON s.student_no = r.student_no
-- RIGHT JOIN
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
RIGHT JOIN result AS r
ON s.student_no = r.student_no
-- LEFT JOIN
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
LEFT JOIN result AS r
ON s.student_no = r.student_no
-- 查询缺考的同学
-- LEFT JOIN
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
LEFT JOIN result AS r
ON s.student_no = r.student_no
WHERE student_result IS NULL
-- 思考题(查询了参加考试的同学信息：学号，学生姓名，科目名，分数)
/*
思路：
1、分析需求，分析查询的字段来自哪些表,student、result、subject（连接查询）
2、确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中的 student_no = 成绩表中的 student_no
*/
-- 1、先将student表和result表进行连接成一张表
SELECT stu.student_no,student_name,subject_no,student_result
FROM student AS stu
RIGHT JOIN result AS res
ON stu.student_no = res.student_no
-- 2、再将上面的合成表与subject表进行连接
SELECT stu.student_no,student_name,subject_name,student_result
FROM student AS stu
RIGHT JOIN result AS res
ON stu.student_no = res.student_no
INNER JOIN `subject` AS sub
ON res.subject_no = sub.subject_no
-- 我们要查询哪些数据 SELECT
-- 从哪几个表中查 FROM 表 XXX JOIN 连接的表 ON 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
-- FROM a LEFT JOIN b 以左边的a表为基准来查
-- FROM a LEFT JOIN b 以右边的b表为基准来查
| 操作 | 描述 |
| INNER JOIN | 如果表中至少有一个匹配，就返回行 |
| LEFT JOIN | 会从左表中返回所有的值，即使右表中没有匹配 |
| RIGHT JOIN | 会从右表中返回所有的值，即使左表中没有匹配 |
分页和排序
排序（ORDER BY）
-- 排序：升序 ASC 降序 DESC(DESC还有一个作用就是显示表结构：DESC 表名)
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果根据 成绩升序排序(降序：DESC)
SELECT s.`student_no`,`student_name`,`subject_name`,`student_result`
FROM student AS s
INNER JOIN result AS r
ON r.student_no=s.student_no
INNER JOIN `subject`
ON r.subject_no=`subject`.subject_no
WHERE `subject_name`='高等数学-2' OR `subject_name`='C语言-1'
ORDER BY student_result ASC
分页（LIMIT）
-- 分页
-- 100万条数据
-- 为什么要排序？ 1、缓解数据库压力 2、给人的体验更好 瀑布流：百度搜素图片时无限刷新
-- 分页，每页只显示1条数据
-- 语法: LIMIT 起始值，页面大小（每页数据的总量）
-- 网页应用：当前，总的页数
-- LIMIT 0,4 1~4
-- LIMIT 1,4 2~5
SELECT stu.student_no,student_name,subject_name,student_result
FROM student AS stu
RIGHT JOIN result AS res
ON stu.student_no = res.student_no
INNER JOIN `subject` AS sub
ON res.subject_no = sub.subject_no
ORDER BY student_result DESC
-- LIMIT 0,4
LIMIT 4,4
-- 第一页 LIMIT 0,5
-- 第二页 LIMIT 5,5
-- 第三页 LIMIT 10,5
-- 第n页 LIMIT 5(n-1),5 --》总结：(n-1)*pageSize,pageSize pageSize:页面大小 n ：当前页 (n-1)*pageSize：起始值
-- 总页数=数据总数/页面大小
语法：语法: LIMIT 查询起始下标，页面大小（每页数据的总量：pageSize）
思考：查询 java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号、姓名、课程名称、分数）
SELECT s.`student_no`,`student_name`,`subject_name`,`student_result`
FROM student AS s
INNER JOIN result AS r
ON r.student_no=s.student_no
INNER JOIN `subject`
ON r.subject_no=`subject`.subject_no
WHERE `subject_name`='java第一学年' AND `student_result`>80
ORDER BY student_result DESC
LIMIT 0,10
子查询和嵌套查询
本质：在where语句中嵌套一个子查询语句，where（select * from ）
-- WHERE
-- 1、查询 C语言-2 的所有考试结果（学号，科目编号，成绩），通过成绩降序排列
-- 方式一： 使用连接查询
SELECT s.`student_no`,sub.`subject_no`,`student_result`
FROM student AS s
INNER JOIN result AS r
ON s.student_no=r.student_no
INNER JOIN `subject` AS sub
ON r.subject_no=sub.subject_no
WHERE `subject_name`='C语言-2'
ORDER BY `student_result` DESC
-- 方式二： 使用子查询（由里及外）
SELECT `student_no`,`subject_no`,`student_result`
FROM result AS r
WHERE subject_no=(
-- 查询 C语言-2 的编号
-- SELECT subject_no FROM `subject` WHERE subject_name='C语言-2'
SELECT subject_no
FROM `subject`
WHERE subject_name='C语言-2'
)
ORDER BY `student_result` DESC
-- 查询课程为 高等数学-2 且分数不小于 70 的同学的学号和姓名
-- 方式一：
SELECT DISTINCT student.`student_no`,`student_name`
FROM student
INNER JOIN result
ON student.student_no=result.student_no
INNER JOIN `subject` sub
ON result.subject_no=sub.subject_no
WHERE student_result>=70 AND subject_name='高等数学-2'
-- 方式二：
-- 1、分数不小于70分的学生的学号和姓名（学号，姓名）
SELECT DISTINCT student.`student_no`,`student_name`
FROM student
INNER JOIN result
ON student.student_no=result.student_no
WHERE student_result>=70
-- 2、在这个基础上增加一个科目，高等数学-2
SELECT DISTINCT student.`student_no`,`student_name`
FROM student
INNER JOIN result
ON student.student_no=result.student_no
WHERE student_result>=70 AND subject_no=(
-- 查询 高等数学-2 的编号
-- SELECT subject_no FROM `subject` WHERE subject_name='高等数学-2'
SELECT subject_no FROM `subject`
WHERE subject_name='高等数学-2'
)
-- 再改造:嵌套查询 查出来是多个值用IN，不能用等号
SELECT student_no,student_name FROM student WHERE student_no IN(
SELECT student_no FROM result WHERE student_result>=70 AND subject_no=(
-- 查询 高等数学-2 的编号
-- SELECT subject_no FROM `subject` WHERE subject_name='高等数学-2'
SELECT subject_no
FROM `subject`
WHERE subject_name='高等数学-2'
)
)
分组及过滤
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT subject_name,AVG(student_result) AS 平均分,MAX(student_result) AS 最高分,MIN(student_result) AS 最低分
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subject_no=sub.subject_no
GROUP BY r.subject_no -- 通过什么字段来分组
HAVING 平均分>80 -- 过滤条件
MySQL函数
-
单行函数
注意MYSQL下标是从1开始，JAVA是从0
-
数值函数
ABS(x) 返回x的绝对值
SIGN(X) 返回X的符号。正数返回1，负数返回-1，0返回0
PI() 返回圆周率的值
CEIL(x)，CEILING(x) 返回大于或等于某个值的最小整数
FLOOR(x) 返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…) 返回列表中的最小值
GREATEST(e1,e2,e3…) 返回列表中的最大值
MOD(x,y) 返回X除以Y后的余数
RAND() 返回0~1的随机值
RAND(x) 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x) 返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y) 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y) 返回数字x截断为y位小数的结果
SQRT(x) 返回x的平方根。当X的值为负数时，返回NULLSELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;-
字符串函数
UPPER(列|字符串) 将字符串每个字符转为大写
LOWER(列|字符串) 将字符串每个字符转为小写
CONCAT(str1，str2，. . . ) 将所有字符串连接成一个字符串
CONCAT_WS(分隔符，str1，str2，. . . ) 将所有字符串通过分隔符连接
REPLACE(列|字符串，旧字符串，新字符串) 使用新字符串替换旧字符串
INSERT(列|字符串，开始位置，位移几位，新字符串) 使用新字符串替换旧字符串
LENGTH(列|字符串) 求字符串底层的字节长度
CHAR_LENGTH(列|字符串) 求字符串底层的字符长度
SUBSTR(列|字符串，开始点[，长度]) 字符串截取
LEFT(str，len) 获取字符串左边len个字符组成的字符串
RIGHT(str，len) 获取字符串右边len个字符组成的字符串
MID(str，pos，len) 获取字符串中从pos(第几个)位置开始，长度为len的字符串
ASCII(字符) 返回与指定字符对应的十进制整数
CHR(数字) 返回与整数对应的字符
RPAD(列|字符串，长度，填充字符) 用指定的字符在字符串左填充
LPAD(列|字符串，长度，填充字符) 用指定的字符在字符串右填充
LTRIM(字符串)、RTRIM(字符串) 去掉字符串左或右的空格
TRIM(列|字符串) 去掉字符串左右空格
TRIM(需要去除的字符串 FROM 列|字符串) 去掉字符串左右需要去除的字符串
INSTR(列|字符串，要查找的字符串，开始位置，出现位置) 查找一个子字符串是否在指定的位置上出现
LOCATE(需要查找的值，列||字符串) 从值中查找对应的值，返回第一次出现的下标，不存在返回0select
ASCII('B'),LENGTH('我们'),CHAR_LENGTH('我们'),LENGTH('hello'),CHAR_LENGTH('hello'),
CONCAT('A','C','B'),CONCAT_WS('a','A','C','B'),
INSERT('Helloworld',3,2,'wwww'),REPLACE('helloworld','ow','OW'),
LPAD('100',10,' '),RPAD('100',10,' '),
LOCATE('a','aabbccddss')
from dual;
-
日期和时间函数
获取
NOW() /SYSDATE()：获取当前日期时间
CURDATE()/CURRENT_DATE ：获取当前的日期
CURTIME() /CURRENT_TIME：获取当前的时间
YEAR(date)/MONTH(date)/DAY(date)： 获取日期中的年月日
HOUR(time)/MINUTE(time)/SECOND(time)：获取时间的时分秒
MONTHNAME(date)：获取月份，英文：January
DAYNAME(date)：获取星期几，英文：MONDAY，TUESDAY....SUNDAY
WEEKDAY(date)：获取周几，数字，周1是0，周日是6
DAYOFWEEK(date)：获取周几,数字，周天是1，周六是7
QUARTER(date)：获取季度，1-4
WEEK(date)/WEEKOFYEAR(date)：获取一年中的第几周
DAYOFYEAR(date)：一年中的第几天
DAYOFMONNTH(date)：日期所处月份的第几天
操作
EXTARCT(unit FROM date)：通过设置不同参数来获取
转换
TIME_TO_SEC(TIME)：时间转成秒数，公式等于 小时*3600+分钟*60+秒
SEC_TO_TIME(TIME)：秒数转成时间
计算
模式1：
DATE_ADD(datetime,INTERVAL 【num】type)/ADDDATE(datetime,INTERVAL 【num】 type)：加上对应的参数
DATE_SUB()/SUBDATE() 减去对应的参数
模式2：
ADDTIME(time1,time2)：对应日期加上time2,如果是纯数字代表秒。当time2是：'1:1:3'，time1则加上1小时，1分钟，3秒
SUBTIME(time,time)：和add差不多，这个是减去
DATEDIFF(date1,date2)：返回date1-date2的日期相差的天数
TIMEDIFF(time1,time2)：返回time1-time2的时间间隔
FROM_DAYS(N)：返回从0000年1月1日起，第N天的日期
TO_DAYS(date)：返回date举例0000年1月1日多少天
LAST_DAY(date)：返回这个月的最后一天
MAKEDATE(year,n)：返回给出年份，从1月1日起n天后的日期
MAKETIME(hour,minute,second)：将给定的小时，分钟，秒数拼接返回
PERIOD_ADD(time,n)：返回time加上n秒后的时间
格式化
DATE_FORMAT(date,fmt)：按照字符串fmt格式转化成date
TIME_FORMAT(time,fmt)：抓化成time
GET_FORMAT(date_type,format_type)：返回日期的显示格式
STR_DATE(str,fmt)：将字符串解析成一个date流程控制函数
IF(条件,value1,value2)：判断语句条件成立value1,否则返回value2
IFNULL(value,value1)：value是null时返回value1
CASE WHEN 条件1 THEN... WHEN 条件2 THEN...ELSE...END：相当于java的if..else if...
CASE value WHEN value1 THEN... WHEN value2 THEN...END : 相当于 switch() case;
加密函数
MD5(str) 不可逆，固定值
SHA(str) 不可逆，固定值多行函数
聚合函数的介绍
聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。
常用的聚合函数
- count(col): 表示求指定列的总行数
- max(col): 表示求指定列的最大值
- min(col): 表示求指定列的最小值
- sum(col): 表示求指定列的和
- avg(col): 表示求指定列的平均值
求总行数
-- 返回非NULL数据的总行数.
select count(height) from students;
-- 返回总行数，包含null值记录;
select count(*) from students;求最大值
-- 查询女生的编号最大值
select max(id) from students where gender = 2;求最小值
-- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;求和
-- 查询男生的总身高
select sum(height) from students where gender = 1;
-- 平均身高
select sum(height) / count(*) from students where gender = 1;求平均值
-- 求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
-- 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;说明
ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。
聚合函数的特点
聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做
事务
事务是一组原子性的sql语句，或者说是一个独立的工作单元。
事务有四个特性：
- 原子性(Atomicity)
- 一致性(Consistency)
- 隔离型(Isolation)
- 持久性(Durability)
基础
什么是事务？
要么都成功，要么都失败
核心：将一组SQL放在一个批次中去执行
——————
SQL执行 A给B转账 A 1000 ——>200 B 200
SQL执行 B收到A的钱 A 800 ——> B 400
——————
事务原则：ACID原则 原子性，一致性，隔离性，持久性 （脏读、幻读…）
-
原子性表示，这两个步骤一起成功，或者一起失败，不能只发生一个动作
-
一致性表示，事务前后的数据完整性要保证一致（A和B的总的钱转账前后一定1200）
-
持久性表示，事务结束后的数据不随着外界原因（服务器宕机或者断电）导致数据丢失（事务没有提交，恢复到原状；事务已经提交，持久化到数据库），即事务一旦提交就不可逆，被持久化到数据库中了
-
隔离性表示，针对多个用户同时操作，主要是排除其他事务对本次事务的影响
-
脏读： 一个事务读取到了另一个事务未提交的数据操作结果。这是相当危险的，因为很可能所有的操作都被回滚。
-
不可重复读： 不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。
-
包括以下情况：
（1） 虚读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。
（2） 幻读（Phantom Reads）：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺 少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成 的。
-
执行事务
-- 事务
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 -- 关闭自动提交
SET autocommit = 1 -- 开启（默认的）
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的SQL都在同一个事务内
INSERT XXX
INSERT XXX
-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到原来的样子（失败！）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
事务的实现
①原子性的实现
什么是原子性
一个事务必须被视为不可分割的最小工作单位，一个事务中的所有操作要么全部成功提交，要么全部失败回滚，对于一个事务来说不可能只执行其中的部分操作，这就是事务的原子性。
undo log 的生成
每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上
所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete，update的逆向为update等。
根据undo log 进行回滚回滚操作就是要还原到原来的状态，undo log记录了数据被修改前的信息以及新增和被删除的数据信息，根据undo log生成回滚语句，比如：
(1) 如果在回滚日志里有新增数据记录，则生成删除该条的语句
(2) 如果在回滚日志里有删除数据记录，则生成生成该条的语句
(3) 如果在回滚日志里有修改数据记录，则生成修改到原先数据的语句
②持久性的实现
事务一旦提交，其所作做的修改会永久保存到数据库中，此时即使系统崩溃修改的数据也不会丢失。
MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘IO,然而即使是使用SSD磁盘IO也是非常消耗性能的。为此，为了提升性能InnoDB提供了缓冲池(Buffer Pool)，Buffer Pool中包含了磁盘数据页的映射，可以当做缓存来使用：
读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；
写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中
③隔离性实现
隔离性是事务ACID特性里最复杂的一个。在SQL标准里定义了四种隔离级别，每一种级别都规定一个事务中的修改，哪些是事务之间可见的，哪些是不可见的。级别越低的隔离级别可以执行越高的并发，但同时实现复杂度以及开销也越大。
Mysql 隔离级别有以下四种（级别由低到高）：
READ UNCOMMITED (未提交读)
READ COMMITED (提交读)
REPEATABLE READ (可重复读)
SERIALIZABLE (可重复读)
只要彻底理解了隔离级别以及他的实现原理就相当于理解了ACID里的隔离型。前面说过原子性，隔离性，持久性的目的都是为了要做到一致性，但隔离型跟其他两个有所区别，原子性和持久性是为了要实现数据的可性保障靠，比如要做到宕机后的恢复，以及错误后的回滚。
redo log 与 undo log介绍
①redo log
redo log叫做重做日志，用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中。
redo log 有什么作用？
mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。
总结
redo log是用来恢复数据的用于保障，已提交事务的持久化特性
②undo log
undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。
undo log 有什么作用？
undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。
总结
undo log是用来回滚数据的用于保障未提交事务的原子性
索引
索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。索引是关系数据库中对某一列或多个列的值进行预排序的数据结构，在 MySQL 中也被称为 Key。
通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
注意事项：有序性是因为一切二分法查找法都要求数据已经是排好顺序的。如果把索引看做 key（虽然 key 数据也是来自于表单中一行记录的某些字段值），那么 value 在 MyISAM 中就是记录的在存储文件中的地址，而在 InnoDB 中 value 直接就是对应的一行数据。
基础
假设我们有一张数据表 workers(员工表)，该表有三个字段（列）,分别是name、age 和address。假设表works有上万行数据，现在需要从这个表中查找出所有名字是‘ZhangSan’的雇员信息，你会快速的写出SQL语句：
select name,age,address from workers where name='ZhangSan'如果数据库还没有索引这个东西，一旦我们运行这个SQL查询，查找名字为ZhangSan的雇员的过程中，究竟会发生什么？数据库不得不在workes表中的每一行查找并确定雇员的名字（name）是否为‘ZhangSan’。
由于我们想要得到每一个名字为ZhangSan的雇员信息，在查询到第一个符合条件的行后，不能停止查询，因为可能还有其他符合条件的行，所以必须一行一行的查找直到最后一行——这就意味数据库不得不检查上万行数据才能找到所有名字为ZhangSan的雇员。这就是所谓的全表扫描，显然这种模式效率太慢。
使用索引的全部意义就是：通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（定义真特么拗口）。大白话意思是索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。
请牢记这一点：索引是一种数据结构。
作用
数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。索引所起的作用类似书籍目录，可用于快速定位、检索数据。索引对于提高数据库的性能有很大的帮助。
使用场景
索引的本质实际上还是存储在磁盘上的数据结构，它可以有的存储结构：
- 二叉搜索树；
- 红黑树；
- Hash 表；
- B-Tree；
- B+Tree；
其中 MySQL 的 InnoDB 支持 B+Tree 以及 Hash 表，下面会具体分析各个数据结构的区别。
索引的优势
（1）提高查询效率（降低IO使用率）
（2）降低CPU使用率
比如查询order by age desc，因为B+索引树本身就是排好序的，所以再查询如果触发索引，就不用再重新查询了。
索引的分类
（1）单值索引
（2）唯一索引
（3）联合索引
（4）主键索引
唯一索引和主键索引唯一的区别：主键索引不能为null
在一个表中，主键索引只能有一个，唯一索引可以有多个
全文索引（FULLTEXT），在特定的数据库引擎下才有，比如MYISAM
MySQL索引原理 -> B+树
MySQL索引的底层数据结构是B+树
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
B-Tree结构图中每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
B+Tree相对于B-Tree有几点不同
- 非叶子节点只存储键值信息。
- 所有叶子节点之间都有一个链指针。
- 数据记录都存放在叶子节点中。
- 将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。
索引使用
创建索引
alter table user add INDEX `user_index_username_password` (`username`,`password`)索引测试
快速定位数据
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加全文索引（索引名） 列名
ALTER TABLE school.`student` ADD FULLTEXT `student_name`(`student_name`)
-- EXPLAIN 分析SQL执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(student_name) AGAINST('张')
测试索引
-- 测试索引
CREATE TABLE app_user (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男 1：女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
-- 插入100万数据(函数)
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'123345@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
-- 执行函数
SELECT mock_data();
SELECT * FROM app_user WHERE `name`= '用户9999' -- 查询时间: 0.493s
SELECT * FROM app_user WHERE `name`= '用户9999' -- 查询时间: 0.485s
SELECT * FROM account -- 查询时间: 0s
EXPLAIN SELECT * FROM app_user WHERE `name`= '用户9999'
-- id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`)
-- 创建索引后查询
SELECT * FROM app_user WHERE `name`= '用户9999' -- 查询时间: 0.009s
EXPLAIN SELECT * FROM app_user WHERE `name`= '用户9999'
未创建索引
创建索引后
索引在小数据量的时候用处不大，但是在大数据的时候，区别十分明显
索引创建原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
如何触发联合索引
1、对user表建立联合索引username、password
2、触发联合索引
（1）使用联合索引的全部索引键可触发联合索引
（2）使用联合索引的全部索引键，但是用or连接的，不可触发联合索引
（3）单独使用联合索引的左边第一个字段时，可触发联合索引
（4）单独使用联合索引的其它字段时，不可触发联合索引
分析sql的执行计划-explain
explain可以模拟sql优化执行sql语句。
explan使用简介
（1）用户表
（2）部门表
（3）未触发索引
（4）触发索引
（5）结果分析
explain中第一行出现的表是驱动表。
- 指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]
- 未指定联接条件时，行数少的表为[驱动表]
对驱动表直接进行排序就会触发索引，对非驱动表进行排序不会触发索引。
explain查询结果简介
（1）id：SELECT识别符。这是SELECT的查询序列号。
（2）select_type：SELECT类型：
- SIMPLE： 简单SELECT(不使用UNION或子查询)
- PRIMARY： 最外面的SELECT
- UNION：UNION中的第二个或后面的SELECT语句
- DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
- UNION RESULT：UNION的结果
- SUBQUERY：子查询中的第一个SELECT
- DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
- DERIVED：导出表的SELECT(FROM子句的子查询)
（3）table：表名
（4）type：联接类型
- system：表仅有一行(=系统表)。这是const联接类型的一个特例。
- const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
- eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
- ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或操作符的带索引的列。
- ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
- index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
- unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
- index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range
- index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
- all：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。
（5）possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
（6）key：key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
（7）key_len：key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。
（8）ref：ref列显示使用哪个列或常数与key一起从表中选择行。
（9）rows：rows列显示MySQL认为它执行查询时必须检查的行数。
（10）Extra：该列包含MySQL解决查询的详细信息。
- Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
- Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
- range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
- Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
- Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
- Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
- Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。
- Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描。
- Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。
通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。
回表
简单来说就是where没有索引得列，然后先根据列查索引，然后根据索引拿叶子节点得数据。这种情况多出现在非聚集索引，因为聚集索引本身就处于非叶子节点上无需回表。
但是并不是所有得非聚集索引都需要回表，比如复合索引，他的叶子节点是除了索引值外，还包含列得值，如果select中都是这里面得列，就不用回表。实在不懂就继续往下看：
索引结构
要搞明白这个问题，需要大家首先明白 MySQL 中索引存储的数据结构。这个其实很多小伙伴可能也都听说过，B+Tree 嘛！
B+Tree 是什么？那你得先明白什么是 B-Tree，来看如下一张图：
前面是 B-Tree，后面是 B+Tree，两者的区别在于：
- B-Tree 中，所有节点都会带有指向具体记录的指针；B+Tree 中只有叶子结点会带有指向具体记录的指针。
- B-Tree 中不同的叶子之间没有连在一起；B+Tree 中所有的叶子结点通过指针连接在一起。
- B-Tree 中可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定；B+Tree 中，一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定。
基于上面两点分析，我们可以得出如下结论：
- B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。
- B+Tree 中，叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动。
对于第一点，一个 B+Tree 可以存多少条数据呢？以主键索引的 B+Tree 为例（二级索引存储数据量的计算原理类似，但是叶子节点和非叶子节点上存储的数据格式略有差异），我们可以简单算一下。
计算机在存储数据的时候，最小存储单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）最小单元是块，一个块的大小是 4KB。InnoDB 引擎存储数据的时候，是以页为单位的，每个数据页的大小默认是 16KB，即四个块。
基于这样的知识储备，我们可以大致算一下一个 B+Tree 能存多少数据。
假设数据库中一条记录是 1KB，那么一个页就可以存 16 条数据（叶子结点）；对于非叶子结点存储的则是主键值+指针，在 InnoDB 中，一个指针的大小是 6 个字节，假设我们的主键是 bigint ，那么主键占 8 个字节，当然还有其他一些头信息也会占用字节我们这里就不考虑了，我们大概算一下，小伙伴们心里有数即可：
假设每条记录的大小为 1KB（1024 字节），而每个页面的大小是 16KB（16,384 字节）。
叶子节点（存储数据条目）
16,384字节 /1024字节=16条记录
非叶子节点（存储索引条目）
每个非叶子结点的条目包含主键（8 字节）和指针（6 字节），因此每个条目的大小为：
8字节（主键）+6字节（指针）=14字节/条
所以，每个非叶子结点的页面可以存储的条目数为：
16,384字节/14字节=1,169条索引
因此，每个非叶子节点的页面最多存储 1,169 条索引条目。
B+Tree 层数存储量计算
第一层（根节点）：根节点通常只有一个页面（除非数据量过大），它可以存储最多 1,169 条索引条目，即根节点最多指向 1,169 个子页面。
第二层（非叶子节点）：
第二层的每个非叶子节点最多指向 1,169 个子节点。
如果根节点有 1,169 条索引条目，那么第二层一共有 1,169 个非叶子节点，它们每个指向 1,169 个子页面。
第二层最多指向 1,169 × 1,169 = 1,368,761 个页面。
第三层（叶子节点）：每个叶子节点最多存储 16 条记录，每个页面存储 16 条数据。第三层（叶子节点）最多存储：1,368,761个页面×16条记录/页=21,901,216条记录
所以结论是：
一个三层 B+Tree 能存储 21,901,216 条记录，这表示叶子节点（存储数据）的总数，而不是指每个页面的存储量。如果你想要更多层的 B+Tree，数据量会继续指数级增长。
可以存储 2100万 条数据。
在 InnoDB 存储引擎中，B+Tree 的高度一般为 2-4 层，这就可以满足千万级的数据的存储，查找数据的时候，一次页的查找代表一次 IO，那我们通过主键索引查询的时候，其实最多只需要 2-4 次 IO 操作就可以了。
大家先搞明白这个 B+Tree。
两类索引
大家知道，MySQL 中的索引有很多中不同的分类方式，可以按照数据结构分，可以按照逻辑角度分，也可以按照物理存储分，其中，按照物理存储方式，可以分为聚簇索引和非聚簇索引。
我们日常所说的主键索引，其实就是聚簇索引（Clustered Index）;主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引（Secondary Index），或者叫作辅助索引。
对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：
- 主键索引的叶子结点存储的是一行完整的数据。
- 非主键索引的叶子结点存储的则是主键值。
这就是两者最大的区别。
所以，当我们需要查询的时候：
- 如果是通过主键索引来查询数据，例如
select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。
- 如果是通过非主键索引来查询数据，例如
select * from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。
对于第二种查询方式而言，一共搜索了两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。
从上面的分析中我们也能看出，通过非主键索引查询要扫描两棵 B+Tree，而通过主键索引查询只需要扫描一棵 B+Tree，所以如果条件允许，还是建议在查询中优先选择通过主键索引进行搜索。
一定会回表吗？
那么不用主键索引就一定需要回表吗？
不一定！
如果查询的列本身就存在于索引中，那么即使使用二级索引，一样也是不需要回表的。
举个例子，我有如下一张表：
uname 和 address 字段组成了一个复合索引，那么此时，虽然这是一个二级索引，但是索引树的叶子节点中除了保存主键值，也保存了 address 的值。
我们来看如下分析：
可以看到，此时使用到了 uname 索引，但是最后的 Extra 的值为
Using index，这就表示用到了索引覆盖扫描（覆盖索引），此时直接从索引中过滤不需要的记录并返回命中的结果，这一步是在 MySQL 服务器层完成的，并且不需要回表。
扩展
我们再来捋一捋为什么在数据库中建议使用自增主键。
- 自增主键往往占用空间比较小，int 占 4 个字节，bigint 占 8 个字节。由于二级索引的叶子节点存储的就是主键，所以如果主键占用空间小，意味着二级索引的叶子节点将来占用的空间小（间接降低 B+Tree 的高度，提高搜索效率）。
- 自增主键插入的时候比较快，直接插入即可，不会涉及到叶子节点分裂等问题（不需要挪动其他记录）；而其他非自增主键插入的时候，可能要插入到两个已有的数据中间，就有可能导致叶子节点分裂等问题，插入效率低（要挪动其他记录）。
权限管理和备份
用户管理
Navicat 可视化管理
SQL命令操作
用户表：mysql.user
本质：读这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER xiaoshu IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD=PASSWORD('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR xiaoshu = PASSWORD('123456')
-- 重命名 RENAME USER 原名字 TO 新名字
RENAME USER xiaoshu TO XIAOSHU
-- 用户授权 ALL PRIVILEGES：全部的权限，库.表;除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO XIAOSHU
-- 查看权限
SHOW GRANTS FOR XIAOSHU -- GRANT ALL PRIVILEGES ON *.* TO 'XIAOSHU'@'%' 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION root用户的权限
-- 撤销权限 REVOKE 撤销哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM XIAOSHU
-- 删除用户
DROP USER XIAOSHU
MySQL备份
为什么要备份：
- 保证重要的数据不丢失
- 数据转移 A——>B
MySQL数据库备份的方式：
- 直接拷贝物理文件
- 在可视化工具中转储SQL文件
- 使用命令行导出 mysqldump cmd命令行中使用
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名.sql -- 备份一张表
C:\Users\kevin>mysqldump -hlocalhost -uroot -p123456 school student >D:a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名.sql -- 备份多张表
# mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名.sql -- 备份数据库
#导入数据库
#在登录的情况下，切换到指定的数据库
#source 备份文件
source D:/a.sql
作用：
- 假设你要备份数据库，防止数据丢失
- 把数据库给朋友，直接给sql文件即可！
数据库三大范式
为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式（1NF）原子性：保证每一列不可再分
第二范式（2NF）前提：满足第一范式
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
每张表只描述一件事情
第三范式（3NF）前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范数据库的设计
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据库降低为小数据量的查询：索引）
JDBC数据库驱动
SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范，俗称JDBC；Java数据库连接，（Java Database Connectivity，简称JDBC）。
这些规范的实现由具体的厂商去做~
对于开发人员来说，我们只需要掌握JDBC接口的操作即可！
第一个JDBC程序
-
创建测试数据库
-- 创建学习jdbc数据库
CREATE DATABASE jdbcstudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcstudy;
CREATE TABLE users(
`id` INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
`email` VARCHAR(60),
`birthday` DATE
);
INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)
VALUES(1,'张三','123456','zs@sina.com','1980-12-04'),
(2,'李四','123456','lisi@sina.com','1981-12-04'),
(3,'王五','123456','wangwu@sina.com','1982-12-04');
-
创建一个普通项目
-
导入数据库驱动（jar包）
-
编写测试代码
//jdbc程序
public class jdbcdemo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法，加载驱动
//2、用户信息和url useSSL=true:使用安全连接 serverTimezone=UTC:设置时区，不然会报时区错误
//String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
String username = "root";
String password = "123456";
//3、连接成功，返回数据库对象 Connection：代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象 Statement:执行SQL的对象
Statement statement = connection.createStatement();
//5.使用 执行SQL的对象 去执行SQL，可能存在结果，查看返回结果
String sql = "select * from users";
//查询：executeQuery() 更新：executeUpdate() 所有的删除和插入都叫更新：executeUpdate()
ResultSet resultSet = statement.executeQuery(sql); //返回的结果集,结果集中封装了全部的查询出来的结果
while (resultSet.next()){
System.out.println("id ="+resultSet.getObject("id"));
System.out.println("name ="+resultSet.getObject("name"));
System.out.println("pwd ="+resultSet.getObject("password"));
System.out.println("email ="+resultSet.getObject("email"));
System.out.println("birthday ="+resultSet.getObject("birthday"));
System.out.println("========================================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
- 加载驱动
- 连接数据库DriverManager
- 获取执行SQL的对象 Statement
- 获得返回的结果集
- 释放连接
JDBC操作事务
要么都成功，要么都失败
ACID原则
- 原子性：要么全部成功，要么全部失败
- 一致性：总数不变
- 隔离性：多个进程互不干扰
- 持久性：一旦提交不可逆，持久化到数据库了
隔离性的问题：
- 脏读：一个事务读取了另外一个没有提交的事务
- 不可重复读：在同一个事务内，重复读取表中数据，表数据发生了改变
- 幻读：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致
- 开启事务con.setAutoCommit(false);
- 一组业务执行完毕，提交事务
- 可以在catch语句中显示的定义回滚语句，但是默认失败就会回滚
正常情况提交
import com.kmu.shu.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//关闭自动提交 自动会开启事务
con.setAutoCommit(false);//开启事务
// A 转 B 100元
String sql1 = "update account set money=money-100 where name='A'";
st = con.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update account set money=money+100 where name='B'";
st = con.prepareStatement(sql2);
st.executeUpdate();
//业务完毕，提交事务
con.commit();
System.out.println("A 转 B 100元 成功！");
} catch (SQLException e) {
e.printStackTrace();
try {
con.rollback(); //如果失败就回滚事务
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
异常情况提交事务
import com.kmu.shu.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction2 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//关闭自动提交 自动会开启事务
con.setAutoCommit(false);//开启事务
// A 转 B 100元
String sql1 = "update account set money=money-100 where name='A'";
st = con.prepareStatement(sql1);
st.executeUpdate();
//int x=1/0; //报错
String sql2 = "update account set money=money+100 where name='B'";
st = con.prepareStatement(sql2);
st.executeUpdate();
//业务完毕，提交事务
con.commit();
System.out.println("A 转 B 100元 成功！");
} catch (SQLException e) {
//如果失败则会默认回滚
e.printStackTrace();
/*try {
con.rollback(); //如果失败就回滚事务
} catch (SQLException ex) {
ex.printStackTrace();
}*/
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
sql优化简介
什么情况下进行sql优化
性能低、执行时间太长、等待时间太长、连接查询、索引失效。
优化方案：
1. 性能低的优化方案：
-
主从同步和读写分离： 通过数据库的主从复制实现读写分离，主库用于写操作，从库用于读操作，减少主库的负载，提高系统整体的吞吐量。
-
Redis 缓存分流： 使用 Redis 缓存热点数据，避免频繁访问数据库，减少数据库压力，提高响应速度。
-
JVM 缓存： 使用 JVM 层的缓存机制（如
ConcurrentHashMap或Guava Cache）缓存频繁访问的数据，减少数据库查询和 IO 操作。
2. 执行时间过长的优化方案：
-
分页： 对于大数据集的查询，使用分页查询避免一次性查询大量数据。分页查询可显著减少数据库压力，提高响应速度。
-
索引优化：
-
加索引： 对查询条件频繁使用的字段（如
WHERE子句中的条件、JOIN中的连接字段等）添加索引，提升查询效率。 -
最左前缀原则： 在复合索引中，确保查询条件按照索引的最左前缀规则使用，这样才能最大限度地利用复合索引。
-
-
关联查询优化： 对于复杂的
JOIN查询，确保查询条件正确且索引得当。避免无效的JOIN或CROSS JOIN操作，确保查询高效。 -
任务拆分： 对于长时间执行的业务操作，可以考虑拆分任务并分批执行，避免一次性执行大量数据操作。
-
SQL 过滤和 Java 代码过滤： 在 SQL 层尽可能地做过滤操作（如
WHERE条件），减少数据传输和后续的 Java 层处理，提升性能。
3. 等待时间太长的优化方案：
-
增加数据库连接池数量： 配置适当大小的数据库连接池，避免因连接数不足导致线程等待。
-
慢 SQL 日志监控：
-
启用 MyBatis Plus 的慢 SQL 监控，监控执行时间较长的 SQL 查询，及时发现并优化性能瓶颈。
-
启用 MySQL 的慢 SQL 日志，记录执行时间超过指定阈值的 SQL 查询。
-
启用 Druid 数据库性能监控，监控数据库连接池性能，发现数据库连接、SQL 执行等方面的问题。
-
-
SQL 超时配置：
-
配置数据库连接池的超时时间，避免请求等待过长时间。
-
在 MySQL 中配置
wait_timeout和interactive_timeout参数，限制连接的最大空闲时间。 -
设置查询的最大执行时间 (
max_execution_time)，防止长时间运行的查询影响系统性能。
-
4. 连接查询优化：
-
避免关联查询中的
NULL值： 确保连接查询中的字段没有NULL值，避免影响查询结果或导致意外的性能问题。 -
避免多表关联： 在可能的情况下，避免多表连接查询。将多表查询拆解为单表查询，或在业务逻辑中分步处理，减少一次性查询的复杂度。
-
小表驱动大表： 在
JOIN操作中，尽量使用小表驱动大表。MySQL 在执行JOIN查询时会根据表的大小选择合适的执行计划。小表驱动大表可以减少内存消耗，提高查询效率。
5. 索引失效的优化方案：
-
强制使用索引： 使用
USE INDEX或FORCE INDEX强制查询使用某个特定索引，避免数据库自行选择不合适的索引。 -
避免使用函数： 在查询条件中避免使用函数（如
WHERE UPPER(column_name) = 'VALUE'），因为函数会导致索引失效。可以考虑将函数计算移至查询外部，或者提前计算好所需的值。 -
避免在
ORDER BY中使用不适合的字段： 如果ORDER BY字段没有索引，数据库可能会进行全表扫描，导致性能下降。确保排序字段具有索引，或者优化排序方式。
6. 服务器优化：
-
增加内存： 通过增加服务器的内存容量来提升数据库的缓存能力，减少磁盘 I/O，提高查询性能。
-
磁盘空间跟上节奏： 确保磁盘有足够的空间来存储数据库文件和索引文件，避免磁盘空间不足导致性能瓶颈或系统崩溃。
-
合理配置数据库缓冲池： 调整数据库的缓存配置，如 InnoDB 的
innodb_buffer_pool_size，确保能够将更多的数据和索引加载到内存中，减少磁盘 I/O 操作。 -
优化硬盘 I/O： 如果数据库的数据量非常大，可以考虑使用更快速的硬盘（如 SSD），或者调整磁盘 RAID 配置以提升数据访问速度。
7. 其他优化建议：
-
数据库分区： 对于非常大的表，可以考虑使用数据库分区（如按日期、范围等进行分区），将大表拆分成多个小表，减少每次查询的数据量，提高查询效率。
-
分布式数据库： 如果单机数据库无法满足负载需求，可以考虑使用分布式数据库系统（如 Sharding）来水平扩展数据库，分担查询压力。
-
数据库的查询缓存： 在某些情况下，开启数据库的查询缓存（如 MySQL 的
query_cache），可以显著提高查询性能，但需注意在高并发写操作的场景中可能影响性能。
sql语句执行过程
（1）编写过程
select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...（2）解析过程
from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
扫一扫
2126
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
