基础知识:
1.域(Domain):一组具有相同数据类型的值的集合。
例如:实属、整数、字符串
2.笛卡尔积:给定一组域D1,D2,它们可以完全相同也可以不同。D1×D2={(d1,d2)}
3.关系(二维表):笛卡尔积的有意义的子集。
4.元组:关系的行,或称为记录。
5.属性:关系的列,或称为字段。
基础代码:
1.emp表
create table emp (
empno int primary key,
ename nvarchar(200),
job nvarchar(200),
mgr int ,
hiredate datetime,
sal int ,
comm int,
deptno int constraint fk_deptno foreign key references dept(deptno)
)
注:
int()取整。
primary key主键——约束唯一标识数据库表中的每条记录,主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键,并且每个表只能有一个主键。
nvarchar(n)——n 定义字符串的长度
区分:char、varchar、这两个只能用于单字节来存储数据,适合英文,中文会不兼容。我们常用的汉字需要用两个字节来存储,所以就要使用nchar、nvarchar。
foreign key外键——一个表中的foreign key指向另一个表中的unique key(唯一约束的键)。用于建立表与表之间的关系,以保证数据的完整性和一致性。
2.dept表
create table dept(
deptno int primary key,
dname nvarchar(200),
loc nvarchar(200),
)
3.between
1)查找工资在1500到3000之间(包括1500和3000)的所有员工的信息
select *from emp where sal >= 1500 and sal<=3000
或者
select * from emp where sal between 1500 and 3000
2)查找工资小于1500或者大于3000的所有员工的信息
select * from emp where sal<1500 or sal >3000
或者
select* from emp where sal not between 1500 and 3000
4、in
1)把sal等于1500、3000、5000的值输出
select * from emp where sal in (1500,3000,5000)
或者
select * from emp where sal = 1500 or sal = 3000 or sal = 5000
2)把sal不等于1500、3000、5000的值输出
select * from emp where sal not in (1500,3000,5000)
或者
select * from emp where sal <> 1500 or sal <> 3000 or sal <> 5000
注:数据库中不等于有两种表示:"!= "、"<> "推荐使用第二种.
5、top
1)输出前两行数据
select top 2 * from emp
2)查询表前百分之15的数据
select top 15 percent * from emp
3)把工资在1500到3000之间(包括1500和3000)的员工中工资最高的前四个人的信息
select top 4 * from emp where sal between 1500 and 3000 order by sal desc
注:desc 降序,不写则默认升序
6、order by
use test1
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升序排序
--order by a desc b,c,d desc只对a产生影响,不会对b,c,d产生影响
select * from emp order by deptno,sal desc
--问题:desc是否会对deptno产生影响?
--回答:不会,先按deptno升序,再按sal降序
7、聚合函数【多行记录返回一个值,通常用于统计分组的信息】
select LOWER(ename) from emp --lower()是单行函数,最终返回14行
select MAX(sal) from emp --max()是多行函数,最终返回一行
select COUNT(*) from emp --返回emp表所有记录的个数
8、gourp by
select * from emp
1)输出每个部门的编号和该部门的平均工资
select deptno,AVG(sal) as 该部门的平均工资 from emp group by deptno
select deptno,avg(sal) 部门平均工资, ename
from emp
group by deptno; --前两个是分组信息,最后一个是14行信息
select deptno,ename,sal from emp
order by deptno;
select deptno,ename,sal from emp
group by deptno,ename,sal
order by deptno,sal;
select deptno,ename,avg(sal)平均工资 from emp
group by deptno,ename,sal
order by deptno;
总结:使用了 group by 后 select 中只能出现分组后的整体信息,不能出现组内的详细信息
select * from emp
order by deptno,job;
select deptno,job,avg(sal)
from emp
group by deptno,job
order by deptno
select deptno,job,avg(sal+isnull(comm,0)),count(*),max(sal+isnull(comm,0)), min(sal+isnull(comm,0))
from emp
group by deptno,job
order by deptno
--group by a,b,c 先执行a大组,b小组,c小小组,之后再按最小组呈现出select后面的内容
--
select mgr,COUNT(ename)as 员工人数 from emp group by mgr
--
select comm,count(*) from emp group by comm
9、having
输出部门平均工资大于2000的部门编号 部门的平均工资
select deptno,AVG(sal)
from emp
group by deptno
having AVG(sal)>2000
判断下列sql语句是否正确
1、统计输出部门平均工资大于2000的部门编号
select deptno,AVG(sal)as 平均工资
from emp
group by deptno
having AVG(sal)>2000
2、把姓名不包含A的所有的员工按部门编号分组,统计输出部门平均工资大于2000的部门编号部门的平均工资
select deptno,AVG(sal) "平均工资"
from emp
where ename not like '%A%'
group by deptno
having AVG(sal) > 2000
3、把工资大于2000的员工的姓名和部门的编号输出
select e.ename as 员工姓名, d.deptno as 部门编号
from emp e,dept d
where e.deptno = d.deptno and sal>2000
等价于
select e.ename as 员工姓名,d.deptno as 部门编号
from emp e
join dept d
on e.deptno = d.deptno
where sal > 2000
4、把工资大于2000的员工的姓名和部门的编号和工资等级输出
select e.ename as 员工姓名,e.deptno as 部门编号,s.grade as 工资等级
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
5、求出每个员工的姓名 、部门编号 、薪水 和薪水的等级
select e.ename 员工姓名,e.deptno 部门编号,e.sal 薪水,s.grade 薪水等级
from emp e
join salgrade s
on sal>=losal and sal<=hisal
6、 查找每个部门编号 该部门所有员工的平均工资 平均工资的等级
select 部门编号,avg_sal 部门员工平均工资, grade 平均工资等级
from
(select e.deptno 部门编号, AVG(sal) avg_sal
from emp e
group by e.deptno ) t
join salgrade s
on t.avg_sal between s.losal and s.hisal
等价于
select deptno 部门编号,avg_sal 部门员工平均工资, grade 平均工资等级
from salgrade s
join(select e.deptno deptno, AVG(sal) avg_sal
from emp e
group by e.deptno ) t
on t.avg_sal between s.losal and s.hisal
7、求出emp表中所有领导的姓名
select empno ,ename 领导姓名
from emp
where empno in(select mgr from emp)
8、求出平均薪水最高的部门的编号和部门的平均工资
select top 1 deptno 部门编号,AVG(sal) 平均工资
from emp e
group by deptno
order by AVG(sal)desc
9、有一个人工资最低,把这个人排除,剩下的人中工资最低的前三个人的姓名、 工资 、部门编号 、部门名称、 工资等级、 输出
select ename 姓名,sal 工资,t.deptno 部门编号,d.dname 部门名称,s.grade 工资等级
from
(select top 3 *
from emp
where sal >
(select MIN(sal)
from emp)
order by sal) t
join dept d
on t.deptno = d.deptno
join salgrade s
on sal between s.losal and s.hisal
10、把工资大于1500的所有员工按部门分组,把部门平均工资大于2000的最高前两个的部门编号,部门名称,部门平均工资等级
select t.deptno 部门编号,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
order by AVG(e.sal)desc )t
on d.deptno = t.deptno
join salgrade s
on avg_sal between s.losal and s.hisal
11、用聚合函数求薪水最高的员工信息
select *
from emp
where sal = (select MAX(sal)from emp)
select * from emp
order by sal asc
12、求出平均工资最高的部门的编号和部门的平均工资
select deptno 部门, avg_sal 部门最高的平均工资
from
(
select deptno ,AVG(sal) avg_sal
from emp e
group by deptno
)t
where t.avg_sal =
(
select MAX(avg_sal)
from(
select deptno ,AVG(sal) avg_sal
from emp e
group by deptno
)t
)
13、输出工资最高的前三个员工的信息
select top 3 *
from emp
order by sal desc --从输出结果来看,先执行order by 后执行top
注:从输出结果来看,先执行order by 后执行top
14、工资从高到低排序,输出工资是第7-9的员工信息
select top 3 *
from emp
where empno not in (select top 6 empno
from emp
order by sal desc)
order by sal desc
--另一道题
select top 3 *
from emp
where empno not in (--sal前两名过滤
)
order by sal desc
join dept
on emp.deptno=dept.deptno
join salgrade
on sal>=losal and sal<=hisal
sc