数据库小结1

数据库:

基本的概念:
数据库管理系统(Database Management System--DBMS):
管理数据的一个软件系统
SQL(Structure Query Language),结构化查询语言,
它是操作数据库管理系统的一个标准:主要对数据库进行CRUD的操作,
数据库的运算都是通过SQL来完成的;
create:创建
delete:删除
update:修改
retrieve:检索

SQL根据命令功能的不同分为5个部分:
1.DCL(Data Control Language)数据控制语句:
grant,revoke...


2.DDL(Data Definition Language)数据定义语句:
create,alter,drop,truncate,comment on,rename to...


3.DML(Data Manipulating Language)数据操纵语句:
insert,update,delete


4.DQL(Date Query Language)数据查询语句:select


5.DTL(Data Transaction Language)数据事务语句:
commit,rollback,savepoint
-------------------------------------------------------------


Oracle(甲骨文) www.oracle.com

Oracle数据库有服务端和客户端组成:
服务端:
由一堆的数据库后台进程与监听器以及数据库所组成,其中最
主要的是以下两个服务,
OracleServiceXE Oracle数据库的主服务
OracleXETNSListener Oracle数据库的监听器

客户端:有多种
1.Oracle自带一个命令行客户端:sqlplus
   启动:运行cmd打开cmd命令窗口输入sqlplus


2.Navicat 第三方图形界面客户端
  
  快捷键:
  ctrl + q 开启查询
  ctrl + r 运行SQL命令
  ctrl + shift + r 运行选中的SQL命令

创建用户2步骤:


1.创建一个(普通)用户:
  语法:create user 用户名 identified by 密码;


2.授权给一个用户:
  语法:grant 权限,权限... to 用户名;


oracle中内置了3个角色
DBA 系统管理权限;
connect 创建会话连接权限;
resource 操作基本数据库对象的权限;

修改密码: alter user 用户名 identified by 新密码;


删除用户: drop user 用户名;(删除时注意权限问题)


账户解锁: alter user 用户名 account unlock;


-------------------------------------------------------------


sqlplus中常用命令:


1.切换用户:
  conn 用户名/密码;


2.显示当前登录的用户名:
  show user;


3.执行数据库脚本:
  start 或者 @ *.sql(可以直接把脚本拖到命令窗口中)


  例如:start D:/oralce_cn.sql;


  数据库脚本:用于创建数据库对象的语句集合
  目的:在数据库中创建储存数据的表格并且在表格中初始化数据


4.查看表的结构:这是sqlplus的命令,不是sql的命令
  desc 表的名字;
  
5.清屏:
  clear screen;


6.回忆记录:
  方向键上下


7.编辑多行sql语句:
  edit
-------------------------------------------------------------


基本查询:


表格(table):它是由行(rows)和列(column)组成的二维空间
它是数据库存贮数据的基本单位,将来我们的数据就是存贮在表中;


查询的本质:
1.要查什么:
2.去哪里查:


语法: select 列名 from 表格名;


例如:
-- 找出员工的所有的信息;
select * from s_emp;

排重: distinct
--------------------------------------------------------------------------


列的数据类型:
1.数字类型,可以做所有的数学运算
  number     number(4) 代表整数 最大能存9999
   number(7,2)代表double类型  整数长度为5 小数位为2.


2.字符型,可以做拼接的运算
  注:字符串在数据库中用'';
  varchar2(20) 代表字符串最长为20;
  char(20) 代表定长20;


3.日期类型,可以做加减运算
  Date 年月日时分秒
  Timestamp 年月日时分秒,还有小数位,如3.2秒


4.大数据类型
  clob character large object大字符型对象 最大可存4G
  blob binary large object大二进制对象 最大可存4G


注:大数据类型不支持查看结果


如:
sql具有运算的能力
--从员工表中查询出员工名字和他的年薪
select first_name 名字,salary*12 年薪 from s_emp;
select first_name "名   字",salary*12 "年   薪" from s_emp;


sql具有字符串拼接能力并且可以给列取名
-- 从s_emp表中查出员工的姓名;
select first_name ||' '|| last_name 全名 from s_emp;


--字符串拼接(***是**入职的,工资是***,职称是***)
select 
first_name ||' '|| last_name ||'是'||to_char(start_date,'yyyy-mm-dd')||
'入职的,工资是'||salary||',职称是'||title 员工信息描述
from s_emp;


注意:
oralce数据库中:
字符串使用''包裹
""可以取别名,以及用来保存格式,还可以区分大小写
-----------------------------------------------------------------------------


空值置换函数


--查询所有员工的名字,年薪(不考虑提成)
select first_name,salary*12 from s_emp;


  ""的作用给我们的查询出的列起个别名 并保持格式
  别名不一定需要"";如果要保持格式必须有""


--查询所有员工的名字,年薪(考虑提成)
(错误)
select first_name,salary*12*(1 + commission_pct/100) from s_emp;


(正确)
select first_name,salary*12*(1 + nvl(commission_pct/100,0)) 
from s_emp;


null和所有的值进行运算最后都为null;
空值置换函数nvl(commission_pct/100,0)
如果commission_pct/100的值为null 则表达式的值为0
如果commission_pct/100的值不为null 则表达式的值为commission_pct/100


注:nvl()中的两个值要为同一数据类型


---------------------------------------------------------------------------
条件查询:
where字句用来过滤查询的数据,它对字面量大小写是敏感的


出现在where后面的运算符有:


1.比较运算符:
 > , < , >= , <= , =  , != ^= <>


--查出在41部门的员工名字,工资;
select first_name,salary,dept_id from s_emp where dept_id=41;

2.逻辑运算符:
and,or,not


--查出41部门工资高于1200的员工名字,工资;
select first_name,salary from s_emp where dept_id=41 and salary>1200;

3.其他运算符:
in(list) list是一个列表，多个值使用逗号隔开
not in()
between and 在指定的范围之内，是全闭空间
is null
is not null


模糊匹配:like


通配符:
_ 通配任意单个字符
% 通配任意多个字符


--查出在41,42,50部门的员工名,薪水;
select first_name,salary from s_emp where dept_id in(41,42,50);

--找出工资大于1500并且没有提成的员工;
select * from s_emp where salary>1500 and commission_pct is null;


注意:查询数据时条件是否为null,我们使用关键字is或者is null,千万不能使用=/!=


--查出名字是以M打头的员工;
select * from s_emp where first_name like 'M%';


--查出姓名中第三个字母是e的员工;
select * from s_emp where first_name like '__e%';

补充:
如果我们要通配的字符中本身就有_或%,则
需要通过关键字escape来转义;


select * from s_emp where first_name like '/_a%' escape '/';
--------------------------------------------------------------------------


排序字句:
order by 列名 asc(升序,默认可不写) | desc (降序)


--找出6月份入职的员工姓名,工资,职称,并按工资降序排序
select first_name,salary,title from s_emp
where start_date like '%6月%' order by salary desc;

--查询出年薪低于25000的员工名,职称,并按年薪升序排序
select first_name,title,salary*12*(1+nvl(commission_pct/100,0)) 年薪
from s_emp where salary*12*(1+nvl(commission_pct/100,0))<25000
order by 3;


注意:
order by 3表示按照查询结果的第三列进行排序
--------------------------------------------------------------------------


dual是一张虚拟的表格,没有任何的意义,只是为了充当构建完整的select语句


如:查询当前数据库系统时间
select sysdate from dual;


单行函数(single function):
多行函数(组函数)


oracle中提供了大量的函数


处理方式的不同分为:
1.数字函数:
  round(列|值|表达式,有效位数); 四舍五入
  trunc(列|值|表达式,有效位数); 直接截取
  mod(列|值,列|值); 求模(余)
  abs(列|值); 求绝对值


select round(3.1415926,3) from dual;
select trunc(3.1415926,3) from dual;
select mod(10,3) from dual;


--查询出订单表中总金额(取整)
select id,round(total,0) from s_ord;


select round(3.14159,3) from dual;  //3.142
select round(3.14159,0) from dual;  //3
select round(3.14159,-1) from dual; //0

2.字符函数
length(列|值|表达式); 求长度


--查询员工表中每个员工姓氏的长度
select first_name,length(first_name) from s_emp;


upper(列|值|表达式);  转大写字母
lower(列|值|表达式);  转小写字母


initcap(列|值|表达式); 每个单词首字母大写
select initcap('hello world') from dual;


lpad(列|值,宽度,填充字符);  从左边补不足宽度个填充字符
rpad(列|值,宽度,填充字符);  从右边补不足宽度个填充字符
select rpad('abc',10,'de') from dual;//abcdededed


ltrim(列|值,截取字符); 从左边截取字符 
rtrim(列|值,截取字符); 从右边截取字符
select rtrim('abcdededededede','de') from dual;//abc
select rtrim('abcdededeedede','de') from dual;//abc
select rtrim('abcdededeadedede','de') from dual;//abcdededea


replace(列|值,被置换的字符,置换的字符) 置换指定字符
select replace('hello world!','o','*') from dual;


translate()转换指定字符
select translate('I l6o9ve y8o7u!','abcdefghijklmnopqrstuvwxyz0123456789',
  'abcdefghijklmnopqrstuvwxyz') 
from dual;


select replace('abcdededeadedede','de','') from dual;
或者
select translate('abcdededeadedede','abcde','abc') from dual;//abca


instr()查找指定字符串在长字符串中所在的位置
--select instr('go,go,quickly!','go') from dual;
--select instr('go,go,quickly!','go',2) from dual;
--select instr('go,go,quickly!','go',-1) from dual;
--select instr('go,go,quickly!','go',-1,2) from dual;


注意:数据库字符串下标从1开始计数




substr()
select substr('hello world!', 3) from dual;
select length(substr('hello world!',3,4)) from dual;
//从3号下标开始切割,保留4个字符




decode(),类似一个三目运算符
比较第一和第二个参数,如果相等取第三个参数,如果不相等取第四个参数
select decode('A','A','B','C') from dual;
select decode(1,1,2,3) from dual;
select decode(1,2,2,3) from dual;


case when 可以把它看成switch功能:
select name "部门名",
case region_id
     when 1 then '北美'
     when 2 then '南美'
     when 3 then '中东/非洲'
     when 4 then '亚洲'
     when 5 then '欧洲'
     else '未知区域'
end "区域名"
from s_dept;


--找出职称是 stock clerk的员工
select * from s_emp where upper(title)=upper('stock clerk');

--请把员工的工资分为3等,超过2000元的为高等在1500和2000之间的为中等
低于1500的为低等
select first_name 姓名,salary 工资,
case
when salary<1500 then '低等'
when salary between 1500 and 2000 then '中等'
when salary>2000 then '高等'
end 工资等级
from s_emp;


注意:
如果when后面表达式中出现了要选择的列,则case后面不能再添加列名,
反之,如果when后面是字面量,则case后面必须添加列名以确定该字面量
所属的列

3.日期函数


oracle 默认的日期格式为 dd-mon-yy


dd 表示2位数的日
mon/month 代表月份,如:6月
mm 代表2位数的月,如:06
yyyy 代表4位数年份
HH24/HH 代表小时
mi 代表分钟
ss 代表秒
fm 表示去掉前面的0,如:fm 06,以后值6
day 表示星期几




日期函数:
sysdate 当前日期
months_between(date1,date2) 2个日期之间的月数
add_months(date1,n)    在date1的基础上加上几个月
next_day(date1,'星期几')    在date1的基础上下个星期几是什么时候
last_day(date)    date日期所在月的最后一天是什么时候


例如:
--查询当前日期下一个星期四是几号?
select next_day(sysdate,'星期四') from dual;


修改当前会话语言环境:
alter session set nls_language = 'american';//改为英文
alter session set nls_language = 'simplified chinese';//改为简体中文


修改当前会话日期的格式:
alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss';




找出员工的工作月数:
select first_name,months_between(sysdate,start_date) from s_emp;


查询员工的工作天数:
select first_name,sysdate - start_date from s_emp;


计算一年前,当前,一年后的时间:
select add_months(sysdate,-12),sysdate,add_months(sysdate,12) from dual;


当前日期前六个月的最后一天;
select last_day(add_months(sysdate,-6)) from dual;




4.转换函数


to_number(char), 把字符转换成数字


to_date(char[,fmt]), 
把字符串转换成日期,如果不传fmt参数,则采用默认格式(DD-MON-YY)来解析

to_char 把数字或日期格式化为字符串
  1.to_char(number,fmt);  格式化数字
  2.to_char(date, fmt);   格式化日期


--把123字符串转换成123数字
select to_number('123') from dual;


--把1986-04-13字符串按照模板yyyy-mm-dd转换成日期
select to_date('1986-04-13','yyyy-mm-dd') from dual;


--把数字格式化为字符串的时候可以用的一些符号:
 9  代表任意的数据
 L  代表本地的货币符号
 $  代表美元
 0  代表0
 .  代表.
 ,  代表,


练习:


--格式化输出员工的工资($1,500.00)
select first_name,to_char(salary,'L999,999,999.99') from s_emp;


--把员工的入职日期格式化为年/月/日
  select to_char(start_date,'yyyy/MM/dd') from S_EMP;

--找出每个员工的名字和它的薪水(如:$2,500.00)
select first_name,to_char(salary,'$999,999,999,999,999.99') 
from s_emp;