ORACLE函数使用及查询

我整理了一下，但是实在没时间去一个一个注释，大家看了我的另一份文档，ORACLE函数大全，
或者入门的应该很容易看懂。^_^~


-- 字符函数
SELECT initcap('hello') AS VALUE
FROM dual;

SELECT lower('FUN') AS VALUE
FROM dual;

SELECT upper('sun') AS VALUE
FROM dual;

SELECT ltrim('xyzadams', 'xyz') AS VALUE
FROM dual;

SELECT rtrim('xyzadams', 'ams') AS VALUE
FROM dual;

SELECT translate('jack', 'j', 'b') AS VALUE
FROM dual;

SELECT REPLACE('jack and jue', 'j', 'bl') AS VALUE
FROM dual;

SELECT instr('worldwide', 'd') AS VALUE
FROM dual;

SELECT substr('abcdefg', 3, 2) AS VALUE
FROM dual;

SELECT concat('Hello', ' world') AS VALUE
FROM dual;

SELECT NAME, decode(sex, 1, 'GG', 0, 'MM'), Age, ClassID
FROM student;

SELECT LPAD('function', 15, '=')
FROM dual;

SELECT TRIM(9 FROM 9999876789999)
FROM dual;

SELECT TRIM('9' FROM '9999876789999')
FROM dual;

-- 日期函数
SELECT SYSDATE AS VALUE
FROM dual;

SELECT ADD_MONTHS(SYSDATE, 12) AS VALUE
FROM dual;

SELECT MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(SYSDATE, 12)) AS VALUE
FROM dual;

SELECT LAST_DAY(SYSDATE) AS VALUE
FROM dual;

SELECT ROUND(SYSDATE) AS VALUE
FROM dual;

SELECT NEXT_DAY(SYSDATE, 1) AS VALUE
FROM dual;

SELECT TRUNC(SYSDATE, 'YEAR') AS VALUE
FROM DUAL;

SELECT EXTRACT(YEAR FROM SYSDATE) AS VALUE
FROM DUAL;

————————————————————————————————————————
--分组查询

SELECT CLASS.ID AS ClassID,
SUM(CASE Sex
WHEN 1 THEN
1
ELSE
0
END) AS 男生人数,
SUM(CASE Sex
WHEN 0 THEN
1
ELSE
0
END) AS 女生人数,
COUNT(Student.ID) AS 总人数
FROM Student
RIGHT JOIN CLASS ON CLASS.Id = Student.Classid
GROUP BY CLASS.id
HAVING COUNT(Student.ID) <= 10;

——————————————————————————————————————————
--操作符

SELECT ID AS 标识,
NAME AS 姓名,
sex AS 性别,
age AS 年龄,
classid AS 班级标识
FROM Student
WHERE NAME LIKE '周%' AND
sex = 1 AND
age > 20;

-- 集合操作符
SELECT ID
FROM Student
UNION -- 返回消除重复后的行
SELECT ID
FROM CLASS;

SELECT ID
FROM Student
UNION ALL -- 返回所有行
SELECT ID
FROM CLASS;

SELECT ID
FROM Student
INTERSECT --返回重复行
SELECT ID
FROM CLASS;

SELECT ID
FROM Student
MINUS -- 返回排除行
SELECT ID
FROM CLASS;

-- || 运算符
SELECT 'Hello' || ' ' || 'World!' AS VALUE
FROM dual;
SELECT 'Today"s date is ' || AS VALUE
FROM dual;

——————————————————————————————————————————
--DML

SELECT ID AS 标识,
NAME AS

姓名,
sex AS 性别,
age AS 年龄,
classid AS 班级标识
FROM Student;

CREATE SEQUENCE SE_Student_ID INCREMENT BY 1 START WITH 1 MAXVALUE 9999 CYCLE NOCACHE;

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '周润发', 1, 23, 1);

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '周星驰', 1, 21, 1);

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '周慧敏', 0, 22, 1);

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '周杰伦', 1, 20, 1);

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '刘翔', 1, 20, 2);

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '刘易斯', 1, 20, 2);

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '张柏芝', 0, 18, 3);
COMMIT;

——————————————————————————————————————————
--DDL

CREATE TABLE Student (
ID INTEGER NOT NULL,
NAME VARCHAR2(8) NOT NULL,
SEX NUMBER(1) NOT NULL,
Age NUMBER(2) NOT NULL,
ClassID INTEGER NOT NULL,
CONSTRAINT Student_PK PRIMARY KEY(ID)
);

-- 创建外键约束
ALTER TABLE student
ADD CONSTRAINT FK_Student_ClassID_Class_ID FOREIGN KEY(ClassID)
REFERENCES CLASS(Id);


———————————————————————————————————————————
--DCL

GRANT ALL ON TT.CLASS TO TT;
GRANT ALL ON TT.Student TO TT;

———————————————————————————————————————————
--TCL

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '汤姆', 1, 18, 1);

SAVEPOINT mark_1;

INSERT INTO Student
(id, NAME, sex, age, classid)
VALUES
(SE_Student_ID.NEXTVAL, '杰瑞', 1, 18, 1);

SAVEPOINT mark_2;

ROLLBACK TO SAVEPOINT mark_1;
COMMIT;