SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
SHOW VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'admin_%' ;
SHOW VARIABLES LIKE 'character_%' ;
SELECT @@global.max_connections ;
SELECT @@global.character_set_client ;
SELECT @@global.pseudo_thread_id ;
SELECT @@session.max_connections ;
SELECT @@session.character_set_client ;
SELECT @@session.pseudo_thread_id ;
SELECT @@character_set_client ;
SET @@global.max_connections = 161 ;
SET GLOBAL max_connections = 171 ;
SET @@session.character_set_client = 'gbk' ;
SET SESSION character_set_client = 'gbk' ;
CREATE DATABASE dbtest16;
USE dbtest16;
CREATE TABLE employees
AS
SELECT * FROM atguigudb. ` employees` ;
CREATE TABLE departments
AS
SELECT * FROM atguigudb. ` departments` ;
SELECT * FROM employees;
SELECT * FROM departments;
SET @m1 = 1 ;
SET @m2 := 2 ;
SET @sum := @m1 + @m2 ;
SELECT @sum ;
SELECT @count := COUNT ( * ) FROM employees;
SELECT @count ;
SELECT AVG ( salary) INTO @avg_sal FROM employees;
SELECT @avg_sal ;
DELIMITER
CREATE PROCEDURE test_var( )
BEGIN
DECLARE a INT DEFAULT 0 ;
DECLARE b INT ;
DECLARE emp_name VARCHAR ( 25 ) ;
SET a = 1 ;
SET b := 2 ;
SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101 ;
SELECT a, b, emp_name;
END
DELIMITER ;
CALL test_var( ) ;
DELIMITER
CREATE PROCEDURE test_pro( )
BEGIN
DECLARE emp_name VARCHAR ( 25 ) ;
DECLARE sal DOUBLE ( 10 , 2 ) DEFAULT 0.0 ;
SELECT last_name, salary INTO emp_name, sal
FROM employees
WHERE employee_id = 102 ;
SELECT emp_name, sal;
END
DELIMITER ;
CALL test_pro( ) ;
SELECT last_name, salary FROM employees
WHERE employee_id = 102 ;
SET @v1 = 10 ;
SET @v2 := 20 ;
SET @result := @v1 + @v2 ;
SELECT @result ;
DELIMITER
CREATE PROCEDURE add_value( )
BEGIN
DECLARE value1, value2, sum_val INT ;
SET value1 = 10 ;
SET value2 := 100 ;
SET sum_val = value1 + value2;
SELECT sum_val;
END
DELIMITER ;
CALL add_value( ) ;
DELIMITER
CREATE PROCEDURE different_salary( IN emp_id INT , OUT dif_salary DOUBLE )
BEGIN
DECLARE emp_sal DOUBLE DEFAULT 0.0 ;
DECLARE mgr_sal DOUBLE DEFAULT 0.0 ;
DECLARE mgr_id INT DEFAULT 0 ;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id;
SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id;
SET dif_salary = mgr_sal - emp_sal;
END
DELIMITER ;
SET @emp_id := 103 ;
SET @dif_sal := 0 ;
CALL different_salary( @emp_id , @dif_sal ) ;
SELECT @dif_sal ;
SELECT * FROM employees;
INSERT INTO employees( last_name)
VALUES ( 'Tom' ) ;
DESC employees;
DELIMITER
CREATE PROCEDURE UpdateDataNoCondition( )
BEGIN
SET @x = 1 ;
UPDATE employees SET email = NULL WHERE last_name = 'Abel' ;
SET @x = 2 ;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel' ;
SET @x = 3 ;
END
DELIMITER ;
CALL UpdateDataNoCondition( ) ;
SELECT @x ;
DECLARE Field_Not_Be_NULL CONDITION FOR 1048 ;
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000' ;
DECLARE command_not_allowed CONDITION FOR 1148 ;
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000' ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE' ;
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE' ;
DECLARE no_such_table CONDITION FOR 1146 ;
DECLARE CONTINUE HANDLER FOR no_such_table SET @info = 'NO_SUCH_TABLE' ;
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR' ;
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE' ;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR' ;
DROP PROCEDURE UpdateDataNoCondition;
DELIMITER
CREATE PROCEDURE UpdateDataNoCondition( )
BEGIN
DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = - 1 ;
SET @x = 1 ;
UPDATE employees SET email = NULL WHERE last_name = 'Abel' ;
SET @x = 2 ;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel' ;
SET @x = 3 ;
END
DELIMITER ;
CALL UpdateDataNoCondition( ) ;
SELECT @x , @prc_value ;
CREATE TABLE departments
AS
SELECT * FROM atguigudb. ` departments` ;
DESC departments;
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE ( department_id) ;
DELIMITER
CREATE PROCEDURE InsertDataWithCondition( )
BEGIN
SET @x = 1 ;
INSERT INTO departments( department_name) VALUES ( '测试' ) ;
SET @x = 2 ;
INSERT INTO departments( department_name) VALUES ( '测试' ) ;
SET @x = 3 ;
END
DELIMITER ;
CALL InsertDataWithCondition( ) ;
SELECT @x ;
DROP PROCEDURE IF EXISTS InsertDataWithCondition;
DELIMITER
CREATE PROCEDURE InsertDataWithCondition( )
BEGIN
DECLARE duplicate_entry CONDITION FOR 1062 ;
DECLARE EXIT HANDLER FOR duplicate_entry SET @pro_value = - 1 ;
SET @x = 1 ;
INSERT INTO departments( department_name) VALUES ( '测试' ) ;
SET @x = 2 ;
INSERT INTO departments( department_name) VALUES ( '测试' ) ;
SET @x = 3 ;
END
DELIMITER ;
CALL InsertDataWithCondition( ) ;
SELECT @x , @pro_value ;
DELIMITER
CREATE PROCEDURE test_if( )
BEGIN
DECLARE age INT DEFAULT 20 ;
IF age > 40
THEN SELECT '中老年' ;
ELSEIF age > 18
THEN SELECT '青壮年' ;
ELSEIF age > 8
THEN SELECT '青少年' ;
ELSE
SELECT '婴幼儿' ;
END IF ;
END
DELIMITER ;
CALL test_if( ) ;
DROP PROCEDURE test_if;
DELIMITER
CREATE PROCEDURE update_salary_by_eid1( IN emp_id INT )
BEGIN
DECLARE emp_sal DOUBLE ;
DECLARE hire_year DOUBLE ;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT DATEDIFF( CURDATE( ) , hire_date) / 365 INTO hire_year FROM employees WHERE employee_id = emp_id;
IF emp_sal < 8000 AND hire_year >= 5
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
END IF ;
END
DELIMITER ;
CALL update_salary_by_eid1( 104 ) ;
SELECT DATEDIFF( CURDATE( ) , hire_date) / 365 , employee_id, salary
FROM employees
WHERE salary < 8000 AND DATEDIFF( CURDATE( ) , hire_date) / 365 >= 5 ;
DROP PROCEDURE update_salary_by_eid1;
DELIMITER
CREATE PROCEDURE update_salary_by_eid2( IN emp_id INT )
BEGIN
DECLARE emp_sal DOUBLE ;
DECLARE hire_year DOUBLE ;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT DATEDIFF( CURDATE( ) , hire_date) / 365 INTO hire_year FROM employees WHERE employee_id = emp_id;
IF emp_sal < 9000 AND hire_year >= 5
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END IF ;
END
DELIMITER ;
CALL update_salary_by_eid2( 103 ) ;
CALL update_salary_by_eid2( 104 ) ;
SELECT * FROM employees
WHERE employee_id IN ( 103 , 104 ) ;
DELIMITER
CREATE PROCEDURE update_salary_by_eid3( IN emp_id INT )
BEGIN
DECLARE emp_sal DOUBLE ;
DECLARE bonus DOUBLE ;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
IF emp_sal < 9000
THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
ELSEIF emp_sal < 10000 AND bonus IS NULL
THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END IF ;
END
DELIMITER ;
CALL update_salary_by_eid3( 102 ) ;
CALL update_salary_by_eid3( 103 ) ;
CALL update_salary_by_eid3( 104 ) ;
SELECT *
FROM employees
WHERE employee_id IN ( 102 , 103 , 104 ) ;
DELIMITER
CREATE PROCEDURE test_case( )
BEGIN
DECLARE var1 INT DEFAULT 10 ;
CASE
WHEN var1 >= 100 THEN SELECT '三位数' ;
WHEN var1 >= 10 THEN SELECT '两位数' ;
ELSE SELECT '个数位' ;
END CASE ;
END
DELIMITER ;
CALL test_case( ) ;
DROP PROCEDURE test_case;
DELIMITER
CREATE PROCEDURE update_salary_by_eid4( IN emp_id INT )
BEGIN
DECLARE emp_sal DOUBLE ;
DECLARE bonus DOUBLE ;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
CASE
WHEN emp_sal < 9000 THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
WHEN emp_sal < 10000 AND bonus IS NULL THEN UPDATE employees SET commission_pct = 0.01
WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END CASE ;
END
DELIMITER ;
CALL update_salary_by_eid4( 103 ) ;
CALL update_salary_by_eid4( 104 ) ;
CALL update_salary_by_eid4( 105 ) ;
SELECT *
FROM employees
WHERE employee_id IN ( 103 , 104 , 105 ) ;
DELIMITER
CREATE PROCEDURE update_salary_by_eid5( IN emp_id INT )
BEGIN
DECLARE hire_year INT ;
SELECT ROUND ( DATEDIFF( CURDATE( ) , hire_date) / 365 ) INTO hire_year
FROM employees WHERE employee_id = emp_id;
CASE hire_year
WHEN 0 THEN UPDATE employees SET salary = salary + 50 WHERE employee_id = emp_id;
WHEN 1 THEN UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
WHEN 2 THEN UPDATE employees SET salary = salary + 200 WHERE employee_id = emp_id;
WHEN 3 THEN UPDATE employees SET salary = salary + 300 WHERE employee_id = emp_id;
WHEN 4 THEN UPDATE employees SET salary = salary + 400 WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
END CASE ;
END
DELIMITER ;
CALL update_salary_by_eid5( 101 ) ;
SELECT *
FROM employees
DROP PROCEDURE update_salary_by_eid5;
DELIMITER
CREATE PROCEDURE test_loop( )
BEGIN
DECLARE num INT DEFAULT 1 ;
loop_label:LOOP
SET num = num + 1 ;
IF num >= 10 THEN LEAVE loop_label;
END IF ;
END LOOP loop_label;
SELECT num;
END
DELIMITER ;
CALL test_loop( ) ;
DELIMITER
CREATE PROCEDURE update_salary_loop( OUT num INT )
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE loop_count INT DEFAULT 0 ;
SELECT AVG ( salary) INTO avg_sal FROM employees;
loop_lab:LOOP
IF avg_sal >= 12000
THEN LEAVE loop_lab;
END IF ;
UPDATE employees SET salary = salary * 1.1 ;
SELECT AVG ( salary) INTO avg_sal FROM employees;
SET loop_count = loop_count + 1 ;
END LOOP loop_lab;
SET num = loop_count;
END
DELIMITER ;
SELECT AVG ( salary) FROM employees;
CALL update_salary_loop( @num ) ;
SELECT @num ;
DELIMITER
CREATE PROCEDURE test_while( )
BEGIN
DECLARE num INT DEFAULT 1 ;
WHILE num <= 10 DO
SET num = num + 1 ;
END WHILE ;
SELECT num;
END
DELIMITER ;
CALL test_while( ) ;
DELIMITER
CREATE PROCEDURE update_salary_while( OUT num INT )
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE while_count INT DEFAULT 0 ;
SELECT AVG ( salary) INTO avg_sal FROM employees;
WHILE avg_sal > 5000 DO
UPDATE employees SET salary = salary * 0.9 ;
SET while_count = while_count + 1 ;
SELECT AVG ( salary) INTO avg_sal FROM employees;
END WHILE ;
SET num = while_count;
END
DELIMITER ;
CALL update_salary_while( @num ) ;
SELECT @num ;
SELECT AVG ( salary) FROM employees;
DELIMITER
CREATE PROCEDURE test_repeat( )
BEGIN
DECLARE num INT DEFAULT 1 ;
REPEAT
SET num = num + 1 ;
UNTIL num >= 10
END REPEAT ;
SELECT num;
END
DELIMITER ;
CALL test_repeat( ) ;
DELIMITER
CREATE PROCEDURE update_salary_repeat( OUT num INT )
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE repeat_count INT DEFAULT 0 ;
SELECT AVG ( salary) INTO avg_sal FROM employees;
REPEAT
UPDATE employees SET salary = salary * 1.15 ;
SET repeat_count = repeat_count + 1 ;
SELECT AVG ( salary) INTO avg_sal FROM employees;
UNTIL avg_sal >= 13000
END REPEAT ;
SET num = repeat_count;
END
DELIMITER ;
CALL update_salary_repeat( @num ) ;
SELECT @num ;
SELECT AVG ( salary) FROM employees;
DELIMITER
CREATE PROCEDURE leave_begin( IN num INT )
begin_label:BEGIN
IF num <= 0
THEN LEAVE begin_label;
ELSEIF num = 1
THEN SELECT AVG ( salary) FROM employees;
ELSEIF num = 2
THEN SELECT MIN ( salary) FROM employees;
ELSE
SELECT MAX ( salary) FROM employees;
END IF ;
SELECT COUNT ( * ) FROM employees;
END
DELIMITER ;
CALL leave_begin( 1 ) ;
DELIMITER
CREATE PROCEDURE leave_while( OUT num INT )
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE while_count INT DEFAULT 0 ;
SELECT AVG ( salary) INTO avg_sal FROM employees;
while_label:WHILE TRUE DO
IF avg_sal <= 10000 THEN
LEAVE while_label;
END IF ;
UPDATE employees SET salary = salary * 0.9 ;
SET while_count = while_count + 1 ;
SELECT AVG ( salary) INTO avg_sal FROM employees;
END WHILE ;
SET num = while_count;
END
DELIMITER ;
CALL leave_while( @num ) ;
SELECT @num ;
SELECT AVG ( salary) FROM employees;
DELIMITER
CREATE PROCEDURE test_iterate( )
BEGIN
DECLARE num INT DEFAULT 0 ;
loop_label:LOOP
SET num = num + 1 ;
IF num < 10
THEN ITERATE loop_label;
ELSEIF num > 15
THEN LEAVE loop_label;
END IF ;
SELECT '尚硅谷:让天下没有难学的技术' ;
END LOOP ;
END
DELIMITER ;
CALL test_iterate( ) ;
SELECT * FROM employees;
DELIMITER
CREATE PROCEDURE get_count_by_limit_total_salary( IN limit_total_salary DOUBLE , OUT total_count INT )
BEGIN
DECLARE sum_sal DOUBLE DEFAULT 0.0 ;
DECLARE emp_sal DOUBLE ;
DECLARE emp_count INT DEFAULT 0 ;
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC ;
OPEN emp_cursor;
REPEAT
FETCH emp_cursor INTO emp_sal;
SET sum_sal = sum_sal + emp_sal;
SET emp_count = emp_count + 1 ;
UNTIL sum_sal >= limit_total_salary
END REPEAT ;
SET total_count = emp_count;
CLOSE emp_cursor;
END
DELIMITER ;
CALL get_count_by_limit_total_salary( 200000 , @total_count ) ;
SELECT @total_count ;
CREATE DATABASE test16_var_cursor;
USE test16_var_cursor;
CREATE TABLE employees
AS
SELECT * FROM atguigudb. ` employees` ;
CREATE TABLE departments
AS
SELECT * FROM atguigudb. ` departments` ;
SET GLOBAL log_bin_trust_function_creators = 1 ;
DELIMITER $
CREATE FUNCTION get_count( )
RETURNS INT
BEGIN
DECLARE emp_count INT ;
SELECT COUNT ( * ) INTO emp_count FROM employees;
RETURN emp_count;
END $
DELIMITER ;
SELECT get_count( ) ;
DELIMITER $
CREATE FUNCTION ename_salary( emp_name VARCHAR ( 15 ) )
RETURNS DOUBLE
BEGIN
SET @sal = 0 ;
SELECT salary INTO @sal FROM employees WHERE last_name = emp_name;
RETURN @sal ;
END $
DELIMITER ;
SELECT ename_salary( 'Abel' ) ;
SELECT @sal ;
DELIMITER
CREATE FUNCTION dept_sal( dept_name VARCHAR ( 15 ) )
RETURNS DOUBLE
BEGIN
DECLARE avg_sal DOUBLE ;
SELECT AVG ( salary) INTO avg_sal
FROM employees e JOIN departments d
ON e. department_id = d. department_id
WHERE d. department_name = dept_name;
RETURN avg_sal;
END
DELIMITER ;
SELECT * FROM departments;
SELECT dept_sal( 'Marketing' ) ;
DELIMITER
CREATE FUNCTION add_float( value1 FLOAT , value2 FLOAT )
RETURNS FLOAT
BEGIN
DECLARE sum_val FLOAT ;
SET sum_val = value1 + value2;
RETURN sum_val;
END
DELIMITER ;
SET @v1 := 12.2 ;
SET @v2 = 2.3 ;
SELECT add_float( @v1 , @v2 ) ;
DELIMITER $
CREATE FUNCTION test_if_case1( score DOUBLE )
RETURNS CHAR
BEGIN
DECLARE score_level CHAR ;
IF score > 90
THEN SET score_level = 'A' ;
ELSEIF score > 80
THEN SET score_level = 'B' ;
ELSEIF score > 60
THEN SET score_level = 'C' ;
ELSE
SET score_level = 'D' ;
END IF ;
RETURN score_level;
END $
DELIMITER ;
SELECT test_if_case1( 56 ) ;
DELIMITER $
CREATE FUNCTION test_if_case2( score DOUBLE )
RETURNS CHAR
BEGIN
DECLARE score_level CHAR ;
CASE
WHEN score > 90 THEN SET score_level = 'A' ;
WHEN score > 80 THEN SET score_level = 'B' ;
WHEN score > 60 THEN SET score_level = 'C' ;
ELSE SET score_level = 'D' ;
END CASE ;
RETURN score_level;
END $
DELIMITER ;
SELECT test_if_case2( 76 ) ;
DELIMITER $
CREATE PROCEDURE test_if_pro( IN sal DOUBLE )
BEGIN
IF sal < 3000
THEN DELETE FROM employees WHERE salary = sal;
ELSEIF sal <= 5000
THEN UPDATE employees SET salary = salary + 1000 WHERE salary = sal;
ELSE
UPDATE employees SET salary = salary + 500 WHERE salary = sal;
END IF ;
END $
DELIMITER ;
CALL test_if_pro( 24000 ) ;
SELECT * FROM employees;
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT ,
user_name VARCHAR ( 25 ) NOT NULL ,
user_pwd VARCHAR ( 35 ) NOT NULL
) ;
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE insert_data( IN insert_count INT )
BEGIN
DECLARE init_count INT DEFAULT 1 ;
WHILE init_count <= insert_count DO
INSERT INTO admin( user_name, user_pwd) VALUES ( CONCAT( 'atguigu-' , init_count) , ROUND ( RAND( ) * 1000000 ) ) ;
SET init_count = init_count + 1 ;
END WHILE ;
END $
DELIMITER ;
CALL insert_data( 100 ) ;
DELIMITER $
CREATE PROCEDURE update_salary( IN dept_id INT , IN change_sal_count INT )
BEGIN
DECLARE emp_id INT ;
DECLARE emp_hire_date DATE ;
DECLARE init_count INT DEFAULT 1 ;
DECLARE add_sal_rate DOUBLE ;
DECLARE emp_cursor CURSOR FOR SELECT employee_id, hire_date FROM employees
WHERE department_id = dept_id ORDER BY salary ASC ;
OPEN emp_cursor;
WHILE init_count <= change_sal_count DO
FETCH emp_cursor INTO emp_id, emp_hire_date;
IF ( YEAR ( emp_hire_date) < 1995 )
THEN SET add_sal_rate = 1.2 ;
ELSEIF ( YEAR ( emp_hire_date) <= 1998 )
THEN SET add_sal_rate = 1.15 ;
ELSEIF ( YEAR ( emp_hire_date) <= 2001 )
THEN SET add_sal_rate = 1.10 ;
ELSE
SET add_sal_rate = 1.05 ;
END IF ;
UPDATE employees SET salary = salary * add_sal_rate
WHERE employee_id = emp_id;
SET init_count = init_count + 1 ;
END WHILE ;
CLOSE emp_cursor;
END $
DELIMITER ;
CALL update_salary( 50 , 3 ) ;
SELECT employee_id, hire_date, salary
FROM employees
WHERE department_id = 50
ORDER BY salary ASC ;