Oracle数据库知识和SQL语句总结

Oracle数据库：存放数据的仓库

1)保存数据
– file是轻量级保存数据。
– 数据库是重量级保存数据需要安装软件。

2)管理数据
有效增删改查(CRUD)，使用SQL语句来操作。

3)在项目开发中，运行在项目的后台。
项目分前台和后台。前台做界面。
如：前台注册界面
后台保存数据—保存到数据库中

数据库分为
**大型数据库：**oracle，db2 银行系统(数据量大，安全性高，需要付费)
**中型数据库：**sql server，mysql 网站开发(数据量大，安全性相对不高，免费的)
小型数据库： access，sqlite(数据量小，安全性低，免费的)

Oracle是一门数据库，
数据库:存放数据，有效的管理数据CRUD。 重量级保存数据的方式，需要安装软件。
总结一下简单的CRUD的操作

sql命令分类：
数据操作语言:DML
select,insert,update,delete

数据定义语言：DDL
create,alter,drop,rename,truncate

事务控制语句
commit,rollback,savepoint

数据控制语言：DCL
grant(授权),revoke(回收权限)

数据库中的对象：
表：数据库最基本的结构

视图：一张虚表，从一张表或多张表查询的数据组成的一张虚表。

序列：主要用来生成主键的值

索引：主要用来提供检索的效率

同义词：为对象取别名

存储单元：PL/SQL，将一条或者多条SQL语句通过一些语句(判断，循环)连接起来。

---

基本指令：

1)使用sys用户登录到数据库

sqlplus "/as sysdba"

2)使用system用户登录到数据库

 sqlplus 用户名：system口令：安装oracle数据库时的口令使用briup用户登录到数据库

**3)**sqlplus briup/briup

show user安装oracle后，默认提供两个管理员的用户sys,systemsys:相当于总裁system:相当于总经理briup:普通用户

4)切换到其他用户下

conn 用户名/密码

//创建用户：用户名为briup,密码为briup

create user briup identified by briup;

//授权命令

grant connect,resource to briup;

//切换用户

conn briup/briup

//修改日期的语言格式

alter session set nls_date_language=english;

//执行脚本文件

start D:/oracle.sqloracle.sql的路径：D:/oracle.sql

//查询当前用户下所有的表

select table_name from user_tables;

---

重点部分，SQL语句
sql执行流程:sql引擎解析sql语句
select———-5
from————1 //from后起的别名是可以作用到其他所有语句上的
where———–2
group by——–3
having———-4
order by——–6
查询表select
1、格式：select [distinct] {*|字段名1,字段名2..}
from 表名
[where 字段名=？ and …]

项目中不建议写*,效率低
1)先查询表中所有的字段名
2)再根据字段名去查询数据
2、 可以使用算术符 + - * /
a)针对数字类型number(+-*/)
例如：查年薪

         select salary*12         from s_emp;

b)针对日期类型date(+-)
select start_date,start_date+1
from s_emp;
3、取别名
a)在需要取别名的字段后空格，然后加上别名

    select last_name,(salary+100)*12 sal    from s_emp;

b)使用as关键字

   select last_name,(salary+100)*12 as Sal   from s_emp;

c)使用”“,可以区分大小写和特殊字符

select last_name,(salary+100)*12 "Sal"   from s_emp;

   select last_name,(salary+100)*12 "年薪"   from s_emp;

4、数据库处理空值

nvl(可能出现空值的字段,默认值)如果可能出现空值的字段为空，nvl函数处理的结果为默认值。如果可能出现空值的字段不为空， nvl函数处理的结果为本身的值。

5、字段拼接：||

查询last_name,first_name取别名name```select last_name||','||first_name namefrom s_emp;```注意：字符串使用单引号

**6、**sql*plus工具中编辑命令

**a）**list/l ： 显示buffer中sql命令

b）/ ： 执行buffer中的sql命令

c）替换内容:c

先定位：1后替换：c/oldStr/newStr 如： c/names/nameselect last_namefrom s_emp;

d）追加内容：a

先定位：1后追加：a ,salary执行：/select last_name,salaryfrom s_empwhere dept_id=42;

e）插入一行：i / input

先定位：2后插入：i where dept_id=42执行：/

f）删除一行：del

先定位：3后删除：del

g）重写一行

select last_name,salaryfrom s_emp;重写的行号 重写的内容如：1 select first_name,dept_id    执行：/

