SQL速学教程

傷城~ 2022-03-19 02:16 225阅读 0赞

> 注：  
> 1.本文为《SQL必知必会》（作者：Ben Forta）的学习笔记；  
> 2.以下每一节中出现的sql语句的例子仅仅是作为一个语法示例；

## 1.了解数据库 ##

概念：数据库（database）、表（table）、列（column）、行/记录（row）、主键（primary key）。  
SQL数据类型说明：  
**字符串数据类型**，通常用来存储例如名字、地址、电话号码、邮政编码等的数据。有两种基本的字符串类型，分别为定长字符串和变长字符串。字符串值必须括在单引号内。  
CHAR-1~255个字符的定长字符串；  
NCHAR-CHAR的特殊形式，用来支持多字节或Unicode字符；  
NVARCHAR-TEXT的特殊形式，用来支持多字节或Unicode字符；  
TEXT（或LONG、MEMO、VARCHAR）-变长文本；  
**数值数据类型**。  
BIT-单个二进制，0或1；  
DECEMAL（或NUMERIC）-定点或精度可变的浮点数；  
FLOAT（或NUMBER）-浮点数；  
INT（或INTEGER）-4字节整数；  
REAL-4字节浮点数；  
SMALLINT-2字节整数，-32768~32767；  
TINYINT-1字节整数，0~255；  
**日期和时间数据类型**，不同DBMS可能格式不同。通常的日期格式为‘2015-12-30’，通常的事件格式为‘21:46:29’。  
DATE-日期；  
DATETIME（或TIMESTAMP）-日期时间；  
SMALLDATETIME-日期时间，精确到分；  
TIME-时间；  
**二进制数据类型**，可包含诸如图像、多媒体、字处理文档等数据。  
BINARY-定长二进制数据；  
LONG RAW-变长二进制数据，最长2GB；  
RAW（某些DBMS实现为BINARY）-变长二进制数据，最长255B；  
VARBINARY-变长二进制数据；

## 2.检索数据 ##

**SELECT**关键字。可以检索多个列；SQL关键字不区分大小写，例如select,Select,SELECT会被识别为同一个关键字。但是对于表名和列名，需要区分大小写。

select * from products;
    select prod_id,prod_name from products;

**DISTINCT**关键字。检索唯一值，DISTINCT会保证检索出来的数据结果只包含不同的项，如果DISTINCT后面跟随有多个列，那会是这些列的组合。

select distince vend_id from products;

注释：行注释：–test; \#test;块注释：/*test*/;

## 3.排序 ##

**ORDER BY**关键字。ORDER BY字句必须是SQL语句的最后一个子句，否则会报错。当按多个列排序的时候，会依次按照指定的各个列进行排序。ASC（ascending）是升序排序，DESC（descending）是降序排序。

select * from products order by prod_name DESC;

## 4.过滤数据 ##

查询时需要伴随搜索条件（或称为过滤条件），使用**WHERE**子句进行过滤。注意ORDER BY子句要出现在WHERE子句之后。  
WHERE子句操作符包括：  
=：等于；  
<>、!=：不等于（其中<>语法更早，兼容性较好）；  
<：小于；  
<=：小于等于；  
!<：不小于；

> ：大于；  
> =：大于等于；  
> !>：不大于；  
> **BETWEEN… AND…**：在两者之间；  
> **IS NULL**：为NULL值；

select * from products where prod_price between 5 and 10;
    select * from products where prod_price is null;

## 5.高级数据过滤 ##

WHERE字句可以使用操作符进行连接，连接操作符有AND和OR。在同时出现两个操作符的情况下，AND的计算优先级更高，所以多个操作符的字句需要用括号进行优先级区分。

select * from products where (vend_id=’DLL01’ or vend_id=’BSR01’) and prod_price>=10;

**IN**关键字。IN用来指定条件范围，范围中的每个条件都匹配，各个条件用逗号分隔。IN其实实现了OR的功能，但是其性能优于OR。IN可以包含其他SELECT语句，从而能动态的建立WHERE字句。

select * from products where vend_id in (‘DLL01’,BRS01’’);

**NOT**关键字。NOT否定其后跟随的条件语句。NOT经常和IN一起连用。

select * from products where not vend_id=’DLL01’;

## 6.通配符过滤 ##

