应用T-SQL查询数据

创建Students数据库

CREATE DATABASE Students
 ON PRIMARY
 (
    NAME='Students_data',
    FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Students_data.mdf',
    SIZE=5MB,
    MAXSIZE=100MB,
    FILEGROWTH=2MB

 )
 LOG ON
 (
    NAME='Students_log',
    FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Students_log.ldf',
    SIZE=5MB,
    FILEGROWTH=2MB
 )

在Students数据库中，创建 StuInfo表，StuMarks表

USE Students
CREATE TABLE StuInfo
(
    StuID int NOT NULL PRIMARY KEY,
    StuName varchar(30) NOT NULL,
    StuSex char(2) DEFAULT('男')
)

USE Students
CREATE TABLE StuMarks
(
    ExamNO int NOT NULL PRIMARY KEY ,
    StuID int NOT NULL REFERENCES StuInfo(StuID),
    Subject varchar(30) NOT NULL,
    Score int NOT NULL CHECK(Score >= 0 and Score <=100) 
)

检查创建的表中的约束

USE Students
EXEC sp_helpconstraint @objname=StuInfo

在StuInfo中插入数据

USE Students
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(1,'张三',DEFAULT)
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(2,'李四',DEFAULT)
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(3,'王五','女')
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(4,'马六','女')
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(5,'赵七',DEFAULT)

在StuMarks中插入数据

USE Students
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(1,1,'java',98)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(2,1,'html',80)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(3,2,'java',70)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(4,3,'java',60)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(5,4,'java',55)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(6,5,'java',66)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(7,5,'html',48)

--或者
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(1,1,'java',98),(2,1,'html',80),(3,2,'java',70),(4,3,'java',60),(5,4,'java',55),(6,5,'java',66),(7,5,'html',48)

选取全部字段查询

SELECT * FROM StuInfo

选取部分查询

SELECT StuID,Subject,Score FROM StuMarks

设置字段别名

USE Students
SELECT StuID as '学号',姓名=StuName,StuSex '性别' FROM StuInfo

字段的计算

USE Students
SELECT StuSex+'-'+StuName as '性别-学员姓名' FROM StuInfo

使用ALL关键字返回全部记录

SELECT ALL StuID,StuName,StuSex FROM StuInfo

使用DISTINCT关键字过滤重复记录

USE Students
SELECT  StuID FROM StuMarks
SELECT DISTINCT StuID FROM StuMarks

使用TOP关键字仅显示前面若干条记录

USE Students
SELECT TOP 4 * FROM StuInfo
SELECT TOP 40 * FROM StuInfo

对查询结果筛选 where

1.比较运算符（大小比较，包括>,>=,=,<,<=,<>,!>,!<）

USE Students
SELECT * FROM StuInfo WHERE StuSex='男'
SELECT * FROM StuMarks WHERE Score >90
SELECT * FROM StuInfo WHERE StuName <> '李四'

2.范围运算符（表达式值是否在指定的范围）

• BETWEEN...AND...
• NOT BETWEEN...AND...

USE Students
SELECT * FROM StuMarks WHERE Score BETWEEN 80 AND 100
SELECT * FROM StuMarks WHERE Score NOT BETWEEN 80 AND 100

3.列表运算符（判断表达式是否为列表中的制定项）

• IN(项1，项2...)
• NOT IN(项1，项2....)

USE Students
SELECT * FROM StuInfo WHERE StuID IN(1,3,5)
SELECT * FROM StuInfo WHERE StuID NOT IN(1,3,5)

4.空值判断符（判断表达式是否为空）

• IS NULL
• IS NOT NULL

USE Students
SELECT * FROM StuMarks WHERE Score IS NULL
SELECT * FROM StuMarks WHERE Score IS NOT NULL

5.逻辑运算符（用于多条件的逻辑连接）

• AND
• OR

USE Students
SELECT * FROM StuMarks 
SELECT * FROM StuMarks WHERE Score > 80 AND Subject ='java'
SELECT * FROM StuMarks WHERE Score > 80 OR Subject ='html'
SELECT * FROM StuMarks WHERE StuID = 1 OR StuID =3 OR StuID=5

6.模式匹配符（判断是否与指定的字符通配格式相符）

• LIKE
• NOT LIKE

USE Students
SELECT * FROM StuMarks
SELECT * FROM StuInfo WHERE StuName LIKE '%张%' 
SELECT * FROM StuMarks WHERE Subject LIKE '[a-z]ava'
SELECT * FROM StuMarks WHERE Subject LIKE '[^i-z]tml'
SELECT * FROM StuInfo WHERE StuName LIKE '_六'
SELECT * FROM StuInfo WHERE StuName LIKE '%张%' 
SELECT * FROM StuInfo WHERE StuName NOT LIKE '%张%' 

对查询结果排序

1.使用ORDER BY 子句

USE Students
SELECT * FROM StuMarks
SELECT * FROM StuMarks WHERE Subject='java' ORDER BY Score ASC
SELECT * FROM StuMarks WHERE Subject='java' ORDER BY Score DESC

2.使用ORDER BY子句对多列进行排序

USE Students
SELECT * FROM StuMarks
SELECT * FROM StuMarks WHERE Subject='java' ORDER BY Score DESC,StuID DESC

3.ORDER BY子句与TOP关键字一起使用

USE Students
SELECT * FROM StuMarks
SELECT TOP 3 * FROM StuMarks WHERE Subject='java' ORDER BY Score DESC

对查询结果计算

1.使用SUM函数计算字段的累加和

USE Students
SELECT * FROM StuMarks
-- 查询java考试成绩的总和
SELECT SUM(Score) as java成绩的总和 FROM StuMarks WHERE Subject='java' 

2.使用AVG函数计算字段的平均值

USE Students
SELECT * FROM StuMarks
-- 查询java考试成绩的平均分
SELECT AVG(Score) as java成绩的平均分 FROM StuMarks WHERE Subject='java' 

3.使用MAX和MIN 函数计算字段的最大值和最小值

USE Students
SELECT * FROM StuMarks
-- 查询java考试成绩的最高分和最低分
SELECT MAX(Score) as java成绩的最高分,MIN(Score) as java成绩的最低分 FROM StuMarks WHERE Subject='java' 

4.使用COUNT函数统计记录行数

USE Students
SELECT * FROM StuInfo
-- 查询学员总人数
SELECT COUNT(*) as 学员总数 FROM StuInfo

SELECT * FROM StuMarks
--查询StuMarks中有几门科目
SELECT COUNT(DISTINCT Subject) as 科目数量 FROM StuMarks

对查询结果分组

1.使用GROUP BY 子句

USE Students
SELECT * FROM StuMarks
--分别统计参加每个科目考试的人数
SELECT Subject  as 科目,COUNT(*) AS 人数  FROM StuMarks GROUP BY subject

2.使用HAVING子句

USE Students
SELECT * FROM StuMarks
--统计平均分大于65分的科目
SELECT Subject  as 科目,AVG(Score) AS 平均分  FROM StuMarks GROUP BY subject HAVING AVG(Score)>65