实例6(简单查询).sql

经典的sql资料

--上课内容:  第四章 用语句查询表中的数据(简单查询)

-- 4.1.1 准备工作：创建表并插入数据

  -- 4.1.1.1 创建表

create table stu_info
(
t_number char(8),
t_name varchar(10),
t_gender char(2),
t_birthday datetime
)

create table course
(
c_number char(6),
c_name varchar(20),
c_credit int,
c_hour int,
c_teacher varchar(10)
)


create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)


  -- 4.1.1.2 为表设置约束

  --将要设置为主键的t_number字段设为非空

alter table stu_info alter column t_number char(8) not null

--把t_number字段设为主键

alter table stu_info add constraint stu_pr primary key clustered(t_number)


  --将要设置为主键的c_number字段设为非空

alter table course alter column c_number char(6) not null

  --把c_number字段设为主键

alter table course add constraint course_pr primary key clustered(c_number)

  -- 4.1.1.3 插入数据

  --向stu_info表插入数据
insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')

  --向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')
insert into course values('100105','大学英语',NULL,80,'刘杰')

  --向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)

  -- 4.1.1.4 查看输入是否有误，有错误就修改掉

select * from stu_info
select * from course
select * from exam


-- 4.1.2 查询操作

  -- 4.1.2.1.主要结构

SELECT select_list		选择列表（字段名或全选）

[INTO new_table]		新的表

FROM table_source 		表

[WHERE search_condition]	条件语句

[GROUP BY group_by_expr]	分组表达式

[HAVING search_condition]	查询条件

[ORDER BY order_exp[ASC|DESC]]	排序表达式


  -- 4.1.2.2 SELECT语句中至少包括SELECT子句和FROM子句

    -- 使用完全限定名称

      SELECT * FROM [server_name].[database_name].[owner].[object]

      --例：

