Died line

35
文章
9
分类
30
标签

单行函数-通用函数-多表查询使用

####sql语句的注意点

  • sql语言不区分大小写
  • 关键字不能被缩写也不能被分行
  • 各子句一般要分行写
  • 使用缩进提高语句可读性。

####基本的sql SELECT语句

1
2
3
4
5
6
7
8
9
10
11
12
desc employees; 		// 获取employees的表结构 这个是sql plus中才有的


SELECT * from employees;  //查询表中所有数据

SELECT EMPLOYEE_ID ,FIRST_NAME, LAST_NAME from employees;	//只查询所选的字段的值

select last_name,salary,12*salary + 1000
from employees   可以在select语句中使用乘法来算一年的工资

 select sysdate,sysdate + 1,sysdate - 1
  2  from dual;  对当前日期进行加减运算 日期不能做乘除

####空值进行乘除操作
– 空值不同于0，凡是空值参与的运算结果都为空

1
2
select employee_id ,salary,salary*(1 + commission_pct)
from employees

####给输出的值起别名，输出结果是使用别名表示的

1
2
select employee_id id,last_name name,12*salary salary
from employees

#####如果你想要将别名小写，可以将别名加上双引号，别名小写，输出结果就是小写

1
2
select employee_id "id",last_name "name",12*salary "salary"
from employees

#####还有一种取别名的方式是加上as但是没必要跟不加是一样的

####连接符||的使用，可以将多列合并,在sql中是使用单引号表示字符串,sql中只有是写别名的时候使用双引号

1
2
select last_name||'`s job is '||job_id as details
from employees

输出

