MysqL数据库
Mysql常用数据类型
字符串(char、varchar)
varchar (size) 最大是65532字节并不是65532个字符
原因:
在UTF-8编码下最大21844字符 因为会预留1~3个字节用于记录大小,在UTF-8下每个字符占用3个字节
UTF-8 varchar(size) size =(65535-3) / 3 = 21844 个字符
GBK varchar(size) size =(65535-3) / 2 = 32766 个字符
字符串使用细节
细节1、
varchar (size) char(size)
size指的是字符长度并不是字节大小
细节2、
char(4)是定长,就是说,即使你插入‘aa’ ,也会占用分配的4个字符的空间
varchar(4)是变长(变化的大小),就是说,如果你插入了‘aa’,实际占用空间大小并不是四个字符,而是按照实际占用空间来分配
varchar本事还需要占用1~3个字节来记录存放的内容长度
真实占用空间= ‘aa’ + 占用的3个字节
细节3
什么时候使用char,什么时候使用varchar
- 如果数据是定长,推荐使用char,比如md5的密码,邮编,手机号,身份证号等char()
- 如果一个字段的长度是不确定,我们使用varchar,比如留言、文章
查询速度:char > varchar
日期类型的使用
DATE 日期: yyyy-MM-dd
TIME : HH:mm:ss
DATETIME : yyyy-MM-dd HH:mm:ss
时间戳(TIMESTMP):login_time -----登录时间,如果希望loign_time列自动更新时间
login_time TIMESTMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTMP
修改表-基本介绍
CRUD(基本使用)
INSERT 语句
#练习insert 语句
- 创建一张商品表 goods(id int ,goods_name varchar(10), price double);
- 添加两条记录
创建表
CREATE TABLE `goods`(
id INT,
goods_name VARCHAR(10),
price double);
添加数据
INSERT INTO `goods` (id,goods_name,price) VALUES(10, '华为手机', 3000);
INSERT INTO `goods` (id,goods_name,price) VALUES(20, '苹果手机', 3000);
SELECT * FROM goods;
INSERT 语句的细节
UPDATE 语句
多列修改“ ,” 相隔
UPDATE 语句的细节
DELETE 语句
DELETE 语句的细节
*SELECT 语句
SELECT语句的顺序
基本语法
注意:DISTINCT要查出来的数据完全一样才会去重,单个字段一样不会去重
SELECT WHERE子句常用运算符
LIKE
模糊查询中 LIKE运算符 ‘%’ 表示任何字符任何长度
BETWEEN … AND …
BETWEEN … AND… 是闭区间【80,90】 即80跟90都取得到
IN
SELECT 使用order by子句排序
ASC 升序【默认】从小到大 DESC 降序 从大到小
——使用别名排序
DESC降序从大到小
SELECT grouo by子句分组
如何理解分组
分组完之后使用having再次过滤得到想要的数据
增强group by 的使用
案例1
函数
统计/合计函数
统计函数 - count
count() 和 count(列)的区别
解释: count()返回满足条件的记录的行数
count(列):统计满足条件的某列有多少个,但是会排除为 null 的情况
合计函数 - sum、avg,MAX/min
SUM:
注意:sum仅对数值起作用
对多列求和,“,”号不能少
AVG:
MAX/min:
字符串函数
CONCAT 字符串拼接函数
REPLACE 字符替换
在job字段中如果是MANAGER 就地换成中文经理
数学函数
CONV
CONV(数值,进制,要转成的进制)
RAND
日期函数
在插入语句中使用时间戳函数
在date基础上加减日期(DATE_ADD)
DATE_ADD(日期,INTERVAL 要加的时间)
如在 2021-12-4 18:54:21 加上10分钟
DATE_ADD(‘2021-12-4 18:54:21’,INTERVAL 10 MINUTE)
细节说明
year 年 minute 分钟 second 秒 day 天
YEAR|Month|DAY| DATE (datetime)函数的使用
假如NOW()= 2021-12-4分别返回
2021 / 12 / 4
加密和系统函数
加密函数(MD5)
实际应用
流程控制语句
IF(expr1,expr2,expr3)如果expr为true,则返回expr2 否则返回 expr3
SELECT IF(TRUE,'北京','上海') FROM DUAL; 因为是TRUE 所以返回北京
SELECT IF(FALSE,'北京','上海') FROM DUAL; 因为是FALSE 所以返回上海
IFNULL(expr1,expr2) 如果expr1不为空NULL,则返回expr1,否则返回expr2
SELECT IFNULL(NULL, '哈哈哈') 因为expr1是空NULL,所以返回哈哈哈
SELECT IFNULL('Jack', '哈哈哈') 因为expr1是'Jack'不为空,所以返回'Jack'
多重分支(类似JAVA IF ELSE IF ELSE)
SELECT CASE
WHEN TRUE THEN 'Jack'
WHEN FALSE THEN 'tom'
ELSE 'mary' END
//因为WHEN 后面是TRUE THEN 后面跟着是Jack 所以成立返回Jack
//该语句返回'Jack'
SELECT CASE
WHEN FALSE THEN 'Jack'
WHEN TRUE THEN 'tom'
ELSE 'mary' END
//该语句返回'tom'
SELECT CASE
WHEN TRUE THEN 'Jack'
WHEN TRUE THEN 'tom'
ELSE 'Jack' END
//该语句返回'Jack'
//因为直接TRUE 返回了Jack 不会继续往下走了所以只返回Jack
多重分支的实际应用
limit 分页查询公式
分页公式
每页记录数 *(页码-1),每页记录数
多表查询
笛卡尔集
自连接
自连接特点
- 在同一张表内当成两张表使用
- 需要给表起别名 如 (表名 空格 表别名)
- 列名不明确,可以指定列的别名
原先表结构
案例1
将emp一张表当成两张表 分别取名 worker(工人) boss(上级),附加查询条件过滤:工人的上级编号=职员编号 这个职员就是工人职员的上级
子查询
子查询的演示
案例1
单行子查询
得到部门编号
嵌套(单行)子查询
案例2
多行子查询
把上面的查询结果当做子查询使用
在多行子查询使用all、any操作符
案例1 ALL操作符
ALL: 满足ALL条件中所有的
案例1 ANY操作符
ANY: 满足ANY条件中起其中一个就可以
多列子查询
案例1
得到smith的部门和岗位
表复制
将emp表的数据复制(插入)到my_tab01表
1、先创建一张空表 my_tab01
2、把emp表的记录复制到 my_tab01
表的自我复制
表数据的去重
第二步操作说明
- DISTINCT * 之后可以得到去重之后的my_tab02表
- 但只是我们得到的是去重之后的my_tab02实际上的my_tab02还是有重复数据的
- 将得到my_tab02去重之后的数据插入到我们新建的临时表my_tmp这时候my_tmp临时表就是一个没有重复数据的表
第三步操作说明
将有重复数据的my_tab02表清空
第四步
把临时表中的数据加入到我们刚清空记录的my_tab02表
第五步
删除临时表
此时my_tab02表数据去重完成
MySQL表外连接(左连接,右连接)
左外连接
左连接语法
SELECT ... form 表1 left join 表2 on 条件
//表1:就是左表 表2:就是右表 leftjoin 左表会完全显示
普通查询跟左外连接的区别
传统多表查询
改成左外连接
右外连接
MySQL 约束
Primary key 主键
Unique 唯一
主键跟unique的区别
主键:唯一,不为空,一张表只能有一个主键,即使是复合主键也只能有一个复合主键
unique:不重复(唯一),可以有多个null,一张表可以有多个unique字段
Foreign key 外键
Foreign key 外键演示
解释:从my_stu表 class_id字段作为外键指向my_class表id字段
Foreign key 外键约束测试
Foreign key 外键细节
Check 约束(Mysql5.7不生效)
Check 使用
创建表并添加check约束
AUTO_INCREMENT 自动递增
修改表自动递增开始值
ALTER TABLE login_info AUTO_INCREMENT = 103
ALTER TABLE 表名 AUTO_INCREMENT = 新的开始值
MySQL 索引优化速度
使用索引
添加索引后会增加表文件的大小
索引就是拿空间来换取时间(效率)
索引优化的效果
- 表 emp 数据有八百万条,当查询条件是 Where ename = ‘axJxC’ 此时执行速度是4.5S
- 在表emp中ename列(字段)添加索引后执行速度是0.003s
- 创建索引后,只对创建了索引的列(字段)有效,当查询条件变了一个字段需要重新添加索引
索引的原理
没有索引如何查找记录?
如果没有索引会进行全表扫描从1开始比对直到比对到9找到我们要的记录
建立索引为什么会快?
如果对表进行dml(修改,删除,添加)会对索引重新进行维护,但是在实际应用中查询量远远大于增删改量,所以利大于弊的!
对id建立了索引,索引的结构
创建索引
索引的种类
创建索引
创建唯一索引
创建普通索引
查询索引
MySQL 事务
什么是事务?
事务的理解
事务的具体操作和概念
设置了一个a保存点执行了插入语句插入tom
设置了一个b保存点执行了插入语句插入jack
当回退到b保存点则数据表只有一条tom记录说明撤销了插入jack的操作
Rollback 回到事务开始的时候
commit 提交事务所有操作生效保存点自动删除
事务细节
我们一般写的增删改语句默认是没有事务开启,是自动提交的无法回滚执行即生效
MySQL 事务隔离级别
事务的ACID特性
事务隔离级别介绍
帮助理解
脏读:一个连接的事务中查询到,另一个连接的事务所做增删改操作后的记录
不可重复读:一个连接在一个事务中多次进行同一查询,由于另一个连接的事务进行修改或删除导致查询出不同结果发生不可重复读
幻读:一个连接在一个事务中多次进行同一查询,由于另一个连接的事务进行插入导致查询出不同结果发生幻读
演示MySQL 事务隔离级别
隔离级别指令
MySQL 表类型和存储引擎
查看所有的存储引擎
MySQL表类型和存储引擎
表的类型是存储引擎决定的
memory 引擎应用场景
MySQL 视图(View)
视图基本概念
视图和对应真是表的关系
视图的使用
视图的创建
视图细节
MySQL 管理
MySQL用户的创建与删除
用户的创建
用户的删除
用户权限
MySQL 中的权限
给用户授权
授权语法
回收授权
部分老版本需要刷新才能生效权限(执行权限生效指令)
指令演示