1. 变量相关
临时变量
-- 定义在函数体或存储过程中的变量
-- 用法在讲函数时会提到
用户变量, 也称会话变量
-- 用户变量只对当前连接用户有效, 其他连接用户无法访问
-- 使用 @ 标识符声明用户变量
SET @age = 20; -- 定义一个值为 20 的 age 变量
-- 使用 SELECT 来查看用户
SELECT @age;
-- 使用 := 来在查询语句中进行赋值
SELECT @maxage := MAX(age) FROM student;
-- 注意事项:
-- 不区分大小写
-- 同一个账户, 一旦退出, 用户变量也不会再存在
-- 如果用户变量不存在, 使用 SELECT 查看会返回 NULL
系统变量
-- 任何一个用户都可以访问
-- 使用 @@ 来作为标识符
-- 查看所有的系统变量
SHOW VARIABLES;
SHOW VARIABLES\G; -- 可以使用显示不用过长
-- 同样使用 SELECT 语句来进行查询
SELECT @@age;
-- 修改系统变量
-- 临时修改, 只有当前用户使用这个值
SET variable_name = new_value; -- 一需要 @@
SET wait_timeout = 20;
-- 永久修改
- SET GLOBAL variable_name = new_value;
- SET GLOBAL wait_timeout = 20;
-- 不能自己定义一个新的系统变量
-- 不区分大小写
2. 账户管理
账户管理的应用场景
在实际项目开发中, 不可能让每个开发人员都使用 root 账户进行登录
根据开发人员角色的不同, 会分配具有不同权限的账户
MySQL 账户体系
服务实例级账户: 启动了一个 mysqld, 即为启动一个数据库实例如果某用户拥有服务实例分配的权限, 那么该账号就可以删除所有的数据库, 连同这些库中的表
数据库级别账户: 对特定数据库执行增删改查 (CRUD) 的所有操作, 最常用的一种级别
数据表级别账户: 对特定表执行 CRUD 的所有操作
字段级别账户: 对某些表的特定字段进行操作
存储程序级别账户: 对存储程序进行 CRUD 的操作
查看账户
-- 需要有 服务实例级 的权限登录后进行操作
-- 账户都存储在 mysql.user 表中
DESC mysql.user; -- 查看 mysql.user 表的结构
-- 关注字段: host, user, authentication_string(即 password)
SELECT host, user, authentication_string FROM mysql.user;
-- 这里看到的密码是加密后的
创建账户
-- 需要使用实例级别账户登录后操作 []表示可省略
CREATE USER user_name [IDENTIFIED BY 'password'];
-- 需要注意的是, 这里有 user_name 是区分大小写的, 并且不能重复创建
-- 示例:
CREATE USER demon; -- 无密码, 输入 mysql -u demon 即可登录
CREATE USER demon IDENTIFIED BY 'demon';
授权权限
-- 需要使用实例级别账户登录后操作
-- 常用权限主要包括: CREATEALTERINSERTUPDATEDELETESELECT
-- 如果需要分配所有权限, 使用 ALL PRIVILEGES
-- 创建账户并授权
GRANT privilege1,p2.... ON database_name to 'username'@'hostname' [IDENTIFIED 'password'];
-- 对已经存在用户进行授权
GRANT privilege1,p2.... ON database_name to username1, username2,... WITH GRANT OPTION;
-- 注意这个语句只能增加权限, 不能修改权限
-- 示例
-- 创建一个新账户 semon 并对 test 数据库授予所有权限
GRANT ALL PRIVILEGES ON test.* TO 'semon'@'%' IDENTIFIED BY 'semon';
-- 注意这里数据库名后需要加 .*, 表示对数据库中的所有都授予权限, 包括表名, 函数等
-- % 表示任意主机, 一般不写死
-- 增加 demon 账户的 CREATE 权限
GRANT CREATE ON test.* TO demon WITH GRANT OPTION;
查看账户权限
SHOW GRANTS FOR username;
刷新权限设置
-- 使用这个命令使权限生效
-- 尤其是你对那些权限表 userdbhost 等做了 update 或者 delete 更新的时候
-- 如果遇到使用 grant 后权限没有更新的情况, 只要对权限做了更改就使用 FLUSH PRIVILEGES 命令来刷新权限
FLUSH PRIVILEGES;
回收权限
-- 需要使用实例级别账户登录后操作
-- 使用 revoke 可以将用户的权限进行撤销
REVOKE privilege1, p2 ... ON database_name FROM 'usernmae'@'hostname';
-- 示例
-- 回收 semon 账户的 CREATE 权限
REVOKE CREATE ON test.* FROM 'semon'@'%';
删除账户
-- 语法 1: 使用 root 登录
DROP USER 'username'@'hostname';
-- 语法 2: 使用 root 登录, 操作 mysql.user 表
DELETE FROM mysql.user WHERE user = 'username';
-- 修改后需要刷新
FLUSH PRIVILEGES;
修改密码
-- 不需要登录
-- 一般用于自己修改自己账户的密码
mysqladmin -u username -p password 'new-password';
-- 使用实例级别账户登录, 如 root
-- 一般用于修改自己或别人的密码, 一般是级别高的人管理其他人的密码
UPDATE mysql.user SET authentication_string = PASSWORD('new-password') WHERE user = 'username';
-- 修改完后需要刷新权限
FLUSH PRIVILEGES;
3. 函数
函数的创建
- CREATE FUNCTION function_name(paramlist) RETURNS return_type function_body
- /*
- 1. 其中, 参数列表的格式:
- 参数名 参数类型, 参数名 参数类型...
- 2. function_body
- 用 begin... end 包裹
- 3. 临时变量的声明
- 所谓临时变量, 就是 begin..end 间的变量
- delare 变量名 变量类型 [default 默认值, 可选]
- 4. 临时变量赋值
- set 变量名 = 表达式
- 5. 函数体中的语句用英文的 ; 隔开
- 但是由于 MySQL 中标志一个 SQL 语句的结束用的就是 ;
- 所以, 在执行函数时, 遇到第一个带 ; 的语句, 就结束了
- 因此, 如果想函数正常执行, 需要临时修改 SQL 语句的结束标志符
- DELIMITER 要修改的新的标志符
- */
-- 一个完整的函数创建的语法
- DELIMITER // -- 将 SQL 语句结束符改为 //
- CREATE FUNCTION function_name(p1 type1, p2 type2...) RETURNS type
- BEGIN
statement1; -- 语句之间用 ; 隔开
...
RETURN result; -- 函数是一定有返回值的
- END
- // -- 整体函数的结束
DELIMITER ; -- 改回为 ;
查看函数是否创建成功
-- 函数创建之后, 会存在 mysql.proc 表
-- mysql 是 database
SELECT name, type, db FROM mysql.proc [WHERE name = 'function_name'];
调用函数
SELECT 函数名();
删除函数
-- 由上面知道, 函数是在 mysql.proc 表中
-- 所以如果想删除函数, 也只需要在该表中进行操作即可
DELETE FROM mysql.proc WHERE name = 'function_name';
简单示例
-- 定义一个实现加法的函数
DELIMITER //;
-- 注意方法名不要与 mysql.proc 中内置的一些方法名重复, 会报错
- CREATE FUNCTION my_add(a INT, b INT) RETURNS INT
- BEGIN
- DECLARE res INT;
- SET res = a + b;
- RETURN res;
- END
- //
- DELIMITER ;
-- 输出 0~100 中的偶数
-- 用这个例子来介绍 while 循环语句与 if 语句
- DELIMITER //
- CREATE FUNCTION my_printodd() RETURNS VARCHAR(500)
- BEGIN
- DECLARE i INT DEFAULT 0;
- DECLARE res VARCHAR(500) DEFAULT '';
- WHILE i < 100 DO
IF i % 2 = 0 THEN -- 注意这里判断相等, 只有一个 = 号
SET res = CONCAT(res, ' ', i); -- CONCAT 是内置函数
- END IF;
- END WHILE;
- RETURN res;
- END
- //
- DELIMITER ;
使用 SELECT INTO 定义一个函数
-- 函数中不能调用 SQL 语句, 但是 SELECT INTO 除外
-- 示例
- DELIMITER //
- CREATE FUNCTION my_f() RETURNS INT
- BEGIN
- DECLARE res INT DEFAULT 0;
- SELECT COUNT(*) FROM student INTO res;
- RETURN res;
- END
- //
- DELIMITER ;
4. 存储过程
什么是存储过程以及为什么需要存储过程
/*
1. 什么是存储过程?
存储过程是存储在数据库服务器中的一组 SQL 语句
我们可以通过在查询中调用一个特定的名称来执行这些 SQL 语句
2. 为什么需要存储过程?
存储过程可以简单理解为数据库中的程序
它可以在不借助外部程序的情况下, 让数据库自己解决一个复杂的问题, 比如批量处理 SQL 语句等
*/
存储过程的创建
/*
存储过程的特点:
1. 存储过程与函数很相似, 但比函数更加灵活
2. 它没有返回值, 只注重过程
3. 它比函数更加灵活, 可以在里面使用 SQL 语句
*/
-- 通用结构:
- DELIMITER //
- CREATE PROCEDURE procedure_name(params)
- BEGIN
- BODY
- END
- //
- DELIMITER ;
参数定义
/*
存储过程比函数略复杂的地方, 体现在存储过程中接受的参数定义类型要比函数多
函数中, 函数的参数定义是(p1, p1_type...)
而存储过程中, 参数分为三种类型:
1. IN : 表示传入类型的参数, 如果不写默认就是 IN
2. OUT : 表示传出类型的参数
3. INOUT : 既能传入又能传出一般不用, 会造成语义不明确
这里其实也很好理解, 因为存储过程没有 RETURN 语句,
OUT 类型的参数其实就相当于 RETURN 的作用
*/
临时变量定义 (BEGINEND 之间的变量)
-- 和函数中临时变量的定义相同
示例
-- 实现传入一个学生的 id, 删除对应 id 的学生, 然后返回删除后学生表的学生总人数
/*
实现分析:
1. 首先考虑用函数实现, 但是删除学生, 需要用到 delete 语句, 所以函数不行
2. 考虑用存储过程实现, 但是存储过程没有返回值
3. 可以考虑用到 out 类型的参数来实现返回数值的作用
综上, 这个需求可以使用存储过程来实现
*/
-- 实现代码
- DELIMITER //
- CREATE PROCEDURE proc_delStuByIDAndGetCount(sid INT, OUT scount INT)
- BEGIN
- DELETE FROM student WHERE id = sid;
- SELECT COUNT(*) FROM student INTO scount;
- END
- //
- DELIMITER ;
存储过程的调用
-- 无参存储过程的调用
CALL procedure_name;
-- 如果有参数, 特别是有 out 类型的参数时
-- 使用用户变量进行接受即可
-- 比如调用上述示例中的存储过程: proc_delStuByIDAndGetCount
SET @scount = 0; -- 定义一个用户变量, 也可以不用定义直接传参
- CALL proc_delStuByIDAndGetCount(9, @scount);
- SELECT @scount;
5. 视图
什么是视图
/*
简单来说, 视图就是对 SELECT 语句的封装
对于复杂的查询, 如果在多处使用, 要想更改, 就很麻烦
这时候视图就能解决这一问题
视图可以视为存储在数据库中一个张虚拟的表
*/
视图的创建
CREATE VIEW view_name AS SELECT...
-- 创建视图, 查询学生所在班级信息
- CREATE VIEW v_stu_cls AS SELECT s.id AS '学号', s.name AS '姓名', c.name AS '班级名称'
- FROM student AS s
- JOIN class AS c ON s.class_id = c.id;
视图的查看
-- 查看创建的视图
SHOW TABLES; -- 由此也可以看出, 视图就是一张表
-- 调用视图
SELECT * FROM v_stu_cls;
SELECT 学号 FROM v_stu_cls;
SELECT 学号 FROM v_stu_cls WHERE 学号 = 10;
视图的删除
DROP VIEW view_name;
函数存储过程视图的比较
函数是对一个方法的封装, 在 MySQL 中可分为自定义函数与内置函数
函数中的参数只是一种类型, 就是输入类型, 函数必须有返回值
存储过程是数据库用于处理复杂 SQL 的一段程序, 它可以实现一般 SQL 实现不了的程序
存储过程可以简单认为是 SQL 与程序代码的结合体
存储过程中的参数类型有三种, OUT 类型可类比于函数中的返回值, 我们一般用一个用户变量来接受它
视图是对一个复杂 SELECT 语句的封装
视图可以简单的认为是一张表, 一张由 SELECT 查询结果而组成的一张数据表
6. 事务
为什么要有事务
简单的转账示例, 转账操作至少涉及两方, A 方扣钱, B 方得钱如果扣钱方成功, 而得钱方失败这样会导致重复扣钱的问题
事务就是为了解决这一问题而出现的, 事务将整个类似 "转账" 的操作看成是一个操作集合, 对整体集合进行操作规定
事务的特点 (ACID)
原子性 (Atomicity) : 事务中的全部操作, 要么全部完成 , 要么全部不做
一致性 (Consistency) : 几个并行执行的事务, 其执行结果必须与按某一顺序串行执行结果一致
隔离性 (Isolation) : 一个事务不受另一事务的影响, 拿转账来说, 假设用户 A 和用户 B 两者的钱加起来一共是 5000, 那么不管 A 和 B 之间如何转账, 转几次账, 事务结束后两个用户的钱相加起来应该还得是 5000, 这就是事务的一致性
持久性 (Durability) : 对于已提交事务, 系统必须保证该事务对数据库的改变不被丢失, 即使数据库出现故障
名词解释
Commit : 事务提交, 表示整个事务下的操作集全部有效
Rollback : 事务回滚, 表示整个事务下的操作集全部作废, 数据将还原到操作前的状态
事务不隔离, 在并发访问时带来的问题
脏读: 一个事务读到了另一个事务未提交的数据比如事务一将 A 账户的钱由 100 改为 500, 而此时一个并发的事务二读取 A 账户的钱, 这时事务二读到的数据是 500 而事务一这时又将事务回滚, 导致 A 账户钱变加 100 此时若事务二再次读取, 会发现数据是 100 这样, 之前读取到的 500 就是脏数据
不可重复读: 一个事务读取到另一个事务已经提交的数据, 并且这个数据是在 UPDATE 的操作下被修改的比如上述例子, 事务二先读取 A 账户的钱, 发现是 100, 接着事务一将钱数进行 UPDATE 成 500 并提交整个事务此时事务二再读取时, 会发现得到的结果是 500 这就出现了一个事务多次查询同一个属性却得到了不同的结果值
虚读 / 幻读: 一个事务读取到另一个事务已经提交的数据, 并且这个数据是在 INSERT 的操作下被修改的比如, 事务一先读取 A 账户的钱, 得到 100 , 同时将 A 账户的名称改为 B 此时事务二新增一个账户 A , 并将 A 账户的钱设置为 100 这时当事务一再次查询时, 会发现 A 账户还没有修改, 产生了 '幻觉'
三者的区别: 总体来说, 可以分为两大类问题: 读未提交和读已提交脏读是读未提交事务数据; 后两者则是读已提交数据而后两者的区别在于已提交数据是如何操作的, 不可重复读是执行 UPDATE 操作, 而后者是执行 INSERT 操作
事务的隔离级别
读未提交 (read uncommitted) : 一个事务读到另一个事务没有提交的数据上述三个问题都存在
读已提交 (read committed) : 一个事务在写时禁止其他事务读写, 表示必须提交后的数据才能被读取
未解决: 不可重复读虚读 / 幻读
解决: 脏读
可重复读 (repeatable read): 一个事务在写时禁止其他事务读写, 一个事务在读时, 禁止其他事务写
未解决: 虚读 / 幻读
解决: 脏读, 不可重复读
串行化 (serializable) : 每次只能执行一个事务, 上述问题全部解决但是这种级别效率低, 一般不用
常见的数据库事务隔离:
数据库 | 默认级别 |
---|---|
MySQL | 可重复读(Repeatable Read) |
Oracle | 读提交(Read Committed) |
SQLServer | 读提交(Read Committed) |
DB2 | 读提交(Read Committed) |
PostgreSQL | 读提交(Read Committed) |
事务命令
-- 在 MySQL 中, 表的引擎类型必须是 innodb 类型才能使用事务, innodb 是 MySQL 默认引擎
-- 修改数据的命令会触发事务, 包括 INSERTUPDATEDELETE
-- 开启事务: 开启事务后, 变更会维护到本地缓存中, 而不维护到物理表中
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 在 MySQL 中我们执行 INSERTUPDATEDELETE 语句后, 默认是自动提交事务的
-- 这个自动提交的设置是系统变量 AUTOCOMMIT 来控制的, 默认值是 1
-- 如果想要关闭可修改它的值
SET [GLOBAL] AUTOCOMMIT = 0;
-- 修改后, 如果想要当前窗口 (表示当前事务) 做出的修改, 其他窗口 (表示其他事务) 能看到, 必须手动提交
7. 索引
问题引入:
-- 创建一个表 demo
- CREATE TABLE demo(
- id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
- name VARCHAR(50)
- );
-- 使用存储过程来创建大量数据
- DELIMITER //
- CREATE PROCEDURE proc_create()
- BEGIN
- DECLARE i INT default 0;
- WHILE i <= 100000 DO
- INSERT INTO demo(name) VALUES (CONCAT('demo', i));
- SET i = i + 1;
- END WHILE;
- END
- //
- DELIMITER ;
-- 调用存储过程执行插入操作
CALL proc_create();
-- 开启 SQL 运行时间监测
SET PROFILING = 1;
-- 查询 name 值为 demo10000 的数据
SELECT * FROM demo WHERE name = 'demo10000';
-- 查询执行时间
SHOW PROFILES; -- 0.02965800
-- 查询 id 值 为 10000 的数据
SELECT * FROM demo WHERE id = '10000';
-- 查询执行时间
SHOW PROFILES; -- 0.00109200
-- 执行上面语句, 会发现, 通过 id 查询比通过 name 查询时间要少很多
-- 这是因为 id 是主键, 有索引关联
-- 因此, 如果当某个字段, 经常用作查询的字段, 而数据量又很大时, 通常我们需要使用索引
索引的创建
-- 1. 如果指定字段是字符串, 需要指定长度, 建议长度与定义字段时的长度一致
-- 2. 如果不是字符串, 可以不填写长度部分
-- 3. 语法格式如下:
-- create index 索引名称 on 表名(字符名称(长度))
-- 4. 索引的缺点:
-- 4.1 虽然它大大提高了查询速度, 同时却会降低更新表的速度, 如对表进行 INSERTUPDATE 和 DELETE, 因为更新表时, MySQL 不仅需要保存数据, 还要保存索引文件
-- 4.2 建立索引会占用磁盘空间的索引文件
CREATE INDEX name_index ON demo(name(50));
-- 查询 name 值为 demo10000 的数据
SELECT * FROM demo WHERE name = 'demo10000';
-- 查询执行时间
SHOW PROFILES; -- 0.00082700
查看索引
SHOW INDEX FROM table_name;
删除索引
DROP INDEX index_name ON table_name;
8. 数据库的备份与恢复
备份
mysqldump -uroot -p database_name > url/xxx.sql;
恢复
mysql -uroot -p new_database_name < url/xxx.sql;
9. 执行外部的 sql 文件
-- 登录 MySQL 环境
- mysql -u root -p
- -- source url/xxx.sql
10. Python 与 MySQL 交互
Python 中操作 MySQL 步骤图解
安装 MySQL 模块
- # 以 mac + python3 环境为例
- pip install pymysql
代码示例
- #!/usr/bin/env python
- # -*- coding:utf-8 -*-
- # __author : Demon
- # date : 2018-02-27 14:26
- from pymysql import *
- def insert():
- # 1. get the connection object
- # host, user, password, database, port, charset
- conn = NULL
- cur = NULL
- try:
- conn = connect(host="localhost",
- user="root",
- # use your own password
- password="123456",
- # use your own database name
- database="test",
- port=3306,
- # notice that it's'utf8'not'utf-8'# if use'utf-8'will raise error'NoneType has no attribute encoding'charset="utf8"
- )
- # 2. get the db operation object
- cur = conn.cursor()
- # 3. write SQL and execute the SQL
- # insert_sql = "INSERT INTO demo(name) VALUES ('demo10000000')"
- # cur.execute(insert_sql)
- # update_sql = "UPDATE demo SET name ='demo'WHERE id = 1"
- # cur.execute(update_sql)
- delete_sql = "DELETE FROM demo WHERE id = 1"
- cur.execute(delete_sql)
- # fetchone
- # res = cur.fetchone()
- # print(res)
- # 4. if data has been changed in Python, we need commit by ourself
- conn.commit()
- except Exception as e:
- print(e)
- finally:
- if cur:
- cur.close()
- if conn:
- conn.close()
- def select():
- # 1. get the connection object
- # host, user, password, database, port, charset
- conn = NULL
- cur = NULL
- try:
- conn = connect(host="localhost",
- user="root",
- # use your own password
- password="123456",
- # use your own database name
- database="test",
- port=3306,
- # notice that it's'utf8'not'utf-8'# if use'utf-8'will raise error'NoneType has no attribute encoding'charset="utf8"
- )
- # 2. get the db operation object
- cur = conn.cursor()
- # 3. write SQL and execute the SQL
- params = [2]
- select_sql = "SELECT * FROM demo WHERE id = %s"
- cur.execute(select_sql, params)
- # 4. get the result by cur.fetchone() and cur.fetchall()
- res = cur.fetchone() # the result type is tuple
- print(res)
- # fetchall() return nested tuple ((), (), ())
- # SELECT need not commit
- # conn.commit()
- except Exception as e:
- print(e)
- finally:
- if cur:
- cur.close()
- if conn:
- conn.close()
- if __name__ == "__main__":
- # insert()
- select()
11. 数据库中常见的安全问题
SQL 的注入
-- 一个简单的 SQL 插入语句
INSERT INTO student(name) VALUES ('Demon';DROP DATABASE test;');
-- 这里的用户名实际上是 Demon';DROP DATABASE test;
-- 但是我们都知道, SQL 的结束符是 ;
-- 当遇到第一个 ; 号时, 第一个语句执行结束
-- 这样就会执行到 DROP 语句, 那整个数据库就会被删除掉
-- 实际这里出现问题的是因为在 name 值中, 多了一个引号, 只要我们加上转义字符, 即可达到本身的目的
INSERT INTO student(name) VALUES ('Demon\';DROP DATABASE test;');
撞库
根据在某个站点获得的用户名和密码, 去登录其他站点, 从而得到更多信息
因为通常我们的习惯是在不同网站注册, 大多会使用相同的密码
如果某个网站设计得不好, 被人窃取密码, 再用相同的密码去登录其他网站, 就会很危险
安全建议
对于 SQL 注入而言, 容易发生在表单提交中, 因此永远不要相信用户的输入, 要对用户的输入先进行正则匹配, 如果有特殊字符, 要进行转义
对于撞库, 最好不同网站使用不同的密码, 特别是对重要的网站同时要定期更换自己的密码
来源: https://www.cnblogs.com/yrrAwx/p/8523361.html