数据库sql

缺乏、安全感 2022-07-26 05:12 201阅读 0赞

# **SQL注入问题** #

对付 SQL 注入漏洞有两种方式：过滤敏感字符和使用参数化 SQL。

（1）过滤敏感字符的思路非常简单， 由于恶意攻击者一般需要在输入框中输入的文本一般含  
有 or、and、select、delete 之类的字符串片段，所以在拼接 SQL 之前检查用户提交的文本中  
是否含有这些敏感字符串，如果含有则终止操作。

（2）使用参数化 SQL

query.SetParameter(":user", user);

# **创建数据表** #

SQL 语句 CREATE TABLE 用于创建数据表，其基本语法如下：

CREATE TABLE 表名

(

字段名 1 字段类型，

字段名 2 字段类型，

字段名 3 字段类型，

………………

约束定义 1,

约束定义 2,

………………

)

## **主键** ##

MYSQL,MSSQLServer:  
CREATE TABLE T\_Bus (FNumber VARCHAR(20),FDriverName VARCHAR(20),  
FUsedYears INT,PRIMARY KEY (FNumber))  
Oracle:  
CREATE TABLE T\_Bus (FNumber VARCHAR2(20),FDriverName VARCHAR2(20),  
FUsedYears NUMBER (10),PRIMARY KEY (FNumber))  
DB2:  
CREATE TABLE T\_Bus (FNumber VARCHAR(20) NOT NULL,FDriverName  
VARCHAR(20),  
FUsedYears INT,PRIMARY KEY (FNumber))

## **外键** ##

FOREIGN KEY 外键字段名称 REFERENCES 目标表名(被关联的字段名称)

MYSQL,MSSQLServer,DB2:  
CREATE TABLE T\_Employee (FNumber VARCHAR(20),FName VARCHAR(20),  
FDepartmentId VARCHAR(20),  
FOREIGN KEY (FDepartmentId) REFERENCES T\_Department(FId))  
Oracle:  
CREATE TABLE T\_Employee (FNumber VARCHAR2(20),FName VARCHAR2(20),  
FDepartmentId VARCHAR2(20),  
FOREIGN KEY (FDepartmentId) REFERENCES T\_Department(FId))

# **修改已有数据表** #

## **添加字段** ##

ALTER TABLE 待修改的表名 ADD 字段名 字段类型

ALTER TABLE T\_PERSON ADD FFavorite VARCHAR(20)

## **删除字段** ##

ALTER TABLE 待修改的表名 DROP 待删除的字段名

ALTER TABLET\_Person DROP FAge

DB2 中不能删除字段，所以这个 SQL 语句在 DB2 中是无法正确执行的。

# **删除数据表** #

DROP TABLE 要删除的表名

DROP TABLE T\_Person

必须首先删除引用表，然后才能删除被引  
用表。比如 A 表创建了指向 B 表的外键关联关系， 那么必须首先删除 A 表后才能删除 B 表。

# **数据的增删改** #

## **INSERT 语句** ##

INSERT INTO T\_Person(FName,FAge,FRemark) VALUES('Tom',18,'USA')3

外键是指向另一个表中已有数据的约束，因此外键值必须是在目标表中存在的

## **数据的更新** ##

**UPDATE** T\_Person  
**SET** FAge =  22  
**WHERE** FName ='jim' **OR** FName='LXF'

## **数据的删除** ##

DELETE FROM T\_Person WHERE FAge > 20 or FRemark = 'Mars'

# **数据的检索** #

## **简单的数据检索** ##

**SELECT** \* **FROM** T\_Employee

**SELECT** FNumber **AS** Number1,FName **AS** Name,FAge **AS** Age,FSalary **AS** Salary **FROM**  
T\_Employee

## **按条件过滤** ##

**SELECT** FName **FROM** T\_Employee  
**WHERE** FSalary<5000

**SELECT** \* **FROM** T\_Employee  
**WHERE** FSalary<5000 **OR** FAge>25

## **数据汇总** ##

**COUNT**(\*)统计的是结果集的总条数，而COUNT(FName)统计的则是除了结果集中 FName 不为空值（也就是不等于 NULL）的记录的总条数。

## **排序** ##

**SELECT** \* **FROM** T\_Employee  
**ORDER BY** FAge ASC

**SELECT** \* **FROM** T\_Employee  
**ORDER BY** FAge **DESC**,FSalary **DESC**

ORDER BY 子句完全可以与 WHERE 子句一起使用，唯一需要注意的就是 ORDER BY 子句要  
放到 WHERE 子句之后，不能颠倒它们的顺序。

