首页 > 代码库 > 20170621_oracle练习

20170621_oracle练习

启动Oracle:
启动OracleOraDb11g_home1TNSListener
lsnrctl start
启动OracleServiceORCL
net start OracleServiceORCL
关闭
lsnrctl stop和net stop OracleServiceORCL

 

技术分享

 

 

技术分享

 

技术分享

 

 

技术分享
Spool 目录  (把sql语句都记录在txt文件中)spool  e:\xxx.txtSpool off 结束SQL> --清除屏幕信息SQL> clear screenSQL> --查看表空间SQL> select * from v$tablespace; SQL> --设置sql语句显示的长度SQL> set linesize 500;SQL> select * from v$tablespace;SQL> --创建表空间SQL> create tablespace   t11  2  datafile E:\U2\t11.dbf  3  size 200m  4  autoextend on next 20  maxsize unlimited;表空间已创建。SQL> select * from v$tablespace;SQL> --查看表空间 是否是 自动扩容SQL> select tablespace_name,autoextensible from dba_data_files;SQL> --取消自动扩容SQL> alter  database datafile E:\U2\t11.dbf  autoextend off;数据库已更改。SQL> --开启SQL> alter  database datafile E:\U2\t11.dbf  autoextend on;数据库已更改。SQL> --删除表空间SQL> drop  tablespace t11;表空间已删除。SQL> select * from v$tablespace;SQL> --只是删除了表空间  但是数据文件还在SQL> --删除表空间的同时,删除数据文件 SQL> drop  tablespace t11 including contents and datafiles;
01.表空间的创建与删除

 

删除表空间,文件还在!
删除文件,用户还在!
都删除必须使用下面的!

drop tablespace 空间名
    including contents and datafiles
      cascade constraint
 drop user 用户名 cascade

技术分享
SQL> --创建用户 并且分配默认的表空间  如果没有指定 则在users表空间中SQL> create  user wym identified by wym default tablespace t10 ;用户已创建。SQL> --授权SQL> grant connect,resource to t11;授权成功。SQL> --切换用户SQL> conn  wym/wym;SQL> conn / as sysdba;已连接。SQL> --使用dba  查询所有的用户名以及默认的表空间   SQL> select username,default_tablespace from dba_users;--更换用户的默认表空间alter user 用户名  default tablespace 表空间的名称!--修改用户名密码SQL> alter user 用户名 identified by 新密码SQL> --删除用户  以及相关的所有信息SQL> drop  user t11 cascade;
02.创建用户

技术分享

 

