本文使用的数据库来自https://www.yiibai.com/mysql/sample-database.html ，

连接SQL

# 打开 MySQL 服务
sudo service mysql start
#连接SQL服务器，参数-h为sql服务器地址，-u为用户名，默认为root，-p为密码
mysql -h host -u root -p password
# 退出SQL
exit

数据库与数据表

显示-SHOW

SHOW DATABASES;
SHOW TABLES;
--# eg.数据表概览
mysql> show tables;
+--------------------+
| Tables_in_yiibaidb |
+--------------------+
| customers          |
| employees          |
| items              |
| offices            |
| orderdetails       |
| orders             |
| payments           |
| productlines       |
| products           |
| tokens             |
+--------------------+
10 rows in set (0.00 sec)

创建-CREAT

--#IF NOT EXISTS可选参数
CREATE DATABASE [IF NOT EXISTS] DATABASE_NAME;
CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type[size] 
                    [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]);

创建数据表时需要指定一下信息：

表名
表名字段

定义每个表字段

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `customerNumber` INT UNSIGNED AUTO_INCREMENT,
  `customerName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` int(11) DEFAULT NULL,
  `creditLimit` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`customerNumber`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

删除-DROP

小心，执行删除命令后所有数据都会消失。

1 2	drop database DATABASE_NAME; DROP TABLE table_name ;

选择数据库-USE

1	use DATABASE_NAME;

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度，浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度，浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。
类型|大小(字节)|范围|格式|用途
:- | :- | :- | :- | :- |
DATE|3|1000-01-01/9999-12-31|YYYY-MM-DD|日期值
TIME|3|’-838:59:59’/‘838:59:59’|HH:MM:SS|时间值或持续时间
YEAR|1|1901/2155|YYYY|年份值
DATETIME|8|1000-01-01 00:00:00/9999-12-31 23:59:59|YYYY-MM-DD HH:MM:SS|混合日期和时间值
TIMESTAMP|4|1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07|YYYYMMDD HHMMSS|混合日期和时间值，时间戳

字符串类型

类型	大小	用途
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字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

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

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

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

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

数据的插入- INSERT INTO

1
2
3

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

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

1 2	INSERT INTO `customers` VALUES ('103', 'Atelier graphique', '40.32.2555', '54, rue Royale', 'Nantes', '44000', '1370', '21000.00');

数据的查询-SELECT

1	SELECT column_name,column_name FROM table_name [WHERE Clause][LIMIT N][ OFFSET M];

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
WHERE 子句
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

WHERE 子句操作符包括：=（一个等号即可，不是==）、<>, !=、> 、< 、>=、<=

比较运算符包括：and、or、in、between、like等

UPDATE

UPDATE 命令修改 MySQL 数据表数据

UPDATE table_name SET field1=new-value1, field2=new-value2
    [WHERE Clause]
-- #eg:
UPDATE customers SET customerName='jack', city='NewYork'
    WHERE customerNumber=119;
-- #结果：
mysql> select customerName,city from customers where customerNumber=119;
+-------------------+--------+
| customerName      | city   |
+-------------------+--------+
| La Rochelle Gifts | Nantes |
+-------------------+--------+
1 row in set (0.00 sec)
mysql> UPDATE customers SET customerName='jack', city='NewYork'
    ->     WHERE customerNumber=119;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select customerName,city from customers where customerNumber=119;        +--------------+---------+
| customerName | city    |
+--------------+---------+
| jack         | NewYork |
+--------------+---------+
1 row in set (0.00 sec)

DELETE

使用 DELETE FROM 命令来删除 MySQL 数据表中的记录。

DELETE FROM table_name [WHERE Clause]
--# eg.
DELETE FROM customers WHERE customerNumber=119;
--#结果
mysql> select customerName,city from customers where customerNumber=119;
+--------------+---------+
| customerName | city    |
+--------------+---------+
| jack         | NewYork |
+--------------+---------+
1 row in set (0.00 sec)
mysql> DELETE FROM customers WHERE customerNumber=119;
Query OK, 1 row affected (0.05 sec)
mysql> select customerName,city from customers where customerNumber=119;
Empty set (0.00 sec)

LIKE 子句

使用 LIKE 子句进行模糊匹配。

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
--#eg.
mysql> select customerName,city from customers where city like 'n%' limit 5;
+------------------------+-----------+
| customerName           | city      |
+------------------------+-----------+
| Atelier graphique      | Nantes    |
| Land of Toys Inc.      | NYC       |
| Muscle Machine Inc     | NYC       |
| American Souvenirs Inc | New Haven |
| Vitachrome Inc.        | NYC       |
+------------------------+-----------+
5 rows in set (0.00 sec)

你可以在 WHERE 子句中使用LIKE子句。使用LIKE子句代替等号 =。
LIKE 中 % 代表任意字符

UNION 操作符

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

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT] --#DISTINCT: 可选，删除结果集中重复的数据。ALL: 可选，返回所有结果集，包含重复数据。
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
--#eg1. ALL
mysql> select city from customers where city like 'f%'
    -> union ALL
    -> select city from customers where city like 'g%';
