数据库-Oracle 基本SQL语句

 

一、SQL语言基础

什么是SQL语言

• 结构化的查询语句（Structured Query Language）
• SQL是最重要的关系数据库操作语言，是所有关系数据库管理系统的标准语言
• SQL是一种非过程化语言，只需要提出做什么，而不需要指明怎么做

SQL可以做什么

• 1数据库数据的增删查改操作（CRUD）
• 数据库对象的创建，修改和删除操作
• 用户权限/角色的授予和取消
• 事务控制

SQL语言的分类 

• DQL（数据查询语言）      select
• DML（数据操作语言）      insert、update、delete
• DDL（数据定义语言）       create、alert、drop
• DCL（数据控制语言）       grant、revoke
• TCL(数据事务控制语言)     SAVEPOINT、ROLLBACK、SET TRANSACTION、COMMIT

数据操作语言针对表中的数据，而数据定义语言针对数据库对象（表、索引、视图、触发器、存储过程、函数、表空间等）

二、关系数据库基本概念

• 关系：整个二维表·
• 关系名：表格名称
• 元组：行数据（记录）
• 属性：列数据（字段）
• 属性名：列名称（字段名）
• 主键：唯一确定元组的属性组（关键字）
• 域：属性的取值范围

三、select单表查询语句

• select子句

select * from 表名      查询表中所有的数据

select 字段名1,字段名2,...from 表名      查询指定指定字段的所有值，不同字段之间使用逗号隔开

使用算数表达式：select 字段名(运算符),字段名(运算符),from 表名

注：在select语句中，对数值型数据可以使用算数运算符创建表达式

示例

emp表：员工表

dept表：部门表

salgrady:薪资水平表

Balance:

查询所有员工信息

• select * from emp

查询员工姓名，工作，薪资

• select ename,job,sal from emp

查询所有员工一年的工资，以及每个员工年终3000后的所有工资（不包括津贴）

• select  ename,sal*12,sal*12+3000 from emp   (使用运算符)

• 使用字段别名

字段别名

1. 重命名查询结果中的字段，以增强可读性
2. 别名如果含有空格或其他特殊字符或大小写敏感，需用双引号引起来
3. as可以省略

• select ename 姓名,job 工作,sal*12 年资,sal*12+3000 年薪 from emp      直接在字段后面添加别名
• select ename "姓名",job "工作",sal*12 "年资",sal*12+3000 "年薪" from emp   使用双引号添加别名，不能使用单引号。
• select ename as 姓名,job as 工作,sal*12 as 年资,sal*12+3000 as 年薪 from emp    使用as关键字添加别名
• select ename "姓  名",job 工作,sal*12 年资,sal*12+3000 年薪 from emp          如果别名中有空格或者其他特殊字符，使用双引号。

• 使用链接符

使用||符号进行字符链接

• select ename || '的工作是' ||job|| '并且月工资是' ||sal from emp         字符链接，普通字符使用单引号

• 去除重复行

缺省情况下，查询结果中包含所有符合条件的记录行，包括重复行，可以使用distinct关键字去除重复值

去除重复的工作

• select distinct job from emp     

多字段去除重复，去除的是重复的一整条信息

• select distinct job,sal from emp;

distinct的作用范围是后面所有字段的组合

• order by排序

语法：select 字段名,字段名....from 表名 order by 字段名

使用order by子句对查询结果进行排序

排序方式包括升序（asc,缺省）和降序（desc）两种

查询按照薪资排序的员工姓名，工作，薪资

• select ename,job,sal from emp order by sal;          使用order by 排序
• select ename,job,sal from emp order by sal desc;     使用desc关键字从大到小排序
• select ename,job,sal from emp order by sal asc;        使用asc关键字从小到大排序，默认是从小到大排序
• select * from emp order by sal,ename;                       使用多字段进行排序时，优先按照第一个字段排序，然后依次按照其他字段排序。
• select ename,job,sal as 工资  from emp order by 工资;       使用别名进行排序。
• select ename,job,sal from emp order by sal*12;         使用算数表达式

• where条件查询

指定查询条件使用where子句

语法：

select * from 表名 where 条件

注意：条件中字段值区分大小写，字段名不区分大小写，字段值使用单引号括起来

1. 在where中可以使用算术表达式 =,<,>,<=,>=
2. 使用order by对筛选结果进行排序，order by 出现在where后面

• select * from emp where deptno=10;
• select * from emp where ename="SMith";
• select * from emp where hiredate='02-4月-81';

注意：

1. 字符串和日期值要用单引号括起来
2. 字符串大小写敏感
3. 日期值格式敏感，缺省的日期格式是'DD-MON-RR'

查询条件中可以使用比较运算符

查看工资大于2900的员工的信息

• select * from emp where sal>2900;
• select * from emp where sal>'2900';         筛选条件是个数字时也可以使用单引号
• select * from emp where job='CLERK'       在筛选条件中字段值如果是字符需要加上单引号

在SQL语句中字段的值是区分大小写的

