SQL语言

SQL结构化查询语言(Structured Query Language) 《SQL必知必会》 什么叫数据库？ 数据库是指长期储存在计算机内的、有组织的、可共享的数据集合。 常用的数据库： ? Oracle ? My SQL ? SQL Server, Access ? DB2 SQL分类： ? 查询语句 ? 查询语句只有一种，即Select语句 ? DML语句（Data Manipulation Language） ? 包含Insert、Update、Delete等常用语句 ? DDL语句（Data Definition Language） ? 包含Create、Alter、Drop等常用语句 ? 事务控制语句（Transaction Control） ? 包含Commit、Rollback等常用语句 实验用数据库表： ? 查看所有表：select table_name from user_tables;  Select * from tabs;  mysql数据库中:需要选择一个数据库,然后才能查看所有的表:use 数据库名称  ;  show tables 显示所有的表名。 ? 查看表结构：describe dept;(或者desc dept;) desc 表名 查看表结构。 ? emp表雇员表 ? Empno: 雇员工号          Ename: 雇员名字 ? Job:工作。（秘书、销售、经理、分析员、保管） ? Mgr(manager):经理的工号      Hiredate:雇用日期 ? Sal:  工资      Comm: 津贴      Deptno: 所属部门号 ? dept表部门表 ? Deptno:部门号  Dname:部门名字  Loc: 地址 ? salgrade表薪水等级 ? Grade:等级    losal:最低工资  hisal:最高工资 ? bonus表奖金 ? Ename:雇员名字，  job:工作，  ? sal: 工资  comm:津贴 ? ORACLE常用数据类型 数据类型 含义 Varchar2(n) 变长字符串，存储空间等与实际空间的数据大小，最大为4K Char(n) 定长字符串，存储空间大小固定 Number(p,s) 有效位数或小数 Date 年、月、日、时、分、秒 Long 变长字符串，最大字节数达到2GB     Select语句： ? 检索单个列 ? select  col  from tableName; ? 检索多个列 ? select  col1, col2,col3  from tableName; ? 检索所有列 ? select  *  from tableName; ? 使用通配符的优点：书写方便、可以检索未知列 ? 使用通配符的缺点：降低检索的性能 ? 给检索出的列起个别名 ? select job  "gong zuo"  from emp; ? select job  as  "gong zuo"  from emp; 注意： Sql语句和大小写： Sql关键字不区分大小写， 但是表名、列名以及值可能不同。这依赖于数据库的设置。 现在的oracle和mysql中，表名和列名是不区分大小写的。但是表中的值是区分大小写的。   Oracle中单引号和双引号的区别： 双引号一般是用来起别名的，如果alias里面有空格或其它保留符号，必须使用双引号。而单引号是用来特制的，比如字符串的引用，日期字符串的引用，都必须包括在单引号中，可以参与运算或其它表达式中。两者不可混用，其中，两个单引号可以作为单引号的转义使用，意思就是一个真正的、没有特殊功能的单引号。例如select substr(‘I”am a pig’,1,4)。 Mysql中单引号和双引号区别不大。   select练习1： --每个员工的所有信息 select * from emp; --每个人的部门编号，姓名，薪水 select empno, ename, sal from emp; --每个人的年薪 select ename, sal*12 from emp; --计算2*3的值 select 2*3 from emp; --计算2*3的值(dual) select 2*3 from dual; --得到当前时间 select sysdate from dual; --可以给列起别名,比如求每个人的年薪 select ename, sal*12 annual_sal from emp; --如果别名中有空格，需要用双引号 select ename, sal*12 "annual sal" from emp; --如果没有内容，则为空 select ename, sal, comm from emp; /*null*/ --当空字段参与计算，则结果是null --例如：计算每个人的全年的收入包括月薪和年终奖 select ename, sal * 12 + comm from emp; select ename, sal * 12 + nvl(comm,0) from emp; --可以将多个字符串拼在一起。比如：求每个人的薪水，格式为smith-sal-123 select ename || '-' || sal || '-' || comm from emp; --如果字符串中有单引号，需要用另外一个单引号转义，比如：这样一个字符串： he's friend select ename || 'he''s friend' from emp;   排序检索出的数据 按照sql规范，如果不显式指定数据排序，则认为数据排序时无意义的！ ? 按照单个列排序 ? order  by col 按照所得收入降序排列，所得收入：sal+comm ● select * from emp order by sal+comm desc;（补充） select * from emp order by sal desc； select ename,sal+nvl(comm,0) from emp order by nvl(comm,0) desc; ? 降序和升序（默认是升序）： ? order  by col desc (asc) ? 按多个列排序 ? order by  col1 desc(asc),  col2  desc(asc) 按部门号升序排列，同一个部门的话，按照薪水降序排列。 select deptno,ename,sal from emp order by deptno,sal desc; select * from emp order by sal desc, comm desc; 过滤数据（where子句） 基本运算符： = 等于 <> 不等于 != 不等于 < 小于 <= 小于等于 > 大于 >= 大于等于 Between…and … 在指定的两个值之间(包含这两个值) Is null (is not null) 为null值     基本练习： --可以是数值类型的等值判断。比如：求10这个部门的所有员工 select * from emp where deptno = 10; --可以是字符串类型的等值判断。比如：求叫KING的这个人的信息 select * from emp where ename = 'KING'; --也可以是不等值判断。比如：求薪水大于2000的员工信息 select * from emp where sal > 2000; --字符串也可以做不等值判断，比如：求所有ename大于'CBA'的员工信息。 select ename from emp where ename > 'CBA'; --求部门不是10的部门 select * from emp where deptno <> 10; --求薪水在800和1500之间的员工信息 select * from emp where sal between 800 and 1500; --也可以写成 select * from emp where sal >= 800 and sal <= 1500; /*这样写则不可以 --select * from emp where 800 <= sal <= 1500; */ --求入职时间在20-2月-81之后的员工信息 select ename, hiredate from emp where hiredate > '20-2月-81';   ? 组合where子句 ? AND操作符 ? OR操作符 ? 计算次序问题的解决，最好用括号进行分组处理 列出deptno为10或者30，并且工资>2000的所有人。 select * from Emp where deptno=30 or deptno=10 and sal>2000; 这个命令列出的人中薪水有<2000的，为什么？因为，sql在处理or和and的时候，优先处理and。所以上面的这句话的意思是： select * from Emp where deptno=30 or (deptno=10 and sal>2000); 列出所有deptno=30的人。或者deptno=10并且sal>2000的人。   ? SQL优化问题： ? AND:  把检索结果较少的条件放到前面 ? OR:  把检索结果较多的条件放到前面 ? In操作符优势 ? 语法清晰直观、更容易管理 ? In操作符一般比or操作符清单执行更快 ? 可以包含其他select语句，实现子查询。 select * from emp where deptno=10 or deptno=20 or deptno=30; select * from emp where deptno in(10,20,30); select * from emp where deptno not in(10,30); select ename, empno , sal from emp where ename in ('KING', 'SMITH', 'AA');   ? NOT操作符 ? 用于否定后面所跟的条件 ? not  (条件) ? not  in (…) ? is not null oracle和mysql现在都支持。挺好。 select * from Emp where not (deptno=10 or deptno=20); select * from Emp where not ( deptno in(10,20)); select * from Emp where deptno not in(10,20); select * from Emp where comm is not null; select * from Emp where not (comm is null);   ? LIKE操作符 ? %通配符表示任意字符出现任意次数 ? _通配符表示任意字符出现一次 select * from emp where ename like ‘%H%’; select * from emp where ename like ‘M%’;//以M开头的， select * from emp where ename like ‘m%’; //什么都搜不到，是区分大小写的。 --特殊字符需要转义。比如：求员工中包含特殊字符%的员工信息 select ename from emp where ename like '%\%%' escape '\';   ? 技巧和注意事项： ? 不能过度使用通配符。如果其他操作符能达到目的，就不要使用通配符。 ? 确实需要使用通配符时，除非绝对必要，否则不要把通配符用到搜索模式最开始处，因为这样搜索起来是最慢的。 ? Oracle 10g新特性：正则表达式的使用(了解) select * from emp where  regexp_like(ename,'[SM].'); 创建计算字段 ? 为什么需要计算字段？ 存储在数据库表中的数据一般不是应用程序所需要的格式。我们经常需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序中重新格式化。  这就是计算字段发挥作用的所在。 ? 计算字段并不实际存在于数据库表中，也不会修改数据库表中的信息。 ? 拼接字段(||) ? 首选|| (mysql中||表示or，一般用concat() ) ? 使用别名 ? 使用AS,或者空格隔开。有别名后，可以在其他地方引用。 ? 执行算术计算 select sal+comm as shouru from emp ;  处理含有null的字段： 任何含有null值的数学表达式最后的结果都为空值 必须将null值转化成0: nvl(comm,0) select sal+nvl(comm,10000) shouru from emp ; 任何含有null值的字符串表达式，null被当作空字符串来处理。 不同的数据库产品对于null值的处理都有不同的规则，当我们的数据中含有null值时往往会增加程序的处理难度。同时如果我们想让自己的某些sql语句可以扩月多个数据库产品时，null值的处理往往会给我们添加很多障碍。因此：建议在实际当中开发数据库应用系统的时候不要使用空值。   数据处理函数 函数一般是在数据上执行的，他给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。 每一个数据库都有特定的函数，比如：取当前日期，access,now(), db2用current_date, mysql用curdate(), oracle用sysdate; sql server和sybase用getdate(). select sysdate from dual;----oracle select current_time() from dual;---- mysql:时间。 select current_date() form dual;  ---mysql；日期 select current_timestamp() from dual;---mysql：日期时间 数据处理函数差异造成的数据库移植问题： SQL虽然有 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 规范，但是各个数据库产品都有自己的数据处理函数，这就造成了很麻烦的一个问题。如果你的代码要移植到其他数据库。Sql可以移植，没有问题。但是sql函数都是不同的，所以会给代码的移植造成很大的困难。 ? 文本处理函数 ? Lower(), upper(), substr(), length(),  trim(),ltrim(),rtrim(), ? to_date(),  to_number() ?   initcap()使串中的所有单词的首字母变为大写   substr()取子串   select ename, lower(ename) from emp; select ename, lower(ename) “dddd” from emp; select ename, upper(lower(ename)) from emp; select ename, substr(ename,1,3) from emp; select ename, substr(ename,2) from emp; select ename, length(ename) from emp;   ? 时间处理函数 ? to_char(), sysdate(oracle) ? 数值处理函数 ? round() ,四舍五入 ? floor()下取整   ---19.9也变19 ? ceil(),上取整   ----19.1 变20 ? to_char() ? abs()取绝对值 ? 其他函数 ? nvl()用于处理空值 ? 组函数 ? avg(), max(), min(), sum(), count() to_char(), to_date(),  to_number()用法： 格式控制符 含义 备注 YYYY, YY 代表四位、两位数字和年份 用于to_char, to-date MM 代表用数字表示的月份 DD 数字表示的日 DY 星期(DAY的缩写) AM/PM am、pm均可表示上下午 HH24,HH12 12小时或24小时时间 MI 分钟数 SS 秒数 9 代表一位数字，如果该位没有数字则不进行显示。但对于小数点后面的部分仍会强制显示0 用于to_char, to_number 0 代表一位数字，如果该位没有数字则强制显示0 $ 显示美元符号 L 显示本地货币符号(中国是：￥) . 就是小数点 , 显示千分位       select to_date(‘2004-09-19’,’yyyy-mm-dd ‘) from dual; select to_date(‘04-9-19’,’yy-mm-dd ‘) from dual; select to_date(‘04,05,19’,’yy,mm,dd ‘) from dual; select to_date(‘04,05,19,10,23,40’,’yy,mm,dd,hh12,mi,ss ‘) from dual;  求某天是星期几： select to_char(to_date('2002-08-26','yyyy-mm-dd'),'dy') from dual; 两个日期间的天数  select floor(sysdate - to_date('20020405','yyyymmdd')) from dual; to_number(). 格式控制符 含义 9 代表一位数字，如果该位没有数字则不进行显示。但对于小数点后面的部分仍会强制显示0 0 代表一位数字，如果该位没有数字则强制显示0 $ 显示美元符号 L 显示本地货币符号(中国是：￥) . 就是小数点 , 显示千分位     select to_number(‘$39343.78’,’$99990.000’) from dual;  select to_number('12.3')  from dual; select to_number('11.23','000.000') from dual; 日期和时间处理函数： to_char(), sysdate select sysdate, to_char(sysdate,’yy,mm,dd’)from emp ;  select sysdate, to_char(sysdate,’yy%mm%dd’)from emp ;  select to_char(12.3,'$99999.0000') from dual; 基本练习： --把所有姓名变成小写 select lower(ename) from emp; --把所有姓名变成大写 select upper(ename) from emp; --求所有人名中包含'a'的员工信息不区分大小写 select ename from emp where lower(ename) like '%a%'; --截取子字符串，比如求Hello的一部分 select substr('Hello', 2) from dual; --求Hello的一部分，并指明长度 select substr('Hello', 2, 3) from dual; --求ascii码对应的字符 select chr(65) from dual; --求字符对应的ascii码 select ascii('中') from dual; --四舍五入 select round(23.652) from dual; --四舍五入小数点后面多少位 select round(23.652, 1) from dual; --四舍五入小数点前面多少位 select round(23.652, -1) from dual; -------------------------------------------------------- --important!日期转换函数 -------------------------------------------------------- --将当前日期转换成1981-03-12 12:00:00这种形式的字符串 select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; --将1981-03-12 12:00:00字符串转换成日期 select to_date('1981-03-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS') from dual; --将每个人的薪水转换成固定格式的字符串 select to_char(sal, 'L00,000.9999') from emp; --将固定格式的字符串转换成数值 select to_number('$1,250.00', '$9,999.99') from dual; --null当null参与计算时候，需要要nvl这个函数 select ename, sal*12+comm from emp; select ename, sal*12+ nvl(comm, 0) from emp;   组函数： ? avg()返回某列的平均值 select avg(sal) from emp;  //求所有人的平均工资 avg()忽略列值为null的行，不计入这一行，就当没他。 select avg(comm) from emp ;  //求津贴的平均数为550，只计入4个人。其他为null的没记入。 select avg(nvl(comm,0)) from emp;  //想计入的话，可以通过nvl处理数据 ? min()返回某列的最小值(忽略列值为null的行) ? select min(sal) from emp; select ename from emp where sal = (select min(sal) from emp); //查工资最低的人。 max()返回某列的最大值(忽略列值为null的行) select max(sal) from emp; 对非数值数据使用max()。在用于文本数据时，如果数据按相应的列排序，则max（）返回最后一行。 select max(ename) from emp order by ename; ? sum()返回某列值的和(忽略列值为null的行) select sum(sal) from emp; select sum(comm+300) from emp; select max(comm+300) from emp; select max(sal+comm) from emp; ? count()返回某列的行数 count(column)忽略null值的行。Count(*)的话不忽略。 select count(ename) from emp;//结果14 select count(comm) from emp;  //结果：4 select count(*) form emp where sal>2000; distinct: select avg(distinct sal) from emp;  //所有sal值相同的行，只计入一行。 Distinct必须使用列名。不能用于count（*）这样的用法。 select count(distinct sal)  from emp; select count(distinct *)  from emp; 这些函数是高效设计的，他们返回结果一般比你在自己的客户机应用程序中计算要快得多。 数据分组 分组允许把数据分为多个逻辑组，然后以逻辑组为单位进行数据汇总。 创建分组（Group by子句必须出现在where子句之后，order by子句之前。） Group by子句可以包含任意数目的列。一列？多列？ 除组函数计算语句外，select语句中的每个列都必须在group by子句中给出。 select ename,deptno from emp group by deptno;  //报错了。 上面的语句将会报错，这是因为如果按照deptno进行分组的话，每组会有一个薪水最大值，但每个组里面有多个名字，不能够产生唯一的ename,因此这样就出现了不匹配。 按照deptno分组，计算每个部门中员工的最高薪水是多少？ select max(sal),deptno from emp group by deptno; 如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分为一组。 select comm from emp group by comm; 组函数嵌套： 最多两层： 查询这三个部门里面，平均薪水最高的部门是多少： Select max(avg(sal)) from emp group by deptno; 过滤分组 Sql还允许过滤分组，规定包括哪些分组，排除哪些分组。 Where过滤行，having过滤分组。 Having支持所有where操作符。 Where在数据分组前进行过滤，having在数据分组后进行过滤。这是一个重要的区别，where排除的行不包括在分组中。这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组。 select avg(sal) as  avg_sal, deptno  from emp group by deptno having avg_sal>2000;  //报错了。 select avg(sal),deptno  from emp group by deptno having avg(sal)>2000; 基本练习： --group function组函数 --求所有人的薪水的总和，平均值，最大值，最小值 select sum(sal) , avg(sal), max(sal) , min(sal) from emp; --求总的行数 select count(*) from emp; --求总的行树，（可以指定具体的字段）但如果字段有null值的时候需要小心使用 select count(comm) from emp; --也可以过滤掉重复的行之后统计行数 select count(distinct deptno) from emp; --可以指明按照哪个字段进行分组.比如；分部门统计最高薪水 select deptno, max(sal) from emp group by deptno; --也可以按照多个字段来分组统计，比如：分部门和岗位，统计最高薪水和行数 select deptno, job , max(sal), count(*) from emp group by deptno, job; -------------------------------------------------------- --重要：出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中。 -------------------------------------------------------- --select ename, deptno, max(sal) from emp group by deptno; --select ename, max(sal) from emp; --求薪水最高的员工姓名 select ename from emp where sal = (select max(sal) from emp); --having从句的用法 --求平均薪水是2000以上的部门 select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;   分组和排序 一般在使用group by子句时，应该也给出order by子句。这是保证数据正确排序的唯一方法。 select avg(sal),deptno  from emp group by deptno having avg(sal)>2000 order by deptno; select子句的排列顺序： 子句 说明 是否必须使用 Select 要返回的列或表达式 是 From 从中检索数据的表 仅在从表选择数据时使用 Where 行级过滤 否 group by 分组说明 仅在按组计算聚集时使用 Having 组级过滤 否 order by 输出排序顺序 否       事实上排列顺序也是sql的执行顺序： 1. 先执行where子句进行行级过滤 2. 将where子句过滤后的结果，进行分组 3. 将分好组的数据进行组级别的过滤 4. 最后将最终数据进行排序 基本练习： 求每个部门的平均薪水： select avg(sal),deptno from emp where sal>2000 group by deptno having avg(sal)>30 order by deptno desc; 求每个部门薪水在1200以上的雇员的平均薪水、最高薪水，并且分组结果中只包含平均薪水大于1500的部门，排序按照部门平均薪水倒序排列 select max(sal),avg(sal), deptno from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc; /* 把雇员按部门分组， 求最高薪水， 部门号， 过滤掉名字中第二个字母是'A'的， 要求分组后的平均薪水>1500， 按照部门编号倒序排列 */ select deptno, max(sal) from emp where ename not like '_A%' group by deptno having avg(sal) > 1500 order by deptno desc;   子查询 ? 子查询：即嵌套在其他查询中的查询。 ? 理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。 ? 子查询总是由内向外执行。 ? 写子查询时注意格式。不然阅读起来比较困难 ? 子查询嵌套数目无限制，但是处于性能的考虑，实际工作中尽量不要嵌套太多的子查询。 ? Where子句中写子查询 ? 返回一个字段多行记录时，用in 我们要查在雇员中有哪些人是经理人，也就是说，有哪些人的empno号在mgr这个字段中出现过，这个时候，应当首先查询mgr中有哪些号码，然后再看看有哪些人的雇员号码在此出现： select empno,ename from emp where empno in (select distinct mgr from emp);   ? 返回一个值时，可以用=,<,>等等。 有哪些人的薪水是在整个雇员的平均薪水之上的： 1. 首先求所有雇员的平均薪水 select avg(sal+nvl(comm,0)) from emp 2. 然后求： select ename,empno, sal, sal+nvl(comm,0) from emp where sal+nvl(comm,0)>(select avg(sal+nvl(comm,0)) from emp);   ? From子句中写子查询 ? 返回多字段的记录时，使用。这个时候可以将子查询当作一张多字段的表来看待。 每个部门平均薪水的等级，可以这样考虑，首先将每个部门的平均薪水求出来，然后把结果当成一张表，再用这张结果表和salgrade表做连接，以此求得薪水等级。 1. 先求出每个部门平均薪水的表t。 2. 将t和salgrade进行关联查询就可以了。 select * from salgrade s, (select deptno,avg(sal) avg_sal from emp group by deptno) t where t.avg_sal between s.losal and s.hisal;   练习： 1. 求平均薪水最高的部门的部门编号 select deptno from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno); 2. 求出emp表中哪些人是经理人，打印出名字和编号 课堂中已做过。 3. 求比普通员工的最高薪水还要高的经理人名称 select ename, sal from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null) ) and empno in (select distinct mgr from emp where mgr is not null)   Oracle和mysql的分页技术： Oracle分页处理： Oracle下select语句每个结果集中都有一个伪字段存在，这个字段的名字叫做rownum.用来标识每条记录的行号，行号从1开始，每次递增1. 但是，rownum只能使用<,<=,不能使用=,>,>=。 Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rownum 是当符合条件记录集合生成的时候打上的一个序号标记 所以序号标记先有然后才是 排序 。 select ename from emp; select ename from emp where rownum<=5; 而且当rownum和order by一起使用时，会首先选出符合rownum条件的记录，然后再进行排序，这回给我们的查询带来难度。 如：我们要求出薪水最高的前5个人时，一般人最直接的想法是： select ename,sal from emp where rownum<=5 order by sal desc; 但是因为这个语句先用where子句过滤掉了其他行。所以出来的结果并不是薪水最高的前5个人。 这时候呢，我们就需要用的子查询： select r,ename,sal from (select rownum r,ename,sal from (select * from emp order by sal desc)) where r<9 and r>5; -------这个有难度。   mysql里面的分页： 关键字：limit(startIndex,length)。示例： select * from emp where sal>1000 limit 5,10; 注： 给表起别名的好处： 1. 缩短sql语句。 2. 允许在单条select语句中多次使用相同的表。 3. 可以用于自连接 注意事项： select ename, e.deptno,dname from emp e,dept d where e.deptno=d.deptno;   表的联接 SQL最强大的功能之一就是能在数据查询的执行中联结表。 ? 创建联接 在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。Where子句座位过滤条件，他只包含哪些匹配给定条件的行。没有where子句，第一个表中的每一行都将与第二个表中的每一行配对，而不管他们逻辑上是否可以配在一起。------这个叫笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。 ? 笛卡尔积 ? 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数 ? 应该保证所有联结都有where子句，不然数据库返回比想要的数据多得多的数据 select ename,dname from emp,dept;  //返回14*4=56个数据。 ? 完全限定列名 在引用的列可能出现二义性时，必须使用完全限定列名（用一个句点分隔的表名和列名）。 打印出雇员名字、所在单位名字： select ename, emp.deptno,dname from emp dept where emp.deptno=dept.deptno;   自联接： 自联结通常作为外部语句用来替代从相同表中检索数据的使用子查询语句，虽然最终的结果相同。但是一般情况，很多数据库软件处理自联结的速度要比处理子查询的速度快得多。 --检索出雇员姓名和该雇员的经理的姓名 select e1.ename "employee", e2.ename "manager" from emp e1, emp e2 where e1.mgr=e2.empno; 自联结用法 select e1.ename, e2.ename from emp e1,(select ename,mgr from emp) e2 where e1.mgr=e2.empno; 可用子查询代替   在where子句中进行多表连接(SQL1992) ? select ename,dname from emp,dept; ? 缺点：sql1992的语法规则，语句过滤条件和表连接的条件都放到了where子句中 。当条件过多时，联结条件多，过滤条件多时，就容易造成混淆。 求编号30部门的雇员名字和所在部门名称： select ename,dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=30;   在where子句中进行多表连接(SQL1999) ? 修正了整个缺点，把联结条件，过滤条件分开来： ? ? select ename,dname from emp    join  dept on  emp.deptno=dept.deptno where  emp.deptno=30; ? 联结 ? 内联结(只显示符合条件的行，一般用它) select dname,ename from emp join dept on emp.deptno=dept.deptno; ? 左外联结(显示左边表的全部行，右边表符合条件的行) select dname,ename from emp left outer join dept on emp.deptno=dept.deptno; --为了显示结果，可以再emp表中增加一行数据，该数据没有部门！ ? 右外联结(显示右边表的全部行，左边表符合条件的行) select dname,ename from emp right outer join dept on emp.deptno=dept.deptno; ? 全外联结(显示左、右两个表的全部行) select dname,ename from emp full outer join dept on emp.deptno=dept.deptno; 注：其中outer也可以省略，简写为left join , right join , full join 作业： 1. 求部门平均薪水的等级 --(select avg(sal) avg_sal,deptno from emp group by deptno) t1 --select * from salgrade select avg_sal,deptno,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t1 join salgrade on t1.avg_sal>=losal and t1.avg_sal<=hisal; 2. 求平均薪水的等级最低的部门名称 select dept.deptno,dname,grade from dept join (select avg_sal,deptno,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t1 join salgrade on t1.avg_sal>=losal and t1.avg_sal<=hisal where grade = ( select min(grade) from (select avg_sal,deptno,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t1 join salgrade on t1.avg_sal>=losal and t1.avg_sal<=hisal ) t2 ) ) t3 on dept.deptno=t3.deptno;   3. 求部门经理人中平均薪水最低的部门名称 1. 先查出经理人及平均薪水等级 select empno, ename , grade , deptno ,sal from emp e, salgrade s where e.sal between s.losal and s.hisal and e.empno in (select distinct mgr from emp) 2. 将经理人及平均薪水进行分组 select deptno,avg(sal) mgr_avg_sal from (select empno, ename , grade , deptno ,sal from emp e, salgrade s where e.sal between s.losal and s.hisal and e.empno in (select distinct mgr from emp) ) t1 group by deptno; 3. 查出最后结果 select mgr_avg_sal,deptno from (select deptno,avg(sal) mgr_avg_sal from (select empno, ename , grade , deptno ,sal from emp e, salgrade s where e.sal between s.losal and s.hisal and e.empno in (select distinct mgr from emp) ) t1 group by deptno ) t3 where t3.mgr_avg_sal = (select min(mgr_avg_sal) from (select deptno,avg(sal) mgr_avg_sal from (select empno, ename , grade , deptno ,sal from emp e, salgrade s where e.sal between s.losal and s.hisal and e.empno in (select distinct mgr from emp) ) t1 group by deptno ) t2 );   4. 求薪水最高的前5名雇员 5. 求薪水最高的第6到第10名雇员   Oracle的集合操作： union : 得到两个结果集的并集(不含重复值) select ename, empno from emp where deptno = 10 union select ename, empno from emp where deptno = 20 --相当于 select ename, empno from emp where deptno = 10 or deptno = 20;   minus : 求前一个结果集减去后一个结果集的差集（不包含重复值） select ename, empno from emp where deptno in (10,20) minus select ename, empno from emp where sal < 1500 --相当于 select ename, empno from emp where deptno in (10,20) and sal >= 1500; --minus方法求薪水最高的第6到第10名雇员(重点掌握) select rownum r,t.* from (select * from emp order by sal desc) t where rownum<=10 minus select rownum r,t.* from (select * from emp order by sal desc) t where rownum<=5   [注意] 结果集中的对应列之间的数据类型必须相同才能进行合并,否则会出现异常. DML语句： 插入数据： insert语句的基本语法： insert into tableName [（列1，列2，列3…..）] values (列1的值，[列2的值，列3的值，… ]) . 1. 插入完整一行记录 insert into empcopy (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1111,'gao','clerk',7902,sysdate,10000,3000,40);   2. 插入行的一部分 a) 使用上面的列名，还可以插入部分行，即指定部分行的列名。但是省略的列必须满足下面的条件： i. 该列定义为允许Null值。 ii. 在表定义中给出默认值，这表示如果不给出值，将使用默认值。 iii. 如果不符合上面两个条件，将会报错。不能成功插入。 insert into empcopy (empno,ename) values(1111,'gao');   3. 从一个表复制到另一个表 create table empCopy as Select * from emp; 更新数据： 1. 更新表中特定行。(有where子句) 2. 更新表中所有行。(无where子句) 典型语句： ? update tableName set  col1=1, col2=2  where  ….. 删除数据： ? 典型语句： ? delete from tableName where …; ? Delete从表中删除行，甚至是表中所有的行，但是不删除表本身，也就是不删除表结构。 ? 如果想从表中删除所有的行，不要使用delete，可使用truncate table 语句，他完成相同的工作，但是速度更快。 附： 所谓的CRUD(create read update delete)操作, 即增删改查！ 事务处理： 问题： 事务处理可以用来维护数据库的完整性，他保证成批的sql操作要么完全执行，要么完全不执行。 假如由于某种数据库故障，如超出磁盘空间、安全限制、表锁等。如果故障发生在行插入之后，…或者一个行刚插入一部分。这就会造成一些问题。 那么，事务处理是一种机制，用来管理必须成批执行的sql操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，他们或者作为整体执行，或者不完全执行。如果没有错误发生，整组语句一起提交给数据库表。如果发生错误，则进行回退，以恢复数据库到某个已知且安全的状态。 ? 基本概念： ? 事务：transaction  指一组要么完全执行，要么完全不执行的sql语句 ? 回退：rollback  指撤销指定sql语句的过程。 ? 提交：commit  指将未存储的sql语句结果写入数据库表。 ? 作用： ? 事务处理可以用来维护数据库的完整性，他保证成批的sql操作要么完全执行，要么完全不执行。 ? 适用范围： ? 事务处理用来管理insert, update和delete语句。不能回退select语句、不能回退create, drop操作。事务处理中可以使用这些语句，但进行回退时，他们不被撤销。 ? 在Oracle中，一个Transaction起始于一条DML(Insert、Update和Delete )语句，结束于以下的几种情况： ? 用户显式执行Commit语句提交操作或Rollback语句回退。 ? 当执行DDL(Create、Alter、Drop)语句事务自动提交。 ? 用户正常断开连接时，Transaction自动提交。 ? 系统崩溃或断电时事务自动回退。 表的创建（DDL） ? create table tableName (column datatype [default …], …); ? 设计要求： ? 建立一张用来存储学生信息的表，表中的字段包含了学生的学号、姓名、年龄、入学日期、 年级 六年级体育公开课教案九年级家长会课件PPT下载六年级家长会PPT课件一年级上册汉语拼音练习题六年级上册道德与法治课件 、班级、email等信息，并且为grade指定了默认值为1，如果在插入数据时不指定grade得值，就代表是一年级的学生 ? create table stu ( id number(6), name varchar2(20) not null, sex number(1) , age number(3), sdate date, grade number(2), class number(4), email varchar2(50) unique );   ? 约束：当我们创建表的时候，同时可以指定所插入数据的一些规则，比如说某个字段不能为空值，某个字段的值（比如年龄）不能小于零等等，这些规则称为约束。在Oracle内部，约束跟表一样，也是一种数据库对象，也有它应有的名字，默认以SYS_Cn的方式指定。 ? 常用的约束有以下几种： ? NOT NULL 非空 该字段的值不能为空！ ? UNIQUE KEY 唯一 该字段的值不能够有重复的记录。但是：unique约束只是代表该字段的值不能重复，但空值是允许的 ? PRIMARY KEY 主键 从语法上看，主键相当于非空约束和唯一约束的组合，但是在一张表中只允许有一个主键，这个主键可以是一个或者多个子段的组合 表中任意列只要满足以下条件，都可以用于主键：1. 任意两行的主键值都不相同 2. 每行都具有一个主键值（即列中不允许NULL值） 3. 包含主键值的列不修改或更新 4. 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行   ? FOREIGN KEY 外键 1. 外键是表中的一个列，其值必须在另一表的主键中列出。 2. 外键约束建立于一张表的两个字段或两张表的两个字段上，用于保证两个字段的关系，即：子表外键字段的值必须在参照字段的取值中存在，并且，如果字段的值被别的字段参照时，记录不允许被删除。   ? CHECK 自定义检查约束 Check约束用于检验字段的值是否符合某个条件表达式。不常用，实际开发中，一般通过程序进行数据检查。   ? 设计要求： ? 建立一张用来存储学生信息的表，表中的字段包含了学生的学号、姓名、年龄、入学日期、年级、班级、email等信息，并且为grade指定了默认值为1，如果在插入数据时不指定grade得值，就代表是一年级的学生 ? create table stu ( id number(6), name varchar2(20) not null, sex number(1) , age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50) not null unique ); 字段级约束： create table stu ( id number(6), name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50) constraint stu_email_uni unique ); 表级约束： create table stu ( id number(6), name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50), constraint stu_email_uni unique(email,name) ); 增加主键约束： create table stu(id number(6) primary key,name varchar2(20) constraint stu_name_nn not null,sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50),constraint stu_email_uni unique(email) ); constraint stu_id_name_pk primary key (id, name) 练习：创建两张表:部门（编号 主键约束，部门的名称 非空约束） 员工( 编号 主键约束，员工的名称 非空约束，员工所属的部门 外键约束 ) 测试外键约束： create table class ( id number(4) primary key, name varchar2(20) not null ); create table stu ( id number(6), name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4) references class(id), email varchar2(50) constraint stu_email_uni unique ); 当我们建立了这样的联系后，如果想执行下面的语句向stu表中插入数据： insert into stu values (1, 'test', 0, 22, to_date('2005-03-02' , 'YYYY-MM-DD'), 1, 1, 'test@test.com') 结果将是： ERROR 位于第 1 行: ORA-02291: 违反完整约束条件 (SCOTT.STU_CLASS_FK) - 未找到父项关键字 这是因为有外键关系的存在，我们想插入stu表中任何的class的值，必须在class表中id字段找到，因此，我们首先执行下面的语句： insert into class values (1, ‘Class1’); 然后再执行上面的操作即可成功。 当stu表中已经有记录参照了class表中id为1的记录时，如果我们想执行下面的操作： delete from class where id = 1;   表的修改 当一张表已经建好，并且其中有了部分数据的时候，我们要修改表的结构，如果采用先删除再重建的方法就不适合，这时可以直接采用修改表结构的语法，即alter table语句，该语句也可以用于对于表约束的修改，下面给出几个例子： 增加字段 例如要给stu表增加一个家庭住址的字段，在表已经建立，并且已经有了部分数据的情况下，我们可以这样写： alter table stu add (addr varchar2(100)); 删除字段 例如要讲我们刚才新增的addr字段进行删除，可以这样写： alter table stu drop (addr); 修改字段 例如我们要修改新增的addr字段的精度，可以这样写： alter table stu modify (addr varchar2(50)); 值得注意的是当字段进行修改的时候，如果原来该字段中没有任何数据，那么可以将字段修改为任何类型，否则，所修改的类型必须能够容纳原有的数据。 删除或增加约束条件 我们也可以使用alter table语句来删除约束条件或者增加新的约束条件，例如，我们要删除stu表中的外键约束stu_class_fk，可以这样写： alter table stu drop constraint stu_class_fk; 同样也可以使用该语句加入新的约束： alter table stu add constraint stu_class_fk foreign key (classId) references class(id); 值得注意的是，非空约束不能够使用类似的语法，只能够通过修改字段来同时修改约束，例如： 将name字段上的非空约束去除： alter table stu modify (name varchar2(20) null); 在name字段上添加非空约束： alter table stu modify (name varchar2(20) not null); 当无法确定一个约束的名字的时候，可以向系统表中寻找，有关系统表的操作，请参考附录《数据库常用DBA操作》，也可以将原来的表删除，然后建立新的表。 删除表： Drop table empCopy; 数据库对象—索引 ? 作用： ? 索引是为了加快对数据的搜索速度而设立的，如果说数据库中的数据是一本字典的话，索引就是字典的目录，譬如说我们要查“赢”这个字，直接从字典一页一页的翻的话速度将会很慢，而通过目录首先查到拼音’Ying’的起始页码，然后从该页码开始查，速度将会快得多，因此，如果想增加对表中某个字段或某几个字段的查询速度，就可以为他们建立起索引，但是，索引的建立也并非没有任何代价，索引是要占空间的，而且索引太多会引起插入数据、修改数据、删除数据时速度的降低，对于太长的字段，建立索引的帮助并不大，因此，除非必要，否则不要随意添加索引。 ? 开发中使用索引的要点： ? 1. 索引改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。 ? 2. 索引数据可能要占用大量的存储空间。 ? 3. 并非所有的数据都适合于索引。唯一性不好的数据（如省）从索引的到的好处不比具有更多可能值的数据（如姓名）从索引得到的好处多。 ? 4. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能是索引的备选。 ? 5. 可以在索引中定义多个列（如省加城市），这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。 ? 创建索引： ? create index idx_empcopy_ename on empcopy(ename); ? 删除索引： ? drop index index_name; 数据库对象—序列 序列是oracle专有的对象，它用来产生一个自动递增的数列 ? 创建序列 ? create sequence seq_empcopy_id   start with 1 increment by 1; ? 使用序列 ? insert into empcopy  (empno,ename) values (seq_empcopy_id.nextval, ‘TEST’); ? 删除序列 ? drop sequence seq_empcopy_id; 数据库对象—视图 使用system用户为scott增加权限：grant create view,create table to scott; 使用system用户为scott解锁：alter user  scott  account  unlock; ? 定义： ? 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。视图包含的不是数据而是需要检索数据的查询。 ? 视图就是一张虚的表，或者说就是一个子查询，只不过这个子查询具有一个名字，可以通过这个名字来访问子查询的结果，适当的利用视图，可以使我们的查询变得简单，但是如果视图建立的太多的话，会给我们系统的维护带来麻烦，比如如果表的结构了，与这张表有关的任何视图必须跟着修改，视图很多的时候这将是一件很麻烦的事情，因此，除非必要，否则不要轻易建立视图 ? 视图的作用： ? 重用sql语句, 简化复杂的sql操作。在编写查询后，可以方便地重用它而不必知道他的基本查询细节。 ? 使用表的组成部分而不是整个表 ? 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。 ? 更改数据格式和显示。 ? 创建视图 ? create view v$_emp_dept   as select emp.deptno,ename,dname from emp join dept  on  emp.deptno=dept.deptno; ? 使用视图 ? select * from v$_emp_dept; ? 删除视图 ? Drop view  v$_emp_dept； ? 示例： 还记得吗？我们在学习Select语句时，通过子查询和多表连接，我们可以建立非常复杂的查询语句，例如， 我们要求平均薪水的等级最低的部门，它的部门名称是什么，如果我们完全使用子查询，这个查询是相当复杂的： select dname, grade from (select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t, salgrade s where t.avg_sal between s.losal and s.hisal ) t1, dept where t1.deptno = dept.deptno and t1.grade = (select min(grade) from (select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t, salgrade s where t.avg_sal between s.losal and s.hisal ) ); 仔细分析这条select语句，你会发现这其中有很多的重复的语句，为什么不能把这些重复的语句起一个简单的名字呢？实际上，视图正具有这个作用，以上面的查询为例，如果我们建立这样的一个视图，来存储部门平均薪水的信息： create view v$_temp as (select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t, salgrade s where t.avg_sal between s.losal and s.hisal ); 上面的查询就可以简化为： select dname from dept, v$_temp where v$_temp.deptno = dept.deptno and grade = (select min(grade) from v$_temp);