SQL 必知必会笔记整理.docx
- 文档编号:7724783
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:42
- 大小:234.68KB
SQL 必知必会笔记整理.docx
《SQL 必知必会笔记整理.docx》由会员分享,可在线阅读,更多相关《SQL 必知必会笔记整理.docx(42页珍藏版)》请在冰豆网上搜索。
SQL必知必会笔记整理
SQL必知必会笔记整理
目录
SQL必知必会笔记整理1
<1>了解SQL4
<2>检索和排序数据5
<3>过滤数据7
<4>使用通配符过滤数据9
<5>创建计算字段10
<6>使用数据处理函数11
<7>汇总数据——使用聚合函数13
<8>分组数据16
<9>使用子查询17
<10>联结表18
<11>创建高级联结20
<12>组合查询23
<13>插入数据24
<14>更新和删除数据27
<15>创建和操纵表29
<16>使用视图32
<17>使用存储过程33
<18>管理事务处理35
<19>高级SQL特性37
<1>了解SQL
1.1基本概念
数据库(DataBase)
保存有组织的数据库的容器(通常是一个文件或一组文件)。
表(Table)
某种特定数据类型的结构化清单。
模式(Schema)
关于数据库和表的布局及特性的信息。
列(Column)
表中的一个字段。
所有表都是由一个或多个列组成的。
数据类型
所允许的数据库的类型。
每个表都有相应的数据类型,它限制(或允许)该类中存储的数据。
行(Row)
表中的一个记录
主键
一列(或多列),其值能够唯一标准表中的每一行
满足以下条件的任何列都可以作为主键:
1.任意两行都不具有相同的值
2.每一行都必须有一个主键值(主键列不能为NULL值)
3.主键列中的值不允许修改或更新
4.主键值不能重复(如果某行从表中删除,它的主键将不能赋给以后的新行)
1.2关于SQL
SQL是一种专门用来与数据库沟通的语言。
SQL扩展
标准SQL由ANSI标准委员会管理,从而成为ANSISQL。
在ANSISQL的基础上,所有主要的DBMS,在实现ANSISQL的基础上都会有自己的扩展,例如:
PL/SQL、Transact-SQL等。
<2>检索和排序数据
2.1.检索数据
使用SELECT检索表数据,必须至少给出两条信息——想选择什么,以及从什么地方选择。
检索不同的值
使用DISTINCT关键字,检索不同的值,使用示例:
SELECTDISTINCTvend_idFROMProducts;
限制结果
使用关键字限制返回行数:
1.SQLServer、Access ——使用TOP关键字
2.DB2 ——使用FETCHFIRST5(行数)ROWSONLY
3.Oracle ——使用ROWNUM(行计数器)
4.MySQL、MariaDB、PostgreSQL、SQLite —— LIMIT子句
注释
行内注释
SELECTprod_name--这是一条注释
FROMProducts;
多行注释
/*SELECTprod_name,vend_id
FROMProducts;*/
SELECTprod_nameFROMProducts;
2.2.排序检索数据
ORDERBY子句
ORDERBY子句取一个或多个列的名字,据此对输出进行排序,示例:
SELECTprod_nameFROMProductsORDERBYprod_name;
指定排序方向
数据排序默认使用升序排列(ASC),可以使用DESC(降序)关键字进行降序排列
提示
DESC关键字只应用到直接位于其前面的列名,如果想在多个列上进行降序排序,必须对每一列指定DESC关键字。
<3>过滤数据
3.1.过滤数据
在同时使用ORDERBY 和WHERE 子句时,应该让ORDERBY 位于WHERE之后,否则将会产生错误
WHERE子句操作符
范围值检查
使用BETWEEN 操作符,示例:
SELECTprod_name,prod_price
FROMProducts
WHEREprod_priceBETWEEN5AND10;
3.2.高级数据过滤
组合WHERE子句
AND操作符
WHERE子句中的关键字,用来指示检索满足所有给定条件的行:
FROMProducts
WHEREvend_id='DLL01'ANDprod_price<=4;
OR操作符
WHERE子句中使用的关键字,用来表示检索匹配任一给定条件的行:
SELECTprod_name,prod_price
FROMProducts
WHEREvend_id='DLL01'ORvend_id=‘BRS01’;
IN操作符
WHERE子句中用来指定要匹配值的清单的关键字,功能与OR相当:
SELECTprod_name,prod_price
FROMProducts
WHEREvend_idIN('DLL01','BRS01')
ORDERBYprod_name;
NOT操作符
WHERE子句中用来否定其后条件的关键字:
SELECTprod_name
FROMProducts
WHERENOTvend_id='DLL01'
ORDERBYprod_name;
<4>使用通配符过滤数据
4.1.LIKE操作符
通配符(wildcard):
用来匹配值的一部分的特殊字符。
百分号(%)通配符
最常使用的通配符是百分号(%)。
在搜索串中,%表示任何字符出现任意次数:
SELECTprod_id,prod_name
FROMProducts
WHEREprod_nameLIKE'Fish%';
下划线(_)通配符
只匹配单个字符,而不是多个字符,下面的示例有两个_通配符:
SELECTprod_id,prod_name
FROMProducts
WHEREprod_nameLIKE'__inchteddybear';
^脱字号
用于否定集合,在使用多个WHERE子句时可以简化语法:
--匹配不以J或M起头的任意联系人名
SELECTcust_contact
FROMCustomers
WHEREcust_contactLIKE'[^JM]%'
ORDERBYcust_contact;
使用通配符注意事项
由于通配符搜索一般比其他搜索要耗费更长的处理时间,所有使用时需要注意:
∙不要过度使用通配符。
如果其他操作符能达到相同的目的应该使用其他操作符。
∙在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处。
把通配符置于开始处,搜索起来是最慢的。
∙仔细注意通配符的位置。
如果放错地方,可能不会返回想要的数据。
<5>创建计算字段
字段(field)
基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常与计算字段一起使用。
5.1.拼接(concatenate)
将值联结到一起(将一个值附加到另一个值)构成单个值。
Access和SQLServer使用+号。
DB2、Oracle、PostgreSQL、SQLite和OpenOfficeBase使用||。
详细请参阅具体的DBMS文档。
SELECTvend_name||'('||vend_country||')'FROMVendorsORDERBYvend_name;
说明:
TRIM函数
∙RTRIM()(去掉值右边的所有空格)
∙LTRIM()(去掉字符串左边的空格)
∙TRIM()(去掉字符串左右两边的空格)
使用别名(AS关键字):
SELECTRTRIM(vend_name)||'('||RTRIM(vend_country)||')'
ASvend_title
FROMVendors
ORDERBYvend_name;
5.2.执行算术计算
可以使用SQL支持的算术操作符对检索出的数据进行算术计算
SELECTprod_id,
quantity,
item_price,
quantity*item_priceASexpanded_price
FROMOrderItems
WHEREorder_num=20008;
提示:
如何测试计算
SELECT 语句为测试、检验函数和计算提供了很好的方法。
虽然SELECT 通常用于从表中检索数据,但是省略了FROM子句后就是简单地访问和处理表达式,例如SELECT3*2;将返回6,SELECTTrim('abc');将返回abc,SELECTNow();使用Now()函数返回当前日期和时间。
现在你明白了,可以根据需要使用SELECT 语句进行检验。
<6>使用数据处理函数
与SQL语句不一样,SQL函数不是可移植的。
为特定SQL实现编写的代码在其他实现中可能不正常。
因为各个DBMS厂商对函数的实现有很大的区别。
常用函数 大多数SQL实现支持以下类型的函数:
∙用于处理文本字符串(如删除或填充值,转换值为大写或小写)的文本函数。
∙用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
∙用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差,检查日期有效性)的日期和时间函数。
∙返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数
6.1.文本处理函数
SELECTvend_name,UPPER(vend_name)ASvend_name_upcase
FROMVendors
ORDERBYvend_name;
6.2.日期和时间处理函数
日期和时间采用相应的数据类型存储在表中,每种DBMS都有自己的特殊形式。
日期和时间值以特殊的格式存储,以便能快速和有效地排序或过滤,并且节省物理存储空间。
不同SQL对于日期和时间的处理很不一致,可移植性最差。
注意:
DBMS提供的功能远不止简单的日期成分提取。
大多数DBMS具有比较日期、执行基于日期的运算、选择日期格式等的函数。
但是,可以看到,不同DBMS的日期时间处理函数可能不同。
关于具体DBMS支持的日期时间处理函数,请参阅相应的文档。
6.3.数值处理函数
数值处理函数仅处理数值数据。
这些函数一般主要用于代数、三角或几何运算,因此不像字符串或日期-时间处理函数使用那么频繁。
相对来说在主要DBMS的函数中,数值函数是最一致、最统一的函数。
<7>汇总数据——使用聚合函数
有时候我们需要对表中的数据进行汇总,而不需要数据本身,为了方便这些类型的检索,SQL给出了5个聚合函数,SQL聚合函数在各主要的SQL实现中得到了相当一致的支持。
如下:
7.1.AVG()函数
AVG()通过对表中行数计数并计算其列值之和,求得该列的平均值
SELECTAVG(prod_price)ASavg_price
FROMProducts
WHEREvend_id='DLL01';
注意:
只用于单个列
AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。
为了获得多个列的平均值,必须使用多个AVG()函数。
说明:
NULL值
AVG()函数忽略列值为NULL的行。
7.2.COUNT()函数
COUNT()可以确定表中行的数目或符合特定条件的行的数目。
COUNT()函数的两种使用方式
∙使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
∙使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。
SELECTCOUNT(*)ASnum_cust
FROMCustomers;
说明:
NULL值
如果指定列名,则COUNT()函数会忽略指定列的值为空的行,但如果COUNT()函数中用的是星号(*),则不忽略。
7.3.MAX()函数
MAX()返回指定列中的最大值。
MAX()要求指定列名
SELECTMAX(prod_price)ASmax_price
FROMProducts;
提示:
对非数值数据使用MAX()和MIN()
虽然MAX()一般用来找出最大的数值或日期值,但许多(并非所有)DBMS允许将它用来返回任意列中的最大值,包括返回文本列中的最大值。
在用于文本数据时,MAX()返回按该列排序后的最后一行。
说明:
NULL值
MAX()和Min()函数忽略列值为NULL的行。
7.4.MIN()函数
MIN()的功能正好与MAX()功能相反,它返回指定列的最小值
SELECTMIN(prod_price)ASmin_price
FROMProducts;
7.5.SUM()函数
SUM()用来返回指定列值的和(总计)
SELECTSUM(quantity)ASitems_ordered
FROMOrderItems
WHEREorder_num=20005;
提示:
在多个列上进行计算
如本例所示,利用标准的算术操作符,所有聚集函数都可用来执行多个列上的计算。
说明:
NULL值
SUM()函数忽略列值为NULL的行。
7.6.聚合不同的值
对于上面的五个函数都可以如下使用:
∙对所有行执行计算,指定ALL参数或不指定参数(因为ALL是默认行为)。
∙只包含不同的值,指定DISTINCT 参数。
提示:
ALL为默认
ALL参数不需要指定,因为它是默认行为。
如果不指定DISTINCT,则假定为ALL。
DISTINCT示例
SELECTAVG(DISTINCTprod_price)ASavg_price
FROMProducts
WHEREvend_id='DLL01';
注意:
DISTINCT不能用于COUNT(*)
如果指定列名,则DISTINCT只能用于COUNT()。
DISTINCT不能用于COUNT(*)。
类似地,DISTINCT必须使用列名,不能用于计算或表达式。
<8>分组数据
8.1.使用GROUPBY子句创建分组
示例:
SELECTvend_id,COUNT(*)ASnum_prods
FROMProducts
GROUPBYvend_id;
注意
∙GROUPBY子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组。
∙如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。
如果列中有多行NULL值,它们将分为一组。
∙GROUPBY子句必须出现在WHERE子句之后,ORDERBY子句之前。
8.2.使用HAVING过滤分组
除了能用GROUPBY分组数据外,SQL还允许过滤分组,规定包括哪些分组,排除哪些分组
示例:
SELECTcust_id,COUNT(*)ASorders
FROMOrders
GROUPBYcust_id
HAVINGCOUNT(*)>=2;
说明:
HAVING和WHERE的差别
WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。
这是一个重要的区别,WHERE排除的行不包括在分组中。
这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组。
8.3.分组和排序
提示:
不要忘记ORDERBY
一般在使用GROUPBY子句时,应该也给出ORDERBY子句。
这是保证数据正确排序的唯一方法。
千万不要仅依赖GROUPBY排序数据。
8.4.SELECT子句顺序
<9>使用子查询
子查询(subquery),即嵌套在其他查询中的查询。
9.1.利用子查询进行过滤
SELECT语句中,子查询总是从内向外处理。
示例:
1SELECTcust_name,cust_contact
2FROMCustomers
3WHEREcust_idIN(SELECTcust_id
4FROMOrder
5WHEREorder_numIN(SELECTorder_num
6FROMOrderItems
7WHEREprod_id='RGAN01'));
注意:
只能是单列
作为子查询的SELECT语句只能查询单个列。
企图检索多个列将返回错误
提示:
格式化SQL
包含子查询的SELECT语句难以阅读和调试,它们在较为复杂时更是如此。
如上所示,把子查询分解为多行并进行适当的缩进,能极大地简化子查询的使用。
提示:
谨慎使用子查询
在WHERE子句中使用子查询能够编写出功能很强且很灵活的SQL语句。
对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
9.2.作为计算字段使用子查询
示例:
1SELECTcust_name,
2cust_state,
3(SELECTCOUNT(*)
4FROMOrders
5WHEREOrders.cust_id=Customers.cust_id)ASorders
6FROMCustomers
7ORDERBYcust_name;
<10>联结表
可伸缩(scale)
能够适应不断增加的工作量而不失败。
设计良好的数据库或应用程序称为可伸缩性好(scalewell)。
联结(JOIN)
联结(JOIN)是一种机制,用来在一条SELECT语句中关联表,因此称为联结
10.1.创建联结
创建联结非常简单,指定要联结的所有表以及关联它们的方式即可:
1SELECTvend_name,prod_name,prod_price
2FROMVendors,Products
3WHEREVendors.vend_id=Products.vend_id;
警告:
完全限定列名
在引用的列可能出现歧义时,必须使用完全限定列名(用一个句点分隔表名和列名)。
如果引用一个没有用表名限制的具有歧义的列名,大多数DBMS会返回错误。
10.2.WHERE子句的重要性
使用WHERE子句建立联结关系似乎有点奇怪,但实际上是有个很充分的理由的。
要记住,在一条SELECT语句中联结几个表时,相应的关系是在运行中构造的
笛卡儿积(cartesianproduct)
由没有联结条件的表关系返回的结果为笛卡儿积。
检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
注意:
不要忘了WHERE子句
要保证所有联结都有WHERE子句,否则DBMS将返回比想要的数据多得多的数据。
同理,要保证WHERE子句的正确性。
不正确的过滤条件会导致DBMS返回不正确的数据。
提示:
叉联结
有时,返回笛卡儿积的联结,也称叉联结(crossjoin)。
10.3.内联结
基于两个表之间的相等测试的等值联接。
这种联结也称为内联结(innerjoin)
和之前简单等值语法相同的内联查询:
1SELECTvend_name,prod_name,prod_price
2FROMVendorsINNERJOINProducts
3ONVendors.vend_id=Products.vend_id;
10.4.联结多个表
SQL不限制一条SELECT语句中可以联结的表的数目。
创建联结的基本规则也相同。
首先列出所有表,然后定义表之间的关系。
例如:
1SELECTprod_name,vend_name,prod_price,quantity
2FROMOrderItems,Products,Vendors
3WHEREProducts.vend_id=Vendors.vend_id
4ANDOrderItems.prod_id=Products.prod_id
5ANDorder_num=20007;
注意:
性能考虑
DBMS在运行时关联指定的每个表,以处理联结。
这种处理可能非常耗费资源,因此应该注意,不要联结不必要的表。
联结的表越多,性能下降越厉害。
提示:
多做实验
可以看到,执行任一给定的SQL操作一般不止一种方法。
很少有绝对正确或绝对错误的方法。
性能可能会受操作类型、所使用的DBMS、表中数据量、是否存在索引或键等条件的影响。
因此,有必要试验不同的选择机制,找出最适合具体情况的方法。
<11>创建高级联结
11.1.使用表别名
SQL除了可以对列名和计算字段使用别名,还允许给表名起别名。
这样做有两个主要理由:
∙缩短SQL语句;
∙允许在一条SELECT语句中多次使用相同的表。
使用表别名示例:
1SELECTcust_name,cust_contact
2FROMCustomersASC,OrdersASO,OrderItemsASOI
3WHEREC.cust_id=O.cust_id
4ANDOI.order_num=O.order_num
5ANDprod_id='RGAN01';
注意:
Oracle中没有AS
Oracle不支持AS关键字。
要在Oracle中使用别名,可以不用AS,简单地指定列名即可(因此,应该是CustomersC,而不是CustomersASC)。
11.2.使用不同类型的联结
除了内联结或者等值联结的简单联结,还有以下三种类型的联结:
∙自联结(self-join)
∙自然联结(naturaljoin)
∙外联结(outerjoin)
自联结
示例:
1SELECTc1.cust_id,c1.cust_name,c1.cust_contact
2FROMCustomersASc1,CustomersASc2
3WHEREc1.cust_name=c2.cust_name
4ANDc2.cust_contact='JimJones';
提示:
用自联结而不用子查询
自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查询语句。
虽然最终的结果是相同的,但许多DBMS处理联结远比处理子查询快得多。
应该试一下两种方法,以确定哪一种的性能更好。
自然联结
标准的联结(前一笔记中介绍的内联结)返回所有数据,相同的列甚至多次出现。
自然联结排除多次出现,使每一列只返回一次。
自然联结要求你只能选择那些唯一的列,一般通过对一个表使用通配符(SELECT*),而对其他表的列使用明确的子集来完成。
1//通配符只对第一个表使用。
所有其他列明确列出,所以没有重复的列被检索出来
2SELECTC.*,O.order_num,O.order_date,
3OI.prod_id,OI.quantity,OI.item_price
4FROMCusto
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 必知必会笔记整理 必知必会 笔记 整理