mysql第二次打卡

项目三：超过5名学生的课（难度：简单）
题目：创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。
例如,表:
±--------±-----------+
| student | class | ±--------±-----------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | | A | Math | ±--------±-----------
编写一个 SQL 查询，列出所有超过或等于5名学生的课。

解答如下：
创建courses表，并插入数据：

CREATE TABLE courses(`student` VARCHAR(225) , `class` VARCHAR(225)); #创建表

#插入数据
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');

SELECT * from courses

输出courses表如下：

student  class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math

然后列出所有超过或等于5名学生的课程

SELECT class FROM courses GROUP BY class HAVING COUNT(*) >= 5;

输出结果：

class
Math

如果想要输出总共有多少人选择这个课，则可以这样：

SELECT class,Count(*) AS '数量' FROM courses  GROUP BY class,'数量' HAVING COUNT(*) >= 5 ;

输出结果：

class  数量
Math	7

项目四：交换工资（难度：简单）
题目：创建一个 salary表，如下所示，有m=男性 和 f=女性的值 ，交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
解答如下：
创建courses表，并插入数据：

CREATE TABLE salary(`id` INT NOT NULL PRIMARY KEY,  `name` VARCHAR(225),`sex` VARCHAR(225),`salary` INT NOT NULL ) ;
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);

SELECT * FROM salary

输出结果为：

id name sex salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

用update更新表中要求的内容：

UPDATE salary
SET
       sex = 'f'
WHERE
       id = 1 OR id =3; 
			 
UPDATE salary
SET
       sex = 'm'
WHERE
       id = 2 OR id =4; 

输出结果：

id name sex salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

项目五：组合两张表 （难度：简单）
题目：在数据库中创建表1和表2，并各插入三行数据（自己造），编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

创建表person和address：

CREATE TABLE person(`person_id` INT NOT NULL PRIMARY KEY, `first_name` VARCHAR(225), `last_name` VARCHAR(225));

INSERT INTO person
VALUES
   (1,'Peter','Jones'),
	 (2,'Bernice','Smith'),
	 (3,'Grace','Peterson');

SELECT *FROM person

#创建address表
CREATE TABLE address ( `address_id` INT NOT NULL PRIMARY KEY, `person_id` INT NOT NULL, `city` VARCHAR ( 225 ), `state` VARCHAR ( 225 ) );
INSERT INTO address
VALUES
	( 1001, 3, 'Las Vegas', 'NV' ),
	( 1002, 2, 'San Rafael', 'CA' ),
	( 1003, 1, 'Allentown', 'PV' );
SELECT *  FROM  address

输出结果：

person_id first_name last_name
1	Peter	Jones
2	Bernice	Smith
3	Grace	Peterson
address_id person_id city state
1001	3	Las Vegas	NV
1002	2	San Rafael	CA
1003	1	Allentown	PV

连接无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

SELECT first_name,last_name,city,state  FROM person
LEFT JOIN 
  address ON person.person_id = address.person_id 
ORDER BY
	person.person_id;

输出结果：

first_name last_name city state
Peter	Jones	Allentown	PV 
Bernice	Smith	San Rafael	CA
Grace	Peterson	Las Vegas	NV

项目六：删除重复的邮箱
题目：
编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 **Id ***最小 *的那个。
创建email表，并插入数据：

CREATE TABLE email ( Id INT NOT NULL PRIMARY KEY, Email VARCHAR ( 255 ) ); #创建表 

INSERT INTO email        
VALUES
	( '1', 'a@b.com' ); #插入数据
INSERT INTO email
VALUES
	( '2', 'c@d.com' );
INSERT INTO email
VALUES
	( '3', 'a@b.com' );
	
SELECT *FROM email;  #显示表

输出结果：

Id  Email
1	a@b.com
2	c@d.com
3	a@b.com

利用delete命令删除重复数据：
注意：利用delete命名删除数据是永久的删除

DELETE e1
FROM email AS e1, Email AS e2  #as可省略
WHERE
  e1.id > e2.Id AND e1.Email = e2.Email

SELECT *FROM email

输出结果：

Id  Email
1	a@b.com
2	c@d.com