mysql> create tableemp1(-> emp_id int auto_increment primary key not null,-> emp_name varchar(20),-> age int,-> dept_id int);
Query OK,0 rows affected (0.21sec)
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.08sec)
Records:6 Duplicates: 0 Warnings: 0mysql> create tabledep1(-> dept_id int,-> dept_name varchar(100)->);
Query OK,0 rows affected (0.34sec)mysql> insert into dep1 values
-> (200,'HR'),-> (201,'TEC'),-> (202,'SALE'),-> (203,'FINANCE');
Query OK,4 rows affected (0.09sec)
Records:4 Duplicates: 0 Warnings: 0mysql> select * fromemp1,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.03sec)
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.00sec)
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.00sec)
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.00sec)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.00sec)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.00sec)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.00sec)
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.00sec)
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.02sec)mysql> select * from emp1 where dept_id in (select dept_id fromdep1);+--------+----------+------+---------+
| 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)
扫一扫
744
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
