Oracle_Day02(SQL语言，DML相关的SQL语言，单行函数)

Oracle_Day02(SQL语言，DML相关的SQL语言，单行函数)

SQL语言

结构化查询语言，适用于所有的关系型数据库。但是在遵循SQL标准的同时，各种数据库在某些方面也有一些细微的差别，将每种数据库在sql中的细微差别，称之为方言。
SQL语言可以执行对数据库的增删改查（CRUD）。

SQL分类：

• 数据操纵语言：主要用于完成数据的CRUD操作 DML

  • 常用的语句：
    • Select：查询
    • Insert：插入
    • Update：更新
    • Delete： 删除

• 数据的定义语言：用来定义数据的库以及表的结构，及创建、删除、修改等操作 DDL

  • 常用语句：
    • Create table：创建表
    • Alter table：修改表
    • Drop table：删除表
    • Create view：创建视图
    • Create Index：创建索引

• 数据控制语言：控制对数据库的访问，可以对用户进行权限的赋予和回收、事务处理 DCL

  • 常用语句：
  • Grant：授权
  • Revoke：回收权限
  • COMMIT：提交事务
  • Rollback：回滚事务

SQL语句的注意事项:

1. 注意大小写的使用
   列名（字段名）、参数、变量名，小写
   关键字、对象名，大写
   命名时只能使用字母、数字和下划线，不能以数字开头
2. 要合理使用空格提高sql语句的可读性
3. 要注意代码的格式，要有合理的缩进
4. 合理使用注释
   Oracle 的注释：
   单行注释：–
   多行注释:/* */
   Mysql的注释：#

DML相关SQL语句

查询：Select
格式： SELECT * | column FROM tablename;
*是通配符，表示查询表中的所有字段
Column字段：根据需要列出要查询的字段，多个字段之间使用逗号分隔

1. 查询所有的列：

-- 查询EMP表中所有的雇员信息
SELECT * FROM emp;
-- 查询所有的部门信息
SELECT * FROM dept;

2. 查询特定的列：

--查询雇员的姓名、职位、薪资 
--清除我们所要插叙你的数据的来源
SELECT ename,job,sal FROM emp;

SQL语句的特点：

• 大小写不敏感
• Sql语句可以一行或多行都行，以分号来进行标记
• SQL语句需要良好的格式提高可读性
• 需要添加必要的注释提高可维护性

算术运算符：

数字和日期使用的算术运算符

-- 查询雇员的工号，姓名 ，职位，薪资，奖金，并为每一个员工的薪资增加1000元 作为过节费
SELECT empno,ename,job,sal,comm,sal+1000 FROM emp;
--  查询雇员的工号，姓名 ，职位，薪资，奖金， 并计算每一个员工的年薪
SELECT empno,ename,job,sal,comm,sal*12+comm from emp;
SELECT empno,ename,job,sal,comm,(sal+comm) *12 from emp;

算术运算的优先级：先乘除后加减，可以通过括号来改变运算的顺序

列的别名：

给某个列重新进行命名，便于使用和区别，使得语义更加明确
别名的使用：
列明 AS 别名

  --别名
  SELECT empno as no,ename as name,sal, sal*12 as salForYear from emp;
  -- 在给列进行起别名的时候  as 可以省略v
  SELECT empno   no,ename   name,sal, sal*12   salForYear from emp;

连接符 ||

  --查询雇员的姓名 职位 薪资，入职时间作为个人基本信息输出到一列 
  SELECT ename||job||sal||hiredate AS "personalInfo" from emp;
  --查询雇员的姓名 职位 薪资，入职时间作为个人基本信息输出到一列，每个信息之间使用—分隔
  SELECT ename||'-'||job||'-'||sal||'-'||hiredate AS personalInfo FROM emp;

字符串：

在oracle中 字符串可以作为select语句中的一个字符，数字 ，日期等出现
日期和字符只能在单引号中出现，oracle中的字符串是使用单引号表示的

去除重复记录：

  --查询雇员所在的部门编号
  SELECT deptno FROM emp;
  --去除查询中的重复的记录
  SELECT DISTINCT deptno FROM emp;

过滤查询：

Where + 过滤条件
Select  *|distinct  column|表达式 as 别名 from tablename where 条件；

  -- 查询20部门的所有雇员信息
  SELECT * FROM emp WHERE deptno=20;
  -- 查询员工薪资低于1000的员工
  SELECT * FROM emp WHERE sal < 1000;
  --查询姓名为SMITH雇员的基本信息，注意 对于字符串是严格区分大小写的
  SELECT * FROM emp WHERE ename='SMITH';
  --查询入职日期为17-12月-80的雇员信息，日期作为字符串出现需要加单引号，同时月份要使用英文缩写
  SELECT * FROM emp WHERE hiredate='17-dec-80';

