数据库基本操作1229

1、数据库：数据仓库小型数据库：Access FoxBase中型数据库：MySQL SQLServer中大型：Sybase Oracle DB2都支持标准的SQL语言Oracle数据库基本组成Oracle服务网络服务，监听服务,数据库服务.Oracle实例 配置数据库实例的DataBase Configuration AssistantOracle客户端SQL Plus 客户端如果要连上数据库，必须要有两个服务：数据库实例服务数据库监听服务Oracle数据库默认管理员用户SysSystemsysdba数据的操作一般不会用管理员用户，而是会用普通用户默认的普通用户 scott/tiger 被锁定给用

2、户解锁需要管理员权限：alter user 用户名 account unlock;将用户锁定需要管理员权限alter user 用户名 account lock;切换用户conn 用户名;查看当前用户show user;断开当前用户disc退出客户端exit修改其他用户密码：alter user 用户名 identified by 新密码;修改自己的密码password创建新用户create user 用户名 identified by 密码;新用户没有任何权限给用户一个连接数据库的权限grant connect to 用户名;删除用户drop user 用户名;对于数据库的操作，使用SQL语

3、言SQL：结构化查询语言DDL:数据定义语言Create 创建Alter修改Drop删除DML:数据操作语言Insert into 插入数据Update 修改数据Delete 删除数据Select 查询数据DCL:数据控制语言Grant 授权Revoke 撤销授权TCL:事物控制语言创建用户:create user wangerxiao identified by 1234;给用户授权：Grant connect to wangerxiao;/允许连接数据库Grant resource to wangerxiao;/允许使用表空间切换到新用户Conn wangerxiao/1234;在默认表空

