Oracle（Oracle） DB2（IBM） SQL Server（MS） MySQL（Oracle）

重启oracle数据库

在系统命令行输入

sqlplus /nolog
conn sys / as sysdba
shutdow immediate;
startup;

查找并杀死超时语句

select sid,serial#,sql_text,executions 
from v$sql 
join v$session on v$sql.sql_id=v$session.sql_id
where cpu_time>20000;

检查查出来的会话，如果sql_text正确的话，记下sid和serial#，执行以下语 alter system kill session 'id,serial#';

查询字段在哪些表 SELECT TABLENAME,COLUMNNAME FROM USERTABCOLUMNS WHERE COLUMN_NAME='列名'

SQL:结构化查询语句 用来操作数据库的语言

所有的数据库都支持标准的SQL语句 sql语句包含 DDL,DML,TCL,DQL,DCL这几类语句

DDL语句 DDL语句时用来增删改数据库对象的 数据库对象:表,视图,索引,序列 truncate table zsemp 删除表中所有数据 delete from where desc employeezs 查看表 DROP TABLE Employee_zs 删除表

sql语句是不区分大小写的 ,但是字符串的值(直接量)是 区分大小写的,字符串的直接量是用单引号括起来的

数据库中的所有数据类型默认值都是null, 在创建表时,可以使用DEFAULT文字符串单独指定默认值 CREATE TABLE EMPLOYEE_ZS( ID NUMBER(4), NAME VArCHAR2(20) NOT NULL, GENDER CHAR(1) DEFAULT 'M', BIRTH DATE, SALARY NUMBER(6,2)DEFAULT 5000, JOB VArCHAR2(30) DEFAULT 'CLECK', DEPTNO NUMBER(2) );

CHAR表示固定长度的字符类型，经常被定义成CHAR（N）形式, N表示占用的字节数，N的最大取值是2000 VARCHAR2表示变长的字符类型，定义格式是VARCHAR2（N）, N表示最多可占用的字节数，最大长度是4000字节。 DATE用于定义日期时间的数据，长度是7个字节 非空约束 当一个字段被NOT NULL修饰后 ,该字段在任何情况下都不为NULL. 可以在查看表中体现出来

修改表名 RENAME OLDNAME TO NEWNAME RENAME EMPLOYEEZS TO ZSEMP

修改表结构 1 添加新的字段 向表ZSEMP中添加新的字段HIREDATE ALTER TABLE ZSEMP ADD( hiredate DATE )

2 删除表中现有字段 将ZSEMP表中HIREDATE删除 ALTER TABLE ZSEMP DROP(HIREDATE)

3 修改表中现有字段 修改字段可以修改字段的类型,长度,默认值,非空约束 但是表中若已存在数据,那么修改字段的时候尽量 不修改 类型,若修改长度尽量不要缩小,否则可能导致修改失败 ALTER TABLE ZS_EMP MODIFY( job varchar2(15) default'cleck' )

DML语句 DML语句用来对表中的数据进行相关操作,包括 增 , 删 , 改.

1 插入(增)操作 INSERT INTO ZS_EMP
(ID,NAME,SALARY,DEPTNO) VALUES (1,'zs',6000,10)

select * from zs_emp 查看表所有数据

2 修改数据 UPDATE语句用于修改表中数据,需要使用WHERE添加条件以修改满足条件的 记录,若不添加WHERE则是全表所有数据修改.

UPDATE ZS_EMP SET SALARY =8000 where name ='son'

3 DELETE语句用于删除表中记录,通常需要使用WHERE添加条件来删除满足条件的 记录,若不添加WHERE是清空表操作

DELETE FROM ZS_EMP where name ='zs'

DQL语句 dql用于查询数据库中的数据 组函数在计算时会忽略null值行。

DQL必须包含两个子句: SELECT,FROM SELECT 用于指定要查询的字段,可以是表中的字段,函数和表达式 FROM子句用来指定数据来源的表 SELECT * FROM ZSEMP SELECT ENAME, JOB , SAL ,DEPtNO FROM ZSEMP where sal>2000

SELECT子句中也可以使用函数或者表达式 查看年薪如下 SELECT ENAME , SAL ,SAL*12 FROM ZSEMP

字符串函数 1:CONCAT(CHAR2,CHAR1) 将两个参数字符串连接在一起 SELECT ConCAT(ENAME,concat(',',SAL)) from zsemp

'||'可以连接字符串 SELECT ENAME||','||SAL FROM ZSEMP

2 LENGTH(CHAR) 查询字符串长度 SELECT ENAME ,LENGTH(ENAME) FROM ZSEMP where ename ='SMITH'

3 伪表 DUAL 伪表不是一张真的存在的表,当查询的内容与任何表数据无关时,可以使用伪表

SELECT SYSDATE FROM DUAL

4 UPPER , LOWER, INITCAP 将字符串转换为大写,小写,首字母大写 SELECT UPPER('helloworld'), LOWER('HELLOWORLD'), INITCAP('HELLO WORLD') from dual

当表中大小写混搭时 无法查询到内容时 SELECT ENAME,JOB,SAL FROM ZSEMP where lower(ename) =lower('smith')

5 TRIM LTRIM RTRIM 去除字串两端的字符 SELECT TRIM('e'FROM'eeeksddsdkeejeee') FROM DUAL 去除字符串任意一端的字符

SELECT LTRIM('dasfsadafsdflistfgds','daf') from dual

SELECT RTRIM('ddddddfsdlistddsfsfsafddsss','ds') from dual

6 lpaD , RPAD补位函数 将制定字符串显示指定长度,当不足时补充若干个指定字符以达到该长度 右补位左对齐 左部位右对齐 位数小于字符串位数时成截取

SELECT ENAME , RPAD(SAL,5,' ') from zsemp

7 SUBSTR(CHAR,M[,N]) 截取制定字符串,从M处连续截取N个字符,N若不指定或者超世纪可截取长度,都会截取 到字符串末尾 数据库中下标从1开始 M若为负数,则是从倒数位置开始计算位置并截取 SELECT SUBSTR('thinking in java',10,2) FROM DUAL select SUBSTR('thinking in java',-7,2) from dual

8 INSTR(CHAR1,CHAR2[,M[,N]] 查看CHAR2在CHAR1中的位置 M为从哪里开始查找,不写默认为1 N为第几次出现,不写默认为1

SELECT INSTR('thinking in java','in','3','2') FROM DUAL

数字函数 1 ROUND(N[,M]) 四舍五入保留N小数点后面M位,M可以为负数 若M不写默认为0,保留到个位 若是负数则是保留到十位以上的数字

SELECT ROUND(45.678,1) FROM DUAL SELECT ROUND(45.678,0) FROM DUAL SELECT ROUND(55.678,-2) FROM DUAL 超过数字位数为0 或10+

2 TRUNC函数 与ROUND类似 但不四舍五入 作用是截取数字 SELECT TRUNC(45.678,1) FROM DUAL 45.6 SELECT TRUNC(45.6478,2) FROM DUAL 45.64 select trunc(55.67,-2) from dual 超过数字位数只能为0

3 MOD(M,N) 取余函数 N为余数 N为0直接返回M SELECT ENAME,SAL,MOD(SAL,1000) FROM ZSEMP

4 CEIL(),FLOOR() 取整函数 上取整和下取整 SELECT CEIL(45.6) FROM DUAL 46 select floor(45.6) from dual 45

日期转换函数 日期类型 两个常用关键字 SYSDATE:对应数据库的一个内置函数,返回一个 DATE类型 数据,表示当前 系统时间 SYSTIMESTAMP : 返回一个 时间戳类型 的当前系统时间 SELECT SYSdate FROM DUAL SELECT SYSTIMESTAMP FROM DUAL

1:TODATE() 可以将给定字符串按照指定日期格式转为DATE类型值 在日期格式字符串凡不是英文,符号的其他字符,都需要使用双引号括起来 yy本世纪 rr0-49本世纪 50-99上世纪 SELECT TODATE('1989-12-02 9:58:46', --如-改成年月日时分秒 'YYYY-MM-DD HH24:MI:SS') --此处的要用双引号括起来 FROM DUAL 2: TOCHAR(DATE,CHAR) SELECT TOCHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"') FROM DUAL

2 日期类型的计算(按天) 对一个日期加减一个数字等同于加减天数 两个日期做减法,差为相差的天数

查看明天日期 SELECT SYSDATE+1 FROM DUAL 查看员工的工龄多少天 SELECT ENAME,SYSDATE-HIREDATE from zsemp

日期函数 1:LASTDAY(DATE) 返回给定日期所在的月的月底日期 查看当月月底 SELECT LASTDAY(SYSDATE) FROM DUAL

2 ADD_MONTHS(DATE,I) 对指定date加减指定月

查看员工入职三个月后的转正时间 SELECT ENAME ,ADD_MONTHS(HIREDATE,3) FROM ZSEMP

3 MONTHSBETWEEN(DATE1,DATE2) 计算两个指定日期之间相差的月 查看每个员工入职至今多少个月 select ename, monthsbetween(sysdate,hiredate) from zsemp

4 NEXTDAY(DATE,I) 获取从DATE的下一天开始第一个周I-1的日期 1为周天和java相同 select nextday(sysdate,5) from dual

5 LEAST,GREATEST 求最大最小值 日期也可以 select least(sysdate,to_date('1989-2-9','YYYY-MM-DD')) FROM DUAL

6 extract(date from datetime)函数 获取一个日期中的指定时间分量的值 查看今年是哪年 SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL

查看1981年入职的员工 SELECT ENAME,HIREDATE FROM ZSEMP where extract(year from hiredate) = 1981

CREATE TABLE STUDENT( ID NUMBER(10),NAME VARCHAR2(20),GENDER VARCHAR2(3) ) INSERT INTO student VALUES (1,'zs','男') INSERT INTO student VALUES (2,'zsson','男') INSERT INTO student (ID,NAME) VALUES (3,'zslv') UPDATE STUDENT SET gender=NULL WHERE NAME='zs'

DELETE FROM STUDENT where gender is null 判断是不是null使用is和is not SELECT * FROM STUDENT

NULL的运算 NULL与字符串进行连接字符串不会发生任何变化 NULL参与数字运算结果都是 NULL

SELECT NAME||NULL 无变化 FROM student

获取员工收入(SALARY+COMM) SELECT NAME,SALARY,SALARY+comm 如何comm为null 结果为null from student

空值函数 1 NVL(ARG1,ARG2) 当ARG1为NULL时,函数返回ARG2 当ARG1为非空时,返回ARG1 该函数意义:将NULL值替换为非NULL值

获取员工收入(SALARY+COMM) SELECT NAME,SALARY,SALARY+nvl(comm,0) from student

2 NVL2(ARG1,ARG2,ARG3) 当arg1为null时返回arg3,不为null时返回arg2

查看每个员工是否有绩效 有绩效的显示'youjixiao' 没有显示'meiyou ' SELECT eNAME,comm,NVL2(comm,'有绩效','没有绩效') from zsemp

列别名 当一个SELECT语句中包含函数或者表达式时,查询的结果及对应的 该字段就是使用这个函数或者表达式作为字段名,可读性差,为此可以 给这样的字段添加别名

select ENAME,SAL*12 sal --或者双引号"sal" from ZSEMP

关系符号 select SAL from zsemp where SAL>1000

select DEPTNO from ZSEMP where DEPTNO<>10 --不等于

select HIREDATE from ZSEMP where hiredate>to_date('1989-12-02','YYYY-MM-DD')

逻辑符号 and or and优先级高于or select ENAME ,deptno,SAL from ZSEMP where deptno<>20 and sal>1500

select ENAME,job,SAL from ZSEMP --and优先级高于or where sal>1000 and (job='SAILSMAN'or job= 'CLERK')

模糊查询 like 关键词 %任意多个字符(0到任意) 一个字符 查看名字第二个字母是A的第四个字母是T的员工 select ENAME from ZSEMP where ENAME like 'A_T%'

select ENAME from ZSEMP where ename like '%A%'

in(list) not in(list) 其中一个在列表中或者不在列表中 查看职位是CLERK或者SALESMAN的员工 常用于子查询的判断中

select ENAME,job from ZSEMP where job in('CLERK','SALESMAN')

BETWEENAND 指定一个范围 查看工资在1500到3000之间的员工

select ENAME,job,SAL from ZSEMP where sal between 1500 and 3000

any(list) all(list) 配合>,<,<=,>=使用

>any() 大于列表之一
<any()
>all()大于列表中所有
<all()小于列表所有

select ename, SAL from zsEMP where SAL>any(3000,5555,55556) select ENAME ,SAL from ZSEMP where sal< all(3333,999,8888)

查询条件使用表达式和函数 select ENAME ,SAL from ZSEMP where ENAME =UPPER('scott')

select ENAME ,SAL from ZSEMP where sal*12>30000

distinct 去除重复行 select distinct job from ZSEMP --查询所有工作职位

select distinct job,deptno from zsemp 多字段去重,组合去重

order by 排序 order by[ASC升或DESC降] order BY必须写在DQL的最后一个子句上

所有工资的排名 select Ename ,deptno, SAL from ZSEMP order by SAL desc 多字段排序,有优先级,首先按照第一个字段排序,当第一个字段有重复值的时候才按照第二 个字段排序,以此类推 select ENAME ,DEPTNO,SAL from ZSEMP order by SAL desc,DEPTNO

null被认为是最大值 ENAME ,COMM from ZSEMP order by comm

聚合函数(多行函数,集合函数) 忽略null值 作用是对结果集的指定字段进行统计然后得出一个结果

max(),min() 最高工资 select max(sal) MAX ,min(comm)min from zsemp

AVG(),SUM() 平均工资和工资总和 select trunc(avg(sal),0 )平均工资 ,sum(sal) from zsemp

COUNT() 数的统计 公司多少人 select count(1) from zsemp

关于忽略NULL值 select AVG(COMM) from ZSEMP --不准确 select AVG(NVL(COMM,0)) from ZSEMP --将null替换为0

分组 GROUP BY子句 GrOUP BY可以将结果集按照给定字段值一样的纪录进行分组,配合聚合函数 可以对不用的分组分别统计结果 select AVG(SAL) ,deptno from ZSEMP 各个部门的平均工资 group by deptno

select max(SAL) ,job from ZSEMP 每种职位的最高工资 group by job

多字段分组 select AVG(SAL) ,DEPTNO,job from ZSEMP group by deptno ,job

此处sql语句报错 不允许使用分组函数 select min(SAL) ,DEPTNO from ZSEMP where min(sal)>1000 group by DEPTNO 应用 HAVING子句 HAVING子句必须跟在GROUP BY子句以后作用是 添加过滤条件来过滤GROUP by 的分组,它可以将不满足条件的分组去除 having子句可以使用聚合函数作为过滤条件 select min(SAL) ,DEPTNO from ZSEMP group by DEPTNO having min(sal)>1000

平均工资高于2000的部门的最低工资 select min(SAL),DEPTNO from ZSEMP group by DEPTNO having avg(sal)>2000

select AVG(SAL), job from ZSEMP group by job having min(sal)>1000

关联查询 查询数据的从多张表中关联查询一个结果集 关联查询的重点是添加连接条件 连接条件的作用是告知数据库表与表之间的数据是怎么样应对的 关联查询通常要添加连接条件,否则会产生笛卡尔积,通常是一个无意义的结果集

select E.ENAME ,D.DNAME,E.DEPTNO
from ZSEMP e, ZSDEPT d --表的别名 where e.deptno=d.deptno 当关联查询的表中有同名字段, 需要通过表明或者别名来指定该字段的归属

在关联查询中过滤条件与连接条件 必须同时成立 查看RESEARCH部门的员工信息 select E.ename,D.DNAME from ZSEMP E,ZSDEPT D where E.DEPTNO=D.DEPTNO and D.DNAME ='RESEARCH'

select E.ENAME, D.DNAME --不添加关联条件会产生笛卡尔积,要避免 from ZSEMP E,ZSDEPT D

内连接 e1 join e2 on 关联条件 select E.ENAME,D.DNAME from ZSEMP E join ZSDEPT D
on E.DEPTNO=D.DEPTNO where E.ENAME='FORD' 关联查询忽略不满足关联条件的记录

外连接 外链接在关联查询时可以将不满足连接条件的记录也查询出来 外连接分为: 左外链接 右外链接 全外链接 左外链接:以JOIN左侧表作为驱动表 驱动表中所有的数据都要列出来,那么当该表某条记录不满足连接条件时,那么来自右侧表 的字段值全部为NULL select E.ENAME,D.DNAME,D.LOC from ZSEMP E left outer join ZSDEPT D --左外链接 on E.DEPTNO=D.DEPTNO

select E.ENAME,D.DNAME,D.LOC from ZSEMP E right outer join ZSDEPT D --右外链接 on E.DEPTNO=D.DEPTNO

select E.ENAME,D.DNAME,D.LOC from ZSEMP E full outer join ZSDEPT D --全外链接 on E.DEPTNO=D.DEPTNO

自连接 当当前表中的一条记录可以对应当前表中的一条其他记录时 这种设计称为自连接 查看每个与昂以及上司的名字 select E.ENAME,M.ENAME --自连接实现 from ZSEMP E,ZSEMP M where E.MGR=M.EMPNO

select E.ENAME,M.ENAME --内连接实现 from ZSEMP E join ZSEMP M on E.MGR =M.EMPNO

查看SMITH的上司是谁在哪工作 select E.ENAME,N.LOC from ZSEMP E join ZSEMP M on E.MGR=M.EMPNO join ZSDEPT N on E.DEPTNO=N.DEPTNO where e.ename='SMITH'

select * from zsemp

子查询 自查询是一条查询语句,它是嵌套在其他 SQL语句中的,目的是为了给外层查询 提供数据 查看谁的工资高于CLARK? select ENAME ,SAL from ZSEMP where SAL >(select SAL from ZSEMP where ename='CLARK' )

和SMITH同职位的人 select ENAME from ZSEMP where job =(select job from ZSEMP where ename ='SMITH' )

查看谁的工资高于公司平均工资 select ENAME , SAL from ZSEMP where SAL>(select AVG(SAL) from ZSEMP )

在DDL中使用子查询 可以根据一个查询结果集快速构建一张表 select E.EMPNO,E.ENAME,E.SAL,E.job,D.DEPTNO,D.LOC from ZSEMP E left outer join ZSDEpt D on E.DEPTNO=D.DEPTNO

create table ZICHAXUNBIAO as select E.EMPNO,E.ENAME,E.SAL,E.job,D.DEPTNO,D.LOC from ZSEMP E, ZSDEPT D where E.DEPTNO=D.DEPTNO(+) 右外连接

desc ZICHAXUNBIAO

DML语句中使用子查询 将SMITH所在部门的员工工资上调20% update ZSEMP set SAL =SAL*1.2 where DEPTNO=(select DEPTNO from ZSEMP where ename='SMITH' )

将CLARK同部门的员工删除 delete zsEMP where DEPTNO=(select DEPTNO from ZSEMP where ename='CLARK' )

当子查询为多行单列时,那么在用作判断条件 中时要搭配IN any ALL使用 查看与职位是SALESMAN同部门的其他职位员工 select ENAME,job ,deptno from ZSEMP where DEPTNO in (select DEPTNO from ZSEMP where job='SALESMAN' ) and job<>'SALESMAN'

查看比职位是SALESMAN和CLERK工资都高的员工 select ENAME,SAL from ZSEMP where SAL>all(select SAL from ZSEMP where job in('SALESMAN','CLERK') )

EXISTS关键字 EXISTS关键字后面跟一个子查询,当该子查询可以查询出 至少一条记录时,EXISTS条件成立 查看有员工的部门有哪些 select D.DEPTNO,D.LOC from ZSDEPT d where exists(select * from ZSEMP E where e.deptno=d.deptno ) 查看那些人是领导 select ENAME,job from ZSEMP e where exists(select *from ZSEMP M where M.MGR=E.EMPNO) 查看部门最低薪水,前提是该部门的最低薪水高于30号部门 select min(SAL),DEPTNO from ZSEMP group by deptno having min(SAL)>(select min(SAL) from ZSEMP where deptno =30 )

在FROM子句中使用子查询通常是将子查询的结果当做一张表 看待,基于该查询的结果进行二次查询使用

查看谁的工资高于其所在部门的平均工资

select e.ENAME ,e.SAL,e.DEPTNO from zsemp e , (select deptno ,AVG(SAL) avgsal from ZSEMP group by DEPTNO) S where e.deptno =s.deptno and e.sal>s.avgsal

子查询用于select select E.ENAME,E.deptno,(select D.DNAME from ZSDEPT d where E.DEPTNO=D.DEPTNO) DNAME from ZSEMP E

select E.ENAME,E.DEPTNO,D.DNAME from ZSEMP E,ZSDEPT D where E.DEPTNO=D.DEPTNO(+)

分页查询 分页查询就是将数据分段查询出来,一次只能查询数据的一部分 这样做可以减少系统资源的开销,减少数据量可以提高网络传输速度

分页在不同数据库中的SQL语句不同

ORACLE中提供了一个伪列:rownum ROWNUM字段不存在于任何一张表,但是每张表都可以查询 该字段,该字段的值是结果集中每条纪录的行号. ROWNUM字段的值是动态生成的,伴随查询过程. 只要可以查询出一条记录,ROWNUM就会为该条记录生成行号,从1开始每次递增1

由于ROWNUB是在查询表的过程中进行编号的,所以在使用ROWNUB的 对结果集编行号的查询过程中不要使用ROWNUB做大于1以上数字的判断 否则结果集没有任何数据

select * from (select rownum R,T.* from (select ENAME, SAL from ZSEMP order by SAL desc) t) where R between 6 and 10

select * from (select ROWNUm R,T.* from (select ENAME ,SAL from ZSEMP order by SAL desc) T where rownum<=10) where r>=6

PAGESIZE 每页显示的条目数 page 页数

start:(PAGE-1)PAGESIZE+1 end:PAGEPAGESIZE

DECODE语句 DECODE(字段,CHAR1,1,CHAR2,2) 字段=char1 就返回1 select ENAME,job,SAL, DECODE(job, 'SALESMAN',SAL1.05, 'MANAGER',SAL1.2, 'ANALYST',SAL1.1, SAL) SSAL from zsemp 类似DECODE的CASE语句 select ENAME,job,SAL, case job when'SALESMAN' then SAL1.05 when'MANAGER' then SAL1.2 when 'ANALYST' then SAL1.1 else SAL end SSAL from zsemp

用在group by中 select COUNT(*),DECODE(job, 'MANAGER','VIP', 'ANALYST','VIP', 'OTHER') from ZSEMP group by DECODE(job, 'MANAGER','VIP', 'ANALYST','VIP', 'OTHER') 用在ORDER by 排序中 select dNAME,loc from ZSDEPT order by DECODE(DNAME, null值最大排在最后 'OPERATIONS',1, 'ACCOUNTING',2, 'SALES',3)

排序函数 排序函数允许将结果集按照指定字段进行分组 在组内按照指定字段排序,然后该函数为每组生成一个行号

row_number():生成组内连续且唯一的数字

查看每个部门的工资排名 select ENAME,SAL,DEPTNO, ROW_NUMBER() over( partition by DEPTNO order by sal desc) wc from ZSEMP

RANK语句 生成不连续且不唯一的数字 select ENAME,SAL,DEPTNO, RANK() over( partition by DEPTNO order by SAL desc) WC from zsemp

DENSERANK 函数 生成数字连续且不唯一的数字 select ENAME,SAL,DEPTNO, denserank() over( partition by DEPTNO order by SAL desc) WC from ZSEMP

create table ZSSALES( YEAHID number not null, MONTHID number not null, DAYID number not null, SALESVALUE number(10,2) not null ) insert into ZSSALES select TRUNC(DBMSRANDOM.value(2010,2012)) as YEAHID, TRUNC(DBMSRANDOM.value(1,13)) as MONTHID, TRUNC(DBMSRANDOM.value(1,32)) as DAYID, ROUND(DBMSRANDOM.value(1,100),2) as SALESID from DUAL connect by level <=1000

集合 union 并集 select ENAME,SAL,job from ZSEMP where SAL>2500 union select ENAME, SAL, job from ZSEMP where job ='SALESMAN'

union all 全并集 select ENAME,SAL,job from ZSEMP where SAL>1000 union select ENAME ,SAL,job from ZSEMP where job ='SALESMAN'

intersect 交集 select ENAME,SAL,job from ZSEMP where SAL>1000 intersect select ENAME,SAL,job from ZSEMP where job='SALESMAN'

minus 差集
select ENAME,SAL,job from ZSEMP where SAL>1000 minus select ENAME,SAL,job from ZSEMP where job ='SALESMAN'

高级分组函数 1 rollup() group by rollup(a,B,C) 等同于 group by a,B,C union all group by a,B union all group by a union all 全表

查看每天,每月,每年,及所有营业额 select YEAHID,MONTHID,DAYID, sum(salesvalue) from ZSSALES group by rollup(yeahid,monthid,day_id)

2 cube() CUBU的分组策略为每个参数的组合进行以此分组 group by cube(a,B,C) 次数为2的参数个数次方 ABC AB AC BC a B C 全 select YEAHID,MONTHID,DAYID,SUM(SALESVALUE) from ZSsales group by cube(YEAHID,MONTHID,DAYID) order by yeahid

查看每天与每月的营业额 grouping sets() 该函数允许自行制定分组策略,然后将这些分组统计的结果并在一起,函数的 每个参数为一种分组方式 select YEAHID,MONTHID, DAYID,SUM(SALESVALUE) from ZSSALES group by grouping sets( (YEAHID,MONTHID,DAYID),(YEAHID,MONTHID) ) order by yeahid

视图 视图也是数据库对象之一 在SQL语句中体现的角色与表一致,但视图只是对应一个查询语句的结果集 创建视图: CREATe view VzsEMP10 as select EMPNO,SAL,ENAME,DEPTNO from ZSEMP where deptno =10

desc VZSEMP10 select * from Vzsemp10

视图根据对应的SQL语句不同,分为 简单视图,复杂视图,连接视图 连接视图视为复杂视图的一种

当对应的子查询不含有函数,表达式,分组,去重,关联函数的视图成为 简单视图,相反就是复杂视图,连接视图,指子查询使用了关联查询

视图对应的子查询的查询字段可以使用别名 那么该字段的名字就是这个别名,若字段含有 函数或表达式,那么该字段必须指定别名

存在就修改,不存在创建 create or replace view VZSEMP10 as select Ename,empno,SAL ,DEPTNO from zsemp where deptno =10

对视图进行DML操作 对视图进行DML操作就是对视图数据来源的基础表 进行的操作 只能对简单视图就行DML操作,复杂视图不可以 insert into VZSEMP10 对简单视图进行插入操作,基表无法再视图显示的值将为NULL,若该值为 (ENAME,EMPNO,SAL,DEPTNO) 非空,则将插入失败 values ('jack',1001,2000,10)

update VZSEMP10 set SAL=3000 where ename='jack'

delete VZSEMP10 where ename='jack'

对视图操作不当会造成基表的污染 insert into VZSEMP10 --基表中有新的数据,但视图对此数据不可控 同update values ('jack',1000,2000,20) 可以为视图添加检查选项,来保证视图进行DML操作时不会对基表造成污染 with check option 但视图添加了检查选项后,视图要求对视图中数据进行DML操作后,视图必须对该记录可见,否则不允许操作 create or replace view VZSEMp10 as select ENAME,EMPNO,SAL,DEPTNO from ZSEMP where DEPTNO =10 with check option

为视图添加只读选项 当一个视图添加了只读选项,该视图不能进行DML操作 with read only

create or replace view VZSEMP10 as select ENAME,EMPNO,SAL,DEPTNO from ZSEMP where DEPTNO =10 with read only

查看数据字典,有助于了解曾经创建过的对象 select * from USEROBJECTS where objectname like '%ZS%' select * from USERVIEWS select * from USERTABLES

复杂视图(不能进行dml操作)

查看各个部门的工资情况 create view VDEPTSAL as select min(E.SAL) MINSAL, max(E.SAL) MAXSAL, AVG(E.SAL) AVGSAL, SUM(E.SAL) SUMSAL, E.DEPTNO,D.dname from ZSEMP E ,ZSDEPT D where E.DEPTNO =D.DEPTNO group by e.deptno ,d.dname

查看谁的工资高于部门平均工资 select e.ENAME ,e.SAL ,e.DEPTNO from ZSEMP E,VDEPTSAL T where E.DEPTNO=T.DEPTNO and E.SAL>T.AVGSAL 查看每个部门工资最高的员工? select E.ENAME,E.SAL,E.DEPTNO from ZSEMP E,VDEPTSAL T where E.DEPTNO=T.DEPTNO and e.sal=t.maxsal

序列 序列也是数据库对象之一 作用是根据指定的规则生成一系列数字 一般用于伪表每一条记录的主键字段提供值

创建序列 create sequence SEQZSEMPID start with 1 increment by 1

select * from SEQZSEMPID 序列支持两个伪列: NEXTVAL:获取序列下一个数字,序列会根据序列最后 生成的数字加上步进来得到,NEXTVAL会导致序列 发生步进,序列是不能回退的. CURRVAL:获取序列最后一次生成的数字,需要注意的是: 新创建的序列必须在使用NEXTVAL生成一个数字后才可以使用 currval

select SEQZSEMPID.NEXTVAL from DUAL select SEQZSEMPID.CURRVAL from dual

insert into ZSEMP (ENAME,job,EMPNO,SAL) values ('rosenod','jjj',SEQZSEMPID.NEXTVAL,3600)

删除序列 drop sequence seqzsempid

索引 在ZSEMP上的ENAME字段添加索引 create index idxzsempename on zsemp(ename) 索引是数据库对象之一 索引是加快查询效率的机制 索引的建立以及应用是数据库自行完成的

符合索引,是基于多个列的索引

如果经常在ORDER BY语句中使用JOB和SAL作为排序的依据,可以建立 复合索引 create index IDXENAMEJS on ZSEMP(job,SAL) 当只用JOB,SAL 作为条件查询时 ,会自动应用此索引 select ENAME,job,sal from ZSEMP order by job,SAL

创建基于函数的索引 create index IDXZSEMPUPPER_ENAME on ZSEMP(UPPER(ENAME))

LIKE模糊查询不会使用索引

重建索引 alter index idxzsempename rebuild

删除索引 drop index IDXZSEMPENAME

约束 非空约束 create table EMPLOYEES( ENAME number(10), SAL number(6,2), HIREDATE date constraint employeeshiredatenn not null)

select CONSTRAINTNAME,COLUMNNAME from DBACONSCOLUMNS where TABLENAME='employees'; alter table employees drop constraint employeeshiredate_nn 清除约束

修改表时添加非空约束

alter table EMPLOYEES modify (ENAME number(6,2) not null)

取消非空约束 alter table EMPLOYEES modify (ename number(6,2) null)

唯一性约束 唯一性约束要求该字段每条记录的值不能重复,NULL除外] unique create table EMPLOYEES222( id number(10) unique, ENAME VArCHAR2(10), SAL number(6,2), HIREDATE date, constraint EMPLOYESS1SALUK unique(sal) )

