【SQL语句】学习笔记

2021-08-29 10:12:21

SQL语法

DML 和 DDL

数据操作语言 (DML)

  • SELECT - 从数据库表中获取数据
  • UPDATE - 更新数据库表中的数据
  • DELETE - 从数据库表中删除数据
  • INSERT INTO - 向数据库表中插入数据

数据定义语言 (DDL)

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

Select

查找

-- 语法
Select 列名 from 表名;
-- 示例
Select * from table;

select distinct

查找并返回唯一的不同值

-- 语法
SELECT DISTINCT 列名称 FROM 表名称

-- 示例
SELECT DISTINCT name FROM Person;

where

加条件

-- 语法
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

-- 示例
SELECT * FROM Persons WHERE City='Beijing'

and和or

and：第一个条件和第二个条件都成立，则 AND 运算符显示一条记录，表示并且
or：第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录，表示或者

-- 语法


-- 示例
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

-- 示例
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

-- 示例
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
AND LastName='Carter'

order by

用于根据指定的列对结果集进行排序，默认按照升序对记录进行排序（升序ASC，降序DESC）

-- 语法


-- 示例
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

insert into

向表格中插入新的行

-- 语法
INSERT INTO 表名称 VALUES (值1, 值2,....)

-- 示例
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

-- 语法
INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)

-- 示例
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

update

用于修改表中的数据

-- 语法
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

-- 示例
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'

delete

用于删除表中的行

-- 语法
DELETE FROM 表名称 WHERE 列名称 = 值

-- 示例
DELETE FROM Person WHERE LastName = 'Wilson' 

-- 删除所有行，保留表的结构、属性和索引
DELETE FROM table_name
DELETE * FROM table_name

limit

用于规定要返回的记录的数目，也是web数据分页的具体实现

-- 语法
SELECT column_name(s) FROM table_name LIMIT number

-- 示例
SELECT * FROM Persons LIMIT 5

-- 分页
SELECT * FROM Persons LIMIT 0,5
-- limit 起始索引 一页显示几条

like

模糊查询

-- 语法
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

-- 示例
SELECT * FROM Persons
WHERE City LIKE 'N%'

通配符

% _ []

-- 示例：从 "Persons" 表中选取居住在以 "Ne" 开始的城市里的人
SELECT * FROM Persons
WHERE City LIKE 'Ne%'

-- 示例：从 "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人
SELECT * FROM Persons
WHERE FirstName LIKE '_eorge'

-- 示例：从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是一个任意字符，然后是 "er"
SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er'

-- 示例：从 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons
WHERE City LIKE '[ALN]%'

-- 示例：从 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'

in

规定多个值

-- 语法
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

-- 示例
SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

between…and

作用是选取介于两个值之间的数据范围

-- 语法
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

-- 示例
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'

-- 示例
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Adams' AND 'Carter'

alias

可以为列名称和表名称指定别名

-- 语法:表的 SQL Alias 语法
SELECT column_name(s)
FROM table_name
AS alias_name

-- 语法:列的 SQL Alias 语法
SELECT column_name AS alias_name
FROM table_name

-- 示例
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'

join

用于根据两个或多个表中的列之间的关系，从这些表中查询数据

join区别：

  • JOIN: 如果表中有至少一个匹配，则返回行
  • LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
  • FULL JOIN: 只要其中一个表中存在匹配，就返回行
-- 语法

-- 示例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

inner join

如果表1中的行在表2中没有匹配，就不会列出这些行
也就是说，以多个表为标准，必须同时满足条件

-- 语法
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name


-- 示例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

left join

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行
也就是说，以左表为标准

-- 语法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name


-- 示例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

right join

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行
也就是说，以右表为标准

-- 语法
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name


-- 示例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行。

full join

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行
不是指定的以左或右为标准，而是以二者为标准

-- 语法
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name


-- 示例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 “Persons” 中的行在表 “Orders” 中没有匹配，或者如果 “Orders” 中的行在表 “Persons” 中没有匹配，这些行同样会列出。

union和union all

用于合并两个或多个 SELECT 语句的结果集
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

区别：
union all是展示所有的结果，union会在union all的基础上去重

-- 语法：union
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

-- 示例
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA




-- 语法：union all
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

-- 示例
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

select into

用于创建表的备份复件，从一个表中选取数据，然后把数据插入另一个表中

-- 语法
SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

-- 示例
SELECT *
INTO Persons_backup
FROM Persons

-- 示例：IN 子句可用于向另一个数据库中拷贝表
SELECT *
INTO Persons IN 'Backup.mdb'
FROM Persons

create database

用于创建数据库

-- 语法
CREATE DATABASE database_name

-- 示例
CREATE DATABASE my_db

create table

用于创建数据库中的表

-- 语法
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

-- 示例
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

约束 (Constraints)

约束用于限制加入表的数据的类型。

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

我们将主要探讨以下几种约束：

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT

not null (约束)

NOT NULL 约束强制列不接受 NULL 值

-- 示例
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

unique (约束)

UNIQUE 约束唯一标识数据库表中的每条记录
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

-------------------- 创建时设置约束 --------------------
-- 示例：单一字段
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)
-- 示例：多字段
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)


-------------------- 创建后设置约束 --------------------
-- 示例：单一字段
ALTER TABLE Persons
ADD UNIQUE (Id_P)
-- 示例：多字段
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

-- 示例：撤销约束
ALTER TABLE Persons
DROP INDEX uc_PersonID

