数据库SQL---查询

1、查询所有列

      select *from emp;--*表示所有的,from emp表示从emp表中查询。

2、查询指定列

      select empno,ename from emp;
      select 888 from emp;--ok,输出的行数是emp表的行数,每行只有一个字段,值是888。
      select 5;--OK,不推荐。
3、消除重复元祖:distinct

      select distinct deptno from emp;--distinct deptno会过滤掉重复的deptno,也可以过滤掉null,即如果有多个null只输出一个。
      select distinct comm,deptno from emp;--把comm和deptno的组合进行过滤。

      select deptno,distinct comm from emp;--error,逻辑上有冲突。

4、给属性列取别名:as

      select ename,sal*12 as "年薪" from emp;--as可以省略。

5、查询经过计算的列

      select ename,sal*12 as "年薪" from emp;--as可以省略。

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

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

      select * from emp where sal>=1500 and sal<=3000;--查找工资在1500到3000之间含两者的所有员工的信息。

      select * from emp where sal<>1500 and sal<>3000 and sal<>5000----把sal既不是1500也不是3000也不是5000的记录输出,数据库中不等于有两种表示:!= <>推荐使用第二种,对或取反是并且,对并且取反是或。

7、范围查询:between...and;not between...and

      select * from emp where sal between 1500 and 3000--查找工资在1500到3000之间含两者的所有员工的信息。
      select * from emp where sal not between 1500 and 3000--查找工资在1500到3000之间不含两者的所有员工的信息。

8、集合查询:in(属于若干个孤立的值)

      select * from emp where sal in (1500,3000,5000);
      select * from emp where sal not in (15000,3000,5000);--把sal既不是1500也不是3000也不是5000的记录输出
9、空值查询:null(没有值,空值)

1)零和null是不一样的,null表示空值,没有值,零表示一个确定的值。
2)null不能参加的运算:<>     !=    =
3)null可以参与的运算:is     not is
      select * from emp where comm is null;---输出奖金为空的员工信息
      select * from emp where comm is not null;---输出奖金不为空的员工信息
      select * from emp where comm <> null;---错,输出为空
      select * from emp where comm != null;---错,输出为空
      select * from emp where comm = null;---错,输出为空
4)任何类型的数据都允许为null
      create table t1 (name nvarchar(20),cnt int,riqi datetime);
      insert into t1 values (null,null,null);---正确
5)任何数字与null参与数学运算的结果永远是null
      ---输出每个员工的姓名年薪(包含奖金)comm假设是一年的奖金。
          select empno,ename,sal*12+comm "年薪" from emp;---错,null不能参与任何数据运算否则结果为空。
      ---正确的写法:
          select ename,sal*12+isnull(comm,0) "年薪" from emp;---isnull(comm,0)如果comm是null就返回零否则返回comm的值。  

10、字符匹配查询(模糊查询)

1)格式:select 字段的集合 from 表名 where 某个字段的名字 like 匹配的条件。匹配额条件通常含有通配符。
2)通配符:
(1)%---表示任意0个或多个字符
          select * from emp where ename like ‘%A%‘---ename只要含有字母A就输出。
          select * from emp where ename like ‘A%‘---ename只要首字母为A就输出。
          select * from emp where ename like ‘%A‘---ename只要尾字母为A就输出。
(2)_(下划线)---表示任意单个字符
               select * from emp where ename like ‘_A%‘---ename只要第二个字母为A就输出。
          [a-f]---表示a到f中的热任意单个字符,只能是abcdef中的任意一个字符
               select * from emp where ename like ‘_[A-F]%‘---把ename中第二个字符是A或B或C或D或E或F的记录输出
          [a,f]---表示a或f
          [^a-c]---表示不是a也不是b也不是c的任意单个字符
               select * from emp where ename like ‘_[^A-F]%‘---把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是F的记录输出
(3)匹配的条件必须用单引号括起来,不能省略,也不能改用双引号

(4)通配符作为不同字符使用的问题
          预备操作:create table student
                                        (name varchar(20) null
                                         ,age int);
                             insert into student values (‘张三‘,88);
                             insert into student values (‘tom‘,66);
                             insert into student values (‘a_b‘,22);
                             insert into student values (‘c%d‘,44);
                             insert into student values (‘abc_fe‘,99);
                             insert into student values (‘haobin‘,77);
                             insert into student values (‘HaoBin‘,55);
                             insert into student values (‘c%‘,33);
                             insert into student values (‘long‘‘s‘,100);
                             select * from student;
                             select * from student where name like ‘%\%%‘ escape ‘\‘---把name中包含有%的输出
                             select * from student where name like ‘%\_%‘ escape ‘\‘---把name中包含有_的输出   

