Oracle数据库中的常用的方法及对表的操作

DDL数据库定义语言：create alter  drop
DCL数据库控制语言：grant revoke
DML数据库操作语言：select insert update delect 


1.创建表
create table 表名；


2.对表重新命名
rename 新表名 to 旧表名；


3.删除表
drop table 表名；


4.增加列
alter table 表名 add 列名 添加列的类型；


5.删除列
alter table 表名 drop column 列名；


6.insert 语句，向表中插入数据
insert into 表名（添加的列名，...）values（添加的值，和添加的表列一直）；


7.修改表（update）更新表
update 表名 set 更新列的内容 [where 条件]；


8.delete语句删除行
delect from 表名，[where 条件]


9.截断表--表被截断，表的结构还在，只是表的内容被删除
truncate table 表名；


10.select 查询语句
select * from 表名 [where 子句（查询的条件）]


11.列的别名
select sal as "员工工资" from 表名；


12.连接运算符（||）或 concat（arg1，arg2）；
select 列名1||列名2 from 表名；
或者
select concat（列名1，列名2） from 表名；

13.查询表的结构（关键字：describe）

describe  表名；

14.any运算符（满足任意一个即可）
语法：查询emp表，empno值只要大于列表(7369, 7521, 7499)中的任意一个值即满足条件；


15.all运算符 （满足全部的条件）
语法：在WHERE子句中使用ALL运算符与值列表中的所有值进行比较
 
16. like运算符 （对字符串进行字符串的匹配）
例如：匹配’_o%’，表示第一个字母任意，第二个是o，%表示最后可以是任意个字母
select * from emp where ename like '_M%';


17.in运算符（对应你查询的条件，一一对应）
例如：查询empno在(7369, 7521, 7499)中的员工
select * from emp
where empno in (7369, 7521, 7499);


18. between 运算符
  在where子句中可以使用between检查某列的值是否在一个指定的范围内。包含区间两端的值
 例如： 查询工资在800到1300之间的员工
select* from emp
wheresal between 800 AND 1300;


19.and运算符
   并且的意思


20.or运算符
  或者的意思


21.order by 对查询结果进行排序（order by 必须在from子句后或where子句后(如果有where子句)）
  查询EMP表，并对ename列进行排序
select * from emp order by ename;
（asc 升序排序 ， desc  降序进行排列）


22.distinct关键字 （删除重复的行）
  查询emp表中，删除重复的job值
select distinct job from emp;


23.concat（x,y）函数--将x和y拼接起来，并返回新的字符串。


24.initcap（x）函数--将字母字符串转换为每个词首字母为大写，其他字母为小写


25.instr（x,  find_string  [,  start]   [,occurrence]）函数
--返回指定字符串find_string在x中数字位置。可以指定开始搜索的位置start，并提供该字符串出现的次数occurrence。start和occurrence默认为1，表示从字符串开始的位置开始搜索，并返回第一次出现的位置;


例如，在emp表中的ename列，从1位置开始查找第2个’L’出现的位置
select ename, instr(ename, 'L', 1, 2) 
from emp where empno = 7499;


26.length(X) 函数--返回表达式中的字符数


27.lower(X)函数--将字母字符值转换为小写


28.upper(X)函数--将字母字符值转换为大写


29.ltrim(x[,trim_string])-函数--从x字符串左侧去除所有的trim_string字符串，如果没有指定trim_string字符串，则默认为去除左侧空白字符
  
30.rtrim(x[,trim_string])--函数--从x字符串右侧去除所有的trim_string字符串，如果没有指定trim_string字符串，则默认为去除右侧空白字符


31.trim(trim_string from x)-函数--从x字符串两侧去除trim_string字符串
 
32.nvl(x,value)-函数
  用于将一个null值转换为另外一个值。如果x是null值的话返回value值，否则返回x值本身


33.nvl2(x,value1,value2)
  如果x不为null值，返回value1，否则返回value2