比较运算：

符号	意义
=	等于 （赋值运算符 :=）
>	大于
<	小于
>=	大于等于
<=	小于等于
<>(!=)	不等于

其他的比较运算：

符号	意义
between… and…	取介于连个值之间的
IN(值1，值2….)	等于其中的任意一个
LIKE	模糊查询 匹配查询
IS NULL	空值

  --查询薪资介于1500--3000的员工 包含边界值
  SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;
--查询部门编号为10,20,30部门的员工
  SELECT * FROM emp WHERE deptno IN(10,20,30);
  --查询姓名以S开头的员工 
  /*在LIKE使用中，一般会用到两个通配符 
         _ 代表一个字符
         % 代表零个或多个任意字符
  */ 
  SELECT * FROM emp WHERE ename LIKE 'S%';
  --查询姓名中第二个字母为A的员工
  SELECT * FROM emp WHERE ename LIKE '_A%';
  --查询姓名中包含字母A的员工信息
  SELECT *　FROM emp WHERE ename LIKE '%A%';

  --查询出除了10部门的所有员工
  SELECT * FROM emp WHERE deptno <> 10;
  SELECT * FROM emp WHERE deptno != 10;

NULL ：

在算术运算中，如果某一个字段中包含空值，则运算之后的结果为空
区分空值和0：空值是null 表示什么都没有，不是0也不是空格

  --查询没有奖金的雇员信息
  SELECT * FROM emp WHERE comm IS NULL;
  --查询所有有奖金的雇员信息
  SELECT * FROM emp  WHERE comm IS NOT NULL;

逻辑运算：

符号	意义
AND	逻辑与
OR	逻辑或
NOT	取反

  -- 查询工资大于1000,并且姓名开头字母为A的雇员信息
  SELECT * FROM emp WHERE sal > 1000 AND ename LIKE 'A%';
  --查询工资大于1000或者职位为CLERK的雇员信息
  SELECT * FROM emp  WHERE sal>1000 OR job ='CLERK';
-- 查询职位不是CLERK的雇员信息
  SELECT * FROM emp WHERE job != 'CLERK';
  SELECT * FROM emp WHERE NOT  job = 'CLERK';

运算符的优先级:

可以通过添加括号来改变运算符的优先级

查询排序:

ORDER BY子句
	ASC 升序 
	DESC 降序

-- 查询所有雇员信息,并按照薪资从高到低排序
 SELECT * FROM emp  ORDER BY sal desc;
 SELECT * FROM emp  ORDER BY sal ; --默认排序方式为升序
 SELECT * FROM emp  ORDER BY sal ASC;

日期型的数据排序:

  --查询所有雇员信息 并按照雇佣日期进行排序
  SELECT * FROM employees ORDER BY hire_date desc;
  SELECT * FROM employees  ORDER BY hire_date ;

根据别名排序:

  -- 计算所有雇员的年薪 并排序
  SELECT empno,ename,sal, sal * 12 yearSal FROM emp  ORDER BY  yearSal;

  -- 计算所有雇员的年薪 并排序
  SELECT  empno,ename,sal, sal * 12 yearSal FROM emp  ORDER BY  yearSal;
  --按照姓名排序
  SELECT * FROM emp ORDER BY ename;
  --按照年薪进行排序，如果年薪相同，则按照姓名排序
  SELECT empno,ename,sal, sal * 12 yearSal  FROM emp ORDER BY yearSal ,ename;

先过滤后排序:

  -- 对20部门的员工按照薪资进行排序
  SELECT * FROM emp WHERE deptno = 20 ORDER BY sal desc;

SELECT 查询字段 FROM 表名 WHERE 条件 ORDER BY 排序字段 DESC/ASC;

单行函数:

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是一列或一个值

字符函数:

大小写控制函数：
Lower：将所有字母转换为小写
Upper：转换为大写
INITCAP：首字母大写

-- 查询emp表中的smith
SELECT * FROM emp WHERE ename = 'SMITH';
select * FROM emp WHERE lower(ename) = 'smith';
select lower(ename)  from emp;
select initcap(ename) from emp;

字符控制函数：

--字符控制函数
select concat('Hello', 'World') from dual;--字符串拼接
select substr('helloworld',2,3) from dual;--字符串截取  下表是从1开始的 从什么位置开始  截取的长度
select length('helloworld') from dual;--长度
select instr('helloworld','o') from dual;--字符在字符串中第一次出现的索引
select lpad(sal,10,'*') from emp;--将字段的长度补全为10位，不足的左边补为*
select rpad(sal,10,'*') from emp;;--将字段的长度补全为10位，不足的右边补为*
select trim('  he  ll o world  ') from dual;-- 去除字符串左右空格
select replace('helloworld','l','L') from dual;--用大写L替换小写l

数字函数:

• ROUND: 四舍五入
  • ROUND(45.926, -2) 45.93
• TRUNC: 截断
  • TRUNC(45.926, 2) 45.92
• MOD: 求余
  • MOD(1600, 300) 100

--数值函数
select round(12.345) from dual; --四舍五入 不保留小数
select round(12.345,2) from dual;-- 四舍五入 保留两位小数
select trunc(69.35) from dual;-- 截断 只保留需要的位数 不需要的全部舍弃 不进行四舍五入
select trunc(69.35,1) from dual;
select mod(10,3) from dual;--求余

日期函数:

Oracle提供了很多的日期时间相关函数 可以对日期进行加减操作 ，操作规律
日期- 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字

select  sysdate from dual;--查询系统的当前时间

在PLSQL中,日期的显示格式与设置有关

--日期时间相关
select  sysdate from dual;--查询系统的当前时间
select (sysdate - 7) from dual;
select (sysdate + 7) from dual;
--计算雇员表中所有的雇员从入职距离当前时间的周数
select ename,round((sysdate - hiredate)/7)  week from emp;

select ename,round(months_between(sysdate,hiredate)) from emp;
select add_months(sysdate,3) from dual;
select next_day(sysdate,'MONDAY') from dual;
select last_day(sysdate) from dual;

转换函数：

隐性转换 自动完成

显式数据类型转换:

TO_CHAR函数对日期的转换：
TO_CHAR(date, ‘format_model’)
格式:
• 必须包含在单引号中而且大小写敏感。
• 可以包含任意的有效的日期格式。
• 日期之间用逗号隔开。
SELECT TO_CHAR(sysdate,‘yyyy-mm-dd hh:mi:ss’) FROM dual;
日期格式的元素:

时间格式:

使用双引号向日期中添加字符:

--获取系统日期 并按照yyyy-mm-dd hh:mi:ss格式显示
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--获取系统日期 并按照yyyy-mm-dd hh:mi:ss格式显示
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
-- 查询所有雇员信息 并将入职日期按照yyyy-mm-dd hh:mi:ss格式显示
select ename,job,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
-- 查询1985年以后入职的员工
select ename,job,hiredate from emp where to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') > '1986-1-1 00:00:00';

数字到字符的转换：
TO_CHAR函数对数字的转换

--完成数值到字符的转换 
SELECT to_char(sal,'$999,999,999.00' ) FROM emp ;
SELECT to_char(sal,'L999,999,999.00' ) FROM emp ;

--字符串转换为数字：
SELECT to_number('$123456789.6963','$999999999999.0000'), to_number('123456789.6963','999999999999.0000'), to_number('123456789.6963') from dual;

通用函数：

这些函数适用于任何数据类型，同时也适用于空值：

• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, …, exprn)

1. NVL 将空值（null）转换为一个数值，转换的对象可以是数值、字符、日期

-- NVL函数：计算员工的年薪，将comm计算在内 
SELECT ename,sal,(sal * 12) + NVL(comm,100) yearSal FROM emp;
-- 对于没有MGR的员工统一由Scott管理
SELECT ename,sal,NVL(mgr,7788) FROM emp;

2. NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

-- 查询员工的姓名、薪资、奖金，如果奖金为空，则此时显示年薪（包含奖金） 如果计算得到的年薪为空，则显示10000
SELECT ename,sal,nvl2(comm,(sal *12 +comm),10000) from emp;

3. NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1

-- 查询员工的姓名和职位的名称的长度，如果长度相同 则结果返回null  否则返回员工姓名的长度
SELECT ename, LENGTH(ename) "expr1",job, LENGTH(job)  "expr2",
       NULLIF(LENGTH(ename), LENGTH(job)) result
FROM emp;

4. COALESCE：如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE

SELECT ename,sal,coalesce(comm,(sal *12 +comm),10000) from emp;

SQL 和SQL*PLUS

SQL：
结构化查询语言，是用来查询数据和操作数据库
SQLplus 工具（环境）：主要用来编写SQL语句和相关命令 ，命令不能改变数据库中的数据的值。
SQLPLUS可以执行的 命令：

• 查看表的结构
• 编辑SQL语句
• 执行SQL语句
  查看表的结构：
  
  查询语句：
  Select *|distinct column|表达式 as 别名 from tablename;