ORACLE.docx

1、ORACLEORACLE1. 用sqlplusw2. 主机字符串：表示选择数据库的名称，如果不输，表示默认的数据库服务。3. 可用命令：a) Set linesize 300;设行的长度为300b) Set pagesize 50;每页显示50条c) 删除数据库用户：drop user USERNAME cascaded) Ed 文件名 用记事本进行编辑e) 文件名 的方式执行命令，也可以通过找到磁盘上的文件，执行的时候要指定文件的路径：d:demo.txt，也可以不要，如果后缀是.sql可以不要输入后缀。f) 在sqlplusw也可以使用其他用户连接。如：sys或system：conn 用户

2、名/密码as sysdba|sysoper，sys是超级管理员，必须加上as sysdba等。g) 在sys下，如果想访问其他用户下的表的话，则必须加上用户名 如：select * from scott.emp;h) Show user，显示当前正在连接的用户是什么。i) Select * from tab; 可以得到这个用户下所有的表。j) Desc 表名称; 可以查看这个表的结构。其中number(7,2)表示：小数位有2位，整数位有5位，共7位。k) / 表示重复执行上一行命令。4. SQL语句a) 首先，了解scott用户下的表i. Emp(雇员表)雇员表(emp)No字段类型描述1E

3、MPNONUMBER(4)雇员编号2ENAMEVARCHAR2(10)雇员名称3JOBVARCHAR2(9)工作职位4MGRNUMBER(4)雇员的领导编号5HIREDATEDATE雇佣日期6SALNUMBER(7,2)表示工资7COMMNUMBER(7,2)表示奖金，或者称为佣金8DEPTNONUMBER(2)部门编号ii. 部门表(dept)iii. 工资等级表(salgrade)，用此表表示一个工资的等级。iv. 奖金表(bonus)，来用表示雇员的工资和奖金。b) SQL(Structured Query Language 结构查询语句)。i. DML：Data Manipulatio

4、n Language，数据库操作语言。用于检索或者修改数据。ii. DDL：Data Definition Language，数据定义语言。用于定义数据的结构，如创建、修改、或者删除数据库对象。iii. DCL：Data Control Language，数据控制语言。用于定义数据库用户的权限。c) select 语句i. 起别名：select empno 职员编号 from emp;ii. 去重复项：distinct。Select distinct * from emp;但，在消除重复列的时候，必须保证所有列都重复才能消除（即两条数据是守全相同的，包括主键）。iii. 要实现：“编号是736

5、9的雇员，姓名是：SMITH，工作是：CLERK”要使用“|”表示，如果要加入一些显示信息的话，所有的其他的固定的信息要使用“”括起来。如：select 编号是：| empno | 的雇员，姓名是： | ename | ，工作是： | job from emp;结果为：编号是：7369的雇员，姓名是：SMITH，工作是：CLERK。iv. 在查询中也可以使用四则运算功能，例如：要求出雇员的姓名及年薪。如：select ename, sal * 12 income from emp;程序中可以支持“+、-、*、/”的语句，要有优先顺序，先乘除后加减。d) 限定查询（where语句）i. Sele

6、ct distinct * | 具体的列 别名 from 表名 where 条件ii. 例如：工资2000，select * from emp where sal 2000;iii. 空的表示：IS NULL。不为空的表示：IS NOT NULL。iv. NOT ,AND ,OR, BETWEEN AND的用法v. 例：查询1981年雇佣的雇员1981年1月1日1981年12月31日之间雇佣的雇员日期表示时要加入“”。select * from emp where hiredate between 1 -1月 -81 and 31 -12月 -81;vi. Oracle中是对大小写敏感的，所以

