SQL语句 总结

查询语句

基础查询语句

• show tables
• SELECT
• between
• like
• AND
• OR
• NOT
• IN
• MAX
• MIN
• SUM
• AVG
• count
• ucase
• lcase
• MID
• LEN
• ROUND
• NOW
• FORMAT
• ANY
• ALL

show tables -- 显示所有表名
select * from <table_name>
select * from <table_name> where Age between 18 AND 20 -- 18到20之间
select * from <table_name> where field1 like '%value' -- 模糊查找，其中，占位符'%'代表任意多，'?'代表一个
select * from <table_name> where field1 Age>18 And Name LIKE '张%' -- 年纪>18 并且 姓张的
select * from <table_name> where field1 Age>18 OR Name LIKE '张%' -- 年纪>18 或者 姓张的
select * from <table_name> where Age IN (18,19,20) -- 年纪为18，19,20其中之一
select * from <table_name> where Name NOT LIKE '张%' -- 不姓张的
select Name AS TheName from <table_name> -- 把column Name的名字显示为TheName
select MAX(Score) from Students -- 显示分数最高的
select MIN(Score) from Students -- 显示分数最低的
select SUM(Score) from SC where StudentID='1001' -- 学号为1001的人，所有成绩的和
select AVG(Score) from SC where StudentID='1001' -- 学号为1001的人，所有成绩平均值
select count(*) as total_count from <table_name> -- 显示查到的数据的数量
SELECT UCASE(column_name) FROM table_name -- 转换为大写
SELECT LCASE(column_name) FROM table_name -- 转换为小写
SELECT MID(column_name,start[,length]) FROM table_name
-- 提取字符，start:开始位置(起始位1)，length:要返回的字符数，不填写返回所有剩余文本
SELECT LEN(column_name) FROM table_name -- 字符串长度
SELECT ROUND(column_name,decimals) FROM table_name -- 指定小数位数取舍 decimals:剩下的小数位数
SELECT NOW() FROM table_name -- 获取现在时间
SELECT FORMAT(column_name,format) FROM table_name -- 格式化如 FORMAT(Now(),'YYYY-MM-DD')
select * from S where age > all (select age from S where class = 1) -- 要查找一个学生，其年龄大于1班所有学生

select * from S where age > any (select age from S where class = 1) -- 要查找一个学生，其年龄大于1班任意一个学生即可(等同于：大于年龄最小的学生)

高级查询语句

Order By 顺序

select * from <table_name> Order By Age,Score [DESC] -- 其中，desc为降序
select * from <table_name> Order By Age DESC,Score -- 基于Age降序，然后基于Score升序

Distinct 去除重复值

select Distinct city from <table_name> -- 如果有重复的值，只显示一个

Group by 结合合计函数

select AVG(Score) from <table_name> group by StudentID

Having 基于Group by的条件函数

SELECT name 
FROM t<table_name>
Group By StudentID
Having AVG(Score) >= 60
-- 平均分>=60的学生的名字

子查询

delete from Students where ID NOT in (SELECT MAX(ID) FROM Students) -- 删除ID最大的学生

TOP 记录数量/LIMIT(MySQL)

SELECT TOP 3 ID,Name FROM Students ORDER BY Age DESC -- 显示年纪最高的3个学生的ID和姓名

-- mysql:
SELECT * FROM Students  ORDER BY Age DESC LIMIT 0,3 --从第0个开始，取3个年纪最高的学生和ID

Case 计算条件列表

-- 简单表达式
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
-- 简单搜索式
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

例子：

SELECT ID, Name, CASE Sex WHEN '0' THEN '男' WHEN '1' THEN '女' ELSE '其他' END AS Sex
FROM Students
-- 将 性别 原本为0,1的值，转换为男女

EXCEPT 排除 (与not in 的区别为：EXCEPT会自动去除重复的)

SELECT * FROM TableA EXCEPT （SELECT * FROM TableB） -- 在TableA中，但不在TableB中

INTERSECT 排除 (与 in 的区别为：INTERSECT会自动去除重复的)

SELECT * FROM TableA INTERSECT （SELECT * FROM TableB） -- 在TableA中，且在TableB中

联合查找

假设两个表：

Persons表

Orders表

两个表可以通过Id_P 连接起来。

结果集为：

实现方法：

1.普通联合查找：

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

2. Join On查找

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

不同的 SQL Join

除了在上面的例子中使用的 INNER JOIN（内连接），我们还可以使用其他几种连接。

下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。

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

Union，将两个表查询的内容连接到一起

select  field_name  from <table_name>
union
select  field_name from <table_name>

union是默认去除重复的，如果允许重复值， 需要使用 Union ALL

修改操作语句

SELECT INTO 把查询到的数据，插入另一个表中

SELECT *
INTO new_table_name [IN external_database] -- IN 后面是另一个数据库的名字
FROM old_tablename

CREATE DATABASE 创建数据库

CREATE DATABASE database_name

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)
)

空的Persons表类似下图：

Constrains

创建表的限制

• NOT NULL: 不可以有NULL值(也就是非空)
• UNIQUE: 唯一标识
• PRIMARY KEY: 主键
• FOREIGN KEY:外键
• CHECK:限制值的范围
• DEFAULT:设置插入时的默认值

