关闭

sql学习笔记(韩顺平)

579人阅读 评论(0) 收藏 举报
分类:
--创建表
create table dept
(
deptId int primary key,--部门id
deptName nvarchar(50), --部门名称
deptLoc nvarchar(50)   --部门地址
)

--创建emp表
create table emp
(
empNo int primary key,
empName nvarchar(10),
job nvarchar(20),
mgr int,
hiredate datetime,
sal numeric(10, 2),
comm numeric(10, 2),
deptNo int foreign key references dept(deptId) --外键只能指向主键并且类型要一致
)

select * from dept
insert into dept (deptId, deptName, deptLoc) values(10,'ACCOUNTING','NEW YORK');
insert into dept (deptId, deptName, deptLoc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptId, deptName, deptLoc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptId, deptName, deptLoc) values (40, 'OPERATIONS', 'BOSTON');

select * from emp;
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, null, 20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);


--查询SMITH的薪水,工作和所在的部门
select sal, job, deptNo from emp where empName = 'SMITH'

--取消重复行
----统计一共有多少部门编号
select deptNo from emp;
select distinct deptNo from emp;

--显示每个员工的年工资
select empName, sal*16+isnull(comm, 0)*16 as '年工资' from emp order by '年工资' desc;

--显示工资高于3000的员工
select * from emp where sal > 3000

--查询在1982-1-1号之后入职的员工
select * from emp where hiredate > '1982-1-1'

--查询工资在2000到2500之间的员工的信息
select * from emp where sal between 2500 and 3000

--查询首字母为S的员工的姓名和工资
select empName, sal from emp where empName like 'S%'; --%代表任意个数的字符

--查询第三个字符为o的员工的信息
select * from emp where empName like '__o%';  --下划线表示单个字符

--查询empNo为7369,7499或者7521的员工的信息
select * from emp where empNo in (7369,7499,7521)

--显示没有上级的员工的信息
select empName from emp where mgr is null;

--查询工资高于500或者是岗位是MANAGER的,并且姓名的首字母是‘j’的员工的信息
select * from emp where (sal > 500 or job = 'MANAGER') and empName like 'j%';

--按照工资从低到高对员工的信息进行排名
select * from emp order by sal desc;
select * from emp order by hiredate asc;

--按照部门号升序而雇员的工资降序排列
select * from emp order by deptNo asc, sal desc;

--统计每个员工的年薪,从高到低排序显示
select empName,(sal+isnull(comm,0))*13 as '年薪' from emp order by '年薪' desc;

--显示所有的员工中工资最高的和工资最低的员工的姓名
select empName, sal from emp where sal = (select max(sal) from emp)
select * from emp
select empName,sal from emp where sal = (select min(sal) from emp);

--显示员工的平均工资和工资总和
select sum(sal) as '总工资', avg(sal) as '平均工资' from emp;

--显示工资高于平均工资的员工的姓名和该员工的工资,并且显示平均工资
select empName,sal,(select avg(sal) from emp) as '平均工资' from emp where sal > (select avg(sal) from emp);

--统计一共有多少员工
select count(*) as '员工数量' from emp;

--显示每个部门的平均工资和最高工资,并且按照平均工资递减排序显示
select job, avg(sal) as '平均工资', max(sal) as '最高工资' from emp group by job order by avg(sal) desc;

--显示每个部门的每个岗位的平均工资
select deptNo, job, avg(sal), min(sal) from emp group by deptNo, job order by deptNo

--显示平均工资低于2000的部门号和他的平均工资,并且按照从低到高排序显示
--having要对分组查询的结果进行筛选
select deptNo,avg(sal) from emp group by deptNo having avg(sal) > 2000 order by avg(sal)

--分组函数可以出现的地方:选择列表,having, order by中
--如果在select语句中同时出现group by,having,order by,那么他们的顺序是group by, having, order by
--在select的选择列表中如果有列,表达式和分组函数,则这些列和表达式必须出现在group by中

select * from emp;
select * from dept;

select * from emp, dept;--笛卡尔积

--显示完整的员工的信息,包裹员工的工作地址
select empNo, empName,job, mgr, hiredate, sal, comm, e.deptNo, deptName, deptLoc
from emp e, dept
where dept.deptId = e.deptNo

--显示部门号为10的部门的员工名,部门名称, 工资
select deptId, empName, sal from emp as e, dept as d where e.deptNo = d.deptId and deptId = 10
--显示部门号为10的部门的员工名,部门名称, 工资和平均工资
--?

--显示雇员的名字,工资,所在部门个名字,并按照部门排序
select empName,sal, deptName from emp e, dept d where e.deptNo = d.deptId order by d.deptName

----自连接
--显示FORD的上级
select empName from emp where empNo = (select mgr from emp where empName = 'FORD')
select * from emp;

--显示所有人的上级的姓名和本人的名字
select boss.empName '老板', emp.empName '员工' from emp boss, emp where boss.empNo = emp.mgr
select * from emp;


----自查询
--显示与SMITH在同一个部门的所有的员工
select empName from emp where deptNo = (select deptNo from emp where empName = 'SMITH')

--查询和部门10的工作相同的员工的名字,岗位,工资和部门号
select empName, job, sal, deptNo
from emp
where job in (select distinct job from emp where deptNo = 10) and deptNo != 10



----在from子句中使用自查询
--高于部门平均工资的员工的信息
select * from emp                                    --作为第一张表
select AVG(sal), deptNo from emp group by deptNo    --作为第二张表

--高于部门平均工资的员工的信息
select empName, sal, avgSal, emp.deptNo from emp,
(select avg(sal) as avgSal, deptNo from emp group by deptNo) as avgSalTable
where avgSalTable.deptNo = emp.deptNo
and sal > avgSal

