Oracle数据库登录命令用户管理建表修改字段数据类型约束增删改查

阳光穿透心脏的1/2处 2024-04-18 14:37 92阅读 0赞

什么是数据库？  
存储数据的仓库

优点:  
1.可存储大量数据  
2.方便检索  
3.保持数据的一致性、完整性  
4.安全，可共享

解决的问题?  
数据的持久化(把数据写到磁盘中)

安装看安装文档:

口令管理:  
sys/change\_on\_install 系统用户 为了方便密码可以不用change\_on\_install,而是用sys  
system/manager 系统用户  
scott/tiger 普通用户  
sh/sh 大数据用

判断是否安装安装成功:

1.查看数据库安装的服务都是否启动了  
2.开始菜单栏有很多数据库的图片  
3. 通过 sqlplus 登录普通用户

登录的命令:  
用户: systen/manager  
scott/tiger

普通用户登录:  
scott/tiger

登录详细操作:  
sqlplus scott/tiger@localhost:1521/orcl

用户切换:  
conn system/manager as sysdba;  
管理员用户简写:  
connn / as sysdba;

显示当前的用户:  
show user  
清屏的命令:  
host cls

用户的锁定:  
alter user scott account lock; 用户锁定了  
alter user scott account unlock 解锁

查表的操作:  
select \* from emp;  
设置输出的格式大小:  
set linesize 120;  
set pagesize 30

4张原始的表的介绍:  
emp 雇员表  
dept 部门表  
salgrade 薪资等级表  
查看当前用户的所有表"  
select \* from tab;

二:用户管理: (系统管理员管理用户)  
system /sys 系统管理员

1.创建用户:  
语法: create user 用户名 identified by 密码;

注意:  
1, 管理员才能创建用户  
2. 创建用户需要授权才能登陆 管理员要给用户授权

2.用户授权:  
用户 (张三)  
权限 删除,添加,查询,修改  
角色 管理员,普通用户,经理 (角色的目的更好的管理权限)  
数据库中的角色: connect resource dba;

授权: 可以给角色 获取权限     
    语法: grant 角色、权限 to 用户名;
    grant connect to root; //没有创建就表的权限 只有连接的权限 
    
    grant resource to root; // 可以处理资源的权限(建表 插入数据...)
      
    创建表的语句:
     create table t_stu(
        sid number(3),
      sname varchar2(20)
     );

回收权限:  
revoke 角色/权限 from 用户;  
revoke connect from root;

修改密码:  
alter user 用户名 identified by 密码;

用户的删除:  
drop user 用户名 cascade  
cascade(联机删除 把用户产生的数据也给删除)

– 表的操作  
java 中创建一个类

public class Person\{  
private int age; // 属性 数据类型 属性名称;  
private String name;  
\}

sql表也是类似:  
1.表的创建  
语法:  
create table t\_name(  
字段1 数据类型1(数据长度),  
字段2 数据类型2(数据长度),  
字段3 数据类型3(数据长度),  
…  
);

create table t\_student(  
sid number(4),  
sname varchar2(20)，  
sage number(2)

);  
CREATE TABLE T\_STU(  
SID NUMBER(3),  
SNAME VARCHAR2(20)  
);  
注意事项:  
1.oracle 是不区分大小,数据库在执行sql语句 会把我们的sql 小写转成大写  
2.如果表存在 不能重复创建  
3，每个字段使用逗号隔开,最后一个字段不加逗号  
4. 表的命名规则:t\_表名

1.  删除表:  
    drop table 表名 \[cascade consistent\];  
    drop table t\_stu;  
    – select \* from t\_stu;

3.表的字段添加  
alter table 表名 add 字段名 字段类型;

alter table t_student add ssex varchar2(2);

4.修改字段类型  
alter table 表名 modify 字段 字段类型  
alter table t\_student modify ssex number(1);

5.修改字段名:  
alert table 表名 rename column 源字段 to 新字段;  
alter table t\_student rename column ssex to sex;

6.删除字段  
alter table 表名 drop column 字段名;  
alter table t\_student drop column sex;

– 注释  
分类:  
1. 普通注释 –  
2. 表的注释

表的注释:  
               comment on table 表名 is '解释';
               comment on table t_student is '学生表';
              
             字段的注释:
               comment on column 表名.字段名 is '解释说明'; 
               comment on column t_student.sid is '学生id';
               comment on column t_student.sname is '学生姓名';
               comment on column t_student.sage is '学生年龄';

– Oracle中数据类型  
– 1.字符型

1. char 字符型[长度固定]
           能够存储最大长度是2000 个字节 
           系统会分配固定长度空间 不会应为数据大小而做出调整 节约计算空间的时间 提高效率
           
       2.  varchar2 字符型[长度是可变]
           能存存储最大长度是4000字节 
           相比char 内存利用更高  会计算实际存储长度动态分配内存
           
       3.long 字符型[大型字符]
            能存存储最大2G文本
          create table testChar(
              text1 char(4),
              text2 varchar2(4)，
              text3 long，
              text4 char，
              -- text5 varchar2(10)
          );  
         注意: char 可以不给长度 默认是1个长度 varchar2 必须给长度

–2.数据型  
number 数值型 可以存储小数 可以存储整数  
格式: number\[p(，s)\]  
p 整数 s表示小数  
number 默认长度是32位 最高精度38位  
number(5) 表示整数 长度是5  
number(5,2) 表示真整数是(5-2)位 小数2位 如果小数不够补0 如果小 太多了 四舍五入保留2位小数

create table testNumber(
             num1 number,
             num2 number(5),
             num3 number(5,2)
        );

–3.时间型  
date 日期 精确到秒 2019-9-7:11:22:33  
timestamp 时间戳 精确到 秒后面的6位 2019-9-7:11:22:33.000000

create table testDate(
           bir date,
           mytime  timestamp
        );

