5.数据单表，多表查询，更新，添加，删除

#查询数据
#select 的基本语法
select 属性列表
from 表名和视图列表
where 条件表达式1
group by 属性名1{having条件表达式2}
order by 属性名2{asc|desc}
#1查询所有字段
select * from student
#2在select 子句中列举需要显示的字段名
select sno, sname,ssex from student
#3指定字段查询
select sno,sname from student
#查询经过计算后的字段，计算学生的年龄
select sno,sname,2021-year(sbirthday) from student
#别名设置
select sno,sname,2021-year(sbirthday) as '年龄' from student
#消除重复信息
select distinct cno,grade from sc
#使用聚合函数count,sum,avg,max,min
#查询student表中总人数
select count(*) from student
#统计sc表中学号为0268的学生总成绩
select sum(grade) from sc where sno=0268
#计算所有学生的平均成绩
select avg(grade) from sc
#查询sc表的最高最低成绩
select max(grade) as '最高分',min(grade) as '最低分' from sc

#单表查询where子句
#查询学号为0268学生的信息
select * from student where sno=0268
#带in关键字的查询
#查询学号为0268,0269的学生记录
select * from student where sno in(0268,0269)
#带between and的范围查询
#查询学号在0268-0270之间的学生信息
select * from student where sno between 0268 and 0270
#带like的字符匹配查询
#查询张姓的学生信息
select * from student where sname like '张%'
#查询张姓，名字的第三个字为娇的学生信息
select * from student where sname like '张_娇'
#查询空值
#查询学生成绩为空的学生信息
select * from sc where grade is null
#带and的多条价查询
#查询学号为0268的男学生的信息
select * from student where sno=0268 and ssex='男'
#带or的多条件查询
#查询snow为0268或者ssex为女的学生信息
select * from student where sno=0268 or ssex='女'

#单表查询 order by子句
#查询student表，并按sbirthday字段进行排序
select * from student order by sbirthday
#按学号升序排列显示学生的所有成绩，同一学好则按课程编号降序排序
select * from sc order by sno asc,cno desc
#group by子句
#分别查询每位学生锁参加的开始科目及其课程编号，每个学生用一行显示
select sno,group_concat(cno) from sc group by sno
#group by 与聚合函数一起使用
#查询每位学生所参加考试的科目总数
select sno,count(sno) from sc group by sno
#group by与having一起使用
#查询考试科目大于等于3的学生学号，及其考试科目
select sno,count(sno) from sc group by sno having count(sno)>=3
#按多个字段进行分组
#查询每位学生及其每门课程的信息
select * from sc group by sno,cno
#group by与with rollup(记录最后添加一条记录，记录总数)一起使用
#查询每位学生所参加的科目考试，并统计学生的总人数
select sno,count(sno) from sc group by sno with rollup
#显示每个学生参与考试的科目及其科目总数
select sno,group_concat(cno) as '选修科目',count(sno) as '总计' from sc group by sno with rollup

#limit子句
#不指定初始位置
#查询student表前两条记录
select * from student limit 2
#指定初始位置
#查询student表的2-3条记录
select * from student limit 1,2

#多表查询
#内连接查询
select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno
#外连接查询
select 属性名列表
from 表名1 left/right join 表名2
on 表名1.属性名1=表名2.属性名2
#左连接
select student.sno,sname,cno,grade 
    from student left join sc 
        on student.sno=sc.sno 
#右连接
select student.sno,sname,cno,grade 
    from student right join sc 
        on student.sno=sc.sno 

#符合连接查询
#查询成绩高于80分学生的学号，系别和他们的成绩
select student.sno,sname,sdept,grade
    from student,sc
    where student.sno=sc.sno and grade>80
    order by grade asc

#子查询/嵌套查询
#带in的关键字子查询,not in与之相反
#查询成绩大于80的学生的基本信息
select * from student where sno in(select sno from sc where grade>80)
#带比较运算符的子查询
#查询成绩大于平均成绩的学生的学号
select sno from sc where grade>(select avg(grade) from sc)
#带exists关键字的子查询
#exists表示存在，使用exists关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
#如果存在一个学生的学号为0268，则显示所有学生的信息，否则则不显示
select * from student where exists (select sno from student where sno=0268)
#带any关键字的子查询
#any表示满足其中任一条件
#查询student表中，是否存在一位网络系的学生年龄比所有通信系学生的年龄都大
select * from student 
    where sdept='网络系' and sbirthday>=any(select sbirthday from student where sdept='通信系')
#带all关键字的子查询
#all满足所有条件才会返回所有结果，只有内层查询语句返回所有的结果，才可以执行外层查询语句。
#查询年龄最小的学生基本信息
select * from student where sbirthday>=all(select sbirthday from student)
#合并查询结果union(合并查询结果时会消除相同结果)/union all（不会消除相同结果）
select sno from student union select cno from sc

desc student
#插入数据
insert into student values('0235','张小雨','女','1998-12-16','网络系')
#插入多条数据
insert into student values('0213','李明','男','1996-03-18','网络系'),
    ('0215','赵青青','女','1996-07-14','新闻系'),
    ('0217','李半吨','男','1996-05-26','网络系'),
    ('0218','王二小','男','1996-06-16','网络系')
    
#插入查询后的结果
CREATE TABLE fuben (
    sno CHAR ( 9 ) NOT NULL COMMENT '学号',
    sname VARCHAR ( 10 ) NOT NULL COMMENT '姓名',
    ssex CHAR ( 2 ) DEFAULT NULL COMMENT '性别',
    sbirthday date DEFAULT NULL COMMENT '年龄',
    sdept VARCHAR ( 8 ) NOT NULL COMMENT '系别',
    PRIMARY KEY ( sno ) 
);
#将student查询的四条记录插入fuben
insert into fuben
select * from student limit 4
#将student中年龄大于20的学生信息插入副本中
insert into fuben
select * from student where (year(curdate())-year(sbirthday))>20

#修改数据
#修改一个字段的值
#将王小二的系别改为通信系
select * from student where sname='王二小'
update student set sdept='通信系' where sname='王二小'

#删除数据、
#删除表
delete from 表名
#删除单条信息
delete from student where sname='王二小'