oracle一个创建用户、创建表空间、授权、建表的完整过程和常用命令

oracle一个创建用户、创建表空间、授权、建表的完整过程

--创建表空间create tablespace xinjian_space datafile 'D:\app\as\product\11.2.0\dbhome_1\NETWORK\ADMIN\xinjian_space.dbf' size 300M;--创建用户，并赋予表空间create user comdba identified by root default tablespace xinjian_space;--将connect,resource权限赋予zzg用户grant connect,resource,create table,create view,create session,unlimited tablespace to comdba--将dba权限赋予zzg用户grant dba to SCOTT--查询用户具有的权限select * from session_privs;--根据结果集创建表CREATE TABLE INFOS1 AS SELECT * FROM INFOS;--复制表结构CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2;--建表和约束CREATE TABLE INFOS(STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号STUNAME VARCHAR2(10) NOT NULL, --姓名GENDER VARCHAR2(2) NOT NULL, --性别AGE NUMBER(2) NOT NULL, --年龄SEAT NUMBER(2) NOT NULL, --座号ENROLLDATE DATE, --入学时间STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址CLASSNO VARCHAR2(4) NOT NULL, --班号 班号=学期序号+班级序号CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ,CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女'),CONSTRAINTS UN_STUNAME UNIQUE(STUNAME) );ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ;ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDERCHECK(GENDER = '男' OR GENDER = '女');ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEATCHECK(SEAT >=0 AND SEAT <=50) ;ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGECHECK(AGE >=0 AND AGE<=100) ;ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNOCHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR(CLASSNO >='2001' AND CLASSNO<='2999')) ;ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME) ;CREATE TABLE SCORES(ID NUMBER , --ID ①TERM VARCHAR2(2), --学期 S1或S2STUID VARCHAR2(7) NOT NULL, --学号EXAMNO VARCHAR2(7) NOT NULL, --考号 E+班号+序号WRITTENSCORE NUMBER(4,1) NOT NULL, --笔试成绩LABSCORE NUMBER(4,1) NOT NULL,--机试成绩CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2'));ALTER TABLE SCORESADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2');ALTER TABLE SCORESADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCESINFOS(STUID);--增加一列,添加到表的最后一列alter table luxw8add(age number(3) default 18 check(age<40));alter table luxw8--创建索引create unique index uq_sal on emp(sal);--删除约束drop constraint luxw8_personid_nn ;alter table luxw8drop primary key cascade;----------------------------------------------------------------------------------------------------------插入数据INSERT INTO INFOS VALUES (  's100102', '林冲', '男', 22, 2,TO_DATE('2009-8-9 06:30:10',' YYYY-MM-DD HH24:MI:SS '), '西安', '1001' );INSERT INTO INFOS VALUES ('s100104','阮小二','男',26,3,SYSDATE,default,'1001'); COMMIT;INSERT INTO INFOS2 SELECT * FROM INFOS;COMMIT;--UPDATE 操作 UPDATE INFOS SET CLASSNO='1002',STUADDRESS='山东莱芜'WHERE STUNAME='阮小二';commit;--DELETE 操作DELETE FROM INFOS WHERE STUID='s100103';commit;--DISTINCT 消除重复行SELECT DISTINCT DEPTNO FROM EMP;--BETWEEN…AND…SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000;--LIKE 模糊查询SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'J%S';-------------------------------------------------------------------------------------------------------------内连接select e.last_name,e.dept_id,d.id,d.namefrom s_emp e join s_dept don e.dept_id=d.idwhere e.last_name='Smith';--左链接select c.name,c.sales_rep_id,e.id,e.last_namefrom s_customer c left join s_emp eon c.sales_rep_id=e.id;--右连接select c.name,c.sales_rep_id,e.id,e.last_namefrom s_emp e right join s_customer con c.sales_rep_id=e.id;--全连接select c.name,c.sales_rep_id,e.id,e.last_namefrom s_customer c full join s_emp eon c.sales_rep_id=e.id;--自连接select e.last_name,m.last_namefrom s_emp e,s_emp mwhere e.manager_id=m.id;--打印出和 Smith 在同一部门的员工的姓名 select last_name,dept_id,start_datefrom s_empwhere dept_id=(select dept_idfrom s_empwhere last_name='Smith');--------------------------------------------------------------------------------------字符函数ASCII(x) 返回字符 x 的 ASCII 码。CONCAT(x,y) 连接字符串 x 和 y。INSTR(x, str [,start] [,n)在 x 中查找 str，可以指定从 start 开始，也可以指定从第 n 次开始。LENGTH(x) 返回 x 的长度。LOWER(x) x 转换为小写。UPPER(x) x 转换为大写。LTRIM(x[,trim_str]) 把 x 的左边截去 trim_str 字符串，缺省截去空格。RTRIM(x[,trim_str]) 把 x 的右边截去 trim_str 字符串，缺省截去空格。TRIM([trim_str FROM] x) 把 x 的两边截去 trim_str 字符串，缺省截去空格。REPLACE(x,old,new) 在 x 中查找 old，并替换为 new。SUBSTR(x,start[,length])返回 x 的字串，从 staart 处开始，截取 length 个字符，缺省 length，默认到结尾。-------------------------------------------------------------------------------数字函数ABS(x) x 绝对值 ABS(-3)=3ACOS(x) x 的反余弦 ACOS(1)=0COS(x) 余弦 COS(1)=1.57079633CEIL(x) 大于或等于 x 的最小值 CEIL(5.4)=6FLOOR(x) 小于或等于 x 的最大值 FLOOR(5.8)=5LOG(x,y) x 为底 y 的对数 LOG(2,4)=2MOD(x,y) x 除以 y 的余数 MOD(8,3)=2POWER(x,y) x 的 y 次幂 POWER(2,3)=8ROUND(x[,y]) x 在第 y 位四舍五入 ROUND(3.456,2)=3.46SQRT(x) x 的平方根 SQRT(4)=2TRUNC(x[,y]) x 在第 y 位截断 TRUNC(3.456,2)=3.45------------------------------------------------------------------------------------------日期函数ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。d 表示日期， n 表示要加的月数。LAST_DAY(d)，返回指定日期当月的最后一天。ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天。EXTRACT(fmt FROM d)，提取日期中的特定部分（fmt 为： YEAR、 MONTH、 DAY、 HOUR、 MINUTE、 SECOND）--------------------------------------------------------------------------------------转换函数TO_CHAR(d|n[,fmt])日期和数字转换为制定格式的字符串。 fmt 是格式化字符串TO_DATE(x [,fmt])把一个字符串以 fmt 格式转换为一个日期类型。TO_NUMBER(x[,fmt])把一个字符串以 fmt 格式转换为一个数字NVL(x,value)如果 x 为空，返回 value，否则返回 x。NVL2(x,value1,value2)如果 x 非空，返回 value1，否则返回 value2------------------------------------------------------------------------------------AVG 平均值 AVG(表达式)SUM 求和 SUM(表达式)MIN、 MAX 最小值、最大值 MIN(表达式)、 MAX(表达式)COUNT 数据统计 COUNT(表达式)------------------------------------------------------------------------------------pl/sql--if then elsif end ifDECLAREnewSal emp.sal % TYPE;BEGINSELECT sal INTO newSal FROM empWHERE ename='JAMES';IF newSal>1500 THENUPDATE empSET comm=1000WHERE ename='JAMES';ELSIF newSal>1500 THENUPDATE empSET comm=800WHERE ename='JAMES';ELSEUPDATE empSET comm=400WHERE ename='JAMES';END IF;END;--when thenDECLAREv_grade CHAR(1):=UPPER('&p_grade'); ①BEGINCASE v_grade ②WHEN 'A' THENdbms_output.put_line('Excellent');WHEN 'B' THENdbms_output.put_line('Very Good');WHEN 'C' THENdbms_output.put_line('Good');ELSEdbms_output.put_line('No such grade');END CASE;END;--loop 遍历游标declarev_student student%rowtype;cursor c_student(v_id binary_integer) isselect * from student where id>v_id;beginopen c_student(10);loopfetch c_student into v_student;exit when c_student%notfound;dbms_output.put_line('name: ' || v_student.name);end loop;close c_student;end;--for 循环遍历,最简单,用的最多,不需要声明 v_student、 打开关闭游标、 fetch。declarecursor c_student(v_id binary_integer) isselect * from student where id>v_id;beginfor v_student in c_student(10) loopdbms_output.put_line('name: '||v_student.name);end loop;end;