SQL

创建表

–创建表的语法：
CREATE TABLE
[database_name.[schema_name].|schema_name.]table_name(
column_name datatype [NULL | NOT NULL] [,…n])
[ ON filegroup ]
[ TEXTIMAGE_ON filegroup ]

–参数说明
–database_name：数据库名称，如果未指定数据库名，则将表创建于当前活动的数据库中。
–schema_name：数据库架构。默认情况下，创建表的架构是dbo。
–table_name：表名称
–ON filegroup 指定表存放在哪个文件组中，否则存放在默认文件组中（即主文件组Primary） 。
–TEXTIMAGE_ON filegroup 指定text、 ntext、 image 字段的数据存放在哪个文件组中， 否则存放在默认文件组中 （即主文件组Primary） 。
–表中每行数据 也就是每条记录的数据大小被限制为8060字节，但text、next、image这三种类型的数据不计入其中。
–按页存储，每页8KB，标头96B，另外底部还有最小36B的保留区，因而数据行可用仅为8060B(实际不得超过8053，因为系统还会对每行数据附加至少7B的系统数据)。

–identity(a,b) 用于设置为标识 a为起始值 b为增量
create table myFriend (
id int identity(100,1) primary key,
name varchar(20),
adress varchar(100)
)

insert into myFriend (name,adress) values(‘name1’,’地址1’);
insert into myFriend (name,adress) values(‘name2’,’地址2’);
insert into myFriend (name,adress) values(‘name3’,’地址3’);
insert into myFriend (name,adress) values(‘name4’,’地址4’);
insert into myFriend (name,adress) values(‘name5’,’地址5’);

–@@identity 获取最近一次添加到自动编号字段在数值
select @@identity;

–GUID 在数据库中对应的类型为uniqueidentifier 产生函数为newid()
create table tableGuid(
id uniqueidentifier default newid() primary key,
name varchar(10)
);
insert into tableGuid(name) values(‘name’);
select * from tableGuid;

–用户自定义数据
–一个数据库中，多次用到的类型，为了避免不同表类设置不同而引发错误，可自定义数据类型。
– 定义方法： 调用系统定义好的存储过程sp_addtype

EXEC sp_addtype 类型名称,系统数据类型,NULL or NOT NULL;
EXEC sp_addtype Scaletype, ‘decimal(5,2)’, ‘NULL’;
– 删除调用sp_droptype 类型名称 ;

–修改表
–修改表中的列 名称 类型等
ALTER TABLE 表名 RENAME COLUMN 原名称 TO 新名称;
ALTER TABLE 表名 ALTER COLUMN 列名 类型 ……;
– 添加字段
ALTER TABLE 表名 ADD COLUMN
列名 类型 ……[,其他列];
– 删除字段
ALTER TABLE 表名 DROP COLUMN 列名1，列名2……;

–删除表
DROP TABLE 表名;

约束

–check 约束
create table tableCheck (
id int identity primary key,
sex varchar(2)
check (sex in (‘男’,’女’)),
age int
constraint age_check
check (age between 1 and 100),
)
–将check约束写在后面
CREATE TABLE tableCheck2 (
id int identity,
sex char(2),
age int,
check (sex in (‘男’,’女’)),
check (age BETWEEN 1 AND 100),
PRIMARY KEY (id)
)

