oracle sql语句2

-- 创建表空间

CREATE TABLESPACE tengyong01 DATAFILE 'E:/oracle/data1/tengyong01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M;

-- 删除表空间

DROP TABLESPACE tengyong01 INCLUDING CONTENTS;

-- 创建用户

CREATE USER tengyong IDENTIFIED BY tengyong DEFAULT TABLESPACE tengyong01 TEMPORARY TABLESPACE temp;

-- 表结构

DESC tablename;

-- 删除用户: 当前正在连接的用户无法删除, 必须先终止会话

SELECT * FROM V$SESSION WHERE username = 'TENGYONG';

ALTER SYSTEM KILL SESSION '148,375'; -- 参数是分别是: sid, serial#

DROP USER tengyong CASCADE;

-- 给用户赋权限: 权限角色: connect, recource(创建过程, 触发器等), dba, execute_catalog_role, select_catalog_role, delete_catalog_role, exp_full_database, imp_full_database, recovery_catalog_owner

GRANT CONNECT,DBA TO tengyong;

-- 赋于用户修改mytable表结构的权限

GRANT ALTER ON mytable TO tengyong;

-- 所有用户均可执行包, 过程, 函数,  public参数

GRANT EXECUTE ON [名称] TO PUBLIC;

-- 建索引权限: index, references, select, update, delete

GRANT INDEX ON [表名] TO tengyong;

-- 修改角色: not identified: 非验证, identified by private: 数据库验证

ALTER ROLE [角色名] NOT IDENTIFIED;

ALTER ROLE [角色名] IDENTIFIED BY PRIVATE;

-- 删除角色

DROP ROLE [角色名];

-- 显示角色信息

SELECT * FROM DBA_ROLES;

-- 显示用户具有的角色

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='TENGYONG';

-- 当前会话激活的角色

SELECT * FROM SESSION_ROLES;

-- 显示对象权限

SELECT * FROM DBA_TAB_PRIVS WHERE grantee='TENGYONG';

-- 收回用户权限

REVOKE DBA FROM tengyong;

-- 修改用户密码

ALTER USER tengyong IDENTIFIED BY tengyong;

-- 锁定用户: 该用户不能访问数据库

ALTER USER tengyong ACCOUNT LOCK;

-- 解锁用户: 该用户可以访问数据

ALTER USER tengyong ACCOUNT UNLOCK;

-- 显示当前用户: 在sqlplus下, 即命令行下

SHOW USER;

-- 显示所有的用户信息

SELECT * FROM dba_users;

-- 显示用户表空间配额

SELECT * FROM DBA_TS_QUOTAS WHERE username='TENGYONG';

-- 系统权限列表

SELECT * FROM SYSTEM_PRIVILEGE_MAP;

-- 查询用户所有具有的权限

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'TENGYONG';

-- 显示当前会话所具有的权限

SELECT * FROM SESSION_PRIVS;

-- 执行存储过程

EXECUTE [存储过程名]

-- 创建视图

