Sql语句学习笔记

先登陆服务器：telnet192.168.0.23公帐号：openlab-open123tarena-tarena再进入SQL:sqlplussd0807/sd0807帐号：sd0807密码同样公帐号:openlab-open123设置环境变量：ORACLE_SID=oral10gexportORACLE_SIDunsetORACLE_SIDORACLE_HOME=.-变局部变量-变全局变量-卸载环境变量-安装路径；直接用一句语句也可以，如下exportORACLE_HOME=/oracledata/./bin:一、注意事项：大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调式。“！”在SQL环境下执行Unix命令。SQL语句是由简单的英语单词构成；这些英语单词称为关键字/保留字，不做它用SQL由多个关键字构成。SQL语句由子句构成，有些子句是必须的，有些是可选的。在处理SQL语句时，其中所有的空格都被忽略（空格只用来分开单词，连续多个空格当一个用）。SQL语句可以在一行上写出，建议多行写出，便于阅读和调试。多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号，但特定的DBMS可能必须在单条SQL语句后加分号。SQL语句的最后一句要以“；”号结束、写子句顺序Selectcolumn,group_functionFromtableWhereconditionGroupbygroup_by_expressionHavinggroup_conditionOrderbycolumn;-最后三、常用简单语句：clearscreen:清屏edit:编辑刚才的一句。desc/describe：（列出所有列名称）用法:DESCRIBEschema.objectdb_linkdual：亚表，临时用。如：descdual；/fromdual；rollback：回溯，回溯到上次操作前的状态，把这次事务操作作废，只有一次（DDL和DCL语句会自动提交，不能回溯）。可以用commit语句提交，这样就回溯不回了。setpauseonoff：设置分屏（设置不分屏）setpausepleaseputanenterkey且setpauseon：设置带有提示的分屏oerrora904：查看错误setheadoff：去掉表头setfeedoff：去掉表尾保存在oracle数据库中的所有操作细节：spooloracleday01.txt：开始记录spooloff：开始保存细节四、SELECT语句：选择操作、投影操作。select:从一个或多个表中检索一个或多个数据列。包含信息：想选择什么表，从什么地方选择。必须要有From子句。（最常用）当从多张表里查询的时候，会产生笛卡尔积；可用条件过滤它。当两个表有相同字段时必须加前缀，列名前需加表名和“”，如“s_emp.id”。1、用法：SELECTcolumns,prod2,prod3yUFROMTable1,table2名分号结束如：selectidfroms_emp;selectlast_name,namefroms_emp,s_deptwheres_emp.dept_id=s_dept.id;-列表每人所在部门SELECT*FROMProducts;-检索所有列。数据太多时，最好别使用上句，会使DBMS降低检索和应用程序的性能。（*通配符）2、对数据类型的列可进行运算（如加减乘除）。3、对列起别名：有直接起别名，加AS起别名，用双引号起别名等三种方法（单引号，引起字符串；双引号，引起别名。起别名有符号，或者区分大小写时，必须用双引号）多表查询时，可给表起别名。（给列起别名，列空格列别名；给表起别名，表空格表别名；）。如：Selectfirst_nameEMPLOYEES,12*（salary+100）ASMONEY,manager_idID1Froms_empE;4、字段的拼接，可用双竖线（双竖线只能用于select语句里）。不同的DBMS可能使用不同的操作符;拼接的字段同样可以起别名。如：Selectfirst_name|last_name|,|titleEmployeesFroms_emp;排他锁：Selectid,salaryFroms_empwhereid=1ForUpdate;可以阻止他人并发的修改，直到你解锁。如果已有锁则自动退出：Selectid,salaryFroms_empwhereid=1ForUpdateNoWait;FORUPDATE：可以再加OF精确到某格。如：.ForUpdateOFsalary.注意要解锁。五、ORDERBY子句，排序Orderby：按某排序列表（默认升序asc，由低到高；可加desc，改成降序由高到低）检索返回数据的顺序没有特殊意义，为了明确地排序用SELECT语句检索出的数据，可使用ORDERBY子句。ORDERBY子句取一个或多个列的名字。对空值，按无穷大处理（升序中，空值排最后；降序中排最前）。1、用法：Selectprod_id,prod_price,prod_nameFromProductsOrderByprod_price,prod_name;（从左到右执行排序，先排price）ORDERBY子句中使用的列将是为显示所选择的列，但是实际上并不一定要这样，用非检索的列排序数据是完全合法的。为了按多个列排序，列名之间用逗号分开。2、支持按相对列位置进行排序。输入SELECTprod_id,prod_price,prod_nameFROMProductsORDERBY2，3-（2指price，3指name）3、升序、降序。默认是升序（asc，从小到大排序），想降序时用desc。女口：SELECTprod_id,prod_price,prod_nameFROMProductsORDERBYprod_priceDESC;注意：DESC关键字只应用到直接位于其前面的列名。如果想在多个列上进行排序，必须对每个列指定DESC关键字。升序是默认的，可不写，但降序必须写。六、WHERE子句，选择、过滤其后只能跟逻辑语句，返回值只有ture或false女口：selectlast_name,salaryfroms_empwheresalary=1000;-找出工资1000的人WHERE子句操作符：1、逻辑比较运算符一殆丁一等于!=不等于，还有（心这两个同样表示不等于）大于=大于等于小于=小于等于2、SQL比较运算符betweenand：在两者之间。（BETWEEN小值AND大值）如：selectlast_name,salaryfroms_empwheresalarybetween1000and1500；-工资1000到1500的人，包括1000和1500。in（列表）：在列表里面的。女口：selectlast_name,dept_idfroms_empwheredept_idin（41,42）;第41、42部门的人like：包含某内容的。模糊查询可以利用通配符创建比较特定数据的搜索模式，通配符只能用于文本，非文本数据类型不能使用通配符。通配符在搜索模式中任意位置使用，并且可以使用多个通配符。通配符%表示任何字符出现任意次数；还能代表搜索模式中给定位置的0个或多个字符。下划线匹配单个任意字符。如：selecttable_namefromuser_tableswheretable_namelikeS_%escape;找出“S_“开头的,由于下划线有任意字符的含义，故需另外定义转移符。但习惯用“”，为方便其他程序员阅读和检测，一般不改用其他的。likeM%：M开头的like_a%:第二个字符是a的like%a%所有含a的（“_”表示一个任意字符；“%”表示任意多个任意字符。）单引号里面的内容，大小写敏感。单引号用来限定字符串，如果将值与串类型的列进行比较，则需要限定引号；用来与数值列进行比较时，不用引号。isnull：是空。（NULL表示不包含值。与空格、0是不同的。）如：SELECTprod_name,prod_priceFROMProductsWHEREprod_priceISNULL;七、高级检索（逻辑运算符）：通常我们需要根据多个条件检索数据。可以使用AND或OR、NOT等连接相关的条件计算次序可以通过圆括号（）来明确地分组。不要过分依赖默认计算次序，使用圆括号（）没有坏处，它能消除二义性。and：条件与如SELECTprod_id,prod_price,prod_nameFROMProductsWHEREprod_price4ANDvend_id=DELLor：条件或（注：and的优先级比or更高，改变优先级可用括号）如SELECTprod_id,prod_price,prod_nameFROMProductsWHEREprod_priceheoArefdou输入：selecttrim(heoArefdou)fromdual;-：heoArefdouLOWER:转小写lower(SQLCourse)-sqlcourseUPPER:转大写upper(SQLCourse)-SQLCOURSEINITCAP：首字母转大写，其余转小写initcap(SQLCourse)-SqlCourseCONCAT:合成。双竖线只能在select语句里面用，这个可用于任何语句。Concat(Good,String)-GoodStringSUBSTR：截取。Substr(String,1,3)-Str第一个数字“1”，表示从第几个开始截取；若要从倒数第几个开始，用负数，如“2”表示倒数第2个。上式中第2个数字“3”表示截取多少个。LENGTH:统计长度。Length(String)-6NVL：转换空值日期和时间处理：Oracle日期格式：DD-MMM-YYYY(D代表日期date，M代表月month，Y代表年year)女口：SELECTprod_name(DAY表示完整的星期几，DY显示星期的前三个字母)FROMProductsWHEREprod_timeBETWEENto_date(01-JAN-2008)ANDto_date(31-DEC-2008);日期可以进行加减，默认单位是1天。日期与日期可以相减，得出天数；日期与日期但不能相加。sysdate系统的当天Months_Between(01-Sep-95,11-Jan-94)-19.774194相差多少个月Between里面也可以填函数。Add_months(11-Jan-94,6)-11-Jul-94增加多少个月Next_day(01-Sep-95,Friday)-08-Sep-95下一个星期五。其中的Friday可用6替代，因为星期日=1Last_day(01-Sep-95)-30-Sep-95这个月的最后一天数值处理：可以运用于代数，三角，几何ROUND：四舍五入Round(45.925,2)45.93Round(45.925,0)46Round(45.925,-1)50逗号前一个数是要处理的数据源，后一个参数表示保留多少位小数。后一参数是负数时，表示舍去小数点前的几位，例3是舍去个位及其后的。不写后一参数时，默认不保留小数。TRUNC：舍去末位。直接舍去，不会进位。Trung(45.925,2)45.92Trung(45.925,2)45.92Trung(45.925,2)45.92日期的舍取：常用的数值处理函数有：ABS()绝对值ABS(-5741.5854)-5741.5854PI()圆周率注意：oracle中不支持PI()函数；MYSql支持PI()函数。SIN()正统值Oracle还支持COS()、ASIN()、ACOS()函数SQRT()平方根转化：TO_CHAR(number,fmt)：把数值转换成字符串显示数字的命令9：正常显示数字；0：显示包括0的数值形式，空位强制补0；$：以美元符号显示货币；L：按当前环境显示相关的货币符号；.和，：在固定位置出现“.”点和“，”逗号；不够位时，四舍五入例题：SQLselectOrder|To_char(id)|2 wasfilledforatotalof3 |To_char(total,fm$9,999,999)4 froms_ordwhereship_date=21-SEP-92;TO_NUMBER(char)：把字符转换成数字九、链接内链接：严格匹配两表的记录。外链接分左链接和右链接:会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。左链接加号在右面如：有TABLE1TABLE21的一条记录在2里面没有匹配上，那么1里面的记录保留2的一条记录在1里面没有匹配上，那么2丢弃右链接正好相反-例题：哪些人是领导。selectdistinctb.id,b.last_namemanagerfroms_empa,s_empbwherea.manager_id=b.id(+);左右顺序有区别，这是另外新建一个表，要显示的是第二个表格的内容。+放在没有匹配行的表一侧，令表格能完整显示出来。标准写法：内连接用INNER，左连接用LEFT，右连接用RIGHT。selectdistinctb.id,b.last_namemanagerfroms_empaLEFTjoins_empbONa.manager_id=b.id;十、组函数：分组允许将数据分为多个逻辑组，以便能对每个组进行聚集计算。Group：分组Groupby:分组。(默认按升序对所分的组排序；想要降序要用orderby)可以包括任意数目的列。如果嵌入了分组，数据将在最后规定的分组上进行汇总。GROUPBY子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数。*如果在SELECT中使用表达式，则必须在GROUPBY子句中指定相同的表达式，不能使用别名。除聚合计算语句外，SELECT语句中的每个列都必须在GROUPBY子句中给出。如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL，它们将分为一组。Having：过滤。分组之后，不能再用where，要用having选择过滤。Having不能单独存在,必须跟在groupby后面。WHERE在数据分组前进行过滤，HAVING在数据分组后过滤。可以在SQL中同时使用WHERE和HAVING，先执行WHERE，再执行HAVING。聚合函数：AVG：平均值COUNT:计数MAX：最大值(忽略值为NULL的行，但不能用AVG(*)(Count(列)不计算空值；但COUNT(*)表示统计表中所有行数，包含空值)(忽略列值为NULL的行。但有些DBMS还允许返回文本列中的最大值，行。)MIN:最小值小值。在作用于文本数据时，如果数据按照相应的列排序，则MAX()返回最后一(忽略值为NULL的行。不能用MIN(*)。一般是找出数值或者日期值的最但有些DBMS还允许返回文本列中的最小值，这时返回文本最前一行)SUM:求和(忽略值为NULL的值。SUM不能作用于字符串类型，而MAX()，MIN()函数能。也不能SUM(*)子查询：查询语句的嵌套可以用于任意select语句里面，但子查询不能出现orderby。子查询总是从内向外处理。作为子查询的SELECT语句只能查询单个列，企图检索多个列，将会错误。女口：找出工资最低的人selectmin(last_name),min(salary)froms_emp;或者用子查询selectlast_name,salaryfroms_empwheresalary=(selectmin(salary)froms_emp);E-R图：属性:E(Entity)-R(Relationship)*(Mandatorymarked强制的)强制的非空属性o(Optionalmarked可选的)可选属性(可以有值也可以没有)#*(Primarymarked)表示此属性唯一且非空约束：针对表中的字段进行定义的。PK：primarykey(主键约束，PK=UK+NN)保证实体的完整性，保证记录的唯一主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键(CompositePrimaryKey)。FK：foreignkey（外建约束）保证引用的完整性,外键约束,外键的取值是受另外一张表中的主键或唯一值的约束,不能够取其他值,只能够引用主键会唯一键的值，被引用的表，叫做parenttable（父表），引用方的表叫做childtable（子表）；childtable（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。U：uniquekey（唯一键UK），值为唯一，不能重复。在有唯一性约束的列，可以有多个空值，因为空值不相等。NN：NOTNULL，不能为空。index（索引）是数据库特有的一类对象，实际应用中一定要考虑索引，view（示图）数量关系:一对一关系多对一关系一对多关系多对多关系范式：好处：降低数据冗余；减少完整性问题；标识实体，关系和表第一范式（Firstnormalform：1Nf），每一个属性说一件事情。所有的属性都必须是单值，也就是属性只表示单一的意义。（记录可以重复，会有大量冗余，没有任何限制）第二范式（2N范式），最少有一个属性要求唯一且非空PK,其他跟他有关联（记录不可重复，但是数据可能会出现冗余）。第三范式（3N范式），非主属性只能依赖于主属性，不能依赖于其他非主属性。（解决数据冗余问题,不能存在推理能得出的数据）一般情况会做到第三范式。创建表：CreateTable表名(字段名1类型(数据长度default.)约束条件，字段名2类型(数据长度)约束条件)；建表的名称：必须字母开头；最多30字符；只能使用“AZ、az、09、_、$、#”；同一目录下不能有同名的表；表名不能跟关键字、特殊含意字符同样。如：createtablenumber_1(n1number(2,4),n2number(3,-1),n3number)；createtablet_sd0808(idnumber(12)primarykey,namevarchar(30)notnull)；MySQL的:createtablestudent(oidintprimarykey,ACTNOvarchar(20)notnullunique,BALANCEdouble);-MySQL的number类型分小类了，Oracle只有number，且MySQL的数值型不用定大小Oracle的:createtablet_ad(oidnumber(15)primarykey,ACTNOvarchar(20)notnullunique,BALANCEnumber(20);INSERT:插入(或添加)行到数据库表中的关键字。插入方式有以下几种：插入完整的行；插入行的一部分；插入某些查询的结果。对于INSERT操作，可能需要客户机/服务器的DBMS中的特定的安全权限。插入行(方式一)INSERTINTOproductsVALUES(2008,TV,222.22,US);依赖于表中定义的顺序，不提倡使用。有空值时需要自己补上。插入行(方式二)INSERTINTOproducts(id,name,price,vend_name)VALUES(2008,TV,222.22,US);依赖于逻辑顺序，会自动补上空值，提倡使用。插入检索出的数据:可以插入多条行到数据库表中INSERTINTOproducts(*,*,*,*)SELECT*,*,*,*FROMproducts_copy;如果这个表为空，则没有行被插入，不会产生错误，因为操作是合法的。可以使用WHERE加以行过滤。复制表:将一个表的内容复制到一个全新的表(在运行中创建，开始可以不存在)CREATETABLE新表名ASSELECT*FROM表名;INSERTINTO与CREATETABLEASSELECT不同，前者是导入数据，而后者是导入表。任何SELECT选项和子句都可以使用，包括WHERE和GROUPBY。可利用联接从多个表插入数据。不管从多少个表中检索数据，数据都只能插入到单个表中。更新数据UPDATE语句需要提供以下信息:要更新的表；列名和新值；确定要更新的哪些行的过滤条件。UPDATE表名SETvend_name=HP,prod_name=NEWCOMPUTERWHEREvend_name=IBM;-UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。也可以将一个列值更新为NULL。删除数据DELETE语句DELETEFROMproductsWHEREprod_name=COMPUTER;全行删除，不要省略WHERE，注意安全。DELETE不需要列名或通配符。删除整行而不是删除列。DELETE是删除表的内容而不是删除表。如果想从表中删除所有内容，可以使用TRUNCATETABLE语句（清空表格），它更快。数字字典表：Sequence:排列。存储物理地址Index:索引。依附于表，为提高检索速度。View:视图。看到表的一部分数据。限制数据访问。简化查询。数据独立性。本质上是一个sql查询语句。CreateorRelaceForce|noForceView视图名（alias,alias）别名列表Assubquery*WithCheckOption*Constraint+WithReadOnly注意:有些DBMS不允许分组或排序视图,不能有Orderby语句。可以有Select语句。删除视图：DROPVIEW视图名Rownum:纬列。内存里排序的前N个。在where语句中，可以用=1,和=N或N；但不能用=“或N。因为这是内存读取，没有1就丢弃再新建1。只能从1开始。需要从中间开始时，需二重子rownum语句需取别名。经典应用:Top-nAnalysis（求前N名或最后N名）Select查询列表,RownumFrom（Select查询列表（要对应）From表OrderbyTop-N_字段）WhereRownum=N分页显示:-取工资第510名的员工（二重子rownum语句，取别名）selectrn,id,last_name,salaryFrom（selectid,last_name,salary,RownumrnFrom（Selectid,last_name,salaryfroms_emporderbysalarydesc）whererownum=10）wherernbetween5and10;Union:合并表SelectUnionSelect把两个Select语句的表合并。要求两表的字段数目和类型按顺序对应。合并后的表，自动过滤重复的行。Intersect：交。同上例，把两个Select表相交。Minus：减。把相交的内容减去。notexists除运算。添加字段(列)：AlterTable表名Add(columndataypeDefaultexprNotNull,columndatatype.);添加有非空限制的字段时，要加Default语句字段名字不可以直接改名，需要添加新字段，再复制旧字段后删除旧字段。添加约束：AlterTable表名AddCONSTRAINTconstrainttype(column);添加非空约束时，要用Modify语句。查看约束名时，可以违反约束再看出错提示；或者查看约束字典descuser_constraints减少字段：AlterTable表名Drop(column,column.);删除约束：AlterTable表名DropCONSTRAINTcolumn;或：AlterTable表名DropPrimaryKeyCascade;暂时关闭约束，并非删除：AlterTable表名DisableCONSTRAINTcolumnCascade;打开刚才关闭的约束：AlterTable表名EnableCONSTRAINTcolumn;修改字段：AlterTable表名Modify(columndataypeDefaultexprNotNull,columndatatype.);修改字段的类型、大小、约束、非空限制、空值转换。删除表：会删除表的所有数据，所有索引也会删除，约束条件也删除，不可以rollback恢复。DropTable表名CascadeConstraints;力加CascadeConstraints把子表的约束条件也删除；但只加Cascade会把子表也删除。改表名：Rename原表名To新表名;清空表格：TRUNCATETABLE表名;相比Delete,TruncateTable清空很快，但不可恢复。清空后释放内存。Delete删除后可以rollback。清空后不释放内存。