–[]表示一位
ALTER TABLE tableCheck2
ADD phone char(11)
CHECK (phone LIKE ‘[1][3-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’ )

–设置是否在插入和修改时启用check约束
–格式：ALTER TABLE 表名 CHECK|NOCHECK CONSTRAINT 约束名
–因而在定义约束的时候最好加上CONSTRAINT关键字为约束取名

ALTER TABLE tableCheck
NOCHECK CONSTRAINT age_check
ALTER TABLE tableCheck
CHECK CONSTRAINT age_check

–删除约束
ALTER TABLE tableCheck2
DROP CONSTRAINT age_check
–check约束内容不支持修改，只能删除再重建

–PRIMARY KEY 约束
–格式：创建表时:1、列名后面直接带上 PRIMARY KEY 2、在列定义完成之后，写入PRIMARY KEY (列名)
–如果使用CONSTRAINT 关键字为主键约束命名 则命名必须唯一（同一数据库中），且此列必须为NOT NULL
CREATE TABLE demoPrimary(
id int CONSTRAINT id_primary PRIMARY KEY
)
CREATE TABLE demoPrimary1(
stuID int NOT NULL,
couID int NOT NULL,
PRIMARY KEY (stuID,couID)
)

–修改约束
CREATE TABLE demoPrimary2(
id int NOT NULL
)
ALTER TABLE demoPrimary2
ADD CONSTRAINT id_primary_2
PRIMARY KEY (id)

–同check约束，定义后不支持修改，需要删除后重建
–删除
ALTER TABLE demoPrimary2
DROP CONSTRAINT id_primary_2

–UNIQUE 约束
一个表只能有一个Primary key约束 但可以有多个unique约束
primary key要求字段必须为NOT NULL 但unique可以允许为NULL，但记录中NULL只能出现一次
创建unique约束后会自动创建一个唯一索引

CREATE TABLE demoUnique(
id int NOT NULL PRIMARY KEY,
personID char(18) NOT NULL UNIQUE,
heaID char(10)
CONSTRAINT hID_Unique UNIQUE
)
–UNIQUE也可以是两个字段或多个字段共同构成 写为UNIQUE(列1，列2 )
–与分写写的UNIQUE(列1)，UNIQUE(列2)不同，分写写表示列1，列2 都是唯一约束。而在一起表示列1和列2组合起来是唯一约束

–修改
ALTER TABLE demoUnique
ADD stuID char(8) CONSTRAINT stuID_Unique UNIQUE

–删除
ALTER TABLE demoUnique
DROP stuID_Unique
–删除UNIQUE约束的时，自动产生的唯一索引也会一并删除

–FOREIGN KEY 约束
–FOREIGN KEY约束所指定的字段数目和数据类型， 必须与其链接到的PRIMARY KEY
–或UNIQUE 约束所指定的字段数目和数据类型完全相同。
CREATE TABLE tableUser(
userID int IDENTITY PRIMARY KEY,
userName varchar(20)
)
CREATE TABLE tableOrder(
orderID int IDENTITY PRIMARY KEY,
userID int FOREIGN KEY REFERENCES tableUser(userID)
)

–DEFAULT 默认值
–方式一：
CREATE TABLE tableDefault(
id uniqueidentity NOT NULL DEFAULT NEWID() PRIMARY KEY
)
–方式二：
ALTER TABLE tableDefault(
ADD
name varchar(20),
adress varchar(100),
DEFAULT ‘新姓名’ FOR name,
DEFAULT ‘新地址’ FOR adress
)
–Default 对象
–CREATE：DEFAULT 默认对象名 AS 默认值
CREATE DEFAULT s_inventory_d AS ‘原因未知’
–BINDE: EXEC sp_bindefault ‘默认对象名’, ‘表.列’
EXEC sp_bindefault ‘s_inventory_d’ ,’s_inventory.Out_of_stock_explanation’

–声明局部变量DECLARE @变量名 数据类型
–创建后变量的初始值为null
DECLARE @userName varchar(20)
SET @userName=’Tayor’
SELECT @userName;

–table数据类型 不能作为字段，仅用于局部变量，可临时存储表

DECLARE @myTable TABLE(
id char(5),
name varchar(10)
)
INSERT INTO @myTable (id,name) values (‘00001’,’SWITCH’)
SELECT * FROM @myTable

–函数

–LEN(‘str’)函数取得字符串的长度，会自动去除尾部空格，头部空格不会
SELECT LEN(‘我叫 ALEX’)–7
SELECT LEN(‘我叫 ALEX ‘)–7
SELECT LEN(’ 我叫 ALEX’)–9

–SUBSTRING(字符串，开始位置，长度) 从指定的字符串的开始位置开始，获取指定长度的字符串。
–首字符索引为1
SELECT SUBSTRING(‘this is sql’,1,4) – this
–大小写转换
SELECT LOWER(‘This is sql’);
SELECT UPPER(‘This is sql’);

–无参数函数，@@开头 之前被称为全局变量
–调用无需函数后的小括号
@@ROWCOUNT 最后一次执行的表达式处理了多少行数据
@@IDENTITY 最后依次添加的自动编号的值
@@VERSION 返回SQL SERVER 的版本号

–BEGIN … END 将多个sql语句作为一个整体处理
IF ((SELECT COUNT(*) FROM 飞狐工作室 WHERE 员工性别=1)>0)
BEGIN
PRINT ‘男性员工平均工资：’;
PRINT ”;
SELECT AVG(目前薪资) FROM 飞狐工作室 WHERE 员工性别=1 ;
IF((SELECT COUNT(*) FROM 飞狐工作室 WHERE 员工性别=1 AND 婚姻状况=1)>0)
BEGIN
PRINT ‘已婚男性平均工资：’;
PRINT ”;
SELECT AVG(目前薪资) FROM 飞狐工作室 WHERE 员工性别=1 AND 婚姻状况=1;
END
END

–循环 WHILE
– 用于重复多次执行某项工作
– 可用CONTINUE 使程序直接跳回WHILE 命令行做条件判断
– 可用BREAK 命令，直接退出所在循环。
– 循环可以嵌套

游标

–游标
DECLARE
@Name varchar(10),
@Sex bit,
@Married bit,
@Phone char(10),
@ChineseSex char(2),
@ChineseMarried char(4)
–1 声明游标
DECLARE staInfo_cursor CURSOR FOR
SELECT 姓名,员工性别,婚姻状况,电话号码 FROM 飞狐工作室
–2 打开游标
OPEN staInfo_cursor

–3 开始
–@@fetch_status是SQL SERVER的一个全局变量，值的改变是由于FETCH NEXT FROM 游标名
– 0 执行成功
– -1 fetch语句执行失败或此行不再结果集中
– -2 被提取的行不存在
FETCH NEXT FROM staInfo_cursor
INTO @Name,@sex,@Married,@Phone
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ‘员工姓名：’+ @name
IF(@sex=1)
SET @ChineseSex=’男’
ELSE
SET @ChineseSex=’女’
IF(@Married=1)
SET @ChineseMarried=’已婚’
ELSE
SET @ChineseMarried=’未婚’
PRINT ‘性别：’+@ChineseSex
PRINT ‘婚姻状况：’+@ChineseMarried
PRINT ‘电话’+@Phone
PRINT REPLICATE(‘-‘,30)

        --继续下一条        FETCH NEXT FROM staInfo_cursor             INTO  @Name,@sex,@Married,@Phone    END

–4 关闭游标
CLOSE staInfo_cursor
–5 释放游标
DEALLOCATE staInfo_cursor

–CASE 函数

SELECT
姓名,
CASE 员工性别
WHEN 1 THEN ‘男’
WHEN 0 THEN ‘女’
END AS 性别
FROM 飞狐工作室

SELECT
姓名,
目前薪资,
目前薪资 * CASE
WHEN (目前薪资>=60000) THEN 0.35
WHEN (目前薪资>=50000) THEN 0.25
WHEN (目前薪资>=40000) THEN 0.2
WHEN (目前薪资>=20000) THEN 0.15
ELSE 0
END 应缴税费 –新列名
FROM 飞狐工作室

SELECT
客户.客户编号,
客户.公司名称,
订货主档.订单号码,
订货主档.收货人
FROM 客户 LEFT OUTER JOIN 订货主档 ON 客户.客户编号=订货主档.客户编号
WHERE 订货主档.订单号码 is NULL

–查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及平均销售数目。
–1 将要用的数据读出存入一张表
SELECT
a.员工编号,a.姓名,sum(c.数量) 销售总量
FROM 员工 a
INNER JOIN 订货主档 b ON a.员工编号=b.员工编号
INNER JOIN 订货明细 c ON b.订单号码=c.订单号码
GROUP BY a.员工编号,a.姓名

–2自身连接查询
SELECT
t1.员工编号,t1.姓名,t1.销售总量,
COUNT(t2.员工编号) 比他销售多的业务员数目,
AVG(t2.销售总量) 比他高的平均销售数目
FROM
tableTemp t1 INNER JOIN tableTemp t2 ON t1.销售总量

索引

索引类型

• 存储结构：
  聚集索引：表中的数据记录实际存放的次序将会与聚集索引中相对应的键值的实际存放次序完全相同。
  

  下列数据访问操作非常适合使用聚集索引：
  如果某字段所包含的有差别的数据较少。如：血型、部门、州名称
  使用BETWEEN、>、>=、<、<=等运算符返回介于特定范围的数据记录的查询
  会按特定次序访问字段
  会返回大结果集的查询
  经常使用在查询的连接条件或GROUP BY 语句中的字段。
  使用聚焦索引搜索唯一键列的速度也非常快。

在每一个表最多只能拥有一个聚集索引。
聚集索引可以是唯一索引或非唯一索引。
聚集索引的键列的数目越少越好。
如果一个字段的内容经常变动，则非常不适合给这个字段创建聚集索引。因为键值一变动，会导致整个表的所有数据记录必须重排一次。

**非聚集索引**：  

• 数据唯一性
  唯一索引
  非唯一索引

–格式：
CREATE UNIQUE NONCLUSTERED
INDEX IX_Phone ON Customers (Phone)

CREATE UNIQUE NONCLUSTERED
INDEX IX_LFHH ON Employees (LastName,FirstName,HireDate,HomePhone)

SQL Server 会自动为PRIMARY KEY 和UNIQUE 约束的列上创建唯一索引。

–复制客户表，但不含数据，比创建快，且能保证数据类型一致
SELECT * FROM 客户 INTO 客户1
WHERE 客户编号 IS NULL
–插入数据
INSERT INTO 客户1
SELECT * FROM 客户 WHERE 城市 IN (‘北京市’,’天津市’,’上海市’)

–
SELECT * FROM 客户1

SELECT * INTO T1 FROM 章立民工作室
WHERE 员工编号 IS NULL
–插入数据
INSERT INTO T1

SELECT * FROM 章立民工作室

SELECT * FROM T1

UPDATE T1 SET 目前薪资=目前薪资+5000
WHERE 性别=’女’

UPDATE T1 SET
目前薪资=(SELECT AVG(目前薪资) FROM T1)
WHERE 目前薪资<(SELECT AVG(目前薪资) FROM T1)

–找出性别为男性且姓名为“许建仁”的员工，并将其姓名更改为“章立民” ，目前薪资则更改成起薪的 2.5倍。

UPDATE T1 SET
姓名=’章立民’;
目前薪资=目前薪资*2.5;
WHERE
姓名=’许建仁’
AND 性别=’男’

–删除 TRUNCATE TABLE 表名称 命令可直接删除表中的所有记录

demo

–客户表
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘s_customer’)
DROP TABLE s_customer
CREATE TABLE s_customer (
ID varchar(3) NOT NULL PRIMARY KEY,
Name varchar(20) Not NUll ,
Phone varchar(20) ,
Address varchar(20),
City varchar(20),
[State] varchar(15),
Country varchar(20),
Zip_code varchar(15),
Credit_rating varchar(9),
Sales_rep_id varchar(3),
Region_id varchar(3),
comments varchar(255)
)

