-- 創建練習數據庫
CREATE DATABASE db_programming;
USE db_programming;-- 創建員工表（包含各種數據類型）
CREATE TABLE employees (emp_id INT PRIMARY KEY AUTO_INCREMENT,emp_name VARCHAR(20) NOT NULL,salary DECIMAL(10,2) DEFAULT 0.00,hire_date DATE
);

??2.導入實例數據?

-- 插入測試數據
INSERT INTO employees (emp_name, salary, hire_date) VALUES
('張三', 8000.00, '2020-01-15'),
('李四', 7500.00, '2021-03-20'),
('王五', NULL, '2022-05-10');

一、常量和變量

1.常量

SELECT 100 AS int_const, PI() AS math_const, 'Hello' AS str_const;

2.變量

1.用戶變量

SET @user_var = 100;
SELECT @user_var := salary FROM employees WHERE emp_id = 1;

2.系統變量

SELECT @@version AS mysql_version, @@character_set_server AS charset;

3.局部變量

DELIMITER $$
CREATE PROCEDURE var_demo()
BEGINDECLARE local_var INT DEFAULT 10;SET local_var = local_var * 2;SELECT local_var;
END$$
DELIMITER ;
CALL var_demo();

二、系統內置函數

1.數學函數

SELECT ABS(-10), ROUND(3.1415,2), CEIL(2.3), RAND();

2.字符串函數

SELECT CONCAT(emp_name, '-', emp_id) AS name_id,SUBSTRING(emp_name, 1, 1) AS first_char,REVERSE(emp_name) AS reversed_name
FROM employees;

3.日期和時間函數

SELECT NOW() AS current_time,DATEDIFF(NOW(), hire_date) AS work_days
FROM employees;

4.其他函數

SELECT IFNULL(salary, 0) AS actual_salary,VERSION(),DATABASE()
FROM employees;

三、流程控制語句

1.順序語句

1.BEGIN | END語句塊

DELIMITER $$
CREATE PROCEDURE salary_adjust()
BEGINSTART TRANSACTION;UPDATE employees SET salary = salary * 1.1;COMMIT;
END$$
DELIMITER ;

2.DELIMITER命令

2.分支語句

1.IF語句

DELIMITER $$
CREATE FUNCTION get_grade(salary DECIMAL) RETURNS VARCHAR(10)
BEGINIF salary > 8000 THEN RETURN '高薪';ELSEIF salary > 5000 THEN RETURN '中薪';ELSE RETURN '基礎';END IF;
END$$
DELIMITER ;

2.CASE語句

SELECT emp_name,CASE WHEN salary IS NULL THEN '未定薪'WHEN salary > 8000 THEN '高薪'ELSE '常規薪資'END AS salary_level
FROM employees;

3.循環語句

1.WHLER語句

DELIMITER $$
CREATE PROCEDURE while_demo()
BEGINDECLARE total INT DEFAULT 0;DECLARE i INT DEFAULT 1;WHILE i <= 5 DOSET total = total + i;SET i = i + 1;END WHILE;SELECT total;
END$$
DELIMITER ;

2.PEPEAT語句

DELIMITER $$
CREATE PROCEDURE repeat_demo()
BEGINDECLARE total INT DEFAULT 0;DECLARE i INT DEFAULT 1;REPEATSET total = total + i;SET i = i + 1;UNTIL i > 5 END REPEAT;SELECT total;
END$$
DELIMITER ;

3.LOOP語句

DELIMITER $$
CREATE PROCEDURE loop_demo()
BEGINDECLARE fact INT DEFAULT 1;DECLARE n INT DEFAULT 5;factorial: LOOPSET fact = fact * n;SET n = n - 1;IF n = 0 THENLEAVE factorial;END IF;END LOOP;SELECT fact;
END$$
DELIMITER ;

額。。。小累，明天再優化。