SQL命令一览

Posted on | Post modified: | In |
Words count in article: 2.2k

语法

基础语法

查找某列 select
1
2
SELECT 列名称[, 列名] FROM 表名称
SELECT * FROM 表名称  -- 查找表中所有列
去重 distinct
1
SELECT DISTINCT 列名称 FROM 表名称
条件约束 where

为select添加约束条件，可用如下运算符

操作符 描述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式 
1
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
结合多个约束 AND & OR

在where子句中组合多个条件

1
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值 AND/OR 列 运算符 值
对结果排序 ORDER BY

逆序排列在语句后追加DESC，按多个列排列用逗号分割

1
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

其中company 按降序排列，ordernumber按升序排列。

合并列中相同的行 GROUP BY
1
2
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

按customer分类合并。

HAVING子句

having的作用主要是where无法和聚合函数一起使用。

1
2
3
4
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

having常常与group by子句一起使用，若出现group by，则having需放在其后面。

插入新行INSERT INTO
1
INSERT INTO table_name [列1, 列2,...] VALUES (值1, 值2,....)
更新数据 UPDATE SET

将根据条件查找到的数据更新为新的值

1
2
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'
删除数据 DELETE
1
2
DELETE FROM 表名称 WHERE 列名称 = 值  -- 删除满足条件的行
DELETE * FROM table_name  -- 删除所有行，但保留表结构、属性、索引

其他关键字

分页 LIMIT
1
2
SELECT * FROM table_name LIMIT [offset,] rows
SELECT * FROM table_name LIMIT rows OFFSET offset
模式搜索 LIKE
1
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

其中模式可用如下通配符

通配符 描述
% 代表零个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
charlist或者[!charlist] 不在字符列中的任何单一字符
指定多个值 IN

用于在where子句中指定多个值

1
2
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter') -- 选取姓氏为这两个的人
指定范围 BETWEEN AND

指定选取的数据范围，可在BETWEEN前加NOT反向选择

1
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
指定别名 AS

可为列名和表名指定别名

1
SELECT column_name AS alias_colname FROM table_name AS alias_tabname
连接表 JOIN ON

用于将多个表中的内容连接起来，也可以手动将列选出来，使用where筛选。

使用where：

1
2
3
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

使用JOIN ON

1
2
3
4
5
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

join的四个类别

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

JOIN默认是INNER JOIN

合并查询结果 UNIOS

union合并的查询结果必须有相同数量的列，列也需要有相似的数据类型。

1
2
3
SELECT column_name(s) FROM table_name1
UNION [ALL]
SELECT column_name(s) FROM table_name2

使用 UNION ALL表示允许重复。

备份表 SELECT INTO
1
2
3
SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename
创建数据库 CREATE DATABASE
1
CREATE DATABASE database_name
创建数据表 CREATE TABLE
1
2
3
4
5
6
7
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

支持的数据类型有：

数据类型 描述
integer(size)int(size)smallint(size)tinyint(size) 仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d)numeric(size,d) 容纳带有小数的数字。”size” 规定数字的最大位数。”d” 规定小数点右侧的最大位数。
char(size) 容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。
varchar(size) 容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。
date(yyyymmdd) 容纳日期。
为表添加约束

约束类型有：

  • NOT NULL 非空约束
  • UNIQUE 唯一性约束
  • PRIMARY KEY 主键约束
  • FOREIGN KEY 外键约束
  • CHECK 特定值约束(>0等)
  • DEFAULT 指定默认值
1
2
3
4
5
6
7
8
CREATE TABLE Persons
(
Id_P int NOT NULL,             -- 限制该列不接收空值
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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 PRIMARY KEY,  -- 直接指明
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

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

1
2
3
4
5
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

使用DROP删除约束

1
2
3
4
5
ALTER TABLE Persons
DROP PRIMARY KEY

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
创建索引 CREATE INDEX
1
2
CREATE [UNIQUE] INDEX index_name
ON table_name (column_name)
删除索引 DROP INDEX
1
ALTER TABLE table_name DROP INDEX index_name
设置自增属性

一般用于为主键设置自增属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MySQL
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)
)

SQL Server
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,      -- 关键字
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
空值处理

