mysql常用语句与函数大全及举例

壹露轩杨 發表於 2026-1-7 11:23:27

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">各个子句的执行顺序</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">where子句</a></li><li><a href="#_lab2_0_1">group by子句</a></li><li><a href="#_lab2_0_2">having子句</a></li><li><a href="#_lab2_0_3">distinct关键字</a></li><li><a href="#_lab2_0_4">order by子句</a></li><li><a href="#_lab2_0_5">limit关键字</a></li></ul><li><a href="#_label1">常用函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_6">数值函数</a></li><li><a href="#_lab2_1_7">日期函数</a></li></ul><li><a href="#_label2">其他函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_8">单条件分支函数：if</a></li><li><a href="#_lab2_2_9">多条件分支函数：case</a></li></ul><li><a href="#_label3">窗口函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_10">窗口函数介绍</a></li><li><a href="#_lab2_3_11">over函数解析</a></li><li><a href="#_lab2_3_12">排名函数</a></li></ul><li><a href="#_label4">关联查询</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_13">关联分类</a></li><li><a href="#_lab2_4_14">交叉连接</a></li><li><a href="#_lab2_4_15">union 操作符</a></li></ul><li><a href="#_label5">子查询</a></li><ul class="second_class_ul"></ul><li><a href="#_label6">列题</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>各个子句的执行顺序</h2>
<p>了解mysql的查询语句的执行顺序，会对编写sql语句有一定的帮助。</p>
<ul><li><strong>from</strong>子句:基于表进行查询操作</li><li><strong>where</strong>子句:进行条件筛选或者条件过滤</li><li><strong>group by</strong>子句:对剩下的数据进行分组查询。</li><li><strong>having</strong>子句：分组后，再次条件筛选或过滤</li><li><strong>select</strong>子句：目的是选择业务需求的字段进行显示</li><li><strong>order by</strong>子句：对选择后的字段进行排序</li><li><strong>limit</strong>子句:进行分页查询，或者是查询前n条记录</li></ul>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>where子句</h3>
<p>where关键字后，用于书写筛选的条件。条件可以是：</p>
<p>1、比较运算：  = < <= = != <></p>
<p>2、逻辑连接  ： AND（同时满足） OR（满足其一）</p>
<p>3、区间：         BETWEEN … AND … 等价于 ≥ 且 ≤<br />                           NOT BETWEEN … AND … 等价于 < 或 ></p>
<p>4、集合匹配：   <span><strong>IN (…)</strong></span><span> </span>在列表内任一值<br />                      <span>      </span><strong><span>NOT IN (…) </span></strong>不在列表内所有值   </p>
<p>                        <span><strong>    ALL (…) </strong></span>大于集合中最大值<br />                           <strong><span>ANY (…)</span></strong> 大于集合中最小值</p>
<p>                        （MySQL 中 ALL/ANY 仅子查询可用）</p>
<p>5、模糊匹配  ：  LIKE '_abc%' _ 任意 1 字符，% 任意 0+ 字符</p>
<p>6、空值判断  ：  IS NULL</p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>group by子句</h3>
<ol><li><strong>count </strong>(column name|常量|*)  返回每组中的总记录数</li><li><strong>sum</strong> (column namel常量)        返回每组中指定字段的值的总和</li><li><strong>avg</strong> (column name)                返回每组中指定字段的值的平均值</li><li><strong>max</strong> (column name)                返回每组中指定字段的值的最大值</li><li><strong>min</strong> (column name)                返回每组中指定字段的值的最小值</li></ol>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>having子句</h3>
<p>使用了group by子句后，再次对数据进行筛选和过滤，筛选的条件与where相同</p>
<p class="maodian"><a name="_lab2_0_3"></a></p><h3>distinct关键字</h3>
<ul><li><strong>置于列名前。</strong>
<ul><li>当select子句中有多个字段时，distinct必须要位于第一个字段的前面，表示这些字段的值的组合进行去重。</li><li>也可以位于函数中指定字段的第一个字段前面</li></ul></li></ul>
<p class="maodian"><a name="_lab2_0_4"></a></p><h3>order by子句</h3>
<p>它的执行时机是在select子句后执行，通常用于对查询出来的数据进行排序的。</p>
<p><code>order by colName [,colName ]... </code></p>
<p><strong>asc</strong>：表示根据指定字段的值升序排序，是默认值，可以省略不写。</p>
<p><strong>desc</strong>：表示根据指定字段的值降序排序。</p>
<p>可以指定多个字段，分别进行排序。当前一个字段的值相同时，后一个字段的排序规则才会生效。</p>
<p class="maodian"><a name="_lab2_0_5"></a></p><h3>limit关键字</h3>
<p>当查询出来的数据量过大，导致当前屏幕不能一次性全部展示出来时，我们可以使用limit关键字进行限制条数查询。</p>
<p><code>limit size;</code></p>
<p>size：表示要查询的数量</p>
<p>off参数：表示从第几条记录开始查询，记录的索引从0开始的。如果没有该参数，表示从第一条开始查询size条。</p>
<p class="maodian"><a name="_label1"></a></p><h2>常用函数</h2>
<p class="maodian"><a name="_lab2_1_6"></a></p><h3>数值函数</h3>
<table><thead><tr><th colspan="1" rowspan="1"><p><strong>函数</strong></p></th><th colspan="1" rowspan="1"><p><strong>函数说明</strong></p></th></tr></thead><tbody><tr><td colspan="1" rowspan="1"><p>pow(x,y)/power(x,y)</p></td><td colspan="1" rowspan="1"><p>返回x的y次幂</p></td></tr><tr><td colspan="1" rowspan="1"><p>sqrt(n)</p></td><td colspan="1" rowspan="1"><p>返回非负数n的平方根</p></td></tr><tr><td colspan="1" rowspan="1"><p>pi()</p></td><td colspan="1" rowspan="1"><p>返回圆周率</p></td></tr><tr><td colspan="1" rowspan="1"><p>rand()、rand(n)</p></td><td colspan="1" rowspan="1"><p>返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)</p></td></tr><tr><td colspan="1" rowspan="1"><p>truncate(n,d)</p></td><td colspan="1" rowspan="1"><p>保留数字n的d位小数并返回</p></td></tr><tr><td colspan="1" rowspan="1"><p>least(x,y,...)、greatest(x,y,...)</p></td><td colspan="1" rowspan="1"><p>求最小值或最大值</p></td></tr><tr><td colspan="1" rowspan="1"><p>mod(n,m)</p></td><td colspan="1" rowspan="1"><p>取模运算,返回n被m除的余数</p></td></tr><tr><td colspan="1" rowspan="1"><p>ceil(n)、ceiling(n)、floor(n)</p></td><td colspan="1" rowspan="1"><p>向上/向下取整函数</p></td></tr><tr><td colspan="1" rowspan="1"><p>round(n[,d])</p></td><td colspan="1" rowspan="1"><p>返回n的四舍五入值,保留d位小数(d的默认值为0)</p></td></tr></tbody></table>
<p class="maodian"><a name="_lab2_1_7"></a></p><h3>日期函数</h3>
<table><tbody><tr><td colspan="1" rowspan="1"><p><strong>函数</strong></p></td><td colspan="1" rowspan="1"><p><strong>函数说明</strong></p></td></tr><tr><td colspan="1" rowspan="1"><p>curdate()\curtime()\now()\sysdate()\current_timestamp()</p></td><td colspan="1" rowspan="1"><p>获取系统时间</p></td></tr><tr><td colspan="1" rowspan="1"><p>dayofweek(date) \weekday(date) \dayname(date)</p></td><td colspan="1" rowspan="1"><p>获取星期几</p></td></tr><tr><td colspan="1" rowspan="1"><p>dayofmonth(date) \dayofyear(date) \monthname(date)</p></td><td colspan="1" rowspan="1"><p>获取第几天</p></td></tr><tr><td colspan="1" rowspan="1"><p>year(date)\month(date)\day(date) \ hour(date) \minute(date) \second(date)</p></td><td colspan="1" rowspan="1"><p>获取时间分量</p></td></tr><tr><td colspan="1" rowspan="1"><p>date_format(date,format) （%Y年 %m月 %d日 %h时 %i分 %s秒 %p上下午 %W星期）</p></td><td colspan="1" rowspan="1"><p>日期格式化，根据format字符串格式化date的值</p></td></tr><tr><td colspan="1" rowspan="1"><p>date_add(date,interval value unit) \date_sub(date,interval value unit)</p></td><td colspan="1" rowspan="1"><p>日期运算</p></td></tr><tr><td colspan="1" rowspan="1"><p>adddate(date,interval value unit) \subdate(date,interval value unit)</p></td><td colspan="1" rowspan="1"><p>日期运算</p></td></tr></tbody></table>
<p class="maodian"><a name="_label2"></a></p><h2>其他函数</h2>
<p class="maodian"><a name="_lab2_2_8"></a></p><h3>单条件分支函数：if</h3>
<p>语法：if(express,value1,value2):</p>
<p>解析：如果express表达式成立，就返回value1,否则返回value2.</p>
<p class="maodian"><a name="_lab2_2_9"></a></p><h3>多条件分支函数：case</h3>
<div class="jb51code"><pre class="brush:sql;">写法1
casecolumn_name
when value1 then returnValue1
when value2 then returnValue2
...
else returnValueN end;
写法2
case
when condition1 then returnValue1
when condition2 then returnValue2
...
else returnValueN end;</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>窗口函数</h2>
<p class="maodian"><a name="_lab2_3_10"></a></p><h3>窗口函数介绍</h3>
<p>窗口函数，也被称为分析函数，是 MySQL 8.0 引入的一项强大功能，它能够在查询结果集中对数据进行分组、排序和计算，而无需使用临时表或自连接。窗口函数的语法结构如下：</p>
<div class="jb51code"><pre class="brush:sql;">window_function(expr) OVER (
, ...])</pre></div>
<p>window_function有如下分类：</p>
<table><thead><tr><th colspan="1" rowspan="1"><p>能类型</p></th><th colspan="1" rowspan="1"><p>函数名</p></th><th colspan="1" rowspan="1"><p>应用场景</p></th></tr></thead><tbody><tr><td colspan="1" rowspan="1"><p>聚合类</p></td><td colspan="1" rowspan="1"><p>SUM, AVG, COUNT、max、min</p></td><td colspan="1" rowspan="1"><p>对窗口内的数据进行聚合计算</p></td></tr><tr><td colspan="1" rowspan="1"><p>排名类</p></td><td colspan="1" rowspan="1"><p>ROW_NUMBER, RANK, DENSE_RANK</p></td><td colspan="1" rowspan="1"><p>对数据进行排序并生成排名</p></td></tr><tr><td colspan="1" rowspan="1"><p>分布类</p></td><td colspan="1" rowspan="1"><p>PERCENT_RANK, CUME_DIST</p></td><td colspan="1" rowspan="1"><p>计算分布情况</p></td></tr><tr><td colspan="1" rowspan="1"><p>偏移类</p></td><td colspan="1" rowspan="1"><p>LAG, LEAD</p></td><td colspan="1" rowspan="1"><p>获取上下行数据</p></td></tr></tbody></table>
<p class="maodian"><a name="_lab2_3_11"></a></p><h3>over函数解析</h3>
<p>OVER() 函数是窗口函数（Window Function）的一部分，用于在查询中执行基于一组的行计算，同时保留这些行的原始记录。这与传统的聚合函数（如 SUM()、AVG() 等）不同，后者通常会将多行合并为一行输出。</p>
<ul><li>PARTITION BY：将数据划分为多个逻辑分区（类似GROUP BY）,每个分区独立计算</li><li>ORDER BY：定义窗口内行的排序方式，影响如累计、排名等计算顺序</li></ul>
<p class="maodian"><a name="_lab2_3_12"></a></p><h3>排名函数</h3>
<ul><li><strong>2）row_number() </strong></li><li>给排序过的表记录分配行号，从1开始的连续自然数</li><li><strong>3）rank()</strong></li><li>给排序过的表记录分配名次。相同的值名次一样，后续的排名出现跳跃情况</li><li><strong>4）dense_rank()</strong></li><li>给排序过的表记录分配名次。相同的值名次一样，后续的排名不出现跳跃情况</li></ul>
<p class="maodian"><a name="_label4"></a></p><h2>关联查询</h2>
<p class="maodian"><a name="_lab2_4_13"></a></p><h3>关联分类</h3>
<table><thead><tr><th colspan="1" rowspan="1"><p>分类</p></th><th colspan="1" rowspan="1"><p>语法</p></th><th colspan="1" rowspan="1"><p>解析</p></th></tr></thead><tbody><tr><td colspan="1" rowspan="1"><p>内连接</p></td><td colspan="1" rowspan="1"><p>table_name join table_name <strong>on</strong> condition</p></td><td colspan="1" rowspan="1"><p>返回满足条件的记录组合</p></td></tr><tr><td colspan="1" rowspan="1"><p>左外连接</p></td><td colspan="1" rowspan="1"><p>table_name left join table_name on condition</p></td><td colspan="1" rowspan="1"><p>左表为主表，除了返回满足条件的记录组合外，左表中剩余记录也返回，右表字段以null形式占位。</p></td></tr><tr><td colspan="1" rowspan="1"><p>右外连接</p></td><td colspan="1" rowspan="1"><p>table_name right join table_name on condition</p></td><td colspan="1" rowspan="1"><p>右表为主表，除了返回满足条件的记录组合外，右表中剩余记录也返回，左表字段以null形式占位。</p></td></tr></tbody></table>
<p class="maodian"><a name="_lab2_4_14"></a></p><h3>交叉连接</h3>
<p>在使用join连接或者逗号连接查询，但是没有使用on或where关键字来指定关联条件时，就会出现交叉连接</p>
<p>这种交叉连接，产生的记录数为两张表的记录数的乘积，这种结果也被称之为<strong>笛卡尔积</strong>。</p>
<p class="maodian"><a name="_lab2_4_15"></a></p><h3>union 操作符</h3>
<p>如果我们想要将两个查询的结果集<strong>合并</strong>到一起，我们就可以使用union 操作符。</p>
<div class="jb51code"><pre class="brush:sql;">selectcolumn_name,column_name,.... from table_name
union
selectcolumn_name,column_name,.... from table_name </pre></div>
<ul><li><code>union all</code>：两个子句中的重复部分，会保留，不去重。</li><li><code>union</code>：会<strong>去掉</strong>并集中的<strong>重复记录</strong></li></ul>
<p class="maodian"><a name="_label5"></a></p><h2>子查询</h2>
<p>有的时候，当一个查询语句A所需要的数据，不是直观在表中体现，而是另外一个查询语句B查询出来的结果，那么查询语句A就是主查询语句，查询语句B就是子查询语句。这种查询我们称之为高级关联查询，也叫做子查询。</p>
<p><span>子查询语句的位置可以在</span><code><span>where</span></code><span>、</span><code><span>from</span></code><span>、</span><code><span>having</span></code><span>、</span><code><span>select</span></code><span>这四种子句中。</span></p>
<p class="maodian"><a name="_label6"></a></p><h2>列题</h2>
<div class="jb51code"><pre class="brush:sql;">--建表
--学生表
CREATE TABLE `Student`(
   `s_id` VARCHAR(20),
   `s_name` VARCHAR(20) NOT NULL DEFAULT '',
   `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
   `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
   PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
   `c_id`VARCHAR(20),
   `c_name` VARCHAR(20) NOT NULL DEFAULT '',
   `t_id` VARCHAR(20) NOT NULL,
   PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
   `t_id` VARCHAR(20),
   `t_name` VARCHAR(20) NOT NULL DEFAULT '',
   PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
   `s_id` VARCHAR(20),
   `c_id`VARCHAR(20),
   `s_score` INT(3),
   PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
insert into Course values('04' , '体育' , '01');
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select st.s_id , st.s_name , s1.s_score a1 ,s2.s_score a2
from student st
LEFT join score s1 on st.s_id = s1.s_id AND s1.c_id = '01'
LEFT JOIN score s2 on st.s_id = s2.s_id AND s2.c_id = '02'
where s1.s_score IS NOT NULL
AND s1.s_score > COALESCE(s2.s_score, 0);
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select st.s_id , st.s_name , s1.s_score a1,s2.s_score a2
from student st
LEFT join score s1 on st.s_id = s1.s_id AND s1.c_id = '01'
LEFT JOIN score s2 on st.s_id = s2.s_id AND s2.c_id = '02'
where COALESCE(s1.s_score, 0)< COALESCE(s2.s_score, 0)
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select st.s_id , st.s_name , avg(sc.s_score)AS avg_score
from student st
JOIN score sc on st.s_id = sc.s_id
group by st.s_id, st.s_name
HAVING AVG(sc.s_score) >= 60;
SELECT
st.s_id,
st.s_name,
AVG(sc.s_score) AS avg_score
FROM
Student st
JOIN
Score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id, st.s_name
HAVING
AVG(sc.s_score) >= 60;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
            (包括有成绩的和无成绩的)
SELECT
st.s_id,
st.s_name,
AVG(sc.s_score) 平均成绩
FROM
Student st
LEFT JOIN
Score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id, st.s_name
HAVING
AVG(sc.s_score) < 60 or AVG(sc.s_score) is NULL ;
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
st.s_id,
st.s_name,
count(sc.c_id),
SUM(sc.s_score)
FROM
student st LEFT JOIN score sc on st.s_id=sc.s_id
GROUP BY st.s_id;
6、查询"李"姓老师的数量
SELECT
count(0)
from
teacher
WHERE
t_name LIKE '李%';
7、查询学过"张三"老师授课的同学的信息
SELECT
st.s_id,st.s_name,st.s_birth,st.s_sex
FROM
student st
JOIN scoresc on st.s_id=sc.s_id
JOIN course c on sc.c_id=c.c_id
JOIN teacher t on c.t_id=t.t_id
WHERE
t.t_name='张三'
GROUP BY st.s_id;
8、查询没学过"张三"老师授课的同学的信息
SELECT *
FROM student
WHERE s_id not in (SELECT sc.s_id
FROM scoresc
JOIN course c on sc.c_id=c.c_id
JOIN teacher t on c.t_id=t.t_id
WHERE t.t_name ='张三' GROUP BY sc.s_id );
SELECT *
FROM student
WHERE s_id not in (SELECT st.s_id
FROM student st
JOIN scoresc on st.s_id=sc.s_id
JOIN course c on sc.c_id=c.c_id
JOIN teacher t on c.t_id=t.t_id
WHERE
t.t_name='张三'
GROUP BY st.s_id
);
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
st.s_id,st.s_name,st.s_birth,st.s_sex
FROM
student st
JOIN score s1 on st.s_id=s1.s_id and s1.c_id='01'
JOIN score s2 on st.s_id=s2.s_id and s2.c_id='02'
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT st.* FROM
student st
JOIN score s1 on st.s_id=s1.s_id and s1.c_id='01'
WHERE st.s_id NOT IN (
SELECT
st.s_id
FROM
student st
JOIN score s2 on st.s_id=s2.s_id and s2.c_id='02');
11、查询没有学全所有课程的同学的信息
SELECT st.* FROM
student st
WHERE st.s_id NOT IN (
SELECT st.s_id FROM
student st
JOIN score s1 on st.s_id=s1.s_id and s1.c_id='01'
WHERE st.s_id IN (
SELECT st.s_id FROM student st
JOIN score s2 on st.s_id=s2.s_id and s2.c_id='02')
AND st.s_idin (
SELECT st.s_id FROM student st
JOIN score s3 on st.s_id=s3.s_id and s3.c_id='03'))
SELECT st.*
FROM student st
LEFT JOIN score sc on st.s_id=sc.s_id
GROUP BY st.s_id
HAVING COUNT(1)<3;
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT *
FROM student st
JOIN score s2 on st.s_id=s2.s_id
WHERE st.s_id <> '01' AND s2.c_id in(
SELECT s.c_id
FROM score s WHERE s.s_id='01')
GROUP BY st.s_id;
SELECT s.c_id
FROM score s WHERE s.s_id='01';
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT st.*
FROM Student st
JOIN (
SELECT s_id,COUNT(*)AS cnt,             -- 该生修的课程门数
         GROUP_CONCAT(c_id ORDER BY c_id) AS courses -- 课程按字典序拼成串
FROM Score
GROUP BY s_id
) t ON st.s_id = t.s_id
WHERE st.s_id <> '01'                      -- 排除 01 自己
AND t.cnt = (SELECT COUNT(*) FROM Score WHERE s_id = '01')    -- 门数相同
AND t.courses = (SELECT GROUP_CONCAT(c_id ORDER BY c_id)
               FROM Score WHERE s_id = '01');                -- 课程串相同
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT t.s_name
FROM student t
WHERE t.s_id not in(
SELECT s.s_id
FROM score s
WHERE s.c_id in(
SELECT c_id
FROM teacher t
JOIN course c on t.t_id=c.t_id
WHERE t.t_name='张三'));
SELECT s.s_id
FROM score s
WHERE s.c_id in(
SELECT c_id
FROM teacher t
JOIN course c on t.t_id=c.t_id
WHERE t.t_name='张三');
SELECT c_id
FROM teacher t
JOIN course c on t.t_id=c.t_id
WHERE t.t_name='张三';
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT t.s_id ,t.s_name,avg(s.s_score)
FROM student t
join score s on t.s_id=s.s_id
WHERE s.s_score<60
GROUP BY s.s_id
HAVINGcount(0)>=2;
SELECT s.s_id
FROM score s
WHERE s.s_score<60
GROUP BY s.s_id
HAVINGcount(0)>=2;
16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT t.*,s.s_score
FROM student t
join score s on t.s_id=s.s_id and c_id='01'
WHERE s.s_score<60
order by s.s_score DESC;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
st.s_id,
st.s_name,
MAX(CASE WHEN sc.c_id = '01' THEN sc.s_score END) AS 语文,
MAX(CASE WHEN sc.c_id = '02' THEN sc.s_score END) AS 数学,
MAX(CASE WHEN sc.c_id = '03' THEN sc.s_score END) AS 英语,
ROUND(AVG(sc.s_score), 2) AS avg_score
FROM Student st
JOIN Score sc ON st.s_id = sc.s_id
GROUP BY st.s_id, st.s_name
ORDER BY avg_score DESC;</pre></div>

頁: [1]

琼殿技术论坛's Archiver

mysql常用语句与函数大全及举例