– 4.大数据类型 LOB  
blob 存储大型二进制文件  
clob 存储字符文件,类似 long

– 约束

1.  非空约束  
    非空 限定某个字段不能为空  
    create table 表名(  
    字段 字段类型 not null  
    );  
    create table testNull(  
    id number(3) not null,  
    name varchar2(10)  
    );  
    2.默认值的约束  
    如果某个字段没有值 可以设置一个默认值
    
    create table teseDefault(  
    id number(10) not null,  
    name varchar2(10) default ‘逍遥’,  
    age number(2) default 18  
    );
2.  唯一约束  
    唯一约束: 表示某个字段值在表中是唯一标识  
    create table testUnique(  
    id number(5) unique not null,  
    name varchar(10) default ‘老王你’  
    );  
    注意:  
    1.如果2个数据不为空 唯一约束不相等 （如果都为空数据是 可以插入）  
    2. 一个字段可以加入多个约束

4.主键约束 unique + not null  
表示是 设定的字段 是唯一约束不能为空  
主键是唯一标识符 (加快查询效率 自动创建索引)  
create table testPrimary(

id number(4) primary key,
          name varchar2(10) default '老宋'
    );
    
    联合主键:  
    create table testKey(
       id number(3),
       name varchar2(20)
    );
    
    -- 创建表后给testKey 添加唯一约束 
    alter table 表名 add constraint 主键标识符 primary key(字段名);
    alter table testKey add constraint pk_key  primary key (id);
    -- 多个字段联合起来组成主键 
    alter table testKey add constraint pk_key  primary key (id，name);
    
    注意:
      1.主键是唯一标识 一张表 只能有一个主键 
      2. 多个字段联合为一个联合主键 
      3. 一张表可有多个   unique + not null 但是只能有一个主键

5.外键约束:  
外键是从表中重复出现,在主表中作为主键存在 外键关联2张表  
主表: 主键的表 一方  
从表：外键的表 多方

多方关联一方 多方设置外键 
      班级  主表   一方      员工 多方  
      学生  从表  多方       部门 一方
       
     java中的对象模型:
     
     class Student{
         private int sid;
         private String name;
         private int ClassId;
           
     }   
     
     class Calss(
       private int Classid；
       private String className;
     );
    
    数据库中的模型:
    -- 从表
    create table t_stu(
         sid number(3),
         sname varchar2(20),
         classId number(4)，
         constraint fk_stu_class foreign key (classId) references t_class(cid)
    );

– constraint fk\_stu\_class foreign key (classId) 把 字段 classId声明为外键 references t\_class(cid)关联其他表的字段

– 主表  
create table t\_class(  
cid number(4) primary key，  
cname varchar2(10)  
);

– 创建表完成后添加外键  
alter table t\_stu add constraint fk\_stu\_class foreign key(classId) references t\_class(cid);

外键的特点:  
1. 外键可以在从表中重复出现 ，他在主表中是主键  
2. 一张表可以有多个外键  
3. 外键可以为null  
4. 外键的作用是关联多种表,可以建立表之间的关系 (一对一 一对多 多对多)  
5. 删除主表时候 如果 从表有使用主表的数据 是不能删除的， 从表的数据可以直接删除  
6. 2表关联了 先删除 从表 在删除主表  
7。 关键是多表查询单 必要条件

表的关系:
      一对一     老公--- 老婆
      一对多     部门--- 员工
      多对多     学生----老师    
       
    练习: 
    myemp 表 
      ename
      eno
      dno 
    mydept表
      dname
      dno 
    创建表 设置外键
    
    6.check约束:
       某个字段可以在一定的设置范围存储
       
       create table t_stu(
           sid number(5) primary key,
           sname varchar2(20)，
       --  sage number(2) check(sage>20)     
        -- sage number(2) check(sage = 18 or sage=20)   
           sage number(2) check(sage in (18,19,20))
       );
      
    check 约束可以给字段加运算符 和一些关键字: 
       > < = 
       and or 
       in / not in 
       is null / is not null 
    建表以后如何添加check约束 
    alter table t_stu add constraint ck_stu_age check(sage in (18,19,20));

– DML  
数据操纵语言用于插入、修改和删除数据  
数据操纵语言是最常用的SQL命令  
数据操纵语言命令包括：  
INSERT  
1,插入操作 表中出入一行数据  
方法1:  
insert into 表名(字段1，字段2…) values(val1,val1 …);

create table t_stu(
         sid number(2),
         sname varchar2(20),
         sage number(2)
       );       
       
       insert into t_stu(sid,sname,sage) values (1,'慕容复'，20);
       insert into t_stu(sid,sname,sage) values (1,'王语嫣'，20);
       
       insert into t_stu(sid,sname) values (1,'虚竹');
       commit;
       commit;
       
       注意:
          1. 字符串的插入用 '' 包裹 
          2. 插入数据在内存中 (事务提交)
          3. 几个表的就是几个val值
     方式2:
       插入所有的字段的时候 可以省略字段名 
       insert into 表名  values (val1,val2...)
       insert into t_stu values (3,'段正淳'，50);
       
     多表数据的插入:
      create table t_stu_copy(
         sid number(2),
         sname varchar2(20),
         sage number(2)
      );
    插入多条记录
    -- 把 t_stu 表中数据查出来  插入到 t_stu_copy 
     insert into t_stu_copy(sid,sname) select sid,sname from t_stu; 
    
    -- 复制表
    复制表结构和数据
    create table t_stu_copy1 as select * from t_stu; 
    
    仅仅复制表结构
    -- where 1 != 1 不把数据查出来 
    create table t_stu_copy2 as select * from t_stu where 1 != 1;

– 序列: 是oracle 独有的 用来结果我们的插入实现主键自动增长的操作

