Note for SoloLearn SQL

本文主要关于SQL的基本语法的笔记，来自SoloLearn。

基本概念

介绍数据库

数据库

数据库是指以利于容易地连接、有效管理和更新的方式来管理的一系列的数据。数据库由储存相关联的信息的表格组成，举例来说，假设我们需要建立一个如YouTube的网站，那么我们需要数据库来存储视频信息、用户名与密码以及评论等等。

数据库表格

数据库表格用类似Excel的方式存储和展示数据，数据库通常由许多的表格构成，例如想象一个由名字和电话号码构成的表格。

primary key

primary key是表格中特殊的一列，主要特性是

  • 每行都有独特的primary key
  • 不能为null

例如下图中，ID是个primary key的好选择，因为可能会有重名的情况。

什么是SQL

我们已经理解了什么是数据库，那么理解什么是SQL就很简单了。SQL 全称是结构化序列语言（Structured Query Language）。SQL用于连接和操作数据库，而MySQL指的是一种能理解SQL的程序语言。可以这么说，SQL是一种标准，而有许多遵循这个版本却自带许多特性的程序语言的实现。

SQL可以：

  • 插入、更新、删除数据库里的记录
  • 创建新的数据库、表格、存储程序和外观
  • 从数据库取回数据

SQL语句 SELECT

基本SQL语句

SHOW DATABASES：返回服务器上所有数据库的序列

SHOW TABLES：返回当前数据库上的所有表格

SHOW COLUMNS FROM customers：返回选定表格内的列信息

SELECT 语句

1
2
SELECT column_list
FROM table_name

在制定表格内选中一列或者多列

语法规则

多行语句

SQL允许同时运行多句语句

1
2
SELECT FirstName FROM customers;
SELECT City FROM customers;

大小写不敏感

SQL是大小写不敏感的，如下三行是同样的效果

1
2
3
select City from customers;
SELECT City FROM customers;
sElEct City From customers;

忽略空白

whitespace和多行被忽略，以下语句是ok的

1
2
3
4
SELECT        City


FROM customers;

选择多列

选择多列

以逗号分隔，可以同时选择多列

1
2
SELECT FirstName, LastName, City
FROM customers;

选择所有列

SELECT * FROM customers;

DISTINCT和LIMIT

DISTINCT关键词

DISTINCT关键词指排除重复项

1
2
SELECT DISTINCT column_name1, column_name2
FROM table_name;

LIMIT关键词

指定返回的子集大小

1
2
3
SELECT column list
FROM table_name
LIMIT [number of records];

如选择前五项

1
2
SELECT ID, FirstName, LastName, City
FROM customers LIMIT 5;

选择从3后面的4项

1
2
SELECT ID, FirstName, LastName, City
 FROM customers LIMIT 3, 4;

排序

全名

当要操作拥有相同列名字的多个表格时，可以使用全名如

1
2
3
SELECT City FROM customers;

SELECT customers.City FROM customers;

Order By

Order By用于与SELECT来排序数据
用 Firstname排序

1
2
SELECT * FROM customers
ORDER BY FirstName;

多列排序

1
2
SELECT * FROM customers
ORDER BY LastName, Age;

先用LastName排序，在LastName相同的情况下，用Age排序

Filtering, Functions, Subqueries

WHERE语句

WHERE语句

WHERE语句类似一个过滤器的作用

1
2
3
SELECT column_list
FROM table_name
WHERE condition;

SQL操作符

比较操作符和逻辑操作符用于WHERE语句内

BETWEEN 操作符

用于选中一定范围内的值

1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Text 值

需要用单引号

1
2
3
SELECT ID, FirstName, LastName, City
FROM customers
WHERE City = 'New York';

AND OR逻辑操作符

逻辑操作符用于操作两个布尔值，返回true、false、或者null

1
2
3
SELECT ID, FirstName, LastName, Age
FROM customers
WHERE Age >= 30 AND Age <= 40;
1
2
SELECT * FROM customers
WHERE City = 'New York' OR City = 'Chicago';

结合AND & OR

