查询记录 · 微笔记

##查询表中所有列所有记录 ~~~ select * from user; ~~~ 返回信息： ~~~ +----+-----------+--------+--------------------+-------+-------+-------------+-----------------------+ | ID | NAME | GENDER | SID | LEVEL | CLASS | ACHIEVEMENT | REMAKRS | +----+-----------+--------+--------------------+-------+-------+-------------+-----------------------+ | 1 | 安牡丹 | 2 | 350213199106258506 | 2 | 6 | 85 | 爱好音乐、美术 | | 2 | 刘婷 | 2 | 110235198609120622 | 2 | 2 | 95 | | | 3 | 姚瑶 | 2 | 110258198508282882 | 2 | 8 | 90 | | | 4 | 王明 | 1 | 110623198202162462 | 2 | 4 | 90 | 爱好篮球 | | 5 | 孙丽 | 2 | 126123198305241624 | 1 | 6 | 91 | | | 6 | 冯敏 | 2 | 133621198206252137 | 2 | 1 | 99 | 爱好运动 | | 7 | 高原 | 1 | 133621198206252137 | 2 | 1 | 90 | | | 8 | 张寒 | 1 | 135236198508265971 | 1 | 5 | 89 | | | 9 | 张红 | 1 | 140235198310232314 | 2 | 3 | 88 | | +----+-----------+--------+--------------------+-------+-------+-------------+-----------------------+ ~~~ ##查询指定字段学生姓名、年级、班级、爱好 ~~~ select NAME,LEVEL,CLASS,REMAKRS from user ; ~~~ 返回信息： ~~~ +-----------+-------+-------+-----------------------+ | NAME | LEVEL | CLASS | REMAKRS | +-----------+-------+-------+-----------------------+ | 安牡丹 | 2 | 6 | 爱好音乐、美术 | | 刘婷 | 2 | 2 | | | 姚瑶 | 2 | 8 | | | 王明 | 2 | 4 | 爱好篮球 | | 孙丽 | 1 | 6 | | | 冯敏 | 2 | 1 | 爱好运动 | | 高原 | 2 | 1 | | | 张寒 | 1 | 5 | | | 张平 | 2 | 3 | | +-----------+-------+-------+-----------------------+ ~~~ ##条件查询查询年级为2年级的学生信息 ~~~ select * from user where LEVEL=2; ~~~ 查询年级为2年级，班级为1班的学生信息 ~~~ select * from user where level=2 and class=1; ~~~ >where level=2 class=1 会报错必须用and来隔离，and为与，两个列的内容必须满足才能被查出来。查询年级为1年级6班的学生或2年级1班的学生 ~~~ select * from user where level=1 and class=6 or level=2 and class=1; ~~~ 查询成绩90分到100分范围的学生 ~~~ select * from user where ACHIEVEMENT BETWEEN 90 AND 100; ~~~ 查询成绩低于90分或者大于95分 ~~~ select * from user where ACHIEVEMENT < 90 or ACHIEVEMENT > 95; ~~~ 查询成绩为89分，90分，91分的学生信息 ~~~ select * from user where ACHIEVEMENT >= 89 OR ACHIEVEMENT <= 91; select * from user where ACHIEVEMENT IN (89,90,91); select * from user where ACHIEVEMENT BETWEEN 89 and 91; ~~~ 查询名字里姓刘的学生 ~~~ select * from user where NAME LIKE '刘%'; ~~~ >"%"表示任意字符的意思，"\_"表示单个字符查询姓刘而且名字只有一个字的学生 ~~~ select * from user where NAME like '刘_'; ~~~ 匹配11位手机号码，前两位以13开头 ~~~ select * from user where ACHIEVEMENT like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'; ~~~ 查询备注不为空的学生信息 ~~~ select * from user where REMAKRS is not null; ~~~ 查询前5条记录 ~~~ select * from user limit 5; ~~~ 改变查询结果的列名称 >[info]强调：语句中包含中文的必须用双引号或者单引号包起来。 ~~~ select NAME AS '姓名' , LEVEL AS '年级', CLASS AS '班级' from user; ~~~ 取出2年级学生的总成绩 ~~~ select sum(ACHIEVEMENT) AS '总成绩' from user; ~~~ 查询结果按成绩从高到低排序 ~~~ select * from user order by ACHIEVEMENT desc; ~~~ >从低到搞排序就把desc改成asc ###分组查询按照年级的总成绩 ~~~ select level , sum(achievement) from user group by level; ~~~ 按照年级分组查询总成绩并且从高到低排序 ~~~ select level , sum(achievement) from user group by level order by sum(achievement) desc; ~~~ 按照年级的平均分来显示 ~~~ select level , avg(achievement) from user group by level; ~~~ 按照年级最大成绩来显示 ~~~ select level , max(achievement) from user group by level; ~~~ 按照年级最小成绩来显示 ~~~ select level,min(achievement) from user group by level; ~~~ 查询所有学生的成绩最高分，最小分，平均分，总分，并按照别名方式显示 ~~~ select max(achievement) as '最高分' , min(achievement) as '最低分' , avg(achievement) as '平均分' , sum(achievement) as '总分' from user; ~~~ 按年级分组查询总成绩大于200的年级，并显示年级总成绩 ~~~ select level , sum(achievement) as '总成绩' from user group by level having sum(achievement)>200; ~~~ 查询整数时可以同时进行运算，比如计算每个用户的年薪是多少？ ~~~ select NAME AS '名字',ACHIEVEMENT*12 AS '年薪' from user; ~~~