**LIKE**关键字。LIKE指示SQL语句后面的搜索模式利用通配符。使用通配符会显著降低查询性能，开头放通配符是最慢的查询语句。  
通配符：%、\_  
%：匹配除null以外的任意任何数量的文本；  
\_：匹配单个任意文本；

select * from products where prod_name like ‘% inch’;
    select * from products where prod_name like ‘_ inch’;

## 7.使用计算字段 ##

拼接字段。CONCAT()函数将所有参数拼接成一个字段输出；TRIM(),LTRIM(),RTRIM()分别去除两边、左边、右边的空白符。  
**AS**关键字。计算字段可以被命名为一个别名，这样后端语言就可以获取这个导出列。AS关键字是可选的。

select concat(vend_name,’ (’,vend_country,’)’) as vend_title from vendors order by vend_name;

使用计算符号获得算数计算字段。支持+,-,\*,/四种数学符号。

select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num=20008;

SELECT语句没有FROM字句的话，就可以执行一个计算语句。例如select 3\*2;

## 8.使用函数处理数据 ##

不同的DBMS对函数的支持存在差异，因此带有函数的SQL语句是不可移植的；  
基本函数有四大类：**文本函数、数值函数、时间函数、系统函数**；

## 9.汇总数据 ##

AVG()函数，返回某列的平均值。*会报错，忽略null值，作用于文本列时无意义；  
COUNT()函数，返回某列的行数。count(*)会对表行数计算，而count(column)会忽略null值；  
MAX()函数，返回某列最大值。\*会报错，忽略null值，作用于文本时返回文本序列最后一个值；  
MIN()函数，返回某列最小值。*会报错，忽略null值，作用于文本时返回文本序列第一个值；  
SUM()函数，返回某列值之和。在参数中可以进行多个列的计算，忽略null值；  
聚集函数的DISTINCT参数。不能用于count(*)，因为每一行都是不一样的。用于MAX和MIN是没有意义的；

select avg(distinct prod_price) as avg_price from Products where vend_id=’DLL01’;

## 10.分组数据 ##

**GROUP BY**关键字。该关键字将查询出来的数据按照指定列进行分组，要着重强调数据库查询结果的分组概念。GROUP BY不允许带有长度可变的数据类型（如文本或备注型字段）。NULL将作为一个独立的分组值。

select vend_id,count(*) as num_prods from Products group by vend_id;

**HAVING**关键字。该关键字用于过滤分组，语法和WHERE完全一样（包括通配符条件和带多个操作符的子句），效果也很接近，唯一的差异就是HAVING直接作用于分组，而WHERE作用于行。

select cust_id,count(*) as orders from Orders group by cust_id having count(*)>=2;

WHERE和HAVING的差异：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不出现在分组中。

select vend_id,count(*) as num_prods from Products where prod_price>=4 group by vend_id having count(*)>=2;

（查询出prod\_price价格大于4的数据，按照vend\_id订单号分组，筛选出分组中条数大于等于2的分组）  
ORDER BY在分组中的排序。在有GROUP BY的语句中，都应该有ORDER BY语句配合。GROUP BY出现在WHERE子句之后，ORDER BY子句之前；

select order_num,count(*) as items from OrderItems group by order_num having count(*)>=3 order by items,order_num;

（查询出订单号、该订单下的总数，从订单表，按照订单号分组，筛选出其中条数大于等于3的分组，将分组结果先按照count(\*)总数排序，再按照订单号排序）

## 11.使用子查询 ##

**子查询**通常出现在WHERE子句的IN操作符中，和计算字段中；

select cust_id from Orders where order_num in (select order_num from OrderItems where prod_id=’RGAN01’);

mysql的子查询功能在4.1版本才引入；  
子查询语句必须是查询单个列，其他会报错；  
子查询的性能并不高，应控制嵌套层数；  
可以在计算字段使用子查询，如果有条件比较时务必注意使用完全限定列名来避免列名冲突；

select cust_name,cust_state,(select count(*) from Orders where Orders.cust_id=Customers.cust_id) as orders from Customers order by cust_name;

如果不指定**完全限定列名**，则相当于执行了一个恒等条件：

select count(*) from Orders where cust_id=cust_id;

（这条语句将永远返回表行总数）

## 12.联结表 ##

联结表（等值联结或内联结）有两种基本的形式：WHERE子句和JOIN语句。

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

