SQL server 学习总结.docx
《SQL server 学习总结.docx》由会员分享，可在线阅读，更多相关《SQL server 学习总结.docx（36页珍藏版）》请在冰豆网上搜索。
SQLserver学习总结
SQL（StructureQueryLanguage）：
结构化查询语言，是用于访问关系型数据库的
专用语言，同样也是数据库的核心语言。
它功能强大、效率高、简单易学易维护
。
正因为它简单，也就暴露出它的缺点来，就好比便宜没好货一样（当然，SQL绝
对是物美价廉的实惠“货”）。
SQL是非过程性语言，即大多数语句都是独立执行的，与上下文无关，而绝大部分
应用都是一个完整的过程，显然用SQL完全实现这些功能是很困难的，而为了解决
这个问题大多数数据库公司作了如下两方面的工作：
（1）：
扩充SQL，在SQL中引入过程性结构
（2）：
把SQL嵌入到高级语言中，以便一起完成一个完整的应用
SQL的分类
SQL语言共分为三大类：
数据操作语言（DML），数据定义语言（DDL），数据控制
语言（DCL）
数据操作语言主要有四种形式：
插入：
INSERT
更新：
UPDATE
删除：
DELETE
查询：
SELECT
数据定义语言主要用来创建数据库中的各种对象---表、视图、索引、同义词、聚
簇等
CREATEDATABASE创建数据库
CREATETABLE创建表
DROPTABLE删除表
ALTERTABLE修改表的结构
CREATEVIEW创建视图
DROPVIEW删除视图
CREATEINDEX创建索引
DROPINDEX删除索引
数据控制语言（DCL）：
用来授予或回收访问数据库的某种特权，并进行控制数据
库操纵事务发生的时间及效果和对数据库实行监视等！
如：
GRANT授权
REVOKE取消权限
ROLLBACK[WORK]TO[SAVEPOINT]回退到某一点
ROLLBACK回滚---回滚命令使数据库状态回到上次最后提交的状态，其操作方法
如下：
SQL>ROLLBACK;
COMMIT[WORK]提交---在对数据库进行插入、删除和修改操作时，只有当事务
在提交到数据库时才算完成，在事务提交前，只有操作数据库的这个人才能有权
看到所做的事情，别人只有在最后提交完成后才可以看到。
而提交数据有三种类
型：
显式提交
隐式提交
自动提交
显式提交：
用COMMIT命令直接完成的提交为显式提交，其命令方式为：
SQL>COMMIT;
隐式提交：
用SQL命令间接完成的提交为隐式提交，这些命令为：
ALTERAUDITCOMMENTCONNECTCREATEDISCONNECTDROPEXITGRANTNOAUDIT
QUITREVOKERENAME
自动提交：
若把AUTOCOMMIT参数设置为ON，则在插入、修改、删除语句执行后，系统将自动
进行提交，这就是传说中的自动提交，其命令方式为：
SQL>SETAUTOCOMMITON;
上一篇文章我也介绍了ISQL*Plus和OEM，其实ISQL*Plus是在oracle9i产品中新
增的，并在oracle10g中得到加强。
接下来我们首先来了解一下SQL中最常用到的语句：
SELECT（查询）
那么我们通过SELECT语句可以做哪些事情呢？
可以做如下几种操作：
列选择：
选择表中特定的列
行选择：
选择表中特定的行
连接：
能够使用SELECT语句的连接功能来进行数据库对象的连接，并返回几个对
象的集合的数据，这些数据被存储在不同的表、视图中（所谓视图可以将其想象
成表，只是一般不存储实际的数据），在这些不同的表、视图之间是可以创建连
接的。
连接可以针对两个表或者视图，也可以针对多个表或者视图。
并且连接还
有不同的类型，比如外连接、内连接、左连接、右连接等。
来看一下SELECT命令的语法，一个SELECT语句必须包括一个SELECT子句和一个
FROM子句：
SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable;
其中[]里面的内容是可选项
SELECT是一个或多个字段的列表
*选择所有的列
DISTINCT禁止重复
column|expression选择指定的字段或表达式
alias给所选择的列不同的标题
FROM指定包含列的表
如：
列出所有列：
SQL>select*fromtest;
列出所有列：
SQL>select
department_id,department_name,manager_id,location_idfromtest;
列出指定列：
SQL>selectdepartment_id,department_namefromtest;---当然
这里的列的排列顺序是可以自定义的，你也可以这样：
select
department_name,department_idfromtest;
除了可以选择表中的列之外，我们还可以选择伪列。
一个伪列的行为像表中的列
一样，但不是真正存储在表中的，你是不能插入、删除伪列的值。
一些可用的伪
列有：
CURRVALNEXTVALLEVELROWIDROWNUM。
其中CURRVALNEXTVAL来自于序列
（SEQUENCE），LEVEL来自于分析函数，ROWID显示了该列的实际存储位置，
ROWNUM显示了该列在当前条件下的顺序号。
注意：
在ISQL*Plus中子句最后的分号（；）不是必须的，但是还是推荐使用！
在SQL语句中我们可以使用别名来覆盖列标题，比如：
SQL>selectlast_name,hire_date,salaryfromtest;可以改成：
SQL>selectlast_nameln,hire_datehd,salarysrfromtest;
SQL中的算术表达式
工作中可能需要修改数据的显示方式，如执行计算、假定推测等等，这些都可能
用到算术表达式。
一个算术表达式可以包括列名、固定的数字值、和算术运算符
。
常用的运算符：
+加-减*乘/除
我们可以对NUMBER、DATE、TIMESTAMP使用加、减的操作，如：
select2+department_id,department_id-2,department_id*3,department_id/4
fromtest;
其运算优先级和数学中是一样的，先乘除后加减，“（）”里面优先执行！
如：
SQL>selectdepartment_name,2*（department_id+200-2）fromtest;
定义空值
空是一个难以获得的、未分配的、未知的，或不适用的值（相当于值被设置为
NULL）。
空和0或者和空格是不相同的，0是一个数字，而空格是一个字符。
任何
列的数据类型都可以包含为空，但是某些约束如：
NOTNULL和PRIMARYKEY可以用
来防止在列中为空！
空值无法和数字型的列和常量进行计算，但是可以和字符串
类型的列和常量进行连接。
如：
SQL>selectdepartment_id,department_name,testn-2fromtest;
SQL>selectdepartment_id,department_name,testn+2fromtest;
由上面的两个语句可以看到没有任何结果，因为空值无法和数字型的列和常量进
行计算。
但是继续看下面的SQL语句：
SQL>selectdepartment_id,department_name,testn||'test'fromtest;
定义列别名
在SELECT列表中的列名后面指定别名，列名和别名之间用空格分开。
默认情况下
，别名标题用大写字母显示。
如果别名中包含空格或者特殊字符（例如#或者&
等等），或者大小写敏感，将别名放在双引号“”中。
如：
SQL>selectdepartment_iddid,department_namednamefromtest;
SQL>selectdepartment_idasdid,department_nameasdnamefromtest;
SQL>selectdepartment_id"did",department_name"dname"fromtest;
SQL>selectdepartment_idas部门编号,department_nameas部门名称from
test;
以上几种方法均可以用！
虽然列别名用小写字母定义，但是在查询显示结果中，列标题用大写字母显示，
因为默认情况下列标题用大写字母显示。
还有注意的输出的列标题与列名的定义
完全相同。
在一个SQL语句中，一个列别名既能用在SELECT语句中也能用在ORDERBY子句中，
但是不能在WHERE子句中使用列别名。
如：
SQL>selectdepartment_iddid,department_namedname,2/testnotfrom
testorderbydid;
可正常查询
SQL>selectdepartment_iddid,department_namedname,2/testnotfrom
testorderbydidwheredid>0;
这里不能正常查询，会报错
连字运算符
主要作用：
连接列或者字符串到其它的列，构造一个字符表达式的合成列
表示方法：
||
学习目的：
进行列与列之间、列与算术表达式之间或者列与常数值之间的连接，
来创建一个字符表达式。
连字运算符两边的列被合并成一个单个的输出列。
如：
SQL>selectdepartment_id||department_nameas"depart"fromtest;
文字字符串
文字字符串是包含在SELECT列表中的一个字符串，一个数字或者一个日期，并且
不是列名或者别名。
相同的行
默认情况下除非你特别指出，否则sqlplus显示的查询结果中不限制重复的行。
如
：
SQL>select*fromnumtest;
你会看到有很多重复的行，那么如何去除重复的行呢？
很简单，在SELECT子句中
用DISTINCT关键字就能去掉相同的行，同样在DISTINCT关键字后面可以指定多个
列。
如：
SQL>selectdistinct*fromnumtest;
SQL>selectdistinctnumtfromnumtest;
SQL>selectdistinctnamefromnumtest;
条件和排序
条件：
where子句后实现
排序：
orderby子句后实现
在这之前先来查询EMPLOYEES表
SQL>selectemployee_id,last_name,job_iddepartment_idfromemployees;
如果我们需要选择出部门100的员工，如何做呢？
我们可以用where子句限制返回的行，where子句跟着from子句。
格式：
select*|{[distinct]column|expression[alias],...}fromtable
[whereecondition（s）];
如：
SQL>selectemployee_id,last_name,job_id,department_idfrom
employeeswheredepartment_id=100;
条件中使用字符串和日期
在WHERE子句中字符串和日期必须包含在单引号（‘’）中。
但是，数字常数不应
该包含在单引号中。
且所有的字符搜索是大小写敏感的。
oracle数据库以内部数字格式存储日期：
世纪、年、月、日、小时、分和秒。
默
认的日期显示是DD-MON-YYYY。
几种常见的比较条件
oracleSQL中的比较条件包含以下几种运算符号：
=等于
>大于
>=大于等于
<小于
<=小于等于
<>不等于
!
=不等于
^=不等于
between...and...在两个值之间（包含）
in（set）匹配一个任意值列表
like匹配一个字符模板
isnull是一个空值
isnotnull不是一个空值
使用比较条件：
SQL>selectlast_name,salaryfromemployeeswheresalary<=2300;
使用between条件：
SQL>selectlast_name,salaryfromemployeeswheresalarybetween3000
and5000;
between...and...实际上是由oracle服务器转变为一对and条件：
（a>=下限）and
（a<=上限），因此between...and...并没有性能上的提高，只是逻辑上的简单
使用in条件
SQL>selectemployee_id,last_name,salary,manager_idfromemployees
wheremanager_idin（100,101,201）;
在in条件中可以使用任何数据类型。
如：
SQL>selectemployee_id,manager_id,department_idfromemployeeswhere
last_namein（'hartstein','vargas'）;
如果in条件中的成员是字符或日期，它们必须放在单引号（‘’）中。
in条件同
样也是没有得到性能上的提高，只是逻辑上简单了。
使用like条件：
有些时候不知道要搜索的确切的值，这个时候可以用like条件匹配一个字符模板
的行。
oracle世界中有两个通配符（%和_）可以用来构造搜索串。
其中%表示0个
或多个字符，_表示一个字符。
如：
SQL>selectfirst_namefromemployeeswherefirst_namelike's%';
SQL>selectlast_namefromemployeeswherelast_namelike'_o%';
我们也可以使用escape标识符可以搜索实际的%和_符号，比如你想要搜索包含
‘SA_’的字符串，可以使用如下命令：
SQL>selectemployee_id,last_name,job_idfromemployeeswherejob_id
like'%SA\_%AN'escape'\';
而如果你想要搜索包含%SA的字符串，可以使用下面的命令：
SQL>select*fromtestwheredepartment_namelike'\%SA%'escape'\';
escape选项指定反斜线（\）为换码符，在SQL命令中，换码符字符在下划线和百
分号的前面，原因是oracle服务器逐字解释字符串。
使用NULL/NOTNULL条件
SQL>selectlast_name,manager_idfromemployeeswheremanager_idis
null；
SQL>selectlast_name,job_id,commission_pctfromemployeeswhere
commission_pctisnotnullandrownum<10;
逻辑条件
逻辑条件用于组合两个比较条件的结果来产生一个基于这些条件的单个结果，或
者逆转一个单个条件的结果。
SQL有三个逻辑运算符，分别是：
andornot如：
SQL>selectemployee_id,last_name,job_id,salaryfromemployeeswhere
salary>=10000andjob_idlike'%MAN%';
SQL>selectemployee_id,last_name,job_id,salaryfromemployeeswhere
salary>=10000orjob_idlike'%MAN%';
SQL>selectlast_name,job_idfromemployeeswherejob_idnotin
（'IT_PROG','ST_CLERK','SA_REP'）;
SQL>selectlast_name,job_idfromemployeeswherejob_idnotlike'%
A%';
同样not运算符也可以用于另一个SQL运算符，如：
inbetweennull
.......wherejob_idnotin（'AC_ACCOUNT','AD_VP'）
.......wheresalarynotbetween10000and15000
.......wherecommission_pctisnotnull
优先规则
优先规则定义表达式求值和计算的顺序，默认情况下SQL的执行顺序为：
算术运算
、连字操作、比较操作、is[not]null,like,[not]in5、[not]between、not逻辑
条件、and逻辑条件、or逻辑条件。
如：
SQL>selectlast_name,job_id,salaryfromemployeeswhere
job_id='SA_REP'orjob_id='AD_PRES'andsalary>15000;
第一个条件是：
job_id是AD_PRES并且薪水高于15000
第二个条件是：
job_id是SA_REP
使用括号强制优先权：
SQL>selectlast_name,job_id,salaryfromemployeeswhere
（job_id='SA_REP'orjob_id='AD_PRES'）andsalary>15000;
排序
排序分为升序、降序
语法格式：
selectexprfromtable[wherecondition（s）][orderby{column,expr}
[asc|desc]];
orderby指定排序显示返回的行，asc以升序排列，desc以降序排列。
orderby子
句在select语句的最后（除非使用了forupdate语句）。
如：
SQL>selectlast_name,job_id,department_id,hire_datefromemployees
orderbyhire_date;
降序排列
SQL>selectlast_name,job_id,department_id,hire_datefromemployees
orderbyhire_datedesc;
默认情况下oracle系统是将查询的结果进行升序排列的
反转默认排序
SQL>selectlast_name,salaryfromemployeesorderby2desc;
用列别名进行排序
SQL>selectemployee_id,last_name,salary*12annsalfromemployees
orderbyannsal;
多列排序
SQL>selectlast_name,department_id,salaryfromemployeesorderby
department_id,salarydesc;
oraclesql单行函数
函数是SQL的一个非常强有力的特性，函数能够用于：
执行数据计算、修改单个数
据项、操纵输出进行行分组、格式化显示的日期和数字、转换列数据类型。
SQL函
数分为单行函数和多行函数。
单行函数：
这些函数仅对单个行进行运算，并且每行返回一个结果，其中单行函
数还有很多种类型，比如：
字符、数字、日期、转换
多行函数：
这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被
称为组函数。
单行函数的特点：
只对一行值进行操作，如有多行值，将分别对多行值进行操作
，而不会将多行值作为一个整体进行操作。
语法格式：
function_name（arg1,arg2,...）
function_name---是函数的名字
arg1,arg2---是由函数使用的任意参数，可以由一个列名或者一个表达式提供
常用的单行函数有：
字符函数
数字函数
日期函数
转换函数
通用函数
字符函数：
字符函数包括：
大小写处理函数、字符处理函数
大小写处理函数：
LOWERUPPERINITCAP
字符处理函数：
CONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE
LOWER：
将指定字符串内字符变为小写如：
selectlower（'whatisthis'）from
dual;
UPPER：
将指定字符串内字符变为大写如：
selectupper（'whatisthis'）from
dual;
INITCAP：
将字符串中单词的第一个字母转换为大写，其它则转换为小写
如：
selectinitcap（'whatisthis'）fromdual;
CONCAT：
连接字符串，等同于||如：
SQL>selectconcat（'aa','bb'）fromdual;
CONC
----
aabb
SUBSTR：
截取指定长度的字符串。
n1---开始长度
n2---截取的字符串长度，如果为空，默认截取到字符串结尾
注意：
如果n1为0的时候，则从第一个字符算起。
同时当n1>0的时候，oracle默认是从左向右确认起始位置进行截取。
如：
SQL>selectsubstr（'whatisthis',5,3）fromdual;
SUB
---
is
但是如果n1<0的时候，oracle默认是从右向左确认起始位置进行截取。
如：
SQL>selectsubstr（'whatisthis',-6,3）fromdual;
SUB
---
st
当n1>c1.length的时候则返回为空。
LENGTH：
返回指定字符串的长度。
如：
SQL>selectlength（'1234567'）fromdual;
LENGTH（'1234567'）
-----------------
7
INSTR：
至于INSTR函数的作用看如下命令
SQL>selectinstr（'abcdefg','e',-3）fromdual;
INSTR（'ABCDEFG','E',-3）
-----------------------
5
格式：
instr（c1,c2[,n1[,n2]]）
作用：
返回c2在c1中的位置。
其中c1是原字符串，c2是要寻找的字符串，n1是查
询起始位置（正值表示从
左到右、负值表
升级会员 