技术分享
create table DEPT(  deptno NUMBER(2) not null,  dname  VARCHAR2(20),  loc    VARCHAR2(13));  alter table DEPT  add constraint PK_DEPT primary key (DEPTNO);create table TEACHER(  tno       NUMBER(4) not null,  tname     VARCHAR2(30) not null,  tid       CHAR(18),  gendar    CHAR(3),  birthdate DATE,  job       VARCHAR2(20),  hiredate  DATE,  sal       NUMBER(7,2),  deptno    NUMBER(2),  mgrno     NUMBER(4),  comm      NUMBER(7,2)); insert into DEPT (deptno, dname, loc)values (40, 人力部, 北京海淀);insert into DEPT (deptno, dname, loc)values (30, 产品部, 北京海淀);insert into DEPT (deptno, dname, loc)values (20, 招生部, 上海虹桥);insert into DEPT (deptno, dname, loc)values (10, 研究院, 北京海淀);insert into DEPT (deptno, dname, loc)values (50, 系统部, 北京昌平);commit;insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1002, 赵辉, 110101760609001   , , to_date(09-06-1976, dd-mm-yyyy), 考试专员, to_date(23-05-2006 02:40:40, dd-mm-yyyy hh24:mi:ss), 29370.95, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1003, 杨利乐, 110101198705256045, , to_date(25-05-1987, dd-mm-yyyy), 考试专员, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 7134.32, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1004, 王益辉, 110101199108154770, , to_date(15-08-1991, dd-mm-yyyy), 营销专员, to_date(16-01-2012 03:12:43, dd-mm-yyyy hh24:mi:ss), 30737.36, 20, null, 2300.00);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1006, 秦璐璐, 110101199004153068, , to_date(14-04-1990, dd-mm-yyyy), 运维工程师, to_date(20-06-2012 00:47:09, dd-mm-yyyy hh24:mi:ss), 9013.25, 10, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1007, 梁改焕, 110101196911206548, , to_date(20-11-1969, dd-mm-yyyy), 讲师, to_date(13-06-2006 23:07:02, dd-mm-yyyy hh24:mi:ss), 8664.16, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1008, 冯爱仙, 110101196804021521, , to_date(02-04-1968, dd-mm-yyyy), 讲师, to_date(06-06-2006 13:41:50, dd-mm-yyyy hh24:mi:ss), 29936.29, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1009, 秦亚杰, 110101196312163531, , to_date(16-12-1963, dd-mm-yyyy), 岗位分析师, to_date(13-07-2005 06:32:30, dd-mm-yyyy hh24:mi:ss), 24991.44, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1010, 刘浩星, 110101198806283516, , to_date(28-06-1988, dd-mm-yyyy), 考试专员, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 22645.92, null, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1011, 秦春叶, 110101197103033540, , to_date(03-03-1971, dd-mm-yyyy), 网络营销师, to_date(07-07-2007 16:02:04, dd-mm-yyyy hh24:mi:ss), 31653.23, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1012, 陈水花, 110101192811043026, , to_date(04-11-1928, dd-mm-yyyy), 考试专员, to_date(29-01-2005 23:21:59, dd-mm-yyyy hh24:mi:ss), 29773.37, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1013, 马付妮, 110101195806133522, , to_date(13-06-1958, dd-mm-yyyy), 研发, to_date(17-11-2004 08:54:04, dd-mm-yyyy hh24:mi:ss), 17041.60, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1015, 刘得安, 110101195210083518, , to_date(08-10-1952, dd-mm-yyyy), 考试专员, to_date(13-03-2005 23:25:37, dd-mm-yyyy hh24:mi:ss), 30356.99, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1016, 高利芹, 110101198307276020, , to_date(27-07-1983, dd-mm-yyyy), 岗位分析师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 25751.36, null, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1017, 刘志刚, 110101197806164493, , to_date(16-06-1978, dd-mm-yyyy), 运维工程师, to_date(10-05-2008 13:46:33, dd-mm-yyyy hh24:mi:ss), 22344.73, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1018, 高心立, 110101194102076011, , to_date(07-02-1941, dd-mm-yyyy), 网络营销师, to_date(03-03-2005 15:17:07, dd-mm-yyyy hh24:mi:ss), 19981.02, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1019, 徐丽, 11010119790809354X, , to_date(09-08-1979, dd-mm-yyyy), 研发, to_date(09-12-2009 18:37:22, dd-mm-yyyy hh24:mi:ss), 10950.33, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1021, 刘志红, 110101196504284452, , to_date(28-04-1965, dd-mm-yyyy), 岗位分析师, to_date(20-09-2011 10:51:33, dd-mm-yyyy hh24:mi:ss), 7122.60, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1022, 孔鹏飞, 110101198603304014, , to_date(30-03-1986, dd-mm-yyyy), 人力资源师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 7045.28, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1023, 李建坡, 110101731107301   , , to_date(07-11-1973, dd-mm-yyyy), 网络营销师, to_date(04-07-2009 06:28:20, dd-mm-yyyy hh24:mi:ss), 21133.91, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1024, 冯爱敏, 110101196907283840, , to_date(18-07-1969, dd-mm-yyyy), 营销专员, to_date(12-09-2005 22:41:06, dd-mm-yyyy hh24:mi:ss), 3360.91, 20, null, 50000.00);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1025, 薛栓成, 110101400710051   , , to_date(10-07-1940, dd-mm-yyyy), 网络营销师, to_date(03-09-2007 14:55:06, dd-mm-yyyy hh24:mi:ss), 12136.65, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1026, 牛伟伟, 11010119851120602X, , to_date(20-11-1985, dd-mm-yyyy), 岗位分析师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 6622.66, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1027, 郭香云, 110101196010154525, , to_date(15-10-1960, dd-mm-yyyy), 网络营销师, to_date(03-05-2005 13:01:22, dd-mm-yyyy hh24:mi:ss), 26436.18, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1028, 朱花枝, 110101195605154028, , to_date(15-05-1956, dd-mm-yyyy), 研发, to_date(11-09-2007 07:41:01, dd-mm-yyyy hh24:mi:ss), 10095.22, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1029, 李岗, 110101196505163095, , to_date(16-05-1965, dd-mm-yyyy), 岗位分析师, to_date(09-11-2007 17:26:38, dd-mm-yyyy hh24:mi:ss), 23928.84, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1030, 李军会, 110101197706152524, , to_date(15-06-1977, dd-mm-yyyy), 营销专员, to_date(12-05-2007 19:48:21, dd-mm-yyyy hh24:mi:ss), 3492.19, 20, null, 25000.00);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1031, 孙龙举, 110101198908306037, , to_date(30-08-1989, dd-mm-yyyy), 运维工程师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 4323.84, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1032, 韩朝旭, 110101199003270513, , to_date(27-03-1900, dd-mm-yyyy), 人力资源师, to_date(11-09-2010 17:54:30, dd-mm-yyyy hh24:mi:ss), 13740.92, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1033, 孙少光, 110101198809274535, , to_date(27-09-1988, dd-mm-yyyy), 网络营销师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 19778.62, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1035, 邓香敏, 110101196607163109, , to_date(16-07-1966, dd-mm-yyyy), 人力资源师, to_date(12-04-2007 17:47:14, dd-mm-yyyy hh24:mi:ss), 29463.38, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1037, 霍振方, 110101198411070014, , to_date(07-11-1984, dd-mm-yyyy), 讲师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 23731.31, 40, 1087, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1038, 许双鸽, 110101781217004   , , to_date(17-12-1978, dd-mm-yyyy), 网络营销师, to_date(05-09-2007 18:45:00, dd-mm-yyyy hh24:mi:ss), 16512.52, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1040, 张春红, 110101197806176089, , to_date(17-06-1978, dd-mm-yyyy), 运维工程师, to_date(21-11-2004 06:52:30, dd-mm-yyyy hh24:mi:ss), 31728.83, 10, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1041, 卢玉莲, 110101510606004   , , to_date(06-06-1951, dd-mm-yyyy), 人力资源师, to_date(30-03-2009 17:05:45, dd-mm-yyyy hh24:mi:ss), 17596.43, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1042, 李军会, 110101197706152524, , to_date(15-06-1977, dd-mm-yyyy), 讲师, to_date(13-06-2008 03:24:47, dd-mm-yyyy hh24:mi:ss), 8443.59, 50, 1008, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1043, 朱水娃, 110101195210033512, , to_date(03-10-1952, dd-mm-yyyy), 讲师, to_date(11-10-2009 03:40:53, dd-mm-yyyy hh24:mi:ss), 9123.09, 30, 1008, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1045, 高大荣, 110101195504073528, , to_date(07-04-1955, dd-mm-yyyy), 考试专员, to_date(07-12-2005 14:56:05, dd-mm-yyyy hh24:mi:ss), 4000.00, 10, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1046, 秦付根, 110101195301034035, , to_date(03-01-1953, dd-mm-yyyy), 讲师, to_date(10-12-2004 22:51:51, dd-mm-yyyy hh24:mi:ss), 16420.33, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1047, 樊增妮, 110101410715352   , , to_date(15-07-1941, dd-mm-yyyy), 网络营销师, to_date(02-12-2005 14:41:54, dd-mm-yyyy hh24:mi:ss), 13316.01, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1048, 宋素敏, 11010119720111458X, , to_date(11-02-1972, dd-mm-yyyy), 岗位分析师, to_date(26-09-2004 12:15:57, dd-mm-yyyy hh24:mi:ss), 2142.92, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1049, 李记, 110101194803256012, , to_date(25-03-1948, dd-mm-yyyy), 网络营销师, to_date(23-03-2006 19:12:45, dd-mm-yyyy hh24:mi:ss), 13866.09, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1050, 秦单风, 110101198911153625, , to_date(15-11-1989, dd-mm-yyyy), 考试专员, to_date(21-10-2012 05:31:34, dd-mm-yyyy hh24:mi:ss), 21419.38, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1051, 秦占豪, 110101198509034011, , to_date(03-09-1985, dd-mm-yyyy), 讲师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 31564.79, 40, 1087, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1054, 张利娟, 110101197901173062, , to_date(17-01-1979, dd-mm-yyyy), 网络营销师, to_date(08-05-2011 22:37:13, dd-mm-yyyy hh24:mi:ss), 7419.99, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1055, 陈向琼, 11010119871010354X, , to_date(10-10-1987, dd-mm-yyyy), 讲师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 4000.00, 10, 1087, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1056, 杨晓磊, 110101199105106079, , to_date(10-05-1991, dd-mm-yyyy), 研发, to_date(05-04-2012 22:16:05, dd-mm-yyyy hh24:mi:ss), 20812.41, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1057, 黄芳菊, 110101196611133520, , to_date(13-11-1966, dd-mm-yyyy), 网络营销师, to_date(04-07-2008 17:36:20, dd-mm-yyyy hh24:mi:ss), 12711.46, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1058, 李红勤, 110101197209084047, , to_date(08-09-1972, dd-mm-yyyy), 运维工程师, to_date(01-03-2007 05:57:34, dd-mm-yyyy hh24:mi:ss), 21119.95, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1060, 薛栓成, 110101400710051   , , to_date(10-07-1940, dd-mm-yyyy), 考试专员, to_date(22-08-2007 08:06:47, dd-mm-yyyy hh24:mi:ss), 24544.66, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1061, 高发娃, 110101192905226032, , to_date(22-05-1929, dd-mm-yyyy), 运维工程师, to_date(12-12-2004 22:49:04, dd-mm-yyyy hh24:mi:ss), 23458.34, 10, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1062, 李新昌, 110101196403106013, , to_date(10-03-1964, dd-mm-yyyy), 研发, to_date(01-04-2011 18:00:33, dd-mm-yyyy hh24:mi:ss), 18605.46, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1065, 赵永刚, 110101197507310014, , to_date(31-07-1975, dd-mm-yyyy), 岗位分析师, to_date(05-12-2009 15:00:29, dd-mm-yyyy hh24:mi:ss), 3155.96, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1067, 冯世伟, 110101198811154532, , to_date(15-11-1988, dd-mm-yyyy), 讲师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 17854.88, 40, 1087, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1068, 张延付, 110101197005233514, , to_date(23-05-1970, dd-mm-yyyy), 岗位分析师, to_date(21-05-2009 10:16:16, dd-mm-yyyy hh24:mi:ss), 24294.43, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1070, 陈粉, 110101195801206040, , to_date(20-01-1958, dd-mm-yyyy), 营销专员, to_date(29-06-2011 21:27:04, dd-mm-yyyy hh24:mi:ss), 17708.56, 20, null, 600.00);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1071, 张根祥, 110101194412126513, , to_date(12-12-1944, dd-mm-yyyy), 岗位分析师, to_date(06-10-2006 12:14:34, dd-mm-yyyy hh24:mi:ss), 8802.52, null, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1074, 李秋彩, 110101197411186020, , to_date(18-11-1974, dd-mm-yyyy), 考试专员, to_date(09-04-2005 01:11:29, dd-mm-yyyy hh24:mi:ss), 19315.97, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1075, 李建玲, 110101198110164423, , to_date(16-10-1981, dd-mm-yyyy), 运维工程师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 9023.25, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1076, 张仙, 110101530824316   , , to_date(24-08-1953, dd-mm-yyyy), 考试专员, to_date(09-05-2011 11:15:29, dd-mm-yyyy hh24:mi:ss), 8654.83, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1077, 朱金典, 110101196810023531, , to_date(02-10-1968, dd-mm-yyyy), 营销专员, to_date(11-01-2008 11:13:31, dd-mm-yyyy hh24:mi:ss), 6687.97, 20, null, 2700.00);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1079, 孙玉环, 110101195006056047, , to_date(05-06-1950, dd-mm-yyyy), 考试专员, to_date(17-11-2008 12:48:12, dd-mm-yyyy hh24:mi:ss), 7490.24, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1080, 邢中景, 110101198208284519, , to_date(28-08-1982, dd-mm-yyyy), 运维工程师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 24990.57, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1081, 韩巧真, 110101198404190026, , to_date(19-04-1984, dd-mm-yyyy), 人力资源师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 9880.26, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1084, 杜巧琴, 110101196201036089, , to_date(03-01-1962, dd-mm-yyyy), 岗位分析师, to_date(13-01-2011 16:11:23, dd-mm-yyyy hh24:mi:ss), 21655.36, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1085, 席妞娃, 110101194601154026, , to_date(15-01-1946, dd-mm-yyyy), 运维工程师, to_date(15-08-2008 06:36:11, dd-mm-yyyy hh24:mi:ss), 24615.12, 20, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1087, 薛素粉, 110101196701044528, , to_date(04-01-1967, dd-mm-yyyy), 讲师, to_date(10-09-2007 06:07:14, dd-mm-yyyy hh24:mi:ss), 12357.56, null, 1008, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1088, 王雪英, 110101193311264521, , to_date(26-11-1933, dd-mm-yyyy), 岗位分析师, to_date(18-02-2006 23:37:15, dd-mm-yyyy hh24:mi:ss), 23670.53, 10, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1089, 刘秀菊, 110101530414306   , , to_date(14-04-1953, dd-mm-yyyy), 研发, to_date(11-09-2004 18:17:49, dd-mm-yyyy hh24:mi:ss), 22608.20, null, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1090, 刘芳芳, 110101198912216025, , to_date(21-12-1989, dd-mm-yyyy), 网络营销师, to_date(10-10-2012 00:24:11, dd-mm-yyyy hh24:mi:ss), 9749.45, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1091, 郝方方, 110101198605165580, , to_date(16-05-1986, dd-mm-yyyy), 运维工程师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 5944.72, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1092, 赵利萍, 110101196712063564, , to_date(06-12-1967, dd-mm-yyyy), 岗位分析师, to_date(01-08-2006 17:26:08, dd-mm-yyyy hh24:mi:ss), 4575.09, 30, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1093, 杨联星, 110101195312284013, , to_date(28-12-1953, dd-mm-yyyy), 考试专员, to_date(27-10-2009 08:41:47, dd-mm-yyyy hh24:mi:ss), 24545.93, 40, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1094, 冯焕, 110101194205093524, , to_date(09-05-1942, dd-mm-yyyy), 运维工程师, to_date(27-09-2010 18:25:55, dd-mm-yyyy hh24:mi:ss), 21391.42, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1096, 杨晓雨, 110101198910070043, , to_date(07-10-1989, dd-mm-yyyy), 营销专员, to_date(07-08-2013 05:49:14, dd-mm-yyyy hh24:mi:ss), 4161.31, 20, null, 2322.00);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1097, 柴帅友, 110101198510254038, , to_date(25-10-1985, dd-mm-yyyy), 网络营销师, to_date(24-11-2011 16:08:38, dd-mm-yyyy hh24:mi:ss), 7805.94, 50, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1098, 韩国强, 110101621016003   , , to_date(16-10-1962, dd-mm-yyyy), 研发, to_date(01-09-2007 12:30:52, dd-mm-yyyy hh24:mi:ss), 13755.87, null, null, null);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)values (1099, 王参妮, 11010119490831352X, , to_date(31-08-1949, dd-mm-yyyy), 岗位分析师, to_date(11-10-2005 13:17:18, dd-mm-yyyy hh24:mi:ss), 26627.94, 30, null, null);commit;
需要的sql语句

 