11、逻辑查询:and or not

      select * from emp where sal=1500 or sal=3000 or sal=5000;

12、排序运算:order by(以某个字段排序),asc是升序默认可以不写,desc是降序

1)order by a,b---a和b都是升序,如果不指定排序的标准,则默认是升序,升序用asc表示,默认可以不写。
2)order by a,b desc---a升序,b降序,为一个字段指定的排序标准并不会对另一个字段产生影响。
3)order by a desc,b---a降序,b升序
4)order by a desc,b desc---a和b都降序,建议为每个字段指定排序的标准。
5)例子:asc是升序的意思默认可以不写,desc是降序
      select * from emp order by sal;--默认升序排列
      select * from emp order by deptno,sal;---先按照deptno升序排列,如果deptno相同,再按照sal升序排列
      select * from emp order by deptno desc,sal;---先按照deptno降序排列,如果deptno相同,再按照sal升序排列。desc只对deptno产生影响不会对后面的sal产生影响。
      select * from emp order by deptno,sal desc;---先按照deptno升序排列,如果deptno相同,再按照sal降序排列,desc只对sal产生影响不会对deptno产生影响。

13、聚合查询(多行记录返回一个值,通常用于统计分组的信息)
1)函数的分类:
(1)单行函数:每一行返回一个值
(2)多行函数:多行返回一个值
(3)聚合函数是多行函数
          select lower(ename) from emp;---最终返回的是行lower()是单行函数
          select max(sal) from emp;---返回行max()是多行函数
2)聚合函数分类:
(1)max()
(2)min()
(3)avg()---平均值
(4)count()---求个数
         count(*)---返回表中所有记录的个数
                select count(*) from emp;---返回emp表所有记录的个数
         count(字段名)---返回字段值非空的记录的个数,重复的记录也会被当做有效的记录
                select count(deptno) from emp;---deptno重复的记录被当做有效的记录
                select count(comm) from emp;---comm为null的记录不会被当做有效的记录
         count(distinct 字段名)---返回字段不重复并且非空的记录的个数
                select count (distinct deptno) from emp;---统计deptno不重复的记录的个数
3)注意的问题:
      select max(sal),min(sal),count(*) from emp;---正确
      select max(sal) "",min(sal) "",count(*) "" from emp;---正确
      select max(sal),lower(ename) from emp;---错误,单行函数和多行函数不能混用
      select max(sal) from emp;---正确,默认把所有的信息当做一组

14、分组聚合

1)group by
(1)格式:group by 字段的集合
(2)功能:把表中的记录按照字段分成不同的组。
(3)例子:查询不同部门的平均工资
                    select deptno,avg(sal) as "部门平均工资" from emp group by deptno
(4)理解group by a,b,c 的用法:先按a分组,如果a相同,再按b分组,如果b相同,再按c分组,最终统计的是最小分组的信息。
(5)使用了group by 之后 select 中只能出现分组之后的整体信息,不能出现组内的详细信息。
2)having(对分组之后的信息进行过滤)
(1)having子句是用来对分组之后的数据进行过滤,因此使用having时通常会先使用group by。
(2)如果没使用group by 但使用了having,则意味着having 把所有的记录当做一组来进行过滤,极少用。
          select count(*) from emp having avg(sal)>1000
(3)having子句出现的字段必须是分组之后的组的整体信息,不允许出现组内的详细信息。
(4)尽管select 字段中可以出现别名,但having子句中不能出现字段的别名,只能使用字段最原始的名字。
(5)having 和where 的异同
          相同:都是对数据进行过滤,只保留有效的数据;都不允许出现字段的别名,只允许出现最原始的字段的名字。
          不同:where 是对原始的记录过滤,having是对分组之后的记录过滤。
                     where必须写在having前面,顺序不可颠倒,否则运行出错。
          例子:把工资大于2000,统计输出部门平均工资大于3000的部门的部门编号、部门的平均工资
                             select deptno ,avg(sal)"平均工资",count(*)"部门人数",max(sal)"部门的最高工资"
                             from emp where sal>2000---where是对原始记录进行过滤
                             group by deptno having avg(sal)>3000---对分组之后的记录进行过滤
                     其中不可以将where 写在having后面

15、连接查询