以上查询语句通过WHERE子句的形式联结了Vendors和Products两个表。  
要注意：（1）多个表出现在一个语句中，必须使用完全限定列名，防止歧义报错；（2）如果在联结表的时候没有WHERE子句，将会得到的结果是表1每一行和表2每一行联结，得出一个n\*n的结果，这个结果数量级成为**笛卡尔积**。  
使用WHERE子句可以联结多个表：

select cust_name from Customers,Orders,OrderItems where Customers.cust_id=Orders.cust_id and OrderItems.order_num=Orders.order_num and prod_id=’RGAN01’;

**INNER JOIN**关键字。

select vend_name,prod_name,prod_price from Vendors inner join Products on Vendors.vend_id=Products.vend_id;

以上语句也是内联结的一种写法，并且这种JOIN语句是ANSI SQL规范首选的联结语法。

## 13.创建高级联结 ##

SQL除了可以对列名和计算字段使用别名，还可以给表名起别名。因此在联结语句中，可以使用表别名来缩短完全限定列名的长度。注意，Oracle中没有AS关键字。  
自联结（self-join）：对于需要联结同一个表内的列来查询的场景，可以使用“子查询”和“自联结”两种解决方案，但是自联结的性能会更优。

select c1.cust_id from Customers as c1,Customers as c2 where c1.cust_name=c2.cust_name and c2.cust_contact=’Jim Jones’;

以上语句通过自联结查询，同样的结果可以通过子查询得到。  
自然联结（natural-join）：自然联结保证每个列只返回一次。这种规则由查询的语句来控制。  
外联结（outer-join）：外联结包括没有关联行的行，使用**LEFT OUTER JOIN**关键字或者**RIGHT OUTER JOIN**关键字（有的sql还支持全外联结：**FULL OUTER JOIN**关键字）。

select Customers.cust_id,Orders.order_num from Customers left outer join Orders on Customers.cust_id=Orders.cust_id;

以上语句会查询出Customers表中和Orders关联的行，并且还会查询出Customers表中不满足关联条件的行（left outer join会保证关联条件左侧的表的行全部返回）。

## 14.组合查询 ##

**UNION**关键字。可以将数条SQL查询组合起来，将结果合并成一个返回。UNION得到的结果同样可以通过使用多个WHERE子句来得到，但是UNION语句会更加清晰。在性能上，UNION和多个WHERE子句的优劣需要在各个不同的DBMS内测试得出。  
在使用UNION关键字时，要保证多条查询语句查询的列出现的次序相同，否则结果会错乱。  
UNION默认去除结果中重复的行，如果要保留这些重复行，使用**UNION ALL**关键字。  
对UNION之后的结果进行ORDER BY排序时，排序只作用于合并的结果，不会单独作用于各个查询结果。  
UNION语句可以来自多个表。

select cust_name,cust_contact from Customers where cust_state in (‘IL’,’IN’)
    union all
    select cust_name,cust_contact from Customers where cust_name=’Fun4all’;

## 15.插入数据 ##

**INSERT INTO**关键字。其中INTO关键字是可选的，但是为了保证可移植性，建议不省略INTO关键字。插入语句可以指定特定的列名，这样可以做到不填那些非必填列，建议总是将插入列名列出来，这样即使表结构发生变化，插入语句仍然可以生效。如果没有指定插入列名，则必须在**VALUES**关键字后面表明所有列的值，如果数量缺失，DBMS会报错。

insert into Customers(cust_id,cust_name,cust_address) values(‘106’,’ToyLand’,’123 Any Street’);

**INSERT SELECT**语句。这种语法可以插入检索出的数据，其中的SELECT语句可以包含WHERE子句，用来筛选数据。INSERT SELECT语句可以插入多条数据。

insert into Customers(cust_id,cust_name) select cust_id,cust_name from CustNew;

（这条语句中，CustNew必须是存在的表。如果该表为空，则语句仍然执行，只是不会有数据发生变化）。  
**SELECT INTO**语句。这种语法可以直接从一个表复制到另一个表。INSERT SELECT和SELECTINTO的区别在于，INSERT SELECT是插入数据，而SELECT INTO是导出数据。SELECT INTO中SELECT语句也可以包含其任何子句，例如WHERE、GROUP BY以及表联结等。

select * into CustCopy from Customers;

