- 创建一个名称为mydb的数据库并使用该数据库。
create database mydb;
2.在mydb数据库中创建两张表，分别是emp表和dept表，字段信息如下所示。
emp表
| 字段 | 类型 | 是否为空 | 是否主键 | 备注说明 |
|---|---|---|---|---|
| id | int(4) | NO | PRI | 员工编号 |
| ename | varchar(30) | NO | 员工姓名 | |
| gender | char(2) | YES | 员工性别 | |
| salary | float | YES | 员工工资 | |
| did | int(4) | NO | 所属部门编号 |
dept表
| 字段 | 类型 | 是否为空 | 是否主键 | 备注说明 |
|---|---|---|---|---|
| did | int(4) | NO | PRI | 部门编号 |
| dname | varchar(36) | YES | 部门名称 |
mysql> create table emp (id int(4) not null primary key,ename varchar(30) not null,gender char(23),salary float,did int(4) not null);
mysql> create table dept (did int(4) not null primary key,dname varchar(36));
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> alter table emp convert to character set utf8mb4;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table dept convert to character set utf8mb4;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.(1)向emp表中添加5条记录，记录信息如下。
| id | ename | gender | salary | did |
|---|---|---|---|---|
| 01 | 李兰 | 女 | 3500 | 30 |
| 02 | 张俊成 | 男 | 4800 | 10 |
| 03 | 宋清 | 女 | 6000 | 30 |
| 04 | 李志博 | 男 | 4500 | 20 |
| 05 | 吴志 | 男 | 3800 | 40 |
mysql> insert into emp (id,ename,gender,salary,did) values (01,'李兰','女',3500,30),(02,'张俊成','男',4800,10),
-> (03,'宋清','女',6000,30),
-> (04,'李志博','男',4500,20),
-> (05,'吴志','男',3800,40);
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
(2)向dept表中添加4条记录，记录信息如下。
| did | dname |
|---|---|
| 10 | 技术部 |
| 20 | 财务部 |
| 30 | 销售部 |
| 40 | 后勤部 |
mysql> insert into dept (did,dname) values (10,'技术部'),(20,'财务部'),(30,'销售部'),(40,'后勤部');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
4.查询出销售部的员工信息。
mysql> select * from emp where did=30;
+----+-------+--------+--------+-----+
| id | ename | gender | salary | did |
+----+-------+--------+--------+-----+
| 1 | 李兰 | 女 | 3500 | 30 |
| 3 | 宋清 | 女 | 6000 | 30 |
+----+-------+--------+--------+-----+
子查询做法：
mysql> select * from emp where did=(select did from dept where dname='销售部');
+----+-------+--------+--------+-----+
| id | ename | gender | salary | did |
+----+-------+--------+--------+-----+
| 1 | 李兰 | 女 | 3500 | 30 |
| 3 | 宋清 | 女 | 6000 | 30 |
+----+-------+--------+--------+-----+
2 rows in set (0.06 sec)
5.查询得出emp表中性别是男的员工的平均工资。
mysql> select avg(salary) from emp where gender='男';
+-------------------+
| avg(salary) |
+-------------------+
| 4366.666666666667 |
+-------------------+
1 row in set (0.00 sec)
6.查询出emp表中名字中包含“志”的员工信息。
mysql> select * from emp where ename like '%志%';
+----+--------+--------+--------+-----+
| id | ename | gender | salary | did |
+----+--------+--------+--------+-----+
| 4 | 李志博 | 男 | 4500 | 20 |
| 5 | 吴志 | 男 | 3800 | 40 |
+----+--------+--------+--------+-----+
2 rows in set (0.00 sec)
7.查询所有员工姓名及对应部门的名称。
mysql> select ename,did from emp;
+--------+-----+
| ename | did |
+--------+-----+
| 李兰 | 30 |
| 张俊成 | 10 |
| 宋清 | 30 |
| 李志博 | 20 |
| 吴志 | 40 |
+--------+-----+
5 rows in set (0.00 sec)
mysql> select e.ename,d.dname from emp as e,dept as d where e.did =d.did;
+--------+--------+
| ename | dname |
+--------+--------+
| 李兰 | 销售部 |
| 张俊成 | 技术部 |
| 宋清 | 销售部 |
| 李志博 | 财务部 |
+--------+--------+
4 rows in set (0.00 sec)
左连接
mysql> select e.ename,d.dname from emp e left join dept d on e.did=d.did;
+--------+--------+
| ename | dname |
+--------+--------+
| 李兰 | 销售部 |
| 张俊成 | 技术部 |
| 宋清 | 销售部 |
| 李志博 | 财务部 |
| 吴志 | NULL |
+--------+--------+
5 rows in set (0.02 sec)
mysql>
8.公司架构调整导致要撤销后勤部，请你完成对dept表中相关数据的删除工作。
mysql> delete from dept where did=40;
Query OK, 1 row affected (0.08 sec)
mysql> desc dept;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| did | int(4) | NO | PRI | NULL | |
| dname | varchar(36) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)
实验结果
mysql>
mysql>
mysql>
mysql>
mysql> create database mydb;
Query OK, 1 row affected (0.06 sec)
mysql> create table emp (id int(4) not null primary key,ename varchar(30) not n
ull,gender char(23),salary float,did int(4) not null);
ERROR 1046 (3D000): No database selected
mysql> create table emp (id int(4) not null primary key,ename varchar(30) not n
ull,gender char(23),salary float,did int(4) not null);
ERROR 1046 (3D000): No database selected
mysql> use mydb;
Database changed
mysql> create table emp (id int(4) not null primary key,ename varchar(30) not n
ull,gender char(23),salary float,did int(4) not null);
Query OK, 0 rows affected (0.17 sec)
mysql> create table dept (did int(4) not null primary key,dname varchar(36));
Query OK, 0 rows affected (0.03 sec)
mysql> alter table emp convert to character set utf8mb4;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table dname convert to character set utf8mb4;
ERROR 1146 (42S02): Table 'mydb.dname' doesn't exist
mysql> alter table dept convert to character set utf8mb4;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into emp (id,ename,gender,salary,did) values (01,'李兰','女',3500,
30),(02,'张俊成','男',4800,10),
-> (03,'宋清','女',6000,30),
-> (04,'李志博','男',4500,20),
-> (05,'吴志','男',3800,40);
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> insert into dept (did,dname) values (10,'技术部'),(20,'财务部'),(30,'销售
部'),(40,'后勤部');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from emp where did=30;
+----+-------+--------+--------+-----+
| id | ename | gender | salary | did |
+----+-------+--------+--------+-----+
| 1 | 李兰 | 女 | 3500 | 30 |
| 3 | 宋清 | 女 | 6000 | 30 |
+----+-------+--------+--------+-----+
2 rows in set (0.02 sec)
mysql> select avg(salary) from emp where gender='男';
+-------------------+
| avg(salary) |
+-------------------+
| 4366.666666666667 |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from emp where ename like '%志%';
+----+--------+--------+--------+-----+
| id | ename | gender | salary | did |
+----+--------+--------+--------+-----+
| 4 | 李志博 | 男 | 4500 | 20 |
| 5 | 吴志 | 男 | 3800 | 40 |
+----+--------+--------+--------+-----+
2 rows in set (0.00 sec)
mysql> select ename,did from emp;
+--------+-----+
| ename | did |
+--------+-----+
| 李兰 | 30 |
| 张俊成 | 10 |
| 宋清 | 30 |
| 李志博 | 20 |
| 吴志 | 40 |
+--------+-----+
5 rows in set (0.00 sec)
mysql> delete from dept where did=40;
Query OK, 1 row affected (0.08 sec)
mysql> desc dept;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| did | int(4) | NO | PRI | NULL | |
| dname | varchar(36) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)
mysql> show all columns from dept;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'all c
olumns from dept' at line 1
mysql> show full columns from dept;
+-------+-------------+--------------------+------+-----+---------+-------+-----
----------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Priv
ileges | Comment |
+-------+-------------+--------------------+------+-----+---------+-------+-----
----------------------------+---------+
| did | int(4) | NULL | NO | PRI | NULL | | sele
ct,insert,update,references | |
| dname | varchar(36) | utf8mb4_general_ci | YES | | NULL | | sele
ct,insert,update,references | |
+-------+-------------+--------------------+------+-----+---------+-------+-----
----------------------------+---------+
2 rows in set (0.00 sec)
mysql> select * from emp where did=(select did from dept where dname='销售部');
+----+-------+--------+--------+-----+
| id | ename | gender | salary | did |
+----+-------+--------+--------+-----+
| 1 | 李兰 | 女 | 3500 | 30 |
| 3 | 宋清 | 女 | 6000 | 30 |
+----+-------+--------+--------+-----+
2 rows in set (0.06 sec)
mysql>