–部门信息表
CREATE TABLE s_dept(
ID varchar(3) NOT NULL ,
Name varchar(20),
Region_id varchar(3)
)

–地区信息表
CREATE TABLE s_region(
ID varchar(3) NOT NULL ,
Name varchar(20)
)

–雇员信息表
CREATE TABLE s_emp(
ID varchar(3) NOT NULL ,
Last_name varchar(20),
First_name varchar(20),
Userid varchar(8),
Start_date datetime,
Comments varchar(25),
Manager_id varchar(3),
Title varchar(25),
Dept_id varchar(3),
Salary money,
Commision_pct numeric(4,2)
)

–仓库信息表
CREATE TABLE s_warehouse(
ID varchar(7) not null PRIMARY KEY,
Region_id varchar(3),
Address varchar(20),
City varchar(20),
[State] varchar(15),
Zip_code varchar(15),
Country varchar(20),
Phone varchar(20) ,
Manager_id varchar(3)
)

–产品信息
CREATE TABLE s_product(
ID varchar(7),
Name varchar(25),
Short_desc varchar(255),
Suggestes_whlsl_price money,
whlsl_units varchar(10)
)

–存货信息
CREATE TABLE s_inventory(
product_id varchar(7) NOT NULL,
Warehouse_id varchar(7) NOT NULL,
Amount_in_stock int,
Reorder_point int,
Max_in_stock int,
Out_of_stock_explanation varchar(255),
Restock_date datetime
)

