MySQL 基础 - 表操作与表联结

2.1 MySQL 基础 （二）- 表操作

1. 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。

2. 用SQL语句创建表

语句解释； 设定列类型 、大小、约束； 设定主键
创建MySQL数据表需要以下信息：

• 表名
• 表字段名
• 定义每个表字段

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

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl：

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;

实例解析：

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

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

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

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

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

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

注：添加数据的时候可以规定列进行添加。如果所有的列都要添加数据可以不规定列进行添加数据：

4. 用SQL语句删除表

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

MySQL中删除数据表非常容易操作， 但在进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

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

DROP TABLE table_name ;

三种删除方式区别：
1、删除表内数据，用 delete。格式为：

delete from 表名 where 删除条件;

实例：删除学生表内姓名为张三的记录。

delete from  student where  T_name = "张三";

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

truncate table 表名;

实例：清除学生表内的所有数据。

truncate  table  student;

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

drop  table  表名;

实例：删除学生表。

drop table student;

对比：
1、当你不再需要该表时， 用 drop;

2、当你仍要保留该表，但要删除所有记录时， 用 truncate;

3、当你要删除部分记录时， 用 delete。

5. 用SQL语句修改表

修改列名：
语法: ALTER TABLE 表名字 CHANGE 列名称 新列名称

修改表中数据：
Update 语句用于修改表中的数据。

语法： UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

将王老五的PersonId字段修改为 ’ 3 ’

UPDATE Person SET PersonId = '3' WHERE  PersonId = '5';

删除行：
DELETE 语句用于删除表中的数据。
DELETE 语句的基本格式如下所示：

DELETE  FROM tbl_name
WHERE  which rows to delete;

其中，WHERE子句是可选的，它用于指定需要删除掉哪些行。如果没有WHERE 子句，那么将删除表里面的所有行。这意味着，越简单的DELETE语句越危险，例如下面的语句：

DELETE FROM tbl_name

它会彻底删除表里的内容，因此务必要小心使用。

删除列：

   alter table 表名 drop column 列名;

新建列：

   alter table 表名 add column 列名 varchar(30);

新建行：
insert可以插入多条记录，记录用圆括号括起来，用逗号分隔开

insert into table_name(col1,col2...) values (col1,col2,...),(col2,col2,...)

项目三：超过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(20) NOT NULL PRIMARY KEY,
   class VARCHAR(25) 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');

列出所有超过或等于5名学生的课:

 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  IF NOT EXISTS `salary` (
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
sex ENUM('m','f','u') DEFAULT 'u',
salary INT DEFAULT 0
);

插入数据后表内容：

交换：

UPDATE salary 
SET sex=IF(sex='f','m','f');

2.2 MySQL 基础 （三）- 表联结

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

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

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

---

建Person表：

CREATE TABLE IF NOT EXISTS Person
(     PersonId INT NOT NULL,
      FirstName VARCHAR(30) NOT NULL,
      LastName VARCHAR(30) NOT NULL);

建Address表：

CREATE TABLE IF NOT EXISTS Address(
AddressId INT ,
 PersonId INT NOT NULL, 
 City VARCHAR(30) NOT NULL, 
 State VARCHAR(30) NOT NULL);

插入数据：

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

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