Oracle数据库sql语句全

拾光的遗迹

已于 2022-03-29 12:06:29 修改

阅读量2.3k

点赞数 8

文章标签： oracle sql 数据库

于 2020-05-27 20:42:59 首次发布

本文链接：https://blog.csdn.net/qq_43168442/article/details/105319851

版权

– 创建女孩表，字段有：姓名、年龄、电话号码、是否有男朋友

  create table girl(
      name  varchar2(20),
      age   number(3),
      phone varchar2(20),
      bf    number(1)
  );

– 向该表中插入’貂蝉’、‘西施’、'王昭君’的个人信息

insert into girl values('貂蝉', 16, '110', 1);
insert into girl values('西施', 20, '120', 0);
insert into girl values('王昭君', 18, '119', 1);

– 更新该表中’貂蝉’的电话号码为119

 update girl set phone = '119' where name = '貂蝉';

– 更新该表中所有人的年龄为18

 update girl set age = 18;

– 删除名字为’王昭君’的记录

  delete from girl where name = '王昭君';

– 删除所有记录

  delete from girl;

– 删除女孩表

  drop table girl;

Select查询语句

1.Select查询语句
1.1 查看表结构的方式 desc 表名; – 表示查看表结构，也就是查看该表的所有字段以及类型等信息。

desc s_emp  -- 员工表

名称             空值       类型            
-------------- -------- ------------- 
ID             NOT NULL NUMBER(7)         -- 员工编号
LAST_NAME               VARCHAR2(25)      -- 员工的姓
FIRST_NAME              VARCHAR2(25)      -- 员工的名
USERID                  VARCHAR2(8)       -- 员工的昵称
START_DATE              DATE              -- 员工的入职日期
COMMENTS                VARCHAR2(255)     -- 备注
MANAGER_ID              NUMBER(7)         -- 领导的编号
TITLE                   VARCHAR2(25)      -- 职位名称
DEPT_ID                 NUMBER(7)         -- 部门编号
SALARY                  NUMBER(11,2)      -- 薪水
COMMISSION_PCT          NUMBER(4,2)       -- 提成

desc s_dept -- 部门表
名称        空值       类型           
--------- -------- ------------ 
ID        NOT NULL NUMBER(7)     -- 部门的编号
NAME      NOT NULL VARCHAR2(25)  -- 部门的名称
REGION_ID          NUMBER(7)     -- 办公区域的编号

desc s_region -- 区域表
名称   空值       类型           
---- -------- ------------ 
ID   NOT NULL NUMBER(7)         -- 区域的编号
NAME          VARCHAR2(50)      -- 区域的名称

desc salgrade -- 薪水等级表
名称    空值       类型        
----- -------- --------- 
GRADE NOT NULL NUMBER(7)       -- 薪水等级
LOSAL          NUMBER(7)       -- 最低薪水
HISAL          NUMBER(7)       -- 最高薪水

desc student   -- 学生表
名称          空值       类型            
----------- -------- ------------- 
STUDENTNO   NOT NULL VARCHAR2(50)   -- 学生学号
LOGINPWD    NOT NULL VARCHAR2(20)   -- 登录密码
STUDENTNAME NOT NULL VARCHAR2(50)   -- 学生姓名
SEX         NOT NULL CHAR(3)        -- 学生性别
GRADEID     NOT NULL NUMBER(2)      -- 年级编号
PHONE       NOT NULL VARCHAR2(255)  -- 电话号码
ADDRESS              VARCHAR2(255)  -- 通讯地址
BORNDATE             DATE           -- 出生日期
EMAIL                VARCHAR2(50)   -- 电子邮箱

desc grade    -- 年级表
名称        空值       类型           
--------- -------- ------------ 
GRADEID   NOT NULL NUMBER(2)        -- 年级编号
GRADENAME NOT NULL VARCHAR2(20)     -- 年级名称

desc subject  -- 课程表
名称          空值       类型           
----------- -------- ------------ 
SUBJECTID   NOT NULL NUMBER(2)      -- 课程编号
SUBJECTNAME NOT NULL VARCHAR2(50)   -- 课程名称
CLASSHOUR   NOT NULL NUMBER(2)      -- 课程课时
GRADEID     NOT NULL NUMBER(2)      -- 年级编号

desc result    -- 成绩表
名称            空值       类型           
------------- -------- ------------ 
ID            NOT NULL NUMBER(4)    -- 结果编号
STUDENTNO     NOT NULL VARCHAR2(50) -- 学生编号
SUBJECTID     NOT NULL NUMBER(2)    -- 课程编号
STUDENTRESULT          NUMBER(4)    -- 考试成绩
EXAMDATE      NOT NULL DATE         -- 考试日期

1.2 查询表中字段的数值

   select 列名1,列名2,... from 表名;

如：
– 查询员工表中所有员工的编号

  select id from s_emp;

– 查询员工表中所有员工的编号、名字以及薪水

   select id,first_name,salary from s_emp;

– 查询员工表中所有员工的编号、姓、名等所有字段

   select id,last_name,first_name,userid,start_date,comments,manager_id,title
,dept_id,salary,commission_pct from s_emp;

– 查询员工表中所有字段的简化写法

   select * from s_emp;

1.3 常用的算术运算符

加法 - 减法 * 乘法 / 除法 mod 取余

如：
– 查询员工表中每个员工加500元过节费后的薪水

   select salary,salary+500 from s_emp;

– 查询员工表中每个员工减200元后的薪水

   select id,first_name,salary,salary-200 from s_emp;

– 查询员工表中所有员工的年薪

   select first_name,salary,salary*16 from s_emp;

– 查询员工表中所有员工的日薪

   select first_name,salary,salary/22 from s_emp;

– 查询员工表中9对2取余的结果

   select first_name,salary,mod(9,2) from s_emp;

1.4 列的别名

select 列名1 [as] 列的别名1, 列名2 [as] 列的别名2, … from 表名;

如：
– 查询员工表中所有员工的年薪，并将列名设置为 ‘年薪’

   select salary, salary*16 as 年薪 from s_emp;

– as 关键字可以省略

   select salary, salary*16 年薪 from s_emp;

– 希望在年薪中间加上空格

   select salary, salary*16 年 薪 from s_emp;   -- error

– 更正上述的错误

   select salary, salary*16 "年 薪" from s_emp;

– 查询员工表中所有员工的年薪，并将列名设置为’Income’

   select salary, salary*16 Income from s_emp;

– ''和""中括起来的内容是区分大小写的

   select salary, salary*16 "Income" from s_emp;

注意：

   a.当别名中需要区分大小写，或者有空格这类特殊字符时，需要使用 双引号 括起来
   b.当需要描述字符串数据时，就需要使用 单引号 括起来；

1.5 字符串的拼接

在sql语句中使用 || 表示拼接字符串的含义。

如：
– 查询员工表中所有员工的姓名

   select last_name, first_name from s_emp;

– 将姓名拼接起来组成一个整体，并起别名为 “姓名”

select last_name||first_name "姓  名" from s_emp;

– 将姓名之间使用字符串’-'拼接起来

   select last_name||'-'||first_name "姓   名" from s_emp;

– 将姓名之间使用字符串’_'拼接起来

   select last_name||'_'||first_name "姓   名" from s_emp;

