Context、Select(day01)

Oracle
sql: 4天
plsql: 2天
proc: 2天

1. 数据库介绍
   1.1 数据库简介
   1.1.1 数据管理技术的发展
   人工管理阶段：20世纪50年代中期之前

   文件管理阶段：20世纪的50年代末期到60年代中期
   缺点：数据冗余
   数据的不准确
   数据之间的联系弱
   数据库管理阶段：
   数据库技术诞生的三大标志性事件：
   1968年，IBM公司---> IMS
   1969年，DBTG发布了一系列的报告---> 标准和规范
   1970年，IBM的研究员E.F.Codd 发表了一系列论文
   提出了关系模型
   特点：采用复杂的结构化的数据模型
   最低的冗余度
   数据完整性
   数据库系统为用户提供了方便的接口
   控制功能：
   并发控制
   数据库的恢复
   安全性
   系统更加灵活
   信息处理方式不在以程序为中心，而是以数据为中心

   1.1.2 和数据库相关的几个术语
   数据(data):数据库存储的基本对象，包括文字、图形、声音、视频等
   数据库(database):长期存储在计算机内、有组织、可共享的大量数据的集合
   数据库管理系统(DBMS):是位于操作系统和用户之间的一层应用程序，科学的组织、存储数据，高效的获取和维护数据
   数据库系统(DBS): 由数据库、数据库管理系统、应用程序和数据库管理员(或用户) 组成的系统。
   数据库管理员: DBA

   1.1.3 关系数据库
   用二维表保存数据的数据库
   表头 字段
   行
   列
   字段值

1.2 主流的关系型数据库产品
商业型
Oracle Oracle(甲骨文) 10g 11g 12c
DB2 IBM
sql server 微软
sybase
开源
mysql Oracle

1.3 sql语言
SQL(Structured Query Language): 结构化查询语言
SQL分为：
数据查询语言(DQL): select
用来查询数据中的数据 使用最广泛、语法最灵活
数据操作语言(DML): insert delete update
用来改变数据库中的数据
数据定义语句(DDL): create drop alter
用来创建、删除、修改数据库对象
事务控制语句(TCL): commit rollback savepoint
用来保证数据的一致性
数据控制语句(DCL): grant、revoke、create user
用来执行权限的授予和回收、创建用户等

1.4 远程登录服务器
1.4.1 开发工具
sql*plus
sqlplus:oracle提供，和数据库进行交互的工具
命令提示符下的工具
oracle sqldeveloper: 可视化工具

  1.4.2 远程登录
    第一步：远程登录服务器
        telnet  ip
        用户名：
        密码：

    第二步：使用sqlplus工具
      sqlplus
      输入用户名:
      输入密码:

    现场班：
    telnet 172.60.5.80     或172.60.5.81
    用户：oracle
    密码:   oracle

    sqlplus
    输入用户名: openlab
    输入密码: open123

    SQL>
    sqlplus命令：可以不以分号结尾
    sql语句: 必须以分号结尾(学习的内容)

1.5 描述表结构
sqlplus命令：desc
desc 表名[;]
desc s_emp;

Name Null? Type

---

ID 员工编号 NOT NULL NUMBER(7)
LAST_NAME 姓 NOT NULL 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)

Name:表中的字段名
Null? 该字段是否允许为空
NOT NULL:不允许为空
Type: 字段的数据类型
number(p,s) 数字类型
p: 有效数字的位数(从第一个非零数字开始)
1<=p<=38 默认38
s: 精度
-84<=s<=127
number ---> number(38,0)
number(p) ----> number(p,0)
number(p,s)
varchar2(n) 变长字符串 n不能省略 1~4000bytes
char(n) 定长字符串 n默认为1 1~2000bytes

        date    日期

2. select语句
   2.1 几个概念
   选择：选中部分行，全部列
   投影：选中全部行，部分列
   表连接：需要的数据来自于多张表
   2.2 select语句的子句
   基本的查询语句: select..from
   where子句
   order by子句
   单行函数
   表连接
   组函数和分组
   子查询

3.select..from语句
select 字段列表 from 表名;
3.1 列出表中一个字段
select 字段名 from 表名;
-- 列出所有员工的工资
select salary from s_emp;

3.2 列出表中的多个字段
select 字段名,字段名,.... from 表名;
-- 列出员工的编号、名字、职位、工资
select id,first_name,title,salary from s_emp;

3.3 列出表中全部字段
  --
  select id,last_name,first_name,userid,..... from s_emp;
  -- 使用 * 代替所有字段
  select * from s_emp;

3.4 算数运算   一般指的是数字类型
  +  -  *   /  
  -- 列出员工的信息，包括编号、名字、工资、年收入
  12*salary + 1000
  select id,first_name,salary,12*salary+1000 from s_emp;

3.5 字段或表达式 命名 别名
  3.5.1 语法
   字段或表达式 [as] 别名
   select id,first_name as name,salary,
       12*salary+1000 yearsal  from s_emp;
  3.5.2 使用 "" 
    -- 屏蔽特殊字符或关键字等
    select id,first_name as name,salary,
       12*salary+1000 "year sal"  from s_emp;
    -- 大小写敏感
    select id,first_name as name,salary,
       12*salary+1000 "YearSal"  from s_emp;

