MySQL数据库工程师入门实战课程视频教程
4167 人在学
1.desctable_name可以查询表的结构
2.怎么获取有哪些用户在使用数据库
selectusernamefromv$session;
3.如何在Oracle服务器上通过SQLPLUS查看本机IP地址?
selectsys_context('userenv','ip_address')fromdual;
如果是登陆本机数据库,只能返回127.0.0.1
4.如何给表、列加注释?
SQL>commentontable表is'表注释';
注释已创建
SQL>commentoncolumn表.列is'列注释';
注释已创建。
SQL>select*fromuser_tab_commentswherecommentsisnotnull;
5.如何在ORACLE中取毫秒?
selectsystimestampfromdual;
6.如何在字符串里加回车?
添加一个||chr(10)
select'Welcometovisit'||chr(10)||'www.CSDN.NET'fromdual;
7.怎样修改oracel数据库的默认日期?
altersessionsetnls_date_format='yyyymmddhh24miss';
8.怎么可以看到数据库有多少个tablespace?
select*fromdba_tablespaces;
9.如何显示当前连接用户?
SHOWUSER
10.如何测试SQL语句执行所用的时间?
SQL>settimingon;
11.怎么把select出来的结果导到一个文本文件中?
SQL>SPOOLF:\\ABCD.TXT;
SQL>select*fromtable;
SQL>spooloff;
12.如何在sqlplus下改变字段大小?
altertabletable_namemodify(field_namevarchar2(100));
改大行,改小不行(除非都是空的)
13.如果修改表名?
altertableold_table_namerenametonew_table_name;
14.如何搜索出前N条记录?(desc降序)
SELECT*FROMTablenameWHEREROWNUM<n
ORDERBYcolumn;
15.如何在给现有的日期加上2年?
selectadd_months(sysdate,24)fromdual;
16.Connectstring是指什么?
应该是tnsnames.ora中的服务名后面的内容
17.返回大于等于N的最小整数值?
SELECTCEIL(-10.102)FROMDUAL;
18.返回小于等于N的最大整数值?
SELECTFLOOR(2.3)FROMDUAL;
19.返回行的物理地址
SELECTROWID,enameFROMtablenameWHEREdeptno=20;
20.将N秒转换为时分秒格式?
setserverouton
declare
Nnumber:=1000000;
retvarchar2(100);
begin
ret:=trunc(n/3600)||'小时'||to_char(to_date(mod(n,3600),'sssss'),'fmmi"分"ss"秒"');
dbms_output.put_line(ret);
end;
21.如何监控当前数据库谁在运行什么SQL语句?
SELECTosuser,username,sql_textfromv$sessiona,v$sqltextb
wherea.sql_address=b.addressorderbyaddress,piece;
22.如何知道当前用户的ID号?
SQL>SHOWUSER;
OR
SQL>selectuserfromdual;
23.如何知道使用CPU多的用户session?
11是cpuusedbythissession
selecta.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100value
fromv$sessiona,v$processb,v$sesstatc
wherec.statistic#=11andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;
24.Oracle建立表空间和用户
建立表空间和用户的步骤:
用户
建立:createuser用户名identifiedby"密码";
授权:grantcreatesessionto用户名;
grantcreatetableto用户名;
grantcreatetablespaceto用户名;
grantcreateviewto用户名;
表空间
建立表空间(一般建N个存数据的表空间和一个索引空间):
createtablespace表空间名
datafile'路径(要先建好路径)\\***.dbf'size*M
tempfile'路径\\***.dbf'size*M
autoextendon--自动增长
--还有一些定义大小的命令,看需要
defaultstorage(
initial100K,
next100k,
);
用户权限
授予用户使用表空间的权限:
alteruser用户名quotaunlimitedon表空间;
或alteruser用户名quota*Mon表空间;
createtablespacezqdatafile'D:\\zq\\zw.dbf'SIZE1000MAUTOALLOCATE;
修改用户的默认表空间
alteruserusernamedefaulttablespacetablespacename;
25.在sqlplus中清屏命令:clearsrcclearscreen;clscr;
怎样用语句查询表空间里面表的内容?
selecttable_namefromall_tableswheretablespace_name='zq';
selecttable_namefromuser_tableswheretablespace_name='xx'
26.如何查询表在哪个表空间中?(单引号里面的要大写)
SELECTtablespace_nameFROMUSER_TABLESWHEREtable_name='YOUR_TABLENAME'
查一下,这个表是哪个用户下的,如果是本用户则可以用上面的sql
如果是别的用户的表你就用
SELECTtablespace_nameFROMDBA_TABLESWHEREtable_name='YOUR_TABLENAME'andowner='表的OWNER'
还有你要确定你查的确实是一个表而不是view或SYNONYM
而且在引号里面的表名和owner都要用大写字母
27.表的创建
createtableaa
(avarchar2(10),
bnumber(8,2),
cdate
)tablespaceusers;
如果在创建用户时没有指定默认表空间,系统默认表空间为System,在创建表时必须指定tablespace;
28.如何查询一个表空间下的所有表(单引号里面的要大写)
selecttable_namefromuser_tableswheretablespace_name='表空间名';
29.更改计算机名后会出现OracleORA-12541:TNS:nolistener错误解决方法
D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN\\listener.ora
修改为现在的计算机名,再次启动OracleOraHome90TNSListener服务成功
30.创建表时默认表空间是SYSAUX
31.oracle10gemDatabaseControl的启动问题修复
打开http://localhost:1158/em/显示数据库状态没有启动,提示用户登录错误ORA-28000:theaccountislocked,使用PL/SQL或SQL*plus
连接是正常的。到网上搜索一番,没有找到确切的原因。其中一个可能的原因是用户DBSNMP的密码和sys用户的密码不一致,导致
OracleDBConsoleSID服务错误,网上很多朋友说使用emca(EMConfigurationAssistant)工具进行修复。
在命令行里键入下面两个命令就可以修复数据库的em
emca-reposrecreate
emca-configdbcontroldb
另外,网上很多朋友给出“emca-r”这样的命令,我试了之后发现10g版本的命令格式已经有所变化,具体的命令格式可以通过命令
的帮助获得。在命令行中键入
emcahelp=y
可以查看详细的命令格式。
常用的命令语法:
emca-reposcreate创建一个EM资料库
emca-reposrecreate重建一个EM资料库
emca-reposdrop删除一个EM资料库
emca-configdbcontroldb配置数据库的DatabaseControl
emca-deconfigdbcontroldb删除数据库的DatabaseControl配置
emca-reconfigports重新配置dbcontrol的端口,默认端口在1158
emctlstartconsole启动EMconsole服务,使用前需要先设置ORACLE_SID环境变量
emctlstopconsole停止EMconsole服务,使用前需要先设置ORACLE_SID环境变量
32.解决启动Oracle9i的OEM或OMS的常见问题(VTK-1000)
能否正常启动OEM或OMS关键有以下两点:
第一.Oracle的系统服务是否开启;
第二.登录时用的用户名和口令是否正确。
那么先针对第一点谈谈Oracle的系统服务。在完全安装的情况下,Oracle的系统服务共有11项:
1.OracleOLAP9.0.1.0.1
2.OracleOLAPAgent
3.OracleOraHome90Agent
4.OracleOraHome90ClientCache
5.OracleOraHome90HTTPServer
6.OracleOraHome90ManagementServer(0.5M)
7.OracleOraHome90PagingServer
8.OracleOraHome90SNMPPeerEncapsulator
9.OracleOraHome90SNMPPeerMasterAgent
10.OracleOraHome90TNSListener(5.2M)
11.OracleServiceORACLE(70M)
(注:OraHome90是可以在安装时改变的Oracle的主目录名称,是安装时的默认值)
其中最重要的服务有3个,分别是OracleOraHome90ManagementServer、OracleOraHome90TNSListener与
OracleServiceORACLE。下面就来看一下有哪些启动错误与它们有关。
1.Oracle系统提示:Ora-12541:TNS:没有监听器;
2.操作系统提示:在本地计算机无法启动OMS服务
错误:1053:服务并未及时响应来控制请求附带;
以上两种错误提示大都是由OracleOraHome90TNSListener监听服务引起的。
解决方法:控制面版->管理工具->服务->右键单击“OracleOraHome90TNSListener”,再单击“启动”。
3.Oracle系统提示:Ora-12500:TNS:监听程序无法启动专用服务器进程;
该错误是由OracleServiceORACLE专用服务器进程引起的。
解决方法:控制面版->管理工具->服务->右键单击“OracleServiceORACLE”,再单击“启动”。
4.Oracle系统提示:VTK-1000:无法连接到ManagementServer。
请验证您已输入OracleManagementServer的正确主机名和状态。
该错误引起的原因有两种,一是OracleOraHome90ManagementServer还没启动;二是没有输入主机名。
解决方法:控制面版->管理工具->服务->右键单击“OracleOraHome90ManagementServer”,
再单击“启动”,
或是输入您这台计算机的完整名称。
接着针对第二点谈谈登录时用的用户名和口令。
在安装结束后,系统提供了两个默认的数据库系统管理员,其用户名和口令分别是SYS/change_on_install和SYSTEM/manager,同时系统还
提供了登录OMS的用户名和口令:sysman/oem_temp。这里容易出现错误的是在登录OMS是用SYS或SYSTEM作为用户名进行登录,那么Oracle系统
就回有“登录身份证明不正确”的提示。
小结:这三个服务的启动或关闭还有先后的顺序。一般来讲,启动时必须先启动OracleOraHome90TNSListener再启动
OracleOraHome90ManagementServer或OracleServiceORACLE,在启动OracleOraHome90ManagementServer时,同时也启动了
OracleServiceORACLE。而关闭时必须先关闭OracleOraHome90ManagementServer再关闭OracleOraHome90TNSListener或OracleServiceORACLE,
关闭OracleOraHome90ManagementServer时,若有提示输入用户名和口令,请输入sysman的用户名和口令,以确保成功的执行。有些其他提示如
:资源已被占用,I/O重复,端口已被使用等等之类的话,那最好与系统管理员联系,再寻求解决办法。
1)查询数据库名:
SQL>selectnamefromv$database;
(2)查询数据库实例名:
SQL>selectinstance_namefromv$instance;
(3)查询数据库服务名:
SQL>selectvaluefromv$parameterwherename='service_names';//(小写)
(4)查询全局数据库名(sys用户):
SQL>selectvalue$fromprops$wherename='GLOBAL_DB_NAME';//字符串区分大小写
监视用户会话:
SQL>selectusername,sid,serial#,machinefromv$session;
删除用户会话:
SQL>altersystemkillsession'sid,serail#';
//使用下面的方法可以重复执行上一条SQL语句(在SQL*Plus中)
SQL>l//小写字母L,显示上一条SQL语句
1*selectusername,sid,serial#,machinefromv$session
SQL>///正斜杠:重复执行上一条SQL语句
SQL>setlinesize1000//将SQL*Plus中显示行宽设成1000个字符。
SQL>connectsystem/manager@orasjz//在SQL*Plus中直接连接到另一台机器上的数据库,@字符后是另
一台机器的tnsname
4.手工配置Oracle网络连接:主要是配置"tnsnames.ora"文件。
A.手工配置Oracle网络连接配置文件:tnsnames.ora
B.手工配置Oracle监听进程配置文件:listener.ora
UNIX下启动进程命令:
$lsnrctlstart//启动监听进程
$lsnrctlstatus//显示监听进程状态
$lsnrctlstop//停止监听进程
启动进程的命令与UNIX相同。
LISTENER=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))
)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=ORA54)(PORT=1521))
//注意:上面一行的HOST必须是你的主机名,否则监听会出问题,也可以使用你的机器的IP地址
)
)
(DESCRIPTION=
(PROTOCOL_STACK=
(PRESENTATION=GIOP)
(SESSION=RAW)
)
(ADDRESS=(PROTOCOL=TCP)(HOST=ORA54)(PORT=2481))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PLSExtProc)
(ORACLE_HOME=..........)
(PROGRAM=extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=ora54)
(ORACLE_HOME=..........)
(SID_NAME=ora54)//注意:ora54为数据库的SID名称,不能更改,否则监听出问题
)
)
5.将主机字符串(HostString)写入注册表(简化SQL*Plus的登录)
regedit.exe->HKLM->Software->Oracle->home0
增加关键字:local(字符串),键值:主机字符串名。
/*将下面的内容复制到一个.reg文件中,在Windows2000中双击执行,即可实现增加或者修改local键值的
作用
WindowsRegistryEditorVersion5.00
[HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\HOME0]
"local"="ora54"
*/
[SVRMGRL:ServerManager的使用方法]
D:\\>svrmgrl
SVRMGR>connectinternal//使用svrmgrl,进入后使用的第一个命令必须是这个命令。
口令://如果要求输入口令,请输入oracle
连接成功。
SVRMGR>selectnamefromv$database;//检查当前使用的数据库名
NAME
---------
ORA44
已选择1行。
SVRMGR>shutdownimmediate//关闭当前使用的数据库
已关闭数据库。
已卸下数据库。
已关闭ORACLE实例。
SVRMGR>startup//启动当前使用的数据库,如果无效,请使用startupforce
已启动ORACLE实例。
系统全局区域合计有24433932个字节
FixedSize70924个字节
VariableSize7507968个字节
DatabaseBuffers16777216个字节
RedoBuffers77824个字节
ORA-00205:?????????????????????
SVRMGR>
[另一个启动oracle数据库的例子]
d:\\>sqlplusinternal/oracle
SQL>startupforce//强行重新启动数据库。
[修改口令字]
SQL>grantconnecttosystemidentifiedbyNewPassword;//如果用数字作口令,需要使用双引号括起
来
SQL>grantconnecttosysidentifiedbyNewPassword;
SQL>alterusersystemidentifiedbyNewPassword;
SQL>alterusersysidentifiedbyNewPassword;
SQL>password//需要输入原口令
//注:以上修改口令的方法等价;sys与system用户可以互相修改口令;如果sys与system用户的口令都忘
记了,使用如下方法:
D:\\>svrmgrl
SVRMGR>connectinternal/oracle
连接成功。
SVRMGR>grantconnecttosystemidentifiedbymanager;
语句已处理。
SVRMGR>exit
服务器管理程序结束。
、Oracle产品组成
查询数据库选件产品:
SQL>select*fromv$option;
一般都是True,如果是False,可以双击激活。
/*cartridges(小产品的)插件,(大产品的)选件(options)*/
[SYS用户是Oracle数据库中权限最大的用户。]
[SQL*Plus登录方法]
1.c:\\>sqlplus"/assysdba"
2.c:\\>sqlplusinternal
//注意:以上两种方法可以类似的使用于SQL*Plus的图形登录界面中
//用这种登录方法登录进去,所使用的用户均为SYS。
[之所以会出现这种登录方法,是因为在NT的用户组中存在一个ORA_DBA的本地组,凡在此组中的用户使用
操作系统认证,即在此组中的用户登录数据库时不需要密码。]
[orapwd命令:修改internal用户的口令字]
/*orapwd的命令行参数
D:\\>orapwd
Usage:orapwdfile=password=entries=
where
file-nameofpasswordfile(mand),
password-passwordforSYSandINTERNAL(mand),
entries-maximumnumberofdistinctDBAandOPERs(opt),
Therearenospacesaroundtheequal-to(=)character.*/
修改internal口令字(internal默认口令为oracle)认证方法(AB两个步骤):
A.修改Oracle登录认证方法:
修改文件SQLNET.ORA文件。
SQLNET.AUTHENTICATION_SERVICES=(NTS)//将这一行前面加上#号注释掉,即可将Oracle的认证方法由操作
系统认证改为Oracle认证
SQLNET.ORA文件的位置:
UNIX:/u01/app/oracle/product/8.1.6/network/admin/sqlnet.ora
WindowsNT/2000:d:\\oracle\\ora81\\network\\admin\\sqlnet.ora
B.修改Internal口令字:
WindowsNT/2000:
C:\\>ORAPWDfile=d:\\oracle\\ora81\\database\\pwdora8i.ora
password=YourPassword//YourPassword为你要设置的密码
entries=30
/*ORAPWDfile=d:\\oracle\\ora81\\database\\pwdora54.orapassword=qeventries=30*/
UNIX:
$orapwdfile=$ORACLE_HOME/dbs/orapwSID
password=YourPassword
entries=30
然后重新启动Oracle服务(服务->OracleServiceHOSTNAME)。
/*在执行上述命令之前,需要先将相应目录的pwdora8i.ora(或orapwSID)文件删除或者改名,因为口令字
文件不能重名。*/
[tkprof跟踪文件整理工具]
$tkprofx.trcx.txt
Windows2000/NT:
d:\\oracle\\admin\\db_name\\udump\\*.trc
UNIX:/u01/app/oracle/amdin/db_name/udump/*.trc//*/
Oracle数据分区技术:8.0以后开始使用
(一)LOB(LargeObject)大对象类型数据:
1.BLOB:存储二进制数据,如图象、视频、声音等,用于代替Longraw类型(Oracle7.0以前的数据类型,今
后不再支持)
2.CLOB:存储大字符,如:个人简历,用于代替long字段。
3.NCLOB:其它民族语言的支持
(1)数据库字符集NLS:
Server端:NLS_CHARACTERSET:(如果为以下的字符集,Oracle的数据库可以用来存储汉字)
ZHS16GBK(Oraclei8,Oracle8)
ZHS16CGB231280(Oracle7.3,8,8i)
此参数位置在数据字典中,查询核心字符集(语言、日期、货币等):
SQL>select*fromnls_database_parameters;
Client端:NLS_LANG=SimplifiedChinese_CHINA.ZHS16GBK(如果没有设置,默认是英文)
regedit.exe
UNIXClient中:
$NLS_LANG="simplifiedchinese"_china.zhs16gbk
$exportNLS_LANG
//一般将上述环境变量放入.profile文件中。
/***********************************************插入内容
********************************************************
(1)sys是一种用户,internal是一种方式,用来启动关闭数据库,9.0以后不再使用internal,全部是sys
。
(2)SQL>setcomv7//将8i版本暂时退回到7版
(3)SQL>altersystemsuspend;//使用internal用户执行,冻结数据
SQL>altersystemresume;//取消冻结,恢复正常
**********************************************************************************************
******************/
(2)Oracle数据库核心字符集修改方法:
[*]修改数据字典(使用SYS用户):
SQL>updateprops$setvalue$='ZHS16GBK'wherename='NLS_CHARACTERSET';
SQL>commit;
然后重新启动数据库。
4.BFILE:外部文件存储,将数据存储在服务器硬盘
5、SQL关键字(命令)
SQL关键字(以下命令为SQL命令,以分号结束)
1.Alter:修改表结构
2.Audit:审计,NoAudit(取消审计)
3.Commit:数据提交,相反的命令:Rollback(回退)
4.Comment:将oracle的注释写入数据字典
5.Create:建表、数组、索引、视图等,相反的命令:Drop
6.delete:删除表中的数据,Drop是把表中的数据包括结构全部删除
7.Grant:授权,Revoke:收权(权限回收)
8.Insert:在表中插入新行
9.Lock:将表强行锁住
10.Rename:修改表名
11.Select:数据查询
12.Update:数据更新,修改某一个列
13.Validate:校验,对数据进行校验。
6、SQL命令的输入:
在SQL>提示符后输入命令,可以输入多行,以分号结束
7、SQL*Plus关键字:以回车结束
1.@:执行外部命令,格式:@路径\\文件名
2.#:注释
3./:执行上一条命令
4.Accept:接收键盘输入的命令
5.Append(简化为a):在行尾增加字符串
SQL>at
1*select*fromdept
SQL>/
6.Break:分组,Syntax:breakon分组列skipn//n为每两组之间间隔的空行
/*例:
SQL>breakonjob
SQL>select*fromemporderbyjob;//此处务必按分组列排序
SQL>breakonjobskip1
SQL>/
*/
7.Btitle,Ttitle:设置表尾、表头
SQL>Ttitle'表头'
SQL>Btitle'表尾'
SQL>Ttitleoff//失效
8.Change:写错了改正,Syntax:C/old/new
/*例:
SQL>select*fromdetp;
select*fromdetp
SQL>c/tp/pt
1*select*fromdept
SQL>/
9.Clear:清除
SQL>clearbreak//清除前面的break设置
SQL>clearbuff//清除SQL*Plus缓冲区中的SQL语句
10.Column:列定义,用于定义列的显示格式
语法:SQL>column列名format格式
例:SQL>Columnsalformat$99.999999
11.computer:统计计算
12.connect:从一个用户退出,直接进入另一个用户
13.disconnect:中断当前用户的连接
14.copy:远程复制(用的较少,一般数据链路)
15.define:定义
16.undefine:取消定义
17.del:删除当前行
18.describe(简写desc):显示表结构
例:SQL>descemp
19.document:文档注释
20.edit(简化ed):调入外部编辑器编辑缓冲区中的SQL语句,保存后,可以直接通过“/”运行编辑过的SQL
语句
21.get:将外部文件调入不执行,只供显示
例:SQL>getc:\\sqlplus.ora
22.host:执行操作系统命令
例:SQL>hostdir
23.input(简化为I):行插入,在当前行的后面插入。
24.List(简化为L):列示
25.Pause:设置屏幕暂停
SQL>setpauseon//暂停
SQL>setpauseoff//不暂停
SQL>set'more'pauseon[???]
26.Quit:退出
27.Remark:注释
28.Run(R):类似“/”
29.Save:将已经运行过的SQL命令保存到磁盘
例:SQL>savec:\\run.sqlreplace//覆盖保存
append//追加
30.set:设置
31.show:显示环境变量的值
32.Spool:显示跟踪
例:SQL>spoolc:\\文件名
SQL>spooloff//停止记录
33.Start:类似@,执行外部的SQL文件
33.Timing:服务器端某一命令的执行时间跟踪
数据字典:描述系统信息的表、视图、同义词等,由系统自动维护。
例:SQL>select*fromtabs;
1.显示当前所登录的用户:select*fromall_users;
2.user_xxx:描述用户创建的对象,如:user_tables,user_views,user_indexes
3.dba_XXX:数据库管理员(sys,system)专用数据字典
4.all_XXX:描述用户创建的对象,其他用户授权可以存取的对象。
all_objects:全部的数据字典
1.查询全表的数据:
SQL>select*fromemp;
2.查询某(几)个列:SQL>selectename,salfromemp;
3.DISTINCT标识:只显示不相同的列
例:
SQL>selectjobfromemp;//显示结果中有相同的记录
SQL>selectdistinctjobfromemp;//只显示不相同的职业
4.使用orderby将显示结果排序:ASC升序(默认),DESC降序
5.使用where指出查询条件:
SQL>select*fromempwheresal>2000;
[字符型或日期型用单引号括起来,并区分大小写]
6.设置日期显示格式:
(1)确定日历格式:设置参数nls_calendar。
SQL>altersessionsetnls_calendar='JapaneseImperial';//设置为日本日历
SQL>altersessionsetnls_calendar='ROCofficial';//设置为台湾日历
SQL>altersessionsetnls_calendar='Gregorian';//设置为格林尼治日历,这是我们要设置成的日历。
SQL>selectsysdatefromdual;//查询系统时间
(2)确定日期格式:参数nls_date_format
常用的日期格式:
yyyy.mm.dd
yyyy-mm-dd
yyyy/mm/dd
yyyy"年"mm"月"dd"日"//如果要显示汉字,请用双引号括起来
yyyy"年"mm"月"dd"日"dy//dy表示星期
命令格式:
SQL>altersessionsetnls_date_format='格式';
[此命令只修改前端的显示,退出后即失效,要想永久有效,要修改注册表,在注册表中
HKLM->Software->Oracle->Home0增加字符串关键字nls_date_format,键值为日期格式,此处的格式不需要
用单引号括起来。]
例:SQL>altersessionsetnls_date_format='yyyy"年"mm"月"dd"日"dy';
7.Oracle登录自动执行文件glogin.sql:每次用户登录时自动执行该文件,可以将一些环境变量(如
linesize)的设置命令或者其它命令放入该文件中。
glogin.sql文件路径:
Windows下:d:\\oracle\\ora81\\sqlplus\\admin\\glogin.sql
UNIX:/u01/app/oracle/product/8.1.6/sqlplus/admin/glogin.sql
二、运算符与谓词
1、算术运算符:+,-,*,/
SQL>selectsal,sal*12fromemp;//计算工资及年薪。
SQL>selectsal,sal+commfromemp;//错误语句,因为comm中存在空值,会使运算结果也会出现空值。
SQL>selectsal,sal+NVL(comm,0)fromemp;//正确语句,NVL为空值运算函数,当comm为空值时,
该函数返回第二个值0。
2、逻辑运算符:NOT,AND,OR,三个运算符的优先级别依次降低。
3、比较运算符:>,<,=,>=,<=,!=
4、谓词:
(1)IN(或NOTIN):等于(不等于)列表中的任意值。
SQL>select*fromempwherejobin('MANAGER','CLERK')
(2)(NOT)BETWEENAND:表示从小到大的一个范围。[必须是从小到大]
SQL>select*fromempwheresalnotbetween2000and3000;
(3)LIKE:模式匹配
SQL>select*fromempwhereenamelike'S%';//寻找ename为S打头的记录
%:表示任意字符串
_(下划线):表示一个任意字符
SQL>select*fromempwhereenamelike'李%';//可以使用中文
SQL>select*fromempwhereenamelike'S_I%';//寻找ename为S打头,第三个字母为I的记录
SQL>selectobject_namefromall_objectswhereobject_namelike'DBA%';//搜索数据字典中与dba
有关系的对象
(4)(NOT)NULL(空值):
SQL>select*fromempwherecommisNULL;//查询哪些人没有资金。
5.伪列:
(1)rowid:唯一行标识,行被删除之后,rowid不变。
(2)rownum:行号,一行被删除之后,后面的行号会随之改变
SQL>selectempno,ename,rowid,rownumfromemp;
[查询SQL语句的相关数据字典]
(1)SQL>selectsql_textfromv$sqlarea;//查询以前使用过的SQL语句
(2)查询当前连接用户的SQL语句:
SQL>selectuser_name,sql_textfromv$open_cursor;//需要用sys或system用户执行
三、列名别名
SQL>selectename,salasSalaryfromemp;
SQL>selectename职工姓名,sal工资fromemp;
*#/\\select都不允许做别名,如果一定要用,用双引号括起来。
数据操纵语言(DML)
一、数据插入:
1.对于表中全部列插入
语法:SQL>insertinto表名values(值表达式);
例:SQL>Insertintodeptvalues(51,'软件开发部','北京');
[*]用desc显示表结构、数据类型、顺序SQL>descdept//注意,不要将linesize设置的太大,否则看不
到表结构,设置成100即可。
[*]所插数据必须与目标列一致。
[*]字符与日期数据使用单引号
2.对于表中部分列插入
语法:SQL>insertinto表名(列名1,列名2...)values(值表达式);
[*]对于表中的非空列必须插入数据
SQL>insertintoemp(empno,ename,job,hiredate,deptno)values(1234,'李大力','工程师',
sysdate-30,10);
/*sys或system用户访问其它用户的表的方法
SQL>connectsystem/ab@ora44
已连接。
SQL>select*fromuser01.emp*/
/********************commit及Rollback用法:下例是Rollback的一个例子。
SQL>deletedeptwheredeptno=51;
已删除1行。
SQL>select*fromdept;
SQL>rollback;
SQL>select*fromdept;
DEPTNODNAMELOC
-------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
50COMPUTERBEIJING
51软件开发部北京
已选择6行。
*************************************************/
3.使用参数(变量),临时输入值
SQL>insertinto表名(列名1,列名2,...)values(&x1,&x2,...);
SQL>insertdept(deptno,dname,loc)values(&x1,'&x2','&x3')
SQL>insertintodeptvalues(&x1,'&x2','&x3');//在命令行中给字符或日期型参数加上单引号,则
下面输入时不再需要输入单引号
输入x1的值:53
输入x2的值:dd
输入x3的值:ee
原值1:insertintodeptvalues(&x1,'&x2','&x3')
新值1:insertintodeptvalues(53,'dd','ee')
//definex=7788
//也可以这样用:select&x1,&x2fromemp;
4.使用子查询从另一个表中复制数据
语法:Insertinto表名select子句;
SQL>inserttoemp(empno,hiredate,deptno)
selectdeptno+7300,sysdate,deptnofromdept;
SQL>inserttoemp(empno,hiredate,deptno)
selectdeptno+7300,sysdate,deptnofromdept@数据库链路名;//表示dept这个表在网络上的其它服务
器中,注意:@之后不是连接串。
SQL>createtablepayasselectename,salfromemp;//用复制的方法创建表
SQL>insertintopayselect*frompay;//自己复制自己,可以使一个表飞速扩大。
二、数据更新
语法:SQL>Update表名set列名=值表达式where条件;
例:
SQL>updateempsetsal=sal+100wheresal<2000;
SQL>updateempsetsal=5000,job='MANAGER'whereename='SMITH';
SQL>updateempsetcomm=100wherecommisnull;
三、数据删除
语法:SQL>Deletefrom表名where条件;//删除数据保留结构,可以回退
SQL>Truncatetable表名;//删除数据保留结构,不可以回退,效率高
SQL>Droptable表名;//删除数据及结构,不可以回退
数据控制语言(DCL)
一、事务提交:对于数据的插入、更新、删除,只有提交后,数据才真正改变,在提交之前,只有修改了数
据的用户才可以看到数据的改变,而其他用户看不到数据的改变。
1.显式数据提交语法:SQL>commit;
/*某一个用户对表进行更新等写操作之后,如果没有commit,那么其它用户不能对表进行更新操作,否则
就会死掉。
查锁方法:
SQL>selectusername,sid,serial#fromv$session;
解锁方法:
SQL>altersystemkillsession'sid,serial#';
**********************************************************************************************
*******/
2.隐式数据提交:
下列命令是隐式提交命令:
Create,Alter,Drop,Connect,Disconnect,Grant,Revoke,Rename,Exit,Quit,Audit,NoAudit
3.自动数据提交:
SQL>setautocommiton//打开自动数据提交开关
SQL>setautocommitoff//关闭自动数据提交开关(默认)
二、事务回退
语法:SQL>Rollback;//使数据库回退到最近一次提交后状态,如果一次也没有提交过,回到最原始状态
SQL>Rollbac;
SQL>Rollba;
SQL>Rollb;
SQL>Roll;
//以上的用法都是正确的。
三、设置保存点:
SQL>SavePointa;
回退到保存点:
SQL>Rollbacktoa;//此处的Rollback不能再简写。
数据定义语言(DDL)
包括:创建基表、视图、同义词、索引、数据库链路、序列等
一、创建基表
语法:
SQL>CreateTable表名(
列名1数据类型,
列名2数据类型,
...);//最多可以到1000个字段
例:SQL>CreateTableproduct(
p_name,varchar2(20),
p_id_numbernumber(7),
p_datedate);
1.数据类型
(1)字符型
char(n):n<=2000,固定长度,如果实际长度不够,前面用空格补齐。
varchar2(n):n<=4000,可变长度,不用空格补齐。
(2)数字型number(n):整数,number(n,d):小数
(3)日期型
(4)二进制raw(8i以前的,现在不支持了)
(5)大字符long:建议不要使用,现在不支持了
(6)blob:存储二进制
(7)CLOB:存储大字符
SQL>Createtableemployee(
namevarchar2(30),
salarynumber(7,2),
b_datedate,
photeblob,
resumeclob);
[*]修改数据库兼容性参数(如果在执行上面的SQL语句时,提示clob字段“默认字符集具有不同的宽度”,
则需要修改数据库兼容性。):
D:\\oracle\\amdin\\db_name\\pfile\\init.ora
compatible=8.0.5=改为=>8.1.0或8.1.5
改完后重新启动数据库使修改生效。
/**********************************************
修改前:
SQL>Createtableemployee(
2namevarchar2(30),
3salarynumber(7,2),
4b_datedate,
5photeblob,
6resumeclob);
resumeclob)
*
ERROR位于第6行:
ORA-22866:默认字符集具有不同的宽度
修改后:
SQL>Createtableemployee(
2namevarchar2(30),
3salarynumber(7,2),
4b_datedate,
5photeblob,
6resumeclob);
表已创建。
************************************************/
2.约束条件:数据完整性约束条件(DataIntegrityConstaints)
Oracle常用约束条件:
(1)非空约束:NOTNULL
(2)唯一性约束:UNIQUE
(3)主键:PRIMARYKEY,主键同时具有上面两个约束条件,一个表中只允许有一个主键。
(4)外键:FOREIGNKEY,这一列值从其它表中取出,允许重复,但不允许修改。
(5)检查:CHECK(e.g.check(sal>200)
(6)引用(参考):REFERENCES,只能用其它表或者本表的某一列列值,不能随便修改。
(7)缺省值:DEFAULT,如果没有输入,自动使用DEFAULT值。
3.约束条件的定义方法:
可以定义为列的一部分,也可以定义为表的一部分。
(1)定义为列的一部分:
SQL>CreateTableproduct(
p_namevarchar2(20)unique,
p_idnumber(7)primarykey,
p_datedatenotnull);
[*]Check、Default约束条件:
SQL>Createtableemployee(
namevarchar2(20),
idnumber(7)primarykey,
salnumber(11)check(sal>200andsal<2000),
h_datedatedefaultsysdate);
一个列具有多个约束条件的写法:
SQL>CreateTableproduct(
p_namevarchar2(20)unique,
p_idnumber(7)primarykey,
check(p_id>=111andp_id<=999),
p_datedatenotnull);
例:
SQL>insertintoemployee(name,id,sal)values('Smith',2,201);
SQL>select*fromemployee;
(2)定义为表的一部分(不能用在Default和NotNUll的定义上):
SQL>CreateTableProduct(
p_namevarchar2(20),
p_idnumber(7),
p_datedatenotnull,
constraintp_id_pkprimarykey(p_id),
constraintp_name_ukunique(p_name));
[*]查询约束条件:
SQL>select*fromuser_constraintswheretable_name='PRODUCT';
(3)另一种约束定义方法:
SQL>CreateTableProduct(
p_namevarchar2(20),
p_idnumber(7)constraintpk_p_idprimarykey,
p_datedateconstraintfk_p_datenotnull);
4.使用外键创建主从基表
(1)创建主表(定义主表):
SQL>CreateTableproduct(
p_namevarchar2(20),
p_idnumber(7)primarykey,
p_datedatenotnull);
(2)创建子表,定义外键
SQL>createtablesales_list(
sales_namevarchar2(20),
sales_idnumber(7)primarykey,
p_idnumber(7),
constraintp_id_fkforeignkey(p_id)referencesproduct(p_id));
5.数据完整性约束条件的修改
(1)删除约束条件:
A.删除主键约束:
SQL>Altertableproductdropprimarykey;
SQL>Altertableproductdropconstraintp_id_pk;
//以上两种方法等价
B.删除唯一性约束:
SQL>Altertableproductdropunique(p_name);
SQL>Altertableproductdropconstraintp_id_uk;
C.删除非空约束
SQL>Altertableproductmodify(p_dateNULL);
D.删除缺省值:
SQL>Altertableproductmodify(p_datedefaultnull);
(2)增加约束条件
A.增加主键约束:
SQL>Altertableproductaddprimarykey(p_id);
SQL>Altertableproductaddconstraintp_id_pkprimarykey(p_id);
B.增加非空约束
SQL>altertableproductmodify(p_datenotnull);
C.增加缺省值
SQL>Altertableproductmodify(p_datedefaultsysdate-1);
[*]查询缺省值:
SQL>selecttable_name,column_name,data_defaultfromuser_tab_columns;
二、修改表结构
1.在表中增加新列:
SQL>Altertableproductadd(p_listnumber(7),p_locvarchar2(20));
2.删除一个列(只适用于Oracle8i以后的版本):
SQL>Altertableproductdrop(p_list,p_loc);//删除多个列
SQL>Altertableproductdropcolumnp_list;//删除一个列
3.修改列宽:
SQL>Altertableproductmodify(p_namevarchar2(40));
//增加列宽没有约束,但减小列宽要求列中数据为空。使用此命令也可以修改列的数据类型。
三、视图(View):视图是虚表。
[*]视图不存储数据
[*]数据来源于基表
[*]不是数据的复制
[*]在同一个表上可以创建多个视图
1.创建视图的语法:
SQL>CreateorReplaceview视图名
asselect语句;
例:SQL>createviewmanager
as
select*fromempwherejob='MANAGER';//select*fromtab;检查
SQL>select*frommanager;//查询视图如果查询表
//第二次创建视图,可以使用orreplace参数,不需要再删除而直接覆盖同名视图。
SQL>createorreplaceviewmanager
as
select*fromempwherejob='MANAGER';
[几点说明:]
(1)在创建视图时,不得使用orderby排序。
(2)在视图中插入数据,则数据被插入到基表中,所以,如果要向视图插入数据,则创建视图时,必须包含
表中全部非空列。
(3)用户视图数据字典:
SQL>selectview_name,textfromuser_views;
2.视图列别名:
错误语句:
SQL>createviewpaymentas
selectsal,sal*12,nul(comm,0)/salfromemp;//错误原因:视图可以视同为表,所以列名也要符合
规定,而sal*12则是不符合规矩的列名。
正确语句:
SQL>createviewpayment(c1,c2,c3)as//c1,c2,c3即为视图列别名
selectsal,sal*12,nvl(comm,0)/salfromemp;
3.创建视图时增加约束条件:WITHCHECKOPTION
SQL>Createorreplaceviewdeptno20as
selectempno,ename,deptnofromempwheredeptno=20;
SQL>Insertintodeptno20values(1236,'李力',30);
SQL>select*fromdeptno20;
//上面的语句会出现能够通过视图入基表中插入数据,但却不能通过视图看到插入的数据的问题,解决办
法:
SQL>Createorreplaceviewdeptno20as
selectempno,ename,deptnofromempwheredeptno=20
WITHCHECKOPTION;
4.创建Oracle8i的实体化视图(MaterializedView):视图不依赖于基表,基表被删除后,视图仍然正常。
一般用于两个远程数据库之间的访问,通过数据链路来实现。
(1)以DBA用户登录,为用户授予创建实体化视图的权限:
SQL>GrantCreateMaterializedViewto用户名;
(2)以获权用户登录,创建实体化视图:
SQL>CreateMaterializedViewmanageras
select*fromempwherejob='MANAGER';
(3)删除实体化视图:
SQL>DropMaterializedViewmanager;
删除基表:
SQL>droptable表名;
删除视图:
SQL>dropview视图名;
三、创建数据库链路(Databaselink):
数据库链路:用于数据库之间的远程数据复制。
DB1(UNIX)<-------DB2(NT)
若要将DB2中的数据复制到DB1,则需要在DB1中创建数据库链路指向DB2数据库。
创建数据库链路的步骤:
(1)创建好连接串。
(2)创建数据库链路。
1.创建数据库链路的语法:
SQL>Createdatabaselink数据库链路名
connectto用户名identifiedby口令
using'主机字符串';
[*]数据库链路名必须与远程数据库的全局数据库名(数据库名.域名,若没有数据库名,就是数据库名)相同
[*]用户名及口令为远程数据库的用户名及口令
[*]主机字符串为本机tnsnames.ora中网络连接串。
SQL>Createdatabaselinkora31connecttouser30identifiedbyuser30using'ora31';
2.使用数据库链路:
SQL>select*fromproduct@ora31;
SQL>insertintoproduct@ora31values(...);
SQL>Createtableproductasselect*fromproduct@ora31;
3.删除数据库链路:
SQL>Dropdatabaselinkora31;
(一)创建数据库触发器实现两个数据库之间实时数据传输。
DB1(UNIX)<-------DB2(NT)
若要将DB2中的数据复制到DB1,则需要在DB1中创建数据库链路指向DB2数据库。
[*]在对方的数据库(DB2)上建立触发器,即数据发送方的数据库上建立触发器。
[*]在数据发送方建立指向DB1的数据库链路。
[操作步骤(假设db1为ora31,db2为ora8i):]
(1)在DB1上建立用来复制远程数据的表:
SQL>Createtableproduct
asselect*fromproduct@ora31;
(2)在DB2上建立到DB1的数据库链路:
SQL>Createdatabaselinkora31connecttouser30identifiedbyuser30using'ora31';
(3)在DB2上建立触发器:
SQL>CreateorReplacetriggerinsert_productbeforeinsertonproduct
foreachrow
Begin
Insertintoproduct@ora8i
values(:new.p_id,:new.p_name);
Endinsert_product;
//上述创建数据库触发器的语句,请以.号结束,然后以/执行。
/*查看SQL语句执行的错误信息:
SQL>showerrors
*/
(4)在DB2上测试数据的自动复制是否成功:
SQL>Insertintoproductvalues(1005,'测试商品');//在db2的表中插入一条数据
SQL>select*fromproduct;//检查数据是否正确插入本地表
SQL>select*fromproduct@ora31;//检查数据是否复制到了db1的表中
查询数据库链路信息:
SQL>selectusername,passwordfromuser_db_links;
数据更新:
SQL>CreateorReplacetriggerupdate_product
beforeupdateonproduct
foreachrow
Begin
updateproduct@ora8i
setp_id=:new.p_id,p_name=new.p_namewherep_id=:old.p_id
Endupdate_product;
数据删除:
SQL>CreateorReplacetriggerdelete_product
beforedeleteonproduct
foreachrow
Begin
deletefromproduct@ora8iwherep_id=:old.pid
Enddelete_product
(二).创建快照(实体化视图)实现两个数据库之间定时数据库传输:
快照:要求主副站点数据库的用户名相同
(1)在主节点创建快照日志
语法:SQL>Createsnapshotlogon主节点表名;//主节点基表必须含有主键
(2)在副节点创建快照
语法:
SQL>Createsnapshot快照名
refresh刷新方式
next时间间隔
withprimarykey
forupdate
asselect*from主节点表名@数据库链路名;
[*]刷新方式:
Compelete:完全刷新
Force:强制刷新(建议使用,强制刷新自动先fast刷新,然后再force刷新)
Fast:快速刷新
[*]时间间隔:以天为单位。
sysdate+1/4//六个小时刷新一次
sysdate+1/1440//一分钟刷新一次
SQL>showuser
USER为"STUD29"
SQL>createsnapshotlogondept;
实体化视图日志已创建。
SQL>showuser
USER为"STUD29"
SQL>select*fromdept;
SQL>insertintodeptvalues(60,'testsnap','snapshot');
[2]副节点
SQL>connectsystem/ab
已连接。
SQL>createuserstud29identifiedbystud29;
用户已创建
SQL>grantconnect,resourcetostud29;
授权成功。
SQL>grantcreatesnapshottostud29;
授权成功。
SQL>connectstud29/stud29;
已连接。
SQL>connectsystem/ab
已连接。
SQL>grantcreatedatabaselinktostud29;
授权成功。
SQL>connectstud29/stud29
已连接。
SQL>createdatabaselinkora8iconnecttostud29identifiedbystud29using'tea';
数据库链接已创建。
SQL>createsnapshotdept
实体化视图已创建。
*******************************************************************************************/
创建索引(indexes):
语法:SQL>createindex索引名on表名(列名);
例:SQL>createindexindex_dept_dnameondept(dname);
索引数据字典:
SQL>selectindex_name,table_owner,table_name,fromuser_indexes;
五、创建序列(Sequences):
语法:SQL>Createsequence序列名
startwith起始编码
incrementby步长
maxvalue终止编码;
SQL>createsequenceid_code
startwith2
incrementby2
maxvalue999;
序列使用方法:
id_code.nextval//下一个值
id_code.currval//当前值
第一次要使用nextval,然后以后每次使用currval。
insertintostudentvalues(id_code.nextval,'姓名');
[*]||(双竖线)在oracle中是连接符号,将两个字符串连成一个,如:'A'||'B'='AB'
数据库分区技术
一、什么是数据分区?
数据分区是指把一个表划分成若干小块。在创建表的结构时应考虑好分区方案,选择表中某一列或多列
数据作为分区关键字,该关键字决定哪些数据分到哪些区。Oracle对分区进行管理,新插入数据自动存储到
相应的分区。
二、创建分区表:
SQL>Createtableemployee(
idnumber(7),
namevarchar2(20),
salnumber(7,2))
Partitionbyrange(sal)
(Partitionp1valueslessthan(500)tablespaceusers,
Partitionp2valueslessthan(800)tablespacetools,
partitionp3valueslessthan(1000)tablespacesystem);
//p1,p2,p3是三个分区的名字,users,tools,system是三个表空间的名字。lessthan是小于(不包含
)。
几点说明:
[.]所插数据不得大于LESSTHAN中的最大值
[.]可以使用MAXVALUE(如上面的语句中,要求工资不能大于1000,如果出现这种情况,则应该改成下面的
语句:
SQL>Createtableemployee(
idnumber(7),
namevarchar2(20),
salnumber(7,2))
Partitionbyrange(sal)
(Partitionp1valueslessthan(500)tablespaceusers,
Partitionp2valueslessthan(800)tablespacetools,
partitionp3valueslessthan(1000)tablespacesystem,
partitionp4valueslessthen(maxvale)tablespaceusers);
[.]不指定表空间时,则该区使用该用户的缺省表空间。
*查询每个用户的用户缺省表空间:
SQL>selectusername,default_tablespacefromdba_users;//使用dba用户查询
[*]查询可以使用的表空间名字:
SQL>selecttablespace_name,file_namefromdba_data_files;//使用dba(sys或system)来执行
SQL>selectdba_users.username,dba_users.default_tablespace,dba_data_files.file_name
fromdba_users,dba_data_files
wheredba_users.default_tablespace=dba_data_files.tablespace_name;
[temp表空间不能用于数据分区。]
三、分区表的查询方法:
SQL>select*fromemployee;//按没有分区的方法查询
SQL>select*fromemployeepartition(p1);//只查询p1分区的数据。
SQL>createtablepart3asselect*fromemployeepartition(p3);
四、分区表的修改:
1、增加分区:
SQL>AltertableemployeeADD
partitionp4valueslessthan(1500)tablespaceusers;
[*]分区数据字典:
SQL>selectpartition_name,high_value,tablespace_name
fromuser_tab_partitions
wheretable_name='EMPLOYEE';
2、删除分区:
SQL>AltertableemployeeDROPpartitionp4;//结构数据全部删除(相应分区及数据全部被删除)
SQL>AltertableemployeeTRUNCATEpartitionp4;//保留结构(即区还存在),数据删除
3、修改区名:
SQL>AltertableemployeeRENAMEpartitionp4top5;
4、分区数据移动:将分区数据从一个表空间移动到另一个表空间
SQL>AltertableemployeeMOVEpartitionp4tablespacesystem;
5、分区的拆分:
SQL>AltertableemployeeSPLIT
partitionp3at(900)
into(partitionp31,partitionp32);
/*关于数据字典的几点说明:
v$打头的数据字典,后面不会以s结尾,例如:V$database,v$session;
user打头的,后面都会以s结尾(复数),如:user_tab_partitions,user_tables
dba打头的,有的以s结尾,有的不。
***************************************************************************/
6、分区的合并:
SQL>AltertableemployeeMERGEpartitionsp31,p32intopartitionp3;
SQL*Plus报表功能
/**********************插入内容:数据字典的一些说明*****************************
(1)user_XXX:用户,例如:user_tables
(2)dba_XXX:DBA专用
(3)all_XXX:本用户建的,或者其它用户创建本用户可以查询的(需要其它用户的授权)
(4)v$XXX:动态数据字典,如:v$database,v$instance,v$session,这些数据字典在oracle不启动时也
能查询
***********************************************************************************/
一、定义表头与表尾
SQL>ttitle'表头'
SQL>btitile'表尾'
失效:SQL>ttitleoff
SQL>btittleoff
二、定义列名
语法:SQL>column列名heading别名//别名不区分大小写
三、定义列格式:
SQL>column列名Format格式
常用列格式:An:A为字符,n为最大字符宽度。
$99.9999.99
9.99eeee
例:SQL>Columnsalformat$99.9999.99
SQL>ColumncommlikeSal
四、分组命令:
语法:SQL>breakon列名skipn
例:SQL>breakondeptnoskip2
SQL>select*fromemporderbydeptno;
五、统计计算:
语法:SQL>compute函数of统计列onskipn//可以使用的函数有:sum,max,min,avg,count,var(
斜方差),std(标准差)
例:SQL>computesumofsalondeptno
清除命令:SQL>clearcompute
SQL>clearbreak
SQL>clearcolumn
增加报表级统计:
SQL>breakondeptnoonREPORT
SQL>computesumofsalonreport
//整个报表出一个结果,上面两行都要运行,那么整个报表会根据你的设置出一个sumofsal的总计结果
。
函数
一、日期格式转换函数:to_char(日期变量,'格式')//格式要用单引号括起来
(1)日期格式构成方法:
年月日时分秒
yymmddhh(12小时制)miss
yyyymondy(星期)hh24(24小时制)
monthday
A.yy.mm.dd,yy/mm/dd,yy-mm-dd,yyyy.mm.dd,...加中文也可以,中文要用又引号括起来
SQL>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;//dual是一个虚拟表,任何用户
都可以使用。
二、聚组函数:从一组中返回汇总信息
聚组函数有:Sum,count,countdistinct,max,min,avg,stddev(标准差)
例:SQL>selectmin(sal),max(sal),avg(sal),sum(sal)formemp;
SQL>selectename,job,salfromempwheresal=(slectmax(sal)fromemp);
SQL>selectcount(*)fromemp;
复杂查询
一、连接查询:
问:Smith在哪里工作?
答:SQL>selectlocfromdept,empwhereename='SMITH'andemp.deptno=dept.deptno;
二、集合查询[请参考PowerPiont教程:SQL讲稿.pptP29,30,31]
集合操作是将多个基表的查询结果作UNION运算。
交操作:Intersect
差操作:MINUS
三、子查询(Subqueries):
子查询是在where子句中包含的查询语句,是由系列简单构成的复杂查询。
问:谁与smith在同一部门工作?
答:SQL>selectdeptnofromempwhereename='SMITH';
SQL>selectenamefromempwheredeptno=20;
将两个语句合起来:Selectenamefromempwheredeptno=(selectdeptnofromempwhereename=
'SMITH');
四、同义词(Synonym):
1、创建私有同义词:
语法:SQL>createsynonym同义词名for代替项;
user01:
SQL>grantselect,upateonproducttouser02;
user02:
SQL>Createsynonymproductforuser01.product;
SQL>select*fromproduct;//这里的product即user01.product。
同义词数据字典:
SQL>selectsynonym_name,owner,table_namefromall_synonyms;
SQL>selectsynonym_name,table_namefromuser_synonyms;
2、DBA可以创建公共同义词(PublicSynonym):公共同义词全体用户可以存取
语法:SQL>createpublicsynonym公共同义词名for代替项;
SCOTT:
SQL>grantselectonpaymenttopublic;
SYSTEM:
SQL>createpublicsynonympaymentforscott.payment;
3.删除同义词:
User:SQL>dropsynonym私有同义词名;
DBA:SQL>droppublicsynonym公共同义词名;
找Oracle视频教程学oracle,上课课家教育IT培训专家。