SQL必知必会总结

SQL比知必会笔记

创建相关表

CREATE TABLE Customers

(

  cust_id      char(10)  NOT NULL ,

  cust_name    char(50)  NOT NULL ,

  cust_address char(50)  NULL ,

  cust_city    char(50)  NULL ,

  cust_state   char(5)   NULL ,

  cust_zip     char(10)  NULL ,

  cust_country char(50)  NULL ,

  cust_contact char(50)  NULL ,

  cust_email   char(255) NULL 

);

-- -----------------------

-- Create OrderItems table

-- -----------------------

CREATE TABLE OrderItems

(

  order_num  int          NOT NULL ,

  order_item int          NOT NULL ,

  prod_id    char(10)     NOT NULL ,

  quantity   int          NOT NULL ,

  item_price decimal(8,2) NOT NULL 

);

-- -------------------

-- Create Orders table

-- -------------------

CREATE TABLE Orders

(

  order_num  int      NOT NULL ,

  order_date datetime NOT NULL ,

  cust_id    char(10) NOT NULL 

);

-- ---------------------

-- Create Products table

-- ---------------------

CREATE TABLE Products

(

  prod_id    char(10)      NOT NULL ,

  vend_id    char(10)      NOT NULL ,

  prod_name  char(255)     NOT NULL ,

  prod_price decimal(8,2)  NOT NULL ,

  prod_desc  text          NULL 

);

-- --------------------

-- Create Vendors table

-- --------------------

CREATE TABLE Vendors

(

  vend_id      char(10) NOT NULL ,

  vend_name    char(50) NOT NULL ,

  vend_address char(50) NULL ,

  vend_city    char(50) NULL ,

  vend_state   char(5)  NULL ,

  vend_zip     char(10) NULL ,

  vend_country char(50) NULL 

);

-- -------------------

-- Define primary keys

-- -------------------

ALTER TABLE Customers ADD PRIMARY KEY (cust_id);

ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);

ALTER TABLE Orders ADD PRIMARY KEY (order_num);

ALTER TABLE Products ADD PRIMARY KEY (prod_id);

ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);

-- -------------------

-- Define foreign keys

-- -------------------

ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);

ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);

ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

了解SQL

表由列组成，列也称为字段，表的行称为一个记录

表名是唯一的，应该总是定义主键，SQL不区分大小写。

所有的空格都被忽略，所以SQL语句可以写成多行。

查询基本用法：

选择单列(返回的数据没有特定的顺序)： 

SELECT prod_name FROM Products;

选择多列： 

SELECT prod_id, prod_name, prod_price FROM Products

检索所有的列(建议少用) 

SELECT * FORM Products

检索不同的值： 

下面的检索会有重复 

SELECT vend_id FROM Products 

去除重复（DISTINCT关键字作用域所有列）： 

SELECT DISTINCT vend_id FROM Products;

限制结果(第5行起的5行数据，行数从0算): SELECT prod_name FORM Products LIMIT 5 OFFSET 5

注释

行内注释：– 或 # 多行注释：/*commemt*/

排序检索数据

排序

指定一条order by语句时，应该保证它是select语句的最后一条子句，同时用非检索（不是查出来的）的列排序数据是完全合法的。

select prod_name form Products order by prod_name

按多个列排序(prod_price相同时，按照prod_name排序)

select prod_id, prod_price, prod_name from Products order by prod_price, prod_name

按列位置排序(先按第二列排序，再按第三列排序，可以混用实际列名和相对位置)

select prod_id, prod_price, prod_name from Products order by 2, 3

指定排序方向(DESC指定降序)

在字典排序顺序中，A被视为与a相同

select prod_id, prod_price, prod_name from Products order by prod_price desc

select prod_id, prod_price, prod_name from Products order by prod_price DESC, prod_name

过滤数据

使用where子句(order by位于where子句后面)

select prod_name, prod_price from Products where prod_price=3

where子句操作符（有冗余的）

操作符 说明 操作符 说明

= 等于 > 大于

<> 不等于 >= 大于等于

!= 不等于 !> 不大于

< 小于 BETWEEN 在指定的两个值之间

<= 小于等于 IS NULL 为NULL值

!< 不小于

检查单个值

select prod_name, prod_price from Products where prod_price < 10

不匹配检查