– 订单信息
CREATE TABLE s_ord(
ID varchar(3) NOT NULL PRIMARY KEY,
Customer_id varchar(3),
Date_ordered datetime,
Date_shipped datetime,
Sales_rep_id varchar(3),
Total money,
Payment_type varchar(6),
Order_filled char(1) not null
)

– 项目信息
CREATE TABLE s_item (
Ord_id varchar(3),
Item_id varchar(7),
product_id varchar(7),
price money,
Quantity int,
Quantity_shipped int
)

–1. s_customer 客户的信誉等级（Credit_rating）只能取 Excellent、Good、Poor
ALTER TABLE s_customer
ADD CHECK (Credit_rating IN (‘Excellent’,’Good’,’Poor’))

–2. s_dept 表中，name 及 region_id 的组合必须是唯一的，确保在地区中部门名是唯一的。
–unique
ALTER TABLE s_dept
ADD UNIQUE(name,region_id)

–索引
CREATE UNIQUE NONCLUSTERED
INDEX index_n_r ON s_dept(name,region_id)

–3. 任何雇员的佣金百分率必须为以下值之一：10、12.5、15、17.5、20
ALTER TABLE s_emp
ADD CHECK (Commision_pct IN (10,12.5,15,17.5,20))

–4. 在表 s_inventory 中，product_id 及 warehouse_id 的组合必须是唯一的。
ALTER TABLE s_inventory
ADD PRIMARY KEY (product_id,warehouse_id)

