Num01–>mysql账户管理
Test01–>定义
在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud
MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种
1、服务实例级账号：启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
2、数据库级别账号：对特定数据库执行增删改查的所有操作
3、数据表级别账号：对特定表执行增删改查等所有操作
4、字段级别的权限：对某些表的特定字段进行操作
5、存储程序级别的账号：对存储程序进行增删改查的操作
6、账户的操作主要包括创建账户、删除账户、修改密码、授于权限等
注意：进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限
Test02–>授予权限
需要使用实例级账户登录后操作，以root为例
常用权限主要包括：create、alter、drop、insert、update、delete、select
如果分配所有权限，可以使用all privileges
创建账户并授权
语法如下：
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';
授权
语法如下：
grant 权限名称 on 数据库 to 账户1,账户2,... with grant option;
示例：
step1：使用root登录
mysql -uroot -p
回车后写密码，然后回车
step2：创建账户并授予所有权限，说明如下
用户名为py1，密码为123
操作python数据库的所有对象python.*
访问主机通常使用百分号%表示此账户可以使用任何ip的主机登录访问此数据库
访问主机可以设置成localhost或具体的ip，表示只允许本机或特定主机访问
grant all privileges on python.* to 'py1'@'%' identified by '123';
step3：退出root的登录
quit
step4：使用py1账户登录
mysql -u py1 -p
回车后写密码，然后回车
Test03–>回收权限
需要使用实例级账户登录后操作，以root为例
如果不希望某用户拥有此权限，可以将此权限从用户上撤销
语法如下：
revoke 权限列表 on 数据库名.* from '用户名'@'主机';
示例
step1：使用py1登录后，向表classes中插入数据python3
use python;
insert into classes(name) values('python3');
step2：退出py1
quit
使用root登录
mysql -uroot -p
回车后写密码，然后回车
step3：回收insert权限
revoke insert on python.* from 'py1'@'%';
step4：退出root
quit
step5：使用py1账户登录
mysql -u py1 -p
回车后写密码，然后回车
step6：向表classes中插入数据python3，就会报错
use python;
insert into classes(name) values('python3');
Test04–>账户操作
需要使用实例级账户登录后操作，以root为例
主要操作包括：
查看所有用户
修改密码
删除用户
1、查看所有用户
所有用户及权限信息存储在mysql数据库的user表中
查看user表的结构
desc user\G;
主要字段说明：
host表示允许访问的主机
user表示用户名
authentication_string表示密码，为加密后的值
查看所有用户
select host,user,authentication_string from user;
2、修改密码
语法1：不需登录
mysqladmin -u py1 -p password '新密码'
例：
mysqladmin -u py1 -p password '123456'
回车后写密码，然后回车
语法2：使用root登录，修改mysql数据库的user表
使用password()函数进行密码加密
注意修改完成后需要刷新权限
update user set authentication_string=password('新密码') where user='用户名';
例：
update user set authentication_string=password('123') where user='py1';
刷新权限：flush privileges
语法1用于账户自己修改密码
语法2用于修改自己或其它账户的密码，一般是dba或经理修改员工的密码
3、删除账户
语法1：使用root登录
drop user '用户名'@'主机';
例：
drop user 'py1'@'%';
语法2：使用root登录，删除mysql数据库的user表中数据
delete from user where user='用户名';
例：
delete from user where user='py1';
推荐使用语法1删除用户
如果主机的字母大写时，使用语法1删除失败，采用语法2方式
Num02–>mysql存储过程
Test01–>定义
存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合，可以视为批处理，但是其作用不仅仅局限于批处理
Test02–>mysql创建存储过程
语法如下
delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;
说明：delimiter用于设置分割符，默认为分号
在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符
示例
要求：创建查询过程，查询学生信息
step1：设置分割符
delimiter //
step2：创建存储过程
create procedure proc_stu()
begin
select * from students;
end
//
step3：还原分割符
delimiter ;
Test03–>mysql查看存储过程
所有存储过程和函数，都存储在mysql数据库下的proc表中
查看表结构
desc mysql.proc\G;
主要字段说明：
name表示名称
type表示类型，为存储过程、函数
body表示正文脚本
db表示属于的数据库
查看python数据库的所有存储过程
select name,type,body from mysql.proc where db='python';
Test04–>mysql调用存储过程
语法如下
call 存储过程(参数列表);
示例：
要求：调用存储过程proc_stu
call proc_stu();
Test05–>mysql删除存储过程
语法如下
drop procedure 存储过程名称;
说明：存储过程内部的sql语句无法修改，如果之前创建的存储过程不能满足要求，可以删除后重新创建
示例：
要求：删除存储过程proc_stu
drop procedure proc_stu;
Num03–>mysql函数
Test01–>mysql创建函数
语法如下
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter ;
说明：delimiter用于设置分割符，默认为分号
在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符
示例
要求：创建函数py_trim，用于删除字符串左右两侧的空格
step1：设置分割符
delimiter $$
step2：创建函数
create function py_trim(str varchar(100)) returns varchar(100)
begin
declare x varchar(100);
set x=ltrim(rtrim(str));
return x;
end
$$
step3：还原分割符
delimiter ;
Test02–>mysql查看函数
所有函数存储在mysql数据库下的proc表中
说明：存储过程与函数都存储在proc表中，区别在type字段，func表中无数据
查看python数据库中的函数
select name,type from mysql.proc where db='python';
Test03–>mysql函数调用
语法如下
select 函数名称(参数列表);
示例
要求：调用自定义函数py_trim
select py_trim(' a ');
Test04–>mysql函数删除
说明：函数的代码无法修改，如果不能满足要求可以删除后重新创建
语法如下
drop function 函数名称;
示例
要求：删除函数py_trim
drop function py_trim;
Test05–>mysql函数变量
声明变量，语法如下
declare 变量名 类型 default 默认值;
例：
declare x varchar(100);
设置变量值，语法如下
set 变量名=值;
例：
set x='abc';
使用变量：将变量写到表达式中，即可调用变量的值
Test06–>mysql函数判断
语法如下
if 条件1 then
语句1;
elseif 条件2 then
语句2;
else
语句
end if;
Test07–>mysql函数循环
语法如下
while 条件 do
语句;
end while;
退出循环：leave，相当于break
退出本次循环：iterate，相当于continue
Num04–>mysql视图
对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦
解决：定义视图
视图本质就是对查询的封装
定义视图，建议以v_开头
create view 视图名称 as select语句;
例：创建视图，查询学生对应的成绩信息
create view v_stu_sco as
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;
查看视图：查看表会将所有的视图也列出来
show tables;
删除视图
drop view 视图名称;
例：
drop view v_stu_sco;
使用：视图的用途就是查询
select * from v_stu_score;
Num05–>mysql事务
Test01–>定义
为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景
例如：A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：
检查A的账户余额>500元；
A账户扣除500元；
B账户增加500元；
正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此
事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性
事务四大特性(简称ACID)
原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
事务命令
要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
查看表的创建语句，可以看到engine=innodb
show create table students;
修改数据的命令会触发事务，包括insert、update、delete
开启事务，命令如下：
开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
begin;
提交事务，命令如下
将缓存中的数据变更维护到物理表中
commit;
回滚事务，命令如下：
放弃缓存中变更的数据
rollback;
Test02–>提交
为了演示效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表
step1：连接
终端1：查询学生信息
select * from students;
step2：增加数据
终端2：开启事务，插入数据
begin;
insert into students(sname) values('张飞');
终端2：查询数据，此时有新增的数据
select * from students;
step3：查询
终端1：查询数据，发现并没有新增的数据
select * from students;
step4：提交
终端2：完成提交
commit;
step5：查询
终端1：查询，发现有新增的数据
select * from students;
Test03–>回滚
为了演示效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表
step1：连接
终端1
select * from students;
step2：增加数据
终端2：开启事务，插入数据
begin;
insert into students(sname) values('张飞');
终端2：查询数据，此时有新增的数据
select * from students;
step3：查询
终端1：查询数据，发现并没有新增的数据
select * from students;
step4：回滚
终端2：完成回滚
rollback;
step5：查询
终端1：查询数据，发现没有新增的数据
select * from students;
Num06–>mysql索引
思考：在图书馆中是如何找到一本书的？
一般的应用系统对比数据库的读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重
当数据库中数据量很大时，查找数据会变得很慢
优化方案：索引
主键和唯一索引，都是索引，可以提高查询速度
主键是数据物理存储的位置
索引会单独创建一个目录，对应数据的位置
索引分单列索引和组合索引
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引
组合索引，即一个索引包含多个列
语法
查看索引
show index from 表名;
创建索引
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
删除索引：
drop index 索引名称 on 表名;
缺点
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
建立索引会占用磁盘空间的索引文件
示例
创建测试表testindex
create table test_index(title varchar(10));
向表中加入十万条数据
创建存储过程proc_test，在存储过程中实现插入数据的操作
step1：定义分割符
delimiter //
step2：定义存储过程
create procedure proc_test()
begin
declare i int default 0;
while i<100000 do
insert into test_index(title) values(concat('test',i));
set i=i+1;
end while;
end //
step3：还原分割符
delimiter ;
执行存储过程proc_test
call proc_test();
查询
开启运行时间监测：
set profiling=1;
查找第1万条数据test10000
select * from test_index where title='test10000';
查看执行的时间：
show profiles;
为表title_index的title列创建索引：
create index title_index on test_index(title(10));
执行查询语句：
select * from test_index where title='test10000';
再次查看执行的时间
show profiles;
|
