一、 基本
(一)三种进入客户端的方式
1.Dos命令行下:sqlplus用户名:scott密码:tiger,sqlplus scott/tiger
2.开始-运行-sqlplus图形版的SQLPLUS
3.http://localhost:5560/isqlplus
(二)解锁
1.sqlplus sys/*** as sysdba//***为密码
2.alter user scott account unlock; //用户scott解锁
(三)描述表
1.desc emp //描述emp表
2.dual //只有一条记录,用来计算各种表达式,显示特殊数据
EX:select sysdate from dual;//显示当前时间
(四)基本设置
1.set linesize 200;//用于设定每行显示的宽度
2.set pagesize 30;//设置显示的页数
(五)创建新用户
1.backup scott
exp//导出***用户至当前目录
2.超级管理员登陆
sqlplus sys/*** as sysdba
已在SQL中:conn sys/密码as sysdba;
删除用户:drop user *** cascade;
3.create user
create user fxw identified by ffxxww default tablespace users quota 10M on users;
4.grant
grant create session, create table, create view to fxw;
5.import the data
imp//由当前目录导入***用户
(六)简单备份方法
create table dept 2 as select * from dept;
(七)回退
rollback;
(八)注释
1.多行:/* */
2.单行--
二、select语句
(一)取数据
1.select * from salgrade;//把salgrade表中的所有数据取出
2.select ename,deptno from emp;//把emp中所有数据的ename和deptno取出
(二)取别名
1.select ename,sal*12 annual_sal from emp;//给sal*12取别名annual_sal
2.select ename,sal*12 "annual sal" from emp;//双引号使其保持原来的格式
(三)空值的处理
1.任何含有空值的表达式,结果都是空值
2.select ename, sal, comm from emp where comm is null;
3.select ename, sal, comm from emp where comm is not null;
(四)字符串
1.字符串的表示:''
2.字符串连接符:||
3.两个单引号''表示一个单引号'
EX:select ename || 'asfsdf' from emp;
(五) distinct
select distinct deptno from emp;//去掉重复的deptno
(六) where
1.select * from emp where deptno = 10;//从emp中取出deptno=10的数据
2.select * from emp where empno < > 10;//<>表示不等于
3.select ename, sal from emp where sal between 800 and 1500;
等价于select ename, sal from emp where sal >= 800 and sal <= 1500;
4.select sal from emp where sal in (800, 1500, 2000);
//取出sal等于800或1500或2000的值
5.select ename from emp where ename like '%ALL';
//模糊查询,通配符(正则表达式)
1)_表示一个字符,%表示一个或多个字符
2)如果本身就有%,使用转义字符
3)修改转义字符符号
EX:select ename from emp where ename like '%$%ALL' escape '$';
(七) order by
1.select * from dept order by deptno desc;
//排序,默认升序(asc),降序加desc
2.select * fromemp order by deptno asc, ename desc;//组合
(八) sql_function (单行函数)
1.lower
select lower(ename) from emp;//转换成小写
2.upper
select upper(ename) from emp;//转换成大写
3.substr
select substr(ename, 1, 3) from emp;
//从第一个字符开始截,一共截3个字
4.chr
select chr(65) from dual;//ASCII码转换成字符
5.ascii
select ascii('A') from dual; //字符转换成ASCII码
6.round
1)select round(23.6552) from dual; //四舍五入
2)select round(23.6552, 2) from dual;//四舍五入到小数点后2位
7.to_char
1)select to_char(sal, '$99,999.9999') from emp;
//格式化输出数字(9代表一位数字,没有就不显示,但对于小数点后的强制显示)
2)select to_char(sal, '$00,000.0000') from emp;
//0代表一位数字,没有显示为0
3)select to_char(sal, 'L99,999.9999') from emp;//L代表本地货币
4)select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;
//格式化输出日期
5)select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
//格式化输出当前时间,24小时格式输出小时数
8.to_date
select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
//输出1981年2月20日后入职的数据
9.to_number
select sal from emp where sal > to_number('$1,250.00', '$9,999.99');
//输出薪水大于$1,250.00的数据
10.nvl
select ename, sal*12 + nvl(comm, 0) from emp;
//如果comm的值是空值用0替代,否则直接输出
(九) group function(组函数)
1.区别:
1)单行函数:一条输入对应一条输出
2)组函数:多条输入对应一条输出
2.max
select max(sal) from emp;//求最高薪水值
3.min
select min(sal) from emp;//求最低薪水值
4.avg
1)select to_char(avg(sal), '999999.99') from emp;
//求平均薪水值,并保留到小数点后两位
2)select round(avg(sal), 2) from emp;
//求平均薪水值,并四舍五入到小数点后两位
5.sum
select sum(sal) from emp;//求和
6.count
1)select count(*) from emp;//求出emp表中有多少条记录
2)select count(comm) from emp; //count不计算空值
3)select count(*) from emp where deptno = 10;
//10这个部门一共有多少人
(十) group by
1.select deptno, avg(sal) from emp group by deptno;//分组求出平均薪水
2.select deptno, avg(sal) from emp group by deptno, job;//组合分组
3.错误:select ename, max(sal) from emp;
1)因为max只能有一个值,但是等于max的值可能有好几个,不能匹配。
2)应用子查询:
select ename from emp where sal = (select max(sal) from emp);
4.规则:出现在select的字段。如果没有出现在组函数里必须出现在group by里
Having where是对单条语句进行过滤,对分组后的使用having
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;
(十一)单条select执行顺序:
select ... from... where ... group by... having ... order by...;
(十二)子查询
在select语句中套select语句):
select ename, sal from emp where sal > (select avg(sal) from emp);
技巧:把子查询的结果当成一张表
(十三)table connection
1.自连接
select e1.ename, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
2.连接-SQL1992: select ename, dname from emp, dept;//笛卡尔乘积
1)select ename, dname from emp, dept where emp.deptno = dept.deptno;//等值连接
2)select ename, grade from emp e,salgrade s where e.sal between s.losal and s.hisal; //非等连接
3.连接-SQL1999:
1)select ename, dname from emp cross join dept;//笛卡尔乘积
2)select ename, dname from emp join dept on (emp.deptno = dept.deptno);
//等值连接
#:另一种写法:
select ename, dname from emp join dept using(deptno);
//但不推荐,要求条件过多,如类型一致,少了不宜查错
3)select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);//非等值连接
4.新语法把过滤条件和连接条件分开,读起来更明确。
select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno);
5.外连接
1)select e1.ename, e2.ename from emp e1 left outer join emp e2 on(e1.mgr = e2.empno);
//左外连接会把左边那张表的多余(不能和另一张表连接)的数据拿出来,outer可省略
2)select ename, dname from emp e right outer join dept d on (e.deptno = d.deptno); //右外连接
3)select ename, dname from emp e full outer join dept d on (e.deptno = d.deptno);//全连接
(十四)rownum
1.默认显示:先导入的先显示
2.伪字段:
rownum是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列(强调:先要有结果集)。简单的说rownum是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1,而有其他大于1的值。
EX:select ename from emp where rownum <= 5;
3.限制:不能接>号和>=号
4.oracle特有,其他数据库不兼容
5.所有数据库里取第几行到第几行最麻烦:
select ename from (select rownum r, ename from emp) where r > 10;
(十五)多表连接子查询:
1.求部门中哪些人薪水最高:
select ename,sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);
2.求部门平均薪水的等级:
select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);
3.求部门平均的薪水等级:
select deptno, avg(grade) from (select deptno, ename, grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t;
4.求哪些人是经理人:
select ename from emp where empno in (select distinct mgr from emp);
5.不准用组函数,求薪水的最高值:
select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
6.求平均薪水最高的部门的部门编号:
select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno));
7.求平均薪水最高的部门的部门名称:
select dname from dept where deptno = (select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno)));
#:组函数嵌套(最多只能嵌套两层):
select dname from dept where deptno = (select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg(sal)) from emp group by deptno));
8.求平均薪水的等级最低的部门的部门名称:
select dname, t1.deptno, grade, avg_sal from (
select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal) )t1
join dept on (t1.deptno = dept.deptno)
where t1.grade = (
select min(grade) from (
select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s
on(t.avg_sal between s.losal and s.hisal))
);
#:简化办法-创建视图:
1)create view v$_dept_avg_sal_info as select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from
emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal);
2)select dname, t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1 join dept on (t1.deptno = dept.deptno) where t1.grade = (select min(grade) from v$_dept_avg_sal_info t2);
3)若权限不足,则:
conn sys/密码as sysdba;
grant create table, creat view to scott;
9.求比普通员工的最高薪水还要高的经理人名称:
select ename from emp where empno in (select distinct mgr from emp where mgr is not null)
and sal > (
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)
);
10.球薪水最高的前5名雇员
select ename, sal from (select ename, sal from emp order by sal desc) where rownum <= 5;
11.求薪水最高的第六到第十名雇员
select ename, sal from (select ename, sal, rownum r from (select ename, sal from emp order by sal desc)) where r >=6 and r <= 10;
(十六)面试题:
1.比较效率
select * from emp where deptno = 10 and ename like ‘%A%’;
select * from emp where ename like ‘%A%’ and deptno = 10;
第一个效率高,对数字的比较效率较高
2.有三个表格S,C,SC
S(SNO, SNAME)代表(学号,姓名)
C(CNO, CNAM, CTEACHER)代表(课号,课名,教师)
SC(SNO, CNO, SCGRADE)代表(学号,课号,课号成绩)
问题:
1, 找出没选过“liming”老师的所有学生姓名
select sname from s join sc on (s.sno = sc.sno) join c(c.cno = sc.cno) where c.cteacher <> ‘liming’;
2, 列出两门以上(含两门)不及格学生姓名及平均成绩
select sname where sno in (select sno from sc where scgrade < 60
group by sno having count(*) >= 2);
3, 即学过1号课程又学过2号课程所有学生的姓名
select sname from s where sno in (select sno from sc where cno = 1 and cno in (select sno from sc where cno = 2);
三、DML语句
(一) select
(二) insert
1.insert into dept2 values (50, ‘bill’, ‘dl’);
2.insert into dept2 (deptno, dname) values (60, ‘setve’);
3.insert into dept2 select * from dept;
#:要求,子查询拿出来的东西必须与插入对象的类型一致
(三) update
update emp2 set sal = sal * 2, ename = ename || ‘-‘ where deptno =10;
(四) delete
delete from dept where deptno = 10;
(五)事物(transaction)控制语句
1.开始
一个transaction起始于一条DML语句
#:一个rollback全回退
2.结束:
1)rollback;
2)commit;
#:之后再rollback;无效
3)执行DDL或DCL语句时自动commit
4)正常断开连接,自动commit,非正常断开自动rollback
四、DDL语句及数据库常用对象
(一) create
1.创建一张表
create table stu (id number(8) primary key, name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50)),
constraint stu_name_email_uni unique(email, name)
constraint stu_class_fk foreign key (class) re references class(id)
);
create table class ( id number(4) primary key, name warchar2(20) not null);
2.constraint
1)constraint约束取名
2)字段约束:写在字段名后 限制:无法写组合约束
3)表级约束:可组合约束
4)五种约束
a)NOT NULL (非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值
b)CHECK (检查)--检查在约束中指定的条件是否得到了满足
c)UNIQUE (唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的
#:两个null不认为是重复
d)PRIMARY KEY (主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束(语法上等于唯一+非空,逻辑上还有约束)
e)POREIGN KEY (外键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外键可以指向主键或者其他表的唯一键
#:外键约束被参考的字段必须是主键
#:有子记录则不能删除
(二) alter
1.alter table add(addr varchar2(100));
2.alter table stu drop (addr);
3.alter table stu modify (addr varchar2(150));
4.alter table stu drop constraintstu_class_fk;
5.alter table stu add constraint stu_class_fk foreignkey (class) references class(id);
(三) drop
drop table stu;
(四)数据库常用对象
1.table
2.数据字典表
1)dictionary
a)存放数据字典表的表
b)desc dictionary;
c)select table_name from dictionary where table_name like ‘USER%’;
2)user_tables
a)desc user_tables;
b)select table_name from user_tables;
3)user_views
select view_name from user_views;
4)user_constraints
select constraint_name from user_constraints;
desc user_constraint;
3.index
1)create index idx_stu_email on stu(email);
2)drop index idx_stu_email;
3)select index_name from user_indexes;
4)当加主键约束或者唯一约束,则自动建立索引
5)一般在当访问字段量特别大,效率特别低时,建立索引,但不要轻易建立索引
6)优点:读的时候效率更高
7)缺点:插入时效率更低,占用空间
4.view
1)为子查询取别名
2)视图可以用来更新数据,但是很少用
3)优点:简化查询,保护私有数据
4)缺点:增加维护支出(表结构修改,视图也得改)
5.sequence
1)oracle特有
2)用来产生一个唯一的不间断的数字序列,一般用来做主键
3)create table article (
id number,
title varchar2(1024),
cont long);
4)create sequence seq;
5)create sequence seq start with 10 increment by 2;
6)drop sequence seq;
7)select seq.nextval from dual;
8)insert into article values(seg.nextval, ‘a’, 1234);
五、 数据库设计的三范式
(一)范式
数据库设计的规则
(二)原则
不存在冗余数据
(三)第一范式
要有主键,列不可分
(四)第二范式
不能存在部分依赖:
当一张表里有多个字段作为主键时,非主键的字段不能依赖部分主键
(五)第三范式
不能存在传递依赖:
(六)注意
实际情况具体分析,可能会违反三范式
六、PL_SQL
(一) oracle内部使用的编程语言
(二) EX
1)begin
dbms_output.put_line(‘HelloWorld!’);
end;
2)set serveroutput on;//显示输出
3)declare
v_name varchar(20);//默认v_开头
begin
v_name := ‘myname’;
dbms_output.put_line(v_name);
ends;
4)declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line(‘error’);
end;
5)declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where empno = 7369;