常见的MySQL语句使用

转储.sql文件

• mysqldump -u root db1 > db1.sql -p1234 表结构+数据 insert
• mysqldump -u root -d db1 > db1.sql -p1234 表结构
• mysqldump -u root db1 < db1.sql -p1234 导入.sql

#  临时表查询
select id from(select * from student where num>60) as stuprocess where num<80
# 临时表+函数同时使用
select id,stuprocess.avg from(select *,avg(num) as avg from student where num>60) as stuprocess where num<80
#  单select返回值作为查询字段
按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
    select sc.student_id,
        (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
        (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
        (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
        count(sc.course_id),
        avg(sc.num)
    from score as sc
    group by student_id desc 
# CASE WHEN X THEN X ELSE X
按各科平均成绩从低到高和及格率的百分数从高到低顺序；
    思路：case when .. then
    select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;
#常见操作
向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
    思路：
        由于insert 支持 
                inset into tb1(xx,xx) select x1,x2 from tb2;
        所有，获取所有没上过002课的所有人，获取002的平均成绩
 
    insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)
    from student where sid not in (
        select student_id from score where course_id = 2
    )
查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
    思路：
        获取 001 同学选择的所有课程
        获取课程在其中的所有人以及所有课程
        根据学生筛选，获取所有学生信息
        再与学生表连接，获取姓名
 
        select student_id,sname, count(course_id)
        from score left join student on score.student_id = student.sid
        where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id
 
查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
        先找到和001的学过的所有人
        然后个数 ＝ 001所有学科     ＝＝》 其他人可能选择的更多
 
        select student_id,sname, count(course_id)
        from score left join student on score.student_id = student.sid
        where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) ＝ (select count(course_id) from score where student_id = 1)
 
查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
         
        个数相同
        002学过的也学过
 
        select student_id,sname from score left join student on score.student_id = student.sid where student_id in (
            select student_id from score  where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)
        ) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)

临时表应该先查大表再关联小表减少查询，提高效率。使用时会创建临时数据表，连接断开就会删除。
视图，屏蔽数据方便查找，虚拟存在并产生临时文件，效率不高。
TIMESTAMP和DATETIME的不同点：

1> 两者的存储方式不一样

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回,因此可用于国际化，比如：中国地区保存修改时间，在美国地区查看会转换为美国时间

而对于DATETIME，不做任何改变，原样输入和输出。

2> 两者所能存储的时间范围不一样

timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。

datetime 所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

外键用于约束插入与删除。
distinct和group by的区别博客1、博客2
参考博客