SQL语句汇总

向用户输入数据

 

使用Insert语句

INSERT INTO 表名称 VALUES (值1, 值2,....)

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

 

1、向Department表插入3行数据

insert Departmentvalues('11','数学系')

insert Department(DepartName,DepartNo)values('物理系','12')

insert Department(DepartNo,DepartName)values('13','化学系')

go

select * FromDepartment

 

出现错误一般情况：一是重复值，如DepartNo值是唯一的不允许输入重复值；二是有些列值不允许为空

 

使用insert与select子句输入数据

Insert每次只能输入一行数据，insert语句与select子句配合可以一次输入多行数据

Insert table_name

Select column_list  From  table_list where  search_conditions

注意：insert中指定表和select子句得到的结果集一定要兼容，即列的数量和顺序必须相同，列的数据类型和长度要相同，或者能够自动进行数据类型转换

 

2、将Department表中的数据输入到Department1(该表不存在)表中

createtable Department1(

DepartNo nvarchar(2)notnull,

DepartName nvarchar(30)notnull

)

go

 

insert Department1select*From Department

 

使用select  into 输入数据

select  into是在输入数据的过程中建立新表

SQL SELECT INTO 语法

您可以把所有的列插入新表：

SELECT *

INTO new_table_name [IN externaldatabase]

FROM old_tablename

或者只把希望的列插入新表：

SELECT column_name(s)

INTO new_table_name [IN externaldatabase]

FROM old_tablename

 

3、将Department表中的数据行输入到Department2表中

         select * intoDepartment2 From Department

 

修改用户数据

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

 

4、需要将‘电子出版概论’课程的上课时间修改为‘周二晚’

         update Courseset SchoolTime='周二晚'where CouName='电子出版概论'

 

5、现需要将‘01数据库’班所有学生的选课密码初始化为‘123456’

         update Studentset Pwd='123456'where ClassNo=(select ClassNoFromClass where ClassName='01数据库')

 

    update StudentSet Pwd='123456'From Student ,Classwhere ClassName='01数据库'and Class.ClassNo=Student.ClassNo

 

删除用户表数据

DELETE FROM 表名称 WHERE 列名称 = 值

 

6、学号为‘00000005’的同学因故取消课程编号为‘017’的选修课

delete StuCou where StuNo='00000005' and CouNo='017'

go

update Courseset WillNum= WillNum-1where CouNo='017'

go

因为该同学取消‘017’的课程，所以课程Course表017的课程报名人数减少1，所以update子句写为：update Courseset WillNum= WillNum-1where CouNo='017'

 

7、学号为00000005的同学因故取消'中餐菜肴制作' 选修课程

delete StuCouFrom StuCou,Coursewhere StuNo='00000005'and CouName='中餐菜肴制作'and StuCou.CouNo=Course.CouNo

go

 

update Courseset WillNum=WillNum-1where CouName='中餐菜肴制作'

go

----------------------------------------------------------------------------------------------------淫荡的分割线----------------------------------------------------------------------------------------------------------------

sqlcmd -Usa 设置

SQL Server Configuration Manager -> 网络配置 -> 协议

               tcp/ip属性

               保持活动状态 --> 30000

               全部侦听     --> 否

               无延迟       --> 否

               已启用       --> 是

               IP地址

               IP地址      --> 你的IP   //本地ip地址处

               TCP动态端口 --> 不填

               TCP端口     --> 1433

               活动        --> 是

               已启用      --> 是

 

Select 什么  From 那个表 Where，order by有什么限制条件等

select 要查询那些列 From要查询的列来自哪些列 where查询条件 compute聚合函数

1、从student表中查询学号为00000001同学的学号和姓名

  

   查询的是学号StuNo 和姓名 StuName即是select StuNo ,StuName

   从表Student中即是 from Student

   限制条件是StuNo='00000001' 即是 whereStuNo='000001'

  

   select StuNo,StuNamefrom StudentwhereStuNo='00000001'

 

