小布老师SQL学习笔记.docx
《小布老师SQL学习笔记.docx》由会员分享，可在线阅读，更多相关《小布老师SQL学习笔记.docx（42页珍藏版）》请在冰点文库上搜索。
小布老师SQL学习笔记
SQL基础介绍Oracle11gSQLFundamentalsTrainingIntroduction
1、primarykey主键表中的唯一的标示非空
2、foreignkey外键在本表中引用的另外一张表的主键
3、ER实体关系模型
4、SQL的分类
4.1、Datamanipulationlanguage（DML）数据操作语言
Select查询数据
Insert插入数据
Update更新数据
Delete删除数据
Merge合并数据
4.2、Datadefinitionlanguage（DDL）数据定义语言
Create创建表
Alter修改表
Drop删除表
Rename重命名表
Truncate截取
Comment
4.3、Datacontrollanguage（DCL）数据控制语言
Grant
Revoke取消
4.4、Transactioncontrol事物控制
Commit提交
Rollback回滚
Savepoint
SQL>showuser--查看当前用户
USERis"SYS"
descdba_users--查看所有用户
SQL>setlines200
SQL>selectusername,account_statusfromdba_users;
USERNAMEACCOUNT_STATUS
--------------------------------------------------------------
PERFSTATOPEN
HROPEN
OEOPEN
SHOPEN
OPS$ORACLEOPEN
USER01OPEN
DIPEXPIRED&LOCKED
IXEXPIRED&LOCKED
MDDATAEXPIRED&LOCKED
PMEXPIRED&LOCKED
BIEXPIRED&LOCKED
SQL>selecttable_namefromuser_tables;--查看存在的表
--字符串和日期型是左对齐，数字型是右对齐，表头是大写。
SQL>select*fromdepartments;
DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_ID
----------------------------------------------------------------
10Administration2001700
20Marketing2011800
30Purchasing1141700
01--RestrictingDataUsingtheSQLSELECTStatement
1、算数表达式+-*/
SQL>selectlast_name,salary,salary+300fromemployees;
LAST_NAMESALARYSALARY+300
---------------------------------------------
Walsh45331.6845631.68
Feeney45190.5645490.56
107rowsselected.
--算数表达式中有NULL值结果为NULL
2、别名用双引号括起来
selectlast_nameas"Name",commission_pctas"Comm"fromemployees;
NameComm
-----------------------------------
Ozer.25
Bloom.2
Fox.2
Orderby中可以使用别名和指定序号
3、链接||将两个字段连起来显示
--两个字段中插入空格
selectlast_name||‘'||job_idas"Employees"fromemployees;
Employees
------------------------------------
OConnellSH_CLERK
GrantSH_CLERK
WhalenAD_ASST
selectlast_name||'isa'||job_idas"Employees"fromemployees;
Employees
-----------------------------------------
OConnellisaSH_CLERK
GrantisaSH_CLERK
WhalenisaAD_ASST
4、distinct查不同的值
selectdistinctdepartment_idfromemployees;
02--RestrictingandSortingData
1、Escape字符转义
SQL>selectlast_name,job_idfromemployees
2wherejob_idlike'%SA\_%'escape'\';
--escape'\'是将斜线设置为转义字符，把下划线转义成了一个普通的字符
LAST_NAMEJOB_ID
-----------------------------------
RussellSA_MAN
PartnersSA_MAN
ErrazurizSA_MAN
2、SQL执行优先级
3、替换变量&和&&简单的字符串的替换；
是客户端软件的功能
SQL>selectlast_name,salaryfromemployees
2whereemployee_id=&employee_num;如是字符串和日期一定要加单引号
Entervalueforemployee_num:
123
old2:
whereemployee_id=&employee_num
new2:
whereemployee_id=123
LAST_NAMESALARY
-----------------------------------
Vollman50161.44
双&&替换变量后，只用输入一次。
4、Define定义变量
SQL>defineemployee=200
SQL>selectemployee_id,last_namefromemployees
2whereemployee_id=&employee;
old2:
whereemployee_id=&employee
new2:
whereemployee_id=200
EMPLOYEE_IDLAST_NAME
------------------------------------
200Whalen
Setverifyoff关闭提示
Setverifyon打开提示
03--UsingSingle-RowFunctionstoCustomizeOutput单行函数
1、函数
2、单行函数：
字符串函数，数值函数，日期函数，转换函数conversion，通用函数general
2.1、字符串函数
Lower字符变大写
Upper字符变小写
Initcap首字大写
2.2、字符串操作函数
SQL>selectconcat（'12345','67890'）fromdual;--将两个字符串连接起来
CONCAT（'12
----------
1234567890
SQL>selectsubstr（'123456789',1,5）fromdual;--截取1到5喂
SUBST
-----
12345
SQL>selectsubstr（'123456789',5）fromdual;--截取5位以后的
SUBST
-----
56789
SQL>selectlength（'1234567890'）fromdual;--计算字符串的长度
LENGTH（'1234567890'）
--------------------
10
SQL>selectinstr（'1234567890',0）fromdual;--查看0所在的位置
INSTR（'1234567890',0）
---------------------
10
SQL>selectlpad（'12345',10,'*'）fromdual;--在左侧添加指定的字符
LPAD（'1234
----------
*****12345
SQL>selectrpad（'12345',10,'*'）fromdual;--在右侧添加指定的字符
RPAD（'1234
----------
12345*****
SQL>selectreplace（'1234567890','1','0'）fromdual;--替换字符
REPLACE（'1
----------
023*******
SQL>selecttrim（'1'from'1234567890'）fromdual;--去除字符
TRIM（'1'F
---------
234567890
2.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、日期函数date
Sysdate显示系统时间
SQL>selectsysdatefromdual;
SYSDATE
-------------------
2012-02-22:
13:
50:
30
Last_day（‘日期’）显示当月的最后一天
SQL>Selectlast_day（'20120201'）fromdual;
LAST_DAY（'20120201'
-------------------
2012-02-29:
00:
00:
00
Add_months（‘日期’,’增加的月数’）
SQL>Selectadd_months（'20120222','1'）fromdual;
ADD_MONTHS（'2012022
-------------------
2012-03-22:
00:
00:
00
04--UsingConversionFunctionsandconditionalExpressions转换函数
1、To_char日期转字符
SQL>selectemployee_id,to_char（hire_date,'YYYY-MM-DD'）hired
2fromemployees
3wherelast_name='Higgins';
EMPLOYEE_IDHIRED
---------------------
2051994-06-07
2、加fm去掉前导字符
SQL>selectemployee_id,to_char（hire_date,'fmYYYY-MM-DD'）hired
2fromemployees
3wherelast_name='Higgins';
EMPLOYEE_IDHIRED
---------------------
2051994-6-7
显示时间
SQL>selectemployee_id,to_char（hire_date,'YYYY-MM-DDHH24:
MI:
SS'）hired
2fromemployees
3wherelast_name='Higgins';
EMPLOYEE_IDHIRED
------------------------------
2051994-06-0700:
00:
00
3、To_char数值转字符
SQL>selectto_char（salary,'$99,999.00'）SALARY
2fromemployees
3wherelast_name='Ernst';
SALARY
-----------
$49,530.72
4、To_number字符转数值
SQL>selectto_number（'-$12,345.67','$99,999.99'）fromdual;
TO_NUMBER（'-$12,345.67','$99,999.99'）
-------------------------------------
-12345.67
5、To_date字符转日期
SQL>selectto_date（'July4,2007','MonthDD,YYYY'）FROMdual;
TO_DATE（'JULY4,2007
-------------------
2007-07-04:
00:
00:
00
6、加fx为精准配备
SQL>selectto_date（'July4,2007','fxMonthDD,YYYY'）FROMdual;
selectto_date（'July4,2007','fxMonthDD,YYYY'）FROMdual
ERRORatline1:
ORA-01858:
anon-numericcharacterwasfoundwhereanumericwasexpected
7、显示系统当前时间sysdate
SQL>selectto_char（sysdate,'YYYY-MM-DDHH24:
MI:
SS'）sysdatafromdual;
SYSDATA
-------------------
2012-02-2015:
22:
54
2.、General通用函数
2.1、NVL判断函数
计算一年的工资并且加上分红
NVL（commission_pct,0）
NVL对括号中第一个参数进行判断，如第一个参数为非NULL则显示其本身，如第一个参数为NULL则显示为0
2.2、NVL2判断函数2
NVL（commission_pct,’SAL+COMM’,’SAL’）
NVL对括号中第一个参数进行判断，如第一个参数为非NULL则显示第二个参数，如第一个参数为NULL则显示第三个参数。
2.3、NULLIF
NULLIF将括号中的两个参数进行比较，如不等显示第一个参数，如相等显示为NULL
2.4、COALESCE将括号中的参数，自左向右进行逐个判断，如第一个参数为非NULL，则显示其本身，判断结束，如第一个参数为NULL，则对下一个参数进行判断，以此类推，如前面的参数都为NULL，则显示NULL。
2.5、CASE
根据部门的不同来计算工资
2.6、DECODE是oracle特有的函数，等同于CAES，但写法简单。
05--ReportingAggregatedDataUsingtheGroupFunctions组函数
1、Having过滤groupby的结果
SELECTcolumn,group_functionFROMtable
[WHEREcondition]
[GROUPBYgroup_by_expression]
[HAVINGgroup_condition]
[ORDERBYcolumn];
字句的执行次序
06--DisplayingDatafromMutipleTables从多表查询数据
1、JOIN的语法
--国际标准
SELECTtable1.column,table2.column
FROMtable1
[NATURALJOINtable2]|
[JOINtable2USING（volumn_name）]|
[JOINtable2
ON（table1.column_name=table2.column_name）]|
[LEFT|RIGHT|FULLOUTERJOINtable2
ON（table1.column_name=table2.column_name）]|
[CROSSJOINtable2];
--ORACLE标准
SELECTtable1.column,table2.column
FROMtable1,table2
WHEREtable1.column1=table2.column2;
2、别名
SQL>Selectemployee_id,last_name,e.department_id,department_name
2Fromemployeese,departmentsd
3Wheree.department_id=d.department_id;
MPLOYEE_IDLAST_NAMEDEPARTMENT_IDDEPARTMENT_NAME
-------------------------------------------------------------------------------
108Greenberg100Finance
111Sciarra100Finance
112Urman100Finance
113Popp100Finance
109Faviet100Finance
206Gietz110Accounting
205Higgins110Accounting
106rowsselected.
3、NATURALJOIN自然连接
系统自动将不同表中的相同的列进行自然连接
--国际标准
SQL>Selectdepartment_id,department_name,location_id,city
2Fromdepartments
3Naturaljoinlocations;
DEPARTMENT_IDDEPARTMENT_NAMELOCATION_IDCITY
------------------------------------------------------------------------------------
270Payroll1700Seattle
20Marketing1800Toronto
40HumanResources2400London
80Sales2500Oxford
70PublicRelations2700Munich
27rowsselected.
--oracle标准
SQL>Selectd.department_id,d.department_name,d.location_id,l.city
2Fromdepartmentsd,locationsl
3Whered.location_id=l.location_id;
DEPARTMENT_IDDEPARTMENT_NAMELOCATION_IDCITY
------------------------------------------------------------------------------------
270Payroll1700Seattle
20Marketing1800Toronto
40HumanResources2400London
80Sales2500Oxford
70PublicRelations2700Munich
27rowsselected.
4、USING指定不同表中的某一列进行连接
--国际标准
SQL>Selectemployee_id,last_name,location_id,department_id
2Fromemployeesjoindepartments
3Using（department_id）;
EMPLOYEE_IDLAST_NAMELOCATION_IDDEPARTMENT_ID
------------------------------------------------------------
108Greenberg1700100
111Sciarra1700100
112Urman1700100
113Popp1700100
109Faviet1700100
206Gietz1700110
205Higgins1700110
106rowsselected.
--oracle标准
SQL>Selectd.location_id,d.city,l.department_id
2Fromlocationsd,departmentsl
3Whered.location_id=l.location_id;
LOCATION_IDCITYDEPARTMENT_ID
------------------------------------------------------
1700Seattle270
1800Toronto20
2400London40
2500Oxford80
2700Munich70
27rowsselected.
5、ON连接两个表的列名可以不同，但内容要相同
SQL>Selecte.employee_id,e.last_name,e.department_id,d.location_id
2Fromemployeesejoindepartmentsd
3On（e.department_id=d.department_id）;
EMPLOYEE_IDLAST_NAMEDEPARTMENT_IDLOCATION_ID
------------------------------------------------------------
108Greenberg1001700
111Sciarra1001700
112Urman1001700
113Popp1001700
109Faviet1001700
206Gietz1101700
205Higgins110
升级会员 