—5. 给 s_inventory 表的 Out_of_stock_explanation 列设定默认值为“原因未知” 。
–CREATE DEFAULT 默认对象名 AS 默认值
–CREATE
CREATE DEFAULT s_inventory_d AS ‘原因未知’
–BINDE: EXEC sp_bindefault ‘默认对象名’, ‘表.列’
EXEC sp_bindefault ‘s_inventory_d’ ,’s_inventory.Out_of_stock_explanation’

–6. 建立 DEFAULT 对象，名称为 AddressDefault，值为“地址不祥” ，并将该对象绑定到 s_customer 表、s_warehouse 表、的 Address 列上。
CREATE DEFAULT AdressDefault AS ‘地址不详’

EXEC sp_bindefault ‘AdressDefault’ ,’s_customer.Address’;
EXEC sp_bindefault ‘AdressDefault’ ,’s_warehouse.Address’;

–1. 查询出所有订单中订购数量>=100 产品， 要求包括订单号码、 订单日期、 要货日期、产品编号、单价、数量，且按订单号码升序排序。
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=’myView1’)
DROP VIEW myView1
GO
CREATE VIEW myView1
AS
SELECT TOP 100 PERCENT a.订单号码,a.订单日期 ,a.要货日期,b.产品编号, b.单价,b.数量
FROM 订货主档 a INNER JOIN 订货明细 b
ON a.订单号码=b.订单号码
WHERE b.数量>100
ORDER BY a.订单号码 ASC

–2. 查询出订购产品的“上海市”客户名单，要求在查询结果中显示客户编号、公司名称、订单号码、订单日期、送货城市、产品、供应商、单价、数量、折扣。
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=’myView2’)
DROP VIEW myView2
GO
CREATE VIEW myView2
AS
SELECT a.客户编号,a.公司名称,b.订单号码,b.订单日期,b.送货城市,d.产品,e.供应商,c.单价,c.数量,c.折扣
FROM 客户 a INNER JOIN 订货主档 b ON a.客户编号=b.客户编号
INNER JOIN 订货明细 c ON c.订单号码=b.订单号码
INNER JOIN 产品资料 d ON d.产品编号=c.产品编号
INNER JOIN 供应商 e ON d.供应商编号=e.供应商编号
WHERE a.城市 = ‘上海市’

–1. 建立存储过程 sp1，要求传递入参数@name，功能是查询出与该参数指定的姓名相对应的雇员表中的雇员名单，要求包括列：身份证号码、姓名、出生日期、年龄，最后请写出调用语句。
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘sp1’ AND type = ‘P’)
DROP PROCEDURE sp1
GO
CREATE PROCEDURE sp1
@name varchar(10)
AS
SELECT 身份证号码,员工姓名,出生日期,DATEDIFF(YEAR,出生日期,GETDATE()) 年龄
FROM 雇员
WHERE 员工姓名=@name

GO
EXEC sp1 ‘涂一文’

–2. 建立存储过程 sp2，要求传递入参数@name，功能是将与该参数指定的姓名相对应的雇员表中的雇员的目前薪资增加 20％，最后请写出调用语句。
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name=’sp2’ and TYPE=’P’)
DROP PROCEDURE sp2
GO
CREATE PROCEDURE sp2
@name varchar(10)
AS
UPDATE 雇员 SET 目前薪资=1.2*目前薪资 WHERE 员工姓名=@name

GO
EXEC sp2 ‘涂一文’

–3. 建立存储过程 sp3，要求传递入参数@dep（用于表示“部门” ） ，功能是返回＠avgsalary，表示“雇员”表中该部门的平均薪资，最后请写出调用语句。

GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name=’sp3’ AND type=’P’)
DROP PROCEDURE sp3
GO
CREATE PROCEDURE sp3
@dep nvarchar(10),
@avgsalary money OUTPUT
AS
SELECT @avgsalary=AVG(目前薪资) FROM 雇员 WHERE 任职部门=@dep
GO
DECLARE @avg11 money;
EXEC sp3 ‘管理部’,@avg11 OUTPUT ;
SELECT @avg11
PRINT ‘平均工资：’ +CONVERT(varchar, @avg11)

–1. 列出“客户”表中所有城市名称，城市名称相同者只列出一次。
SELECT DISTINCT 城市 FROM 客户