7、在查询中要注意大小写。如：查姓名为：SMITH的雇员。Select * from emp where ename=smith;结果没有数据。Select * from emp where ename=SMITH;即可查出。vii. IN操作符，语法：字段 IN（值1，值2，值n）;select * from emp where empno in (7369, 7499, 7521);可用NOT IN取反。IN操作符不光可以用在数字上，也可以用在字符串的信息上。如：要求查询出姓名是SMITH、ALLEN、KING的信息。select * from emp where ename in (SMIT

8、H,ALLEN,KING);viii. LIKE通配符：%：可以匹配任意长度的内容。_：可以匹配一个长度的内容。查询出姓名第二位字母为“M”的雇员信息：select * from emp where ename like _M%;姓名中有“M”的雇员信息：select * from emp where ename like %M%;如果在使用LIKE的时候没有指定关键字，则表示查询全部。select * from emp where ename like %;查询出了所有记录。ix. ,=,=,=等计算的符号。不等于符号：“”、“!=”两种表示方法。e) 对结果进行排序（order by子句）

9、i. ASC|DESC（升序|降序）默认升序ii. select * from emp order by sal; iii. 当有两个排序条件时，先按前一个，若前一个条件里的数据相等，再按第二个。select * from emp order by sal desc, hiredate;这里就是当工资相等时，按雇佣日期排序。f) 单行函数数据库系统中，每个数据库之间最大的区别点就在于函数的支持上，使用函数可以完成一系列的操作功能。i. 语法：function_name(column|expression,arg1,arg2,)(一) function_name:函数名称(二) column:数

10、据库列名(三) expression:字符串或计算表达式(四) arg1,arg2:函数中使用参数单行函数分类： 字符函数：接受字符输入并且返回字符或数值是专门处理字符的，可将大写转成小写，还能求出字符长度。1. 将小写转换为大写：select upper(smith) from dual（dual是什么？）;2. SELECT * FROM emp WHERE ename = UPPER(smith);3. 转小写：SELECT LOWER(HELLO WORLD) FROM dual;4. 将单词首字母变大写：SELECT INITCAP(HELLO WORLD) FROM dual;5.

11、 字符串除了可以使用”|”连接之外，还可以使用CONCAT()函数进行连接。不如：“|”好用。SELECT CONCAT(HELLO, WORLD) FROM dual;6. 字符串截取：substr()7. 字符串长度：length()8. 内容替换：replace()SELECT substr(hello,1,3) 截取字符串, length(hello) 字符串长度, replace(hello,l,x) 字符串替换FROM dual;9. Oracle中substr从0或从1开始效果是一样的。范例：要求显示所有雇员的姓名及姓名的后三位。a) SELECT ename 姓名, subst

12、r(ename,length(ename)-2) 后三位 FROM emp;b) SELECT ename 姓名, substr(ename,-3) 后三位 FROM emp;（只在Oracle中可用，其他数据库不明） 数值函数：接受数值输入并返回数值1. 四舍五入：ROUND()a) SELECT ROUND(789.536) FROM dual;b) 保留两位小数：SELECT ROUND(789.536,2) FROM dual;c) 可直接对整数进行四舍五入：SELECT ROUND(789.536,-2) FROM dual;2. 截断小数位：TRUNC()a) 与ROUND()不同

13、，在TRUNC()操作中，不会保留任何小数，而且小数点也不会执行四舍五入的操作。b) SELECT TRUNC(789.536) FROM DUAL; 结果：789c) 也可以保留小数点的位数：SELECT TRUNC(789.536,2) FROM DUAL;结果：789.53d) SELECT TRUNC(789.536,-2) FROM DUAL;结果：7003. 取余（取模）：MODa) SELECT MOD(10,3) FROM DUAL;结果：1 日期函数：接受日期型数据进行操作a) 日期-数字=日期日期+数字=日期日期-日期=数字（天数）b) 显示10部门雇员进入公司的星期数要想

14、完成此操作，则应先知道当前日期，在Oracle中可以通过以下操作求出当前日期，用sysdate表示。SELECT SYSDATE FROM DUAL;SELECT empno, ename, ROUND(SYSDATE-hiredate)/7 FROM emp;c) MONTHS_BETWEEN():求出给写日期范围的月数ADD_MONTH():在指定日期上加上指定月数，求出之后的日期NEXT_DAY():下一个的今天是那一个日期下一个星期一是是几号：select next_day(sysdate, 星期一) from dual;LAST_DAY():求出给定日期的最后一天日期本月的最后一天：