技术分享
--创建表create table teacher(tNo number(4) not null,tName varchar2(20) not null,birthday date);--查询当前用户下面所有的表select * from tab;--查询表中的数据select * from teacher;--01.给表中新增两个字段alter table teacher add(sal number(7,2),wechat varchar2(20));--02.修改字段的属性alter table teacher modify(tName varchar2(10));--03.删除一个字段alter table teacher drop column wechat;--04.新增主键alter table teacher add constraint pk_teacher_tno  primary key(tno);--05.添加唯一约束alter  table teacher add constraint uk_teacher_tname unique(tname);--06.增加一个性别字段alter table teacher add(sax char(2));--07.修改字段的名称alter  table  teacher   rename column sax to sex;--08.增加sex字段的检查约束alter table teacher add constraint ck_teacher_sex check(sex in(,));--09.删除检查约束alter table teacher drop constraint ck_teacher_sex;--10.创建外键约束 在student 从表中创建create  table  student(sNo  number(4) not null primary key,tNum  number(4) not null)alter  table  student add constraint fk_teacher_student foreign key(tNum)references teacher(tNo);
03.表和约束

 

技术分享
--sql  分类DDL 数据定义语言  create  alter drop  truncateDML 数据操作语言  insert delete update  selectTCL  事务控制语言  commit rollback DCL 数据控制语言grant revoke--创建序列create sequence sq_teacher_tno  --序列的名称start with 10   --开始的位置increment by 1   --递增的序列值maxvalue 9999999999   --设置最大值--是否循环  cycle  |  nocycle  --是否保存到内存中   cache| nocache--查询创建的序列信息select * from user_sequences;--查询序列的当前值select SQ_TEACHER_TNO.Currval from dual;--查询序列的下个值select SQ_TEACHER_TNO.nextval from dual;--使用序列新增数据insert into teacher(tno,tname)  values(SQ_TEACHER_TNO.Nextval,小黑2)--dual确实是一个表,只有一个字段select * from dual;select 99*99 from dual;select sysdate from dual;select to_char(sysdate,yyyy-MM-dd hh:mi:ss) from dual;--sql标准 规定!  select 语句中必须要有from ! 就是用dual来--当作一个伪表!也就是查询的信息不存在任何一个表中的时候!
04.序列和伪表

 

