Linux——MySQL高级(day19)

目录

一、用户与权限管理

1.1 创建与赋予权限

1.2 查看和回收权限

1.3 修改密码

1.4 删除用户

1.5 查看当前登录

二、事务

2.1 概述

2.2 手动提交

2.3 自动提交

2.4 隐式处理

三、视图和索引

3.1 视图

3.2 索引 

四、存储过程

4.1 创建、调用和删除存储过程

4.2 存储过程的变量

4.3 条件语句

4.4 while循环语句

五、函数

5.1 内置函数-字符串函数

5.2 日期时间函数

5.3 自定义函数

5.4 查询变量

六、Python DB-API

七、查询实战

八、小结

8.1 知识总结

8.2 mysql索引区别

8.3 mysql存错过程

8.4 mysql函数

8.5 存储过程和函数区别


一、用户与权限管理

1.1 创建与赋予权限

Linux——MySQL高级(day19)_第1张图片Linux——MySQL高级(day19)_第2张图片

create user 'peter'@'%' identified by '123465'   # 创建用户 peter,
                                                 # %:允许所有用户登录这个用户访问数据库

刚创建的新用户是什么权限都没有,需要赋予权限

grant select on mysql.* to 'peter'@'%';
# 赋予select查看权限; .*表示可查看mysql下的所有表,也可以选择性允许查看表
flush privileges;         # 记得刷新一下,让修改立即生效

(1)本地赋予创建用户并赋予权限

Linux——MySQL高级(day19)_第3张图片

(2)在外部访问数据库,没给权限时,访问失败,如第一行命令,给完权限可以访问,并可以select查看

Linux——MySQL高级(day19)_第4张图片

1.2 查看和回收权限

Linux——MySQL高级(day19)_第5张图片

Linux——MySQL高级(day19)_第6张图片

Linux——MySQL高级(day19)_第7张图片

show grants for python;会报错,因为host默认是@%

所以要修改 show grants for python@localhost;

1.3 修改密码

Linux——MySQL高级(day19)_第8张图片

1.4 删除用户

Linux——MySQL高级(day19)_第9张图片

Linux——MySQL高级(day19)_第10张图片

1.5 查看当前登录

查看当前登录的用户:是本机登录的root用户

Linux——MySQL高级(day19)_第11张图片

二、事务

2.1 概述

Linux——MySQL高级(day19)_第12张图片

Linux——MySQL高级(day19)_第13张图片

Linux——MySQL高级(day19)_第14张图片

2.2 手动提交

Linux——MySQL高级(day19)_第15张图片Linux——MySQL高级(day19)_第16张图片

Linux——MySQL高级(day19)_第17张图片

begin开启事务后,未提交,删除的修改命令储存在本地缓存中,会回滚,所以另一终端看没有删除,提交后就会删除

Linux——MySQL高级(day19)_第18张图片

Linux——MySQL高级(day19)_第19张图片

如果有误操作还未提交的,可以使用rollback回滚一下,返回刚才的操作;没提交时,出现异常,也会自动回滚

2.3 自动提交

Linux——MySQL高级(day19)_第20张图片

 所以没有使用begin也是有事务存在的,默认就是自动提交

2.4 隐式处理

Linux——MySQL高级(day19)_第21张图片

三、视图和索引

3.1 视图

Linux——MySQL高级(day19)_第22张图片

视图:是一张虚拟的表,这个表的数据结构和数据是由select语句来指定的,不会生成真实的文件,本质上就是对查询的封装。

什么场景下用到视图

场景1:如果某个查询结果出现的非常频繁,也就是说,经常用这个查询结果来做子查询,此时就可以使用视图了,用户可以将注意力集中在所关心的数据上,可以定义结构简单,清晰的查询操作[视图]

场景2:保密的诉求,可以过滤敏感数据,比如有一个工资表,希望只有财务能看到员工工资,其他人看不到,那么就可以用视图来包装这样的数据,过滤掉敏感的字段

Linux——MySQL高级(day19)_第23张图片

