Oracle
---创建Employees
create table Employees(
employeeID varchar2(6) primary key,
ename varchar2(10) not null,
Birthday date not null,
sex char(4) not null,
address varchar2(20) ,
zip varchar2(6),
Phonenumber varchar2(12),
Emailaddress varchar2(30),
departmentID varchar2(3) not null
);
---创建Departments
create table Departments(
departmentID varchar2(3) primary key,
departmentName varchar2(30) not null,
note varchar2(16)
);
---创建Salary
create table Salary(
employeeID varchar2(6) primary key,
income number(8,2) not null,
outcome number(8,2) not null
);
---------------------------------------------------------------------------
--插入测试数据
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010001,'王平',to_date('1981-01-05','yyyy-mm-dd'),'女','1');
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010003,'韦严平',to_date('1979-11-05','yyyy-mm-dd'),'女','2');
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010021,'吴庆红',to_date('1984-07-02','yyyy-mm-dd'),'女','3');
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010031,'李方',to_date('1980-8-4','yyyy-mm-dd'),'女','4');
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010005,'李明',to_date('1985-02-10','yyyy-mm-dd'),'女','5');
insert into employees
values(10091,'王平',to_date('1988-11-05','yyyy-mm-dd'),'女','上海','false','18270197894','liukangwen@163.com','1');
select * from employees where address is null;
--------
insert into Departments(departmentID,departmentName)values(1,'办公室');
insert into Departments(departmentID,departmentName)values(2,'人力资源');
insert into Departments(departmentID,departmentName)values(3,'销售部');
insert into Departments(departmentID,departmentName)values(4,'财务部');
insert into Departments(departmentID,departmentName)values(5,'生产部');
---------
insert into Salary values(010001,3000,100);
insert into Salary values(010003,3000,50);
insert into Salary values(010021,5000,50);
insert into Salary values(010031,3000,50);
insert into Salary values(010005,2500,50);
select * from Employees;
select * from Departments;
select * from Salary;
1、基本SQL SELECT语句
目标：sql select语句功能，
执行简单的悬着语句，
sql语言和sql*plus命令的不同
select * from Departments;--选择全部列 *
select departmentname from Departments;--选择特定的列
--SQL不敏感大小写，随便在几行书写，关键字不缩写不分行，子句要分行，缩进提高可读
sql*plus
算术运算符：数字和日期使用数学表达式 +-*/(符号优先级不用多说)
select income+200 from salary;
select income*2+100 from salary;
select income*(2+100) from salary;
--空值不是空值和0，是未指定的和null,数学运算时当作0
select income as "收入" from salary;--as用于别名可以省略，别名区分大小写
select income||outcome from salary;--将列于列，列与字符（不能中文）连接在一起
--中文不能直接||且要"",其它用''
select income || 'is a'|| outcome as "输出" from salary;
select income from salary;--默认有重复行
select distinct income from salary;--去重
desc[ribe] employees;--显示表结构，这里没成功可能被设置权限了吧
2、过滤和排序数据
--过滤
select * from salary where income > 4000.00;
--字符大小写敏感，日期格式敏感，都在''里
select ename,birthday from Employees;
--比较运算符：= > < >= <= <>
select * from salary where outcome <> 50.00;--不等于
--其它比较符：between..and..两者之间 in(set)等于其中一个值
--like(%，_) 模糊查询 is null 空值
select * from salary where income between 2000 and 4000;
select * from salary where income in(2500,2324,6999);
select * from employees where ename like '%平';--以平为尾
select * from employees where ename like '%平%';--包含平
select * from employees where ename like '平%';--以平开头
select * from employees where ename like '_平';--平前有一个字
--注意：#_可以和%一起用，但是必须是指定有几位
insert into employees
values(10091,'王平',to_date('1988-11-05','yyyy-mm-dd'),'女','上海','false','18270197894','liukangwen@163.com','1');
select * from employees where address is null;
select * from employees where address is not null;
--逻辑运算符and or not
select * from employees where departmentid>3 and address is null;
select * from employees where departmentid>3 or address is not null;
---优先级不记，括号提高就可以
select * from employees order by birthday;--默认升序asc,可省略
select * from employees order by birthday desc;--降序desc
select ename,sex,employeeid id from employees order by id;--用了别人而已，一样
select * from employees order by birthday,employeeid desc;--多列排序先列1再列2依次下去
3、单行函数
select * from Employees;
select * from Departments;
select * from Salary;
select UPPER(zip) from employees;--大写
select lower(zip) from employees;--小写
select initcap(zip) from employees;--第一个字符大写
select ename,concat(zip,'dui') from employees;--连接内容
select substr(phonenumber,8,11) from employees;--oracle是以第8位开始
select length(phonenumber) from employees;--长度
select instr(phonenumber,'9') from employees;--返回以第一个找到的下标为主
--下面两个没成功
select lpad(phonenumber,5,'*32') from employees;--左边字符用指定*32填充，总和位是5位
select rpad(phonenumber,9,'*') from employees;--不知为啥我的
select trim('1' from phonenumber) from employees;--去除指定内容
select round(45.926,2) from dual;--四舍五入，2正数表示还有几位小数，负数表示正数那边了
select trunc(45.926,2),trunc(45.926),trunc(45.926,-1) from dual;--截断
select mod(1600,300) from dual;--求余
--dual ：伪表，真实存在的表，为了方便数据严重而存在的临时表，dual表名
--日期 默认DD-MON-RR
select ename,birthday from employees where ename like '_平';
select sysdate from dual;
select sysdate-birthday from employees;--日期加减后是天数，返回天数
select sysdate+10 from employees;--日期与数字加减表示是天数，返回日期
select months_between('01-sep-95','11-jan-94') from dual;--返回月数
select add_months('01-sep-95',6) from dual;--增加月数
select next_day('01-sep-95','fri') from dual;--指定下一个礼拜几--加上几天到下个礼拜的时间
select last_day('01-sep-95') from dual;--本月最后一天日期
select round(sysdate,'month') from dual;--四舍五入按月天数
select round(sysdate,'year') from dual;--四舍五入按年月数
select trunc(sysdate,'month') from dual;--截断按月天数
select trunc(sysdate,'year') from dual;--截断按年月数
--转换函数
--to_number(数值类型的字符):将字符转换为数值
--to_char(数值或者是日期):将数值或者日期转换为字符
--to_date(日期格式的字符)：将字符转换为日期
--字符转数字
select to_number('123')+2 from dual;
--和java一样，字符可以看成基本类型一种，所以可以隐式转换--->'1000'=1000
--数字转字符
--9表示位置占位，例如999,999,999会将数字按照三个一组使用逗号隔开。
--L表示人民币符号，$表示美元符号
--0可以进行占位分组，但是如果真实数据位数不足，会使用0进行补位。
--,表示千位符 .表示小数点
select to_char(12345,'$999,999') from dual;--多几个999没关系
select to_char(123456,'l999,999,999') from dual;--大小写都可以
select to_char(123445234,'000,000,000.000') from dual;--0表示对应位数，多可少不行，少会变成#
--字符转日期--显示结果虽然不会变成你要的格式，但是符合要求的会列出来
select * from employees where birthday >to_date('1982-01-01','yyyy-mm-dd');
select * from employees where birthday >to_date('1982/01/01','yyyy/mm/dd');
select * from employees where to_char(birthday,'yyyy/mm/dd')>'1982/01/01';
--日期转换为字符 date--->char--说明-与/系统会解析，中文的话就要用"".
-- yyyy-mm-dd
-- yyyy/mm/dd
--'yyyy"年"mm"月"dd"日"
select * from employees where to_char(birthday,'yyyy-mm-dd')>'1982-01-01';
select * from employees where to_char(birthday,'yyyy/mm/dd')>'1982/01/01';
错误select to_char(birthday,'yyyy"年"mm"月"dd"日"') from employees ;
--单行函数可以嵌套
select ename,nvl(to_char(phonenumber,'$999,999,999,999,999'),'value is null')from employees;
--其它函数（适用于任何数据类型）
--nvl()：nvl(字段名，新的值)
--如果字段值不为null，则返回该字段的值。如果为null则返回新的值
--nvl2()：nvl2(字段名，处理1，处理2)
--如果字段值不为null，则执行处理1，为null执行处理2
--decode()：decode(字段名,值1，处理1，值2，处理2，值3，处理3,...,公共处理)--对null和空值以及不对呀不处理
--如果字段的值和decode中的条件值相同则执行对象的处理。--对象处理
--coalesce:为null就改为指定的值，部位null就为原有的值
--nullif(a,b):a=b返回null,否则返回a.--往往是字段相比较。
select nvl(address,'家里蹲') from employees;
select nvl2(address,'加里蒂','beijing') from employees;--不为空处理1，为空处理2
select decode(address,'加里蒂','beijing','null','beijing1','上海','北京') from employees;
select coalesce (address,'加里蒂') from employees;
select ename,nullif(zip,'false') from employees;
--条件表达式if-then-else表达式有下列2种
--decode函数
select ename,decode(ename,'王平', 2*departmentid,'韦严平',3*departmentid)from employees;
--case表达式--注意每个地方都要改变我，不能直接departmentid
select ename,case ename when '王平' then 2*departmentid
when '韦严平' then 3*departmentid
else 1*departmentid end as "部门"
from employees;
--多表查询
--别名加以区分以及方便
select * from Employees;
select * from Departments;
select * from Salary;
--cross join 笛卡尔
select * from employees e cross join salary s where e.employeeid=s.employeeid;
--多表连接--n表至少n-1个连接条件
select e.ename,d.departmentname,s.income from employees e,departments d,salary s
where d.departmentid=e.departmentid and s.employeeid = e.employeeid;
--非等值连接
select e.ename,d.departmentname,e.birthday from employees e,departments d
where birthday between to_date('1985-01-01','yyyy-mm-dd') and to_date ('1988-01-01','yyyy-mm-dd');
--外连接符号是（+）--左右外连接
select e.ename,d.departmentname from employees e,departments d
where d.departmentid(+)=e.departmentid;
select e.ename,d.departmentname from employees e,departments d
where d.departmentid=e.departmentid(+);
--自连接--一般连接有重复用distinct，不等的话查查出多表值会有多个值，这时候看情况写
select e.ename,e1.birthday from employees e,employees e1
where e.birthday=e1.birthday;
--1999的语法就有join
from table1
[cross join table2] | [natural join table2] | [natural join table2]
| [join table2 on ...]|[left|right|full outer join table2 on...]
--叉集和笛卡儿积相同--cross join
select ename,departmentname from employees cross join departments;
--自然连接--natural join,必须有相同名字列，且数据类型相同，自动去重
select ename,departmentname from employees natural join departments;
--natural join 和using互斥使用，后者可以指定等值连接中要用的列（可以多列）
select e.ename,d.departmentname from employees e join departments d using(departmentid);
--on子句进行多表连接
--内连接与外连接
--外连接
--左外left [outer] join
select e.ename,d.departmentname from employees e left join departments d
on d.departmentid=e.departmentid;
--右外right [outer] join
select e.ename,d.departmentname from employees e right join departments d
on d.departmentid=e.departmentid;
--全外 full [outer] join
select e.ename,d.departmentname from employees e full outer join departments d
on d.departmentid=e.departmentid;
--增加连接条件
select distinct e.ename,d.departmentname from employees e full outer join departments d
on d.departmentid=e.departmentid and e.ename='王平';--结果没看懂，有问题？？？？
--转义字符可以将特殊字符转为普通字符
select * from emp where ename like '%/_%' escape '/'
分组函数
GROUP BY进行分组
Having 过滤分组结果集
select * from Employees;
select * from Departments;
select * from Salary;
--个人认为组函数就是多行函数
--组函数忽略空值avg(income)，nvl不能忽略空值avg(nvl(income,0))
avg count max min stddev(标志偏离，略) sum
select avg(income),max(income),min(income),sum(income),count(income) from salary;
--group by 相同组值和在一起再根据要求运算
select sum(income) from salary group by outcome;--group by
select sum(income) from salary group by outcome,employeeid;--多列要同时满足多列分组。
--未包含组函数的列必须在group by中，如下面的outcome.
select outcome,sum(income) from salary group by outcome;
--组函数在having中使用，在where不能--如下
--在where中报错select outcome,sum(income) from salary where avg(income)>1000 group by outcome;
--在having就可以--自己-最好将having写在group by后面
select outcome,sum(income) from salary having avg(income)>1000 group by outcome;
select outcome,sum(income) from salary group by outcome having avg(income)>1000;
--嵌套组函数--必须在group by
select max(avg(income)) from salary group by employeeid;
--from-->where--->group by-->select--->having--->order by
--子查询
--1、在主查询之前执行一次完成
--2、查结果用于主查询需要的值域
--使用注意点：1、不能出现单行里出现多行子查询
-- 2、除非Top-N，不然不要再子查询使用order by
--单行子查询
select ename,sex,zip,birthday from employees
where sex=(select sex from employees where departmentid =2)
and departmentid > (select departmentid from employees where departmentid =2);
--在子查询中使用组函数
select ename,sex,birthday from employees
where departmentid =(select min(departmentid) from employees);
--子查询使用having子句--别忘了，多行无要在group by,group by 不与where
select departmentid,sum(employeeid) from employees
group by departmentid having max(birthday)>(select birthday from employees where employeeid=10001);
--非法使用子查询--如下：单行查询返回多行报错；因为=只能对一个值
select * from salary
where income =(select max(income) from salary group by employeeid);
--select max(income) from salary group by employeeid会返回多行，可能不同
--select * from employees where sex='女';返回多行，但是相同
--子查询空值，无返回值
select ename,birthday,sex from employees
where employeeid = (select employeeid from employees where address='北京');
--多行子查询
--多行比较符 in(等于任何一个) any(与任意一个值比较) all(与所有值比较)
--多行子查询使用any
select * from employees where
departmentid = any (select departmentid from salary where income > 3000) and (departmentid <> 500);
--多行子查询使用all
select * from employees where departmentid
= all(select departmentid from salary where income > 3000) and (departmentid <> 500);
--多行子查询空
select * from employees where
departmentid not in (select departmentid from salary where income > 3000) and (departmentid <> 500);
--sql*plus
--使用变量 &单个 &&两个
select * from employees where employeeid=&employeeid1111;
--字符和日期要加''
select * from employees where ename='&endame1';
--哪里都可以用变量
select &cloumn,sum(&dea) from &employees group by &cloumn &condition1 order by &a;
--预定义define,undefine结束定义-未成功
define ename1 = '王平';
select * from employees where ename=&ename1;
--&&解决同一变量重复赋值-未成功
select &&ename1 from employees order by &ename1;
--verify显示变量代替前后的sql语句---未成功
set verify on
select employeeid from employees where employeeid=&em;
--sql*plus环境看看就好
set system_variable value--控制当前会话
set echo on / show echo--显示当前设置
--set命令 arraysize,feedback,heading,long等，参数就算了，看看就好
--格式命令 column,ttitle,btitle,break---具体使用，pdf有，看看就好
--使用脚本创建报告--略
--数据处理--时刻注意不要单行得到多行而报错
--dml
select * from Employees;
select * from Departments;
select * from Salary;
insert into salary values(110001,3500,110);
--其实下面都对，只是建表时设置了不能为空
insert into salary(employeeid,income)values(120022,4333);
insert into salary(employeeid,income,outcome) values(120022,4333,null);
--插入指定的值--sysdate,或者自定义的to_date
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010101,'李四',sysdate,'男','9');
--从其它表中拷贝数据--需要省略values，字段要对应
--自己：由于values要省略select才有用，但是时部分，其它字段失效所以没用
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid)
'010131',select ename from employees where departmentname='办公室',sysdate,'男','10';
--由于有些值唯一，所以报错
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid)
select Employeeid,ename,Birthday,sex,Departmentid from employees where ename='王平';
--报错和上面一样，要完整的。--自己
insert into salary((select employeeid from employees),income)values(120022,4333);
--跟新
update employees set address='北京' where employeeid=10101;
update employees set zip='true';--不用where指定修改所有
--更新子查询
update employees set address=(select zip from employees where departmentid=5),
sex=(select sex from employees where ename='李四')
where departmentid =3;
update employees set departmentid = 7 where address='上海';
delete from a;--删除全部数据
delete from a where id=num;--指定删除某条数据
--delete子句
delete from employees where departmentid =
(select departmentid from departments where departmentid=2);
--如果该主键用于外键使用就会报错，典型的破坏数据完整性
delete from departments where departmentid=4;
--合并语句--判断插入和更新和在一起
merge into salary s using employees e on(s.employeeid=e.employeeid)
when matched then update set s.outcome=40
when not matched then insert values (100020,3432,200);
--数据库事务----？？？？待在mysql操作一遍
--事物部分组成：一个或多个DML,一个DDL,一个DCL
--以第一个DML开始，以下面其中一个结束（DDL或DCL（自动提交）,commit或rollback,异常终了，正常结束）
--回滚到保存点
savepoint a1;--或者用 start transaction
update salary set income =8000 where outcome=50;
rollback to a1;
commit ;
select * from Salary;
--自动提交：DDL,DCL,正常结束
--异常会自动回滚
--当DML（默认行锁）中，本用户能够通过回滚看到DML之前数据，其它用户只能等你操作完才能看到。----重
--提交之后，锁被释放，所有保存点被释放，回不去了，其它用户可以看到了。
--回滚会释放锁
--oracle会自动创建隐式保留点，用户可以通过commit或rollback结束事物
--读一致性：提供一个一致试图，各子用户dml不影响其他用户，查与修改互不等待
--锁：并行事物中避免资源竞争，事务结束前存在，。。。如避免用户操作等
-- 独占锁：屏蔽其它用户操作（排他锁）
--共享锁：允许其它用户操作 独占 ----
-- DML:表共享行独占
--queries:不需加锁
--DDL:保护对象定义
--创建和管理表
--数据库对象 描述
表 基本数据集合，有行和列组成
视图 从表中抽出的逻辑上相关数据集合
序列 提供规律数值
索引 提高查询效率
同义词 给对象起别名
--表和列命名规则略
--创建表：普通创建，引用其它表，不在同一数据库使用用户名前缀引用对象
create table dept(deptno number(2),dname varchar2(14),loc varchar2(13));
--数据字段:oracle自动创建的表
--查看用户定义创建好的表
select table_name from user_tables;
--查看用户定义数据库对象
select distinct object_type from user_objects;
--查看用户定义表，视图，同义词，序列
select * from user_catalog;
--查看表结构
--user_tab_cols 用户表列信息
--user_col_comments 列注释信息
--user_constraints 表约束
--user_cons_columns 约束中用户可访问列
--子查询创建表--加条件相当于插入数据，不加条件直接创建
create table salary80
AS select employeeid,income,outcome from salary where income=8000;
select * from salary80;
--alter table
alter table salary80 add job varchar2(20);
alter table salary80 drop column job;
alter table salary80 modify income varchar2(20);
alter table salary add job varchar2(20);
alter table salary set unused column job;--标记为不可用列(可以多列)，相当于删除该列，但是该列存在
alter table salary drop unused columns;--删除不可用列
drop table dept80--删除表（索引，提交，不能回滚。。）
--改变对象名称
rename salary1 to salary;
--清空表
savepoint a1;
truncate table salary;--删除所有数据，不可以回滚
delete from salary;--可以回滚
select * from salary; rollback to a1;
--给表添加注释
comment on table salary is '该表是薪资表';
--数据字典提共的查看注释
select * from all_col_comments;
user_col_comments all_tab_comments user_tab_comments
--约束
--not null unique primary key foreign key check
--约束定义在表级和列级，查看通过数据字典
create table a(column a, column b,,,
column d [column_constraint]
....... [table_contraint])
--not null
create table a(id, number(6),
last_name varchar2(20) not null,--系统命名
first_name varchar2(20) constraint a_first_name_afd not null);--自己命名
--unique
create table a(id, number(6),
first_name varchar2(20) constraint a_first_name_afd unique(id));
--primary key
create table a(id, number(6),
first_name varchar2(20) constraint a_first_name_afd primary key(id));
--foreign key--外键的值在其他表主键一定要有对应
create table a(id, number(6),
first_name varchar2(20)
constraint a_first_name_afd foreign key(id) references departments(dept_id));
--foreign key 约束关键字
foreign key :指定本表列 references:父表（列）
on delete cascade:父表列删除，子表对应列删除
on delete set null;设置本表某列为空
--check 约束
--level等伪列，sysdate等函数，舍弃其他列值都不允许；
--就是自定义只在本表中有用可以
name varchar2(20) constraint emp_name_df check (name <> 'null')
--约束能够添加和删除但是不能修改
--添加约束
alter table a add constraint a_daf_f primary key(id)
--删除约束--2
alter table a drop constraint a_daf_f;
altr table a drop primary key cascade;
--无效化约束 disable cascade
alter table a disable constraint a_daf_f cascade;
--激活约束enable,如果是激活unique和主键这系统会自动创建对应索引
alter table a enable constraint a_daf_f;
--级联约束
alter table test1 drop (column1,column2,,)cascade constraints;
--查询约束 user_constraints
--查询定义约束 user_cons_columns
--视图
--常见数据库对象
表 基本数据存储集合，由行和列组成
索引 提高查效率
视图 从表中的逻辑上的数据集合
序列 提供规律的数值
同义词 给对象起别名
--视图：控制数据访问，简化查询，数据独立，避免重复访问相同数据
--简单视图：一个表，DML可以有，分组和函数没有
--复杂视图：一个或多个表，DML看情况，分组和函数有
--创建视图
create view view_name as select * from salary;
describe view_name--公司这里desc没用
select * from view_name;
--根据子查询创建视图，顺带可以别名
create view view_name1 as select * from salary where outcome=50;
select * from view_name1;
--修改视图用create or replace view--as后面是修改后的视图内容
create or replace view view_name as select outcome o from salary where employeeid=10003;
--创建复杂视图--必须要有别名
create view view_name211 as select max(outcome) maxl1,employeeid from salary group by employeeid;
--视图使用dml注意点略
--规定视图范围有用--with check option
create or replace view view_name as select outcome o from salary where employeeid=10003 with check option 条件;
--屏蔽dml操作视图 --with read only
create or replace view view_name as select outcome o from salary where employeeid=10003 with read only;
--删除视图
drop view view_name;
--临时视图
sql中的子查询就是临时视图，临时视图不是数据库对象
--top-n：用于查询最大最小个数值
select rownum as rank,income from
(select income from salary order by income desc) where rownum <=3;
--其它数据库对象
--常见数据库对象
表 基本的数据集合，由列和行组成
视图 从表中抽出逻辑上的数据集合
索引 提高查询效率
序列 提供有规律数值
同义词 给对象起别名
--序列：自动提供唯一数值，共享对象，用于提供主键--自己类主键一样（具体不看）sequence
--索引：快速定位数据，减少磁盘I/O读取来提高查询速度，索引与表相互独立
自动创建索引：primary key 与 unique
手动创建：其它
create index sal_idx on salary(income);
select * from salary;
create index sal_j on salary(income,outcome);
什么时候索引：where常用的，表列多比较大，不能包含表达式
--查询索引 user_indexes 和 user_ind_colums
--基于函数的索引 --只能单行函数
create index s_idx on salary(upper(income));
--删除索引--该索引拥有者或者drop any index权限可以删除
drop index index1;
--同义词：方便访问其它用户对象，缩短对象名字长度
create [public] synonym a for object;
create synonym d_sum for salary;
drop synonym d_sum;
--控制用户权限
权限：
系统权限：对数据库权限--100多种，常见由dml用户，备份表等
对象权限：操作对象权限
方案：一组对象集合，如表，视图等
数据库安全性：系统安全性，数据安全性
--创建用户
create user lkw identified by 123456;
--创建用户之后就有权利
create session(会话) table(表)sequence(序列)
view(创建视图)procedure(创建过程)
--赋予系统权限
grant create session/table/sequence/view to lkw;
--创建角色，为角色赋予权限，将角色赋予用户
create role manager;
grant create session/table/sequence/view to manager;
grant manager to lkw;
--修改密码
alter user lkw identified by 111111;
--对象权限（对象拥有者有所有权限），有查询，更新等等等
grant select on salary to lkw;--分配查询权限给lkw。
grant insert,select,update on salary to lkw with grant option;
--给所有对象分配权限
grant select,insert ,update,delete on salary to public;
--查询权限分配情况
role|user_sys|tab|col|_privs|_made|_recd
--回收对象权限
revoke select|all on object from user|role|public
revoke select on salary from lkw;
--创建数据库连接
create public database link hq.acme.com using 'sales';
select * from emp@hq.acme.com--远程连接
--set运算符:
--通过举例--顺序影响结果，列要对应，显示第一个查询表，除了all都去重且按第一个查询表升序，
union(去重)/union all(不去重)并集--必须要有同样的字段
select employeeid from salary union select employeeid from employees;
intersect交集
select employeeid from salary intersect select employeeid from employees;
minus补集--左cu右
select employeeid from salary minus select employeeid from employees;
--时间日期略
--扩展group by--多组扩展--了解即可
--rollup产生n+1中分组结果-n为分组列
select sum(employeeid),income,outcome from salary group by rollup(income,outcome);
--cube产生2的n次方分组结果
select sum(employeeid),income,outcome from salary group by cube(income,outcome);
--grouping函数返回0/1,区分空值
select sum(employeeid),income,outcome,grouping(income),
grouping(outcome) from salary group by cube(income,outcome);
--grouping sets
select sum(employeeid),income,outcome from salary
group by grouping sets((income,outcome),(income,employeeid));
--复合列
select sum(employeeid),income,outcome from salary
group by rollup(outcome,(income,employeeid));
--连接分组集
select sum(employeeid),income,outcome from salary
group by employeeid,rollup(outcome),cube(income,employeeid);
--高级子查询--主查询没执行一行都要执行一次子查询
子查询:在主查询之前执行
select * from salary where outcome<(select avg(outcome) from salary);
--多列子查询
select * from salary where (outcome,income) in
(select outcome,income from salary where income=2500 and outcome=50);
--from中使用子句
select b.employeeid,a.income from salary a,
(select employeeid,address,sex from employees) b;
--exists--true就不在子查询找，反之接着找。
select * from salary where exists
(select outcome,income from salary where income=2500 and outcome=50);
--not exists 与上面想反
select * from salary where not exists
(select outcome,income from salary where income=2500 and outcome=50);
--with子句：避免select重复找（类mybits的sql）查找到放到临时表，提高查询效率、
with sal as(select * from salary where income>2500)
select * from sal where outcome>100;
--分级查询--树 略
--DML和DDL扩展 略
等值：=连接，值相同就可以，列出对应所有列包括重复行
自然：各表要有一样的字段名，去除重复
内：系统默认，inner可省，还可在后面加上where
自：一个表与自身
外：
-- Create table
create table TEST.TEST01
(
ID NUMBER not null,
NAME VARCHAR2(255),
PASSWORD VARCHAR2(255),
PHONE VARCHAR2(255),
EMAIL VARCHAR2(255),
CREATETIME VARCHAR2(255),
MODIFYTIME VARCHAR2(255)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column TEST.TEST01.ID
is '主键';
comment on column TEST.TEST01.NAME
is '姓名';
comment on column TEST.TEST01.PASSWORD
is '密码';
comment on column TEST.TEST01.PHONE
is '手机号码';
comment on column TEST.TEST01.EMAIL
is '邮箱邮件';
comment on column TEST.TEST01.CREATETIME
is '创建时间';
comment on column TEST.TEST01.MODIFYTIME
is '修改时间';
//自己建的主键失败应该。
INSERT INTO TEST.TEST01(ID,NAME,PASSWORD,PHONE,EMAIL,CREATETIME,MODIFYTIME)
VALUES(1,'张无忌',4343432,18209288933,'42342@qq.com','2001-06-11','2012-06-23')
delete from TEST.TEST01 WHERE ID=1
//--按顺序默认赋值，不写的说明是空值
INSERT INTO TEST.TEST01(ID,NAME,PASSWORD)
VALUES(12,'张无忌',4343432)
INSERT INTO TEST.TEST01 VALUES(2,'张2',null,null,null,null,null)--null和不填一样效果
SYSDATE--系统时间
Insert into a(hire_date) values(sysdate)--插入当前系统时间
INSERT INTO TEST.TEST01(ID,NAME,PASSWORD,PHONE,EMAIL,CREATETIME,MODIFYTIME)
VALUES(1,'张无忌',4343432,18209288933,'42342@qq.com',SYSDATE,'2012-06-23')
注意填值要对应
INSERT INTO TEST.TEST01(ID,NAME,PASSWORD)
VALUES(&ID,'&NAME',&PASSWORD)
从其他表插入数据：
INSERT INTO TEST.TEST01(ID,NAME,PASSWORD)
select id,name,password from test02 where name like ‘_lkw%’;
select * from test.test01;
insert into test.test01(id,name,password,phone,email,createtime,modifytime)
values(1,'刘康伟','42423','18270190987','129@qq.com',sysdate,sysdate+1);--天数+1
update test.test01 set name ='刘康雯';
alter table test.test01 modify (phone varchar(20));--修改表字段长度
alter table test.test01 modify (phone number(20));--报错，这里phone必须为空才可修改类型
alter table test.test01 rename column phone to phone1;--修改字段名
alter table test.test01 add jianli varchar(255);--增加一个字段
delete table test.test01 column phone1;--错误，语法是这样
alter table test.test01 constraint 约束名 foreign key(jianli) references;--错误，语法是这样
alter table test.test01 constraint 约束名 primary key(列名)
alter table test.test01 constraint 约束名 unique(列名)
alter table test.test01 constraint 约束名 default(内容) for 列名
alter table test.test01 constraint 约束名 check(内容)
alter table tablename
drop constraint 约束名
DML数据操纵语言： SELECT、UPDATE、INSERT、DELETE
DDL 数据库定义语言：CREATE、ALTER、DROP等
DCL 数据库控制语言：设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句