技术分享
--导入sql  语句  01.cmd   02.sqlplus 用户名/密码  03.@ sql语句的地址  --01.查询老师的姓名和对应导师的姓名   自连接select t1.tname as 老师姓名,t2.tname as 导师的姓名from teacher t1,teacher t2where t1.mgrno=t2.tno--02. 查询老师姓名,部门名称和部门编号select tname,dname,dept.deptnofrom teacher,dept where teacher.deptno=dept.deptno--03.查询 姓 王 的老师信息  _代表一个字符  %select * from teacherwhere tname like 王%select * from teacherwhere tname like 王_--04.查询陈老师和王老师的薪水和姓名select tname,sal from teacherwhere tname like 陈% or tname like 王%--05.给所有姓王的老师 增加薪水update teacher set sal=sal+20000where tname like 王%--06.删除所有的teacher表中的数据  ! 表结构还在delete  from teacher--07.删除表! 包含数据和表结构drop table dept--08.回忆外键约束  建立在从表中alter table teacher add constraint fk_teacher_deptnoforeign key(deptno)references dept(deptno)--09.查询女姓老师的编号和姓名select tno,tname from teacherwhere gendar=--10.查询薪水在10K-20k之间的老师编号,姓名  薪水select tno,tname,sal from teacherwhere sal between 10000 and 20000--11.查询职位是 讲师或者研发的老师姓名  按照薪水的降序排列select tname,sal,job from teacherwhere job in(讲师,研发)order by sal desc--12.查询部门所有数据的insert语句select insert into dept values(||deptno||,‘‘‘||dname||‘‘‘,‘‘‘||loc||‘‘‘);from dept
05.DML操作

 

