MySQL基础（二）

MySQL基础（二）

教程链接：
https://www.yiibai.com/mysql/create-table.html
http://www.runoob.com/mysql/mysql-handling-duplicates.html

一、表操作

1. MySQL表数据类型
2. 用SQL语句创建表
   语句解释
   设定列类型 、大小、约束
   设定主键
3. 用SQL语句向表中添加数据
   语句解释
   多种添加方式（指定列名；不指定列名）
4. 用SQL语句删除表
   语句解释
   DELETE
   DROP
   TRUNCATE
   不同方式的区别
5. 用SQL语句修改表
   修改列名
   修改表中数据
   删除行
   删除列
   新建列
   新建行

二、表连结

1. MySQL别名
2. INNER JOIN
3. LEFT JOIN
4. CROSS JOIN
5. 自连接
6. UNION
7. 以上几种方式的区别和联系

项目三：列出所有超过或等于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名学生的课。
应该输出:
+---------+
| class   |
+---------+
| Math    |
+---------+
Note:
学生在每个课中不应被重复计算。

代码：

#创建表
CREATE TABLE courses (
student VARCHAR(50) NOT NULL,
class VARCHAR(50) NOT NULL
);

#插入数据
INSERT INTO courses(student, class)
    -> VALUES('A', 'Math');
INSERT INTO courses(student, class)
    -> VALUES('B', 'English');
INSERT INTO courses(student, class)
    -> VALUES('C', 'Math');
INSERT INTO courses(student, class)
    -> VALUES('D', 'Biology');
INSERT INTO courses(student, class)
    -> VALUES('E','Math')
INSERT INTO courses(student, class)
    -> VALUES('F','Computer');
INSERT INTO courses(student, class)
    -> VALUES('G', 'Math');
INSERT INTO courses(student, class)
    -> VALUES('H','Math');
INSERT INTO courses(student, class)
    -> VALUES('I','Math');
INSERT INTO courses(student, class)
    -> VALUES('J', 'Math');

#列出所有超过或等于5名学生的课
SELECT class FROM courses GROUP BY class HAVING count(class) >= 5;

结果：

表的创建与插入数据：

所有超过或等于5名学生的课：

项目四：交换工资

题目：

创建一个 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    |

代码：

#创建表
mysql> CREATE TABLE salary(
    -> id INT NOT NULL,
    -> name VARCHAR(50) NOT NULL,
    -> sex VARCHAR(50) NOT NULL,
    -> salary INT NOT NULL
    -> );

#插入数据
INSERT INTO salary(id, name, sex, salary)
    -> VALUES(1, 'A', 'm', 2500);
INSERT INTO salary(id, name, sex, salary)
    -> VALUES(2, 'B', 'f', 1500);
INSERT INTO salary(id, name, sex, salary)
    -> VALUES(3, 'C', 'm', 5500);
INSERT INTO salary(id, name, sex, salary)
    -> VALUES(4, 'D', 'f', 500);

#交换所有的 f 和 m 值
UPDATE salary SET sex = CASE sex WHEN 'f' THEN 'm' WHEN 'm' THEN 'f' ELSE sex END WHERE sex in ('f', 'm');

结果：

创建表及插入数据：

#交换所有的 f 和 m 值

项目五：组合两张表

题目：

在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person
+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address
+-------------+---------+
| 列名         | 类型    |
+-------------+---------+t
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

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

代码：

#创建表Person
mysql> CREATE TABLE Person(
    -> PersonId INT(1) NOT NULL AUTO_INCREMENT,
    -> FirstName VARCHAR(50) NOT NULL,
    -> LastName VARCHAR(50) NOT NULL,
    -> PRIMARY KEY (PersonId)
    -> );
#插入数据
mysql> INSERT INTO Person(PersonId, FirstName, LastName)
    -> VALUES ('1', 'dong', 'zhao'),
    -> ('2', 'xi', 'qian'),
    -> ('3', 'nan', 'sun')
    -> ('4', 'bei', 'li');

#创建表Address
mysql> CREATE TABLE Address(
    -> AddressId INT(2) NOT NULL AUTO_INCREMENT,
    -> PersonId INT(1) NOT NULL,
    -> City VARCHAR(50) NOT NULL,
    -> State VARCHAR(50) NOT NULL,
    -> PRIMARY KEY (AddressId)
    -> );

#插入数据
mysql> INSERT INTO Address(AddressId, PersonId, City, State)
    -> VALUES (11, 1, 'hrb','hlj'),
    -> (22, 2, 'sy', 'jl'),
    -> (33, 3, 'dl', 'ln'),
    -> (44, 4, 'cs', 'hn');

#组合这两个表
SELECT PersonId, AddressId, FirstName, LastName, City, State FROM Person  INNER JOIN Address USING (PersonId);

#查询组合表
SELECT FirstName, LastName, City, State FROM Person  INNER JOIN Address USING (PersonId);

结果：

Person表：

Address表：

组合这两个表：

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

项目六：删除重复的邮箱

题目：

编写一个 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  |
+----+------------------+

代码：

#创建表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');

#删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个
SELECT Id, Email FROM email GROUP BY Email;

结果：

参考资料：

易百教程：https://www.yiibai.com/mysql/create-table.html