目录
一, 表结构(DDL)
DDL数据库定义(create、alter、drop、rename)
1,创建一张表
CREATE TABLE table_name (
id NUMBER(4),
name VARCHAR2(20) NOT NULL,
gender CHAR(1) DEFAULT 'M',
birth DATE,
salary NUMBER(6,2),
jobVARCHAR2(30),
deptno NUMBER(2)
);
2,查看表结构
DESC table_name
3,删除表结构
DROP TABLE table_name
4,修改表名
RENAME old_name TO new_name
5,为表添加表字段
ALTER TABLE table_name
ADD (hiredate DATE DEFAULT sysdate);
6,删除表中字段(一列)
ALTER TABLE emp_fanchuanqi
DROP (hiredate);
7,修改表字段
ALTER TABLE table_name
MODIFY (
job VARCHAR2(40)DEFAULT 'CLERK'
);
select * from user_tables where table_name like 'WF%' --查询当前用户所拥有的表
二, 表数据(DML)
DML数据库操纵(insert、delete、select、update、truncate)
1,插入数据
INSERT INTO
emp_fanchuanqi(id,name,salary)
VALUES(1,'boss',1500)
2,查询表数据
SELECT * FROM emp_fanchuanqi / dual(虚表) ;
3,修改表数据
UPDATE emp_fanchuanqi
SET job='MANAGER'
WHERE salary=8500;
4,删除表数据
DELETE FROM emp_fanchuanqi
WHERE name='tom'
三, 函数
1,TO_DATE()函数
SELECT
TO_DATE(
'2008-08-1222:45:33', 'yyyy-mm-ddhh24:mi:ss' )
FROM DUAL;
SELECT
TO_CHAR(
sysdate, 'yyyy"年"mm"月"dd"日" hh:mi:ss' )
FROM DUAL;
2,“||”的用法
"||"在数据库中是连接字符串,相当于java中的"+",注意和java中的"||"区分。
例如:
java中 "hello"+"world" ==> "helloworld"
DB中 ‘hello’||'world' ==> 'helloworld'
oracle中 CONCAT('hello','world') ==>'helloworld'
'think'||'in'||'java' 等价于CONCAT(CONCAT('think','in'),'java')
3,LENGTH函数
SELECT ename,LENGTH(ename)
FROM table_name
4,大写,小写,首字母大写
SELECT
UPPER(ename),
LOWER(ename),
INITCAP(ename)
FROM table_name;
5,去空白,左空白,右空白
SELECT TRIM('e'from'eeeeeliteeeeee') FROM DUAL;
SELECT LTRIM('eeeeliteeeee','e') FROM DUAL;
SELECT RTRIM('eeeeliteeeee','e') FROM DUAL;
6,补字符
SELECT LPAD('aaa',5,'$') FROM dual ------ $$aaa
SELECT RPAD('aaa',5,'$') FROM DUAL ------ aaa$$
7,NVL函数
NVL函数的作用:将null替换为给定值
SELECT ename,sal,NVL(comm,0) FROM emp_fanchuanqi
SELECT ename,NVL2(comm,'有奖金','没奖金') FROMemp_fanchuanqi
8,AND与OR
AND优先级是高于OR的。
SELECT ename, sal,job FROM emp_fanchuanqi
WHERE
sal > 1250 AND job = 'CLERK' OR job = 'SALESMAN'
9,ANY,ALL与IN
>ANY 大于最小的
<ANY 小于最大的
>ALL 大于最大的
<ALL 小于最小的
区别:in是与给定的数据进行等值或不等值比较
any和all是与给定数据进行范围比较
SELECT ename, sal, job FROM emp_fanchuanqi
WHERE
job IN ('MANAGER','SALESMAN','CLERK')
SELECT empno, ename, job, sal, deptno FROM emp
WHERE sal > ALL(2500,4000,4500);
10,聚合函数
用在where后必须有group by
例:查看每个部门中每种职位的工资情况
SELECT
MAX(sal), MIN(sal),AVG(sal), SUM(sal) FROM emp
GROUP BY deptno,job
例:查看平均工资超过1800的部门,以及平均工资
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno HAVING AVG(sal)>1800
11,左右连接
--内连接和where相同
inner join
--左向外连接,返回左边表所有符合条件的
left join
--右向外连接,返回右边表所有符合条件的
right join
--完整外部连接,左向外连接和右向外连接的合集
full join
例:select* from a left join b on a.id=b.id;
12,like的用法
like匹配符有:
%:0个或多个任意字符
_:一个任意字符
like '%a' :以a结尾的字符串
like 'a%' :以a开始的字符串
like '%a%' :包含a开始的字符串
13,子查询
例:简单子查询(where后面的子查询)
SELECT ename,sal FROM emp
WHERE
sal < (SELECT AVG(sal) FROM emp)
例:查看 与SALESMAN相同部门的其他员工都有谁
SELECT ename,job,deptno FROM emp
WHERE
deptno IN (SELECT deptno FROM emp WHERE job='SALESMAN')
AND job <> 'SALESMAN'
例:比30号部门最低工资高的最低工资的部门
SELECT deptno,MIN(sal) FROM emp
GROUP BY deptno
HAVING
MIN(sal) >(SELECT MIN(sal) FROM emp WHERE deptno=30)
例:from后面的子查询
SELECT ename,sal,deptno FROM
emp e, (SELECT deptno , AVG(sal) avgsal FROM emp GROUP BY deptno) a
WHERE e.deptno = a.deptno AND e.sal > a.avgsal
14,分页
分页的算法:page页数 pagesize一页的条数
(page-1)*pagesize+1 start
page*pagesize end
分页的三步:
1:排序
2:编号
3:取范围
SELECT *
FROM (
SELECTROWNUM rw,e.*
FROM (
SELECT empno,ename,sal
FROM emp
ORDER BY empno
) e
)
WHERE rw BETWEEN 1 AND 3
15,常用总结
Group by 必须放在order by和limit之前,distinct(去重)必须放在开头,
union和union all
SQL中UNION和Union All指令的目的是将两个SQL语句的结果合并起来。union和Union All只是将两个结果联结起来一起显示,并不是联结两个表。区别是:union是集合中的并集运算,即把所有的查询结果并集处理,而union all表示无论多条结果中有无重复都将各自的结果相加,而不删除其中重复部分。