MySQL 高级用法

1.用户与权限管理

创建用户并分配权限：

创建用户：create user '用户名'@'ip地址'

分配权限：grant 权限 on 数据库 to ‘用户名’@'ip地址'

立即生效：flush privileges;

查看权限：show grants for 用户名

回收权限：revoke select on *.* from 'Sway'@'%'

还可以对数据库中的表进行权限设置。

普通用户修改自己的密码： MySQLadmin -upython -p password 新密码

root账号修改普通用户密码：

update MySQL.user set authentication_string=password(123)（新密码） where user='用户名'

刷新权限：flush privileges

删除用户：drop user '用户名'@'主机'

或者进入mysql库，使用：delete from user where user='用户名'

2.事务

事务特性：原子性（不可被分割）、一致性（系统处于数据一致状态）、隔离性（事务之间互不干扰）、持久性（事务一旦提交，对数据的改变是永久性的）

手动提交事务：begin开启一个事务，在进行commit之前，内容都存到缓存中，若过程中出现错误，会自动回滚（rollback）到事务开始前的状态

自动提交：默认情况下每一条sql都是事务自动执行。

set autocommit = 0时，禁止自动提交，直到使用commit或rollback

隐式提交：执行（CREATE ALTER DROP TRUNCATE RENAME GRANT REVOKE）会进行隐式提交

隐式回滚：客户端出现问题时，连接中断，崩溃等。

3.视图和索引

视图：是一张虚拟的表，这个表的数据和结构是由select语句来指定的，不会生成真实的文件。所以视图本质上就是查询，对查询的封装。可以直接用视图的名字代替select查询结果

视图使用场景1：某个查询结果出现的特别频繁（经常拿这个结果做子查询），此时可以使用视图，来简化操作，用户可以将注意力集中在所关心的数据上，可以定义结构简单清晰的查询操作【视图】。

视图使用场景2：保密诉求。可以过滤敏感数据，比如工资表，可以使用视图来包装这样的数据。

创建视图：create view 视图名词 as select语句;

查看数图：查看表的时候会将所有的视图也列出来

删除视图：drop view 视图名称

调用视图：select * from 视图名称

索引：针对数据量大的数据库进行查询的优化方法。

主键索引：创建：alter table 表名 add primary key(列名);用来区分表中的唯一记录，创建的时候就有，是一种特殊的唯一索引，不允许有空值。

唯一索引：创建：alter table 表名 add unique(列名);唯一约束，和普通索引类似，但索引列的值必须唯一，允许有空值。

普通索引：创建：alter table 表名 add index(列名);最基本的索引，没有什么限制。

全文索引：创建：alter table add fulltext(列名);仅可用于 myisam引擎，针对较大的数据，varchar,text,生成全文索引耗时间和空间

组合索引：创建：alter table 表名 add index 索引名(列1，列2，列2);为了更好提高mysql效率，允许建立组合索引，遵循最左前缀原则。

查看索引：show index from 表名

删除索引：drop index 索引名 on 表名

4.存储过程

是一个特殊的函数，是对一些列sql语句的包装

创建存储过程语法：

delimiter //

create procedure 存储过程名称（参数列表）

begin

sql语句

end

//

delimiter;

查看所有的存储过程：show procedure status;

存储过程调用：call qiuzhi_test_pro()

删除存储过程：drop procedure 存储过程名称

存储过程中的变量：

声明变量：DECLARE 变量名 数据类型 DEFAULT 默认值;(变量名可以有任何Mysql数据类型，如int,varchar,datetime等)

变量赋值：set，也可将select语句的查询结果分配给变量

条件语句：

if 语句：IF expression THEN statements;    END IF

if else语句：

while语句: WHILE expression DO statements END WHILE

5.函数

内置函数-字符串函数：

select ascii('a')

select char(97)

concat(),substring(),length(),left(),right()

ltrim(str),rtrim(str),trim(方向 'x' from 'xxrfxxx'),方向有leading,both,trailing

space(),replace(),lower(),upper(),year(),month()等等，使用时自查

自定义函数：

创建：

delimiter $$

create function 函数名称（参数列表）returns 返回类型

begin

sql 语句

end

$$

delimiter;

存储过程和函数的区别：

1.函数的限制较多，比如不能使用临时表，只能使用表变量。存储过程限制较少，实现的功能逻辑比函数要复杂一些，函数的实现功能针对性强一些。

2.返回值不同，函数是必须要返回值，且仅仅返回一个结果集。存储过程可以没有返回值，返回的数据比较灵活，可以返回结果集。

3.调用的语法不同：函数经常嵌入到sql中使用，通过select 函数名（）

存储过程通过call去调用。

6.python 连接数据库

使用pymysql模块，首先导入

使用connect函数创建连接，里面有需要输入的内容：例如con = connect(host='192.168.19.129',..)

建立游标：cur = con.cursor()

使用游标进行命令输入：cur.execute('select * from students')括号里的字符串就是需要执行的sql语句

使用fetchall显示所有：result = cur.fetchall()

使用循环显示指定内容：for i in result:         print('姓名：{0}  地址：{1}'.format(i[1],i[5]))

根据自己的表格内容调整格式化语句

最后一定不要忘记关闭连接：cur.close()\ncon.close()

插入数据：

可以将sql语句赋给一个变量：insertsql='insert into students(name,hometown) values('张三','汉东')'

将变量给到execute中：cur.execute(insertsql)

最后结束事务：conn.commit()

也可以将该语句以类似于格式化的方式进行描述：cur.execute('select * from students where id=%s',[5])意思是显示出students表中，id=5的数据信息。