MySQL（二）

练习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);

效果显示如上图。