SQL基础
来源:互联网 发布:伦敦高级应召女郎 知乎 编辑:程序博客网 时间:2024/06/04 01:28
- select语句
- Select * from employees;
- 数值或日期类型的字段可以进行‘加减乘除’运算。
- null不能进行四则运算
- 连接符‘||’
- 去重复:distinct
- 条件和排序
- Select * from employees e where e.last_name like '_o%';
- Select * from t_char where a like '%K%%' escape 'K'; (escape用来指定转义字符),上一句查询结果等同于 Select * from t_char where a like '%\%%' escape '\';
- In, not in, is null, is not null ,like ,>,<,=,<>,!=,
- 单行函数
- 单行sql函数类型:
- General:
- character:
- lower('AAA'),
- upper('aaa'),
- initcap('cAp')--首字母大写,
- Concat('Hello','World'),连接字符串
- Substr('HelloWorld',1,5),从第一位开始截取5个字符
- Length('HelloWorld'),
- Instr('HelloWorld','W'),寻找特定字符在字符串中的位置
- Lpad('salary',10,'*'),保留10位数字,长度不够的用*从左边补齐
- Rpad('salary',10,'*'),保留10位数字,长度不够的用*从右边补齐
- Trim('H' from 'HelloWorld'),从Helloworld中去掉字符H,注意:位于字符串中间的字符无法去掉,只能去掉头尾位置的字符(包括空格)ps:Trim('H' from ' HelloWorld ')无法去掉H。
- Trim(' HelloWorld')
- number:
- round(34.239, 2)四舍五入保留两位小数(Round(34.239 ,-1)相当于精确到十位)
- Trunc(34.239, 3)直接保留两位小数,不进行四舍五入
- Mod(13, 4)取余(模)运算
- date,
- SELECT last_name, Months_between ( SYSDATE, hire_date) FROM employees WHERE manager_id = 100;计算从hire_date到当前时间之间的月份差
- Add_months(sysdate, 6)当前月份加上6个月
- Next_day(sysdate,'星期五'),计算当前日期的下个周五的日期
- Next_day(sysdate, 2) ,明天的日期加上两天
- Last_day(sysdate),当前月份的最后一天
- Round(sysdate,'month'),精确到月份(下半月会舍入到下一月)
- Round(sysdate,'year'),精确到年
- Trunc(sysdate,'month')--2016-07-01 精确到月份,不论上下半月都是到本月
- round(sysdate,'year')--2017-01-01 精确到年
- To_char()函数,日期到字符串的转换
- 单行sql函数类型:
日期格式化元素:
- yyyy,
- year,
- Mm
- Month
- mon,三个字母的英文描述月份简称
- Dd
- day,英文描述的星期几
- dy,三个字母的英文描述的星期几
- HH24:MI:SS AM时分秒的格式化
- DDspth,英文描述的月中的第几天
- fm,格式化关键字,可选
- Select last_name, to_char(hire_date,'fmDD " of" month yyyy') as hiredate
From employees; --17 of 6月 1987
- Conversion
select null, nvl(null,1) from dual; --null,1 找到第一个不为空的
select NULLIF (1, 1),NULLIF(1, 2) fromdual; --null,1 (相等为空,不等就返回第一个)
select COALESCE(null,null,1,null,2 )from dual; --找到第一个不为空的内容
SELECT NVL2(0,'1','2') FROM dual; --判断第一个,若为空返回第三个,否则返回第二个
- 条件表达式
SELECT last_name
,job_id
,salary
,CASE job_id
WHEN 'IT_PROG' THEN
1.10 * salary
WHEN 'ST_CLERK' THEN
1.15 * salary
WHEN 'SA_REP' THEN
1.20 * salary
ELSE
salary
END "REVISED_SALARY"
FROM employees;
SELECT last_name
,job_id
,salary
,decode(job_id
, 'IT_PROG'
, 1.10 * salary
, 'ST_CLERK'
, 1.15 * salary
, 'SA_REP'
, 1.20 * salary
, salary) revised_salary
FROM employees;
- 多表连接
- 外联接
SELECT E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+);--右外连接
SELECT E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES ERIGHT OUTER JOIN DEPARTMENTS D
ONE.DEPARTMENT_ID = D.DEPARTMENT_ID;-右外连接
ps:需要内容完整的表内容,所以内容少的表需要加‘+’
SELECT E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;--全外连接
- Using
SELECT E.LAST_NAME, DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
USING (DEPARTMENT_ID);
- 子查询
SELECT employee_id
,last_name
,job_id
,salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
SELECT employee_id
,last_name
,job_id
,salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
- 事务控制
- Commit
- Rollback
- savepoint
- 锁
- Select * from emp for update nowait;
- 表
- 数据类型
Varchar2(size)
可变长字符串
Char(size)
定长字符串
Number(p,s)
可变长数值
date
日期时间
Long
可变长大字符串,最大可到2G
clob
可变长大字符串数据,最大可到4G
Raw and long raw
二进制数据
Blob
大二进制数据,最大可到4G
Bfile
存储与外部文件的二进制数据,最大可到4G
Rowid
64进制十八位长度的数据,用以标识行的地址
Timestamp
精确到分秒级的数据类型
Interval year to month
表示几年几个月的间隔
Interval day to second
表示几天几小时几分几秒的间隔
- 数据类型
- 表的操作
- 添加字段
Alter table testtab3 add (test_code varchar(20));
- 修改字段
- Alter table testtab3 modify (test_code number);
- 删除字段
- Alter table testtab3 drop test_code;
- 删除表
- Drop table testtab3;
- 清空表
- Delete from testtab3 where …;
- Truncate table testtab3;
- truncate与delete的区别:1.没有rollback机会,2.HWM标记复位
- 更改表名
- Rename oldname to newname;
- 约束
- 常用的约束有:
NOT NULL
非空约束
UNIQUE
唯一约束
PRIMARY KEY
主键约束
FOREIGN KEY
外键约束
CHECK
自定义约束
- 创建约束的方法
- 创建表的时候同时创建
- 另外单独创建
- 常用的约束有:
create table testtab3
( Pk1 number ,
field1 varchar2(200)
);
- ALTER TABLE testtab3 ADD CONSTRAINT testtab3_PK PRIMARY KEY(Pk1) ;
- ALTER TABLE DETARTMENTS DROP PRIMARY KEY CASCADE;级联删除
- ALTER TABLE DETARTMENTS DROP constraint emp_id_fk;
- ALTER TABLE EMPLOYEES DISABLE/ENABLE CONSTRAINT EMP_ID_PK;失效/生效约束
- ALTER TABLE TESTTAB3 DROP (PK1) CASCADE CONSTRAINTS;
- 视图
- Create view as select ….
- 序列
CREATE SEQUENCE seq_test
INCREMENT BY 2
START WITH 0
MAXVALUE 9999999999
MINVALUE 0
NOCYCLE
CACHE 20;
SELECT seq_test.nextval FROM dual;
SELECT seq_test.currval FROM dual;
ALTER SEQUENCE seq_test
INCREMENT BY 10
MAXVALUE 10000
NOCACHE;
- 索引
- Create index emp_last_name on employees(last_name);
- 函数索引,如果对某一列使用函数,则这一列的索引是用不到的,除非建立函数索引
- 同义词
- CREATE SYNONYM EMPl FOR SCOTT.EMP;
- 集合操作
并集
Union
并集(保留重复项)
Union all
差集
Minus
交集
intersect
- 用户权限
CREATE USER hposIDENTIFIED BY hpos;
ALTER USER hpos DEFAULT TABLESPACE hpos_data QUOTA UNLIMITED ON hpos_data;
ALTER USER hpos TEMPORARY TABLESPACE temp;
GRANT CREATE SESSION
,CREATE TABLE
,CREATE PROCEDURE
,CREATE SEQUENCE
,CREATE TRIGGER
,CREATE VIEW
,CREATE SYNONYM
,ALTER SESSION
TO hpos;
GRANT RESOURCE TO hpos;
CREATE ROLE manager;
GRANT CREATE TABLE, CREATE VIEW TO manager;
GRANT manager TO a, b, c;
REMOVE SELECT ON department FROM scott;
- 跨数据库访问数据(创建DB-LINK)
CREATE PUBLIC DATABASE LINK hg.acme.com
USING 'sales';--sales指的是hg.acme.com中的一个用户
Select * fromemp@hq.acme.com;
- Group by增强
- 在group by中使用rollup产生常规的分组汇总行,以及分组小计
SELECT department_id
,job_id
,SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BYROLLUP(department_id, job_id);
- 在group by中使用cube产生常规的分组汇总,从左到右再从右到左,共汇总两次
SELECT department_id
,job_id
,SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BYCUBE(department_id, job_id);
- 使用grouping
SELECT department_id deptid
,job_id job
,SUM(salary)
,GROUPING(department_id) grp_dept
,GROUPING(job_id) grp_job
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
SELECT department_id deptid
,job_id job
,SUM(salary)
,GROUPING(department_id) grp_dept
,GROUPING(job_id) grp_job
FROM employees
WHERE department_id < 50
GROUP BY cube(department_id, job_id);
- 子查询进阶
SELECT last_name
,salary
,department_id
FROM employees OUTER
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id =outer.department_id);
内部与外部使用同一张表,利用相同的字段进行关联
WITH dept_costsAS
(SELECT d.department_name
,SUM(e.salary) AS dept_total
FROM employees e
,departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS
(SELECT SUM(dept_total) / COUNT(*) AS dept_avg FROM dept_costs)
SELECT * FROM dept_costs WHERE dept_total > (SELECT dept_avg FROM avg_cost) ORDER BY department_name;
虚拟表dep_costs和表avg_cost,最后使用一个查询语句
- 递归查询
SELECT last_name || ' reports to ' ||PRIOR last_name "Walk Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
CONNECT BY PRIOR:从上往下查的时候,上级的employee等于下级的manager_id
SELECT employee_id
,last_name
,job_id
,manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id;
从下往上查,下级的employee等于上级的manager_id
- 分析函数
SELECT E.LAST_NAME,
E.SALARY,
D.DEPARTMENT_NAME,
AVG(E.SALARY)OVER(PARTITION BY D.DEPARTMENT_NAME) DEPARTMENT_AVG_SALARY,
MAX(E.SALARY)OVER(PARTITION BY D.DEPARTMENT_NAME) DEPARTMENT_MAX_SALARY,
MIN(E.SALARY)OVER(PARTITION BY D.DEPARTMENT_NAME) DEPARTMENT_MIN_SALARY
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SELECT D.DEPARTMENT_NAME,
E.LAST_NAME,
E.SALARY,
RANK() OVER(PARTITION BY D.DEPARTMENT_NAME ORDER BY E.SALARY DESC) DEPT_SALARY_RANK1,
DENSE_RANK() OVER(PARTITION BY D.DEPARTMENT_NAMEORDER BY E.SALARY DESC) DEPT_SALARY_RANK2,
ROW_NUMBER() OVER(PARTITIONBY D.DEPARTMENT_NAME ORDERBY E.SALARY DESC) DEPT_SALARY_RANK3
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
- 闪回
SELECT * FROM departmentsAS OF TIMESTAMP SYSDATE -5/(24*60)
WHERE department_name = 'Shipping';
删除的信息可以通过闪回找到,闪回的时间跟数据库的设置有关
- 临时表
- Create global temporary table temp_table_session(head_id number)on commit preserve rows;
- 全局临时表 在我门开发程序中经常使用,在不同的会话之间可以屏蔽数据,可以有触发器,检查约束,索引等。
- 基于会话
- 基于会话的临时表,在会话断开之前,或者通过一个delete或truncate物理的删除行之前,这些行会一直存在于这个临时表中,只有我的会话可以看到这些行,即使我已经提交了,其他会话也无法看到我的行。
- 基于事务
- 基于事务的临时表,我的会话提交时,临时表中的行就不见了,只需把分配给这个表的临时区段交回,这些行就会消失,在临时表的自动清除过程中不存在开销。
- 物化视图
- 在一段时间内保存固定的数据看,提供自动刷新和手动刷新的机制
CREATE MATERIALIZED VIEW materialized_view_name
BUILD [IMMEDIATE|DEFERRED] --1.创建方式
REFRESH [COMPLETE|FAST|FORCE|NEVER] --2.物化视图刷新方式
ON [COMMIT|DEMAND] --3.刷新触发方式
START WITH (start_date) --4.开始时间
NEXT (interval_date) --5.间隔时间
WITH [PRIMARY KEY|ROWID] --6.默认
primary key ENABLE QUERY REWRITE --7.是否启用查询重写
AS --8.关键字
SELECT STATEMENT; --9.基表选取数据的 select语句
CREATE MATERIALIZED VIEW employees_90 --创建物化视图
BUILD IMMEDIATE --在视图编写好后创建
REFRESH FAST WITH PRIMARY KEY --根据主表主键增量刷新(FAST,增量)
ON DEMAND --在用户需要时,由用户刷新
ENABLE QUERY REWRITE --查询重写
AS
SELECT * FROM employees where department_id = 90;
- 物化视图刷新
Begin
Dbms_mview.refresh('employees_90','f');
End;
- 字符函数使用
Select length('中国') from dual;
2
Select lengthb('中国') from dual;
4
Utf-8汉字 3个字节
Select substr('上海汉得',2,2) from dual;
海汉
Select substr('上海汉得',3,2) from dual;
汉得
Select substrb('上海汉得',2,2) from dual;
Select substrb('上海汉得',3,2) from dual;
海
- 策略函数
- 创建策略函数 Function:
CREATE OR REPLACE FUNCTION vpd_test(p_schema VARCHAR2, p_object VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN 'employee_id >=200';
END;
对数据库对象应用策略函数
BEGIN
dbms_rls.add_policy(object_schema => 'STUDENT1',
object_name => 'EMPLOYEES',
policy_name => 'VPD_TEST',
function_schema => 'STUDENT1',
policy_function => 'VPD_TEST');
END;
通过查询数据字典,确认数据库对象上是否有策略函数:
SELECT * FROM dba_policies t WHERE t.object_name = 'EMPLOYEES';
- SQL基础
- SQL基础
- SQL基础
- SQL 基础
- sql:基础
- SQL基础
- SQL基础
- SQL基础
- SQL基础
- SQL基础
- SQL 基础
- sql基础
- SQL基础
- sql基础
- SQL基础
- SQL基础
- sql 基础
- sql基础
- oracle什么时候需要commit
- django python报错:SNIMissingWarning,InsecurePlatformWarning
- 3.最基础的vi编辑命令笔记:vi(alias vi='vim')命令
- linux命令(1)
- jobdu1431
- SQL基础
- NFS实践及自动挂载问题
- 备案期间网站是否能正常访问
- Android L SystemUI 流程简要分析
- 你怎样看待梁定郊的贿谢之行.
- java并发库之Executors常用的创建ExecutorService的几个方法说明
- mongoDB学习笔记二
- HM编码器代码阅读(29)——码率控制
- java的锁机制