– 将姓名之间使用单引号拼接起来

   select last_name||'''||first_name "姓   名" from s_emp;  -- error

– 单引号代表转义字符 ''表示显示一个单引号

   select last_name||''''||first_name "姓   名" from s_emp;
   select last_name||''''''||first_name "姓   名" from s_emp;
   select last_name||''''||''''||first_name "姓   名" from s_emp;

1.6 where子句

select 列名1 [as] 列的别名1, 列名2 [as] 列的别名2, … from 表名 where 查询的条件表达式;

1.7 常用的比较运算符

 = 等于 <> 不等于   < 小于   <= 小于等于   > 大于    >= 大于等于

注意：

sql语句中没有赋值的概念，只有=表示等于的含义。

如：
– 查询员工表中薪水等于800的员工编号，名字以及薪水信息

  select id, first_name, salary from s_emp
   where salary = 800;

– 查询员工表中薪水不等于800的员工编号，名字以及薪水信息

 select id, first_name, salary from s_emp
   where salary <> 800;

– 查询员工表中薪水大于1000的员工编号，名字以及薪水信息

 select id, first_name, salary from s_emp
   where salary > 1000;

– 查询员工表中薪水小于等于800的员工编号，名字以及薪水信息

  select id, first_name, salary from s_emp
   where salary <= 800;

1.8 常用的逻辑运算符

and(&&) 并且 or(||) 或者 not(!) 取反

如：
– 查询员工表中薪水大于等于1000并且部门编号为41的员工信息

  select id, first_name, salary, dept_id from s_emp
   where salary >= 1000 and dept_id = 41;

 select id, first_name, salary, dept_id from s_emp
   where salary >= 1000 && dept_id = 41;               -- error

– 查询员工表中薪水大于等于1000 并且部门编号不为41的员工信息

 select id, first_name, salary, dept_id from s_emp
   where salary >= 1000 and dept_id <> 41;
   select id, first_name, salary, dept_id from s_emp
   where salary >= 1000 and not dept_id = 41;

– 查询员工表中薪水小于800 或者部门编号为42的员工信息

 select id, first_name, salary, dept_id from s_emp
   where salary < 800 or dept_id = 42;

– 查询员工表中薪水小于1000 并且部门编号为31 或者 42

 select id, first_name, salary, dept_id from s_emp
   where salary < 1000 and dept_id = 31 or dept_id = 42;   -- 优先级
   select id, first_name, salary, dept_id from s_emp
   where salary < 1000 and (dept_id = 31 or dept_id = 42); -- 提高优先级

– 见识一下极限条件

   select * from s_emp where 1 = 1;  -- 全部查询完毕
   select * from s_emp where 1 != 1; -- 没有报错，但一条记录都没有查询出来
   select * from s_emp where 1 <> 1; -- 推荐该运算符
   -- 查询员工的日薪，last_name = 'Dancs';
   select last_name, salary, salary/21 from s_emp
   where last_name = 'dancs';        -- 没有查询到
   select last_name, salary, salary/21 from s_emp
   where last_name = 'Dancs';        -- 可以查询到

注意：

  a.and运算符的优先级高于or，该运算符会被优先处理
  b.在where子句中的条件是从右向左依次判断的，
     对于and运算符来说，应该尽可能将条件更容易为假的放在右边；
     对于or运算符来说，应该尽可能将条件更容易为真的放在右边；

1.9 between…and的使用

 between ... and  表示在...和...之间，该语句表达的是闭区间。

如：
– 查询员工表中薪水范围在800到1000之间的所有员工编号、名字以及薪水信息

   select id, first_name, salary from s_emp
   where salary between 800 and 1000;
   select id, first_name, salary from s_emp
   where salary >= 800 and salary <= 1000;  
   select id, first_name, salary from s_emp
   where salary > 800 and salary < 1000;

– 查询员工表中薪水范围不在1000到1300之间

 select id, first_name, salary from s_emp
   where salary not between 1000 and 1300;
   select id, first_name, salary from s_emp
   where salary < 1000 or salary > 1300;

– 常见的错误，不会报错但啥也查不到

 select id, first_name, salary from s_emp
   where salary between 1000 and 800; 
   select id, first_name, salary from s_emp
   where salary >= 1000 and salary <= 800;

案例：
– 查询员工表中提成为空的员工编号，名字以及提成信息

   select id, first_name, commission_pct from s_emp    -- 没有报错，但无结果
   where commission_pct = null;

– 查询员工表中提成不为空的员工编号，名字以及提成信息

  select id, first_name, commission_pct from s_emp   -- 没有报错，但无结果
   where commission_pct <> null;

1.10 空值的处理

is null - 用于判断是否为空
is not null - 用于判断是否不为空

如：
– 查询员工表中提成为空的员工编号，名字以及提成信息

 select id, first_name, commission_pct from s_emp    -- 查询有结果
   where commission_pct is null;

– 查询员工表中提成不为空的员工编号，名字以及提成信息

 select id, first_name, commission_pct from s_emp   -- 查询有结果
   where commission_pct is not null;

– 查询员工表中所有员工的薪水，加上提成的薪水

 select salary, salary+commission_pct from s_emp;   -- 查询结果有问题

 nvl(参数1, 参数2) - 若参数1不为空，则函数的结果就是参数1的数值
                     - 若参数1为空，则函数的结果就是参数2的数值

如：
– 查询员工表中所有员工的薪水，加上提成的薪水

  select salary, salary+nvl(commission_pct, 0) from s_emp;

注意：

任何数据和空运算的结果还是空！

1.11 去重处理

select [distinct] 列名1 [as] 列的别名1, 列名2 [as] 列的别名2, … from 表名 where
查询的条件表达式;

如：
– 查询员工表中薪水大于800的员工编号，名字以及薪水信息

 select id, first_name, salary from s_emp
   where salary > 800;

– 查询员工表中薪水大于800的薪水等级，去掉重复的薪水

  select distinct salary from s_emp
   where salary > 800;

– 查询员工表中薪水、领导的编号信息

   select salary, manager_id from s_emp;

– 查询员工表中去重之后的薪水和领导编号
– 只有薪水和领导编号都相同时才能去重

   select distinct salary, manager_id from s_emp;

–查看表结构s_dept表，并且从s_dept表中，查询ID、name字段对应的内容

  desc s_dept;
   select id, name from s_dept;

–查看员工表中所有员工的每个月涨薪100元后的年薪

  select salary, (salary+100+nvl(commission_pct,0))*16 "年  薪" from s_emp;

–当前时间使用sysdate获取,计算一下每位员工在公司的天数，要求显示字段：

--id、first_name、入职时间、在公司的天数，其中在公司天数用days显示(起别名)
   select id, first_name, start_date, sysdate-start_date "days" from s_emp;

– 查询当前系统的时间，由于s_emp有25条记录，因此查询结果为25条但内容一样

   select sysdate from s_emp;

– 查看dual这张伪表的结构

  desc dual;

– 若以后查询的内容与表中内容无关时，则使用伪表来满足sql的语法格式

   select sysdate from dual;

–查询员工信息：员工编号、员工姓名以及员工的领导编号，没领导就设置为-1

  select id "员工编号", last_name||'-'||first_name "员工姓名", nvl(manager_id, -1) "领导编号" from s_emp;

