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的数据信息。