**h）**sql*plus在文件的命令
save filename:把buffer中命令保存到文件中
如：save D:/test.sql
get filename:把文件中命令显示在工具上
如：get D:/test.sql
@ filename/start filename:执行脚本文件
如：start D:/test.sql
@ D:/test.sql
edit filename:将文件以编辑的方式打开
如：edit D:/test.sql
spool filename:将工具中所有内容都写入到文件中
如： spool D:/test1.sql
select last_name from s_emp;
show user
select * from s_dept;
spool off;
注意：spool必须和spool off一起使用。
exit:退出
I）设置字段的显示格式：col
select last_name,first_name,salary,start_date
from s_emp;
col last_name format a15;
col first_name format a15;
fomat 可以用 for 来代替
注意：format不能通过a来设置数字类型
col salary format a10;
select salary from s_emp;
清除salary显示格式：
col salary clear;

排序order by

    a)升序排序ASC,降序排序DESC    b)order by放在select语句最后的位置    c)order by后面跟需要排序的字段名再加排序规则        1)如果没有指定排序规则则按照默认排序(升序)        2)需要排序的字段,字段可以写字段名也可以写别名也可以写select后的序列号            如: select last_name,salary                from s_emp                order by 2 ASC;    d)字符串的比较规则是第一个字母比 相同则比第二个 以此类推 比较的是ASCII码    e)多个字段同时排序 用,隔开 先按照第一个字段排序 在第一个字段的值相同      则把相同数据按照第二个字段排序 以此类推    f)对于空值的排序规则:        空值被看成---无穷大    1.查询所有员工的last_name和工资,并对工资进行升序排列        select last_name,salary        from s_emp        order by salary ASC;    2.查询所有员工的last_name和工资,并对last_name进行升序排列        select last_name,salary        from s_emp        order by 1 ASC;    3.查询所有员工的last_name和工资,先按照salary进行降序排序,再按照last_name      进行升序排序        select last_name,salary        from s_emp        order by 2 DESC,1 ASC;    4.查询所有员工的last_name和提成,并对提成进行升序排序        select last_name,commission_pct        from s_emp        order by 2 ASC;

限定性的查询where

    a)将数据进行过滤筛选    b)where放在select语句的from后面    c)where后跟表达式        字段名 比较符 常量值    d)比较符        逻辑比较符= < >           不等于:  != <> ^=        SQL比较符            between..and..  :在小数和大数之间            in(list)        :在集合中  in(1,54,66,4,2)            like            :模糊匹配,包含某个东西            is null         :是空的        逻辑操作符            and            or            not    select name    from s_dept;    where    1.查看41号部门员工的信息        select last_name,salary,dept_id        from s_emp        where dept_id=41;    2.查询Ngao这个员工的信息        select last_name,salary,dept_id        from s_emp        where last_name='Ngao';    注意 : sql命令不区分大小写 字符串是区分大小写的    3.查询工资在1000到1500之间的员工信息    4.查询41号或者42号部门员工信息    5.查询提成为空的员工信息    6.查询last_name包含N员工的信息        通配符: %:匹配0或者多个字符                _:匹配单个字符        select last_name,salary        from s_emp        where last_name like '%N%';    7.查询出所有不分大小写的N        upper()将括号内容变为大写        lower()将括号内容变为小写        select last_name,salary        from s_emp        where upper(last_name) like '%N%';        select last_name,salary        from s_emp        where lower(last_name) like '%n%';    8.查询last_name第二个字符为a的员工的信息        select last_name,salary        from s_emp        where last_name like '_a%';    9.查询last_name以_开头的员工信息        先插入一列进行测试        insert into s_emp(id,last_name)        values(999,'_briup');        commit;        escape '任意字符'  :组合成了一个转义字符 自定义转义字符        select last_name        from s_emp        where last_name like '/_%' escape '/';

函数

