SQL基础

最新推荐文章于 2018-07-19 16:37:08 发布

小飞飞_2012

最新推荐文章于 2018-07-19 16:37:08 发布

阅读量322

点赞数

分类专栏：数据库文章标签： sql 编程数据库

本文链接：https://blog.csdn.net/xiaofeienen/article/details/55096445

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

select语句
1. Select * from employees;
2. 数值或日期类型的字段可以进行‘加减乘除’运算。
3. null不能进行四则运算
4. 连接符 ‘||’
5. 去重复：distinct

条件和排序
1. Select * from employees e where e.last_name like '_o%';
2. Select * from t_char where a like '%K%%' escape 'K'; (escape用来指定转义字符)，上一句查询结果等同于 Select * from t_char where a like '%\%%' escape '\';
3. In, not in, is null, is not null ,like ,>,<,=,<>,!=,
单行函数
1. 单行sql函数类型:
  1. General:
  2. character:
    1. lower('AAA')，
    2. upper('aaa'),
    3. initcap('cAp')--首字母大写,
    4. Concat('Hello','World'), 连接字符串
    5. Substr('HelloWorld',1,5),从第一位开始截取5个字符
    6. Length('HelloWorld'),
    7. Instr('HelloWorld','W'),寻找特定字符在字符串中的位置
    8. Lpad('salary',10,'*'),保留10位数字，长度不够的用*从左边补齐
    9. Rpad('salary',10,'*'),保留10位数字，长度不够的用*从右边补齐
    10. Trim('H' from 'HelloWorld'),从Helloworld中去掉字符H，注意：位于字符串中间的字符无法去掉，只能去掉头尾位置的字符（包括空格）ps：Trim('H' from ' HelloWorld ')无法去掉H。
    11. Trim(' HelloWorld')
  3. number:
    1. round(34.239, 2) 四舍五入保留两位小数（Round(34.239 ,-1) 相当于精确到十位）
    2. Trunc(34.239, 3) 直接保留两位小数，不进行四舍五入
    3. Mod(13, 4) 取余（模）运算
  4. date,
    1. SELECT last_name, Months_between ( SYSDATE, hire_date) FROM employees WHERE manager_id = 100; 计算从hire_date到当前时间之间的月份差
    2. Add_months(sysdate, 6) 当前月份加上6个月
    3. Next_day(sysdate,'星期五'),计算当前日期的下个周五的日期
    4. Next_day(sysdate, 2) ,明天的日期加上两天
    5. Last_day(sysdate), 当前月份的最后一天
    6. Round(sysdate,'month'),精确到月份（下半月会舍入到下一月）
    7. Round(sysdate,'year'),精确到年
    8. Trunc(sysdate,'month') --2016-07-01 精确到月份，不论上下半月都是到本月
    9. round（sysdate,'year')--2017-01-01 精确到年
    10. To_char()函数，日期到字符串的转换

日期格式化元素：

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;

多表连接
1. 外联接

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 E RIGHT OUTER JOIN DEPARTMENTS D

ON E.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';

事务控制
1. Commit
2. Rollback
3. savepoint
锁
1. 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	表示几天几小时几分几秒的间隔

表的操作
1. 添加字段

Alter table testtab3 add （test_code varchar(20));

修改字段
1. Alter table testtab3 modify (test_code number);
删除字段
1. Alter table testtab3 drop test_code;
删除表
1. Drop table testtab3;
清空表
1. Delete from testtab3 where …;
2. Truncate table testtab3;
3. truncate与delete的区别：1.没有rollback机会，2.HWM标记复位
更改表名
1. Rename oldname to newname;

约束
1. 常用的约束有：
  1. NOT NULL
    
    非空约束
    
    UNIQUE
    
    唯一约束
    
    PRIMARY KEY
    
    主键约束
    
    FOREIGN KEY
    
    外键约束
    
    CHECK
    
    自定义约束
2. 创建约束的方法
  1. 创建表的时候同时创建
  2. 另外单独创建

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;

视图
1. 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;

索引
1. Create index emp_last_name on employees(last_name);
2. 函数索引，如果对某一列使用函数，则这一列的索引是用不到的，除非建立函数索引
同义词
1. CREATE SYNONYM EMPl FOR SCOTT.EMP;

集合操作
1. 并集
  
  Union
  
  并集（保留重复项）
  
  Union all
  
  差集
  
  Minus
  
  交集
  
  intersect
用户权限

CREATE USER hpos IDENTIFIED 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 * from emp@hq.acme.com;

Group by增强
1. 在group by中使用rollup产生常规的分组汇总行，以及分组小计

SELECT department_id

,job_id

,SUM(salary)

FROM employees

WHERE department_id < 60

GROUP BY ROLLUP(department_id, job_id);

在group by中使用cube产生常规的分组汇总，从左到右再从右到左，共汇总两次

SELECT department_id

,job_id

,SUM(salary)

FROM employees

WHERE department_id < 60

GROUP BY CUBE(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_costs AS

(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_NAME ORDER BY E.SALARY DESC) DEPT_SALARY_RANK2,

ROW_NUMBER() OVER(PARTITION BY D.DEPARTMENT_NAME ORDER BY E.SALARY DESC) DEPT_SALARY_RANK3

FROM EMPLOYEES E, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

闪回

SELECT * FROM departments AS OF TIMESTAMP SYSDATE -5/(24*60)

WHERE department_name = 'Shipping';

删除的信息可以通过闪回找到，闪回的时间跟数据库的设置有关

临时表
1. Create global temporary table temp_table_session(head_id number)on commit preserve rows;
2. 全局临时表在我门开发程序中经常使用，在不同的会话之间可以屏蔽数据，可以有触发器，检查约束，索引等。
3. 基于会话
  1. 基于会话的临时表，在会话断开之前，或者通过一个delete或truncate物理的删除行之前，这些行会一直存在于这个临时表中，只有我的会话可以看到这些行，即使我已经提交了，其他会话也无法看到我的行。
4. 基于事务
  1. 基于事务的临时表，我的会话提交时，临时表中的行就不见了，只需把分配给这个表的临时区段交回，这些行就会消失，在临时表的自动清除过程中不存在开销。
物化视图
1. 在一段时间内保存固定的数据看，提供自动刷新和手动刷新的机制

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 --查询重写

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;	海

策略函数
1. 创建策略函数 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';

小飞飞_2012

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL基础

select语句Select * from employees;数值或日期类型的字段可以进行‘加减乘除’运算。null不能进行四则运算连接符‘||’去重复：distinct条件和排序Select * from employees e where e.last_name like '_o%';Select * from t_char w
复制链接

扫一扫