数据库之oracle

一.oracle数据库：

1.登录时指定数据库：

cmd-> sqlplus system/orcl@orcl as sysdba;

查看当前使用的数据库名：
select instance_name from v$instance;
show parameter instance_name;

2.添加用户：

	创建用户时，需要具有管理员权限。sys，system
语法：
create user 用户名 identified by 用户密码[account lock| unlock]

3.修改密码：

alter user 用户名 identified by 新密码

4.修改用户锁定：

alter user 用户名 account lock;
修改用户未锁定：
alter user 用户名 account unlock;

5.授权命令：

创建session的权限：grant create session to qiku;
创建table的权限：grant create table to qiku;
创建针对表操作权限：grant insert,select,update,delete on table_name to qiku;

解除权限：
revoke create table from qiku;

查看当前用户的用户名：
select username from user_users;

查询当前用户所有的表：
select table_name from user_tables;

6.创建表语句:

create table table_name(
	column datatype not null,
	column2 datatype null
);

as 关键复制表：
create table stu2 as select * from stu;

scott：普通用户


create table student(
	stuno number not null primary key,
	name varchar2(20) not null unique,
	gender varchar2(2),
	age number,
	classname varchar2(20)
);

7.插入数据：

insert into student values(100,'张三','男',22,'大数据');

注意：插入数据之后要提交：commit

提交：commit
撤销：rollback

8.约束：

约束就是为了数据库的数据完整性的保证。
约束：
    1> 主键约束 
       创建主键来实现主键约束：确保主键值的唯一性
       primary key
       constraint pk_id primary key(id);
       修改表创建主键：
       alter table table_name add primary key(id);
       删除主键：
       alter table student drop primary key;
    2> 外键约束
       存在于主表和从表之间
       主表中主键值和子表中的外键值是对应的。
    3> 唯一性约束
       unique：确保当前字段值的唯一性，不能有重复

       创建惟一性约束
	CREATE TABLE USERS
	(
	       USER_ID NUMBER PRIMARY KEY,
	       USER_NAME VARCHAR2(20),
	       EMAIL VARCHAR2(100) UNIQUE
	)
	添加惟一性约束
	ALTER TABLE USERS ADD CONSTRAINT UQ_EMAIL UNIQUE (EMAIL)
	删除惟一性约束
	ALTER TABLE USERS DROP CONSTRAINT UQ_EMAIL
4.检查约束:
	作用：给字段指定值的范围，插入数据库的时候，值必须在指定范围，否则违反检查约束，无法插入数据。
	例：alter table student add constraint ck_gender check(gender in ('男','女'));
5.默认值和非空约束：
	默认值：为指定的字段设置一个默认值，当该字段没有输入数据的时候，会自动设置默认值。
	alter table student modify classname default '指定的默认值'
	非空约束：
	alter table student modify name not null
	空约束：
	alter table student modify name null

9.修改表结构：

1.修改列：字段
语法：alter table 表名 modify(列名 类型 约束);
alter table student modify(name varchar2(100) not null);
2.添加新的列：字段
语法：alter table 表名 add(列名 类型 约束);
alter table student add(email varchar2(50) null);
3.删除列：字段
语法：alter table 表名 drop column 列名
alter table student drop column email;

删除表：
drop table 表名
drop table student;

drop database db_name;

数据操作：
查询表中所有的字段：
select * from emp;
查询指定字段：
select ename,sal from emp;

查询带伪列的数据：需要给表指定别名 t
select t.*,t.rowid from emp t;

拼接字符串： || 
select '员工是：' || ename from emp;
查询并运算 + - * / ：
select ename,sal+1000 from emp;

别名： as 
select ename as 姓名 from emp
select ename 姓名 from emp
select ename as '姓名' from emp：oracle中别名不能加单引号

where条件查询：
查询工资大于8000的员工：
select * from emp where sal > 8000

查询1981年入职的员工：
select * from scott.emp where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd')
to_date(value,format); 把字符串转成日期类型
to_date('1981-10-10','yyyy-mm-dd');

10.oracle函数：

函数是数据库的完善程度的重要标识。
函数的语法：
function_name(column|expression,[arg1,arg2,…])

function_name：函数名
	count(exp);
	avg(column);
column:参数 字段
expression：字符串或表达式
args：参数列表

oracle的内置函数可以直接被调用。
scott.function_name(args);

字符串处理函数String：

UPPER(str) : 把小写字母转成大写字母
	dual：虚表/伪表
LOWER(str) : 把大写字母转换为小字母
Initcap(str): 把第一个字母大写，其余的转换为小写
concat('str1','str2')：拼接字符串
|| :拼接字符串
length('asdfsdf'):统计字符串长度
substr('str',1,lenth):截取字符串
replace('str','s','b'):替换自定的字符串
to_char(sysdate,'yyyy-mm-dd'):把日期类型转换成字符串类型

数值函数：Math
round(num) : 四舍五入方法
trunc(num,2):截取小说
mod(num,num2):模运算取余

日期函数：
sysdate：获取当前系统时间
months_between(sysdate,date)：获取指定的日期的范围有多少个月
add_months(sysdate,4);添加指定的月份
next_day(sysdate,'星期四'); 下一个星期四是几号
LAST_DAY(sysdate);查看当前月份的最后一天

round(d[,fmt])：返回一个以fmt为格式的四舍五入日期值
		如果 fmt为’YAER’ 四舍五入到某年的1月1日
		如果fmt为’MONTH’四舍五入到某月的1日
		如果fmt 为’DDD’ 四舍五入到某一天
