sql

创建用户

CREATE USER MARTIN

IDENTIFIED BY martinpwd

DEFAULT TABLESPACE USERS

TEMPORARYTABLESPACE TEMP

GRANT CONNECT TO MARTIN; 

GRANT RESOURCE TO MARTIN; 

GRANT CREATE SEQUENCE TO MARTIN; 

SQL 操作符

一算术操作符
二比较操作符 
比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等


三
四


SELECT losal FROM "SCOTT"."SALGRADE";--取出所有值
SELECT losal+500 losal500 FROM "SCOTT"."SALGRADE";--+500取出


SELECT * FROM emp WHERE ENAME='';


SELECT *FROM emp WHERE HIREDATE <TO_DATE('1987-01-01', 'yyyy-mm-dd')--87年一月一号以前的
--in 查询多个值组成的集合
--LIKE 用于模糊查询
--集合操作
--INTERSECT并 A+B
--交集UNION AB中共同的部分
--差集MINUS 存在前面 不存在后面 从A里把B去掉
-- (...||...) as 连接 将多个列合并成一个列
select ('编号：'||e.EMPNO,'姓名：'||e.ENAME,'岗位：'||e.JOB) as 员工信息 from EMP e;


SQL 函数
一单行函数
日期函数
数字函数
字符函数
转换函数
其他函数
1.日期函数
日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果
日期函数包括：
ADD_MONTHS
MONTHS_BETWEEN
LAST_DAY
ROUND
NEXT_DAY
TRUNC
EXTRACT


2.字符函数
--LTRIM去掉左边字符串
select LTRIM('123hddh123','123')from dual;
--RTRIM去掉右边字符串
select RTRIM('123hddh123','123')from dual;
--TRIM去掉最左最右空格
SELECT TRIM('    fefafaf   dsfdfef   ')from dual;
--TRANSLATE替换字符串中的单个字符
SELECT TRANSLATE('jack','j','b')from dual;
--INSTR获得某个字符所在的位置 Substr (char, m, n) 获得之后把它抠出来
SELECT INSTR ('213e2shshshhj32', 'e2')from dual;
--LPAD 往左填充  RPAD(expr1, n, expr2)往右填充 填完7位
SELECT LPAD('QWE', 7, '1')FROM dual;
--求字符串长度
SELECT LENGTH('dfsdgvdg1345')FROM dual;
--转换 先判断取值 再替换对应后面的值 比较快捷
SELECT DECODE(deptno,10,'统计',20,'研究部',30,'实验室',40,'财务部')from DEPT;


3.转换函数
--TO_CHAR 将数据类型转换成字符串
SELECT TO_CHAR(HIREDATE) from EMP;
--TO_DATE 转换为日期 可以按照指定格式
SELECT TO_DATE(‘2005-12-06’ , ‘yyyy-mm-dd’)FROM dual;
--TO_NUMBER 转换为数字
SELECT TO_NUMBER('21435')FROM dual;


4.转换空值的函数
--nvl 若是空值 则改为0 不是则不改
SELECT NVL(MGR, 0)from EMP;
--nul2 非空改1 空改0
SELECT NVL2(MGR,1,0)from EMP;




二 分组函数
GROUP BY
    合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
    GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
    如果直接使用该语句，返回的结果只有每个分组的第一个数据。
-- ORDER BY 语句用于根据指定的列对结果集进行排序。该语句默认按照升序对记录进行排序。 使用 DESC 关键字可以反序排列记录。
-- 如果要排序的是多个字段，结果的顺序由靠前的字段优先决定。
    语法：SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
    不适用合计函数直接使用：
select * from students group by class;


having 分组之后再进行筛选
   对表中的数据分组后，会得到一个分组后的结果集，对该结果集进行删选用having
        SELECT 班级ID=tsclassid,总人数=COUNT(*)
FROM tblstudent
       GROUP BY tsclassid
       HAVING COUNT(*)>3—having后需写聚合表达式


where和having的区别
 注意：Having中不能使用未参与分组的列，Having不能替代where。作用不一样，Having是对组进行过滤。
Having 是Group By的条件对分组后的数据进行筛选（与Where类似，都是筛选，只不过having是用来筛选分组后的组的。）
在Where中不能使用聚合函数，必须使用Having，Having要位于GroupBy之后。
Having的使用几乎是与where一样的，也可以用in。
Having count(*) in (5,8,10)


三 分析函数
--在前面加列编号
select ROWNUM,ename FROM emp;
--RANK 具有相等值的行排位相同，序数随后跳跃
--DENSE_RANK 具有相等值的行排位相同，序号是连续的

SELECT rank(mgr) over (ORDER BY MGR) as rnk from emp ;

事物和数据库对象

CREATE table t_quser2
(qid NUMBER nut null primary key
qnaem VARCHAR2(20)
);
--创建序列
create sequence seq_tquser
start with 1
maxvalue 99999
nocycle
nocache;
--序列的使用
select seq_quser.currvalue from dual;--查看当前值
--序列创建好之后先要next一下才能使用
INSERT into t_quser (qid,qname) VALUES (1,'q1');
INSERT into t_quser (qid,qname) VALUES (2,'q1');
INSERT into t_quser2 (qid,qname) VALUES (seq_tquser.nextvalue,'q1');
--创建视图
CREATE view v_emp --视图的名字一般按照t_ 的格式取
as select e.empno,e.ename,e.job
from emp e;
--创建之后就可以对视图进行查询
SELECT * from v_emp;


CREATE view v_emp_dept 
as SELECT e.empno,e.ename,d.dname
from emp e,DEPT d
WHERE e.DEPTNO=d.DEPTNO;


SELECT * from v_emp_dept;
--创建索引  只管创建 不会显示 执行语句时 会自动按照索引查询数据
create index idx_emp_empno on emp(empno);
--组合索引
SELECT INDEX idx_emp_hiredate_sal on emp (hiredate,sal)
--重复值比较多时
SELECT bitmap INDEX idx_emp_job on EMP(job);
--将字段通过某种函数映射 建立索引
create INDEX idx_emp_ename on emp (lower(ename));