DETAILS
---------------------------------------------
Walsh`s job is SH_CLERK
Feeney`s job is SH_CLERK
OConnell`s job is SH_CLERK
Grant`s job is SH_CLERK
Whalen`s job is AD_ASST
Hartstein`s job is MK_MAN
Fay`s job is MK_REP
Mavris`s job is HR_REP
Baer`s job is PR_REP
Higgins`s job is AC_MGR
Gietz`s job is AC_ACCOUNT

#####distinct 关键字去重一列重复的数据

1
select distinct department_id from employees

####过滤和排序数据
查询员工id大于200的

1
2
3
select employee_id,first_name,last_name 
from employees
where employee_id>200

查询所有工资大于5000的员工

1
2
3
select employee_id,first_name,last_name,salary 
from employees
where salary>5000

查询工资大于4000小于7000的员工

1
2
3
select last_name,hire_date,salary
from employees
where salary >= 4000 and salary < 7000

查询lastname = Higgins 的员工

#####注意字符串和日期需要加单引号

1
2
3
select employee_id,first_name,last_name,salary 
from employees
where last_name = 'Higgins'

查询日期等于1994-6-7的员工姓名

#####注意日期格式需要像下面这样写

######第一种转换方法

1
2
3
select last_name,hire_date
from employees
where hire_date = '7-6月-1994'

######第二种转换方法（推荐）注意当你格式化时间的时候如果前面有0必须加

1
2
3
4
select last_name,hire_date
from employees
--where hire_date = '7-6月-1994'
where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07'

####过滤条件的操作符

= 等于(不是==)

> 大于

>= 大于等于

< 小于

<= 小于等于

<> 不等于 （也可以使用!=）

:= 赋值

####其他比较运算符

between …. and … 在两个值之间

如：

1
2
3
4
5

select last_name,hire_date,salary
from employees
--where salary >= 4000 and salary <= 7000
where salary between 4000 and 7000

in(set) 等于值列表中的一个
和使用or的效果是一样的不过更简洁

1
2
3
4
select last_name,department_id,salary
from employees
--where department_id = 90 or department_id = 80 or department_id = 70
where department_id in(70,80,90)

like 模糊查询

#####查询所有姓名最后一个字符是a的员工

1
2
3
4
select last_name,department_id,salary
from employees
--查询所有姓名最后一个字符是a的员工
where last_name like '%a'

#####查询所有姓名包含a的员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
	select last_name,department_id,salary
	from employees
	--查询所有姓名包含a的员工
	where last_name like '%a%'
#####查询所有姓名第二位是字符a的员工  加下划线代表一个占位符
	select last_name,department_id,salary
	from employees
	--查询所有姓名第二位是字符a的员工
	where last_name like '_a%'
#####查询姓名中包含_的员工
#####注意需要使用\或者任意字符转义  并且在后面使用escape关键字代表那个字符是转义字符
	
	select last_name,department_id,salary
	from employees
	--查询所有姓名中含有_的员工
	where last_name like '%\_%' escape '\'

####is null 判断为空 is not null 非空
查询commission_pct中所有的空值

1
2
3
select last_name,department_id,salary,commission_pct
from employees
where commission_pct is null

####排序 order by

查询工作部门id等于80的员工 按照工资从小到大排序

1
2
3
4
select last_name,department_id,salary,commission_pct
from employees 
where department_id = 80
order by salary asc

查询工作部门id等于80的员工，按照工资从大到小排序

1
2
3
4
select last_name,department_id,salary,commission_pct
from employees
where department_id = 80
order by salary desc

首选工资从低到高如果工资一样按照last_name来排序

1
2
3
4
select last_name,department_id,salary,commission_pct
from employees
--where department_id = 80
order by salary asc,last_name asc

计算一年的工资按照一年的工资来排序

1
2
3
4
select last_name,department_id,salary,commission_pct,salary *12 anunal_sal
from employees
--where department_id = 80
order by anunal_sal

####练习

1.两种查询指定日期的方式

1
2
3
4
select last_name,hire_date
from employees
--where hire_date = '24-4月-1998'
where to_char(hire_date,'yyyy-mm-dd') = '1998-04-24'

2.查询工资在5000-8000的员工 按照从大到小排序

1
2
3
4
select last_name,salary
from employees
where salary >= 5000 and salary <=8000
order by salary desc

3.查询last_name中含有_的员工信息

1
2
3
select employee_id,last_name,salary
from employees
where last_name like '%\_%' escape '\'

4.选择在1994年雇佣的员工姓名和雇佣时间

第一种方法

1
2
3
select last_name,hire_date
from employees
where hire_date like '%94'

第二种方式（推荐）

1
2
3
select last_name,hire_date
from employees
where to_char(hire_date,'yyyy') = '1994'

5.选择公司中没有管理者的员工姓名的job_id

1
2
3
select last_name,job_id
from employees
where manager_id is null

6.选择公司中有奖金的员工姓名，工资和奖金级别

1
2
3
4
5
6
7
8
9
	select last_name,salary,commission_pct 
	from employees
	where commission_pct is not null
```	
7.选择公司中第三个字母是a的员工
```sql	
	select last_name,salary
	from employees
	where last_name like '__a%'

8.选择公司姓名中有a和e的员工

第一种方式

1
2
3
select last_name,salary
from employees
where  last_name like '%a%' and last_name like '%e%'

第二种方式

1
2
3
select last_name,salary
from employees
where  last_name like '%a%e%' or last_name like '%e%a%'

####单行函数

#####大小写控制函数

lower 小写

upper 大写

initcap 保持不变

1
2
select lower('LIYANILOVEYOU'),upper('zeixihuan'),initcap('AskASDJAL')
from dual

当你不清楚字符串是大小写的时候可以全转成小写查询

1
2
select * from employees
where lower(last_name) = 'king'

####字符控制函数

  • concat 字符串拼接

  • substr 字符串截取从第一个数开始 取第二个数的长度

  • length 判断字符串的长度

例1

1
2
select concat('hello','world'),substr('hello,world',2,4),length('hello,world!')
from dual

  • instr 判断单个字符在字符串中首次出现的位置，如果不存在就返回0

    1
    2
    select instr('hello,java','l') 
    from dual

  • lpad 这里是将将输出结果变成10位，用空格补齐

    1
    2
    3
    4
    5
    6
    7
    		select employee_id,last_name,lpad(salary,10,' '),rpad(salary,10,' ')
    		from employees
    ```	

    * trim 只去除首位和末位
    ```sql	
    		select trim('h' from 'hello,hworldh') from dual

  • replace 将第二个字符替换成第三个字符 是替换所有的字符

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    		select replace('abcbdb','b','m') 
    		from dual
    ```		

    * round  保留小数 四舍五入

    ```sql		
    		select round(22.345,2),round(436.34,-2),round(436.34)
    		from dual
    		--结果
    		ROUND(22.345,2) ROUND(436.34,-2) ROUND(436.34)
    		--------------- ---------------- -------------
    		          22.35              400           436

  • trunc 保留小数 全部舍去

    1
    2
    select trunc(22.345,2),trunc(436.34,-2),trunc(436.34)
    from dual

求日期雇佣日期到当前日期的天数使用trunc 球余

1
2
select employee_id,last_name,trunc(sysdate-hire_date) wored_day
from employees

  • mod求余
1
select mod(1100,300) from dual;

####日期函数

  • month_between 两个日期相差的月数
  • add_months 向指定日期加上若干的月数
  • next_day 指定日期的下一个星期x对应的日期
  • last_day 本月的最后一天
  • round 日期四舍五入

  • trunc 日期截断

  • 求减去三个月的时候，加上两个月的时间，下个星期日的时间

    1
    select add_months(sysdate,-3),add_months(sysdate,2),next_day(sysdate,'星期日') from dual

  • 来公司的员工中hiredate是每个月倒数第二天来公司的员工有哪些

    1
    2
    3
    4
    --来公司的员工中hiredate是每个月倒数第二天来公司的员工有哪些
    select last_name,hire_date
    from employees
    where hire_date = last_day(hire_date-1)

  • 获取当前月份最后一天减2的日期

    1
    select last_day(sysdate)-2 from dual

  • 获取当前月份的四舍五入的值，当前小时的截断值（就算大于5也不取）
    *

    1
    2
    select round(sysdate,'month'),round(sysdate,'mm'),trunc(sysdate,'hh')
    from dual

####隐式数据类型转换

####date <===> varchar2 <===> number

1
2
3
select '12'+2 from dual;

select sysdate+2 from dual;

####显示类型转换

  • 使用to_char 来转换日期
  • 使用to_date可以将字符串转成日期
1
2
3
4
select hire_date from employees
--where hire_date = '7-6月-94'
--where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07'
where to_date('1994-06-07','yyyy-mm-dd') = hire_date

  • 使用to_char将雇佣日期转换成指定的格式 

    1
    2
    3
    select employee_id,to_char(hire_date,'yyyy"年"mm"月"dd"日"') "雇佣时间"
    from employees
    where to_char(hire_date,'yyyy"年"mm"月"dd"日"') = '1994年06月07日'

  • 将数字转换成字符串

  • 如果需要前面需要补0就使用000，不然就直接使用999就是数据转字符串的格式

  • 如果使用的是美元就在0前面加上$，本地货币使用L 

    1
    select to_char(1234567.89,'$000,999,99.99') from dual

  • 将字符串转换为数字

    1
    2
    select to_number('￥012,345,67.89','L000,000,99.99') +1
    from dual

###通用函数

  • NVL(expr1,expr2)
    • 当expr1中的结果为空的时候，将expr1替换成expr2
    • 如例子：使用nvl就能将空值替换成0从而输出结果
      1
      2
      3
      --求公司员工的年薪 （含commission_pct）
      select employee_id last_name,salary*12*(1+nvl(commission_pct,0)) annual_salary
      from employees
1
2
3
4
--因为字段类型原本是使用数字你用字符替换数字需要使用to_char来替换
--输出last_name department_id 当department为空时，将它显示成'没有部门'
select last_name,nvl(to_char(department_id,'999999'),'没有部门')
from employees

  • NVL2(expr1,expr2，expr3)

    • nvl2是nvl的一种特殊化，为空返回expr3,不为空返回expr2
      1
      2
      3
      4
         --查询奖金的奖金率，若为空，返回0.01
      --不为空，返回实际奖金率+0.015
      select last_name,commission_pct,nvl2(commission_pct,commission_pct+0.015,0.01)
      from employees

  • NULLIF(expr1,expr2)

  • COALESE(expr1,expr2,….)

####条件表达式

#####case的使用

1
2
3
4
5
6
7
8
9
--查询部门号10,20,30的员工信息
--如果是部门10，打印工资的1.1倍
--如果是20，打印工资的1.2倍
--是30,打印工资的1.3倍
select employee_id,last_name,department_id,case department_id when 10 then salary*1.1
                                                              when 20 then salary*1.2
                                                              else salary*1.3 end new_sal
from employees
where department_id in(10,20,30)

#####或者使用decode来实现相同的功能

1
2
3
4
5
6
7
8
9
10
--查询部门号10,20,30的员工信息
--如果是部门10，打印工资的1.1倍
--如果是20，打印工资的1.2倍
--是30,打印工资的1.3倍
select employee_id,last_name,department_id,decode(department_id,10,1.10 * salary,
                                                                20,1.20 * salary,
                                                                30,1.30 * salary,
                                                                salary)new_sal
from employees    
where department_id in(10,20,30)

####单行函数的练习题

  • 打印出”20018年12月10日 9:25:10” 格式的当前系统的日期和时间
1
2
3
--"20018年12月10日 9:25:10" 格式的当前系统的日期和时间
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss')
from dual

  • 格式化数字 123456 转成123,456.00

    1
    2
    select to_char(123456,'999,999.99')
    from dual

  • 查询员工编号姓名工资和工资1.2倍的结果，用new_salary表示

  • 1
    2
    select employee_id,last_name,salary,salary*1.2 new_salary
    from employees

  • 将员工的姓名按照首字母排序，查询姓名的长度

    1
    2
    3
    select last_name,length(last_name)
    from employees
    order by last_name asc

  • 查询员工的姓名并写出员工工作的月份数、

    1
    2
    select last_name,hire_date,round(months_between(sysdate,hire_date) ,1)worked_month
    from employees

  • 查询员工的姓名并写出员工工作的月份数、并且按月份数降序排序

    1
    2
    3
    select last_name,hire_date,round(months_between(sysdate,hire_date) ,1)worked_month
    from employees
    order by worked_month desc

  • 6.做一个查询，产生下面的结果

  • <last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns $24000 monthly but wants $72000

sql

1
2
3
select last_name||'earns'||to_char(salary,'$999999')||'monthly but wants'||to_char(salary * 3,'$999999')
dream_salary
from employees

  • 7.使用decode函数，按照下面的条件：

    job                  grade
AD_PRES                A
ST_MAN               B
IT_PROG              C
SA_REP                D
ST_CLERK               E
产生下面的结果    
Last_name    Job_id    Grade
king    AD_PRES    A

sql

1
2
3
4
5
6
select last_name,job_id,decode(job_id,'AD_PRES','A',
                                       'ST_MAN','B',
                                       'IT_PROG','C',
                                       'SA_REP','D',
                                       'ST_CLERK','E') grade
from employees

使用case重写一遍这个题

1
2
3
4
5
6
7
select last_name,job_id,case job_id when 'AD_PRES' then 'A'
                                     when 'ST_MAN' then 'B'
                                     when 'IT_PROG' then 'C'
                                     when'SA_REP' then 'D'
                                     when 'ST_CLERK' then 'E'
                                     else job_id end  grade
from employees

###多表查询

####如果没有过滤条件就会出现笛卡尔积错误

####联合departments，和employees表进行查询 使用where进行等值连接

1
2
3
select employees.employee_id,employees.department_id,departments.department_name
from employees,departments
where employees.department_id = departments.department_id

将数据表重命名以减少代码量，如果数据仅存在一个表中其前缀也可以省略

1
2
3
select employee_id,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id

联合三个表进行查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
	select employee_id,e.department_id,department_name,l.city
	from employees e,departments d,locations l
	where e.department_id = d.department_id and d.location_id = l.location_id
```	





