目录
===================================================
笔记md格式连接
https://download.csdn.net/download/zhuwenaptx/69469339Mysql学习
sql语句的分类
-
DQL :数据查询语言 select
-
DML ：数据库操作语言 ：insert delete update
-
DDL ：数据定义语言 ，凡是带有create drop alter 都是你DDL 分别是对表的新建，删除和修改
-
TCL :事务控制语言 事务提交：commit 和事务回滚 ：rollback
-
DCL ：数据库控制语言 ： 授权 grant 撤销授权 revoke
简单查询
-
select 字段名，字段名 from 表名
-
select * from 表名 （效率低，可读性差，在实际开发中不建议用）
-
select 字段名 as 别名 from 表名 使用as关键字起别名，只是改变查询结果的名;as 关键字可以省略；别名如果有空格可以采用单引号
-
字段可以使用数学表达式
条件查询
不是将表中的所有数据都查出来，查询符合条件的
select 字段名，字段名，..... from 表名 where 表达式
-
= 等于
-
<> 、!= 不等于
-
< 小于
-
> 大于
-
>= 大于等于
-
<= 小于等于
-
between ... and ... 两个值之间 （是个闭区间，包含开头和结尾）
-
is null 是否为空 is not null 是否不为空
-
and 并且
-
or 或者 and比or的优先级高
-
in 包含 not in 不在这个范围内
-
not not 可以取非 主要用于is 或 in 中
-
like 模糊查询 支持%或者下划线匹配 % 匹配任意字符，下划线，一个下划线匹配一个字符
-
\ 表示转义字符 转义特殊字符
排序
默认是升序 （ASC）
ORDER BY DESC 按照降序排列
多字字段排序 order by 字段名1 desc ， 字段名2 asc ； 只有字段名1相等才会轮到字段名2 进行排序‘
顺序 from --> where -->select --> order by
常见的数据处理函数
单行处理函数
特点：一个输入对应一个输出 ；多行处理函数特点 ：多个输入对应一个输出
-
lower 转换小写
-
upper 转换大写
-
substr 取字符串 sbustr(被截取的字符串，起始下标，截取长度) 注意起始下标从 1 开始
-
length 长度 length(字段名)
-
trim 去空格
-
str_to_date 将字符串转换成日期
-
date_format 格式化日期
-
format 设置千分位、
-
round 四舍五入 round （字段，保留的位置）
-
rand() 生成随机数 产生的为 [0,1] 的随机数 select ROUND(rand()*100 ,0) 为100以内的随机数
-
ifnull 可以将null转化为一个具体的值 ； null 只要参与运算结果久违null ；ifnull(数据，被当为什么值) 如果’数据‘ 为 null 就为 ’被当成什么值‘
-
case....when ......then.... when...then.... else end（类似if else 语句） case when ...then （注意结束需要用end）
-
concat 字符串拼接 concat (字符串1，字符串2)；
分组函数（多行处理函数）
-
count 计数
-
sum 求和
-
avg 求平均值
-
max 最大值
-
min 最小值
注意： 分组函数在使用的时候必须先进行分组，然后才能使用。如果没有进行分组，默认整张表为一组数据。
-
分组函数自动忽略null ，不需要对null 提前进行处理
-
分组函数中 count(*) 和count (具体字段) 的区别
count(*) 表统计表当中的总行数
count (具体字段) 表示统计该字段下不为null的元素
-
分组函数不能直接使用在where子句中 （因为分组函数在使用的时候必须先分组才能使用，where执行的时候还没有分组）
-
所有的分组函数可以组合在一起一起用
分组查询
先进行分组，然后对每一组的数据进行条件查询
select ... from .... group by ....
执行顺序
from ----> where ----> group by ---->having----> select ----> order by
当一条select 中如果有group by 语句，select后面只能跟参加分组的字段，以及分组函数。（可以多字段分组）
使用having子句可以对分完组的数据进行过滤，但是它必须和group by 一起用
where 和having 优先选择where ，where解决不了的 在使用having
去除重复数据
不会修改原表数据
distinct
distinct 必须出现在所有数据的最前方
distinct (a,b) 表示两个字段联合去重
连接查询
多表进行联合查询数据，成为连接查询
-
年代分类
SQL92
SQL 99 (重点学习)
-
连接方式分类
-
内连接
-
等值连接
-
非等值连接
-
自连接
-
-
外连接
-
左外连接 （左连接）
-
右外连接 （右连接）
-
-
全连接
-
-
具体内容
当两张表进行连接查询并且没有任何限制的时候，出现了表的记录互相乘的记录，这被称为笛卡尔积
两张表连接查询，增加条件进行查询，并没有减少查询次数。
表的连接次数越多，效率越低，所以尽量减少连接次数
-
内连接 ---- 等值连接
select A.a,B.b from A,B where A.c=B.c SQL92
select A.a,B.b from A (inner) join B on A.c=B.c SQL99
inner 可以省略，但是带上更好，可以区分是什么连接
SQL92 表连接条件和后续筛选条件糅杂在一起，结构不清晰
-
内连接 ---非等值连接
连接条件不是等于号
-
内连接 ---自连接
把一张表看成两张表
-
外连接 ---左连接
以左边的表为主，显示左边表的所有数据
-
外连接 --- 右连接
以右边的表为主，显示右边表的所有数据
-
-
子查询
先执行子查询在执行整个查询（嵌套查询）
-
where 子句的子查询
-
from 子句的子查询 （可以把子查询中结果当成一个临时表）
-
select 子句后面的子查询 （把查询语句当成字段名）
union 合并查询结果集
union效率 较高一些，union可以减少匹配次数，也可以完成两个结果集的拼接
注意事项：
-
拼接的时候要求两个结果集的列数相同
-
结果集合并的时候要求列和列的数据类型一致（mysql不会报错，orcal会报错）
limit
是将查询的结果集的一部分数据拿出来，通常使用在分页查询中
limit startIndex， length
startIndex 起始下标，从0开始 （缺省默认从0开始）
length 长度
注意： mysql中limit 在order by 之后执行
通用的分页
limit （N-1)length , length
mysql 的执行顺序 总结
form
on
join
where
group by
分组函数（聚合函数）count ，sum,avg
having
select
distinct
order by
limit offset
建表和修改数据
建表是DDL语句 （create 、drop、alter）
-
建表语句
create table 表名 （ • 字段名1 数据类型， • 字段名2 数据类型 *default 对应的默认值* • );
所有标识符小写，单词与单词之间用下划线_ 连接
快速复制表结构（把查询结果当做新表）
create table 表名1 as select * from 表名2 ; 把表2复制成表1
-
常见的数据类型
-
varchar ：可变长度的字符串 255
-
char ：给定长度的字符串 255
-
int : 数字中的整数型 ，等同于java的int 11
-
bigint ：数字中的长整型，等同于java的long
-
float ：单精度浮点型数据
-
double ：双精度浮点型数据
-
year ： 存年份 2021
-
date ：存日期 2021-12-22
-
time ：存时间 15:00:01
-
datetime ：存日期时间 2021-12-22 15:01:10
-
clob :字符大对象，最多可以存储4G 的字符串
-
blob ：二进制大对象 图片，声音，视频等流媒体数据 （插入数据的时候需要使用IO流）
-
text ：超过255字节的只能用varchar或者text，超过varchar范围的会自动强制转换为text类型 字符大对像，是clob的别名
-
json ：存储json
-
decimal ：存储金钱等比较精确的金融数据
-
tinyint : 短int 通常用一个字段表示为java的布尔型
-
-
删除
drop table 表名; 如果不存在会报错 drop table if exists 表名; 如果表不存在不会报错
修改数据是 DML 语句 （insert、update、delete）
-
插入数据
insert into 表名 （字段名1，字段名2，字段名3） values(值1，值2，值3)；
字段名和值要一一对应（尤其是数据类型）
如果没有给其他数据指定值的话，默认是null ，insert执行成功就会产生一条数据。
insert 语句的字段名可以省略，但是要求所有的字段的值都写上。（不建议使用）
多行插入
insert into 表名 （字段名1，字段名2，字段名3） values(值1，值2，值3)，(值1，值2，值3)，(值1，值2，值3)；
把查询结果作为记录插入表中
insert into 表名 （查询语句）
mysql 的日期格式
-
%Y 年
-
%m 月
-
%d 日
-
%h 时 12小时
-
%H 时 24小时
-
%i 分
-
%s 秒
-
修改数据
update 表名 set 字段名1 = 值1 ，字段名2 = 值2 ... where 条件
如果没有条件会修改所有的数据
-
删除数据
delete from 表名 where 条件;
没有条件整张表的数据都会被删除
删除数据的原理 ： 删除内容，但是硬盘上的空间不释放，（可以进行回滚），导致删除效率很低。
truncate 语句删除表
truncate table 表名 (DDL 操作)
删除效率高，但是不可恢复数据。
-
约束
在创建表的时候，我们可以给表的字段加上一些约束，保证这个表中的数据是完整的有效的
-
非空约束 （not null）
非空约束的字段不能为null ；只有列级约束
-
唯一性约束 (unique)
唯一性约束的字段不能重复但是可以为null
-
主键约束 (primary key) ---PK
主键约束：就是一种约束 主键字段：该字段添加了主键约束，该字段就叫主键字段
主键值： 主键字段中的每一个值都为主键值
主键值是每一行记录的唯一标识（身份证号）；每一张表都要有主键，没有主键该表无效；
主键的特征：不能为空，也不能重复
添加主键约束：
create table 表名 （ • 字段名1 数据类型 primary key， //单一主键 • 字段名2 数据类型 ， • 字段名3 数据类型 • );
create table 表名 （ • 字段名1 数据类型 auto_increment ， //自增 • 字段名2 数据类型 ， • 字段名3 数据类型， • primary key（字段名1，字段名2） // 复合主键 • );
一张表主键约束只有一个 ： 联合约束（复合主键）也是一个主键
-
外键约束 (foregin key) ---FK
外键约束：是一种约束
外键字段：添加外键约束的字段
外键值：外键字段中的每一个值
外键值是另一个表的值，这个外键表就是另一个表的子表。
foregin key （字段名） references 表名（字段名）
引用的字段必须具有唯一性，但是可以为null
-
检查约束 （check）(mysql不支持，orcal支持)
联合约束：（多个字段联合在一起进行约束）
unique (字段1，字段2，字段3) 多个字段联合约束，多个字段都一样才不能插入数据。
约束添加在列后面为列级约束，添加在最后面的适合为表级约束
表级约束：
create table 表名 （ • 字段名1 数据类型， • 字段名2 数据类型 ， • 字段名3 数据类型， • unique (字段1，字段2，字段3) • );
列级约束：
create table 表名 （ • 字段名1 数据类型 not null， • 字段名2 数据类型 ， • 字段名3 数据类型 • );
字段名1 数据类型 not null unique，
该两个约束联合使用，在mysql中 改字段为主键约束字段，orcal不是这样的
存储引擎
存储引擎是mysql中特有的一个术语
不同方式的存储引擎 导致不同的存储方式数据的方式。
show create table 表名 ； 展示创建表时候的语句
可以再建表的时候给表添加指定的存储引擎
ENGINE : 存储引擎 （InnoDB）
CHARSET ： 字符集 （utf8）
查看mysql支持的引擎
查看数据库的版本号
SELECT VERSION()
事务（transaction）
一个事务就是一个完整的业务逻辑。（最少的操作单元，要么同时成功，要么同时失败）
只有DML语句（insert、update、delete） 才会涉及到事务，这些要语句要保证数据的安全性。
事务就是多条DML语句同时成功或者同时失败。
InnoDB :提供了一组用来记录事务性活动的日志文件，用来存储DML语句的操作，以此来支持提交事务和回滚事务。
提交事务
commit
mysql默认情况下是支持自动事务提交的，每执行一次就会提交一次。
关闭自动提交事务： start transaction;
回滚事务
rollback
事务的特性
-
A： 原子性： 说明事务是最小的工作单元。不可再分
-
C：一致性 ：所有事务要求在同一个事务当中，所有的操作必须同时成功，或者同时失败，以保证数据的安全和一致性
-
I：隔离性：A事务和B事务之间具有一定的隔离
-
D：持久性：事务最终最终结束的一个保障，事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。
事务的隔离性
隔离级别从上到下递增
-
读未提交 ：read uncommitted （最低的隔离级别）
事务A可以读取到事务B没有提交的数据
存在的问题： 脏数据
-
读已提交 read committed
事务A可以读取到事务B已经提交的数据
解决了脏数据问题
存在问题：不可重复读取数据 （每次读取的数据不一样）
orcal数据默认的
-
可重复读 repeatable read
事务A开启之后，不管多久，每次再事务A 中读取的数据都是一致的，即使事务B已经对数据进行了修改，事务A读取的数据还是不变
可能出现了幻读
解决了不可重复读的问题。
只要事务不结束，读取的数据还是不变的；
mysql中默认的事务隔离级别是 repeatable read
-
序列化/串行化 serializable （最高的隔离级别）
效率最低，解决了所有问题；表示的事事务排队，不能并发。
设置全局的事务隔离级别
set global transaction isolation level read uncommitted
查看事务的隔离级别
select @@tx_isolation 低版本 SELECT @@TRANSACTION_ISOLATION 高版本
索引
索引在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然多个字段联合在一起也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
注意：缩小扫描范围就是扫描某个区间，mysql中的索引也需要排序的，mysql中的索引是一个B-Tree 数据结构
mysql查询数据的方式: 全表扫描；根据索引扫描。
索引的实现原理
-
在任何数据库中的主键都会自动添加索引对象，在mysql中一个字段如果有unique约束的话，也会自动添加索引对象
-
在任何数据库中，任何一张表的任何一条记录在硬盘的存储上都有一个硬盘的物理存储编号。
-
在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称为tablespace中。在MEMORY存储引擎中，索引被存储在内存中。不管索引
平衡二叉树 ：左小，右大
添加索引条件
-
数据量大
-
该字段经常出现在where后面，以条件形式的出现。
-
该字段很少的DML （insert、update、delete） 操作；（DML操作会使得索引重新排序）
建议不要随意添加索引，因为索引也需要维护，建议通过unique字段进行查询。
创建索引
create index 索引名 on 表名(字段名);
删除索引
drop index 索引名 on 表名;
查询语句是否使用了索引
explain select 语句;
索引失效
-
select * from t_user where name like '%A'; 索引字段吗模糊查询时候%开头，导致索引不起作用
-
使用or时候会失效，如果使用or时候，必须两边的字段都有索引，不然的话索引就会失效
-
使用复合索引的时候，没有使用左侧的列查找，索引失效。
create index composite_index on t_user(name,sex);
如果使用sex进行查询的话，这个索引就会失效，但是使用name进行查询的话，索引是起作用的
-
where当中索引列（字段）参加了运算，那么该字段的索引就会失效
-
在where当中索引列（字段）使用了函数，那么该字段的索引就会失效
索引的分类
-
单一索引
-
复合索引
-
主键索引
-
唯一性索引：具有unique约束的字段上添加索引
视图
(view)
站在不同的角度去看待同一份数据
简化开发，简化复杂的查询sql。
视图对象也是存储在硬盘上，不会消失。
创建视图
create view 视图名 as select * from t_user;(后面必须是DQL语句)
视图创建完成之后，可以对其正常进行CRUD（增删改查）
删除视图
drop view 视图名
视图特点
通过对视图进行CRUD操作会影响到原表的数据
同理原表进行CRUD操作也会影响到视图
DBA常用命令
创建用户
create user username identified by 'password'
授权
grant all privileges on dbname.tbname to 'username'@'login in' identified by 'password' with grant option 1.dbname=* 表示所有数据库 2.tbname=* 表示所有表 3.login ip =% 表示任何ip 4. password 为空 表示不需要登录密码就可以登录 5. with grant option 表示该用户还可以授权给其他用户
数据导出（在dos命令下）
mysqldump 数据库名> 位置.sql -uroot -p123 数据库 mysqldump 数据库名 表名> 位置.sql -uroot -p123 数据库表
数据导入（创建数据库，之后再导入）
source sql文件
数据库设计三范式
数据库表设计的依据（可以避免数据库的冗余，空间的浪费）
-
第一范式 （最核心，最重要）
要求任何表必须有主键，每一个字段原子性不可再分
-
第二范式
建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖
多对多三张表，关系表两外键
-
第三范式
建立在第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
A依赖B，B依赖C 这样就是传递依赖
一对多两张表，多的表加外键
扫一扫
389
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