2、从课程表(Course)中查询课程类别，要求消除值相同的那些行 (消除相同值)

      

   课程类别的列名为Kind ，select子句为：select Kind

  Kind来自Course表，from子句为：from Course

   消除值相同使用distinct， select的子句为：select distinct Kind

  

   select distinctKind from Course

   select distinctKind ,Credit fromCourse

 

3、从学生表(Student)中查询所有的信息，要求只显示查询结果的前6行或者6%

 

   限制条件是查询结果的前6行，select子句为select top 6 *

   查询结果的6%，select子句为select top 6 percent *

       select top 6 * from Student

  

4、改变查询结果列标题三种方法

   (1)第一种方法：

      select'课程编码'=CouNo,'课程名称'=CouName,'教师'=Teacher,'上课时间'=SchoolTime,'限制选课人数'=LimitNum,'报名人数'=WillNum

From Course

 

   (2)第二种方法：

      select CouNo'课程编号',CouName'课程名称',Teacher'教师',SchoolTime'上课时间',LimitNum'限制选课人数',WillNum'报名人数'From Course

 

   (3)第三种方法：

      select CouNoas'课程编号',CouNameas'课程名称',Teacheras'教师',SchoolTimeas'上课时间',LimitNumas'限制选课人数',WillNumas'报名人数'From Course

 

5、在查询结果中显示字符串(显示字符串)

  select '课程名称'=CouName,'课程编号为：','课程编号'=CouNofrom Course

 

6、在课程表中查询课程类别为'信息技术'而且学分为2的课程信息(where限制)

   select*from CoursewhereKind='信息技术'and Credit='2'

 

7、查询课程表的课程信息、报名人数与限制人数之比(表达式可以是列名、函数、或常数的列表)

   select*,WillNum/LimitNumfrom Course

 

8、查询课程表中最小的报名人数、最大的报名人数以及平均报名人数(MIN函数,MAX函数,AVG函数)

  select '最小的报名人数'=MIN(WillNum),'最大的报名人数'=MAX(WillNum),'平均报名人数'=AVG(WillNum)From Course

 

9、查询课程信息、报名人数与限选人数之比。要求查询结果按照报名人数升序排序(ASC指明为升序，DESC指明降序，系统默认升序排列，order  by排列查询结果)

Select*,WillNum/LimitNum'报名人数与限选人数之比'from Courseorderby WillNum ASC

Select *,WillNum/LimitNum'报名人数与限选人数之比'from Courseorderby 报名人数与限选人数之比

 

10、查询课程表的教师名、课程编号、课程名，要求查询结果首先按照教师名降序排列，教师名相同时，按照课程编号排序

select Teacher'教师名',CouNo'课程编号',CouName'课程名'from Course order by Teacherdesc,CouNo

 

11、查询课程编号为004,007,013的课程信息 (in子句给出列值范围，004,007,013为或的关系)

select*fromCoursewhere CouNo='004'or CouNo='007'or CouNo='013'

 

   select*from Coursewhere CouNoin('004','007','013')

 

12、查询课程编号不为004,007,013的课程编号和课程名称(not in)

   select CouNo,CouNamefrom CoursewhereCouNonotin('004','007','013')

 

   select CouNo,CouNamefrom Coursewhere CouNo<>'004'and CouNo <>'007'and CouNo<>'013'

 

13、like实现模糊查询  (%匹配任意长度，_匹配任意单个字符，[ ]匹配所给定范围或集合中任意单个字符，[^]匹配所给定的不在范围或集合中任意单个字符)

查询课程名以字母D开始的课程信息

select * fromCourse where CouNamelike'D%'

 

14、查询课程名以 ’制作’ 两个结尾的课程信息

  select * from Course whereCouNamelike'%制作'

 

15、查询姓名第二个字为 ’宝’ 的学生信息

  select * from Student whereStuNamelike'_宝%'

 

16、查询不姓刘的学生信息

  select * from Student whereStuNamelike'[^刘]%'

  select * fromStudent where StuNamenotlike'刘%'

 

