数据库技术：数据库SQL---查询

作者:xqy1874更新于： 2020-05-18 09:40:22

　　SQL语言，是结构化查询语言(StructuredQueryLanguage)的简称。SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

　　1、查询所有列

　　select*fromemp;--*表示所有的，fromemp表示从emp表中查询。

　　2、查询指定列

　　selectempno,enamefromemp;

　　select888fromemp;--ok,输出的行数是emp表的行数，每行只有一个字段，值是888。

　　select5；--OK，不推荐。

　　3、消除重复元祖：distinct

　　selectdistinctdeptnofromemp;--distinctdeptno会过滤掉重复的deptno，也可以过滤掉null，即如果有多个null只输出一个。

　　selectdistinctcomm,deptnofromemp;--把comm和deptno的组合进行过滤。

　　selectdeptno,distinctcommfromemp;--error，逻辑上有冲突。

　　4、给属性列取别名：as

　　selectename,sal*12as"年薪"fromemp;--as可以省略。

　　5、查询经过计算的列

　　selectename,sal*12as"年薪"fromemp;--as可以省略。

　　lower()将大写字母改为小写字母；upper()将字符串转换为大写字母。

　　6、比较运算：>,>=,<,<=,!=(<>),=（等值连接）

　　select*fromempwheresal>=1500andsal<=3000;--查找工资在1500到3000之间含两者的所有员工的信息。

　　select*fromempwheresal<>1500andsal<>3000andsal<>5000----把sal既不是1500也不是3000也不是5000的记录输出，数据库中不等于有两种表示：！=<>推荐使用第二种，对或取反是并且，对并且取反是或。

　　7、范围查询：between...and;notbetween...and

　　select*fromempwheresalbetween1500and3000--查找工资在1500到3000之间含两者的所有员工的信息。

　　select*fromempwheresalnotbetween1500and3000--查找工资在1500到3000之间不含两者的所有员工的信息。

　　8、集合查询：in（属于若干个孤立的值）

　　select*fromempwheresalin(1500,3000,5000);

　　select*fromempwheresalnotin(15000,3000,5000);--把sal既不是1500也不是3000也不是5000的记录输出

　　9、空值查询：null（没有值，空值）

　　1）零和null是不一样的，null表示空值，没有值，零表示一个确定的值。

　　2）null不能参加的运算：<>!==

　　3）null可以参与的运算：isnotis

　　select*fromempwherecommisnull;---输出奖金为空的员工信息

　　select*fromempwherecommisnotnull;---输出奖金不为空的员工信息

　　select*fromempwherecomm<>null;---错，输出为空

　　select*fromempwherecomm!=null;---错，输出为空

　　select*fromempwherecomm=null;---错，输出为空

　　4）任何类型的数据都允许为null

　　createtablet1(namenvarchar(20),cntint,riqidatetime);

　　insertintot1values(null,null,null);---正确

　　5）任何数字与null参与数学运算的结果永远是null

　　---输出每个员工的姓名年薪（包含奖金）comm假设是一年的奖金。

　　selectempno,ename,sal*12+comm"年薪"fromemp;---错，null不能参与任何数据运算否则结果为空。

　　---正确的写法：

　　selectename,sal*12+isnull(comm,0)"年薪"fromemp;---isnull(comm,0)如果comm是null就返回零否则返回comm的值。

　　10、字符匹配查询（模糊查询）

　　1）格式：select字段的集合from表名where某个字段的名字like匹配的条件。匹配额条件通常含有通配符。

　　2）通配符：

　　（1）%---表示任意0个或多个字符

　　select*fromempwhereenamelike'%A%'---ename只要含有字母A就输出。

　　select*fromempwhereenamelike'A%'---ename只要首字母为A就输出。

　　select*fromempwhereenamelike'%A'---ename只要尾字母为A就输出。

　　（2）_（下划线）---表示任意单个字符

　　select*fromempwhereenamelike'_A%'---ename只要第二个字母为A就输出。

　　[a-f]---表示a到f中的热任意单个字符，只能是abcdef中的任意一个字符

　　select*fromempwhereenamelike'_[A-F]%'---把ename中第二个字符是A或B或C或D或E或F的记录输出

　　[a,f]---表示a或f

　　[^a-c]---表示不是a也不是b也不是c的任意单个字符

　　select*fromempwhereenamelike'_[^A-F]%'---把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是F的记录输出

　　（3）匹配的条件必须用单引号括起来，不能省略，也不能改用双引号

　　（4）通配符作为不同字符使用的问题

　　预备操作：createtablestudent

　　(namevarchar(20)null

　　,ageint);

　　insertintostudentvalues('张三',88);

　　insertintostudentvalues('tom',66);

　　insertintostudentvalues('a_b',22);

　　insertintostudentvalues('c%d',44);

　　insertintostudentvalues('abc_fe',99);

　　insertintostudentvalues('haobin',77);

　　insertintostudentvalues('HaoBin',55);

　　insertintostudentvalues('c%',33);

　　insertintostudentvalues('long''s',100);

　　select*fromstudent;