15、select last_day(sysdate) from dual;d) 转换函数：从一种数据类型转换为另一种数据类型TO_CHAR（）：转换成字符串将日期拆分：select empno, ename, TO_CHAR(hiredate, yyyy) year, TO_CHAR(hiredate, mm) month, TO_CHAR(hiredate, dd) day from emp;结果：按中国人的显示方式：select empno, ename, TO_CHAR(hiredate, yyyy-mm-dd) from emp;结果： 从运行结果可以发现，如果是5月，则会用05表示，这个

16、0称为前导0，如果不想显示，可以使用fm去掉这些0。select empno, ename, TO_CHAR(hiredate, fmyyyy-mm-dd) from emp;显示工资时，按中国人的习惯，用，分割，遂，可用TO_CHAR()进行格式化; 9:表示一位数字。select empno, ename, TO_CHAR(sal, 99,999) from emp; $:表示美元 L：Local，表示用本地语言进行金额显示。select TO_CHAR(sal, L99,999) from emp;TO_NUMBER（）：转换成数字 把字符串变成数字相加：select to_number

17、(123) + to_number(123) from dual;TO_DATE（）：将字符串转换成日期select to_date(2011-03-14, yyyy-mm-dd) from dual; 通用函数：NVL函数、DECODE函数 NVL：可以将一个指定的null值变为指定的内容。select empno, ename, (sal+NVL(comm,0)*12 from emp; DECODE()：类似于IFELSE语句select decode(1,1,内容是1,2,内容是2,3,内容是3) from dual;范例：要求查询出雇员的编号，姓名，雇佣日期及工作，将工作替换成中文显

18、示。select empno 雇员编号, ename 雇员名称, hiredate 雇佣日期,DECODE(job, CLERK, 业务员, upper(salesman), 销售人员, upper(manager), 经理, upper(analyst), 分析员, upper(president), 总裁)职位from emp;g) 多表查询（重点）查询使用到一张以上的表，则为多表查询。可用where：select * from emp e, dept d where e.deptno = d.deptno;表自身关联，如查上级。SELECT e.ename, e.job, m.ename

19、FROM emp e, emp mWHERE e.mgr = m.empno;h) 左、右连接（重点）SELECT e.empno, e.ename, d.deptno, d.dname, d.locFROM emp e, dept dWHERE e.deptno(+) = d.deptno;右连接WHERE e.deptno=d.deptno(+);左连接(+)在=的左边是右连接，在=的右边是左连接，默认是左连接。(+)在=的左边，以右边的表为准，反之，则以左边的表为准。i) 有关连接的语法SELECT tabel1.column, table2.columnFROM table1CROSS

20、 JOIN table2|NATURAL JOIN table2|JOIN table2 USING(column_name)|JOIN table2 ON(table1.column_name = table2.column_name)|LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.coulumn_name = table2.column_name);j) 交叉连接（CROSS JOIN）：产生笛卡尔积SELECT * FROM emp CROSS JOIN dept;k) 自然连接（NATUAL JOIN）：自动进行关联字段的匹配SELECT *

21、FROM emp NATURAL JOIN dept;去除了笛卡尔积l) USING子句：直接关联的操作列SELECT * FROM emp e JOIN dept d USING(deptno) WHERE deptno=30;m) ON子句：用户自己编写连接的条件SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno) WHERE e.deptno=30;结果与USING是一样的。n) 左连接（左外连接）、右连接（右外连接）：LEFT JOIN, RIGHT JOIN。SELECT e.ename,d.deptno,d.dname,d.lo

22、c FROM emp e RIGHT JOIN dept d ON(e.deptno=d.deptno);5. 组函数和分组统计（重点）a) 分组：例如，把所有的男生分为一组，女生分为一组如果想求出每一组的平均身高，平均年龄等，这就需要使用分组函数。6. 组函数a) 在SQL中常用的组函数有以下几个：i. COUNT():求出全部的记录数SELECT COUNT(empno) FROM emp;ii. MAX(): 求出一组中的最大值iii. MIN(): 求出最小值求最大最小值，一般针对于数字的应用上。求出所有员工的最低工资。SELECT MIN(sal) from emp;iv. AVG(): 求出平均值v. SUM(): 求和7. 分组统计8. 9.