extract(fmt from d) 从日期d中按照fmt格式提取值
		    fmt格式为 YEAR/MONTH/DAY/HOUR/MINUTE/SECOND
		    其中YEAR/MONTH/DAY可以与date类型和timestamp类型匹配，
		    但是HOUR/MINUTE/SECOND只能与timestamp匹配使用
例：
select extract(year from sysdate) as year,
       extract(month from sysdate) as year,
       extract(day from sysdate) as year,
       extract(hour from systimestamp) as hour,
       extract(minute from systimestamp) as minute,
       extract(second from systimestamp) as second
    from dual;

转换函数：
to_char：转成字符串
         to_char(sysdate,'yyyy-mm-dd') 把日期转成字符串
	 to_char(sysdate,'yyyy')获取年
	 to_char(sysdate,'mm')获取月
	 to_char(sysdate,'dd')获取日
to_number:把字符串转成数值，一般用于数学运算
	select to_number('123') + 345 from dual;
to_date:把字符串转成日期
	select to_date('2019-08-08','yyyy-mm-dd') from dual;

通用函数：
NVL(column,0):如果column的值为null那么它的值设置为0,如果不是null那么就是它的原值
         column == null?0:column
DECODE():多分支语句
	if..else if....else
案例：
SELECT empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期,
	DECODE(job,'CLERK',' 业务员','SALESMAN',
	' 销售人员','MANAGER',' 经理',
	'ANALYST',' 分析员','PRESIDENT',' 总裁')
	FROM scott.emp ;
组函数：
1.count(1) 统计表中总记录数
2.max(column)统计最大值
3.min(column)统计最小值
4.avg(column)求平均数
5.sum(column)求和

分组：
关键字：group by 
语法：
select 列名 from 表名 group by 列名 order by desc

having 子句：把分组函数作为调节的时候，需要使用having 子句
分组的原则：
只要一列中有重复的内容的时候，才考虑分组。

排序：
	order by 列名 asc/desc
	asc: 升序
	desc：降序

子查询：
	把一个查询的查询结构作为另一个查询的条件：

	select * from emp where emp = (查询语句)
	select * from emp where empno in (select empno from emp where empno = (select empno from emp where ename = ''));
	
	把查询的结果作为一个表进行查询：
	select * from (select * from emp) t where empno = (select empno from emp where ename = '');

	注意：所有的子查询必须在()中编写代码。

	ANY：关键字，针对多个值
	ALL：关键字，针对多个值
	案例：
	-- >any 比结果集中最小值大(800,950,1300) > 800都显示
	SELECT * FROM emp WHERE sal > ANY(SELECT MIN(sal) FROM emp GROUP BY deptno) ;
	-- >all 比结果集中最大值大(800,950,1300) > 1300大的都显示
	SELECT * FROM emp WHERE sal > ALL(SELECT MIN(sal) FROM emp GROUP BY deptno) ;

	-- < ANY:比里面最大的值要小 (800,950,1300)  < 1300
	SELECT * FROM emp WHERE sal < ANY(SELECT MIN(sal) FROM emp GROUP BY deptno) ;
	--  <ALL:比最小的值要小 (800,950,1300) < 800
	SELECT * FROM emp WHERE sal < ALL(SELECT MIN(sal) FROM emp GROUP BY deptno) ;

导入导出：
	指定表数据导出：
		exp scott/orcl@orcl file=d:/scoot.dmp log=d:/back.log tables=emp,dept
	全表导出：
		exp system/orcl@orcl file=d:/back.dmp log=d:/back.log full = y
	用户导出：
		exp scott/orcl@orcl file=d:/scoot.dmp log=d:/back.log

	导入：imp scott/orcl@orcl file = d:/sc.dmp tables=emp,dept

oracle中自动增长：
序列：oracle中针对主键的自动增长可以使用序列。
序列根据设定每次固定增长相同的数字。
curval:获取序列当前的值
nextval:获取序列的下一个值
语法：
create sequence emp_sequesces ：序列名
minvalue 1000 ：最小值
maxvalue 999999999 ：最大值
start with 1000 ：开始值
increment by 1; ：每次增长值

11.jdbc操作oracle数据库：

操作步骤：
1.创建java工程，导入oracle驱动jar包：jdbc-oracle6.jar / ojdbc6.jar 
2.加载oracle驱动
  Class.forName("oracle.jdbc.OracleDriver");
3.创建数据库连接对象
  Connection con = DriverManager.getConnection(url,user,password);
  url: jdbc:oracle:thin:@localhost:1521:orcl
  user:scott/system
  password:orcl
4.创建执行sql的预编译对象
  ps = con.prepareStatement(sql);
5.执行sql语句，返回结果
  ResultSet rs = ps.executeQuery();
6.遍历rs结果集
7.关闭资源：后打开的先关闭

12.JDBC+ORACLE实现客户关系管理系统：

CRM系统：
需求分析：
功能：1.登录，注册
2.客户管理：
查询
添加
修改
删除
创建表：
1.用户表
userinfo：
字段：id,username,password
create table userinfo(
id number not null primary key,
username varchar2(20) unique,
password varchar2(20)
);
2.客户表:
customer:
字段：id,name,gender,age,phone,email,address,company,conent
create table customer(
id number not null primary key,
name varchar2(20) not null,
gender varchar2(2),
age number,
phone varchar2(20),
email varchar2(20),
address varchar2(50),
company varchar2(50),
content varchar2(128)
);