地球上最全的SQL命令 珍藏版.docx
《地球上最全的SQL命令 珍藏版.docx》由会员分享，可在线阅读，更多相关《地球上最全的SQL命令 珍藏版.docx（53页珍藏版）》请在冰豆网上搜索。
地球上最全的SQL命令珍藏版
OracleSQLday1
OracleSQL（Oracle9i9.2.0.1.0）
一、DataBase
保存数据，以表的形式表现数据
二、SQL
SQL（structurequerylanguage结构化查询语言），是操作关系型数据库中的对象
DDL（datadefinitionlanguage数据定义语言），用于建表或删表操作，以及对表约束进行修改。
createtable,altertable,droptable对表结构的增、删、改操作。
DML（Datamanipulationlanguage数据操作语言），向表中插入纪录，修改纪录
insert,update,delete
transaction,事务控制语言，由DML语句组成的，commit;,rollback，savepoint
select查询语句
dcl数据控制语言，授权语句grant,revoke
altersessionaltersystem
三、Oracle
DB（database）
DBMS数据库管理系统（databasemanagementsystem）
DBA数据库管理员
数据库管理系统有：
ms微软的sqlserver,
IBM的db2，imformix,
mysql,
oracle的oracle920,
sybase的ase
四、相关操作
echo$PATH配置环境变量，加/oracledata/oracle/product/9.2.0/bin,把oracle命令加上
echo$ORACLE_SID查看Oracle数据库的实例，实例是访问数据库的方法。
缺省端口号是：
1521
1、sqlplus访问数据库命令（本地访问/远程访问），和数据库建立连接的命令，是数据库操作的环境
sqlplus用户名/密码
2、showuser显示当前用户的用户名
改变身份可以直接connect用户名/密码---这个是sqlplus命令
在sqlplus中可以使用!
可以在shell和sqlplus间切换，!
shell命令可以在sqlplus中使用shell命令
。
实际上是sqlplus开了子进程来执行shell命令。
3、Oracle数据库中的表分两类：
用户表（用户使用操作的表），系统表（数据库系统维护的表，也叫数据
字典）
对用户表的DDL操作触发了对系统表的DML操作！
五、基本语法
!
!
!
1、select查询语句
对一个表进行查询操作首先要熟悉表结构。
selecttable_namefromuser_tables;（查询系统表）
以上的查询语句就是查询本用户下所拥有的所有表的表名。
投影操作，只查看选择的字段的信息。
select
选择操作，查看字段中的特定某些信息。
where
联接操作，多表查询，通过表间连接，查寻出多表中的信息
（1）selecttable_namefromuser_tables;（查询系统表）
以上的查询语句就是查询本用户下所拥有的所有表的表名。
（2）sqlplus的buffer中会缓存最后一条sql语句，可以使用"/"来执行这最后一条sql语句，也可以使用
edit命令来编辑最后一条sql语句。
l命令（list）（sqlplus命令）可以显示buffer中最后一条命令。
（3）desc[表名]
这是一条sqlplus命令，注意他不是sql语句，这条命令用于查看表的结构。
describe的缩写
[字段名][字段的类型]，这是使用完desc命令后显示的表结构。
例：
descs_emp员工表
显示结果：
NameNullType
------------------------------------------------------------------
IDNOTNULLNUMBER（7）
LAST_NAMENOTNULLVARCHAR2（25）
FIRST_NAMEVARCHAR2（25）
USERIDVARCHAR2（8）
START_DATEDATE
COMMENTSVARCHAR2（255）
MANAGER_IDNUMBER（7）
TITLEVARCHAR2（25）
DEPT_IDNUMBER（7）
SALARYNUMBER（11,2）
COMMISSION_PCTNUMBER（4,2）
Name是字段名，Null是此字段是否允许为空，notnull是不允许为空。
Type是类型，number是数字类型、varchar2是字符类型，date是日期类型。
表中字段的含义：
IDID
LAST_NAME姓
FIRST_NAME名
START_DATE入职日期
COMMENTS说明
MANAGER_ID经理ID
TITLE职务
DEPT_ID部门
SALARY月薪
COMMISSION_PCT提成
descs_dept部门表
NameNull?
Type
---------------------------------------------------------
IDNOTNULLNUMBER（7）
NAMENOTNULLVARCHAR2（25）
REGION_ID（地区号）NUMBER（7）
descs_region地区表
（4）select[表的字段名1],[表的字段名2],...from表名;
select*from表名;查询表中所有字段的信息
（5）关键字不能拆分，sql语句，以及表名，字段名是大小写不敏感的。
sql语句要以";"结尾，来表示sql语句结束，如果不加";"系统不会执行此条sql语句，并提示。
在Oracle中字符显示是左对齐，数值右对齐。
（6）在select语句中可以使用数学表达式。
select[表达式（必须包含本表字段名）],[...],....from表名；
运算的优先级的先乘除后加减，同级自左向右运算，括号改变优先级。
selectfirst_name,salary*12froms_emp;找出每个人的年薪
（7）别名
select[字段名或表达式]["别名"]，[...]["..."],....from表名；
可以通过在字段名或表达式后加空格"别名"，可以给列，或者表达式结果其别名。
表达式别名若有空格必须加双引号。
selectfirst_name,salary*12annual_salaryfroms_emp;
（8）字符串拼接使用||符号
select目标字段名||''||目标字段名from表名；
注意：
在Oracle中的字符或字符串要用单引号，双引号用来起别名
别名中需要使用空格，或是大小写敏感时需要用".."包含。
练习：
自己写一条SQL语句，执行的结果是select*from...;
其中...是每张表的表名
即在每张表的表名前加“select*from”，后加“;”
select'select*from'||table_name||';'fromuser_tables;
2、处理错误
执行查询时
无效标识一般表示字段名错误
表或视图不存在表示表名错误
（1）!
oerrora[错误号]，系统可以显示错误的原因和如何修改。
如果命令错误输入可以使用edit或ed来修改输
入错误。
实际上是在编辑缓存文件中的最后一条sql语句。
也可以使用（change）c/错误字段/正确字段，来进行替换操作进行修改。
只有在Linux平台使用
!
相当于host，没有断连接，只是切换了一下，执行shell命令
（2）edit命令来编辑最后一条sql语句。
3、sqlplus设置
setpauseon回车响应，分屏显示，只在本会话中有效
setpauseoff关闭分屏显示。
setpause"..."设置分屏显示的提示信息。
setpauseon先输出提示信息，回车响应，分屏显示
setheadoff提头输出关闭
setfeedoff结尾输出关闭
setechooff回写关闭
spool文件名.sql写入指定文件
spooloff关闭写入。
4、sql脚本
也就是在文件中写有sql语句的文件，可以在sqlplus中运行。
引入sql脚本
sqlplus用户名/密码@sql脚本（注意：
在用户名密码输入结束后一定要加空格然后再写@sql脚本）
在脚本中最后一行写上“exit”，则运行完脚本以后，回到shell上
5、Oracle中的空值
空值会当无穷大处理，其实空值根本就不会存储，只是看作是无穷大。
Oracle中空值处理函数NVL（字段名，值），如果这个字段为空，替换为第二个参数的指定值，如果不
为空，则会返回其原值。
例：
selectfirst_name,salary*12*（1+NVL（commission_pct,0）/100）"totalsalary"froms_emp;查询每
个员工一年的总收入
6、distinct关键字，去掉重复行（这个关键字会触发排序操作）
例：
selectdistinctdept_idfroms_emp;
查询员工都分配在哪些不同的部门
selectdistinctdept_id,titlefroms_emp;查询部门都有哪些不同的职位
distinct可以使dept_id与title的联合唯一
注意：
distinct，关键字之后会对from之前的字段进行排重操作。
六、选择操作
1、orderby排序是按照存储顺序输出的，orderby后面可以跟字段名，别名，位置，也可以有多个字段，当第
一个字段的值一样时，按第二个字段排序
排序子句ASC（默认，升序）DESC（降序）
orderby目标列名（也可以是别名）排序顺序（不写排序顺序，会默认为升序排序）
例：
selectfirst_namefroms_emporderbyfirst_name;等价于selectfirst_namefroms_emporderby
1;此处的1表示first_name。
selectfirst_namefroms_emporderbyfirst_namedesc;
selectfirst_name,salaryfroms_emporderbysalary;按照工资的升序排序
selectfirst_name,salaryfroms_emporderbysalarydesc;按照工资的降序排序
selectfirst_name,salary,commission_pctfroms_emporderbysalary,commission_pct;先按工资
排，工资一样的再按提成排序，升序显示注意：
升序空值在结果的末尾，降序空值在结果的前面。
2、where子句（选择操作）
where子句使用在select...from...后面，用来选择所需（符合条件的）的记录
例：
selectfirst_name,salaryfroms_empwheresalary>1500;找出工资比1500高的员工
此处不能用where2>1500
查询Carmen的工资：
selectfirst_name,salaryfroms_empwherefirst_name='Carmen';
注意:
字符需要加引号，数字不需要。
引号之内的内容是大小写敏感的
where后面跟的是表达式也就是XXX=XXX，XXXbetweenXandX，XXXin（X，X，X）
between...and...,表示结果在这之间，betweenand是一个闭区间，也就相当于...<=...and...>=
...。
找出工资在1500到2000之间的：
selectfirst_name,salaryfroms_empwheresalarybetween1500
and2000;
等价于：
selectfirst_name,salaryfroms_empwheresalary>=1500andsalary<=2000;
!
=，<>，^=，这三个都标识不等于，<=，>=，=，这些运算符都可以使用。
...in（va1,val2,...）判断结果是否在这个集合中存在，等于其中随变一个就行了，可以说in=any
in中集合的数据是有顺序的，概率高的数据放在前面可提高效率
查找这三个部门里任意一个部门的员工
selectfirst_namefroms_empwheredept_idin（41,42,32）;等价于
selectfirst_namefroms_empwheredept_id=any（41,42,32）;
应该把出现频率较高的数据放在集合的前面会提高效率
notin<>all没有顺序，notin不等于all
空值会对notin造成影响，也就是不等于任何值，但是空值例外。
like'...'字符串通配查询，'%'表示0或任意多个字符，'_'，表示任意一个字符。
注意：
转义的用法：
like‘S\_%’escape‘\’
表名都是以大写存储的
例：
找出表名是以S_开头的表
selecttable_namefromuser_tableswheretable_namelike'S\_%'escape'\';把\定义为转
义字符
...isnull使用来判断值是否为空。
isnotnull判断值不为空
例：
查看哪些人没有提成
selectfirst_namefroms_empwherecommission_pctisnull;
找出42部门每个员工的年薪并且按年薪从大到小的顺序显示：
selectfirst_name,salary*12*（1+nvl（commission_pct,0）/100）total_salaryfrom
s_empwheredept_id=42orderby2desc;
...and...表示只有两个条件同时满足
...or...表示条件只要满足其中只一就可以
all...是要求都满足条件。
注意：
Oracle中的字符串是严格区分大小写的。
（1）注意数据类型，数字类型直接写，字符用'......'，缺省格式的Date可以用'......'，只有别名才用""包
含。
（2）选择合适的运算符
2、单行函数（一个输入，一个输出）
多行函数又叫组函数（多个输入，一个输出）例平均值
1.字符函数
字符是大小写敏感的
转小写lower（字段名）---其中的参数可以是一个字符串常量或是一个字段名
例：
selectlower（first_name）froms_emp;把每一个字符都变成小写。
selectlower（'SqlCourse'）fromdual;这个dual表可以用来测试。
selectfirst_name,salaryfroms_empwherelower（first_name）='carmen';通用性好
selectfirst_name,salaryfroms_empwherefirst_name='Carmen';效率高，平时用的时
候尽量写这种，当实在不知道数据是什么样时才用上面的。
转大写upper（字段名）
首字母大写initcap（字段名）
字符串拼接concat（字段1,字段2）
截取子串substr（字段名,起始位置，取字符个数）
返回字符串长度length（字符串）
dual表，是专门用于函数测试和运算的，他只有一条记录
可以使用"-"表示从右向左数，取的时候可以从左往右取。
例：
selectfirst_name,substr（first_name,length（first_name）-1,2）froms_emp;
selectsubstr（first_name,-2,2）subfroms_emp;（取后两个）
selectsubstr（first_name,2,2）subfroms_emp;（取前两个）
nvl是一般单行函数，支持任何数据类型
2.数值函数
四舍五入round（数据,保留小数点后几位）
可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1表示个位（保留到十位）。
例：
selectround（15.36,1）fromdual;
截取数字函数trunc（数据，保留的位数（小数点后位数））截取个位之后补0
例：
selecttrunc（123.456,1）fromdual;
这个网上有函数的参考文档
3.日期函数
日期格式，
全日期格式世纪信息，年月日，时分秒。
缺省日期格式，日-月-年dd-mon-rr
修改当前会话的日期格式，会按照指定的格式输出日期
altersessionsetnls_date_format='yyyymmddhh24:
mi:
ss';
返回当前日期sysdate
例：
selectsysdatefromdual;显示今天的日期
selectsysdate+1fromdual;获得明天的日期，加1，单位是天
例:
10分钟以后的时间selectsysdate,sysdate+1/144fromdual;
日期是格式敏感的
求两个日期间相隔了多少个月months_between（date1,date2）
加减指定数量的月份add_months（date,月数），月数可以为负，负值就是减去相应的月数。
从date日期开始的第一个星期五next_day（date,FriDay）
返回月末的日期last_day（date）
round不能使用缺省的日期格式
例：
selectround（to_date（'02-JAN-07','DD-MM-RR'）,'MONTH'）fromdual;
截取日期trunc（date,'年或月或日或时分秒'）
例：
selectnext_day（sysdate,2）fromdual;
例：
selecttrunc（add_months（sysdate,1）,'month'）fromdual;练习：
返回下个月的第一天的日期
selectround（last_day（sysdate）,'MONTH'）fromdual;
selecttrunc（last_day（sysdate）+1）fromdual;
selectadd_months（trunc（sysdate,'MONTH'）,1）fromdual;
selecttrunc（add_months（sysdate,1）,'MONTH'）fromdual;
4.不同数据类型间转换函数
将日期转成字符to_char（date,'日期格式'）
日期格式要用有效格式，格式大小写敏感'yyyymmddhh24:
mi:
ss'（完整的日期可格式）
year'（全拼的年）,'mm'（2位数字表示的月）'month'（全拼的月），'dy'（3位字母表示的星期），'day'（星期
的全拼），'ddspth'（日期的序数词）
例：
显示完整的系统日期
selectto_char（sysdate,'yyyymmddhh24:
mi:
ss'）fromdual;
显示42号部门员工的入职日期：
selectfirst_name,to_char（start_date,'yyyymmddhh24:
mi:
ss'）
froms_empwheredept_id=42;
找出3月份入职的员工：
selectfirst_name,start_datefroms_empwhereto_char
（start_date,'mm'）='03';
查询3号入职的员工
selectfirst_name,start_datefroms_empwhereto_char（start_date,'dd'）='03';
selectfirst_name,start_datefroms_empwhereto_char（start_date,'fmdd'）='3';'fm'是去掉前
导0
创建表：
createtablehiloo（c1date）;
向表中插入日期（hiloo是表名）
insertintohiloovalues（'01-JAN-07'）;
insertintohiloovalues（to_date（'2007010110:
10:
10','yyyymmddhh24:
mi:
ss'））;
将字符转换成数字to_number（'...'）,缺省是按十进制来算，字符串只能是0---9的数字
selectto_number（'ab','xx'）fromdual;把ab转成十六进制
将数字转字符to_char（number，'fmt'）fmt是数字格式
selectto_char（salary,'$99,999.99'）froms_emp;
selectto_char（salary,'$00,000.00'）froms_emp;##########表示越界，大于显示宽度
selectto_char（salary,'L00,000.00'）froms_emp;
将字符串转成日期to_date（'...','日期格式'）
例：
selectto_char（to_date（'20061103','yyyymmdd'）,'dd-month-yy'）fromdual；
5、对字段的显示格式化
column命令---sqlplus命令
column命令列格式的定义
columnlast_nameclear;清除已定义的格式
column目标列名查看这个类是否定义了格式
column目标列名formatA..设置列宽。
columnlast_nameheading'Employee|Name'FORMATA15
升级会员 