在SQL中字段是不区分大小写的

运算符                                                   含义

BETWEEN.....AND                 界于两值之间（包括边界）

IN                                             出现在集合中（或）

LIKE                                         模糊查询

IS NULL                                   为空值

IS NOT NULL                          不为空

AND                                         用于多条件的与查询

OR                                            用于对条件的或查询

 

查询工资在2000-3000之间的员工的信息

• select * from emp where sal>=2000 and sal<=3000 ;           使用and关键字进行“与”的多条件查询
• select *from emp where sal between 2000 and 3000;           使用between...and关键字进行查询

查询工作为SALESMAN,ANALYST,MANAGER的员工信息

• select *from emp where job='SALESMAN' or job='ANALYST' or job=',MANAGER';             使用or关键字进行“或”的多条件查询
• select *from emp where job in('SALESMAN','ANALYST','MANAGER');                                 使用in关键字进行“或”的多条件查询

查询姓名中包含s的，以s开头的，以s结尾的，第二个字符为A的员工的信息

• select * from emp where ename like '%s%';             使用like关键字，姓名中包含s的，%代表任意多个字符
• select * from emp where ename like 's%';                   使用like关键字，姓名中以s开头
• select * from emp where ename like '%s';                  以s结尾的
• select * from emp where ename like '_A%';               第二个字符为A的，使用"_"指定位置包含指定字符的信息，"_"代表任意一个字符

对于特殊符号可使用ESCAPE标识符来查找

查询名字中包含下划线的用户信息

• select  * from  emp where ename like '%\_%' escape '\';           使用escape关键字将普通字符设置成为转译字符

查询有津贴的员工的信息

• select * from emp where  comm is not null;

查询没有津贴的员工的信息

•  select * from emp where  comm is  null；

查询工作为SALESMAN，MANAGER并且工资大于2500的员工信息

• select * from emp where job='SALESMAN' or job='MANAGER' and sal>250            and的优先级别高于or
• select * from emp where (job='SALESMAN' or job='MANAGER') and sal>2500        使用小括号提升where筛选条件的执行优先级别

 

• 使用函数

使用函数可以大大提高select语句操作数据库的能力 ，他给数据的转换和处理提供了方便

函数只是将取出的数据进行处理，不会改变数据库中的值

Oracle函数分为单行函数和多行函数两大类

单行函数

字符函数 

   

数字函数 

日期函数   

转换函数 

Oracle的类型转换分为自动类型转换和强制转换

在转换的时候改变的是数据的类型，数据内容不会改变，可以指定格式

尽管数据类型之间可以进行自动转换，仍建议使用转换函数，以保持良好的设计风格，常用类型转换函数有

to_char():将数字字符转换为字符类型，将日期转换为字符类型      to_char（数字/日期）

to_date():将字符类型的日期转换为日期类型                to_date(char)

to_number():将数字字符转换为数字类型的数值，     to_number(数字字符)

number-->char   转换的时候使用的是默认格式

• select to_char(123) from dual
• select sal,to_char(sal)  from emp;

number-->char      使用指定的格式

to_char（number,'格式'）

格式：$代表美元符，9代表数字占位 。例如：L999,999,999表示三位一组的显示方式，L代表人民币符号，0代表保留数字个数，不足用0补充

• select to_char(sal),to_char(sal,'L999,999,999') from emp 

char-->date ,转换的字符必须是日期格式的字符串，默认格式  dd-mm-yyyy

注意：因为字符串有很多，所以在字符转换为日期的时候，需要指定格式，因为日期是具备一定格式的字符组合。

字符转换为日期时，指定的格式为字符的日期顺序，无需指定间隔符。

• select to_date ('05-12月-2016') from dual;
• select to_date('2019-03-12',yyyy-mm-dd) from dual;         使用指定的格式将指定的日期字符串转换为日期
• select to_date('2019-03-12',mm/dd/yyyy) from dual;

注意：因为日期本身就具有一定的格式，在不是指定格式的情况下会默认使用dd-mm-yyyy格式显示数据，指定的格式会作为日期转换为字符串类型的显示格式存在。

可使用sysdate函数获取当前系统的日期和时间

• SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;

多行函数

sum() ：sum(字段名)       返回字段的和

avg()：avg(字段名)           返回平均值

count()：   

• count(*):用来查询表中有多少条记录
• count(字段名)     用来查询某个字段有值的个数
• count(distinct  字段名)     先去除重复再计数

max()：max(字段名)         返回此字段的最大值

min() ：min(字段名)          返回此字段的最小值

注意：多行函数不能和普通字段直接出席在查询语句中,除非group by

多行函数和单行函数不能直接出现在查询语句中，除非group by 。

查看员工的最高工资

• select max(sal) from emp;

查看员工的最低工资

• select min(sal) from emp;

查看员工的平均工资

• select avg(sal) from emp;

查看员工的工资之和

• select sum(sal) from emp;

查询公司有多少员工

• select count(*) from emp         使用count(*)来查看一张表中有多少条记录