–2. 列出“客户”表中城市列的值为“北京市”的客户，要求只包括列：客户编号、公司名称、联系人，并且将列出的查询结果输出到表“北京客户”中。

SELECT 客户编号,公司名称,联系人 INTO 北京客户 FROM 客户 WHERE 城市=’北京市’;
SELECT * FROM 北京客户;

–3. 列出“飞狐工作室”表中，在行销部、业务部、财务部任职的员工的员工编号、姓名、电话号码。

SELECT 员工编号,姓名,电话号码 FROM 飞狐工作室 WHERE 部门 IN (‘行销部’,’业务部’,’财务部’)

–4. 查询出“飞狐工作室”表中，起薪在 20000～25000 之间的员工编号、姓名。

SELECT 员工编号,姓名 FROM 飞狐工作室 WHERE 起薪 BETWEEN 20000 AND 25000;

–5. 查询出“飞狐工作室”表中，本月出生的员工编号、姓名。

SELECT 员工编号,姓名 FROM 飞狐工作室 WHERE MONTH(出生日期)=MONTH(GETDATE());

–6. 查询出“飞狐工作室”表中，姓名中含“一”的员工编号、姓名。

SELECT 员工编号,姓名 FROM 飞狐工作室 WHERE 姓名 LIKE ‘%一%’ ;

–7. 查询出“飞狐工作室”表中，姓“温”的员工编号、姓名。

SELECT 员工编号,姓名 FROM 飞狐工作室 WHERE 姓名 LIKE ‘温%’;

–8. 查询出“飞狐工作室”表中，姓名中第二个字为“一”的员工编号、姓名。

SELECT 员工编号,姓名 FROM 飞狐工作室 WHERE 姓名 LIKE ‘_一%’ ;

–9. 查询出“飞狐工作室”表中，身份证号码第一个字符为 M、N、A 的员工编号、姓名。

SELECT 员工编号,姓名 FROM 飞狐工作室 WHERE 身份证号码 LIKE ‘[M|A|N]%’;

–10. 查询出“飞狐工作室”表中，身份证号码第一个字符不介于 A～M 的员工编号、姓名。

SELECT 员工编号,姓名 FROM 飞狐工作室 WHERE 身份证号码 LIKE ‘[^A-M]%’;

–11. 查询出“飞狐工作室”表中，家庭地址中含_字符的员工编号、姓名。*

SELECT 员工编号,姓名 FROM 飞狐工作室 WHERE 家庭地址 LIKE ‘%_%’ escape ‘\’ ;

–12. 查询每一位客户的订货情况，查询结果中要包含客户编号、公司名称、订单号码、订单日期、送货地址。

SELECT a.客户编号,a.公司名称,b.订单号码,b.订单日期,b.送货地址
FROM 客户 a INNER JOIN 订货主档 b ON a.客户编号=b.客户编号

–13. 查询每一位客户的订货情况，查询结果中要包含客户编号、公司名称、订单号码、订单日期、产品编号、单价、数量。

SELECT a.客户编号,a.公司名称,b.订单号码,b.订单日期,c.产品编号,c.单价,c.数量
FROM 客户 a
INNER JOIN 订货主档 b ON a.客户编号=b.客户编号
INNER JOIN 订货明细 c ON c.订单号码=b.订单号码

–14. 查询每一位客户的订货情况，希望那些未下订单的客户数据也要出现在查询结中，要求选取列：客户编号、公司名称、订单号码、订单日期、送货地址。

SELECT a.客户编号,a.公司名称,b.订单号码,b.订单日期,b.送货地址
FROM 客户 a
LEFT JOIN 订货主档 b ON a.客户编号=b.客户编号

–15. 查询出所有曾经在 1997 年以后订货的的客户编号、公司名称、订单号码、订单日期、产品编号、产品、单价、数量。

SELECT a.客户编号,a.公司名称,b.订单号码,b.订单日期,c.产品编号,d.产品,c.单价,c.数量
FROM 客户 a
INNER JOIN 订货主档 b ON a.客户编号=b.客户编号
INNER JOIN 订货明细 c ON c.订单号码=b.订单号码
INNER JOIN 产品资料 d ON c.产品编号=d.产品编号
WHERE YEAR(b.订单日期)>1997

–16. 统计出“雇员”表中，有多少员工未婚。

SELECT COUNT(*) FROM 雇员 WHERE 婚姻状况=’false’

–17. 统计出“雇员”表中，有多少员工住在“深圳市” 。

SELECT COUNT(*) FROM 雇员 WHERE 家庭住址 LIKE ‘深圳市%’;

