MYSqL测试题2_mysql练习题2

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)