单引号用来限定字符串，如果将值与字符串类型的列进行比较，就需要限定引号，用来与数值列进行比较的值不用引号

select vend_id, prod_name from Products where vend_id <> 'DLL01'

范围值检查

select prod_name, prod_price from Products where prod_price between 5 and 10

空值检查

select prod_name from Porducts where prod_prices IS NULL

高级数据过滤

组合where子句(可以多个and, or进行连接)

select porod_id, prod_price, prod_name from Products where vend_id = 'DLL01' and prod_price <= 4

注意and结合优先级大于or

select prod_name, prod_price from Products where (vend_id = 'DLL01' or vend_id = 'BRS01') and prod_price >= 10

IN操作符(与or功能相同，IN操作符的语法更清楚)

select prod_name, prod_price from Products where vend_id IN ('DLL01', 'BRS01') order by prod_name

NOT操作符，否定后面的条件（在复杂语句中使用有优势）

select prod_name from Products where NOT vend_id = 'DLL01' order by prod_name

也可以：

select prod_name from Products where vend_id <> 'DLL01' order by prod_name

通配符进行过滤

%号通配符(接受Fish之后的任意字符，0个或多个，一条语句可以写多个%)

select prod_id, prod_name from Products where prod_name LIKE 'Fish%'

有些数据库会用空格来填充剩下的字段空间，这时候注意：

LIKE 'f%y' 可能匹配不上f开头，y结尾的内容，要用：

LIKE 'f%y%'

%不能匹配NULL

下划线匹配单个字符

select prod_id, prod_name from Products where prod_name LIKE '_ inch teddy bear'

方括号[]指定一个字符集，必须配置指定的位置（只有微软的支持）

select cust_contact from customers where cust_contact LIKE '[JM]%' order by cust_contact

匹配JM中任意一个字符，也是只能匹配单个字符

可以用[^JM]来表示否定

创建计算字段(使用+号拼接结果，+号也可以用||替换)，下面查询的结果将会显示：name空格…(country空格…）

select vend_name + '(' + vend_country + ')' from Vendors ordery by vend_name

Mysql或MariaDB则需要使用以下语句:

select Contact(vend_name, '(', vend_country,')') from Vendors order by vend_name

许多数据库保存填充为列宽的文本值(即会填充空格)，mysql中括号中的空格可以这样去掉：

select rtrim(vend_name) + '(' + RTRIM(vend_country) + ')' from vendors order by vend_name

使用别名(别名既可以是一个单词，也可以是一个字符串，如果是后者，要放在括号中)：

select RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title from Vendors order by vend_name

执行算术操作：

select prod_id, quantity, 

item_price,

quantity*item_price AS expand_price

from OrderItems where order_num = 20008

使用函数

文本处理函数

select vend_name, UPPER(vend_name) as vend_name_upcase from Vendors order by vend_name

常见的文本处理函数：

LEFT():返回字符串左边的字符 LENGTH():返回字符串的长度 LOWER():将字符串转换为小写 LTRIM():去掉字符串左边的字符 RIGHT():返回字符串右边的字符 SOUNDEX()：返回字符串的SOUNDEX值 UPPER()：将字符串转为大写

select cust_name, cust_contact from Customers where cust_contact='Michael Green'

//匹配所有发音类似的联系名

select cust_name, cust_contact from Customers where SOUNDEX(cust_contact)=SOUNDEX('Michael Green')

日期和时间处理函数

//SQLServer中检索2012年的所有订单，第一个参数是返回值

select order_num from Orders where DATEPART(yy, order_date)=2012

//Mysql

select order_num from orders where YEAR(order_date)=2012

数值处理函数

ABS():返回绝对值

COS():余弦

EXP():指数值

PI():圆周率

SIN():正弦

SQRT():数的平方根

TAN():正切

汇总数据

聚集函数

AVG(): 返回某列的平均值 COUNT():返回行数 MAX():最大值 MIN():最小值 SUM():返回某列值之和、

//AVG()只能用于单个列，会忽略列值为NULL的行

select AVG(prod_price) as avg_price from Products

//计算所有行，不会忽略值为NULL的行

select COUNT(*) as num_cust from Customers

//只对单个列计数，忽略NULL的行

select COUNT(cust_email) as num_cust from Customers

//返回指定列的最大值，用于文本的话返回该列排序后的最后一行，忽略NULL

select MAX(prod_price) AS max_price FROM Products

//同上，只不过意思相反

select MIN(prod_price) AS min_price FROM Products

//忽略NULL值

select sum(quantity*quantity) as total_price from OrderItems where order_num=20005

//DISTINCT针对值不同的列操作，不能用于COUNT(*)，与DISTINCE相反的参数是ALL，但是是默认的

select AVG(DISTINCT prod_price) AS avg_price from Products where vend_id='DLL01'

//组合聚集函数

select COUNT(*) AS num_items,

MIN(prod_price) AS price_min,

MAX(prod_price) AS price_max,

AVG(prod_price) AS pirce_avg

FROM Products;

分组数据

数据分组

order by子句必须出现在where子句之后，order by 子句之前

select COUNT(*) AS num_prods FROM Products group by vend_id;

过滤分组(HAVING),在数据分组后进行过滤(WHERE在分组前过滤)

select cust_id, COUNT(*) AS orders from orders GROUP BY cust_id HAVING COUNT(*) >= 2

select vend_id, COUNT(*) AS num_prods FROM Products where prod_price>=4 group by vend_id

having count(*)>=2

select order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*)>=3

