Oracle mysql数据库sql语言小记Word文件下载.docx

Oracle mysql数据库sql语言小记Word文件下载.docx

《Oracle mysql数据库sql语言小记Word文件下载.docx》由会员分享，可在线阅读，更多相关《Oracle mysql数据库sql语言小记Word文件下载.docx（49页珍藏版）》请在冰豆网上搜索。

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