day3

2.1 MySQL 基础 （二）- 表操作

1. MySQL表数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串类型

数值型

INYINT------------>小整数值

SMALLINT---------->大整数值

MEDIUMINT--------->大整数值

INT或INTEGER------>大整数值

BIGINT----------------->极大整数值

FLOAT------------------->浮点数值

DOUBLE---------------->双精度浮点数值

DECIMAL--------------->小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR

DATE-------------->YYYY-MM-DD---------->日期值

TIME--------------->HH:MM:SS--------------->时间值或持续时间

YEAR--------------->YYYY--------------------->年份值

DATETIME-------->YYYY-MM-DD HH:MM:SS---->混合时间和日期

TIMESTAMP------>YYYYMMDD HHMMSS------>时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

CHAR---------->0-255字节----------->定长字符串

VARCHAR----->0-65535 字节------->变长字符串

TINYBLOB---->0-255字节------------->不超过 255 个字符的二进制字符串

TINYTEXT---->0-255字节-------------->短文本字符串

BLOB----------->0-65 535字节----------->二进制形式的长文本数据

TEXT------------>0-65 535字节---------->长文本数据

MEDIUMBLOB–>0-16 777 215字节---->二进制形式的中等长度文本数据

2. 用SQL语句创建表

设定列类型 、大小、约束,设定主键
示例:

create table salary （
   id  int not null primary key,//设定约束（not null),设定主键( primary key)
   name varchar(255),//设定列类型(varchar)
   sex varchar(255),
   salary int
   );

常见约束：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。
  参考资料：http://www.runoob.com/sql/sql-constraints.html

3. 用SQL语句向表中添加数据

多种添加方式：
插入完整的行（不指定列）/插入行的一部分（指定列）

插入完整的行（不指定列）：

使用基本的 INSERT 语法

 INSERT INTO Customers
 VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

插入行的一部分（指定列）：

使用 INSERT 的推荐方法是明确给出表的列名。使用这种插入数据语法，还可以省略列，这表示可以只给某些列提供值，给其他列不提供值

INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA')

在前面的例子中，没有给 cust_contact 和 cust_email 这两列提
供值。这表示没必要在 INSERT 语句中包含它们。因此，这里的 INSERT
语句省略了这两列及其对应的值
省略列
如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列
必须满足以下某个条件。

• 该列定义为允许 NULL 值（无值或空值）
• 在表定义中给出默认值。这表示如果不给出值，将使用默认值

修改表中的数据：

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

UPDATE 语句总是以要更新的表名开始。在这个例子中，要更新的表名为Customers 。 SET 命令用来将新值赋给被更新的列。在这里， SET 子句设置 cust_email 列为指定的值

4. 用SQL语句删除表

一般用delete、drop、truncate三种语句俩实现

DELETE

删除整个表
DELETE FROM table_name
或者
DELETE FROM table_name;

删除指定数据(需要加上指定条件），如：

DELETE FROM test WHERE age=30 AND country='US';

SQL DELETE 语句中的 WHERE 子句规定哪条记录或者哪些记录需要删除。如果省略了 WHERE 子句，所有的记录都将被删除

DROP

删除整个表，并释放空间，将表删除的一干二净

DROP table_name;

TRUNCATE

删除表test里的内容，并释放空间，但不删除表的定义，表的结构还在

TRUNCATE table_name;

不同方式的区别

delete：删除表的内容，表的结构还存在，不释放空间，可以回滚恢复；
drop：删除表内容和结构，释放空间，没有备份表之前要慎用；
truncate：删除表的内容，表的结构存在，可以释放空间,没有备份表之前要慎用；

运行速度：

drop > truncate > delete

6. 用SQL语句修改表

修改列名

修改表中数据

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

删除行

使用delete语句。 DELETE 删除整行而不是删除列，它 不需要列名或通配符

删除列

UPDATE table_name
SET col_name = NULL

或

ALTER TABLE table_name
DROP COLUMN col_name;

新建列

ALTER TABLE Vendors

ADD vend_phone CHAR(20);

新建行

用INSER语句,示例：

INSERT INTO Customers
 VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

这个例子将一个新顾客插入到 Customers 表中。存储到表中每一列的数据在 VALUES 子句中给出，必须给每一列提供一个值。如果某列没有值，如上面的 cust_contact 和 cust_email 列，则应该使用 NULL 值（假定表允许对该列指定空值）。各列必须以它们在表定义中出现的次序填充；

编写 INSERT 语句的更安全（不过更烦琐）的方法如下：

INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

因为提供了列名， VALUES 必须以其指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条INSERT 语句仍然能正确工作。使用 INSERT 的推荐方法是明确给出表的列名。

#作业#
项目三：超过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:
学生在每个课中不应被重复计算。
select calss from course group by class having count(distinct syudent)>=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

update salary set sex=if(sex='f','m','f')

2.2 MySQL 基础 （三）- 表联结

1.MySQL别名

通过使用 SQL，可以为表名称或列名称指定别名。创建别名是为了让列名称的可读性更强。

列的 SQL 别名语法

SELECT column_name AS 别名
FROM table_name;

表的 SQL 别名语法

SELECT column_name(s)
FROM table_name AS 别名;

2.JOIN

SQL JOIN 子句用于基于这些表之间的共同字段，把来自两个或多个表的行结合起来。

主要有下面几种join的形式：
INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

INNER JOIN

只返回两个表中连接字段相等的行

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

INNER JOIN 与 JOIN 是相同的。

LEFT JOIN

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或者

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

RIGHT JION

关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

 SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集， 内部的每个 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。 [^1]

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

#作业#
项目五：组合两张表 （难度：简单）
在数据库中创建表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

select  FirstName,LastName,City,State  from Person a right join Address b on a.PersonId = b.PersonId

项目六：删除重复的邮箱（难度：简单）
编写一个 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 |
±—±-----------------+

delete p1 from email p1,email p2 where p1.Email = p2.Email and p1.ID > p2.ID