第三章SQL查询与函数

SQL查询与函数一、SQL概述SQL（StructuredQueryLanguage)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。SQL语言主要包含5个部分数据定义语言DataDefinitionLanguage(DDL），用来建立数据库、数据对象和定义其列。例如：CREATE、DROP、ALTER等语句。数据操作语言DataManipulationLanguage(DML），用来插入、修改、删除、查询，可以修改数据库中的数据。例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句数据查询语言(DataQueryLanguage,DQL)是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。例如：SELECT（查询）数据控制语言DataControllingLanguage（DCL），用来控制数据库组件的存取允许、存取权限等。例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。事务控制语言（TransactionalControlLanguage，TCL），用于维护数据的一致性，包括COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）3条语句二、Oracle的数据类型类型参数描述字符类型Char1~2000字节固定长度字符串，长度不够的用空格补充varchar21~4000字节可变长度字符串，与CHAR类型相比，使用VARCHAR2可以节省磁盘空间，但查询效率没有char类型高数值类型Number(m,n)m(1~38)n(-84~127)可以存储正数、负数、零、定点数和精度为38位的浮点数，其中，M表示精度，代表数字的总位数；N表示小数点右边数字的位数日期类型Date7字节用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日，7个字节分别表示世纪、年、月、日、时、分和秒二进制数据类型Raw1~2000字节可变长二进制数据，在具体定义字段的时候必须指明最大长度nlongraw1~2GB可变长二进制数据LOB数据类型Clob1~4GB只能存储字符数据Nclob1~4GB保存本地语言字符集数据Blob1~4GB以二进制信息保存数据三、DDL语言1.Createtable命令用于创建表。在创建表时，经常会创建该表的主键、外键、唯一约束、Check约束等语法结构createtable表名([字段名][类型][约束]………..PRIMARYKEY(column1,column2),FOREIGNKEY(column1,column2,…..column_n),REFERENCEStablename(column1,column2,…..column_n))例子：createtablestudent(stuNochar(32)primarykey,--主键约束stuNamevarchar2(20)notnull,--非空约束cardIdchar(20)unique,--唯一约束sexchar(2)check(sex='男'orsex='女'),--检查约束addressvarchar2(100)default'地址不详'--默认约束);createtablemark(midintprimarykey,--主键约束stuNochar(32)notnull,courseNamevarchar2(20)notnull,--非空约束scorenumber(3)notnullcheck(score=0andscore=100),--非空约束,检查约束foreignkey(stuno)referencesstudent(stuNo)--表级外键约束);2.Altertable命令对已经存在的表进行修改，可以新增或删除字段，修改字段名或其类型和类型长度。修改表名altertableold_tablerenametonew_table修改字段名altertabletable_namerenamecolumnold_columntonew_column添加字段altertabletable_nameadd(new_columnvarhcar2(200)default‘null’,new_column2number)修改数据类型altertabletable_namemodifiy(filednamevarchar2(100))删除字段altertabletable_namedropcolumncolumn_name添加主键altertabletable_nameaddconstraintpk_nameprimarykey(column_id)删除主键altertabletable_namedropconstraintpk_name添加外键,检查,唯一约束altertabletable_nameaddconstraintfk_nameforeignkey(column_id)references主表(主键)添加检查约束Altertablestuaddconstraintck_snamecheck(length(sname)=2)添加唯一约束altertablestuaddconstraintuq_snameunique(sname)删除外键altertabletable_namedropconstraintfk_name3.Droptable命令用于从数据库中删除表及全部数据droptabletable_name[purge];--purge:清除缓存4.Truncatetable命令可以快速删除表的记录并释放空间，不使用事务处理，速度快且效率高，但无法回滚事务。truncatetabletable_name5.其他create命令CREATEINDEX：创建数据表索引CREATEPROCEDURE：创建存储过程CREATEFUNCTION：创建用户函数CREATEVIEW：创建视图CREATETRIGGER：创建触发程序CREATESEQUENCE6.SEQUENCE在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。创建语法CREATESEQUENCEsequence_marksINCREMENTBY1--每次加几个STARTWITH1--从1开始计数NOMAXVALUE--不设置最大值NOCYCLE--一直累加，不循环CACHE10--使序列号预分配,默认NOCACHE列子SELECTsequence_marks.currvalfromdualinsertintotable_namevalues(sequence_marks.nextval)删除dropsequencesequence_marks四、DML语言1.INSERT语句Insertintotable_name(column1,column2…….column_n)values(val1….)--插入多条insertinto表名(列名.....)select列名....from源表名insertintostuselect1,'abc'fromdualunionselect2,'abcd'fromdual;2.UPDATE语句updatetable_namesetcolumn1=value,….where[condition…]3.DELETE语句Deletefromtable_namewhere[condition…]五、运算符1.算术运算符[+,-,*,/,mod(10,3)]2.关系运算符[,=,,=,!=,,=]BETWEEN…AND、IN、LIKE和ISNULL3.逻辑运算符andornot4.集合操作符union,unionall,minus,INTERSECTunion:取出2个表数据合并，去除重复记录unionall:取出2个表数据合并，保留重复记录minus:取出2个表中不同数据Intersect:取出2个表中共有数据【交集】5.连接操作符||六、DQL语言1.概述基本语法SELECTcolumn_list[*查询所有数据][INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]]例子SQLselect*fromem--查询所有数据SQLselectename,jobfromem--查询指定的字段数据SQLselect*fromempwheresal1000--加条件2.聚合函数聚合函数对一组值执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与SELECT语句的GROUPBY子句一同使用。不能在WHERE子句中使用组函数。AVG(expression):返回集合中各值的平均值--查询所有人都的平均工资selectavg(sal)fromempCOUNT(expression):以Int32形式返回集合中的项数--查询工资低于2000的人数selectcount(*)fromempwheresal2000MAX(expression):返回集合中的最大值--查询最高工资selectmax(sal)fromempMIN(expression):返回集合中的最小值--查询最低工资selectmax(sal)fromempSUM(expression):返回集合中所有值的总和--查询部门编号为20的工资总和selectsum(sal)fromempwheredeptno=203.排序函数用于根据指定的列对结果集进行排序ORDERBY语句--查询所有信息并按工资排序select*fromemporderbysal升序(asc)--查询所有信息并按工资升序排序select*fromemporderbysalasc降序(desc)--查询所有信息并按工资降序排序select*fromemporderbysaldescROWNUM与ORDERBY【ROWNUM：伪列，只能使用,=,!=】4.分组函数分组函数作用于一组数据，并对一组数据返回一个值。GROUPBY子句--查询每个部门的平均工资selectavg(sal)fromempgroupbydeptnoHAVING子句--查询部门的平均工资大于2000selectavg(sal)fromempgroupbydeptnohavingavg(sal)20005.连接查询连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。内连接内连接也叫连接，是最早的一种连接。还可以被称为普通连接或--查询工资最高的5个人信息selecte.*,rownum编号from(select*fromemporderbysaldesc)ewhererownum=5【或rownum!=6】者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。等值连接:select*fromempinnerjoindeptonemp.deptno=dept.deptnoselect*fromemp,deptwhereemp.deptno=dept.deptno不等值连接:select*fromempinnerjoindeptonemp.deptno!=dept.deptno外连接外连接分为三种：左外连接，右外连接，全外连接。对应SQL：LEFT/RIGHT/FULLOUTERJOIN。通常我们省略outer这个关键字。写成：LEFT/RIGH