注：问题处以‘问：’开头
create index 索引名 on 表名 (字段名 ASC);添加索引
select 命令的标准格式如下：
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
字符串相关函数
1、
INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1为被搜索的字符串，
C2为希望搜索的字符串；
I为搜索的开始位置,默认为1；
J为出现的位置,默认为1。
INSTRB类似INSTR,区别于，对于单字节字符系统，返回的值以字节为单位，中文一个字符算2个字节。
select instr('浙江网新恩普软件有限公司', '恩普') from dual;
返回值是5
select instrb('浙江网新恩普软件有限公司', '恩普') from dual；
返回值是9
2、
SUBSTR(C,M,[N])返回C中字母位置M开始的N个字符。
若M=0，则M当1看待，若M为正数，从左到右算，反之，从右往左数。
若N缺省，则设置为整个字符串，若N小于1，则返回null。
如M、N用了浮点型，则被转化为整型。
SUBSTRB(C,M,[N])类似substr，不过参数M,N用字节表示。
select substr('浙江网新恩普软件有限公司',5,2) from dual;
返回值是恩普
select substrb('浙江网新恩普软件有限公司',9,2) from dual;
返回值是恩
3、
LENGTH(C) 返回字符串C的长度，包括填充的字符。
若C有数据型CHAR，则字符串的长度包括其结尾的空格。若C为NULL，则该函数返回NULL；如果输入的是字符型需要加引号。
LENGTHB(C)类似LENGTH(C)，区别于，返回的值以字节为单位；
select length('恩普') from dual;
返回值是2
select lengthb('恩普') from dual;
返回值是4
4、
REPLACE(C1,C2，[C3]) 返回用C3字符串替换掉C2以后的C1。含c2本身
若C3没有指明，则C2内容将被删除。
select replace('ESOFTEPSOFT','E','34') from dual;
LOWER(C) 转换为小写;
UPPER(C) 转换为大写;
5、
TRIM(C)删除指定字符串中左边或右边的字符（默认为空格）;C内容可以用表达式，如select trim('E' from 'EEPSOFTEPSOFTE')EEPSOFTEPSOFTE from dual
LTRIM(C1,[C2]) 删除左边字符，C2默认为空格。
select ltrim('EEPSOFTEPSOFTE','E') EEPSOFTEPSOFTE from dual
RTRIM(C1,[C2]) 删除右边字符，C2默认为空格。
6、
LPAD(C1,N,[C2]) C1左边补C2增长到总长度为N。C2未设定时，默认空格，当C2+C1的长度大于N时，仅C2的前几个被复制，当C2+C1的长度小于N时，则重复补C2直到总长度为N。 C2由左边开始填充
RPAD(C1,N[,C2])相似于LPAD，它是右补足。
select lpad('SOFT','10','EiiP') from dual
数字相关函数
1、
ABS(n)返回N的绝对值，通常为正值，N必须是数字。
select ABS(-4) from dual;
返回值4
2、
CEIL(N)返回大于或等于N的最小整数值。
select ceil(1.1) from dual;
返回值 2
select ceil(-1.1) from dual;
返回值 -1
3、
FLOOR(N)返回小于或等于N的最大整数值。
select floor(1.1) from dual;
返回值 1
select floor(-1.1) from dual;
返回值 -2
4、
TRUNC(M，[N])返回到N位小数的M的值。N缺省值为0，结尾为一个整数值，如果N为负数，则结尾到小数点左边相应的位置上。
select trunc(111.111,2) from dual;
返回值111.11
select trunc(111.111,-2) from dual;
返回值 100
5、
ROUND(M,[N])与TRUNC类似，区别在于取数截取时进行了四舍五入的判断。select round(555.545,1) from dual;
返回值 555.5
6、
MOD(M,N)返回M 除以N的余数。
select mod(5,2) from dual 返回值 1
7、
SIGN(N)如果N为正返回1，如果负数返回-1，如果0，返回0。与DECODE函数组合，可以实现数字大小比较的条件语句。 N可以为一个算术式。
select sign(3-9) from dual; 返回值 -1
日期相关函数
1、
ADD_MONTHS(D,N) 返回日期D加上N个月后的结果。
如果N个月后的日期中不存在D中的日期，则返回该月的月末日期。
select to_char(add_months(sysdate,98),'yyyy-mm-dd') from dual;
返回值 2020-07-17
2、
LAST_DAY(D)给出D所在月的最后一天的日期
select to_char(last_day(sysdate),'yyyy-mm-dd') from dual
返回值 2012-05-31
3、
MONTHS_BETWEEN(D1,D2)返回D1、D2之间的月份值。若D1 select to_char(months_between(to_date('2017.11.29','yyyy.mm.dd'), to_date('1998.11.29','yyyy.mm.dd'))) from dual 返回值 228 4、 SYSDATE，返回数据库服务器当前的日期和时间，无参数 类型操作相关函数 1、 TO_CHAR 将日期、数字等其他类型转换为字符型，日期一般为”yyyy-mm-dd hh24:mi:ss”的格式转换，数字一般为”999999.99”或”0000.00”的格式转换。注：数字转换的时候带四舍五入功能，如果转换数字位数不够格式长度的9代表加空格，0代表加零。 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual 返回值 2012-05-17 15:08:42 select to_char(2322.33466,'9990000000.00') from dual 返回值 0002322.33 2、 TO_DATE 将字符串转换为日期型 select to_date('2013.02.16','yyyy-mm-dd') from dual 返回值 2013/2/16 3、 TO_NUMBER将字符串转换为数字 select to_number('2322.33466') from dual 返回值2322.33466 统计相关函数 1、GREATEST(E,[E]...)返回参数中的最大值，比较类型可以为字符型、日期型或数字型。注如果是对比是顺序字符逐一对比，取同位置最大是参量并结束对比。select greatest('2012-09-12','2019-12-09','2001-10-22') from dual 返回值 2019-12-09 select greatest('aaabc','aaawa','aaa2s') from dual 返回值 aaawa select greatest(1112,1113,1211) from dual 返回值 1211 2、 LEAST返回最小值。格式和greatest类似 3、 MAX 求结果集中的某列最大值，一般用于取表中某列的最大值。 MIN 求结果集中的某列最小值，与MAX类似。 select min(a.idseno) from sbdb_psiden a 4、 NVL(E1,E2) 替换空值函数，当E1为空时，返回E2，否则，返回E1。对空值有 限制的地方需要用到。问：什么叫对空值有限制的地方需要用到？ select nvl('','wssw') from dual 返回值 wssw select nvl('ddd','wssw') from dual 返回值 ddd 5、 SUM 求结果集中的某列或表达式的和。 6、 COUNT 求结果集中的某列的个数 条件判断相关函数 1、 CASE WHEN 表达式1成立 THEN 值1 WHEN表达式2成立 THEN 值2 … WHEN 表达式n成立 THEN 值n ELSE 缺省值 END CASE select case when 1-4<>1 then 'a' when 1+2<1 then 'b' when 2+1>1 then 'c' else 'd' end iu from dual;注只要一个表达式成立就输出结果并结束查询。返回值： a 2、 DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 该函数的含义如下：IF 条件=值1 THEN RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN(翻译值2) ... ELSIF 条件=值n THEN RETURN(翻译值n) ELSE RETURN(缺省值) END IF select psname 姓名，bdate 出生日期, decode(pdcode,'01','养老保险','02','医疗保险','03','失业保险','04','工伤保险','05','生育保险') 参保险种， decode(pdcodezt,'0','正常','不缴费') 缴费状态 from sbdv_psoparch where rownum<20 内置列 1、 ROWNUM 该行在当前条件下的顺序号。如1、2、3…。可以用来选取部分数据，也可以实现分页处理等。 2、 ROWID该列的实际存储位置 select rowid,psname from sbdb_psiden where rownum<20 分组排序相关 1、 GROUP BY 分组 HAVING DISTINCT 禁止重复 ORDER BY 排序其中ASC表示升序，为默认值，DESC为降序。ORDER BY不能按ntext、text和image数据类型进行排序。 集合运算 1、 UNION 查询可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显示，即执行联合查询。取两个语句的并集，去除相同项。 2、 UNION ALL查询可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显示，即执行联合查询。取两个语句的并集。 3、 INTERSECT 得到两个查询结果的交集。 4、 MINUS 得到两个查询的减集。 5、 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。？ 多表关联 1、 exists（sql 返回结果集为真）和in 差不多 not exists (sql 不返回结果集为真） 如下： 表A ID NAME 1 A1 2 A2 3 A3 表B ID AID NAME 11 B1 22 B2 32 B3 表A和表B是１对多的关系 A.ID => B.AID SELECT ID,NAME FROM A WHERE EXIST (SELECT*FROM B WHERE A.ID=B.AID) 执行结果为 1 A1 2 A2 原因可以按照如下分析 SELECT ID,NAME FROM A WHERE EXISTS (SELECT*FROM B WHERE B.AID=１) --->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据 SELECT ID,NAME FROM A WHERE EXISTS (SELECT*FROM B WHERE B.AID=2) --->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据 SELECT ID,NAME FROM A WHERE EXISTS (SELECT*FROM B WHERE B.AID=3) --->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据 NOT EXISTS就是反过来 SELECT ID,NAME FROM A WHERE NOT EXIST (SELECT*FROM B WHERE A.ID=B.AID) 执行结果为 3 A3 数据库操作语言 SQL语言包含3个部分： 数据操作语言Data Manipulation Language(DML），操作：make/ 例如：INSERT （插入）、UPDATE（修改）、DELETE（删除）语句。 数据定义语言Data Definition Language(DDL），定义：definition/ 例如：create、drop、alter等语句。 数据控制语言Data Controlling Language（DCL），控制：control/ 例如：grant、revoke、commit、rollback等语句。 1、insert 插入值 INSERT INTO 表名（列名1，列名2，…） VALUES (值1,值2，…); 2、update语句用来更新或修改一行或多行中的值 Update table_name set column_name=’values’ where 条件 简单用法： update customers set city_name='北京' where customer_id<1000 两表(多表)关联update ： 仅在where字句中的连接--这次提取的数据都是VIP，且包括新增的,所以顺便 更新客户类别 update customers a -- 使用别名 set customer_type='01' --01 为vip，00为普通 where exists (select 1 from tmp_cust_city b where b.customer_id=a.customer_id) 3、DELETE 和 truncate 语句用来删除数据表中一行或多行的数据，您也可以删除资料表中的所有数据行。DELETE FROM 表名称 WHERE 列名称 = 值 Truncate table table_name 4、DROP：属于DDL语句，作用是删除整个表、删除指定的用户、删除指定的存储空间等等 语法： drop table table_name [cascade constraint] drop user user_name [cascade] drop tablespace tablespace_name including content and datafiles 5、 数据定义语言DDL，数据定义语言DDL用来创建数据库中的各种对象-----表、 视图、 索引、同义词、聚簇等(TABLE/VIEW/INDEX/SYN/CLUSTER) 语句： 语句名称含义 CREATE DATABASE 创建数据库 CREATE TABLE 创建表 DROP TABLE 删除表 ALTER TABLE 修改表的结构 CREATE VIEW 创建视图 DROP VIEW 删除视图 CREATE INDEX 创建索引 DROP INDEX 删除索引 修改表明：rename 旧表名 to 新表名; Alter： ALTER TABLE (表名) ADD (列名数据类型); --添加列 ALTER TABLE (表名) MODIFY (列名数据类型); --修改列的数据类型 ALTER TABLE (表名) RENAME COLUMN (当前列名) TO (新列名); 修改列名 ALTER TABLE (表名) DROP COLUMN (列名);--删除列 ALTER TABLE (当前表名) RENAME TO (新表名);--修改表明 6、 Comment on 注释命令 Comment on table table_name is ‘注释语句‘；表的注释命令 Comment on column column_name is ‘注释语句’；列的注释命令 7、 rollback to savepoint ‘保存点的点名’--回到保存点的状态。 Rollback回滚命令使数据库状态回到上次最后提交的状态 循环 1、 LOOP --循环起始标识 语句1; 语句2; EXIT [WHEN 条件]; END LOOP; --循环结束标识 该循环的作用是反复执行LOOP与END LOOP之间的语句。 EXIT用于在循环过程中退出循环，WHEN用于定义EXIT的退出条件。如果没有WHEN条件，遇到EXIT语句则无条件退出循环。declare x number; begin x:=0; loop --循环开始 x:=x+1; if x>3 then --循环体 ex it; end if; dbms_output.put_line('内x的值'||x); --循环体 end loop; --结束循环 dbms_output.put_line('外X的值'||x); end; / 输出结果: 内x的值1 内x的值2 内x的值3 外X的值4 2、 while和loop结合循环的案例 declare x number; y varchar2(10); begin x:=1; while x<=3 loop --while和loop循环 x:=x+1; dbms_output.put_line('x的值为'||x); end loop; --结束循环 end; 3、 FOR LOOP循环 FOR循环是固定次数循环，格式如下： FOR 控制变量in [REVERSE] 下限..上限 LOOP 语句1; 语句2; END LOOP; 循环控制变量是隐含定义的，不需要声明。 下限和上限用于指明循环次数。正常情况下循环控制变量的 取值由下限到上限递增，REVERSE关键字表示循环控制变量的取 值由上限到下限递减。 begin for I in 1..5 loop --for和loop结合 dbms_output.put_line('i的值'||i); end loop; dbms_output.put_line('out'); end; 4、 SELECT INTO 语句 SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。 SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。SQL SELECT INTO 语法 您可以把所有的列插入新表： SELECT *INTO new_table_name [IN externaldatabase] FROM old_tablename 或者只把希望的列插入新表： SELECT column_name(s)INTO new_table_name [IN externaldatabase] FROM old_tablename SQL SELECT INTO 实例 - 制作备份复件 下面的例子会制作 "Persons" 表的备份复件： SELECT *INTO Persons_backupFROM PersonsIN 子句可用于向另一个数据库中拷贝表： SELECT *INTO Persons IN 'Backup.mdb'FROM Persons如果我们希望拷贝某些域，可以在 SELECT 语句后列出这些域： SELECT LastName,FirstNameINTO Persons_backupFROM Persons SQL SELECT INTO 实例 - 带有 WHERE 子句 我们也可以添加 WHERE 子句。 下面的例子通过从 "Persons" 表中提取居住在 "Beijing" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表： SELECT LastName,FirstnameINTO Persons_backupFROM PersonsWHERE City='Beijing' 把一张旧表里的字段复制到另外一张新表中.可以这样写sql语句 select * into 新表 from 旧表 where 新表.id=旧表.id DBMS_OUTPUT、set serveroutput on等相关 1、enable：在serveroutput on的情况下，用来使dbms_output生效(默认即打开) 2、disable：在serveroutput on的情况下，用来使dbms_output失效 3、put：将内容写到内存，等到put_line时一起输出 4、put_line：不用多说了，输出字符 5、new_line：作为一行的结束，可以理解为写入buffer时的换行符 6、get_line：获取没有输出的buffer中的信息 7、get_lines：以数组形式来获取所有buffer中的信息 需要注意以下几点： ----------------------- 1、set serveroutput on：如果要在sqlplus中看到dbms_output的输出，则必须设置该参数值为on 2、每行能容纳的最大值是32767bytes 3、buffer的默认值是20000bytes，可设置的最小值为2000bytes，最大值为1000000bytes 下面举几个例子来说明一些不常用的方法的使用： 例子1：(put和new_line) ----------------------- SQL> set serveroutput on SQL> begin 2 dbms_output.put_line('three names will be written.'); 3 dbms_output.put('j'); --增加至buffer 4 dbms_output.new_line; --回车 5 dbms_output.put('s'); --增加至buffer 6 dbms_output.new_line; --回车 7 dbms_output.put('t'); --增加至buffer 8 dbms_output.new_line; --回车 9 dbms_output.put_line('over.'); --与之前所有一起输出 10 end; 11 / three names will be written. j s t over. PL/SQL procedure successfully completed 例子2：(put_line) ----------------------- SQL> set serveroutput off; SQL> create table t(a int,b int,c int); Table created SQL> insert into t values(111111,222222,333333); 1 row inserted SQL> insert into t values(444444,555555,666666); 1 row inserted SQL> insert into t values(777777,888888,999999); 1 row inserted SQL> create table tt(a int,b varchar2(100)); Table created SQL> declare 2 msg varchar2(120); 3 cursor t_cur is select * from t order by a; 4 v_line varchar2(100); 5 v_status integer := 0; 6 begin 7 dbms_output.enable; 8 for i in t_cur loop 9 msg := i.a || ',' || i.b || ',' || i.c; 10 dbms_output.put_line(msg); 11 end loop; 12 13 dbms_output.get_line(v_line,v_status); 14 while v_status = 0 loop 15 insert into tt values(v_status, v_line); 16 dbms_output.get_line(v_line,v_status); 17 end loop; 18 end; 19 / PL/SQL procedure successfully completed SQL> select * from tt; A B --- ------------------------------ 0 111111,222222,333333 0 444444,555555,666666 0 777777,888888,999999 注：使用get_line时不能用put_line输出，因为put_line之后会将buffer 清空。(当然在serveroutput off的情况下put_line是不影响buffer的) 例子3：(put_lines) ----------------------- SQL> set serveroutput on SQL> declare 2 v_data dbms_output.chararr; 3 v_numlines number; 4 begin 5 -- enable the buffer first. 6 dbms_output.enable(1000000); 7 8 dbms_output.put_line('line one'); 9 dbms_output.put_line('line two'); 10 dbms_output.put_line('line three'); 11 12 v_numlines := 3; 13 dbms_output.get_lines(v_data, v_numlines); 14 for v_counter in 1..v_numlines loop 15 dbms_output.put_line(v_data(v_counter)); 16 end loop; 17 end; 18 / line one line two line three PL/SQL procedure successfully completed Oracle中分析函数用法小结 一.分析函数适用场景: ○1需要对同样的数据进行不同级别的聚合操作 ○2需要在表内将多条数据和同一条数据进行多次的比较 ○3需要在排序完的结果集上进行额外的过滤操作 二.分析函数语法: FUNCTION_NAME( chapter 7 1.Oracle数据库物理存储结构的组成及各个部分的功能、性质。 数据文件：用于存储数据库中的所有数据 控制文件：用于记录和描述数据库的物理存储结构信息 重做日志文件：用于记录外部程序（用户）对数据库的改变操作 归档文件：用于保存已经写满的重做日志文件 初始化参数文件：用于设置数据库启动时的参数初始化值 跟踪文件：用于记录用户进程、数据库后台进程等的运行情况 口令文件：用于保存具有SYSDBA,SYSOPER权限的用户名和SYS用户口令； 警告文件：用于记录数据库的重要活动以及发生的错误； 备份文件：用于存放数据库备份所产生的文件。 2.数据文件管理： （1）数据文件与表空间的关系 从物理角度看，数据库由数据文件构成，数据存储在数据文件中；从逻辑角度看，数据库是由表空间构成的，数据存储在表空间中。一个表空间包含多个数据文件，但一个数据文件只能属于一个表空间。 在oracle数据库中，数据文件是依附于表空间而存在。一个表空间可以包含多个数据文件，但一个数据文件只能从属于一个表空间。在逻辑上，数据库对象都存放在表空间中，实质上是存放在表空间所对应的数据文件中。 （2）数据文件的添加、数据文件大小的修改autoextend on 、resize（操作）、数据文件的移植 添加： 向表空间添加一个大小为xMB的数据文件： alter tablespace 表空间名add datafile ‘路径\*.dbf’ size xMB; 向表空间添加一个自动增长的数据文件： alter tablespace 表空间名add datafile ‘路径\*.dbf’ size xMB autoextend on next 1M maxsize 50MB; 向临时表空间添加一个大小为xMB的数据文件： alter tablespace 表空间名 add tempfile‘路径\*.dbf’ size xMB; 修改： ①修改大小 修改数据文件大小为yMB： Alter database datafile ‘路径\*.dbf’ resize yMB; 修改文件的自动增长方式： Alter database datafile ‘路径\*.dbf’ autoextend on next 1M maxsize unlimited； Oracle数据库实验报告 实验一：Oracle 10g安装卸载及相关工具配置 一、实验目标： 安装Oracle 10g，了解OEM，通过DBCA安装数据库，通过DBCA删除数据库，sqldeveloper连接数据库，卸载oracle 10g。 二、实验学时数 2学时 三、实验步骤和内容： 1、安装Oracle10g（默认安装数据库） 双击setup.exe， 选择基本安装，安装目录D：盘，标准版，默认数据库orcl，口令bhbh。 进入先决条件检查界面时：网络配置需求选项不用打勾，直接下一步，是。 直到安装成功。 2、登陆和了解OEM 主要是已网页的形式来对数据库进行管理。 http://主机IP:1158/em 用户名：sys 口令：bhbh 身份：sysdba 或者 用户名：system 口令：bhbh 身份：normal 3、通过DBCA删除已安装的默认数据库orcl 程序->Oracle - OraDb10g_home1->配置和移植工具->Database Configuration Assistant->删除数据库->…… 4、通过DBCA安装数据库xscj 程序->Oracle - OraDb10g_home1->配置和移植工具->Database Configuration Assistant->创建数据库->…… 5、sqldeveloper连接数据库 打开sqldeveloper，新建连接 连接名：system_ora 用户名：system 口令：bhbh 主机名：本机计算机名 SID:xscj 测试，显示成功，连接，保存。 6、卸载oracle 10g Windows下 1>停止所有Oracle服务，点Universal Installer卸载 2>删除注册表中的所有关于Oracle项 在HKEY_LOCAL_MACHINE\SOFTWARE下，删除Oracle目录 3>删除硬盘上所有Oracle文件。 （1）Oracle安装文件 （2）系统目录下，在Program files文件夹中的Oracle文件 四、上机作业 根据实验步骤完成逐个实验目标中的任务。 五、心得体会 通过这次的实验，我了解了oracle数据库的情况。懂得了数据库就是把数据存储在一个类似与仓库的地方，需要用时才从数据库里调出来。通过上机实践，知道了装数据库和卸载数据库，并且学会了怎样连数据库。 实验二：Oracle 10g手工建数据库 一、实验目标： 安装Oracle 10g数据库环境，手工建立数据库；通过Net Configuration Assistant建立监听，使用sqldeveloper连接数据库测试。 二、实验学时数 2学时 三、实验步骤和内容： 先安装好Oracle 10g数据库环境（不安装默认数据库）。 1.创建好相关的目录 oracle视图总结(转) 视图简介: 视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。 视图的优点： 1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。 2.用户通过简单的查询可以从复杂查询中得到结果。 3.维护数据的独立性，试图可从多个表检索数据。 4.对于相同的数据可产生不同的视图。 视图的分类： 视图分为简单视图和复杂视图。 两者区别如下： 1.简单视图只从单表里获取数据，复杂视图从多表获取数据； 2.简单视图不包含函数和数据组，复杂视图包含； 3.简单视图可以实现DML操作，复杂视图不可以。 视图的创建： CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] 其中： OR REPLACE：若所创建的试图已经存在，ORACLE自动重建该视图； FORCE：不管基表是否存在ORACLE都会自动创建该视图； NOFORCE：只有基表都存在ORACLE才会创建该视图： alias：为视图产生的列定义的别名； subquery：一条完整的SELECT语句，可以在该语句中定义别名； WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束； WITH READ ONLY ：该视图上不能进行任何DML操作。 例如： Sql代码 1.CREATE OR REPLACE VIEW dept_sum_vw 2.(name,minsal,maxsal,avgsal) 1、数据库基本语句 （1）表结构处理 创建一个表：cteate table 表名（列1 类型，列2 类型）; 修改表的名字 alter table 旧表名 rename to 新表名 查看表结构 desc 表名（cmd） 添加一个字段 alter table 表名 add（列类型）; 修改字段类型 alter table 表名 modify(列类型); 删除一个字段 alter table 表名 drop column列名; 删除表 drop table 表名 修改列名 alter table 表名 rename column 旧列名 to 新列名; （2）表数据处理 增加数据：insert into 表名 values(所有列的值); insert into 表名（列）values(对应的值); 更新语句：update 表 set 列=新的值，…[where 条件] 删除数据：delete from 表名 where 条件 删除所有数据，不会影响表结构，不会记录日志， 数据不能恢复--》删除很快: truncate table 表名 删除所有数据，包括表结构一并删除: drop table 表名 去除重复的显示：select distinct 列 from 表名 日期类型:to_date(字符串1，字符串2)字符串1是日期的字 符串，字符串2是格式 to_date('1990-1-1','yyyy-mm-dd')-->返回日期的 类型是1990-1-1 （3）查询语句 1）内连接 select a.*,b.* from a inner join b on a.id=b.parent_id 湘潭大学 实验报告 课程： Oracle数据库 实验题目：数据查询 学院：信息工程学院 专业：计算机科学与技术2班 学号： 2013551417 姓名：韩林波 指导教师：郭云飞 完成日期： 2015.5.25 一．上机目的 1. 掌握Select语句的运用， 2. 掌握一些函数的应用， 3. 掌握子查询的运用， 4. 掌握连接和分组的应用， 5. 掌握视图的创建。 二．实验内容 常用oracle语句的学习，与相应视图的创建 三．上机作业 写出下列应用对应的SQL语句，并将查询语句定义为视图，视图名根据题号依次命名为V1、V2、…，如果一个应用要定义多个视图，则视图名根据题号依次命名为V1_1、V1_2、…。 针对基本表EMP和DEPT完成下列查询 1) 检索EMP中所有的记录。 create or replace view v1 as select * from emp; 2) 列出工资在1000到2000之间的所有员工的ENAME，DEPTNO，SAL。 create or replace view v2 as select ename,deptno,sal from emp where sal between 1000 and 2000; 3) 显示DEPT表中的部门号和部门名称，并按部门名称排序。 create or replace view v3 as select dname,deptno from dept group by DNAME,deptno; 4) 显示所有不同的工作类型。 create or replace view v4 as select distinct job from emp; 5) 列出部门号在10到20之间的所有员工，并按名字的字母排序。 create or replace view v5 as select ename from emp where deptno between 10 and 20 order by ename; 1、在开发环境中连接到数据库，进行基本的select查询操作； 2、熟悉plsql的使用； 3、熟悉sqlplus相关命令（登录、查询、导入导出等） 登录sqlplus： 第一：使用dos窗口登录sqlplus Sqlplus 用户名/密码@数据库实例名 Sqlplus system/密码@数据库实例名as sysdba 第二：使用oracle自带的一个sqlplus登录，提供界面，显得更简单一些。 显示当前用户名：show user; 创建一个用户：create user 用户名identified by 密码； 给用户赋予登录的权限：grant connect to 用户名；（此时才可以使用这个用户来登录这个数据库。）给其赋予dba的权限。 修改用户的密码：alter user 用户名identified by 新密码； 查询： Select * from t_user; Select id from t_user; Select name from t_user; Select birthday from t_user; Select id,name from t_user; 导入导出： 导出表: （注意，导出表的exp命令不是在sqlplus下使用的，是在dos窗口下使用的命令。） exp userid=test/sa@test tables=(qx) file=d:\e1223.dmp exp userid=test/sa@test tables=(t_user,qx) file=f:\test.dmp 导出方案： Exp userid=test/sa@test owner=test file=f:\test2.dmp 导出数据库： Exp userid=test/sa@test full=y inctype=complete file=f:\all.dmp 导入表： 下面以一个例子来说明： 看下面的图，我的用户名test，密码sa，数据库实例名test，所有的表都在这里 现在我执行导出表JD的操作：exp userid=test/sa@test tables=(jd) file=f:\jd.dmp 在我的f盘下就出现了这么一个.dmp文件 一．创建用户 在YGGL数据库中创建一个用户MANAGER，授予DBA角色和SYSDBA系统权限，它可以替代system系统用户。 CONNECT SYSTEM/123456 AS SYSDAB CREATE USER MANAGER PROFILE "DEFAULT" IDENTIFIED BY manager DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; GRANT SYSDBA TO "MANAGER"; GRANT "COUNTNECT" TO "MANAGER"; GRANT "DBA" TO "MANAGER"; ALTER user MANGER GRANT CONNECT THROUGH SYSTEM; 二：创建角色 在YGGL数据库中创建一个角色ADMIN，授予DBA角色和SYSDBA系统权限。CREATE ROLE ADMIN IDENTIFILED BY "manager"; GRANT SYSDAB TO ADMIN ; GRANT SYSOPER TO ADMIN; GRANT "CONNECT" TO ADMIN; GRANT "DBA" TO "ADMIN"; 一．创建概要文件 在YGGL数据库中创建概要文件YGGL_PROFILE并分配给用户MANAGER。CREATE PROFILE "XSCJ _PROFILE"LIMIT COMPOSITE_LIMIT DEFAULT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 20 PASSWORD_GRACE_TIME 60 PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME 120 PASSWORD_VERIFY_FUNCTION DEFAULT （1）使用命令方式分别向YGGL数据库个表中插入一行记录 INSERT INTO ZPEMPLOYEES V ALUES('011112','罗林 ',TO_DATE('26-06-1973','DD-MM-YYYY'),1,'解放路100 ',210002,4055663,5); INSERT INTO ZPDEPARTMENTS V ALUES('6','人力资源',NULL); INSERT INTO ZPSALARY V ALUES ('011112',1200.09,50) （2）修改表salry的记录值 UPDATE ZPSALARY SET InCome=2890 WHERE EmployeeID='011112'; （3）删除Salary中的一行记录 DELETE FROM ZPSALARY WHERE EmployeeID='01112'; 3.索引和完整性 (1).对YGGL数据库中的Employees表的DepartmentID列建立索引 CREATE INDEX PK_XS_BAK ON ZPEMPLOYEES(DepartmentID) TABLESPACE USERS PCTFREE 48 INITRANS 10 MAXTRANS 100 STORAGE (INITIAL 64K NEXT 64K MINEXTENTS 5 MAXEXTENTS 20 Oracle实验总结 Oracle实验总结 Oracle实验总结 近日做了基于ORACLE的数据库安全、对象sql数据库，XML数据库的实验，其中遇到很多问题，先总结如下。 1、安装Oracle的过程中可以‘选择建立数据库’，也可以选择‘只安装软件’，后者可在安装好软件后，建立数据库，可参考网上的很多安装步骤。 2、Oracle的默认用户有sys和system,在命令行或者sQl*plus中登陆时，写法如下： Connectsys/123456assysdba;其中123456是密码，在创建数据库的时候设置的。 普通用户登录，connectzhangsan/123456; 3、grantselect不支持对表中选定的几列授权，只能将整个表的select权限授权出去。所以实验中先建立了基于选课信息表的视图SelectiveInfo1，视图中包含成绩列，学号列以及班级列（不包含其他的列）,再将对视图SelectiveInfo1的select权限授予角色teacher。 4、创建用户的时候，一定要说明该用户对表空间的权限即（quotaunlimitedontablespace或者具体限定大小的句子），否则该用户即使被授予了建表权限，也不能建表。 创建用户的时候必须给用户授予createsession的权限，这是最基本的权限。 5、不同的用户可以创建相同的表格，例如，teacher用户创建了table1,student 用户也创建了table1,且这两个表格式完全相同的，那么其他用户需要用这两个表格时，可以用teacher.table1和student.table1加以区分。系统用户创建的 实验三数据库的启动和关闭 实验目的 （1）掌握使用DBCA创建数据库的方法 （2）掌握启动和关闭数据库的命令 实验内容 （1）使用DBCA创建数据库test，并通过SQLPLUS登录到test数据库。 （两种方式：A. set oracle_sid=test ，然后再正常登录 B．sqlplus sys/密码@test as sysdba 登录以后可以通过show parameter db_name查看数据库的名称）（2）练习启动数据库的五种命令，并理解它们的使用含义。练习使用TRANSACTIONAL、IMMEDIATE选项来关闭数据库。 （3）只有数据库处于open模式下scott用户才能连接上，nomount和mount 模式不行。试着设计一系列命令，验证这句话的正确性。 （4）对于startup restrict，分别使用system用户和scott用户连接，将命令结果和由此总结出来的结论记录到实验报告中，再使用alter命令结束限制 访问状态，并将使用的命令按顺序记录到实验报告中。 实验数据记录及分析（或程序及运行结果） （1）sqlplus sys/123@test as sysdba show parameter db_name （2）sqlplus sys/orcl@orcl as sysdba Shutdown immediate Startup nomount Alter database mount; Shutdown immediate Startup force Shutdown transactional Startup restrict Shutdown immediate (3) sqlplus sys/orcl@orcl as sysdba Shutdown immediate Conn scott/tiger 没有打开数据库 conn sys as sysdba orcl shutdown immediate startup mount conn scott/tiger 没有打开数据库 conn sys as sysdba orcl shutdown immediate startup open conn scott/tiger （4）sqlplus sys/orcl@orcl as sysdba oracle的sqlldr的使用总结 part i一：sql loader 的特点 oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。比如说exp和imp可以对数据库中的数据进行导出和导出的工作，是一种很好的数据库备份和恢复的工具，因此主要用在数据库的热备份和恢复方面。有着速度快，使用简单，快捷的优点；同时也有一些缺点，比如在不同版本数据库之间的导出、导入的过程之中，总会出现这样或者那样的问题，这个也许是oracle 公司自己产品的兼容性的问题吧。 sql loader 工具却没有这方面的问题，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢，另外对blob 等类型的数据就有点麻烦了。 二：sql loader 的帮助 C:\>sqlldr SQL*Loader: Release 9.2.0.1.0 - Production on 星期六10月9 14:48:12 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 用法: SQLLDR keyword=value [,keyword=value,...] 有效的关键字: userid -- ORACLE username/password control -- Control file name log -- Log file name bad -- Bad file name data -- Data file name discard -- Discard file name discardmax -- Number of discards to allow (全部默认) skip -- Number of logical records to skip (默认0) load -- Number of logical records to load (全部默认) errors -- Number of errors to allow (默认50) rows -- Number of rows in conventional path bind array or between direct p ath data saves （默认: 常规路径64, 所有直接路径） bindsize -- Size of conventional path bind array in bytes(默认256000) silent -- Suppress messages during run (header,feedback,errors,discards,part itions) direct -- use direct path (默认FALSE) parfile -- parameter file: name of file that contains parameter specification s parallel -- do parallel load (默认FALSE) file -- File to allocate extents from skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默 认FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus 基础知识 表3.2 Oracle数据类型 表3.3 XSB的表结构 操作表 创建表 CREATE TABLE [schema.] table_name ( column_namedatatype [DEFAULT expression] [column_constraint][,…n] oracle体系结构总结 传统上Oracle体系结构由内存结构、进程结构、存储结构组成。其中，内存结构由SGA、PGA组成；进程结构由用户进程和Oracle进程组成；存储结构由逻辑存储、物理存储组成。 通过数据库的启动过程和一条sql的执行过程可以将oracle的内存结构、进程结构、存储结构串起来。 一、内存结构 (1)SGA(System Global Area):一个包含实例数据和控制信息的共享内存区域 (2)PGA(Program Global Area):一个包含服务器进程和后台进程的数据和控制信息的共享内存区域 1.1系统全局区SGA 系统全局区，是DB Server 中实例的必要组成部分，由很多小内存区共同构成，各个小内存区存放不同的信息，系统全局区用于暂存可以被所有进程（包括server process 以及background process）共享的信息，对系统的运行性能有好处，SGA 在实例启动的时候自动分配，实例关闭时自动释放。 SGA 暂存系统的大量共享数据，对系统有相当的性能影响，所以需要为SGA 选择适当的管理方式，根据所使用的数据库版本不同，SGA 的管理有三种方式：1）8i:SGA 的大小由所有内存组件大小之和决定，不能直接定义SGA 大小，对内存组件的大小可后台进行修改，但只能通过直接修改参数文件的内存参数大小，而且修改完数据库必须重起后才能生效，所以这种内存管理方法叫做SGA 的静态管理。 2）9i:SGA 的大小由初始化参数SGA_MAX_SIZ E 确定，各个内存组件大小之和不能超过这个参数。可以直接通过命令进行修改内存组件大小而不用重启，这种叫做SGA 的动态管理 声明：此文档只作为学习参考，不得用作它途！ 实验一了解ORACLE环境，使用ORACLE数据库实用工具 1.目的要求： 了解ORACLE数据库的各个常用工具软件 2.实验内容： 在ORACEL数据库下使用SQL*PLUS ，SQL*PLUS Worksheet，PL/SQL Developer 工具，企业管理器等实用工具与Oracle交互。并在企业管理器中观察ORACLE的底层存储原理。在PL/SQL Developer中书写简单的SQL语言。 3.主要仪器设备及软件 1）PC 2）ORACLE数据库 实验二熟悉SQL语言 1.目的要求 在SQL*PLUS或PL/SQL Developer工具中编写SQL语句 2.实验内容 在ORACLE 数据库中定义用户，给用户赋权限，创建，修改和删除表格，视图等数据库对象，并向表格中插入，修改和删除数据。体会SQL语言中ORACLE的“方言”。 对自己建立的表做查询：包括单表查询，多表查询，嵌套查询，分组查询，相关查询 3.主要仪器设备及软件 1）PC 2）ORACLE数据库 自定义用户：create user taozi identified by taozi; 给用户赋DBA权限：grant dba to taozi; 创建表格 student,sc,course: Create table student (sno char(10) primary key, sname varchar(20) not null, sage smallint, ssex char(2), sdept varchar(20)); Create table course (cno char(10) primary key, cname varchar(50) not null, credit smallint); Create table sc (sno char(10), cno char(10), grade smallint, primary key(sno,cno)); 创建视图：create view oracle as (select sno,sname,sage from student); 删除视图：delete oracle; 为student 表增加一列 jiguan: alter table student add jiguan varchar(10); 删除jiguan 列：alter table student drop column jiguan; 删除student 表结构：drop table student; 插入数据：insert into student values('004','AA','21','f','MA'); insert into student values('005','BB','20','m','CS'); 高级查询总结 A．层次查询：start with……connec by prior…….. select lpad(' ',3*level)||ename,job,level from emp start with mgr is null connect by prior empno=mgr; 从根结点向下查，lpad()为左添加，level为第几层，prior为查找的方向；此句若省掉start with….则表示要遍历整个树型结构；若出现level，则后面一定要跟connect by B.交叉报表(case when then end 与decode()) select ename,case when sal>=1500then sal*1.01 else sal*1.1 end工资 from emp; select姓名, max(case课程when'语文'then分数end) 语文, max(case课程when'数学'then分数end) 数学, max(case课程when'历史'then分数end) 历史 from学生group by姓名;------(交叉报表与函数一起使用) select ename,sum(decode(sal,'sal',comm,null)) 奖金from emp group by ename;--可实现分支 decode(条件,(值),(返回值),…………,(默认值)) 部门 select sal,decode(sign(sal-1500),-1,1.1*sal,0,1.1*sal,1,1.05*sal) from emp; C.连接查询 1．等值： select * from emp,dept where emp.deptno(+)=dept.deptno; ‘+’在左则以右表为主，反之亦然 2.非等值：where的运算符不是等号 3.自然连接: select * from emp natural join dept 4.99乘法: select * from emp e full join dept d using (deptno) where deptno=10; --where必须放在using(on)的后面 D集合查询： 1．A Union B:消除重复行，有all则可以重复,默认第一列升序select ename,sal from deptno=20 union select ename,sal from job=’CLERK’; 2．A intersect B:A与B的交集 select ename,sal from deptno=20 intersect select ename,sal from job=’CLERK’; 3.A minus B:在A中减去既属于A又属于B的一部分 个人声明：本文档所有代码并没有经过上机验证，仅供参考，另外，因为只是自己总结的关于最基本的知识点，所以难免遗漏，所以请参阅相关材料观看，最后，版权所有，翻版没事！哈哈~~~~ 1，关系模型的数据结构非常简单，它只含有单一的数据结构——关系，在用户看来就是一张二维表，关系数据库就是用二维表表示关系以及他们之间的联系。 2.关于数据库的操作主要就是增删改查，其中尤其以查最重要，下面正对这几种数据库操作做一些sql语句方面的简单整理，以用来加强印象： 由于今天使用了mysql数据库，所以呢，先将一些使用的必须基本命令总结一些： 首先是安装了mysql数据库之后，进入dos命令提示窗口， 接下来我们要进入我们的数据库，那么要先进入mysql的bin路径下， 然后输入mysql -u root -p，然后会提示输入密码，然后进入， 进入后的命令提示符是mysql> 在mysql新建表之间和使用oracle 10g还是不一样的，先新建数据库， Create database 数据库名字; 增 A.首先是新建表：create table table_name( 实验八存储过程的使用 一、实验目的 1、熟练掌握存储过程的定义及使用 二、实验要求 1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成 实验内容的预习准备工作； 2、能认真独立完成实验内容； 3、实验后做好实验总结，根据实验情况完成实验报告。 三、实验内容 创建图书管理库的图书、读者和借阅三个基本表的表结构： 图书表： BOOK ( BOOK_ID NUMBER(10), SORT V ARCHAR2(10), BOOK_NAME V ARCHAR2(50), WRITER V ARCHAR2(10), OUTPUT V ARCHAR2(50), PRICE NUMBER(3)); 读者表 READER ( READER_ID NUMBER(3), COMPANY V ARCHAR2(10), NAME V ARCHAR2(10), SEX V ARCHAR2(2), GRADE V ARCHAR2(10), ADDR V ARCHAR2(50)); 借阅表 BORROW ( READER_ID NUMBER(3), BOOK_ID NUMBER(10), BORROW_DA TE DA TE); 插入数据: BOOK表: insert into book values(445501,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(445502,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(445503,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(332211,'TP5/10','计算机基础','李伟','高等教育出版社',18.00); insert into book values(112266,'TP3/12','FoxBASE','张三','电子工业出版社',23.60); insert into book values(665544,'TS7/21','高等数学','刘明','高等教育出版社',20.00); insert into book values(114455,'TR9/12','线性代数','孙业','北京大学出版社',20.80); insert into book values(113388,'TR7/90','大学英语','胡玲','清华大学出版社',12.50); insert into book values(446601,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(446602,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(446603,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(449901,'TP4/14','FoxPro大全','周虹','科学出版社',32.70); insert into book values(449902,'TP4/14','FoxPro大全','周虹','科学出版社',32.70); insert into book values(118801,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80); insert into book values(118802,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80); READER表: insert into reader values(111,'信息系','王维利','女','教授','1号楼424'); insert into reader values(112,'财会系','李立','男','副教授','2号楼316'); insert into reader values(113,'经济系','张三','男','讲师','3号楼105'); insert into reader values(114,'信息系','周华发','男','讲师','1号楼316'); insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224'); insert into reader values(116,'信息系','李明','男','副教授','1号楼318'); insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214'); insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216'); insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318'); insert into reader values(120,'国际贸易','李雪','男','副教授','4号楼506'); insert into reader values(121,'国际贸易','李爽','女','讲师','4号楼510'); insert into reader values(122,'国际贸易','王纯','女','讲师','4号楼512'); insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202'); insert into reader values(124,'财会系','朱海','男','讲师','2号楼210'); insert into reader values(125,'财会系','马英明','男','副教授','2号楼212'); BORROW表:Oracle中分析函数用法小结
oracle复习大纲(1)
Oracle实验报告
oracle视图总结
Oracle数据库基本知识点
oracle湘潭大学数据库数据查询实验报告
学习oracle数据库的总结(图文整理)
Oracle复习
Oracle实验总结
oracle实验报告总结
oracle的sqlldr的使用总结
Oracle数据库期末复习知识点整理
oracle体系结构总结
四川师范大学《oracle》实验报告
Oracle高级查询总结
关于数据库知识点的再学习和整理
oracle实验--存储过程
相关主题
文本预览