资源描述：
《oraclesql语句速查》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、oracleSQL:StructureQueryLanguage常见数据类型Char(n)定长字符串Varchar2(n)变长字符串Number(m,p)数字类型Date日期类型SQL语句分类1.查询语句:SELECT2.DML语句(数据操作语言)INSERT/UPDATE/DELETE/MERGE3.DDL语句(数据定义语言)CREATE/ALTER/DROP/TRUNCATE4.DCL语句(数据控制语言)GRANT/REVOKE5.事务控制语句:COMMIT/ROLLBACK/SAVEPOINT一.SELECT语句1.基本查询格式SELECT{*,column[alias],...}FR
2、OMtable;例子SELECT*FROMdepartments;SELECTdepartment_id,department_nameFROMdepartments;带算数表达式的查询语句SELECTlast_name,salary,salary*12FROMemployees;SELECTlast_name,salary,salary*12+100FROMemployees;包含连接表达式的SELECT语句SELECTlast_name
3、
4、job_idFRMemployees;SELECTlast_name
5、
6、'workat'
7、
8、job_idFROMemployees;包含字段别名的SE
9、LECT语句SELECTlastname"姓名",salary"薪水"FROMemployees.包含文本字符串的SELECT语句SELECTlast_name
10、
11、'isa'
12、
13、job_idAS"EmployeeDetails"FROMemployees;去重关键字DISTINCTSELECTDISTINCTdepartment_idFROMemployees;取得日期格式的查询语句SELECT*FROMv$nls_parameters;取得当前日期日期的查询语句SELECTsysdateFROMDUAL;1.带限制条件的查询语句格式SELECT[DISTINCT]{*,column[ali
14、as],...}FROMtable[WHEREcondition(s)]包含比较操作符的查询语句SELECTlast_nameFROMemployeesWHEREsalary<=1500;常见操作符><>=<==<>BETWEEN...ANDIN(list)LIKEISNULL例子SELECT*FROMemployeesWHEREmanageer_idIN(1,2,3);SELECTlast_nameFROMemployeesWHERElast_nameISNULL;包含通配符的SELECT语句%表示零或多个字符_表示一个字符例子SELECTlast_nameFROMemployeesWHE
15、RElast_nameLIKE'_A%';WHERE条件的逻辑运算逻辑运算符ANDORNOT例子SELECT*FROMemployeeWHEREsalary>1ANDjob_id='C';SELECT*FROMemployeeWHEREjob_idNOTIN('Clerk','Manager');SELECT*FROMemployeeWHERE(job_id="A"ORjob_id='C')ANDsalary>1000;包含排序的查询语句排序关键字ORDERBYASC升序缺省DESC降序例子SELECT*FROMemployeesORDERBYhire_dateDESC;SELECT*FRO
16、MemployeesORDERBYhire_dateDESC,salaryASC;2.函数函数的基本格式Function_name(column
17、expression,[arg1,arg2,...])字符函数LOWER,UPPER,INITCAP,CONCAT,SUBSTR,LENGTH,INSTR,LPAD,RPAD,TRIM,REPLACE.LOW强制小写UPPER强制大学INITCAP首字母大写例子SELECT*FROMemployeesWHERELOWER(last_name)='blake';字符串连接CONCAT取子串SUBSTR找到子instrINSTR补位函数LPAD去除特殊
18、trimTRIMreplaceREPLACE例子SELECTreplace('oracleSQL','oracle','training')testFROMDUAL把oracleSQL中的oracle替换成trainingSELECTlast_name,CONCAT(last_namejob)CAN,LENGTH(last_name)LEN,INSTR(last_name,'A')INSFROMemploye