primary key 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。


-------------------- 创建时设置约束 --------------------
-- 示例：单一字段
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)
-- 示例：多字段
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)


-------------------- 创建后设置约束 --------------------
-- 示例：单一字段
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)
-- 示例：多字段
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

-- 示例：撤销约束
ALTER TABLE Persons
DROP PRIMARY KEY

foreign key 约束

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。


-------------------- 创建时设置约束 --------------------
-- 示例：单一字段
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
-- 示例：多字段
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)


-------------------- 创建后设置约束 --------------------
-- 示例：单一字段
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
-- 示例：多字段
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

-- 示例：撤销约束
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

check 约束

CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。


-------------------- 创建时设置约束 --------------------
-- 示例：单一字段
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)
-- 示例：多字段
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)


-------------------- 创建后设置约束 --------------------
-- 示例：单一字段
ALTER TABLE Persons
ADD CHECK (Id_P>0)
-- 示例：多字段
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

-- 示例：撤销约束
ALTER TABLE Persons
DROP CHECK chk_Person

default 约束

DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。


-------------------- 创建时设置约束 --------------------
-- 示例：单一字段
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

-------------------- 创建后设置约束 --------------------
-- 示例：单一字段
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

-- 示例：撤销约束
ALTER TABLE Persons
ALTER City DROP DEFAULT

create index

CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

-- 语法：在表上创建一个简单的索引。允许使用重复的值
CREATE INDEX index_name
ON table_name (column_name)

-- 语法：在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

-- 示例
CREATE INDEX PersonIndex
ON Person (LastName) 

-- 示例：组合索引
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

撤销索引、表以及数据库

使用drop删除

-- 语法：删除索引
ALTER TABLE table_name DROP INDEX index_name

-- 语法：删除表
DROP TABLE 表名称

-- 语法：删除数据库
DROP DATABASE 数据库名称

-- 语法：删除数据，不删除表结构
TRUNCATE TABLE 表名称

alter table

用于在已有的表中添加、修改或删除列

-- 语法：添加列
ALTER TABLE table_name
ADD column_name datatype

-- 语法：删除列
ALTER TABLE table_name 
DROP COLUMN column_name

-- 语法：修改列数据类型
ALTER TABLE table_name 
ALTER COLUMN column_name type

auto increment

在新记录插入表中时生成一个唯一、自增的数字
默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1

-- 示例
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

-- 示例：设置起始值
ALTER TABLE Persons AUTO_INCREMENT=100

view

视图是基于 SQL 语句的结果集的可视化的表

-- 语法：创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

-- 示例：创建视图
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

-- 示例：查询
SELECT * FROM [Current Product List]

-- 语法：更新视图
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

-- 示例：更新视图
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

-- 语法：撤销视图
SQL DROP VIEW Syntax
DROP VIEW view_name

Date 函数

日期
数据库提供函数可以实现提取日期中的任意信息，也有时间格式相关的函数

NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间

-- 示例
SELECT * FROM Orders WHERE OrderDate='2008-12-26'

详细查看Date相关函数

null

NULL 值是遗漏的未知数据。
默认地，表的列可以存放 NULL 值。

如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
NULL 值的处理方式与其他值不同。
NULL 用作未知的或不适用的值的占位符。
注释：无法比较 NULL 和 0；它们是不等价的。

-- 示例：使用is null查找null值
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

-- 示例
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

-- 示例：如果是null值，设置指定值，使用IFNULL()函数
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
-- 示例：如果是null值，设置指定值，使用COALESCE()函数
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

Text 类型
Number 类型
Date 类型

服务器 - RDBMS

DBMS - 数据库管理系统（Database Management System)

RDBMS - 关系数据库管理系统（Relational Database Management System）

SQL 函数

SQL 拥有很多可用于计数和计算的内建函数。

自定义函数语法

SELECT function(列) FROM 表

函数分为两类

  • Aggregate 合计函数（Aggregate functions）
    Aggregate 函数的操作面向一系列的值，并返回一个单一的值
  • Scalar 函数
    Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值

AVG 函数

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

-- 语法
SELECT AVG(column_name) FROM table_name

-- 示例
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

COUNT() 函数

返回匹配指定条件的行数，NULL 不计入

-- 语法
SELECT COUNT(column_name) FROM table_name

SELECT COUNT(*) FROM table_name

SELECT COUNT(DISTINCT column_name) FROM table_name

-- 示例
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter'

SELECT COUNT(*) AS NumberOfOrders FROM Orders

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

FIRST() 函数

返回指定的字段中第一个记录的值

-- 语法
SELECT FIRST(column_name) FROM table_name

-- 示例
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM OrdersOrders

LAST() 函数

LAST() 函数返回指定的字段中最后一个记录的值。

-- 语法
SELECT LAST(column_name) FROM table_name

-- 示例
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

MAX() 函数

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

-- 语法
SELECT MAX(column_name) FROM table_name

-- 示例
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

MIN() 函数

MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

-- 语法
SELECT MIN(column_name) FROM table_name

-- 示例
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

SUM() 函数

SUM 函数返回数值列的总数。

-- 语法
SELECT SUM(column_name) FROM table_name

-- 示例
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

GROUP BY 语句

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

-- 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

-- 示例
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

-- 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

-- 示例
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

参考资料

https://www.w3school.com.cn/sql/sql_syntax.asp

上一篇：2021-05-23 SQL 练习


下一篇：lambd对list的简单使用