1 mysql> create table classcharger( 2 -> id tinyint primary key auto_increment, 3 -> name varchar(20), 4 -> age int, 5 -> is_marriged boolean 6 -> ); 7 Query OK, 0 rows affected (0.29 sec) 8 9 mysql> insert into classcharger(name,age,is_marriged) values ('冰冰',12,0), 10 -> ('丹丹',14,0), 11 -> ('歪歪',22,0), 12 -> ('姗姗',20,0), 13 -> ('小雨',21,0); 14 Query OK, 5 rows affected (0.07 sec) 15 Records: 5 Duplicates: 0 Warnings: 0 16 17 18 19 mysql> CREATE TABLE student2( 20 -> id int PRIMARY key auto_increment, 21 -> name varchar(11), 22 -> charger_id TINYINT, 23 -> foreign key (charger_id) REFERENCES classcharger(id)); 24 Query OK, 0 rows affected (0.24 sec) 25 26 mysql> show tables; 27 +----------------+ 28 | Tables_in_fang | 29 +----------------+ 30 | aa | 31 | class | 32 | classcharger | 33 | course | 34 | gou | 35 | jie | 36 | score | 37 | student | 38 | student1 | 39 | student2 | 40 | teacher | 41 +----------------+ 42 11 rows in set (0.00 sec) 43 44 45 mysql> insert into student2(name,charger_id) values('alvin1',2), 46 -> ('alvun2',4), 47 -> ('alvun3',1), 48 -> ('alvun4',3), 49 -> ('alvun5',1), 50 -> ('alvun6',3), 51 -> ('alvun7',2); 52 Query OK, 7 rows affected (0.08 sec) 53 Records: 7 Duplicates: 0 Warnings: 0 54 55 mysql> select * from student2; 56 +----+--------+------------+ 57 | id | name | charger_id | 58 +----+--------+------------+ 59 | 1 | alvin1 | 2 | 60 | 2 | alvun2 | 4 | 61 | 3 | alvun3 | 1 | 62 | 4 | alvun4 | 3 | 63 | 5 | alvun5 | 1 | 64 | 6 | alvun6 | 3 | 65 | 7 | alvun7 | 2 | 66 +----+--------+------------+ 67 7 rows in set (0.00 sec) 68 69 70 71 """ 72 报错信息mysql> delete from classcharger where id=1; 73 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key 74 constraint fails (`fang`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN 75 KEY (`charger_id`) REFERENCES `classcharger` (`id`)) 76 """ 77 78 79 80 mysql> update student2 set charger_id=2 where id=3 or id=5; 81 Query OK, 2 rows affected (0.10 sec) 82 Rows matched: 2 Changed: 2 Warnings: 0 83 84 mysql> select * from student2; 85 +----+--------+------------+ 86 | id | name | charger_id | 87 +----+--------+------------+ 88 | 1 | alvin1 | 2 | 89 | 2 | alvun2 | 4 | 90 | 3 | alvun3 | 2 | 91 | 4 | alvun4 | 3 | 92 | 5 | alvun5 | 2 | 93 | 6 | alvun6 | 3 | 94 | 7 | alvun7 | 2 | 95 +----+--------+------------+ 96 7 rows in set (0.00 sec) 97 98 99 100 mysql> delete from classcharger where id=1; 101 Query OK, 1 row affected (0.06 sec) 102 103 mysql> select * from student2; 104 +----+--------+------------+ 105 | id | name | charger_id | 106 +----+--------+------------+ 107 | 1 | alvin1 | 2 | 108 | 2 | alvun2 | 4 | 109 | 3 | alvun3 | 2 | 110 | 4 | alvun4 | 3 | 111 | 5 | alvun5 | 2 | 112 | 6 | alvun6 | 3 | 113 | 7 | alvun7 | 2 | 114 +----+--------+------------+ 115 7 rows in set (0.00 sec)
mysql> create table emp1( -> emp_id int auto_increment primary key not null, -> emp_name varchar(20), -> age int, -> dept_id int); Query OK, 0 rows affected (0.21 sec) mysql> insert into emp1(emp_name,age,dept_id) values ('A',19,200), -> ('B',26,201), -> ('C',30,201), -> -> ('D',24,202), -> ('E',20,200), -> ('F',38,204); Query OK, 6 rows affected (0.08 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> create table dep1( -> dept_id int, -> dept_name varchar(100) -> ); Query OK, 0 rows affected (0.34 sec) mysql> insert into dep1 values -> (200,'HR'), -> (201,'TEC'), -> (202,'SALE'), -> (203,'FINANCE'); Query OK, 4 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from emp1,dep1; +--------+----------+------+---------+---------+-----------+ | emp_id | emp_name | age | dept_id | dept_id | dept_name | +--------+----------+------+---------+---------+-----------+ | 1 | A | 19 | 200 | 200 | HR | | 1 | A | 19 | 200 | 201 | TEC | | 1 | A | 19 | 200 | 202 | SALE | | 1 | A | 19 | 200 | 203 | FINANCE | | 2 | B | 26 | 201 | 200 | HR | | 2 | B | 26 | 201 | 201 | TEC | | 2 | B | 26 | 201 | 202 | SALE | | 2 | B | 26 | 201 | 203 | FINANCE | | 3 | C | 30 | 201 | 200 | HR | | 3 | C | 30 | 201 | 201 | TEC | | 3 | C | 30 | 201 | 202 | SALE | | 3 | C | 30 | 201 | 203 | FINANCE | | 4 | D | 24 | 202 | 200 | HR | | 4 | D | 24 | 202 | 201 | TEC | | 4 | D | 24 | 202 | 202 | SALE | | 4 | D | 24 | 202 | 203 | FINANCE | | 5 | E | 20 | 200 | 200 | HR | | 5 | E | 20 | 200 | 201 | TEC | | 5 | E | 20 | 200 | 202 | SALE | | 5 | E | 20 | 200 | 203 | FINANCE | | 6 | F | 38 | 204 | 200 | HR | | 6 | F | 38 | 204 | 201 | TEC | | 6 | F | 38 | 204 | 202 | SALE | | 6 | F | 38 | 204 | 203 | FINANCE | +--------+----------+------+---------+---------+-----------+ 24 rows in set (0.03 sec) mysql> select * from emp1,dep1 where emp1.dept_id=dep1.dept_id; +--------+----------+------+---------+---------+-----------+ | emp_id | emp_name | age | dept_id | dept_id | dept_name | +--------+----------+------+---------+---------+-----------+ | 1 | A | 19 | 200 | 200 | HR | | 2 | B | 26 | 201 | 201 | TEC | | 3 | C | 30 | 201 | 201 | TEC | | 4 | D | 24 | 202 | 202 | SALE | | 5 | E | 20 | 200 | 200 | HR | +--------+----------+------+---------+---------+-----------+ 5 rows in set (0.00 sec) mysql> select dept_name from emp1,dep1 where emp1.dept_id=dep1.dept_id; +-----------+ | dept_name | +-----------+ | HR | | TEC | | TEC | | SALE | | HR | +-----------+ 5 rows in set (0.00 sec) mysql> select dept_name from emp1,dep1 where emp1.dept_id=dep1.dept_id and emp_name='C'; +-----------+ | dept_name | +-----------+ | TEC | +-----------+ 1 row in set (0.00 sec) mysql> select * from emp1 inner join dep1 on emp1.dept_id=dep1.dept_id; +--------+----------+------+---------+---------+-----------+ | emp_id | emp_name | age | dept_id | dept_id | dept_name | +--------+----------+------+---------+---------+-----------+ | 1 | A | 19 | 200 | 200 | HR | | 2 | B | 26 | 201 | 201 | TEC | | 3 | C | 30 | 201 | 201 | TEC | | 4 | D | 24 | 202 | 202 | SALE | | 5 | E | 20 | 200 | 200 | HR | +--------+----------+------+---------+---------+-----------+ 5 rows in set (0.00 sec) mysql> select * from emp1 inner join dep1 on emp1.dept_id=dep1.dept_id where emp_name='C'; +--------+----------+------+---------+---------+-----------+ | emp_id | emp_name | age | dept_id | dept_id | dept_name | +--------+----------+------+---------+---------+-----------+ | 3 | C | 30 | 201 | 201 | TEC | +--------+----------+------+---------+---------+-----------+ 1 row in set (0.00 sec) mysql> select * from emp1 right join dep1 on emp1.dept_id=dep1.dept_id; +--------+----------+------+---------+---------+-----------+ | emp_id | emp_name | age | dept_id | dept_id | dept_name | +--------+----------+------+---------+---------+-----------+ | 1 | A | 19 | 200 | 200 | HR | | 2 | B | 26 | 201 | 201 | TEC | | 3 | C | 30 | 201 | 201 | TEC | | 4 | D | 24 | 202 | 202 | SALE | | 5 | E | 20 | 200 | 200 | HR | | NULL | NULL | NULL | NULL | 203 | FINANCE | +--------+----------+------+---------+---------+-----------+ 6 rows in set (0.00 sec) mysql> select * from emp1 left join dep1 on emp1.dept_id=dep1.dept_id; +--------+----------+------+---------+---------+-----------+ | emp_id | emp_name | age | dept_id | dept_id | dept_name | +--------+----------+------+---------+---------+-----------+ | 1 | A | 19 | 200 | 200 | HR | | 5 | E | 20 | 200 | 200 | HR | | 2 | B | 26 | 201 | 201 | TEC | | 3 | C | 30 | 201 | 201 | TEC | | 4 | D | 24 | 202 | 202 | SALE | | 6 | F | 38 | 204 | NULL | NULL | +--------+----------+------+---------+---------+-----------+ 6 rows in set (0.00 sec) mysql> select * from emp1 where dept_id in (200,201,202,203); +--------+----------+------+---------+ | emp_id | emp_name | age | dept_id | +--------+----------+------+---------+ | 1 | A | 19 | 200 | | 2 | B | 26 | 201 | | 3 | C | 30 | 201 | | 4 | D | 24 | 202 | | 5 | E | 20 | 200 | +--------+----------+------+---------+ 5 rows in set (0.02 sec) mysql> select * from emp1 where dept_id in (select dept_id from dep1); +--------+----------+------+---------+ | emp_id | emp_name | age | dept_id | +--------+----------+------+---------+ | 1 | A | 19 | 200 | | 2 | B | 26 | 201 | | 3 | C | 30 | 201 | | 4 | D | 24 | 202 | | 5 | E | 20 | 200 | +--------+----------+------+---------+ 5 rows in set (0.00 sec)