+--------------+
| city         |
+--------------+
| Frankfurt    |
| Frankfurt    |
| Fribourg     |
| Glendale     |
| Genve        |
| Glendale     |
| Graz         |
| Glen Waverly |
+--------------+
8 rows in set (0.00 sec)
--#eg2. DISTINCT
mysql> select city from customers where city like 'f%'
    -> union DISTINCT
    -> select city from customers where city like 'g%';
+--------------+
| city         |
+--------------+
| Frankfurt    |
| Fribourg     |
| Glendale     |
| Genve        |
| Graz         |
| Glen Waverly |
+--------------+
6 rows in set (0.00 sec)

ORDER BY 子句

SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据。

1 2	SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]

使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。

--# eg.使用post local code 排序
mysql> select city,postalCode from customers where city like 'a%' ORDER BY postalCode;
+------------+------------+
| city       | postalCode |
+------------+------------+
| Auckland   | NULL       |
| Auckland   | NULL       |
| Auckland   | NULL       |
| Amsterdam  | 1043 GR    |
| Aachen     | 52066      |
| Allentown  | 70267      |
+------------+------------+
6 rows in set (0.00 sec)
mysql> select city,postalCode from customers where city like 'a%';
+------------+------------+
| city       | postalCode |
+------------+------------+
| Allentown  | 70267      |
| Amsterdam  | 1043 GR    |
| Auckland   | NULL       |
| Auckland   | NULL       |
| Aachen     | 52066      |
| Auckland   | NULL       |
+------------+------------+
6 rows in set (0.00 sec)

GROUP BY 语句

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
-- # 查看数据结构
mysql> select * from payments limit 5;
+----------------+-------------+-------------+----------+
| customerNumber | checkNumber | paymentDate | amount   |
+----------------+-------------+-------------+----------+
|            103 | HQ336336    | 2014-10-19  |  6066.78 |
|            103 | JM555205    | 2013-06-05  | 14571.44 |
|            103 | OM314933    | 2014-12-18  |  1676.14 |
|            112 | BO864823    | 2014-12-17  | 14191.12 |
|            112 | HQ55022     | 2013-06-06  | 32641.98 |
+----------------+-------------+-------------+----------+
5 rows in set (0.00 sec)
-- # 统计顾客购买次数、花费金额
SELECT customerNumber, count(*),SUM(amount),avg(amount)
FROM payments
WHERE customerNumber < 150
GROUP BY customerNumber;
--#result
mysql> SELECT customerNumber, count(*),SUM(amount),avg(amount)
    -> FROM payments
    -> WHERE customerNumber < 150
    -> GROUP BY customerNumber;
+----------------+----------+-------------+--------------+
| customerNumber | count(*) | SUM(amount) | avg(amount)  |
+----------------+----------+-------------+--------------+
|            103 |        3 |    22314.36 |  7438.120000 |
|            112 |        3 |    80180.98 | 26726.993333 |
|            114 |        4 |   180585.07 | 45146.267500 |
|            119 |        3 |   116949.68 | 38983.226667 |
|            121 |        4 |   104224.79 | 26056.197500 |
|            124 |        9 |   584188.24 | 64909.804444 |
|            128 |        4 |    75937.76 | 18984.440000 |
|            129 |        3 |    66710.56 | 22236.853333 |
|            131 |        3 |   107639.94 | 35879.980000 |
|            141 |       13 |   715738.98 | 55056.844615 |
|            144 |        2 |    43680.65 | 21840.325000 |
|            145 |        4 |   107446.50 | 26861.625000 |
|            146 |        3 |   130305.35 | 43435.116667 |
|            148 |        4 |   156251.03 | 39062.757500 |
+----------------+----------+-------------+--------------+
14 rows in set (0.00 sec)

JOIN

使用 MySQL 的 JOIN 在两个或多个表中查询数据。可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

图片来自菜鸟教程

INNER JOIN

--# eg1.选择在纽约（NYC）的顾客消费信息，以下inner join与where等价
--# join写法
SELECT a.customerNumber,a.customerName,a.city,b.amount
FROM customers a inner join payments b
on a.city = 'NYC' and a.customerNumber=b.customerNumber;
--# where写法
SELECT a.customerNumber,a.customerName,a.city,b.amount
FROM customers a ,payments b
where a.city = 'NYC' and a.customerNumber=b.customerNumber;
--# result
mysql> SELECT a.customerNumber,a.customerName,a.city,b.amount
    -> FROM customers a ,payments b
    -> where a.city = 'NYC' and a.customerNumber=b.customerNumber;