技术分享
--子查询  查询的结果集 被当作 另一个查询语句的表 --01.查询招生部 所有男老师的姓名select  tname,dname from teacher,deptwhere teacher.deptno=dept.deptnoand teacher.gendar=  and dept.dname=招生部--子查询实现select  tname,deptno from teacherwhere deptno=(select deptno from dept where dname=招生部)and gendar=--02.查询老师姓名和对应的部门名称   使用子查询???!  --不使用别名 会默认把子查询语句作为列名select tname,(select dname from dept where deptno=teacher.deptno) as 部门from  teacher--03.查询在招生部以及人力部门的老师信息select * from teacherwhere deptno in(select deptno from dept where dname in(招生部,人力部))--使用exists 代替in   使用not exists 代替 not in--exists 并不是返回一个结果集  返回true 或者falseselect * from teacherwhere  exists (select deptno from dept where  deptno=teacher.deptno and dname in(招生部,人力部))
06.子查询

 

--  oracle中的链接操作符select  tname||====||sal||===deptno  from  teacher--事务控制语句--01.创建dept表create    table  dept(deptno number(2) primary key,dname   varchar2(20),loc   varchar2(20));--02.插入数据insert  into  dept  values(10,人力部,北京海淀);insert  into  dept  values(20,财务部,北京海淀);insert  into  dept  values(30,市场部,北京海淀);insert  into  dept  values(40,技术部,北京海淀);--03.再次插入两条数据insert  into  dept  values(50,市场部1,北京海淀);insert  into  dept  values(60,技术部2,北京海淀);--设置回滚点savepoint  a;--再插入新数据insert  into  dept  values(70,市场部3,北京海淀);--事务回滚到指定的回滚点rollback  to savepoint a;--04.查询dept表  有 50,60的部门select *  from dept;--05.回滚事务rollback;-- 查询dept表  有没有 50,60的部门select * from dept;

 