(1)Select查询语句

1.Select查询语句
1.1 复习查询语句 select [distinct] 字段名1 [as 别名1], 字段名2 [as 别名2], …
from 表名 [where 查询的条件];

如：

 -- 查询员工表中部门编号为41或42的员工编号、员工名字以及部门编号
   select id, first_name, dept_id from s_emp
   where dept_id = 41 or dept_id = 42;

1.2 按照范围进行查找

between ... and ...   -- 表示查询在...和...之间的内容，而且是闭区间
in ...                -- 表示查询在...里面的任何内容
not in ...            -- 表示查询不在...里面的数据内容

如：

 -- 查询员工表中部门编号在41和42之间的员工编号、员工名字以及部门编号
   select id, first_name, dept_id from s_emp
   where dept_id between 41 and 42;
   select id, first_name, dept_id from s_emp
   where dept_id >= 41 and dept_id <= 42;
   select id, first_name, dept_id from s_emp
   where dept_id in(41, 42);
  
   -- 查询员工表中所有部门编号的员工编号、员工名字以及部门编号
   select id, first_name, dept_id from s_emp;
   -- 查询员工表中所有部门编号并且进行去重处理
   select distinct dept_id from s_emp;
   -- 查询员工表中部门编号在41和42之间的员工编号、员工名字以及部门编号
   -- 由于not in使用太复杂而且效率较低，因此以后的开发中尽量不要使用
   select id, first_name, dept_id from s_emp
   where dept_id not in(43, 34, 44, 31, 35, 50, 45, 32, 33, 10);

1.3 按照条件进行模糊查询

 like ...  -- 表示像...，通常进行模糊查询，搭配使用的通配符有：
        _  -- 表示任意一个字符
        %  -- 表示任意多个字符
 not like ... (了解)

如：

-- 查询员工表中名字使用B开头的员工编号和员工名字
   select id, first_name from s_emp
   where first_name like 'b%';        -- 查询不到数据
   select id, first_name from s_emp
   where first_name like 'B%';
   -- 查询员工表中名字里含有'德'的员工编号和员工名字
   select id, first_name from s_emp
   where first_name like '%德%';
   -- 查询员工表中名字末尾是'德'前面只有一个字符的员工编号和员工名字
   select id, first_name from s_emp
   where first_name like '_德';
   -- 查询员工表中名字末尾是'克'的员工编号和员工名字
   select id, first_name from s_emp
   where first_name like '%克';
   -- 查询员工表中名字中含有'拉'的员工编号和员工名字
   select id, first_name from s_emp
   where first_name like '%拉%';
   -- 查询员工表中名字末尾是'拉'的员工编号和员工名字
   select id, first_name from s_emp
   where first_name like '%拉';

   -- 向员工表中插入数据内容 '1001' '_欣霖'
   insert into s_emp (id, first_name) values('1001', '_欣霖');
   -- 查询员工表中使用'_'为开头的员工编号和员工名字
   select id, first_name from s_emp
   where first_name like '_%';      -- 其中_被当做通配符处理，查询到所有数据
   -- 尝试使用单引号进行转义
   select id, first_name from s_emp
   where first_name like''_%';      -- error: 无效的字符，单引号无法转义_
   -- 实现对_进行转义
   -- 表示将'\'当做转义字符来转义'\'后面字符的含义
   select id, first_name from s_emp
   where first_name like '\_%' escape '\'; 
   -- 表示将'#'当做转义字符来转义'\'后面字符的含义
   select id, first_name from s_emp
   where first_name like '#_%' escape '#';

– 查询“山东”的学生姓名、电话、住址 student学生表

 select studentname, phone, address from student
   where address = '山东';                          -- 语句无误，查不到数据
   select studentname, phone, address from student
   where address like '山东%';

– 查询名称中含有“p”字母的科目信息 subject课程表

   select * from subject
   where subjectname like '%p%';

– 查询学号为S1101004的指定1，2，3科目考试成绩 result成绩表

   select studentno, studentresult, subjectid from result
   where studentno = 'S1101004' and subjectid in (1, 2, 3);

1.4 order by子句

select [distinct] 字段名1 [as 别名1], 字段名2 [as 别名2], … from 表名
[where 查询的条件]; [order by 字段/别名/位置编号 asc/desc] ; – 默认是从小到大排序(asc)

– 查询员工表中所有员工的名字和薪水，并且按照薪水进行排序

select first_name, salary from s_emp
   order by salary;                      -- 默认按照升序排列，而且null放在最后
   select first_name, salary from s_emp

order by salary asc; – asc可以写也可以不写

– 查询员工表中所有员工的名字和薪水，要求按照薪水进行降序排列

select first_name, salary from s_emp
   order by salary desc;                 -- null放在最前面，影响体验度
   -- 若希望将null值放在后面
   select first_name, salary from s_emp
   order by salary desc nulls last;

– 查询员工表中所有员工的名字和薪水，将薪水起别名为 sal并按照sal进行升序

   select first_name, salary sal from s_emp
   order by sal;
   -- 查询员工表中所有员工的名字和年薪，并按照年薪进行升序排列
   select first_name, salary, salary*16 "年 薪" from s_emp
   order by "年 薪";

 -- 查询员工表中所有员工的名字和薪水，并根据位置编号(下标)进行升序排序
   select first_name, salary from s_emp
   order by 0;                            -- error，位置编号从1开始
   select first_name, salary from s_emp
   order by 1;                            -- 表示按照位置编号1(名字)开始排序
   select first_name, salary from s_emp
   order by 2;                            -- 表示按照位置编号2(薪水)开始排序
   select first_name, salary from s_emp
   order by 3;                            -- error，查询的列数总共2列
   select first_name, salary, salary*16 from s_emp
   order by 3;                            -- 表示按照年薪排序

  -- 查询员工表中所有员工的部门编号，薪水，要求先按照部门编号进行降序排序
   select dept_id, salary from s_emp
   order by dept_id desc;   
   -- 查询员工表中所有员工的部门编号，薪水，先按照部门编号降序，再按照薪水升序
   select dept_id, salary from s_emp
   order by dept_id desc, salary;    
   -- 查询员工表中所有员工的部门编号，薪水，先按照部门编号降序，再按照薪水降序
   select dept_id, salary from s_emp
   order by dept_id desc, salary desc;    -- 多字段排序时，需要写多个desc
   -- 先按照部门编号降序，再按照薪水降序，再按照员工编号升序
   select dept_id, salary, id from s_emp
   order by dept_id desc, salary desc, id asc;

1.5 字符串相关的常用函数

如：
   -- 实现计算字符串'hello'的长度
   select length('hello') 字符串长度 from dual;
   -- 实现将字符串'hello'转换为大写
   select upper('hello') 转换为大写 from dual;
   -- 实现将字符串'HELLO'转换为小写
   select lower('HELLO') 转换为小写 from dual;   
   -- 实现将字符串'hello'中的首字母转换为大写
   select initcap('hello') 首字母转大写 from dual;
   -- 实现将字符串'hello'中从下标2开始取出3个字符，下标从1开始
   select substr('hello', 2, 3) 获取子串 from dual;  -- ell
   -- 实现将字符串'hello'和'world'进行拼接
   select concat('hello', 'world') 拼接字符串 from dual; -- helloworld
   -- 实现字符串中内容的替换，将'e'替换为'E'
   select replace('hello', 'e', 'E') 字符串替换 from dual; -- hEllo
   -- 实现字符串中子串的查找，查找'l'第一次出现的下标位置
   select instr('hello', 'l') 子串查找 from dual; -- 3
   -- 实现去除字符串中两端的空白字符
   select length(trim('   hello     ')) 去除空白字符 from dual;

1.6 数值相关的常用函数

如：
   -- 查询数据3.1415926保留3位小数并四舍五入后的结果
   select round(3.1415926, 3) from dual;   -- 3.142
   -- 查询数据保留1位小数并四舍五入后的结果
   select round(3.1415926, 1) from dual;   -- 3.1
   -- 查询数据保留0位小数，也就是取整数并四舍五入的结果
   select round(3.1415926, 0) from dual;   -- 3
   -- 查询数据保留-1位小数，也就是将整数的最后一位进行四舍五入
   select round(13.1415926, -1) from dual; -- 10

   -- 查询数据3.1415926保留3位小数, 没有四舍五入后的结果
   select trunc(3.1415926, 3) from dual;   -- 3.141
   -- 查询数据保留1位小数并没有四舍五入后的结果
   select trunc(3.1415926, 1) from dual;   -- 3.1
   -- 查询数据保留0位小数，也就是取整数并没有四舍五入的结果
   select trunc(3.1415926, 0) from dual;   -- 3
   -- 查询数据保留-1位小数，也就是将整数的最后一位不进行四舍五入
   select trunc(13.1415926, -1) from dual; -- 10

1.7 日期相关的函数

to_date() - 主要用于将字符串类型转换为日期类型后插入到数据库中；
to_char() - 主要用于将日期类型转换为字符串类型后从数据库中取出数据；

如：

 -- 查询当前系统时间
   select sysdate from dual;
   -- 查询当前系统时间的昨天
   select sysdate-1 from dual;
   -- 查询当前系统时间的明天
   select sysdate+1 from dual;
   -- 查询员工表中员工的编号、名字以及入职日期，要求入职日期按照年月日
   select id, first_name, start_date from s_emp;
   select id, first_name, to_char(start_date, 'yyyy-mm-dd') 入职日期 from s_emp;
   -- 向员工表中插入编号为'1005', '张斌',to_date('2019-07-23', 'yyyy-mm-dd')
   insert into s_emp (id, first_name, start_date) values('1005', '张斌',to_date('2019-07-23', 'yyyy-mm-dd')); 

   -- 查询当前系统时间的下一个月
   select add_months(sysdate, 1) from dual;
   -- 查询当前系统时间的下两个月
   select add_months(sysdate, 2) from dual;
   -- 查询当前系统时间的上一个月
   select add_months(sysdate, -1) from dual;


   -- 查询当前系统时间+1个月+1天+1小时+1分钟后的时间
   select to_char(add_months(sysdate, 1) + 1 + 1/24 + 1/24/60, 'yyyy-mm-dd hh24:mi:ss') 处理后的时间 from dual;

   -- 查询当前系统时间并按照dd进行截取，也就是dd后面的数据全部丢弃
   select to_char(trunc(sysdate, 'dd'), 'yyyy-mm-dd hh24:mi:ss') from dual; 
   -- 查询当前系统时间并按照mm进行截取
   select to_char(trunc(sysdate, 'mm'), 'yyyy-mm-dd hh24:mi:ss') from dual;
   -- 查询当前系统时间并按照yyyy进行截图
   select to_char(trunc(sysdate, 'yyyy'), 'yyyy-mm-dd hh24:mi:ss') from dual;

案例讲解：
– 求当前月份的最后一分钟0秒表示的date数据

  -- 2019-7-31  23:59:00
   select to_char(trunc(add_months(sysdate, 1), 'mm')-1/24/60, 'yyyy-mm-dd hh24:mi:ss') from dual;

– 求下个月的第三天的倒数十分钟0秒表示的date数据**

 -- 2019-8-03  23:50:00
   select to_char(trunc(add_months(sysdate, 1), 'mm')+3 - 1/24/60*10, 'yyyy-mm-dd hh24:mi:ss') from dual;

– 查询s_emp表格(id,last_name,start_date),按照start_date排序

查询的条件为: 入职日期在2018年1月1日到2018年12月31日

   select id, last_name, start_date from s_emp
   where start_date between to_date('2018-01-01', 'yyyy-mm-dd')
   and to_date('2018-12-31', 'yyyy-mm-dd')
   order by start_date;

– 查询员工表中部门在31、32、33的员工编号、员工姓名以及部门编号信息

 select id, last_name||first_name "姓 名", dept_id from s_emp
   where dept_id in (31, 32, 33);

– 查询成绩表中学生编号、课程编号以及学生成绩信息，要求按照学生编号
– 进行降序排列，若学生编号一样按照课程编号降序，
– 若课程编号一样按照成绩升序排序

 select id, subjectid, studentresult from result
   order by id desc, subjectid desc, studentresult asc;

– 查询s_emp表所有first_name包含b或B的员工，显示id、first_name、salary信息

select id, first_name, salary from s_emp
   where first_name like '%b%' or first_name like '%B%';
   select id, first_name, salary from s_emp
   where lower(first_name) like '%b%';

– 显示s_emp表中first_name中4个字符以后的内容

 select first_name, substr(first_name, 5, length(first_name)-4) from s_emp;

– 查询今天过生日的员工信息，假设入职日期就是出生日期

select id, first_name, to_char(start_date, 'yyyy-mm-dd') from s_emp
   where to_char(start_date, 'mm-dd') = to_char(sysdate, 'mm-dd');

(1)Select查询语句

1.Select查询语句

1.1 常用的聚合(多行)函数

所谓单行函数主要指单条数据传入给该函数后处理的结果还是单条数据；
所谓聚合(多行)函数主要指多条数据传入给函数后处理的结果是单条数据；

常用的聚合函数有：

 sum() - 主要用于实现多个数据累加和的计算，只能计算数值类型的数据；
 avg() - 主要用于实现多个数据平均值的计算，只能计算数值类型的数据；
 max() - 主要用于实现多个数据最大值的计算，可以计算任意类型的数据；
 min() - 主要用于实现多个数据最小值的计算，可以计算任意类型的数据；
 count() - 主要用于实现多个数据的计数，可以计算任意类型数据，如：count(*)

如：

 -- 查询员工表中所有员工的总薪水信息
   select sum(salary) 人力成本 from s_emp;
   -- 查询员工表中所有员工的平均薪水信息
   select avg(salary) 平均薪水 from s_emp;
   -- 查询员工表中所有员工的最高薪水信息
   select max(salary) 最高薪水 from s_emp;
   -- 查询员工表中资历最小的员工信息
   select max(start_date) 资历最小 from s_emp;
   -- 查询员工表中的最低薪水
   select min(salary) 最低薪水 from s_emp;
   -- 查询员工表中的资历最老的员工信息
   select min(start_date) 资历最老 from s_emp;
   -- 查询员工表中的员工数量
   select count(*) 员工数量 from s_emp;