34.replace(x,,  search_string,  replace_string)--函数--从字符串x中搜索search_string字符串，并使用replace_string字符串替换。并不会修改数据库中原始值。


35.substr(x,start [,length])-函数--返回字符串中的指定的字符，这些字符从字符串的第start个位置开始，长度为length个字符；如果start是负数，则从x字符串的末尾开始算起；如果length省略，则将返回一直到字符串末尾的所有字符
--例如，从EMP表中提取ename列从第2个字符开始，长度为3的字符串
select substr(ename, 2, 3) from emp；


36.to_char(x [,  format])-函数--将x转化为字符串。 format为转换的格式，可以为数字格式或日期格式


37.to_number(x,[,format])-函数-将x转换为数字。可以指定format格式


38.cast()函数--将x转换为指定的兼容的数据库类型


39.to_date(x[,format])--函数--将x字符串转换为日期


40.avg(x)函数--返回X的平均值


41.count(x)函数--返回统计的行数


42.max(x)函数——返回X的最大值


43.min(X)函数——返回X的最小值


44.sum（x）函数--返回X的总计值


45.分组函数（group by 列名）按列名进行分组
例如：统计emp表中所在部门，可以使用group by进行分组
select deptno  from emp group by deptno;


46.having 函数--可以使用having子句过滤分组后的行，--group by使用时可以不使用having ，但是使用having时必须有group by；


47.主键的约束 primary key；


48.unique关键字，唯一的约束；


49.default 默认约束；


50.check 检查约束；


51.constraint 创建指定名称的约束；


52.references  person（指定外键）外键
如果，不写在外键的后面 加 foreign key（外键名）；


53.on delete cascade --在创建外键约束时可以添加on delete cascade选项，那么当主表的数据被删除时，子表对应的行同样也自动被删除。


54.alter table 表名 drop constraint 约束名；
删除约束；


55.停用约束；
约束在创建完之后就生效了，也可以在创建时在constraint关键字末尾加disable关键字，默认停用它


56.user_constraints视图查看约束信息
例如：查看T4表的约束信息
select constraint_name, constraint_type, status, deferrable, deferred  from user_constraints
where table_name = 'T4';


57.查看列约束信息（user_cons_columns）
owner：约束的拥有者
constraint_name：约束名
table_name：约束所在的表
column_name：约束定义所在的列
例如：select owner,constraint_name,table_name,column_name
from user_cons_columns
 where table_name=‘T4';
 
58.多表查询
select 查询的内容 from 表1[],表2[]...
 where 条件 ；


59.--第六章 高级查询


--不消除笛卡尔积
select e.empno,e.ename,e.sal,d.dname from emp e,dept d


-- 要求查询出每一个雇员的  编号、姓名、工资、部门名称、工资等级
select e.empno,e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s
where e.deptno=d.deptno and sal>=losal and sal<=hisal--消除笛卡尔乘积的条件数量=查询的表数量-1


-- 要求查询出每一个雇员的  编号、姓名、工资、部门名称、工资等级
select e.empno,e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s
where e.deptno=d.deptno and sal>=losal and sal<=hisal and e.empno=7369 


select * from emp;
--等值链接   查询员工的姓名 和他的上司
select e2.ename 员工，e1.ename 上司 from emp e1,emp e2
where e1.empno=e2.mgr;


--内连接返回的行只有满足连接条件才会返回。如果连接条件的列中有NULL值，那么该行则不会返回  关键字：Inner join


select e.ename,d.dname from emp e inner join dept d
on e.deptno=d.deptno




--外链接 外连接返回的行满足连接条件，也包括在连接条件的列包含空值的行 
--左链接，关键字左边的都显示，右边的有对应的显示，没有对应的显示null  Left outer join,
select d.dname，e.ename from dept d left join emp e
on e.deptno=d.deptno


--右链接，关键字右边的都显示，左边的有对应的显示，没有对应的显示null right outer join
select d.dname，e.ename from emp e right join dept d
on e.deptno=d.deptno


