Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.53 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use yggl;
Database changed
mysql> SOURCE D:/360安全浏览器下载/sy122.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.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 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, 5 rows affected (0.00 sec)
Records: 5 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.01 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, 13 rows affected (0.00 sec)
Records: 13 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, 13 rows affected (0.00 sec)
Records: 13 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> ALTER TABLE `departments`
-> MODIFY COLUMN `部门号` char(3);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE `employees`
-> ADD CONSTRAINT `fk_department_id`
-> FOREIGN KEY (`部门号`)
-> REFERENCES `departments`(`部门号`)
-> ON DELETE RESTRICT
-> ON UPDATE CASCADE;
Query OK, 13 rows affected (0.02 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> DELETE FROM `departments` WHERE `部门号` = '1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`yggl`.`employees`, CONSTRAINT `fk_department_id` FOREIGN KEY (`部门号`) REFERENCES `departments` (`部门号`) ON UPDATE CASCADE)
mysql> UPDATE `departments` SET `部门号` = '11' WHERE `部门号` = '1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM `employees`;
+--------+--------+------+------------+------+----------+-----------------+----------+--------+
| 编号 | 姓名 | 学历 | 出生日期 | 性别 | 工作时间 | 住址 | 电话 | 部门号 |
+--------+--------+------+------------+------+----------+-----------------+----------+--------+
| 000001 | 王林 | 大专 | 1966-01-23 | 1 | 8 | 中山路32*-1-508 | 83355668 | 2 |
| 010008 | 伍容华 | 本科 | 1976-03-28 | 1 | 3 | 北京东路100-2 | 83321231 | 11 |
| 020010 | 王向容 | 硕士 | 1982-12-09 | 1 | 2 | 四牌楼 10-0-108 | 53792361 | 11 |
| 020018 | 李丽 | 大专 | 1960-07-30 | 0 | 6 | 中山东路102-2 | 83413301 | 11 |
| 102201 | 刘明 | 本科 | 1972-10-18 | 1 | 3 | 虎踞路100-2 | 83606608 | 5 |
| 102208 | 朱俊 | 硕士 | 1965-09-28 | 1 | 2 | 牌楼巷5-3-106 | 64895568 | 5 |
| 102345 | 朱慧名 | 本科 | 1973-12-03 | 1 | 8 | 演达大道 | 87631234 | 11 |
| 108991 | 钟敏 | 硕士 | 1979-08-10 | 0 | 4 | 中山路10-3-105 | 83346454 | 3 |
| 111006 | 张石兵 | 本科 | 1974-10-01 | 1 | 1 | 解放军34-1-203 | 33358777 | 5 |
| 210678 | 林涛 | 大专 | 1972-10-18 | 1 | 2 | 中山北路24-35 | 83345656 | 3 |
| 302566 | 李玉珉 | 本科 | 1968-09-20 | 1 | 3 | 热和路209-3 | 58765991 | 4 |
| 308759 | 叶凡 | 本科 | 1978-11-18 | 1 | 2 | 北京西路3-7-52 | 83364644 | 4 |
| 504209 | 陈林琳 | 大专 | 1969-09-03 | 0 | 5 | 汉中路120-4-12 | 34445656 | 4 |
+--------+--------+------+------------+------+----------+-----------------+----------+--------+
13 rows in set (0.00 sec)
mysql> INSERT INTO `employees` (`员工号`, `姓名`, `部门号`, `职位`, `工资`)
-> VALUES ('员工号值', '姓名值', '7', '职位值', 工资值);
ERROR 1054 (42S22): Unknown column '工资值' in 'field list'
mysql> INSERT INTO employees (部门号)
-> VALUES (7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yggl`.`employees`, CONSTRAINT `fk_department_id` FOREIGN KEY (`部门号`) REFERENCES `departments` (`部门号`) ON UPDATE CASCADE)
mysql> ALTER TABLE `salary`
-> ADD CONSTRAINT `fk_employee_salary`
-> FOREIGN KEY (`编号`)
-> REFERENCES `employees` (`编号`)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE;
Query OK, 13 rows affected (0.04 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> DELETE FROM `employees` WHERE `编号` = '210678';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM `salary`;
+--------+---------+--------+
| 编号 | 收入 | 支出 |
+--------+---------+--------+
| 000001 | 2100.8 | 123.09 |
| 010008 | 1582.62 | 88.03 |
| 020010 | 2860 | 198 |
| 020018 | 2347.68 | 180 |
| 102201 | 2569.88 | 185.65 |
| 102208 | 1456 | 100 |
| 102345 | 1800 | 234 |
| 108991 | 3259.98 | 281.52 |
| 111006 | 1987.01 | 79.58 |
| 302566 | 2980.7 | 210.2 |
| 308759 | 2531.98 | 199.08 |
| 504209 | 2066.15 | 108 |
+--------+---------+--------+
12 rows in set (0.00 sec)
mysql> UPDATE `employees` SET `编号` = '123456' WHERE `编号` = '102345';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM `salary`;
+--------+---------+--------+
| 编号 | 收入 | 支出 |
+--------+---------+--------+
| 000001 | 2100.8 | 123.09 |
| 010008 | 1582.62 | 88.03 |
| 020010 | 2860 | 198 |
| 020018 | 2347.68 | 180 |
| 102201 | 2569.88 | 185.65 |
| 102208 | 1456 | 100 |
| 108991 | 3259.98 | 281.52 |
| 111006 | 1987.01 | 79.58 |
| 123456 | 1800 | 234 |
| 302566 | 2980.7 | 210.2 |
| 308759 | 2531.98 | 199.08 |
| 504209 | 2066.15 | 108 |
+--------+---------+--------+
12 rows in set (0.00 sec)
mysql> INSERT INTO `salary` VALUES ('888888', 3456, 234);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yggl`.`salary`, CONSTRAINT `fk_employee_salary` FOREIGN KEY (`编号`) REFERENCES `employees` (`编号`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> SELECT * FROM `salary`;
+--------+---------+--------+
| 编号 | 收入 | 支出 |
+--------+---------+--------+
| 000001 | 2100.8 | 123.09 |
| 010008 | 1582.62 | 88.03 |
| 020010 | 2860 | 198 |
| 020018 | 2347.68 | 180 |
| 102201 | 2569.88 | 185.65 |
| 102208 | 1456 | 100 |
| 108991 | 3259.98 | 281.52 |
| 111006 | 1987.01 | 79.58 |
| 123456 | 1800 | 234 |
| 302566 | 2980.7 | 210.2 |
| 308759 | 2531.98 | 199.08 |
| 504209 | 2066.15 | 108 |
+--------+---------+--------+
12 rows in set (0.00 sec)
MySQL?
最新推荐文章于 2024-10-02 12:42:27 发布