技术分享
--01. 子查询   查询招生部门所有的男老师姓名select tname,(select dname from dept where deptno=teacher.deptno) as 部门名称from teacherwhere deptno=(select deptno from dept where dname=招生部)and gendar=--01. 使用内连接  01 查询招生部门所有的男老师姓名select tname,dnamefrom teacher t,dept dwhere t.deptno=d.deptnoand gendar= and dname=招生部--01. 使用内连接  02查询招生部门所有的男老师姓名select tname,dnamefrom teacher t inner join dept don t.deptno=d.deptnowhere gendar= and dname=招生部--02.自连接  查询老师的姓名 和导师的姓名select t1.tname 老师姓名,t2.tname 导师姓名from teacher t1,teacher t2where t1.mgrno=t2.tno--03.使用左外链接查询老师的姓名,对应导师的姓名 以及部门select  t1.tname 老师姓名,t2.tname 导师姓名,d.dname 部门名称from  teacher t1 left join teacher t2on t1.mgrno=t2.tnoleft join  dept don t1.deptno=d.deptno--使用内连接select  t1.tname 老师姓名,t2.tname 导师姓名,d.dname 部门名称from teacher t1,teacher t2,dept dwhere  t1.mgrno=t2.tno and t1.deptno=d.deptno--04.左外链接  以左表为准   右表中没有匹配的数据 返回空select * from teacher t1 left join dept don t1.deptno=d.deptno--05.右外链接  以右表为准   左表中没有匹配的数据  不显示select * from teacher t1 right join dept don t1.deptno=d.deptno
07.连接查询

 

技术分享
--01.substr 字符串的截取  从第1个位置查询 到第3个位置结束select substr(world,1,3) from dual;--从第1个位置查询之后的所有select substr(world,1) from dual;--会把字符串转换成数字  如果不能转换就不能查询select substr(world,1) from dual;--02.instr 查询字符在 字符串中的下标!  从1开始 没有 返回0select instr(world,o) from dual;--03.to_char把日期转换成指定格式的字符串  hh24小时制select to_char(sysdate,yyyy-MM-dd hh24:mi:ss) from dual;select to_char(sysdate,yyyy"年"-MM"月"-dd"日" hh24:mi:ss) from dual;--04.to_date把字符串转换成指定格式的日期select to_date(2017-02-28 11:16:56,yyyy-MM-dd hh24:mi:ss) from dual;--05.nvl(e1,e2) e1代表列名 e2替换空的值--如果e1位空则返回e2, 如果不会空直接返回当前的值select nvl(mgrno,500) from teacher--06.nvl2(e1,e2,e3) 如果e1位空则返回e3 不为空返回e2select nvl2(mgrno,10000,500) from teacher--07.decode(value,if1,then1,if2,then2.....,else)-- 如果value的值等于if1,返回then1-- 如果value的值等于if2,返回then2--如果value的值不等于任何if,则返回elseselect decode(mgrno,null,0,1008,5000,1) from teacher
08.字符串的截取

 

技术分享
--  聚合函数--查询总记录数 count(1) 替换count(*)select count(1)  from teacher--查询薪水总和select sum(sal)  from teacher--查询最高薪水select max(sal)  from teacher--查询最低薪水select min(sal)  from teacher--查询平均薪水select avg(sal)  from teacher--查询各个部门的 编号,薪水(平均,最高,总和)-- 按照部门的总薪水进行  降序排列select deptno,avg(sal),max(sal),sum(sal)from teachergroup by deptnoorder by sum(sal) desc-- 再增加一个条件  (部门人数在10人以上)select deptno,avg(sal),max(sal),sum(sal)from teachergroup by deptnohaving count(deptno)>10order by sum(sal) desc--验证我们的  部门编号 为空的select count(1)  from teacher where deptno is null 
09.聚合函数

 