sql的空值NULL不能直接比较，需要用is null或is not null和ifnull()函数来比较

1
2
3
4
5
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

函数用法

1
SELECT function(列) FROM 表

函数的基本类型有两种，聚合函数和标量函数。其中聚合函数的输入可以是多个列，而标量函数只能是一列。

聚合函数：

函数 描述
AVG(column) 返回某列的平均值
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG
COUNT(column) 返回某列的行数（不包括NULL值）
COUNT(*) 返回被选行数
COUNT(DISTINCT column) 返回相异结果的数目
FIRST(column) 返回在指定的域中第一个记录的值（SQLServer2000 不支持）
LAST(column) 返回在指定的域中最后一个记录的值（SQLServer2000 不支持）
MAX(column) 返回某列的最高值
MIN(column) 返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column) 返回某列的总和
VAR(column)
VARP(column)

标量函数：

函数 描述
UCASE(c) 将某个域转换为大写
LCASE(c) 将某个域转换为小写
MID(c,start[,end]) 从某个文本域提取字符
LEN(c) 返回某个文本域的长度
INSTR(c,char) 返回在某个文本域中指定字符的数值位置
LEFT(c,number_of_char) 返回某个被请求的文本域的左侧部分
RIGHT(c,number_of_char) 返回某个被请求的文本域的右侧部分
ROUND(c,decimals) 对某个数值域进行指定小数位数的四舍五入
MOD(x,y) 返回除法操作的余数
NOW() 返回当前的系统日期
FORMAT(c,format) 改变某个域的显示方式
DATEDIFF(d,date1,date2) 用于执行日期计算
count
1
SELECT COUNT(DISTINCT column_name) FROM table_name  -- distinct 指定统计不同的数量
first、last

返回第一个/最后一个记录的值

1
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
max/min
avg
sum
ucase

将字段的值转为大写。

mid

用于在文本中提取字符。

1
2
3
SELECT MID(column_name,start[,length]) FROM table_name

SELECT MID(City,1,3) as SmallCity FROM Persons

其中column_name和start（起始为1）是必须的，length忽略则返回剩余的全部。

len

获取列中值的长度。

round

限制浮点数范围

1
SELECT ROUND(column_name,decimals) FROM table_name

decimals指定小数的位数。

format

修改显示格式

1
2
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
day,month,year(日期函数)

eg: 统计2021-08月的每天的数据

1
2
3
4
select day(date) as d
from table
where month(date) = '08' and year(date) = 2021
group by day

快速参考

语句 语法
AND / OR SELECT column_name(s) FROM table_name WHERE condition AND\ OR condition
ALTER TABLE (add column) ALTER TABLE table_name ADD column_name datatype
ALTER TABLE (drop column) ALTER TABLE table_name DROP COLUMN column_name
AS (alias for column) SELECT column_name AS column_alias FROM table_name
AS (alias for table) SELECT column_name FROM table_name AS table_alias
BETWEEN SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE CREATE DATABASE database_name
CREATE INDEX CREATE INDEX index_name ON table_name (column_name)
CREATE TABLE CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ……. )
CREATE UNIQUE INDEX CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE FROM DELETE FROM table_name (Note: Deletes the entire table!!)orDELETE FROM table_name WHERE condition
DROP DATABASE DROP DATABASE database_name
DROP INDEX DROP INDEX table_name.index_name
DROP TABLE DROP TABLE table_name
GROUP BY SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1
HAVING SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value
IN SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO INSERT INTO table_name VALUES (value1, value2,….)orINSERT INTO table_name (column_name1, column_name2,…) VALUES (value1, value2,….)
LIKE SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY SELECT column_name(s) FROM table_name ORDER BY column_name [ASC\ DESC]
SELECT SELECT column_name(s) FROM table_name
SELECT * SELECT * FROM table_name
SELECT DISTINCT SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO (used to create backup copies of tables) SELECT INTO new_table_name FROM original_table_nameor*SELECT column_name(s) INTO new_table_name FROM original_table_name
TRUNCATE TABLE (deletes only the data inside the table) TRUNCATE TABLE table_name
UPDATE UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value
WHERE SELECT column_name(s) FROM table_name WHERE condition