数据库day1

telnet 10.10.20.20
0855info/0855info
ping 10.10.20.20

存储数据使用文件系统的缺点
1.没有数据类型
2.不安全
3.查询数据效率低
4.对数据完整性,没有约束
5.没有考虑到多用户情况

数据库定义
一个软件, 用来存储管理数据
DataBase Management System
DB
DBMS

Oracle 数据库 神谕
MySql SqlServer 中小型
Oracle DB2 企业级

数据库如何存储数据
数据库使用表(table)来存储数据
列(column), 关系(relation)

Users
username password name address zip
huxz 5678 胡鑫喆 北苑家园 100021
liucy 1234 刘纯阳 清华园 100084

Orders
orderId orderDate totalPrice state username
300001 .... 650 N huxz

数据库的工作模式
客户端(client)-服务器(Server)
通过SQL语言进行交互(Structured Query Language)
SQL语言的功能对数据进行添加, 修改, 删除, 查询

如何连接到Oracle
1.使用telnet登录到主机
telnet 10.10.20.20 目的:为了使用oracle客户端
kettas/kettas123
2.使用客户端工具sqlplus登录数据库
sqlplus
cksd0907/cksd0907
3.使用SQL
查询当前用户有哪些表
列 (表名) 系统表
SELECT table_name FROM user_tables;
获得表的列信息
DESC 表; (SQLPLUS命令)
select deptno, dname, loc from dept;

常见的SQLPlus命令
DESC / List Start Save Change Exit COL
sqlplus命令只能sqlplus环境
sqlplus命令可以不以分号结束
要执行操作系统命令使用!+操作系统命令
/ 执行最近的SQL命令
COL 格式化输出结果
col 列 format a宽度

col empno format 9999
col mgr format 9999
col ename format a10
col job format a10
col sal format 9999.99
col comm format 9999.99
col deptno format 99
set pagesize 200

select empno,ename,mgr,job,
hiredate,sal,comm,deptno from emp;

List 显示最近执行的SQL命令
Save 保存最近执行的一条SQL命令
save 2.txt
Change/C
c/要替换的子串/新子串
start/@
执行文本文件中的SQL语句

=====================================
查询数据
SELECT 列1, 列2, .... FROM　表 WHERE 条件

select empno, sal from emp;
-- comm 佣金
select empno, ename, job, mgr, hiredate, sal, comm,
deptno from emp;
等价于
select * from emp;
*代表所有的列
缺点:1.有可能查询出不必要的列
2.*执行效率没有直接写列名高

使用WHERE 子句限制结果数
查询工资高于2000的员工
select * from emp where sal > 2000;
select * from emp where deptno = 10;
OR AND NOT

> >= < <= =
不等于<> !=
select * from emp where deptno <> 10;
select * from emp where deptno = 20 and sal > 1000;

关键字
between ... and
select * from emp where sal >=1000 and sal <=3000;
等价于
select * from emp where sal between 1000 and 3000;

in关键字
select * from emp where deptno = 10 or deptno = 20;
等价于
select * from emp where deptno in (10,20);

select * from emp where job = 'SALESMAN'
or job = '

MANAGER';
select * from emp
where job in ('SALESMAN','MANAGER');

Like关键字 模糊查询
select * from emp where ename = 'SMITH';
select * from emp where ename like 'S%';
%代表0到多个任意字符
_代表一个任意字符
select * from emp where ename like '%M%'
and ename not like 'M%'
and ename not like '%M';
select * from emp where ename like 'SMI_H';
SMIHS
SMIMH
SMISH





100 + 200
select 100+200 from emp;
查询语句中可以有常量和表达式,表达式运算的次数
跟表中记录数有关.
+ - * /
abs 求绝对值
mod 求余数
系统表 DUAL 特殊表,只有一行一列,用于单行函数的测试
select mod(10, 3) from dual;

日期函数
字符串 日期
'1980-1-1'

日期转换为字符串
to_char(日期, 格式化字符串)
yyyy mm dd hh24 mi ss
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
from dual;
to_date(字符串, 格式化字符串)
select to_date('1999-3-1', 'yyyy-dd-mm')

求1980年入职的员工
select * from emp where to_char(hiredate,'yyyy')
= '1980';
select * from emp where to_char(hiredate,'mm')
= '02';
求81年12月之后入职的员工
select * from emp where
hiredate > to_date('1981-12','yyyy-mm');

trunc 截断
返回值也是日期
select trunc(sysdate,'year') from dual;
-- 按年截断, 只保留年份值, 月日变一,时分秒归零

select trunc(sysdate,'month') from dual;

select trunc(sysdate,'day') from dual;
-- 按日截断,截断到本周第一天

select to_char(
trunc(sysdate),'yyyy-mm-dd hh24:mi:ss')
from dual;

9i 10g 11g
Date Timestamp
日期+- 以天为单位
select to_char(sysdate +
1/24,'yyyy-mm-dd hh24:mi:ss')
from dual;

加减月份
add_months
select add_months(sysdate,-1) from dual;
===================================
列别名
select add_months(sysdate,-1) AS prevMonth from dual;
select add_months(sysdate,-1) prevMonth from dual;

