练习1：
题目要求：创建如下所示的 courses 表 ，有: student (学生) 和 class (课程)
step1:创建表格：
CREATE TABLE courses(
student VARCHAR(255) NOT NULL ,
class VARCHAR(255) NOT NULL
);
step2：插入数据
INSERT INTO courses VALUES('A','Math');
INSERT INTO courses VALUES('B','English');
INSERT INTO courses VALUES('C','Math');
INSERT INTO courses VALUES('D','Biology');
INSERT INTO courses VALUES('E','Math');
INSERT INTO courses VALUES('F','Computer');
INSERT INTO courses VALUES('G','Math');
INSERT INTO courses VALUES('H','Math');
INSERT INTO courses VALUES('I','Math');
INSERT INTO courses VALUES('A','Math');
step3:进行过滤–因学生A出现两次，且要求学生在每个课中不应被重复计算
select * from courses GROUP BY student
step4:编写查询语句，列出所有超过或等于5名学生的课
select class from (select * from courses GROUP BY student) as a GROUP BY a.class having count(class) >= 5;
step5:结果展示
练习2：---------update的更新查询（无中间临时表）
题目要求：创建一个 salary 表，如下所示，有m=男性 和 f=女性的值 。
应用到知识点：三目运算符（交换无中间临时列表）
step1:创建表格
CREATE TABLE salary(
id int NOT NULL PRIMARY key,
name varchar(255) NOT NULL,
sex VARCHAR(2) NOT NULL,
salary int(255) NOT NULL
);
step2:插入数据
insert into salary VALUES('1','A','m','2500');
insert into salary VALUES('2','B','f','1500');
insert into salary VALUES('3','C','m','5500');
insert into salary VALUES('4','D','f','500');
step3:进行交换
update salary set sex=if(sex='f','m','f')
step4：结果展示
练习3：—组合两张表格
在数据库中创建表1和表2，并各插入三行数据
题目要求：
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State
step1:创建表格及写入数据
表1：
create table person(
personid int unsigned primary key not null auto_increment,
firstname varchar(20) not null,
lastname varchar(20) not null
);
desc person;
insert into person values(1,"lei","li");
insert into person values(2,"ping","wang");
insert into person values(3,"gou","tou");
select * from person;
create table adderss(
addressid int unsigned primary key not null,
personid int unsigned not null,
city varchar(20),
state varchar(20)
);
insert into adderss values(121,1,"pudong","shanghai");
insert into adderss values(122,2,"zhangjiang","pudong");
insert into adderss values(132,3,"dizhonghai","songjiang");
select * from adderss;
step2:表1，表2效果显示
step3:组合表格
select person.firstname,person.lastname,adderss.city,adderss.state from person, adderss where person.personid=adderss.personid;
step4:组合表格效果显示
练习4：-------删除重复邮箱
题目要求：编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
思路1：首先依据id升做排序，后统计，则同email自动取的最小id。
select * from (select * from email group by id,email order by id asc) as a group by a.email having count(*)>=1;
效果显示：
思路2：首先选出同email中的最小id对应的值，再联合不同的那个email，构建新的表
select * from email where id in (select min(id) from email group by email having count(*)>1 ) union (select * from email group by email having count(*)=1);
效果显示如上图。