MySQL高级

5 MySQL高级

本节课程学习MySQL数据库的高级操作，这些操作主要是由DBA或项目经理来完成，对于初级开发人员，不需要熟练操作，这部分内容作为了解，对于未来的工作是加分项
账户管理：创建账户、修改密码、分配权限等
存储过程：创建、删除、查看存储过程
函数：创建、删除、查看函数，mysql中定义变量、选择语句、循环语句
视图：完成查询语句的封装
事务：保证一个业务逻辑的完整性
索引：对于大数据量可以提高查询速度
练习：根据性格测试的数据设计表，并编写创建表的脚本

账户管理

在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud
MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种
服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
数据库级别账号：对特定数据库执行增删改查的所有操作
数据表级别账号：对特定表执行增删改查等所有操作
字段级别的权限：对某些表的特定字段进行操作
存储程序级别的账号：对存储程序进行增删改查的操作
账户的操作主要包括创建账户、删除账户、修改密码、授权权限等
注意：进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限
下面主要演示数据库级操作权限，因为通常都使用这种方式

• 查看所有用户

  desc user\G;

• 创建账户并授予权限

  grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

• 授权

  grant 权限名称 on 数据库 to 账户1,账户2,... with grant option;

示例

• step1：使用root登录

mysql -uroot -p
回车后写密码，然后回车

• step2:

• 用户名为tony，密码为mysql

• 操作python数据库的所有对象python.*

• 访问主机通常使用百分号%表示此账户可以使用任何ip的主机登录访问此数据库

• 访问主机可以设置成localhost或具体的ip，表示只允许本机或特定主机访问

  grant all privileges on python.* to 'tony'@'%' identified by 'mysql';

• 查看用户有哪些权限

  show grant for tony

• step3：退出root的登录
  

  quit

回收权限

• 语法如下：
  

  revoke 权限列表 on 数据库名.* from '用户名'@'主机';

示例

• step1：使用py登录后，向表classes中插入数据python3

  use python;
insert into classes(name) values('python3');

• step2：退出py

quit

• step3：回收insert权限

revoke insert on python.* from 'py'@'%';

• step4：退出root

quit

• 权限回收,inset失效

修改密码

• 语法1：不需登录修改密码

mysqladmin -u py -p password '新密码'
例：
mysqladmin -u py -p password '123456'
回车后写密码，然后回车

• 语法2：使用root登录，修改mysql数据库的user表

update user set authentication_string=password('新密码') where user='用户名';
例：
update user set authentication_string=password('123') where user='py';
刷新权限：flush privileges

删除账户

• 语法1：使用root登录

drop user '用户名'@'主机';
例：
drop user 'py'@'%';

• 语法2：使用root登录，删除mysql数据库的user表中数据
• 这种方法还要删除db_mysql中的user表

delete from user where user='用户名';
例：
delete from user where user='py';
– 操作结束之后需要刷新权限
flush privileges

忘记 root 账户密码怎么办 !_!

• 去更改配置文件,实现免密登陆root账户,在重置密码

SQL注入攻击(数据安全)

简介

SQL注入攻击是黑客对数据库进行攻击的常用手段之一。随着B/S模式应用开发的发展，使用这种模式编写应用程序的程序员也越来越多。但是由于程序员的水平及经验也参差不齐，相当大一部分程序员在编写代码的时候，没有对用户输入数据的合法性进行判断，使应用程序存在安全隐患。用户可以提交一段数据库查询代码，根据程序返回的结果，获得某些他想得知的数据，这就是所谓的SQL Injection，即SQL注入。

SQL注入攻击属于数据库安全攻击手段之一，可以通过数据库安全防护技术实现有效防护，数据库安全防护技术包括：数据库漏扫、数据库加密、数据库防火墙、数据脱敏、数据库安全审计系统。

SQL注入攻击会导致的数据库安全风险包括：刷库、拖库、撞库。

刷库

黑客通过入侵网站或内部运维人员多次从数据库中窃取新的用户资料和敏感信息的行为。
现在的刷库方法，从开发公司开始渗透，从分站的C段开始渗透，社工目标方公司，社工目标方管理人员（google那些ID的资料）。其中用到的东西，目标方的习惯（文件夹文件名命名习惯，目标方的重要邮箱，目标方的MSN，电话，管理人员的资料，MSN，MSN密码，目标方用的安全软件等等）大杀器我见过很多了，office系列，pdf等社工邮件，一个接一个。没有大杀器的，就用chm木马，lnk后门，自解压包，不要以为这些方法很容易识破，但就是可以。我社工国内某目标，用的就是自解压包搞掂的。

拖库

拖库本来是数据库领域的术语，指从数据库中导出数据。到了黑客攻击泛滥的今天，它被用来指网站遭到入侵后，黑客窃取其数据库。

撞库