--（+）也可以表示左链接 和 右链接--（+）一般放在子表的一端
select d.dname，e.ename from emp e , dept d
where e.deptno(+)=d.deptno
--------------------------------------------
--单行子查询
--查询比平均工资高的员工编号、姓名及工资
select empno,ename,sal from emp
where sal>(select avg(sal) from emp)


--要求查询工资最高的雇员信息
select * from emp
where sal=(select max(sal) from emp)


--使用ANY查询任意满足工资低于最低档工资的员工信
--用子查询进行多行查询
 select * from emp
 where sal<any(select sal from emp)
 
 --查询工资高于部门20中所有员工的雇员信息
 select * from emp
 where sal>all(select sal from emp where deptno=20)
 
 --使用子查询 对 多列进行查询
 
 --通过多列子查询查询出每个部门员工的最低工资，然后再查询出满足这些条件的员工信息
 select empno,sal from emp 
 where(deptno,sal) in( select deptno ,min(sal) from emp group by deptno)
 
 --查询工资高于SMITH工资的所有员工
 select * from emp 
 where sal>(select sal from emp where ename='SMITH')
 
 --查询工资高于所有部门的平均工资的员工
 select * from emp 
 where sal>all( select avg(sal) from emp group by deptno)
 
 --用 rownum 实现分页
 select * from (
select rownum m,empno,ename,job,hiredate,sal from emp
where rownum<=10
) temp    where temp.m>5


--
select * from (select rownum m,e.* from emp e) temp
 where m>5 and m<=10






--


--创建一个
create sequence tese1_id


--查找序列
select * from user_sequences;


--创建一个表
create  table tese2
(
id int primary key,
name char(20)
)
--对表进行赋值
insert into tese2(id,name)
values (tese1_id.nextval,'zs');


--查看表
 select * from tese2;
 select tese1_id.currval from dual;
 
 drop table tese2;
 drop sequence tese1_id;
 
 
 --索引 ----------------------------华丽丽的分割线-----------------------------------
 
 --创建一个表 进行测试
 
 CREATE TABLE customers (
  customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,--主键
  first_name VARCHAR2(10) NOT NULL,
  last_name VARCHAR2(10) NOT NULL,
  dob DATE,
  phone VARCHAR2(12)
);
--插入测试数据
INSERT INTO customers
VALUES (1, '张三', 'Brown', '01-1月-1965', '800-555-1211');
INSERT INTO customers 
VALUES (2, '李四', 'Green', '05-2月-1968', '800-555-1212');
INSERT INTO customers
VALUES (3, '王五', 'White', '16-3月-1971', '800-555-1213');
INSERT INTO customers
VALUES (4, '赵六', 'Black', NULL, '800-555-1214');
INSERT INTO customers
VALUES (5, '刘二麻子', 'Blue', '20-5月-1970', NULL);
 
select * from customers;
 


--查询姓名为 “Brown” 的人
SELECT customer_id, first_name, last_name
FROM customers
WHERE last_name = 'Brown';


--创建索引
create index i_customers_lasr_name 
on customers(last_name);--创建列的索引


--创建唯一索引 unique
create unique index i_customers_phone
 on customers(phone);
 
 --创建一个函数索引  创建一个函数索引的话，查询条件也必须是函数查询，否则不起作用
 
 --创建一个函数查询
 select first_name ,last_name from  customers
where upper(last_name) = UPPER('Brown');


--创建一个函数索引
create index fun_customers_last_name
on customers(UPPER('Brown'));


--查询索引信息 表名 必须大写
select * from user_indexes
where table_name='CUSTOMERS';


select index_name from user_indexes
where table_name='CUSTOMERS';


--修改索引（修改索引的名称）
alter index i_customers_phone
rename to i_customers_phone_number;


--删除索引
drop index i_customers_phone_number;


--创建视图索引
create bitmap index fun_customers_dob
on customers(dob);


select * from emp;

----------------