SQL查询四之模糊查询

--使用like进行模糊查询

--查询所有姓王的同学的信息

select * from student where stuName like '王%'

        --'%'号与任意个字符相匹配其实就是0到n个

--查询所有赵姓同学的信息并且其名字是两个字

select * from student where stuName like '赵_'
        --'_'号与一个字符相匹配

--查询第二字为志的同学的信息
select * from student where stuName like'_志%'

insert into student values('20060211','赵红','女','1985-05-28 00:00:00','电子商务',null,'电子商务系')
--查询没有平均成绩的同学信息

select * from student where stuAvgrade is null

--创建选课表(课程表)



create table sc
(
    stuId char(8),
    cName varchar(20),--课程名
    cGrade numeric(3,1) check(cGrade >=0 And cGrade <= 100)
    primary key(stuId,cName) --联合主键
)
go


insert into sc values('20060201','英语',80.2)
insert into sc values('20060201','数据库原理',70.0)
insert into sc values('20060201','算法设计与分析',92.4)
insert into sc values('20060202','英语',81.9)
insert into sc values('20060202','算法设计与分析',85.2)
insert into sc values('20060203','多媒体技术',68.1)
go


--查询选了课的学生的学生信息以及所选课程名称和成绩

select * from student
select * from sc

select s.*,sc.*  from student s, sc
where s.stuId = sc.stuId
order by s.stuId



select student.*,sc.* from student, sc

select student.*, sc.* from student, sc
order by student.stuId --这叫全映射，又叫笛卡尔乘积


select student.*,sc.stuId, cName, cGrade from student, sc
where student.stuId = sc.stuId
order by sc.stuId



select student.stuId as 学号, stuName as 姓名, stuSex as 性别,
stuSpeciality as 专业, stuDept as 系别, cName as 课程名称, cGrade as 课程成绩
from student,sc
where student.stuId = sc.stuId

--查询所有没有选英语课的学生的信息


select * from student
select * from sc

select student.*,cName from student, sc
where student.stuId = sc.stuId and cName <> '英语'

select student.*,cName from student, sc
where student.stuId = sc.stuId and student.stuId not in (选了英语课的学生的ID)

--选了英语课的学生的ID
select stuId from sc where cName = '英语'

select student.*,cName from student, sc
where student.stuId = sc.stuId and student.stuId not in (
    select stuId from sc where cName = '英语'
)

--查询学生李好的同专业同学的信息



--嵌套子查询版
select * from student where stuSpeciality in 
(
    select stuSpeciality from student where stuName = '李好'
)
and stuName <> '李好'




--查询学生李好的同系同学的信息不包含李好的信息

select * from student
select * from student



select s1.* from student s1, student s2
where s1.stuDept = s2.stuDept 
and s2.stuName = '李好' 
and s1.stuName <> '李好'



select s2.* from student s1, student s2
where s1.stuDept = s2.stuDept
and s1.stuName = '李好'
and s2.stuName <> '李好'




--查找同城好友
select h1.* from haoyou h1, haoyou h2
where h1.city = h2.city
and h2.hName = '李白'
and h1.hName <> '李白'