1
2
3
SELECT * FROM customers
WHERE City = 'New York'
AND (Age=30 OR Age=35);

IN NOT IN

IN操作符用于需要将一列与许多值比较的情况，如用OR语句

1
2
3
4
SELECT * FROM customers
WHERE City = 'New York'
OR City = 'Los Angeles'
OR City = 'Chicago';

用IN语句替代可简写为

1
2
SELECT * FROM customers
WHERE City IN ('New York', 'Los Angeles', 'Chicago');

NOT IN就是反面情况

1
2
SELECT * FROM customers
WHERE City NOT IN ('New York', 'Los Angeles', 'Chicago');

Custom 列

CONCAT语句

CONCAT语句用于联合多个Text值，返回字符串

SELECT CONCAT(FirstName, ', ' , City) FROM customers;

AS 操作符

AS 操作符用于生成新的列

1
2
SELECT CONCAT(FirstName,', ', City) AS new_column
FROM customers;

算数操作符

      • /

如将Salary自增500

1
2
SELECT ID, FirstName, LastName, Salary+500 AS Salary
FROM employees;

函数

UPPER LOWER函数

用于转换为大小写格式

1
2
SELECT FirstName, UPPER(LastName) AS LastName
FROM employees;

SQRT 、 AVG

SQRT返回平方根

1
2
SELECT Salary, SQRT(Salary)
FROM employees;

AVG返回均值

1
SELECT AVG(Salary) FROM employees;

SUM函数

返回列的加和

1
SELECT SUM(Salary) FROM employees;

子语句

子语句是指含有其他语句的语句，假设我们需要选中全部薪水大于均值的人，那么可能需要先获取均值

SELECT AVG(Salary) FROM employees;

再

1
2
3
SELECT FirstName, Salary FROM employees
WHERE  Salary > 3100
ORDER BY Salary DESC;

其实可以结合起来

1
2
3
SELECT FirstName, Salary FROM employees
WHERE  Salary > (SELECT AVG(Salary) FROM employees)
ORDER BY Salary DESC;

LIKE和MIN

LIKE函数

LIKE用于在WHERE里指定条件

1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

例如，选择所有A开头的人

1
2
SELECT * FROM employees
WHERE FirstName LIKE 'A%';

选择所有s结尾名字的人

1
2
SELECT * FROM employees
WHERE LastName LIKE '%s';

MIN函数

用于选择最小值

SELECT MIN(Salary) AS Salary FROM employees;

JOIN、表操作

合并表格

对于以下两个表格

可用以下语句结合

1
2
3
4
SELECT customers.ID, customers.Name, orders.Name, orders.Amount
FROM customers, orders
WHERE customers.ID=orders.Customer_ID
ORDER BY customers.ID;

结果

Join的类型

可以用“小名”来简化join操作

1
2
3
4
SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID=ord.Customer_ID
ORDER BY ct.ID;

JOIN类型

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN

INNER JOIN等同于JOIN

1
2
3
SELECT column_name(s)
FROM table1 INNER JOIN table2
ON table1.column_name=table2.column_name;

关系如图

LEFT JOIN 返回所有左边表格的行，甚至是在右边表格没有符合的也是如此。

基本语法为

1
2
3
SELECT table1.column1, table2.column2...
FROM table1 LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

举例来说，考虑以下两个依次是customers和items，

1
2
3
SELECT customers.Name, items.Name
FROM customers LEFT OUTER JOIN items
ON customers.ID=items.Seller_id;

结果

如果用RIGHT JOIN

1
2
3
SELECT table1.column1, table2.column2...
FROM table1 RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

结果是

UNION

当需要合并相似的表格时，可以用到UNION and UNION ALL，两者主要区别是前者会丢弃重复项。

如考虑以下两个表格

使用语句

1
2
3
SELECT ID, FirstName, LastName, City FROM First
UNION
SELECT ID, FirstName, LastName, City FROM Second;

结果为

而使用UNION ALL

1
2
3
SELECT ID, FirstName, LastName, City FROM First
UNION ALL
SELECT ID, FirstName, LastName, City FROM Second;

结果为

INSERT语句

插入语句

