-- 用户变量只对当前连接用户有效，其他连接用户无法访问
-- 使用 @ 标识符声明用户变量
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;
-- 不能自己定义一个新的系统变量
-- 不区分大小写

-- 需要有 服务实例级 的权限登录后进行操作
-- 账户都存储在 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';

-- 需要使用实例级别账户登录后操作
-- 常用权限主要包括：CREATE、ALTER、INSERT、UPDATE、DELETE、SELECT
-- 如果需要分配所有权限，使用 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;

-- 使用这个命令使权限生效
-- 尤其是你对那些权限表user、db、host等做了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;

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 ;

-- 函数中不能调用 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 ;

/*
 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 的作用
*/

-- 和函数中临时变量的定义相同

-- 实现传入一个学生的 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;

/*
简单来说，视图就是对 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 中，表的引擎类型必须是 innodb 类型才能使用事务， innodb 是 MySQL 默认引擎
-- 修改数据的命令会触发事务，包括 INSERT、UPDATE、DELETE

-- 开启事务：开启事务后，变更会维护到本地缓存中，而不维护到物理表中
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 在 MySQL 中我们执行 INSERT、UPDATE、DELETE 语句后，默认是自动提交事务的
-- 这个自动提交的设置是系统变量 AUTOCOMMIT 来控制的，默认值是 1
-- 如果想要关闭可修改它的值
SET [GLOBAL] AUTOCOMMIT = 0;
-- 修改后，如果想要当前窗口(表示当前事务)做出的修改，其他窗口(表示其他事务)能看到，必须手动提交

-- 创建一个表 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 虽然它大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和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;

mysqldump -uroot -p database_name > url/xxx.sql;

mysql -uroot -p new_database_name < url/xxx.sql;

-- 登录 MySQL 环境
mysql -u root -p

-- source url/xxx.sql

# 以 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()

-- 一个简单的 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;');