首页手记 Transact-SQL查询语句精华大全及实例演示

Transact-SQL查询语句精华大全及实例演示

标签：

SQL Server

本次我将通过一个小案例：工厂管理系统数据字典......让大家回顾 SQL Server 的知识点复习与巩固，同样如果你的是小白也没有关系的，只要你跟着案例手敲代码，不熟也能生巧，着眼于代码实战，学得会，熟读唐诗三百首，不会作诗也能吟~
创建工厂管理的数据库factory，打开数据库factory

CREATE DATABASE factory
GO
USE factory
GO

首先根据工厂管理系统数据字典factory创建四张表：
worker表（职工表）

depart表（部门表）

salary表（工资表）

study表（员工培训表）

-----创建table worker表：

CREATE TABLE worker(
wid varchar(3) PRIMARY KEY NOT NULL,
wname varchar(50) NOT NULL,
wsex char(2) CHECK(wsex in('男','女'))，
wbirthdate date,
wpary char(2) CHECK(wpary in('是','否')),
wjobdate date,
FOREIGN KEY deipid REFERENCES depart(did)
)
GO

--创建depart表

CREATE TABLE depart
(
did char(1) PRIMARY KEY,
dname varchar(20),
dmaster char(3),
droom char(10)
)
GO

--创建salary表


CREATE TABLE salary
(
wid char(3) ,
sdate date,
totalsalary decimal(10,1),
actualsalary decimal(10,1),
PRIMARY KEY (wid,sdate)
)
GO

--创建员工培训表study


CREATE TABLE study
(
study_id char(3) ,
study_name varchar(20),
wid char(3),
grade varchar(10),
PRIMARY KEY (study_id,wid)
)
GO

至于四张表的数据我已经上传到百度云共享，大家可以下载然后根据数据利用 INSERT INTO 表名 VALUES(....)语句进行插入执行即可

链接：http://pan.baidu.com/s/1pLIuTKn 密码：6syp
实例演示：
----创建一个新表worker_f，然后将worker表中所有女职工的职工号、职工名、出生日期、部门号，将这三个字段的信息插入到worker_f表中（汇集女职工的职工号、职工名、出生日期、部门号）

CREATE TABLE worker_f(
wid varchar(3) PRIMARY KEY NOT NULL,
wname varchar(10) NOT NULL,
wbrithdate date,
depid char(1)
)
INSERT INTO worker_f
SELECT wid,wname,wbrithdate,depid
FROM worker
WHERE wsex='女'

SELECT *
FROM worker_f

----利用SQL语句创建depart表的索引

CREATE INDEX index_depid ON worker(depid ASC)

----简单查询
--查询worker表中女职工所出现的部门号（depid）,相同的只出现一次

SELECT DISTINCT depid AS 部门号
FROM worker
WHERE wsex='女'

----查询worker表中前两项职工信息（前50%的职工信息）

SELECT TOP 2 *
FROM worker

SELECT TOP 50 PERCENT *
FROM worker

--查询salary表中前两项工资最高的职工信息

SELECT TOP 2 *
FROM salary INNER JOIN worker 
ON salary.wid=worker.wid
ORDER BY actualsalary DESC

--查询salary表中输出的totalsalary（应发工资）最高的前18%的职工信息
利用笛卡尔积查询法

SELECT 18 PERCENT *
FROM salary x,worker y
WHERE x.wid=y.wid
ORDER BY totalsalary DESC

----查询空值 is null is not null 信息 (is 是不能用=代替的)
----查询depart表中部门经理为空的部门信息

SELECT *
FROM depart
WHERE dmaster IS NULL

--重命名法则

SELECT  dmaster AS 部门经理, dname AS 部门名称
FROM depart

SELECT *
FROM depart

--条件查询
--查询salary（工资）表中实际工资(actualsalary)大于3000的职工号和实际工资

SELECT wid,actualsalary
FROM salary
WHERE actualsalary>3000

--查询salary（工资）表中实际工资(actualsalary)在2000和3000之间
--的职工号和实际工资

SELECT wid,actualsalary
FROM salary 
WHERE actualsalary BETWEEN 2000 AND 3000

--查询worker(职工)表中在部门“1”或“2”工作的职工的职工号、姓名、部门号
--用两种方法实现集合IN AND

SELECT  wid,wname,depid
FROM worker
WHERE depid in('1','2')

SELECT  wid,wname,depid
FROM worker
WHERE depid=1 or depid=2

----模糊查询
--查询worker(职工)表中所有姓“孙”职工的职工号、姓名和性别;

SELECT wid,wname,wsex
FROM worker
WHERE wname LIKE '孙%'

----查询worker(职工)表中所有姓名第二个字不是“华”的职工号、姓名和性别

