SQL数据库的T-SQL 查询语句

T-SQL 查询语句

一、简单查询

1、列出全部学生的信息。

SELECT * FROM 学生表

2、列出软件测试专业全部学生的学号及姓名。

SELECT 学号,姓名 FROM 学生表 WHERE 专业=‘软件测试’

3、列出所有必修课的课号。

方法一：SELECT 课号 FROM 课程表 where 类型='必修课'

方法二：SELECT DISTINCT 课号 FROM 选课表 where 类型='必修课'

4、求1号课成绩大于80分的学生的学号及成绩，并按成绩由高到低列出。

SELECT 学号,成绩 FROM 选课表 WHERE 课号='1' AND 成绩>80 ORDER BY 成绩 DESC

5、列出非软件测试专业学生的名单。

方法一：SELECT 姓名 FROM 学生表 WHERE 专业<>'软测试件'

方法二：SELECT 姓名 FROM 学生表 WHERE NOT 专业='软件测试'

方法三：SELECT 姓名 FROM 学生表 WHERE 专业!='软件测试'

6、查询成绩在70~80分之间的学生选课得分情况

方法一：SELECT * FROM 选课表 WHERE 成绩>=70 AND 成绩<=80

方法二：SELECT * FROM 选课表 WHERE 成绩 BETWEEN 70 AND 80

不在此范围内的查询：（注意写出和以下语句等价的语句）

SELECT * FROM 选课表 WHERE 成绩 NOT BETWEEN 70 AND 80

7、列出选修1号课或3号课的全体学生的学号和成绩。

方法一：SELECT 学号,成绩 FROM 选课表 WHERE 课号='1' OR 课号='3'

方法二：SELECT 学号,成绩 FROM 选课表 WHERE 课号 IN ('1','3')

相反条件查询：SELECT 学号,成绩 FROM 选课表 WHERE 课号 NOT IN ('1','3')

8、列出所有10级学生的学生成绩情况。

SELECT * FROM 选课表 WHERE 学号 LIKE '10%'

SELECT * FROM 选课表 WHERE 学号 LIKE '10_ _ _ _ _ _'

相反条件查询：SELECT * FROM 选课表 WHERE 学号 NOT LIKE '10%'

9、列出成绩为空值(或不为空值)的学生的学号和课号。

答案一：SELECT 学号,课号 FROM 选课表 WHERE 成绩 IS NULL

答案二：SELECT 学号,课号 FROM 选课表 WHERE 成绩 IS NOT NULL

10、求出所有学生的总成绩。

SELECT SUM(成绩) AS 总成绩 FROM 选课表

11、列出每个学生的平均成绩。

SELECT 学号,AVG(成绩) AS 平均成绩 FROM 选课表 GROUP BY 学号

12、列出各科的平均成绩、最高成绩、最低成绩和选课人数。

SELECT 课号,AVG(成绩) AS 平均成绩,MAX(成绩) AS 最高分, MIN(成绩) AS 最低分,COUNT(学号) AS 选课人数 FROM 选课表 GROUP BY 课号

二、连接查询

（一）简单连接

1、列出选修1号课的学生姓名及成绩。

SELECT 姓名,成绩 FROM 学生表,选课表 WHERE 学生.学号=选课.学号 AND 课号='1'

2、列出选修1号课的学生的学号、姓名及成绩。

SELECT S.学号,姓名,成绩 FROM 学生表 S,选课表 X WHERE S.学号=X.学号 AND 课号='1'

3、求出总分大于150的学生的学号、姓名及总成绩。

SELECT 学生表.学号,姓名,SUM(成绩) AS 总成绩 FROM 学生表,选课表

WHERE 学生表.学号=选课表.学号 GROUP BY 选课表.学号 HAVING SUM(成绩)>150

（二）自连接查询

1、列出那些专业相同的学生相应的姓名及专业信息。

SELECT a.姓名,b.姓名,专业 FROM 学生表 a,学生表 b WHERE a.学号<>b.学号 AND a.专业=b.专业

2、求至少选修1号课和2号课的学生的学号。

SELECT X.学号 FROM 选课 X,选课 Y WHERE X.学号=Y.学号 AND X.课号='1'AND Y.课号='2'

（三）超连接

1、列出选修1号课的学生姓名及成绩。

方法一：（使用简单连接查询格式）

SELECT 姓名,成绩 FROM 学生表,选课表 WHERE 学生表.学号=选课表.学号 AND 课号='1'

方法二：（使用内部连接格式）

SELECT 姓名,成绩 FROM 学生表 INNER JOIN 选课表 ON 学生表.学号=选课表.学号 WHERE 课号='1'

