《Oracle 数据库》

一、常用sql语句：

1.连接系统管理员账号：conn system/system(安装时口令);

2.创建新账号：create user Leo(账号名) identified by Leo(密码)；

3.给新账号授权：grant connect,resource to Leo;

4.从某个账户收权：revoke resource from Leo; 

5.连接新账号：conn Leo/Leo;

6.显示当前客户：show user;

7.查看表结构：desc s_emp(表名);

8.无需密码以dba身份登陆：conn / as sysdba;  //为所欲为的方式，危险

9.增删改查基本语法：

 

二、数据库函数

 

1.NVL(commission,0)；这个函数可以将一个字段（例：commission）中的null值替换成0，以便于运算。例如计算提成的时候，有些员工没有提成。

案例：列出每个员工的一年的总收入？(NVL)

select (NVL(commission_pct,0)/100+1)*12*salary from s_emp;

 

2.to_char(sysdate,‘yy‘):以自定义格式显示日期；

案例：找出入职时间是 90年的所有员工信息？

select first_name,start_date,to_char(start_date, ‘yy‘)from s_emp where to_char(start_date, ‘yy‘)=90;

 

3.lower(字段)/upper(字段): 将某个字段的值变成全小写/全大写，以匹配未知大小写的字符；

案例：当不知道‘Carmen’在数据库是大小写的时，找出‘Carmen’的工资？

select first_name ,salary from s_emp where lower(first_name) = ‘carmen‘;

 

4.substr(attribute，indexFrom,length):截取属性值为字符串的部分字符，从indexFrom开始，截取长度为length。indexFrom为负表示倒数第几个。

案例：列出每个员工名字（last_name）的最后两个字符？

select id,substr(last_name, -2, 2) from s_emp;

 

5.round(attribute,index): 求出某个属性的近似值， 四舍五入第index位。index为负数的时候表示小数点向左第几位，整数向右第几位。

案例：列出员工的工资的近似值，精确到十位？

select id,first_name, round(salary,-1) from s_emp; 

 

6.trunc(attribute,index);求出某个属性的近似值，去掉index位及之后的零头。index为负数的时候表示小数点向左第几位，整数向右第几位。

案例：列出员工工资的近似值，去掉100元以下的零头？

select id，first_name,trunc(salary,-2) from s_emp;

 

7.sysdate:获取当前系统时间。当前系统时间的格式收到本地化的严格影响，因此想要获取自己需要的格式必须通过tochar(sysdate,‘yy-mm-dd hh:mi:ss‘)获得；

案例：查出下一天、下一分钟、下一秒的时间

select to_char(sysdate,‘yy-mon-dd hh24:mi:ss‘) as now, to_char(sysdate+1,‘yy-mon-dd hh24:mi:ss‘) as nextday, to_char(1/(24*60)+sysdate ,‘yy-mon-dd hh24:mi:ss‘) as nextmin,to_char(1/(24*60*60)+sysdate,‘yy-mon-dd hh24:mi:ss‘)as nextSec from dual;

 

8.day:在日期中表示星期几。

案例：求某(今)天是星期几

select to_char(sysdate,‘day‘) from dual;

 

9.to_date(‘2015-11-09‘,‘yyyy-mm-dd‘):将字符串格式的日期转换成日期。

 

10.last_day(sysdate):获得给定日期的当月的最后一天的日期。

9和10案例：找出今年的天数

select  last_day(to_date(‘1215‘,‘mm/yy‘))+1 - to_date(‘010115‘,‘dd/mm/yy‘) from dual;

 

11.avg(attribute):某个字段值得平均数。

案例：列出提成的平均值？

select avg(commission_pct) from s_emp;

 

12.count(attribute) : 某个字段记录的个数；

案例：求出有提成的员工个数？

select count(*) from s_emp where commission_pct is not null;

 

13.group by :按照...分组。by后面可以跟多个字段，即按照多个字段分组。

案例1：求出各部门的平均工资？（单个字段）

select dept_id,avg(salary) from s_emp group by dept_id;

案例2：求出各部门不同职位的平均工资？（多个字段）

select dept_id,title,avg(salary) from s_emp group by dept_id,title;

 

14.having：过滤掉分组以后不想要的结果。

