SQL数据库语句3资料

--2015年1月27日--1having字句：对分组以后的数据再进行过滤--1)格式：--select列名/聚合函数--from表名--where条件(第一次对所有的数据过滤)--groupby列名--having条件2(第二次过滤：针对每一组的数据进行过滤)---orderby列名/别名--2)步骤：a)where条件-- b)groupby根据每一个列进行分组-- c)select列名/聚合函数，列名一般在groupby后面-- d)having对每一组进行过滤-- e)orderby对最终结果进行排序--案例：查询出版社不为null的所有的图书，每个出版社图书进货量总和，平均值，最大值，及所对应--的出版社的名字，过滤掉进货总量低于80000的信息，按照进货总量进行升序排列selectsum(numinput)进货量总和,avg(numinput)进货量平均值,max(numinput)进货量最大值,pubfrombookwherepubisnotnullgroupbypubhavingsum(numinput)>=80000orderby进货量总和--案例：查询进货量高于10000的所有的图书，求出每种类型图书的进货量总和超过27000的所有类型的图书的--进货量总和及库存量平均值，库存量最大值，类型名字，根据库存量的平均值进行降序排列selectsum(numinput)进货量总和,avg(numstore)库存量平均值,max(numstore)库存量最大值,typefrombookwherenuminput>10000groupbytypehavingsum(numinput)>27000orderbyavg(numstore)desc--案例：查询作者不为null,而且出版社不为null,每个出版社进货量的平均值超过120000的图书，显示每个出版社--进货量的平均值，库存量总和，库存量最小值，最后库存量的总和进行降序排列selectavg(numinput)进货量平均值,sum(numstore)库存量总和,min(numstore)库存量最小值frombookwhereauthorisnotnullandpubisnotnullgroupbypubhavingavg(numinput)>120000orderbysum(numstore)desc--2子查询(嵌套查询)：在一个查询的内部又包含了一个或多个查询--案例：进货量超过平均进货量的图书的名称，作者，类型，进货量--分析：首先取出平均进货量的值，然后求出平均进货量的图书的信息selectavg(numinput)frombook--83272selectbname,author,type,numinputfrombookwherenuminput>(selectavg(numinput)frombook)--案例：查询库存量超过清华大学出版社所有图书中最低的库存量，显示其编号，书名，作者，库存量，进货量selectbid,bname,author,numstore,numinputfrombookwherenumstore>(selectmin(numstore)frombookwherepub='清华大学出版社’--案例：查询图书的类型和郭敬明所写图书类型一致的编号，书名，类型，作者selectbid,bname,type,authorfrombookwheretype=（selecttypefrombookwhereauthor='郭敬明’）--主键约束--1）用于约束用于定义表中主键，主键是表中唯一确定每一条数据的标识符，其值不能为null，而且不能重复--2）一个表只能定义一个主键（主键约束）--3）主键的组成：可以由一个列或者多个列组合而成，多个列组合为一个主键，叫做联合主键，联合主键中--每一个列，都不能为nullcreatetableworker1（idintprimarykey,namevarchar（20）,）insertintoworker1values（1,,卫青'）select*fromworker1--反例：insertintoworker1values（1,'李广'）--4）设置主键约束的同时给主键约束起一个名字createtableworker2（idintconstraintw2PKprimarykey,namevarchar（20））insertintoworker2values（1,'英布'）select*fromworker2--反例：insertintoworker2values（1,,彭越'）--创建一个empJob表，用来保存emp中的主键--job表中的主键，用来保存员工（emp）跟职位（job）之间对应关系，主键是由eid和jid合在一起组成--notnull：非空，该列必须有值createtableempJob(eidintnotnull,jidintnotnull,salaryint,constraintejPKprimarykey(eid,jid))insertintoempJobvalues(1,1,100)insertintoempJobvalues(1,2,200)insertintoempJobvalues(2,1,300)--反例：insertintoempJobvalues(1,1,500)--eid和jid的值不能完全一致createtableworker3(idintnotnull,namevarchar(20),constraintw3PKprimarykey(id))--5)在表的外界来设置该表的主键--altertable表名--addconstraint约束名primarykey(列名)--案例：给book2中bid设置为主键altertablebook2addconstraintb2PKprimarykey(bid)--案例：给book3，book4，book5分别添加主键altertablebook3addconstraintb3PKprimarykey(bid)altertablebook4addconstraintb4PKprimarykey(bid)altertablebook5addconstraintb5PKprimarykey(bid)--4唯一约束：表中某些字段取值必须是唯一的不能重复，在这些字段上加入唯一约束unique--1）使用唯一约束修饰的列，该列可以出现null，但是只能出现一次--2)一个表中可以出现多个唯一约束--3)唯一约束不能修饰主键所在的列--4)可以把多个列都用同一个唯一约束修饰--5)唯一约束跟主键的区别--a)一个表中只可以定义一个主键约束，但可以定义多个唯一约束--b)唯一约束修饰的列，可以为null，主键约束所在的列，永远不能为nullcreatetableworker4(idintprimarykey,namevarchar(20)unique,emailvarchar(20)unique,)insertintoworker4values(1,,李白','bai@163.com')--反例：insertintoworker4values(2,'李白,，'bai@163.com')insertintoworker4values(2,null,null)--反例：insertintoworker4values(3,null,null)select*fromworker4--6在表的外界加入一个唯一约束--altertable表名--addconstraint约束名unique(列名)--案例：在book中bname设置唯一约束altertablebookaddconstraintbk_name_uqunique(bname)--案例：book2中bname，author都用同一个唯一约束altertablebook2addconstraintbk_bname_authorunique(bname,author)--7删除唯一约束--altertable表名--dropconstraint约束名字altertablebookdropconstraintbk_name_uqunique(bname)--5notnull：表中被notnull修饰的列，必须有值，而且一张表中可以有多个notnullcreatetableworker6（idintprimarykey,namevarchar（20）uniquenotnull）insertintoworker6values（1,'张居正'）--反例：insertintoworker6values（2,null）--6检查约束：对输入的列值设置输入的条件，满足条件才让输入，反之不让输入，限制输入的值createtableworker7（idintprimarykey,namevarchar（20）notnullunique,sexchar（2）check（sexin（'男','女'）），emailvarchar（50）notnull,）insertintoworker7values（1,,李成梁','男','liang@126.com'）select*fromworker7--反例：insertintoworker7values（2,'贾似道','a','jia@126.com'）--1）表外界添加检查约束--案例：在worker7中email添加一个检查约束，要求必须包含@altertable表名addconstraint约束名check（约束条件）altertableworker7addconstraintch_worker7_emailcheck（emaillike'%@%'）--2）删除检查约束--altertabledropconstraint约束名altertableworker7dropconstraintch_worker7_email--案例：创建一个Customer表，bid（int）主键，name（varchar（50）非空，唯一），password（varchar（50）非空）--age（int检查要求年龄必须是6~70之间，非空），sex（char（2），要求加入检查约束，非空）--email(varchar(50)，必须包含@)createtableCustomer]bidintprimarykey,namevarchar(50)notnullunique,passwordvarchar(50)notnull,ageintnotnull,sexchar(2)notnull,emailvarchar(50)constraintCK_Customer_emailcheck(emaillike'%@%'))altertableCustomeraddconstraintCK_Customer_agecheck(agebetween6and70)altertableCustomeraddconstraintCK_Customer_sexcheck(sexin('男','女'))--7默认值约束(default)--默认值约束：在指定的列没有插入对应的数值，系统自动指定数值--1)每个列只能定义一个默认值约束--2)不能对应identity修饰的列不能使用默认值约束--3)默认值可以是常量，函数，null(少用)等createtableworker8(idintprimarykey,namevarchar(50)notnullunique,ageintdefault25,)insertintoworker8values(2,'白居易',null)insertintoworker8(id,name)values(1,'苏轼')select*fromworker8insertintoworker8values(3,'曹操',46)--getdate()：获得当前系统的时间--案例：创建worker9，入职时间(hiredate)定义为datetime，默认值通过datetime()获得当前系统时间’createtableworker9(idintprimarykey,namevarchar(50)notnullunique,ageint,hiredatedatetimedefault(getdate()))insertintoworker9(id,name,age)values(1,,柴荣',35)select*fromworker9--4)在外界给表添加一个默认值约束--altertableworker9--addconstraint约束名--default数值for列名--案例：给worker9,age追加一个默认值为30altertableworker9addconstraintage_defdefault30forageinsertintoworker9(id,name)values(2,'赵匡胤')select*fromworker9--5)删除默认值约束--altertable表名--dropconstraint约束名altertableworker9dropconstraintage_def--案例：创建student,id(int,pk)，name(varchar(50),非空，唯一)，age(int检查约束：6~30，默认值：20)--email(varchar(50),非空，检查约束：必须有@)--hiredate(datetime,默认值是当前系统的时间)--address(varchar(50)，非空，唯一)createtablestudent(idintprimarykey,namevarchar(50)notnullunique,ageintdefault20check(agebetween6and30),emailvarchar(50)notnullcheck(emaillike'%@%'),hiredatedatetimedefault(getdate()),addressvarchar(50)notnullunique)--book表--案例：查询进货量超过漫画类进货量平均值的图书的编号，名字，作者，进货量selectbid,bname,author,numinputfrombookwherenuminput>(selectavg(numinput)frombookwheretype='漫画类’)应Telnet172,17.12,6login:openlabPassword:Loginincorrectlogin:openlabPassword-login:WedJan2808=51=43from01Mlcposi/steiTisInc.SunOS5-10GenericJanuary2S@5havenewinail-^bash-3.00$^qlplusSQL^Plms=Release.9一PraduetianonJedJan2811£08:392S15Cop^ricfh-t<c>1982,23H6.Opac1b.fl11 Reserved.(Entci*user—ima.irtesahtsell411Entci* "Connectedto-O^acleD-at-abase10<jEnterpi'iseEditionRelease10B2B2B0一Pi'cducticinWiththePai'titioniniOLftPandDataMining-optionsSQL>s--20150128--1外键约束:用来维护多张表的关联关系,在其中的一张表上添加一列--该列的取值，是与之关联的另一张表主键的值，这个列就是外键，--如何设置外键就是外键约束--外键维护两种表之间关联关系--外键所在的表示子表，没有外键的表示父表--1）子表:外键所在的表 父表:没有外键的表-----父表的数据必须要先于子表的数据而存在--2）外键的写法：--foreignkey:外键---a）在表外来指定外键：--altertable子表名--addconstraint约束名--foreignkey（列名:外键所在的列）--references父表名（主键所在的列）--父表createtabledept（didintprimarykey,dnamevarchar（50）notnullunique,locvarchar（50）notnullunique,）--子表createtableemp（eideintprimarykey,enamevarchar（50）notnullunique,salintnotnull,deptIdint）altertableempaddconstraintemp_dept_FKforeignkey（deptId）referencesdept（did）insertintoempvalues（1,'关羽',100,1）--反例要先有父类，才能有子类--报错:INSERT语句与FOREIGNKEY约束"emp_dept_FK"冲突。--该冲突发生于数据库"library"，表"dbo.dept",column'did'。insertintodeptvalues（1,'财务','上海'）insertintodeptvalues（2,'人事','北京'）insertintodeptvalues（3,'研发','深圳'）insertintoempvalues（1,'关羽',100,2）insertintoempvalues（2,'张飞',80,1）insertintoempvalues（3,'黄忠',70,2）insertintoempvalues（4,'魏延',79,2）insertintoempvalues（5,'关兴',69,3）insertintoempvalues（6,'张任',70,1）select*fromdeptselect*fromemp--b)表的内部指定外键：--constraint约束名--foreignkey(列名:指定外键的列)--references主表(主键的值)--案例:创建emp2,dept2在emp2中指定dept2中指定--deptId作为外键,emp2和dept2的结构域emp和dept—样createtabledept2(didintprimarykey,dnamevarchar(50)notnullunique,locvarchar(50)notnullunique,)createtableemp2(eideintprimarykey,enamevarchar(50)notnullunique,salintnotnull,deptIdint,constraintemp2_dept2_FKforeignkey(deptId)referencesdept2(did))--子查询(二)--案例：查询上海地区所有员工的姓名--1)找到上海地区的部门的编号(did)selectdidfromdeptwhereloc='上海’--2)根据did-->deptId，根据deptId值找到对应的员工selectename,salfromempwheredeptIdin(selectdidfromdeptwhereloc='上海’--案例：查询跟关羽有关在同一个部门的工作的员工的信息--1)找到关羽所在的部门(deptId)selectdeptIdfromempwhereename='关羽’--2)根据deptId查询所有员工的信息select*fromempwheredeptId=(selectdeptIdfromempwhereename='关羽’)select*fromempselect*fromdept--案例：超过财务部门最高的工资的员工，所在的部门名字及地址--分析：1)财务部门最高的工资selectdidfromdeptwheredname='财务’--2)超过最高工资员工的编号selecteidefromdeptwheresal>(selectmax(sal)fromempwheredeptId=(selectdidfromdeptwheredname='财务’))--3)根据返回员工编号找到对应的部门编号selectdeptIdfromempwhereeidein(selecteidefromdeptwheresal>(selectmax(sal)fromempwheredeptId=(selectdidfromdeptwheredname='财务’)))--4)根据部门编号找到对应部门名字及地址selectdname,locfromdeptwheredid=(selectdeptIdfromempwhereeidein(selecteidefromdeptwheresal>(selectmax(sal)fromempwheredeptId=(selectdidfromdeptwheredname='财务’))))--案例：查询‘张飞’所在部门的地址及名称--分析：)‘张飞’所在部门的编号selectdeptIdfromempwhereename='张飞’--2)查询该部门的地址和名称selectloc,dnamefromdeptwheredid=(selectdeptIdfromempwhereename='张飞’)--视图：视图是一个虚拟表，可以利用视图来简化我们的查询(可以通过一个视图查看一张或多张表的数据）--1）视图只是用来查看数据，不能用来保存数据，删除视图，对表中数据没有任何影响--2）视图可以简化查询操作，可以在视图中根据需要集中，简化，定制用户需要查询数据，从而不必访问表--格式：--createview视图名--as--查询语句--案例：创建一个视图book_view，显示进货量在~200000之间所有的图书的编号，书名，作者，进货量，库存量selectbid,bname,author,numinput,numstorefrombookwherenuminputbetween30000and200000createviewbook_view1asselectbid,bname,author,numinput,numstorefrombookwherenuminputbetween30000and200000select*frombook_view1--根据book_view1，查询库存量在到之间所有作者不为null的图书，显示书名，作者，进货量selectbnameauthornuminputfrombook_view1whereauthorisnotnull--案例：创建视图book_view2，包含book表中书名，作者，类型，出版社，库存量，而且作者，类型，出版社都不为空createviewbook_view2asselectbname,author,type,pub,numstorefrombookwhereauthorisnotnullandtypeisnotnullandpubisnotnullselect*frombook_view2--4）删除视图：dropview视图名