(这条语句将创建CustCopy表，并创建\*所有字段，从Customers表填充过去)。  
上条语句MYSQL中等效的语法：

create table CustCopy as select * from Customers;

## 16.更新和删除数据 ##

**UPDATE**关键字。UPDATE更新语句中必须有WHERE子句进行数据筛选，否则将对表中的所有行进行更新，结果是毁灭性的。

update Customers set cust_email=null where cust_id=’105’;

**DELETE**关键字。DELETE删除语句中必须有WHERE子句进行数据筛选，否则将删除表中所有的行，结果是毁灭性的。如果被删除的数据有外键关联关系，则删除将会失败，因此在数据库中设置外键关系是安全性保证。

delete from Customers where cust_id=’105’;

使用UPDATE和DELETE语句的重要原则：  
保证每个表都有主键，保证外键会生效，防止错误的删除；  
在执行UPDATE和DELETE之前，先用SELECT验证WHERE子句的正确性，保证筛选条件是正确的。

## 17.创建和操作表 ##

## 18使用视图 ##

### （1）概念 ###

视图是虚拟的表。注意视图并没有数据（没有提前执行），它在每次调用时才会执行其语句，查询出相关的表。如果用多个联结和过滤创建了复杂的视图并嵌套了视图，将会严重降低执行性能。  
所有DBMS的视图创建语法一致，使用**CREATE VIEW**语句来创建。删除视图使用**DROP VIEW**语句

### （2）限制 ###

许多DBMS禁止在视图查询中使用ORDER BY子句；  
视图不能索引，也不能有关联的触发器或默认值；

### （3）创建一个视图并使用它的代码： ###

--创建视图
    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’;
    --删除视图
    drop view ProductCustomers;

## 19.使用存储过程 ##

### （1）概念 ###

存储过程是保存了一条或多条SQL语句，并稍加处理的函数。存储过程就是用SQL写函数，可以被调用，传参。  
存储过程往往会被编译好保存，因此会增加执行效率。但是存储过程的可移植性非常差，不同DBMS语句差异非常大，因此不常见使用。

### （2）以下展示一个Oracle的存储过程： ###

--创建
    create procedure MailingListCount(ListCount out integer) is v_rows integer;
    begin 
    select count(*) into v_rows from Customers where not cust_email is null;
    ListCount := v_rows;
    end;

（创建名为MailingListCount的存储过程，参数为ListCount，并且OUT关键字表明这个参数是作为返回值使用，存储过程内查询出所有带有email的客户信息，并算出count总数，将总数返回给调用者）。

--执行
    var ReturnValue Number EXEC MailingListCount(:ReturnValue);
    select ReturnValue;

（创建了ReturnValue这个局部变量，然后调用了存储过程，最终使用select语句显示出ReturnValue的值）。

## 20.管理事务处理 ##

### （1）概念 ###

事务可以确保成批的SQL操作要么完全执行（批量SQL没报错），要么完全不执行（批量SQL报错）。当处理的业务问题需要前后操作多个表，多次插入修改时，需要使用事务处理。事务也存在较大的兼容性问题，不同DBMS语法不同。  
事务处理用来管理INSERT、UPDATE、DELETE语句，不能回退SELECT、CREATE、DROP语句。不能回退的语句可以出现在事务处理中，但是不会受到影响。

### （2）事务处理中的关键词 ###

事务（transaction）：指一组SQL语句；  
回退（rollback）：指撤销指定SQL语句的过程；  
提交（commit）：指将SQL语句的执行结果存储提交（通常语句执行隐式提交implicit commit）；  
保留点（savepoint）：事务处理中的临时占位符，可对它发布回退（和回退整个事务不同。一般保留点越多越好，可更灵活的控制回退）；

### （3）下面是一个SQL Server的事务代码示例： ###

--开启事务
    begin transaction
    insert into Customers(cust_id,cust_name)
    values(‘10010’,’Toys Emporium’);
    --设置一个保留点，名字叫StartOrder
    save transaction StartOrder;
    insert into Orders(order_num,order_date,cust_id)
    values(20100,’2001/12/1’,’10010’);
    --如果操作出现错误，回滚到保留点
    if @@ERROR <> 0 rollback transaction StartOrder;
    --如果整个过程没有错误，提交修改
    commit transaction;

## 21使用游标 ##

### （1）概念 ###

