SHOW  VARIABLES LIKE '%long_query_time%';

SHOW DATABASES;
CREATE DATABASE bigData;
USE bigData;

CREATE TABLE dept(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    dname VARCHAR(20) NOT NULL DEFAULT "",
    loc VARCHAR(13) NOT NULL DEFAULT ""
)ENGINE = INNODB DEFAULT CHARSET = GBK;


CREATE TABLE emp(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    ename VARCHAR(20) NOT NULL DEFAULT "",
    job VARCHAR(9) NOT NULL DEFAULT "",
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    hiredate DATE NOT NULL,
    sal DECIMAL(7,2) NOT NULL,
    comm DECIMAL(7,2) NOT NULL,
    deptno MEDIUMINT UNSIGNED NOT NULL NULL DEFAULT 0
)ENGINE = INNODB DEFAULT CHARSET = GBK;

SHOW TABLES;

SHOW VARIABLES LIKE '%slow_query_log%';
#创建函数，假如报错，this function has none of deterministic 。。。
#由于开启过慢查询日志，因为我们开启了bin——log，我们就必须为我们的function指定一个参数
SHOW VARIABLES LIKE '%long_query_time%';
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = 1;
#永久方法 windows 下 my。ini【mysqlid】加上 log_bin_trust_function_creators = 1
#linux 下 /etc/my。conf下my。conf【mysqlid】加上log_bin_trust_function_creators = 1
SELECT * FROM emp;


DELIMITER $
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmoopqrstuvwxyz';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i< n DO
 SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 SET i = i+1;
 END WHILE;
 RETURN return_str;
END $$

SELECT NOW() FROM DUAL;
    


DELIMITER $
CREATE FUNCTION rand_num( ) RETURNS INT(5)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i = FLOOR(100+RAND()*10);
 RETURN i;
END $$

DELIMITER $
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit =0;
    REPEAT
    SET i = i+1;
    INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 
    VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

DESC dept;
DELIMITER $
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN 
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i+1;
    INSERT INTO dept(deptno,dname,loc) VALUES((START+i),rand_string(10),rand_string(8));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$


CALL insert_emp();
SELECT * FROM dept;
CALL insert_dept(100,10);

#show profile 是mysql提供用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优和测量

#默认情况下，参数处于关闭状态，并保存最近15次的运行结果

SHOW VARIABLES LIKE 'profiling';
SET profiling = ON;
SHOW DATABASES;
USE mysqlgj;

SELECT * FROM mysqlgj.tbl_emp e RIGHT JOIN mysqlgj.tbl_dept d ON e.deptid = d.id;

USE bigData;
DESC emp;

SELECT id FROM emp ;
SHOW PROFILES;

SHOW PROFILE CPU,BLOCK IO FOR QUERY 47;
SELECT * FROM tbl_emp WHERE id = 4;

USE bigData;

DESC emp;