mysql进阶笔记

连表查询join

思路： 超过一张表的话，就需要连表查询；确定使用哪种连接查询；确定交叉点（判断的条件是学生表中的number == 成绩表中的number）

--join on 是个语法，而where是等值查询
--student学生信息表；result学生成绩表
select s.number,name,subject,grade
from student as s
inner join result as r
on s.number == r.number;
--要查询那些数据 select ...
--从那几个表中查 from 表 XXX join 连接的表 on 交叉条件
--假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
-- from a left join b --以左边的表为准
-- from a right join b --以右边的表为准

自连接（自己跟自己连接）

把一张表拆分成两张表。

分页和排序

分页limit
为什么分页？缓解数据库压力，给人更好的体验。但是现在的好多数据采用的是瀑布流的方式。
网页：当前，总的页数，页面大小
语法：limit 起始值，页面的大小
排序order by

子查询

where(值是计算出来的)
本质：在where语句中嵌套一个子查询语句 where(select * from)，由里及外的查询

mysql的函数

常用函数

--算数
select ABS(-8);
select CEILING(9.4);--向上取整
select FLOOR(9.4);--向下取整
select RAND();--返回一个随机数
select SIGN(0);--返回参数的符号
--字符串函数
select CHAR_LENGTH('hello');
select CONCAT('我'，'gabe');--拼接字符串
select INSERT('你好啊'，1,2'我好');
select LOWER();--转小写
select UPPER();--转大写
select INSTR('APPLE','A');--找A的位置
--时间和日期函数（记住）
select CURRENT_DATE();--获取当前日期
select CURDATE();
select NOW();
select LOCALTIME();
select SYSDATE();
select YEAR(NOW());
select MONTH(NOW());
--系统
select USER();
select VERSION();

聚合函数（常用的）
计算平均值，最大值最小值的函数

--COUNT()统计表中有多少个记录
select COUNT(studentname) from student;——指定列会忽略所有的NULL值
select COUNT(*) from student;--不会忽略NULL值，计算行数，所有的列都会走
select COUNT(1) from result;--不会忽略NULL值，计算行数，只走一个列
--SUM()
selecct SUM('score') as 总和 from result;
selecct AVG('score') as 平均 from result;
selecct MAX('score') as 最大 from result;
selecct MIN('score') as 最小 from result;
--GROUP BY 字段 --通过该字段来分组 where中不能使用聚合函数

数据库级别MD5加密

MD5不可逆，具体值的md5是一样的。

INSERT INTO testmd5 VALUES(4, 'XIAOMING',MD5('123456'));

事务

什么是事务：要么都成功，要么都失败。A给B转账。（将一组SQL放在一个批次中去执行）
事务的原则：ACID原则（原子性，一致性，隔离性，持久性）
原子性：要么都成功，要么都失败
一致性：最终的一致性（转账的总数是一致的）
持久性：事务如果没有提交，就回恢复到原状；事务要是提交了，就持久到数据库了。事务一旦提交就不可逆了。
隔离性：多个用户操作的时候，互相不受影响
隔离所导致的问题：
脏读：一个事务读取了另一个事务未提交的数据
不可重复读：在一个事务中读取表中的某一行数据，多次读取结果不同（这不一定是错误，只是某些场合不对）
幻读：是指在一个事务内读到了别的事务插入的数据，导致前后读取不一致（一般是行影响，多读了一行）

--mysql 是默认开启事务自动提交的
set autocommit = 0;--关闭
set autocommit = 1;--开启（默认开启）
--手动处理事务
set autocommit = 0;--关闭
--事务开启
start transation --标记一个事务的开始，从这个之后的sql都是在一个事务内

--提交：持久化(成功)
commit
--回滚：回到原来的样子（失败）
rollback
--事务结束
set autocommit = 1;--开启自动提交


savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名 --回滚到指定保存点
                  --删除保存点

索引

索引是帮助mysql高效获取数据的数据结构。

索引的分类

• 主键索引
  唯一的标识，主键不可重复，只能有一个列作为主键

• 唯一索引
  避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

• 常规索引
  默认的，index,key关键字来设置

• 全文索引
  在特定的数据库引擎下才有，muisam
  快速定位数据

--显示所有的索引信息
show index from table_name;
--增加一个索引
alter table school.student add fulltext index studentname(`studentname`);
--分析sql执行的状况

用户管理

命令操作

--用户表 mysql.user
本质对这张用户表进行增删改查
--创建用户
create user gabe identified '123456';
--修改密码（修改当前用户密码）
set password = password('123456');
--修改密码（修改指定用户密码）
set password for gabe = password('123456');
--给用户重命名
rename user gabe to gabe2;
--用户授权(除了给别人授权都可以)
grant all privileges on *.* to gabe2;
--查询权限
show grants for gabe--查询普通用户
show grants for root@localhost--查询root用户权限 
--撤销权限
remove all privileges on *.* from gabe2;

数据库备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移
  备份方式：
• 直接拷贝物理文件
• 使用图像化界面
• 使用命令行导出 mysqldump

--将两个数据库student 和 result 导出到D盘
mysqldump -hlocalhost -uroot -p123456 school student result > D:/a.sql
--登录的情况下，切换到指定的数据库。导入+要导入的文件
source D:/a.sql

PS：导出sql文件给他人

规范数据库的设计

当数据库比较复杂的时候，就需要设计了
糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据的插入和删除都会麻烦、异常（屏蔽使用物理外键）
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统
  软件开发中关于数据库的设计：
• 分析需求：分析业务和需要处理的数据库需求
• 概要设计：设计关系图

三大范式

为什么需要数据规范化：

• 信息重复
• 更新异常
• 插入异常：无法显示正常信息
• 删除异常：丢失有效的信息
  三大范式：
• 第一范式
  原子性：要求数据库表中的每一列都是不可分割的原子数据项。
• 第二范式
  前提：满足第一范式。每张表只描述一件事情
• 第三范式
  前提：满足第一范式、第二范式。需要确保数据表中的每一项数据都和主键直接相关，而不是间接相关。

SQL注入问题

sql存在安全漏洞，
总结自狂神mysql教程狂神mysql教程