SQL全篇
一、SQL语句类型
1、DML:(Data Manipulation Language)数据操纵语言
DML用于查询与修改数据记录,包括如下SQL语句:
INSERT:添加数据到数据库中
UPDATE:修改数据库中的数据
DELETE:删除数据库中的数据
SELECT:选择(查询)数据
SELECT是SQL语言的基础,最为重要。
(横向改记录)
2、DDL:(Data Definition Language)数据定义语言
DDL用于定义数据库的结构,比如创建、修改或删除数据库对象,包括如下SQL语句:
CREATE TABLE:创建数据库表
ALTER TABLE:更改表结构、添加、删除、修改列长度
DROP TABLE:删除表
CREATE INDEX:在表上建立索引
DROP INDEX:删除索引
(纵向改列)
3、DCL:(Data Control Language)数据控制语言
DCL用来控制数据库的访问,包括如下SQL语句:
GRANT:授予访问权限
REVOKE:撤销访问权限
COMMIT:提交事务处理
ROLLBACK:事务处理回退
SAVEPOINT:设置保存点
LOCK:对数据库的特定部分进行锁定
二、员工表结构
通过desc操作可以看到表的列和数据类型
commission_pct:奖金率
导入三张表:
SQL> @d:/del_data.sql;
SQL> @d:/hr_cre.sql;
SQL> @d:/hr_popul.sql;
SQL> desc employees;
SQL> select employee_id, last_name, email
2 from employees;
三、基本SQL-SELECT语句
1、注意:
SQL 语言大小写不敏感
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写
使用缩进提高语句的可读性。
2、算术运算符(数字和日期)
描述 | 操作符 |
---|---|
加 | + |
减 | - |
乘 | * |
除 | / |
注意:
-
操作符优先级:* / + -
-
乘除的优先级高于加减
-
同一优先级运算符从左向右执行
-
括号内的运算先执行
SQL> select 8*4 from dual;
8*4
----------
32
SQL> select last_name,salary,12*salary+1000
2 from employees;
SQL> select sysdate, sysdate+1, sysdate-2 from dual;
SYSDATE SYSDATE+1 SYSDATE-2
----------- ----------- -----------
2020/10/21 2020/10/22 2020/10/19
注意:日期函数只能做加减,不能做乘除
3、编辑框
SQL> ed
SQL> edit
4、空值
SQL> ed
select employee_id, salary, salary*(1+commission_pct)
from employees
--(不可以加上分号)
SQL> /
commission_pct:奖金率(空值不同于0,凡是空值参与的运算,结果都为空null)
5、列的别名
1)紧跟列名;
2)也可以在列名和别名之间加入关键字‘AS’;
3)别名使用双引号,以便在别名中包含空格或特殊的字符并区分大小写。
select employee_id id, last_name name,12*salary annua_sal
from employees
select employee_id as "id", last_name "name",12*salary "annua sal"
from employees
6、连接符
把列与列,列与字符连接在一起
用 “||”表示
可以用来“合成”列
select last_name||'`s job_id is '||job_id as details ---这一整句的别名叫details
from employees
7、字符串
字符串可以是 SELECT 列表中的一个字符,数字,日期
日期和字符只能在单引号中出现
每当返回一行时,字符串被输出一次
8、重复行
默认情况下,查询会返回全部行,包括重复行。
在 SELECT 子句中使用关键字 ‘DISTINCT’ 删除重复行。
select distinct department_id
from employees
此时只有12条记录,如果不添加distinct,则返回107条记录。
9、显示表结构
SQL> desc employees;
SQL> describe employees;
补充:SQL和SQL PLUS的区别
SQL:
- 一种语言
- ANSI 标准
- 关键字不能缩写
- 使用语句控制数据库中的表的定义信息和表中的数据
SQL*Plus:
- 一种环境
- Oracle 的特性之一
- 关键字可以缩写(desc全称describe,ed全称是edit,就是SQL PLUS的命令)
- 命令不能改变数据库中的数据的值
- 集中运行
My SQL:
- 是个数据库
练习题:
(1)
select last_name, distinct department_id
from employees
此种写法是错误的,因为last_name有107条,depart_id如果去除重复则只有12条了。
(2)SQL PLUS命令不能控制数据库(SQL语句可以管理数据库)
(3)显示出表employees的全部列(列比较多,选择其中几列),各个列之间用逗号连接,列头显示成OUT_PUT
SQL> select employee_id ||','|| last_name ||','||salary "OUT_PUT" from employees;
四、过滤和排序数据
-
在查询中过滤行。
-
在查询中对行进行排序。
1、字符和日期
字符和日期要包含在单引号中。
字符大小写敏感,日期格式敏感。
默认的日期格式是 DD-MON月-RR。
select employee_id,last_name,salary
from employees
where last_name = 'Higgins'
注意:字符串里面肯定是要区分大小写的。
select last_name,hire_date
from employees
----where hire_date='7-6月-1994'(英文版本这种写法不可以)
where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07'
2、比较运算
操作符 | 含义 |
---|---|
= | 等于(不是==) |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
<> | 不等于(也可以是!=) |
select last_name,hire_date,salary
from employees
where salary>=4000 and salary<7000
其他比较运算:
操作符 | 含义 |
---|---|
BETWEEN…AND… | 在两个值之间(包含边界) |
IN(set) | 等于值列表中的一个 |
LIKE | 模糊查询 |
IS NULL | 空值 |
select last_name,hire_date,salary
from employees
-----where salary>=4000 and salary<=7000
where salary between 4000 and 7000
select last_name,department_id
from employees
----where department_id=90 or department_id=80 or department_id=70
where department_id in(70,80,90)
-----员工中名字含有字符a的员工有哪些
select last_name,department_id,salary
from employees
where last_name like '%a%'
-----员工中名字末位是字符a的员工有哪些
select last_name,department_id,salary
from employees
where last_name like '%a'
-----员工名字第二位是字符a的员工有哪些(下划线表示一个空格)
select last_name,department_id,salary
from employees
where last_name like '_a%'
-----员工名字第三位是字符a的员工有哪些
select last_name,department_id,salary
from employees
where last_name like '__a%'
这里数据一直无法更新,可能是因为要commit
---update employees
---set last_name = 'Wha_len'
---where last_name = 'whalen'
-----员工名字含有_字符的员工有哪些
select last_name,department_id,salary
from employees
where last_name like '%\_%' escape '\'
select last_name,department_id,salary,commission_pct
from employees
where commission_pct is null
select last_name,department_id,salary,commission_pct
from employees
where commission_pct is not null
3、逻辑运算
操作符 | 含义 |
---|---|
AND | 逻辑并 |
OR | 逻辑或 |
NOT | 逻辑否 |
select last_name,department_id,salary,commission_pct
from employees
where department_id=80 and salary <=8000
4、优先级
优先级 | |
---|---|
1 | 算术运算符 |
2 | 连接符 |
3 | 比较符 |
4 | IS (NOT) NULL,LIKE,(NOT) IN |
5 | (NOT) BETWEEN |
6 | NOT |
7 | AND |
8 | OR |
可以使用括号改变优先级顺序
5、排序
使用 ORDER BY 子句排序
ASC(ascend): 升序(默认顺序)
DESC(descend): 降序
ORDER BY 子句在SELECT语句的结尾
select last_name,department_id,salary
from employees
where department_id=80
order by salary desc
多层排序:如果工资相同,则按照名字顺序排序
select last_name,department_id,salary
from employees
where department_id=80
order by salary asc,last_name asc
别名排序:
select last_name,department_id,salary,12*salary annual_sal
from employees
where department_id=80
order by annual_sal
练习题:
(1)查询1998-4-24来公司的员工有哪些?
select last_name,hire_date
from employees
where to_char(hire_date,'yyyy-mm-dd') = '1998-04-24'
一定要写04。。。
(2)查询LAST_NAME 中含有’_'字符的所有员工
---update employees
---set last_name = 'Jones_Tom'
---where employee_id = 195
此时,更新操作可以完成。
select *
from employees
where last_name like '%\_%' escape '\'
(3)选择工资不在5000到12000的员工的姓名和工资
select last_name,salary
from employees
-----where salary < 5000 or salary > 12000
where salary not between 5000 and 12000
(4)选择雇用时间在1998-02-01到1998-05-01之间的员工姓名,job_id和雇用时间
select last_name,job_id,hire_date
from employees
where to_char(hire_date,'yyyy-mm-dd') between '1998-02-01' and '1998-05-01'
(5)选择在1994年雇用的员工的姓名和雇用时间
select last_name,hire_date
from employees
----where to_char(hire_date,'yyyy')='1994'
where hire_date like '%94'(写1994没有查询记录)
(6)选择姓名中有字母a和e的员工姓名
select last_name
from employees
where last_name like '%a%e%' or last_name like '%e%a%'
五、单行函数
SQL中不同类型的函数
在 SELECT 语句中使用字符,数字,日期和转换函数
使用条件表达式
1、定义
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以转换数据类型
- 可以嵌套
- 参数可以是一列或一个值
2、字符函数
(1)大小写控制函数:
LOWER,UPPER,INITCAP
INITCAP:首字母大写
SQL> select initcap('ABC abc') from dual;
INITCAP('ABCABC')
-----------------
Abc Abc
select *
from employees
where upper(last_name) = 'KING'
可以在不记得人名到底是大写还是小写时使用
(2)字符控制函数:
CONCAT,SUBSTR,LENGTH,INSTR,LPAD|RPAD,TRIM,REPLACE
SUBSTR:从第几位开始,共取几位
INSTR:某个字符在字符串中首次出现的位置,第一位是1;如果没有,返回的是0
LPAD:左边补位,共十位,不满十位,左边补星号或者空格之类的,对于salary,左边补空格比较合适
RPAD:右变补位
TRIM:将一个字符从一个字符串中移除(只能移除首位的)
REPLACE:取代(取代的是所有出现过的)
SQL> select trim('h' from 'hhhellohworldh') from dual;
TRIM('H'FROM'HHHELLOHWORLDH')
-----------------------------
ellohworld
SQL> select replace('abcdab','b','m') from dual;
REPLACE('ABCDAB','B','M')
-------------------------
amcdam
补充:trim去除空格
1)不使用任何参数
SQL> select trim(' 11 ') aa from dual;
这是最常见的一种使用方法,都使用默认的参数,默认情况下TRIM会同时删除字符串前后出现的空格。
2)使用both参数,效果等同于方法1)不使用任何参数
SQL> select trim(both from ' 11 ') aa from dual;
“BOTH”参数表示同时去除字符串前后所指定的内容(默认情况下删除空格)。
3)使用leading与trailing参数
SQL> select trim(leading from ' 11 ') aa from dual;
SQL> select trim(trailing from ' 11 ') aa from dual;
可以发现使用leading参数可以去除字符串右端的空格,而trailing参数则可以去除字符串左端的空格。正如oracle提供的rtrim()与ltrim()。
4)使用trim_character参数
trim_character参数改变了“删除空格”的默认行为。如果想要删除字符串’xxxxWORLDxxxx’前后出现的“x”,“trim_character”参数就派上用场了。
SQL> select trim('x' from 'xxxxWORLDxxxx') aaaaa from dual; 结果:word
trim_character配合“both”、“trailing”和“leading”三个参数使用效果如下,与之前演示类似。看结果,不赘述。
SQL> select trim('x' from 'xxxxWORLDxxxx') aaaaa from dual;
SQL> select trim(both 'x' from 'xxxxWORLDxxxx') aaaaa from dual;
SQL> select trim(trailing 'x' from 'xxxxWORLDxxxx') aaaaa from dual; 结果:xxxxWORLD
SQL> select trim(leading 'x' from 'xxxxWORLDxxxx') aaaaa from dual; 结果:WORLDxxxx
5)必须注意的一点是这里的“trim_character”参数只允许包含一个字符,不支持多字符。trim不能满足我们去除多字符要求,但是我们可以使用rtrim和ltrim来处理。
- 使用rtrim
SQL> select rtrim('ORxxxxWORLDxxxxOR','OR') aaaaa from dual; ORxxxxWORLDxxxx
- 使用ltrim
SQL> select ltrim('ORxxxxWORLDxxxxOR','OR') aaaaa from dual; xxxxWORLDxxxxOR
- 联合使用RTRIM和LTRIM函数
SQL> select rtrim(ltrim('ORxxxxWORLDxxxxOR','OR'),'OR') aaaaa from dual; xxxxWORLDxxxx
使用rtrim和ltrim函数时的注意事项:“OR”不表示整个“OR”字符串进行匹配,而是发现任意的字符“O”或字符“R”均做删除操作。
6)replace 函数用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。来看下replace函数的语法描述:replace(‘string_replace1’,‘string_replace2’,‘string_replace3’)
‘string_replace1’ 待搜索的字符串表达式,string_replace1 可以是字符数据或二进制数据。
‘string_replace2’ 待查找的字符串表达式,string_replace2 可以是字符数据或二进制数据。
‘string_replace3’ 替换用的字符串表达式,string_replace3 可以是字符数据或二进制数据。
返回类型,如果 string_replace(1、2 或 3)是支持的字符数据类型之一,则返回字符数据;如果 string_replace(1、2 或 3)是支持的 binary 数据类型之一,则返回二进制数据。
这里我们依然以去空格为例。
SQL> select replace(' aa kk ',' ','') abcd from dual; 结果:aakk
与使用trim函数的结果进行对比,使用replace函数不仅可以去除字符串两端的空格,也可去除字符串内部的空格。当然,如果只是要去除字符串两端的空格,使用trim函数效率会更高。
3、数字函数
ROUND: 四舍五入
ROUND(45.926, 2) 45.93
TRUNC: 截断
TRUNC(45.926, 2) 45.92
MOD: 求余
MOD(1600, 300) 100
select round(435.45,2),round(435.45),round(435.45,-2) from dual
ROUND(435.45,2) ROUND(435.45) ROUND(435.45,-2)
--------------- ------------- ----------------
435.45 435 400
select round(435.45,1),round(435.45),round(435.45,-1) from dual
ROUND(435.45,1) ROUND(435.45) ROUND(435.45,-1)
--------------- ------------- ----------------
435.5 435 440
select trunc(435.45,1),trunc(435.45),trunc(435.45,-1) from dual
TRUNC(435.45,1) TRUNC(435.45) TRUNC(435.45,-1)
--------------- ------------- ----------------
435.4 435 430
4、日期函数
(1)日期的数学运算
- 在日期上加上或减去一个数字结果仍为日期。
- 两个日期相减返回日期之间相差的天数。(日期不允许做加法运算,无意义)
- 可以用数字除24来向日期中加上或减去天数。
(2)日期函数
select months_between(sysdate,hire_date) from employees
此时结果是小数,有sysdate的最好前面加上trunc函数或者round函数
select add_months(sysdate,2),add_months(sysdate,-3),next_day (sysdate,'Sunday')from dual
ADD_MONTHS(SYSDATE,2) ADD_MONTHS(SYSDATE,-3) NEXT_DAY(SYSDATE,'SUNDAY')
--------------------- ---------------------- --------------------------
2020/12/26 11:24:04 2020/7/26 11:24:04 2020/11/1 11:24:04
-----来公司的员工中,hire_date是每个月倒数第二天来公司的有哪些:
select last_name,hire_date
from employees
where hire_date = last_day(hire_date)-1
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2020/10/31 11:34:
(3)一些举例:
- MONTHS_BETWEEN (‘01-SEP-95’,‘11-JAN-94’) 19.6774194
- ADD_MONTHS (‘11-JAN-94’,6) ‘11-JUL-94’
- NEXT_DAY (‘01-SEP-95’,‘FRIDAY’) ‘08-SEP-95’
- LAST_DAY(‘01-FEB-95’) ‘28-FEB-95’
- Assume SYSDATE = ‘25-JUL-95’:
-
- ROUND(SYSDATE,‘MONTH’) 01-AUG-95
- ROUND(SYSDATE ,‘YEAR’) 01-JAN-96
- TRUNC(SYSDATE ,‘MONTH’) 01-JUL-95
- TRUNC(SYSDATE ,‘YEAR’) 01-JAN-95
select round(sysdate,'month'),round(sysdate,'mm'),trunc(sysdate,'hh') from dual
ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'MM') TRUNC(SYSDATE,'HH')
---------------------- ------------------- -------------------
2020/11/1 2020/11/1 2020/10/26 14:00:00
因为今天10.26,这个月过半了,所以显示11.1
(4)
yyyy 年
mm month 月
dd 日
day 星期
hh 小时
mi 分钟
ss 秒
补充:
1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
2.select trunc(sysdate, ‘mm’) from dual --2013-01-01 返回当月第一天.
3.select trunc(sysdate,‘yy’) from dual --2013-01-01 返回当年第一天
4.select trunc(sysdate,‘dd’) from dual --2013-01-06 返回当前年月日
5.select trunc(sysdate,‘yyyy’) from dual --2013-01-01 返回当年第一天
6.select trunc(sysdate,‘d’) from dual --2013-01-06 (星期天)返回当前星期的第一天
7.select trunc(sysdate, ‘hh’) from dual --2013-01-06 17:00:00 当前时间为17:35
8.select trunc(sysdate, ‘mi’) from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
5、转换函数
(1)隐式数据类型转换
oracle自动完成下列转换:
SQL> select '12'+2 from dual;
'12'+2
----------
14
SQL> select sysdate+'2' from dual;
SYSDATE+'2'
-----------
2020/10/28
(2)显式数据类型转换
1)to_char函数对日期的转换
格式:
必须包含在单引号中而且大小写敏感。
可以包含任意的有效的日期格式。
日期之间用逗号隔开。
日期格式的元素:
时间格式:
HH24:MI:SS AM 15:45:32 PM
DD “of” MONTH 12 of OCTOBER
select employee_id,hire_date
from employees
where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07'
EMPLOYEE_ID HIRE_DATE
----------- -----------
203 1994/6/7
204 1994/6/7
205 1994/6/7
206 1994/6/7
select employee_id,to_char(hire_date,'yyyy"年"mm"月"dd"日"')
from employees
where to_char(hire_date,'yyyy"年"mm"月"dd"日"')='1994年06月07日'
EMPLOYEE_ID TO_CHAR(HIRE_DATE,'YYYY"年"MM"月"DD"日"')
----------- --------------------------------------
203 1994年06月07日
204 1994年06月07日
205 1994年06月07日
206 1994年06月07日
2)to_date对字符串的转换
select employee_id,hire_date
from employees
where to_date('1994-06-07','yyyy-mm-dd') = hire_date
EMPLOYEE_ID HIRE_DATE
----------- -----------
203 1994/6/7
204 1994/6/7
205 1994/6/7
206 1994/6/7
注意:此处还是“/”是因为,默认格式是hiredate格式。
3)to_char函数对数字的转换
SQL> select to_char(1234567,'999,999,999.99') from dual;
TO_CHAR(1234567,'999,999,999.99')
---------------------------------
1,234,567.00
SQL> select to_char(1234567,'000,000,999.99') from dual;
TO_CHAR(1234567,'000,000,999.99')
---------------------------------
001,234,567.00
SQL> select to_char(1234567,'$000,000,999.99') from dual;
TO_CHAR(1234567,'$000,000,999.99')
----------------------------------
$001,234,567.00
SQL> select to_char(1234567,'L000,000,999.99') from dual;
TO_CHAR(1234567,'L000,000,999.99')
----------------------------------
$001,234,567.00
L:本地的货币符号
4)to_number对字符串的转换
select to_number('$001,234,567.89','L000,000,999.99') from dual
TO_NUMBER('$001,234,567.89','L000,000,999.99')
----------------------------------------------
1234567.89
就可以来做运算了
select to_number('$001,234,567.89','L000,000,999.99')+1 from dual
TO_NUMBER('$001,234,567.89','L000,000,999.99')+1
------------------------------------------------
1234568.89
补充:oracle中默认时间格式
(1)Oracle的日期格式
Oracle缺省的时间格式即时间数据的显示形式,与所使用的字符集有关。一般显示年月日,而不显示时分秒。
例如,使用us7ascii字符集(或者是其他的英语字符集)时,缺省的时间格式显示为:28-Jan-2003;