数据库实习报告

1、数据库基础知识 数据库有关系数据库，层次数据库，文档数据库等。（我们主要用的是关系数据库）关于数据库相关术语的定义： 数据：人类可以感知的信息，可以在计算机系统记录下来的信息。 数据库：一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。 数据库管理系统(database management system)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称dbms。 数据库系统DBS（Data Base System，简称DBS）通常由软件、数据库和数据管理员组成。其软件主要包括操作系统、各种宿主语言、实用程序以及数据库管理系统。数据库由数据库管理系统统一管理，

2、数据的插入、修改和检索均要通过数据库管理系统进行。数据管理员负责创建、监控和维护整个数据库，使数据能被任何有权使用的人有效使用。1、目前用到的数据库： 大型数据库：oracle（多用于企业），db2，informics，sybase，terradata（BI,仓库）。 小型数据库：SQLServer，mysql。 其中常用的数据库有： oracle，db2，terradata。 下面就oracle数据库来说：2、Oracle的作用： 性能调整，分布式数据库，集群,备份数据库,服务管理，数据管理，管理多数据库，数据仓库。3、开发工具： PL/SQL（基于C/S，常用），ISQL*PLUS（基于w

3、eb），SQL*PLUS （基于C/S） 。下面介绍一下如何操作这几个工具。 1、isqlplusctl start启动服务（cmd模式下输入）2、进入网页http:/主机名(IP地址)：5560/isqlplusa、isqlplus（基于web） 3、输入用户名和密码，连接标识符为：orcl，点击登陆。输入SQL语句看看能不能执行，如果不能执行，可以把前面那个网页中主机号改为localhost试试。b、SQLPLUS（基于c/s) 进入cmd，查ip的指令:ipconfig, 登录用户：sqlplus 用户/口令。 Sqlplus如果没有执行rollback或commit（即非正常关闭，服务

