小布老师SQL学习笔记.doc

Liao SQL基础介绍Oracle 11g SQL Fundamentals Training Introduction 1、primary key 主键 表中的唯一的标示 非空2、foreign key 外键 在本表中引用的另外一张表的主键3、ER 实体关系模型4、SQL的分类4.1、Data manipulation language(DML)数据操作语言Select 查询数据Insert 插入数据Update 更新数据Delete 删除数据Merge 合并数据4.2、Data definition language(DDL)数据定义语言Create 创建表Alter 修改表Drop 删除表Rename 重命名表Truncate 截取Comment4.3、Data control language(DCL)数据控制语言Grant Revoke 取消4.4、Transaction control事物控制Commit 提交Rollback 回滚SavepointSQL show user -查看当前用户USER is SYSdesc dba_users -查看所有用户SQL set lines 200SQL select username,account_status from dba_users;USERNAME ACCOUNT_STATUS- -PERFSTAT OPENHR OPENOE OPENSH OPENOPS$ORACLE OPENUSER01 OPENDIP EXPIRED & LOCKEDIX EXPIRED & LOCKEDMDDATA EXPIRED & LOCKEDPM EXPIRED & LOCKEDBI EXPIRED & LOCKEDSQL select table_name from user_tables; -查看存在的表-字符串和日期型是左对齐，数字型是右对齐，表头是大写。SQL select * from departments;DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID- - - - 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 170001-Restricting Data Using the SQL SELECT Statement1、算数表达式 + - * / 可以用于除FROM子句的其他子句SQL select last_name,salary,salary + 300 from employees;LAST_NAME SALARY SALARY+300- - -Walsh 45331.68 45631.68Feeney 45190.56 45490.56107 rows selected.-算数表达式中有NULL值结果为NULL-和字符串做运算时，结果为字符串。2、别名 用双引号括起来 ( 不能用于WHERE子句)select last_name as Name,commission_pct as Comm from employees;Name Comm- -Ozer .25Bloom .2Fox .2Order by 中可以使用别名和指定序号3、链接 | 将两个字段连起来显示-两个字段中插入空格select last_name | | job_id as Employees from employees;Employees-OConnell SH_CLERKGrant SH_CLERKWhalen AD_ASSTselect last_name | is a |job_id as Employees from employees;Employees-OConnell is a SH_CLERKGrant is a SH_CLERK;Whalen is a AD_ASSTq可以输出“” 单引号(实验证明应该是输出一段自定义字符)4、distinct查不同的值 （范围是整个SELECT 后的所有字段）select distinct department_id from employees;02-Restricting and Sorting Data补充where子句：1） 子句内的需要比较的字符需要用单引号引起来（比如where xxx=123），而且字符是区分大小写的。2） 子句如果比较的是日期字符（默认的格式是日-月-年），需要注意日期格式。3） 运算符例子：Between and:select emp_name,sal where sal between 2500 adn 3000;Select emp_name,sal where emp_name where emp_name between tony adn suen 字符也可以比较，A-Z，这样比较Like 通配符% 代表0或者多个字符_ 代表1个字符Select emp_name from emp where empname like 张%4） IS NULL / IS NOT NULL 字段是否为空WHERE 子句里判断5） 逻辑运算符AND OR NOT1、Escape字符转义SQL select last_name,job_id from employees 2 where job_id like %SA_% escape ; escape 来定义转义字符，也可以是| 或者其他的-escape 是将斜线设置为转义字符，把下划线转义成了一个普通的字符LAST_NAME JOB_ID- -Russell SA_MANPartners SA_MANErrazuriz SA_MAN2、 SQL执行优先级补充：排序ORDER BY 默认是升序，DESC是降序。ORDER BY子句里可以使用别名，WHERE ,group by , having子句不能使用Order by N 后面可以自己加数字，代表SELECT第N个字段，但不值得推荐。Order by 后面可以跟多个字段，表示先按照第一个字段先排序，然后再第一个字段中相同的值中，第二例再排序。如果有NULL值，升序是放到最后，降序是放到开始。但也可以自己改变：表的别名：另外：1、 form 子句的别名和国际标准不一样：不能加AS，其他子句可以。2、 表的别名设置后，其他地方使用表就只能用别名。列的别名则不用这样Where 子句不能包含多组函数，而且3、替换变量 &和& 简单的字符串的替换 ；几乎可以适用所有子句是客户端软件的功能SQL select last_name,salary from employees 2 where employee_id=&employee_num; 如是字符串和日期一定要加单引号Enter value for employee_num: 123old 2: where employee_id=&employee_numnew 2: where employee_id=123LAST_NAME SALARY- -Vollman 50161.44双&替换变量后，同一个变量，只用输入一次。4、 Define定义变量（适合用在批处理里面）SQL define employee=200SQL select employee_id,last_name from employees 2 where employee_id=&employee;old 2: where employee_id=&employeenew 2: where employee_id=200EMPLOYEE_ID LAST_NAME- - 200 WhalenSet verify off 关闭提示Set verify on 打开提示03-Using Single-Row Functions to Customize Output单行函数1 、函数2、单行函数：字符串函数，数值函数，日期函数，转换函数conversion，通用函数general2.1、字符串函数Lower 字符变大写Upper 字符变小写Initcap 首字大写2.2、字符串操作函数SQL select concat(12345,67890) from dual;-将两个字符串连接起来CONCAT(12-1234567890SQL select substr(123456789,1,5) from dual; -截取1到5喂SUBST-12345SQL select substr(123456789,5) from dual;-截取5位以后的SUBST-56789SQL select length(1234567890) from dual; -计算字符串的长度LENGTH(1234567890)- 10SQL select instr(1234567890,0) from dual; -查看0所在的位置INSTR(1234567890,0)- 10SQL select lpad(12345,10,*) from dual; -在左侧添加指定的字符LPAD(1234-*12345SQL select rpad(12345,10,*) from dual; -在右侧添加指定的字符RPAD(1234-12345*SQL select replace(1234567890,1,0) from dual; -替换字符REPLACE(1-0234567890SQL select trim(1 from 1234567890) from dual; -去除字符TRIM(1F-2345678902.3、数值函数2.3.1、Round(数值,保留小数点的位数) 0为小数点位 正值向右移，负值向左移，进行四舍五入。2.3.2、trunc (数值,保留小数点的位数) 0为小数点位 正值向右移，负值向左移，直接去掉后面的值2.3.3、Mod(数值，除数)取余数 mod(1600,500) 1600/500余100经常用于判断数值的奇偶 mod(数值，2)2.4、日期函数 dateSysdate显示系统时间SQL select sysdate from dual; SYSDATE-2012-02-22:13:50:30Last_day (日期) 显示当月的最后一天SQL Select last_day (20120201) from dual;LAST_DAY(20120201-2012-02-29:00:00:00Add_months(日期,增加的月数)SQL Select add_months (20120222,1) from dual;ADD_MONTHS(2012022-2012-03-22:00:00:0004-Using Conversion Functions and conditional Expressions 转换函数1、To_char 日期转字符SQL select employee_id,to_char(hire_date,YYYY-MM-DD) hired 2 from employees 3 where last_name=Higgins;EMPLOYEE_ID HIRED- - 205 1994-06-072、加fm去掉前导字符SQL select employee_id,to_char(hire_date,fmYYYY-MM-DD) hired 2 from employees 3 where last_name=Higgins;EMPLOYEE_ID HIRED- - 205 1994-6-7显示时间SQL select employee_id,to_char(hire_date,YYYY-MM-DD HH24:MI:SS) hired 2 from employees 3 where last_name=Higgins;EMPLOYEE_ID HIRED- - 205 1994-06-07 00:00:003、To_char 数值转字符SQL select to_char(salary,$99,999.00) SALARY 2 from employees 3 where last_name=Ernst;SALARY- $49,530.724、To_number 字符转数值SQL select to_number(-$12,345.67,$99,999.99) from dual;TO_NUMBER(-$12,345.67,$99,999.99)- -12345.675、To_date 字符转日期SQL select to_date(July 4, 2007,Month DD, YYYY) FROM dual;TO_DATE(JULY4,2007-2007-07-04:00:00:006、加fx为精准配备SQL select to_date(July 4, 2007,fxMonth DD, YYYY) FROM dual;select to_date(July 4, 2007,fxMonth DD, YYYY) FROM dualERROR at line 1:ORA-01858: a non-numeric character was found where a numeric was expected7、显示系统当前时间 sysdateSQL select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) sysdata from dual;SYSDATA-2012-02-20 15:22:542.、General 通用函数2.1、NVL判断函数计算一年的工资并且加上分红NVL(commission_pct,0)NVL对括号中第一个参数进行判断，如第一个参数为非NULL则显示其本身，如第一个参数为NULL则显示为02.2、NVL2判断函数2NVL(commission_pct,SAL+COMM,SAL)NVL对括号中第一个参数进行判断，如第一个参数为非NULL则显示第二个参数，如第一个参数为NULL则显示第三个参数。2.3、NULLIFNULLIF将括号中的两个参数进行比较，如不等显示第一个参数，如相等显示为NULL2.4、COALESCE 将括号中的参数，自左向右进行逐个判断，如第一个参数为非NULL，则显示其本身，判断结束，如第一个参数为NULL，则对下一个参数进行判断，以此类推，如前面的参数都为NULL，则显示NULL。2.5、CASE根据部门的不同来计算工资2.6、DECODE 是oracle特有的函数，等同于CASE，但写法简单。05-Reporting Aggregated Data Using the Group Functions组函数Where 子句不能用列别名，在1、Having 过滤group by 的结果SELECT column,group_function FROM tableWHERE condition GROUP BY group_by_expressionHAVING group_conditionORDER BY column ;字句的执行次序06-Displaying Data from Mutiple Tables 从多表查询数据1、JOIN的语法-国际标准SELECT table1.column, table2.columnFROM table1NATURAL JOIN table2 |JOIN table2 USING (volumn_name) |JOIN table2ON (table1.column_name = table2.column_name) |LEFT|RIGHT|FULL OUTER JOIN table2ON (table1.column_name = table2.column_name) |CROSS JOIN table2;-ORACLE标准SELECT table1.column, table2.columnFROM table1,table2WHERE table1.column1 = table2.column2;2、别名SQL Select employee_id,last_name,e.department_id,department_name 2 From employees e,departments d 3 Where e.department_id = d.department_id;MPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME- - - - 108 Greenberg 100 Finance 111 Sciarra 100 Finance 112 Urman 100 Finance 113 Popp 100 Finance 109 Faviet 100 Finance 206 Gietz 110 Accounting 205 Higgins 110 Accounting106 rows selected.3、NATURAL JOIN自然连接 (和using 不能共存)系统自动将不同表中的相同的列进行自然连接-国际标准SQL Select department_id,department_name,location_id,city 2 From departments 3 Natural join locations;DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY- - - - 270 Payroll 1700 Seattle 20 Marketing 1800 Toronto 40 Human Resources 2400 London 80 Sales 2500 Oxford 70 Public Relations 2700 Munich27 rows selected.-oracle标准SQL Select d.department_id,d.department_name,d.location_id,l.city 2 From departments d,locations l 3 Where d.location_id=l.location_id;DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY- - - - 270 Payroll 1700 Seattle 20 Marketing 1800 Toronto 40 Human Resources 2400 London 80 Sales 2500 Oxford 70 Public Relations 2700 Munich27 rows selected.4、USING 指定不同表中的某一列进行连接（只要求名字相同，不要求数据类型相同）-国际标准SQL Select employee_id,last_name,location_id,department_id 2 From employees join departments 3 Using (department_id); （括号注意要加）EMPLOYEE_ID LAST_NAME LOCATION_ID DEPARTMENT_ID- - - - 108 Greenberg 1700 100 111 Sciarra 1700 100 112 Urman 1700 100 113 Popp 1700 100 109 Faviet 1700 100 206 Gietz 1700 110 205 Higgins 1700 110106 rows selected.国际标准补充：如果using 语句中的列，where也使用了这列，那么where子句中不要加前缀。-oracle标准SQL Select d.location_id, d.city,l.department_id 2 From locations d,departments l 3 Where d.location_id = l.location_id;LOCATION_ID CITY DEPARTMENT_ID- - - 1700 Seattle 270 1800 Toronto 20 2400 London 40 2500 Oxford 80 2700 Munich 7027 rows selected.5、ON 连接两个表的列名可以不同，但内容要相同SQL Select e.employee_id,e.last_name,e.department_id,d.location_id 2 From employees e join departments d 3 On (e.department_id=d.department_id);EMPLOYEE_ID LAST_NAME DEPARTMENT_ID LOCATION_ID- - - - 108 Greenberg 100 1700 111 Sciarra 100 1700 112 Urman 100 1700 113 Popp 100 1700 109 Faviet 100 1700 206 Gietz 110 1700 205 Higgins 110 1700106 rows selected.5.1、多表进行连接SQL select employee_id,city,department_name 2 from employees e 3 join departments d 4 on d.department_id=e.department_id 5 join locations l 6 on d.location_id=l.location_id;EMPLOYEE_ID CITY DEPARTMENT_NAME- - - 191 South San Francisco Shipping 192 South San Francisco Shipping 193 South San Francisco Shipping 194 South San Francisco Shipping 195 South San Francisco Shipping 196 South San Francisco Shipping 197 South San Francisco Shipping106 rows selected.SQL run 1 select employee_id,city,department_name 2 from employees e,departments d,locations l 3 where e.department_id=d.department_id 4* and d.location_id=l.location_idEMPLOYEE_ID CITY DEPARTMENT_NAME- - - 191 South San Francisco Shipping 192 South San Francisco Shipping 193 South San Francisco Shipping 194 South San Francisco Shipping 195 South San Francisco Shipping 196 South San Francisco Shipping 197 South San Francisco Shipping106 rows selected.6、Self-Joins用于同一张表的JIONSQL select a.last_name| works for |b.last_name 2 from employees a,employees b 3 where a.manager_id=b.employee_id;A.LAST_NAME|WORKSFOR|B.LAST_NAME-Ozer works for CambraultJohnson works for ZlotkeyGrant works for ZlotkeyLivingston works for ZlotkeyTaylor works for ZlotkeyHutton works for ZlotkeyAbel works for Zlotkey106 rows selected.7、Nonequijoin 非对称连接Outer Join 将匹配不上的列也显示出来8、LEFT OUTER JOIN 将左表中未匹配的内容也显示出来-国际标准SQL select e.last_name,e.department_id,d.department_name 2 from employees e left outer join departments d 3 on (e.department_id=d.department_id);107 rows selected.SQL select e.last_name,e.department_id,d.department_name 2 from employees e,departments d 3 where e.department_id=d.department_id(+);107 rows selected.SQL select count(*) from employees; COUNT(*)- 1079、RIGHT OUTER JOIN将右表中未匹配的内容也显示出来SQL run 1 select e.last_name,e.department_id,d.department_name 2 from employees e right outer join departments d 3* on (e.department_id=d.department_id)122 rows selected.SQL run 1 select e.last_name,e.department_id,d.department_name 2 from employees e,departments d 3* where e.department_id(+)=d.department_id122 rows selected.-106+16=122/*有16部门没有雇员*/10、FULL OUTER JOIN 将左右表中未匹配的内容都显示出来SQL select e.last_name,d.department_id,d.department_name 2 from employees e full outer join departments d 3 on (e.department_id=d.department_id);123 rows selected.11、Cross Join A表数据数乘以B表的数据数-国际标准1 select last_name,department_name from employees2* cross join departments2889 rows selected.-oracle标准Select last_name,department_name from employees,departments;2889 rows selected.-107*27=288907-Using Subqueries to Solve Queries 子查询补充：注意 AAA=NULL j结果 AAA IS NULL的区别1、在HAVING字句中使用子查询SQL run 1 select department_id,min(salary) from e