地球上最全的SQL命令 珍藏版Word格式文档下载.docx
《地球上最全的SQL命令 珍藏版Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《地球上最全的SQL命令 珍藏版Word格式文档下载.docx(53页珍藏版)》请在冰豆网上搜索。
五、基本语法
!
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
---------------------------------------------------------
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'
通用性好
效率高,平时用的时
候尽量写这种,当实在不知道数据是什么样时才用上面的。
转大写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'
截取日期trunc(date,'
年或月或日或时分秒'
)
selectnext_day(sysdate,2)fromdual;
selecttrunc(add_months(sysdate,1),'
month'
练习:
返回下个月的第一天的日期
selectround(last_day(sysdate),'
selecttrunc(last_day(sysdate)+1)fromdual;
selectadd_months(trunc(sysdate,'
),1)fromdual;
selecttrunc(add_months(sysdate,1),'
4.不同数据类型间转换函数
将日期转成字符to_char(date,'
日期格式'
)
日期格式要用有效格式,格式大小写敏感'
(完整的日期可格式)
year'
(全拼的年),'
mm'
(2位数字表示的月)'
(全拼的月),'
dy'
(3位字母表示的星期),'
day'
(星期
的全拼),'
ddspth'
(日期的序数词)
显示完整的系统日期
selectto_char(sysdate,'
)fromdual;
显示42号部门员工的入职日期:
selectfirst_name,to_char(start_date,'
froms_empwheredept_id=42;
找出3月份入职的员工:
selectfirst_name,start_datefroms_empwhereto_char
(start_date,'
)='
03'
查询3号入职的员工
selectfirst_name,start_datefroms_empwhereto_char(start_date,'
dd'
fmdd'
3'
fm'
是去掉前
导0
创建表:
createtablehiloo(c1date);
向表中插入日期(hiloo是表名)
insertintohiloovalues('
01-JAN-07'
);
insertintohiloovalues(to_date('
2007010110:
10:
10'
));
将字符转换成数字to_number('
),缺省是按十进制来算,字符串只能是0---9的数字
selectto_number('
ab'
xx'
把ab转成十六进制
将数字转字符to_char(number,'
fmt'
)fmt是数字格式
selectto_char(salary,'
$99,999.99'
)froms_emp;
$00,000.00'
##########表示越界,大于显示宽度
L00,000.00'
将字符串转成日期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