Oracle数据库随堂笔记

一、Oracle创建表空间创建用户以及用户授权

1、创建表空间

create tablespace demo  (创建表空间名字为demo)
datafile 'e:\app\tablespace\demo.dbf'  (数据文件所在的位置)
size 500m  (数据文件的空间500m)
autoextend on  (自动扩展大小)
next 10m;  (每次扩展10m)

表空间只是一个逻辑单位,真正存放数据的地方应该叫数据文件,创建完表空间之后应该指定一个数据文件所在的位置,后缀名为.dbf

2、删除表空间

drop tablespace demo;

3、创建用户

create user zhou  (登录的用户名)
identified by root  (登录的密码)
default tablespace demo;  (指定该用户出生的地方,即表空间)

4、给用户授权

-- Oracle数据库中常用角色
    	connect -- 连接角色,基本角色
    	resource -- 开发者角色
    	dba -- 超级管理员角色
grant connect,resource,dba to demo;

5、切换用户

connect demo;

二、数据类型介绍和表的创建

1、Oracle数据类型

2、表的创建

-- 创建一个person表:
	create table person(
		pid number(20),
		pname varchar2(10),
	);

三、修改表结构

1、添加列

alter table 表名 add (字段 数据类型) [default ‘输入默认值’] [null/not null];

(1)添加一列
alter table person add gender number(1);

(2)添加多列
alter table person add (gender number(1), birth date);

2、修改列(字段)类型

alter table 表名 modify(字段 数据类型 [default ‘输入默认值’] [null/not null] , 字段 数据类型 [default ‘输入默认值’] [null/not null]) ; 修改多个字段用逗号隔开.

alter table person modify gender char(1);

char 和 varchar2 的区别:
varchar2 是可变长度, 而 char 是定长.

3、修改列(字段)名称

alter table 表名 rename column 字段 to 字段;

alter table person rename column gender to sex;

4、删除列(字段)

alter table 表名 drop column (字段);

alter table person drop column sex;

四、数据的增删改

1、查询表中记录

select * from person;

2、添加数据

insert into 表名称 (字段1, 字段2, ...) values (数据1, 数据2, ...);

	insert into person (pid, pname) values (1, ‘小明’);

注: 凡是做了’增删改’操作,一定要记得加上提交数据( commit; )

3、修改数据

update 表名称 set 字段=内容, 字段=内容 [where 更新条件(s)];
如果更新的过程中你并没有设置更新条件, 那么就会更新所有数据

update person set pname = ‘小马’ where pid = 1;

4、删除数据

-- 三个删除
	(1)delete from person; 删除表中全部记录
	(2)drop table person; 删除表结构
	(3)truncate table person;  先删除表,再次创建表,效果都能同于删除表中全部记录,在数据量大的情况下,尤其在表中带有索引的情况下,该操作效率高(索引可以提供查询效率,但是会影响增删改效率)
delete from 表名称 [where 删除标记];
delete from person where pname = ‘小马’;

五、简单语句/常用函数

1、select语句

(1) 全表查询

select * from 表名;
select * from emp;

(2) 字段查询

select 字段, 字段 from 表名;
	select empno, ename, sal, deptno from emp;

(3)条件查询

select 字段... from 表名 where 条件;
	select * from emp where sal>2000;
	select * from emp where sal>2000 and sal<3000;
	select * from emp where sal between 2000 and 3000;
	select * from emp where deptno = 10;

(4)逻辑查询 OR 或 AND 与

select * from emp where deptno = 10 and job = ‘manager’;
select * from emp where deptno = 20 and job = ‘analyst’;
select * fron emp where (deptno =10 and job = ‘manager’) or (deptno = 20 and job = ‘analyst’);
select * from emp where empno = 7782 or empno = 7788 or empno = 7369;

(4)IN

select 字段 from 表名称 where 字段 in(字段值);
	select * from emp where empno in(7782, 7788, 7369);

(5)不等于!=、<>、not in

select * from emp where deptno != 10; -- 推荐
select * from emp where deptno < > 10;
select * from emp where deptno not in(10);

(6)IS NULL 将为空的数据查询出来

select * from emp where comm is null;

(7)模糊查询

% 表示任意可以使0到多个字符, _ 表示任意一个字符
	-- 查询员工姓名中以s开头的信息
		select * from emp where ename like ‘s%’;
	-- 查询员工姓名中以s结尾的信息
		select * from emp where ename like ‘%s’;
	-- 查询员工姓名中第三位是a的信息
		select * from emp where ename like ‘__a%’;
	-- 查询员工姓名中含有s的信息
		select * from emp where ename like ‘%s%’;

(8)排序ORDER BY

-- 按照数字
	select * from emp order by sal; -- 系统默认升序
	select * from emp order by sal desc; -- 降序
-- 按照字母
	select * from emp order by ename asc; -- 升序
	select * from emp order by ename desc; -- 降序