查询有津贴的员工人数

• select count(comm) from emp;       使用count(字段名)，查询该字段有值得记录数

查询公司有多少工作种类

• select count(distinct job) from emp;            先去除重复再查询 

多行函数与空值

多行函数除了count(*)外，都跳过空值而处理非空值

可使用NVL()函数强制多行函数处理空值

其他函数

NVL(字段名，执行)       相当于java中的if条件判断

NVL2(字段名，值，值)      相当于 java中的if(){ }else{ }判断

decode(字段名，条件1，执行内容1，条件2，执行内容2,....默认执行内容)          相当于java中的if(){ }else if(){ }...else{ }

查询所有员工的月薪及姓名和工作

select ename,job,sal+nvl(comm,0) 月薪 from emp；

select ename.job,nvl2(comm,sal+comm,sal) 月薪 from emp；

显示员工的职称

select ename,job,decode(job,'MANAGER','经理','SALESMAN','销售人员','普通员工') from emp;

• group by分组

         在多行函数中不能直接使用普通字段，除非group by

         在多行函数中不能直接使用单行函数，除非group by

1. 使用group by进行数据分组，select   多行函数， 分组字段 from 表名 group by 分组字段
2. 多字段进行分组的时候，按照字段顺序进行分组，第一条件分组完成后，继续使用其他条件依次分组。
3. group by可以和order by联合使用
4. 可以和单行函数联合进行分组，注意：使用了单行函数那么在查询语句中必须也要使用

查询最高工资和员工数

select max(sal),count(*) from emp;

查询不同部门的最高工资

• select  deptno,max(sal) from emp group by deptno                       使用group by进行分组查询，分组的字段可以出现在查询中，其他字段仍然不可以

查询不同工作岗位的员工数

select lower(job) ,count(*) from emp group by lower(job);         使用单行函数进行分组

查询不同部门的不同工作岗位的并且人数大于1的信息

select deptno, job,count(*) from emp where count(*) >1  group by  deptno,job, order by deptno;

注意：出现在select列表中的字段，如果不是包含在多行函数中，那么该字段必须同时在group by子句中出现

错误：select ename,job,sum(sal)  from emp group by job;

包含在group by 子句中的字段则不必须出现在select列表中

如果没有group by子句，select列表中不允许出现字段（单行函数）与多行函数混用的情况

• select empno, sal from emp; //合法
• select avg(sal) from emp; //合法
• select empno, avg(sal) from emp; //非法

不允许在WHERE 子句中使用多行函数

• having 分组筛查

对分组查询的结果进行过滤，要使用having语句

having语句过滤分组后的结果，只能出现在group by 从句后面，而where从句要出现在group by语句前面

where过滤行，having过滤分组，having支持所有where操作符

语法格式

• select column, group_function(column)
• from table
• [where condition]
• [group by  group_by_expression]
• [having group_condition]
• [order by column];

where条件筛选的执行顺序：from-->where-->group by-->select

having条件筛选的执行顺序：from-->group by -->having -->select

where的执行效率比having高，能使用where的情况下尽量不要使用having

查询不同部门的不同岗位的并且人数大于1的信息

使用where语句进行筛选

• select count(*) from emp where count(*)>1 group by deptno,job

使用having语句进行筛选

• select count(*) from emp group by deptno,job having count(*)>1

三、DML

insert语句

往表中插入数据的语句是insert语句，方式有两种，一种是记录值的插入，一种是查询结果的插入

记录值得插入语法如下：

• insert in 表名(字段1,字段2,...)   values('值1','值2',...)

一次插入操作只插入一行

如果插入的数据是全字段数据，字段可以省略不写。部分字段，必须加上字段说明和字段值，但是主键不能为空

主键：用来唯一标识一条数据的字段通常社会主键，主键是唯一不可以重复的

添加所有的列

• insert into emp (empno,ename,job,mgr,hiredate,sal, comm, deptno) values(1111,'gao','clerk',7902,sysdate, 10000,3000,40)

此处插入的元素中列的个数、顺序与emp的结构完全一致，因此表名之后的类名可以省略不写 

• insert into emp  values(2222,'gaohs','clerk',7902,sysdate,10000,3000,40)

添加部分列

• insert into emp(empno,ename)  values (3333,'xiaozhang')

要求省略的列必须满足以下条件：

1. 该列定义为允许null值
2. 在表定义中给出默认值，这表示如果不给出值，将使用默认值

如果不符合上面两个条件，将会报错，不能成功插入

update

用于修改基本表中的元组的某些列

语法：update 表名 set 字段名1='字段值1',字段名2='字段值2',....条件

将部门70的名字改为教学部2

• update dept set dname='教学部2' where deptno='70';

 

delect

sql的删除操作是指从基本表中删除元组

语法：delete  表名 where 条件

删除部门编号为70的数据

delete dept where deptno='70'              删除指定数据

如果想从表中删除所有的行，不要使用delete，可使用truncate table，完成相同的工作，速度更快，但是次关键字不能回滚数据