Linux——MySQL高级(day19)_第24张图片

 Linux——MySQL高级(day19)_第25张图片

3.2 索引 

Linux——MySQL高级(day19)_第26张图片

 Linux——MySQL高级(day19)_第27张图片

mysql索引的区别:
主键索引:他是一种特殊的唯一索引,不允许有空值
普通索引:最基本的索引没有什么限制
唯一索引[unique]:唯一约束,跟我们的普通索引类似,索引列的值必须唯一,允许有空值
全文索引: 仅可用于 myisam,针对的是较大的数据,varchar ,text,生成全文索引很耗时间和空间
组合索引:为了更好的提高mysql效率,允许建立组合索引,遵循 最左前缀 原则

Linux——MySQL高级(day19)_第28张图片

-- create table myindex(
-- id int NOT NULL AUTO_INCREMENT,
-- test varchar(10),
-- PRIMARY KEY (`id`)
-- )ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

show INDEX from myindex;

-- ALTER table myindex add INDEX index_my_test(test);

-- drop INDEX index_my_test on myindex;

Linux——MySQL高级(day19)_第29张图片

Linux——MySQL高级(day19)_第30张图片

 Linux——MySQL高级(day19)_第31张图片

四、存储过程

4.1 创建、调用和删除存储过程

Linux——MySQL高级(day19)_第32张图片

Linux——MySQL高级(day19)_第33张图片

 Linux——MySQL高级(day19)_第34张图片

delimiter //
create PROCEDURE qiuzhi_test_pro()
BEGIN
select * from tb_student where gender='1';
END
//
delimiter;

-- 查看所有的存储过程
-- show PROCEDURE STATUS;
-- 调用存储过程
-- CALL qiuzhi_test_pro()

Linux——MySQL高级(day19)_第35张图片

Linux——MySQL高级(day19)_第36张图片

4.2 存储过程的变量

Linux——MySQL高级(day19)_第37张图片Linux——MySQL高级(day19)_第38张图片

4.3 条件语句

Linux——MySQL高级(day19)_第39张图片

Linux——MySQL高级(day19)_第40张图片

4.4 while循环语句

Linux——MySQL高级(day19)_第41张图片

DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
# 先执行这一语句,如果test_mysql_while_loop存在的话就删除

int autocommit=0; # 关闭自动提交

START TRANSACTION; # 插入一个事务

Linux——MySQL高级(day19)_第42张图片

Linux——MySQL高级(day19)_第43张图片

Linux——MySQL高级(day19)_第44张图片

此时没有索引

Linux——MySQL高级(day19)_第45张图片

加了索引index后

Linux——MySQL高级(day19)_第46张图片

五、函数

5.1 内置函数-字符串函数

Linux——MySQL高级(day19)_第47张图片

Linux——MySQL高级(day19)_第48张图片

 Linux——MySQL高级(day19)_第49张图片

5.2 日期时间函数

Linux——MySQL高级(day19)_第50张图片

 Linux——MySQL高级(day19)_第51张图片

Linux——MySQL高级(day19)_第52张图片

5.3 自定义函数

Linux——MySQL高级(day19)_第53张图片

Linux——MySQL高级(day19)_第54张图片

Linux——MySQL高级(day19)_第55张图片

最好是先判断该函数名有没有存在

Linux——MySQL高级(day19)_第56张图片

Linux——MySQL高级(day19)_第57张图片

5.4 查询变量

Linux——MySQL高级(day19)_第58张图片

 也可不先定义变量,直接使用

Linux——MySQL高级(day19)_第59张图片

Linux——MySQL高级(day19)_第60张图片函数必须要返回值,且仅仅返回一个结果集

六、Python DB-API

Linux——MySQL高级(day19)_第61张图片

 Linux——MySQL高级(day19)_第62张图片

 打开pycharm 终端输入 pip install pymysql 安装PyMySQL

Linux——MySQL高级(day19)_第63张图片

Linux——MySQL高级(day19)_第64张图片

Linux——MySQL高级(day19)_第65张图片

七、查询实战