单值函数: 不会影响数据的个数 如果有n条数据处理之后还是n条数据多值函数(组函数): 会影响数据的个数 如果有n条数据处理之后可能是1条数据1.在字符串上的单值函数    LOWER/lower:将字符串转换成小写    UPPER/upper:将字符串转换成大写    INITCAP/initcap:将字符串转换成首字母大写,其他字母小写    CONCAT/concat:将两个字符串拼接成一个字符串    SUBSTR/substr:求子串    LENGTH/length:求字符串的长度    NVL/nvl:处理空值的函数        dual:一张哑表            1)用来满足select语句的格式            2)用来测试函数        select concat('hello','world')        from dual;        //第一个是母串        //第二个是子串的起始位置        //第三个是子串的长度        select substr('hell world',3,7)        from dual;        练习:查询员工全名，使用，隔开                 全名以大写方式显示，并且要求first_name的长度为6，                按照全名进行降序排序                select upper(concat(last_name,first_name)) as name                from s_emp                where length(first_name)=6                order by name DESC;    2.在数值上的单值函数        ROUND/round:四舍五入        TRUNC/trunc:只舍不取        MOD/mod:取余        select round(45.89) from dual;  //46        select round(45.89,1) from dual;  //45.9        select round(45.89,2) from dual;  //45.89        select round(45.89,-1) from dual;  //50        select round(45.89,-2) from dual;  //0        select round(55.89,-2) from dual;  //100    3.在日期上的单值函数,注意：在进行日期函数之前 需要转换语言alter session set nls_date_language=english;        select sysdate from dual;        日期的默认格式:  DD-MON-YY        MONTHS_BETWEEN/months_between:两个日期之间相差的月数        ADD_MONTHS/add_months:在一个日期上加上月数之后的日期        NEXT_DAY/next_day:即将来临的星期几是多少号        select next_day(sysdate,6) from dual;        注意：以为Oracle是国外推出的，所以日期中  一周的第六天  是星期五        LAST_DAY/last_day:一个日期所在这个月的最后一天的日期        ROUND:四舍五入            根据month进行 应该看day            根据year进行 应该看month        TRUNC:只舍不取            根据month进行 应该看day            根据year进行 应该看month            select round(to_date('15-JUN-17'),'month')            from dual;            15日舍 16入 6月舍 7月入    4.转换函数        to_char:将数字或者日期转换成字符串            1)将数字类型转换为字符串类型(钱)                to_char(number,'格式')                $:西方人显示货币的符号                L:显示本地货币符号                ,:进行分割                .:小数点                9:不满位数时不强制补齐                0:不满位数时强制补齐                fm:清除前面的空格或者0(某些时候清除0)            注意:定义字符串的长度大于或等于数值的长度 否则会显示#                select to_char(salary,'$99,999.00')                from s_emp;            2)将日期类型转换成字符串类型                select to_char(sysdate,'YYYY MM D DD DDD YEAR MONTH MON DAY DY ddsp ddspth')                from dual;                select to_char(system,'YYYY-MM-DD HH24-MI-SS AM')                from dual;            日期格式模型的元素                year: 英文年份(例如:two thousand seven(2007年))                month: 英文月份(例如:march(3月))                mon:英文月份的缩写(例如:mar)                DAY: 星期几的英文单词(例如:monday(星期一))                DY: 星期几的前三个字母(例如:mon)                ddsp:英文拼写的天(例如:five(5号))                ddspth:英文拼写的第几天(例如:fifth(第五天))                d: 一个星期的数字表示，星期天为1，星期六为7                dd: 一个月的第几天                ddd: 一年的第几天                yyyy:四位数的年                mm:二位数的月                hh24:24小时制                mi:分钟                ss:秒                AM:上午                PM:下午        to_number:将字符串类型转换成数字类型        to_date:将字符串类型转换成日期类型

多表查询

