LionKing数据科学专栏

购买普通会员高级会员可以解锁网站精华内容且享受VIP服务的优惠

想要查看更多数据科学相关的内容请关注我们的微信公众号知乎专栏

SQL

SQL简介

SQL的全称为结构化查询语言(Structured Query Language),是用于关系数据库(relational database)管理的编程语言。

SQL中的数据库可以看作一个表格,其中每一行是一个记录(record),每一列为数据的一个属性。例如如下表格:

name age country
'Leo' 18 'United States'
'Diana' 7 'United States'
'Shango' 9 'United Kingdom'
'Jessica' 25 'China'

SQL与数据库的交互通过语句(clause),每一条语句都会有不同的作用,其中最主要的功能如下:

主流的操作系统都预装了SQL的工具,或不难安装。SQL有不同的类型,其语法略有区别。这里介绍MySQL的语法。

MySQL基本语法

创建数据库

可以通过

SHOW DATABASES;
	

查看现有数据库名称。若需要创造一个新的数据库,名字为my_db,可以使用如下命令:

CREATE DATABASE IF NOT EXISTS my_db;
	

该语句会在my_db数据库不存在时创造该数据库,否则不进行操作并输出警告。

使用如下命令可以切换至my_db数据库:

USE my_db;
	

创建表格

创建表格时需要给出表格的每一列的名称和类型,常见的类型有:

类型 含义
CHAR(size) 固定长度为size的字符串,size不能超过255,适合长度相对固定或需要频繁更新的列
VARCHAR(size) 可变长度不超过size的字符串,size不能超过255,适合长度变化且不需要频繁更新的列
TEXT 长度不超过$2^{16} - 1$的字符串
INT $-2^{31}$到$2^{31} - 1$之间的整数,同C/C++中的int范围
INT UNSIGNED 0到$2^{32} - 1$之间的整数,同C/C++中unsigned int范围
DOUBLE 浮点数
DATE 格式为YYYY-MM-DD的日期
DATETIME 格式为YYYY-MM-DD HH:MM:SS的日期和时间组合
YEAR(4) 格式为YYYY的年份

创建表格的语法为

CREATE TABLE 表格名 (
    列名1 类型1,
    列名2 类型2,
    ...,
    列名k 类型k
);
	

INSERT语句

使用INSERT语句可以对已有数据库插入记录,语法如下:

INSERT INTO 表格名 (列名1, 列名2, ..., 列名k) VALUES
  (值1, 值2, ..., 值k), (值1, 值2, ..., 值k), ...
	

UPDATE语句

使用UPDATE语句可以对已有记录进行更新,语法如下:

UPDATE 表格名 SET 列名1 = 值1, 列名2 = 值2, ..., 列名k = 值k WHERE 筛选条件
	

该语句可以将所有满足筛选条件的记录的列名1, 列名2, ..., 列名k分别改为值1, 值2, ..., 值k。

筛选条件的语法将在下文WHERE语句中讲解。

DELETE语句

使用DELETE语句可以对表格中的记录进行删除,语法如下:

DELETE FROM 表格名 WHERE 筛选条件
	

可以从表格中删除满足筛选条件的记录。

SELECT语句

基本语法

SELECT是SQL中最常用的语句,其效果是从表格中选取全部/部分列,往往结合WHERE使用。本节只介绍基本的SELECT语。

SELECT有以下用法:

  1. SELECT * FROM 表格名
    	  

    可以选取整个表格的所有列,往往用来查看数据。

  2. SELECT 列名1, 列名2, ..., 列名k FROM 表格名
    	  

    可以选取表格中的一列($k = 1$)或多列($k \gt 1$)

  3. SELECT 函数1(列名1), 函数2(列名2), ..., 函数k(列名k) FROM 表格名
    	  

    可以选取表格中的列并且作函数的变换,一般配合下文将要介绍的GROUP BY一起使用。

    常见的函数有:

    AVG 平均值
    COUNT 个数
    FIRST 第一个
    LAST 最后一个
    MAX 最大值
    MIN 最小值
    SUM 总和
    UCASE 转换成大写
    LCASE 转换成小写
    LENGTH 字符串长度
    ROUND(x, k) 对x舍入k位

WHERE语句

有时候我们需要筛选出关键性的信息,如我们只需要年龄不超过20岁的人的名单,或只要国家为亚洲国家的人的名单。WHERE语句可以进行此类筛选。

