第一章 oracle数据库基础
第一章 oracle数据库基础
1、oracle简介-->数据库管理系统
1.1：数据库
1.2：全局数据库名
1.3：数据库实例
1.4：表空间
1.5：数据文件(扩展名：.dbf)
1.6：控制文件(扩展名：.ctl)
1.7：日志文件(扩展名：.log)
1.8：模式和模式对象
2、连接oracle数据库
2.1 SQL*Plus工具
2.2 PL/SQL Developer工具
3、配置oracle数据库
3.1 配置监听器（LISTENER）
3.2 配置一个本地网络服务名 (TNSNAME)
4、SQL语言
4.1数据定义语言(创建、更改、截断、删除)
4.1.1 //创建表
CREATE TABLE STUDENT
(
stuNo CHAR(6) NOT NULL,
stuName VARCHAR2(20) NOT NULL,
stuAge NUMBER(3,0)NOT NULL,
stuID NUMBER(18,0),
stuSeat NUMBER(2,0)
);
4.1.2 //删除表的所有行且不记录日志
TRUNCATE TABLE<tablename>;
4.2数据操作语言(插入、选择、更新、删除)
4.2.1 语法角度
4.2.1.1 选择无重复的行
关键字：DISTINCT;
4.2.1.2 带条件和排序的sql命令
带条件：WHERE
排序：ORDER BY 列名 ASC或DESC
4.2.1.3 使用列别名
关键字：as 或 空格
例：select stuName as "姓名",stuAge as "年龄" from STUDENT；
4.2.1.4 利用现有的表创建新表
语法：CREATE TABLE<tablename2> ＡＳ SELECT {* | column(s)} FROM <tablename1> [WHERE <condition>]；
此命令可以把现有表的数据复制到新表中，也可以仅复制选的的列或只复制结构而不复制记录
4.2.2 技巧角度
4.2.2.1 查看表中行数
执行语句：SELECT COUNT(1) FROM STUDENT； --效率较高
SELECT COUNT(*) FROM STUDENT； --效率较低
4.2.2.2 取出某列不存在重复的记录
例：SELECT stuName，stuAge FROM STUDENT
GROUP BY stuName,stuAge
HAVING(COUNT(stuName || stuAge)<2);
4.2.2.3 删除某列重复的行（保留一行记录）
//......
4.2.2.4 查看当前用户所有数据量>100万的表的信息
例: SELECT table_name FROM user_all_tables a WHERE a.num_rows>1000000;
其中user_all_tables 为系统提供的数据视图。
4.3事务控制语言(提交、保存点、回滚)
4.3.1 COMMIT:(提交事务，保存对数据库的所有修改)
4.3.2 ROLLBACK:(回滚事务，取消对数据库所做的任何修改)
4.3.3 SAVEPOINT:(在事务中创建存储点)
/
4.3.4 ROLLBACK TO<SavaPoint_Name>:(将事务回滚到存储点)
4.4数据控制语言--为用户提供权限控制命令++
5、SQL操作符
5.1 算术操作符
加：+
减：-
乘：*
除：/
5.2 比较操作符
等于：=
不等于：！=
大于：>
小于：<
大于等于：>=
小于等于:<=
两个值之间：BETWEEN...AND
与列表中的值匹配：IN
匹配字符模式：LIKE
是否为空：IS NULL
5.3 逻辑操作符
与：AND
或：OR
非：NOT
5.4 集合操作符
UNION(联合)-->返回两个查询所有不重复的行
UNION ALL(联合所有)-->合并两个查询所选定的所有行，包括重复行
INTERSECT(交集)-->返回两个查询都有的行
MINUS(减集)-->只返回由第一个查询选定而不被第二个查询选定的行，
即在第一个查询结果中排除在第二个查询结果中出现的行
5.5 连接操作符
||:将两个或多个字符串合并成一个字符串，或将一个字符与数值合并在一起
6 SQL函数（单行函数、聚合函数、分析函数）
6.1 单行函数
6.1.1 字符函数
6.1.2 日期函数
sysdate -->系统当前日期
例： select sysdate from dual;--->dual:虚表
6.1.3 数字函数
6.1.4 转换函数
6.1.1.1 TO_CHAR()
转换成字符串类型
例：SELECT TO_CHAR(SYSDATE,'YYYY "年" fmMM "月" fmDD "日" HH24:MI:SS') FROM dual;
--输出系统当前日期
6.1.1.2 TO_DATE()
转换成日期类型
例：SELECT TO_DATE('2012-01-12','yyyy-mm-dd') FROM dual;
6.1.1.2 TO_NUMBER()
转换成数值类型
例：SELECT SQRT(TO_NUMBER('100')) FROM dual;
6.1.1 其他函数
6.1.1.1 NVL(EXP1,EXP2)-->如果EXP1位NULL，返回EXP2，否则返回EXP1;
6.1.1.2 NVL2(EXP1,EXP2,EXP3)-->如果EXP1位NULL，返回EXP2，否则返回EXP3;
6.1.1.3 DECODE(value,if1,then1,if2,then2,...else)-->如果value的值为if1，则返回then1的值，如果value的值为if2，
则返回then2的值，。。。。否则返回else的值
6.2 聚合函数
。。。
6.3 分析函数
语法：函数名([参数]) OVER ([分区子句][排序子句])
6.3.1 ROW_NUMBER
返回一个唯一的值，当遇到相同数据时，排名按照记录集中的记录的顺序依次递增
6.3.2 DENSE_RANK
返回一个唯一的值，当遇到相同数据时，所用相同数据的排名是一样的
6.3.3 RANK
返回一个唯一的值，当遇到相同数据时，所用相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名
--样例
/*创建订单表*/
CREATE TABLE orders
(
orderid NUMBER(12) PRIMARY KEY,--订单编号
order_date DATE NOT NULL,--订货日期
order_mode VARCHAR2(8),--订货模式
customer_id NUMBER(6) NOT NULL,--客户编号
order_status NUMBER(2),--订单状态
order_total NUMBER(8,2),--总定价
sales_rep_id NUMBER(6),--销售代表id
promotion_id NUMBER(6)--推广员编号
);
/*创建客户表*/
CREATE TABLE customers
(
customer_id NUMBER(6),--客户编号
cust_first_name VARCHAR2(20) NOT NULL,--名
cust_last_name Varchar2(20) NOT NULL,--姓氏
nls_language Varchar2(3),--语言
nls_territory VARCHAR2(30),--地域
credit_limit NUMBER(9,2),--信贷限额
cust_email VARCHAR2(30),--邮箱
account_mgr_id NUMBER(6),--客户经理
marital_status VARCHAR2(30),--婚姻状态
gender CHAR(1)--性别
);
/*创建员工表*/
CREATE TABLE employee
(
empno NUMBER(4) NOT NULL, --员工编号
ename VARCHAR2(10), --员工姓名
job VARCHAR2(9), --员工工种
mgr NUMBER(4), --经理编号
hiredate DATE, --受雇日期
sal NUMBER(7,2), --员工薪水
comm NUMBER(7,2), --福利
deptno NUMBER(2) --部门编号
);
/*创建部门表*/
CREATE TABLE dept
(
deptno NUMBER(2) primary key, --部门编号
dname VARCHAR2(14), --部门名称
loc VARCHAR2(13) --地址
);
/*向部门表插入数据*/
INSERT INTO dept VALUES(1,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES(2,'RESEARCH','DALLAS');
INSERT INTO dept VALUES(3,'SALES','CHICAGO');
INSERT INTO dept VALUES(4,'OPERATIONS','BOSTION');
/*向员工表插入数据*/
。。。。。
/*查询表中数据信息*/
SELECT * FROM customers;
SELECT * FROM orders;
SELECT * FROM employee;
SELECT * FROM dept;
SELECT * FROM orders--订单表
SELECT * FROM customers--客户表
SELECT * FROM employee--员工表
--1、查询客户表索引不重复的地域
SELECT DISTINCT nls_territory FROM customers;
--2、在订单表中找出总结在1万到10万之间的订单号，顾客姓氏，客户经理
SELECT o.orderid,c.cust_last_name,c.account_mgr_id
FROM orders o INNER JOIN customers c
ON o.customer_id=c.customer_id
WHERE o.order_total BETWEEN '10000' AND '100000';
--3、在客户表中找出所在地域为AMERICA的客户经理名称和薪水
SELECT e.ename,e.sal
FROM customers c INNER JOIN employee e
ON c.account_mgr_id=e.mgr
WHERE c.nls_territory='AMERICA';
--4、在客户表中找出地域为AMERICA，ITALY，INDIA，CHINA的客户编号及语言
SELECT customer_id,nls_language
FROM customers
WHERE nls_territory IN ('AMERICA','ITALY','INDIA','CHINA')
--5、在客户表中找出姓氏首字母为F的客户编号和邮箱
SELECT customer_id,cust_email
FROM customers
WHERE cust_last_name LIKE 'F%';
--6、查找所有客户姓名和所下的订单编号
SELECT c.cust_last_name,o.orderid
FROM customers c INNER JOIN orders o
ON c.customer_id = o.customer_id;
--7、根据员工表编写sql语句，只有当最低工资少于5000且最高工资超过15000时，才显示部门编号及该部门最低工资和最高工资
SELECT deptno, MIN(sal+comm),MAX(sal+comm)
FROM employee
WHERE (SELECT MIN(sal+comm) FROM employee)<6600 and (SELECT MAX(sal+comm) FROM employee)>7000
GROUP BY deptno
--8、根据员工表编写sql语句，显示各部门的每个工作类别中支付的最高工资
SELECT max(sal+comm),MIN(sal+comm) FROM employee
WHERE 1=1
GROUP BY deptno
--合并两条数据
SELECT ename || job FROM employee;
--统计部门编号大于20的部门编号和总人数
SELECT deptno,COUNT(empno) FROM employee
GROUP BY deptno
HAVING(deptno>20);
--分页显示第2页数据
SELECT * FROM (SELECT ROWNUM rn,e.* FROM employee e)
WHERE rn>4 AND rn<8;
--编辑表
ALTER TABLE employee
--ADD(emp_add VARCHAR2(20));--增加列
--MODIFY(emp_add VARCHAR2(10));--修改列
--DROP COLUMN emp_add;--删除列
--获取系统当前日期
select sysdate from dual;
--将日期字符串转换为日期类型
SELECT TO_DATE('2015-12-12','yyyy-mm-dd') "日期" FROM dual;
--转换成字符串类型
SELECT TO_CHAR(SYSDATE,'YYYY "年" fmMM "月" fmDD "日" HH24:MI:SS') "日期" FROM dual;
--转换成数值类型
SELECT SQRT(TO_NUMBER('100')) "数值" FROM dual;