insert into EMPLOYEES1 (id,ENAME,SAL) values (1,'assas',5421)

给ENAME字段添加唯一约束 alter table EMPLOYEES1 add constraint employess1nameuk unique(ename)

主键约束 主键约束要求该字段的值唯一且不为空 主键约束只能在一张表的一个字段上建立 主键:使用该字段的值可以唯一定位表中的一条数据 create table EMPLOYEES2( id number(10) primary key, ENAME VALCHAR2(20), SAL number(10), HIREDATE date )

外键约束 ALTER TABLE employees4 ADD CONSTRAINT employees4deptnofk FOREIGN KEY (deptno) REFERENCES dept(deptno);

直接在定义字段后添加 REFERENCES account(accountid) B.在表所有字段定义结束后添加 FOREIGN KEY(accountid) REFERENCES account(accountid) C.在表创建后执行如下命令： ALTER TABLE service ADD FOREIGN KEY(accountid) REFERENCES account(accountid) desc employees

检查约束 alter table EMPLOYEeS2 add constraint EMPLOYEES2SALCHECK check(sal>2000)

我们可以从一个表中复制所有的列插入到另一个已存在的表中： INSERT INTO table2 SELECT * FROM table1; 或者我们可以只复制希望的列插入到另一个已存在的表中： INSERT INTO table2 (columnname(s)) SELECT columnname(s) FROM table1;

1. 复制表结构及其数据： create table tablenamenew as select * from tablenameold
2. 只复制表结构： create table tablenamenew as select * from tablenameold where 1=2; 或者： create table tablenamenew like tablenameold

您可以把所有的列插入新表：

SELECT * INTO newtablename [IN externaldatabase] FROM old_tablename 或者只把希望的列插入新表：

SELECT columnname(s) INTO newtablename [IN externaldatabase] FROM oldtablename

MERGE INTO dosscard d USING (select devicecode from specialmeasure where measureid=? ) t
ON (d.devicecode=t.devicecode )
WHEN MATCHED THEN
update set specialid='measure' ,special='计量器具',isspecial='1',typeno=?,measurerange=?,degree=?,memo=?,measureattribute=? where craftcode=? WHEN NOT MATCHED THEN insert (devicecardid,auditing,devicecode,craftcode,typeno,devicename,devicetype,measurerange,degree,factory,outno,outdate,deptname,usestate,abctype,installplace,usedate,orivalue,memo,typename,createdate, special, specialid,isspecial,measure_attribute) values({NEWKEYID},1,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'仪表设备',{TODAY},'计量器具','measure','1',?)