撞库是黑客通过收集互联网已泄露的用户和密码信息，生成对应的字典表，尝试批量登陆其他网站后，得到一系列可以登录的用户。很多用户在不同网站使用的是相同的帐号密码，因此黑客可以通过获取用户在A网站的账户从而尝试登录B网址，这就可以理解为撞库攻击。

提及“撞库”，就不能不说“脱库”和“洗库”。

在黑客术语里面，”脱库“是指黑客入侵有价值的网络站点，把注册用户的资料数据库全部盗走的行为，因为谐音，也经常被称作“脱库”，360的库带计划，奖励提交漏洞的白帽子，也是因此而得名。在取得大量的用户数据之后，黑客会通过一系列的技术手段和黑色产业链将有价值的用户数据变现，这通常也被称作“洗库”。最后黑客将得到的数据在其它网站上进行尝试登陆，叫做”撞库“，因为很多用户喜欢使用统一的用户名密码，”撞库“也可以使黑客收获颇丰。

函数

创建

• 语法如下
  

  `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 ;

查看

• 所有函数存储在mysql数据库下的proc表中

• 说明：存储过程与函数都存储在proc表中，区别在type字段，func表中无数据

• 查看python数据库中的函数

select name,type from mysql.proc where db='python';

调用

• 语法如下

select 函数名称(参数列表);

示例

• 要求：调用自定义函数py_trim
  

  select py_trim(' a b c ');

删除

• 说明：函数的代码无法修改，如果不能满足要求可以删除后重新创建

• 语法如下

drop function 函数名称;

示例

• 要求：删除函数py_trim

drop function py_trim;

变量

临时变量

• 声明临时变量，只能在 begin … end 体重语法如下

declare 变量名 类型 default 默认值;
例：
declare x varchar(100);

• 设置变量值，语法如下
  

  set 变量名=值;
例：
set x='abc’;

用户变量/会话变量

• 只有在当前连接用户有效,其他连接用户无法访问

• 使用@标识声明用户变量

• 使用 select 来查看用户变量

定义

-- set @变量名 = 值;
set @age = 20;
-- 查看用户变量
select @age;
-- 但是如果在 select 语句中这么使用就会有问题, 因为 '=' 在 select 语句中是判断是否相等
-- mysql 使用 := 在 select 语句中表示赋值
set @maxage = 0;
select @maxage := max(age) from students;

系统变量

• 任何一个用户都可以访问

• 使用 @@来标识系统变量

• 使用 select 来查看系统变量

-- 查询所有的系统变量
show variables;
-- 查看系统变量
select @@max_connections;
-- 当前用户/会话中修改系统变量
set max_connections = 1000;
-- 全局修改,所有的访问用户都会生效
set global max_connection = 1000;

综合示例

• 要求：定义函数odd，输出1-100间的偶数

• 代码 ：

delimiter $$
create function odd () returns varchar(300)
begin
declare i int default 1;
declare x varchar(300) default '';
while i <=100 do
if i%2=0 then
set x=concat(x,' ',i);
end if;
set i=i+1;
end while;
return x;
end

$$
delimiter ;

• 调用函数
  

  select odd();


刷新

flush privileges;

存储过程

• 存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合，可以视为批处理，但是其作用不仅仅局限于批处理

• 本节主要介绍如何创建存储过程，以及如何调用、查看、修改、删除存储过程

• 存储过程也可以调用其他存储过程

• 推荐阅读：存储过程详解

创建

• 语法如下

  delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;

• 说明：delimiter用于设置分割符，默认为分号

• 在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符

示例

• 要求：创建查询过程，查询学生信息

• code:

  delimiter //
create procedure proc_stu()
begin
select * from students;
end
//
delimiter ;

• 执行效果图如下:
  

查看

• 所有存储过程和函数，都存储在mysql数据库下的proc表中

• 查看表结构

• 主要字段说明：

  • name表示名称
  • type表示类型，为存储过程、函数
  • body表示正文脚本
  • db表示属于的数据库

• 查看python数据库的所有存储过程

  select name,type,body from mysql.proc where db='python';

调用

• 语法如下

call 存储过程(参数列表);

• 要求：调用存储过程proc_stu
  

  call proc_stu();

删除

• 语法如下

drop procedure 存储过程名称;

• 要求：删除存储过程proc_stu
  

drop procedure proc_stu;

存储过程和函数的区别

相同点

• 存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.
• 存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql, 不需要重复编译
• 减少网络交互,减少网络访问流量

>

不同点

• 标识符不同,函数的标识符是 function, 过程:procedure
• 函数中有返回值,且必须有返回值,而过程没有返回值,但是可以通过设置参数类型(in,out)来实现多个参数或者返回值
• 函数使用 select 调用,存储过程需要使用 call 调用
• select 语句可以在存储过中调用,但是除了 select … into 之外的 select 语句都不能再函数中调用
• 通过 in out 参数,过程相关函数更加灵活,可以返回多个结果
• 在实际开发中根据个人喜好选择使用函数或者存储过程