首先公布一下DAY4的作业答案。
项目五:
--创建表
create table person (
personid int not null primary key,
firstname varchar(20) not null,
lastname varchar(20) not null
);
--插入数据
insert into person values (101, 'ming', 'Yao');
insert into person values (102, 'lei', 'Wu');
insert into person values (103, 'zhi', 'Zheng');
--查询结果
mysql> select * from person;
+----------+-----------+----------+
| personid | firstname | lastname |
+----------+-----------+----------+
| 101 | ming | Yao |
| 102 | lei | Wu |
| 103 | zhi | Zheng |
+----------+-----------+----------+
3 rows in set (0.00 sec)
--创建表
create table address (
addressid int not null primary key,
personid int not null,
city varchar(20) not null,
state varchar(20) not null
);
--插入数据
insert into address values (8000, 9999, 'xian', 'UA');
insert into address values (8008, 102, 'beijing', 'AB');
insert into address values (9009, 103, 'hangzhou', 'AC');
--查询结果
mysql> select * from address;
+-----------+----------+----------+-------+
| addressid | personid | city | state |
+-----------+----------+----------+-------+
| 8000 | 9999 | xian | UA |
| 8008 | 102 | beijing | AB |
| 9009 | 103 | hangzhou | AC |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)
mysql> select firstname, lastname, a.city, a.state
-> from person
-> left join address as a on person.personid = a.personid;
+-----------+----------+----------+-------+
| firstname | lastname | city | state |
+-----------+----------+----------+-------+
| ming | Yao | NULL | NULL |
| lei | Wu | beijing | AB |
| zhi | Zheng | hangzhou | AC |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)
项目六:
#问题分析
a. 删除重复的电子邮箱
b. 需要保留ID最小的那个(删除的ID比留下的那一条ID要大)
c. 考察自连接知识,把一张表当两张表来用。
--code--
mysql> select * from email;
+----+---------+
| ID | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
3 rows in set (0.00 sec)
mysql> delete e1
-> from email as e1, email as e2
-> where e1.Email = e2.Email
-> and e1.ID > e2.ID;
Query OK, 1 row affected (0.01 sec)
mysql> select * from email;
+----+---------+
| ID | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
+----+---------+
2 rows in set (0.00 sec)
继续今日DAY5内容:
1. 数据导入导出(公众号回复em获取文件)
navicat导入导出方法:
连接数据库账户
选择数据world
导入向导
导入类型选择excel
导入过程中可以修改字段类型
导入结果:
导出步骤:
选择数据库
选择表
导出向导
导出结果:
2. 作业
项目七: 各部门工资最高的员工(难度:中等)
创建Employee 表,包含所有员工信息,每个员工有其对应的 Id, salary 和
department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
创建Department 表,包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,
Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
项目八: 换座位(难度:中等)
小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
请创建如下所示seat表:
示例:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表,则输出结果如下:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意:
如果学生人数是奇数,则不需要改变最后一个同学的座位。
答案明天揭晓。
喜欢就给个好看吧~~