`
afunti
  • 浏览: 105768 次
  • 性别: Icon_minigender_1
  • 来自: 安徽
社区版块
存档分类
最新评论

sql

阅读更多


DML(Data Manipulation Language):insert,delete,update,select

DCL(Data Control Language):grant、revoke、audit、noaudit、rename

DDL( Data Definition Language):create、alter、drop 、


1.解锁用户hr

用sys用户登录输入:

    alter user hr identified by pswd account unlock;

    alter user scott account unlock;(解锁)

2.

(1).group by   对查询结果按指定列值对记录进行分组,即该列值相等的记录为一个组,通常会在每组中使用分组函数进行汇总。统计

(2). having   在分组之后的结果集中筛选满足指定条件的组  必须与group by组合使用。

    Group function 组函数

        max,min,avg ,count,sum函数


  分组函数注意事项:

    1>.分组函数只能出现在所查询的列、order by子句、having子句中,不能出现在where子句和group by子句中

    2>.除了count(*)之外,其他分组函数,包括count(column_name),都会忽略用于分组的列的值为null的行

               count  返回记录行的行数

               select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个. 

    3>.如果所查询的列同时包含列、表达式、分组函数,那么这些列、表达式都必须出现在group by子句中。

    4>.在分组函数中可以指定all和distinct选项。其中,all是默认选项,表示该函数作用于所有的行(包括值重复的行)而distinct则只作用于不同的行。如count(distinct country_id)

       select count(distinct deptno) from emp;--统计共有多少种deptno

    5>.having子句只与组有关,where子句只与记录行有关,在having子句的后面只能使用分组函数本身,而不能使用列的别名限制分组结果时,必须使用having子句 ,而不能在where子句中使用分组函数来限制分组结果(即where子句中不能使用分组函数 ),因为where子句是在进行分组处理之前过滤数据行,而having子句则是在分组处理之后才过滤数据组。

    6>. 求薪水值最高的人的名字.

       select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.

       应如下求:

       select ename from emp where sal=(select max(sal) from emp);

       Group by语句应注意,

       出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.

     7>. 组函数最多嵌套两层


(3) select 语句:

       1>.计算数据可以用空表:比如:.select 2*3 from dual

       2>.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别,加双引号保持原大小写。不加全变大写。在sql语句中加"  "可以保持你输入的格式不变


       3>. select ename || "abcd" 如果连接字符串中含有单引号,用两个单引号代替一个单引号。


 字符串连接:

 a.select region_id ||'是指'||region_name as 地区编码的意思 from regions  

 b. select ename||'sss' from emp;

   ||  连接字符串  ''中存放字符串  如果要想显示带有单引号的字段 ,可以这么写:

   select ename||'s''ss' from emp;

   

4>.  select ename from emp where ename like '%$%%' escape '$';

--选出ename中含有%的数据  $是自定义的转义字符 默认的是 \

5>.select ename, sal, hiredate from dmp where hiredate > '20-2月-81';

        --入职年份在81年2月20日之后的数据

select...from..where ...group by..having...order by...(执行的顺序:先取数据,取完数据进行过滤(where),过滤完了进行分组(group by),分组完了再对分组后的结果进行过滤(having),最后的结果进行排序(order by))

(4)distinct

        select deptno from emp;

        select distinct deptno from emp;

        select distinct deptno from emp;

        select distinct deptno ,job from emp

        去掉deptno,job两者组合的重复。更多的项,就是这么多项的组合的不重复组合。

(5)sql 函数:

单行函数:

select ename from emp where lower(ename) like '_a%';

--先将ename转化为小写,然后再选择其中第二个字母是a的数据  等价于:

--select ename from emp where ename like '_a%' or '_A%';

select substr(ename, 2, 3) from emp;

--截取ename所有的元素从第二个字母开始长度为3的字符串

select chr(65) from dual;

--chr()将ascII码转化为相应的字母

select ascii('A') from dual;

-- ascii() 将字母转化为相应的ascii码

select round(34.334) from dual;

--round() 读数据四舍五入(结果显示34)

select round(34.334,2) from dual;

--四舍五入到小数的第二位(显示结果为34.33)

round(34.334,-1) 

--显示结果为30

 select round(avg(sal),2) from emp;

           --结果:2073.21

转化数字的显示格式

select to_char(sal,'$99,999.999') from emp;

select to_char(avg(sal),'99999999,99') from emp;

--to_char() 将显示的结果显示为指定的格式 ,'$99,999.999'其中9代表一位数字 结果显示中如果相应位没有数字就不显示,有就显示,‘,’代表千位符 ‘.’ 代表小数点{显示结果举例:$1,600.000} 这种函数在对齐数据时非常有用

select to_char(sal,'L99,999.999') from emp;

--  结果格式为¥1,600.000   (L:代表本地符号)

select to_char(sal,'L00,000.000') from emp;

--将结果中相应位没有的数字补0 如¥01,600.0000

<对数字的转化非常的少用,因为这可以在java中处理>



转化日期的显示格式:

select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;

--12小时格式

select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp;

--小时数是24进制显示

select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:45','YYYY-MM-DD HH:MI:SS');

--to_date(b,a)将b按照a的格式进行解析 即上句中将1981-2-20 12:34:45按照YYYY-MM-DD HH:MI:SS这种格式进行解析

select sal from emp where sal > to_number('$1,200.000','$9,999,999');

-- to_number(b,a) 将特定的字符串转换为相应格式的数字

处理空值:

select ename, sal*12+nvl(cumm,0) f rom emp;

--nvl(a,0)  如果a的值是空则用0代替它,否则用a的值进行计算

(6).Oracle表连接和子查询练习:

--求部门中哪些人的薪水最高:

select ename, sal from emp 

join (select max(sal) max_sal, deptno from emp group by deptno) t 

on(emp.sal =t.max_sal and emp.deptno = t.deptno);

--求部门平均薪水的等级:

select deptno, avg_sal, grade from

(select deptno, avg(sal) avg_sal from emp group by deptno) t

join salgrade s on (t.avg_sal between s.losal and s.hisal);

--求部门平均的薪水等级:

select deptno, avg(grade) from 

(select deptno, grade, sal from emp e join salgrade s on (e.sal between s.losal and hisal )) t 

group by deptno ;

--雇员中哪些是经理人:

select ename,job, mgr from emp where empno in (select distinct mgr from emp);

--不用组函数求薪水的最高值:(用自连接)

select sal from emp where sal not in ( select e1.sal from emp e1,emp e2 where e1.sal < e2.sal)

老师写的:select distinct sal from emp where sal not in ( select distinct e1.sal from emp e1 join emp e2 on ( e1.sal < e2.sal))

--求平均薪水最高的部门编号:

select deptno, avg_sal from

(select avg(sal) avg_sal, deptno from emp group by deptno)

where avg_sal=

(select max(avg_sal) max_val from

(select avg(sal) avg_sal, deptno from emp group by deptno))

-----------------------***********

组函数是可以嵌套的,但是最多嵌套两层

如上面的可以写成:

select deptno, avg_sal from

(select avg(sal) avg_sal, deptno from emp group by deptno)

where avg_sal=

(select max(avg(sal)) max_val from emp group by deptno)

----------------------------*********

--求平均薪水最高的部门名称:

select dname from dept d join

(

       (select avg(sal) avg_sal,deptno from emp group by deptno)

 where avg_sal =

      (select max(avg_sal) from

          (select avg(sal) avg_sal,deptno from emp group by deptno)

      )

) a on(d.deptno = a.deptno)

另一种写法:

select dname from dept where deptno=

(

 select deptno from

       (select avg(sal) avg_sal,deptno from emp group by deptno)

 where avg_sal =

      (select max(avg_sal) from

          (select avg(sal) avg_sal,deptno from emp group by deptno)

      )

)

--求平均薪水等级最低的部门名称:

select dname from dept where deptno =

(select deptno from 

 (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

where grade =

       (select min(grade) from

          (select avg(sal) avg_sal,deptno from emp group by deptno) t

join salgrade s on(t.avg_sal between s.losal and hisal)

       )

)

老师:

select dname,t1.deptno, grade avg_sal from 

 (

  select deptno,grade,avg_sal from 

     (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

  ) t1 

join dept on(t1.deptno = dept.deptno)

where t1.grade =

     (

         select min(grade) from

           (select avg(sal) avg_sal,deptno from emp group by deptno) t

          join salgrade s on(t.avg_sal between s.losal and hisal)  

         ) 

select dname,t1.deptno, grade avg_sal from 

(

  select deptno,grade,avg_sal from 

     (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

  ) t1 

join dept on(t1.deptno = dept.deptno)

where t1.grade =

     ( 

         select min(grade) from

           (

  select deptno,grade,avg_sal from 

     (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

  )

       )

       

       

多列子查询:

 查询公司中与employee_id=198 的雇员的工资、岗位都相同的员工的信息:

 select first_name,last_name ,salary,job_id

from employees 

where (salary,job_id) = (select salary,job_id 

                         from employees

                         where employee_id = 198)

                         

查询每个部门中最高工资的雇员信息:

select distinct department_id,

     (select max(salary) from employees b

       where b.department_id=a.department_id)  max_salary 

from employees a

order by department_id     

等价于:

select department_id,job_id,salary

           from employees a

           where salary=(select max(salary) from employees b

                          where b.department_id = a.department_id)

           order by department_id                     

                         

--求比普通员工的最高薪水还要高的经理人的名称:

select ename form emp

where empno in (select distinct mgr from emp where mgr is not null)

and

sal>

  select max(sal) from emp whereempno not in 

     (select distinct mgr from emp where mgr is not null)

)


3.表的连接:

相等连接往往是通过主键、外键关系建立的,而不相等连接时相当的随意的,只要在数据类型上可以比较即可。

(1).自连接 (给自己起一个别名,当做两张表的链接):

     select e1.ename, e2.ename from emp e1,emp e2 where e.mgr = e2.empno;

(2).等值链接

select ename, dname from emp join dept on( emp.deptno = dept.deptno)

--等价于 select ename, dname from emp ,dept where emp.deptno = dept.deptno;(旧的语法)      新的语法通常在where中只写过滤条件

(3).非等值链接

slect ename,dname, grade from

     emp e join dept d on(e.deptno=d.deptno)

     join salgrade s on(e.sal between s.losal and s.hisal)

     where ename not like '_A%';

把每张表连接 条件不混在一起,然后数据过滤条件全部区分开来。读起来更清晰,更容易懂一点。

(4).外连接:

a.左外连接:会把左边这张表多余数据显示出来。

     select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer

     

b.右外连接:

   select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。

      

c.全外连接:既把左边多余数据,也把右边多余数据拿出来。

     select ename,dname from emp e full join dept d on(e.deptno =d.deptno);

     

      d.using 简化外连接:

select e.first_name,d.department_name from employees e

inner join departments d

using(department_id)

where e.employee_id=177

 注意:如果希望在所查询的列中包括用于连接的列,则只能使用该列的列名,如上例连接的department_id,在查询中只能用department_id 而不能用d.department_id;

     在using子句中的连接列前面也不能使用表明限定词即:不能用using(d.department_id)代替using(department_id)

4.集合查询:

 语法:

 select 语句1 

 [union all  | union  | minus  | intersect]

 select 语句2

 order by column_name

 union all: 返回两个结果集的所有的行,包括重复行 即并

 union    : 返回两个结果集的所有行, 不包括重复行 即并

 minus    : 返回第一个结果集中有但是第二个结果集中没有的行,即差

 intersect: 返回两个结果集中都有的行,即 交

 注意:a.两个结果集的列的名称可以不同,最后结果集中的列名采用的是第一个结果集中的列名称

       b.只能有一个order by子句,且只能出现在最后一个select 语句的最后面。 order by子句中只能使用第一个select 语句所查询列的列名或者别名,如果列名cc.与第二个select 语句的列名重复,则必须使用别名或列的次序号

       d.查询嵌套最多嵌套255层   

5.子查询:

(1).create table中使用子查询:

   create table

   emp(emp_id,f_name,h_date,sal,dept_id)

   as

   select employee_id,first_name,hire_date,salary,department_id

   from employees

   where department_id in (90,110)

   order by department_id,employee_id;


(2).create view 中使用子查询

首先 创建一个视图(create view V$(再加上你的视图名称)){权限不足时可这么做 conn sys/tiger as sysdba; 

grant create table,create view to scott}

 

create  or replace view v$dept_avg_sal_info as

select deptno,grade,avg_sal from 

     (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

select dname,t1.deptno, grade avg_sal from 

v$dept_avg_sal_info t1 

join dept on(t1.deptno = dept.deptno)

where t1.grade =

     ( 

         select min(grade) from

           v$dept_avg_sal_info

       )

       

(3).insert语句中使用子查询:

    insert into emp

    select employee_id,first_name,hire_date,salary,department_id

    from employees

    where department_id =20;

    如果要针对某些列插入数据,其完整语法如下:(一定要有主键列和not null列)

     insert into emp(employee_id,first_name,hire_date,salary,department_id)

    select employee_id,first_name,hire_date,salary,department_id

    from employees

    where department_id =20;


(4).delete语句中使用子查询:

    delete from emp

    where sal>(select avg(max_salary) from jobs);

(5).update语句中使用子查询:

    update emp

    set (h_date,sal)=

    (select h_date,sal from emp where emp_id = 205)

    where dept_id =20;

6.insert

   insert into table [column1,.....]  values ( value1,....)  

   如果数字为插入数据,可以直接提供数字数据;如果为字符列或者日期插入数据,则必须使用单引号。如果字符串中本来就有单引号,则要在其前面再加一个单引号如:“亚洲’asia”写成“亚洲‘’asia”。日期要符合默认的日期格式,否则用to_date函数进行格式转换

7.update

  update tablename

  set column1 = value1[,column2 = value2]

  [where condition]

  如果数字为更新数据,可以直接提供数字数据;如果为字符列或者日期更新数据,则必须使用单引号。如果字符串中本来就有单引号,则要在其前面再加一个单引号。

  日期要符合默认的日期格式,否则用to_date函数进行格式转换  

8.delete

  delete from tablename

  [where condition]

9.事务

  事务是用户定义的一组操作序列,由一条或多条相关的sql语句组成,是数据库应用程序的基本逻辑单位。一般来讲一个应用程序是由一个或多个事务组成的。事实上,事务中的sql语句仅限制于dml命令,而ddl和dcl语句是不能被回退的(即事务对其不起作用,他们会自动提交事务)  

 1>.commit   提交

 2>.rollback  回滚

 3>.savepoint 设置保存点

 4>.rollback to savepoint spname/rollback to spname   回滚到保存点

 5>.set transaction  设置事务的属性

                (1).set transaction read write

                用于设置可读写的事务。在这种事务中可以执行dml语句来更新数据。这是事务的默认设置。

                (2).set transaction read only

                用于设置只读事务,在只读事务中不能用dml语句来更新数据。可以将数据库“冻结”到该事务开始的那一点上,即查询到的是已经存在于数据库中的数据,即便在此同时其他事务更改并提交了数据库中的数据。

                (3).set transaction isolation level read committed

                用于设置“读已经提交事务”的隔离等级。在此隔离等级(默认的隔离等级)下,事务中每个dml语句所操作的数据,是在该语句开始之前已提交了的数据,它提供语句级的读一致性。

                (4).set transaction isolation level serializable

                用于设置"串行化事务”的隔离等级。在serializable隔离等级下,事务中每个dml语句所操作的数据,是在该事务开始之前已提交了的数据,并且可以执行dml语句来更新数据库中的数据,还可以查看到更新的结果。提供事务级的读一致性。

 6>.set constraints 设置可延迟约束的检验时机

 set constraints {constraints_name1[,constraints_name2].....|  all}

 {deferred | immediate};

 all 表示该事务所涉及的所有可以延迟的约束;immediate 表示在媒体dml语句之后就立即检验;deferred(默认选项)表示在提交事务的时候才检验。

 在创建表的时候就可以创建约束并指定是否可以用set constraints 语句进行延迟检验

 如:

  create table test (

 a   number not null,

 constraint pk_a primary key(a) validate,

 constraint ck_a check(a>0) deferrable initially immediate);

7>.事务的acid属性(atomicity[原子性]、consistency[一致性]、isolation[隔离性]、durability[持久性])

8>.在oracle中通过执行insert、update、delete语句修改数据时,oracle都会生成两种重要信息:undo和redo

  在数据库由于系统故障或错误而在事务执行期间崩溃之后,再次启动数据库时:smon后台进程就会根据undo信息(位于undo表空间中的数据文件)、redo信息(位于重做日志文件)负责对数据库进行恢复。

  在处理commit语句时,oracle会静重做日志缓冲区(在内存中)中的内容用lgwr后台进程写入重做日志文件(在外存或磁盘中)。

分享到:
评论

相关推荐

    通过SqlCmd执行超大SQL文件

    ##通过sqlcmd执行sql文件 由于sql文件过大,超过了100M,再数据库的窗口执行,结果超出内存了,对于特别大的sql文件可以使用sqlcmd进行执行 ###1.打开cmd窗口 运行–cmd–进入到sql文件所在的文件夹。 如果是win7可...

    java sql操作工具类 java sql操作工具类

    java sql操作工具类 java sql操作工具类java sql操作工具类 java sql操作工具类java sql操作工具类 java sql操作工具类java sql操作工具类 java sql操作工具类java sql操作工具类 java sql操作工具类java sql操作...

    SQLServer_2000-2008_R2查询智能分析器RedGate_SQL_Prompt_V5.3.4.1_Crack_Keygen破解教程注册机免费

    在我个人编写SQL脚本时,至少会把SQL的格式排列成易于阅读的,因为其他人会阅读到你的SQL,无论是在程序中或是脚本文件中,良好的排版不仅让人看起来赏心悦目,在和他人之间做交流时也省时省力,不会因为揉成一团的...

    SQL优化 SQL优化软件 SQL优化工具

    SQL优化是数据库管理中的关键环节,它涉及到提升查询性能、减少资源消耗以及改善系统整体效率。SQL优化软件和工具能够帮助数据库管理员(DBA)和开发人员找出性能瓶颈,优化查询逻辑,从而提高数据库系统的响应速度...

    SQLPrompt5.3破解

    本人在Windows7 64位+SQL Server 2012环境下测试通过(系统是全新安装) 使用方法: 1,安装SQLPrompt v5.3,这个不多说。 2,安装完毕后,断开网络连接。 3,打开Visual Studio或者SQL Server Management Studio(版本...

    SQLPrompt for SQLServer2016 智能提示插件 SQL2016 提示

    SQLPrompt for SQLServer2016 智能提示插件 SQL2016 提示 SQLPrompt最新版本 绿色版 SQL Prompt 是一款拥有SQL智能提示功能的SQL Server和VS插件。SQL Prompt能根据数据库的对象名称,语法和用户编写的代码片段自动...

    sqlserver自动生成sql语句工具sqlserver转oracle

    在IT行业中,数据库管理系统是核心组成部分,SQL Server和Oracle分别是微软和甲骨文公司推出的两款广泛应用的关系型数据库系统。在企业级应用中,有时需要在不同的数据库系统间进行数据迁移或兼容性处理,这就涉及到...

    sql server 导入超大SQL脚本文件

    SQL Server 导入超大 SQL 脚本文件 SQL Server 是一种关系型数据库管理系统,广泛应用于各种行业。然而,在实际应用中,我们经常会遇到导入超大 SQL 脚本文件的问题。本文将介绍如何使用 osql 工具来导入超大 SQL ...

    Android通过webservice连接Sqlserver实例

    在Android开发中,有时我们需要与远程数据库进行交互,例如SQLServer。这个场景通常是通过Web服务,如WebService来实现。本文将详细介绍如何在Android应用中利用WebService接口连接到SQLServer数据库,实现数据的增...

    SQL SQLPrompt 9 SQL 2016/2017可用

    SQL Prompt是Redgate Software开发的一款高效SQL代码编辑工具,它为SQL Server的开发人员提供了智能提示、格式化、重构和代码分析等功能,极大地提升了编写和维护SQL代码的效率。SQL Prompt 9是该系列的最新版本,...

    SQL 语法 SQL 总结 SQL教程

    SQL 基础 SQL 首页 SQL 简介 SQL 语法 SQL select SQL distinct SQL where SQL AND & OR SQL Order By SQL insert SQL update SQL delete SQL 高级 SQL Top SQL Like SQL 通配符 SQL In SQL Between ...

    SQLMonitor oracle跟踪SQL工具

    《SQLMonitor:Oracle数据库SQL跟踪与分析利器》 在IT行业中,数据库的高效管理与优化是至关重要的。针对Oracle数据库,有一款名为SQLMonitor的工具,它专为跟踪和监控SQL语句而设计,帮助开发者和DBA们找出程序...

    Sql Server 2014 安装包

    Sql Server 2014 安装包 SQL Server 2014是微软推出的一款关系型数据库管理系统,它在企业级数据管理和分析领域扮演着重要的角色。此安装包包含两个主要组件:SQL Management Studio和SQL Server Express。 1. **...

    AI自动生成SQL语句的开源代码 sqlcoder-main.zip

    开源的AI自动生成SQL语句源代码,这款SQLCoder-70B-Alpha在文本到SQL的转换能力上超越了包括GPT-4在内的所有通用模型,它能更准确地理解你的需求,并生成相应的SQL查询。SQLCoder2和SQLCoder-7B模型已经向公众开放,...

    sqlserver驱动包 jdbc驱动 sqljdbc.jar和sqljdbc4.jar

    SQL Server驱动包是用于Java应用程序通过JDBC(Java Database Connectivity)接口与Microsoft SQL Server数据库进行交互的必备组件。本文将详细介绍这两个重要的驱动文件——sqljdbc.jar和sqljdbc4.jar,以及如何...

    sqlserver驱动包:sqljdbc4.jar

    SQL Server驱动包`sqljdbc4.jar`是微软官方提供的Java数据库连接器(JDBC),用于在Java应用程序中与Microsoft SQL Server进行通信。JDBC是Java编程语言中的一个标准API,它使得开发人员能够以标准化的方式访问各种...

    sqlservr32和sqlservr64.zip

    标题中的"sqlservr32和sqlservr64.zip"指的是SQL Server 2005服务中的两个关键组件,`sqlservr32.exe`和`sqlservr64.exe`。这两个文件是SQL Server服务的核心执行文件,分别对应于32位和64位操作系统。在Windows 8和...

    SQLTracker,抓取sql语句的工具

    SQLTracker是一款专为数据库操作监控设计的工具,它在IT领域中主要用于跟踪和记录SQL语句的执行情况。SQL(Structured Query Language)是用于管理关系数据库的编程语言,包括查询、更新、插入和删除数据等操作。SQL...

    oracle sqldeveloper连接mysql、SQLServer第三方dll

    解决oracle sqldeveloper无法连接mysql、SQLServer问题,sqlDeveloper是ORACLE数据库开发工具,自带的是无法连接MS SQL Server以及mysql的,想连接的话需要第三方工具。 使用方法: 解压出来后将2个jar放入jlib...

    Oracle Sql语句转换成Mysql Sql语句

    在数据库管理领域,Oracle SQL和MySQL SQL是两种广泛使用的SQL方言,它们在语法和功能上存在一定的差异。当需要将一个基于Oracle SQL的应用程序迁移到MySQL环境时,就需要进行SQL语句的转换工作。本项目提供了一个...

Global site tag (gtag.js) - Google Analytics