sql语句两个练习 emp dept 复杂查询

查询指定列

select sal from emp WHERE ename='SMITH'    //转换成小写也行

如何取消重复行    统计共有多少个部门编号  

distinct只能消除完全一样的行，会保留一行

SELECT DISTINCT deptno FROM emp    //抛弃的是完全一样的

使用算数表达式  显示每个雇员的年工资

SELECT ename,sal*13 '年工资' FROM emp    '年工资' 为这一列的别名

使用where子句

1、显示工资高于3000的员工

SELECT * from emp WHERE sal>3000

2、查找1982年1月1号以后入职的员工

SELECT * from emp WHERE hiredate>'1982-1-1'  //1982-1-1这是一个固定格式

3、显示工资在2000-2500之间的员工 的情况  between包含小的

SELECT * from emp WHERE sal BETWEEN 2000 AND 2500

4、模糊查询   

4.1显示首字母为s员工姓名   %代表0到多个字符

     SELECT ename,sal from emp WHERE ename LIKE 'S%'

4.2如何显示第三个字符为大写o的所有员工的姓名和工资  两个下划线

    SELECT ename,sal from emp WHERE ename LIKE '__o%'

4.3如何显示empno为123    345   800。。。的雇员的情况  使用in关键字

    SELECT * from emp WHERE empno in(123,345,800)

5、显示没有上级的雇员的情况    IS NULL

    SELECT * from emp WHERE mgr IS NULL;

6、查询工资高于500岗位是manager的雇员同时他们的姓名首字母为J

SELECT * FROM emp WHERE (sal>500 OR job='MANAGER') AND ename LIKE 'J%'

order  by

1、如何按照工资从高到底来显示  系统默认是升序排列ASC  降序是desc

SELECT * FROM emp ORDER BY sal ASC

2、如何按照入职的先后顺序排列

SELECT * FROM emp ORDER BY hiredate ASC

3、按照部门升序而员工的工资降序排列  order By 可以根据不同的字段排序

SELECT * FROM emp ORDER BY deptno ASC ,sal DESC

4、统计每个人的年薪并按照从低到高的顺序排列  对别名进行排序，但是别名不能为汉语

SELECT ename,(sal*13+comm) nian from emp  ORDER BY nian

表的复杂查询  

聚合函数

1、如何显示工资最高的和工资最低的

SELECT MIN(sal) FROM emp

2、如何显示最低工资和该雇员的名字

SELECT ename,sal FROM emp WHERE sal=(SELECT min(sal) FROM emp)

   sql语句的原则：能够减少数据集的语句尽量写在右边  sql语句从右往左执行

3、显示所有员工的品军工资和工资总和

SELECT ename,sal FROM emp WHERE sal=(SELECT min(sal) FROM emp)

4、把高于平均工资的的雇员的名字和他的工资显示出来

SELECT ename,sal  FROM emp WHERE sal>(SELECT AVG(SAL) FROM emp);

5、统计共有多少员工

select count(*) FROM emp

group By

1、显示每个部门的平均工资和最高工资  并显示部门名称

SELECT AVG(sal) '平均工资',deptno FROM emp GROUP BY deptno

2、显示每个部门的每种岗位的平均工资和最低工资
SELECT avg(sal),MIN(sal),empno,ename FROM emp GROUP BY deptno,job ORDER BY deptno

3、显示平均工资低于2000的部门号和它的平均工资

SELECT AVG(sal) ,deptno FROM emp GROUP BY deptno HAVING  AVG(sal)<2000

注：havin往往和group by结合使用，可以对分组查询结果进行筛选

分组函数只能出现在选择列表

如果在select 语句中同时包含有group  by，having，order  by在选择列表中，如果有列、表达式、分组那么这些列和表达式必须出现在一个group by中否则会出错

多表查询

两个或者两个以上的表  笛卡儿积

1、通过什么关系把两张表结合起来

没有这个关系的话就会把两张表按照笛卡儿积结合起来 SELECT * FROM emp,dept

从最右边的每一条和次右边的表的每条记录结合，形成右*次右条记录

1、显示雇员的名字，雇员的工资以及所在部门的名称

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno

如果两张表都有相同名字的字段，则需要带表名（别名）

SELECT e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno

2、显示部门名称号为10 的部门名称、员工和工资

SELECT d.dname,ename,sal FROM emp e,dept d WHERE e.deptno=10 AND e.deptno=d.deptno; 

3、显示雇员名、雇员工资以及所在部门的名字并按部门排序

SELECT e.ename,e.sal  FROM emp e,dept d WHERE e.deptno=d.deptno ORDER BY  d.dname

自连接

同一张表的连接查询:看成两张表，

1、显示某个员工的上级