创建序列的语法:
    create sequence SEQ_STU
    minvalue 1  --  最小是 1
    maxvalue 9999 -- 最大 
    start with 2  -- 开始位置 
    increment by 1 -- 增量 
    cache 20;   -- 缓存大小 
    
     序列的使用方式:
       nextval:  获取下一值的序列 
       currval;  获取当前会话的序列
     结合 插入语句使用 序列: (实现 表的主键字段自动增长)
      -- dual (伪表 专门用来写测试sql 的一些语句)
       select seq_stu.nextval from dual; -- 6
       select seq_stu.currval from dual;-- 6 
       
       create table t_stu(
         sid number(2) primary key,
         sname varchar2(20),
         sage number(2)
       )  
       SEGMENT CREATION IMMEDIATE;
       
       insert into t_stu(sid,sname,sage) values (seq_stu.nextval,'鸠摩智',45);
    
    问题?  插入的序列从 2开始 ? 
    ·Oracle从 11.2.0.1版本开始，提供了一个“延迟段创建”特性：即
      当我们创建了新的表(table)和序列(sequence)，
      在插入(insert)语句时，序列会跳过第一个值(1)。
      所以结果是插入的序列值从 2(序列的第二个值) 开始， 而不是 1开始。   
     解决办法:
     创建表时让seqment立即执行，
     SEGMENT CREATION IMMEDIATE;
     
     注意: 
       1.每次执行序列 会自动增长
       2.序列本身是一个变化的数据  可以独立使用 可以交互使用(不建议交互使用)
       3 nextval 获取下一序列  currval 获取当前的序列  从新开始了一个会话 不能获取 当前会话序列(currval) 
         需要先获取下一个序列  才可以 currval
       4. 创建序列时候 默认从2开始  解决(创建表的时候  添加 SEGMENT CREATION IMMEDIATE)  
            
    UPDATE 更新语句
     
     语法:
        update 表名 set 字段1= val1，字段2=val2 where 判断条件;
        
        create table t_person(
          pid number(2)，
          pname varchar2(20),
          page number(2)
        );
         insert into t_person(pid,pname,page) values (1,'乔峰',45);
         insert into t_person(pid,pname,page) values (2,'段誉',35);
         insert into t_person(pid,pname,page) values (3,'段正淳',42);
         insert into t_person(pid,pname,page) values (4,'丁春秋',60);
         insert into t_person(pid,pname,page) values (5,'无崖子',70);
         commit;
         -- 修改 pid 为1 数据 
         update  t_person set pname = '王语嫣' where page = 45;
         
         -- 修改多条数据   or 或者 in 
         
          update  t_person set pname = '秦红棉' where pid = 2 or pid = 3;
          update  t_person set pname = '李秋水',page = 33 where pid in (3,4);
         
          -- 没有给判断条件 
          update  t_person set pname = '王语嫣';
          
         - 注意: 
            修改的时候一定要把加  where判断条件  
     
    DELETE  
        语法:  delete from 表名 where 条件
        
        -- 通过id删除 
        delete from t_person where pid = 1;
        -- 删除需要带条件 
       delete删除 先将删除的数据放在内存中 执行commit 才是删除数据库 
       
       truncate 也 可以删除数据 这种删除是不经过内存 直接删除 
       
        
    SELECT
      查询语句:(以系统自带的3张表进行查询)
       
      查询分类:  
        单表查询: 
          1. 基础查询
          2. 条件查询 
          3. 模糊查询
          4. 排序查询
          5. 聚合函数 + 分组
          6. 子查询 
          7  分页查询
          
        多表从查询:  
          1. 合并结果集
          2. 连接查询 
          3. 子查询
          
     1. 基本查询:
        语法:
        1.  select * from 表名; 查询所有的字段 
        2.  select 字段 as [别名] from 表名; 查询一些字段 
        
        -- 查询所有的员工信息 
        select * from emp;
        -- 查询编号,姓名 职位  薪资 
        select empno,ename,job,sal from emp;
        
        查询结果显示标题 (编号,姓名 职位  薪资)
        -- as 添加别名  (员工编号")
        -- as 可以省略   "" 可以省略 
         select empno as "员工编号" ,ename 姓名,job,sal from emp;
        
        -- 查询所有emp 的职位 
        select job from emp; -- 有重复 去重复
        -- 去重复  distinct
        select distinct job from emp;
        
        -- 查询中进行四则运算  查员工年薪 
        select ename,job,(sal * 12 + 200) 年薪 from emp;
     
        -- 查询常量的使用 
         select '员工',ename,job from emp; -- 可以是字符串 常量 
         select 9527,ename,job from emp;  -- 可以是数组常量 (省略 '')
         
        -- 可以使用 || 拼接符  
        
        select empno || ename from emp;
        -- 不能""
        select  '编号:' || empno || '姓名:'|| ename  员工信息 from emp;
        
        -- 查询的时候 表 可以使用别名 
        select e.ename,e.job from emp e;
        
          
     2.条件查询:
      语法:select 列名 
      from 表名 
      where 判断条件;      
      1. 关系运算符  > =    (<> !=) 不等于2中写法 
      sal > 1500 员工 
      select * from emp where sal > 1500;
      名字叫soctt 的员工信息
      select * from emp where ename = 'SCOTT';
      
      职位不是 ANALYST 的员工 
      
      select * from emp where job <>'ANALYST';
      select * from emp where job !='ANALYST';
     2. 逻辑运算符  and  or not 
      sal 在 1500 -2000 之间的  
      select * from emp where sal > 1500 and sal < 2500;
      
      查询sal > 3000 或者 名字叫 SCOTT 
        select * from emp where sal > 3000 or ename = 'SCOTT';
      
      SAL 不在这个 1500 -2000
       select * from emp where not (sal > 1500 and sal < 2500);      
     
    3.区间运算 
      between ... and ...  可以用在 数值 和 时间上 
      1. 查找在 1200 到 2500 之间 
       select * from emp where sal between 1200 and 2500;
      2. 在 1981 年入职的员工   
       select * from emp where hiredate between '01-1月-81' and '30-12月-81';
     
    4. 判断 null 操作 
       is null  is not null 
       -- comm 是奖金  
       -- 奖金为null 的员工 
       select * from emp where comm is null;
       -- 不为空 
       select * from emp where comm is not null;
    5. in   和  not in  
    
        在in 什么中 
         判断员工标号 :是否在 (7499,7521,8888) 
        select * from emp where empno  = 7499 or empno = 7521 or empno  =8888;
        select * from emp where empno in (7499,7521,8888);

三:like (模糊查询)  
通配符的解释:  
1.\_ 代表匹配一位  
2. % 匹配 0 -多位

1.查以 姓名以 A开头的员工 
       select * from emp where ename like 'A%';  
       2.以s结尾  
        select * from emp where ename like '%S';  
       3. 查询 第二字符为A的官员 
       select * from emp where ename like '_A%';
       4. 是否包含s的员工 
       select * from emp where ename like '%S%';

4.排序查询  
排序查询的语法:

select * from t
        where 条件 
        order by 字段;
        
        -- sql的执行顺序 
        1.查表名 
        2. 查询 where判断条件 
        3. select *  字段
        4. 排序 
      
     1.查询所有的员工 
     
     select * from emp; -- 通过插入顺序进行 展示的 
     
     -- ASC 升序  (默认是升序)
     -- desc 降序
     
     -- 通过薪资进行排序 (底到高) 升序 
       select * from emp
       order by sal ASC;
     --  默认是升序  asc 可以省略 
      select * from emp
       order by sal desc;
     
     -- 按照入职时间进行排序(升序) 如果入职时间相同 薪资排序 (降序)
     
     select * from emp 
     order by hiredate asc,sal desc;

#### — 常用函数 ####

##### 字符函数 #####

常用函数  
Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。  
函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数：  
1.单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，  
比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。

常用的单行函数有：  
字符函数：对字符串操作。  
数字函数：对数字进行计算，返回一个数字。  
转换函数：可以将一种数据类型转换为另外一种数据类型。  
日期函数：对日期和时间进行处理。

2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x)返回结果集中x列的总合。