Linux——MySQL高级(day19)_第66张图片

Linux——MySQL高级(day19)_第67张图片

Linux——MySQL高级(day19)_第68张图片

Linux——MySQL高级(day19)_第69张图片

八、小结

8.1 知识总结

Linux——MySQL高级(day19)_第70张图片

Linux——MySQL高级(day19)_第71张图片

 Linux——MySQL高级(day19)_第72张图片

8.2 mysql索引区别

mysql索引的区别:
主键索引:他是一种特殊的唯一索引,不允许有空值
普通索引:最基本的索引没有什么限制
唯一索引[unique]:唯一约束,跟我们的普通索引类似,索引列的值必须唯一,允许有空值
全文索引: 仅可用于 myisam,针对的是较大的数据,varchar ,text,生成全文索引很耗时间和空间
组合索引:为了更好的提高mysql效率,允许建立组合索引,遵循 最左前缀 原则

8.3 mysql存错过程

delimiter就是告诉mysql解释器,该段命令是否已经结束了,
是否可以执行了。默认情况下,delimiter是分号;,遇到分号就执行。
后面的双美元符号 就是告诉mysql,遇到双美元符号再执行

这是正确的存储过程写法,可以成功执行,增加了DELIMITER,
简单解释下这个命令的用途,在MySQL中每行命令都是用“;”结尾,
回车后自动执行,在存储过程中“;”往往不代表指令结束,马上运行,
而DELIMITER原本就是“;”的意思,因此用这个命令转换一下“;”为 “//”,这样
只有收到“//”才认为指令结束可以执行
-- delimiter //
-- create PROCEDURE qiuzhi_test_pro()
-- BEGIN
-- select * from tb_student where gender='1';
-- END
-- //
-- delimiter
-- 查看所有的存储过程
-- show PROCEDURE STATUS; 
-- --调用存储过程
CALL qiuzhi_test_pro()
没有添加索引的时间
500086    0.0702345    select * from myindex where test='test400000'
添加完索引的时间
500117    0.000352    select * from myindex where test='test400000'

8.4 mysql函数

-- --创建函数 
-- create FUNCTION hello_fun() returns VARCHAR(20)
-- BEGIN
-- RETURN 'hello sql';
-- end
-- show FUNCTION STATUS
-- select hello_fun()
-- delimiter $$ 
-- DROP FUNCTION IF EXISTS hello_say$$
-- create function hello_say(uname VARCHAR(10)) returns VARCHAR(20) 
-- begin 
-- return CONCAT('hello--',uname);
-- end 
-- $$ 
-- delimiter ; 
-- select hello_say('李伟')  --调用
-- select hello_say(sname) as name from tb_student  --调用

CREATE FUNCTION `total_record_var`() RETURNS varchar(200) CHARSET utf8
begin 
-- DECLARE total_count int default 0;
SELECT count(1) from tb_student into @total_count_student;
SELECT count(1) from tb_course into @total_count_course;
return CONCAT('学生表中的数据记录是--',@total_count_student,'课程表中的数据记录是--',@total_count_course);
end

-- delimiter $$ 
-- DROP FUNCTION IF EXISTS fun_if$$
-- create function fun_if(age int) returns VARCHAR(100) 
-- begin 
-- if age<20 THEN set @msg='嘿 你还不到20岁';
-- else set @msg='嘿 你已经操作20岁 可以参军 报效祖国了';
-- END if;
-- return @msg;
-- end 
-- $$ 
-- delimiter ; 
SELECT fun_if(10)

8.5 存储过程和函数区别

存储过程和存储函数区别
1: 函数的限制比较多,比如不能使用临时表,只能使用表变量,存储过程限制比较少
2:存储过程实现的功能逻辑相对来讲要复杂一些,而函数的实现功能针对性强一些
3:返回值不同,函数是必须要返回值的,且仅仅返回一个结果,存储过程可以没有返回值,但是能返回结果集
4:调用的语法不同
函数经常会嵌入到sql中使用,通过select 函数名()
存储过程通过call 语句去调用

你可能感兴趣的