SELECT  wid,wname,wsex 
FROM worker
WHERE wname NOT LIKE '_华%'

----查询worker表中既不姓张也不姓李或姓孙的职工的职工号、姓名、性别

SELECT  wid,wname,wsex
FROM worker
WHERE wname NOT LIKE '[张李孙]%'

----查询worker（职工）表中男职工是党员的职工号和姓名

SELECT wid,wname,wparty
FROM worker
WHERE wsex='男' AND wparty='是'

--统计查询
--查询salary（工资）表中日期为‘2011-01-04’的总工资(totalsalary)的平均工资
--方法一

SELECT AVG(totalsalry) AS 平均工资
FROM salary
WHERE sdate='2011-01-04'

--方法二

SELECT AVG(totalsalary) as '2011-01-04平均工资'
FROM salary
WHERE YEAR(sdate)=2011 and MONTH(sdate)=01 and DAY(sdate)=04

--查询职工的总数

SELECT  COUNT(*) AS 职工总数 
FROM worker

--查询在salary（工资）表中发过工资的职工人数，一个职工只计数一次

SELECT COUNT(DISTINCT wid) AS 职工人数
FROM salary

--查询salary（工资）表中最低的实发工资

SELECT MIN(actualsalary)  AS 最低工资
FROM salary

--查询salary（工资）表中最高的实际工资

SELECT MAX(actualsalary)  AS 最高工资
FROM salary

--查询salary（工资）表中‘2011-01-04’ 的工资的总额
--方法一

SELECT SUM(actualsalary) AS 工资总额
FROM salary
WHERE sdate='2011-01-04'

--方法二

SELECT  SUM(actualsalary) AS 工资总额
FROM salary
WHERE YEAR(sdate)=2011 and MONTH(sdate)=01 and DAY(sdate)=04

--附加子句查询
--order by 子句
--查询职工的职工号、职工姓名、出生日期、部门号，查询结果按照出生日期从早到晚排序

SELECT wid,wname,wbirthdate,depid
FROM worker
ORDER BY wbirthdate ASC

--查询职工的职工号、职工姓名、出生日期、部门号，查询结果按照出生日期从晚到早排序

SELECT wid,wname,wbirthdate,depid
FROM worker
ORDER BY wbirthdate DESC

--查询职工的职工号、职工姓名、出生日期、部门号，查询结果按照部门号
--从大到小排序，同一部门的按照出生日期从早到晚排序

SELECT wid,wname,wbirthdate,depid
FROM worker
ORDER BY depid DESC,wbirthdate DESC

--group by 子句
--分别统计男职工和女职工的人数

SELECT  wsex  AS 性别 ,COUNT(*) AS 职工人数
FROM worker
GROUP BY wsex

--分别统计每个日期的应发工资(totalsalary)总和

SELECT sdate AS 发工资日期 ,SUM(totalsalary) AS 应发工资总和
FROM salary
GROUP BY sdate

--having子句 (为分组 group by 后设置条件)
--分别统计每位员工的应发工资(totalsalary)总和，
--并且只显示工资总和在5000元以上的信息

SELECT wid as 职工号,SUM(totalsalary) AS 工资总和
FROM salary
GROUP BY wid HAVING SUM(totalsalary)>5000

--统计worker表中各部门党员的人数，并且显示党员人数在1个人以上的相关信息

SELECT  depid AS 部门号,COUNT(*) AS 党员人数
FROM worker 
WHERE wparty='是' 
GROUP BY depid HAVING COUNT(*)>=1

--插入数据
--向depart（部门）表中插入一行数据（‘7’,‘管理处’, ‘004’, ‘3205’）

INSERT INTO depart
VALUES('7','管理处','004','3205')

SELECT *
FROM depart

--向worker(职工)表插入一行数据，职工号:010，职工名:李飞龙，
--生日:1967-04-01，部门号:4

INSERT INTO worker(wid,wname,wbirthdate,depid)
VALUES ('010','李飞龙','1967-04-01','4')

--向salary(工资)表插入一行数据，职工号：010,totalsalary:2800

INSERT INTO salary(wid,sdate,totalsalary)
VALUES('010','2011-01-04',2800)

--双表内部连接查询
--查询职工的职工号，姓名，部门名，并按职工号排序。
--在where中指定内部连接的条件

SELECT wid,wname,dname
FROM worker x,depart y
WHERE x.depid=y.did
ORDER BY wid DESC

--查询职工的职工号，姓名，部门名，并按职工号排序。
--在from中指定内部连接的条件

SELECT wid,wname,dname
FROM worker x INNER JOIN depart y 
ON x.depid=y.did
ORDER BY wid DESC