Test：

 -- 查询成绩表中所有学员的总成绩
   select sum(studentresult) 总成绩 from result;
   -- 查询成绩表中所有学员成绩的平均成绩
   select avg(studentresult) 平均成绩 from result;
   -- 查询成绩表中所有学员的最高成绩
   select max(studentresult) 最高分 from result;
   -- 查询成绩表中所有学员的最低成绩
   select min(studentresult) 最低分 from result;
   -- 查询成绩表中所有学员的成绩个数
   select count(*) 成绩个数 from result;
   -- 查询员工表中所有员工提成的平均值
   select round(avg(nvl(commission_pct, 0)), 2) from s_emp;

1.2 group by子句

 select 字段名1 [as 别名1], 字段名2 [as 别名2], ... from 表名
   [where 条件表达式]
   [group by 字段名1, 字段名2]
   [order by 字段名/别名/位置编号];

如：

  -- 查询学生表中每个年级的总人数，显示年级编号和总人数
   select gradeid, count(*) from student
   group by gradeid
   order by count(*);

注意：

select后面允许出现的字段只能是group by后面跟的字段以及聚合函数。

Test：

 -- 查询每门课程的平均成绩，每...就表示按照...分组  result成绩表
   select subjectid 课程编号, round(avg(studentresult),2) 平均分 from result
   group by subjectid
   order by avg(studentresult);
   
   -- 查询学生表中每个年级中每种性别的总人数,，并按照年级排序
   select gradeid, sex, count(*) from student
   group by gradeid,sex
   order by gradeid;

   -- 查询员工表中每个部门的员工人数，并按照人数进行排序
   select dept_id, count(*) from s_emp
   group by dept_id
   order by count(*);

1.3 having子句

select 字段名1 [as 别名1], 字段名2 [as 别名2], ... from 表名
   [where 条件表达式]
   [group by 字段名1, 字段名2]
   [having 条件表达式]
   [order by 字段名/别名/位置编号];

如：

 -- 查询学生表中每个年级的总人数，并将总人数超过18人的年级编号显示出来
   select gradeid, count(*) from student
   where count(*) > 18
   group by gradeid;                     -- error: where子句中不能使用聚合函数

   select gradeid, count(*) from student
   group by gradeid
   having count(*) > 18                  -- having子句可以对分组的结果再次筛选
   order by count(*);

   -- 查询每个年级的总课时，并升序排列 （subject表）
   select gradeid, sum(classhour) from subject
   group by gradeid
   order by sum(classhour) asc;

   -- 查询每个学员的平均分（result表）
   select studentno, round(avg(studentresult), 2) from result
   group by studentno;

   -- 查询每门课程的平均分，并降序排列（result表）
   select subjectid, round(avg(studentresult), 2) from result
   group by subjectid
   order by avg(studentresult) desc;

   -- 查询每个学生的总分，并降序排列（result表）
   select studentno, sum(studentresult) from result
   group by studentno
   order by sum(studentresult) desc;

   -- 查询每门课程的平均分超过60的课程信息 
   select subjectid, round(avg(studentresult), 2) from result
   group by subjectid
   having avg(studentresult) > 60;

   -- 查询员工表中每个部门的总人数超过2个人的部门信息
   select dept_id, count(*) from s_emp
   group by dept_id
   having count(*) > 2;

   -- 查询员工表中每个部门的平均薪水超过1000元的部门信息
   select dept_id, round(avg(salary), 2) from s_emp
   group by dept_id
   having avg(salary) > 1000; 
   
Select查询语句的执行流程：
   from 表名  =>  where子句 => group by分组 => having子句 => select取出数据
=> order by子句

1.4 子查询

 所谓子查询主要指 让一次查询的结果作为条件再次进行查询的过程，又叫做多次查询
如：
   -- 从学生表中查询比'崔今生'年龄小的学生信息，也就是出生日期比'崔今生'大
   -- 首先从学生表中查询名字为'崔今生'的出生日期
   select borndate from student
   where studentname = '崔今生';          -- 查询结果: 1990-1-5

   -- 其次从学生表中查询出生日期比'崔今生'出生日期大的学生信息
   select borndate from student
   where borndate > to_date('1990-01-05', 'yyyy-mm-dd');
   
   -- 使用子查询来实现上述功能
   select borndate from student
   where borndate > (select borndate from student
   	where studentname = '崔今生');   

练习：
   -- 使用子查询在员工表中查询与'埃琳娜'在同一个部门的员工信息
   -- 首先查询员工表中'埃琳娜'所在的部门编号
   select dept_id from s_emp
   where first_name = '埃琳娜';               -- 查询结果：41

   -- 其次查询员工表中与'埃琳娜'所在部门编号相等的员工信息
   select * from s_emp
   where dept_id = 41;

   -- 合并为子查询语句
   select * from s_emp
   where dept_id = (select dept_id from s_emp
   	where first_name = '埃琳娜');

案例讲解：
    -- 查询'JavaSE'课程并且考了100分的学生信息
    select * from student
    where studentno = 'S1101019';

    -- 从课程表中查询'JavaSE'课程对应的课程编号
    select subjectid from subject
    where subjectname = 'JavaSE';                 -- 查询结果： 1

    -- 从成绩表中查询'JavaSE'课程并且考了100分的学生编号
    select studentno from result 
    where subjectid = 1 and studentresult = 100;  -- 查询结果：S1101019

    -- 合并上述代码
    select * from student
    where studentno = (select studentno from result 
    	where subjectid = (select subjectid from subject
    		where subjectname = 'JavaSE' ) and studentresult = 100 );

练习：
    -- 查询“青铜”阶段开设的课程
    -- 该需求涉及到  grade阶段表 和 subject课程表，表之间通过 gradeid 字段关联 
    -- 先由阶段名称可以查询到阶段编号，再由阶段的编号得到课程名称
    select subjectname from subject 
    where gradeid = (select gradeid from grade 
    	where gradename = '青铜');   

    -- 查询参加最近一次“HTML和CSS网页技术”考试成绩的最高分和最低分
    -- 该需求涉及到 subject课程表 和 result成绩表，表之间通过subjectid字段关联
    -- 先由课程名称可以查询到课程编号，再由课程编号可以查询到考试成绩和
    -- 最近一次考试的时间，再根据考试成绩得到最高分和最低分
    select max(studentresult) 最高分, min(studentresult) from result
    	where subjectid = ( select subjectid from subject
    		where subjectname = 'HTML和CSS网页技术')
    	and examdate = ( select max(examdate) from result
    		where subjectid = ( select subjectid from subject
    			where subjectname = 'HTML和CSS网页技术')); 

案例讲解：
    -- 查询'JavaSE'课程考试成绩不及格的学生名称
    -- 该需求涉及到 subject课程表 和 result成绩表 以及 student学生表
    -- 其中subject表和result表之间采用 subjectid 字段关联
    -- 其中result表和student表之间采用 studentno 字段关联
    -- 先由课程名称可以查询到课程编号，再由课程编号以及考试成绩可以查询到学号
    -- 再根据学生的学号可以得到学生的名字
    select studentname from student 
    where studentno = ( select studentno from result
    	where studentresult < 60 and subjectid =(select subjectid from subject 
    		where subjectname  = 'JavaSE' ));     -- error: 有多个人不及格
    
    -- 当子查询的结果有多个数据内容时，则将 = 换成in即可
    select studentname from student 
    where studentno in ( select studentno from result
    	where studentresult < 60 and subjectid =(select subjectid from subject 
    		where subjectname  = 'JavaSE' ));

    -- 当子查询的结果有多个数据内容时，则在 = 的后面加上any关键字即可
    select studentname from student 
    where studentno = any ( select studentno from result
    	where studentresult < 60 and subjectid =(select subjectid from subject 
    		where subjectname  = 'JavaSE' )); 

