Microsoft SQL Server学习（六）--查询语句

• 联合查询

use student

--建表
create table class_A(
id int primary key,
name varchar(50),
sex char(50),
course varchar(50),
score float
)
--建表
create table course(
cno int primary key,
cname varchar(50),
number int
)
--插入数据
insert into course values (1,'java',2)
insert into course values (2,'python',1)
insert into course values (3,'Linux',1)
insert into course values (4,'oracle',1)
insert into course values (5,'music',1)
insert into course values (6,'actor',1)
insert into course values (7,'singer',1)
--插入数据
insert into class_A values(1,'刘备','male','java',85)
insert into class_A values(2,'周瑜','male','python',76.56)
insert into class_A values(3,'貂蝉','female','Linux',99)
insert into class_A values(4,'关羽','male','oracle',69.66)
insert into class_A values(5,'文章','male','java',55)
insert into class_A values(6,'玛丽','female','music',45)
insert into class_A values(7,'麦当娜','female','actor',99)
insert into class_A values(8,'vava','female','singer',75)

insert into class_A values(10,'lomo','','','')
insert into course values (9,'','')

1.联合查询

(1)笛卡尔积

select * from 表名1， 表名2

(2)内联

1)

select * from 表名1 inner join 表名2

on 表名1.列名1=表名2.列名2

2)

select * from 表名1,表名2

where表名1.列名1=表名2.列名2

(3)外联

   1)左联：select * from 表1 left join 表名2 on 表名1.列名1=表2.列名2
   2)右联：select * from 表1 right join 表名2 on 表名1.列名1=表2.列名2
   3)以左联解释：以左表为主，右表为辅，若左表数据多，缺少的数据以null填充；若左表数据少，多余的数据不显示

(4)全联：

两张表全部显示，若不存在数据关联，缺少的数据以null填充
select * from 表名1 full join 表名2
on 表名1.列名1=表名2.列名2

(5)自联：

Select * from 旧表名 新表名1，旧表名 新表名2
where 新表名1.列名1=新表名2.列名2

--联合查询
--笛卡尔积
select * from class_A,course

--内联
--方法一
select * from class_A,course
where class_A.course=course.cname
--方法二
select * from class_A inner join course
on class_A.course=course.cname

--外联
--左联
select * from class_A left join course on
class_A.course = course.cname

--右联
select * from class_A right join course on
class_A.course = course.cname

--全联
select * from class_A full join course on
class_A.course = course.cname

use student

学生信息表
create table student_info(
student_id int primary key,
student_name varchar(50),
student_sex char(50),
born_date datetime,
class_no int,
tele_number int,
ru_date datetime,
address text,
commet varchar(50)
)

课程信息表
create table course_info(
course_no int primary key,
course_name varchar(50),
course_type varchar(50),
course_start int,
course_time int,
course_score int
)

--成绩信息表
create table result_info(
student_id int ,
course_no int ,
result int
)

--班级信息表
create table class_info(
class_no int primary key,
class_name varchar(50) ,
director varchar(50),
profession varchar(50)
)

--select语句中，一个字段前加上单引号字符串，对后面的字段进行说明。
select student_name,'家庭住址',address from student_info

select student_name, year(GETDATE())-year(born_date) as 年龄 from student_info

--discinct 去重复项记录
select distinct class_no from student_info

--列出女性学生的姓名的家庭住址
select student_name,'家庭住址' as 描述,address
from student_info
where student_sex = '女'

--列出2014年以后出生的学生的基本信息
select * from student_info
where year(born_date) >'2014'

select * from student_info
where born_date >'2014-12-31'

--列出广州的男同学的姓名、电话和地址
select student_name,tele_number,address
from student_info
where student_sex='男'
and address like '广州%'

--列出电话开头是136的学生基本信息

select * from student_info
where tele_number like '136%'

--列出所有不是2岁的同学的姓名、性别、出生年月
select student_name,student_sex,born_date
from student_info
where (YEAR(GETDATE())-YEAR(born_date))!=2

--特殊运算符使用
--[NOT]between...and...
--列出所有2-3岁之间的同学的姓名、性别、出生年月

select student_name,student_sex,born_date
from student_info
where (YEAR(GETDATE())-YEAR(born_date)) not between 2 and 3 

select student_name,student_sex,born_date
from student_info
where (YEAR(GETDATE())-YEAR(born_date))  between 2 and 3 

