数据库操作基础
--使用查询列表
列名 IN (value1,value2,...)
等同于使用or连接的多个条件
select * from student
where studentname in('张宏','雷立')
--字符串模糊匹配
列名 like '匹配字符串'
--例 查询所有不姓张与李的学生信息
select *
from student
where studentname like '[^张李]%'
--where studentname not like '[张李]%'
--where not studentname like '[张李]%'
--例 查询所有家在成都的学生信息
select *
from student
where homeaddr like '成都%'
--空值的判定
列名 is null
select *
from student
where homeaddr is null
/*update student
set homeaddr=null
where homeaddr=''*/
--distinct关键字
功能：从返回的结果数据集中删除重复的行，
例select distinct sex
from student
例 查询所有修了课的学生的学号
select distinct studentid from grade
--top关键字
返回表中前面一定数量的数据
top n 返回前几行数据
top percent 返回百分子n行数据
select top 3 * from student
select top 3 percent * from student
--排序
order by 列名 asc升序/desc降序,
例 查询所有学生信息按年龄排升序
select *
from student
order by birth desc
例 查询年龄最大的3名学生信息
select top 3 *
from student
order by birth
--多表连接
select 目标列 from 表1
join 表2 on 表1.列＝表2.列
…
--连接类型
inner join 内连接 (对应关系）
left join 左外连接 （无对应关系）
right join 右外连接
full join 全外连接
inner join 内连接
cross join 交叉连接(无连接条件）
--例 查询张宏的成绩记录(学号，姓名，课程名，成绩)
select s.studentid,s.studentname,coursename,grade
from student s
join grade g on s.studentid=g.studentid
join course c on c.courseid=g.courseid
--例 查询所有计算机系的学生信息
select s.* from Department d
join class c on d.DepartmentID=c.DepartmentID
join student s on s.ClassID=c.classid
where departmentname='计算机系’
—例 查询所有没有修课的学生信息
select s.* from student
left join grade on student.studentid=grade.studentid
where courseid is null
—查询与张宏同班的学生信息
select classid from student where studentname=‘张宏’
select * from student where classid=‘Cs010901’
方法2
declare @cid char(8)
select @cid=classid from student where studentname=‘张宏’
select ## from student where classid=#cid
方法3(多表)
方法4(不相关子查询)
select *
from student
where classid=(select classid from student where studentname=‘张宏')
select *
from student s1 where classid in(select classid from student s2 where student=‘张宏’）
--查询所有没有修课的学生
--无关子查询
select *
from student where students in(select distinct studentid from grade)
相关子查询
where exists(子查询) —子查询只返回真假
—例 把张宏的每科成绩＋5
update grade
set grade=grade+5
where student=(select student from student where studentname=‘张宏’)
—例 删除张宏的所有成绩记录
delete grade
where studentid=(select student from student where studentname=‘张宏’)
—查询
查询09软件测试一班的所有学生
—子查询
select *
from student
where classic = (select classid from class where classname=‘09软件测试1班’)
—多表连接
select s.*
from students join class c on s.classid=c.classid
where classname=‘09软件测试一班'
—使用select into
格式: select 目标列 into 新表名 from 表名
功能:将结果集保存为一个表
—例 把所有老师的编号，姓名插入到teach表中
select teacherid teachername into teach from Teacher
—数据转换
case
when 条件1 then 结果1
…
when条件n then 结果n
else 结果n+1
end
—例子
SELECT dbo.Student.StudentID, dbo.Student.StudentName, dbo.Grade.Grade, dbo.Course.CourseName,
case
when Grade>=90 then '优'
when Grade>=80 then '良'
when Grade>=70 then '中'
when Grade>=60 then '及格'
else '不及格'
end as 等级
FROM dbo.Class INNER JOIN
dbo.Student ON dbo.Class.ClassID = dbo.Student.ClassID INNER JOIN
dbo.Grade ON dbo.Student.StudentID = dbo.Grade.StudentID INNER JOIN
dbo.Course ON dbo.Grade.CourseID = dbo.Course.CourseID
---------------数据汇总-------------
--聚合函数
count 统计
max 最大
min 最小
sum 求和
avg 平均
用法
count(*) count(列名) count(distinct 列名)--取消重复
group by
select COUNT(*)
from Student
where Sex='男'
select sex,COUNT(*) num
from Student
group by sex
select COUNT(*)
from Course
join in student course.CourseID on student.
SELECT studentname,count(*),AVG(Grade),MAX(grade) max,MIN(grade) min,SUM(grade) sum
FROM Student
left join Grade on student.StudentID=grade.StudentID
group by student.studentid,studentname
where StudentName='张宏'
select COUNT(*),COUNT(studentname),COUNT(distinct studentname),COUNT(homeaddr)
from student
select studentid,studentname,COUNT(*) num
from Student
group by Sex
----------------把子查询当作使用表---------
select StudentID,StudentName,num from
(select sex,COUNT(*) num
from Student
group by Sex) as s1
join Student as s2
on s1.Sex=s2.Sex
