sql温习

1.||连接符
2.集合操作：union与union all区别；intersect返回两个表中共有的行；minus返回的记录是存在于第一个表中但是不存在于第二个表中的记录。in操作：select * from friends where state in ('ca','co','la');
3.between:select * from price where wholesale between 0.25 and 0.75 (注意：between包括边界值)
4.函数学习笔记：
(1)通用函数：max,min,sum,count,avg;
 VARIANCE(方差)
  select variance(hits) from teamstates
 STDDEV(返回一列数值的标准差)
(2)日期/时间函数
   ADD_MONTHS：为某个时间延迟多少个月。
 SELECT TASK,TARTDATE,ADD_MONTHS(ENDDATE,2) FROM PROJECT;
   LAST_DAY：计算制定月份的最后一天。
 SELECT LAST_DAY(ENDDATE) FROM PROJECT;
   MONTHS_BETWEEN:计算制定日期之间有多少个月；
        SELECT MONTHS_BETWEEN(ENDDATE,STARTDATE) FROM PROJECT;
   NEW_TIME:把给定的时间按调整到你所在的时区
 SELECT ENDDATE EDT,NEW_TIME(ENDDATE,'EDT','PDT') FROM PROJECT
   NEXT_DAY：将返回与指定日期在同一星期或者之后一个星期内的，你所要求的星期天数的确切日期。
      如果你想知道你所指定的日期的星期五是几号，你可以这样写：
        SELECT SATRTDATE,NEXT_DAY(STARTDATE,'FRIDAY') FROM PROJECT;
   SYSDATE:将返回系统的日期和时间
 SELECT DISTINCT SYSDATE FROM PROJECT;
(3)数学函数
   ABS函数返回给定数字的绝对值，例如：
 SELECT ABS(A) ABSOLUTE_VALUE FROM NUMBERS;
   CEIL和 FLOOR:CEIL返回与给定参数相等或比给定参数再大的整数。FLOOR则正好相反，它返回与给定参数相等或者比给定参数小的最大整  数。
   COS、COSH、SIN、SINH、TAN、TANH三角函数
   EXP：将返回以给定的参数为指数，以E为底数的幂值。
        SELECT A,EXP(A) FROM NUMBERS;
   LN and LOG

   MOD:取模
 SELECT MOD(A,B) FROM NUMBERS
   POWER:该函数可以返回某一个数对另一个数的幂，在使用幂函数是，第一个参数为底数，第二个为指数。
 SELECT A,B,POWER(A,B) FROM NUMBERS;
   SIGN：如果参数为负数，那么SIGN返回-1，如果参数的值为整数，那么返回1；
   SQRT：该函数返回参数的平方根。
(4)字符函数
   CHR:该函数返回与所给数值参数等当的字符，返回的字符取决于数据库所依赖的字符集。
 SELECT CHR(65) FROM CHARACTERS;返回A
   CONCAT：将字符串连接起来。
 SELECT CONCAT(FIRSTNAME,LASTNAME) FROM CHARACTERS;
   INITCAP:该函数将参数的第一个字母变为大写，此外的字母则转换成小写。
   LOWER和UPPER:将参数全部转换成者小写或大写；
   LPAD与RPAD：这两个函数最少需要两个参数，最多需要三个参数。第一个参数是需要处理的字符串，第二个参数是需要将字符串扩充的宽        带，第三个参数表示加宽部分用什么字符来填充。
   LTRIM AND RTRIM:至少需要一个参数，最多允许两个参数。第一个和LPAD和RPAD类似，为一个字符串，第二个是一个字符或字符串。默认     为空格。
   REPLACE:它的工作就如果它的名字所说的那样该函数需要三个参数第一个参数是需要搜索的字符串第二个参数是搜索的内容第三个参数则    是需要替换成的字符串如果第三个参数省略或者是NULL 那么将只执行搜索操作而不会替换任何内容.
 SELECT LASTNAME REPLACE (LASTNAME,'ST','**') REPLACEMENT FROM CHARACTERS
  SUBSTR
  TRANSLATE:这一函数有三个参数:目标字符串源字符串和目的字符串,在目标字符串与源字符串中均出现的字符将会被替换成对应的目的字     符串的字符。
  INSTR：如果需要知道在一个字符串中满足特定的内容的位置可以使用INSTR， 它的第一个参数是目标字符串，第二个参数是匹配的内容，         第三和第四个参数是数字，用以指定开始搜索的起点以及指出第几个满足条件的将会被返回.下例将从字符串的第二个字符开始搜
         索并返回第一个以O 开头的字符的位置：
  SELECT LASTNAME INSTR （LASTNAME,'o', 2, 1) FROM CHARACTERS;
  LENGTH:将返回指定字符串的长度
 SELECT FIRSTNAME LENGTH (RTRIM(FIRSTNAME)) FROM CHARACTERS