　　select*fromstudentwherenamelike'%\\%%'escape'\\'---把name中包含有%的输出

　　select*fromstudentwherenamelike'%\\_%'escape'\\'---把name中包含有_的输出

　　11、逻辑查询：andornot

　　select*fromempwheresal=1500orsal=3000orsal=5000;

　　12、排序运算：orderby（以某个字段排序），asc是升序默认可以不写，desc是降序

　　1）orderbya,b---a和b都是升序，如果不指定排序的标准，则默认是升序，升序用asc表示，默认可以不写。

　　2）orderbya,bdesc---a升序，b降序，为一个字段指定的排序标准并不会对另一个字段产生影响。

　　3）orderbyadesc,b---a降序，b升序

　　4）orderbyadesc,bdesc---a和b都降序，建议为每个字段指定排序的标准。

　　5）例子：asc是升序的意思默认可以不写，desc是降序

　　select*fromemporderbysal;--默认升序排列

　　select*fromemporderbydeptno,sal;---先按照deptno升序排列，如果deptno相同，再按照sal升序排列

　　select*fromemporderbydeptnodesc,sal;---先按照deptno降序排列，如果deptno相同，再按照sal升序排列。desc只对deptno产生影响不会对后面的sal产生影响。

　　select*fromemporderbydeptno,saldesc;---先按照deptno升序排列，如果deptno相同，再按照sal降序排列，desc只对sal产生影响不会对deptno产生影响。

　　13、聚合查询（多行记录返回一个值，通常用于统计分组的信息）

　　1）函数的分类：

　　（1）单行函数：每一行返回一个值

　　（2）多行函数：多行返回一个值

　　（3）聚合函数是多行函数

　　selectlower(ename)fromemp;---最终返回的是行lower()是单行函数

　　selectmax(sal)fromemp;---返回行max()是多行函数

　　2）聚合函数分类：

　　（1）max()

　　（2）min()

　　（3）avg()---平均值

　　（4）count()---求个数

　　count(*)---返回表中所有记录的个数

　　selectcount(*)fromemp;---返回emp表所有记录的个数

　　count(字段名)---返回字段值非空的记录的个数，重复的记录也会被当做有效的记录

　　selectcount(deptno)fromemp;---deptno重复的记录被当做有效的记录

　　selectcount(comm)fromemp;---comm为null的记录不会被当做有效的记录

　　count(distinct字段名)---返回字段不重复并且非空的记录的个数

　　selectcount(distinctdeptno)fromemp;---统计deptno不重复的记录的个数

　　3）注意的问题：

　　selectmax(sal),min(sal),count(*)fromemp;---正确

　　selectmax(sal)"",min(sal)"",count(*)""fromemp;---正确

　　selectmax(sal),lower(ename)fromemp;---错误，单行函数和多行函数不能混用

　　selectmax(sal)fromemp;---正确，默认把所有的信息当做一组

　　14、分组聚合

　　1）groupby

　　（1）格式：groupby字段的集合

　　（2）功能：把表中的记录按照字段分成不同的组。

　　（3）例子：查询不同部门的平均工资

　　selectdeptno,avg(sal)as"部门平均工资"fromempgroupbydeptno

　　（4）理解groupbya,b,c的用法：先按a分组，如果a相同，再按b分组，如果b相同，再按c分组，最终统计的是最小分组的信息。

　　（5）使用了groupby之后select中只能出现分组之后的整体信息，不能出现组内的详细信息。

　　2）having（对分组之后的信息进行过滤）

　　（1）having子句是用来对分组之后的数据进行过滤，因此使用having时通常会先使用groupby。

　　（2）如果没使用groupby但使用了having，则意味着having把所有的记录当做一组来进行过滤，极少用。

　　selectcount(*)fromemphavingavg(sal)>1000

　　（3）having子句出现的字段必须是分组之后的组的整体信息，不允许出现组内的详细信息。

　　（4）尽管select字段中可以出现别名，但having子句中不能出现字段的别名，只能使用字段最原始的名字。

　　（5）having和where的异同

　　相同：都是对数据进行过滤，只保留有效的数据；都不允许出现字段的别名，只允许出现最原始的字段的名字。

　　不同：where是对原始的记录过滤，having是对分组之后的记录过滤。

　　where必须写在having前面，顺序不可颠倒，否则运行出错。

　　例子：把工资大于2000，统计输出部门平均工资大于3000的部门的部门编号、部门的平均工资

　　selectdeptno,avg(sal)"平均工资",count(*)"部门人数",max(sal)"部门的最高工资"

　　fromempwheresal>2000---where是对原始记录进行过滤

　　groupbydeptnohavingavg(sal)>3000---对分组之后的记录进行过滤

　　其中不可以将where写在having后面

　　15、连接查询

　　1）定义：将两个表或者两个以上的表以一定的连接条件连接起来，从中检索出满足条件的数据。

　　2）分类：

　　（1）内连接

　　select...fromA,B的用法

　　产生的结果：行数是A和B的乘积，列数是A和B之和，即把A表的每一条记录都和B表的每一条记录组合在一起，形成笛卡尔积，即把B表的每一条记录都和A表的每一条记录组合在一起，形成笛卡尔积。

