OracleOracleOracle Oracle &&& & SQLSQLSQL SQL 基本介绍: Oracle 公司 也提供应用系统; 我们涉及的是数据库管理系统 DBMS(多用户系统); 数据库操作语言 SQL——结构化查询语言; SQL 操作对象为 DB 中的数据,表现形式为——库和表。 Oracle 用表管理表: 一、Oracle Oracle Oracle Oracle 数据库中常用的数据类型 varchar2(长度) 可变长字符串 char(长度) 定长 number() 表示整数或者浮点数 date 日期类型 二、数据库查询 1、SELECT SELECT SELECT SELECT 语句 从表中提取查询数据.语法为: SELECT[DISTINCT] column1,column2,group_function,… FROM tablename [WHERE conditions] [GROUPBY group_by_expression] [HAVING group_condition] [ORDERBY column [ASC/DESC]]; 默认为 ASC 说明:SELECT 子句用于指定检索数据库的中哪些列,FROM 子句用于指定从哪 一张表或视图中检索数据。 注:distinct 会触发排序 2、WHERE WHERE WHERE WHERE 子句。 WHERE 子句用来选择符合条件的记录。 between ... and ... 表示结果在这之间,between and 是一个闭区间; !=,<>,^=^=^= ^= 这三个都可以表示不等于; Oracle DBDBDB DB selectselectselect select table_nametable_nametable_name table_name fromfromfrom from user_tables;user_tables;user_tables; user_tables; ////// // 查看当前有那些表 descdescdesc desc s_emps_emps_emp s_emp ////// // 查看表结构 以上两条很有用 用户表 系统表((( ( 数据字典))) ) telnettelnettelnet telnet 192.168.0.2192.168.0.2192.168.0.2 192.168.0.2 333 3 通过sqlplus sqlplus sqlplus sqlplus 命令 与数据库建立连接,才可操作 DDLDDLDDL DDL 、DMLDMLDML DML 环境变量: ① PATHPATHPATH PATH 中要加入 sqlplus sqlplus sqlplus sqlplus 所在的目录 ② ORACLE_HOMEORACLE_HOMEORACLE_HOME ORACLE_HOME Oracle Oracle Oracle Oracle 安装目录 ③ ORACLE_SIDORACLE_SIDORACLE_SID ORACLE_SID 表示连接的数据库 ////// // 一定要设置,否则无法连数据库 sqlplussqlplussqlplus sqlplus sd0709sd0709sd0709 sd0709 /// / sd0709sd0709sd0709 sd0709 SQL>SQL>SQL> SQL> 表示这是 SQL SQL SQL SQL 环境 sqlplus sqlplus sqlplus sqlplus 口令 比如命令: SQL>SQL>SQL> SQL> showshowshow show useruseruser user ////// // 看登录的用户名 sqlplus sqlplus sqlplus sqlplus 命令 in (va1,val2,...) 判断结果是否在这个集合中存在; ininin in 等价于:=any=any=any =any 注意顺序会影响执行效率 like '...' 表示字符串通配查询,'%'表示 0或多个字符, '_' 表示一个字符; 注意字符大小写敏感 知道转义的用法:like ‘S\_%’ escape ‘\’ ... and ... 表示只有两个条件同时满足; ... or ... 表示条件只要满足其中之一就可以; all ... 是要求都满足条件; not ..... 可以与以上的条件产生相反的效果; notnotnot not ininin in 等价于: <>all<>all<>all <>all 注意空值的影响,空值与任何值比较结果都为空!!! ! ... is null 用来判断值是否为空。 3、ORDER BY 子句 ORDERBY 子句使得 SQL 在显示查询结果时将各返回行按顺序排列,返回行的 排列顺序由 ORDERBY 子句指定的表达式的值确定。 ASC(默认,升序) DESC(降序) order by 目标列名(别名) 排序顺序(不写则默认为升序) 例:select first_name from s_emp order by first_name; //默认为升序 select first_name from s_emp order by first_name desc; //降序 三、SQL SQL SQL SQL 常用的命令分类及例子 数据定义语言<操作表的结构>: DDLDDLDDL DDL 数据操纵语言<操作表的数据>: DMLDMLDML DML 事务控制语言: TCLTCLTCL TCL 数据控制语言:grant(授予)和 revoke(回收)。与权限有关 DCL 1、数据定义语言 DDL DDL DDL DDL 举例: SQL> create table myTab(no number(4),name varchar2(20)); //创建一个名为 myTab 的表,包含两列分别为 no 和name; SQL> alter table myTab modify (name varchar2(25)); //修改 myTab 中的 name 列 , 使此列能容纳 25 个字符; SQL> alter table myTab add (tel_no varchar2(20)); //给表 myTab 增加一列 tel_no; SQL> alter table myTab drop column tel_no; //删除表 myTab 的tel_no 列; SQL> drop table myTab; //删除表 myTab; create( 创 建 ) alter(更改) drop(删除) insert(插入) select(选择) delete(删除) update(更 新 ) 命令commit (提交) savepoint(保存点) rollback(回滚) 必须要掌握,使用频 度高! 记住,事务处处流淌 在我们的应用中! SQL> truncate table myTab; //删除表 myTab 中的所有行(截断表),注意:此操作 不可以 rollback。 2、数据操纵语言 DML DML DML DML 举例: SQL> insert into myTab values(‘001’,’John’);//向表 myTab 中插入一行数据 ; SQL> select distinct salary from s_emp where salary>1500 order by salary desc; //选 择表中 salary 大于 1500 的数据,按照 salary 的降序进行排列输出; SQL> create table empa as select id,first_name,salary from s_emp; //从s_emp 表中选择“first_name,salary”的数据建立新表 empa; SQL> create table empa as select * from s_emp where salary>1200; //使用一个条件根据现有表 s_emp 创建一个表 empa; SQL> delete from empa where salary<1500; //删除表 empa 中salary 小于 1500 的行; SQL> update empa set salary=1500 where salary<1500; //更新,将表 empa 中salary 小于 1500 的记录的 salary 值全部改为 1500。 3、事务控制语言 TCL TCL TCL TCL 举例: SQL> commit; //用于提交并结束事务处理; SQL> savepoint mark1; //保存点类似于标记,用来标记事务中可以应用回滚的点; SQL> rollback to savepoint mark1; //回滚到保存点 mark1。 四、Oracle Oracle Oracle Oracle 数据库函数 注意:dual 表(虚表)是专门用于函数测试和运算的. 单行函数 1、字符函数 字符是大小写敏感的 转小写 lower(字段名) 转大写 upper(字段名) 首字母大写 initcap(字段名) 字符串拼接 concat(字段 1, 字段 2) 截取子串 substr(字段名, 起始位置,取字符个数) 字符串长度 length(…) 忽略空值 nvl(…) 例: select first_name,substr(first_name,2,2) sub from s_emp;(从名字的第二个字符 开始取两个字符) select first_name,substr(first_name,-2,2) sub from s_emp;(从名字的倒数第二个 字符开始取两个字符) 2、数值函数 四舍五入函数 round(数据,保留到小数点后几位) 1表示保留到小数点后一位,-1 表示保留到小数点前一位。 例:select round(15.36,1) from dual; 截取数值函数 trunc(数据,保留到小数点后几位) 例:select trunc(123.456,1) from dual; 截取到小数点后一位,注意:与round 函数不同,不会四舍五入。 3、日期函数 缺省日期格式:日-月-年 dd-mon-rrdd-mon-rrdd-mon-rr dd-mon-rr 修改当前会话的日期格式,会按照指定的格式输出日期 alter session set nls_date_format='yyyy mm dd hh24:mi:ss'; 返回当前日期 sysdatesysdatesysdate sysdate 世纪、年、月、日、时、分、秒 例:select sysdate from dual; 另外,date 类型可以运算: select sysdate-1,sysdate,sysdate+1 from dual; 单位为 1天 select sysdate,sysdate+1/144 from dual; 加10 分钟 两个日期相减表示 相隔多少天。 使用函数: select trunc(add_months(sysdate,1),’MONTH’) from dual; //取下个月首天 4、不同数据类型间转换函数 将日期转成特定格式字符串 tochar(date,'日期格式') 日期格式要用有效格式,格式大小写敏感 'yyyy mm dd hh24:mi:ss'(标准日期 格式),'year'(年的全拼),'month'(月的全拼),'day'(星期的全拼),'ddspth' (日期的全 拼) 例:select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual; select to_char(sysdate,'year month day ddspth')from dual; //大小写敏感 select to_char(sysdate,'YEAR MONTHDAY DDSPTH')from dual; 将字符串转成日期 to_date('...','日期格式') 例:select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual; 五、表连接(关联查询) 等值连接 select table1.column1,table2.column2 from table1 t1,table2 t2 where t1.column3=t2.column4; 表连接时,当表与表之间有同名字段时,可以加上表名或表的别名,加以区分 , 使用时要用表名.字段名或表别名.字段名(列名)。当表的字段名是唯一时,可以 不用加上表名或表的别名。 注意:当为表起了别名,就不能再使用表名.字段名了。 例如:select e.first_name||’’||e.last_name name, d.name from s_emp e, s_dept d where e.dept_id=d.id; 非等值连接 select [表别名 1.字段名 1],[表别名 2.字段名 2],... from 表1 表别名 1 ,表 2 表别名 2 where 表别名 1.字段名 3 ..... 表别名 2.字段名 4 ....可以使比较运算符,也可以使其他的除了'='的运算符 例:select first_name, salary from s_emp where salary between 1000 and 2000; 自连接 把一个表的两个字段关系转换成两个表字段之间的关系. select [表别名 1.字段名 1],[表别名 2.字段名 2],... from 表1 表别名 1 ,表 1 表别名 2 where 表别名 1.字段名 3=表别名 2.字段名 4; 例:select a.first_name ename,b.first_name cname from s_emp a,s_emp b where a.manager_id=b.id; 以上三种连接为内连接,会严格匹配! 外连接 使用一张表中的所有记录去和另一张表中的记录按条件匹配(空值也会匹配),这 个表中的所有记录都会显示。 ***要记录一个都不能少,则在对方的那一边加(+) 等价于 结果为:所有员工及对应部门的记录,包括没有对应部门编号 dept_id 的员工记 录。 2. RIGHT OUTER JOIN:右外连接 结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。 1. LEFT OUTER JOIN:左外连接 select e.last_name, e.dept_id, d.name from s_emp e left outer join s_dept d on (e.dept_id = d.id); select e.last_name, e.dept_id, d.name from s_emp e, s_dept d where e.dept_id=d.id(+); select e.last_name, d.name from s_emp e right outer join s_dept d on (e.dept_id = d.id); select e.last_name,d.name from s_emp e, s_dept d where e.dept_id(+)=d.id; 在加(+)的这一边模拟 数据与之匹配。 掌握哪个部门没有员工的 写法,用外连接。 selectselectselect select e.deptno,d.deptnoe.deptno,d.deptnoe.deptno,d.deptno e.deptno,d.deptno fromfromfrom from empempemp emp e,depte,depte,dept e,dept ddd d wherewherewhere where e.deptno(+)=d.deptnoe.deptno(+)=d.deptnoe.deptno(+)=d.deptno e.deptno(+)=d.deptno andandand and e.deptnoe.deptnoe.deptno e.deptno isisis is null;null;null; null; 3. FULL OUTER JOIN:全外关联 结果为:所有员工及对应部门的记录,包括没有对应部门编号 department_id 的员工记录和没有任何员工的部门记录。 六、组函数 groupgroupgroup group bybyby by 把 select 查询的结果集分成几个小组,这个 group by 子句可以跟 在 select 语句后面或是 having 前面。group by 子句也会触发排序操作,会按分 组字段排序。 select [组函数或分组的字段名]... from 表名 group by [字段名 1],[字段名 2],.....; 例:select avg(salary) from s_emp group by dept_id; 注意:① 组函数会忽略空值,但是 count(*)除外,他会把空记录也统计在内。 ② avg 和sum 这两个函数的参数只能是 number 型的。 ③ count、max、min 可以使用任意类型做参数。 ④ max(..), min(..)求最大值和最小值, ⑤ count(*)统计表中记录数。 例:selectselectselect select max(b.name),max(b.name),max(b.name), max(b.name), avg(a.salary),avg(a.salary),avg(a.salary), avg(a.salary), max(c.name)max(c.name)max(c.name) max(c.name) fromfromfrom from s_emps_emps_emp s_emp a,a,a, a, s_depts_depts_dept s_dept b,b,b, b, s_regions_regions_region s_region ccc c wherewherewhere where a.dept_id=b.ida.dept_id=b.ida.dept_id=b.id a.dept_id=b.id andandand and b.region_id=c.idb.region_id=c.idb.region_id=c.id b.region_id=c.id groupgroupgroup group bybyby by b.id;b.id;b.id; b.id; 注意:只要写了group by 子句,select 后就只能用 groupgroupgroup group bybyby by 之后的字段或者是组 函数。having 子句可以过滤组函数结果或是分组的信息,并且写在group by 子句 后。 七、子查询 可以嵌在 sql 语句中的 select 语句。 在select 语句中嵌套子查询时,会先执行子查询,返回结果再执行主查询。一般 的会将子查询放在运算符的右边。 注意:在使用子查询时,要注意这个运算符是单行的(也就是只能是单值)比如 =要确认返回的是单行;还是多行运算符(范围,多值.比如 ininin in )。配合使用子查 询返回的结果必须符合运算符的用法。 例: select first_name||' '||last_name name // 用 |||||| || 表示字符串拼接 from s_emp where title in (select title from s_emp where dept_id=42); 查询和 42 部门员工职位相同的所有员工的姓名. 在select 后加子查询: 谁的工资比本部门的平均工资高? selectselectselect select e.first_name,e.first_name,e.first_name, e.first_name, e.salary,e.salary,e.salary, e.salary, a.avgsala.avgsala.avgsal a.avgsal select e.dept_id,d.id from s_emp e full outer join s_dept d on (e.dept_id = d.id); fromfromfrom from s_emps_emps_emp s_emp e,e,e, e, (select(select(select (select dept_id,avg(salary)dept_id,avg(salary)dept_id,avg(salary) dept_id,avg(salary) avgsalavgsalavgsal avgsal fromfromfrom from s_emps_emps_emp s_emp groupgroupgroup group bybyby by dept_id)dept_id)dept_id) dept_id) aaa a wherewherewhere where e.dept_ide.dept_ide.dept_id e.dept_id === = a.dept_ida.dept_ida.dept_id a.dept_id andandand and e.salarye.salarye.salary e.salary >>> > a.avgsala.avgsala.avgsal a.avgsal 八、约束 针对表中的字段进行定义的。 111 1 、primaryprimaryprimary primary keykeykey key (主键约束 PKPKPK PK )保证实体的完整性,保证记录的唯一。 主键约束,唯一且非空,并且每一个表中只能有一个主键,有两个字段联合作为 主键时,将两个字段组合在一起唯一标识记录,叫做联合主键。 主键约束的定义: 第一种定义形式: create table test(c number primary key ); 列级约束 第二种定义形式: create table test(c number , primary key(c) ); 表级约束 create table test(c1 number constraints pk_c1 primary key ); 此约束有名字: pk_c1 create table test(c number , c1 number , primary key (c ,c1) ); 用表级约束可以 实现联合主键 222 2 、foreignforeignforeign foreign keykeykey key (外键约束 FKFKFK FK )保证引用的完整性,外键约束,外键的取值是 受另外一张表中的主键或唯一值的约束,不能够取其他值,只能够引用主键或唯 一键的值,被引用的表,叫做 parent table(父表),引用方的表叫做 child table (子表),要想创建子表,就要先创建父表;记录的插入也是如此,先父表后子 表;删除记录,要先删除子表记录,后删除父表记录;要修改记录,如果要修改 父表的记录要保证没有被子表引用。要删表时,要先删子表,后删除父表。(可 以通过使用 cascade constraints 选项来删除父表) carete table parent(c1 number primary key ); 列级约束 create table child (c number primary key , c2 number references parent(c1)); 或表级约束定义: create table child( c number primary key , c2 number , foreign key(c2) references parent(c1)); 333 3 、notnotnot not nullnullnull null (非空约束 NNNNNN NN )这是一个列级约束,在建表时,在数据类型的后面加上 not null ,也就是在插入时不允许插入空值。 例:create table student(id number primary key,name varchar2(32) not null,address varchar2(32)); 444 4 、uniqueuniqueunique unique (唯一约束 UKUKUK UK ) 唯一约束,允许为空,要求插入的记录中的值是唯一的。 例:create table student(id number,name varchar2(32),address varchar2(32),primary key (id),unique (address)); check 约束 检查约束,可以按照指定条件,检查记录的插入。check 中不能使用伪列,不能 使用函数,不能引用其他字段。 例:create table sal (a1 number , check(a1>1000));. 九、事务 TransactionTransactionTransaction Transaction 原子操作,也就是不可分割的操作,必须一起成功一起失败。 事务的结束动作就是 commit;DDL, DCL 语句执行会自动提交 commit。 sqlplus 正常退出是会做提交动作的 commit;,当系统异常退出时,会执行回滚操 作rollback;。 一个没有结束的事务,叫做活动的事务 (active transaction),活动的事务中修改的 数据时,只有本会话(session)才能看见。 十、Oracle Oracle Oracle Oracle 中的伪列 伪列就像 Oracle 中的一个表列,但实际上它并未存储在表中。伪列可以从表中 查询,但是不能插入、更新或删除它们的值。常用的伪列:rowid 和rownum。 rowid:数据库中的每一行都有一个行地址,rowid 伪列返回该行地址。可以使用 rowid 值来定位表中的一行。通常情况下,rowid 值可以唯一地标识数据库中的 一行。 rowid 伪列有以下重要用途: 1)能以最快的方式访问表中的一行; 2)能显示表的行是如何存储的。 3)可以作为表中行的唯一标识。 如:SQL> select rowid, name from s_dept; rownum:对于一个查询返回的每一行,rownum 伪列返回一个数值代表的次序。 rownum 伪列特点: 1) 有个特点要么等于 1 要么小于某个值或小于等于某个值,不能直接等于某 个值, 不能大于某个值。 2)常用于分页显示。 返回的第一行的 rownum 值为 1,第二行的 rownum 值为 2,依此类推。通过使 用rownum 伪列,用户可以限制查询返回的行数。 如:SQL>select * from s_emp where rownum<11; 从s_emp 表中提取 10 条 记 录 。 如:select rn,c1 from(select rownum rn, c1 from test where rownum <= 5) where rn between 3 and 5; //提取第 3条到第 5条的记录 如:找出第二名 select * from (select rownum rn , a.* from (select max(s.name),sum(grade) sg from stu s,grade g where s.id=g.sid group by s.id order by sg desc ) a) where rn=2 十一、序列(sequence)(sequence)(sequence) (sequence) : create sequence 序列名; (不带参数时默认为从 1 开始每次递增 1,oracle 中为了提高产生序列的效率一 般一次性产生 20 个序列放入当前会话的序列池中备用以加快效率) sequence 的参数: increment by n 递增量 start with n 起始值 maxvalue n 最大值 minvalue n 最小值 cycle|no cycle 循环 cache n 缓存(第一次取时会一次取多少个 id存起来) 查看 sequence 视图: desc user_sequences ; select sequence_name , cache_size , last_number from user_sequences; currval 当前的序列数 nextval 下一个序列数,它会自动给当前的序列加一个步长值。 drop sequence 序列名;//删除序列 sequence SQL 脚本 存成文件 vi crtsql.sql 用sqlplus 去运行脚本 @crtsql.sql 去运行 运行脚本的两种方式: sun-server% sqlplus sd0709/sd709 @crtsql.sql@crtsql.sql@crtsql.sql @crtsql.sql //注意有空格 SQL>@crtsql.sql@crtsql.sql@crtsql.sql @crtsql.sql //或者这样也可运行脚本 建议大家用 SQL 脚本来写程序 (一定掌握) edit edit edit edit 命令 来编辑 SQL SQL SQL SQL 语句 !pwd!pwd!pwd !pwd 在 /oracledata/oracle/product/9.2.0/bin /oracledata/oracle/product/9.2.0/bin /oracledata/oracle/product/9.2.0/bin /oracledata/oracle/product/9.2.0/bin 下 afiedt.bufafiedt.bufafiedt.buf afiedt.buf 缓存原来写的语句 l 1 定位在哪一行,再修改
还剩9页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

疯中静思

贡献于2012-06-05

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf