Oracle mysql数据库sql语言小记Word文件下载.docx
《Oracle mysql数据库sql语言小记Word文件下载.docx》由会员分享,可在线阅读,更多相关《Oracle mysql数据库sql语言小记Word文件下载.docx(49页珍藏版)》请在冰豆网上搜索。
![Oracle mysql数据库sql语言小记Word文件下载.docx](https://file1.bdocx.com/fileroot1/2023-2/2/b02e71e0-79ef-49de-b623-8696bcb634f7/b02e71e0-79ef-49de-b623-8696bcb634f71.gif)
IN(值,值)--表示只要在括号内容数据都查询出来
select*fromempwhereenamein('
ADAMS'
'
SCOTT'
JONES'
)
LIKE
--表示模糊查询
select*fromempwhereenamelike'
_A%'
%表示任意0或多个任意字符
_表示1个任意字符
ISNULL
--表示查询字段为空的情况
select*fromempwherecommisnull
--逻辑操作符
用于组合多个计较运算的结果以生成一个或真或假的结果。
逻辑操作符包括与(AND)、或(OR)和非(NOT)
--集合操作符
union--联合(取并集去重复)
unionall--联合所有(取并集不去重复)
intersect--交集
minus--减集
--示例
selectdeptnofromempunionselectdeptnofromdept;
--取并集去重复
selectdeptnofromempunionallselectdeptnofromdept;
--取并集
selectdeptnofromempintersectselectdeptnofromdept;
--取交集
--注意下面两个表的位置不同,结果的区别,从第一个结果里找出不存在于第二个集合里的记录
selectdeptnofromempminusselectdeptnofromdept;
selectdeptnofromdeptminusselectdeptnofromemp;
--取差集
--连接操作符用于将多个字符串或数据值合并成一个字符串"
||"
selectempno||enamefromemp
SQL语言的分类
数据定义语言DDL(createalterdrop)
数据操纵语言DML(insertupdatedeleteselect)
事务控制语言TCL(commitrollback)
数据控制语言DCL(grantrevoke)
--ORACLE数据库中的伪列rowid,rownum
伪列本省不需要我们自己添加,数据库会默认帮我们添加
rowid用于明确区分数据库中的一条件,如果数据库中数据存在2行所有字段都一摸一样的情况可以是用rowid来区分
rownum用于取数据表中前几行的数据,注意,rownum查询只能<
或者<
=,在sqlserver中代替top关键字
selectrownum,rowid,ename,empno,salfromemp;
--数据定义语言DDL
--create表的约束
createtabletemp(
empnonumber,
enamevarchar2(200)default'
lifan'
--表示ename默认为'
deptnonumberdefault110,--表示deptno默认为110
edatedatedefaultsysdate,--默认值,表示为当前日期
addressvarchar2(200)notnull,--非空
salnumber(8,2)constraintemp_salary_mincheck(sal>
0),--检查约束,表示这个字段必须大于0
constraintfk_deptnoforeignkey(deptno)referencesdept(deptno),--外键
constraintpk_empnoprimarykey(empno));
--主键
default表示默认值,如果是字符类型的默认值需要加单引号,数字可以直接写
sysdate表示系统当前时间
notnull表示非空,如果为某个字段修饰为notnull那么这个字段不能插入空值
主键(非空,唯一)*
方法一
直接在某个字段后加primarykey
方法二
先建表,在修改某个字段为主键
altertable表名addconstraint主键名称primarykey(主键字段);
altertabletempaddconstraintpk_empnoprimarykey(empno)
方法三
写在建表语句后
外键
(为表与表之间关联的字段添加约束,保证数据完整性)
外键必须是关联表的主键
先建表,在修改某个字段为外键
altertable表名addconstraint外键名称foreignkey(外键字段)references关联表(主键字段)
altertabletempconstraintfk_deptnoforeignkey(deptno)referencesdept(deptno)
--ALTER(修改表结构)
dual伪表,这个表是没有任何数据也没有表结构,主要用于测试
函数
在SQL中的使用,可以用于查询结果,也可以用于查询条件
select函数fromempwhere函数
--函数(单行函数、分组函数、分析函数)
--单行函数(日期函数、字符函数、数字函数、转换函数、其它函数)
--日期函数
selectadd_months(sysdate,2)fromdual;
--月加
selectmonths_between(sysdate,sysdate)fromdual;
--求两日期月差
selectlast_day(sysdate)fromdual;
--当月最后一天
selectround(sysdate,'
YEAR'
)fromdual;
--YEAR舍入到最近的年,MONTH舍入最近的月DAY天
selectnext_day(sysdate,'
星期二'
--返回下个离自己最近星期几的日期
selecttrunc(sysdate,'
--YEAR截断日期到年月日,与round的区别是只舍不入默认返回天
selectextract(yearfromsysdate)fromdual;
--返回日期的特定部分year,month,day
--字符函数
selectinitcap('
adsfad'
--首写母大写
selectlower('
AAAA'
--转换小写
selectupper('
bbb'
--转换大写
selectchr(67)fromdual;
--返回ASCII码字符
selecttrim(leading'
9'
from'
9123456789'
--去前面
selecttrim(trailing'
--去后面
selecttrim('
--去所有前后
9123456789'
--去空格
selectltrim('
--去左边9
selectrtrim('
--去右边9
selectlength('
string'
--返回字符串长度注意空格也算长度*
selectdecode('
abcd'
1234'
--逐值替换,可替换多个,完全相同才替换
selecttranslate('
jack'
--在jack中用1234翻译abcd来替换其中的字符
selectreplace('
ck'
hh'
--把'
中的ck替换为hh
selectinstr('
c'
--找出JACK中的C所处的位置,索引是从1开始的
selectsubstr('
2,1)fromdual;
--截断字符串,第2个参数表示从索引小标开始,第3个参数表示截取几位
selectconcat('
asdf'
dddd'
--合并字符串
--数字函数
selectceil(44.778)fromdual;
--向上取整
selectfloor(100.2)fromdual;
--向下取整
selectabs(-15)fromdual;
--取绝对值
selectsqrt(4)fromdual;
--求平方根
selectpower(4,2)fromdual;
--取M的N次幂
selectcos(180)fromdual;
--取余弦函数
selectsin(180)fromdual;
--取正弦
selectcosh(0)fromdual;
--双曲余玄值
selectsign(-30)fromdual;
--取符号
selectmod(10,3)fromdual;
--取模
selectround(100.256,2)fromdual;
--四舍五入
selecttrunc(100.256,2)fromdual;
--截断
--转换函数
selectto_char(sysdate,'
YYYY-MM-DD'
--把日期转换为字符创
selectto_char('
456'
c9999'
--添加人民币符号CNY
selectto_date('
2005-2-3'
--将字符转为日期
selectsqrt(to_number('
100'
))fromdual;
--数字转换函数,取平方根
--其它函数
selectnvl('
'
空'
--为空则返回空,不为空则返回自己
selectnvl2('
不为空'
--为空则返回空,不为空则返回不为空
selectnullif('
123'
--相等返回空,否则返回前一个值
--分组函数
selectavg(sal)fromemp;
--求平均值
selectsum(sal)fromemp;
--求和
selectmin(sal)fromemp;
--最小值
selectmax(sal)fromemp;
--最大值
selectcount(*)fromemp;
--求记录数
selectcount(comm)fromemp;
--统计不为空的记录数
selectcount(distinctdeptno)fromemp;
--统计不重复记录数
一般分组函数会结合groupby使用,按什么字段来分组
selectdeptno,max(sal)fromempgroupbydeptno;
--分组统计最大值
selectdeptno,max(sal)fromempgroupbydeptnohavingmax(sal)>
2000;
注意:
分数函数如果作为查询条件时不能写在where后面,写到having后面
--排序
orderby需要排序的列asc/desc,...
asc默认,可以不写,表示升序(从小到大)
desc表示降序(从大到小)
--分析函数
--row_number函数对重复数据排序也不同
--row_number()ovar(orderby排序字段)
selectename,sal,row_number()over(orderbysaldesc)asrankfromemp;
--排序,数据重复排序不同
--rank()over(partitionby'
orderby'
)--重复记录序号跳过
selectdeptno,ename,sal,rank()over(partitionbydeptnoorderbysaldesc,comm)rankfromemp;
--dense_rank()over(partitionby'
)--重复记录连续
selectdeptno,ename,sal,dense_rank()over(partitionbydeptnoorderbysaldesc,comm)rankfromemp;
笛卡尔积(2个表或者两个以上表的结果乘积)
复杂查询(链接查询,子查询)
链接查询(内链接,外连接)
内链接
方式一(innerjoin...on)
select*from表1innerjoin表2on表1.外键=表2.主键
--查询雇员编号,雇员名称,部门名称
selecta.empno,a.ename,b.dnamefromempainnerjoindeptbona.deptno=b.deptno
方式二*
select*from表1,表2where表1.外键=表2.主键
selecta.empno,a.ename,b.dnamefromempa,deptbwherea.deptno=b.deptno
selectempno,ename,dname,dept.deptnofromemp,deptwhereemp.deptno=dept.deptno
三张以上的表情况
select*from表1,表2,表3where表1.外键=表2.主键and表2.外键=表3.主键
外连接(左外连接,右外连接)
左外链接
方式一(leftjoin...on)
select*from表1leftjoin表2on表1.外键=表2.主键
--列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select*fromdeptbleftjoinempaona.deptno=b.deptno
方式二
select*from表1,表2where表1.外键=表2.主键(+)
--列出部门名称和这些部门的雇员,同时列出那些没有部门的雇员
select*fromempa,deptbwherea.deptno=b.deptno(+)
右外链接
方式一(rightjoin...on)
select*from表1rightjoin表2on表1.外键=表2.主键
select*fromemparightjoindeptbona.deptno=b.deptno
select*from表1,表2where表1.外键(+)=表2.主键
select*fromempa,deptbwherea.deptno(+)=b.deptno
子查询(同样用于多表查询,可以用于查询内容,也可以用于查询条件)
用于查询内容
--查询所有雇员的名称,以及他的上级名称
selectempno,ename,mgr,(selectenamefromempbwherea.mgr=b.empno)fromempa
--列出所有雇员的姓名、部门名称和薪金
selecta.ename,a.sal,(selectdnamefromdeptbwherea.deptno=b.deptno)fromempa
用于查询条件
--列出薪金(工资)比'
SMITH'
多的所有雇员信息
(selectsalfromempwhereename='
--列出薪金高于公司平均水平的所有雇员信息
(selectavg(sal)fromemp)
数据库对象
使用同义词
使用序列
创建视图
创建索引
Oracle数据库对象又称模式对象
数据库对象是逻辑结构的集合,最基本的数据库对象是表
其他数据库对象包括:
同义词
同义词是现有对象的一个别名。
–简化SQL语句
–隐藏对象的名称和所有者
–提供对对象的公共访问
同义词共有两种类型:
公有同义词
公有同义词可被所有的数据库用户访问。
CREATESYNONYMemp_synFORemp;
emp_syn为SCOTT.emp 的别名
emp为表名
私有同义词
只能在其模式内访问,且不能与当前模式的对象同名。
CREATEPUBLICSYNONYMemp_synFORemp;
emp_syn为同义词名称
创建或替换现有的同义词
CREATEORREPLACESYNONYMemp_synFORSCOTT.emp;
ORREPLACE替换现有的同义词
删除同义词
SQL>
DROPSYNONYMemp;
DROPPUBLICSYNONYMemp_syn;
序列
序列是用于生成唯一、连续序号的对象
序列可以是升序的,也可以是降序的
使用CREATESEQUENCE语句创建序列
CREATESEQUENCEtoys_seq
STARTWITH10
INCREMENTBY10
MAXVALUE2000
MINVALUE10
NOCYCLE
CACHE10;
指定内存中预先分配的序号数
通过序列的伪列来访问序列的值
–NEXTVAL返回序列的下一个值
–CURRVAL返回序列的当前值
INSERTINTOtoys(toyid,toyname,toyprice)
VALUES(toys_seq.NEXTVAL,‘TWENTY’,25);
VALUES(toys_seq.NEXTVAL,’MAGICPENCIL’,75);
指定学列的下一个值
SELECTtoys_seq.CURRVALFROMdual;
检索序列的当前值
更改和删除序列
使用ALTERSEQUENCE语句修改序列,
不能更改序列的STARTWITH参数
ALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;
使用DROPSEQUENCE语句删除序列
DROPSEQUENCEtoys_seq;
视图
视图以经过定制的方式显示来自一个或多个表的数据
视图可以视为“虚拟表”或“存储的查询”
创建视图所依据的表称为“基表”
视图的优点有:
提供了另外一种级别的表安全性
隐藏的数据的复杂性
简化的用户的SQL命令
隔离基表结构的改变
通过重命名列,从另一个角度提供数据
创建视图的语法:
CREATE[ORREPLACE]VIEW
view_name[(alias[,alias]...)]
ASselect_statement;
CREATEVIEWstud_view
ASSELECTstudno,studname,subno
FROMStud_details;
结果:
创建条件视图
CREATEORREPLACEVIEWpause_viewAS
SELECT*FROMorder_masterWHEREostatus='
p'
;
使用ORDERBY子句创建视图
CREATEORREPLACEVIEWord_venAS
SELECT*FROMvendor_masterORDERBYvenname;
CREATEVIEWStud_sub_viewAS
SELECTStudno,Studname,Submrks,Subname
FROMStud_details,Sub_Details
WHEREStud_details.Subno=Sub_details.Subno;
创建外联接视图
CREATEVIEWven_ord_outj_viewAS
SELECTvm.vencode,venname,orderno,odate,ostatus
FROMvendor_mastervm,order_masterom
WHEREvm.vencode=om.vencode(+);
FROMvendor_mastervmLEFTOUTERJOINorder_masterom
ONvm.vencode