多表查询:第一件事将多张表连接成一张表连接:每张表的每条数据都要连接另一张表的每条数据实质：是一张表的查询多张表连接成一张表时会产生笛卡尔积如何消除笛卡尔积：如果有n张表的连接，至少需要多少个连接条件才可以完全消除笛卡尔积？    使用连接条件放在where中    如果有n张表的连接，至少需要n-1个连接连接方式：1）等连接：连接条件使用=连接    等连接：主键的值等于外键的值            外键值为空的数据是查询不出来的例一：查询所有员工的ID，名字和所在部门的名称     步骤一：分析查询的表             s_emp s_dept     步骤二：连接条件           s_emp.dept_id=s_dept.id     步骤三：写sql语句          select s_emp.id,last_name,name          from s_emp,s_dept          where s_emp.dept_id=s_dept.id;        注意:多表查询时,重名的字段需要表名指定             多表查询时,建议给表取别名             多表查询时,不要忘了写连接条件例二：查询员工的姓名和部门所在地区的名称(3张表)      步骤一：查询的表           s_emp s_dept s_region     步骤二：连接条件          s_emp.dept_id=s_dept.id          s_dept.region_id=s_region.id     步骤三：拼接sql语句         select s_emp.id,last_name,s_region.name         from s_emp e,s_dept d,s_region r         where e.dept_id=d.id and d.region_id=r.id;练习一：查询部门名称包含sa的员工姓名薪水练习二：查询欧洲销售部门的薪水在1000到2000的员工信息  练习三：查询部门名称是5位，该部门员工的薪   水不等于1500，并按员工的薪水降序排序        select e.last_name,e.salary,d.name  2  from s_emp e,s_dept d  3  where e.dept_id=d.id and length(d.name)=5 and e.salary!=1500  4  order by e.salary DESC;2）不等连接：连接条件使用的不是等号的连接            >,<,...between..and drop table s_gender;create table s_gender(    id number(5) primary key,    minSal number(7),    maxSal number(7),    name varchar2(20));insert into s_gendervalues(1,0,1000,'蓝领');insert into s_gender values(2,1000,1500,'白领');insert into s_gender values(3,1500,2500,'金领');commit;查询所有员工的工资等级？3）外连接：把外键为空的数据一并查询出来   使用：(+)  放在连接条件上   规则：把(+)放在查询数据少的一方例一：查询所有员工所在部门的名称，但是需要把所有部门给查询出来insert into s_deptvalues(1000,'teaching',2);commit;SQL> select e.last_name,d.name  2  from s_emp e,s_dept d  3  where e.dept_id(+)=d.id;右外连接：标准的sql：..right join...on....右外连接SQL> select e.last_name,d.name  2  from s_emp e right join s_dept d  3  on e.dept_id=d.id;例二：查询员工所在部门的名称，包括没有部门号的员工(所有员工)SQL> select e.last_name,d.name  2  from s_emp e,s_dept d  3  where e.dept_id=d.id(+);注意：等连接不能查询外键值为空的数据。左外连接：标准的sql：left join ...on...左外连接    select e.last_name,d.name     from s_emp e left join s_dept d     on e.dept_id=d.id;例三：查询员工所在部门的名称，没有部门号的员工所有的部门都查询出来    select e.last_name,d.name     from s_emp e full join s_dept d     on e.dept_id=d.id;标准的sql语句：...full join...on...(全连接)    所有数据库语言通用的SQL语句4）自连接：连接的时候来自同一张表           同一张表可以当作多张表使用。           from s_emp,s_dept,s_region            from s_emp ,s_emp, s_emp...   查询所有员工的经理的last_name,salary包括没有经理的员工。select m.last_name,m.salaryfrom s_emp e,s_emp mwhere e.manager_id=m.id;5)集合连接union:并集，重复的列只显示一行union all：并集，不会消除重复的行minus：差集intersect：交集rownum:伪列，可以分页技术,逻辑位置      只能等于1      不能大于或者大于等于任何正整数      可以小于或者小于等于任何正整数rowid:数据保存到文件中的物理位置部门表中前5条记录？    select *    from s_dept    where rownum<=5;部门表中第3条到第5条数据？    select *    from s_dept    where rownum <=5    minus    select *    from s_dept    where rownum >=2;    page:当前页    count:每一页的数据        

Group Function

    AVG/avg:平均    COUNT/count:求非空数据的个数    MAX/max:最大    MIN/min:最小    SUM/sum:和例一：查询所有员工的平均工资，最高工资最低工资，工资总和，以及有多少个员工?    select avg(salary),max(salary),min(salary),sum(salary),count(last_name)    from s_emp;例二：查询每个部门的平均工资？对平均工资降序排序.平均工资大于1400.简化1：求41号部分员工的平均工资？简化2：求每个部门的平均工资？简化3：平均工资大于1400.SQL> select avg(salary),dept_id  2  from s_emp  3  having avg(salary)>1400  4  group by dept_id;简化4：平均工资降序排序SQL> select avg(salary),dept_id  2  from s_emp  3  having avg(salary)>1400  4  group by dept_id  5  order by avg(salary) DESC;分组注意: a)group by 进行分组         b)将值相同的数据分为一组         c)group by 放在where 后面         *d)组函数不能放在where中,对组函数进行过滤用having. having放在group by后面         e)出现在select后的字段 如果该字段没有出现在组函数中那就必须出现在group by中         f)多个字段同时分组 多个字段的值都相同才分为一组练习：1.查看各个部门的最高工资 SQL> select max(salary),dept_id  2  from s_emp  3  group by dept_id;2.查看各个部门的员工数SQL> select count(last_name),dept_id  2  from s_emp  3  group by dept_id;3.查询各个部门各个职称的平均薪水和最大薪水,并且平均薪水大于2000的部门id；SQL> select avg(salary),max(salary),dept_id,title  2  from s_emp  3  having avg(salary)>2000  4  group by dept_id,title;4.查询title中不包含vp字符串的每个职位的平均薪水和总薪水，并对平均薪水进行降序排列，并且每个职位的总薪水大于5000?

子查询

一条sql语句嵌套一个或者多个sql语句格式：   select...   from..   where 字段名 比较符  (    select...    from..    where..    group by..    having...    order by...   )   group by...   having..   order by...什么情况下使用：比较符中参数不确定的情况子查询出现的地方：1）where子句               2）form子句               3）having子句例一：查询和Ngao在同一个部门的员工id,last_name    步骤一：分析所用的表            s_emp    步骤二：分析子查询            查询和Ngao在哪个部门            select dept_id            from s_emp            where last_name='Ngao';    步骤三：嵌套sql语句            select id,last_name            from s_emp            where dept_id =(                select dept_id                from s_emp                where last_name='Ngao'            );    子查询的结果要和外围查询的条件对应

未完..