方法三：内部连接的INNER短语可以省略。（与方法二等价）

SELECT 姓名,成绩 FROM 学生表 JOIN 选课表 ON 学生表.学号=选课表.学号 WHERE 课号='1'

2、查询没有选修任何课程的学生姓名。

方法一：使用嵌套查询

SELECT 姓名 FROM 学生表 WHERE 学号 NOT IN (SELECT 学号 FROM 选课表)

方法二：使用超连接的右连接。

SELECT 姓名 FROM 选课表 RIGHT JOIN 学生表 ON 选课表.学号=学生表.学号 WHERE 选课表.学号<>学生表.学号

方法三：使用超连接的左连接。（注意表名顺序和方法二的不同）

SELECT 姓名 FROM 学生表 LEFT JOIN 选课表 ON 选课表.学号=学生表.学号 WHERE 选课表.学号<>学生表.学号

三、嵌套查询

（一）普通嵌套与谓词EXISTS

1、列出选修c语言课的学生的学号。

方法一：

SELECT 学号 FROM 选课表 WHERE 课号=(SELECT 课号 FROM 课程表 WHERE 课名='c语言')

方法二：使用谓词EXISTS。注意和方法一格式上的不同。

SELECT 学号 FROM 选课表 WHERE EXISTS (SELECT * FROM 课程表 WHERE 课名='c语言' AND 选课表.课号=课程表.课号)

方法三：

SELECT 学号 FROM 选课表 WHERE 课号 IN (SELECT 课号 FROM 课程表 WHERE 课名='c语言')

（二）量词ANY、SOME、ALL

1、求选修2号课的学生中，成绩比选修1号课的最低成绩要高的学生的学号和成绩。

方法一：

SELECT 学号,成绩 FROM 选课表 WHERE 课号='2' AND 成绩>

(SELECT MIN(成绩) FROM 选课表 WHERE 课号='1')

方法二：ANY等价于SOME，所以可将ANY换成SOME。

SELECT 学号,成绩 FROM 选课表 WHERE 课号='2' AND 成绩>ANY

(SELECT 成绩 FROM 选课表 WHERE 课号='1')

2、求选修2号课的学生中，成绩比选修1号课的任何学生的成绩都要高的那些学生的学号和成绩。

方法一：

SELECT 学号,成绩 FROM 选课表 WHERE 课号='2' AND 成绩>

(SELECT MAX(成绩) FROM 选课表 WHERE 课号='1')

方法二：

SELECT 学号,成绩 FROM 选课表 WHERE 课号='2' AND 成绩>ALL

(SELECT 成绩 FROM 选课表 WHERE 课号='1')

（三）内外层互相关嵌套（外层依赖于内层的查询结果，内层依赖于外层来进一步查询）

1、列出每门课程中成绩最高的选课信息。

SELECT * FROM 选课表 A WHERE 成绩=(SELECT MAX(成绩) FROM 选课表 B WHERE A.课号=B.课号)

2、列出每个学生中成绩低于本人平均成绩的选课信息。

SELECT * FROM 选课表 A WHERE 成绩<(SELECT AVG(成绩) FROM 选课表 B WHERE A.学号=B.学号)

四、操作功能

1、在课程表中插入新的元组（5，大学英语）。

INSERT INTO 课程表(课号,课名) VALUES('5', '大学英语')

2、给学生表中男生的记录加上删除标记。

DELETE FROM 学生表 WHERE 性别='男'

3、将选课表中所有课程的成绩分别提高5分。将课号为“4”的课程的成绩置为空值。

UPDATE 选课表 SET 成绩=成绩+5

UPDATE 选课表 SET 成绩=NULL WHERE 课号='4'

 

案例一：完成下面的查询，使用“products”表。

2、需求描述

在products表中查询出厂日期晚于2014年4月的水果信息

          select * from products where 出厂日期>2014-04 and 种类='水果'

 

在products表中总成本

              select sum (成本) 总成本  from products

 

在products表中查询所有水果的信息，并按照成本从高到低的顺序显示结果

select * from products

where 种类='水果' order by 成本 desc

 

在products表中查询成本在1—5元之间的蔬菜信息

 

                   select * from products

where 成本 between 1 and 5 and 种类='蔬菜'

 

将products表中所有水果的名称、种类和出厂日期信息插入到新表products_new中 .

select 名称,种类,出厂日期 into new01 from products

where 种类='水果'

查询成本低于10元的水果信息

 

                 select * from products

                 where 成本<10 and 种类='水果'

将所有蔬菜的价格上调1元

  update products set 成本=成本+1 where 种类='蔬菜'

 

查询成本最高的5个产品信息

 

                select top 5 * from products

                order by 成本 desc 

                select distinct 种类 from products