####非等值连接  根据employees 中的工资对应job_grades表中的等级来实现对员工工资的分级
```sql	
	select employee_id,last_name,salary,grade_level
	from employees e,job_grades j
	where e.salary between j.lowest_sal and j.highest_sal
```	


####内连接和外连接的区别

* 内连接：合并具有一个列的两个以上的表的行，结果集中不含一个表与另一个表中不匹配的行
* 外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左右表中不满足条件的行，这种连接称为外连接，没有匹配的行时，结果集返回是null，外连接的where子句类似内连接，但连接条件没有匹配行的表后面要加外连接运算符，即用圆括号括起来的加号（+）



####左外连接 就是当左表中的信息右表没有时，将右表值使用null替换
#####这里就是输出所有员工的值，就算有员工没有部门也输出
第一种方式
```sql
	select employee_id,e.department_id,department_name
	from employees e,departments d
	where e.department_id = d.department_id(+)

第二种方式

1
2
3
select employee_id,e.department_id,department_name,city
from employees e  left outer join departments d 
on e.department_id = d.department_id

####右外连接 当右表的信息左表没有将坐标用null值替换

#####这里就是输出所有部门的信息，就算有的部门中一个人都没有也输出
第一种方式

1
2
3
select employee_id,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id(+)

第二种方式

1
2
3
select employee_id,e.department_id,department_name
from employees e  right outer join departments d 
on e.department_id = d.department_id

####满外连接 同时包含左右外连接的所有信息

1
2
3
4
5
6
7
8
9
10
	select employee_id,e.department_id,department_name
	from employees e  full outer join departments d 
	on e.department_id = d.department_id
```	


####使用natual join 实现外连接  因为natual是使用所有相同的列作为判断条件的所以当你想只使用单个列的条件来判断实现就无法使用
```sql
	select employee_id,department_id,department_name
	from employees natural join departments

使用join 后面使用using 加上判断条件实现对这个列的外连接判断 局限性（必须两个表的做判断的列的名称和参数属性必须相同）

1
2
3
select employee_id,department_id,department_name
from employees  join departments 
using (department_id)

####推荐使用这种外连接方式 在前面使用join 后面使用on 加上判定条件
实现对两个表的外连接查询

1
2
3
select employee_id,e.department_id,department_name
from employees e  join departments d 
on e.department_id = d.department_id

实现对三个表的外连接查询

1
2
3
4
5
select employee_id,e.department_id,department_name,city
from employees e  join departments d 
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id

####自连接 查询公司姓名为chen的员工的上司的姓名，工资和邮箱（不使用自连接需要进行两次查询，使用之后一次就够了）

1
2
3
select emp.last_name,manager.last_name,manager.salary,manager.email
from employees emp,employees manager
where emp.manager_id = manager.employee_id and lower(emp.last_name) = 'chen'