数据库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、连接查询
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)
(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);