create database SQLschoolgo      --批 gouse SQLschoolgo-- --------------------------删除表-----------------------------select * from goods--全部删除delete from goods--有条件的批量删除delete from goods where goodsName = ‘面包‘--按主键为条件删除，这个只会删除一条delete from goods where goodsId = 11-- -------------------------更新表----------------------------select * from goods--无条件的批量的更新update goods set goodsName=‘白酒‘--有条件的批量更新update goods set goodsName=‘白酒‘ where goodsName!=‘啤酒‘--指定唯一更新，多列更新update goods set goodsPrice = 2000, goodsName=‘茅台‘ where goodsId = 3update goods set goodsName = ‘aa‘,goodsPrice = 2000, goodsMemo = ‘mm‘ where goodsId = 3-- ---------------创建表和数据库-----------------------------列级约束create table student(    stuId char(8) primary key, --主键约束:唯一，且不能为空    stuName varchar(10) not null, --非空约束    stuSex char(2) check(stuSex = ‘男‘ or stuSex = ‘女‘),                    --检查约束    stuBirth smallDatetime,    stuSpeciality varchar(50) default ‘计算机软件与理念‘,                                --默认值约束    stuAvgrade numeric(3,1) check(stuAvgrade >= 0 and stuAvgrade <= 100),    stuDept varchar(50) default ‘计算机科学系‘)goinsert into student values(‘20060201‘,‘李好‘,‘男‘,‘1987-01-01‘,‘计算机应用技术‘,92.5,‘计算机系‘)insert into student values(‘20060202‘,‘王丫‘,‘女‘,‘1987-02-23‘,‘计算机软件与理论‘,88.8,‘计算机系‘)insert into student values(‘20060203‘,‘王高‘,‘男‘,‘1986-12-15‘,‘网络工程‘,85.8,‘信息工程系‘)insert into student values(‘20060204‘,‘赵刚‘,‘男‘,‘1987-07-01‘,‘网络工程‘,77.8,‘信息工程系‘)insert into student values(‘20060205‘,‘贾志‘,‘男‘,‘1985-09-18‘,‘计算机应用技术‘,45.0,‘计算机系‘)insert into student values(‘20060206‘,‘丽思‘,‘男‘,‘1984-08-01‘,‘计算机应用技术‘,61.3,‘计算机系‘)insert into student values(‘20060207‘,‘赵志远‘,‘男‘,‘1983-11-02‘,‘电子商务‘,72.8,‘电子商务系‘)insert into student values(‘20060208‘,‘王可‘,‘女‘,‘1985-05-28‘,‘电子商务‘,55.7,‘电子商务系‘)goselect * from student-- -------------------------------查询----------------------------------------查询所有列select * from student--查询学生的姓名，性别，专业和系,起友好列名select stuName 学生姓名, stuSex as 性别,     stuSpeciality as 专业,    stuDept as 系from student--选择指定列  -- Avg 平均值select stuAvgrade, stuName from student--给列取别名select stuAvgrade as 平均成绩, stuName as ‘姓名‘ from studentselect stuAvgrade  平均成绩, stuName  ‘姓名‘ from studentselect stuAvgrade as 平均成绩, stuName as 姓名 from student --推荐写法--查询全体学生的出生年份select year(stuBirth) as 出生年份 from studentselect year(‘1987-02-23 00:00:00‘)  ---单列只会显示年份select 200*987   --计算--查询全体学生的出生月份select month(stuBirth) from student---查询全体学生的出生日期select stuName as 学生姓名, day(stuBirth) 日期 from student--查询全体学生的姓名和年龄--0select stuName, 年龄 from student--1year(当前日期) - year(stuBirth)  select getdate()  ---获取系统当前时间--2 year(getdate()) - year(stuBirth)  --- 当前日期-出生日期=现在年龄--3select stuName 学生姓名, year(getdate()) - year(stuBirth) as 年龄 from student----------------------------查询------------------------------------使用distinct查询学生所在系的名select distinct stuDept from student  ---去掉相同的项(保留一个)--使用avg函数查询全体学生的平均成绩的平均值        --下面这些都使用了聚合函数select * from student--非法select stuName from student where stuAvgrade < avg(stuAvgrade)select sum(stuAvgrade) as 总成绩 from studentselect round(avg(stuAvgrade),2) as 平均成绩 from student  ---round 保留小数位 这里不行select max(stuAvgrade) as 最高成绩 from studentselect min(stuAvgrade) as 最小成绩 from student--查询姓名为李好的学员的信息select * from student where stuName = ‘李好‘--查询学生编号为20060205的学员的信息select * from student where stuId= ‘20060205‘--查询学生平均成绩在70分以上的学员的信息select * from student where stuAvgrade >= 70--查询大于(小于)全体学生的平均成绩的平均值的学生信息select * from student where stuAvgrade > (    --子查询    select avg(stuAvgrade) from student)---查询小于平均成绩的学生select stuName, stuAvgrade from student where stuAvgrade < (select avg(stuAvgrade) from student)--使用Between查询所有出生在84年8月1日到86年12月25日之间的学生信息select * from student where stuBirth between ‘1984-08-01‘ And ‘1986-12-25‘ --查找 * 从 学生表 当 生日 在 ‘1984-08-01‘ 和 ‘1986-12-25‘ 之间的时候--使用in查询信息工程系和电子商务系的学生select * from student where stuDept=‘信息工程系‘ or stuDept=‘电子商务系‘ select * from student where stuDept in(‘电子商务系‘,‘信息工程系‘)   ---查询一个条件两个信息--使用count函数查询全体学生的人数select count(stuId) as 人数 from studentselect count(*) as 人数 from student     ------------------  分组查询 -----------------使用group分组查询各系学生的数量,分组统计--select * from student group by stuDept//这句我们本来是想按组来查看学生，但是有错，因为分组必统计--select stuDept,stuName, count(*) from student group by stuDept,stuName //没有意义select stuDept,count(*) as 人数 from student group by stuDept --- 每个系数量,计算人数--查询各系统男女学生的人数select * from studentselect stuDept, stuSex, count(stuId) from student group by stuDept, stuSex order by stuDept    ---order by 把查询的结果进行排序--按组来查看学生select stuDept, stuName, stuSex, stuBirth from student order by stuDept --其实排序刚好有分组列出信息的效果select stuDept, sum(stuAvgrade) as 总成绩 from student group by stuDeptselect stuDept, Max(stuAvgrade) as 最好的一个 from student group by stuDeptselect stuDept, Min(stuAvgrade) as 最差劲的一个 from student group by stuDeptselect stuDept, avg(stuAvgrade) as 平均 from student group by stuDeptselect * from student--查询各系学生信息select stuDept, stuName, stuSex, stuBirth, stuSpeciality, stuAvgrade  from student group by stuDept, stuName, stuSex, stuBirth, stuSpeciality, stuAvgrade --这样写是可以的，但是看起来意义不大，且难以理解你是要干嘛 --查询每个系的各专业的学生人数select stuDept, stuSpeciality, count(*) from studentgroup by stuDept, stuSpeciality--查询每个系的各专业的最好成绩select stuDept, stuSpeciality, max(stuAvgrade) from studentgroup by stuDept, stuSpecialityselect stuDept, stuName, stuSex, stuBirth, stuSpeciality, stuAvgrade  from student order by stuDept  -- order by 是排序关键字 --- dian, ji, xin 按拼音排序select * from student order by stuAvgrade Desc --desc是降序，默认值是Ascselect * from student order by stuAvgrade Asc--使用having子句查询人数大于2的系select stuDept from studentwhere count(*) > 2group by stuDept --非法，where条件部分不能有聚合函数--select stuDept from student where count(*) > 2 group by stuDept 这样的写法是我们很自然就想到的，但是是非法，因为在Sql中不能在where条件后使用有计算的表达式，如聚合函数select stuDept from student group by stuDept having count(*) > 2---自己写的select stuDept from student group by studept having COUNT(*) > 2 and studept <> ‘计算机系‘--查询人数大于2的系并且，不能是计算机系select stuDept, count(*) from student where stuDept <> ‘计算机系‘ group by stuDept having count(*) > 2--- 分组系统自动统计select stuDept from studentwhere stuDept <> ‘计算机系‘group by stuDepthaving count(*) > 2select stuDept as 系, count(*) as 人数, sum(stuAvgrade) as 总成绩, avg(stuAvgrade) as 平均成绩, max(stuAvgrade) as 最好成绩 from studentgroup by stuDepthaving count(*) > 2select * from student--查询平均成绩大于70分的系select stuDept from studentgroup by stuDepthaving avg(stuAvgrade) > 70---查询各系的人数select stuDept,count(*) as 人数 from student    group by stuDeptselect stuDept, sum(stuAvgrade) from student        group by stuDept        having sum(stuAvgrade) > 200select stuDept, Max(stuAvgrade) as 最好的一个 from student        group by stuDept        having Max(stuAvgrade) < 80select stuDept, Min(stuAvgrade) as 最差劲的一个 from student        group by stuDept        having Min(stuAvgrade) < 60--下面两个查询结果是一样的，过分分组是没意义的select stuDept as 系,  sum(stuAvgrade) as 总成绩, avg(stuAvgrade) as 平均成绩, max(stuAvgrade) as 最好成绩 from studentgroup by stuDept , stuNameorder by studeptselect stuDept as 系,stuName,  stuAvgrade as 总成绩, stuAvgrade as 平均成绩, stuAvgrade as 最好成绩 from studentorder by stuDept, stuNameselect * from student--求各系的平均成绩，并且要按从大到小的顺序排select stuDept,  avg(stuAvgrade) as 平均成绩  from student group by stuDept --分组order by  平均成绩 desc --降序  ---查询结果加上要按什么别名排序的名字 + desc  asc --求平均成绩最大的系和它的平均成绩--- select top 1 student.stuDept, avg(student.stuAvgrade) as 平均成绩 from student group by stuDept order by 平均成绩 desc --一时没反应过来写出来这样的查询，它和下下面那个是一样的select studept, avg(stuAvgrade) as avrage from studentgroup by stuDepthaving avg(stuAvgrade) = (    select top 1 avg(stuAvgrade) from student    group by stuDept    order by avg(stuAvgrade) desc)select stuDept, avg(stuAvgrade) from studentgroup by stuDepthaving avg(stuAvgrade) = (    select top 1  avg(stuAvgrade) as 平均成绩 from student --起别名    group by stuDept     order by 平均成绩 desc --按别名排序)select top 1 stuDept, avg(stuAvgrade) as 这里起别名 from studentgroup by stuDeptorder by 这里起别名 desc--求平均成绩头两名的系和它的平均成绩select top 2 stuDept,  avg(stuAvgrade) as avgrade  from student group by stuDeptorder by  avgrade desc-- ---------------------------标量查询-------------------------查询平均成绩最高的系的学员的所有信息    --1.试图直接解决问题select * from student    where stuDept = (最好的系)--发现问题转变成求平均成绩最好的系的名字    --2求平均成绩最好的系的名字select stuDept from student    group by stuDept    having avg(stuAvgrade) = (最好成绩) --发现问题转变成求最好平均成绩是什么    --3求最好平均成绩是多少    select top 1 avg(stuAvgrade) as allAvg from student        group by studept        order by allAvg desc    --倒着走第一步    select stuDept from student    group by stuDept    having avg(stuAvgrade) =     (        select top 1 avg(stuAvgrade) as allAvg from student                group by studept                order by allAvg desc    )        --和上面的查询是等价的，这个是对的    select top 1 stuDept from studentgroup by studeptorder by avg(stuAvgrade) desc---这种写法是最好的select * from student where stuDept = (    select top 1 studept from student group by stuDept order by AVG(stuAvgrade) desc)        --倒着走第二步    select * from student where stuDept = (    select stuDept from student    group by stuDept    having avg(stuAvgrade) =     (        select  top 1 avg(stuAvgrade) as allAvg from student                    group by stuDept                     order by allAvg desc    ))--不啰嗦的写法select * from student where stuDept = (    select  top 1 stuDept as allAvg from student            group by stuDept             order by avg(stuAvgrade) desc)--同时使用where和having进行条件筛选    --并且其平均成绩要大于60的系，并且不能是计算机系select stuDept from student     where stuDept <> ‘计算机系‘    group by stuDept    having avg(stuAvgrade) > 60    --- 前面是需要查询的东西 ,   后面都是利用各种条件进行查询    -- -------------------------模糊查询和多表联合查询及子查询示例----------------------use sqlschoolgo--使用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(‘20060209‘,‘李丽‘,‘女‘,null,‘电子商务‘, 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/*20060209 英语20060209 数学20060210 数学*/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 scselect * from studentselect student.*, sc.* from student, sc   --这叫全映射，又叫笛卡尔乘积order by student.stuId select student.stuId as 学号, stuName as 姓名, stuSex as 性别,stuSpeciality as 专业, stuDept as 系别, cName as 课程名称, cGrade as 课程成绩from student,scwhere student.stuId = sc.stuId--查询所有没有选英语课的学生的信息select student.*,sc.* from student, scwhere student.stuId = sc.stuID and sc.cName <> ‘英语‘select student.*,cName from student,sc where student.stuId = sc.stuId and cName <> ‘英语‘ --有漏洞 不包含英语的选课信息select student.*,SC.*, cName from student, scwhere student.stuId = sc.stuId  and student.stuId not in   ---查询结果，非这些条件(    --选了英语课的学生的ID    select stuID from sc where cName = ‘英语‘    --select student.stuId from student, sc where student.stuId = sc.stuId and cName = ‘英语‘)--正解--查询学生李好的同专业同学的信息--嵌套子查询版select * from student where stuSpeciality =(    --李好的专业    select stuSpeciality from student     where stuName = ‘李好‘) and stuName <> ‘李好‘--查询学生李好的同系同学的信息不包含李好的信息select s2.* from student s1, student s2   --- 笛卡尔乘积  全映射where s1.stuDept = s2.stuDept   --- 系跟系匹配 = 29个  4*4+2*2+3*3 = 29and s1.stuName = ‘李好‘       --- 系跟名字都满足  你好的系是计算机系 等于 计算机系的其他有哪些  4个and s2.stuName <> ‘李好‘   ---并且不等于你好  3个go--查找同城好友select h1.* from haoyou h1, haoyou h2where h1.city = h2.chityand h2.hName = ‘李白‘and h1.hName <> ‘李白‘-- ---------------------内连接外连接和相关子查询 并交差 运算------------------------use sqlschoolgo--内连接(普通连接查询)--Sql92select s.*, cName, cGrade from student s, sc   ---student 全部列 + 上sc的cName cGrade列where s.stuId = sc.stuIdselect sc.* from scselect k.* from student kselect s.*, cName,cGrade from sc    ---这种方法效率高   如果写 *　则两表列出所有列right join student s        ---连接表  仅取出匹配的数据on s.stuId = sc.stuId      ---on 条件-------------------练习--------------------------create table a(    aid int  primary key,    adata varchar(10)  )insert into a values(1,‘a1‘)insert into a values(2,‘a2‘)insert into a values(3,‘a3‘)create  table b(    bid int  primary key,    bdata varchar(10)  )insert into b values(1,‘b1‘)insert into b values(2,‘b2‘)insert into b values(4,‘b3‘)goselect * from aselect * from bselect * from a inner join b on a.aid = b.bidselect * from a left join b on a.aid = b.bidselect * from a right join b on a.aid = b.bidgoselect * from  a where exists(select null) select * from a where 1 = 1select * from a where exists (select 1)exists()是用来判断括号里面的值或者结果集是否为null的,返回的是bit类型的,比如select * from a where exists(select 1 from b where id=a.id),如果select 1 from b where id=a.id这一句能查出来数据,那么查出来的a表中的数据是这样写是一样的:select * from a where id in(select id from b);只不过在用in查询的时候要进行全表查询,效率没有exists高.select * from a where exists(select * from b where a.aid = b.bid)Exists 方法 描述如果在 Dictionary 对象中指定的关键字存在，返回 True，若不存在，返回 False。举个例子吧：select * from a where exists(select * from b where a.id = b.id);搜索a表和b表使用id关联，这条语句的含义是，当b表能够查询出结果时，exists(select * from b where a.id = b.id)子句为真，只有满足exists结果为真时，才会查询出a表的记录。这样解释你明白了吗。-----------------------------------------------------外连接查询select * from sc    --查询所有学生的基本信息和选课情况select s.*, cName,cGrade from student sleft join sc on s.stuId = sc.stuId    --下面的写法和上面的是等价的select s.*, cName,cGrade from sc right join student son sc.stuId = s.stuId--在执行这条insert前要修改一下表insert into sc values(null, ‘中国古代史‘, null)select s.*, cName, cGradefrom student s right join sc on s.stuId = sc.stuIdselect s.*, cName, cGradefrom sc left join student son sc.stuId = s.stuId--查询李好和赵志远所在的专业所有学生select * from student where stuSpeciality in   --- 自己写的(    select stuSpeciality from student where  stuName = ‘李好‘ or stuName = ‘赵志远‘)select student.* from studentwhere stuSpeciality in(    select stuSpeciality from student     where stuName = ‘李好‘ or stuName = ‘赵志远‘)--查询平均成绩比王丫低的学生的信息select * from student where stuAvgRade <(    select stuAvgrade from student where stuName = ‘王丫‘)--使用映射的方式来查，效率会高一些select s1.* from student s1, student s2where s1.stuAvgrade < s2.stuAvgrade and s2.stuName = ‘王丫‘--查询所有选了课的学生的信息(相关子查询)--区别于之前所用的独立子查询，相关子查询要依赖外层查询(父查询)---??? 这种方法也能查询出来select * from student where stuId in(    select stuId from sc group by stuId)------------------------------------------select * from student swhere exists --存在(    select * from sc where sc.stuId = s.stuId)----------------------------------------------对比内联接查询我们可以看到相关子查询的不同之处select s.*, cName from student sinner join sc on s.stuId = sc.stuIdselect * from student s--查询的集合运算(并，交，差运算)        --使用union查询专业为网络工程或者平均成绩在良好(>=80)以上的学生的信息    select * from student where  stuSpeciality = ‘网络工程‘        select * from student where stuAvgrade >= 80     union --把两个结果集联合成一个结果集，要求两个结果集的列数相同    select * from student where stuSpeciality = ‘网络工程‘        --等价的写法    select * from student where stuAvgrade >= 80    or stuSpeciality = ‘网络工程‘    --胡乱一粘    select stuId, stuName from student    union    select stuId,cName from sc            --使用Except查询专业为网络工程而且平均成绩在良好(<=80)以下的学生的信息    select * from student where stuAvgrade <= 80 and stuSpeciality = ‘网络工程‘    select * from student where stuSpeciality = ‘网络工程‘    Except--排除    select * from student where stuAvgrade >= 80-- ---------------------多对多一对多数据完整性----------------------------Create Database newSchoolgouse newSchoolgo create table teacher(    tid varchar(30) primary key,    tName varchar(10) not null,    tSex bit not null)gocreate table student(    stuId varchar(30) primary key,    stuName varchar(20) not null,    stuSex bit not null)go --------中间表-----------create table teacherStudentDetail(    tid varchar(30) foreign key references teacher(tId),    stuId varchar(30) foreign key references student(stuId),    primary key(tid, stuId)    )go/*t001 s002t001 s006t003 s002s002 t001s002 t003*/insert into teacher values(‘t201201‘,‘王‘,1)insert into teacher values(‘t201202‘,‘张‘,1)insert into teacher values(‘t201203‘,‘石头‘,1)insert into student values(‘s201201‘,‘肖华‘,1)insert into student values(‘s201202‘,‘土匪‘,1)insert into student values(‘s201203‘,‘向阳‘,1)insert into student values(‘s201204‘,‘祥子‘,1)insert into student values(‘s201205‘,‘小胖‘,0)insert into teacherStudentDetail values(‘t201202‘,‘s201205‘)insert into teacherStudentDetail values(‘t201203‘,‘s201205‘)insert into teacherStudentDetail values(‘t201201‘,‘s201202‘)insert into teacherStudentDetail values(‘t201202‘,‘s201202‘)insert into teacherStudentDetail values(‘t201203‘,‘s201202‘)insert into teacherStudentDetail values(‘t201202‘,‘s201204‘)insert into teacherStudentDetail values(‘t201203‘,‘s201204‘)select * from teacherselect * from studentselect * from teacherStudentDetail--查询某个老师带的学生--查t201202的老师带了哪些学生select * from student where stuIdin (    select stuId from teacherStudentDetail where tId = ‘t201202‘)--查询某个学生跟的老师select * from teacherwhere tId in(    select tId from teacherStudentDetail    where stuId = ‘s201204‘)