MySQL基础学习2

2.1 MySQL 基础 （二）- 表操作

1. MySQL表数据类型
   https://www.runoob.com/mysql/mysql-data-types.html
   MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
   （1）数值
   （2）日期/时间
   （3）字符串(字符)类型

2. 用SQL语句创建表
   MySQL数据表包括：表名、表字段、名定义每个表字段。

通用语句：
CREATE TABLE table_name (column_name column_type);

语句解释
设定列类型 、大小、约束
设定主键

3. 用SQL语句向表中添加数据
   在MySQL 表中，使用 INSERT INTO SQL语句来插入数据。

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
                       
如果数据是字符型，必须使用单引号或者双引号，如："value"。

语句解释
多种添加方式（指定列名；不指定列名）

4. 用SQL语句删除表

删除语句的代码：
DROP TABLE table_name ;

语句解释
DELETE
DROP
TRUNCATE
不同方式的区别

5. 用SQL语句修改表
   https://blog.csdn.net/xilihong816/article/details/52763249

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

修改列名
修改表中数据
删除行
删除列
新建列
新建行

作业

项目三：编写一个 SQL 查询，列出所有超过或等于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),
class VARCHAR(50)
);

插入数据：
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 class from courses
GROUP BY class
HAVING COUNT(DISTINCT student)>=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 |

创建表：
CREATE TABLE Salary(
id INT PRIMARY KEY,
name VARCHAR(50),
sex VARCHAR(50),
salary INT
);

插入数据：
INSERT INTO Salary VALUES(1, 'A', 'm', 2500);
INSERT INTO Salary VALUES(2, 'B', 'f', 2500);
INSERT INTO Salary VALUES(3, 'C', 'm', 2500);
INSERT INTO Salary VALUES(4, 'D', 'f', 2500);

UPDATE Salary
SET sex = 
CASE when sex = 'f'
THEN 'm'
ELSE  'f'
END;

SELECT * FROM Salary

结果如下：

项目五：有趣的电影

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。
作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。
例如，下表 cinema:
±--------±----------±-------------±----------+ | id | movie | description | rating | ±--------±----------±-------------±----------+ | 1 | War | great 3D | 8.9 | | 2 | Science | fiction | 8.5 | | 3 | irish | boring | 6.2 | | 4 | Ice song | Fantacy | 8.6 | | 5 | House card| Interesting| 9.1 | ±--------±----------±-------------±----------+ 对于上面的例子，则正确的输出是为：
±--------±----------±-------------±----------+ | id | movie | description | rating | ±--------±----------±-------------±----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | ±--------±----------±-------------±----------+

创建表：
DROP TABLE IF EXISTS cinema;