SELECT ename FROM emp where empno=(SELECT mgr FROM emp WHERE ename='ford')

2、显示每个员工的姓名和他的上级的名字

外连接：左外连接  右外连接

SELECT worker.ename '雇员',boss.ename '老板' FROM emp worker,emp boss WHERE worker.mgr=boss.empno

子查询

嵌入在其他sql语句的select语句，

1、单行子查询  只返回一行数据的子查询

显示与smith同一个部门的所有员工

SELECT * FROM emp WHERE deptno=
(SELECT deptno FROM emp WHERE ename='smith')

2、多行子查询 返回多行数据的子查询   字符串的比较有没有  in

查询和部门10的工作相同的工作的雇员的名字，岗位，工资，部门号

SELECT * from emp WHERE job in (SELECT DISTINCT job FROM emp WHERE deptno=10)

3、在from字句中使用子查询

如何显示高于部门平均工资的员工的姓名、薪水、和他部门的平均工资

a、取到各个部门的平均工资

     SELECT AVG(sal),deptno FROM emp GROUP BY deptno

b、把上面的查询结果当作临时表来对待

SELECT emp.ename,emp.sal,temp.myqvg from emp,
(SELECT AVG(sal) myqvg,deptno FROM emp GROUP BY deptno) temp 
WHERE emp.deptno=temp.deptno AND emp.sal>temp.myqvg

在from字句中使用子查询时，可以被当作一个临时表来对待，当在from子句中使用子查询时，必须给子查询指定别名

分页查询

显示第1个到第4个入职的员工

SELECT top 4 * FROM emp ORDER BY hiredate

显示从第5个到第10个记录 

       先排除不要的4个人再从剩下的人里边来排6个这样就是从5个到第10个

SELECT top 6 * FROM emp WHERE empno not in
 (SELECT top 4 empno FROM emp ORDER BY hiredate )
ORDER BY hiredate

   identity(1,1) 表示testID字段是自增长的，从1开始每次加1

create table test(teseId int primary key  identity(1,1))

如何删除一张表的重复记录

1、把cat表的记录distinct记录插入到一个临时表中

select distinct * from #temp from cat   //产生了一个#temp的临时表

2、把cat表的记录清空

delete  from cat   //只删除数据，不删除表的结构  与drop table cat不同

3、把#temp表的没有重复记录的数据插入到cat表里面去

insert into cat select * from #temp

4、删除临时表

drop table #temp   

左外连接和右外连接

显示公司每个员工和它的上级  

原来的解决办法：使用的是内连接  SELECT ename FROM emp where empno=(SELECT mgr FROM emp WHERE ename='ford')

显示公司每个员工和它的上级  以及没有上级的员工的名字也要显示出来

左外连：left join 指左边的表的记录全部出现，如果没有匹配的记录就用空来填

SELECT w.ename,r.ename FROM emp w LEFT JOIN emp r ON w.mgr=r.empno

右外连：right join 指右边的表的记录全部出现，如果没有匹配的记录就用空来填

SELECT w.ename,r.ename FROM emp r right JOIN emp w ON w.mgr=r.empno

维护数据的完整性--约束

约束用于确保数据库中的数据满足特定的商业规则

在sql sever中约束主要包括：not null 、unique 、primary key、forrign key和check五种

not null

如果在列上定义了not null ，那么在插入数据时就必须为这个列插入数据

unique 

如果在列上定义了unique后，那么在插入数据时，这列的数据不可以重复，但是可以为空（这列的值最多只有一个为空），一张表能有多个

primary key  一张表只能有一个   唯一而且不为空

复合主键 ：有多个列共同构成一个主键

create table test3(testId int,testName varchar(30),

primary key(testId,testName ))只有当这两个值都完全相同时，才认为是重复

foreign key

用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是null

主表 dept 从表emp

check 用于强制数据必须满足的条件

 create table test3(testId int,testName varchar(30),sal int check(sal>=1000 and sal<200))

default 的使用

CREATE TABLE mes (mesId int PRIMARY KEY identity (1,1),
maecon VARCHAR(2000) not null,
mesdate datetime DEFAULT getdate())

当插入数据的没有mesdate 这一项，系统就会自己添加当前的默认时间

数据库的基本知识

规范数据化：

达到第一范式必须使数据库中的表都具有以下的性质：

1、每一列都必须只包含一个值，

2、所有表都不能具有相关的重复列

sql语句建立一列是自增的

create table cdat
(
   localt               char(20) not null,
   cd                   char(5) not null,
   snosat               char(2) not null,
   rnorec               char(3) not null,
   id                   INT(20) not null AUTO_INCREMENT,
   primary key (id)
);

其中id就是自增的