3.6 sql中的字符串
3.6.1 字符串的表示方式
单引号：''
'a' 'Hello world'
3.6.2 字符串的拼接
||
-- 把员工的first_name和last_name拼接起来
select id,first_name||last_name as name from s_emp;
-- 把员工的first_name和last_name之间拼接一个 .
select id,first_name||'.'||last_name as name
from s_emp;
-- 把员工的first_name和last_name之间拼接一个 '
使用转义字符：'
select id,first_name||''''||last_name as name
from s_emp;

3.7 NULL值的处理
-- 计算员工的年收入，考虑提成
12salary + 12salarycommission_pct/100
12salary(1+commission_pct/100)
-- 下面语句的结果集是错误的
select id,first_name,title,
12salary*(1+commission_pct/100) as yearsal
from s_emp;
NULL值参与运算的表达式的结果为空

  使用函数 nvl 处理NULL值
  nvl(par1,par2) : 当par1不为空，返回par1
                            放par1为空，返回par2
  nvl(12*salary*(1+commission_pct/100),12*salary)
  12*salary*(1+nvl(commission_pct,0)/100)

  select id,first_name,salary,
      12*salary*(1+nvl(commission_pct,0)/100) as yearsal
         from s_emp;

3.8 排重 distinct
-- 列出员工的职位
select distinct title from s_emp;
-- 多列排重
select distinct title,dept_id from s_emp;

4. where子句
   select ...
   from ..
   where 条件;
   4.1 作用
   根据条件对表中的数据进行筛选，挑选出符合条件的行
   4.2 数字类型的条件
   -- 列出工资大于1400的员工的信息
   select id,first_name,salary from s_emp
   where salary>1400;
   4.3 字符串类型的条件
   -- 列出名字为'Ben'的员工的信息
   select id,first_name,salary from s_emp
   where first_name = 'Ben'; -- 有一行结果
   select id,first_name,salary from s_emp
   where first_name = 'ben'; -- 没有结果
   -- sql中没有 ==
   -- sql中不区分大小写，但是字符串的值是区分的
   4.4 比较运算符
   > < >= <= = !=(<> ^=)
   4.5 sql提供的运算符
   4.5.1 表示一个闭区间 [a,b]
   between a and b
   -- 列出工资在[1100,1550] 之间的员工的信息
   select id,first_name,salary from s_emp
   where salary between 1100 and 1550;

   4.5.2 表示一个列表
   in(值1,值2,...)
   address in('北京','上海','广州')
   -- 列出部门编号为 31，42，50的员工的信息
   select id,first_name,dept_id from s_emp
   where dept_id in(31,42,50);

   4.5.3 模糊查询
      like '包含通配符的字符串'
   
      通配符：
      %: 任意长度的任意字符
      _:  一位任意字符
   
      StuName  like '李_%'
   
      -- 列出first_name首字母为'M'的员工的信息
      select id,first_name from s_emp
        where first_name like 'M%';
      -- 列出first_name第二个字母为'a'的员工的信息
      select id,first_name from s_emp
        where first_name like '_a%';
   
      user_tables: 数字字典 保存当前用户的所有表的信息
      desc user_tables;
      -- 列出user_tables中以'S_'开头的表的信息
      select table_name from user_tables
             where table_name like 'S_%';
      -- 使用转义字符  escape
      select table_name from user_tables
             where table_name like 'S\_%' escape '\';
   

   4.5.4 空值的判断
   is null

    -- 列出manager_id为空的员工的信息
    select id,first_name,title from s_emp
         where manager_id = null;
    select id,first_name,title from s_emp
         where manager_id != null;
       -- 使用=或!=判断null值，结果永远为假
   
     select id,first_name,title from s_emp
         where manager_id is null;
   

   4.6 sql中的逻辑运算符
   and or not
   -- 使用and改写 between and 案例
   列出工资在[1100,1550]之间的员工的信息
   select id,first_name,salary from s_emp
   where salary between 1100 and 1550;
   select id,first_name,salary from s_emp
   where salary>=1100 and salary<=1550;

   -- 使用or改写 in 案例
   列出编号为31，42，50的部门的员工的信息
   select id,first_name,dept_id from s_emp
   where dept_id in(31,42,50);
   select id,first_name,dept_id from s_emp
   where dept_id=31 or dept_id=42 or dept_id=50;

   对立面：
   >                     		<=
   <                     		>=
   =                      		!=(<> ^=)
   
   between and      	not between and
   in			not in(注意NULL值)
   like			not like
   is null			is not null
   
   -- 列出有提成的员工的信息
   select id,first_name,commission_pct from s_emp
       where commission_pct is not null;
   

5. order by子句
   5.1 总是出现在一条select语句的最后
   select 字段列表
   from 表名
   where 子句
   ......
   order by子句;
   5.2 语法
   order by 排序标准 排序方式
   排序方式：
   asc 升序 (自然顺序、字典顺序) 默认排序方式
   desc 降序

   5.3 按照工资降序排序，列出dept_id为31，32，33的员工的信息
   select id,first_name,salary,dept_id from s_emp
   where dept_id in(31,32,33)
   order by salary desc;

   5.4 多列排序
   select id,first_name,salary from s_emp
   order by salary;

    order by 排序标准1 排序方式,排序标准2 排序方式
   

   select id,first_name,salary from s_emp
   order by salary,id desc;
   -- 多列排序时，每一个排序标准的排序方式是自己定义的

   5.5 排序时，默认按照最大值处理
   -- 根据manager_id排序，列出员工的信息
   select id,first_name,manager_id from s_emp
   order by manager_id desc;
   -----------------------------------------------------------------总结
   1.数据库介绍
   sql语言
   sqlplus的命令：desc
   数据类型
   2.select语句
   3.select...from
   4.where子句
   比较运算符：> < >= <= = !=(<> ^=)
   sql提供的运算符
   between and
   in
   like
   is null
   sql中的逻辑运算符
   and or not

6. order by子句

---

练习：

1. 查看s_dept和s_region表的表结构
2. 查询s_dept和s_region表中的数据
3. 计算员工的年收入，列出年收入大于15000的员工的信息，
   并对年收入进行降序排序。