Sql Server2008中基本T-SQL（1）
1.使用SQL插入数据：
use TrainingBase
/*---向表Trainee中插入一条记录---*/ /*---没有插入photo一列，因此该列可为空---*/ INSERT INTO Trainee (TraineeNo,TraineeName,Sex,Address,GradeId,Email,BornDate,IdentityCard) VALUES (10009,'张青',0,'上海虹桥',1,'adc@sohu.com','1985-8-23', '12345678901234567x')
/*---表的字段名省略---*/ INSERT INTO Trainee VALUES (10010,'刘楠',0,2,'13811112222',DEFAULT,'1983-12-12','adc@aptech.com','123456111112222223')
select * from Trainee
2.使用SQL删除数据：
use TrainingBase
/*---删除表Trainee中的数据---*/ /*---删除编号为10013记录---*/ DELETE FROM Trainee WHERE TraineeName = '张青燕'
DELETE FROM Trainee WHERE TraineeName = '田园'
/*---删除表Result中数据---*/ DELETE FROM Result WHERE TraineeNo ='20023'
select * from Trainee
select * from Result
3.使用SQL修改数据：
use TrainingBase
/*---修改表Trainee中的数据---*/ /*---地址不详的实习生地址改为北京女子职业技术学校---*/ UPDATE Trainee SET Address ='北京女子职业技术学校' WHERE Address = '地址不详'
/*---2010-2-15科目为2的成绩低于95分的实习生加5分---*/ UPDATE Result SET TraineeResult = TraineeResult + 5 WHERE TraineeResult < 95 AND SubjectNo = 2 AND ExamDate = '2010-2-15'
select * from Trainee
select * from Result WHERE TraineeResult < 95 AND SubjectNo = 2 AND ExamDate = '2010-2-15'
3.使用SQL查询数据：
<1>简单的查询语句
/*--查询所有数据的行和列--*/ --查询表Trainee、Grade中所有数据 select * from Grade select * from Trainee
/*--查询部分数据的行和部分列--*/ select SubjectName,ClassHour from Subject --显示部分列 SELECT * FROM Trainee WHERE Address = '北京市海淀区' --显示部分行 SELECT TraineeNo,TraineeName,Address FROM Trainee WHERE Address = '北京市海淀区' --显示部分行和部分列
/*--查询对比--*/ select * from Subject select subjectNo,SubjectName,ClassHour,GradeId from Subject
/*--查询中使用别名--*/ SELECT TraineeNo AS 实习生编号,TraineeName 实习生姓名, 实习生地址=Address FROM Trainee
/*--在查询中使用常量列--*/ SELECT 姓名=TraineeName,地址= Address,'河北新龙' AS 实习地点 FROM Trainee
/*--查询空值--*/ SELECT TraineeName,Address FROM Trainee WHERE Email IS NULL
/*--限制查询数据返回的行数--*/ SELECT TOP 5 TraineeName, Address FROM Trainee WHERE Sex = 0
SELECT TOP 20 PERCENT TraineeName, Address FROM Trainee WHERE Sex = 0
/*--按顺序排列查询结果--*/ --降序排列 SELECT TraineeNo AS 实习生编号,SubjectNo AS 科目编号,TraineeResult AS 成绩 FROM Result WHERE TraineeResult> 60 ORDER BY TraineeResult DESC --升序排列 SELECT TraineeNo AS 实习生编号,SubjectNo AS 科目编号,TraineeResult AS 成绩 FROM Result WHERE TraineeResult> 60 ORDER BY TraineeResult
<2>聚合函数查询
/*--SUM的应用--*/ SELECT SUM(TraineeResult) AS 总分 FROM Result WHERE TraineeNo = 20015
/*--AVG的应用--*/ SELECT AVG(TraineeResult) AS 平均成绩 FROM Result WHERE SubjectNo =2
/*--MAX和MIN的应用--*/ SELECT MAX(TraineeResult) AS 最高分, MIN(TraineeResult) AS 最低分 FROM Result WHERE SubjectNo =2
/*--COUNT的应用--*/ SELECT COUNT(*) AS 及格人数 FROM Result WHERE TraineeResult>=60
<3>模糊查询
/*--使用LIKE进行模糊查询--*/ SELECT * FROM Trainee WHERE TraineeName LIKE '张%' --查询姓张的实习生 SELECT * FROM Trainee WHERE TraineeName LIKE '张_' --查询姓张并且姓名是两个字的实习生 SELECT * FROM Trainee WHERE Address LIKE '%北京%' --查询地址中有“北京”二字的实习生
/*--使用BETWEEN进行模糊查询--*/ SELECT * FROM Result WHERE TraineeResult BETWEEN 70 AND 80 --查询分数在60（含）到80（含）之间的记录 SELECT * FROM Trainee WHERE BornDate NOT BETWEEN '1985-1-1' AND '1990-8-1' --查询生日不在1985年1月1日到1990年8月1日之间实习生
/*--使用IN在列举值内进行查询--*/ SELECT TraineeName AS 实习生姓名 FROM Trainee WHERE Address IN ('北京市海淀区','广州','上海虹桥') ORDER BY Address --查询地址在北京市海淀区、广州、上海虹桥中的实习生姓名
SELECT * FROM Result WHERE SubjectNo IN (3,6,7,8) ORDER BY SubjectNo DESC --查询科目编号在3、6、7、8中的考试记录
<4>内联接查询
/*--内联接查询--*/
--在where子句中指定查询条件
--显示字段列表时，使用表名.字段名 SELECT Trainee.TraineeName, Result.SubjectNo,Result.ExamDate, Result.TraineeResult FROM Trainee, Result WHERE Trainee.TraineeNo = Result.TraineeNo
--显示字段列表时，省略表名 SELECT TraineeName, SubjectNo,ExamDate,TraineeResult FROM Trainee, Result WHERE Trainee.TraineeNo = Result.TraineeNo
--查询实习生姓名、考试科目、成绩和日期 SELECT TraineeName,SubjectName,ExamDate,TraineeResult FROM Trainee, Result,Subject WHERE Trainee.TraineeNo = Result.TraineeNo AND Subject.SubjectNo=Result.SubjectNo
--表名使用别名 SELECT T.TraineeNo,TraineeName,SubjectName,ExamDate,TraineeResult FROM Trainee as T, Result as R,Subject as S WHERE T.TraineeNo = R.TraineeNo AND S.SubjectNo=R.SubjectNo
---使用INNER JOIN…ON实现表间关联 SELECT TraineeName,SubjectNo,ExamDate,TraineeResult FROM Trainee INNER JOIN Result ON (Trainee.TraineeNo = Result.TraineeNo)
--使用AS指定表的别名 SELECT TraineeName, SubjectNo, ExamDate, TraineeResult FROM Trainee AS T ,Result AS R WHERE T.TraineeNo = R.TraineeNo
SELECT T.TraineeName,R.SubjectNo,R.ExamDate,R.TraineeResult FROM Trainee AS T INNER JOIN Result AS R ON (T.TraineeNo = R.TraineeNo)
--查询实习生姓名、考试科目、成绩和日期,3个表联接查询 SELECT TraineeName,SubjectName,ExamDate,TraineeResult FROM Trainee AS T INNER JOIN Result AS R ON (T.TraineeNo = R.TraineeNo) INNER JOIN Subject AS S ON (S.SubjectNo=R.SubjectNo)
<5>外联接查询
/*--外联接查询--*/
--左外联接查询 SELECT T.TraineeName, R.SubjectNo, R.TraineeResult FROM Trainee AS T LEFT JOIN Result AS R ON T.TraineeNo = R.TraineeNo
SELECT T.TraineeName, R.SubjectNo, R.TraineeResult FROM Trainee AS T LEFT OUTER JOIN Result AS R ON T.TraineeNo = R.TraineeNo
--右外联接查询 SELECT T.TraineeName, R.SubjectNo, R.TraineeResult FROM Trainee AS T RIGHT OUTER JOIN Result AS R ON T.TraineeNo = R.TraineeNo
<6>使用UNION合并查询
--查询科目表中的科目编号和科目、查询实习生信息表中的实习生编号和姓名，然后合并 SELECT SubjectNo,SubjectName FROM Subject UNION ALL SELECT TraineeNo,TraineeName FROM Trainee
--在Trainee中查询实习生编号、在Result中查询实习生编号，然后合并 --使用ALL合并所有行 SELECT TraineeNo FROM Trainee UNION ALL SELECT TraineeNo FROM Result
--不使用ALL删除重复行 SELECT TraineeNo FROM Trainee UNION SELECT TraineeNo FROM Result
<7>分组查询
/*--分组查询--*/ --查询获得每门课程的平均成绩 SELECT SubjectNo, AVG(TraineeResult) AS 课程平均成绩 FROM Result GROUP BY SubjectNo
---查询男女实习生的人数各是多少。 SELECT COUNT(*) AS 人数,Sex FROM Trainee GROUP BY Sex
--查询每个年级的总人数。 SELECT COUNT(*) AS 年级人数,GradeId FROM Trainee GROUP BY GradeId
--查询每个科目的平均分并且按照由高到低的顺序排列显示。 SELECT SubjectNo, AVG(TraineeResult) AS 课程平均成绩 FROM Result GROUP BY SubjectNo ORDER BY AVG(TraineeResult) DESC
--统计每个学期男女实习生人数 SELECT COUNT(*) AS 人数,GradeId AS 年级,Sex AS 性别 FROM Trainee GROUP BY GradeId,Sex ORDER BY GradeId
--查询年级总人数超过5人的年级 SELECT COUNT(*) AS 人数,GradeId AS 年级 FROM Trainee GROUP BY GradeId HAVING COUNT(*) > 5
--查询平均分及格的课程信息 SELECT SubjectNo AS 课程编号, AVG(TraineeResult) AS 课程平均成绩 FROM Result GROUP BY SubjectNo HAVING AVG(TraineeResult) >= 60
--查询每门课程及格总人数和及格实习生的平均分 SELECT COUNT(*) AS 人数,AVG(TraineeResult) AS 平均分,SubjectNo AS 课程 FROM Result WHERE TraineeResult>=60 GROUP BY SubjectNo
--查询每门课程及格总人数和及格平均分在70分以上的记录。 SELECT COUNT(*) AS 人数,AVG(TraineeResult) AS 平均分,SubjectNo AS 课程 FROM Result WHERE TraineeResult>=60 GROUP BY SubjectNo HAVING AVG(TraineeResult)>=70
<8>查询中使用函数
/*--字符串函数--*/ SELECT CHARINDEX('PBDEV','My PBDEV Subject',1) SELECT CHARINDEX('PBDEV','My PBDEV Subject',5)
SELECT LEN('SQL Server课程')
SELECT UPPER('sql server课程')
SELECT RTRIM (' 周杰伦 ') SELECT LTRIM (' 周杰伦 ')
SELECT RIGHT('买卖提.吐尔松',3) SELECT RIGHT('买卖提.吐尔松',5)
SELECT REPLACE('莫乐可切','可','兰')
/*--日期函数--*/ SELECT GETDATE() --返回当前系统时间
SELECT DATEADD(MM,4,'02/03/2010') --4个月后 SELECT DATEADD(DD,4,'02/03/2010') --4天后 SELECT DATEADD(YY,4,'02/03/2010') --4年后
SELECT DATEDIFF(MM, '01/06/2010', '05/06/2011') SELECT DATEDIFF(DD, '01/06/2010', '05/06/2011') SELECT DATEDIFF(YY, '01/06/2010', '05/06/2011')
SELECT DATENAME(DW, '12/13/2010') SELECT DATENAME(DAY, '12/13/2010') SELECT DATENAME(DD, '12/13/2010') SELECT DATENAME(MONTH, '12/13/2010') SELECT DATENAME(YY, '12/13/2010')
SELECT DATEPART(DAY, '03/15/2010') SELECT DATEPART(YEAR, '03/15/2010') SELECT DATEPART(MONTH, '03/15/2010')
SELECT DAY('03/15/2010') SELECT YEAR('03/15/2010') SELECT MONTH('03/15/2010')
/*--数学函数--*/ SELECT RAND( )
SELECT ROUND(43.543,1) SELECT ROUND(43.545,2)
/*--系统函数--*/ SELECT CONVERT (VARCHAR (5),12345)
SELECT CURRENT_USER
SELECT HOST_NAME()
<8>使用 SELECT INTO 建立新表
--把Trainee表中的TraineeName、Address、Email列值插入到新建表NewAddressList中 SELECT TraineeName,Address,Email INTO NewAddressList FROM Trainee
SELECT * FROM NewAddressList --确认是否插入数据成功
