day02

[size=medium]oracle中的用户管理
grant update on emp to xiaoming --让xiaoming用户有更新emp表的权限

scott用户让xiaoming可以查询emp表，又让xiaoming可以把这个权限传递下去

--如果是对象权限用 with grant option

grant select on emp to xiaoming with grant option

grant select on scott.emp to xiaohong --xiaoming给xiaohong赋权emp查询

revoke select on emp from xiaoming
---如果scott用户收回xiaoming的权限 那么小红的权限也将被收回

--如果是系统权限用 with admin option
grant connect to xiaoming with admin option

6.用户口令的管理--profile

profile 是口令限制，资源限制命令的集合。当创建数据库的时候,oracle会自动创建名称为default的profile文件，如果建立的用户指定的profile选项，oracle会自动将default分配给用户。


一般用dba的身份去执行profile命令
a.账户锁定
用户最多尝试登陆3次，如果3次都错误，账户被锁定2天
1.创建profile：create profile lock_account limit failed_login_accounts 3 password_lock_time 2;

2.分配给用户:alter user tea profile lock_account;

b.给账户解锁
alter user tea account unlock;


3.让用户定期修改密码（终止口令）
10天修改一次密码，宽限2天
1.创建profile: create profile myprofile limit password_life_time 10 password_grace_time 2;

2:分配给用户
alter user tea profile myprofile;

4.口令历史
用户修改密码时，新密码不能与旧密码相同，旧密码的使用需在这次更改的10天后

create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time_10;

alter user tea profile password_history;

5.删除profile文件

drop profile password_history [cascade]


二、表的管理


表的命名：必须以字母开头，不能超过30字符，不能使用系统保留关键字


数据类型

1.字符型
char() 定长，最大2000字符，char(10),存入“小明”占了4个字符 ，其余6个用空格补齐

varchar2() 可变长度，最大4000字符 ，varchar(10),存入“小明”,其实就占了4个字符

2.数字型
number -10的38~~10的38
number(5,2)可以表示小数 有效位为5位，小数点后2位 范围：-999.99~999.99
number(5) 范围 99999~99999


clob (character large object) 存放大数据 最多4g

3.日期型
date --包含年月日时分秒 oracle中默认的格式是：DD-MON-YY 如：“12-1月-1997”

4.图片 blob 二进制数据，存放图片视频等，最大4g 一般以链接的形式保存图片视频数据，数据另外存放在一个文件夹，除非从安全角度考虑，才把图片视频置于数据库中。


如何建表？

create table student(
xh number(4),
xm varchar(20),
sex char(2),
birthday date,
sal number(7,2)
);


create table classes(
classId number(2),
cname vachar2(20)
);


修改表？
--添加一个字段
alter table student add(classId number(2));

--修改字段长度
alter table student modify(xm varchar(30));

--修改字段类型 或名称（表中如果有数据的话不建议）
alter table student modify(xm char(30));

--删除字段(开发中切忌此做法，会造成数据紊乱，添加字段OK)
alter table student drop column sal;

--修改表的名字
rename student to stu;


添加数据？
--向表中所有字段赋值
insert into student values(1,'小明','男','01-3月-99')
oracle 中默认的日期格式‘dd-mon-yy’ dd 日子（天） mon 月份 yy 2 位的年 ‘09-6 月-99’ 1999 年6 月9 日
修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）

alter session set nls_date_format='yyyy-mm-dd';
修改后我们可以插入自己喜欢的日期格式
insert into student values(2,'小明2','男','2011-12-10');

--插入部分字段
insert into student(xh,xm,sex) values(3,'lily','女')

--插入空值
insert into student(xh,xm,sex,birthday) values(4,'tina','女','2011-12-11');

--查询空值
select * from student where birthday is null;

delete from student --删除数据 表结构还在 写日志

--创建一个回滚点
savepoint a
delete from student;
rollback to a;
一个有经验的 DBA，在确保完成无误的情况下要定期创建还原点。


drop table student;--删除表的结构和数据

truncate table student --删除表的数据 不写日志 效率高


表的查询？

简单的查询语句
--查看表结构
DESC emp;
--查询所有列
SELECT * FROM dept;
--切忌动不动就用 select *
SET TIMING ON; --打开显示操作时间的开关，在下面显示查询时间。
CREATE TABLE users(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30));

INSERT INTO users VALUES('a0001', '啊啊啊啊', 'aaaaaaaaaaaaaaaaaaaaaaa');

--从自己复制，加大数据量 大概几万行就可以了 可以用来测试 sql 语句执行效率
INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users;


SELECT COUNT (*) FROM users;---统计行数


--查询指定列
SELECT ename, sal, job, deptno FROM emp;

--如何取消重复行 DISTINCT
SELECT DISTINCT deptno, job FROM emp;

--查询 SMITH 所在部门，工作，薪水
SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH';

注意：oracle 对内容的大小写是区分的，所以 ename='SMITH'和 ename='smith'是不同的


--使用算术表达式 nvl null
问题：如何显示每个雇员的年工资？
SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp;

--使用列的别名
SELECT ename "姓名", sal*12 AS "年收入" FROM emp;

-- 如何处理 null 值
使用 nvl 函数来处理

-- 如何连接字符串（||）
SELECT ename || ' is a ' || job FROM emp;

结果如下：
ENAME||'ISA'||JOB
-------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD is a SALESMAN
JONES is a MANAGER
MARTIN is a SALESMAN
BLAKE is a MANAGER
CLARK is a MANAGER
SCOTT is a ANALYST
KING is a PRESIDENT
TURNER is a SALESMAN
ADAMS is a CLERK
JAMES is a CLERK
FORD is a ANALYST
MILLER is a CLERK

--使用 where 子句
问题：如何显示工资高于 3000的 员工？
SELECT * FROM emp WHERE sal > 3000;

--问题：如何查找 1982.1.1 后入职的员工？
SELECT ename,hiredate FROM emp WHERE hiredate >'1-1 月-1982';

--问题：如何显示工资在 2000到 3000 的员工？
SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000;


--如何使用 like 操作符
%：表示 0 到多个字符 _：表示任意单个字符

问题：如何显示首字符为 S 的员工姓名和工资？
SELECT ename,sal FROM emp WHERE ename like 'S%';


如何显示第三个字符为大写 O的所有员工的姓名和工资？
SELECT ename,sal FROM emp WHERE ename like '__O%';

在 where 条件中使用 in
问题：如何显示 empno 为7844, 7839,123,456 的雇员情况？
SELECT * FROM emp WHERE empno in (7844, 7839,123,456);

--使用 is null 的操作符
问题：如何显示没有上级的雇员的情况？
错误写法：select * from emp where mgr = '';
正确写法：SELECT * FROM emp WHERE mgr is null;

-- 使用逻辑操作符号
问题：查询工资高于 500 或者是岗位为 MANAGER 的雇员，同时还要满足他们的姓名首字母为大写的 J？
SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%';

--使用 order by 字句 默认 asc
问题：如何按照工资的从低到高的顺序显示雇员的信息？
SELECT * FROM emp ORDER by sal;


--问题：按照部门号升序而雇员的工资降序排列
SELECT * FROM emp ORDER by deptno, sal DESC;

--使用列的别名排序
问题：按年薪排序
select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc;
别名需要使用“”号圈中,英文不需要“”号 [/size]