• 数据库相关


    【数据库系统概述】
    常用的数据库有MySql、oracle等。不同数据库都支持sql标准,并且不同数据库在sql标准的基础上进行了一些扩充。
    对于数据库的学习包括:sql>过程、触发器等内容,其中重要程度如下:
    sql>过程、触发器等
    oracle数据库:
    1、oracle的开发部分,包含两个部分:sql+plsql编程
    2、oracle管理部分,数据库配置和运行维护
    【oracle简介】
    oracle默认有sys和system两个用户,其中
    sys: 超级管理员,拥有操作数据库的所有权限
    system:普通管理员
    注意:安装oracle后会出现多个服务,可以设置为手动启动
    重要的两个服务为:
    1、数据库监听服务,如果要通过远程客户端(如sql develop等)连接数据库,或者直接用程序连接数据库,那么此服务必须打开
    2、数据库实力服务:每个数据库都会有这种服务名称如:OracleServiceSID
    【sqlplus简介】
    sqlplus 首先将sql语句放到缓冲区,然后将缓冲区的sql语句提交到数据库执行;
    oracle12c 之中默认数据找不到,需要对数据进行恢复,找scott.sql文件目录下为数据
    通过修改SCOTT.sql修改恢复数据
    数据配置执行顺序为:
    1、打开sqlplus /nolog
    2、运行C##scott.sql
    【sqlplus 常用命令】
    1、格式化命令:
    数据显示出现换行问题,出现数据分页:
    1、首先要解决屏幕宽度:set linesize 300
    set pagesize 30
    2、方便编写长数据库脚本,可以调用记事本:ed,可以在技术本中编辑查询命令,随后可以使用@ 标记执行数据库脚本
    sqlplus执行sql脚本的方法:使用@+脚本
    3、连接操作
    用户之间可以互相切换
    CONN 用户名/密码【as sysdba】
    可以通过show user查询当前用户
    在sys中查询Scott中的表,需要添加用户名在表明前
    select * from tab;查询所有的表
    查看数据表的结构DESC:desc emp;
    执行host命令:host+命令
    host dir;

    关于原始用户的问题:
    恢复原始数据:
    1、登陆sys用户: conn sys/Oracle123456 as sysdba
    2、查看现在的容器名称:show con_name;
    返回值为:CDB$ROOT 为一个CDB容器
    3、改变容器为PDB: alter session set container=pdborcl;
    4、打开pdb数据库:
    alter database pdborcl OPEN;
    5、查看用户
    打开sh和scott用户
    6、切换回cdb
    alter session set container=cdb$root


    语法:
    SELECT[DISTINCT] *|列名 [as] 列别名,列名 [as] 列别名.... FROM 表名 表别名
    使用as设置别名,别名最好不要使用中文
    简单查询中两个字句:
    1、select字句
    distinct 表示去除重复列,仅限于所有列的内容都相同
    2、from子句
    关于字句的执行顺序:
    1、from字句,确定数据来源
    2、select字句,确定要显示的列
    select中的四则运算:
    当参与运算的数值中含有null值时,结果返回为null
    查询月薪、日薪等
    select sal+comm msal from emp;
    添加常量列:
    select 'y' as cl from emp;
    利用“||”进行字符串连接
    select '编号是:'||empno||'姓名是:'||ename from emp; 字符串是用单引号引起来
    【限定查询】

    1、语法:
    SELECT[DISTINCT] *|列名 [as] 列别名,列名 [as] 列别名.... FROM 表名 表别名 where 条件语句
    连接多个条件的逻辑运算符:and or not

    限定查询有三个字句,执行步骤为:
    1、执行from字句,来控制数据的来源
    2、执行where字句,使用限定对数据行过滤
    3、执行select字句,来确定数据列

    常用限定运算符:
    1、关系运算符,确定大小相等关系的比较
    select * from emp where sal《=2000
    select * from emp where ename='smith'
    在使用关系运算符判断字符串时需要注意大小写,并且字符串用单引号;字符串可以直接用“=”比较
    不等于符号“<>”和"!="两种操作形式
    select * from emp where ename!='JAMES';
    select * from emp where ename<>'JAMES';
    工资范围1500到3000
    select * from emp where sal>1500 and sal<3000;
    销售人员基本工资高于1200
    select * from emp where sal>1200 and job='saleman';

    范围查询
    between and 操作符 包含最大值和最小值
    查询出1981年雇员的全部信息;则范围是1981-1-1--1987-12-31
    select * from emp where hiredate between '01-1月-81' and '31-12月-81';

    判断内容是否为null:IS NULL/IS NOT NULL(只能这样判断)
    注意:null不能用等号判断
    select * from emp where empno=7369 and comm is null;

    列表范围查找:IN/NOT IN
    所谓列表范围是指给定了用户的几个值,必须在这些值范围内
    select * from emp where empno IN(7369,7788);
    select * from emp where empno not IN(7369,7788);
    注意:
    关于null的问题,如果在in操作符中包含null;不会影响最终的查询结果,如果在not in中包含null,直接的后果是没有任何数据显示
    select * from emp where empno not IN(7369,null);数据库系统的限制,not in 中有null不返回任何值

    【模糊查询】
    like/not like
    like字句中可以使用连个通配符:
    百分号%:可以匹配任意类型和长度的字符,如果是中文则使用两个百分号%%;(出现一次0次或者多次)
    下划线_:匹配单个任意字符,它常用来限制表达式的长度(出现一次)

    以J开头的:
    select * from emp where ename LIKE 'J%';
    查询字母中任意位置包含J,前后都有的问题用百分号
    select * from emp where ename LIKE '%J%';
    名字长度大于六个字符的:
    select * from emp where ename like'_____%';
    LIKE可以用于数字或者时间类型上面,关键字为空表示查询全部

    【数据排序显示】
    order by 默认升序
    传统数据查询的时候只会设置的逐渐排列,如果希望对指定的列进行排序,就需要使用order by 排序
    语法:
    SELECT[DISTINCT] *|列名 [as] 列别名,列名 [as] 列别名.... FROM 表名 表别名 where 条件语句 order by ASC|DESC
    ASC:升序
    DESC:降序
    在所有sql字句中order by是放在查询语句的最后一行,是最后一个执行的字句,
    select * from emp where sal>1000 order by sal asc;
    范例:按照工资高到底排序,工资相同,按照雇佣日期早到晚
    select * from emp order by sal desc,hiredate;

    【单行函数】
    语法:
    function_name[列] 表达式[参数1,参数2]
    单行函数主要分为以下几种:
    1、字符函数
    以字符数据为主(字符串)
    UPPER()和LOWER()函数转换大小写;ABCD
    SELECT UPPER('abcd') from dual;
    select LOWER(ENAME) FROM EMP;
    SUBSTR()函数
    select substr('abc',2) from dual;--返回bc
    select substr('abc',-1) from dual;--返回c;负数是从后面数为oracle特有的,下标从1开始
    ASCII码:
    select ascii('A') from emp; 65
    select chr(100) from dual; d
    trim函数:
    select ltrim(' adf') from dual;去掉左边空格
    select rtrim(' adf ') from dual;去掉右边空格
    select trim(' adf ') from dual;去掉空格
    填充:
    LPAD
    select lpad('ad',4,'*') from dual; **ad
    查找函数:
    instr
    select instr('floor','oo') from dual; 3
    select instr('floor','QQ') from dual; 0
    2、数值函数
    round 四舍五入
    select round(123.456) from dual; 123 不保留小数
    select round(123.456,2) from dual;123.46 保留两位小数
    select round(123.456,-2) from dual; 100 整数部分取整
    trunc 截取位数
    select trunc(123.456,2) from dual; 123.45
    mod 取模
    select mod(10,3) from dual; 1

    3、日期函数
    日期的计算操作和日期函数的使用
    1、取得当前日期:利用sysdate伪列取得当前时间
    select sysdate from dual;20-8月 -16
    默认情况下只包含了年月日三个内容,可以通过修改默认的语言方式来修改日期格式
    日期的算数运算:
    若干天前的日期:日期-数字=日期
    select sysdate-3 from dual; 17-8月 -16

    若干天后的日期:日期+数字=日期
    select sysdate+3 from dual;23-8月 -16

    两个日期的天数间隔:日期-日期
    select trunc(sysdate-hiredate) from emp; 雇佣天数

    日期的计算函数:
    ADD_MONTHS()函数
    select add_months(sysdate, 3) from dual;20-11月-16 三个月之后的日期
    select add_months(sysdate, -3) from dual;20-5月 -16 三个月之前的日期
    NEXT_DAY()函数
    select NEXT_DAY(sysdate, '星期日') from dual; 查询下一个星期日
    last_day()函数
    select last_day(sysdate) from dual; 本月的最后一天31-8月 -16
    查询当月倒数第三天雇佣的
    select ename from emp where hiredate=last_day(sysdate)-2;
    Month_between()月数
    select trunc(MONTHS_BETWEEN(sysdate, hiredate)/12) 雇佣年数 from emp;
    范例:计算出某一个雇员目前为止雇佣的年数,月数,天数**

    1、需要查询的表:emp
    select ename,trunc(months_between(sysdate,hiredate)/12) years,
    trunc(mod(months_between(sysdate,hiredate),12)) months,sysdate-(last_day(add_months(sysdate,-1))+1) days from emp;
    select ename,month_between(sysdate,hiredate)/12 years

    4、转换函数
    主要使用的数据类型:字符、数字和日期(时间戳)
    TO_CHAR()
    将date类型变成字符串:
    select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual; 2016-08-20 20:26:25
    select to_char(sysdate,'FMyyyy-MM-dd hh24:mi:ss') from dual; 2016-8-20 20:30:14 去除前导0
    查询出每年二月份雇佣的雇员信息
    select * from emp where to_char(hiredate,'MM')=2;
    拆分日期;
    select ename ,empno,to_char(hiredate,'yyyy') 年,to_char(hiredate,'MM') 月,to_char(hiredate,'dd') 日 from emp
    where to_char(hiredate,'MM')=2;ALLEN 7499 1981 02 20
    TO_CHAR()格式化数字:
    TO_DATE()函数;字符串-->日期比较少
    TO_TIMESTAMP()
    TO_NUMBER()基本不用
    select to_number('09') from dual; oracle中支持自动的类型转换,select 09 from dual; 结果相同

    5、通用函数:oracle提供的特色函数
    NVL()函数用于处理null值
    范例:查询年薪;有null参与的运算结果为null
    select nvl(sysdate-null,sysdate) from dual 20-8月 -16 为null的时候为sysdate
    NVL2()函数
    select nvl2(comm, sal+comm,sal) from emp
    NULLIF,相同的结果返回空

    DECODE()函数
    oracle最有特色的函数之一,类似于if else,但是判断的内容都是一个具体的值
    select DECODE(2,1, 'Ground', 2,'Air','默认值') from dual; 如果值为1,返回Ground,如果值为2,返回air,没有匹配的返回“默认值”
    注意:使用decode()函数判断,所有可能出现的数值都要判断,没有判断的内容为null,

    oracle9i后引入case表达式,根据给定的列或者字段依次判断
    select ename,sal,
    case job
    when 'clerk' then sal*1.1
    when 'salesman' then sal*1.2
    else
    sal*1.5
    end 新工资
    from emp;

    【多表查询】复杂查询
    多表查询先使用笛卡儿积的方式查出所有记录,再通过条件进行筛选
    语法:
    SELECT [DISTINCT] *|列明 as 列明 from 表名,表明2... where 条件语句
    笛卡儿积问题:
    select * from emp; 14条记录
    select * from dept; 4条记录
    select * from dept ,emp;56条记录4*16
    隐藏掉笛卡儿积列:使用关联字段
    select * from dept t ,emp e where t.deptno=e.deptno; 显示14行
    注:数据量很大的时候一般不用多表查询,因为笛卡儿积肯定是存在的,关联字段只是隐藏掉笛卡儿积的记录,并没有消除笛卡儿积
    范例:
    查询每个雇员的编号,姓名,职位,基本工资,部门名称,部门职位信息
    1、确定所需要的表:
    emp表:雇员编号,姓名,职位,基本工资
    dept表:部门名称,部门职位信息
    2、确定关联字段
    emp.deptno=dept.deptno
    select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
    from emp e,dept d
    where e.deptno=d.deptno;
    按照sql语句的执行步骤编写:FROM WHERE SELECT
    范例:查出每个雇员的编号,姓名,雇佣日期,基本工资,工资等级
    1、确定所需要的表:
    emp:雇员的编号,姓名,雇佣日期,基本工资
    salgrade:工资等级
    2、确定关联字段
    sal
    select e.ename,e.hiredate,e.sal,s.grade
    from emp e,salgrade s
    where sal between losal and hisal;
    范例:查询出每个雇员的姓名、职位、基本工资、部门名称、工资等级
    1、确定所需要的表:
    2、确定关联字段
    步骤一:查出雇员信息
    步骤二:查出部门表(消除笛卡儿积)
    步骤三:查出工资等级表
    步骤四:等级换成中文
    select e.ename,e.job,e.sal,d.dname,s.grade,
    case s.grade
    when 1 then '第五等'
    when 2 then '第四等'
    when 3 then '第三等'
    when 4 then '第二等'
    when 5 then '第一等'
    end sg
    from emp e,dept d,salgrade s
    where e.deptno=d.deptno and sal between losal and hisal;

    【多表查询】表的连接操作
    目标:清楚表的连接区别:内链接和外连接
    内连接:通过关联字段等值判断进行连接,消除关联字段不相等的连接,来隐藏笛卡儿积现象
    范例:内外链接的区别:
    1、添加一个没有部门信息的雇员
    2、执行以下查询语句
    select * from emp,dept where emp.deptno=dept.deptno;
    没有部门信息的员工没有显示,如果希望emp或者dept表中的数据显示完整,那么可以利用外连接
    范例:使用做外连接希望emp信息全部显示:

    外连接:如果想要某一个表的字段全部显示,则可以使用外连接通过"(+)"进行控制,只能在oracle中使用(+)
    此符号只能实现左边外连接或者右外连接
    左外连接:select * from emp e left outer join dept d on e.deptno=d.deptno;
    右外连接:select * from emp e right outer join dept d on e.deptno=d.deptno;
    全外连接:select * from emp e full outer join dept d on e.deptno=d.deptno;
    注意:只能在oracle中使用(+)进行外连接

    自身关联:
    emp中mgr字段表示雇员的领导信息:
    如果要显示领导信息,需要利用雇员表和雇员表自己的连接操作完成
    范例:查询出雇员表中的雇员姓名、编号和上级领导的编号和姓名

    对于没有领导信息的雇员,对应领导信息全部使用null进行连接
    King没有mgr信息,没有显示;解决方法外连接

    范例:查询在1981年雇佣的全部雇员编号、姓名、雇佣日期(年月日显示)、工作领导姓名,月工资,年工资(基本工资+佣金)工资等级,部门编号,名称,位置,同时要求这些雇员的月工资在1500-3500之间,
    最后按照年工资进行降序排列,工资相同,按照工作排序
    1、确定所需要的数据表
    2、确定已知的关联字段
    数据的集合运算
    集合运算是一种二目运算符,一共包含四种运算符,并差交笛卡儿积:
    语法如下:
    查询语句
    [union|union all |intersect|minus]
    查询语句
    select * from dept; 4条结果
    select * from dept where deptno=10; 一条结果
    两个查询结果返回的结果结构相同
    union(并集)返回若干个查询结果的全部内容,但是重复元祖不显示
    select * from dept union
    select * from dept where deptno=10; 4条结果
    union all(并集)返回若干个查询结果的全部内容,重复元祖也会显示
    select * from dept union all
    select * from dept where deptno=10; 5条结果
    范例:查询所有销售和办事员的信息
    select * from emp where job in('clerk','salesman');
    select * from emp where job='clerk' or job = 'salesman';
    注意:尽量使用union或者union all来代替or,集合操作时,各个查询的结果结构一定要相同
    select * from emp where job='clerk'
    union
    select * from emp where job= 'salesman';

    minus(差集) 返回若干个查询结果中的不同部分
    intersect(交集)返回若干个查询结果中的相同部分


    分组统计查询:
    1、统计函数
    掌握标准统计函数的使用:
    COUNT(*|distinct 列)求出全部的记录数
    count中的参数可以使用*也可以使用字段和dinstinct
    select count(*),count(empno) from emp; empno没有null值,结果一样
    select count(*),count(mgr) from emp;15,14
    范例:count(*)、count(字段)、count(dinstinct)有什么区别
    1、全部统计
    2、不统计null值
    3、不统计重复值
    尽量不使用*,所有函数在没有数据的时候都是返回null;但是count在没有数据的时候返回0,所以在java中是不需要对结果进行判断的


    SUM()求和
    AVG()平均值
    MAX()最大值
    MIN()最小值
    median()中间值
    STDDEV()标准差

    范例统计处公司最早雇佣的和最晚雇佣的
    雇佣日期使用的是date类型,但是在Oracle中的函数是可以进行数据类型的互相转换的,最早雇佣的hiredate值一定是最小的
    select min(hiredate) zuizao,max(hiredate) zuiwan from emp;

    单字段分组查询
    掌握group by的使用
    需求一:公司中要求每个部门一组进行拔河比赛
    需要部门列的内容需要重复
    select * from emp
    job和deptno有重复内容,最好对有重复内容的列进行分组
    需求二:在一个班级中要求男女各一组进行辩论比赛
    语法:
    select 分组字段|统计函数 from 表明 group by 分组字段
    分组使用group by子句时,但是此时SELECT子句允许出现的就是分组字段和统计函数***
    范例:统计处每个部门的人数
    select deptno ,count(empno)
    from emp
    group by deptno;
    范例:统计出每种职位的最低和最高工资
    select job,min(sal),max(sal)
    from emp
    group by job;
    掌握分组查询的使用限制(最为麻烦的地方为此处的限制)
    注意事项一:
    如果一个查询之中不存在group by 子句,select子句中只允许出现统计函数,其他任何字段都不允许出现
    select deptno ,count(*) from emp; 提示”不是单组分组函数“错误
    注意事项二:
    在统计查询之中(存在group by子句) select子句中只允许出现分组字段(group by后面的字段)和统计函数其他任何字段都不允许出现
    注意事项三:
    所有的统计函数允许嵌套使用,但是一旦使用了嵌套的统计函数之后,select字句中不允许出现任何字段,包括分组字段
    范例:
    求出每个部门平均工资最高的工资
    按照部门分组,而后统计处每个部门的平均数值,那么针对这些统计结果求出一个最大值
    范例:
    查询每个部门的名称、部门人数、平均工资,平均服务年限
    1、确定所需要的数据表
    2、确定已知的字段关联


    字句执行顺序
    from where group by select order by
    范例:查询出公司各个工资等级雇员的数量和平均工资
    1、确定所需要的表
    2、确定关联字段
    范例:统计处领取佣金和不领取佣金雇员的平均工资、平均服务年限、雇员人数
    1、
    2、
    多字段分组:
    既然可以在group by子句中出现多个分组字段,那么在select子句中也可以出现多个字段
    范例:要求查询出每个部门的详细信息
    包含字段:部门编号、名称、位置、平均工资、总工资、最高工资、最低工资、部门人数。(使用多字段分组)
    1、确定已知的数据表
    2、确定关联字段
    having子句
    掌握having字句的使用
    当需要对group by分组之后的数据再进行过滤,则只能通过having子句完成
    注意:having子句必须和group by子句一起使用
    查询出所有平均工资大于2000的职位信息、平均工资和雇员人数
    select job,round(avg(sal)),count(empno)
    from emp
    group by job having avg(sal)>2000
    语句的执行顺序 from、where、group by, having, order by
    范例:
    列出至少有一个员工的所有部门编号,名称,并统计出这些部门的平均工资、最低工资、最高工资
    1、确定所需要的数据表
    2、确定已知的关联字段:
    子查询
    子查询中的语法格式并没有任何新的技术,类似于java的内部类,而且在开发之中,子查询的使用绝对是比较多的
    复杂查询=限定查询+多表查询+统计查询+子查询,在笔试之中出现较多的部分。
    范例:查询公司之中工资最低的雇员的完整信息
    select * from emp where sal=(select min(sal) from emp);
    根据返回的数据类型一共分为四种:
    单行单列
    单行多列
    多行多列
    多行单列
    多行多列
    子查询出现的地方:
    1、where
    单行单列
    范例:查询出基本工资比allen工资低的员工

    范例:查询出基本工资高于公司平均工资的雇员

    范例:查找出于ALLEN工作相同,并且接你工资高于雇员编号7521的全部雇员信息
    范例:查询出与SCOTT从事同一工作并且工资相同的雇员(返回单行两列)
    select * from emp where (job,sal)=(select job,sal from emp where ename='SCOTT');
    select * from emp where (job,sal)=(select job j,sal s from emp where ename='SCOTT'); 列不对应
    范例:查询出与7566工作相同并且领导相同的雇员
    范例:查询出于ALLEN同一工作并且同一年雇佣的雇员信息
    多行单列:
    如果子查询返回的是多行单列,主要使用三种操作符:in,any,all,not in
    范例:查询出与每个部门最低工资相同的全部雇员信息
    范例:查询出不与每个部门中最低工资相同的全部雇员信息
    注意:如果在in中子查询的结果又in,如果在not in中子查询返回数据有null就表示不会有任何数据返回
    any操作符
    =any:功能和in相同,但是<>any不等价于not in;
    >any比最大值要大
    <any比最小值要小
    范例:
    all操作符
    空数据判断
    exists用于判断是否有数据返回
    select * from emp where exists(select * from emp where empno=9999);子查询没有内容,不返回
    select * from emp where exists(select * from emp); 有结果返回,数据会全部返回
    2、having,一定表示操作会执行分组
    在having中的子查询一般会返回单行单列,是以一个数值的方式返回
    范例:查询部门编号、雇员人数、平均工资,并且要求部门平均工资高于公司的平均工资
    范例:查询每个部门平均工资最高的部门名称以及平均工资(在统计函数嵌套使用时select字句中不允许出现任何字段,包括分组字段)
    3、from 主要功能是确定数据的来源,来源都是数据表(行+列的集合),所以一般都是多行多列子查询
    范例:查询出每个部门的编号、名称、位置、部门人数、平均工资(可以使用多表查询和子查询两种方法)
    使用子查询来代替多表查询来避免笛卡儿积,所以优先使用子查询
    范例:查询出所有在部门‘sales’工作的员工编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资
    1、确定所需要的数据表

    对于统计函数的使用限制:
    单独使用:不允许出现任何字段
    和group by一起使用:允许出现分组字段
    范例:查询出所有的新近高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期、所在部门名称、位置、上级领导姓名、公司的等级、部门人数、平均工资、平均服务年限。
    1、确定所需要的数据表
    2、确定已知的关联字段
    范例:列出公司各个部门的经理姓名、薪金、部门名称、部门人数、部门的平均工资
    1、确定数据表
    2、确定关联字段
    4、select用的比较少
    范例:查询出部门编号,部门名称,部门人数,部门平均工资
    with子句
    可以使用with创建临时表查询
    范例:查询每个部门的编号,名称、位置、部门平均工资,人数(使用with)
    范例:查询每个部门工资最高的雇员编号、姓名、职位、雇佣日期、工资、部门编号、部门名称,最终的显示结果按照部门编号排序

    分析函数:
    理解分析函数的主要语法:
    理解分窗的使用
    删除语法:
    delect from 表明 where
    更新语法:
    update 表明 set a=b where
    【事务处理】
    指同一个session中的所有sql语句整体执行
    服务器通过session来区分不同的用户,每一个session对应一个用户
    原子性、一致性、隔离性和持久性
    session---缓存
    更新操作要commit之后才会生效
    ROLLBACK回滚,savepoint+保存点名称
    锁的基本概念
    锁指的是不同的session同时操作了同一资源发生的问题
    两个session执行同样的update操作语句:
    两种锁:
    行级锁:
    特点:当一个事务执行了相应的数据操作后如果此事务没有提交,那么会一直以独占的方式锁定这些操作的数据,其他事务要一直到此事务释放后才能进行操作
    表级锁:

  • 相关阅读:
    跳码与一机多终端
    SCRUM REPORT DIRECTORY
    ASE Backend Review
    ASE Beta Sprint
    ASE Beta Sprint
    ASE Backend Alpha Sprint Review
    ASE Alpha Sprint
    ASE Alpha Sprint
    ASE Alpha Sprint
    ASE Alpha Sprint
  • 原文地址:https://www.cnblogs.com/vitasyuan/p/5808844.html
Copyright © 2020-2023  润新知