MySQL|基础|表操作+表联结

MySQL|基础|表操作+表联结

---

1.任务描述

• TASK1：创建如下所示的 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名学生的课。
应该输出：

class
Math

Note：
学生在每个课中不应被重复计算。

• TASK2：交换工资（难度：简单）
  创建一个 salary 表，如下所示，有m=男性 和 f=女性的值 。

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

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
运行你所编写的查询语句之后，将会得到以下表：

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

• TASK3：组合两张表 （难度：简单）
  在数据库中创建表1和表2，并各插入三行数据（自己造）

表1：Person

列名	类型
PersonId	int
FirstName	varchar
LastName	varchar

PersonId 是上表主键

表2：Address

列名	类型
AddressId	int
PersonId	int
City	varchar
State	varchar

AddressId 是上表主键
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

• TASK4：删除重复的邮箱（难度：简单）
  编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

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

Id 是这个表的主键

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行：

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

2.TASK1：列出所有超过或等于5名学生的课【code】

CREATE TABLE
IF
	NOT EXISTS `courses` ( `student` VARCHAR ( 10 ) NOT NULL, `class` VARCHAR ( 40 ) NOT NULL ) ENGINE = INNODB DEFAULT charset = utf8;
INSERT INTO courses ( student, class )
VALUES
	( 'A', 'Math' ),
	( 'B', 'English' ),
	( 'C', 'Math' ),
	( 'D', 'Biology' ),
	( 'E', 'Math' ),
	( 'F', 'Computer' ),
	( 'G', 'Math' ),
	( 'H', 'Math' ),
	( 'I', 'Math' ),
	( 'A', 'Math' );
SELECT DISTINCT
	class
FROM
	courses 
GROUP BY
	class 
HAVING
	count( * ) >= 5;

3.TASK2：交换工资【code】

CREATE TABLE
IF
	NOT EXISTS `salary` (
		`id` INT NOT NULL auto_increment,
		`name` VARCHAR ( 10 ) NOT NULL,
		`sex` VARCHAR ( 10 ) NOT NULL,
		`salary` INT ( 100 ) NOT NULL,
	PRIMARY KEY ( `id` ) 
	) ENGINE = INNODB;
INSERT INTO salary
VALUES
	( 1, 'A', 'm', 2500 ),
	( 2, 'B', 'f', 1500 ),
	( 3, 'C', 'm', 5500 ),
	( 4, 'D', 'f', 500 );
UPDATE salary 
SET sex =
CASE
		sex 
		WHEN 'f' THEN 'm' 
		ELSE 'f' 
END;
select* FROM salary;

4.TASK3：组合两张表 【code】

CREATE TABLE
IF
	NOT EXISTS `Person` (
		`PersonId` INT NOT NULL auto_increment,
		`FirstName` VARCHAR ( 40 ) NOT NULL,
		`LastName` VARCHAR ( 40 ) NOT NULL,
		PRIMARY KEY ( PersonId ) 
	) ENGINE = INNODB DEFAULT charset = utf8;
CREATE TABLE
IF
	NOT EXISTS `Address` (
		`AddressId` INT NOT NULL auto_increment,
		`PersonId` INT NOT NULL,
		`City` VARCHAR ( 100 ) NULL,
		`State` VARCHAR ( 100 ) NULL,
		PRIMARY KEY ( AddressId ) 
	) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO Person
VALUES
	( 1, 'L', 'M' ),
	( 2, 'K', 'K' ),
	( 3, 'W', 'S' );
INSERT INTO Address
VALUES
	( 1, 2, 'A', 'A' ),
	( 2, 3, NULL, NULL ),
	( 3, 5, NULL, NULL );
SELECT
	FirstName,
	LastName,
	City,
	State 
FROM
	Person
	LEFT JOIN Address 
ON
	Person.PersonId = Address.PersonId;

5.TASK4：删除重复的邮箱【code】

CREATE TABLE
IF
	NOT EXISTS `email` ( `Id` INT auto_increment, `Email` VARCHAR ( 50 ) NULL, PRIMARY KEY ( Id ) ) ENGINE = INNODB DEFAULT charset = utf8;
INSERT INTO email
VALUES
	( 1, 'a@b.com' ),
	( 2, 'c@d.com' ),
	( 3, 'a@b.com' );
DELETE e1 
FROM
	email e1,
	email e2 
WHERE
	e1.Id > e2.Id 
	AND e1.Email = e2.Email;