--多表内部连接查询
--查询所有职工的职工号，姓名，部门名和2011 年2月份工资,
--最后一列要求显示‘2011 年2月工资’，并且按部门名排序
--在where中指定内部连接的条件

SELEECT  x.wid AS 职工号 ,wname AS 姓名,dname AS 部门名,actualsalary AS '2011年2月工资'
FROM worker x,depart y,salary z
where x.depid=y.did AND x.wid=z.wid AND YEAR(sdate)=2011 AND MONTH(sdate)=2
ORDER BY dname DESC

--查询所有职工的职工号，姓名，部门名和2011 年2月份工资,
--最后一列要求显示‘2011 年2月工资’，并且按部门名排序
--在from中指定内部连接的条件

SELEECT  x.wid AS 职工号 ,wname AS 姓名,dname AS 部门名,actualsalary AS '2011年2月工资'
FROM worker x INNER JOIN depart y 
ON x.depid=y.did INNER JOIN salary  z ON x.wid=z.wid
AND YEAR(sdate)=2011 and MONTH(sdate)=2
ORDER BY dname DESC

--内部连接

SELECT *
FROM worker INNER JOIN study 
ON worker.wid=study.wid

--外部连接(主表与从表的连接,从表匹配主表的相关信息)
--对于职工表worker和员工培训表study进行左外连接

SELECT *
FROM worker LEFT OUTER JOIN study 
ON worker.wid=study.wid

--对于职工表worker和员工培训表study进行右外连接

SELECT worker.*,study.*
FROM worker RIGHT OUTER JOIN study 
ON worker.wid=study.wid

--对于职工表worker和员工培训表study进行完全外连接

SELECT *
FROM worker FULL OUTER JOIN study 
ON worker.wid=study.wid

--对于职工表worker和员工培训表study进行交叉连接
--方法一

SELECT *
FROM worker CORSS JOIN study

--方法二

SELECT *
FROM worker,study

--求出各部门党员的人数，要求显示部门名和党员人数

SELECT dname AS 部门名,COUNT(*) AS 党员人数
FROM depart INNER JOIN worker
ON depart.did=worker.depid AND wparty='是'
GROUP BY dname

--修改数据
--修改worker表中的数据，将姓名为“李飞龙”的职工的性别修改为“男”

UPDATE worker
SET wsex='男'
WHERE wname='李飞龙'

--利用多表连接来修改数据
--将1975年以前出生的职工2011年1月份的totalsalary增加500元，
--actualsalary增加400元

UPDATE salary
SET totalsalary=totalsalary+500,actualsalary=actualsalary+400
FROM worker inner join salary 
ON worker.wid=salary.wid
WHERE YEAR(sdate)=2011 AND MONTH(sdate)=1 AND YEAR(wbirthdate)<1975

--删除李飞龙的信息

DELETE FROM worker
WHERE wname='李飞龙'

--删除余慧的工资信息

DELETE FROM salary 
FROM salary INNER JOIN worker
ON salary.wid=worker.wid
WHERE wname='余慧'

--union组合结果集(把两张表的内容进行上下拼接，完成查询结果的组合)
--任务一
--增加一个customer客户表，然后查询所有男职工和男客户的信息

SELECT wid as ID,wname as 姓名,wsex as 性别,wbirthdate as 出生日期
FROM worker
WHERE wsex='男'
UNION
SELECT cid,cname,csex,cbirthdate
FROM customer
WHERE csex='男'

--任务二
--对于工资信息表salary，统计该表的工资总和

SELECT wid,sdate,totalsalary,actualsalary
FROM salary
UNION
SELECT '小计',null,SUM(totalsalary),SUM(actualsalary)
FROM salary

--公用表表达式将复杂的查询利用公用表表达式进行简单化操作
--任务一
--利用公用表表达式，查询出“人事处”职工的职工号，职工姓名，部门名

WITH result(wid,wname,dname) AS
(
SELECT wid,wname,dname
FROM worker INNER JOIN depart ON worker.depid=depart.did and dname='人事处'
)
SELECT *
FROM result

任务二
--创建视图
--创建市场部员工培训成绩表的视图viewscbygpxb,要求按成绩的降序输出职工号、姓名、部门名和成绩

CREATE VIEW viewscbygpxb(职工号,姓名,部门名,成绩)
AS
SELECT top(10) x.wid,wname,dname,grade
FROM worker x,depart y,study z
WHERE x.depid=y.did AND x.wid=z.wid AND dname='市场部'
ORDER BY grade DESC

SELECT * 
FROM viewscbygpxb

到此，Transact-SQL查询语句精华大全及实例演示已经结束 !
你的支持是对我最大的鼓励~

点击查看更多内容