(5)转换函数
TO_CHAR:该函数的最初功能是将一个数字转换为字符型,不同的解释器可能会使用它来转换其它的数据类型例如日期型转换为字符型或者是拥  有更多的参数.
 SELECT TESTNUM,TO_CHAR(TESTNUM) FROM CONVERT
TO_NUMBER:该函数与TO_CHAR 函数相对应,显而易见,它是将一个字符串型数字转换为数值型
(6)其它函数
GREATEST 与LEAST:这两个函数将返回几个表达式中最大的和最小的
 SELECT GREATEST ('ALPHA', 'BRAVO', 'FOXTROT', 'DELTA') FROM CONVERT
USER:该函数返回当前使用数据库的用户的名字

(7)
insert select  语句：它允许程序员拷贝一个或一组表的信息到另外一个表中；起到复制表的作用。
 INSERT INTO table_name (col1, col2...)
 SELECT col1, col2... FROM tablename WHERE search_condition
 注意：INSERT SELECT 语句的另外一个用处是当你需要对表进行重新定义时对表进行备份
(8)当使用PERSONAL ORACLE来创建一个表的时候，对表的命名要遵从几个约束。
 1.表的名字不得超过30个字符长，ORACLE对大小写不敏感。但是表的第一个字符必须是字母(A-Z),其余的字符则还可以有下划线，#￥@。当然，在本工程中表的名字不应该重复。表的名字也不可以是ORACLE的保留字(如SELECT)
(9)oracle所支持的数据类型：CHAR,DATE,LONG(可以支持长达2G 的字符),LONG RAW(可以存储长达2G 的二进制内容),NUMBER,RAW(可以存储长不过255 个字节的二进制代码),ROWID(用一个十六进制的数来标明当前行在表内的唯一地址),VARCHAR2(变长的字母或数字长度可以从1 到2000),ORACLE 提供了一个ROWID 字段,它对于每一行均会自动递增
(10)表的存储与尺寸的调整
 CREATE TABLE TABLENAME
 (COLUMN1 CHAR NOT NULL,
 COLUMN2 NUMBER,
 COLUMN3 DATE)
 TABLESPACE TABLESPACE NAME
 STORAGE
 INITIAL SIZE,
 NEXT SIZE,
 MINEXTENTS value,
 MAXEXTENTS value,
 PCTINCREASE value);
