本科毕业后再也没用过数据库，最近在分析gtfs数据(导入到MySQL)，复习下SQL语法知识。

1. 基本语法

1.1 主键和外键

(1)主键PRIMARY KEY

定义在表的一列或多个列上。主键的值唯一并且不能是空值，每个表只能有一个主键，主键列中的值不允许修改或更新，主键值不能重用

-- 主键只有一个域
CREATE TABLE Persons
(
    P_Id int NOT NULL PRIMARY KEY,
)

-- 主键包含多个域
CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

(2)外键FOREIGN KEY

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    PRIMARY KEY (O_Id),
    FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

外键的功能如下：

• The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
• The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

1.2 数据类型

SQL General Data Types
MySQL Data Types
这里更直观：SQL Data Types for Various DBs：MySQL Data Types

1.3 自定义变量

-- 变量定义
SET @var_name = expr [, @var_name = expr] ...
set @station='Arènes';

-- 使用时就用变量@var_name，举例如下： 
SELECT stop_id,stop_name,parent_station FROM stops where stop_name=@station;

详情可查看官方文档User-Defined Variables。

1.4 基本语法

• ;分号用于分隔每行SQL语句
• 注释：#注释一行；-- (至少需跟一个空格)注释一行；/**/可注释多行
• SQL对大小写不敏感，不过，通常关键字全部用大写

SQL 中最重要的 DDL 语句:

• CREATE DATABASE – 创建新数据库
• ALTER DATABASE – 修改数据库
• CREATE TABLE – 创建新表
• ALTER TABLE – 变更（改变）数据库表
• DROP TABLE – 删除表
• CREATE INDEX – 创建索引（搜索键）
• DROP INDEX – 删除索引

2. 操作数据库

2.1 运行SQL语句

安装好MySQL，用如下命令进入MySQL命令行，

mysql -u user_name -p[password] -D db_name 
mysql -u root -pJelline -D gtfs  # -p后面直接接passwd，没有空格 

我习惯将SQL语句写在一个文件，再写个简单的shell脚本。如下：

#! /usr/bin/env bash
cat load.sql | mysql -pJelline -u root  #Jelline是密码

2.2 创建/删除数据库

-- 创建数据库
CREATE DATABASE gtfs   -- CREATE DATABASE IF NOT EXISTS gtfs;
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE utf8_general_ci;
    
-- 删除数据库
DROP DATABASE gtfs; -- DROP DATABASE IF EXISTS gtfs;

2.3 创建DBMS用户

-- 创建DBMS用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
--授权  * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'; 
FLUSH PRIVILEGES;

3. 表格操作

3.1 创建表格

-- 创建表
DROP TABLE IF EXISTS agency;
-- agency_id,agency_name,agency_url,agency_timezone,agency_phone,agency_lang
CREATE TABLE `agency` (
    agency_id VARCHAR(255) NOT NULL PRIMARY KEY,
    agency_name VARCHAR(255),
    agency_url VARCHAR(255),
    agency_timezone VARCHAR(50),
    agency_phone VARCHAR(255),
    agency_lang VARCHAR(50)
);

3.2 查看表格信息

查看表格信息（域、数据类型等），关于SHOW COLUMNS用法可以查看官方文档这里。

DESCRIBE table_name;
-- 或者
SHOW COLUMNS FROM table_name;

-- SHOW COLUMNS完整语法
SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

举例如下：

mysql> SHOW COLUMNS FROM stop_times;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| trip_id             | varchar(255) | YES  | MUL | NULL    |       |
| stop_id             | varchar(255) | YES  | MUL | NULL    |       |
| stop_sequence       | varchar(255) | YES  | MUL | NULL    |       |
| arrival_time        | varchar(8)   | YES  |     | NULL    |       |
| departure_time      | varchar(8)   | YES  |     | NULL    |       |
| stop_headsign       | varchar(8)   | YES  |     | NULL    |       |
| pickup_type         | int(2)       | YES  | MUL | NULL    |       |
| drop_off_type       | int(2)       | YES  | MUL | NULL    |       |
| shape_dist_traveled | varchar(8)   | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+
9 rows in set (0,02 sec)

3.3 导入外部数据

-- 导入数据
LOAD DATA LOCAL INFILE 'agency.txt' INTO TABLE agency FIELDS TERMINATED BY ',' IGNORE 1 LINES;

4. 查询操作

4.1 SELECT

SELECT完整语法见官方文档SELECT Syntax，也太复杂了。常用的如下：

SELECT select_expr 
FROM table_references 
WHERE where_condition
ORDER BY {col_name | expr | position}
LIMIT row_count
INTO OUTFILE 'file_name'

where_condition可以用操作符有：=, <>, >, <, >=, <=, BETWEEN, LIKE, IN。详情见w3school的Operators in The WHERE Clause。除此之外，多个条件可以用AND, OR连接。举例如下：

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München'); 

SELECT * FROM Customers
WHERE City IN ('Paris','London'); 

SELECT * FROM Customers
WHERE Country NOT LIKE '%land%'; 

4.2 JOIN

JOIN用于从多个表中查找结果(SQL joins are used to combine rows from two or more tables)。用文氏图表示如下(图片来源于w3schools)，INNER JOIN等同于，相当于求交集，这点很有用。(PS：其实LEFT/RIGHT JOIN画得并不贴切)

    

从w3schools摘抄部分内容如下：

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

举例如下：

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName; 

-- Note: The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders). 

4.3 UNION

待续

5. 学习资料

• W3School：SQL教程，SQL Tutorial
• 书籍：SQL必知必会
• 练习题：SQL练习50题

6. 一点心得

在做多个表联合查询时，用JOIN语句，在ON语句写上一个表的外键等于另一个表的主键，这样就将所有的表连在一起了，而在再往上添加条件。以下是我写的一条查询语句(估计你也不感兴趣这条语句的功能，就不解释了)：

set @line='T2';
(SELECT DISTINCT stops.stop_name, routes.route_short_name, 'D' AS 'D/A', stop_times.departure_time AS 'time'
FROM stops
JOIN stop_times ON stops.stop_id=stop_times.stop_id AND stop_times.stop_sequence='0' AND stops.parent_station=@parent_station
JOIN trips ON stop_times.trip_id=trips.trip_id 
JOIN routes ON routes.route_id=trips.route_id AND routes.route_short_name=@line
JOIN calendar ON trips.service_id=calendar.service_id AND calendar.monday=1 AND calendar.tuesday=1 AND calendar.wednesday=1 AND calendar.thursday=1 AND calendar.friday=1 -- AND @service_type;
ORDER BY stop_times.departure_time)
UNION
-- arival time
(SELECT DISTINCT stops.stop_name, routes.route_short_name, 'A' AS 'D/A', stop_times.arrival_time AS 'time'
FROM stops
JOIN stop_times ON stops.stop_id=stop_times.stop_id AND stop_times.pickup_type=1 AND stops.parent_station=@parent_station
JOIN trips ON stop_times.trip_id=trips.trip_id 
JOIN routes ON routes.route_id=trips.route_id AND routes.route_short_name=@line
JOIN calendar ON trips.service_id=calendar.service_id AND calendar.monday=1 AND calendar.tuesday=1 AND calendar.wednesday=1 AND calendar.thursday=1 AND calendar.friday=1 -- AND @service_type;
ORDER BY stop_times.departure_time)
ORDER BY time;

参考资料：
[1]How can an SQL query return data from multiple tables

赞赏

微信赞赏支付宝赞赏