oracle基础sql命令集.docx
- 文档编号:27471679
- 上传时间:2023-07-01
- 格式:DOCX
- 页数:15
- 大小:680.92KB
oracle基础sql命令集.docx
《oracle基础sql命令集.docx》由会员分享,可在线阅读,更多相关《oracle基础sql命令集.docx(15页珍藏版)》请在冰豆网上搜索。
oracle基础sql命令集
基础命令
带where条件的查询
Selectjob_idas“gongzuo”fromemployeeswherejob_id=’SA_MAN’;
||连接符,链接多个列的值
SELECTlast_name||job_idAS"Employees"FROMemployees;
Distinct消除重复值
SELECTDISTINCTdepartment_idFROMemployees;
Describe查看表结构,可见写为desc
describeemployees;
descemployees;
比较字符<>=
Betweenand在两个值之间,前小后大
SELECTlast_name,salaryFROMemployeesWHEREsalaryBETWEEN2500AND3500;
In(在某些值范围内)
SELECTemployee_id,last_name,salary,manager_idFROMemployeesWHEREmanager_idIN(100,101,201);
Like匹配字符串
_匹配任意一个字母,%匹配任意数量任意字母
SELECTlast_nameFROMemployeesWHERElast_nameLIKE'_o%';
特殊字符条件,如_和%本身就在字符串内,使用escape指定转义字母注明
SELECTemployee_id,last_name,job_idFROMemployeesWHEREjob_idLIKE'%SA\_%'ESCAPE'\';转义字符为\在\之后的_只被理解成普通字符,没有特殊含义
Order排序默认升序ASE:
升序DESC:
降序
SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBYhire_date;
字符函数
lower字母变小写
selectlower(last_name)fromemployees;
upper字母变大写
selectupper(last_name)fromemployees;
initcap单词首字母大写
selectinitcap(email)fromemployees;
concat连接两列的值(不常用,被||替代)
selectconcat(last_name,salary)fromemployees;
substr取字符串从第X位开始往后Y位的字母,X为负数时从尾往前数,Y不能为负数并且永远是从前往后
selectsubstr(job_id,1,2)fromemployees;X=1Y=2
selectsubstr('1234567890',-5,5)fromdual;X=1Y=5
length字符长度
selectlength('12345')fromdual;
selectlength(job_id)fromemployees;
instr字母所在位置
selectinstr('123123123','1',1,1)fromdual;字母1从第一个字母位置开始搜索,第一次出现的位置
selectinstr('123123123','1',1,2)fromdual;字母1从第一个字母位置开始搜索,第二次出现的位置
lpad和rpad左右填充
selectlpad('123',10,'0')fromdual;在字符左边用0填充到10位
selectrpad('123',10,'0')fromdual;在字符右边用0填充到10位
trim删除字母串头或尾或头和尾的第一个字母(leading,trailing,both)
selecttrim('1'from'1234567890')fromdual;删除字母串第一位的‘1’
selecttrim(trailing'0'from'1234567890')fromdual;删除字母串最后一位的‘0’
round取小数点后几位,四舍五入
selectround(1.1254,2)fromdual;
selectround(1.1247,2)fromdual;
trunc取小数点后几位,不四舍五入
selecttrunc(1.1257,2)fromdual;
mod求余
selectmod(1000,300)fromdual;
日期函数
周一:
Monday周二:
Tuesday周三:
Wendesday周四:
Thursday周五:
Friday周六:
Saturday
周日:
Sunday,缩写的话就取前三个字母。
一月JanuaryJan.二月FebruaryFeb.三月MarchMar.
四月AprilApr.五月MayMay.六月JuneJune.七月JulyJuly.
八月AguestAug.九月SeptemberSept.十月OctoberOct.
十一月NovemberNov.十二月DecemberDec.
sysdate当前系统时间
selectsysdatefromdual;
MONTHS_BETWEEN两个日期之间的月数
selecthire_date,months_between(sysdate,hire_date)fromemployees;
ADD_MONTHS增加月份,也可减少
selecthire_date,add_months(hire_date,2)fromemployees;
selecthire_date,add_months(hire_date,-2)fromemployees;
next_day下一个星系几的日期
selectnext_day(sysdate,'monday')fromdual;
last_day本月最后一天
selectlast_day(sysdate)fromdual;
•–
数字函数也可以用在日期上
假设SYSDATE='25-JUL-95'
•ROUND(SYSDATE,'MONTH')》01-AUG-95
•ROUND(SYSDATE,'YEAR')》01-JAN-96
•TRUNC(SYSDATE,'MONTH')》01-JUL-95
•TRUNC(SYSDATE,'YEAR')》01-JAN-95
字符转换
to_char将数字和日期转换成字符格式
selectto_char(sysdate,'dd/mm/yy')fromdual;
selectto_char(sysdate,'fmddspth"of"monthyyyyfmhh:
mi:
ssam')fromdual;
fm意思为按后面格式填充,ddspth用英文序列方式显示日期,spth前的参数必须为数字,例如ddmmyy
selectto_char(salary,'9999')fromemployees;如果9的个数小于数字位数,显示为###
字母变数字
selectto_number(123)fromdual;
字母变日期
selectto_date('May24,1999','fxMonthDD,YYYY')fromdual;
常规函数
nvl转换空值为指定值
selectnvl(commission_pct,0)fromemployees;将空值转换为0注意转换后的数据类型要与原数据定义类型相同,char、number、date
nvl2非空转换为第一个参数,空值转换为第二个参数
selectnvl2(commission_pct,'sal+com','sal')fromemployees;参数1和参数2必须为相同类型,可以为除long外任意类型,如不相同会自动将参数2转换为参数1
nullif如果两参数相等,返回空值,如不相等,返回第一个参数
selectnullif(1,1)fromdual;
selectnullif(1,2)fromdual;
coalesce加强版nvl,可扩容多个参数,前一个参数为空则取后一个参数的值,如继续为空则继续往后
caseif-else结构语句
selectjob_id,salary,casejob_idwhen'SA_MAN'then1.1*salarywhen'SA_REP'then1.5*salaryelsesalaryend"rd_salary"fromemployees;//case开始,end结束
decode是case和if-else的简化版本
selectjob_id,salary,decode(lower(job_id),'sa_man',salary*2,'mk_man',salary*4,'it_prog',salary*10,salary)gongzifromemployees;参数job_id前用字符参数lower将字母全部改为小写
联接
等值连接又称为简单联接、内联接
selecta.last_name,b.department_idfromemployeesa,departmentsbwherea.department_id=b.department_id;
外联接显示没有匹配条件的其他表项,显示没有+的表中的其他行
SELECTe.last_name,e.department_id,d.department_nameFROMemployeese,departmentsdWHEREe.department_id(+)=d.department_id;
自联接自己与自己联接
SELECTworker.last_name||'worksfor'||manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;
SQL语法的联接写法
table1jointable2
crossjoin全联接,笛卡尔集合
SELECTlast_name,department_nameFROMemployeesCROSSJOINdepartments;
natrualjoin自然联接,自己搜索匹配表之间列名相同的列作为条件
SELECTdepartment_id,department_name,location_id,cityFROMdepartmentsNATURALJOINlocations;
join。
。
。
using()指定表之间使用哪列作为条件
SELECTl.city,d.department_nameFROMlocationslJOINdepartmentsdUSING(location_id)WHERElocation_id=1400;
join。
。
on等价using
SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id);
Left/right/fullouter显示不匹配条件的左边/右边/两边的剩余表项
SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseFULLOUTERJOINdepartmentsdON(e.department_id=d.department_id);
分组
avg平均sum求和max最大min最小count行数,有三个参数*全部、expr非空值、distinctexpr不重复非空值
groupby以某一列的某个值为条件,具有相同值的行为一个组
SELECTdepartment_id,AVG(salary)FROMemployeesGROUPBYdepartment_id;
Having不能使用where来添加限制条件,对分组需要使用having
SELECTdepartment_id,AVG(salary)FROMemployeesHAVINGAVG(salary)>8000GROUPBYdepartment_id;
DML命令
Insertinto插入行
insertintotvalues('a','b','c','d','e');
update更新行
updatetsetc='cc'wheree='ee';//没有where则更新表中所有的c列
delete删除行
deletefromtwheree='ee';
WITHCHECKOPTION?
Default默认值
createtablet(Aintdefault10,Bint,Cint);//建立表的时候可以设置默认值
insertintotvalues(default,10,20);//插入,更新时可使用默认值
MERGE有条件的在表中更新或添加,根据判断条件
MERGEINTOcopy_empc
USINGemployeese
ON(c.employee_id=e.employee_id)
WHENMATCHEDTHEN
UPDATESET
c.first_name=e.first_name,
c.last_name=e.last_name,
c.email=e.email,
c.phone_number=e.phone_number,
c.hire_date=e.hire_date,
c.job_id=e.job_id,
c.salary=e.salary,
mission_pct=mission_pct,
c.manager_id=e.manager_id,
c.department_id=e.department_id
WHENNOTMATCHEDTHEN
INSERTVALUES(e.employee_id,e.first_name,e.last_name,
e.email,e.phone_number,e.hire_date,e.job_id,
e.salary,mission_pct,e.manager_id,
e.department_id);
将emp表的信息全部拷贝到copy表中,判断条件为employee_id,如相等表示此行以存在,则更新,如不相等则不存在,执行插入
事务控制
DDL语句
createtable创建表
createtablecountries(COUNTRY_IDCHAR
(2)NOTNULL,COUNTRY_NAMEVARCHAR2(40),REGION_IDNUMBER);
altertable修改表
add添加列
altertabletadd(cint);
monify修改列
altertabletmodify(cvarchar2(20));
dropcolumn删除列
altertabletdropcolumnc;
setunused设置列不可用(替代删除)
altertabletsetunused(b);
dropunusedcolumns删除设置为不可用的列
altertabletdropunusedcolumns;
droptable删除表
droptablet;
rename重命名
renamettojia;
truncate删除表中所有的行,释放空间,也可以使用delete但是truncate更快
truncatetablejia;
commenton添加备注
commentontablejiais'jiating';
selectTABLE_NAME,COMMENTSfromuser_tab_comments;//查看备注
约束
notnull非空
createtablet(aintnotnull,bvarchar2(20));
unique不能重复
createtablet(aint,bvarchar2(20),constraintt_b_ununique(b));
primarykey主键,一个表只有一个主键,主键默认非空,不能重复
createtablet(aint,bvarchar2(20),constraintt_a_zhuzhiprimarykey(a));
foreignkey
createtablet1(aint,cvarchar2(20),constraintt_a_fkeyforeignkey(a)referencest(a));
check限制条件
createtablet3(aint,dint,constraintt3_a_numbercheck(a>0));
altertable。
。
。
addconstraint在已有的表中添加约束
altertablet3addconstraintt3_d_numcheck(d>2);
dropconstraint删除约束
disableconstraint禁用约束
enableconstraint启用约束
cascadeconstraints配合drop删除有约束条件的列
altertablet3drop(a)cascadeconstraints;
查看约束信息在表user_constraint中
typeC:
check(包括notnull)P:
primarykeyU:
uniqueR:
引用完整性
视图
Createview创建视图
createviewemp_salaryasselectemployee_id,last_name,salaryfromemployees;//创建视图需要权限,在sysdba用户下grantcreateviewtoscott;
WITHCHECKOPTION限制对视图的修改必须满足创建视图时where里的条件
createviewels(e,l,s)asselectEMPLOYEE_ID,LAST_NAME,SALARYfromemployeeswhereEMPLOYEE_IDbetween100and200WITHCHECKOPTION;
updateelssete=300wherel='Whalen';
ERRORatline1:
ORA-01402:
viewWITHCHECKOPTIONwhere-clauseviolation
WITHREADONLY;视图为只读,不允许修改
内联视图直接在查询语句中创建出视图并使用
SELECTa.last_name,a.salary,a.department_id,b.maxsal
FROMemployeesa,(SELECTdepartment_id,max(salary)maxsal
FROMemployees
GROUPBYdepartment_id)b
WHEREa.department_id=b.department_id
ANDa.salary Top-N将需要选取名次的列创建为内联视图再取值 SELECTROWNUMasSENIOR,E.last_name,E.hire_date FROM(SELECTlast_name,hire_dateFROMemployees ORDERBYhire_date)E WHERErownum<=4; CREATESEQUENCE创建序列 createsequencexulieincrementby2startwith10maxvalue100nocachenocycle; 查看序列下一个值 selectxulie.nextvalfromdual; 查看序列当前值 selectxulie.currvalfromdual; insertintotvalues(xulie.nextval,'d');//使用序列号给表赋值 ALTERSEQUENCE修改序列 Createindex创建索引如表中已定义非重复或主键,则此列自动生成索引 createindext_a_indexont(a); createsynonym创建同义词给表起别名,简化访问其他用户表时候的输入 createsynonymjiaforscott.t; 用户管理 Createuser。 。 identifiedby。 。 。 创建用户 createusershenidentifiedbyoracle; grant。 。 to赋予权限 grantcreatesessiontoshen; 表空间给角色授权: alteruser用户名quotaunlimitedon表空间; 回收用户dba的角色: revokedbafromuser dba角色赋予用户: grantdbatouser 修改用户密码 alterusershenidentifiedbyredhat; 赋予表,视图。 。 权限 grantselectonscott.ttoshen; withgrantoption被授权用户可向其他用户授权 public代表所用用户 revoke撤销权限,配合withgrantoption可撤销用户再次授予其他用户的权限
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 基础 sql 命令