轻松学SQL--基于Oracle Database 的学习笔记

 SQL:结构化查询语言
C R U D: 增删改查

table :  name  age  score

desc+表名    ---> 查询表结构
或者用 describe 命令 (desc是describe的简写)
查询语言：SELECT [DISTINCT] {*,column[alias],...} FROM table;
  SELECT identifies what columns FROM identifies which table
 ******* SELECT  查什么  FROM  从哪里查 *******
 
 SELECT dept_id,last_name,manager_id FROM s_emp;

 遇到空值时可以这样操作：
  SELECT last_name,title,salary*NVL(commission_pct,0)/100 COMM FROM s_emp;

 排除查询时出现重名现象 ，用关键字 DISTINCT
   eg:  SELECT DISTINCT name FROM s_dept;
 排除两个字段连合起来还有重复的现象：
   eg:  SELECT DISTINCT dept_id,title FROM s_emp;
 对查询的结果进行排序用 ORDER BY;
   eg:  SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary;
   (默认是升序排序)
   eg:  SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary asc;
   (asc指定为升序排序)
   eg:  SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary desc;
   (desc指定为降序排序)
  

  总结：order by 排序
        asc      升序
        desc     降序

  select last_name EMPLOYEE,start_date from s_emp order by EMPLOYEE desc;
  (在排序的时候可以用别名当做字段[排序的根据]进行排序)--->这里是（EMPLOYEE）。

  select last_name,salary*12 from s_emp order by 2;
  (这里的2是用来指定字段的位置，这里指向 salary*12 ,即按员工的年薪排序)
  select last_name,dept_id,salary from s_emp order by dept_id,salary desc;
  (先按dept_id的升序进行排序，排完后再按salary的降序进行排序)
  注：这里dept_id后没有直接指排序规则，按默认的升序排序。
  

  WHERE关键字：
  SELECT 姓名，部门ID FROM s_emp WHERE dept_id = 42;
  (从s_emp表中查出部门ID=42的员工姓名和部门ID)

  select last_name,dept_id from s_emp where last_name = ‘Biri’;
  (查出员工姓名是Biri的员工的部门ID)
  注：这里的Biri是用单[']号括起来的

  select last_name,salary from s_emp where salary > 1000 order by salary;
  (查出s_emp表中员工工资大于1000的员工的姓名并且按员工工资进行排序)
  注：这里是WHERE和BODER BY结合的例子，ORDER BY放在语句的最后。
  
  查两个范围之间的语句有两种：
  1.select last_name,salary from s_emp where 1500<salary and salary<2000 order by salary;
  2.select last_name,salary from s_emp where salary between 1500 and 2000 order by salary;
  (两句都是用来查出工资在1500-2000之间的员工的姓名)
  注：第二句用了between ..and..语句，在where之后一定要跟上标准；
      between..and..查出的数据是包括限定范围在内的

  in的用法：
  select last_name,dept_id from s_emp where dept_id in (41,42,43);
  (查出部门ID是 41，42，43 的所有员工)

  select last_name,COMMISSION_PCT from s_emp where COMMISSION_PCT is null;
  (查出s_emp表中COMMISSION_PCT为空的所有员工的姓名)
  注：这里用了关键字  ----> is null。

  select last_name,COMMISSION_PCT from s_emp where COMMISSION_PCT is not null;
  (查出s_emp表中COMMISSION_PCT不为空的所有员工的姓名)

  select * from s_emp where last_name like ‘B%’；
  (查出s_emp表中姓名首字母为B的所有员工的信息)

  补充：不等于的三种表示方式： != 、<>、^=
        NOT BETWEEN
        NOT IN
        NOT LIKE
        IS NOT NULL
        [%]是通配符    [_]单配符

  select last_name from s_emp where last_name like '_e%';
  (查出员工姓名第二字符是‘e’的员工)

  select table_name from user_Tables where table_name like 'S/_%' escape '/';
  (查出数据库中以‘S_’开头的所有表，这里‘_’应用‘/’进行转义，如果不转义就会当做单配符使用)
  注：escape关键字用来告诉系统以escape后面的字符做为转义字符。

  注意下面两种语句：
  select last_name,salary,dept_id,title from s_emp where dept_id=41 and title='Stock Clerk';
  查出s_emp表中dept_id=41 且 title='Stock Clerk' 的所有员工
  select last_name,salary,dept_id,title from s_emp where dept_id=41 or title='Stock Clerk';
  查出s_emp表中dept_id=41 或 title='Stock Clerk' 的所有员工

  注意：and 的优先级要比or高
  select last_name,salary,dept_id from s_emp where salary >=1000 and dept_id =44 or dept_id=44;
  (查出s_emp表中salary>=1000且dept_id=44的员工或dept_id=44的员工[优先级])
      select last_name,salary,dept_id from s_emp where salary >=1000 and (dept_id =44 or dept_id=44);
  (时间出s_emp表中dept_id=44或dept_id=42的salary>=1000的员工)

查询语句的总结：
 SELECT[DISTINCT]{*,column[alias],...} FROM table [WHERE condition(s)] [ORDER BY{column,expralias}[ASC|DESC]];
  注：大写部分为关键字，'[]'部分为可选值

 

函数部分：
 function_name(column|expression,[arg1,arg2,...])
 
     转化函数：
 LOWER    转小写
 UPPER    转大写
 INITCAP  首字母大写
 CONCAT 字符连接
 SUBSTR  字符截取
 LENGTH 取字符长度
 NVL

  select last_name from s_emp where lower(last_name) = 'biri';
  (先把表中的数据转化为小写再和指定字段比较)
  注：SQL语句不区分大小写，但字段区分大小写，这样转化可以避免大小写问题。

       数字函数（number functions）:
  ROUND  四舍五入
  TRUNC  截取
   ROUND(45.923,2)--------->45.92
   ROUND(45.923,0)--------->46
   ROUND(45.923,-1)-------->50
   TRUNC(45.923,2)--------->45.92
   TRUNC(45.923)----------->45
   TRUNC(45.923,-1)-------->40

      日期相关函数：
  日期格式：默认格式：  DD-MON-RR
     SYSDATE 可以返回系统当前时间（oracle用）
  select sysdate from dual;
   获取oracle系统当前时间
  select sysdate+1 from dual;
   获取明天日期

   MONTHS_BETWEEN
   ADD_MONTHS
   NEXT_DAY
   LAST_DAY
   ROUND
   TRUNC

   `MONTHS_BETWEEN('01-SEP-95','11-JAN-94')---------------------->19.774194
   `ADD_MONTHS('11-JAN-94',6)------------------------------------>'11-JUL-94'
   `NEXT_DAY('01-SEP-95','FRIDAY')------------------------------->'08-SEP-95'
   `LAST_DAY('01-SEP-95')---------------------------------------->'30-SEP-95'
   `ROUND('25-MAY-95','MONTH')----------------------------------->'01-JAN-95'
   `ROUND('25-MAY-95','YEAR')------------------------------------>'01-JAN-95'
   `TRUNC('25-MAY-95','MONTH')----------------------------------->'01-MAY-95'
   `TRUNC('25-MAY-95','YEAR')------------------------------------>'01-JAN-95'

 

  类型间的互相转化：
   TO_CHAR
   TO_NUMBER
   TO_DATE
    HH24:MI:SS AM

  select start_date, last_name from s_emp where to_char(start_date,'mm')='07';
  (查出s_emp表中入职月份是07的员工，只要是to_char的用法)
  改进：select start_date,last_name from s_emp where to_number(to_char(start_date,'mm'))=7;

ceil 向上取整

 等值连接
 非等值连接查询
 外连接
  左外：以左表为基准，即左表中的数据必须全部出现  (+)在右边
  右外：以右表为基准，即右表中的数据必须全部出现  (+)在左边
   【凡是出现"+"的一边，就有可能出现空值】
 自连接
 内连接
  内连接举例：
  select e.last_name, d.name from s_emp e inner join s_dept d on
   e.dept_id = d.id where e.last_name = 'Biri';
  
 交叉连接

  关联查询语法：
   SELECT table.column,table.column FROM table1,table2 WHERE table1.column1=table2.column2;
  eg: select last_name,name from s_emp,s_dept where dept_id=s_dept.id and last_name='Biri';
  (查出Biri所在部门的名称，这里关联到两个表[s_emp表]和[s_dept表]，
   关联条件是 s_emp.dept_id=s_dept.id)
  注：这里关联到两个表时，字段表示和字段的顺序一定要弄清楚。

 自连接：
  select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where
    e.manager_id = l.id and e.last_name = 'Biri';
  (查出s_emp表中员工名为Biri的员工的领导的名字)
  注：领导也属于员工表的一员，所以本查询语句关联的是s_emp表本身，这里就要把它当作两个表
   来使用，取两个别名来区分表，然后再对应表字段。
  select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where
    e.manager_id = l.id;
  (查出s_emp表中员工对应的领导名，这里有一个缺陷就是可能会少一条数据)
  改进：select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where
    e.manager_id = l.id(+) ;
  (对上面的语句进行了改进，即当总领导的领导为空时也打印出来，这里其实是左外连接)
 标准的SQL语句应该为：
  select e.last_name 员工名,l.last_name 领导名 from s_emp e left outer join s_emp l on
   e.manager_id = l.id;
  (这里使用了语句 "left outer join" 明确表示了左外连接 ，注意后面的where 变成了 on
   相应的 右外连接就"right outer join")

  GROUP BY语句的语法：
   SELECT column,group_function FROM table [WHERE condition]
    [GROUP BY group_by_expression] [HAVING group_condition]
    [ORDER BY column];

  GROUP BY 语句用来分组，HAVING 用来过滤。
  GROUP BY 的组函数
   AVG(DISTINCT|ALL|n)
   COUNT(DISTINCT|ALL|expr|*)
   MAX(DISTINCT|ALL|expr)
   MIN(DISTINCT|ALL|expr)
   SUM(DISTINCT|ALL|n)

  select avg(salary) from s_emp;
  (查出s_emp表中所有员工的平均工资，这里用到了AVG组函数)

  select min(salary) , max(salary) , sum(salary)
   from s_emp
   group by dept_id;
  (以dept_id为标准来查询s_emp表中所有员工的最小、最大和工资之和，此句显示的结果是
   按dept_id的不同进行分组显示)

  注意：观察下面两条语句
  1、select dept_id min(salary) , max(salary) , sum(salary)
   from s_emp
   group by dept_id;
  2、select last_name ,min(salary) from s_emp;
  这两条语句中 第一条 可以执行 而第二条执行不了，原因是，第一条是组函数而第二条是单函数，
   这是由SQL的语法规则而定的

  select e.dept_id,min(e.salary),max(e.salary),sum(e.salary),d.name
   from s_emp e,s_dept d
   where e.dept_id = d.id
   group by dept_id,d.name;
  (以dept_id和s_dept表中的name为标准来查询s_emp表中所有员工的最小、最大、工资之和及部门名称)

  select dept_id,count(*) "Number" from s_emp group by dept_id;
  (按dept_id[部门ID]来统计s_emp表中每个部门有多少个员工[即统计相同的dept_id出现的次数])
  注：dept_id可以和组函数count一起使用的原因是，这里是把dept_id做为了分组的条件

------求除41部门以外各个部门的最小，最高，工资之和，部门名
方法一：select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name
  from s_emp e,s_dept d where e.dept_id=d.id and dept_id !=41
  group by dept_id,d.name order by sum(salary) desc;
方法二：select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name
  from s_emp e,s_dept d where e.dept_id=d.id
  group by dept_id,d.name having dept_id!=41
  order by sum(salary) desc;
  (方法二用到关键字having ，having 必须和group by一起使用，having 的主要功能是过滤
   即保留having后条件成立的内容)
  注：having子句，是对分组之后的数据进行过滤

-----求增均工资高于1500的各个部门的最小，最高，工资之和，部门名
  select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name
  from s_emp e,s_dept d where e.dept_id=d.id
  group by dept_id,d.name having avg(salary)>1500
  order by avg(salary) desc;

 

子查询：
-------查出s_emp表中工资最低的员工
 select last_name from s_emp where salary=(select min(salary) from s_emp);
 (Oracle认可的一个子查询语句，可以简单的理解为查询语句的嵌套)
 注：这里"="后的语句只能返回一个结果，如果返回多个结果就会出错
-------查出平均工资低于32部门的部门ID
 select dept_id ,avg(salary) from s_emp group by dept_id
  having avg(salary)<(select avg(salary) from s_emp where dept_id = 32);
 注：这里用到了组函数、group by、having 子句 还有子查询

 

 

 

 

 

 

 

 

 

 用户名:sys   as   sysdba  
  密码:change_on_install