-- 按照多个字段排序(排序是有顺序的,先按照前面字段排序,如果前面字段相同再按照后面字段进行排序)
	-- 按照部门的编号升序,工资的降序
	select * from emp order by deptno, sal desc;
-- 按照名字的升序,工资的升序
	select * from emp order by job, sal, ename;

(9)统计COUNT

count(字段) 会忽略null值
	select count(comm) from emp;

(10)求最大值 MAX

select max(sal) as maxsal from emp;

(11)求最小值 MIN

select min(sal) from emp;

(12)求和 SUM

select sum(sal) from emp;

(13)求平均值 AVG

select avg(sal) from emp;

(14)去重 DISTINCT

distinct 去掉重复值, 在字段的前面加上distinct
	select deptno from emp;
	select distinct deptno from emp;

(15)分组查询 GROUP BY

-- 按部门统计人数
	select deptno, count(*) as tatals from emp group by deptno;
-- 求部门的平均工资
	select deptno, avg(sal) as avgsal from emp group by deptno;
-- 求每个部门的最高工资
	select deptno, max(sal) as maxsal from emp group by deptno;
-- 求每个部门的最低工资
select deptno, min(sal) as minsal from emp group by deptno;

-- group by 按照字段分组进行结果统计
-- having 对统计结果进行筛选
-- order by 排序
-- 如果三者一起使用: 顺序是先group by 再having 最后order by

-- 求出部门平均工资大于2000的部门并按平均工资进行降序排序
	select deptno, avg(sal) as avgsal from emp group by 
		deptno having avg(sal)>2000 order by avg(sal) desc;

(16)伪表 DUAL

-- 常用系统函数 dual表示伪表

	-- 求绝对值 ABS
		select abs(-50) from dual;
		select abs(4) from dual;

	-- 求平方根 SQRT
		select sqrt(9) from dual;

	-- 求幂 POWER
		select power(2,3) from dual;
		select powet(2,7) from dual;

	-- 四舍五入 ROUND
		-- 去整
			select round(3.1415926) from dual;
			select round(3.8111) from dual;
		-- 保留有效位数
			select round(3.8222, 3) from dual;
			select round(3.8262, 2) from dual;

	-- 字符连接 CONCAT
		select concat(‘青青’, ‘你好’) as ename from dual;
		select concat(‘jeffrey’, ‘你好’) as ename from dual;


	-- 截取字符串 SUBSTR(c1, n1, n2)
		-- c1表示字符串, n1表示从第几位开始(起始值是1), n2表示截取多少位(如果没有n2值就一直截取到末尾)
			select substr(‘jeffrey’, 1, 3) as ename from dual;
			select substr(‘jeffrey’, 2) as ename from dual;
			select substr(‘  zzz  ’, 5) as ename from dual;

		-- 替代 REPLACE
			select replace(‘your love you’, ‘your’, ‘I’) as ename from dual;
			select replace(‘zzz南生与我 很聪明’, ‘很聪明’, ‘真的很聪明’) as ename from dual;

		-- 转换成大写 UPPER
			select upper(‘sdsjsfjs’) as ename from dual;
			select upper(‘JDKSsddJD’) as ename from dual;

		-- 转换成小写 LOWER
			select lower (‘DJFJSFdjfDJFS’) as ename from dual;

		-- 时间类型转换成字符类型 TO_CHAR
			select to_char(sysdate, ‘yyyy-mm-dd hh:mi:ss’) as times from dual;
			select to_char(sysdate, ‘yyyy’) as times from dual;

		-- 字符类型转换成时间类型 TO_DATE
			select to_date(‘2015-12-12’, ‘yyyy-mm-dd’) as times from dual;

		-- 对日期进行加减 ADD_MONTHS
			select add_months(sysdate, 1) from dual;
			select add_months(sysdate, -12) from dual;

		-- 求一个月里面的最后一天 LAST_DAY
			-- 求系统时间的月份的最后一天
				select last_day(sysdate) from dual;
			-- 求emp表里面的受雇日期的月份的最后一天
				select last_day(hiredate) from emp;

2、高级关联查询

(1)内连接

两张表使用join on进行关联查询,进行条件限制,显示出来的数据都是符合条件的.
-- 格式: table_A join table_B on (条件)
	-- 练习: 使用内连接查询员工表和部门表的信息
		select * from emp inner join dept on (emp.deptno = dept.deptno);

(2)外连接

当需求为要查询一张表中的所有数据, 另外一张表满足条件的数据时, 表A全部显示, 表B满足条件显示这种情况要使用外连接
驱动表(主) : 要显示全部数据的那张表, 从动表(副) : 满足条件数据的那张表
①左外连接:
	-- 格式: table_A left [outer] join table_B on 条件
	-- A是驱动表, B是从动表
		-- 练习: 查询所有员工信息及员工所在部门信息
			select emp.empno, emp.ename, emp.job, dept.deptno, dept.dname, dept.loc from emp left join dept on emp.deptno = dept.deptno;
②右外连接: