Oracle入门--day1--SELECT语句、条件限制和排序、单行函数（上）

知识点

SELECT语句

SELECT 语句的作用：
列选择：SELECT Column1, Column2 from table1

行选择：SELECT * from table1

多表连接查询：
SELECT table1.Column1,table2.Column2 from table1,table2…

SELECT 语句语法：
SELECT * | {[DISTINCT] column|expression [alias],...}
FROM table;

 SELECT 表示要取哪些列
 FROM 表示要从哪些表中取

SQL 语句中的数学表达式：对于数值和日期型字段，可以进行 “加减乘除”

SELECT last_name, salary, salary + 300 FROM employees;

关于NULL 的概念 ：
NULL 表示 用 不可用、未赋值、不知道、不适用 ， ， 它既不是0 也不是空格。
记住：一个数值与NULL进行四则运算，其结果是？NULL
在 在Select 的时候给列起个别名（注意双引号的作用）：

 

字符串连接操作符： “||”，作用就是，将自己需要的查询结果连接成一个比较容易看懂的字段

比如：姓名张三 职位 老师 可以 姓名|| '/' ||职位 查询结果：张三/老师

SELECT last_name||job_id AS "Employees"
FROM employees;

 

DISTINCT 去除重复行：
SELECT department_id FROM employees; 默认情况，返回所有行，包括重复行

SELECT DISTINCT department_id FROM employees; 使用DISTINCT

 

条件限制和排序

条件限制的关键词：WHERE

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];

比较操作符：

比较操作符	意义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于
BETWEEN ...AND...	在两个之间（闭区间）
IN(set)	在集合里面
LIKE	匹配一个字符串，可与通配符%搭配使用
ISNULL	试一个空值，注意不要用 = NULL

 

使用LIKE

可使用% 或者_ 作为通配符：
% 代表 0 个或者多个符 字符.
_ 代表一个单个字符.

如果要搜索统配符本身该怎么办呢？：

要求找出含有% 的记录
这需要使用ESCAPE 标识转义字符
select * from t_char where a like „%\%%' escape '\';

 

使用NULL 条件：

SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

使用多个条件组合的逻辑操作符：

AND 所有条件都满足，返回TRUE
OR 只要有一个条件满足，返回TRUE
NOT 如果条件是FALSE,返回TRUE

 

使用ORDER BY 子句进行排序：
ASC ：升序
DESC ：倒序

 

单行函数（上）

SQL 函数类型：单行函数和多行函数

单行函数的类型：

字符函数

大 小写转换函数：

函数                                      结果
LOWER('SQL Course')          sql course
UPPER('SQL Course')            SQL COURSE
INITCAP('SQL course')          Sql Course

 

Oracle 数据库中的数据是大小写敏感的 ：
 

字符串操作函数：

数字操作函数：

日期：（注：客户端是中文的时候 ，月份需要将输入改为 中文的，如 20- 四月 -2020

日期运输：

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS, sysdate+1 as
tomorrow , hire_date + 8/24
FROM employees
WHERE department_id = 90;

 

不同类型的数据转换函数：

 

Oracle 数据类型的 隐私转换规则：
对于赋值操作可以：

 

Oracle 数据类型的 显式转换函数：

 

TO_CHAR() 函数：日期到字符串的转换

TO_CHAR(date, 'format_model') ;

TO_CHAR() 函数：数字到字符串的转换

TO_CHAR(number, 'format_model') ;

TO_NUMBER() 函数：字符串到数字的转换

TO_NUMBER(char[, 'format_model']) ;

TO_DATE() 函数：字符串到日期的转换

TO_DATE() 函数：日期转换时使用RR

 

TO_NUMBER() 函数：字符串到数字的转换

 

函数嵌套
单行函数可以被无限层的嵌套，计算时先计算里层，再计算外层

 

题目：

员工信息表：姓名、工号、性别、年龄、省、市、县（区）、籍贯、出生日期、入职日期、联系电话、部门、部门编码、科段、科段编码、岗位、岗位编码，工作地点，家庭地址

1、查询员工个人信息并且对部门进行排序，将员工信息中的的部门、科段、岗位、姓名组合为一个字段“职务”，如："职务：张三/业务分析/流程与IT科/运营管理部"；
2、查询员工表中有多少个部门信息
3、修改其中某个部门的员工入职时间，整体往后推迟一个星期
4、查询“运营管理部”的员工年龄段在25~30之间的男性职工
5、查询工作地点为空的员工，并按部门排序
6、查询属于“湖南“、“湖北”的员工
7、查询姓名中有"建"字的员工
8、查询在”2020/01/01“以后入职的员工

薪酬表字段：工号、姓名、部门、岗位、工作天数、矿工次数、工资、年份、月份

1、查询”清理工“这个岗位中，工资大于2000的员工
2、查询各部门的最高、最低工资，按部门排序
3、在”运营管理“查出来的工资基础上加上500

 

ps：表其实是不符合规范的，但是，这里主要是为了写作业，满足需求。暂时不考虑那些，勿学。。

 

代码实现及注解：

 

-- drop table t_employee;
-- 建表 员工信息表

create table t_employee
(empno varchar2(10) PRIMARY KEY,
ename varchar2(20) NOT NULL,
sex varchar2(8),
age number(3),
province varchar2(64),
city varchar2(64),
district varchar2(64),
hometown  varchar2(100),
birthday timestamp,
hiredate timestamp,
tel varchar2(11),
deptname varchar2(20),
deptno varchar2(10),
secname varchar2(20),
secno varchar2(10),
jobname varchar2(20),
jobno varchar2(10),
address varchar2(100),
constraint epm_1 check (sex in ('男','女'))
);

/*
ALTER TABLE t_employee modify (empno varchar(10));

ALTER TABLE t_employee add (age number(3));


ALTER TABLE t_employee modify (hiredate data);
ALTER TABLE t_employee modify (birthday data);
*/
commit;

-- 插入数据

insert into t_employee values('yc00059000','晓明哥','男',18,'湖南省',
'娄底市','双峰县','湖南省娄底市',TO_DATE('18-6月-1998','DD-MON-YYYY'),
TO_DATE('17-4月-2020','DD-MON-YYYY'),'15616500000','支付二','def0002',
'不知道','null001','技术顾问','417','广西壮族自治区玉林市。。。');

insert into t_employee values('yc00059018','鹏程弟弟','男',15,'浙江',
'某某市','某县','某省某市',TO_DATE('18-6月-2020','DD-MON-YYYY'),
TO_DATE('17-4月-2020','DD-MON-YYYY'),'15616500001','支付二','def0002',
'不知道','null001','技术顾问','418','广西壮族自治区玉林市。。。');

insert into t_employee values('yc00059420','刘哥','男',22,'四川省',
'某市','某区','四川省某市',TO_DATE('20-5月-1996','DD-MON-YYYY'),
TO_DATE('13-4月-2020','DD-MON-YYYY'),'15616500000','支付二','def0002',
'不知道','null001','技术顾问','425','广西壮族自治区玉林市。。。');

insert into t_employee values('yc00059001','王总','男',55,'湖南省',
'娄底市','双峰县','湖南省娄底市',TO_DATE('8-8月-1968','DD-MON-YYYY'),
TO_DATE('17-4月-2014','DD-MON-YYYY'),'15616500000','支付二','def0002',
'不知道','null001','技术顾问','417','广西壮族自治区玉林市。。。');

insert into t_employee values('yc00059000','王总','男',56,'湖北省',
'娄底市','双峰县','湖南省娄底市',TO_DATE('8-8月-1968','DD-MON-YYYY'),
TO_DATE('17-4月-2014','DD-MON-YYYY'),'15616500000','交付一','def0001',
'运营管理部','null000','经理','000','广西壮族自治区玉林市。。。');

--update t_employee set hometown='湖北省'where empno='yc90059001';

commit;

--1、查询员工个人信息并且对部门进行排序，将员工信息中的
--部门、科段、岗位、姓名组合为一个字段“职务”，
--如："职务：张三/业务分析/流程与IT科/运营管理部"；

-- select * from t_employee;

select empno,ename,ename||'/'||deptname||'/'|| secname||'/'|| jobname
 as detail from t_employee  order by deptno;

--2、查询员工表中有多少个部门信息

select count(distinct(deptno)) as "部门总数" from t_employee ;


--3、修改其中某个部门的员工入职时间，整体往后推迟一个星期

--select ename,deptno,hiredate,hiredate+7 from t_employee;

update t_employee set hiredate = hiredate+7 where deptno='def0002';


--4、查询“运营管理部”的员工年龄段在25~30之间的男性职工

select ename,age from t_employee where age between 50 and 60;


--5、查询工作地点为空的员工，并按部门排序(少写了一列，换成省为空吧

select empno,ename,deptno from t_employee where province is null order by deptno;


--6、查询属于“湖南“、“湖北”的员工

select empno,ename,hometown from t_employee 
where hometown like '湖南%' or  hometown like '湖北%';


--7、查询姓名中有"总"字的员工

select empno,ename from t_employee where ename like '%总%';


--8、查询在”2020/01/01“以后入职的员工

select empno,ename,hiredate from t_employee 
where hiredate > to_date('2020/01/01','yyyy-mm-dd');


--薪酬表字段：工号、姓名、部门、岗位、工作天数、矿工次数、工资、年份、月份


--创建薪水表

create table t_salary
(empno varchar2(10),
ename varchar2(20),
deptname varchar2(20),
deptno varchar2(10),
secname varchar2(20),
secno varchar2(10),
jobname varchar2(20),
jobno varchar2(10),
workday number(2),
notworkday number(2),
esalary number(10,2),
yyear varchar2(4),
mmonth varchar2(2)
);

select * from t_salary;

ALTER TABLE t_salary modify (esalary number(10,2));

insert into t_salary values('yc00059000','晓明哥','支付二','def0002',
'不知道','null001','技术顾问','417',12,18,120.00,'2020','4');

insert into t_salary values('yc00059018','鹏程弟弟','支付二','def0002',
'不知道','null001','技术顾问','418',8,12,1800.00,'2020','4');

insert into t_salary values('yc00059425','刘哥','支付二','def0002',
'不知道','null001','技术顾问','425',8,12,1800.00,'2020','4');

insert into t_salary values('yc00059001','王总','支付二','def0002',
'不知道','null001','技术顾问','417',8,12,1800.00,'2020','4');

insert into t_salary values('yc00059000','王总','交付一','def0001',
'运营管理部','null000','经理','000',25,5,2800.00,'2020','3');

--1、查询”清理工“这个岗位中，工资大于2000的员工

select ename,esalary,jobname from t_salary where esalary > 2000 and jobname='经理';

--2、查询各部门的最高、最低工资，按部门排序

select deptno,max(esalary) as "最高工资",min(esalary) as "最低工资" from t_salary 
group by deptno;

--3、在”运营管理“查出来的工资基础上加上500

select ename,esalary,esalary + 500 as "加500" from t_salary;