oracle总结

1、查看oracle版本信息
select * from v$version;
select * from PRODUCT_COMPONENT_VERSION;
2、展示当前登录用户
show user;
3、切换登录用户
connect scott/tiger;
4、’%A’ 某个字段以A结尾的数据
‘A%’ 某个字段以A开始的数据
‘A_Z’ 以A开头,以Z结尾,中间有一个任何字元的数据;
%AZ% 所有含有AZ的数据
5、having通常在sql语句的最后;
where —>>group by
6、外部连接(OUTER JOIN)
oracle 上,我们会在要选出所有资料的那个表格之后加上一个"(+)"来代表说这个表格中的所有
资料我们都要.
7、截取字符串
select substr(str,pos) from table t where t.id = ‘’;-----这个方法不适用与sqlserver。
取str字段所有从pos开始的字符。
8、数据表的链接有：
1)、内连接(自然连接):只有两个表相匹配的行才能在结果集中出现
2)、外连接:包括
(1)左外连接(左边的表不加限制) where a.a=b.a(+)
(1)右外连接(右边的表不加限制) where a.a(+)=b.a
(1)全外连接(左有两表都不加限制) where a.a=b.a(+) union where a.a(+)=b.a
3)、自连接(连接发生在一张基表内)
9 、order by 1; —按照查询结果集的第一栏排列.(默认是升序ASC)
10、
–查询每一年的出生人数
select to_char(e.birthday,‘yyyy’) as 年份, count() as 人数 from emp e group by to_char(e.birthday,‘yyyy’) order by 1 ;
–查询员工表中工资最高的前三名的员工信息。（ROWNUM）
select * from (select emp. from emp order by emp.wage desc) where rownum<=3;
–查询所有员工的信息，要求显示所在部门的名称（而不是编号）
select empno,ename,job,sal,( select d.dname from dept d where d.deptno=e.deptno ) AS 所在的部门名称 from emp e;
–按员工的工资由高到低排列，只查询出第6至第10条结果，包含第6与第10条结果 (rowNum 本身只能用<=的比较方式)
select * from (
select * from (select emp.*,rownum r from emp order by emp.wage desc) where r<=10
)where r>=6
–查询总和百分比
select e1.empname,e1.wage,e1.wage/(select sum(wage) from emp) pct_to_total from emp e1,emp e2
where e1.wage<=e2.wage or(e1.wage=e2.wage and e1.empname=e2.empname)
group by e1.empname,e1.wage
order by e1.wage desc,e1.empname desc;
11、decode(待比较的对象，如果为这个，那麼结果为这个，否则结果是这个)
select e.empname, e.wage from emp e order by decode(e.wage,3000,1,2), e.wage;

---

CREATE TABLE total_sales (
name varchar(32) default NULL,
sales mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

– Records of total_sales

---

INSERT INTO total_sales VALUES (‘John’, ‘10’);
INSERT INTO total_sales VALUES (‘Jennifer’, ‘15’);
INSERT INTO total_sales VALUES (‘Stella’, ‘20’);
INSERT INTO total_sales VALUES (‘Sophia’, ‘40’);
INSERT INTO total_sales VALUES (‘Greg’, ‘50’);
INSERT INTO total_sales VALUES (‘Jeff’, ‘20’);

##查询排名
SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

##查找中位数
SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);

##算出累计统计
SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

##sql算出总和百分比
SELECT a1.Name, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

sql算出累计总和百分比
SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;