–18. 统计出 “雇员” 表中， 有多少员工雇用年数在 20～30 年之间 （包括 20 年和 30 年） 。

SELECT COUNT(*) FROM 雇员 WHERE DATEDIFF(YEAR,雇用日期, GETDATE() ) BETWEEN 20 AND 30;

–19. 统计出在 1996 年 7 月份下订单的北京市客户有多少位。
SELECT COUNT(*)
FROM 客户 a INNER JOIN 订货主档 b ON a.客户编号 = b.客户编号
WHERE YEAR(b.订单日期)=1996 AND MONTH(b.订单日期)=7 AND a.城市=’北京市’

–20. 统计出“雇员”表中，最高的“目前薪资”是多少。

SELECT TOP 1 目前薪资 FROM 雇员 ORDER BY 目前薪资 DESC

–21. 统计出“雇员”表中， “信息部”最高的“目前薪资”是多少。

SELECT MAX(目前薪资) 最高薪资 FROM 雇员 WHERE 任职部门=’信息部’

–22. 统计出“雇员”表中，最低的“目前薪资”是多少。

SELECT MIN(目前薪资) 最低薪资 FROM 雇员

–23. 统计出“雇员”表中， “信息部”与“业务部”最低的“目前薪资”是多少。

SELECT 任职部门,MIN(目前薪资) 最低薪资
FROM 雇员
WHERE 任职部门=’信息部’ OR 任职部门=’业务部’
GROUP BY 任职部门

–24. 统计出“雇员”表中，员工的平均年龄。

SELECT AVG(DATEDIFF(YEAR,出生日期,GETDATE())) 平均年龄 FROM 雇员

–25. 统计出“雇员”表中，员工的平均“目前薪资” 。

SELECT AVG(目前薪资) 平均薪资 FROM 雇员

–26. 统计出“雇员”表中， “信息部”所发的“目前薪资”总和。

SELECT SUM(目前薪资) 总计 FROM 雇员 WHERE 任职部门=’信息部’

–27. 统计出所有客户的订购总金额。

SELECT SUM(单价*数量) 订购总金额 FROM 订货明细

–28. 统计出“雇员”表中，各个部门的薪资最高值、平均值及人数，另要求统计结果中包括任职部门列。

SELECT 任职部门,MAX(目前薪资) 最高薪资,AVG(目前薪资) 平均薪资,COUNT(身份证号码) 人数
FROM 雇员 GROUP BY 任职部门

–29. 统计出每一个客户的订购次数和订购总金额， 要求统计结果中包括客户编号、 公司名称、采购次数、采购总金额。*

SELECT a.客户编号,a.公司名称,COUNT(b.订单号码) 采购次数,SUM(c.单价*c.数量) 采购总金额
FROM 客户 a INNER JOIN 订货主档 b
ON a.客户编号=b.客户编号
INNER JOIN 订货明细 c
ON c.订单号码=b.订单号码
GROUP BY a.客户编号,a.公司名称

–30. 统计出“员工”表中每一个业务人员的销售总数量，要求列出员工编号、姓名和销售总数。*

SELECT a.员工编号,a.姓名,COUNT(c.单价*c.数量) 销售总数
FROM 员工 a INNER JOIN 订货主档 b
ON a.员工编号=b.员工编号
INNER JOIN 订货明细 c
ON c.订单号码=b.订单号码
GROUP BY a.员工编号,a.姓名

–31. 统计出“飞狐工作室”表中，部门平均薪资大于 30000 的各个部门的薪资最高值、最小值、平均值及人数。*

SELECT 部门,MAX(目前薪资) 薪资最高,MIN(目前薪资) 薪资最低,AVG(目前薪资) 平均值,COUNT(身份证号码) 人数
FROM 飞狐工作室
GROUP BY 部门
HAVING AVG(目前薪资)>30000

–32. 按目前薪资从大到小的顺序，列出“飞狐工作室”表中所有员工的姓名、性别和目前薪资，其中性别列的内容要显示成“男” 、 “女”字样。*

SELECT
姓名,
CASE 员工性别
WHEN 1 THEN ‘男’
WHEN 0 THEN ‘女’
END 性别,
目前薪资
FROM 飞狐工作室
ORDER BY 目前薪资 DESC

–33. 查询出“飞狐工作室”表中，目前薪资倒数前 10 名员工姓名及其薪资。

SELECT DISTINCT TOP 10 姓名,目前薪资 FROM 飞狐工作室 ORDER BY 目前薪资