+----------------+----------------------+------+----------+
| customerNumber | customerName         | city | amount   |
+----------------+----------------------+------+----------+
|            131 | Land of Toys Inc.    | NYC  | 22292.62 |
|            131 | Land of Toys Inc.    | NYC  | 50025.35 |
|            131 | Land of Toys Inc.    | NYC  | 35321.97 |
|            151 | Muscle Machine Inc   | NYC  | 58793.53 |
|            151 | Muscle Machine Inc   | NYC  | 20314.44 |
|            151 | Muscle Machine Inc   | NYC  | 58841.35 |
|            151 | Muscle Machine Inc   | NYC  | 39964.63 |
|            181 | Vitachrome Inc.      | NYC  | 22602.36 |
|            181 | Vitachrome Inc.      | NYC  |  5494.78 |
|            181 | Vitachrome Inc.      | NYC  | 44400.50 |
|            424 | Classic Legends Inc. | NYC  | 25505.98 |
|            424 | Classic Legends Inc. | NYC  | 21665.98 |
|            424 | Classic Legends Inc. | NYC  | 22042.37 |
|            456 | Microscale Inc.      | NYC  | 27550.51 |
|            456 | Microscale Inc.      | NYC  |  1679.92 |
+----------------+----------------------+------+----------+
15 rows in set (0.00 sec)

left join and right join

SELECT a.customerNumber,a.customerName,a.city,b.amount
FROM customers a left join payments b
on a.city = 'NYC' and a.customerNumber=b.customerNumber
limit 10;
--# eg1.左连接选择在纽约（NYC）的顾客消费信息，可见顾客city并不全是NYC，仅有NYC城市的才会出现amount，参考左连接特性
mysql> SELECT a.customerNumber,a.customerName,a.city,b.amount
    -> FROM customers a left join payments b
    -> on a.city = 'NYC' and a.customerNumber=b.customerNumber
    -> limit 10;
+----------------+------------------------------+---------------+----------+
| customerNumber | customerName                 | city          | amount   |
+----------------+------------------------------+---------------+----------+
|            103 | Atelier graphique            | Nantes        |     NULL |
|            112 | Signal Gift Stores           | Las Vegas     |     NULL |
|            114 | Australian Collectors, Co.   | Melbourne     |     NULL |
|            121 | Baane Mini Imports           | Stavern       |     NULL |
|            124 | Mini Gifts Distributors Ltd. | San Rafael    |     NULL |
|            125 | Havel & Zbyszek Co           | Warszawa      |     NULL |
|            128 | Blauer See Auto, Co.         | Frankfurt     |     NULL |
|            129 | Mini Wheels Co.              | San Francisco |     NULL |
|            131 | Land of Toys Inc.            | NYC           | 22292.62 |
|            131 | Land of Toys Inc.            | NYC           | 50025.35 |
+----------------+------------------------------+---------------+----------+
10 rows in set (0.00 sec)

右连接类似。

NULL 值处理

通过 IS NULL 与 IS NOT NULL 判断是否空值

不可使用 = 运算符比较 NULL （NULL=NULL返回false）

mysql> select officecode,phone,state from offices;
+------------+------------------+-------+
| officecode | phone            | state |
+------------+------------------+-------+
| 1          | +1 650 219 4782  | CA    |
| 2          | +1 215 837 0825  | MA    |
| 3          | +1 212 555 3000  | NY    |
| 4          | +33 14 723 4404  | NULL  |
| 5          | +86 33 224 5000  | BJ    |
| 6          | +61 2 9264 2451  | NULL  |
| 7          | +44 20 7877 2041 | NULL  |
+------------+------------------+-------+
7 rows in set (0.00 sec)
mysql> select phone,state from offices where state is NULL;
+------------------+-------+
| phone            | state |
+------------------+-------+
| +33 14 723 4404  | NULL  |
| +61 2 9264 2451  | NULL  |
| +44 20 7877 2041 | NULL  |
+------------------+-------+
3 rows in set (0.00 sec)
mysql> select phone,state from offices where state is not NULL;
+-----------------+-------+
| phone           | state |
+-----------------+-------+
| +1 650 219 4782 | CA    |
| +1 215 837 0825 | MA    |
| +1 212 555 3000 | NY    |
| +86 33 224 5000 | BJ    |
+-----------------+-------+
4 rows in set (0.00 sec)

正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

--# 匹配HQ开头的订单号
mysql> select * from payments where checkNumber regexp '^HQ' limit 5;
+----------------+-------------+-------------+----------+
| customerNumber | checkNumber | paymentDate | amount   |
+----------------+-------------+-------------+----------+
|            103 | HQ336336    | 2014-10-19  |  6066.78 |
|            112 | HQ55022     | 2013-06-06  | 32641.98 |
|            198 | HQ920205    | 2013-07-06  |  6036.96 |
+----------------+-------------+-------------+----------+
3 rows in set (0.00 sec)

ALTER命令

修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。
参考：https://www.yiibai.com/mysql/alter-table.html ， http://www.runoob.com/mysql/mysql-alter.html

Leetcode 练习

176. Second Highest Salary

返回第二大的数

Write a SQL query to get the second highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

解释：

SELECT DISTINCT：DISTINCT关键字用于选择不重复的值
select as：给查询对像起个别名
ORDER BY：返回排序结果，DESC表示降序排列
LIMIT OFFSET：限定数量与偏移量