SELECT \* FROM T\_Employee  
**WHERE FAge>23**  
**ORDER BY** FAge **DESC**,FSalary **DESC**

## **通配符过滤** ##

进行单字符匹配的通配符为半角下划线“\_” ，进行多字符匹配的通配符为半角百分号“%” 。

**SELECT** \* **FROM** T\_Employee  
**WHERE** FName **LIKE** '%n\_'

## **集合匹配** ##

集合匹配只在 MSSQLServer 上提供支持。进行集合匹配的通配符为“\[\]”

以“S”或者“J“开头长度，长度任意

SELECT \* FROM T\_Employee  
WHERE FName LIKE '\[SJ\]%'

比如通配符表达式“\[^bt\]%”匹配第一个字符不为 b 或者 t、长度不限的字符串。

通配符过滤一个非常强大的功能，不过在使用通配符过滤进行检索的时候，数据库系统  
会对全表进行扫描，所以执行速度非常慢。

## **空值检测** ##

**SELECT** \* **FROM** T\_Employee  
**WHERE** FNAME IS NULL

**SELECT** \* **FROM** T\_Employee  
**WHERE** FNAME IS NOT NULL

同时SQL提供了通用的表示“不等于”的运算符“<>”，这样“不  
等于”、“不大于”和“不小于”就分别可以表示成“<>”、“<=”和“>=”。 使用同义运算符、使用NOT运算符。

所以除了“<>”这种方式之外，我们推  
荐使用NOT运算符的方式来表示“非”的语义。

## **多值检测** ##

使用方法为“IN (值1,值2,值3……)”。

**SELECT** FAge,FNumber,FName **FROM** T\_Employee  
**WHERE** FAge **IN** (23,25,28)

## **范围值检测** ##

SQL提供了一个专门用语范围值检测的语句“BETTWEEN AND”，它可以用来检测一个值是否处于某个范围中（包括范围的边界值，也就是闭区间）。

**SELECT** \* **FROM** T\_Employee  
**WHERE** FAGE **BETWEEN** 23 **AND** 27

Where 1=1 用stringBuffer来拼接sql替代。

## **数据分组** ##

数据分组用来将数据分为多个逻辑组，从而可以对每个组进行聚合运算。

也就是没有出现在GROUP BY  
子句中的列（聚合函数除外）是不能放到SELECT语句后的列名列表中的。

**SELECT** FAge **FROM** T\_Employee  
**WHERE** FSubCompany =  'Beijing'  
**GROUP BY** FAge

GROUP BY子句必须放到WHERE语句的之后。

GROUP BY子句中可以指定多个列，只需要将多个列的列名用逗号隔开即可。指定多个分组  
规则以后，数据库系统将按照定义的分组顺序来对数据进行逐层分组，首先按照第一个分组列进  
行分组，然后在每个小组内按照第二个分组列进行再次分组……逐层分组，从而实现“组中组”  
的效果，而查询的结果集是以最末一级分组来进行输出的。

**SELECT** FSubCompany,FAge,**COUNT**(\*) **AS** CountOfThisSubCompAge **FROM**  
T\_Employee  
**GROUP BY** FSubCompany,FAge

## **HAVING 语句分组过滤** ##

聚合函数不能在WHERE语句中使用，必须使用HAVING子句来代替  
**SELECT** FAge,**COUNT**(\*) **AS** CountOfThisAge **FROM** T\_Employee  
**GROUP BY** FAge  
**HAVING COUNT**(\*)>1

使用WHERE的时候  
GROUP BY子句要位于WHERE子句之后，而使用HAVING子句的时候GROUP BY子句要位  
于HAVING子句之前

**SELECT** FAge,**COUNT**(\*) **AS** CountOfThisAge **FROM** T\_Employee  
**WHERE** FName **IS NOT NULL**  
**GROUP BY** FAge

## **Where group by having**  ##

## **限制结果集行数** ##

MYSQL中提供了LIMIT关键字用来限制返回的结果集，LIMIT放在SELECT语句的最后  
位置，语法为“LIMIT 首行行号，要返回的结果集的最大数目”

**SELECT** \* **FROM** T\_Employee **ORDER BY** FSalary **DESC** LIMIT 2,5

MSSQLServer2000中提供了TOP关键字用来返回结果集中的前N条记录，其语法为  
“SELECT TOP 限制结果集数目 字段列表 SELECT语句其余部分”

**select** top  5 \* **from** T\_Employee **order by** FSalary **Desc**