4、间中创建一张表用来保存学生信息：学号，姓名,性别,年龄,成绩Studentnum,name,sex,age,scoreCreate table student(Num number(10),Name varchar2(30),Sex char(3),Age number(3),Score number(5,2);查看表结构：Desc student;修改表结构1. 将num字段的类型改为number(20),并且不允许为nullAlter table student modify num number(20) not null;Alter table student modify name v

5、archar2(20) not null;2. 添加一个新字段tel Alter table student add (tel number(11);3. 删除tel字段Alter table student drop column tel;4. 删除整张表Drop table student;oracle中的数据类型用来定义字段的字符char :定长的 最多2000字节Varchar2：可变长度最多4000字节Long：可变长度 最多2GB数字Number(a,b)Number(a) 表示没有小数部分a表示是几位数包括小数位b表示小数有几位日期Date:精确到秒Timestamp：精确到0.

6、000001秒二进制类型Raw：存储2000字节Long Raw：存储2GBLOB:大对象，以二进制的保存，最大可以4GBCLOB :大字符类型BLOB：大字节类型BFILE：大字节文件1. 保存一条数据到student表中Insert into student(num,name,sex,age,score) values (123,张三丰,男,100,90.5);2. 查询student表中的数据Select num,name,sex,age,score from student;3. 查询所有字段的值Select * from student;4. 带伪列的查询 Select rownum

7、,rowid,num,name,sex,age,score from student;5. 修改第二条数据的学号和姓名Update student set num=111,name=张无忌 where sex=女;6. 删除姓名是张无忌的数据 Delete from student where name=张无忌;任务：1. 使用system用户登录2. 创建一个新用户 zhangsanfeng3. 给新用户授权可以连接数据库，操作表空间4. 切换到新用户zhangsanfeng5. 创建一张新表 WLMJ秘籍编号(num),秘籍名称(name),等级(level),杀伤力(power)6. 往

8、表中至少插入5条数据7. 练习使用DML语句事物控制语言Savepoint 设置回滚点 事物中间的一个临时标记点 可以让事物回滚到指定位置设置回滚点 savepoint a;Rollback 回滚Rollback to a; /回滚到指定位置Rollback;/回滚到初始状态Commit 提交事物：事物一旦被提交，就意味着不可以回滚哪些时候事物会被提交1. 当执行commit语句的时候会提交事物2. 当正常退出客户端的时候会自动提交事物示例用户：scottDeptbak 部门表Deptno：部门编号Dname部门名称Loc部门位置 Empbak 员工表Empno:员工编号Ename员工姓名MG

9、R 上司编号Job岗位Hiradate入职日期Sal工资Comm奖金Deptno 所属部门编号复制表(带数据)Create table empbak as select * from emp ;复制表(不带数据)Create table empbak as select * from emp where 1=2;设计一个事物1. 给7839员工降低1000块工资Update empbak set sal=sal-1000 where empno=7839; 2. 设置保存点Savepoint a;3. 给7369员工加1000块工资Update empbak set sal=sal+1000

10、where empno=7369;4. 设置保存点Savepoint b;5. Update empbak set sal=sal+1000 where empno=7934;6. 回滚到bRollback to b;7. 回滚到aRollback to a;8. 回滚到初始状态Rollback;查询语句Select * from empbak;Select empno,ename,job,sal from empbak;尽量只查询需要查询的字段别名查询Select empno as 编号,ename as 姓名, sal as 工资 from empbak;查询所有人的姓名和年薪带运算符的查

11、询Select ename 姓名,sal*12 年薪 from empbak;查询年薪超过3万的人带条件的查询Select ename 姓名,sal*12 年薪 from empbak where sal*12>30000;-从结果集中去掉重复的数据 distinct-查询empbak表中有多少个工作岗位select distinct job from empbak;-查询工资大于2000，并且小于3000的员工姓名，岗位和工资Select ename,job,sal from empbak where sal>=2000 and sal<=3000;Select ename

12、,job,sal from empbak where sal between 2000 and 3000;-查询工资小于2000，或者大于3000的员工姓名，岗位和工资Select ename,job,sal from empbak where sal<2000 or sal>3000;-查询工作岗位是CLERK 和SALESMAN的所有员工姓名和工资Select ename,sal from empbak where job=CLERK or job=SALESMAN;Select ename,sal from empbak where job in(CLERK ,SALESMA

13、N);-查询工作岗位除了CLERK 和SALESMAN之外的所有员工姓名和工资Select ename,sal from empbak where job!=CLERK and job!=SALESMAN;Select ename,sal from empbak where job<>CLERK and job<>SALESMAN;Select ename,sal from empbak where job not in('CLERK', 'SALESMAN');-job大于任意一个值Select ename ,sal from empb

14、ak where job > any(2000,3000,4000);-job大于所有值Select ename ,sal from empbak where job > all(2000,3000,4000);-查询所有有奖金的人的姓名和奖金Select ename,comm from empbak where comm is not null;-模糊查询% 0个或者多个任意字符_ 1个任意字符-查询所有名字中有S的人的姓名和工资Select ename,sal from empbak where ename like %S%;-查询名字以S开头的人的姓名和工资 Select e

15、name,sal from empbak where ename like 'S%'-查询名字中第二个字符是A的人的姓名和工资Select ename,sal from empbak where ename like '_A%'-查询名字只有4个字母的人的姓名和工资Select ename,sal from empbak where ename like '_'排序语句 order by查询所有人的姓名和工资，按照工资从少到多排序Select ename,sal from empbak order by sal asc;查询所有人的姓名和工资，按

16、照工资从多到少排序Select ename,sal from empbak order by sal desc;-对部门10的员工按照工资从多到少排序Select ename,sal from empbak where deptno=10 order by sal desc; 根据多个字段排序优先按照前面的字段排序，在前面字段值一样的情况下，再按照后面的字段排序Select ename,sal,deptno from empbak order by deptno asc,sal desc;*1224*Oracle中的运算符算术运算符 + - * / 比较运算符 =等于 != <>不

17、等于 > >=大于 大于等于 < <= 小雨 小于等于逻辑运算符And 类似于Java中的&& ,并且Or类似于Java中的 |, 或者Between and 在某一个范围内In在多个值之间取任意一个值Not in 不能取in中的值Any 任意一个值All 所有的值Like 模糊匹配 % 通配0个以上字符_ 通配1个字符连接运算符：用来连接两个SQL语句的union 用来合并两个结果集，如果有重复的数据，就去掉查询员工部门编号大于10的所有员工信息Select * from empbak where deptno>10;查询员工部门编号为20的所有

18、员工信息Select * from empbak where deptno=20;Select * from empbak where deptno>20 union Select * from empbak where deptno=20;union all 用来合并两个结果集，并保留重复的数据Select * from empbak where deptno>10 union all Select * from empbak where deptno=20;intersect 取两个结果集的交集保留两个结果集中相同的数据Select * from empbak where de

19、ptno>10 intersect Select * from empbak where deptno=20;Minus 取两个结果集的差集去掉两个结果集相同的数据Select * from empbak where deptno>10 minus Select * from empbak where deptno=20;Oracle函数单行函数：对每一条数据单独进行处理的函数 不会改变结果集中的数据条数对null值进行处理的函数NVL函数 nvl(字段，初始值) 如果字段的值为空，则用指定值表示计算员工的月收入 =月工资+奖金Select ename,sal,nvl(comm,0

20、),sal+nvl(comm,0) as 收入 from empbak;NVL2 函数 nvl2(字段,值1，值2) 如果字段值不为空，就执行值1，如果为空，就执行值2如果有奖金，就在原奖金的基础上+200，如果没有奖金，就发100的奖金Select ename,comm,nvl2(comm,comm+200,100) as 应发奖金 from empbak;NULLIF 函数 nullif(字段,值) 如果 字段=值，则将字段值改为null如果奖金是300，就变为nullSelect ename,comm,nullif(comm,300) from empbak;日期函数Select tru

21、nc(Sysdate) from dual;-获得当前日期Select add_months(sysdate,3) from dual;-在当前日期上增加相应的月份数months_between(date1,date2) -判断两个时间之间的月份差获得在这个公司工作时间超过30年的员工姓名和工作年限Select ename,months_between(sysdate,hiredate) from empbak where months_between(sysdate,hiredate) >30*12;字符函数-查询empbak表中员工的姓名，全部用小写表示Select Initcap(

22、ename) from empbak;upper('') 将小写转成大写Lower() 将大写转成小写Initcap() 将首字母大写Ltrim('abc123','abc') 如果参数二中任意一个字符出现在参数一的左边，就去掉Rtrim('abc123','123')如果参数二中任意一个字符出现在参数一的右边，就去掉Translate('abca','a','b');如果参数二在参数一中出现，就用参数三代替replace('','',

23、'')如果参数二在参数一中出现，就用参数三代替instr('abcd','c') 获得参数二在参数一种出现的位置substr('abcdef',3,2);从第3个字符开始，截取2个字符Concat('abc','def') 连接两个字符串CHR(97) 获得数字对应的字符ASCII('A') 获得字符对应ASCII码Lpad('abc',10,'*')如果abc不够10位，就在abc的左边加上*Rpad('abc',10,'*&

24、#39;)如果abc不够10位，就在abc的右边加上*length('') 获得字符串的长度trim(' abcabc ') 去掉参数一左右两边的空格数字函数abs() -取绝对值ceil(10.4) -向后取整 结果为11floor(10.6) -向前取整 结果为10round(10.55,1)-保留指定的小数位，四舍五入trunc(10.54,1)-保留指定的小数位，去掉尾数Power(a,b) -计算a的b次方Mod(a,b) -计算 a%bSqrt(a) -计算 a的平方根转换函数To_char() 将日期类型转成字符串类型Select ename,to

25、_char(hiredate,yyyy-MM-dd) from empbak;To_date() 将字符串类型转成日期类型Insert into empbak values(1122,ZHANGSAN,CLERK,7902,to_date(1980-10-20,yyyy-mm-dd),2000,null,30);To_number() -将纯数字字符串转成数字类型的Select to_num(1234) from dual;* 1225 *分组函数Count函数 统计函数，统计查询到的数据的条数的统计部门10有多少员工Select count(*) from empbak where dept

26、no=10;Select count(empno) from empbak where deptno=10;SUM函数 求和函数 统计部门10的员工每个月的总工资Select sum(sal) from empbak where deptno=10;AVG 函数 计算平均值计算部门10的员工每个月的平均工资Select avg(sal) from empbak where deptno=10;MAX函数 求最大值计算部门10的最高工资Select max(sal) from empbak where deptno=10;MIN函数 求最小值计算部门10的最工低资Select min(sal)

27、from empbak where deptno=10;分组函数通常和分组子句一起使用分组子句：group by统计每个部门每个月发出的工资总和Select deptno,sum(sal) from empbak group by deptno;根据职位求各工种的平均工资Select job,avg(sal) from empbak group by job;按照部门统计每个部门的总人数Select deptno,count(empno) from empbak group by deptno;Having 子句 作用类似于where，having是用在分组之后 where用在分组之前按照部门

28、统计每个部门的总人数超过3人的部门Select deptno,count(empno) from empbak group by deptno having count(empno)>3;根据职位求各职位的平均工资,需要取出平均工资超过2000的职位Select job,avg(sal) from empbak group by job having avg(sal)>2000;求出每个经理手下员工的平均工资在2500以上的经理编号及平均工资,并按照平均工资倒叙排序Select mgr,avg(sal) from empbak where mgr is not null group

29、 by mgr having avg(sal)>2000 order by avg(sal) desc;子查询：将查询的结果作为另外一个查询的条件或结果集查找工资最高的员工姓名和工资Select max(sal) from empbak; /查找最高工资Select ename,sal from empbak where sal=(Select max(sal) from empbak);查找各个部门工资最高的人的姓名和工资Select max(sal) from empbak group by deptno;/按照部门查出最高工资Select deptno,ename,sal from

30、 empbak a where sal in (Select max(sal) from empbak b group by deptno having a.deptno=b.deptno) ;Select ename,job,sal from empbak;Select * from (Select ename,job,sal from empbak);分析函数：对数据进行排位的Row_number() 连续的排位Select ename,sal,Row_number() over (order by sal desc) as 排名 from empbak ;Rank() 如果被排序的值一样

31、，则排位一样，后面会跳过相应的位数Select ename,sal,Rank() over (order by sal desc) as 排名from empbak ;Dense_Rank()Select ename,sal,Dense_Rank() over (order by sal desc) as 排名 from empbak ;按照工资排序，取出第1名到第5名Select * from (Select ename,sal,Row_number() over (order by sal desc) as 排名 from empbak) where 排名<=5;按照工资排序，取出第

32、6名到第10名Select * from (Select ename,sal,Row_number() over (order by sal desc) as 排名 from empbak) where 排名>5 and 排名<=10;分页查询当前页码：page=1每页条数：num=5Select * from (Select ename,sal,Row_number() over (order by sal desc) as 排名 from empbak) where 排名>num*(page-1) and 排名<=page*num;*多表查询*查询员工的姓名和所属部

33、门名称Select ename,deptno from empbak;Select deptno,dname from deptbak;Select ename,dname from empbak,deptbak where empbak.deptno=deptbak.deptno;得到部门名->人数Select deptno,count(empno) from empbak group by deptno;Select d.dname ,a.* from deptbak d,(Select deptno,count(empno) from empbak group by deptno)

34、 a where d.deptno=a.deptno;/得到人数最多的部门的人数 select count(empno) from empbak group by deptno;Select max(c) from (select deptno d,count(empno) c from empbak group by deptno);/得到人数最多的部门的编号和人数Select * from (select deptno ,count(empno) c1 from empbak group by deptno) where c1=(Select max(c) from (select cou

35、nt(empno) c from empbak group by deptno);/得到人数最多的部门的名字和人数Select d.dname,a.c from dept d,(Select deptno,count(empno) c from empbak group by deptno) a where d.deptno=a.deptno;Select * from (Select d.dname,a.c1 from dept d,(Select deptno,count(empno) c1 from empbak group by deptno) a where d.deptno=a.deptno) where C1=(Select max(c) from (select count(empno) c from empbak group by deptno);求SMITH所在部门的位置：Select deptno from empbak where ename=SMITH;Select loc from deptbak where deptno =(Select deptno fro