mysql的查询语句

/*插入数据*/
INSERT INTO stu_info VALUES
    ( 6, 'mary', '女','1997-5-22', 1 ),
    (20,'jac', '男','1998-5-22', 2)

-- 更新数据    
UPDATE stu_info SET stuname='jery' where stuid=20

-- 加where删除某行数据，不加where则清空表
delete from stu_info where stuid=20

-- stuid 学号 ：为stuid起别名
select stuid 学号,stuname 姓名 from stu_info where stusex='男'

-- BETWEEN ... AND:选择学号[2，10]之间
select * from stu_info where stuid BETWEEN 2 and 10

-- in：学号为2或者10
select * from student.stu_info where stuid in (2,10);

-- like：模糊查询    %j：表示字符串以j结尾   %j%:表示j在中间的   j%：表示以j开头的字符串
--                                   _:代表单个字符
select * from student.stu_info where stuname like'j%'
select * from student.stu_info where stuname like'j__'
select * from student.stu_info where stuname like'j___'

-- 平均数
SELECT AVG(score)AS 平均分 from score where subid=1
-- 总数
select subid as 科目编号,COUNT(*) as 考试人数 from score where subid=2
-- 最大值，最小值
select subid as 科目编号,MAX(score)as 最大值,MIN(score) as 最小值 from score where subid=2
-- 总数
select SUM(score) from score
-- 分组grop by:根据学号分组
select stuid,AVG(score) from score GROUP BY stuid

select stuid,score+5 as jw from score HAVING jw<80
列出平均值大于75的学生
select stuid,avg(score) from score GROUP BY stuid having AVG(score)>=75

/*
    编写顺序：select ... from ... where ... group by ... having ... order by ...
    执行顺序： where ... group by ... having ... select ... order by ...
*/
-- 升降排序
select stuid,avg(score) from score GROUP BY stuid having AVG(score)>=75
ORDER BY avg(score) asc

-- 限制结果 limit 0,2,查询前2个
select *from student.score where subid=2
order by score desc
limit 0,2

/*多表查询*/
select a.stuid,a.stuname,b.cname from student.stu_info a ,student.classinfo b
    where a.cid=b.cid
    order by a.cid
-- 查找学号为2的班级    
select c.stuid,c.stuname,a.cname from student.classinfo a,student.stu_info c
    where c.cid=a.cid  and c.stuid=2
-- 查找科目为mysql的学生成绩
select a.stuid,a.stuname,b.subname,c.score from student.stu_info a,student.sub b,student.score c
    where a.stuid=c.stuid and c.subid=b.subid and b.subname='mysql'
    
-- 内连接：合并具有同一列的两个以上表的行，结果集不包含一个表与另一个表不匹配的行 inner join
select stuname ,AVG(score) from score inner join student.stu_info
    ON score.stuid=stu_info.stuid where stuname='jack'
-- 左外连接
SELECT a.stuid, `stuname`, stusex, subid, score
FROM student.stu_info a LEFT JOIN student.score b
ON a.stuid = b.stuid 

SELECT b.stuid, `stuname`, stusex, subid, score
FROM score a LEFT JOIN student.stu_info b
ON b.stuid = a.stuid

-- 右外连接
SELECT a.stuid, `stuname`, stusex, subid, score
FROM student.stu_info a RIGHT JOIN score b
ON a.stuid = b.stuid

SELECT a.stuid, `stuname`, stusex, subid, score
FROM score b RIGHT JOIN student.stu_info a
ON a.stuid = b.stuid