目录
一、用户与权限管理
1.1 创建与赋予权限
create user 'peter'@'%' identified by '123465' # 创建用户 peter,
# %:允许所有用户登录这个用户访问数据库
刚创建的新用户是什么权限都没有,需要赋予权限
grant select on mysql.* to 'peter'@'%';
# 赋予select查看权限; .*表示可查看mysql下的所有表,也可以选择性允许查看表
flush privileges; # 记得刷新一下,让修改立即生效
(1)本地赋予创建用户并赋予权限
(2)在外部访问数据库,没给权限时,访问失败,如第一行命令,给完权限可以访问,并可以select查看
1.2 查看和回收权限
show grants for python;会报错,因为host默认是@%
所以要修改 show grants for python@localhost;
1.3 修改密码
1.4 删除用户
1.5 查看当前登录
查看当前登录的用户:是本机登录的root用户
二、事务
2.1 概述
2.2 手动提交
begin开启事务后,未提交,删除的修改命令储存在本地缓存中,会回滚,所以另一终端看没有删除,提交后就会删除
如果有误操作还未提交的,可以使用rollback回滚一下,返回刚才的操作;没提交时,出现异常,也会自动回滚
2.3 自动提交
所以没有使用begin也是有事务存在的,默认就是自动提交
2.4 隐式处理
三、视图和索引
3.1 视图
视图:是一张虚拟的表,这个表的数据结构和数据是由select语句来指定的,不会生成真实的文件,本质上就是对查询的封装。
什么场景下用到视图:
场景1:如果某个查询结果出现的非常频繁,也就是说,经常用这个查询结果来做子查询,此时就可以使用视图了,用户可以将注意力集中在所关心的数据上,可以定义结构简单,清晰的查询操作[视图]
场景2:保密的诉求,可以过滤敏感数据,比如有一个工资表,希望只有财务能看到员工工资,其他人看不到,那么就可以用视图来包装这样的数据,过滤掉敏感的字段
3.2 索引
mysql索引的区别:
主键索引:他是一种特殊的唯一索引,不允许有空值
普通索引:最基本的索引没有什么限制
唯一索引[unique]:唯一约束,跟我们的普通索引类似,索引列的值必须唯一,允许有空值
全文索引: 仅可用于 myisam,针对的是较大的数据,varchar ,text,生成全文索引很耗时间和空间
组合索引:为了更好的提高mysql效率,允许建立组合索引,遵循 最左前缀 原则
-- 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;
四、存储过程
4.1 创建、调用和删除存储过程
delimiter //
create PROCEDURE qiuzhi_test_pro()
BEGIN
select * from tb_student where gender='1';
END
//
delimiter;
-- 查看所有的存储过程
-- show PROCEDURE STATUS;
-- 调用存储过程
-- CALL qiuzhi_test_pro()
4.2 存储过程的变量
4.3 条件语句
4.4 while循环语句
DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
# 先执行这一语句,如果test_mysql_while_loop存在的话就删除
int autocommit=0; # 关闭自动提交
START TRANSACTION; # 插入一个事务
此时没有索引
加了索引index后
五、函数
5.1 内置函数-字符串函数
5.2 日期时间函数
5.3 自定义函数
最好是先判断该函数名有没有存在
5.4 查询变量
也可不先定义变量,直接使用
函数必须要返回值,且仅仅返回一个结果集
六、Python DB-API
打开pycharm 终端输入 pip install pymysql 安装PyMySQL
七、查询实战
八、小结
8.1 知识总结
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 语句去调用