练习：
   -- 查询参加“JavaSE”课程最近一次考试的在读学生名单
   -- 该需求涉及到 subject课程表 和 result成绩表 以及 student学生表
   -- 其中subject表和result表之间采用 subjectid 字段关联
   -- 其中result表 和 student表之间采用 studentno 字段关联
   -- 先由课程名称得到课程编号，再由课程编号和最近一次考试时间得到学生编号
   -- 最后由学生编号得到学生的名字
   select studentname from student
   where studentno = ( select studentno from result
   	where examdate = ( select max(examdate) from result 
   		where subjectid = ( select subjectid from subject 
   			where subjectname = 'JavaSE'))
   	and subjectid = ( select subjectid from subject 
   		where subjectname = 'JavaSE'));
   


   -- 查询和Ben一个部门的员工的id，first_name,dept_id,salary信息
   -- 该需求涉及到s_emp员工表
   -- 先查询Ben所在部门的编号，再根据Bean所在部门的编号查询该部门其它员工信息
   select id, first_name, dept_id, salary from s_emp
   where dept_id = ( select dept_id from s_emp
   	where first_name = 'Ben' );
  

   -- 显示所有工资超过Ben的员工的信息，包括：id,first_name,salary
   -- 该需求涉及到s_emp员工表
   -- 先查询Ben的薪水，再根据Ben的薪水查询比Ben薪水高的员工信息
   select id,first_name,salary from s_emp
   where salary > ( select salary from s_emp
   	where first_name = 'Ben' );

   -- 显示工资高于全公司平均工资的员工信息，包括id,first_name,salary
   -- 该需求涉及到s_emp员工表
   -- 先查询全公司的平均工资，然后根据平均工资查询比平均工资高的员工信息
   select id,first_name,salary from s_emp
   where salary > ( select avg(salary) from s_emp );    

   -- 显示工资高于部门id=43的平均工资 的员工信息，包括id,first_name,salary
   -- 该需求涉及到s_emp员工表
   -- 先查询部门id=43的平均工资，然后查询比部门id=43平均工资还高的员工信息
   select id, first_name, salary from s_emp
   where salary > ( select avg(salary) from s_emp
   	where dept_id = 43 );

   -- 显示所有工资等于 部门43的任意员工 的信息，包括id,first_name,salary
   -- 该需求涉及到s_emp员工表
   -- 先查询部门编号为43号的所有员工的薪水，然后查询薪水等于43号部门中任意
   -- 员工薪水的员工信息
   select id, first_name, salary from s_emp
   where salary = any ( select salary from s_emp
   	where dept_id = 43 );

   -- 返回管理者的信息，id,first_name,salary，也就是id号和manager_id相同
   -- 该需求涉及到s_emp员工表
   -- 先查询员工表中所有的manager_id，再查询id出现在manager_id中的员工信息
   select id, first_name, salary from s_emp
   where id in ( select distinct manager_id from s_emp );


   -- 显示所有工资大于 部门43的所有员工 的信息，包括id,first_name,salary
   -- 该需求涉及到s_emp员工表
   -- 先查询43号部门中的最高薪水，再查询薪水高于43号部门最高薪水的员工信息
   select id, first_name, salary from s_emp
   where salary > ( select max(salary) from s_emp
   	where dept_id = 43 );

   -- 显示平均工资 比部门id=33平均工资高的 部门id和平均工资
   -- 该需求涉及到s_emp员工表
   -- 先查询33号部门的平均工资，再查询平均工资比33号部门平均工资高的部门信息
   select dept_id, avg(salary) from s_emp
   group by dept_id 
   having avg(salary) > ( select avg(salary) from s_emp
   	where dept_id = 33 ); 

------------------------------------------------------------------------------
   (1)Select查询语句
   (2)常用的约束

1.Select查询语句(重中之重)
1.1 复习语法格式
   select 字段名1 [as 别名1], 字段名2 [as 别名2], ... from 表名
   [where 条件表达式]
   [group by 字段名]
   [having 条件表达式]
   [order by 字段名];

1.2 多表查询
   单表查询 - 主要指Select查询语句中from关键字后面只能跟一个表名的查询方式；
   多表查询 - 主要指Select查询语句中from关键字后面可以跟多个表名的查询方式；
            - 也就是指同时从多张表中查询多个字段的方式

1.3 内连接的概念和格式
   内连接主要指两张表都有并且使用比较运算符实现值匹配的所有记录查询的连接方式
   语法格式：
    select [表名1.]字段名1,[表名1.]字段名2,[表名2.]字段名1,[表名2.]字段名2 ...
    from 表名1 [别名1], 表名2 [别名2], ...
    [where 关联条件表达式];

如：
   -- 查询学生表和成绩表中学生的编号、学生的名称以及学生的成绩， 学号相同
   -- 该需求涉及到student学生表 和 result成绩表
   -- 其中student学生表 和 result成绩表 之间使用通过 studentno学号字段关联
   -- 其中学生编号来自于 student表 和 result表
   -- 其中学生名称来自于 student表
   -- 其中学生成绩来自于 result表
   -- error: 未明确定义列，由于两张表中都有studentno字段，因此需要指定表名.
   select studentno, studentname, studentresult from student, result;
   
   select * from student;  -- 查询结果：62行
   select * from result;   -- 查询结果：88行
   -- 下面的查询结果是：5456行  =  62 * 88    (笛卡尔积)
   select student.studentno, studentname, studentresult from student, result; 
   
   -- 实现真正的内连接查询
   select student.studentno, studentname, studentresult from student, result
   where student.studentno = result.studentno;

练习：
   -- 查询员工编号，姓名以及所在部门名称
   -- 该需求涉及到s_emp员工表 和 s_dept部门表
   -- 其中表之间采用 部门编号 字段连接
   -- 其中员工编号和员工姓名来自于 s_emp员工表
   -- 其中部门名称来自于 s_dept部门表
   select s_emp.id, first_name, name from s_emp, s_dept
   where s_emp.dept_id = s_dept.id;

   -- 查询部门编号以及该部门负责的区域名称
   -- 该需求涉及到 s_dept部门表 和 s_region区域表
   -- 其中表之间采用 区域编号 字段连接
   -- 其中部门编号来自于 s_dept部门表
   -- 其中区域名称来自于 s_region区域表
   select s_dept.id, s_region.name from s_dept, s_region
   where s_dept.region_id = s_region.id; 
   
案例：
   -- 查询学生名称、课程名称以及考试成绩
   -- 该需求涉及到三张表：student学生表  subject课程表  result成绩表 
   -- 其中student表 和 result表 之间采用 studentno学生编号关联
   -- 其中result表 和 subject表 之间采用 subjectid课程编号关联
  select studentname, subjectname, studentresult from student, result, subject
  where student.studentno = result.studentno 
  and result.subjectid = subject.subjectid
  order by studentname;