表别名
select *, 100+200 from emp; 错误
select e.*, 100+200 from emp e;
======================================
字符串函数
length
select length('123456') changdu from dual;
select ename, length(ename) from emp;

substr 截取子串
select substr('abcdef', 2, 3) from dual;

instr 求子串下标 indexOf
子串 起始查找位置(从1开始)
select instr('abcdef' ,'cd', 1) from dual;
select instr('abcdcd' ,'cd', 4) from dual;
select instr('abcdcd' ,'ee', 1) from dual;

select * from emp where substr(ename, 1, 1) = 'M';

lower upper(查询大小写不敏感的数据)
smith SMITH Smith
select * from emp where lower(ename) = 'smith';

字符串连接
select 'a' || 'b' || 'c' from dual;
select concat(concat('a', 'b'), 'c') from dual;

NULL
求年薪
select empno,ename, sal*12 + comm from emp;
NULL参与算术运算,结果总为NULL
转换NULL
NVL(comm, 0)
如果第一个参数为null返回第二个参数
如果不为null,直接返回第一个参数

select empno,ename, sal*12 + nvl(comm,0) from emp;

select * from emp where comm is null;
select * from emp where comm is not null;

注意:
0 和 NULL不同
'' 等于 NULL

扩展知识点
decode函数
select empno,ename, sal*12 +
decode(comm,null,0,comm) from emp;

select empno,sal,deptno, decode(
deptno, 10, 'accounting',
20, 'market', 30, 'sales', 'none')
from emp;

case when语句
select empno, ename, deptno,
case when deptno = 10 then 'accounting'
when deptno = 20 then 'market'
else 'none'
end dept_name
from emp;
工资低于1500 low
1500 2500 middle
2500 high
select empno, ename, sal ,
case when sal <=1500 then 'low'
when sal >1500 and sal < 2500 then 'middle'
else 'high'
end lvl
from emp;

select comm ,
case when comm is null then 0
else comm
end
from emp;

重点:字符串函数,日期函数,case when, NULL
======================================
组函数(针对一组数据进行运算)
max 最大值
min 最小值
avg 平均值
sum 求和
进行运算时也不会考虑NULL值
select avg(comm) from emp;

count 求个数
select max(sal) from emp;
1.count(*)
select count(*) from emp; -- 求查询结果的个数
select count(*) from emp where deptno = 10;
2.count(列)
计算此列取值不为NULL的个数
select count(comm) from emp;
3.count(distinct 列) 去除重复值后,计算个数
distinct 作用:去除重复
select distinct job from emp;
--求职位个数
select count(distinct job) from emp;

select count(null) from emp;

小结
WHERE <> != OR AND NOT
between ... and ...
in (值1,值2,...)
like % _
函数
单行函数
日期
字符串
数字
组函数
CASE WHEN THEN　END


part 1
01. 查询员工表所有数据
02. 查询职位(JOB)为'PRESIDENT'的员工的工资
07. 显示10号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息
08. 显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
11. 显示各个部门经理('MANAGER')的基本工资

03. 查询佣金(COMM)为0或为NULL的员工信息
select * from emp where comm = 0 or comm is null;
04. 查询入职日期在1981-5-1到1981-12-31之间的所有员工信息
select * from emp where
hiredate >= to_date('1981-05-01','yyyy-mm-dd')
and
hiredate <= to_date('1981-12-31','yyyy-mm-dd');

05. 查询所有名字长度为4的员工的员工编号,姓名
15. 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
select * from emp where comm > sal;

part 2
03. 把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)
04. 把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
05. 求1982年入职的员工(考察知识点:单行函数)
06. 求1981年下半年入职的员工(考察知识点:单行函数)

part 3
02. 查询当前月有多少

天(考察知识点 日期函数的灵活运用)
select trunc(add_months(sysdate,1), 'month') - trunc(sysdate, 'month') from dual;

扩展题目:求本月第二个星期二是几号?
提示:to_char格式化字符串中单个 'd'表示星期几
1表示周日, 2表示周一,以此类推
select case
when to_char(trunc(sysdate, 'month'), 'd') = 3
then to_char(trunc(sysdate, 'month')+7,'dd')
when to_char(trunc(sysdate, 'month')+1, 'd') = 3
then to_char(trunc(sysdate, 'month')+8,'dd')
when to_char(trunc(sysdate, 'month')+2, 'd') = 3
then to_char(trunc(sysdate, 'month')+9,'dd')
when to_char(trunc(sysdate, 'month')+3, 'd') = 3
then to_char(trunc(sysdate, 'month')+10,'dd')
when to_char(trunc(sysdate, 'month')+4, 'd') = 3
then to_char(trunc(sysdate, 'month')+11,'dd')
when to_char(trunc(sysdate, 'month')+5, 'd') = 3
then to_char(trunc(sysdate, 'month')+12,'dd')
when to_char(trunc(sysdate, 'month')+6, 'd') = 3
then to_char(trunc(sysdate, 'month')+13,'dd')
end
from dual;

select mod(11-to_char(trunc(sysdate, 'month'), 'd'),7)+7
from dual;