游标（cursor）是一个存储在DBMS上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。  
游标功能对基于WEB的应用作用不大。

### （2）代码示例 ###

游标不常用，不同DBMS语法也有差异。以下是一个Oracle版本：

--定义游标
    declare cursor CustCursor
    is 
    select * from Customers where cust_email is null;
    --访问游标数据
    declare type CustCursor is ref cursor return Customers%rowtype;
    declare CustRecord Customers%rowtype
    begin
    --打开游标
    open CustCursor;
    fetch CustCursor into CustRecord;
    --关闭游标
    close CustCursor;
    end;

## 22.高级SQL特性 ##

### （1）约束（constraint） ###

约束是管理如何插入或处理数据库数据的规则。  
<1>主键（primary key）  
主键用来保证一列中的值是唯一的，而且永不改动。任意两行的主键值都不相同，主键不可为NULL，包含主键值得列无法修改和更新，主键值不能重用（即使是曾经被删除的主键，也不可以再使用）。  
创建表时定义主键：

create table Vendors(vend_id char(10) not null primary key);

修改表时定义主键：

alter table Vendors add constraint primary key (vend_id);

<2>外键（foreign key）  
外键是表中的一列，这一列的值必须是另一个表的主键。  
例如Orders（订单）表中包含’ cust\_id’（顾客id），从理论上来说，Orders表中出现的’ cust\_id’必须是Customers（顾客）表中存在的值（否则就是一个不存在的顾客）。而’ cust\_id’正是Customers表的主键，此时就可以将Orders表的’ cust\_id’定义成外键（关联Customers表的主键）。  
当完成了外键的定义，DBMS就会保证在插入Orders表数据时，当出现非法的或不存在的’ cust\_id’值时报错；并且当Orders表中尚存在某个’ cust\_id’的数据时，不可以从Customers表中删除该顾客。  
创建表时定义外键：

create table Orders(cust_id char(10) not null references Customers(cust_id));

修改表时定义外键：

alter table Orders add constraint foreign key (cust_id) references Customers (cust_id);

<3>值约束  
唯一约束：唯一约束用来保证一列中的数据都是唯一的不重复的。但是相比于主键列，唯一约束的列可包含NULL值、可修改或更新、可以重复使用、一个表可以多个列唯一约束。  
检查约束：检查最小值或最大值、指定范围、只允许特定值。

create table OrderItems(quantity integer not null check (quantity>0));

### （2）索引 ###

索引的核心原理是通过排序某列，提升数据查询速度。可以在一张表的一个列或多个列上定义索引，使DBMS保存其内容的一个排序过的列表。建议在取值更多的列上创建索引（如果是类似地名这种经常重复的列，索引带来的优化会很有限，因为重复数据引起的）。  
索引也有缺点：降低了数据插入、修改和删除的性能；索引数据可能要占用大量的存储空间；随着项目的演化，曾经建立的索引可能不再适用，需要周期性的检查索引的效率。

create index prod_name_ind on Products (prod_name);

### （3）触发器 ###

触发器是特殊的存储过程，与特定表上的INSERT、UPDATE、DELETE操作相关联（可在之前或之后触发）。与存储过程不一样的点在于，触发器与单个的表相关联。注意：约束的处理效率比触发器快。  
触发器代码拥有以下数据的访问权：INSERT操作中的所有新数据；UPDATE操作中的所有新数据和旧数据；DELETE操作中删除的数据；  
触发器的常见用途有：保证数据一致；基于某个表的变动在其他表上执行活动；进行额外的验证，并根据需要回退；计算计算列的值或更新时间戳；  
示例采用ORACLE语法：

create trigger customer_state
    after insert or update
    for each row
    begin 
    update Customers set cust_state=Upper(cust_state) where Customers.cust_id=:OLD.cust_id
    end;

以上触发器将保证cust\_state永远是大写的。

### （4）数据库安全 ###

数据库通过用户授权和身份确认来保证其安全性。一般来说，需要保护的操作有：  
对数据库管理功能（创建表、更改或删除表等）的访问；  
对特定数据库或表的访问；  
访问的类型（只读、对特定的列的访问等）；  
仅通过视图或存储过程对表进行访问；  
创建多层次的安全措施，从而允许多种基于登录的访问和控制；  
限制管理用户账号的能力；  
安全性使用SQL的**GRANT**语句和**REVOKE**语句来管理。