练习：
   -- 查询年级编号为1的年级名称、科目名称及学时
   -- 该需要涉及到 grade年级表  和  subject课程表 
   -- 其中 grade表 和 subject表之间采用 gradeid 字段进行关联
   select grade.gradeid, gradename, subjectname, classhour from grade, subject
   where grade.gradeid = subject.gradeid
   and grade.gradeid = 1;

   -- 查询学生学号、姓名、考试科目名称及成绩
   -- 该需求涉及到 student学生表 和 subject课程表 和 result成绩表
   -- 其中student表和result表之间采用 studentno 字段进行关联
   -- 其中result表 和subject表之间采用 subjectid 字段进行关联
   select student.studentno, studentname, subjectname, studentresult
   from student, result, subject
   where student.studentno = result.studentno
   and result.subjectid = subject.subjectid
   order by studentno, studentresult;

   -- 查询参加“JavaSE”考试的学生姓名、成绩、考试日期
   -- 该需求涉及到: student学生表 和 result成绩表
   -- 其中student表 和 result表 之间采用 studentno 进行关联
   select studentname, studentresult, examdate from student, result
   where student.studentno = result.studentno
   and subjectid = (select subjectid from subject
   	where subjectname = 'JavaSE');

   -- 查询员工编号、员工姓名、员工薪水、薪水等级
   -- 该需求涉及到：s_emp员工表 和 salgrade薪水等级表
   -- 其中s_emp表 和 salgrade表之间不能采用 等值连接，但可以采用 非等值连接
   -- 也就是拿着员工表中的每个薪水到薪水等级表中进行薪资范围的确认
   -- 从而也就确认了薪水等级
   select id, first_name, salary, grade from s_emp, salgrade
   where salary between losal and hisal;

   -- 查询员工编号、员工姓名、领导编号、领导姓名
   -- 该需求设计到：s_emp员工表 和 s_emp员工表
   -- 其中s_emp表 和 s_emp表之间采用 manager_id和id关联
   select e.id, e.first_name, e.manager_id, m.first_name from s_emp e, s_emp m
   where e.manager_id = m.id;
    
1.4 外连接的概念和格式
   外连接主要指满足查询条件匹配的记录会显示出来，而不满足查询条件的记录也会显示出来，若没有的数据使用null填充即可；
   左外连接的语法格式：
    select [表名1.]字段名1,[表名1.]字段名2,[表名2.]字段名1,[表名2.]字段名2 ...
    from 表名1 [别名1] left join 表名2 [别名2], ...
    on 条件表达式;      
如：
    -- 若员工表中并不存在没有部门编号的员工时，则插入1个没有部门编号的员工数据

    -- 查询员工编号、员工姓名、部门名称   使用内连接查询
    -- 该需求涉及到：s_emp员工表 和 s_dept部门表
    -- 其中s_emp表 和 s_dept表 之间采用 部门编号 进行关联
    -- 使用内连接查询到的数据是：员工表中存在该员工而且拥有部门编号的员工信息
    select s_emp.id, first_name, name from s_emp, s_dept
    where s_emp.dept_id = s_dept.id;   

    -- 使用左外连接进行查询
    -- 使用员工表中的每条记录与部门表依次进行匹配，若匹配不上则使用null填充
    select s_emp.id, first_name, name from s_emp left join s_dept
    on s_emp.dept_id = s_dept.id;   


    右外连接的语法格式：
    select [表名1.]字段名1,[表名1.]字段名2,[表名2.]字段名1,[表名2.]字段名2 ...
    from 表名1 [别名1] right join 表名2 [别名2], ...
    on 条件表达式;
如：
    -- 实现向部门表中插入一个新的部门而员工表无员工与之对应
    insert into s_dept values(666, '吹牛', 1);

    -- 使用右外连接进行查询
    -- 使用部门表中的每条记录与员工表依次进行匹配，若匹配不上则使用null填充
    select s_emp.id, first_name, name from s_emp right join s_dept
    on s_emp.dept_id = s_dept.id;

    -- 将两张表交换了一下位置，然后将right改成了left，查询结果与上述方式一致
    select s_emp.id, first_name, name from s_dept left join s_emp
    on s_emp.dept_id = s_dept.id;

1.5 分页查询
    当查询到的数据内容无法在一个页面完整显示时，就需要将查询到的数据内容分成好多页分别加以显示。
如：
    -- 查询员工表中所有员工的编号、名字和薪水
    select id, first_name, salary from s_emp;
    -- 使用员工编号实现分页查询的效果，因为员工编号正好是按照次序依次排列的
    select id, first_name, salary from s_emp
    where id between 1 and 10;
    select id, first_name, salary from s_emp
    where id between 11 and 20;
    select id, first_name, salary from s_emp
    where id between 21 and 30;

    -- 使用rownum伪列实现分页查询的效果
    select rownum, id, first_name, salary from s_emp;
    -- 查询第一页
    select rownum, id, first_name, salary from s_emp
    where rownum between 1 and 10;                     
    -- 查询第二页
    select rownum, id, first_name, salary from s_emp
    where rownum between 11 and 20;                    -- error:查询不到数据
    -- 查询第三页  
    select rownum, id, first_name, salary from s_emp
    where rownum between 21 and 30;                    -- error:查询不到数据

    -- 使用三层嵌套的子查询解决上述问题
    -- 查询员工表中的所有数据内容
    select * from s_emp;
    -- 将查询到的所有内容看做是一张新表，然后给新表加入伪列，并得到伪列所有值
    select rownum r, e.* from (select * from s_emp) e;
    -- 根据伪列的所有值进行分页显示
   select id, first_name, salary from 
   ( select rownum r, e.* from (select * from s_emp) e)
    where r between 1 and 10;  

   select id, first_name, salary from 
   ( select rownum r, e.* from (select * from s_emp) e)
   where r between 11 and 20; 

   select id, first_name, salary from 
   ( select rownum r, e.* from (select * from s_emp) e)
   where r between 21 and 30;

DDL：建立表和删除表

什么是约束？

约束是一种规范，可以对表中字段的加限制和规范，这种限制规范叫约束。

2.数据库中的五大约束

1.主键   primary key 对表中的字段要求非空 null和唯一
  2.唯一   unique      表中的字段是唯一，不可以重复。
  3.非空   not  null   表中的字段值不能是null。
  4.检查约束   check   表中的字段必须符合检查条件
  5.外键约束   foreign key
3.约束的具体实现方式
   1.列级约束   在定义表的某一列时，直接对这一列级加约束条件限制。   
   2.表级约束  在定义完表的所有列之后，在选择某些列加约束限制。

列级约束

建立一张表，给id加列级主键约束
   //小白写法
   create table   xdl_bank_account_29(
  
       id number primary key,
       acc_no  varchar(30),
       acc_password varchar(30),
       acc_money number 
   );
   
  insert into xdl_bank_account_29 values(1,'123','1234',3000);

改造表：建立约束时给约束起别名