技术分享
--分析函数--01.rank():具有相等值的  排位相同,但是之后的排名跳跃select sal,tname from teacher order by salselect sal,tname,rank()  over(order by sal) 薪水排名from teacher--02.dense_rank():具有相等值的  排位相同,之后的排名连续select sal,tname,dense_rank()  over(order by sal) 薪水排名from teacher--03.row_number():不管值是否相等,排名都是连续的select sal,tname,row_number()  over(order by sal) 薪水排名from teacher-- 查询各个部门(按照部门查询)的教师薪水 降序后的结果-- partition by deptno 按照部门进行分组select deptno,tname,sal,rank() over(partition by deptno order by sal desc) 薪水排名from teacher--  rank()/dense rank()/ row_number()  over(partition by 分组的字段  order by 排序的字段 desc(降序)/asc(默认升序))
10.分析函数

 

技术分享
-- 联合查询--01.union 并集(两个结果集中所有的数据) 重复数据显示一次select  tno  from  teacher  where  tno<1020unionselect  tno  from  teacher  where  tno<1025--02.union  all并集(两个结果集中所有的数据) 重复数据显示多次select  tno  from  teacher  where  tno<1020union allselect  tno  from  teacher  where  tno<1025--03.intersect 交集(取两个结果集中公共的数据)select  tno  from  teacher  where  tno<1020intersect select  tno  from  teacher  where  tno<1025--04.补集 minus  a>b  取a-bselect  tno  from  teacher  where  tno<1025minusselect  tno  from  teacher  where  tno<1020--伪列:没有存储在真正的表中,但是,可以在表中查询,不能进行增删改操作!-- rowid:表中行的存储地址! A-Za-z0-9+/ 第二位变成B  依次类推select rowid from teacherselect rowid,tname from teacher where rowid=AAASRxAAGAAAAJ7AAA--rownum:查询返回结果集中 行的编号!  分页的时候使用--rownum只能对=1或者<n进行操作!--对于>n这种结果怎么查询?--01.建立临时表--02.让伪列rownum成为临时表中真正的列--03.使用伪列的别名进行操作--查询教师表中 薪水最高的前5名select tname,rownum from teacher order by sal desc--上面的sql执行后 有rownum 是乱序的--那么我们就重新分配rownum--怎么重新分配?  再次查询  查询的表就是上面的sqlselect tname,rownum from (select * from teacher order by sal desc)where rownum<6--执行上面的sql后 发现 rownum 有序了  而且是薪水的降序排列select * from (select * from teacher order by sal desc)where rownum<6  -- 得到薪水的前5名--查询教师表中第5名的select * from (select * from teacher order by sal desc)where rownum=5    -- 不可行select * from(select  t.*,dense_rank() over(order by sal desc) ranks from teacher t)where ranks=5-- 查询6-10条信息  --01.使用分析函数select * from(select  t.*,dense_rank() over(order by sal desc) ranks from teacher t)where ranks>5 and ranks<11--02.三层嵌套select * from teacher order by sal desc-- rownum是乱序 但是薪水已经降序排列了select t.*,rownum rw from (select * from teacher order by sal desc) t-- 我们把上面的sql 当成一个临时表-- rownum 进行了重新排序!--接下来  就开始查询select * from (select t.*,rownum rw  from(select * from teacher order by sal desc) t)where rw>5 and rw<11
11.联合查询

 


 技术分享

技术分享
-- View:视图(虚表),不占用物理空间! --  如果没有权限 -- 使用dba登录  grant  create any  view to 用户名;create or replace  view  teacher_viewas select tname,sal from teacher--  查询视图select * from teacher_view
12.View视图

 

技术分享
--问题描述:在scott用户下创建视图的时候,报错:权限不足。(其他用户以此类推)--解决方法:--1,以dba用户登录sqlplus / as sysdba  --2,赋予scott用户创建VIEW的权限grant create view to scott  --3,以scott用户登录oracleconn scott/tiger  --4,创建视图成功CREATE OR REPLACE VIEW myview AS      SELECT * FROM emp where deptno = 20;  
报错:权限不足

 

技术分享
--PL/SQL (Procedural  Language) 过程化sql语言! 在原本的sql语句上 添加了 逻辑判断--循环等操作!   在数据库 执行pl/sql程序时,pl/sql和sql语句时分别进行解析和执行的!--语法结构     :=  就是赋值语句--01.declare   可选部分  声明 : 变量, 异常,游标...--02.begin     必要部分   书写:sql语句 和pl/sql语句--03.exception  可选部分   针对出现异常时候的处理--04.end       必要部分 -- 根据指定老师的编号查询薪水  输出一个结果declare   v_name  teacher.tname%type;  v_sal   teacher.sal%type;  v_result  varchar2(30);begin    select t.tname,t.sal into v_name,v_sal from teacher t where  tno=1002;   --根据薪水   得到不同的返回结果    if v_sal>=2000 and v_sal<=10000 then      v_result:=还可以吧;    elsif v_sal>10000 and v_sal<=40000 then      v_result:=真可以;    elsif v_sal>40000 then      v_result:=牛牛牛;    else       v_result:=咩咩;    end if;      dbms_output.put_line(v_name||:||v_sal||=====||v_result);        --根据 v_result的值 输出不同的结果    case v_result      when 还可以吧 then         dbms_output.put_line(v_name||工资在2k到10k);      when  真可以 then          dbms_output.put_line(v_name||工资在10k到40k);      when 牛牛牛 then         dbms_output.put_line(v_name||工资在40k以上);        else             dbms_output.put_line(v_name||工资2k以下);      end case;       end;
13.pl/sql 条件判断

 