(10)用一个已经存在的表来建表
 CREATE TABLE NEW_TABLE(FIELD1, FIELD2, FIELD3)
 AS (SELECT FIELD1, FIELD2, FIELD3
 FROM OLD_TABLE <WHERE...>
(11)T-SQL中的go相当于ORACLE中的分号；
(12)视图
 用户可以通过创建视图来查询特定的数据如果你的表有50 列且有成千上万个记
 录但是你只需要其中两列的话你可以创建视图来选择这两列然后从视图中查询你会
 发现查询在数据返回时间上与原来有相当大的不同。
    操作视图应该注意的几点：
 a.对于多表视图你不能使用 DELETE 语句
 b.除非底层表的所有非空列都已经在视图中出现,否则你不能使用INSERT 语句,有这个限制的原因是SQL 不知道应该将什么数据插入    到NOT COLUMNS 限制列中
 c.如果对一个归并的表格插入或更新记录,那么所有被更新的记录必须属于同一个物理表
 d.如果你在创建视图时使用了 DINTINCT 子句那么你就不能插入或更新这个视图
   中的记录
 e.你不能更新视图中的虚拟列
(13)使用索引
 在SQL 中使用索引是其于以下几个原因：
 在使用 UNIQUE 关键字时强制性地保证数据的完整性；
 可以容易地用索引字段或其它字段进行排序；
 提高查询的执行速度；
 CREATE INDEX ID_INDEX ON BILLS(ACCOUNT_ID),在索引没有被删除之前,BILLS表中按照ACCOUNT_ID的顺序进行排序。当表被删除的 时候，所有的索引都会被删除。
 索引可以提高数据的返回速度，但是它使得数据的更新操作变慢在对记录和索引进行更新时请不要忘记这一点如果要进行大量的更  新操作在你执行更新操作时请不要忘记先删除索引，当执行完更新操作后只需要简单的恢复索引即可。对于一次特定的操作系统可  以保存删除的索引18 个小时在这个时间内数据更新完后你可以恢复它。索引会占用你的数据库的空间；不要将索引与表存储在同一  个驱动器上 ，分开存储会去掉访问的冲突从而使结果返回得更快。
 ORACLE不支持UNIQUE 语法。
(14)高级sql
临时表：
我们要讨论的第一个高级主题是临时表的用法，这是一种简单的临时存在于数据库系统当中的表格，当结束数据库的联接或退出登录以后它们会被自动地删除。Transact-SQL 在TempDB 中创建临时表，这个数据库是在你安装SQL-SERVER 时创建的，创建临时表可以使用两种语法格式：
方法一：用这种方法创建的临时表，只要用户退出了就被删除。
create table #table_name (field1 datatype,
.
.
.
fieldn datatype)
方法二：用这种方法创建的临时表，用户退出后还不会删除，要SQLSERVER重启后才会删除：
create table tempdb..table_name(field1 datatype,
.
.
.
fieldn datatype)
临时表只可由它的创建者使用

游标：
    数据库指针类似于字处理程序中的指针。当你按下方向键时游标依次从各行文本中
滚动。按一下向上键游标向上跳一行，而按PageUp 和PageDown 则会向一次翻阅几行。数
据库游标的操作也类似。
    数据库游标允许你选择一组数据。通过翻阅这组数据记录通常被称为数据集检查
每一个游标所在的特定的行。你可以将游标和局部变量组合在一起对每一个记录进行检查，
当游标移动到下一个记录时来执行一些外部操作。
游标的另一个常见的用法是保存查询结果以备以后使用，一个游标结果集是通过执行
SELECT 查询来建立的。如果你的应用程序或过程需要重复使用一组记录，那么第一次建
立游标以后再重复使用将会比多次执行查询快得多。而且你还有在查询的结果集中翻阅的
好处。
Transcat-SQL:
declare cursor_name cursor
for select_statement
[for {read only | update [of column_name_list]}]
oracale SQL:
DECLARE cursor_name CURSOR
FOR {SELECT command | statement_name | block_name}

eg1:
创建游标：
create Artists_Cursor cursor
for select * from ARTISTS
go
打开游标：
open Artists_Cursor
go
用游标进行翻阅：
1> declare @name char(30)
2> declare @homebase char(40)
3> declare @style char(20)
4> declare @artist_id int
5> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
6> print @name
7> print @homebase
8> print @style
9> print char(@artist_id)
10> go
注Transcat-SQL 允许程序员通过下边的命令来实现一次移动多行
set cursor rows number for cursor_name