WHERE语句的一般语法是在一个SELECT语句的末尾加上一定筛选条件

SELECT 列 FROM 表格名 WHERE 筛选条件
	

其中筛选条件可以是单个条件,也可以是多个条件的复合逻辑。

单个条件的语法为

列名 运算符 右值
	

多个条件的语法为

单个条件 AND/OR 单个条件 AND/OR 单个条件 ...
	

运算符有

运算符 含义 例子 输出
= 列的值等于右值 SELECT * FROM Person WHERE country = 'United States' 选择国家为'United States'的记录
<> 列的值不等于右值 SELECT * FROM Person WHERE country <> 'United States' 选择国家不为'United States'的记录
>(=) 列的值大于(等于)右值 SELECT * FROM Person WHERE age > 10 选择年龄大于10的记录
<(=) 列的值小于(等于)右值 SELECT * FROM Person WHERE age < 10 选择年龄小于10的记录
BETWEEN low AND high 列的值在[low, high]区间内 SELECT * FROM Person WHERE age BETWEEN 5 AND 10 选择年龄在5到10之间的记录
LIKE '模糊信息' 列的值可以模糊匹配'模糊信息','%'可以匹配任意字符串(包括空), '_'可以匹配单个字符 SELECT * FROM Person WHERE country LIKE 'United %' OR age < 5 选择国家以'United'开头或年龄小于5的记录
IN (值1, 值2, ..., 值k) 列的值属于值1、值2、...、值k中的一种 SELECT * FROM Person WHERE country IN ('United States', 'China') 选择国家为'United States'或'China'的记录

ORDER BY

在WHERE语句之后加入ORDER BY 列名可以对WHERE语句本身的输出结果关于该列名排序,默认排序由小到大,可以通过DESC后缀改为由大到小。语法如下:

SELECT 列 FROM 表格名 WHERE 筛选条件 ORDER BY 列名1 (DESC), 列名2 (DESC), ..., 列名k (DESC)
	

可以将WHERE语句得到的表格优先关于列名1(降序)排列, 其次关于列名2(降序)排列, ..., 最后关于列名k(降序)排列。

LIMIT

在一个语句之后加入LIMIT k可以只选择前k格记录。

GROUP BY

GROUP BY可以对结果进行一些分组和聚合(aggregation)。语法如下:

SELECT 函数1(列名1), 函数2(列名2), ..., 函数k(列名k) FROM 表格名 WHERE 筛选条件 GROUP BY 列名k, 列名k + 1, ...
	

可以按照列名k, 列名k + 1, ...分组,对于每组计算列名1的函数1、列名2的函数2、...、列名k的函数k。

若对于分组后的结果需要进一步进行过滤,可以加上HAVING语句和筛选条件,注意WHERE无法与聚合函数共同使用:

SELECT 函数(列名) FROM 表格名 WHERE 筛选条件 GROUP BY 列名 HAVING 筛选条件
	

JOIN

JOIN语句可以把多个表格结合起来。语法如下:

SELECT 表格1.列名1, ..., 表格1.列名k, 表格2.列名1, ..., 表格2.列名l FROM 表格1 INNER/LEFT/RIGHT JOIN 表格2 ON 表格1.列名1 = 表格2.列名1
	

可以将表格1中列名1和表格2中列名1相同的记录结合在一起,并且返回它们在表格1中的列名1, ..., 列名k的值和表格2中列名1, ..., 列名l的值。

JOIN的类型为:

使用JOIN语句有时语法会较为复杂,还可以考虑使用效率更低但是语法更简单的如下语法:

SELECT 表格1.列名1, ..., 表格1.列名k, 表格2.列名1, ..., 表格2.列名l FROM 表格1, 表格2 WHERE 表格1.列名1 = 表格2.列名1
        

该语法本质上首先将两个表格的记录两两合并,然后从$n_1 \times n_2$条记录中筛选满足表格1的列名1与表格2的列名1相等的记录,因此需要更长的时间,只适用两个表格不都很大的情况。

UNION

使用UNION语句可以合并多个SELECT语句的结果。语法如下:

SELECT 列名1, ..., 列名k FROM 表格1 UNION SELECT 列名1, ..., 列名k FROM 表格2
        

