转自 http://blog.csdn.net/yangyi22/article/details/7483859 && 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了,创建新表格的时候无需再次设置
需要注意的是,当前数据库中已经存在的数据仍保留现有的编码方式,因此需要自行转码,方法在网上有很多,不再赘述。