--IS NULL 判断表达式的值是否为空值
--列出所有注释内容为空的同学的信息

select * from student_info
where commet is null

--IS NOT NULL 不为空
select * from student_info
where commet is not null

/*
  IN 可以指定一个值表，值表中列出所有可能的值
  当表达式与任意一个值进行匹配，成功返回TRUE
  失败返回FALSE
*/

--列出所有2000001和2000002班的学生的学号、姓名、性别
select student_id,student_name,student_sex
from student_info
where class_no in (2000001,2000002)

select student_id,student_name,student_sex
from student_info
where class_no =2000001 or class_no =2000002

--NOT IN
--列出不是2000001，也不是2000002班的学生的学号、姓名、性别
select student_id,student_name,student_sex
from student_info
where class_no NOT in (2000001,2000002)

--ORDER BY  排序
--ASC 顺序    DESC倒序
/*
ORDER BY _column1, _column2;  _column1升序，_column2升序 

ORDER BY _column1, _column2 DESC;  _column1升序，_column2降序 

ORDER BY _column1 DESC, _column2 ;  _column1降序，_column2升序 

ORDER BY _column1 DESC, _column2 DESC;  _column1降序，_column2降序 */

--能联排序

--列出2015年以后入学的学生的姓名、学号、性别

select student_name,student_id,student_sex from student_info
where YEAR(ru_date)  > 2015
order by ru_date desc, student_id

--group by 对查询结果进行分组，通常和聚合函数一起使用

--统计各个班级的学生总人数

select  class_no,count(student_id) 班级人数
    from student_info
        group by class_no

--统计男女各自的总人数 和平均年龄

select COUNT(student_id) 总人数,
AVG(YEAR(getdate())-year(born_date)) 平均年龄
    from student_info
        group by student_sex

--HAVING 筛选结果表
--WHERE子句 与 HAVING子句的区别
--WHERE子句作用对象是表，从表中选择出满足筛选条件的记录
--HAVING子句作用对象是组,从组中选择出满足筛选条件的记录

--将人数大于2的班级总人数显示出来
select class_no 班级, COUNT(student_id) 总人数
    from student_info
    group by class_no
    having COUNT(student_id)>2

--基于多表的联合查询
/*
连接查询
是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。
通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，
也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中，
表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。
当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。
连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。
为不同实体创建新的表，然后通过连接进行查询。

联合查询是可合并多个相似的选择查询的结果集。
等同于将一个表追加到另一个表，从而实现将两个表的查询组合到一起，
使用谓词为UNION或UNION ALL。
*/

select * from class_info
select * from course_info
select * from result_info
select * from student_info

--等值链接
select class_info.*,student_info.*
 from class_info,student_info
  where class_info.class_no=student_info.class_no

--自然连接(自然连接的表达式中，各表主键和外键进行等值连接)
select student_info.class_no,class_name,director,profession,student_id,
student_name,student_sex,born_date,tele_number,ru_date,address,commet
 from class_info,student_info
  where student_info.class_no=class_info.class_no

/*
注意:查询多个表是列必须明确,任何重复的列名都必须用表明限定
     所有列名如何都用表明限定，则可提高查询的可读性
     如果使用表的别名， 则进一步提高可读性
*/

--采用自身连接，查询不同课程相同成绩的学生的 学号 课程号 和成绩

select a.student_id,a.course_no,b.course_no,a.result
 from  result_info a,result_info b
  where a.result=b.result
   and a.course_no!=b.course_no

--join    on
--

--子查询
--查询小强所在的班级名称

select class_info.class_name from student_info,class_info
where student_info.class_no=class_info.class_no
and student_name like '%小强%'

select class_name from class_info
    where class_no=(
        select class_no from student_info
            where student_name like '%小强%')

--
/*
   连接和子查询的区别
   1.连接的结果可以是合并多个表；子查询的结果只能来自一个表

*/

--使用IN 、NOT IN 的子查询

--insert /update/delete 的子查询

/*  使用比较运算符的子查询(ALL/ANY)
    ALL 表达式要与子查询结果的所有值进行比较 (成功返回TRUE，否则返回FALSE)
    ANY 表达式与子查询结果要满足比较的关系 (成功返回TRUE，否则返回FALSE) */

--使用EXISTS的子查询用于测试结果是否为空表,不为空返回TRUE，否则返回FALSE
--NOT EXISTS刚好相反

--使用UNION运算符组合多个表的结果