![da961e18aef287f6b149a3ad3ec6c471.png](https://i-blog.csdnimg.cn/blog_migrate/ad35d121c97b6d7d9ab847976b605575.jpeg)
数据基础
SQL语法顺序
![8186548da8dbdf19e4007b3a02dfe351.png](https://i-blog.csdnimg.cn/blog_migrate/e510fd94da5ee38f317ea2206800c87f.jpeg)
1.建库
create
2.删库
drop
mysql 中两个字符串拼接
select
3.默认值 default默认值,comment注释 engine引擎
create
4.查看建库,建表语句,表结构
show
5.查看mysql中文件位置
show
![0bedfe9913503de71576a668b75118a9.png](https://i-blog.csdnimg.cn/blog_migrate/3903e935d005af3a433cb8c7b1c0158e.jpeg)
6.修改表
1
7.插入
insert
8.删除
delete
delete,truncate,drop区别? delete 和truncate 都是删除表中的数据,不删除表,表结构索引不变。 delete可以具体到删除哪行,truncate 直接删完。 delete保留自增计时器,truncate直接将自增计数器清空 如果在开启了一个事务,delete删了之后如果取消还可以回滚, truncate直接删了,和事务无关 droptable student 是直接把表删了。
9.更新
update
10.查询
select
![4d3684e73e2048bed5a28dd44a69b2be.png](https://i-blog.csdnimg.cn/blog_migrate/b2ab70be06d4ff1367e6cab26c18fbbd.png)
select
where子句 条件 and or not
select
模糊查询 null,like,in
like
join 多表联查
JOIN ..ON 固定语法
inner
![8d908f1a44d8088d3a8b92e462490071.png](https://i-blog.csdnimg.cn/blog_migrate/b4984a4b2fa0e21333424c74686e18a8.png)
left
![b6ed82d7660d08e0576cf8da23dd96b1.png](https://i-blog.csdnimg.cn/blog_migrate/8692f2ebdef77f852ab6f0f91a3dfa8b.png)
right
![48b8c44afa216710ab6d8f2a8025794e.png](https://i-blog.csdnimg.cn/blog_migrate/998e87ea47c5b514a93ec277d3b6f851.png)
上面三种是最常见的。区别
inner join 是他们通过条件匹配的 a.user_id=u.user_id; 只显示两个相等的行
left join 即使他们的不符合匹配条件 也会将左表中的数据全部查出,如图join2所示 即使右表中没有与之对应的那行数据。
right join同理,右表中的数据全部查出,即使不匹配,所有如图join3所示,左表没有与之对应的那行数据
JOIN ON 有的后面还会跟where 。 on是判断条件,where 时等值查询,可以理解为在on判断条件之后,在进行一次筛选。
SELECT
扩展3表查询 查出文章名,下面的评论人的姓名和评论内容。
user article comment 三个表 先分为两个表,查出数据,在和第三个表查询
select
自连接
用一张表的某行的某字段,去这张表其他行的某字段去查询值。
我们现在有个评论表
评论id,评论用户的id,评论用户的姓名,被评论用户的id,我们现在想在这张表中 查出被评论用户的姓名怎么查。 关键点在于把这张表看成两张表。这样可以理解a表中的tocommentid 要去B表中的 通过commentid 查询到comment_id(被评论用户的姓名) ,实际上是一张表,通过取别名的方式当做两张表而已。
![e1b1e91f9dd585f4e1f9816d0040096a.png](https://i-blog.csdnimg.cn/blog_migrate/2c08abcd74c300549b6b5d75d8f84786.png)
SELECT
![8ea10f00d712339a5d0e555012140dae.png](https://i-blog.csdnimg.cn/blog_migrate/14ae1326e2d2f4086c69c28c1cf38b24.png)
排序 order by
ascend 升序(默认 ),descend降序
select
分页 limit 还有一种瀑布流,往下拉一直有
select
子查询 (重点)
之前像
通过join多表联查,和where子查询 我们不难发现,如果只是需要多经过几次的判断,(子查询里面嵌套一个查询),用子查询,多表联查都可以,逻辑上 可能子查询好理解一点,但是如果我们还需要查询显示出另一张表其他字段,只能用多表联查。例如多个 hobby字段,select s.* 加上 h.hobby即可,而子查询只是经过判断 哪个student_id符合,不能加上字段。
一些函数
![e0a3cd22d9f1fa41fe02b43d7b81a75c.png](https://i-blog.csdnimg.cn/blog_migrate/36ac01fa5209fe3bf97a5fb527bdbb16.png)
group by分组
查某个科目的平均成绩
MD5 加密 不可逆 相当于一个字典
insert
进阶
下面的内容某些讲的可能没有什么关联。
索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。
添加索引
- ALTER table [表明] add [索引种类] index [索引名] (`column`)
- create [可加索引种类] index [索引名] on [表名](字段)
删除 DROP INDEX `indexname`
主键索引
主键 : 能唯一标识一条记录
特点 :
最常见的索引类型
确保数据记录的唯一性
确定快速定位到数据
唯一索引
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
主键索引只能有一个
唯一索引可能有多个
普通索引
全文索引
插入100w条数据
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), '24736743@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();
设计一个数据库
为什么要好好设计数据库?
糟糕的数据库设计 :
- 数据冗余,存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计 :
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用系统的开发
数据库设计遵守三大范式:
- 第一范式 :每一列不可再分
- 第二范式 :满足第一范式,每个字段都和主键有关,每张表只描述一件事。(比如订单表,里面只能有订单方面信息,不可以有其他,例如用户名,职位等)
- 第三范式 :满足第二范式,每个字段和主键直接相关
规范化和性能的关系
- 在数据规范化的同时 , 要综合考虑数据库的性能
- 通过在给定的表中添加额外的字段,减少查询用时(连表变为了单表查询)
数据库中可能会听到sql注入,那什么是sql注入,就是通过字符串拼接的方式是用户密码始终为true,继而暴力登录。
解决 java中用PreparedStatement对象,预编译,将sql 中参数变为?占位符,将参数当做字符,直接替换(假如遇到特殊字符会将他转义掉,例如引号),不存在拼接问题了select * from b where id=?
#,$区别
#
我们mysql逻辑图是什么样的呢?
首先客户端通过身份验证连接上mysql连接器,当我们执行sql时,先查询缓存,如果有命中,直接返回,没有,去分析器:进行词法分析,语法分析,构建一颗语法执行树。优化器:构建执行计划(选择合适的索引),最后上执行器:去执行,返回结果 如果是insert语句,需要将数据放到存储引擎中。
在解析层中有三大缓存操作:
- 1.sql语句缓存 select * from b where id =1构建的语法执行树
- 2.sql执行计划缓存 select AVG(grade) from b group by ...order by..concat....等一些动态的数据,无法缓存他的缓存树,所以只能缓存他的执行计划:执行顺序,是否要重写等。 找缓存先3后1.找不到在2
- 3.结果集缓存 上一次的查询结果k---v 最没用
![5522131a5b7750314811ef8935535d0c.png](https://i-blog.csdnimg.cn/blog_migrate/3d289eb3dd65d38610634e7d68ed5c38.png)
mysql 为每个client 分配一个session(理解为socket),在执行事务,会申请一个独立空间(memory allocation),在里面进行解析,优化,执行等操作。
MYSQ为什么用B+?
linux 1页4KB ,cpu每次拿4页,mysql 默认16kb,一次4页 B+一个索引中的一个节点最多8字节,加上指针6字节,最多14b 16kb/14b=1170,如果是数据的话就放的少一点,所以说mysql用B+,是因为B+一个索引Node最大可放16kb,恰好是cpu一次拿的最大值,而索引越节点放的数据越多,相对二叉树,整颗树的高度,就不会太高。(节点放在磁盘上,每次查找都需要去磁盘拿,高度小磁盘IO次数就少)磁盘IO利用率高
undo log是回滚用的,在执行事务的,如果回滚了,事务会顺着undo log日志,回到最初的数据。
redo log是innodb特有的 在innodb引擎层,记录新数据的日志,为事务中每次的变更记录日志,大小是固定的,理解成一个环形,write_pos逆时针写数据,check_point擦数据,将输入写入硬盘中。
![93ffaaf3b7a1d3d3adb2386f0c2d9355.png](https://i-blog.csdnimg.cn/blog_migrate/555832ffc09de7f54fc670df6578c8fb.png)
Bin log在mysql服务层,它记录了数据库上的所有改变,并以二进制的形式保存在磁盘中;它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制(主从数据库的复制)。语句以“事件”的形式保存,它描述数据更改。
binlog有三种模式:ROW(行模式), Statement(语句模式), Mixed(混合模式)。
row
优点:日志内容会非常清楚的记录每条数据详细的变更细节,即使只更新了一个字段,binlog里也会记录所有字段的数据。
缺点:binlog日志会非常大,mysql主从同步时,会产生大量磁盘IO。
statement记录的只是执行语句。
优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。
缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题。
Mixed(混合模式):在Mixed模式下,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。
Binlog详解_与优秀的人在一起,会使自己变得更优秀-CSDN博客
【狂神说Java】MySQL最新教程通俗易懂_哔哩哔哩 (゜-゜)つロ 干杯~-bilibili
https://blog.csdn.net/Waves___/article/details/105295060