MySql高级知识的一些总结

Num01–>mysql账户管理

Test01–>定义

在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crudMySQL账户体系：根据账户所具有的权限的不同，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的登录quitstep4：使用py1账户登录mysql -u py1 -p回车后写密码，然后回车

Test03–>回收权限

需要使用实例级账户登录后操作，以root为例如果不希望某用户拥有此权限，可以将此权限从用户上撤销语法如下：revoke 权限列表 on 数据库名.* from  '用户名'@'主机';示例step1：使用py1登录后，向表classes中插入数据python3use python;insert into classes(name) values('python3');step2：退出py1quit使用root登录mysql -uroot -p回车后写密码，然后回车step3：回收insert权限revoke insert on python.* from 'py1'@'%';step4：退出rootquitstep5：使用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 存储过程名称(参数列表)beginsql语句end//delimiter ;说明：delimiter用于设置分割符，默认为分号在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符示例要求：创建查询过程，查询学生信息step1：设置分割符delimiter //step2：创建存储过程create procedure proc_stu()beginselect * 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_stucall proc_stu();

Test05–>mysql删除存储过程

语法如下drop procedure 存储过程名称;说明：存储过程内部的sql语句无法修改，如果之前创建的存储过程不能满足要求，可以删除后重新创建示例：要求：删除存储过程proc_studrop procedure proc_stu;

Num03–>mysql函数

Test01–>mysql创建函数

语法如下delimiter $$create function 函数名称(参数列表) returns 返回类型beginsql语句end$$delimiter ;说明：delimiter用于设置分割符，默认为分号在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符示例要求：创建函数py_trim，用于删除字符串左右两侧的空格step1：设置分割符delimiter $$step2：创建函数create function py_trim(str varchar(100)) returns varchar(100)begindeclare 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_trimselect py_trim(' a ');

Test04–>mysql函数删除

说明：函数的代码无法修改，如果不能满足要求可以删除后重新创建语法如下drop function 函数名称;示例要求：删除函数py_trimdrop 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 scoresinner 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=innodbshow 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：连接终端1select * 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不仅要保存数据，还要保存一下索引文件建立索引会占用磁盘空间的索引文件示例创建测试表testindexcreate table test_index(title varchar(10));向表中加入十万条数据创建存储过程proc_test，在存储过程中实现插入数据的操作step1：定义分割符delimiter //step2：定义存储过程create procedure proc_test()begindeclare i int default 0;while i<100000 doinsert into test_index(title) values(concat('test',i));set i=i+1;end while;end //step3：还原分割符delimiter ;执行存储过程proc_testcall proc_test();查询开启运行时间监测：set profiling=1;查找第1万条数据test10000select * 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;