17、查询课程表中教师姓名未定的课程信息(isnull查询指定列未输入的数据行)

  select * fromCourse where Teacherisnull

 

18、查询报名人数少于15人的课程信息 (= , < >不等于,!=,between ,not between ,and, or )

  select * fromCourse where WillNum<15

 

19、查询报名人数少于25，多于15人的课程信息

  select * from Course where WillNum>15 and WillNum<25

  select * from Course where WillNum between 15 and 25

 

20、查询报名人数少于25，多于15人的课程信息，要求查询结果按照报名人数降序排列(between)

  select * from Course whereWillNumbetween 15and25orderbyWillNum desc

 

21、查询报名人数多于25人或者少于15人的课程信息，要求查询结果按照报名人数降序排列(notbetween)

  select * fromCourse where WillNumnotbetween 15and25orderbyWillNumdesc

 

22、查询课程表中‘信息技术’类课程信息，并计算平均报名人数compute进行计算(Select要查询那些列 From要查询的列来自哪些列 where查询条件 compute聚合函数)

select  *from Coursewhere Kind='信息技术'computeavg(WillNum)

 

22、compute by 分组查询结果，必须先使用orderby 对compute by指定的列进行排序

Select 要查询那些列 From要查询的列来自哪些列 where查询条件 orderby要分组显示列名 compute聚合函数by要分组显示的列名

按类别显示课程信息，并计算各类课程的平均报名人数(需要分组统计，又要显示详细信息，用compute by)

select * fromCourse  orderby Kindcompute avg(WillNum)by Kind

 

23、group by 分组汇总查询结果(只需要显示分组结果，不需要详细信息，用group by)

按课程类别分组统计各类课程的平均报名人数

分析：查询结果应有两列课程类别Kind列的值，每类平均报名人数avg（WillNum）的值。Select Kind'课程类别' ,avg(WillNum)'每类平均报名人数'；课程信息来自Course表，所以fromCourse；该题要求汇总结果不需要详细信息，所以使用group by 子句，因对课程类别进行分组计算，所以by Kind。

select  Kind'课程类别',avg(WillNum)'每类平均报名人数'from Coursegroupby Kind

 

24、查询‘信息技术’类课程的平均报名人数 （having子句用于限定对组或者聚合函数的查询条件）

  select Kind'课程类别',avg(WillNum)'每类平均报名人数'from Course where Kind='信息技术'groupby Kind

 

select Kind'课程类别',avg(WillNum)'每类平均报名人数'from Course groupby Kindhaving Kind='信息技术'

 

25、select子句中having子句中使用聚合函数，但是where子句中不能使用聚合函数

查询平均报名人数大于25人的课程类别和每类平均报名人数

  select Kind'课程类别',avg(WillNum)'每类平均报名人数'from Course  groupby KindHAVINGavg(WillNum)>25

 

总结：where对表的原始数据行进行过滤，having对查询结果数据行进行过滤

 

26、子查询是在查询中包含另一个查询的查询，可以使用子查询代替表达式。子查询只能返回一行数据，有时只能返回单个值

例子：查询报名人数大于平均报名人数的课程信息

select * from Course whereWillNum>(selectavg(WillNum)'平均报名人数'from Course )

 

注意：子查询返回的值多于一个就会错误，当子查询跟随在= 、！= 、<、<=、>、之后，子查询用作表达式是不允许的

 

27、union将两个或多个查询结果合并为一个结果。当使用union是需要遵循下面两个规则：

(1)所有查询中的列数和列的顺序必须相同

(2)所有查询中按顺序对应列的数据类型相同或兼容

例子：从班级表中查询班级名，从系部表中查询系统部名称，使用union运算符合并两个查询结果

select ClassName'班级和系名'from Class

union

select DepartNamefrom Department

两个查询结果列数相同都是1列，ClassName和DepartName的数据类型均为char

 

28、从班级表中查询班级名，从系部表中查询系统部名称，使用union运算符合并两个查询结果，要求按降序排列

select ClassName'班级和系名'from Class

union

select DepartName  fromDepartment

order by ClassName desc （列名'DepartName' 无效，如果该语句包含UNION、INTERSECT 或EXCEPT 运算符，则ORDER BY 项必须出现在选择列表中）

 

29、查询来自多个表或视图的信息

(1)对多个表或视图进行查询时，必须使用where子句将表与表进行连接，要写出连接条件，否则就会出现笛卡儿积=A*B（笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。）造成大量的无意义的数据存在，达不到查询的目的，甚至由于占用存储空间过多，导致系统超载、效率低下

(2)连接条件，一般来说，对来自N个表的查询要写出N-1个连接。

 

例子：查询班级信息，要求显示班级编号、班级名称、班级所在的系部编号、系统名称

 

分析：从题意得知，显示结果有4列，分别班级编号ClassNo、班级名称ClassName、班级所在的系部编号DepartNo、系统名称DepartName。所以select 子句为：selectClassNo,ClassName,DepartNo,DepartName

因为select子句中4个列分别来自Class表、Department表，所以From子句为：From Class，Department

连接条件，在Class表中有ClassNo，ClassName和DepartNo，在Department表中有DepartNo和DepartName，所以连接条件为：Class表中的系部编号DepartNo和Department表中系部编号DepartNo相等，写为：Class.DepartNo =Department.DepartNo

 

select  ClassNo,ClassName,DepartNo,DepartNamefrom Class,Department

where Class.DepartNo=Department.DepartNo

然而这样写会报错，因为DepartNo不明确，因为系统不知道使用哪个表的DepartNo，所以如果使用Class表则为：select ClassNo ,ClassName, Class.DepartNo ,DepartName

如果使用Department表则为:

select ClassNo ,ClassName, Department.DepartNo,DepartName

 

表与表之间的连接有内连接、外连接和交叉连接

内连接就是只包含满足连接条件的数据行

外连接就是连接运算的扩展，用它可以处理查询时的信息缺失。外连接又分为左连接、右连接和全连接3种

交叉连接是一种比较少用的连接，交叉连接的结果就是笛卡尔积

在SQL中可以使用两种连接语法形式：一种是ANSI连接语法形式，另外一种是使用sql 连接语法形式。上面这题就是sql server连接语法形式

 

相等连接和自然连接

30、查询学生信息，显示信息包括学生基本信息和班级名称

  select Student.*,ClassNameFromClass,StudentwhereStudent.ClassNo=Class.ClassNo  （sql server连接语法）

 

  select Student.*,ClassNamefromClassjoin StudentonClass.ClassNo=Student.ClassNo （ANSI连接语法）

  这题中查询结果只显示Student表的ClassNo列，没有显示Class表中的ClassNo列，将查询完全相同的ClassNo列只保留了一列，该链接是自然连接

 

  如果该题select子句为：select Student.* ,Class.*,得到的查询结果中会有两列值完全相同的ClassNo，它的连接列，在Student表、Class表中都存在此列，此时连接是相等连接

 

31、查询学生选课信息，要求显示姓名、课程名称、志愿号，按姓名和志愿号排序

         select Student.StuName,Course.CouName, StuCou.WillOrderFromStudent,Course,StuCouwhereStudent.StuNo=StuCou.StuNo and

StuCou.CouNo=Course.CouNoorderby Student.StuName,WillOrder

（存在相同的列才能连接，连接的关键是相同的列）

Student表的主键是StuNo，Course表主键是CouNo,StuCou表有两个主键StuNo和CouNo

  

那么使用ANSI为

         select Student.StuName,Course.CouName, StuCou.WillOrderFrom

StuCou join StudentonStuCou.StuNo=Student.StuNojoin Courseon StuCou.CouNo=Course.CouNoorderby Student.StuName,WillOrder

 

32、查询学生报名“计算机应用工程系”开设的选修课程情况，显示信息包括学生、姓名、课程名称和教授教师

    select Student.StuName,Course.CouName,Course.TeacherFrom Student,StuCou,Course,Department

where StuCou.StuNo = Student.StuNo and StuCou.CouNo = Course.CouNo and Course.DepartNo = Department.DepartNo

and DepartNamelike'计算机应用工程系'

 

    StuCou.StuNo= Student.StuNo查询对应的姓名；StuCou.CouNo= Course.CouNo查询对应的课程名称和教师；Course.DepartNo= Department.DepartNo关联开设课程系部

 

比较连接

33、查询每个班级可以选修的、不是自己所在系部开设的选修课程的信息，显示信息包括班级、课程名、课程类别、学分、老师、上课时间、报名人数和系部名称

Select ClassName ,CouName ,kind,Credit,Teacher,SchoolTime,WillNum,

DepartName From Class ,Course ,Department whereClass.DepartNo<>Course.DepartNo

    限制条件表示为：Course的系部编号DepartNo与Class的系部编号DepartNo不同

 

自身连接

自连接就是一个表和他自身进行连接，属于多表连接的特殊情况。在自连接中，要先将一个表定义为两个不同的名字，然后在From子句中使用这两个名字，在where子句中需要写出 一个连接条件。须注意，在select子句中对列的引用形式为：所定义的表名.列名

 

34、查询课程类别相同但开课系部不同的课程信息，要求显示课程编号、课程名称、课程类别和系部编号，并按照课程编号升序排列查询

         selectdistinct C1.CouNo,C1.CouName,C1.Kind,C1.DepartNofrom Course C1,Course C2

where C1.Kind= C2.Kindand C1.DepartNo<> C2.DepartNoorderby C1.CouNo

 

外连接

使用外连接可以解决使用内连接所产生的信息不完整的问题。外连接分为左连接、右连接和全连接，外连接的sql语句具体体现在From子句上

左连接的From为： From 左表名 left join 右表名 on 连接条件

右连接的From为： From 左表名 right join 右表名 on 连接条件

全连接的From为： From 左表名 Full join 右表名 on 连接条件

A left join（左连接）包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。

right join（右连接），即包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。

full join(全连接)，左右表中所有记录都会选出来，为了包含两个表都不匹配的那些数据行。

左、右连接的关键：首先要确定那个表需要保证完整信息；全连接哪个是左表哪个是右表都无关紧要

 

左连接

35、查询所有学生报名选修课程的详细情况，要求包括已报名选修课程的学生，也包括未报名选修课程的学生情况，显示内容有学号StuNo、课程编号CouNo、课程名称CouName

        select  StuNo,CouName,Course.CouNofrom Courseleftjoin StuCouon Course.CouNo= StuCou.CouNoorderby Course.CouNo

 

右连接

select StuNo ,CouName ,Course.CouNofrom StuCourightjoin Courseon Course.CouNo= StuCou.CouNoorderby Course.CouNo

36、使用右连接查询学生的已报名和为报名的课程信息，显示课程编号、课程名称、学号

         select Course.CouNo,CouName,StuNoFrom Courserightjoin StuCouon Course.CouNo=StuCOu.CouNo

 

全连接

37、使用全连接查询学生报名信息（课程信息、课程名称、学号）

         select Course.CouNo,CouName,StuNoFrom CourseFulljoin StuCou on Course.CouNo=StuCOu.CouNo

 

使用表别名

38、查询学生选课信息，要求显示姓名、课程名称、志愿号，按姓名和志愿号排序

         select StuName,CouName,WillOrderFrom StuCou sc,Students,Course cwheresc.StuNo= s.StuNoand sc.CouNo= c.CouNo

order by StuName,WillOrder

 

使用关键字exists

在where子句中可以使用exists子句，它用于测试跟随的子查询中的行是否存在，如果存在，则返回true 值

39、查询已经报名选修课程的学生信息，要求显示学号和姓名

         select Student.StuNo,StuNameFrom Studentwhere  exists(select StuNoFromStuCouwhere StuNo=Student.StuNo)

 

         select distinct Student.StuNo,StuNameFromStudent,StuCouwhereStudent.StuNo=StuCou.StuNo