MySQL基础学习笔记
MySQL基础学习笔记
MySQL规定：
MySQL不区分大小写
生产数据库要备份，执行要分步操作，避免错误。
概述
为什么要学数据库
- 大数据时代，所有最后落地最普遍的便是数据库
- 数据库是所有软件体系中最核心的存在
什么是数据库
数据库——DataBase——DB，数据仓库，用于存储和管理数据。
数据库分类
关系型数据库——SQL
- MySQL,Oracle,Sql Server,DB2,SQLlite
- 通过表和表、行与列之间的关系进行数据存储
非关系型数据库——NoSQL
- Redis,MongoDB
- 存储的是键值对。
DBMS——数据库管理系统——Database Management System
- 数据库管理软件
- MySQL是数据库管理系统
MySQL简介
稳定版本：5.7 ，8.0
体积小、速度快，成本底、招人成本底
安装建议：尽量不使用exe安装，因为删除麻烦，且会进注册表，尽可能使用压缩包安装。
压缩包安装步骤
- 解压
- 在环境变量path上配置解压后bin所在的目录地址
- 在mysql解压后目录下新建my.ini文件——配置文件
- 管理员模式运行cmd，用于注册注册表
- 初始化MySQL数据库
mysqld --initialize-insecure - 安装MySQL服务
mysqld install - 启动MySQL服务
net start mysql - 跳过登录密码验证注释掉
#skip-grant-tables - 停止MySQL服务
net stop mysql
配置文件如下：
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\Program Files (x86)\mysql\mysql-8.0.19-winx64
# 设置mysql数据库的数据的存放目录,数据库会自动生成，不需要我们创建文件夹
datadir= D:\Program Files (x86)\mysql\mysql-8.0.19-winx64\data
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 跳过登录密码验证 在修改完密码后要把这句注释掉
#skip-grant-tables
skip-grant-tables
修改root密码
mysql 5.7
update mysql.user set password=PASSWORD('123456')where User='root';
flush privileges;
mysql 8.0
alter user 'root'@'localhost' identified by '123456';
#注意-p后不能加空格直接输入密码
mysql -u root -p123456
mysql -u root -p
#再输入密码
SQLyog
SQLyog验证码
SQLyog/Navicat
可以查看历史执行记录，包括创建表，数据库等记录，比navicat详细。
sc delete mysql 清空服务，即删除mysql
Mysql定义结构
创建数据库
默认创建数据库字符集编码和排序规则如下
创建表
连接数据库
当执行命令行连接数据库报错'mysql' 不是内部或外部命令，也不是可运行的程序时，是因为我们没有配置好mysql的环境变量，这时候就需要我们往path上添加mysql的安装地址到bin文件夹路径到path上。
-- 在客户端查看mysql安装目录的语句
show variables like "%char%";
#有以下两种命令行连接数据库的方法，第一种需要我们回车后输入密码
mysql -u root -p
mysql -u root -p123456
命令行执行sql语句注意事项
基本数据库连接操作
-- 5.7.29-log 查看mysql版本
SELECT VERSION();
-- 创建数据库
CREATE DATABASE `my_test` CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 第二种
CREATE DATABASE `my_test` CHARSET= utf8 COLLATE utf8_general_ci;
-- 展示所有的数据库
SHOW DATABASES;
-- 切换数据库
USE school;
-- 创建表sql 我们一般通过navicat等定义创建表，上生产时再执行创建表的sql语句
CREATE TABLE `t_student` (
`id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(30) NOT NULL COMMENT '学生姓名',
`age` int(3) NOT NULL COMMENT '学生年龄',
`create_user` varchar(30) DEFAULT NULL COMMENT '创建人',
`crreate_tim` datetime DEFAULT NULL COMMENT '创建时间',
`update_user` varchar(30) DEFAULT NULL COMMENT '修改人',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
`delete_flag` tinyint(1) DEFAULT NULL COMMENT '是否删除 0-否 1-是',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';
-- 修改用户密码
USE mysql;
UPDATE user SET password=PASSWORD('123456') WHERE user='root';
-- 刷新权限
FLUSH PRIVILEGES;
show variables like "%char%";
-- 查看数据库所有的表
SHOW TABLES;
-- 查看数据库中指定表的表结构信息
DESCRIBE t_student;
-- 退出数据库连接
exit;
-- 单行注释
/*
多行
注释
*/
utf8和utf8mb4的区别
utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。 为了节省空间，一般情况下使用utf8也就够了。 但是为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。
Mysql中的排序规则utf8_unicode_ci、utf8_general_ci的区别总结
首先，ci是 case insensitive, 即 “大小写不敏感”, a 和 A 会在字符判断中会被当做一样的; bin 是二进制, a 和 A 会别区别对待。所以我们在用sql进行匹配查找的时候，如果是ci，则无论大小写都可以匹配出来，而如果是utf8_bin中的bin是二进制，则会区分大小写，这时候就只能唯一匹配到精确的那个值。
SQL语言分类
DDL—— 数据定义语言DDL，（data definition language）
用来创建数据库中的各种对象—–表、视图，DDL操作是隐性提交的！不能rollback 。
CREATE，ALTER，DROP,TRUNCATE,COMMENT ,RENAME
DML—— 数据操纵语言（DML：Data Manipulation Language）
数据的增删改。
INSERT,UPDATE,DELETE,EXPLAIN, PLAN
DQL——数据查询语言（DQL: Data Query Language）
数据的查询
SELECT
DCL—— 数据库控制语言(Database Control Language）
用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句 。
GRANT,REVOKE
TCL——存储过程控制预言（Transaction Control Language）
数据库事务的控制语言。
COMMIT,SAVEPOINT,ROLLBACK,SET TRANSACTION
操作数据库
操作数据库> 操作数据库中的表> 操作数据库中的表的数据
数据库操作示例
-- 创建数据库 IF NOT EXISTS 表示不存在则创建，存在则跳过
CREATE DATABASE IF NOT EXISTS `my_test` CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 删除数据库
DROP DATABASE IF EXISTS `my_test`;
-- 使用数据库
USE school;
-- 查看数据库
SHOW DATABASES;
数据库中表的列类型
数值类型
| 数据库数值类型 | 描述 | 大小(字节) | 对应Java中的类型 |
|---|---|---|---|
| tinyint | 特别小的数据 | 1Byte | byte |
| smallint | 较小的整数 | 2Byte | short |
| mediumint | 中等大小的整数 | 3Byte | |
| int | 标准的整数 | 4Byte | int |
| bigint | 较大的整数 | 8Byte | long |
| float | 浮点数（单精度） | 4Byte | float |
| double | 浮点数（双精度） | 8Byte | double |
| decimal | 字符串形式的浮点数 | BigDecimal |
注：在金融等金额计算时，一般使用decimal来表示金额大小，因为使用double会有精度问题，而使用字符串形式的浮点数则可以完整得表示出金额的大小。
字符串
| 数据库数值类型 | 描述 | 大小(字节) | 对应Java中的类型 |
|---|---|---|---|
| char | 固定大小的字符串 | 0-255 | String |
| varchar | 可变字符串 | 0-65535 | String |
| tinytext | 微型文本 | 2^8 -1 =255 | String |
| text | 文本串 | 2^16 -1 =65535 | String |
日期时间
| 数据库数值类型 | 描述 | 对应Java中的类型 |
|---|---|---|
| date | 日期格式 | Date，LocalDate |
| time | 时间格式 | LocalTime |
| datetime | 日期时间格式 | Date,LocalDateTime |
| timestamp | 时间戳,1970.1.1到现在的毫秒数 | Timestamp |
| year | 年份 | Year |
注：yyyy-MM-dd HH:mm:ss 表示年月日时分秒。月：用MM大写表示是与时间单位的mm作为区别，而时：HH用大写表示24小时制，用小写hh则是12小时制。
VARCHAR 和 TEXT 长度问题
VARCHAR ：varchar在mysql中满足最大行限制，也就是 65535(16k)字节，在mysql中使用 uft-8（mysql中的 utf-8 和我们正 常的编码utf-8不同）字符集一个字符占用三个字节。
①使用 utf-8 字符编码集 varchar 最大长度是 (65535-1)/3=21844 个字符（由于会有1字节的额外占用空间开销， 所以减1）。
②使用 utf-8mb4 字符集（mysql中 utf-8mb4 字符集也就是我们通常使用的 utf-8 字符集），mysql中使用 utf8mb4 字符集一个字符占用4个字节，所以 varchar 最大长度是（65535-1)/4=16383 个字符（由于1字节额外占用空间开销，所以减1）。
TEXT :最大限制是64k， 采用 utf-8 字符集,(262144-1)/3=87381 个字符。采用 utf-8mb4字符集，(262144-1)/4=65535 个字符。
参考
数据库中表的字段属性——重要
无符号——Unsigned
- 无符号整数
- 声明了该列不能声明为负数，比如主键id这个列
0填充——zerofill
- 0填充
- 列声明的位数，沉淀的值如果不足声明的位数则用0来填充，比如：int(3)，沉淀3则在数据库里为003
自增——AUTO_INCREMENT
- 即数据库帮我们做自增，自动在上一条记录的基础上+1（默认）
- 通常用来设置唯一的主键，必须为整数类型，效率高
- 可以自定义设置主键自增的起始值和步长，比如AUTO_INCREMENT=11表示从11开始自增。
非空 NULL NOT NULL
- 设置为 NOT NULL表示非空，如果不设置值则会报错
- 设置为NULL表示可以为空，不填写值则默认为NULL
默认——DEFAULT
- 设置默认值
- 当不指定该列的值时，则会有默认值填充
表设计时必须存在的字段
id 主键
version 版本号，用于乐观锁
delete_flag 删除状态，用于伪删除
gmt_create 创建时间
gmt_update 修改时间
MySQL中数据类型的长度问题总结
Q:int(5)以及varchar(20)长度表示的是什么？
A：int数据类型是固定的4个字节；
但是int(5)和int(11)区别在于，显示的数据位数一个是5位一个是11位，在开启zerofill(填充零）情况下,若int(5)存储的数字长度是小于5的则会在不足位数的前面补充0,但是如果int(5)中存储的数字长度大于5位的话，则按照实际存储的显示(数据大小在int类型的4个字节范围内即可）,也就是说int(M)的M不代表数据的长度；varchar(20)中的20表示的是varchar数据的数据长度最大是20，超过则数据库不会存储，直接报错；
总结:
int(M) M表示的不是数据的最大长度，只是数据宽度，跟0填充有关，并不影响存储多少位长度的数据；
varchar(M) M表示的是varchar类型数据在数据库中存储的最大长度，超过则不存；
参考
DECIMAL(M,D) 中，M就是总长度，D就是小数点后面的长度，超出范围或者长度不够会被截断或补位 。比如： DECIMAL（5,4）=>总长度不超过5位数字，并且小数点后头必须要4位数字：1.2345 DECIMAL（14,9）=>总长度5位数字，整数5位，小数点后9位：12345.123456789
创建数据库表——重要
创建表示例
-- 注意点
-- 表内容在英文()里，表名、字段名和索引名尽量用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用时用 ''括起来
-- 所有语句后面都要加英文逗号 ,最后一句不用加
-- PRIMARY KEY 主键，一张表一般一个唯一主键
CREATE TABLE IF NOT EXISTS `t_student` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR(20) NOT NULL DEFAULT '佚名' COMMENT '姓名',
`age` INT(3) DEFAULT NULL COMMENT '年龄',
`gender` TINYINT(2) DEFAULT NULL COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY(`id`),
KEY `idx_name`(`name`)
) ENGINE= INNODB AUTO_INCREMENT=1000 DEFAULT CHARACTER SET utf8mb4 COLLATE = utf8mb4_general_ci COMMENT '学生表';
CREATE TABLE IF NOT EXISTS `t_student` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR(20) NOT NULL DEFAULT '佚名' COMMENT '姓名',
`age` INT(3) DEFAULT NULL COMMENT '年龄',
`gender` TINYINT(2) DEFAULT NULL COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY(`id`),
KEY `idx_name`(`name`)
) ENGINE= INNODB AUTO_INCREMENT=1000 DEFAULT charset=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表';
格式
注：[表类型][字符集设置] [表名注释]通常不写默认是数据库的默认配置
CREATE TABLE IF NOT EXISTS `表名` (
`字段名` 列类型 [属性] [注释],
`字段名` 列类型 [属性] [注释]
) [表类型] [字符集设置] [表名注释]
查看建表
-- 查看t_student表的创建语句
SHOW CREATE TABLE t_student;
-- 查看创建数据库的语句
SHOW CREATE DATABASE school;
-- 显示表结构
DESC t_student;
数据库数据表的存储引擎
存储引擎是针对表的，每张表都可以设置自己的存储引擎。我们对数据库会设置默认的存储引擎，当我们建表时没有明确指定表的存储引擎时默认就是数据库设置的存储引擎。比较常见的存储引擎有：
- InnoDB——一般默认选择使用
- MyISAM
InnoDB与MyISAM的区别
| InnoDB | MyISAM | |
|---|---|---|
| 事务支持 | 支持 | 不支持 |
| 数据行锁定（行锁） | 支持 | 不支持（表锁） |
| 外键约束 | 支持（一般不喜欢在数据库使用外键） | 不支持 |
| 全文索引 | 不支持（5.7版本后支持） | 支持 |
| 表空间大小 | 较大，约是MyISAM表空间的2倍 | 较小 |
MyISAM——节约空间，速度较快
InnoDB——安全性高，支持事务和多表多用户操作
在物理空间的位置
Mysql数据库的文件存储在装目录下的data文件夹下，一个数据库对应一个文件夹，如数据库school对应data目录下的school文件夹，因此数据库本质还是文件的存储。
#mysql数据存储位置，安装目录下的data文件夹下
C:\ProgramData\MySQL\MySQL Server 5.7\Data
Mysql存储引擎在物理文件下的区别
InnoDB
一张表对应两个文件分别是：
- *.frm——表结构定义文件
- *.ibd——表数据和索引的存储文件
MyISAM
一张表对应两个文件分别是：
- *.frm——表结构定义文件
- *.myd——表数据文件data
- *.myi——表索引文件index
设置数据库表的字符集编码
在创建表结构时设置如下，建议使用这个：
DEFAULT CHARSET=utf8mb4
不设置会是mysql默认的字符集编码，Mysql默认字符集编码是Latin1,不支持中文。
第二种设置字符集编码是在mysql的配置文件my.ini中设置默认的字符集编码为utf8mb4。建议使用第一种在表结构文件中设置，这样在其他服务器的mysql中就一定是我们设置的字符集编码，因为别人不一定在配置文件中设置了默认的字符集编码为utf8mb4。
character-set-server=utf8mb4
修改删除表
修改
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE t_student RENAME AS student;
-- 增加表字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student ADD address VARCHAR(30) DEFAULT NULL COMMENT '地址';
-- 修改表字段 ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE student MODIFY address TIMESTAMP ;
-- CHANGE 字段可以重命名和修改约束， MODIFY 只能修改约束
ALTER TABLE student CHANGE update_time update_date date ;
-- 删除表字段 ALTER TABLE 表名 DROP 字段名;
ALTER TABLE student DROP address;
删除
--删除表 如果存在则删除
DROP TABLE IF EXISTS student;
MySQL的数据管理
外键
CREATE TABLE IF NOT EXISTS `t_grade` (
id BIGINT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
grade_name VARCHAR(30) NOT NULL COMMENT '年级名',
PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8mb4 COMMENT '年级表';
-- 创建外键方式1
-- 1、学生表的grade_id字段要添加年级表的外键索引
-- 2、给这个外键添加约束（执行引用）
CREATE TABLE IF NOT EXISTS `t_student` (
`id` BIGINT ( 10 ) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR ( 30 ) NOT NULL COMMENT '学生姓名',
`age` INT ( 3 ) NOT NULL COMMENT '学生年龄',
`grade_id` BIGINT ( 10 ) NOT NULL COMMENT '年级id',
`create_user` VARCHAR ( 30 ) DEFAULT NULL COMMENT '创建人',
`crreate_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_user` VARCHAR ( 30 ) DEFAULT NULL COMMENT '修改人',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
`delete_flag` TINYINT ( 1 ) DEFAULT NULL COMMENT '是否删除 0-否 1-是',
`address` VARCHAR ( 30 ) DEFAULT NULL COMMENT '地址',
PRIMARY KEY ( `id` ) ,
KEY (`fk_grade_id`) ,
CONSTRAINT `fk_grade_id` FOREIGN KEY `grade_id` REFERENCES `t_grade`(`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '学生表';
-- 创建外键方式2
ALTER TABLE t_student ADD CONSTRAINT `fk_grade_id` FOREIGN KEY(`grade_id`) REFERENCES `t_grade`(`id`);
注意：外键是物理外键，属于数据库级别的外键，必须管理关系导致的问题。每次做delete或update时都要考虑外检约束，导致开发和测试都不方便。因此强制不得使用外键和级联，一切外键概念必须在应用层解决。
最佳实践
- 数据库只是单纯的表，只存放数据和字段
- 通过代码层面实现外键功能
Insert——插入语句
示例
-- 插入语句
-- 格式 insert into 表名([字段名1,字段名2,字段名3...]) values (值1,值2,值3...), (值1,值2,值3...);
-- 插入表的字段如果省略不写则需要在传入值时要所以字段值都填上并且与字段顺序一一对应匹配
-- Column count doesn't match value count at row 1
INSERT INTO t_student VALUES (null,'艾米');
INSERT INTO t_student VALUES (6,'艾米2',18,1,'2002-10-10');
-- 对于自增主键我们在插入时可以使用null代替，这样数据库会自增帮我们插入
INSERT INTO t_student VALUES (null,'艾米2',18,1,'2002-10-10');
insert into t_student(`name`,age) values('大青山',19);
-- 插入多条数据用(),()隔开
insert into t_student(`name`,age) values('池傲天',20),('霍恩斯',200);
注意
- 语法都是英文的，包括英文标的符号
- 插入字段省略则后面valus值必须一一对应
- 插入多条语句用括号和英文逗号隔开。
Update——修改语句
示例
-- 修改
-- 语法 UPDATE 表名 SET column_name=value,[column_name=value] where [条件]
UPDATE t_student SET `name`= '年轻的艾米',`age`=6 WHERE id =1;
UPDATE t_student SET `name`= '年轻的艾米',`age`=6 WHERE id = null;
-- 不加where条件的话则更新全部数据，所以要注意
UPDATE t_student SET `name`= '年轻的艾米',`age`=6 ;
-- 修改id=2到5之间的数据，是闭区间
update t_student set grade_id = '7' where id between 2 and 5;
-- or 或
update t_student set grade_id='8' where id=1 or id =6;
-- and 和
update t_student set grade_id='9' where id=1 and age=20;
-- 设置的value值可以是一个具体的值，也可以是变量
update t_student set crreate_time = CURRENT_TIME where id=1 and age=20;
update t_student set update_time = t_student.crreate_time where id=1 and age=20;
注意
- column表的列名最好都带上“，防止数据库保留的关键字问题
- where条件没有加入则会修改表全部的数据
- 设置的value值可以是一个具体的值，也可以是变量，如
now(),current_time - set=value设置值之间用逗号隔开
Delete、TRUNCATE——删除语句
示例
-- 语法 DELETE FROM 表名 [WHERE 条件]
-- 删除指定条件下的数据
DELETE FROM t_student WHERE id =2;
-- 删除所有数据，避免使用
DELETE FROM t_student ;
-- 更好的表数据删除使用TRUNCATE语句
-- TRUNCATE 表名
-- TRUNCATE 完全清空数据库表
truncate t_student;
Delete和TRUNCATE的区别
- 相同点：都可以删除数据，不会影响表结构
- 不同点：
- TRUNCATE会重新设置自增列，自增计数器会归零，而Delete自增计数器不会变
- TRUNCATE不受事务影响，而Delete可以在事务中回滚rollback
Delete删除的问题
当重启数据库时，InnoDB的自增列会归零，因为计数器存在内存中，断电即失；而MyISAM计数器存放在文件中，不会断电或者重启丢失。
Select——查询（DQL）
简单查询示例
-- 查询表全部字段全部数据
SELECT * from student;
-- 查询指定字段
SELECT studentno,studentname FROM student;
-- 字段和表都可以起别名，可以用as 或者空格
SELECT studentno as 学号,studentname 姓名 FROM student s;
-- 拼接函数 concat
-- 姓名：张伟前来报到,联系号码：13800001234
SELECT CONCAT('姓名：',studentname,'前来报到,联系号码：',phone) FROM student;
-- DISTINCT去重
SELECT DISTINCT r.studentno FROM result r;
通过SELECT进行函数、表达式、变量使用示例
-- 查看系统版本，(函数)形式
-- 5.7.17-log
SELECT VERSION();
-- 用于计算，(表达式)
-- 300
SELECT 100*5-200 AS 结果;
SELECT 100*5-200 AS total;
-- 查看自增的步长，即每次自增多少，(变量)
-- 1
SELECT @@auto_increment_increment;
-- 筛选出成绩加1分查看
SELECT s.studentno `no` , s.studentresult + 1 score FROM result s;
SELECT s.studentno , s.studentresult/10 as score FROM result s;
where条件子句示例
where条件结果问布尔值，由一个或多个表达式组成。
逻辑运算符 与或非可以使用下面两种语句都行，尽量使用第一种英文。
- and or not
- && || !
-- 与或非的其他写法展示
-- and 与
SELECT * FROM result s where s.studentresult >1 && s.studentresult <80;
-- or 或
SELECT * FROM result s where s.studentresult =70 || s.studentresult =68;
-- not 非
SELECT * FROM result s where not s.studentresult =70;
模糊查询示例
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | age is null | age为null则结果为true |
| IS NOT NULL | age is not null | age不为null则结果为true |
| BETWEEN AND | age between a and b | age在a和b之间则结果为true |
| Like | name like ‘%a%’ | 模糊匹配，name包含a则为真 |
| IN | age in(a,b,c) | age在其中某一个值则结果为真 |
-- 模糊查询 like %表示0到任意个字符， _表示一个字符
-- 查询姓张，名字为2个字的学生
SELECT * FROM student s where s.studentname like '张_';
-- 查询名字中带伟的学生
SELECT * FROM student s where s.studentname like '%伟%';
-- in 查询
SELECT * FROM student s where s.studentno in(1000,1001);
联表查询
联表示例
-- 左联、右联等外联是要用on来关联否则报错，on确定主表，无论条件如何主表数据都会返回然后进行筛选。
SELECT * FROM student s LEFT JOIN result r on s.studentno = r.studentno where s.studentname='张伟';
-- 内联时用on或者where进行关联都可以
SELECT * FROM student s INNER JOIN result r where s.studentno = r.studentno and s.studentname='张伟';
SELECT * FROM student s JOIN result r where s.studentno = r.studentno and s.studentname='张伟';
-- 联表查询思路
/*
1、分析需求要的字段来自哪些表
2、确定使用哪种连接查询
3、确定交叉点，比如订单号id
4、判断的条件
*/
-- 学生年级联表查询
select s.studentno,s.studentname,g.gradename,r.studentresult,sub.subjectname from student s
INNER JOIN grade g ON s.gradeid = g.gradeid
INNER JOIN result r ON s.studentno = r.studentno
left JOIN `subject` sub ON sub.subjectno = r.subjectno;
-- 自连接，设计表时把有父子关系的两种层级表合并成一张表
-- 进行查询时自己和自己关联查询
SELECT a.categoryname 父类别,b.categoryname 子类别 from category a,category b where a.categoryid= b.pid;
1000 张伟 大二 85 高等数学-1
1000 张伟 大二 70 高等数学-2
1000 张伟 大二 68 高等数学-3
1000 张伟 大二 98 高等数学-4
1000 张伟 大二 58 C语言-1
七种join理论
mysql进行关联Join时where和on的区别
Join时where和on的区别关键在于left join,right join,full join(inner join)的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
在使用mysql数据库时，使用left join或者right join都必须要使用关键字on，否则就sql执行就报错。可以这样理解：on是建立外连的桥，两张表如何连接就靠on后面的条件。因为外连分主次表，数据以主表为基础，次表对应连接，如果没有on来建立连接，那么次表的数据就不知道如何对应上主表。而内连，又称为直连，不存在主次表之分，取得是两者的交集，因此不需要on。
外连接时，on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。而where条件是在临时表生成好后，再对临时表进行过滤的条件。
分页和排序
分页示例
分页的意义
缓解数据库压力，体验更好，返回数据更快，瀑布流方式分页
-- 排序 ORDER BY 字段名 DESC
-- 升序 ASC ,降序 DESC
SELECT * FROM result r ORDER BY r.studentresult DESC;
-- 分页 limit offset,pagesize 参数描述：(起始值,从0开始),(页面大小)
SELECT * FROM result r ORDER BY r.studentresult DESC LIMIT 0,2;
SELECT * FROM result r ORDER BY r.studentresult DESC LIMIT 2,2;
Java分页实现
/**
* 当前分页总页数
*/
default long getPages() {
if (getSize() == 0) {
return 0L;
}
long pages = getTotal() / getSize();
if (getTotal() % getSize() != 0) {
pages++;
}
return pages;
}
/**
* 计算当前分页偏移量
*/
default long offset() {
long current = getCurrent();
if (current <= 1L) {
return 0L;
}
return (current - 1) * getSize();
}
子查询和嵌套查询
示例
-- 子查询关联查询
SELECT DISTINCT s.studentno,s.studentname FROM student s INNER JOIN result r on s.studentno=r.studentno
WHERE r.studentresult >=80 and r.subjectno=(SELECT subjectno from `subject` sub where sub.subjectname='高等数学-4');
-- 多张表联表查询
SELECT DISTINCT s.studentno,s.studentname FROM student s INNER JOIN result r on s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE r.studentresult >=80 and sub.subjectname='高等数学-4';
-- 嵌套查询
SELECT DISTINCT s.studentno,s.studentname FROM student s where s.studentno IN(
SELECT r.studentno FROM result r WHERE r.studentresult >=80 and r.subjectno =(
SELECT subjectno from `subject` sub where sub.subjectname='高等数学-4'
)
)
函数
常用函数
聚合函数
| 函数 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
where的条件不能用聚合函数，要改用having过滤。
-- 通过分组查询各个科目的最高分平均分等聚合统计
-- GROUP BY r.subjectno 通过什么字段来分组，该字段 不需要是在展示的列名上
SELECT s.subjectname , MAX(r.studentresult) max, min(r.studentresult) min, avg(r.studentresult) avg FROM result r INNER JOIN
`subject` s ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING avg >=80;
执行count(1)、count(*) 与 count(列名) 的区别
count(列名)会忽略字段中数据为null的值，这时候不会计入总数，而count(1)、count(*) 不会忽略null值，只要这一行有数据就会计数，本质是计算行数。
执行效果上：
- count(*)包括了所有的列，相当于行数，统计结果时不会忽略列值为null的记录。
- count(1)会忽略所有的列，用1代表一行数据，统计结果时不会忽略列值为null的记录。
- count(列名)值包括列名那一列，统计结果时当列值数据为null时会忽略计数，不统计。
执行效率上：
- 列名为主键，count(列名)会比count(1)快
- 列名不为主键，count(1)会比count(列名)快
- 如果表多个列并且没有主键，则 count(1 的执行效率优于 count（*）
- 如果有主键，则 select count（主键）的执行效率是最优的
- 如果表只有一个字段，则 select count（*）最优。
https://zhuanlan.zhihu.com/p/89299468
MD5加密
什么是MD5?
MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。
MD5不可逆，相同的密码值MD5是一样的，因此MD5破解网站的原理是后台存储了MD5的字典，用于匹配MD5加密后的值和加密前的值。
MD5加盐：盐被称作“Salt值”，这个值是由系统随机生成的，并且只有系统知道。即便两个用户使用了同一个密码，由于系统为它们生成的salt值不同，散列值也是不同的。
-- 创建用户表，测试md5加密
CREATE table `user`(
`id` BIGINT(8) UNSIGNED auto_increment NOT null COMMENT 'id',
`name` varchar(20) NOT null COMMENT '姓名',
`pwd` VARCHAR(50) NOT null COMMENT '密码',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT charset=utf8mb4;
-- 明文密码
insert into `user`(`name`,`pwd`)VALUES('艾米','123456'),('大青山','123456'),('霍恩斯','123456'),('雷葛','123456');
-- 使用MD5加密
-- 指定用户加密
update `user` set pwd=MD5(pwd) where `name`='艾米';
-- 更新所有用户加密
-- 注意加密后的MD5值是一样的，之所以艾米用户的值不一样是因为更新所有导致二次加密了
update `user` set pwd=MD5(pwd) ;
-- 插入时加密
insert into `user`(`name`,`pwd`)VALUES('绿儿',MD5('123456'));
-- 校验加密，相当于用户登录
-- 将用户登录的密码用md5加密后与数据库的值比对
SELECT * FROM `user` u where u.`name`='绿儿' and u.pwd = MD5('123456');
事务
ACID
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
事务的隔离级别
因为隔离性导致的脏读、幻读、不可重复读。
脏读：
指一个事务读取了另外一个事务未提交的数据。
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
数据库隔离级别设置
set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别
设置描述：
Serializable 可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read 可避免脏读、不可重复读情况的发生。（可重复读）
Read committed 可避免脏读情况发生（读已提交）。
Read uncommitted 最低级别，以上情况均无法保证。(读未提交)
数据库操作事务示例
-- 关闭事务自动提交
SET autocommit =0;
-- mysql默认开启事务自动提交
SET autocommit =1;
START TRANSACTION;
insert into `user`(`name`,`pwd`)VALUES('艾米','123456'),('大青山','123456'),('霍恩斯','123456'),('雷葛','123456');
-- 成功提交事务持久化
COMMIT;
-- 回滚，一般用于失败后回滚数据
ROLLBACK;
-- 保存点
SAVEPOINT a;
-- 回滚到一个事务的保存点
ROLLBACK TO SAVEPOINT a;
-- 释放保存点
RELEASE SAVEPOINT a;
索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。
索引的分类
- 主键索引——PRIMARY KEY
- 唯一索引——UNIQUE KEY
- 普通索引——INDEX 、KEY
- 全文索引——FULLTEXT
如何用EXPLAIN优化sql查询效率
索引示例——测试百万数据的索引效果
-- 测试表
CREATE TABLE `t_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(20) NOT NULL COMMENT '姓名',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`phone` varchar(20) DEFAULT NULL COMMENT '手机号码',
`pwd` varchar(50) DEFAULT NULL COMMENT '密码',
`age` TINYINT(3) DEFAULT NULL COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHAstudent.gradeidRSET=utf8mb4 COMMENT='测试用户表';
-- 插入100w数据函数
-- 写函数前先更改mysql分隔符为$$ ，写函数标记
DELIMITER $$
CREATE FUNCTION 100w_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 1;
WHILE i<num DO
INSERT INTO t_user(`name`,`email`,`phone`,`pwd`,`age`)VALUES(CONCAT('VIP客户',i),'884849324@qq.com',CONCAT('15',FLOOR(RAND()*1000000000)),UUID(),FLOOR(RAND()*100));
SET i =i+1;
END WHILE;
RETURN i;
END
-- 执行函数
SELECT 100w_data();
-- 删除函数
DROP FUNCTION 100w_data;
-- mysql8.0才能并行
SELECT /*+PARALLEL(8)*/ 100w_data();
-- 未加索引前耗时1s。加索引后耗时0.01-0.02s
SELECT * from t_user u where u.`name`='VIP客户100000';
-- 增加索引
ALTER TABLE t_user ADD INDEX idx_name(`name`);
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般用在经常查询的字段上
索引的数据结构
- Hash
- Btree
权限管理和备份
权限添加和删除
可视化工具——navicat
首选我们可以通过可视化工具进行用户的创建，权限的添加和删除。
SQL
但在Linux服务器上我们没有可视化界面因此要使用sql语句执行。
-- % 是允许所有的主机都可以连接
CREATE USER `roy`@`localhost` IDENTIFIED BY '123456';
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option, Index, Insert, Lock Tables, Process, References, Reload, Replication Client, Replication Slave, Select, Show Databases, Show View, Shutdown, Super, Trigger, Update ON *.* TO `roy`@`localhost`;
-- 创建用户
CREATE USER develop IDENTIFIED by '123456';
数据库备份
为什么要备份：
- 保证数据库数据不丢失
- 用于数据的迁移
MySQL数据库备份的方式
- 直接copy物理文件/data目录下的物理文件
- 在可视化工具Navicat等上导出
- 使用命令行导出
可视化工具备份
右击要导出的数据库或表转存数据，可以转存表结构或者表结构及数据。
导入就直接把文件执行即可。
命令行导出——linux中使用
#mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:a.sql
#导出整个数据库
mysqldump -hlocalhost -uroot -p123456 school >D:a.sql
#多张表导出，用空格隔开，命令行是用空格
#mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:a.sql
命令行导入——linux中使用
#登录的情况下导入，先切换到指定的数据库
#source 备份文件
source d:a.sql
#未登录状态 <表示导入 >表示导出
#mysql -u用户名 -p密码 库名< 备份文件
mysql -uroot -p123456 school student <D:a.sql
项目数据库设计
数据库设计对比
数据库三大范式——数据库设计规范
找技术的知识的话比如博客要找有例子的，这样看比较清楚简单一点。
第一范式（1NF—— First Normal Form）
原子性：保证每一列不可再分。
第二范式（2NF）
前提：满足第一范式
单一职责原则：每张表只描述一件事情。
第三范式（3NF）
前提：满足第一、第二范式
确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
也就是订单表里只能关联商品id，而不能把商品价格、商品种类、商品名称等商品表信息都加进来。
规范性和性能的问题
- 关联查询最多不得超过三张表
- 考虑商业化的需求和目标，数据库的性能更重要
- 考虑查询性能问题故意给某些表增加冗余字段。（从多表查询变为单表查询）
其他
保存或更新操作的原理
原理如下代码，先判断出传入的保存/更新对象是否包含主键id，包含的话执行根据id查询语句，如果得到数据则走更新操作，否则执行插入操作。
public boolean saveOrUpdate(T entity) {
if (null == entity) {
return false;
} else {
TableInfo tableInfo = TableInfoHelper.getTableInfo(this.entityClass);
Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!", new Object[0]);
String keyProperty = tableInfo.getKeyProperty();
Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!", new Object[0]);
Object idVal = ReflectionKit.getFieldValue(entity, tableInfo.getKeyProperty());
return !StringUtils.checkValNull(idVal) && !Objects.isNull(this.getById((Serializable)idVal)) ? this.updateById(entity) : this.save(entity);
}
}