-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathFOREIGN KEY.txt
More file actions
102 lines (91 loc) · 3.25 KB
/
FOREIGN KEY.txt
File metadata and controls
102 lines (91 loc) · 3.25 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
mysql> create table department
-> (
-> dept_no numeric(5,0) primary key,
-> dept_name varchar(20)
->
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> create table employee
-> (
-> emp_no numeric(5,0) primary key,
-> emp_name varchar(20),
-> salary int,
-> dept_num int references department
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into department values (1,'SALES'),(2,'RESEARCH'),(3,'MARKETING'),
-> (4,'DEVELOPEMENT'),(5,'HR');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into employee values (101,'NAME1',94890,5),(202,'NAME2',
89641,4),(303,'NAME3',33694,1),
-> (404,'NAME4',45269,3),(505,'NAME5',62562,2);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from department;
+---------+--------------+
| dept_no | dept_name |
+---------+--------------+
| 1 | SALES |
| 2 | RESEARCH |
| 3 | MARKETING |
| 4 | DEVELOPEMENT |
| 5 | HR |
+---------+--------------+
5 rows in set (0.00 sec)
mysql> select * from employee;
+--------+----------+--------+----------+
| emp_no | emp_name | salary | dept_num |
+--------+----------+--------+----------+
| 101 | NAME1 | 94890 | 5 |
| 202 | NAME2 | 89641 | 4 |
| 303 | NAME3 | 33694 | 1 |
| 404 | NAME4 | 45269 | 3 |
| 505 | NAME5 | 62562 | 2 |
+--------+----------+--------+----------+
5 rows in set (0.00 sec)
mysql> update employee
-> set dept_num=6
-> where emp_name='NAME5';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+--------+----------+--------+----------+
| emp_no | emp_name | salary | dept_num |
+--------+----------+--------+----------+
| 101 | NAME1 | 94890 | 5 |
| 202 | NAME2 | 89641 | 4 |
| 303 | NAME3 | 33694 | 1 |
| 404 | NAME4 | 45269 | 3 |
| 505 | NAME5 | 62562 | 6 |
+--------+----------+--------+----------+
5 rows in set (0.00 sec)
mysql> delete
-> from employee
-> where dept_num=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from employee;
+--------+----------+--------+----------+
| emp_no | emp_name | salary | dept_num |
+--------+----------+--------+----------+
| 101 | NAME1 | 94890 | 5 |
| 202 | NAME2 | 89641 | 4 |
| 303 | NAME3 | 33694 | 1 |
| 505 | NAME5 | 62562 | 6 |
+--------+----------+--------+----------+
4 rows in set (0.00 sec)
mysql> update employee
-> set emp_name='NAME6'
-> where dept_num=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+--------+----------+--------+----------+
| emp_no | emp_name | salary | dept_num |
+--------+----------+--------+----------+
| 101 | NAME1 | 94890 | 5 |
| 202 | NAME6 | 89641 | 4 |
| 303 | NAME3 | 33694 | 1 |
| 505 | NAME5 | 62562 | 6 |
+--------+----------+--------+----------+
4 rows in set (0.00 sec)