变量@@sqlstatus 返回最后一次运行FETCH 语句的状态信息
status=0:successful completion of the fetch statement
status=1:the fetch statement resulted in an error
staus=2:there is no more data in the result set.

1> declare @name char(30)
2> declare @homebase char(40)
3> declare @style char(20)
4> declare @artist_id int
5> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
6> while (@@sqlstatus = 0)
7> begin
8> print @name
9> print @homebase
10> print @style
11> print char(@artist_id)
12> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
13> end
14> go
17> close Artists_Cursor ----关闭游标
18> deallocate cursor Artists_Cursor----销毁游标
19> go
OUTPUT:
Soul Asylum   Minneapolis Rock    1
Maurice Ravel   France Classical    2
Dave Matthews  Band Charlottesville Rock   3
Vince Gill    Nashville Country    4
Oingo Boingo    Los Angeles Pop    5
Crowded House    New Zealand Pop    6
Mary Chapin-Carpenter Nashville Country    7
Edward MacDowell U.S.A. Classical     8

存储过程
1> create procedure Print_Artists_Name
2> as
3> declare @name char(30)
4> declare @homebase char(40)
5> declare @style char(20)
6> declare @artist_id int
7> create Artists_Cursor cursor
8> for select * from ARTISTS
9> open Artists_Cursor
10> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
11> while (@@sqlstatus = 0)
12> begin
13> print @name
14> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
15> end
16> close Artists_Cursor
17> deallocate cursor Artists_Cursor
18> go
INPUT：
1> execute Print_Artists_Name
2> go
OUTPUT：
Soul Asylum
Maurice Ravel
Dave Matthews Band
Vince Gill
Oingo Boingo
Crowded House
Mary Chapin-Carpenter
Edward MacDowell

带参数的存储过程：
1> create procedure Match_Names_To_Media @description char(30)
2> as
3> select ARTISTS.name from ARTISTS, MEDIA, RECORDINGS
4> where MEDIA.description = @description and
5> MEDIA.media_type = RECORDINGS.media_type and
6> RECORDINGS.artist_id = ARTISTS.artist_id
7> go
1> execute Match_Names_To_Media "CD"
2> go

在选择语句中使用更新和删除

SQL> UPPDATE EMPLOYEE_TBL
SET LAST_NAME = 'SMITH'
WHERE EXISTS (SELECT EMPLOYEE_ID
FROM PAYROLL_TBL
WHERE EMPLOYEE_ID = 2);

SQL优化时候应该注意，在通常的语句中你应该把最大的条件限制语句放在WHERE 子句的最后，ORACLE 查询优化会对WHERE 子句从后向前读，所以它会最先处理我们放置的条件语
句，避免使用OR，我们发现IN 通常比OR 要快
(15)OLAP 与OLTP 的比较
在线的分析处理OLAP 的数据库是一个对最终用户的查询进行统计和汇总的系统；
在线事务过程OLTP 的数据库则是一个将主要的功能提供给为最终用户输入服务的环境的系统包括用户日复一日的查询
OLTP 系统经常用在以日为基本单位在数据库中操作数据的使用场合，数据仓库
与DSS 可以从在线的事务处理数据库中得到它们所需的数据，有时也可以从其它的OLAP
数据库中得到数据。

SQL SERVER 的诊断工具— — SET 命令

 SET STATISTICS IO ON 可以让服务器返回请示的物理和逻辑页数

l SET STATISTICS TIME On 可以让服务器返回语句的运行时间
l SET SHOWPLAN ON 可以让服务器返回当前正在运行的计划中的查询
l SET NOEXEC ON 可以让服务器编译设计过的查询但不运行
l SET PARSONLY ON 可以让服务器对所设计的查询进行语法检查但并不运行
TRANSACT-SQL 也提供下边的命令来帮助你对输出的显示进行控制
l SET ROWCOUNT N 可以让服务器只返回查询中的前N 行
l SET NOCOUNT ON 不必报告查询所返回的行数