1)定义:将两个表或者两个以上的表以一定的连接条件连接起来,从中检索出满足条件的数据。
2)分类:
(1)内连接
          select ... from A,B 的用法
                产生的结果:行数是A和B的乘积,列数是A和B之和,即把A表的每一条记录都和B表的每一条记录组合在一起,形成笛卡尔积,即把B表的每一条记录都和A表的每一条记录组合在一起,形成笛卡尔积。
                 注意:select * from A,B输出结果和select * from B,A一样,即AB可以互换。
          select ... from A,B where ...的用法
                 产生的笛卡尔积,用where中的条件进行过滤
          select ... from A join B on...的用法
                  join是连接,AB互换结果不变。
          SQL92和SQL99标准的区别
                  select ... from A,B where ...是SQL92标准
                  select ... from A join B on...是SQL99标准
                         输出结果一样
                   推荐使用SQL99标准:容易理解;on和where可以做不同的分工:on指定连接条件,where对连接之后临时表的数据进行过滤。
           例如:把工资大于2000的员工的姓名和部门的名称输出和工资的等级
                      SQL99标准
                              select "E".ename,"D".dname,"S".grade from emp "E" join dept "D"
                              on "E".deptno="D".deptno join salgrade "S"
                              on "E".sal>="S".losal and "E".sal<="S".hisal
                              where "E".sal>2000
                      SQL92标准
                               select "E".ename,"D".dname,"S".grade from emp "E", dept "D",salgrade "S"
                               where "E".sal>2000 and "E".deptno="D".deptno and ("E".sal>="S".losal and "E".sal<="S".hisal)
(2)自连接:一张表自己和自己连接起来查询数据。
        select * from emp a,emp b where b.ename=‘张三‘ and a.deptno=b.deptno;--在员工表中查找与张三在同一个部门的员工的信息。

(3)外连接

        select * from emp,dept where emp.deptno=dept.deptno;

(4)左外连接

        select * from emp left outer join dept on emp.deptno=dept.deptno;

(5)右外连接

        select * from emp right outer join dept on emp.deptno=dept.deptno;

(6)全外连接

        select * from emp full outer join dept on emp.deptno=dept.deptno;

16、联合:表和表之间的数据以纵向的方式连接在一起,前面均是横向连接在一起。
1)例子:输出每个员工的姓名、工资、上司的姓名
                 select "E1".ename,"E1".sal,"E2".ename "上司的姓名" from emp "E1" join emp "E2"
                 on "E1".mgr="E2".empno
                 union
                 select ename,sal,‘已是最大老板‘ from emp where mgr is null
2)若干个select子句要联合成功的话,必须满足两个条件:
(1)若干个select子句输出的列数必须是相等的;
(2)若干个select子句输出列的数据类型至少是兼容的。

17、top(最前面的若干个记录,专属于SqlServer的语法,不可移植到其他数据库)
                 select top 5 * from emp;
                 select top 15 percent * from emp;
                 select top 5 from emp;---错的

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

1)查询的顺序:
      select top...
      from A
      join B
      on...
      join C
      on...
      where...
      group by...
      having...
      order by...
2)例子:把工资大于1500的所有员工按部门分组把部门平均工资大于2000的最高的前2个部门的编号、部门的名称、部门平均工资的等级
(1)第一种写法:
          select "T".*,"D".dname,"S".grade from dept "D"
          join(select top 2 "E".deptno,avg(sal) "avg_sal" from emp "E" join dept "D"
                  on "E".deptno="D".deptno join salgrade "S"
                  on "E".sal between "S".losal and "S".hisal
                  where "E".sal>1500
                  group by "E".deptno
                  having avg("E".sal)>2000
                  order by avg("E".sal) desc
            ) "T"
           on "D".deptno ="T".deptno inner join salgrade “S”
           on "T"."avg_sal" between "S".losal and "S".hisal
(2)第二种写法:
          select "T".*,"D".dname,"S".grade from dept "D"
          join(select top 2 "E".deptno,avg(sal) "avg_sal" from emp
                 where sal>1500
                 group by deptno
                 having avg(sal)>2000
                 order by "avg_sal" desc
            ) "T"
           on "D".deptno ="T".deptno join salgrade “S”
           on "T"."avg_sal" between "S".losal and "S".hisal

19、分页查询
       假设每页显示n条记录,当前要显示的是第m页,表名是A,主键是A_id
       select top n * from A where A_id not in (select top (m-1)*n A_id from emp);

20、嵌套子查询

1)使用in的子查询

       select ename from emp where deptno in (select deptno from dept);

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

       select empno,ename from where sal>=all (select sal from sal where ename=‘张三‘);

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

       select ename from emp where exists (select * from dept);

相关推荐