**SELECT** top  3 \* **FROM** T\_Employee  
**WHERE** FNumber **NOT IN**  
(**SELECT** TOP  5 FNumber **FROM** T\_Employee **ORDER BY** FSalary **DESC**)  
**ORDER BY** FSalary **DESC**

MSSQLServer2005提供了新的  
特性来帮助更好的限制结果集行数的功能，这个新特性就是窗口函数ROW\_NUMBER()。

Oracle中支持窗口函数ROW\_NUMBER()，其用法和MSSQLServer2005中相同，比如我们  
执行下面的SQL语句：  
**SELECT** \* **FROM**  
(  
**SELECT** ROW\_NUMBER() OVER(**ORDER BY** FSalary **DESC**) row\_num,  
FNumber,FName,FSalary,FAge **FROM** T\_Employee  
) a  
**WHERE** a.row\_num>=3 **AND** a.row\_num<=5

DB2还提供了FETCH关键字用来提取结果集的前N行，其语法为“FETCH  
FIRST 条数 ROWS ONLY” ，比如我们执行下面的SQL语句可以得到按工资从高到底排序的  
前6名员工的信息：  
**SELECT** \* **FROM** T\_Employee  
**ORDER BY** FSalary **Desc**  
**FETCH FIRST** 6 **ROWS ONLY**  
需要注意的是**FETCH**子句要放到**ORDER BY**语句的后面，执行完毕我们就能在输出结果中  
看到下面的执行结果

## **常量字段** ##

SELECT 'CowNew集团',918000000,FName,FAge,FSubCompany FROM T\_Employee

## **字段间计算** ##

**SELECT** FNumber,FName,FAge \* FSalary **FROM** T\_Employee

**SELECT** 125\+521,FNumber,FName,FSalary/(FAge-21) **AS** FHappyIndex  
**FROM** T\_Employee

在MYSQL中，当用加号“+”连接两个字段（或者多个字段）的时候，MYSQL会尝  
字段值转换为数字类型（如果转换失败则认为字段值为0） ，然后进行字段的加法运算。

MSSQLServer中可以直接使用加号“+”来拼接字符串

Oracle中使用“||”进行字符串拼接，

**SELECT** '工号为'||FNumber||'的员工姓名为'||FName **FROM** T\_Employee  
**WHERE** FName **IS NOT NULL**

DB2中使用“||”进行字符串拼接，

**SELECT** '工号为'||FNumber||'的员工姓名为'||FName **FROM** T\_Employee  
**WHERE** FName **IS NOT NULL**

## **简单的结果集联合** ##

UNION运算符要放置在两个查询语句之间。

有的时候我们需要组合两个完全不同的查询结果集， 而这两个查询结果之间没有必然的  
联系，只是我们需要将他们显示在一个结果集中而已。在SQL中可以使用UNION运算符来  
将两个或者多个查询结果集联合为一个结果集中。

**联合结果集的原则：**

一是每个结果集必须有相同的列数；二是每个结果集的列必须类型相  
容

每个结果集的列必须类型相容，也就是说结果集的每个对  
应列的数据类型必须相同或者能够转换为同一种数据类型

如果需要在联合结果集中返回所有的记录而不管它们是否唯一，则需要在  
UNION运算符后使用ALL操作符，比如下面的SQL语句：  
**SELECT** FName,FAge **FROM** T\_Employee  
**UNION ALL**  
**SELECT** FName,FAge **FROM** T\_TempEmployee

# **索引** #

索引用来提高数据的检索速度， 而约束则用来保证数据的完整性。

索引是针对字段的，因此创建索引索引的时候需要指定要在那个字段上创建索引，还可  
为多个字段创建一个索引，这样还可以指定索引相关的字段列表。创建索引的 SQL 语句

CREATE INDEX 索引名 ON 表名(字段 1, 字段 2,……字段 n)

**CREATE INDEX** idx\_person\_nameage **ON** T\_Person(FName,FAge)

MYSQL 中的 DROP INDEX 语法如下：  
DROP INDEX 索引名 ON 表名

**DROP INDEX** idx\_person\_name **ON** T\_Person;

MSSQLServer 中的 DROP INDEX 语法如下：  
DROP INDEX 表名.索引名

DROP INDEX T\_Person.idx\_person\_name;

Oracle 和 DB2 中的 DROP INDEX 语句不要求指定表名，只要指定索引名即可，语法如  
下：  
DROP INDEX 索引名

DROP INDEX idx\_person\_name;

select  列名 from 表名(index=你创建的索引名)+条件

# **约束** #

定义复合唯一约束需要定义在所有字段列表之后，语法如下：  
CONSTRAINT 约束名 UNIQUE(字段 1,字段 2……字段 n)