CREATE TABLE cinema (
id INT(11) NOT NULL,
movie VARCHAR(255) DEFAULT NULL,
description VARCHAR(255) DEFAULT NULL,
rating FLOAT(2,1) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据：
INSERT INTO cinema VALUES ('1', 'War', 'great3D', '8.9');
INSERT INTO cinema VALUES ('2', 'Science','fiction', '8.5');
INSERT INTO cinema VALUES ('3', 'irish','boring', '6.2');
INSERT INTO cinema VALUES ('4', 'Ice song','Fantacy', '8.6');
INSERT INTO cinema VALUES ('5', 'House card','Interesting', '9.1');

选取数据：
select * from cinema where description<> 'boring' and  MOD(id, 2) = 1 order by rating desc;

知识点：
奇数：mod(id,2)=1
偶数：mod(id,2)=0

结果如下：

2.2 MySQL 基础 （三）- 表联结

• MySQL别名
• INNER JOIN
• LEFT JOIN
• CROSS JOIN
• 自连接
• UNION
• 以上几种方式的区别和联系

1、JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
2、UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

项目六：组合两张表 （难度：简单）

在数据库中创建表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

使用连接查询，分为内连接和外连接
    内连接（inner join）会舍弃两表中不相同的部分
    外连接（left join和right join）除了显示符合条件的记录外，还显示表中的记录

创建表Person：
CREATE TABLE Person(
PersonId INT,
FirstName VARCHAR(10),
LastName VARCHAR(10)
);

创建表Address：
CREATE TABLE Address(
AddressId INT,
PersionId INT,
City VARCHAR(255),
State VARCHAR(255)
);
插入数据Person和Address：
INSERT INTO Person VALUES (1,'Jobs','Steven');
INSERT INTO Person VALUES(2,'Jack','Ma');
INSERT INTO Person VALUES(3,'Jack','Chen');

INSERT INTO Address VALUES(001,1,'Silicon Valley','California');
INSERT INTO Address VALUES(002,2,'Hangzhou','Zhejiang');
INSERT INTO Address VALUES(003,2,'HongKong','China');

组合选取数据：
SELECT FirstName, LastName, City, State FROM 
        Person LEFT JOIN Address ON Person.PersonId=Address.PersionId; 

结果如下：

项目七：删除重复的邮箱（难度：简单）

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

DROP TABLE IF EXISTS email;

创建表：
CREATE TABLE email ( 
Id INT 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' );

删除重复数据并按要求输出：
DELETE FROM email 
WHERE Id NOT IN 
( SELECT a.min_id FROM ( SELECT MIN( Id ) AS min_id FROM email GROUP BY Email ) a );

结果如下：

项目八：从不订购的客户 （难度：简单）

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
±—±------+ | Id | Name | ±—±------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | ±—±------+ Orders 表：
±—±-----------+ | Id | CustomerId | ±—±-----------+ | 1 | 3 | | 2 | 1 | ±—±-----------+ 例如给定上述表格，你的查询应返回：
±----------+ | Customers | ±----------+ | Henry | | Max | ±----------+

DROP TABLE IF EXISTS Customers;
创建Customers表：
Create table If Not Exists Customer (Id int, Name varchar(255));

DROP TABLE IF EXISTS Orders;
创建Orders 表：
Create table If Not Exists Order1(Id int,CustomerId int);


插入数据：
INSERT INTO Customer VALUES( 1, 'Joe' );
INSERT INTO Customer VALUES( 2, 'Henry' );
INSERT INTO Customer VALUES( 3, 'Sam' );
INSERT INTO Customer VALUES( 4, 'Max' );

INSERT INTO Order1 VALUES( 1, 3);
INSERT INTO Order1 VALUES( 2,1);

查找从未购物的客户：

（1）使用not exists

select c.Name as Customer from Customer c 

where not exists 

(select distinct o.CustomerId Id from Order1 o where c.Id = o.CustomerId);

（2）使用 not in
select c.Name as Customer from Customer c 

where c.Id not in 

(select distinct o.CustomerId Id from Order1 o);

出现问题：faied to add the foreign key constraint

解决办法：
https://blog.csdn.net/xldmx/article/details/85090724
https://blog.csdn.net/xldmx/article/details/85090724

结果如下：

项目九：超过经理收入的员工（难度：简单）

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
±—±------±-------±----------+ | Id | Name | Salary | ManagerId | ±—±------±-------±----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | ±—±------±-------±----------+ 给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
±---------+ | Employee | ±---------+ | Joe | ±---------+

创建表：
CREATE TABLE Employee (
Id int,
Name varchar(255), 
Salary int, 
ManagerId int
);

插入数据：
INSERT INTO Employee VALUES ('1', 'Joe', '70000', '3');
INSERT INTO Employee VALUES('2', 'Henry', '80000', '4');
INSERT INTO Employee VALUES('3', 'Sam', '60000', Null);
INSERT INTO Employee VALUES('4', 'Max', '90000', Null);

查询：
Select e1.Name as Employee from Employee e1 join Employee e2  on e1.ManagerId = e2.Id and e1.Salary > e2.Salary;

或者：Select e1.Name as Employee from Employee e1 , Employee e2  where e1.ManagerId = e2.Id and e1.Salary > e2.Salary;

结果如下：

参考学习：

https://blog.csdn.net/albert184/article/details/90555099