Day3 MySQL入门学习
MySQL 基础 （二）- 表操作&MySQL 基础 （三）- 表联结
一、MySQL 基础 （二）- 表操作
1 MySQL表数据类型
参考教程
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。MySQL5.7官方文档
- 数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。 - 日期和时间类型
表示时间值的日期和时间类型为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 类型，可存储的最大长度不同，可根据实际情况选择。
2 用SQL语句创建表
参考教程1
参考教程2
MySQL中使用CREATE TABLE创建新表。
2.1 语法
CREATE TABLE [IF NOT EXISTS] table_name (
column_list
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.2 语句解释
- 指定要在CREATE TABLE子句之后创建的表的名称。表名在数据库中必须是唯一的。 IF NOT EXISTS是语句的可选部分，允许您检查正在创建的表是否已存在于数据库中。 如果是这种情况，MySQL将忽略整个语句，不会创建任何新的表。 强烈建议在每个CREATE TABLE语句中使用IF NOT EXISTS来防止创建已存在的新表而产生错误。
- 在column_list部分指定表的列表。字段的列用逗号(，)分隔。注意最后一列后面不能有逗号(,)，否则会报错。
- ENGINE 设置存储引擎，CHARSET 设置编码。可以使用任何存储引擎，如：InnoDB，MyISAM，HEAP，EXAMPLE，CSV，ARCHIVE，MERGE， FEDERATED或NDBCLUSTER。如果不明确声明存储引擎，MySQL将默认使用InnoDB。
注：InnoDB自MySQL 5.5之后成为默认存储引擎。 InnoDB表类型带来了诸如ACID事务，引用完整性和崩溃恢复等关系数据库管理系统的诸多好处。在以前的版本中，MySQL使用MyISAM作为默认存储引擎。
2.3 设定列类型 、大小、约束
- 语法
column_name data_type[size] [NOT NULL|NULL] [DEFAULT value]
[AUTO_INCREMENT]
- 语句解释
column_name：指定列的名称。每列具有特定数据类型和大小。
NOT NULL或NULL：表示该列是否接受NULL值。注意NOT NULL列必须有数据，否则插入时报错。
DEFAULT：用于指定列的默认值。
AUTO_INCREMENT：定义列为自增的属性，一般用于主键，有新行插入数值会自动加1。每个表都有一个且只有一个AUTO_INCREMENT列。
2.4 设定主键
- 语法
PRIMARY KEY (col1,col2,...)
- 示例
CREATE TABLE IF NOT EXISTS task (
task_id INT(11) NOT NULL AUTO_INCREMENT,
subject VARCHAR(45) DEFAULT NULL,
start_date DATE DEFAULT NULL,
end_date DATE DEFAULT NULL,
description VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3 用SQL语句向表中添加数据
3.1 添加一行的MySQL INSERT 语句
- 语法
INSERT INTO table_name (column1,column2...)
VALUES (value1,value2,...);
- 语句解释
INSERT INTO的括号方列名，这是可选的，建议指定列名，不同列名用逗号分隔，最后一列后面没有逗号。
VALUES的括号里面放对应列的对应值，如果前面没有指明列，则按位置，依次插入。注意字符型数据要加单引号或者双引号。
3.2 添加多行
INSERT INTO table_name (column1,column2...)
VALUES (value1,value2,...),
(value1,value2,...),
...;
3.3 利用SELECT子句添加多行
INSERT INTO table_1
SELECT c1, c2, FROM table_2;
- 示例
INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'),
('任务-2','2017-01-01','2017-01-02','Description 2'),
('任务-3','2017-01-01','2017-01-02','Description 3');
4 用SQL语句删除表
4.1 三种删除方法
删除操作很简单，但是一定要确实是否需要使用WHERE子句，否则整表将被删除。
- DROP
DROP TABLE table_name;
-DELETE
DELETE FROM table_name;
或者
DELETE * FROM table_name;
- TRUNCATE
TRUNCATE TABLE table_name
4.2 不同方法的区别
DROP：删除表数据和表本身。真正意义上的删除表，操作前一定要小心。
DELETE：删除一行或者多行数据，不删除表本身。
TRUNCATE：仅删除表内数据，不删除表本身。
- 小结
- DROP TABLE table_name : 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;
- TRUNCATE TABLE table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM;
- DELETE FROM table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
- DELETE FROM table_name WHERE XXX : 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;
- DELETE 操作以后，使用 optimize TABLE table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;
- DELETE FROM 表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。
5 表的其他操作
5.1 修改表名
一般情况下，不建议修改表名，避免其他地方引用了该表名，导致报错。
语法[TO]可以省略
ALTER TABLE table_name_old RENAME TO table_name_new
5.2 修改列操作
- 修改列名及字段类型
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。
语法
-- 修改字段类型,修改字段c的类型为CHAR(10)
ALTER TABLE table_name MODIFY c CHAR(10);
-- 修改列名, 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型,
ALTER TABLE table_name CHANGE col_old col_new type;
注意类型不能省略。
- 新建列
MySQL 中使用 ADD 子句来向数据表中添加列，必须指定类的数据类型。
-- 新建列
ALTER TABLE table_name ADD col_name type [字段位置];
- 删除列
-- 删除列
ALTER TABLE table_name DROP column;
- 更新字段值
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
5.3 修改行操作
- 新建行
-- 新建行
INSERT INTO table_name (column1,column2...)
VALUES (value1,value2,...);
- 删除行
-- 指定WHERE子句删除特定的行
DELETE RROM table_name [WHERE Clause];
6 作业
6.1 项目三：超过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:
学生在每个课中不应被重复计算。
- Step 1 创建数据库my_db和表courses，并插入数据
-- 创建数据库my_db
CREATE DATABASE IF NOT EXIST my_db;
Query OK, 1 row affected (0.00 sec)
-- 选择数据库my_db
mysql> use my_db;
Database changed
-- 创建表courses
mysql> CREATE TABLE IF NOT EXISTS courses(
-> student VARCHAR(255),
-> class VARCHAR(255)
-> );
-- 往courses表中添加数据
mysql> INSERT INTO courses(student, class)
-> VALUES('A','Math'),
-> ('B','English'),
-> ('C', 'Math'),
-> ('D', 'Biology'),
-> ('E', 'Math'),
-> ('F', 'Computer'),
-> ('G', 'Math'),
-> ('H', 'Math'),
-> ('I', 'Math'),
-> ('A', 'Math');
-- 查看数据是否插入成功
mysql> SELECT *
-> FROM courses;
+---------+----------+
| student | class |
+---------+----------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
| A | Math |
+---------+----------+
10 rows in set (0.00 sec)
- Step 2 查询代码
mysql> SELECT class
-> FROM courses
-> GROUP BY class
-> HAVING COUNT(DISTINCT student) >= 5;
+-------+
| class |
+-------+
| Math |
+-------+
1 row in set (0.04 sec)
6.2 项目四：交换工资（难度：简单）
创建一个 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 |
- Step 1 创建表salary
-- 创建表salary并添加数据
mysql> CREATE TABLE IF NOT EXISTS salary(
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(255),
-> sex CHAR(1),
-> salary INT,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.20 sec)
-- 添加数据,注意id是自增的，所以没有手动添加
mysql> INSERT INTO salary(name, sex, salary)
-> VALUES('A', 'f', 2500),
-> ('B', 'm', 1500),
-> ('C', 'f', 5500),
-> ('D', 'm', 500);
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
- Step 2 查询
mysql> UPDATE salary
-> SET sex = (
-> CASE
-> WHEN sex = 'f' THEN 'm'
-> WHEN sex = 'm' THEN 'f'
-> END
-> );
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT *
-> FROM salary;
+----+------+------+--------+
| id | name | sex | salary |
+----+------+------+--------+
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
+----+------+------+--------+
4 rows in set (0.00 sec)
7 参考资料
- 《SQL必知必会第四版》
- RUNOOB.comMySQL教程
- 易百MySQL教程
二、MySQL 基础 （三）- 表联结
1 SQL别名
别名（alias）：可以为表名称或列名称指定别名。别名用AS关键字赋予。
1.1 SQL别名的应用场景：
- 查询中超过一个表
- 查询中用到了函数
- 列名称很长或者可读性差
- 需要把两列或者多个列结合在一起
1.2 SQL别名的作用是：
- 方便引用
- 提高可读性
1.3 别名示例
-- 列别名
SELECT column_name AS alias_name
FROM table_name;
-- 表别名
SELECT column_name(s)
FROM table_name AS alias_name;
2 INNER JOIN
内联接，INNER JOIN 关键字在表中存在至少一个匹配时返回行。
INNER JOIN 与 JOIN 是相同的。
- 示意图（交集）
-- 用法一 INNER JOIN
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
-- 用法二 JOIN
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
3 LEFT JOIN
左连接：LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。
- 示意图
-- LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
-- LEFT OUTER JOIN
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
4 RIGHT JOIN
右连接：RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
- 示例
-- RIGHT JOIN
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
-- RIGHT OUTER JOIN
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
5 FULL OUTER JOIN
FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.
FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
- 示例
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
6 CROSS JOIN
即叉联结，也称笛卡尔积联结。由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
- 示例
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
7 自连接(self-join)
在同一个表内进行连接处理。
- 示例
-- 不用自连接
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');
-- 使用自连接
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
8 UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
-- 默认地，UNIION操作选取不同的值
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
-- UNION ALL允许重复的值
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
9 不同联结方法的区别
10 作业
10.1 项目五：组合两张表 （难度：简单）
在数据库中创建表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
- Step 1 建立表Person和Addres
-- 建立表Person
mysql> CREATE TABLE IF NOT EXISTS Person(
-> PersonID INT NOT NULL,
-> FirstName VARCHAR(255),
-> LastName VARCHAR(255),
-> PRIMARY KEY (PersonID)
-> );
Query OK, 0 rows affected (0.17 sec)
-- 写入Person数据
mysql> INSERT INTO person(PersonID, FirstName, LastName)
-> VALUES(101, '三', '张'),
-> (102, '四', '李'),
-> (103, '五', '王');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 建立表Address
mysql> CREATE TABLE IF NOT EXISTS Address(
-> AddressID INT NOT NULL,
-> PersonID INT,
-> City VARCHAR(255),
-> State VARCHAR(255),
-> PRIMARY KEY (AddressID)
-> );
Query OK, 0 rows affected (0.16 sec)
-- 插入表Address数据
mysql> INSERT INTO Address(AddressID, PersonID, City, State)
-> VALUES(333, 101, 'WuHan', 'CN'),
-> (444, 102, 'GuiLin', 'CN'),
-> (555, 104, 'NewYork', 'USA');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
- Step 2 查询语句
mysql> SELECT FirstName, LastName, City, State
-> FROM Person
-> LEFT JOIN Address
-> ON Person.PersonID = Address.PersonID;
+-----------+----------+--------+-------+
| FirstName | LastName | City | State |
+-----------+----------+--------+-------+
| 三 | 张 | WuHan | CN |
| 四 | 李 | GuiLin | CN |
| 五 | 王 | NULL | NULL |
+-----------+----------+--------+-------+
3 rows in set (0.06 sec)
10.2 项目六：删除重复的邮箱（难度：简单）
编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 ID 最小 的那个。
| ID | |
|---|---|
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
ID 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person表应返回以下几行:
| ID | |
|---|---|
| 1 | [email protected] |
| 2 | [email protected] |
-- 创建表email
mysql> CREATE TABLE IF NOT EXISTS email(
-> ID INT NOT NULL AUTO_INCREMENT,
-> Email VARCHAR(255),
-> PRIMARY KEY(ID)
-> );
Query OK, 0 rows affected (0.23 sec)
-- 添加数据
mysql> INSERT INTO email(Email)
-> VALUES('[email protected]'),
-> ('[email protected]'),
-> ('[email protected]');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
- Step 2 查询语句
mysql> DELETE e2
-> FROM email AS e2, email AS e1
-> WHERE e2.Email=e1.Email AND e2.ID>e1.ID;
Query OK, 1 row affected (0.06 sec)
mysql> SELECT *
-> FROM email;
+----+---------+
| ID | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
+----+---------+
2 rows in set (0.00 sec)
11 参考资料
- 《SQL必知必会第四版》