CREATE TABLE T\_Person (FNumber VARCHAR(20),  
FDepartmentNumber VARCHAR(20),  
FName VARCHAR(20),FAge INT,  
CONSTRAINT unic\_dep\_num UNIQUE(FNumber,FDepartmentNumber))

ALTER TABLE 表名 DROP CONSTRAINT 唯一约束名

出现这个错误的原因是因为在这种方式定义的 CHECK 子句中是不能引用其他列的，如果希望 CHECK 子  
句中的条件语句中使用其他列，则必须在 CREATE TABLe 语句的末尾使用 CONSTRAINT 关键字定义它。语法  
为：  
CONSTRAINT 约束名 CHECK(约束条件)

# **表连接** #

进行多表检索最常用的技术就是表连接。

表连接使用 JOIN 关键字将一个或者多个表按照彼此间的关系连接为一个结果集。表连接就像连接两张数据表的连线，线的两端是分别在两张表的特定字段上的。

## **内连接（INNER JOIN）** ##

内连接组合两张表，并且基于两张表中的关联关系来连接它们。使用内连接需要指定表  
中哪些字段组成关联关系，并且需要指定基于什么条件进行连接。内连接的语法如下：  
INNER JOIN table\_name  
ON condition  
其中 table\_name 为被关联的表名，condition 则为进行连接时的条件。

**SELECT** FNumber,FPrice  
**FROM** T\_Order **INNER JOIN** T\_Customer  
**ON** FCustomerId= T\_Customer.FId  
**WHERE** T\_Customer.FName='TOM'

建议使用表连接的时候要显式列所属的表

在大多数数据库系统中，INNER JOIN 中的 INNER 是可选的，INNER JOIN 是默认的连  
接方式。

**SELECT** T\_Order.FId,T\_Order.FNumber,T\_Order.FPrice,  
T\_Customer.FId,T\_Customer.FName,T\_Customer.FAge  
**FROM** T\_Order **JOIN** T\_Customer  
**ON** T\_Order.FCustomerId= T\_Customer.FId

## **不等值连接** ##

到目前为止，本书中所有的连接几乎都是等值连接，也就是在这种连接的ON子句的条  
件包含一个等号运算。等值连接是最常用的连接，因为它指定的连接条件是一个表中的一个  
字段必须等于另一个表中的一个字段。  
处理等值连接， 还存在另外一种不等值连接， 也就是在连接的条件中可以使用小于 （<） 、  
大于（>） 、不等于（<>）等运算符，而且还可以使用LIKE、BETWEEN AND等运算符，甚

**SELECT** T\_Order.FNumber,T\_Order.FPrice,  
T\_Customer.FName,T\_Customer.FAge  
**FROM** T\_Order  
**INNER JOIN** T\_Customer  
**ON** T\_Order.FPrice< T\_Customer.FAge\*5

**SELECT** T\_Order.FNumber,T\_Order.FPrice,  
T\_Customer.FName,T\_Customer.FAge  
**FROM** T\_Order  
**INNER JOIN** T\_Customer  
**ON** T\_Order.FPrice< T\_Customer.FAge\*5  
**and** T\_Order.FCustomerId=T\_Customer.FId

## **交叉连接** ##

与内连接比起来，交叉连接非常简单，因为它不存在ON子句。交叉连接会将涉及到的  
所有表中的所有记录都包含在结果集中。可以采用两种方式来定义交叉连接，分别是隐式的  
和显式的。  
隐式的连接只要在SELECT语句的FROM语句后将要进行交叉连接的表名列出即可

T\_Order做交叉连接：  
SELECT T\_Customer.FId, T\_Customer.FName, T\_Customer.FAge,  
T\_Order.FId, T\_Order.FNumber, T\_Order.FPrice  
FROM T\_Customer, T\_Order

## **自连接** ##

连接的表是同一张  
表，那么它们就可以被称为自连接。  
虽然大部分时间使用连接都是在连接不同的表，但是有的时候表也需要与自身连接，其  
主要用途就是检索一张表内部的匹配情况。

## **外部连接** ##

