Day15：关于MySQL的编程技术——基础知识

前言：先创建一个练习的数据库和数据

1.创建数据库并创建数据表的基本结构

-- 创建练习数据库
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()
BEGIN
    DECLARE 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()
BEGIN
    START 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)
BEGIN
    IF 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()
BEGIN
    DECLARE total INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    WHILE i <= 5 DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
    SELECT total;
END$$
DELIMITER ;

2.PEPEAT语句

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

3.LOOP语句

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

额。。。小累，明天再优化。