字符函数  
函数 说明  
ASCII(x) 返回字符x的ASCII码。  
CONCAT(x,y) 连接字符串x和y。  
INSTR(x, str \[,start\] \[,n) 在x中查找str，可以指定从start开始，也可以指定从第n次开始。  
LENGTH(x) 返回x的长度。  
LOWER(x) x转换为小写。  
UPPER(x) x转换为大写。  
LTRIM(x\[,trim\_str\]) 把x的左边截去trim\_str字符串，缺省截去空格。  
RTRIM(x\[,trim\_str\]) 把x的右边截去trim\_str字符串，缺省截去空格。  
TRIM(\[trim\_str FROM\] x) 把x的两边截去trim\_str字符串，缺省截去空格。  
REPLACE(x,old,new) 在x中查找old，并替换为new。  
SUBSTR(x,start\[,length\]) 返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。

案例:  
select ascii(‘0’) from dual;  
select concat(‘hello’,‘world’) from dual;  
select instr(‘HellorWorld’, ‘or’, 7) from dual;  
select length(‘HelloWorld’) from dual;  
– 查询 名字及长度为 4  
select \* from emp where length(ename) = 4;  
select lower(‘HelloWorld’) from dual;  
select upper(‘HelloWorld’) from dual;  
select ltrim(’=HelloWorld’,’=’) from dual;  
select rtrim('HelloWorld=’,’=’) from dual;  
select trim(’=’ from ‘=HelloWorld’) from dual;  
select replace(‘ABCDE’,‘AB’, ‘HEHE’) from dual;  
select substr(‘ABCDE’, 3, 2) from dual;

##### 数字函数 #####

数字函数  
函数 说明 示例  
ABS(x) x绝对值 ABS(-3)=3  
ACOS(x) x的反余弦 ACOS(1)=0  
COS(x) 余弦 COS(1)=1.57079633  
CEIL(x) 大于或等于x的最小值 CEIL(5.4)=6  
FLOOR(x) 小于或等于x的最大值 FLOOR(5.8)=5  
LOG(x,y) x为底y的对数 LOG(2,4)=2  
MOD(x,y) x除以y的余数 MOD(8,3)=2  
POWER(x,y) x的y次幂 POWER(2,3)=8  
ROUND(x\[,y\]) x在第y位四舍五入 ROUND(3.456,2)=3.46  
SQRT(x) x的平方根 SQRT(4)=2  
TRUNC(x\[,y\]) x在第y位截断 TRUNC(3.456,2)=3.45

1.ROUND(X\[,Y\])，四舍五入。  
在缺省y时，默认y=0；比如：ROUND(3.56)=4。  
y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。  
y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

select round(3.26) from dual; – 保留整数  
select ROUND(5.655,2) from dual; – 保留2位小数  
– 日薪  
select ename,sal,round(sal / 30,1) 日薪 from emp;  
select ROUND(341.654,-2) from dual;

1.  TRUNC(x\[,y\])，直接截取，不四舍五入。  
    在缺省y时，默认y=0；比如：TRUNC (3.56)=3。  
    y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。  
    y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

select TRUNC(5.654) from dual; – 截取去除小数后面的  
select TRUNC(5.658,2) from dual;  
select TRUNC(351.654,-2) from dual;

##### 日期函数 #####

日期函数  
1.ADD\_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。  
select add\_months(sysdate, 12) from dual;

2.LAST\_DAY(d)，返回指定日期当月的最后一天。  
select LAST\_DAY(sysdate) from dual;

3.ROUND(d\[,fmt\])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。  
如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年。  
如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。  
默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。  
如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

select sysdate, round(sysdate),  
round(sysdate, ‘YEAR’),  
round(sysdate, ‘MONTH’),  
round(sysdate, ‘DDD’),  
round(sysdate, ‘DAY’) FROM DUAL;

4.EXTRACT(fmt FROM d)，提取日期中的特定部分。  
fmt为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。  
其中YEAR、MONTH、DAY可以为DATE类型匹配，也可以与TIMESTAMP类型匹配；  
但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配。

SELECT EXTRACT(YEAR FROM SYSDATE),
           EXTRACT(MONTH FROM SYSDATE) ,
           EXTRACT(DAY FROM SYSDATE) ,
           EXTRACT(HOUR FROM SYSTIMESTAMP) ,
           EXTRACT(MINUTE FROM SYSTIMESTAMP) ,
           EXTRACT(SECOND FROM SYSTIMESTAMP)  FROM DUAL;

– 查看 我们的emp的员工的工龄 (当前的事件 - 入职时间)

select ename,job,hiredate from emp;

– 哪一年入职  
select ename,job,hiredate,EXTRACT(YEAR from hiredate) from emp;  
– 查看工龄  
select ename,job,hiredate,EXTRACT(YEAR from Sysdate) - EXTRACT(YEAR from hiredate) from emp;

-- 查看二月份入职的员工  获取月份 
     select ename,job,hiredate from emp
     where extract(MONTH FROM hiredate) = 2;

5.日期转换函数  
to\_date : 将字符串转换成日期  
to\_char : 将日期转换成字符串  
注意:  
yyyy表示四位年份  
mm表示两位月份  
dd表示两位天数  
hh24表示24小时，hh12表示12小时  
mi表示分钟  
ss表示秒钟  
select sysdate,  
to\_char(sysdate,‘yyyy/mm/dd hh:mi:ss’),  
to\_char(sysdate, ‘yyyy-mm-dd’),  
to\_char(sysdate, ‘yyyy-mm’),  
to\_char(sysdate, ‘yyyy’)  
from dual;

注意 sql 中字符串是数字 可以进行运算 
         -- 工龄的计算 
        select ename,job,hiredate from emp;
        
        select ename,job,hiredate,to_char(hiredate,'yyyy') 入职年份 from emp;
        
        -- 工龄
        select ename,job,hiredate,(to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')) 工龄 from emp;

##### 其他函数 #####

6.nvl(column, value) 如果column为空，返回value，否则返回x。  
– 查看 奖金  
select ename,job,sal,comm from emp;

– 查看总工资  
select ename,job,sal + comm 月收入 from emp;  
– 有的人没有工资 (sal + comm ) 如果comm为null 结果为null 不合理

使用nvl 函数 nvl(x,y) x如果为null 可以设置值 y  
select ename,job,sal,nvl(comm,0) from emp;  
– 查看收入  
select ename,job,sal + nvl(comm,0) 月收入 from emp;

7.decode: 类似于Java当中 if 、 if else、if else if else  
create table stu(  
sid number(2)，  
sname varchar2(20)，  
ssex number(1)  
);

select sid,sname,ssex
           , decode(ssex, 1, '女') 
           , decode(ssex, 1, '女', 2, '男')
           , decode(ssex, 1, '女', 2, '男','未知')        
         from stu;

– 多表查询:

当我们使用更多表查询 需要建立表单的模型 
     当我我们关联对象与对象的关系是 我们需要java模型
     
     java模型: 对象模型可以双向关联 依赖是对象的引用 
     数据库中: 表与表之间的管理依赖的是外键
     
    java 模型的依赖的关系 
    1 对 1 关系 
       一个丈夫对应一个妻子 (引用)
    class Wife{
       private int wid;
       private husband hus;
    }
     
    class Husband{
       private int hid;
       private Wife w;
    }

1 对的多 关系  
部门和员工(一个部门有多个员工)

class Emp\{  
private int empno;  
private Dept dept;  
\}

class Dept\{  
private int deptno;  
private List emps;  
\}

java中多对多的实现:

class Student\{  
private int sid;  
private String sname;  
private List teas;  
\}

class Teacher\{  
private int tid;  
private String tname;  
private List stus;  
\}

数据库中的 表与表之间的关系:  
1 对 1  
关联关系(让其中的任意一张表添加外键)

create table wife(  
wid number(2) primary key,  
wname varchar2(20),  
constraints fk\_wife\_hsu\_id foreign key (wid) references husband(hid)  
);

create table husband(
      hid number(2) primary key,
      hname varchar2(20)+

);

1 对多  
多方(从表) 学生 添加外键  
少方(主表) 班级  
关联关系 (从表中设置 外键)  
create table sxtstu(  
sid number(3) primary key ,  
sname varchar2(20)，  
classid number(2),  
constraints fk\_stu\_cls\_id foreign key (classid) references sxtclass(cid)  
);  
create table sxtclass(  
cid number(3) primary key,  
cname varchar2(20)  
);

多对多的关系  
学 和 老师  
关联关系(需要媒人介绍(中间表 设置外键 同时关联2张表 ))  
create table stu(  
sid number(2) primary key,  
sname varchar2(20)  
);

create table teacher(  
tid number(2) primary key,  
tname varchar2(20)  
);

create table stu\_tea\_temp(  
sid number(2),  
tid number(2)，  
– constraints fk\_stu\_id foreign key (sid) references stu(sid)  
– constraints fk\_tea\_id foreign key (tid) references teacher(tid)  
);  
一般不设置外键 使用逻辑外键 (外键通过 逻辑 管理)

#### – 多表查询 ####

1.合并结果集 (了解)  
2.连接查询  
3. 子查询

##### 合并结果集 #####

union 去除重复并且合并 
    union all  不去重复的记录

技巧: 要求合并的多个结果集中 列的类型和 列的数量必须相同  
create table test1(  
tid number(2)，  
tname varchar2(20)  
);

insert into test1 values(1,‘a’);  
insert into test1 values(2,‘b’);  
insert into test1 values(3,‘c’);  
commit;

select \* from test1;

create table test2(  
did number(2)，  
dname varchar2(20)  
);

insert into test2 values(3,‘c’);  
insert into test2 values(4,‘d’);  
insert into test2 values(5,‘f’);  
commit;

把查询 结果 合并成一个集  
union – 结果集去除重复的  
select \* from test1  
union  
select \* from test2;

union all-- 结果集不去除重复

select \* from test1  
union all  
select \* from test2;

##### – 连接查询 #####

分类:  
1. 交叉连接  
交叉连接  
查出的员工的 部门信息  
查表:  
emp 字段 所有  
dept 字段 所有  
select \* from emp; – 15 条数据  
select \* from dept;-- 4 条数据  
select \* from emp,dept；

交叉查询的问题：
           1.会产生很大的结果集(15 * 4 ) 60 性能低
           2. 结果集 有一些数据对我们没意义
    
     2. 等值连接 
            在交叉查询的基础上 加上条件 (表与表之间的 关联字段 deptno )
            添加 条件  加关联字段 deptno
            select * from emp,dept where emp.deptno = dept.deptno; 
            (查询的时候 还是产生了60条数据  通过过滤显示解决没有意义的数据)
            
     3. 内连接 (a inner join  b on (关联字段))
           和等值连接一样的 
            select * from emp ,dept where emp.deptno = dept.deptno; (等值连接写法)
            内连接
            select * from emp inner join dept on (emp.deptno = dept.deptno);
            表使用别名:
             select * from emp e inner join dept d on (e.deptno = d.deptno);  
           解决了查询 结果集 数据太多的问题
           select * from dept;
          问题: 1.   数据补全 (老王，40部门不显示)
       
     4. 外连接 
          左外连接: 把左表所有数据查询出来 
          select * from emp e left join dept d
          on e.deptno = d.deptno; 
          
          解决把 老王查出来 
          右外连接"把右表所有的数据查出来 
           select * from emp e right join dept d
           on e.deptno = d.deptno; 
          
          全外连接 : 把2表不满足的记录 保留a  full join b  on 关联字段
          
          select * from emp e full join dept d on e.deptno = d.deptno; 
          oracle 方言查询:
          于外连接，也可以使用“(+)”来表示。但须注意：
        1. (+)操作符只能出现在where子句中，并且不能与outer join语法同时使用
        2. 当使用(+)操作符执行外连接时，如果在where子句中包含有多个条件，则必须所有条件中都包含(+)操作符
        3. (+)操作符只适用于列，而不能用在表达式上
        4. (+)操作符不能与or和in操作符一起使用
        5. (+)操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接
         
        用(+)来实现，这个+号可以这样来理解：+ 表示补充，即哪个有+，这个表就是匹配表，另外的则是基础表。
        例子
        select t1.c1,t2.c2 from t1,t2 where t1.c=t2.c(+)    --左连接
        select t1.c1,t2.c2 from t1,t2 where t1.c(+)=t2.c    --右连接
           左外连接:
           select * from emp e,dept d where e.deptno = d.deptno(+);  
           右外连接:
           select * from emp e,dept d where e.deptno(+) = d.deptno;
        
        99sql写法:  [JOIN 表名称 ON（条件）|USING（字段）] 
        select * from emp e left join dept d using(deptno);
        
     5. 自然连接: 会多表联查 自己给我们关联数据(了解)
     select e.ename,d.dname from emp e natural join dept d;

统计函数:(聚合函数)

常用的聚合函数"  
sum 求和  
count 统计 (非空不会添加)  
max 最大值  
min 最小值  
avg 平均值

– 查所有的薪资和

select sum(sal) 薪资之和 from emp;

– 统计 job的职位 有几个  
select \* from emp;

select count(distinct job) from emp;  
select distinct job from emp;

– 统计emp中有多少员工数

select count(\*) from emp; – 统计 每个字段的数量 计算每一列的数组 取最大的  
select count(empno) from emp; --统计一个字段的记录条数 select count(comm) from emp;  
select count(1) from emp; – 给出一个好常量值 然后在统计 常量列的记录条数 (常量列不可能为null) 、

– 找出薪资最高的

select max(sal) from emp ;

–薪资最低  
select min(sal) from emp;

– 找出薪资的平均值  
select avg(sal) from emp;

– 分组查询:

– 数据有重复才可以分组

分组语法:  
select \* from t  
where 条件  
group by 字段1…  
order by 排序;  
– 按照部门编号分组 求出每个部门的人数和平均工资  
– 查表 emp  
– 字段 deptno empno sal  
– 查出字段  
select deptno,empno,sal from emp;

--分组操作  (分组后有新的语法要求)
      1. 分组 字段 必须是有有重复的 
      2. 分组后,查询字段必须是 分组或者聚合函数 (mysql没有这个要求)
     select deptno,count(empno),avg(sal) from emp
     group by deptno;
    
    -- 查询部门名称 部门人数 平均薪资 
    
    -- 查表   dept  emp 
    -- 字段   dname  empno sal 
    -- 关联字段 detpno
    -- 查出来  
    select d.dname,e.empno,e.sal  from emp 
    e ,dept d where e.deptno = d.deptno;
    
    -- 分组  dname
    select d.dname,count(e.empno),round(avg(e.sal),2)  from emp 
    e ,dept d where e.deptno = d.deptno
    group by d.dname;
    
    --  右连接 
    -- 保留 2位小数   round(avg(e.sal),2)
    -- nvl(sal，0)  为空给 0 
    select d.dname,count(e.empno),nvl(round(avg(e.sal),2),0)  from emp 
    e right join dept d on e.deptno = d.deptno
    group by d.dname;
    
    -- 多字段分组查询 
    -- 显示每个部门编号，名称位置  位置 部门人数  平均薪资 
     -- 查表   dept  emp 
    -- 字段   dname loc,deptno   empno sal 
    -- 关联字段 detpno
    -- 查出来  
    select d.deptno,d.dname,d.loc,e.empno,e.sal  from emp 
    e right join dept d 
    on e.deptno = d.deptno;
    
    -- 多字段分组 
    select d.deptno,d.dname,d.loc,count(e.empno),nvl(avg(e.sal),0)  from emp 
    e right join dept d 
    on e.deptno = d.deptno
    group by  d.deptno,d.dname,d.loc;
    
    分组查询 HAVING 的使用
    统计出部门的平均工资高于 2000的 部门的详细信息（部门编号、部门名称、部门位置、部门人数、部门平均薪资）。

seledct \* from t  
where 条件  
group by 分组字段  
order by 字段  
having 条件;

having 的使用 :分组后再次过滤 使用having

where的使用: 在执行group by之前的过滤(总过滤) (不能使用聚合函数)  
having 是 在 group by 分组之后进行过滤 (可以使用)

select d.deptno,d.dname,d.loc,e.empno,e.sal 
    from emp e right join  dept d 
    on d.deptno = e.deptno;
    分组:
    
    select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) 
    from emp e right join  dept d 
    on d.deptno = e.deptno
    group by d.deptno,d.dname,d.loc;
    -- 过滤
    select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) 
    from emp e right join  dept d 
    on d.deptno = e.deptno
    group by d.deptno,d.dname,d.loc
    having avg(e.sal) > 2000;

分组查询总和运用  
思考一：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，  
并且要满足从事同一工作的雇员的月工资合计大于$5000，  
输出结果按月工资的合计升序排列：

– 1. 显示非销售人员工作名称  
select \* from emp where job = ‘SALESMAN’;  
– 把非销售 查出来  
select \* from emp where job != ‘SALESMAN’;

– 2 从事同一工作雇员的月工资的总和 (分组 + 聚合)  
select job,sum(sal) from emp  
where job != ‘SALESMAN’  
group by job;  
– 大于 5000  
select job,sum(sal) from emp  
where job != ‘SALESMAN’  
group by job  
having sum(sal) > 5000;

– 排序 升序  
select job,sum(sal) from emp  
where job != ‘SALESMAN’  
group by job  
having sum(sal) > 5000  
order by sum(sal) asc;

思考二：显示部门编号不是30的，的部门详细信息（部门编号、部门名称、部门人数、部门月薪资总和），  
并要求部门月工资总和大于$8000，输出结果按部门月薪资的总和降序排列。  
– 显示部门编号不是30的，的部门详细信息  
– 查表 emp , dept  
– 字段 deptno dname empno sal

– 查出信息  
select \* from emp e right join dept d on e.deptno = d.deptno;  
– 部门不是 30

select d.deptno,d.dname,e.empno,e.sal from  emp e right join dept d 
    on e.deptno = d.deptno
    where e.deptno != 30;

–分组

select d.deptno,d.dname,count(e.empno),sum(e.sal) from  emp e right join dept d 
    on e.deptno = d.deptno
    where e.deptno != 30
    group by d.deptno,d.dname;
    
    -- 综合大于 80000
    select d.deptno,d.dname,count(e.empno),sum(e.sal) from  emp e right join dept d 
    on e.deptno = d.deptno
    where e.deptno != 30
    group by d.deptno,d.dname 
    having sum(e.sal) > 8000;
    
    -- 降序 
    select d.deptno,d.dname,count(e.empno),sum(e.sal) from  emp e right join dept d 
    on e.deptno = d.deptno
    where e.deptno != 30
    group by d.deptno,d.dname 
    having sum(e.sal) > 8000
    order by sum(e.sal) desc;

子查询:  
查询语句中嵌套查询:

嵌套查询 中的结果集:
      单行单列    where/having 中
      多列单行    少见 了解 
      单列多行:    where中
      多行多列    用在from后面  
     
    1. 查询薪资比 SCOTT 高的员工 
    -- 查询 scott  的薪资 
    select sal from emp where ename = 'SCOTT';
    2. SAL > SCOTT 
    
    select * from emp where sal > (
        select sal from emp where ename = 'SCOTT'
    );

– 查询 高于公司 平均薪资的雇员的信息  
– 查出平均薪资  
select avg(sal) from emp;

select * from emp where sal > (
       select avg(sal) from emp
    );

– 单行多列  
查询 和soctt 薪资一样的 job一样的雇员信息

– scott 薪资 和job 查出来  
select SAL,JOB from emp where ename = ‘SCOTT’;

– 判断 使用in 或 = 操作

select \* from emp  
where (sal,job) = (select SAL,JOB from emp where ename = ‘SCOTT’);

select \* from emp  
where (sal,job) in (select SAL,JOB from emp where ename = ‘SCOTT’);

– 单列多行

判断使用 in ,any ,all    
    -- 查询和 进来薪资一样的员工信息 
    -- 经理薪资 
    select sal from emp where job = 'MANAGER';
    -- 薪资和 manager一样的 
    select * from emp where sal in (select sal from emp where job = 'MANAGER');
    
    
    = any 和in 是一样的 
    
    -- 2975 ， 2850  ,2450 
    > any 比子查询返回最小记录还要大的数据 > 2450     
    select * from emp where sal >any (select sal from emp where job = 'MANAGER') 
    
    
    < any  比子查询 返回最大记录还要小  < 2975
    select * from emp where sal  < any (select sal from emp where job = 'MANAGER') 
    
    > all 大于子查询最大的 
     select * from emp where sal  > all (select sal from emp where job = 'MANAGER')；
     或者 
       select * from emp where sal  >  (select max(sal) from emp where job = 'MANAGER')  -- 多行单列转成 当行单列 
    < all 小于最小的 
     select * from emp where sal  < all (select sal from emp where job = 'MANAGER')
    
    -- 多行多列 
      查询每个部门编号  名称 位置  部门人数 平均薪资 
      select  d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)  
      from emp e，dept d
      where e.deptno = d.deptno
      group by d.deptno,d.dname,d.loc;      
      -- 等值查询   emp  15 和 dept 4 表  === > 15 * 4 = 60 次计算
      
      -- 使用子查询 优化sql 
      先子查询 emp 分组 统计 再 和 dept 管理 
      select e.deptno,e.empno,e.sal from emp e;
      
      -- 分组 + 统计  (结果为 多行多列  当做数据源使用 在from 后面可以使用) 
       select e.deptno,count(e.empno),avg(e.sal) from emp e
       group by e.deptno;
       
       select d.dname,d.deptno,d.loc,temp.nums,temp.avgsal from dept d,(
             select e.deptno dno,count(e.empno) nums,avg(e.sal) avgsal 
             from emp e
             group by e.deptno    -- 12条数据  分组 4条数 
       ) temp 
       where d.deptno = temp.dno;
       
      查询  表dept 4条数 temp 表 4条数据  == > 16 次计算   28次计算 
      
      
    伪列:
    rowid 
    rownum 
    
    伪列:是一张表不存在的列 附加的列 
         不会存储表中的真实数据 只能够用于查询 
         
    rowid  用于表示我们的一行记录存储的物理地址 唯一
    
    select * from emp;   
    select ename,rowid  from emp;
    
    rownum: 在查询结果集 会为每一行结果生成一个行号 第一行为 1  一次递增  有1 会有2 
    select * from emp;
    select ename,rownum from emp;
    
    -- 查询emp表中前 1-4 条记录  pageSize = 4  currentPage = 1 
    
      select ename，empno,rownum from emp
      where rownum <= 4;    
    
    -- 查询 5- 8 条数据    pageSize = 4  currentPage = 2 
    select ename，empno,rownum from emp
    where rownum <= 8 and rownum >=5 ;   
    
    -- oracle 的分页需要用到子查询 
      你先要查询  小于的 8 的结果集 有伪列 通过伪列进行判断
    -- mysql 的分页 limit      
    -- 小于 8 的伪列结果集 
    select ename,empno,rownum from emp where rownum <=8; 
    
    -- 通过子查询 过滤 rownum >= 5 
    写法1:  查询的范围小 效率高一些 
    select t.ename,t.empno,t.num from (  
        select ename,empno,rownum num from emp where rownum <=8 )  t
     where t.num >= 5 
    
     写法2:
     select t.ename,t.empno,t.num from (  
        select ename,empno,rownum num from emp)  t
     where t.num >= 5 and t.num <= 8;
    
    -- 查询 9 - 12 的数据    pageSize = 4  currentPage = 3 
      
    select ename,rownum from emp where rownum <= 12;
    
    select  t.ename,t.empno,t.num from  ( 
        select ename,empno,rownum num from emp where rownum <= 12 ) t
    where  t.num >=9 ;   
    
    -- pageSize = 4  currentPage = 1   1- 4记录 
    -- pageSize = 4  currentPage = 2   5- 8记录
    -- pageSize = 4  currentPage = 3   9- 12记录
    
    -- oracle 分页的 sql公式
    select  t.ename,t.empno,t.num from  ( 
        select ename,empno,rownum num from emp where rownum <= pageSize * currentPage ) t
    where  t.num >= (currentPage - 1) * pageSize +1  ;
    
    -- 查出薪资最高的员工信息 
    -- 查询薪资最高的 
    select max(sal) from emp;
    select * from emp where sal = (  select max(sal) from emp);
    
    -- 分页实现 最高的? 
    -- 排序后 
    select * from emp where order by sal desc;
    
    -- 分页 
    
    select t.empno,t.ename,t.sal,rownum from  (select empno,ename,sal from emp order by sal desc ) t;
    
    select t.empno,t.ename,t.sal,rownum from  (
      select empno,ename,sal from emp order by sal desc ) t
    where rownum = 1;
    
    -- 查询 薪资 第三高的  
    
    -- 排序后 查询结果 有 rownum 
    select t.empno,t.ename,t.sal,rownum from  (
       select empno,ename,sal from emp order by sal desc ) t;
    
    -- 判断  
     select t.empno,t.ename,t.sal,rownum from  (
       select empno,ename,sal from emp order by sal desc ) t
     where rownum <=3;
     
     select * from (
         select t.empno,t.ename,t.sal,rownum rn from  (
           select empno,ename,sal from emp order by sal desc ) t
        where rownum <=3
     ) temp 
     where temp.rn = 3;
     
     --- 视图
    view 视图是一张虚表 物理不存在的表  主要目的用于查询 可以操作 MDL(不建议使用)
    
    
    作用: 1. 开放部分字段给用户使用 隐藏  信息 
    
      select * from emp;
      
    创建一个视图 
    要授权:  创建视图 
    切换 conn system/manager as sysdba;
    授权: grant create view to 用户;
    
    create or replace view 视图名字 as [新的数据] [with read only]
    
    -- 创建一个视图 给你三个 字段 
    create or replace view v_emp as select ename,empno,deptno from emp with read only;
    
    select * from v_emp;

– 索引 一般对sql语句优化有很大的作用

主键会自动创建索引  
     
     创建索引语句:
     为员工表中姓名 这一列创建索引 
     create index iindex_emp_name on emp (ename);
     
     索引好处: 
     创建索引能够提高数据库的查询效率 但是会同时影响到 MDL操作的性能
     
     系统介绍索引 大家可以看下 扫盲下 (一共7 篇文章)
     https://blog.csdn.net/why15732625998/article/details/80388236