外部连接主要就是用  
来解决这种空值匹配问题的。  
外部连接的语法与内部连接几乎是一样的，主要区别就是对于空值的处理。外部连接不  
需要两个表具有匹配记录，这样可以指定某个表中的记录总是放到结果集中。根据哪个表中  
的记录总是放到结果集中，外部连接分为三种类型：右外部连接（RIGHT OUTER JOIN） 、  
左外部连接（LEFT OUTER JOIN）和全外部连接（FULL OUTER JOIN） 。  
三者的共同点是都返回符合连接条件的数据，这一点是和内部连接是一样的，不同点在  
于它们对不符合连接条件的数据处理，三者不同点说明如下：  
l 左外部连接还返回左表中不符合连接条件的数据；  
l 左外部连接还返回右表中不符合连接条件的数据；  
l 全外部连接还返回左表中不符合连接条件的数据以及右表中不符合连接条件的数据， 它  
其实是左外部连接和左外部连接的合集。

### **左外部连接** ###

在左外部连接中，左表中所有的记录都会被放到结果集中

**SELECT** o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
**FROM** T\_Order o  
**LEFT OUTER JOIN** T\_Customer c  
**ON** o.FCustomerId=c.FId

### **右外部连接** ###

在右外部连接中不管是否成功匹配连接条件都会返回右表中的  
所有记录。比如下面的SQL语句使用右外部连接查询每张订单的信息以及对应的客户信息：  
**SELECT** o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
**FROM** T\_Order o  
**RIGHT OUTER JOIN** T\_Customer c  
**ON** o.FCustomerId=c.FId

### **全外部连接** ###

几乎所有的数据库系统都支持左外部连接和右外部连接， 但是全外部连接则不是所有数  
据库系统都支持，特别是最常使用的MYSQL就不支持全外部连接。全外部连接是左外部连  
接和右外部连接的合集，因为即使在右表中不存在匹配连接条件的数据，左表中的所有记录  
也将被放到结果集中，同样即使在左表中不存在匹配连接条件的数据，右表中的所有记录也  
将被放到结果集中。  
比如下面的SQL语句使用全外部连接查询每张订单的信息以及对应的客户信息：  
**SELECT** o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
**FROM** T\_Order o  
**FULL OUTER JOIN** T\_Customer c  
**ON**o.FCustomerId=c.FId

**虽然在MYSQL中不支持全外部连接，不过由于全外部连接是左外部连接和右外部连接的**

**合集，所以可以使用左外部连接和右外部连接来模拟实现全外部连接：使用左外部连接和右**

**外部连接分别进行匹配查询，然后使用UNION运算符来取两个查询结果集的合集。比如可**

**以在MYSQL中执行下面的SQL来实现T\_Order表和T\_Customer表的全外部连接：**

**SELECT o.FNumber,o.FPrice,o.FCustomerId,**

**c.FName,c.FAge**

**FROM T\_Order o**

**LEFT OUTER JOIN T\_Customer c**

**ON o.FCustomerId=c.FId**

**UNION**

**SELECT o.FNumber,o.FPrice,o.FCustomerId,**

**c.FName,c.FAge**

**FROM T\_Order o**

**RIGHT OUTER JOIN T\_Customer c**

**ON o.FCustomerId=c.FId**

# **视图** #

Create view name as select \* from xxx

CREATE VIEW "public"."NewView" AS

SELECT a.id,

a.cloudappid,

a.creator,

a.createtime,

a.updater,

a.updatetime,

a.a0,

a.a1,

a.a2,

a.a3,

a.a4,

a.a5,

a.a6,

a.a6a,

a.a7,

a.a8,

a.a9,

a.a10,

a.a79,

a.a80,

a.a81,

a.f7,

a.f8,

a.f9,

a.f10,

a.f11,

a.a10c,

a.a10d,

( SELECT count(\*) AS count

FROM srfpresidentfamily b

WHERE ((b.aid)::text = (a.id)::text)) AS familysum

FROM srfppovertyfamily a;;

## 删除表中数据（效率高） ##

**TRUNCATE TABLE**语句提供了一种删除表中所有记录的快速方法。因为TRUNCATE TABLE语句不记录日志，只记录整个数据页的释放操作，而DELETE语句对每一行修改都记录日志，所以使用TRUNCATE TABLE语句进行删除操作总是比没有指定条件的DELETE语句效率高。TRUNCATE TABLE立即释放了表中数据及索引所占用的全部空间，同时也释放了分配给所有索引的空间。其语法格式如下：

TRUNCATE TABLE \[ \[database.\] owner.\] table\_name

truncate table user ;---删除user表中的数据跟[delete][]用法差不多，但是truncate   
删除的数据是找不回来的，[delete][]删除是可以找回的。truncate一次性删除完，delete是一行一行的删除的，所有truncate删除的速度比较快

[delete]: https://www.baidu.com/s?wd=delete&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1d9mHnLryPhuA7brH-WmHwW0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPHcsnj6Yrj0YPW6vPWm4P1md