select * from test.dbo.course
select * from test..course

   -- 4.1.2.2.1 * 表示查询表或视图中的所有记录
    select * from course

   -- 4.1.2.2.2 DISTINCT 指去掉结果集中的重复行（例：查询course表中c_credit字段，并去掉重复值）
    select c_credit from course
    select distinct c_credit from course  

   -- 4.1.2.2.3 SELECT 字段1，字段n 可以指定查询内容（例：查询course表中course_id,course_name,c_credit字段）
    select c_number,c_name,c_credit from course

   -- 4.1.2.2.4 TOP n 从查询结果中输出前n行（例：查询exam表中的前10条记录）
    select * from exam
    select top 10 * from exam

   -- 4.1.2.2.5 TOP n percent 从查询结果中输出前n%行（例：查询course表中前面10%的记录)
    select top 10 percent * from exam

   -- 4.1.2.2.6 INTO 子句 用于创建新表(例：用INTO子句创建一个新表exam1,表中包含exam表的前10%的记录）
    select top 20 percent * into exam1 from exam
    select * from exam1
    
   -- 4.1.2.2.7 WHERE 子句 用于指定查询返回行的条件（例：查询course表中课程是大学英语的记录）
   select * from course where c_name='大学英语'

     -- 4.1.2.2.7.1 在 WHERE 子句中使用逻辑操作符，可以指定多个条件用 AND OR NOT 连接 
       --（例：查询course表中课程是大学英语而且老师是王维的记录）
         select * from course where c_name='大学英语' and c_teacher='王维'

     -- 4.1.2.2.7.2 在 WHERE 子句中使用比较操作符，=,<,>,<=,>=,<> 
       --（例：查询course表中课程是大学英语而且老师是王维的记录）
         select * from course where c_credit>=3 

     -- 4.1.2.2.7.3 在 WHERE 子句中使用 LIKE 或 NOT LIKE 关键字进行模糊查询
       -- 通配符 % 表示可以匹配任意类型和长度的字符串
         --（例：查询course表中姓“王”老师的记录）
           select * from course where c_teacher like '王%'
           select * from course where c_teacher not like '王%'

       -- 通配符 _(下划线) 表示可以匹配任意单个字符
         --（例：查询stu_info表中后4位是0301的记录）
            select * from stu_info where t_number like '200_0301' 

       -- 通配符 [] 表示指定范围或集合中的任意单个字符
         --（例：查询stu_info表中后1位是1、3的记录）
            select * from stu_info where t_number like '2004030[1,3]'

       --通配符 [^] 表示不属于指定范围或集合中的任意单个字符
         --（例：查询stu_info表中后1位不是1、3的记录）
            select * from stu_info where t_number like '%[^1^3]'

     --4.1.2.2.7.4 在 WHERE 子句中使用 BETWEEN 关键字限制查询范围

       -- 注意：在 BETWEEN 中限定的查询范围包括边界值
       --（例：查询course表中c_hour在60和80之间的记录）
        select * from course where c_hour between 60 and 80

     --4.1.2.2.7.5 在 WHERE 子句中使用 IN 关键字用于确定给定的值是否与子查询或列表中的值相匹配

       --（例：查询course表中c_credit为2,3的记录）
        select * from course where c_credit in (2,3)
        select * from course where c_credit  not in (2,3)

     --4.1.2.2.7.6 在 WHERE 子句中使用 NULL 关键字用于判断某个表达式是否为NULL

       --（例：查询course表中c_credit为12,15的记录）
        select * from course where c_credit is NULL
        select * from course where c_credit is not NULL


 --4.1.2.2.8 GROUP BY 子句指定用来放置输出行的组

   --注意：选择列表中任一非聚合表达式内的所有列都应该包含在 GROUP BY 列表中,一定要注意，text、ntext 和 image 类型的列不能用于 group_by_expression

   --（例：查询exam表中每种c_number 平均分的记录）
    select c_number,avg(t_grade) as '平均分' from exam group by c_number

     --使用CUBE分组查询exam表中每种c_number 积分的记录
      select c_number,avg(t_grade) as '平均分' from exam group by c_number with cube

     --使用ROLLUP分组查询exam表中每种c_number 积分的记录
      select c_number,avg(t_grade) as '平均分' from exam group by c_number with rollup

 --4.1.2.2.9 HAVING 子句 通常与 GROUP BY 子句一起使用，用于为分组指定索引条件

  --(例：查询exam表中每种c_number 平均分大于 80的记录)
  select c_number,avg(t_grade)as '平均分' from exam group by c_number having avg(t_grade)>80

 --4.1.2.2.10 ORDER BY 子句用于对查询结果排序

  --注意：NULL是最小值，ASC升序排在最前；DESC降序排在最后
  --(例：对course 表按c_credit 进行降序排序)
  select * from course order by c_credit desc

 --4.1.2.2.11 COMPUTE 子句用于生成统计结果，且放在新的结果集中

  -- （例：统计exam 表中所有课程的平均分）
    select * from course  compute avg(c_credit) 
  -- 我们可以观察一下，算平均分的时候，把NULL算为0







--练习：

-- 1. 查询本数据库服务器上有哪些数据库,查看test数据库信息 

-- 2. 查询test数据库中,stu_info表的信息

-- 3. 查询 test 数据库中 stu_info表中所有性别为'男',而且1984年出生的学生记录 

-- 4. 查询 test 数据库中 stu_info表中前5条记录写入到stu_info04表中

-- 5. 查询 test 数据库中 exam表中所有学生的记录

-- 6. 查询 test 数据库中 用COMPUTE计算exam表中所有课程考试成绩的平均分

-- 7. 查询 Northwind 数据库中 Products 数据表中 ProductID 为 14 的产品信息

-- 8. 查询 Northwind 数据库中 Orders 数据表中没有按时送达的订单

-- 9. 查询 Northwind 数据库中 Territories 数据表中的地域信息，按照地域名升序排序，去掉重复项

-- 10. 查询 Pubs 数据库中 Titles 数据表中 书名含有 'computer' 的书，并按照价格降序排序

-- 11. 查询 Pubs 数据库中 Titles 数据表中每种类型的书的数量