SQL 三（单表、多表查询）

系统管理员 2022-06-12 10:15 279阅读 0赞

转自 [http://blog.csdn.net/yangyi22/article/details/7483859][http_blog.csdn.net_yangyi22_article_details_7483859] && [http://hi.baidu.com/wcybxzj/blog/item/220266988e5c1b016e068ceb.html][http_hi.baidu.com_wcybxzj_blog_item_220266988e5c1b016e068ceb.html]

**一、给定一张学生成绩表tb\_grade：学号，姓名，科目号，科目名，分数。（如下表）**

（select \* from tb\_grade order by Sno,Cno;）  
\+------+-------+-----+-------+-------+  
| Sno  | Sname | Cno | Cname | score |  
\+------+-------+-----+-------+-------+  
| 1001 | 李菲  |   1 | 语文  |    86 |  
| 1001 | 李菲  |   2 | 数学  |    56 |  
| 1001 | 李菲  |   3 | 物理  |    48 |  
| 1001 | 李菲  |   4 | 化学  |    90 |  
| 1001 | 李菲  |   5 | 英语  |    57 |  
| 1002 | 王琪  |   1 | 语文  |    90 |  
| 1002 | 王琪  |   2 | 数学  |    68 |  
| 1002 | 王琪  |   3 | 物理  |    87 |  
| 1002 | 王琪  |   4 | 化学  |    68 |  
| 1002 | 王琪  |   5 | 英语  |    45 |  
| 1003 | 杨阳  |   1 | 语文  |    86 |  
| 1003 | 杨阳  |   2 | 数学  |    56 |  
| 1003 | 杨阳  |   3 | 物理  |    48 |  
| 1003 | 杨阳  |   4 | 化学  |    90 |  
| 1003 | 杨阳  |   5 | 英语  |    52 |  
| 1004 | 和树  |   1 | 语文  |    82 |  
| 1004 | 和树  |   2 | 数学  |    56 |  
| 1004 | 和树  |   3 | 物理  |    67 |  
| 1004 | 和树  |   4 | 化学  |    30 |  
| 1004 | 和树  |   5 | 英语  |    68 |

\+------+-------+-----+-------+-------+

set names gbk;
    CREATE TABLE tb_grade(
    	Sno INT NOT NULL,
    	Sname VARCHAR(20) CHARACTER SET gbk NOT NULL DEFAULT "李菲",
    	Cno INT NOT NULL,
    	Cname VARCHAR(20) CHARACTER SET gbk NOT NULL DEFAULT "语文",
    	Score INT NOT NULL);
    
    INSERT INTO tb_grade VALUES("1001", "李菲", "1", "语文", "86");
    INSERT INTO tb_grade VALUES("1001", "李菲", "2", "数学", "56");
    INSERT INTO tb_grade VALUES("1001", "李菲", "3", "物理", "48");
    INSERT INTO tb_grade VALUES("1001", "李菲", "4", "化学", "90");
    INSERT INTO tb_grade VALUES("1001", "李菲", "5", "英语", "57");
    INSERT INTO tb_grade VALUES("1002", "王琪", "1", "语文", "90");
    INSERT INTO tb_grade VALUES("1002", "王琪", "2", "数学", "68");
    INSERT INTO tb_grade VALUES("1002", "王琪", "3", "物理", "87");
    INSERT INTO tb_grade VALUES("1002", "王琪", "4", "化学", "68");
    INSERT INTO tb_grade VALUES("1002", "王琪", "5", "英语", "45");
    INSERT INTO tb_grade VALUES("1003", "杨阳", "1", "语文", "86");
    INSERT INTO tb_grade VALUES("1003", "杨阳", "2", "数学", "56");
    INSERT INTO tb_grade VALUES("1003", "杨阳", "3", "物理", "48");
    INSERT INTO tb_grade VALUES("1003", "杨阳", "4", "化学", "90");
    INSERT INTO tb_grade VALUES("1003", "杨阳", "5", "英语", "52");
    INSERT INTO tb_grade VALUES("1004", "和树", "1", "语文", "82");
    INSERT INTO tb_grade VALUES("1004", "和树", "2", "数学", "56");
    INSERT INTO tb_grade VALUES("1004", "和树", "3", "物理", "67");
    INSERT INTO tb_grade VALUES("1004", "和树", "4", "化学", "30");
    INSERT INTO tb_grade VALUES("1004", "和树", "5", "英语", "68");

1、查询不及格科目数大于等于2的学生学号和不及格科目数量：

select Sno,count(score) as '不及格科目数' from tb_grade where score<60 group by Sno having count(score)>=2;

\+------+--------------+  
| Sno  | 不及格科目数 |  
\+------+--------------+  
| 1001 |            3 |  
| 1003 |            3 |  
| 1004 |            2 |  
\+------+--------------+

2、查询不及格科目数大于等于2的学生学号和学生姓名：

select Sno,Sname from tb_grade where score<60 group by Sno having count(score)>=2;

\+------+-------+

| Sno  | Sname |  
\+------+-------+  
| 1001 | 李菲  |  
| 1003 | 杨阳  |  
| 1004 | 和树  |  
\+------+-------+

3、查询不及格科目数大于等于2的学生学号、学生姓名、科目号、科目名称和分数，并按学号降序、科目号升序排序：

select * from tb_grade where score<60 and Sno in( select  Sno  from tb_grade 
    where score<60  group by  Sno having count(score)>=2 ) order by Sno desc,Cno asc;

\+------+-------+-----+-------+-------+

| Sno  | Sname | Cno | Cname | score |  
\+------+-------+-----+-------+-------+  
| 1004 | 和树  |   2 | 数学  |    56 |  
| 1004 | 和树  |   4 | 化学  |    30 |  
| 1003 | 杨阳  |   2 | 数学  |    56 |  
| 1003 | 杨阳  |   3 | 物理  |    48 |  
| 1003 | 杨阳  |   5 | 英语  |    52 |  
| 1001 | 李菲  |   2 | 数学  |    56 |  
| 1001 | 李菲  |   3 | 物理  |    48 |  
| 1001 | 李菲  |   5 | 英语  |    57 |  
\+------+-------+-----+-------+-------+

**二、有三张表：class、student、score**

**班级表 class：                                                           **

\+-------+---------+---------+  
| classID | className |  
\+---------+-----------+  
|       1 | 一班      |  
|       2 | 二班      |  
|       3 | 三班      |  
\+---------+-----------+

set names gbk;
    CREATE TABLE class(
    	classID INT NOT NULL AUTO_INCREMENT,
    	className VARCHAR(20) CHARACTER SET gbk NOT NULL,
    	PRIMARY KEY(classID));
    INSERT INTO class VALUES("1", "一班");
    INSERT INTO class VALUES("2", "二班");
    INSERT INTO class VALUES("3", "三班");

**学生表 student：**

\+-------+---------+---------+  
| stuID | classID | stuName |  
\+-------+---------+---------+  
|  1001 |       1 | 张三    |  
|  1002 |       1 | 李丽    |  
|  1003 |       1 | 钱封    |  
|  1004 |       2 | 杨国    |  
|  1005 |       2 | 小样    |  
|  1006 |       2 | 区天    |  
|  1007 |       3 | 李三宅  |  
|  1008 |       3 | 黄武    |  
|  1009 |       3 | 赵六    |  
\+-------+---------+---------+

CREATE TABLE student(
    	stuID INT NOT NULL AUTO_INCREMENT,
    	classID INT NOT NULL,
    	stuName VARCHAR(20) CHARACTER SET gbk NOT NULL,
    	PRIMARY KEY(stuID));
    INSERT INTO student VALUES("1001",  "1", "张三");
    INSERT INTO student VALUES("1002",  "1", "李丽");
    INSERT INTO student VALUES("1003",  "1", "钱封");
    INSERT INTO student VALUES("1004",  "2", "杨国");
    INSERT INTO student VALUES("1005",  "2", "小样");
    INSERT INTO student VALUES("1006",  "2", "区天");
    INSERT INTO student VALUES("1007",  "3", "李三宅");
    INSERT INTO student VALUES("1008",  "3", "黄武");
    INSERT INTO student VALUES("1009",  "3", "赵六");

**分数表 score：**

\+----------+-------+--------+-------+  
| courseID | stuID | course | score |  
\+----------+-------+--------+-------+  
|        2 |  1001 | 数学   |    73 |  
|        3 |  1001 | 英语   |    79 |  
|        1 |  1001 | 语文   |    81 |  
|        3 |  1002 | 英语   |    87 |  
|        2 |  1002 | 数学   |    83 |  
|        1 |  1002 | 语文   |    79 |  
|        1 |  1003 | 语文   |    65 |  
|        3 |  1003 | 英语   |    65 |  
|        2 |  1003 | 数学   |    97 |  
|        1 |  1004 | 语文   |    78 |  
|        3 |  1004 | 英语   |    78 |  
|        2 |  1004 | 数学   |    86 |  
|        1 |  1005 | 语文   |    67 |  
|        3 |  1005 | 英语   |    88 |  
|        2 |  1005 | 数学   |    89 |  
|        2 |  1006 | 数学   |    90 |  
|        3 |  1006 | 英语   |    92 |  
|        1 |  1006 | 语文   |    98 |  
|        1 |  1007 | 语文   |    85 |  
|        2 |  1007 | 数学   |    78 |  
|        3 |  1007 | 英语   |    72 |  
|        1 |  1008 | 语文   |    78 |  
|        3 |  1008 | 英语   |    77 |  
|        2 |  1008 | 数学   |    85 |  
|        3 |  1009 | 英语   |    94 |  
|        2 |  1009 | 数学   |    91 |  
|        1 |  1009 | 语文   |    68 |  
\+----------+-------+--------+-------+

CREATE TABLE score(
    	courseID INT NOT NULL,
    	stuID INT NOT NULL,
    	course VARCHAR(20) CHARACTER SET gbk NOT NULL,
    	score INT NOT NULL);
    INSERT INTO score VALUES("2", "1001",  "数学", "73");
    INSERT INTO score VALUES("3", "1001",  "英语", "79");
    INSERT INTO score VALUES("1", "1001",  "语文", "81");
    INSERT INTO score VALUES("3", "1002",  "英语", "87");
    INSERT INTO score VALUES("2", "1002",  "数学", "83");
    INSERT INTO score VALUES("1", "1002",  "语文", "79");
    INSERT INTO score VALUES("1", "1003",  "英语", "65");
    INSERT INTO score VALUES("3", "1003",  "数学", "65");
    INSERT INTO score VALUES("2", "1003",  "语文", "97");
    INSERT INTO score VALUES("1", "1004",  "英语", "78");
    INSERT INTO score VALUES("3", "1004",  "数学", "78");
    INSERT INTO score VALUES("2", "1004",  "语文", "86");
    INSERT INTO score VALUES("1", "1005",  "英语", "67");
    INSERT INTO score VALUES("3", "1005",  "数学", "88");
    INSERT INTO score VALUES("2", "1005",  "语文", "89");
    INSERT INTO score VALUES("2", "1006",  "英语", "90");
    INSERT INTO score VALUES("2", "1006",  "数学", "92");
    INSERT INTO score VALUES("2", "1006",  "语文", "97");
    INSERT INTO score VALUES("2", "1007",  "英语", "85");
    INSERT INTO score VALUES("2", "1007",  "数学", "78");
    INSERT INTO score VALUES("2", "1007",  "语文", "72");
    INSERT INTO score VALUES("2", "1008",  "英语", "78");
    INSERT INTO score VALUES("2", "1008",  "数学", "77");
    INSERT INTO score VALUES("2", "1008",  "语文", "85");
    INSERT INTO score VALUES("2", "1009",  "英语", "94");
    INSERT INTO score VALUES("2", "1009",  "数学", "91");
    INSERT INTO score VALUES("2", "1009",  "语文", "68");

1、查询各班各科分数最高的学生学号，姓名，班级名称，科目名称，分数：

select stu.stuID,stu.stuName,c.classID,c.className, sc.score from class c,student stu,
     score sc where c.classID=stu.classID and stu.stuID=sc.stuID order by c.classID,stu.stuID;

===========================================================================

关键字: mysql 中文  
MySQL的默认编码是Latin1，不支持中文，那么如何修改MySQL的默认编码呢，下面以UTF-8为例来说明，可能为gbk或者 UTF-8或其他  
需要注意的是，要修改的地方非常多，相应的修改方法也很多。下面是一种最简单最彻底的方法：   
一、Windows   
1、中止MySQL服务   
2、在MySQL的安装目录下找到my.ini，如果没有就把my-medium.ini复制为一个my.ini即可   
3、打开my.ini以后，在\[client\]和\[mysqld\]下面均加上default-character-set=utf8，保存并关闭   
4、启动MySQL服务   
二、Linux   
1、中止MySQL服务（bin/mysqladmin -u root shutdown）   
2、在/etc/下找到my.cnf，如果没有就把MySQL的安装目录下的support-files目录下的my-medium.cnf复制到/etc/下并改名为my.cnf即可   
3、打开my.cnf以后，在\[client\]和\[mysqld\]下面均加上default-character-set=utf8， 保存并关闭   
4、启动MySQL服务（bin/mysqld\_safe &）   
非常简单，这样的修改一劳永逸，今后MySQL一切相关的默认编码均为UTF-8了，创建新表格的时候无需再次设置   
需要注意的是，当前数据库中已经存在的数据仍保留现有的编码方式，因此需要自行转码，方法在网上有很多，不再赘述。

[http_blog.csdn.net_yangyi22_article_details_7483859]: http://blog.csdn.net/yangyi22/article/details/7483859
[http_hi.baidu.com_wcybxzj_blog_item_220266988e5c1b016e068ceb.html]: http://hi.baidu.com/wcybxzj/blog/item/220266988e5c1b016e068ceb.html