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)
One Last MySQL
最新推荐文章于 2024-07-25 16:06:56 发布