--分页查询
select * from emp;
--显示第一个到第四个入职的员工的信息
select top 4 * from emp order by hiredate

--请显示第5个到第10个入职的雇员(按照时间排序)
select top 6 * from emp
where empNo not in
(select top 4 empNo from emp order by hiredate)
order by hiredate

select * from emp order by hiredate

--显示第11个到13个入职的员工的信息
select top 3 * from emp where empNo not in
(select top 10 empNo from emp order by hiredate)
order by hireDate

--显示薪水排在11到13位的员工的信息
select top 3 * from emp
where empNo not in
(select top 10 empNo from emp order by sal desc)
order by sal desc

select * from emp order by sal desc

--如何删除一张表中的重复记录
create table cat
(
catId int,
catName nvarchar(10)
)
insert into cat values(1, 'a')
insert into cat values(2, 'b')
select * from cat;
--删除一张表中的重复记录
select distinct * into #temp2 from cat
delete from cat
insert into cat select * from #temp2
drop table #temp2

----左外连接
--显示公司的每个员工姓名和他的上级的姓名,没有上级的显示上级为null
select emp.empName,boss.empName from emp, emp as boss where boss.empNo = emp.mgr
select emp.empName,boss.empName from emp left join emp as boss on boss.empNo = emp.mgr
select emp.empName,boss.empName from emp as boss right join emp on boss.empNo = emp.mgr

--完整性约束

create database aaa

create table goods
(
goodsId nvarchar(20) primary key,
goodsName nvarchar(50) not null,
unitPrice numeric(8, 2) check (unitPrice > 0),
category nvarchar(10) check (category in('生活类', '日用类')),
provider nvarchar(30)
)
create table customer
(
customerId nvarchar(30) primary key,
customerName nvarchar(20) not null,
customerAddress nvarchar(30),
email nvarchar(30) unique,
gender nchar(1) check (gender in ('男', '女')),
cardId varchar(20)
)

create table purchase
(
customerId nvarchar(30) foreign key references customer(customerId),
goodsId nvarchar(20) foreign key references goods(goodsId),
nums int check (nums > 0)
)


drop database aaa

--备份数据库
backup database aaa to disk='f:\\sqlPro\\aaa.bak'
--还原数据库
restore database aaa from disk='f:\\sqlPro\\aaa.bak'


0
0
查看评论

韩顺平SqlServer数据库实例中的两张表

-- 创建数据库; create database spdb1; --切换数据库; use spdb1; --创建部门表; create table dept (deptno int primary key,dname nvarchar(30),loc nvarchar(30) ); -- 插入部门...
  • petergunner
  • petergunner
  • 2017-01-26 18:08
  • 221

SQL学习笔记(一)

本文地址:http://blog.csdn.net/shanglianlm/article/details/51890450SQL 中最重要的 DDL ( 数据定义语言)语句: CREATE DATABASE - 创建新数据库 ALTER DATABASE - 修改数据库 CREATE TAB...
  • shanglianlm
  • shanglianlm
  • 2016-07-12 16:47
  • 2446

SQL学习笔记(1)

【7月28日 学习记录】 1.SQL SELECT SELECT语句用于从数据库中选取数据。 SELECT colum_name,column_name FROM table_name; SELECT * FROM table_...
  • lucahan
  • lucahan
  • 2016-07-29 18:47
  • 1104

SQL学习笔记之数据库专题(一):SQL语句详解

SQL是用来存取关系数据库的语言,具有查询、操纵、定义和控制关系型数据库的四方面功能。常见的关系数据库有Oracle,SQLServer,DB2,Sybase。开源不收费的有MYSQL,SQLLite等。今天我们主要以MYSQL为例子,讲解SQL常用的SQL语句。 一.数据库 1.创建数据库...
  • victorfreedom
  • victorfreedom
  • 2015-01-08 15:59
  • 1670

韩顺平讲的SQL作业

  • 2013-06-13 20:12
  • 11KB
  • 下载

sql学习笔记(韩顺平)

--创建表 create table dept ( deptId int primary key,--部门id deptName nvarchar(50), --部门名称 deptLoc nvarchar(50) --部门地址 ) --创建emp表 create table emp ( emp...
  • robin_Xu_shuai
  • robin_Xu_shuai
  • 2017-03-17 17:00
  • 579

SqlHelper类(BY_韩顺平)

****************************************DBUtil工具包获取一个连接*********************************************** package com.xlc.utils; import java.io.FileInp...
  • sinat_28387645
  • sinat_28387645
  • 2016-05-09 21:18
  • 301

韩顺平Spring框架学习,学习笔记(五)

Bean工厂和ApplicationContext上下文 bean工厂介绍: 从applicationContext应用上下文容器获取bean和从bean工厂r容器中获取bean有什么区别:  applicationContext中获取:      App...
  • qq_32517251
  • qq_32517251
  • 2016-05-24 15:15
  • 360

韩顺平linux总结

linux视频教程第0讲.开山篇 为什么学习linux linux是一个开源、免费的操作系统,其稳定性、安全性、处理多并发已经得到业界的认可,目前很多中型,大型甚至是巨型项目都在使用linux linux内核:redhat 、红旗linux、ubuntu、suse、fedora,它们的内核都是...
  • chenyechao
  • chenyechao
  • 2012-01-18 21:28
  • 2467

韩顺平老师SqlHelper.class.php

  • 2015-11-22 21:10
  • 4KB
  • 下载
    个人资料
    • 访问:219611次
    • 积分:3189
    • 等级:
    • 排名:第12760名
    • 原创:95篇
    • 转载:0篇
    • 译文:0篇
    • 评论:65条
    最新评论