One Last MySQL

mysql> use lll;
Database changed
mysql> source C:\Users\Administrator\Downloads\sy101.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 23 rows affected (0.00 sec)
Records: 23  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS record;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE record (
    ->     序号 INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->     用户名 VARCHAR(20),
    ->     操作类型 VARCHAR(4),
    ->     原内容 VARCHAR(20),
    ->     新内容 VARCHAR(20),
    ->     时间 DATETIME
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER $$
mysql> DROP TRIGGER IF EXISTS xs_insert;
    -> CREATE TRIGGER xs_insert BEFORE INSERT
    -> ON xs FOR EACH ROW
    -> BEGIN
    ->     SET @user = USER();
    ->     SET @operator_type = '插入';
    ->     SET @old_content = 'null';
    ->     SET @new_content = CONCAT(NEW.学号, NEW.姓名);
    ->     SET @time = NOW();
    ->     INSERT INTO record (用户名, 操作类型, 原内容, 新内容, 时间)
    ->     VALUES (@user, @operator_type, @old_content, @new_content, @time);
    -> END $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> DELIMITER $$
mysql> DROP TRIGGER IF EXISTS xs_delete;
    -> CREATE TRIGGER xs_delete AFTER DELETE
    -> ON xs FOR EACH ROW
    -> BEGIN
    ->     SET @user = USER();
    ->     SET @operator_type = '删除';
    ->     SET @old_content = CONCAT(OLD.学号, OLD.姓名);
    ->     SET @new_content = 'null';
    ->     SET @time = NOW();
    ->     INSERT INTO record (用户名, 操作类型, 原内容, 新内容, 时间)
    ->     VALUES (@user, @operator_type, @old_content, @new_content, @time);
    -> END $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> INSERT INTO xs VALUES ('081306', '龟蛇弟', '网络安全', 0, '2012-08-08', 4, NULL, NULL)；
    -> INSERT INTO xs VALUES ('081306', '龟蛇弟', '网络安全', 0, '2012-08-08', 4, NULL, NULL)；
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '；
INSERT INTO xs VALUES ('081306', '龟蛇弟', '网络安全', 0, '2012-08-08' at line 1
mysql> INSERT INTO xs VALUES ('081306', '龟蛇弟', '网络安全', 0, '2012-08-08', 4, NULL, NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO xs VALUES ('081307', '乌暗形', '网络安全', 1, '2012-08-18', 4, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from record;
+------+----------------+----------+--------+--------------+---------------------+
| 序号     | 用户名              | 操作类型         | 原内容      | 新内容             | 时间                   |
+------+----------------+----------+--------+--------------+---------------------+
|    1 | root@localhost | 插入         | null   | 081306龟蛇弟      | 2024-06-17 17:10:50 |
|    2 | root@localhost | 插入         | null   | 081307乌暗形      | 2024-06-17 17:11:54 |
+------+----------------+----------+--------+--------------+---------------------+
2 rows in set (0.00 sec)

mysql> DELETE FROM xs WHERE 姓名 = '龟蛇弟';
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM xs WHERE 姓名 = '乌暗形';
Query OK, 1 row affected (0.01 sec)

mysql> select * from record;
+------+----------------+----------+--------------+--------------+---------------------+
| 序号     | 用户名              | 操作类型         | 原内容            | 新内容             | 时间                   |
+------+----------------+----------+--------------+--------------+---------------------+
|    1 | root@localhost | 插入         | null         | 081306龟蛇弟      | 2024-06-17 17:10:50 |
|    2 | root@localhost | 插入         | null         | 081307乌暗形      | 2024-06-17 17:11:54 |
|    3 | root@localhost | 删除        | 081306龟蛇弟      | null         | 2024-06-17 17:12:38 |
|    4 | root@localhost | 删除        | 081307乌暗形      | null         | 2024-06-17 17:12:48 |
+------+----------------+----------+--------------+--------------+---------------------+
4 rows in set (0.00 sec)