查询有哪些产品种类

 select distinct 种类 from products

案例二、完成下面的查询（使用“student”表）

实验需求：

1查询王明的成绩？结果：王明 90

Select 姓名，成绩  from  student where 姓名=’王明’

 

给王明加2分？

Update student set 成绩=成绩+2  where 姓名=’王明’

 

查询备注不为空的学生？

Select  *  from  student  where 备注  is not null

 

查询成绩大于95分以上的学生？

Select  *  from  student  where 成绩>95

 

王明转学，把王明删掉？

Delete from student  where 姓名=‘王明’

 

来了一位新同学，名字叫于美丽？

Insert into student  values(20,’于美丽’,’110110110’,3,100,’勤奋好学’)

查询成绩大于95分，并且班级是2班的都有谁？

Select * from student  where 成绩>95 and 所在班级=2

 

查询成绩大于95分，并且班级是1、2班的都有谁？

Select * from student  where 成绩>95 and 所在班级 between 1  and  2

 

查询平均成绩？

Select avg(成绩) as 平均成绩  from student

 

 

使用truncate删除所有的数据

Truncate  table  student

案例三:

1、向employee表中插入一行数据，要求姓名：张三,身份证号：777666555444333222

职务：运维工程师, 出生日期：2000/10/1,  基本工资：9000

insert into employee (姓名,身份证号,职务,出生日期,基本工资)

values ('张三','777666555444333222','运维工程师','2000/10/1',9000)

 

2、更改employee表中黄蓉的基本工资为11000

update employee set 基本工资='11000' where 姓名='黄蓉'

 

3、查询employee 表中的所有员工信息

select * from employee

 

4、查询employee表中姓名、职务、基本工资列的内容

select 姓名,职务,基本工资 from employee

 

5、查询所有运维工程师的姓名

select 姓名 from employee where 职务='运维工程师'

6、查询基本工资为8000~10000的员工所有信息

select * from employee where 基本工资 between 8000 and 10000

 

7、查询基本工资<10000或>20000的员工所有信息

select * from employee where 基本工资<10000 or 基本工资>20000

 

8、查询基本工资为8000、9000和1000的员工所有信息

select * from employee where 基本工资 in (8000,9000,10000)

 

1. 查询身份证号以66开头的员工所有信息

    select * from employee where 身份证号 like '66%'

 

1. 查询姓杨的运维工程师的信息

 

    select * from employee where 姓名 like '杨%' and 职务='运维工程师'

 

1. 查询备注不为空的员工所有信息

select * from employee where 备注 is not null

 

1. 查询employee表中前5行的数据

 

   select top 5 * from  employee

 

1. 查询employee表中“姓名”和“身份证号”两列数据，查询结果“姓名”列名称显示为“name”,“身份证号”列名称显示为“idcard”

   select 姓名 as name , 身份证号 as idcard from employee

 

1. 查询employee表中所有员工信息，按照基本工资从高到低显示查询结果

select * from employee order by 基本工资 desc

 

1. 查询employee表中有哪些职务（去除重复的职务）

select distinct 职务 from employee

 

1. 在employee表中列出满足身份证号的左起第三位是0、除CTO以外的，所有员工的姓名、身份证号、职务和基本工资，其中姓名字段显示为name，查询结果按照基本工资的由高到低排列。

select 姓名 as name,身份证号,职务,基本工资 from employee

where 身份证号 like '_0%' and 职务!='cto'

order by 基本工资 desc

 

1. 将employee表中所有员工的姓名、身份证号和职务生成一个新表new01

select 姓名,身份证号,职务 into new01 from employee

 

1. 将employee表中所有基本工资大于等于15000的员工的姓名、职务和出生日期保存到新表new02。（提前先创建表new02）
2. insert into new02(姓名,职务,出生日期)
3. select 姓名,职务,出生日期 from employee
4. where 基本工资>=15000

 

19、将employee表中所有员工的姓名、职务和出生日期，以及新输入2名员工相关信息，一起保存到新表new03。（提前先创建表new03）,新输入的2名员工信息如下：

'欧阳锋','人事经理','1988-08-08'  ,  '一灯','财务经理','1977-07-07'

insert into new01(姓名,职务,出生日期)

select '欧阳锋','人事经理','1988-08-08' union

select '一灯','财务经理','1977-07-07' union

select 姓名,职务,出生日期 from employee

 

1. 删除employee表中杨过的记录（使用delete语句）

  delete from employee where 姓名='杨过'

 

1. 删除employee表中所有记录（使用delete语句）

delete from employee

 

 22、删除new01表中所有记录（使用truncate语句）

truncate table new01