ORDER BY items, order_num

使用子查询（推荐使用连接查询）

使用子查询

select cust_id from orders where order_num IN(20007,20008)

//子查询

select cust_id from orders where order_num IN 

(select order_num from orderitems

where prod_id='RGA001')

//使用了表的限定名

select cust_name,

cust_state,

(SELECT COUNT(*)

FROM Orders

WHERE Orders.cust_id = Customers.cust_id) AS orders

from Customers

order by cust_name

连接表

创建连接

//从两个表中选择字段，没有where子句将会返回笛卡尔积,称为等值连接

select vend_name, prod_name, prod_price from Vendors, Products where Vendors.vend_id=Products.vend_id

//内连接（上面其实也是内连接，只不过没采用标明的方式）

select vend_name, prod_name, prod_price from Vendors INNER JOIN Products ON Vendors.vend_id=Porducts.vend_id

连接多个表(连接的表越多，性能下降越厉害)

select prod_name, vend_name, prod_price, quantity from OrderItems, Products, Vendors 

where Porducts.vend_id=Vendors.vend_id

AND OrderItems.prod_id=Products.prod_id

AND order_num=20007

创建高级连接查询

使用表别名(常用于where语句)

select RTRIM(vend_name)+' ('+RTRIM(vend_country)+')' AS vend_title from Vendors

ORDER BY vend_name;

其他一些类型的连接

//自连接

select c1.cust_id, c1.cust_name, c1.cust_contact from Customers AS c1, Customers AS c2

where c1.cust_name = c2.cust_name

AND c2.cust_contact='Jim Jones'; 

//自然连接，去掉重复的列(两张表中拿来比较的列)

select C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity,OI.item_price

from Customers AS C, Orders AS O, OrderItems AS OI

WHERE C.cust_id=O.cust_id

    AND OI.order_num=O.order_num

    AND prod_id='RGAN01'

//外连接，左外连接，以左边为主，选择左边所有的行

select Customers.cust_id, Orders.order_num

from Customers LEFT OUTER JOIN Orders

    On Customers.cust_id=Orders.cust_id

//还有一种是 FULL OUTER JOIN，全外连接，左右都要全选

使用带聚集函数的连接

select Customers.cust_id,

COUNT(Orders.order_num) AS num_ord

FROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id

GROUP BY Customers.cust_id

组合查询

使用UNION操作符组合数条SQL查询,组合成一个查询集，必须是查询相同的列

//这个例子用OR也能实现，对于复杂问题用UNION比较方便

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_state IN('IL', 'IN', 'MI')

UNION

SELECT cust_name, cust_contact, cust_eamil

FROM Customers

WHERE cust_name='Fun4All'

会自动去除重复的行,如果不想去除使用UNION ALL

UNION支持在最后放一个ORDER BY排序语句，对整个结果起作用

插入数据

数据插入,每一列都给出值，并且依赖顺序

select into Customers values('..','..',NULL,NULL)

插入指定列

insert into Customers(cust_id,cust_name...) values('..','..')

省略某些列(该列可以被定义为NULL值)

插入检索出的数据