4、器会直接提交）下面是一些在SQL中操作的指令：select table_name from user_tables;(查看当前用户所有表的表名） Select * from tab ; (查看当前用户的所有表）Desc +表名（例：user_tables）(列出当前用户该表的信息) Show user (查看当前用户名）desc 索引名（例：user_indexes） （列出索引信息）以管理员身份登录的两种方式：1.conn as sysdba (需输入用户名和口令）2.conn / as sysdba (直接以dba身份登录）注意：show parameter db_name（查看数据参数）

5、只能是管理员操作，普通用户不可以conn 用户/口令 （切换到该用户）例如当我们是以管理员登录的，此时我们要切换到其它的用户。oracle默认端口（客户与服务器连接的默认端口）：1521关于退出的三个指令：exit，$，quit （其中exit是回到SQL，$/quit是退出到dos。）建立新服务连接：打开ORACLE客户端（ORACLE NET manager）点击那个绿色的加号，创建服务命名输入网络服务名，点击下一步点击下一步输入主机名，点击下一步输入服务名：orcl，点击下一步点击测试显示测试结果测试成功后，保存。进入cmd模式1、sqlplus 用户名/口令+要访问机器的数据库的名称。

6、（直接登录）2、conn 用户名/口令+要访问机器的数据库名1.Test window(测试SQL语句） 2.SQL window（运行sql语句）mand window(命令窗口类似于sqlplus) edit-可用于修改代码；修改保存完后用/，然后回车运行。4.Explain plan window（解析）c、PL/SQL（基于c/s) 关于PL/SQL，后面会具体的运用。SQL语句书写原则：1.关键字不区分大小写。2.列名和对象名（数据库表，存储过程等）不区分大小写。3.字符值与日期值区分大小写。 4.可以使用缩进格式书写。（ORACLE结束的语句有分号, DB2结束的语句没有分号。）事

7、务： 事务就是一组DML操作或者Selectfor update操作（防止查询的数据在未修改前就发生了改变），要么一次性提交，要么回滚。 只要数据库有变化就会启动事务。select一般不会启动事务。除了select from where for update;这种情况下数据库会启动事务。 在数据库有变化时，最好用commit提交完成（由于事务的隔离性，在同一事务中可以查询出来已插入结果，而在另一事务中则不能查询出来。）SQL语言分类： 2.DML 操作语言（update，delete，insert） 3.事务控制 （commit，rollback，savepoint） savepoint：保存

8、点 4.DDL 定义语言（create，drop，alter） 5.DCL 控制语言（grant，revoke授权与回收权限语句） 1.Select检索数据： a.列出表的结构:desc 表名; b.查询表中所有的列:select * from emp; （每次调用这句话都要重新编译，计算机内存有限，不存在 这样的句柄。而SQL语句调用内存次数频繁，会导致内存的 浪费。最好写列名，不要用*） c.查询特定的列：select 列名 from 表名（列名中间用逗 号隔开） 例：select deptno from dept；当我们查询到的数据有重复行的时候，可以在select后面加上distinc

9、t。select distinct deptno,job from emp;- distinct消除重复的行select ename,sal*12 as“年薪”from emp; as定义一个临时别名 nvl(字段,值)当该字符段所对应的值为空值时，则将值赋给该字段。注意：该字段跟值的数据类型必须匹配。select ename,sal,nvl(comm,0),sal+nvl(comm,0) from emp;连接串 (|) 可连接多个select ename|AS IS |job,sal,nvl(comm,0) from emp;between . and .(作用范围包含边界)select

10、empno,ename,sal from emp where sal between 1100 and 1600;select.from 表 where 列 in（值列表）select empno,ename,sal from emp where sal in (1250);匹配符：like。下划线只能匹配一个，%可以匹配多个（0n。例如：%s%：只要该字符串中含有s就可以匹配成功了。 -s-：第三个字符为s，并且该字符串的长度为5。 select * from dept where dname like %C%;select * from dept where dname like _C%;

11、Is null 与 is not null方便查询字段是否为空的记录行。select empno,ename,sal,comm from emp where comm is null;2.排序（order by） 默认：asc（升序），desc（降序）。 a、select.from.order by 列名表。 select empno,ename,job,nvl(comm,0),(sal*12) as 年薪 from emp order by job desc,ename desc; b、select.from.order by 字段号n（数值）。 按select列表的第n个字段排序。 sel

12、ect empno,ename,job,nvl(comm,0),(sal*12) as 年薪 from emp order by 2;c、select.from.order by 列名表（列名中有空值） 空值在升序中排最后，降序排最前。 select empno,ename,job,comm,(sal*12) as 年薪 from emp order by comm desc;3.insert （添加记录） a、insert into 表名（字段） values （字段值） insert into emp(empno) values (8900);加上commit提交。 select * fr

13、om emp；b、将一个表的数据全部插入到例外一个表中。 1、insert into 表1（字段列表） select （字段列表）from 表2（注意：表1的字段列表跟表2的必须大小、类型一致。） 2、insert /* +append */ into 表1 select * from 表2; （装载数据表2表1,适用大型数据) CREATE TABLE dept1 (DEPTNO NUMBER(2, 0), DNAME VARCHAR2(14), LOC VARCHAR2(13);（可以直接把表导出后，复制就可以了) insert into dept1 select * from dept;

14、（与下句操作结果一样） insert /* +append */ into dept1 select *from dept; select * from dept1; c、插入默认值（default） 该列有默认值的话，插入时插入默认值。如果没有的话，默认为空值。 insert into dept1 values( 60,salse,default); CREATE TABLE dept2 (DEPTNO NUMBER(2, 0), DNAME VARCHAR2(14), LOC varchar(13) default shanghai); insert into dept2 values(

15、60,salse,default); select * from dept2;d、将表拆分插入到其他表(一般是将大型数据表的数据拆分到 多个表中） insert all/first when 条件1 then into 表1 when 条件2 then into 表2 . else into 表n select . 注意first跟all的区别，first可以避免重复插入数据。 insert first/all when deptno=20 then into dept2 when deptno10; Select max(sal) from emp; Select min(sal) from

16、 emp;8、多表 8.1、select 列 from 多表（笛卡尔积） select d.deptno,empno,job from dept d,emp e;(查询到的记录数为（dept的记录数*emp的记录数）8.2、内连接查询(等值，必须满足两个表之间的关系相等)select ename,sal,d.deptno from emp e,dept d where d.deptno in(10,30) and sal1600 and e.deptno=d.deptno;8.3、外连接查询 左外连接-把左边的那个表作为基表，显示左边表的数据，如果右边的表的数据为空，则显示空值。 右外连接-把

17、右边的那个表作为基表，显示右边表的数据，如果左边的表的数据为空，则显示空值。 全外连接左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。 select ename,sal,d.deptno from dept d left join emp e on e.deptno=d.deptno ;select ename,sal,d.deptno from dept d full join emp e on e.deptno=d.deptno ;select ename,sal,d.deptno from dept d right join emp e on e.deptno=d

18、.deptno ;对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：1.（+）操作符只能出现在where子句中，并且不能与 join语法同时使用。2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。3.（+）操作符只适用于列，而不能用在表达式上。4.（+）操作符不能与or和in操作符一起使用。5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。用（+）来实现， 这个+号可以这样来理解：+ 表示补充，即哪个表有加号，这个表就是匹配表，另外那个表就是基表。所以加号写在右表，左表就是全部显示

19、，故是左连接。select ename,sal,d.deptno from dept d ,emp e where d.deptno(+)= e.deptno;（+号在左表，右链接）8.4、case表达式（相当于if then else） case when 条件 then . when 条件 then . . else. end 别名注意：case前面加上逗号。创建字段（别名）8.5、合并查询 Union/union all合并查询的结果集（查询的字段一致，Union可以去掉重复的记录，Union all没有去掉重复的记录）union all包含了重复的记录9、函数 9.1、数字函数 abs

20、(n)-去绝对值函数 Floor(n)-向下取整 Mod(x,y)-取余数 Round(x,m)-四舍五入，小数点后保留m位 Trunc(x,m)-截断函数，小数点后保留m位，直接截断。select abs(-10) 绝对值,floor(2.5) 向下取整,mod(10,4) 求余 from dual; dual速算表 select round(123.4565,3) 四舍五入,trunc(123.4565,3) 截断 from dual;（ 注意round和trunc的差别，round要看看是否进位，而trunc是直接把小数点后3位截断。） 9.2、字符函数 ascii(字母)-字符转换为数

21、字 chr(数值) -数字转换为字符 select ascii(N) 转换为数值,chr(34) 转换为ascii from dual; 连接字符串：concat(串1,串2) -只连接两个字符串 或用|-可连接多个字符串 select concat(ni hao ,ma),ni |hao |ma 可多字符 from dual;initcap(字符串) 将每个单词（单词以空格和符号划分，并且单词的开头是字母）的首字母大写，其余的都变为小写。select initcap(ni hAo ma?) from dual; Instr（字符串1，字符串2，数字n）查找子串，从串1中n位开始找，找到串2在

22、串1的位置，如果没有第三个参数，则从串1的开头位置找起（从1开始，不是0），如果找不到则返回0。select instr(shanghai,an),instr(shanghai,an,5) from dual;Lower()全部转换成小写 ，Upper()-全部转换为大写的select lower(ShangHai),upper(shanghai) from dual; 左填充：lpad(char1,n,char2)-在char1的字符串的左端填充char2字符串，直到字符串的长度达到n，如果字符不够则重复填充char2字符串。如果char1有n位，则只显示char1前n位。 右填充：rpad

23、(char1,n,char2)-在char1的字符串的右端填充char2字符串，直到字符串的长度达到n，如果字符不够则重复填充char2字符串。如果char1有n位，则只显示char1前n位。select lpad(neusoft,10,ab)a,lpad(neusoft,10,abcd) b,lpad(neusoftbcdef,10,ab) c,rpad(neusoft,10,ab) d,rpad(neusoft,10,abcd)e,rpad(neusoftabcde,10,ab) f from dual; Trim(列名)-去字符两头的空格(中间的空格不能去除） Ltrim-去左面的空格

24、rtrim-去右边的空格create table test(name char(20),name2 varchar(20); （创建test表。Varchar与char区别-一个按照实际字符串显示字符数，一个是按照固定字符串显示字符数。varchar是可变的，而char是固定大小的（如果值不够位数，右边补空格）select * from test;insert into test values( li fang , lifang);insert into test values(liming,li yu);commit;select * from test;length（字符串）字符串长度（包

25、含了空格）select length(name) a,length(name2) b,length(trim(name) c,length(trim(name2) d,length(ltrim(name) e,length(ltrim(name2) f,length(rtrim(name) g,length(rtrim(name2) h from test;sysdate系统时间To_char(时间/列名，格式串）To_date（时间，格式串）select to_char(sysdate,yyyy-mm-dd hh24:mm:ss) from dual;select to_date(19910

26、102,yyyy/mm/dd) from dual;update emp set hiredate=(to_date(19830105,yyyy/mm/dd) where empno=8900; select * from emp;l 表(table)l 视图(view)l 索引(index)l 序列(sequence)l 用户(user) 表的种类有很多，例如堆表，序列表等。我们主要介绍的是堆表。关于堆表： 1、表的创建：create or replace table 表名（ 字段名 类型（大小） not null， 其他字段）； 2、类型：date，number（n,m)（m参数可有可无，

27、没有的时 候就是可以存n位的数值。有的话就是数值有n位，其中 包含了小数点后m位，不包含小数点），varchar（可变 长度），char（固定）（若不够位数，右边补空格）， long（用来存放大字符串） 为了提高效率，我们该何时用varchar，何时用char？10.1、表（堆表）char与varchar是一对矛盾的统一体，两者是互补的关系. varchar虽然比char节省空间，但在效率上比char会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的以空间换效率。 varchar虽然比char节省空间，但是如果一个varchar列经常被修改，而且每次被修改的数

28、据的长度不同，这会引起行迁移(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用char代替varchar会更好一些。 约束是在表中定义的用于维护数据库完整性的一些规则. a、主键约束（PRIMARY KEY）：用来定义表的主键.每个表中只能有一个PRIMARY KEY约束；定义为PRIMARY KEY的列不能有重复值且不能为空值NULL。 b、非空约束（NOT NULL）：定义字段中不能包含NULL值，只能在列级定义NOT NULL约束，在同一个表中可以定义多个NOT NULL约束. c、唯一约束（UNIQUE）：定义了UNIQUE约束的

29、列中不能有重复值；可以为一列定义UNIQUE约束，也可以为多个列组合定义UNIQUE约束；对同一个列可以同时定义UNIQUE约束和NOT NULL约束. d、外键约束（FOREIGN KEY）：定义为FOREIGN KEY约束的列称为外键列，被FOREIGN KEY约束引用的列称为引用列.包含外键列的表称为子表，包含引用列的表称为父表.外键列与引用列必须类型一致，名称可以相同也可以不同.FOREIGN KEY约束列的值只能是对应的父表中引用列的值或者NULL值。格式：foreignkey (列名) references 父表(对应的列名) e、check（检查）约束:定义为check约束的列中

30、的值必须满足check的条件。格式：check（条件） 创建一个表（emp1）（包含了五个约束）cascade级联插入一个记录行关于违反约束的例子： a、主键约束 b、唯一约束 c、外键约束（dept中没有部门号50）d、非空约束（job不能为空）e、检查约束（sal字段必须大于0）10.2、视图（视图不能提高性能） 定义：计算机数据库中的视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。 作用：安全，简洁，使用简单。 创建视图的格式：cr

31、eate view 视图名 （字段1） as select 字段2 from 表；（字段1中的字段可以与字段2中字段名称不同） 视图是一句SQL语句，是一条编译好的语句并存放在数据库的对象中，调用视图的时候直接在内存中调用，不用重新编译。（但必须是完全相同的语句）在oracle中完全相同语句需要满足的条件：开始位置一样空格位置和个数一样字母个数、大小、位置一样创建一个视图（注意：一个普通的用户一般没有创建视图的权限）的命令如下： 要创建视图，我们先以管理员身份登录，给用户名授以创建视图的权限。 以dba形式登录数据库，conn /as sysdba; 给用户授权grant create any view to 用户名; 连接用户conn 用户名/口令 创建视图 ：create view test_viewempno) as select empno from emp1;10.3、索引 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引的优点： 1.大大加快数据的检索速度; 2.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时