–34. 查询出“飞狐工作室”表中，目前薪资最高前 10 名员工姓名及其薪资。

SELECT DISTINCT TOP 10 姓名,目前薪资 FROM 飞狐工作室 ORDER BY 目前薪资 DESC

–35. 查询出“飞狐工作室”表中，部门的平均薪资最高的前 3 个部门名及平均薪资。

SELECT DISTINCT TOP 3 部门,AVG(目前薪资) 平均薪资
FROM 飞狐工作室
GROUP BY 部门
ORDER BY AVG(目前薪资) DESC

–36. 查询出销售业绩最佳的前 3 名业务员的员工编号、姓名及该员工的销售总金额。*

SELECT DISTINCT TOP 3 a.员工编号, a.姓名,SUM(c.单价*c.数量) 销售总金额
FROM 员工 a INNER JOIN 订货主档 b
ON a.员工编号=b.员工编号
INNER JOIN 订货明细 c
ON b.订单号码=c.订单号码
GROUP BY a.员工编号, a.姓名
ORDER BY 销售总金额 DESC

–37. 查询出“飞狐工作室”表中，与公司中最低薪资相同的员工姓名及薪资（使用子查询） 。*

SELECT 姓名,目前薪资 FROM 飞狐工作室
WHERE 目前薪资 IN (SELECT MIN(目前薪资) FROM 飞狐工作室)

–38. 查询出“飞狐工作室”表中，超过公司中平均薪资 80％的员工姓名及薪资（使用子查询） 。*

SELECT 姓名,目前薪资 FROM 飞狐工作室
WHERE 目前薪资 > 0.8 * (SELECT AVG(目前薪资) FROM 飞狐工作室 )

–39. 查询出“客户”表中，哪些客户并没有订购过，要求列出客户编号、公司名称。 （使用子查询） 。*

SELECT 客户编号,公司名称 FROM 客户
WHERE 客户编号 NOT IN (
SELECT 客户编号 FROM 订货主档
)

–40. 在 s_region 表中添加两条记录，id、Name 值分别为“001” 、 “中美/加勒比” ， “002” 、“北美” 。

INSERT INTO s_region(ID,NAME) VALUES(‘001’,’中美/加勒比’);
INSERT INTO s_region(ID,NAME) VALUES(‘002’,’北美’);

–41. 先建立“北京客户”空表，结构与“客户”表相同，然后从“客户”表选取“北京市”的客户存入“北京客户”表内。

SELECT * INTO 北京客户 FROM 客户 WHERE 客户编号 IS NULL ;
INSERT INTO 北京客户
SELECT * FROM 客户 WHERE 城市 LIKE ‘北京%’

–42. 先将“雇员”表数据复制到“我的雇员 1”表，然后在“我的雇员 1”表中，将目前薪资都提高 10％。

SELECT * INTO 我的雇员1 FROM 雇员 ;
UPDATE 我的雇员1 SET 目前薪资=1.1*目前薪资;

–43. 先将“雇员”表数据复制到“我的雇员 2”表，然后在“我的雇员 2”表中，将目前薪资低于平均薪资调高成平均薪资。*

SELECT * INTO 我的雇员2 FROM 雇员 ;
UPDATE 我的雇员2 SET 目前薪资=(SELECT AVG(目前薪资) FROM 我的雇员2)
WHERE 目前薪资<(SELECT AVG(目前薪资) FROM 我的雇员2)

–44. 先将“雇员”表数据复制到“我的雇员 3”表，然后在“我的雇员 3”表中，将目前薪资倒数 10 名内的员工删除掉。

SELECT * INTO 我的雇员3 FROM 雇员 ;
DELETE FROM 我的雇员3
WHERE 身份证号码 IN (
SELECT TOP 10 身份证号码 FROM 我的雇员3 ORDER BY 目前薪资 ASC
)

–45. 先将“雇员”表数据复制到“我的雇员 4”表，然后在“我的雇员 4”表中，将目前薪资倒数 10 名内的员工删除掉。*

SELECT * INTO 我的雇员4 FROM 雇员 ;
DELETE FROM 我的雇员4
WHERE 身份证号码 IN (
SELECT TOP 10 身份证号码 FROM 我的雇员4 ORDER BY 目前薪资 ASC
)

–46. 先将“客户”表数据复制到“我的客户 1”表，然后在“我的客户 1”表中，将没有下过订单的客户删除掉。*

SELECT * INTO 我的客户1 FROM 客户 ;
DELETE FROM 我的客户1
WHERE 客户编号 NOT IN(
SELECT DISTINCT 客户编号 FROM 订货主档
)