CREATE VIEW [视图名] AS [sql语句] WITH CHECK OPTION [CONSTRAINT [check约束名];

CREATE VIEW dept_emp_view AS SELECT a.*, b.emp_name,b.emp_sex,b.emp_phone FROM dept a, emp b WHERE a.dept_id=b.dept_id WITH CHECK OPTION CONSTRAINT sex_check;

SELECT * FROM dept_emp_view;

DROP VIEW dept_emp_view;

-- 分页查询

SELECT * FROM (SELECT ROWNUM AS rowsnum,t.* FROM test t) WHERE rowsnum BETWEEN 2 AND 2;

 

SET AUTONOMOUS_TRANSACTION ON EXPLAIN;

-- 连接查询

SELECT a.*,b.* FROM dept a, emp b WHERE a.DEPT_ID=b.DEPT_ID AND a.DEPT_ID=1;

-- 建立簇: 建立索引簇, 建立簇表, 建立簇索引

CREATE CLUSTER dept_emp_clu (dept_id NUMBER(4)) PCTFREE 20 PCTUSED 60 SIZE 500 TABLESPACE tengyong01;

-- 修改簇

ALTER CLUSTER dept_emp_clu PCTFREE 30 PCTUSED 40;

-- 删除簇

DROP CLUSTER dept_emp_clu;

 

 

-- oracle数据类型

CHAR(n)或CHAR(n BYTE) --最长2000长度: n个字节的固定长度

CHAR(n CHAR) --中文2n个字节, 英文n个字节

VARCHAR2(n) --变长字符串, 最长4000个字节

NUMBER(n, m) --数字类型: n数字的总位数, m小数点后位数

DATE --日期类型: 默认格式(DD-MON-YY), 例: '29-4 月-03'

TIMESTAMP --date类型的扩展, 操作和date完全相同, 格式(DD-MON-YY HH.MI.SS AM), 例: '29-04 月-03 04.02.03.000000 下午'

RAW(n) --定义二进制数据, n最大为2000

CLOB --大批量字符, 最大4G

BLOB --大批量二进制数据, 最大4G

-- 聚合函数

MAX

MIN

AVG

SUM

COUNT

VARIANCE

STDDEV

-- 数字函数

ABS(n) -- 绝对值

CEIL(n) --大于或等于n的最小整数

FLOOR(n) -- 小于或等于n的最大整数

MOD(m, n) -- m除以n的余数

POWER(m, n) -- m的n次冥

SQRT(n) -- n的平方根

ROUND(m, [n]) -- 四舍五入, n可选: 精确小数点后n位

SIGN(n) -- 判断n的正负: 正数返回1, 负数返回-1, 0返回0

TRUNC(m, [n]) -- 截取数字, n为正,截取小数点后n位; n为负, 截取小数点前n位; 省略截取整数部分

-- 字符函数

ASCII() -- 字符串首字符的ascii值

CHR(n) -- ascii值转变为字符

CONCAT() -- 连接字符

INITCAP() -- 首字母大写

NLS_INITCAP(char1, [规则]) -- 首字母大写, 其他小写

NLS_UPPER(char1, [规则]) -- 转为大写

NLS_LOWER(char1, [规则]) -- 转为小写

NLS_SORT(char1, [规则]) -- 排序

REPLACE -- 替换

INSTR(char1, char2, [n,[m]]) -- 搜索char2在char1中的位置, n: 开始查过的位置, 为负时从尾部开始, m: 出现次数; n,m默认值均为1

LENGTH() -- 字符串的长度

LOWER -- 转为小写

UPPER -- 转为大写

LPAD(char1, n, char2) -- char1左侧插入n长度的字串, char2的前n长度的字串, 不足时循环

RPAD -- 右侧填充

LTRIM(char1,char2) -- 去掉char1中左侧char2字串

RTRIM -- 去掉右侧字串

SUBSTR(char1, m , [n]) -- 字串截取

TRIM -- 头部尾部特定字串

 

 

SELECT LTRIM('tengyong' , 'teng') FROM dual;

SELECT chr(97) FROM DUAL;

-- 聚合函数统计: rollup: 最后一行, cube: 第一行

SELECT COUNT(b.dept_id),a.dept_name, GROUPING(a.dept_name) FROM DEPT a LEFT JOIN EMP b ON a.dept_id=b.dept_id GROUP BY ROLLUP(a.dept_name);

NVL -- NULL值, 例: NVL(column1, column2)

-- 序列: CURRVAL: 当前序列, NEXTVAL: 下一个序列

CREATE SEQUENCE test_id_sequence

INCREMENT BY 1

START WITH 1

NOMAXVALUE NOCYCLE CACHE 10;

-- 事物

BEGIN

INSERT INTO DEPT VALUES(9, '部门七', 'tengyong');

INSERT INTO emp VALUES(16,10,'呵呵','女','31355661');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

 

 

SELECT VARIANCE(25) AS aaa FROM DEPT;

 

SELECT t. *,t.ROWID,dbms_rowid.rowid_row_number(t.ROWID) FROM test t;

DELETE FROM test WHERE test_id = 4;

 

CREATE TABLE test(

test_id NUMBER(4) PRIMARY KEY,

test_name VARCHAR2(20) NOT NULL,

test_password VARCHAR2(20) NOT NULL

);

 

ALTER TABLE test MODIFY (test_name VARCHAR2(20) CONSTRAINT test_name_unique UNIQUE);

INSERT INTO test VALUES(test_id_sequence.NEXTVAL, 'tengyong', '123456');

INSERT INTO test VALUES(test_id_sequence.NEXTVAL, 'tengyong1', '123456');

INSERT INTO test VALUES(test_id_sequence.NEXTVAL, 'tengyong2', '123456');

INSERT INTO test VALUES(test_id_sequence.NEXTVAL, 'tengyong3', '123456');

 

SELECT * FROM dept;

 

SELECT (DISTINCT dept_name),a.* FROM dept;

SELECT DISTINCT a.*,dept_name || nvl(dept_admin,'') AS temp FROM dept a WHERE ROWNUM <7 ORDER BY dept_id;

 

SELECT SUM(emp_money) FROM emp;

 

SELECT * FROM EMP ORDER BY emp_id;

SELECT * FROM DEPT ORDER BY dept_id;

SELECT COUNT(b.dept_id),a.dept_name FROM DEPT a LEFT JOIN EMP b ON a.dept_id=b.dept_id GROUP BY ROLLUP(a.dept_name);

EXEC dept_tra;

 

SELECT a.*,b.emp_name,CASE WHEN b.emp_sex='男' THEN 'man' ELSE 'woman' END,b.emp_phone FROM dept a LEFT JOIN emp b ON a.DEPT_ID = b.DEPT_ID WHERE b.EMP_NAME IS NULL;

SELECT COUNT(a.dept_name),a.dept_name FROM dept a LEFT JOIN emp b ON a.DEPT_ID = b.DEPT_ID GROUP BY a.dept_name HAVING COUNT(a.dept_name)>3 ORDER BY a.dept_name;

 

DECLARE 

v_convert VARCHAR2(20);

BEGIN

v_convert := CONVERT('中国', 'US7ASCII', 'kfaaj');

DBMS_OUTPUT.put_line('转换结果' || v_convert);

END;

/

 

SELECT to_char(SYSDATE, 'YYYY-MM-DD') FROM dual;

SELECT * FROM dept;

 

DECLARE

v_dept_name dept.DEPT_NAME%TYPE;

v_dept_admin dept.DEPT_ADMIN%TYPE;

BEGIN

SELECT dept_name,dept_admin INTO v_dept_name,v_dept_admin FROM dept WHERE dept_id=1;

DBMS_OUTPUT.put_line('部门：' || v_dept_name);

DBMS_OUTPUT.put_line('负责人：' || v_dept_admin);

END;

/

SET serveroutput ON -- 须在命令行执行

 

DECLARE 

v_emp_id emp.EMP_ID%TYPE:=&empid;

BEGIN

DELETE FROM emp WHERE emp_id = v_emp_id;

END;

/

 

DECLARE

v_dept_id dept.DEPT_ID%TYPE:=&dept_id;

v_dept_name dept.DEPT_NAME%TYPE:='&dept_name';

BEGIN

UPDATE DEPT SET dept_name=v_dept_name WHERE dept_id=v_dept_id;

DBMS_OUTPUT.put_line(SQL%ROWCOUNT);

IF SQL%FOUND THEN

DBMS_OUTPUT.put_line('修改成功！');

ELSE

DBMS_OUTPUT.put_line('失败！');

END IF;

END;

/

DECLARE

v_temp NUMBER(4):=&temp;

BEGIN

IF v_temp=1 THEN

DBMS_OUTPUT.put_line('输入的数字是：' || v_temp);

ELSIF v_temp=2 THEN

DBMS_OUTPUT.put_line('输入的数字是：' || v_temp);

ELSE

DBMS_OUTPUT.put_line('输入的不是1');

END IF;

END;

/

 

DECLARE 

v_num INT :=0;

v_temp INT :=0;

BEGIN

LOOP

SELECT MOD(v_num, 2) INTO v_temp FROM dual;

IF v_temp = 0 THEN

INSERT INTO emp(emp_id,dept_id,emp_name,emp_sex,emp_phone,emp_money) VALUES(emp_id_sequence.NEXTVAL,1,'名','男','12345678901',1321);

ELSE

INSERT INTO emp(emp_id,dept_id,emp_name,emp_sex,emp_phone,emp_money) VALUES(emp_id_sequence.NEXTVAL,2,'名','男','12345678901',1321);

END IF;

EXIT WHEN v_num=20;

v_num:=v_num+1;

END LOOP;

END;

/

 

 

 

INSERT INTO emp(emp_id,dept_id,emp_name,emp_sex,emp_phone,emp_money) VALUES(emp_id_sequence.NEXTVAL,1,'名','1','123',1);

SELECT emp_id_sequence.CURRVAL FROM dual;

CREATE SEQUENCE emp_id_sequence

INCREMENT BY 1

START WITH 16

NOMAXVALUE NOCYCLE CACHE 10;

 

DROP SEQUENCE emp_id_sequence;