《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.增删改查基本语法:
增:insert into e_product(id,name,price) values(10001,'红米手机',599);
删:delete from e_product where id = 10001;
改:update e_product set name='小米电视',price = 2999 where id=10001;
查:select id,name,price from e_product where id = 10001;
二、数据库函数
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---行级锁 当关闭连接或提交的时候释放锁;
面朝大海,春暖花开。