数据库——oracle

搜鞥都哦

为什么要用数据库？　　
文件存储：
　　
1、不易于管理和分析（Linux操作系统下输入ls命令会卡死）
　　
2、不易于共享
　　
3、文件内容不断增大，不易于存储
　　
数据库：用数据结构来管理、存储数据的仓库
　　
DB：Database数据库
　　
DBMS：数据库管理系统
　　
数据库软件：Oracle  MySQL  db2    sql-server   sybase
　　
关系型数据库：由二维表组成
　　
非关系型数据库（NOSQL   not only  sql）：
　　
Web  高并发性mongodb   redis
　　
关系：二维表
　　
二维表：由行和列组成的表格
　　
行：Record一条记录信息
　　
列：字段（Feild） 属性
　　
SQL：（structured  query  language 结构化查询语言）
　　
Oracle甲骨文
　　
Java   sun ---> oracle收购
　　
Mysql   my ----> sun（2008年1） ---> oracle(2009年4)
　　
db2IBM
　　
Sybasesybase  C/S数据库
　　
Sql server  MS 微软
　　
现在是oracle的天下
　　
Oracle商业（收费）大型数据库个人使用
　　
Sql server
　　
Windows 商业（收费）
　　
Mysql开源免费  --->收费免费版本（不提供服务）
　　
一般中小型企业用mysql阿里用mysql
　　
移动、联通、电信都用oracle
　　

　　
数据库是以二维表的形式存储数据
　　
数据库客户端：sqldeveloper
　　
plsql developer（公司一般用）
　　
查看数据库里有哪些表：
　　
select * from all_tables;显示用户有权限看到的所有的表,包括系统表
　　
select *from user_tables;当前用户的所有表
　　
selet *from dba_tables;是管理员可以看到的数据库中所有的表
　　
数据库不难，提升效率难：如双11
　　
SQL：所有数据库基本通用的语言
　　
所有数据库都遵循一套规则
　　
主体语言：Java
　　
脚本：
　　
数据库：
　　
数据结构和算法：
　　
业务知识，业务框架：
　　
SQL:
　　
DDL:Data Definition Language数据定义语言（操作对象是表）
　　
关键字：CREATE:创建数据库对象  建表
　　
DROP：删表
　　
ALTER：改表结构
　　
TRUNCATE：删除表数据，不删除表结构
　　
DML:Data Manipulation Language数据操作语言（操作对象是表的记录）
　　
关键字：INSERT：插入数据
　　
DELETE：删除数据
　　
UPDATE：修改数据
　　
影响的数据，需要事物控制语句才能生效
　　
eg：一张表里两个字段的值
　　
Update** set coll=**,col=**;
　　
TCL:Transaction Control Language事物控制语句
　　
关键字：COMMIT：提交，确定把数据提交到数据库
　　
ROLLBACK：回滚，把影响数据的操作撤销
　　
SAVEPOINT：设置保存点，更有利于回滚到特定的场景
　　
DCL:Data Contro Language数据控制语句（操作对象是用户student）
　　
关键字：GRANT：赋权
　　
REVOKE：回收权限
　　
CREATE USER：创建用户
　　
企业中对权限管理非常严格：允许插数据，不允许删数据
　　
DQL:Data Query Language数据查询语句
　　
关键字：SELECT
　　

　　
oracle数据类型：
　　
1、数字类型（只用NUMBER）
　　
NUMBER（p，s）
　　
P表示总位数
　　
S：表示小数的位数
　　
可正可负，最多位数为38位
　　
INTERGER == NUMBER（38）
　　
例子：NUMBER（7,3）能表示的最大的数：9999.999
　　

　　
2、字符串类型
　　
CHAR（n）：固定长度的字符，定长字符串
　　
n可以省略，默认为1
　　
指定n，代表n个字节宽度，即使存储的数据的字节小于n，也占n个字节，其余的补空格
　　
最长只能存储2000个字节
　　
VARCHAR2（n）：变长字符串
　　
N表示字符串最大的长度
　　
Varchar2（100）只存储10个字符，实际上字符串占的内存和它自身长度是一致的
　　
如：varchar2（100） hello存储5个字节
　　
节省内存
　　
最长能存储4000个字节
　　
LONG：2GB
　　
CLOB
　　

　　
3、时间类型
　　
DATE
　　
默认‘DD-MON-YY（RR）’
　　
Systimestamp  时间辍  （时间毫秒）
　　
Sysdate 系统时间
　　

　　
DDL：
　　
CREATEcreate
　　
建表：
　　
Create  table  table_name(
　　
字段名1   字段类型，
　　
字段名2   字段类型，
　　
....
　　
);
　　
自动提交，不用COMMIT
　　

　　
all_tables:记录数据库所有的表
　　
//owner属有者，拥有者
　　
Table_name:表名
　　
user_tables:记录当前用户下所有的表
　　
//dba_tables:记录所有的表，包括系统表（现在不讲）
　　

　　
约法三章：
　　
1、同一个数据库，同一个用户下，只能有唯一的标识符，同名的表只能有一个；
　　
2、不要用别人的表；
　　
3、自己建了之后记得删掉
　　
Sql语句除了字符串里的内容，都要是英文的
　　
创建表：create  table  ly_student(
　　
name varchar2(20),
　　
id number(12),
　　
gender  char(1),
　　
birth  date
　　
);
　　
Number：默认38位
　　
查看表结构：desc wly_student;
　　
能够查看表的所有字段以及字段类型
　　

　　
往里面插入数据：
　　
Insert into table_name(字段名)   values（值）；
　　
字符串用单引号表示 ’ ’
　　
日期：to_date(‘2017-08-02’,’YYYY-MM-DD’)
　　
alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss';
　　
select sysdate-1,sysdate,sysdate+1 from dual;
　　
查找昨天，今天，明天这一时刻的时间，按指定格式输出
　　
插入：
　　
insert into ly_student(name,id,gender,birth)
　　
values(‘龙’,001,’M’,to_date(‘2017-08-02’,’YYYY-MM-DD’));
　　
精度或者位数超长，报错！
　　
Commit;//插入后要提交
　　
EMPLOYEE_ID NUMBER Primary Key：主键（唯一非空：值不能有重复）
　　
当前面赋值过的值，后面再次被赋值时，会报错，必须赋值
　　
查看表的内容：seletct * from wly_student;
　　
comment on table ly_student is ’student information’;
　　
comment on column ly_student.name is ‘student name’;
　　
comment on column ly_student.id is ‘student id’;
　　
comment on column ly_student.gender is ‘student gender’;
　　
comment on column ly_student.birth is ‘student birth’;
　　
完整的建表语句：
　　
建表的sql脚本及表和字段的详尽说明
　　
===============================================
　　
drop：删除表
　　
drop table ly_student;
　　
按照表名查找特定的表：
　　
select * from user_tables where table_name like '%LY_STUDENT&';
　　
like '%LY_STUDENT&' 可以换成=table_name
　　
需要注意的是：表名大写
　　
drop table ly_student;
　　
create  table  ly_student(
　　
name varchar2(20),
　　
id number(12),
　　
gender  char(1),
　　
birth  date
　　
);
　　

　　
Alter table table_name add(字段名 字段类型);
　　
alter table ly_student add(address VARCHAR2(100));
　　
Alter table table_name drop column字段名；
　　
Alter table table_name drop column address;
　　
删除字段时，要逐行删除该字段的数据，当数据量较大时，效率会比较慢
　　
Alter table table_name modify(字段名 字段类型);
　　
Rename  old_table_name to new_table_name;修改一个表的名字
　　

　　

　　
模型
　　
DDL语句  CREATE  drop  alter
　　
提交DB变量
　　
流程，提供
　　

　　
建表：
　　
1、emp：员工表
　　
id：员工idnumber(5)
　　
first_namelast_name     varchar2(20)
　　
Gender性别char(1)
　　
birth 生日date
　　
hiredate  入职时间date
　　
Deptid  部门idchar(2)
　　
2、Dept表：部门表
　　
Id：部门idchar(2)
　　
Name：部门名称varchar2(40)
　　
插入三个部门，每个部门插入若干员工
　　

　　
建表加复制数据
　　
Create table table_name as select * from other_table_name;
　　
*表示复制所有字段
　　
Create table table_name as select id,name from other_table_name;可以
　　
Create table table_name as select id*2,name from other_table_name;不可以（因为other_table_name里面没有“id*2”这个字段名）
　　
表名不区分大小写
　　
修改字段的类型，（如果有记录可能报错）
　　
Alter table table_name modify column_name type;
　　
修改字段的名字
　　
Alter table table_name rename column old_column to new_column
　　

　　
DML:（所有操作只有不commit，都可以通过回退，得到原来的数据）
　　
Insert into  table_name(字段名)  values(值);
　　
csex CHAR(6) check(csex='男' OR csex='女'),//如果插入数据不是男或者女，会报错
　　
如果插入全表的字段，table_name后面可以省略字段名；
　　
如果是指定插入某些字段，或者值的顺序和字段名的顺序不一样，则需要字段名和值一一匹配。
　　
修改字段的值：
　　
Update table_name set
　　
字段名1=值1，字段名2=值2；
　　
全表所有记录的该字段的值都被修改了
　　
Update table_name set 字段=值 where condition;
　　
不带where条件的update请慎重！
　　
删除记录：
　　
Delete [ from ] table_name;
　　
全表的数据被删除，不带where条件的delete请慎重！
　　
删除指定数据：Delete [from] table_name where condition;
　　
Truncate:清空表数据，不会对表结构造成影响
　　
Truncate table table_name和delete table_name
　　
都可以把表里的数据全部清空（删除）
　　
1、truncate 不可以带条件，delete可以有
　　
2、Truncate 删除数据不可以修复，delete可以通过rollback将删除的数据回复回来（如果commit了，则回退不了）
　　
3、Truncate效率非常高，delete效率比较慢，如果实际中需要清空一张表，优先选择truncate
　　

　　
TCL：(transaction)
　　
Commit:提交
　　
Rollback:执行DML语句之后，只要没有commit;都可以回退；但是如果执行了commit，再去rollback都无法回退。
　　
DQL：select
　　
Select * from table_name;
　　
*代表所有字段都显示，查询tabble_name所有字段的所有记录
　　
Selete 字段1，字段2，...from table_name
　　
查询指定字段的信息
　　
字符串函数：concat   ||拼接字符串
　　
Concat(char1,char2)  concat可以连用
　　
注意转义字符 ’
　　
select concat(first_name,last_name) from ly_emp;  //拼接
　　
select first_name||' '||last_name name from ly_emp; //拼接,中间加空格
　　
select first_name||'‘’'||last_name name from ly_emp; //拼接,中间加 ’
　　

　　
dual 测试表
　　
Length：求字符串的长度
　　
char类型的长度是固定的,varchar2是根据字符串的长度
　　
Lower：将字符串小写
　　
Upper:将字符串大写
　　
Initcap：首字母大写，其余小写
　　
Trim(c1 from c2):把字符c1从字符串c2两端去掉
　　
Ltrm(c1 c2)：把字符c2从字符串c1左边去掉
　　
Rtrim（c1,c2）：把字符c2从字符串c1右边去掉
　　
Lpad(char1,n,char2)：把char1填充为n个字节，在左边补char2
　　
如果n小于length（char1），则会截断，右边截断，剩下左边
　　
Rpad：与lpad相反
　　
select rpad('abcd',7,'*') from dual; ---> abcd***
　　
translate(char1,char2,char3);把字符串char1里面和char2相同的字符，变成char3
　　
select translate('hello','le','WX') from dual; ----> hXWWo
　　
replace(char1,char2,char3):把char1中的char2子字符串换成char3字符串，如果没有char3，则表示去掉char2子字符串部分；
　　
Substr(char1,n,m)从字符串char1中，从第n个开始截取，截取m个字符
　　
n=0（或1）表示从第一个字符开始截取，n0，表示保留m位小数，四舍五入
　　
m 100
　　
Trunc(n,m):和round是一样，但方法不一样，直接舍弃
　　
Ceil(n):向上(大的)取整select ceil(-125.56) from dual; ---> -125
　　
Floor（n）：向下(小的)取整select floor(-125.56) from dual; ---> -126
　　
Mod(n,m):取余n%m：当m=0时，直接返回0
　　
函数可以直接作用于字段
　　
Emp(salary number(8,2) );
　　
Select salary*12 from number;
　　
To_date()；按格式把字符串转换成一个日期
　　
YYYY ---> 年； MM---> 月 ； DD ---> 日；
　　
Mon--->英文的月份；day --->星期几（1表示日）
　　
HH ---> 小时； HH24 ---> 24小时制；mi --->分钟；ss ---> 秒
　　
Add_month:select add_months(birth,3) from ly_emp;所有月份加3
　　
Last_day()：select last_day(birth) from ly_emp;返回月的最后一天
　　
Months_between(a,b)：select months_between(birth,hiredate) from ly_emp;    //两个日期相差的月份
　　
Next_day():select next_day(sysdate,1) from dual;//下周的星期日的日期To_char()：可以把时间和数字转换成字符串
　　
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;将当前时间转化成指定格式
　　
Trunc
　　
Round
　　

　　

　　

　　
Select:查询数据库的数据
　　
单表查询：
　　
1、查询所有字段所有记录：select * from 表名
　　
2、查询指定字段所有记录：select字段，...  form 表名；
　　
3、可以给字段取别名：
　　
Select 字段 字段别名，... from 表名
　　
4、可以用字符串、数字、日期函数对字段进行处理
　　
5、Select可以带条件用where来过滤数据
　　
1、指定字段的值  where  字段=值
　　
2、指定字段的值在某个区间，数字的值可以通过< 和 >来匹配，
　　
< >  != 来匹配不等于某个值的数据
　　
可以用and来连接两个有表达式，表示两个表达式都满足的情况下的所有记录
　　
可以用or来连接两个表达式，只要满足其中之一的表达式则匹配
　　
Select * from table_name where id=1;
　　
Select*from table_name where sal  800(或者sal !=800)
　　
Select * from table_name where sal>800 and sal=800 and sal10;
　　
2、查找部门编号大于10的部门平均工资：先过滤数据然后再组函数
　　
Select avg(sal) from emp where deptno>10 group by deptno;
　　
3、查找部门平均工资大于1600的部门编号及其平均工资：先用组函数求值(分组)，然后再过滤数据
　　
Select deptno,avg(sal) from emp group by deptno having avg(sal)>1600;
　　
Having：用来过滤分组数据，只能用在group by之后；
　　

　　

　　
SQL语句的执行顺序：
　　
1、from语句：从右往左，从后往前执行，建议把数据量少的放后面，数据大的表放前面；
　　
2、Where语句：从右往左，从下往上执行；建议把过滤数据量多的语句放后面；
　　
3、Group by语句：非常消耗资源，建议少用；建议在where的时候尽量过滤多的数据；
　　
4、Having语句：从左往右
　　
5、Select语句：oracle在解析*的时候，会先根据表名去查找这个表的字段（解析字段），需要消耗时间，所以尽量避免使用*，而是用具体的字段替代；
　　
6、Order by语句：从左往右，非常耗资源。
　　

　　

　　
多表查询：
　　
1、显示员工编号、员工姓名、员工所在部门编号及部门名称
　　
关联查询
　　
select * from emp;//14条数据
　　
Select * from dept;//4条语句
　　
Select * from emp,dept;//56条数据
　　
笛卡尔积：两个表数据的记录相乘得到的就是笛卡尔积
　　
等值连接
　　
Select empno,ename,emp.deptno,dname from emp,dept
　　
where emp.deptno=dept.deptno
　　

　　
多表查询：
　　
多个表之间用逗号隔开；如果两个表有同名的字段，需要显示同名的字段时，需要表名.字段名来区别，对于表，也可以有别名
　　
Select e.empno,e.ename,d.deptno,d.dname
　　
From emp e,dmpt d
　　
Where e.deptno=d.deptno;
　　
内连接：把等值连接中表名之间‘，’换成join，再把where换成on；
　　
Select e.empno,e.ename,d.deptno,d.dname
　　
From emp e join dept d
　　
On e.deptno=d.deptno
　　
外连接：
　　
左外连接：left join左边表里的数据一条都不会少（全部匹配），左边表也称为驱动表，然后根据条件去右边的表匹配，匹配不上的记录，该表的字段自动填充为null值。
　　
Select e.empno,e.ename,d.deptno,d.dname
　　
From emp e left join dept d
　　
On e.deptno=d.deptno;
　　
也可以写成：Select e.empno,e.ename,d.deptno,d.dname
　　
From emp e ,dept d
　　
Where e.deptno=d.deptno(+);
　　
//(+) 一定写在驱动表的对面
　　

　　
右外连接：
　　
Select d.deptno,d.dname,e.empno,e.ename
　　
From emp e right join dept d
　　
On e.deptno=d.deptno;
　　
也可以写成：Select d.deptno,d.dname,e.empno,e.ename
　　
From emp e , dept d
　　
Where e.deptno(+)=d.deptno;
　　
全外连接：
　　
Select d.deptno,d.dname,e.empno,e.ename
　　
From emp e full join dept d
　　
On e.deptno=d.deptno;
　　
自连接：对同一张表用别名的方式扩展为两张表，然后再进行关联匹配
　　
select distinct e1.empno,e1.ename
　　
from emp e1 , emp e2
　　
where e1.empno=e2.mgr;
　　
记住：外连接有个关键字outer；left outer join
　　

　　

　　
Sql高级查询：
　　
条件、表可能是从一个select语句中获得
　　
Select语句作为where的子句：
　　
可能where依赖的条件不是一个确定的值，也不是通过函数求得；依赖于一条select的返回值
　　
select语句中嵌套一个select语句，这个嵌入的select语句称为select子句
　　
Select 子句先执行，得到结果之后，再进行父select比较
　　
根据select语句的返回值，可以分为：
　　
单行select子句（= in < > != ...）
　　
多行select子句(ANY  ALL  in  not in)
　　
多列select子句（in）
　　
Select语句：返回一行数据，多行数据，多列数据
　　

　　
返回多条语句：
　　
ANY，ALL函数，不能单独使用，只能和< > = 一起使用
　　
导数据 --> 再建索引 会比直接导数据的效率要高
　　

　　
Create index deptno_index on dept(deptno);
　　

　　
User_objects;---索引、视图、序列均可在这个表里查到
　　
索引一旦建立之后，oracle自动维护，查询时不需要指定使用哪个索引
　　
Select * from emp where empno =**  ---empno
　　
Select * from emp where ename =**  ---ename
　　

　　
如果某个字段的值是唯一的，可以建唯一索引
　　
Create unque index empno_index on(empno);
　　
如果索引字段的值经常DML，可以用Alter index index_name rebuild;
　　
更新索引，将删除的数据的索引删掉，提高内存效率
　　
索引：建在那个字段，经常用来查询数据的字段，这个字段可以是组合字段，group by deptno，mgr
　　
      -------> On(deptno,mgr);
　　
在生产中，索引用的非常多
　　
索引：用空间换时间，消耗内存空间提升效率