可以将两个表格的列名1, ..., 列名k的值合并起来并去重。若不需要去重则将UNION替换为UNION ALL。

SELECT 列名1, ..., 列名k FROM 表格1 UNION ALL SELECT 列名1, ..., 列名k FROM 表格2
        

NESTED

SELECT语句中的表格名可以替换成一整个另外的合法SQL语句,效果相当于将该SQL语句的结果存成一个表格,并且对该表格进行查询。这样的语句成为嵌套(nested)语句。为了方便起见,可以将该结果对应的表格进行命名(alias)。语法如下:

SELECT 新名字.列名1, ..., 新名字.列名k FROM (SELECT 语句) AS 新名字 ...
        

可以将括号中的SELECT语句的结果看作新的表格,并对该表格进行查询。

最后,我们将所有代码合并在一起方便复习:

# 若数据库my_db不存在,创建之
CREATE DATABASE IF NOT EXISTS my_db;
# 转到数据库my_db
USE my_db;

# 若表格已存在,则删除
DROP TABLE IF EXISTS Person;

# 创建表格
CREATE TABLE Person (
    name VARCHAR(150),
    age INT UNSIGNED,
    country VARCHAR(150)
);

# 插入记录
INSERT INTO Person (name, age, country) VALUES
  ('Leo', 18, 'United States'),
  ('Diana', 7, 'United States'),
  ('Shango', 9, 'United Kingdom'),
  ('Jessica', 25, 'China');

# SELECT * FROM 表格名
SELECT * FROM Person;

SELECT name, age FROM Person;

SELECT MAX(name), MIN(age), COUNT(DISTINCT(country)) FROM Person;

SELECT DISTINCT country FROM Person;

SELECT * FROM Person WHERE country = 'United States';

SELECT * FROM Person WHERE country <> 'United States';

SELECT * FROM Person WHERE age > 10;

SELECT * FROM Person WHERE age < 10;

SELECT * FROM Person WHERE age BETWEEN 7 AND 9;

SELECT * FROM Person WHERE country LIKE 'United %';

# UPDATE Person SET age=10 WHERE country='United States';
# DELETE FROM Person WHERE age < 10;

SELECT * FROM Person WHERE age > 10 ORDER BY age;

SELECT * FROM Person WHERE age > 10 ORDER BY age DESC;

SELECT * FROM Person ORDER BY country, age DESC;

SELECT * FROM Person LIMIT 2;

SELECT country, SUM(age) FROM Person GROUP BY country;

SELECT country, SUM(age) AS sum_age FROM Person GROUP BY country HAVING sum_age > 10;

# 创建名字对应的城市的表格
DROP TABLE IF EXISTS City;
CREATE TABLE City (
    name VARCHAR(150),
    city VARCHAR(150)
);
INSERT INTO City (name, city) VALUES
  ('Leo', 'Boston'),
  ('Diana', 'Chicago'),
  ('Jessica', 'Shanghai'),
  ('Mog', 'California');
SELECT * FROM City;

# 将名字在两个表格中同时出现的记录结合
SELECT Person.name AS name, Person.age AS age, Person.country AS country, City.city AS city FROM Person INNER JOIN City ON Person.name = City.name;

# 保留所有名字出现在Person表格的记录
SELECT Person.name AS name, Person.age AS age, Person.country AS country, City.city AS city FROM Person LEFT JOIN City ON Person.name = City.name;

# 保留所有名字出现在City表格的记录
SELECT Person.name AS name, Person.age AS age, Person.country AS country, City.city AS city FROM Person RIGHT JOIN City ON Person.name = City.name;

# 效率更低但语法更简单的写法
SELECT Person.name AS name, Person.age AS age, Person.country AS country, City.city AS city FROM Person, City WHERE Person.name = City.name; 

# 合并两个表格的名字
SELECT name FROM Person UNION SELECT name FROM City;
SELECT name FROM Person UNION ALL SELECT name FROM City;

# 关于name结合两个表格,只保留country为'United '开头的
SELECT A.name AS name, A.age AS age, A.country AS country, City.city AS city FROM (SELECT * FROM Person WHERE country LIKE 'United %') AS A INNER JOIN City ON A.name = City.name;
        

更多SQL相关问题见本网站论坛SQL版面

更多面试问题见面试真题汇总

想要查看更多数据科学相关的内容请关注我们的微信公众号知乎专栏