　　注意：select*fromA,B输出结果和select*fromB,A一样，即AB可以互换。

　　select...fromA,Bwhere...的用法

　　产生的笛卡尔积，用where中的条件进行过滤

　　select...fromAjoinBon...的用法

　　join是连接，AB互换结果不变。

　　SQL92和SQL99标准的区别

　　select...fromA,Bwhere...是SQL92标准

　　select...fromAjoinBon...是SQL99标准

　　输出结果一样

　　推荐使用SQL99标准：容易理解；on和where可以做不同的分工：on指定连接条件，where对连接之后临时表的数据进行过滤。

　　例如：把工资大于2000的员工的姓名和部门的名称输出和工资的等级

　　SQL99标准

　　select"E".ename,"D".dname,"S".gradefromemp"E"joindept"D"

　　on"E".deptno="D".deptnojoinsalgrade"S"

　　on"E".sal>="S".losaland"E".sal<="S".hisal

　　where"E".sal>2000

　　SQL92标准

　　select"E".ename,"D".dname,"S".gradefromemp"E",dept"D",salgrade"S"

　　where"E".sal>2000and"E".deptno="D".deptnoand("E".sal>="S".losaland"E".sal<="S".hisal)

　　（2）自连接：一张表自己和自己连接起来查询数据。

　　select*fromempa,empbwhereb.ename='张三'anda.deptno=b.deptno;--在员工表中查找与张三在同一个部门的员工的信息。

　　（3）外连接

　　select*fromemp,deptwhereemp.deptno=dept.deptno;

　　（4）左外连接

　　select*fromempleftouterjoindeptonemp.deptno=dept.deptno;

　　（5）右外连接

　　select*fromemprightouterjoindeptonemp.deptno=dept.deptno;

　　（6）全外连接

　　select*fromempfullouterjoindeptonemp.deptno=dept.deptno;

　　16、联合：表和表之间的数据以纵向的方式连接在一起，前面均是横向连接在一起。

　　1）例子：输出每个员工的姓名、工资、上司的姓名

　　select"E1".ename,"E1".sal,"E2".ename"上司的姓名"fromemp"E1"joinemp"E2"

　　on"E1".mgr="E2".empno

　　union

　　selectename,sal,'已是最大老板'fromempwheremgrisnull

　　2）若干个select子句要联合成功的话，必须满足两个条件：

　　（1）若干个select子句输出的列数必须是相等的；

　　（2）若干个select子句输出列的数据类型至少是兼容的。

　　17、top（最前面的若干个记录，专属于SqlServer的语法，不可移植到其他数据库）

　　selecttop5*fromemp;

　　selecttop15percent*fromemp;

　　selecttop5fromemp;---错的

　　18、复杂查询：select\\from\\where\\join\\on\\group\\order\\top\\having的混合使用

　　1）查询的顺序：

　　selecttop...

　　fromA

　　joinB

　　on...

　　joinC

　　on...

　　where...

　　groupby...

　　having...

　　orderby...

　　2）例子：把工资大于1500的所有员工按部门分组把部门平均工资大于2000的最高的前2个部门的编号、部门的名称、部门平均工资的等级

　　（1）第一种写法：

　　select"T".*,"D".dname,"S".gradefromdept"D"

　　join(selecttop2"E".deptno,avg(sal)"avg_sal"fromemp"E"joindept"D"

　　on"E".deptno="D".deptnojoinsalgrade"S"

　　on"E".salbetween"S".losaland"S".hisal

　　where"E".sal>1500

　　groupby"E".deptno

　　havingavg("E".sal)>2000

　　orderbyavg("E".sal)desc

　　)"T"

　　on"D".deptno="T".deptnoinnerjoinsalgrade“S”

　　on"T"."avg_sal"between"S".losaland"S".hisal

　　（2）第二种写法：

　　select"T".*,"D".dname,"S".gradefromdept"D"

　　join(selecttop2"E".deptno,avg(sal)"avg_sal"fromemp

　　wheresal>1500

　　groupbydeptno

　　havingavg(sal)>2000

　　orderby"avg_sal"desc

　　)"T"

　　on"D".deptno="T".deptnojoinsalgrade“S”

　　on"T"."avg_sal"between"S".losaland"S".hisal

　　19、分页查询

　　假设每页显示n条记录，当前要显示的是第m页，表名是A，主键是A_id

　　selecttopn*fromAwhereA_idnotin(selecttop(m-1)*nA_idfromemp);

　　20、嵌套子查询

　　1）使用in的子查询

　　selectenamefromempwheredeptnoin(selectdeptnofromdept);

　　2）使用比较运算符的子查询

　　selectempno,enamefromwheresal>=all(selectsalfromsalwhereename='张三');

　　3）使用存在量词exists的子查询

　　selectenamefromempwhereexists(select*fromdept);

　　SQL语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统可以使用相同的结构化查询语言作为数据输入与管理的接口。SQL语言语句可以嵌套，这使他具有极大的灵活性和强大的功能。