Oracle基础教程笔记（二）

SQL语言基础
1.DDL：create alter drop
   DCL：grant revoke
   DML：select insert delete update
2.alter table test add c number;
3.grant select on dept to tt;
4.revoke select on dept from tt;
5.update abc set b='ttt' where a='abc';
6.delete from abc where a='abc';
7.select sysdate from dual;
   select current_date from dual;
8.TO_DATE TO_CHAR TO_NUMBER
9.聚集函数：sum avg max min count
10.select user from dual;
11.select sum(decode(sex,'男',1,0)) 男人数,sum(decode(sex,'女',1,0)) 女人数 from e;
12.select a1,nvl(a2,'为输入') a2 from aa;
13.select distinct a1 from aa;不先是重复语句
14.create view myview as select * from dept;
15.序列是数据库中提供顺序生成的整数的对象，通常序列是由DBA创建和命名的。创建序列时可以定义的属性有最大值，最小值，增量和初始数。
16.select * from aa where a1 like 'a%';
17.内连接标准写法：select a.id,a.name,b.dept from test1 a join test2 b on a.id=b.id;
18.左外连接：左边的无条件显示出来，右面的有匹配的就显示，没有匹配的用空值。
19.无关子查询：select * from e where id in (select id from d);
    相关子查询：select * from e where id in (select id from d where id=e.id);
20.union合并 重复的数据只显示一条

安全管理
1.select user from dual;查看当前用户
2.grant select on scott.dept to test;
3.修改默认表空间：alter user table default tablespace users;
4.修改密码：alter user test identified by test1234;
5.锁定帐号：alter user test account lock;
                alter user test account unlock;
6.创建用户：create user qqq identified by qqq123
                default tablespace NEW_TS;
7.赋予connect权限： grant connect to qqq;
8.切换用户：conn /  as sysdba;
9.允许用户授权给他人：grant select on scott.dept to test with grant option
10.grant all on scott.dept to test with grant option;
11.删除用户：drop user test;
12.系统授权：grant create user to test with admin option;
13.创建角色：create role myrole;
14.给用户分配角色：grant myrole to test；