WHERE用于行过滤。 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。
到字符串:to_char();到日期:to_date();到数字:To_number(char,’format’);
In:跟在WHERE 子句后面规定多个筛选值(where 列名 in (值1,值2,值3)); (确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选) Exsits:将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE,这一行行可作为外查询的结果行,否则不能作为结果。 (指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。) IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
通配符:%代替0到多个字符,_代替一个字符
Nvl(e1,e2):如果e1的值为null,返回e2,否则返回e1本身; Nvl2(e1,e2,e3):如果e1的值为null,返回e3,否则返回e2;
升序asc,降序desc,默认为升序;多列排序:oder by 列名,列名
1复制表中的数据到新建的表中,表不存在
create table tableName1 as select * from tableName2
2复制表中的数据
insert into tableName select * from tableName
1优化SQL查询,去掉不必要的列;2建立查询条件索引;
UNION 操作符用于合并两个或多个 SELECT 语句的结果集;UNION 内部的 SELECT 语句必须拥有相同数量的列,列也必须拥有相似的数据类型,每条 SELECT 语句中的列的顺序必须相同。 UNION 操作符选取不同的值,UNION ALL可以选取重复的数值;
Dual,伪表; CASCADE,级联删除; ||,连接字符串; SUBSTR (char, n, len),取子字符串,选取n位开始,长度为len的子串; LENGTH(char),找出一个字符串的长度; INSTR (char, substr),查找子串位置, LPAD | RPAD (str,len,padstr),返回字符串str,左/右填充至len长度,长度大于len则缩减; TRIM(),移除掉一个字串中的字头或字尾,最常见的用途是移除字首或字尾的空白; REPLACE (char, str1, str2),字符串替换,将子串str1替换为str2; LOWER(char),变为小写; UPPER(char),变为大写 INITCAP(char),首字母变为大写; Floor(),向下取整; Ceil(),向上取整; Round(),四舍五入;
SYSDATE,返回当前日期; ADD_MONTHS(date,n)函数可以得到date之前或之后n个月的新日期; LAST_DAY(date),返回当前月的最后一天; MONTHS_BETWEEN (date1, date2),用于计算date1和date2之间有几个月; NEXT_DAY(date,week),返回下周某一天的日期; TO_CHAR(date,’day’),返回日期date是周几;
例子:
返回两个日期之间的天数:
select floor(sysdate - to_date(‘20161018’,‘yyyymmdd’)) from dual;
Case函数一旦满足了某一个WHEN ,则这一条数据就会退出CASE WHEN,而不再考虑其他CASE; decode(列名,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值);
1、所有字段值都是不可分解的原子值; 2、一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中; 3、每一列数据都和主键直接相关,而不能间接相关;
内连接:INNER JOIN返回两个表中联结字段相等的行; 左连接:LEFT JOIN返回包括左表中的所有记录和右表中联结字段相等的记录; 右连接:RIGHT JOIN返回包括右表中的所有记录和左表中联结字段相等的记录;
GROUP BY 语句可结合一些聚合函数来使用,HAVING语句在GROUP BY语句后筛选数据。 SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
常用的聚合函数
AVG():返回数值列的平均值;
SELECT AVG(column_name) FROM table_name
COUNT():返回匹配指定条件的行数;
SELECT COUNT(column_name) FROM table_name;
FIRST()/LAST() :返回指定的列中第一个/最后一个记录的值;
SELECT FIRST/LAST (column_name) FROM table_name;
MAX()/MIN() :返回指定列的最大值/最小值;
SELECT MIN/MAX(column_name) FROM table_name;
SUM() :返回数值列的总数;
SELECT SUM(column_name) FROM table_name;
视图是基于 SQL 语句的结果集的可视化的表;视图中的字段就是来自一个或多个数据库中的真实的表中的字段。 CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。 --1、一组为了完成特定功能的SQL语句集 --2、第一次编译后再次调用不需要再次编译(高效) --3、不同的数据库存储过程定义的方式不同,思路是相同的,语法不同 CREATE OR REPLACE Procedure myProcedure(Name Varchar2)
Is
触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器可以分为两类:DML触发器和DDL触发器,DDL触发器它们会影响多种数据定义语言语句而激发,这些语句有create、alter、drop语句,DML触发器分为:after触发器(之后触发),insert触发器,update触发器,delete触发器。
NOT NULL(非空)、 UNIQUE(唯一)、 主键、 外键、 CHECK
序列是一种数据库对象,用来自动产生一组唯一的序号; CREATE SEQUENCE sequencename [INCREMENT BY n] 定义序列增长步长,省略为1 [START WITH m] 序列起始值,省略为1,一经创建不能修改 [{MAXVALUE n | NOMAXVALUE}] 序列最大值, [{MINVALUE n | NOMINVALUE}] 序列最小值 [{CYCLE | NOCYCLE}] 到达最大值或最小值后,继续产生序列(默认NOCYCLE) [{CACHE n | NOCACHE}]; CACHE默认是20
rownum用于标记结果集中结果顺序的一个字段,它的特点是按顺序标记,而且是连续的,换句话说就是只有有rownum=1的记录,才可能有rownum=2的记录。rownum关键字只能和<或者<=直接关联,如果是>或者=则需要给他起个别名。
select * from ( select * from ( select s.*,rownum rn from student s ) where rn<=5) where rn>0
1 Oracle数据库的基本类型:char,varchar2,number,date,clob; 2 Oracle的物理结构:数据文件,控制文件和日志文件; 3 Oracle的逻辑结构: 表空间--最大的逻辑存储结构,与物理上的数据文件相对应; 段--数据库终端用户将处理的最小单位; 区--磁盘空间分配的最小单位; 块--管理存储空间的最基本单位;
1:oracle 分页
select * from (select t.*, rownum rn from (select * from menu order by id desc) t where rownum < 10) where rn >=5
2: mysql 分页
select * from music where id limit 5,5
3:oracle中如何快速将一张表的数据复制到另外一张表中(另外一张表不存在,另外一张 表存在,但数据为空)
1、.不存在另一张表时:
create table 新表 as select * from 将要复制的表
2、存在另一张表时:
insert into 新表名 select 字段 from 将要复制的表名
4:音乐专辑
查询出special app:ds:special表中的id 专辑名 并下面有多少首歌曲
Select s.id , min(s.sname),count(m.mid) from special s inner
join ms m on s.id=m.id group by s.id
5:快速删除一张表(不可事物回滚,也就是没有日志记录)
TRUNCATE from 表名
6:inner join
select 查找信息 from 表名 1 inner join 表名2 on 表名1.列名 = 表名2.列名
7:left join
左外连接 select 查找信息 from 表名1 left join 表名2 on 表名1.列名 = 表名2.列名
8:right join
右外连接 select 查找信息 from 表名1 right join 表名2 on 表名1.列名 = 表名2.列名
9:oracle中查询遍历树形结构(start with)
select * from extmenu
start with pid=1
connect by prior id = pid
快速删除父节点以及父节点下的所有节点:
Delete from extmenu where id in (
elect * from extmenu
start with pid=1
connect by prior id = pid
)
10:查询出来60-70,80-90,95-100学生的信息
select * from stu where chengji between 60 and 70 or between 80 and 90 or between 95 and 100
select * from stu where chengji > 60 and chengji < 70 or chengji > 80 and chengji < 90 or chengji > 95 and chengji < 100
11:用exists替换in------进行联表查询
select * from dept where exists(select * from emp where emp.deptno=dept.deptno);
或
select * from dept d inner join emp e on d.deptno = e.deptno(只查询出两表共同拥有的字段数据)
12:删除表中的重复数据:
delete from xin a where a.rowid != (
select max(b.rowid) from xin b
where a.name = b.name
);
13:row_number(),rank() over ,dense_rank() over 按工资排序
select sal,
row_number() over(order by sal desc) rank1,
rank() over(order by sal desc) rank,
dense_rank() over(order by sal desc) drank
from emp
14:select * from (select emp.* from(
dense_rank() over(partition by departNo order by sal desc)
rk from emp )
Where rk=4