MySQL-3

1 MySQL 基础 （二）- 表操作

#学习内容#

1.1 MySQL表数据类型

MySQL 数据类型
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

• 数值类型
  MySQL支持所有标准SQL数值数据类型。
  这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
  关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
  BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
  作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
  
• 日期和时间类型
  表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
  每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
  TIMESTAMP类型有专有的自动更新特性，将在后面描述。
  
• 字符串类型
  字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
  

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

1.2 用SQL语句创建表

创建MySQL数据表需要以下信息：
表名
表字段名
定义每个表字段

语句解释

以下为创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (column_name column_type);

以下示例包含了设定列类型 、大小、约束，以及主键的设定：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

列类型包括数值类型、字符串类型、日期和时间类型，在上一小节有详细说明。
约束包括非空约束(not null)，主键约束(primary key)，外键约束(foreign key)，唯一约束(unique)，默认值约束(default )，check约束。

约束	说明
NOT NULL 约束	NOT NULL 约束强制列不接受 NULL 值。NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
UNIQUE 约束	UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
PRIMARY KEY 约束	PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。
FOREIGN KEY 约束	一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。FOREIGN KEY 约束用于预防破坏表之间连接的动作。FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK 约束	CHECK 约束用于限制列中的值的范围。如果对单个列定义 CHECK 约束，那么该列只允许特定的值。如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
DEFAULT 约束	DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。

1.3 用SQL语句向表中添加数据

语句解释
MySQL 表中使用 INSERT INTO SQL语句来插入数据。
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

多种添加方式（指定列名；不指定列名）
NSERT INTO 语句可以有两种编写形式。
第一种形式无需指定要插入数据的列名，只需提供被插入的值即可。
没有指定要插入数据的列名的形式需要列出插入行的每一列数据:

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值，没有指定到的列的值则为空：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

1.4 用SQL语句删除表

语句解释
以下为删除MySQL数据表的通用语法：

DROP TABLE table_name ;

不同方式的区别

删除表内数据，用 delete。
格式为：

delete from 表名 where 删除条件;

清除表内数据，保存表结构，用 truncate。
格式为：

truncate table 表名;

删除表用 drop，就是啥都没了。
格式为：

drop table  表名;

用法就是：

• 当你不再需要该表时， 用 drop;
• 当你仍要保留该表，但要删除所有记录时， 用 truncate;
• 当你要删除部分记录时， 用 delete。

1.5 用SQL语句修改表

修改列名

ALTER TABLE TABLE_NAME  RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME;

修改表中数据

UPDATE table_name SET column1=value1,column2=value2,...
WHERE some_column=some_value;

删除行

DELETE FROM table_name WHERE some_column=some_value;

删除列
如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name DROP COLUMN column_name

新建列
如需在表中添加列，请使用下面的语法:

alter table 表名 add column 列名 varchar(20) not null;--插入在最后一列
alter table 表名 add column 列名 varchar(20) not null after column1;--插在column1列后面
alter table 表名 add column 列名 varchar(20) not null first;--插入到第一列

新建行

INSERT INTO table_name VALUES (value1,value2,value3,...);

#作业#

项目三：超过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:
学生在每个课中不应被重复计算。

--创建表：
use stu
create table courses (
student varchar(10) not null,
class varchar(30) not null
)

--插入数据：
insert into courses
values('A','Math'),
      ('B','English'),
      ('C','Math'),
      ('D','Biology'),
      ('E','Math'),
      ('F','Computer'),
      ('G','Math'),
      ('H','Math'),
      ('I','Math'),
      ('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

此处交换f和m值 ，采用IF语法：

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
if 具体用法参考
https://www.cnblogs.com/xuhaojun/p/9141396.html

--创建表：
use stu
create table salary(
id varchar(10) not null primary key,
name varchar(20) not null,
sex enum('m','f','a') default 'a',
salary int default 0
)
--插入数据：
insert into salary(name,sex,salary) 
values(1,'A','m',2500),
         	  (2,'B','f',1500),
 			  (3,'C','m',5500),
 			 (4,'D','f',500);
--查询数据：
update salary 
set sex=if(sex='f','m','f');
select * from salary--结果显示交换所有的 f 和 m 值后的表

输出：

2 MySQL 基础 （三）- 表联结

#学习内容#

2.1 MySQL别名

• 为表取别名
  查询数据时，如果表名很长，使用起来不方便，此时，就可以为表取一个别名，用这个别名来代替表的名称 。

SELECT * FROM 表名 [AS] 别名； 
--注意，为表指定别名，AS关键字可以省略不写

• 为字段取别名
  在查询数据时，为了使显示的查询结果更加直观，可以为字段取一个别名 。

SELECT 字段名 [AS] 别名 [，字段名 [AS] 别名，……] FROM 表名； 
--注意，为字段指定别名，AS关键字可以省略不写

2.2 INNER JOIN

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name；

MySQL的INNER JOIN 也可以省略 INNER 使用 JOIN，效果一样。

2.3 LEFT JOIN

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。如果右表中没有匹配，则结果为 NULL。

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name；

注释：MySQL中 LEFT JOIN 和 LEFT OUTER JOIN 两者等价，推荐使用 left join。

2.4 CROSS JOIN

CROSS JOIN 把表A和表B的数据进行一个N*M的组合，即笛卡尔积。

SELECT * FROM TableA CROSS JOIN TableB ；

笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

实际上，在 MySQL 中（仅限于 MySQL） CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。
INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字，因此下面的 SQL 效果是一样的：

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2

2.5 自连接

自连接就是一个表和它自身进行连接，是多表连接的特殊情况。
在自连接查询中，要先在FROM字句中为表分别定义两个不同的别名，然后使用这两个别名写出一个连接条件，条件比较在同一个表中的情况。

示例：

SELECT DISTINCT c1.CouNo 课程编号, c1.CouName 课程名称, c1.Kind 课程类别,c1.DepartNo 系部编号
FROM Course c1 JOIN Course c2 ON c1.Kind=c2.Kind  
AND c1.DepartNo!=c2.DepartNo ORDER BY c1.CouNo;

2.6 UNION

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法
MySQL UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

/*参数
expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选， 检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。
*/

#作业#

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

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

--创建表：
--表1
create table Person(
PersonId int,
FirstName varchar(10),
LastName varchar(10),
primary key(PersonId)
)
--表2
create table Address(
AddressId int,
 PersonId int default 0,
City varchar(100) not null,
State varchar(100) not null,
primary key(AddressId)
)
--插入数据：
insert into Address(PersonId,City,State) 
values(1,'a','a'),
      (2,'b','b'),
 	  (3,'c','c');

--查询语句：
select P.FirstName,P.LastName,A.City,A.State
from Person P left join Address A on P.PersonId=A.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 |
±—±-----------------+

--创建表：
create table if not exists email(
	Id INT auto_increment primary key,
	Email varchar(40) not null
);
--插入数据：
insert into email(Email) 
values('a@b.com'),('c@d.com'),('a@b.com');
--查询语句：
delete e1 from email e1
left join email e2 on e1.Email = e2.Email
where e1.Id>e2.Id