起别名的格式：
     1. constraint 表名_id_pk   主键约束
     2. constraint 表名_id_uk   唯一约束
     3. constraint 表名_id_nn   非空约束
     4. constraint 表名_id_ck   检查约束

 create table xdl_bank_account_29(

       id number constraint xdl_bank_account_29_id_pk primary key,  主键约束
       acc_no  varchar(30) constraint xdl_bank_account_29_acc_no_uk unique,  唯一约束
       acc_password varchar(30) constraint xdl_bank_account_29_acc_password_nn not null,非空约束
       acc_money number constraint xdl_bank_account_29_acc_money_ck check(acc_money>200)   检查约束   
       );

小提示：非空约束可以不起约束名，
唯一约束 null空值判断不了
主键约束（唯一+非空约束）一张表只可以有一个，而非空+唯一约束可以有多个（非空约束+唯一约束！=主键约束）检查约束钱数>8000
注意加约束的时候：标识符应该小于30
constraint xdl_bank_account_29_money_ck check(where acc_money>8000)

—表级约束（最大的优点可以做联合约束）

建立完所有的字段，给字段加约束

 create table xdl_bank_account_29(

       id number,
       acc_no  varchar(30),
       acc_password varchar(30)，
       acc_money number，
       constraint xdl_bank_account_id_pk primary key(id)
联合约束：acc_money number constraint xdl_bank_account_id_pk primary key(id,acc_no)
);

注意：

表级约束不支持非空！

外键约束（foreign key）

    外键就是一张表的字段受限于另外一张表
    外键字段的取值，必须是另外一张表中字段对应的值，要么是null值。
有外键的表叫从表或子表，外键字段的值取自主表或父表。

列级外键约束

  部门和员工表
    1.建立主表(先建立主表，在建立子表)
    create table dept(

      id  number constraint dept_id_pk primary key,
      name varchar(30),
      city varchar(30) 
 
    );

2.建立子表

  create table emp(
      id number constraint emp_id_pk primary key,
      name varchar(30),
      salary number,
      gender varchar(3),
      //建立外键约束
      dept_id number constraint emp_dept_id_fk references//(引用) dept(id)
  );

3.插入数据(最好先插父表)

 insert into emp values(1,'faker',30000,'nan',null)
 insert into emp values(1,'faker',30000,'nan',1)//父表部门id没有1，执行会失败

4.删除数据

要先删除子表，最后再删除父表，避免违反完整性。
除非使用级联技术（外键上加上 on delete cascade）

级联技术：（外键上加上 on delete cascade）

 create table dept(
    id  number constraint dept_id_pk primary key,
    name varchar(30),
    city varchar(30) 
 );

create table emp(
  id number constraint emp_id_pk primary key,
  name varchar(30),
  salary number,
  gender varchar(3),
  //建立外键约束
  dept_id number constraint emp_dept_id_fk references dept(id) on delete cascade

);

5.删除表（先删除子表，再删除父表）
    除非使用先断开主外键，在删除表
    drop table emp cascade constraint;
6.级联置空（建表时外键加 on delete set null） //子表不删除，只是外键置空了（建表时外键加 on delete cascade） //导致子表也删除了

   **7.先建表，再加外键**
    create table emp(
      id number constraint emp_id_pk primary key,
      name varchar(30),
      salary number,
      gender varchar(3),
      dept_id //干掉number constraint emp_dept_id_fk references dept(id) on delete cascade

    );
   create table dept(

      id  number constraint dept_id_pk primary key,
      name varchar(30),
      city varchar(30) 
 
   );
  //在设置父子表的外键关系
  alter table emp add constraint emp_dept_id_fk foreign key(dept_id) references
    dept(id) on delete set null;

事务（transaction）

什么是事务？

    事务  事务又叫交易
    在数据库操作的时候，将多个DML（增 删 改）看成一个整体的过程叫事务，要求这些语句必须同时满足或者同时失败。

    补充:
      删除约束--->   
          alter table xdl_bank_account_29 drop constraint xdl_bank_account_29_ac_money_fk;
      忘记约束名，查询
          select  * from user_constraints;
转账：
   -update  xdl_bank_account_29 set acc_money =acc_moneny-1000 where acc_no='bang';
   -update  xdl_bank_account_29 set acc_money =acc_moneny+1000 where acc_no='faker';

****只有commit提交后，转账才能真正成功，否则就是rollback回滚
        commit ：确认提交
        rollback：撤销回滚

事务的注意事项：

  DDL（建表，删表）会自动提交事务
    DML（增，删，改）不会自动提交事务
    DQL没有事务
    TCL事务控制语句 就是commit，rollback

事务的四大特性
     1.原子性  事务中的多个DML是一个逻辑整体，要么都执行，要么都不执行。
     2.隔离性  一个事务操作时，没有提交时，对另一个事务 数据的变化不可见。  
     3.持久性  事务中的DML语句的状态一旦提交，状态就一直保持，直到下一次事务。
     4.一致性  事务中的DML语句的操作状态必须保持一致

视图

什么是视图？

视图本质上就是一条sql语句，是用来对同一分份物理数据做出不同表现的对象。

创建一个视图

创建视图：  
create or replace view my_account_view as select id,acc_no from xxx;
   简单视图增，改，删除数据记得提交，涉及到事务
   复杂视图不可以增删改
注意：视图占空间大小，但大小忽略不计。一般都认为不占大小。
删除视图：
   drop  view  视图名;

索引

什么是索引？
   索引在数据库中叫index，用来加速数据库查询的一个对象。底层采用树状结构。消耗大量的空间和时间 来加速查询。
  主键和唯一键
      系统会自动建立索引  叫唯一性索引。
3.如何建立索引？
   create index  索引名 on 表名(字段名)
   create index 索引名  on  表名(字段名)
   //给员工表name建立索引
   create index dept_name_index on dept(name);
   删除索引
   drop  index  索引名;

---------------------
序列
1.序列的作用：
     oracle中为主键提供不重复的值
2.如何创建一个序列？
     create sequence 序列名；
     create sequence dept_id_seq;
3.如何使用序列？
     调用序列的nextval 或者currval  使用currval必须调用nextval；
     select dept_id_seq.nextval from dual; 

    insert into dept values(dept_id_seq.nextval,'test'||dept_id_seq.currval,'bj');

4.复杂的序列
   create sequence emp_id_seq start with 100 minvalue 102;
   注意：start  with  值  不得小于 minvalue 值；否则会报错

5.删除序列
  drop  sequence 序列名;

表关系的简介和设计

   表和表之间的关系？
      1     1     一对一
 
      1     m     一对多
     
      m     n     多对多

2.基于表关系的表设计

2.1一对一一对多使用范式拆表

范式：常用的有三大范式

 1. 第一范式    
              表中的字段不可再分
 2. 第二范式

              满足第一范式的前提下，表中数据可以被唯一区分，给表设置主键

 3. 第三范式

              满足第二范式的基础上，消除了传递依赖

拾光的遗迹

关注

8
点赞
踩
41

收藏

觉得还不错? 一键收藏
2
评论
Oracle数据库sql语句全

– 创建女孩表，字段有：姓名、年龄、电话号码、是否有男朋友create table girl(name varchar2(20),age number(3),phone varchar2(20),bf number(1));– 向该表中插入’貂蝉’、‘西施’、'王昭君’的个人信息insert into girl values(‘貂蝉’, 16, ‘110’, 1);i...
复制链接

扫一扫