案例：求出各部门不同职位的平均工资且只显示平均工资高于2000的结果？

select dept_id,title,avg(salary) from s_emp group by dept_id,title having avg(salary)>=2000;

 

三、数据库关键字

 

1.distinct ：写在select中字段前面用于去重；

案例：select distinct name from s_dept;

 

2.between ... and ... : 某字段的值在一个闭区间【】之间；

案例：select * from s_emp where salary between 1500 and 2000;

 

3.in:某字段的值在一个枚举范围之中；

案例：select first_name,salary,dept_id from s_emp where dept_id in(31,42,43);

 

4.like：模糊匹配字符串，与_和%连用；

案例：select * from s_emp where first_name like ‘_e%‘;

 

5.escape：字符串中有些符号具有特殊含义，如下划线_表示模糊匹配中单个字符，但是如果仅想表示下划线则需要使用escape设置转义字符。

案例：select table_name from dba_tables where table_name like‘S@_%‘ escape ‘@‘;

 

6. is null:获得某个字段的值是空的记录；

案例：列出哪些员工没有提成？

select id,first_name from s_emp where commission_pct is null;

 

7.desc:按照某一字段降序排列，与order by连用。

案例：按工资降序显示员工的信息？

select * from s_emp order by salary desc;

 

8.asc:按照某一字段升序排列，与order by 连用。

案例：按提成升序显示员工的信息？

select * from s_emp order by commission_pct asc;

注意：null值按照“无穷大”位置排列；

 

9.order by ：排序。by后面可并列多个字段，优先级按照先后顺序。

案例：先工资降序，再按提成升序显示员工？

select * from s_emp order by salary desc,commission_pct asc;

 

 

四、多表查询

1.join...on...:将两个表连起来。on后面是两个表的关联条件。

案例：列出员工名字和部门名字？

方法1.select first_name||‘ ‘||last_name , name from s_emp e,s_dept d where e.dept_id = d.id ;

方法2.select e.first_name||‘ ‘||e.last_name ,d.name from s_emp e join  s_dept d on e.dept_id = d.id;

注释：Oracle中|| 表示加号，‘‘表示字符串。

 

2.三表关联查询（两种方法）

案例：列出‘Carmen’在哪个地区上班？

--方法一：（Oracle、sqlserver语法）

select r.name  

from s_emp e,s_region r,s_dept d

where e.dept_id = d.id and d.region_id = r.id and e.first_name = ‘Carmen‘;

--方法二：（数据库通用语法）

select r.name

from s_emp e join s_dept d on e.dept_id = d.id join s_region r on d.region_id = r.id

where e.first_name= ‘Carmen‘;

 

五、DDL数据定义语言

1.创建表：

CREATE TABLE E_CATEGORY 

(

  ID NUMBER(12, 0) NOT NULL 

, T_CATEGORY VARCHAR2(25) 

, CONSTRAINT TABLE1_PK PRIMARY KEY 

  (

    ID 

  )

  ENABLE 

);

 

2.向表中插入数据

INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘1‘, ‘智能手机‘)

INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘2‘, ‘数码相机‘)

INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘3‘, ‘笔记本电脑‘)

INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘4‘, ‘衬衫‘)

INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘5‘, ‘外套‘)

INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘6‘, ‘休闲裤‘)

 

 

3.修改表结构：添加一个字段；

alter table e_category add bigcate varchar2(25);

 

4.修改表结构：添加一个外键；

alter table e_product add constraint fk_product_bigcate  foreign key(p_category) references e_bigcate(id);

 

5.修改表结构：修改一个字段：

alter table e_product modify  p_cdate timestamp(8);

 

6.修改表结构：修改字段名；

alter table e_product rename column p_name to name;

 

7.修改表结构：删除外键：

alter table e_product drop constraint fk_product_category;

8.修改表结构：修改外键：

思路：先删除后添加

 

视图：

create view 视图名 as select ...

 

 

序列（sequence）：

min:max:increm://sequenc:currval:nextval;

 

index:索引：1.加快检索速度 

 

数据库的逻辑结构：数据库-表-段-区-块；

 

锁：for update---行级锁  当关闭连接或提交的时候释放锁；