SQL应知应会
SQL学习记录
单行注释：-- 注释内容；
多行注释：/* 注释内容 */；
一.易混淆知识点
1.检索不同值
select distinct 列名1,列名2,列名3 from 表名
当有多个列名作为不重复的筛选条件时:
2.sql中and的优先级比or的优先级高,在遇到多个逻辑运算符时,用圆括号有助于消除歧义,明确求职顺序.
3.in:where子句中用来指定要匹配值的清单的关键字,功能与or相当
4.not 否定where子句后的条件
5.通配符:%表示任何字符出现任意次数, _表示任何字符出现一次,[字符集]匹配方括号字符集中任意一个字符,只能匹配单个子符.
6.||将列拼接起来,
7.去空格:rtrim(列名)去掉字符串右边的所有空格,ltrim(列名)去掉字符串左边的所有空格,trim(列名)去掉字符串左右两边的空格
8.upper(列名)将文本转换为大写
9.使用soundex()函数进行搜索,它匹配所有发音类似的项.
例如:select cust_name,cust_contact from Customers where soundex(cust_contact)=soundex('Michael Green');
soundex()是一个将任何文本串转换为描述其语音表示的字母数字模式的算法
10.extract()函数提取日期成分
例如:select order_num from orders where extract(year from order_date)=2020
11.to_date('2020-12-31','')将两个字符串转换为日期
例如:select order_num from Orders where order_date between to_date('2020-01-01','yyyy-mm-dd')
and to_date('2020-12-31','yyyy-mm-dd');
二.常用的文本处理函数
left() 返回字符串左边的子符
lenght()返回字符串的长度
lower()将子符串转换为小写
ltrim()去掉字符串左边的空格
right() 返回字符串右边的子符
rtrim()去掉字符串右边的空格
substr()提取子符串的组成部分
soundex() 返回子符串的soundex值
upper() 将子符串转换成大写
三.常用的数值处理函数
abs()返回一个数的绝对值
cos()返回一个角度的余弦值
exp()返回一个数的指数值
pi()返回圆周率pi的值
sin()返回一个角度的正弦
sqrt()返回一个数的平方根
tan()返回一个角度的正切
四.SQL聚集函数
avg() 返回某列的平均值
例如:select avg(列名) as avg_price from products
count()返回某列的行数
例如:
select count(*) as num_cust from Customers; 返回表中顾客总数
select count(cust_email) as num_cust from Customers; 只对cust_email不为空的列进行计数
max()返回某列的最大值
例如:select max(prod_price) as max_price from 表名
min()返回某列的最小值
例如:select min(prod_price) as min_price from 表名
sum()返回某列值之和
例如:select sum(quantity) from orderItems
聚集函数:
对所有行执行计算计算,指定all参数或不指定参数(因为all是默认行为)
只包含不同的值,指定distinct参数.如果指定列名,则distinct只能用于count(),distinct不能用于count(*).
五.分组数据
1.创建分组group by,过滤分组having.having支持所有where操作符.
例如:select cust_id,count() as orders from Orders group by cust_id having count()>2;where语句在这里是不起作用的,因为过滤是基于分组聚集值,而不是特定行的值.使用having时应该结合group by子句,而where子句用于标准的行级过滤.
2.order by 与group by
| order by | group by |
|---|---|
| 对产生的输出排序 | 对行分组,但输出可能不是分组的顺序 |
| 任意列都可以使用(甚至非选择的列也可以使用) | 只可能使用选择列或表达式列,而且必须使用每个选择列表达式 |
| 不一定需要 | 如果聚集函数一起使用列(或表达式),则必须使用 |
3.在使用group by的同时也应该给出order by子句,这是保证数据正确排序的唯一方法.
例如:select order_num,count() as items from OrderItems group by order_num having count()>=3 order by items,order_num
4.select子句顺序
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| select | 要返回的列或表达式 | 是 |
| from | 从中检索数据的表 | 仅在从表选择数据时使用 |
| where | 行级过滤 | 否 |
| group by | 分组说明 | 仅在按组计算聚集时使用 |
| having | 组级过滤 | 否 |
| order by | 输出排序顺序 | 否 |
六.使用子查询
1.查询:select 列名1,列名2 from 表名 where 条件
例如:select order_num from OrderItems where prod_id='RGAN01';
2.子查询
例如: 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 = 'RGAN01')
3.作为子查询的select语句只能查询单个列,企图检索返回多个列将返回错误.
七.联结表
1.相同的数据出现多次绝不是一件好事,这是关系数据库设计的基础.关系表的设计就是要把信息分解成多个表,一类数据一个表.各表通过某些共同的值相互关联(所以才叫关系数据库)
2.可伸缩:能够适应不断增加的工作量而不失败.设计良好的数据库或应用程序称为可伸缩性好.
3.创建联结:指定要联结的所有表以及关联它们的方式即可.
例如:select vend_name,prod_name,prod_price from Vendors,Products where Vendors.vend_id=Products.vend_id
4.要保证所有的联结都有where子句,否则DBMS将返回比想要的要多的多的数据,各表数据行数的笛卡儿积,返回笛卡儿积的联结也叫叉联结.
5内联结:它基于两个表之间的相等测试的叫做等值联结.也叫内联结.
6.select vend_name,prod_name,prod_price from vendors inner join products No vendors.vend_id=products.vend_id
等同于
select vend_name,prod_name,prod_price from vendors , products where vendors.vend_id=products.vend_id
where语句条件后联结多个表之间用and
7.联结的表越多,性能下降的越厉害.
8.给表起别名.例如select * from user a. user表的别名就是a
9.联结分三种:自联结,自然联结,外联结(inner join on).
9.组合查询
1.利用union,可给出多条select语句,将它们的结果组合成一个结果集.
例如:select cust_name,cust_contact,cust_email from Customers where cust_state in ('IL','IN','MI')
union
select cust_name,cust_contact,cust_email from Customers where cust_name='Fun4All';
2.union必须由两条或者两条以上的select语句组成,语句之间使用关键字union分隔
3.union中的每个查询必须包含相同的列,表达式或聚集函数.
4.列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型.
5.union遇到第一条查询语句的列名和第二条查询语句的列名不同时,它会返回第一个查询语句的列名
6.使用union all,DBMS不取消重复的行.
10.数据插入
1.插入数据的三种方式:插入完整的行;插入行的一部分;插入某些查询的结果.
例如:insert into Customers values(1006,'toy','newyork',null);语法简单,但不安全应该避免使用.
insert into Customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_state,cust_zip,cust_country,cust_contact,cust_email)
values(1006,'toy','123','new','ny','111','usa',null,null);语法更繁琐,也更安全.
给出列能使SQL代码继续发挥作用,即使表结构发生了变化.
主键具有唯一性,不管使用那种insert语法,values的数目都必须正确.如果不提供列名,则必须给每个表提供一个值;如果提供列名,则必须给出列出的每个列一个值.否则,就会产生一条错误消息,相应的行不能成功插入.
2.插入部分行
给出列名,在values里面赋正确的值
insert into Customer(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
values(10006,'toy','123','new','ny','111','usa');
3.如果表的定义允许,这可以在insert操作中省略某些列,省略的列必须满足以下某个条件:
该列定义为允许null值;在表定义中给出默认值.如果不给出默认值,将使用默认值;
4.插入检索出的数据
例如:insert into Custpmers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
select cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country
from CustNew;
insert 通常只插入一行.要插入多行,必须执行多个insert语句;insert select是个例外,他可以用insert插入多行,不管select语句返回多少行,都将被insert插入.
5.从一个表复制到另一个表
create select将数据复制到一个新表
create table 表名1 as select * from 表名2
例如:create table CustCopy as select * from Customers
注意:任何select选项和子句都可以使用,包括where和group by;可利用联结从多个表插入数据;不管从多少个表中检索数据,数据都只能插入到一个表中.
11.更新和删除数据
1.两种更新方式:更新表中的特定行;更新表中所有行
update 表名 set 列名='内容' where 条件
更新语句有三部分组成:要更新的表;列名和他们的新值;确定要更新那些行的过滤条件;
2.删除数据delete语句
delete分两类:从表中删除特定的行;从表中删除所有的行
例如删除一行:delete from Customers where cust_id =10086
格式:delete from 表名 where 条件
3.delete不需要列名或通配符.delete删除整行而不是删除列,要删出列使用update语句;
4.delete语句从表中删除行,甚至删除表中所有行,但是delete不删除表本身;要想删除所有的行可使用truncate
5.drop,truncate,delete三者区别
| drop删表结构 | truncate(保留表结构) | delete | |
|---|---|---|---|
| 相同点 | 删除内容,释放空间 | 删除内容,释放空间 | 删除内容,释放空间 |
| 不同点 | 删除表定义和结构 | 清空数据,只能对表操作 | 可以对表也可以对行 |
| 彻底程度:drop>truncate>delete | 执行速度:drop>truncate>delete |
6.SQL没有撤销按钮,应该非常小心的使用update和delete,否则可能更新或删除了错误的数据.
12.创建和操纵表
1.格式:create table 表名
例如:创建Products表
create table Products
{
prod_id char(10) not null,
vend_id char(10) not null,
prod_name char(254) not null,
prod_price varchar(100) null
}
2.在创建新的表时,指定的表名必定不存在,否则会出错.防止意外覆盖已有的表,SQL要求首先手工删除该表,然后再重建它,而不是简单地用创建语句覆盖它.
3.每个表列要么是null值要么是not null值,这种状态由表的定义规定;也就是说,定义为not null 的列不能为空,定义为null的列,既可以为空值,也可以不为空值.null值是没有值的,不是空字符串.
4.sql允许默认值,在插入行时如果不给出值,DBMS将自动采用默认值;
例如:create table report01
{
quantity integer not null default 1,
price declmal(8,2) not null
}
5.获得系统日期
| DBMS | 函数/变量 |
|---|---|
| DB2 | current_date |
| MySQL | current_date() |
| Oracle | sysdate |
| PostgreSQL | current_date |
| SQL Server | getdate() |
| SQLite | date('now') |
6.更新表
在表的设计过程中充分考虑未来可能的需求,避免今后对表的结构做出较大改动;
所有的DBMS都允许给现有的表增加列,不过对所增加列的数据类型有所限制;
许多DBMS不允许删除或更改表中的列;
多数DBMS允许重命名表中的列;
许多DBMS限制对已经填有数据的列进行更改,对为填有数据的列几乎没有限制;
7.修改表
格式:alter table 表名;
在alter table之后给出要更改的表名(该表必须存在,否则将出错);
列要做出那些改变;
例如增加列:alter table vendors add vend_phone char(20);
例如删除列:alter table vendors drop column vend_phone
8.手动删除表过程
用新的列布局创建一个新表;
使用insert select语句,从旧表复制数据到新表;
检验包含所需数据的新表;
重命名旧表(如果确定,可以删除它);
用旧表原来的名字重命名新表;
根据需要,重新创建触发器,存储过程,索引和外键.
9.在使用alter table要极为小心应该在改动前做完整的备份(表结构和数据的备份)
10.删除表(删除整个表而不是其内容)
drop table 表01;执行这条语句将永久删除该表
13.视图
1.视图是虚拟的表，与包含数据的表不一样，视图本身不包含数据，只包含使用时动态检索数据的查询，视图内容可以读，但不能更改。
2.为什么使用视图：
简化SQL操作，可以方便的重用而不必知道具体查询细节；
保护数据，使用表的一部分而不是整个表；
更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据；
3.视图本身不包含数据，所有每次查询时都必须处理查询执行时所需要的所有检索，如果用了多个联结和过滤创建了复杂的视图，或者嵌套了视图，性能可能会下降的很厉害。
4.视图的规则和限制
创建视图时，视图与表一样，必须唯一命名；
对于可创建的视图数目没有限制；
创建视图必须具有足够的访问权限；
视图可以嵌套（即可以利用其他视图中检索数据的查询来构造新视图，嵌套视图会严重降低查询性能）；
许多DBMS禁止在视图查询中使用order by子句；
有些DBMS要求对返回的所有列名进行命名，如果列是计算字段，则需要使用别名；
例如：select quantity*items_price as expand_price from OrderItems where order_num=20008;
视图不能索引，也不能有关联的触发器或默认值；
有些DBMS把视图作为只读的查询，这意味着不能将数据写回底层；
有些DBMS允许创建这样的视图，它不能进行导致行不在属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客，如果更新某个顾客，删除他的电子邮件地址，将使该顾客不在属于视图。这是默认的，但有些DBMS会防止这种情况发生。
5.创建视图：create view 视图名 as +SQL语句
注：视图名唯一，如果视图已经存在，需要删除后再重新创建。
删除视图：drop view 视图名
6.视图的主要用途是隐藏复杂的SQL代码：例如
create view 视图名 as select cust_name，cust_contact,prod_id
from Customers,Orders,OrderItems
where Customers.cust_id=order.cust_id
and OrderItems.order_num=Orders.order_num;
14.存储过程
1.存储过程：为以后使用而保存的一条或多条SQL语句。可将其视为批处理，但他们的作用不仅仅局限于批处理。
2.为什么要使用存储过程？
通过把处理封装在一个易用的单元中，可以简化复杂的操作；
不要求反复建立一系列处理步骤，因而保证了数据的一致性；例如多个开发人员都使用同一开发过程，则使用的代码都是相同的；
上一点的延伸就是防止错误；例如：执行的步骤越多，错误概率越大，将处理封装起来能起到防止错误的作用；
上一点的延伸就是安全性。例如：通过存储过程限制对基础数据的访问减少读脏数据的机会；
简化对变动的管理；例如：如果业务逻辑等有变化，使用它的人员甚至不需要知道这些变化；
因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量小提高了性能；
存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码；
3.不同的DBMS中存储过程语法有所不同。
4.执行存储过程：execute 存储过程名字；
5.创建一个存储过程：
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;
解释：该存储过程有一个ListCount的参数。Oracle支持in（传递值给存储过程）out（从存储过程返回值） inout（既传递值给存储过程也从存储过程传回值）
调用存储过程（Oracle）
var returnValue number
exec mailingListCount(:ReturnValue);
select ReturnValue;
这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用select语句显示返回的值。
15.事务
1.事务处理：通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。
2，术语解释：
事务：一组SQL语句
回退：撤销指定SQL语句的过程
提交：指将未存储的SQL语句结果写入数据库表
保留点：指事务处理中设置的临时占位符，可以对他发布回退
3.事务处理用来管理insert，update，和delete语句。不能回退select和create，drop操作。
4.控制事务处理：管理事务的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退；
有的DBMS要求明确标识事务处理块的开始和结束。
例如Oracle语法：set transaction ......
5.多数没有明确标识事务处理在何时结束，事务一直存在，直到被中断。通常commit用于保存更改，rollback用于撤销。
例如：delete from Orders；rollback；
执行delete操作，然后rollback语句撤销；
6.使用commit：一般SQL语句都是针对数据库表直接执行和编写的，即隐式提交，即提交操作是自动进行的。
在事务处理中，提交不会隐式进行。进行明确的提交，使用commit语句。
例如Oracle语法：
set transaction
delete OrderItems where order_num=12345;
delete Orders where order_num =12345;
commit;
7.使用保留点
原因：简单的事务可以进行整个的写入或撤销，复杂的事务可能需要部分提交或回退。
过程：要支持回退部分事务，必须在事务处理块中何时位置放置占位符，这样的话，如果需要回退就可以回退到某个占位符。这种占位符称为保留点
Oracle语法：
savepoint delete1；
回退保留点：rollback to delete1；
在SQL代码中设置任意多保留点，越多越好，保留点越多，回退越灵活。
16.游标
1.SQL检索操作返回一组称为结果集的行，这组返回的行都是与SQL语句相匹配的行；
2.游标是一个存储在DBMS服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集，在存储游标之后，应用程序可以根据需要滚动或浏览其中数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
3创建游标：
Oracle语法：
declare cursor custcursor -- declare 语句用来定义和命名游标 custcursor为游标名
is
select * from customers
where cust_email is null；
使用游标：
open cursor custcursor
关闭游标：
close custcursor
17常用的高级数据处理特性
索引，约束，触发器
约束：管理如何插入或处理数据库数据的规则
DBMS通过在数据表上施加约束来实施引用完整性。
1.主键：是一种特殊的约束，用来保证一列中的值是唯一的，而且永不改动。
条件：任意两行的主键值都不相同；
每行都具有一个主键值（即列中不允许NULL值）；
包含主键值的列从不修改或更新；主键值不能重用；
2.创建主键
create table Vendors
(
vend_id char(10) not null primary key,
vend_country char(50) null
);
给表的vend_id列定义添加关键字primary key使其成为主键。
3.设置主键
alter table Vendors
add constraint primary key（vend_id）; --constraint 约束，限制
4.外键
外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。
DBMS不允许删除在另一个表中具有关联行的行。
5.唯一约束与主键的区别
唯一约束：用来保证一列（或一组列）中数据是唯一的。
表中可包含多个唯一约束，但每个表只允许一个主键；
唯一约束列可包含null值；
唯一约束列可修改或更新；
唯一约束列的值可重复使用；
与主键不一样，唯一约束不能用来定义外键；
6.检查约束
检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。
常见用途：检查最小值或最大值；指定范围；只允许特定的值；
7.创建检查约束
create table OrderItems
（
order_num integer not null,
quantity integer not null check(quantity>0)
）;
8.增加检查约束
add constraint check（gender like ‘[MF]’）;
检查名为gender
9.索引
索引用来排序数据以加快搜索和排序操作的速度
索引靠什么起作用？恰当的排序，可以在一个或多个列上定义索引，使DBMS保存其内容的排过序的列表。DBMS搜索排过序的索引，找出匹配的位置，然后检索这些行。
创建索引前注意事项
索引改善检索操作的性能，但降低了数据插入，修改和删除的性能。在执行插入，修改，删除操作时，DBMS必须动态更新索引；
索引数据可能要占用大量的存储空间；
并非所有数据都适合做索引；
索引用于数据过滤和数据排序；
可以在索引中定义多个列；
创建索引语法：
create index prod_name_ind
ON Products（prod_name）;
prod_name_ind 为索引名
on用来指定被索引的表
Products为被索引的表名
prod_name为被索引的列名
索引的效率随表数据的增加或改变而变化。最好定期检查索引。
10.触发器触发器是特殊存储过程，它在特定的数据库活动发生时自动执行。
触发器可以与特定表上的insert，update，，delete操作或组合相关联。
与存储过程不一样（存储过程只是简单地存储SQL语句），触发器与单个的表相关联。例如：与Orders表上的insert操作相关联的触发器只在Orders表中插入行时执行。
触发器内的代码具有一下数据的访问权：
insert操作中所有的新数据；
update操作中所有新数据和旧数据；
delete操作中删除的数据；
触发器可以在特定操作执行之前或之后执行（具体看具体的DBMS）
触发器常见用途：
保证数据一致性；
基于某个表的变动在其他表上执行活动。
进行额外的验证并根据需要回退数据；
计算计算列的值或更新时间戳；
创建一个触发器（对所有insert和update操作，将Customers表中的cust_state列转换为大写）：
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;
一般来说，约束的处理比触发器快，在可能的时候，尽可能使用约束；
11.数据库安全
任何安全系统的基础都是用户授予和身份确认。
一般保护的操作有：
对数据库管理功能（创建表，更改或删除已存在的表等）的访问；
对特定数据库或表的访问；
访问的类型（只读，对特定列的访问等）；
仅通过视图或存储过程对表进行访问；
创建多层次的安全措施，从而允许多种基于登录的访问和控制；
限制管理用户账号的能力；
浙公网安备 33010602011771号