insert into Customers(cust_id,...)

select cust_id,....

from CustNew

从一个表复制到另一个表

select * into CustCopy from Customers

或

create table CustCopy as select * from Customers

​

更新和删除数据

更新(一个或多个列)

update Customers set cust_email='...' where cust_id='...'

删除数据

delete from Customers where cust_id='...'

创建和操作表

表创建

create table Products

(

prod_id  char(10) not null,

...

);

使用NULL：

NULL值就是没有值，允许NULL也允许在插入的时候不给出该列的值 每个表要么是NULL列，要么是NOT NULL列，不指出，默认为NULL

指定默认值：

quantity INTEGER NOT NULL DEFAULT 1

增加、删除列：

ALTER TABLE Vendors

ADD vend_phone CHAR(20)

ALTER TABLE Vendors

DROP COLUMN vend_phone;

删除列

DROP TABLE CustCopy

使用视图

视图：视图是虚拟的表,方便重复使用以及格式化结果

创建视图：

CREATE VIEW ProductCustomers AS

SELECT cust_name, cust_contact, prod_id

FROM Customers, Orders, OrderItems

WHERE Customers.cust_id=Orders.cust_id

AND OrderItems.order_num=Orders.order_num

使用：

SELECT cust_name, cust_contact

FROM ProductCustomers

WHERE prod_id='RGAN01'

格式化结果的案例：

CREATE VIEW VendorLocations AS

SELECT RTRIM(vend_name) + '('+RTRIM(vend_country)+')'

AS vend_title

FROM Vendors

使用：

SELECT * 

FROM VendorLocations

存储过程

存储过程

@开头的为局部变量，此存储过程没有参数

CREATE PROCEDURE MailingListCount

AS

DECLARE @cnt INTEGER

SELECT @cnt = COUNT(*)

FROM Customers

WHERE NOT cust_email IS NULL;

RETURN @cnt;

调用:

DECLARE @ReturnValue INT

EXECUTE @ReturnValue=MailingListCount;

SELECT @ReturnValue #显示返回值

有参数的存储过程(创建一个新的订单，传入顾客的订单号，GETDATE返回日期)：

CREATE PROCEDURE NewOrder @cust_id CHAR(10)

DECLARE @order_num INTEGER

SELECT @order_num=MAX(order_num) FROM Orders

SELECT @order_num=@order_num+1

INSERT INTO Orders(order_num,order_date,cust_id)

VALUES(@order_num,GETDATE(),@cust_id)

@@IDENTITY获取自动生成的自增长的标识字段

CREATE PROCEDURE NewOrder @cust_id CHAR(10)

AS

INSERT INTO Orders(cust_id)

VALUES(@cust_id)

SELECT order_num=@@IDENTITY

事务管理

一般SQL语句都是针对数据库表直接执行和编写的，即隐式提交，在事务中提交不会隐式进行

BEGIN TRANSACTION

DELETE OrderItems WHERE order_num = 12345

DELETE Orders WHERE order_num = 12345

COMMIT TRANSACTION

部分回退，可以在事务执行的过程中插入一个占位符：

SAVEPOINT delete1

使用该占位符：

ROLLBACK TO delete1

游标

创建游标：

DECLARE CustCursor CURSOR

FOR 

SELECT * FROM Customers

WHERE cust_email IS NULL

使用游标：

OPEN CURSOR CustCursor

关闭游标：

CLOSE CustCursor

高级特性

主键不为空

vend_id CHAR(10) NOT NULL PRIMARY KEY

或者添加主键约束

ALTER TABLE Vendors

ADD CONSTRAINT PRIMARY KEY (vend_id);

外键

cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)

或者添加约束

ALTER TABLE Orders

ADD CONSTRAINT

FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

检查约束

quantity INTEGER NOT NULL CHECK (quantity > 0 )

索引：

类似于排序的原理，加快检索的过程（主键一般都是排序的）

CREATE INDEX prod_name_ind

ON Products (prod_name)

触发器：

下面是一个触发器的例子，对于所有INSERT和UPDATE操作，将cust_state转为大写

CREATE TRIGGER customer_state

ON Customers

FOR INSERT, UPDATE

AS

UPDATE Customers

SET cust_state = Upper(cust_state)

WHERE Customers.cust_id = inserted.cust_id

​

​