NOT NULL 与 UNIQUE

• NOT NULL: 不可以有NULL值(也就是非空)
• UNIQUE: 唯一标识
• ADD UNIQUE:表已经创建后，修改UNIQUE
• 命名约束，多约束 :
• DROP CONSTRAINT： 撤销UNIQUE约束

-- 创建表时定义约束

-- MySQL:
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) -- 要求Id_P是唯一标识
)

-- SQL_Server/Oracle/MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE, -- 要求Id_P是非空，唯一标识
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)



-- 如果表已经创建
ALTER TABLE Persons
ADD UNIQUE (Id_P)



-- 如果要命名UNIQUE约束，且定义多个约束
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)



-- 撤销UNIQUE约束

-- MySQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID

-- SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

PRIMARY KEY:主键

主键必须包含唯一的值。

主键列不能包含NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

-- 创建表时定义主键

-- MySQL:
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)
)

-- SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)


-- 如果命名及定义多个PRIMARY KEY
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)
)


-- 表已存在，修改PRIMARY KEY约束
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)


-- 表已存在，命名及定义多个PRIMARY KEY约束
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

-- 撤销PRIMARY KEY约束

-- MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY

-- SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

FOREIGN KEY:外键

假设两个表：

"Persons" 表：

"Orders" 表：

请注意，"Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列。

"Persons" 表中的 "Id_P" 列是 "Persons" 表中的 PRIMARY KEY。

"Orders" 表中的 "Id_P" 列是 "Orders" 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

-- 创建表时候设定外键
-- MySQL:
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) -- 设置外键Id_P，基于Persons的主键(Id_P)，
)

-- SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY 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)

-- 撤销 FOREIGN KEY 约束
-- MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

-- SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

CHECK:限制值的范围

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

-- 创建表时指定
-- MySQL:
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)
)

-- SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
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 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')

-- 撤销 CHECK 约束
-- MySQL:
ALTER TABLE Persons
DROP CHECK chk_Person

-- SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person

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'
)

-- 也可以通过GETDATA()之类的函数，设置系统值
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)

-- 已有表的情况下修改DEFAULT
-- MySQL:
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)

-- SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

-- MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT

-- SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

INDEX 用于快速查找的索引

用户无法看到索引，它们只能被用来加速搜索/查询。

注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

-- 创建索引，允许使用重复值
CREATE INDEX index_name
ON table_name (column_name)

-- 创建索引，唯一索引(如创建在PRIMARY KEY上)
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

-- 例子，创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 列：
CREATE INDEX PersonIndex
ON Person (LastName) 

-- 降序索引
CREATE INDEX PersonIndex
ON Person (LastName DESC) 

-- 多个列索引
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

DROP 撤销索引/表/数据库

-- 删除索引

-- Microsoft SQLJet (以及 Microsoft Access)
DROP INDEX index_name ON table_name

-- MS SQL Server
DROP INDEX table_name.index_name

--  IBM DB2 和 Oracle
DROP INDEX index_name

-- MySQL
ALTER TABLE table_name DROP INDEX index_name


-- DROP TABLE 删除表
DROP TABLE 表名称

-- DROP DATABASE 删除数据库
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 datatype

Auto-increment ：自增长，新记录插入时，生成唯一的数字。

-- MYSQL:
-- 定义P_Id 为自增长
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
Name varchar(255),
PRIMARY KEY (P_Id)
)

-- 创建起始值（比如从100开始）
ALTER TABLE Persons AUTO_INCREMENT=100


-- SQL Server
-- 定义P_Id 为自增长
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY, -- MS SQL使用INDENTITY关键字执行自增长任务
-- 如想从20开始，每次增长10个，可以改成： IDENTITY(20,10)
Name varchar(255)
)
-- Oracle
-- Oracle比较复杂，需要通过 sequence对 创建自增长字段
-- 1.首先定义sequence
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1 -- 开始值
INCREMENT BY 1 -- 增长幅度
CACHE 10 -- 缓存10个，提高性能
-- 定义P_Id 为自增长

-- 要插入，需要使用nextval函数
INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')

操纵语句

Insert 插入语句

insert into <table_name>(field1,field2) values(value1,value2)

Delete 删除语句

delete from table1 where <条件>

Update 更新语句

update table1 set field1=value1 where <条件>

VIEW(视图)

视图是为了安全，易于使用。

创建视图：

create view v as <query expression>

<query expression>可以是任意查询表达式（如select xxx from xxx）

v表示视图名

创建视图，并设置column的名字

create view v(nn,cc) as select name,countrycode from city 
-- 视图中，把name,countrycode 命名为nn,cc

更新视图

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

撤销视图

DROP VIEW view_name

Date函数

MySQL:

SQL Server:

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
• YEAR - 格式 YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: 唯一的数字

如果Date类型中包含时间，仅根据日期查询，无法查询到时间

数据类型

MySQL 数据类型:文本、数字和日期/时间

Text 类型：

Number 类型：

* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

Date 类型：

* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

SQL Server 数据类型

Character 字符串：

Unicode 字符串：

Binary 类型：

Number 类型：

Date 类型：

其他数据类型：