技术分享
--循环结构--01.loop 语法   loop     执行的语句;     exit when 条件;   end loop;   declare i number;begin  i:=1;  loop    dbms_output.put_line(i);    i:=i+1;    exit when i>50;   end loop;end;    --02.while  语法  while 条件  loop   执行的语句;  end loop;declare i number;begin  i:=1;  while i<=50 loop    dbms_output.put_line(i);    i:=i+1;     end loop;end;--03.for 语法for 变量 in  范围(上限,下限)loop  执行的语句; end loop;declare i number;begin for i in 1..50   loop    dbms_output.put_line(i);    end loop;end;
14.循环语句

 

技术分享
--游标:是系统给我们用户 开设一个数据缓冲区! 存放的是sql语句执行的结果!--每个游标区都有一个名字,用户通过游标一行一行的获取记录!--01.隐式游标  在我们Pl/sql中,程序执行sql语句时 自动创建! ====>sql--02.显示游标  可以返回多行的查询!--03.REF游标   用于处理运行时才能确定的动态sql 查询结果--隐式游标属性   --01.sql%found   影响了一行或者多行 都返回 true   --02.sql%notfound   没有影响行数   返回 true   --03.sql%rowcount    sql语句影响的行数   --04.sql%isopen    游标是否打开,始终为falsedeclare  tname  teacher.tname%type; --声明一个变量  cursor teacher_cursor  is  select tname from teacher; --声明游标begin   open teacher_cursor;  --打开游标    fetch teacher_cursor into tname;   --循环输出tname   while teacher_cursor%found     loop       dbms_output.put_line(教师的姓名====||tname);       fetch teacher_cursor into tname;     end loop;   close teacher_cursor;--关闭游标end;  
15.游标

 

技术分享
--触发器  :old 代表之前的值  :new 更改之后现在的值  这两个值  只能在 for each row 中使用 update语句  :old   :newinsert语句  :newdelete语句  :old   --创建一个teacher_log (只要有人动teacher表,数据就会记录在teacher_log表中)create table teacher_log(logid number not null,old_value varchar2(200),create_date date,log_type number,tno number)--给logid设置主键alter  table teacher_log add constraint pk_teacher_logid primary key(logid);--创建序列create sequence sq_teacher_logidminvalue 1maxvalue 999999999start with 1increment by 1cache 20;--创建触发器 or replace 如果存在 则 修改create or replace  trigger tr_teacherafter insert or update or delete --会在增删改之后 触发on teacher for each row--声明declarev_old_value teacher_log.old_value%type;v_type      teacher_log.log_type%type;v_tno       teacher_log.tno%type;begin  if inserting then    v_type:=1; --新增    v_tno :=:new.tno;    v_old_value:=:new.tno||=====||:new.tname;   elsif deleting then    v_type:=2; --删除    v_tno :=:old.tno;    v_old_value:=:old.tno||=====||:old.tname;   else    v_type:=3; --修改    v_tno :=:old.tno;    v_old_value:=:old.tno||=====||:old.tname||====||:new.sal;  end if;  --将记录写入到 teacher_log insert into teacher_log values (sq_teacher_logid.nextval,v_old_value,sysdate,v_type,v_tno); end tr_teacher;
16.触发器

 

技术分享
--函数 functioncreate or replace function fn_teacher_tid(f_tid varchar2) return varchar2isf_result  teacher.tid%type;begin    if length(f_tid)!=18  then      dbms_output.put_line(身份证不正确);      else       dbms_output.put_line(身份证正确);      end if;    --给返回值赋值    f_result:=substr(f_tid,1,6)||********||substr(f_tid,15);    return f_result;end fn_teacher_tid;  --调用函数select fn_teacher_tid(110101198603304014) from dual;
17.函数

 

技术分享
--存储过程   一组完成特定功能的sql语句集-- 新增教师   身份证不满足要求 报错create or  replace   procedure pro_add_teacher(p_tno teacher.tno%type,p_tname  teacher.tname%type,p_tid  teacher.tid%type,p_sal  teacher.sal%type)ise_tid_validate exception;begin  if length(p_tid)!=18  --判断身份证号不满足18位    then  --抛出异常      raise e_tid_validate;  end if;    --新增  insert into teacher(tno,tname,tid,sal)  values(p_tno,p_tname,p_tid,p_sal);  --手动提交事务  commit;  --对异常进行处理exception    when e_tid_validate then      dbms_output.put_line(请输入正确的身份证号);      when others then          dbms_output.put_line(其他的异常);end pro_add_teacher;      --调用存储过程  call pro_add_teacher(2001,小黑黑,123456789123456789,5000);   
18.存储过程

 


技术分享

 

20170621_oracle练习