INSERT语句用于在表格插入一条数据

1
2
INSERT INTO table_name
VALUES (value1, value2, value3,...);

也可以指定插入列

1
2
INSERT INTO table_name (column1, column2, column3, ...,columnN)  
VALUES (value1, value2, value3,...valueN);

亦可只插入指定列

1
2
INSERT INTO table_name (column1, column2, column3, ...,columnN)  
VALUES (value1, value2, value3,...valueN);

UPDATE、DELETE语句

UPDATE语句用于更改表格

1
2
3
UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE condition;

例如

1
2
3
UPDATE Employees
SET Salary=5000
WHERE ID=1;

也可以同时更改数项

1
2
3
UPDATE Employees
SET Salary=5000, FirstName='Robert'
WHERE ID=1;

DELETE用于删除数据

1
2
DELETE FROM table_name
WHERE condition;

创建表格

CREATE TABLE 用于创建表格

基本语法

1
2
3
4
5
6
7
8
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
columnN data_type(size)
);
  • column_names指定列名
  • data_type指定存储数据的类型如int
  • size指定最大长度

如

1
2
3
4
5
6
7
CREATE TABLE Users
(
   UserID int,
   FirstName varchar(100),
   LastName varchar(100),
   City varchar(100)
);

varchar指的是字符类型

数据类型

数值
INT - 带符号或者非符号integer
FLOAT(M,D) - 带符号浮点数M是展示长度，D是小数点位
DOUBLE(M,D) - 带符号long类型浮点数M是展示长度，D是小数点位

日期和时间
DATE - YYYY-MM-DD 格式
DATETIME - YYYY-MM-DD HH:MM:SS 格式
TIMESTAMP - 从 1970年1月1日午夜开始计算的时间长度
TIME - Stores the time in HH:MM:SS format.

字符串
CHAR(M) - 固定长度的字符串，M是长度，最大为255 byte.
VARCHAR(M) - 可变长度字符串，M是最大长度
BLOB - “二进制大型对象” Binary Large Objects， 用于存储图片等
TEXT - 大规模的text数据

指定primary key

1
2
3
4
5
6
7
8
CREATE TABLE Users
(
   UserID int,
   FirstName varchar(100),
   LastName varchar(100),
   City varchar(100),
   PRIMARY KEY(UserID)
);

NOT NULL 和 AUTO_INCREMENT

SQL约束

NOT NULL - 强制列不能含有NULL
UNIQUE - 不允许列中出现重复值
PRIMARY KEY - 强制表格对于特定的列接受数据并创建单独的加速索引
CHECK - 通过逻辑表达式来判断值是否有效
DEFAULT - 往表格插入数据时，若某列未指定值，则自动插入默认值

例如，如下语句指name列不允许出现NULL

name varchar(100) NOT NULL

AUTO INCREMENT

当新一条记录加入时，自动生成某数，通常从1开始，每次新纪录加入，自增1。

1
2
UserID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (UserID)

ALTER DROP RANAME

ALTER用于对表格中的列进行添加、删除和编辑操作，也可以对表格的约束进行添加或移除。

ALTER TABLE People ADD DateOfBirth date;

DROP用于删除整列

1
2
ALTER TABLE People
DROP COLUMN DateOfBirth;

要删除整个表格

DROP TABLE People;

RENAME用于列改名或者对表格改名

将People的FirstName列改名为name

1
2
ALTER TABLE People
CHANGE FirstName name varchar(100);

将整个表格改名

RENAME TABLE People TO Users;

视图

为了更加方便的组织和操作数据库，经常需要使用视图。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

创建语句如

1
2
3
4
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

假设一表格，如

我们要创建一个视图来展示职工的名字和薪水

1
2
3
CREATE VIEW List AS
SELECT FirstName, Salary
FROM  Employees;

接下来可以类似展示表格一样展示视图

SELECT * FROM List;

可以用以下语句来更新视图

1
2
3
4
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

例如

1
2
3
CREATE OR REPLACE VIEW List AS
SELECT FirstName, LastName, Salary
FROM  Employees;

丢弃视图
DROP VIEW List;