Mysql数据库理论基础之五--SELECT单多表查询、子查询、别名
一、简介
由MySQL AB公司开发，是最流行的开放源码SQL数据库管理系统，主要特点：
专注于为中小企业提供网站设计、网站制作服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业平定免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了上千企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。
1、是一种数据库管理系统
2、是一种关联数据库管理系统
3、是一种开放源码软件，且有大量可用的共享MySQL软件
4、MySQL数据库服务器具有快速、可靠和易于使用的特点
5、MySQL服务器工作在客户端/服务器模式下，或嵌入式系统中
InnoDB存储引擎将InnoDB表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为64TB。
二、MySQL 查询引擎
数据库实验环境：如附件jiaowu.sql ，以下为导入方法
[root@lamp ~]# ll -h
-rw-r--r--. 1 root root 5.2K Jan 7 2015 jiaowu.sql
[root@lamp ~]# mysql -uroot -p < /root/jiaowu.sql
Enter password:
mysql> use jiaowu
Database changed
mysql> show tables;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses |
| scores |
| students |
| tutors |
+------------------+
4 rows in set (0.00 sec)
2.1.SELECT：查询
SELECT select-list FROM tb WHERE qualification 根据标准qualification查找对应的行
查询语句类型： qualification条件 field领域 distinct独特的，没有重复的
简单查询：
多表查询：
子查询：
SELECT * FROM tb_name: #查询tb_name表的所有信息
SELECT field1,field2 FROM tb_name: #投影显示所设定的领域条目（field），一个field就是一列
SELECT [DISTINCT] * FROM tb_name WHERE qualification；#从表中选择符合条件的独特的不重复的条目
FROM子句：
表、多个表、其他SELECT语句
WHERE子句：
布尔关系表达式 >= 、<= 、> 、< 、= 表示大于等于，小于等于，大于，小于，等于。
SELECT用法归纳：
2.2.逻辑关系：
AND(与&&) OR(或||) NOT(非!) XOR(异或)
mysql> SELECT Name,Age,Gender FROM students WHERE NOT Age>20 AND NOT Gender='M';
#选择查询年龄不大于20，且性别不是男的数据,也可以写成下面的条件
mysql> SELECT Name,Age,Gender FROM students WHERE NOT ( Age>20 OR Gender='M' );
2.3.特殊关系： BETWEEN ... AND ... 在两者之间之间。
2.4. LIKE ''
%：任意长度任意字符
_：任意单个字符
Usage:
SELECT Name FROM students WHERE Name LIKE 'Y%'; 查找Name字段以Y开头的数据。
SELECT Name FROM students WHERE Name LIKE '%ing%'; 查找Name中必须包括ing的字段
SELECT Name FROM students WHERE Name LIKE 'Y____'; 查找Name中Y后面至少跟着4个字符
mysql> SELECT Name FROM students WHERE Name LIKE 'Y%';
+--------------+
| Name |
+--------------+
| YangGuo |
| YueLingshang |
| YiLin |
+--------------+
3 rows in set (0.00 sec)
mysql> SELECT Name FROM students WHERE Name LIKE '%ing%';
+--------------+
| Name |
+--------------+
| GuoJing |
| DingDian |
| YueLingshang |
| LingHuchong |
+--------------+
4 rows in set (0.00 sec)
mysql> SELECT Name FROM students WHERE Name LIKE 'Y____';
+-------+
| Name |
+-------+
| YiLin |
+-------+
1 row in set (0.00 sec)
2.5.支持正则表达式：REGEXP或者RLIKE
Usage:#查找Name字段中，行尾为g的数据，行首为X或Y的数据
mysql>SELECT Name,Age FROM students WHERE Name RLIKE '.*g$';
+--------------+------+
| Name | Age |
+--------------+------+
| GuoJing | 19 |
| HuangRong | 16 |
| YueLingshang | 18 |
| LingHuchong | 22 |
+--------------+------+
4 rows in set (0.00 sec)
mysql> SELECT Name,Age FROM students WHERE Name RLIKE '^[xy]';
+--------------+------+
| Name | Age |
+--------------+------+
| YangGuo | 17 |
| YueLingshang | 18 |
| Xuzhu | 26 |
| YiLin | 19 |
+--------------+------+
4 rows in set (0.00 sec)
2.6.离散条件的查找：IN
Usage: 查找Age字段为18或20或25岁的相关数据
mysql> SELECT Name,Age FROM students WHERE Age IN (18,20,25);
+--------------+------+
| Name | Age |
+--------------+------+
| DingDian | 25 |
| YueLingshang | 18 |
| ZhangWuji | 20 |
+--------------+------+
3 rows in set (0.00 sec)
2.7.有空值的时候，比较：IS NULL ,NOT NULL ORDER BY ... 以...某字段升序排列
Usage:查找Name字段为空值的数据
mysql>SELECT Name,Age FROM students WHERE Age IS NOT NULL ;
+--------------+------+
| Name | Age |
+--------------+------+
| GuoJing | 19 |
| YangGuo | 17 |
| DingDian | 25 |
| HuFei | 31 |
| HuangRong | 16 |
| YueLingshang | 18 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LingHuchong | 22 |
| YiLin | 19 |
+--------------+------+
10 rows in set (0.00 sec)
2.8.按照升序或者降序排列出查找的数据：ORDER BY field_name {ASC|DESC};
ASC升序排列(默认值) DESC降序排列
SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name;
#查找CID字段不为空的数据并按字段Name的升序排列；
SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name DESC;
#查找CID字段不为空的数据并按字段Name的降序排列；
mysql>SELECT Name,Age FROM students ORDER BY Age desc;
+--------------+------+
| Name | Age |
+--------------+------+
| HuFei | 31 |
| Xuzhu | 26 |
| DingDian | 25 |
| LingHuchong | 22 |
| ZhangWuji | 20 |
| GuoJing | 19 |
| YiLin | 19 |
| YueLingshang | 18 |
| YangGuo | 17 |
| HuangRong | 16 |
+--------------+------+
10 rows in set (0.00 sec)
2.9.字段别名：AS
Usage: SELECT Name AS Student_Name FROM student;
mysql> SELECT Name FROM student;
+------------+
| Name |
+------------+
| Li Lianjie |
| Cheng Long |
| Yang Guo |
| Guo Jing |
+------------+
4 rows in set (0.00 sec)
mysql> SELECT Name AS Student_Name FROM student;
+--------------+
|Student_Name | #AS别名为Student_Name
+--------------+
| Li Lianjie |
| Cheng Long |
| Yang Guo |
| Guo Jing |
+--------------+
4 rows in set (0.00 sec)
2.10.LIMIT子句：LIMIT [offset,]Count offset偏移多少，Count显示多少
Usage: SELECT Name AS Student_Name FROM student LIMIT 3;
#查找显示Name别名为Student_Name,只显示前3个值
Usage: SELECT Name AS Student_Name FROM student LIMIT 2,2;
#查找显示Name别名为Student_Name,偏移掉前2个数据不显示，显示第2个后的2个值
mysql> SELECT Name AS Student_Name FROM student LIMIT 3;
+--------------+
| Student_Name |
+--------------+
| Li Lianjie |
| Cheng Long |
| Yang Guo |
+--------------+
3 rows in set (0.00 sec)
mysql> SELECT Name AS Student_Name FROM student LIMIT 2,2;
+--------------+
| Student_Name |
+--------------+
| Yang Guo |
| Guo Jing |
+--------------+
2 rows in set (0.00 sec)
2.11.聚合运算：
SUM() 求和, MIN() 最小值, MAX() 最大值,
AVG() 平均值, COUNT() 统计字段中相同数值的个数；
新建立一张表：
mysql>CREATE TABLE class(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,Name CHAR(20) NOT NULL, Age TINYINT NOT NULL);
mysql>INSERT INTO class (Name,Age) VALUES ('Yang Guo',22),('Guo Jing',46),('Xiao Longnv',18),('Huang Rong',40);
mysql> DESC class;
+-------+-------+------+-----+---------+------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(20) | NO | | NULL | |
| Age | tinyint(4) | NO | | NULL | |
+-----+--------+------+-----+---------+-----------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM class;
+----+-------------+-----+
| ID | Name | Age |
+----+-------------+-----+
| 1 | Yang Guo | 22 |
| 2 | Guo Jing | 46 |
| 3 | Xiao Longnv | 18 |
| 4 | Huang Rong | 40 |
+----+-------------+-----+
4 rows in set (0.00 sec)
mysql> SELECT SUM(Age) FROM class;
+----------+
| SUM(Age) |
+----------+
| 126 |
+----------+
1 row in set (0.02 sec)
mysql> SELECT MAX(Age) FROM class;
+----------+
| MAX(Age) |
+----------+
| 46 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT MIN(Age) FROM class;
+----------+
| MIN(Age) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT AVG(Age) FROM class;
+----------+
| AVG(Age) |
+----------+
| 31.5000 |
+----------+
1 row in set (0.00 sec)
2.12.分组：GROUP BY ... HAVING qualification 根据...分组且满足条件qualification
对于GROUP BY的条件选择需用HAVING作为条件筛选，而不是用WHERE
mysql> SELECT Age,Gender FROM class1 GROUP BY Gender; #以性别分组显示
+-----+--------+
| Age | Gender |
+-----+--------+
| 18 | F |
| 22 | M |
+-----+--------+
2 rows in set (0.00 sec)
mysql> SELECT AVG(Age),Gender FROM class1 GROUP BY Gender;#求男同学和女同学的平均年龄。
+----------+--------+
| AVG(Age) | Gender |
+----------+--------+
| 29.0000 | F |
| 34.0000 | M |
+----------+--------+
2 rows in set (0.00 sec)
mysql> SELECT Name,AVG(Age) FROM class1 GROUP BY Gender HAVING Name RLIKE 'Y.*';
#求以Gender分组的年龄平局值，且只显示以Y开头的Name字段的平均值和姓名
+----------+----------+
| Name | AVG(Age) |
+----------+----------+
| Yang Guo | 34.0000 |
+----------+----------+
1 row in set (0.00 sec)
2.13.多表查询 (复合查询)： 连接：
2.13.1.交叉连接： （笛卡尔乘积）
SELECT * FROM students,course; #查询students和course表的内容
2.13.2.自然连接：
查询students表和courses表CID1=CID的选项，并显示名称,只保留具有等值关系的
mysql>select s.Name, c.Cname FROM students AS s,courses AS c WHERE s.CID1=c.CID;
+--------------+------------------+
| Name | Cname |
+--------------+------------------+
| GuoJing | TaiJiquan |
| YangGuo | TaiJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TaiJiquan |
+--------------+------------------+
8 rows in set (0.01 sec)
2.13.3.外连接：
左外连接：左表 LEFT JOIN 右表 ON 条件
右外连接：左表 RIGHT JOIN 右表 ON 条件
显示左表中所有学生，并查看他们所选修的课程名称，没有选修课程的直接显示NULL
mysql> select s.Name, c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;
+--------------+------------------+
| Name | Cname |
+--------------+------------------+
| GuoJing | TaiJiquan |
| YangGuo | TaiJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TaiJiquan |
| LingHuchong | NULL |
| YiLin | NULL |
+--------------+------------------+
10 rows in set (0.00 sec)
查看右表中所有选修课程，并查看有哪些学生选修了，课程没有被选修的直接显示NULL
mysql> select s.Name, c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID;
+--------------+------------------+
| Name | Cname |
+--------------+------------------+
| ZhangWuji | Hamagong |
| GuoJing | TaiJiquan |
| YangGuo | TaiJiquan |
| Xuzhu | TaiJiquan |
| NULL | Yiyangzhi |
| NULL | Jinshejianfa |
| HuangRong | Qianzhuwandushou |
| DingDian | Qishangquan |
| NULL | Qiankundanuoyi |
| HuFei | Wanliduxing |
| YueLingshang | Wanliduxing |
| NULL | Pixiejianfa |
| NULL | Jiuyinbaiguzhua |
+--------------+------------------+
13 rows in set (0.00 sec)
2.13.4.自连接： 对于一个表自己的多个字段进行连接查询
查看students表中TID和SID相同的字段，并显示他们的学生名和老师名。AS取别名
mysql>SELECT c.Name AS student,s.Name AS teacher FROM students AS c,students AS s WHERE c.TID=s.SID;
+-----------+-------------+
| student | teacher |
+-----------+-------------+
| GuoJing | DingDian |
| YangGuo | GuoJing |
| DingDian | ZhangWuji |
| HuFei | HuangRong |
| HuangRong | LingHuchong |
+-----------+-------------+
5 rows in set (0.01 sec)
2.14.子查询：
2.14.1.查询语句里面嵌套其他的子查询，比较操作中使用子查询，子查询只能返回单个值；
查询年龄大于平均年龄的学生姓名
mysql> SELECT Name FROM students WHERE Age > (select AVG(Age) FROM students);
+-------------+
| Name |
+-------------+
| DingDian |
| HuFei |
| Xuzhu |
| LingHuchong |
+-------------+
4 rows in set (0.00 sec)
2.14.2. IN (): 使用子查询
查询学生年龄中与老师年龄一样的学生姓名，IN表示子查询可以是一组数值。
mysql> SELECT Name FROM students WHERE Age IN(select Age FROM students);
+--------------+
| Name |
+--------------+
| GuoJing |
| YangGuo |
| DingDian |
| HuFei |
| HuangRong |
| YueLingshang |
| ZhangWuji |
| Xuzhu |
| LingHuchong |
| YiLin |
+--------------+
10 rows in set (0.00 sec)
2.14.3.FROM中也可以插入子查询：
从SELECT查询的结构的表中再从中查询其他符合条件的数据
mysql>SELECT Name,Age FROM (SELECT Name,Age FROM students) AS t WHERE t.Age >= 20;
+-------------+------+
| Name | Age |
+-------------+------+
| DingDian | 25 |
| HuFei | 31 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LingHuchong | 22 |
+-------------+------+
5 rows in set (0.00 sec)
2.15.联合查询： UNION ：把两张表连接成一张表查询显示出来
把courses表中CID和Couse字段跟class表中的Name和Age字段联合起来显示为一张表
mysql> SELECT Name,Age FROM class;
+-------------+-----+
| Name | Age |
+-------------+-----+
| Yang Guo | 22 |
| Guo Jing | 46 |
| Xiao Longnv | 18 |
| Huang Rong | 40 |
+-------------+-----+
4 rows in set (0.00 sec)
mysql> SELECT CID,Couse FROM courses;
+-----+-----------+
| CID | Couse |
+-----+-----------+
| 1 | physics |
| 2 | english |
| 3 | chemistry |
| 4 | maths |
+-----+-----------+
4 rows in set (0.00 sec)
mysql> (SELECT Name,Age FROM class) UNION (SELECT CID,Couse FROM courses);
+-------------+-----------+
| Name | Age |
+-------------+-----------+
| Yang Guo | 22 |
| Guo Jing | 46 |
| Xiao Longnv | 18 |
| Huang Rong | 40 |
| 1 | physics |
| 2 | english |
| 3 | chemistry |
| 4 | maths |
+-------------+-----------+
8 rows in set (0.00 sec)
3.实例：
mysql> desc courses;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| CID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Cname | varchar(100) | NO | | NULL | |
| TID | smallint(6) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> select * from courses;
+-----+------------------+-----+
| CID | Cname | TID |
+-----+------------------+-----+
| 1 | Hamagong | 2 |
| 2 | TaiJiquan | 3 |
| 3 | Yiyangzhi | 6 |
| 4 | Jinshejianfa | 1 |
| 5 | Qianzhuwandushou | 4 |
| 6 | Qishangquan | 5 |
| 7 | Qiankundanuoyi | 7 |
| 8 | Wanliduxing | 8 |
| 9 | Pixiejianfa | 3 |
| 10 | Jiuyinbaiguzhua | 7 |
+-----+------------------+-----+
10 rows in set (0.00 sec)
mysql> desc students;
+------------+----------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------------------+----------------+
| SID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | YES| | NULL | |
| Gender | enum('F','M') | YES| | M | |
| CID1 | smallint(5) unsigned | YES | | NULL | |
| CID2 | smallint(5) unsigned | YES | | NULL | |
| TID | smallint(6) | YES | | NULL | |
| CreateTime| datetime | YES | | 2012-04-06 10:00:00 | |
+------------+----------------------+------+-----+---------------------+----------------+
8 rows in set (0.00 sec)
mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name | Age | Gender | CID1 | CID2 | TID | CreateTime |
+-----+--------------+------+--------+------+------+------+---------------------+
| 1 | GuoJing | 19 | M | 2 | 7 | 3 | 2012-04-06 10:00:00 |
| 2 | YangGuo| 17 | M | 2 | 3 | 1 | 2012-04-06 10:00:00 |
| 3 | DingDian| 25 | M | 6 | 1 | 7 | 2012-04-06 10:00:00 |
| 4 | HuFei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 |
| 5 | HuangRong|16 | F | 5 | 9 | 9 | 2012-04-06 10:00:00 |
| 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 2012-04-06 10:00:00 |
| 7 | ZhangWuji| 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 |
| 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 |
| 9 | LingHuchong| 22 | M | 11 | NULL | NULL | 2012-04-06 10:00:00 |
|10 | YiLin |19 | F | 18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)
mysql> desc tutors;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| TID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Tname | varchar(50) | NO | | NULL | |
| Gender| enum('F','M') | YES | | M | |
| Age | tinyint(3) unsigned | YES | | NULL| |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
9 rows in set (0.00 sec)
3.1.挑选出courses表中没有被students表中的CID2学习的课程的课程名称；
从students表中找出CID2不空且不重复的行，再从courses表中找到CID不在刚才查找的那些行里面的Cname值
mysql> SELECT Cname FROM courses WHERE CID NOT IN (SELECT DISTINCTCID2 FROM students WHERE CID2 IS NOT NULL); #DISTINCT 除去重复
+------------------+
| Cname |
+------------------+
| TaiJiquan |
| Qianzhuwandushou |
| Qishangquan |
| Wanliduxing |
+------------------+
4 rows in set (0.01 sec)
3.2.找出students表中CID2有两个或者以上的同学学习了的，同一门课程的课程名称；
查找一门课程至少2个同学学习，并显示课程名称。
mysql> SELECT Cname FROM courses WHERE CID IN (SELECT CID2 FROM students GROUP BY CID2 HAVING COUNT(CID2) >= 2);
+----------------+
| Cname |
+----------------+
| Jinshejianfa |
| Qiankundanuoyi |
+----------------+
2 rows in set (0.01 sec)
3.3.显示每一个课程及其相关的老师，没有老师教授的课程将其老师显示为空；
msyql> SELECT t.Tname,c.Cname FROM tutors AS t RIGHT JOIN courses AS c ON t.TID=c.TID;
+--------------+------------------+
| Tname | Cname |
+--------------+------------------+
| HuangYaoshi | Hamagong |
| Miejueshitai | TaiJiquan |
| YuCanghai | Yiyangzhi |
| HongQigong | Jinshejianfa |
| OuYangfeng | Qianzhuwandushou |
| YiDeng | Qishangquan |
| Jinlunfawang | Qiankundanuoyi |
| HuYidao | Wanliduxing |
| Miejueshitai | Pixiejianfa |
| Jinlunfawang | Jiuyinbaiguzhua |
+--------------+------------------+
10 rows in set (0.01 sec)
3.4.显示每一位老师及其所教授的课程，没有教授的课程保持为null;
msyql> SELECT t.Tname,c.Cname FROM tutors AS t LEFT JOIN courses AS c ON t.TID=c.TID;
+--------------+------------------+
| Tname | Cname |
+--------------+------------------+
| HongQigong | Jinshejianfa |
| HuangYaoshi | Hamagong |
| Miejueshitai | TaiJiquan |
| Miejueshitai | Pixiejianfa |
| OuYangfeng | Qianzhuwandushou |
| YiDeng | Qishangquan |
| YuCanghai | Yiyangzhi |
| Jinlunfawang | Qiankundanuoyi |
| Jinlunfawang | Jiuyinbaiguzhua |
| HuYidao | Wanliduxing |
| NingZhongze | NULL |
+--------------+------------------+
11 rows in set (0.00 sec)
3.5.显示每位同学的CID1课程名及其讲授了相关课程的老师名称；
mysql>SELECT Name,Cname,Tname FROM students,courses,tutors WHERE students.CID1=courses.CID AND courses.TID=tutors.TID;
+--------------+------------------+--------------+
| Name | Cname | Tname |
+--------------+------------------+--------------+
| GuoJing | TaiJiquan | Miejueshitai |
| YangGuo | TaiJiquan | Miejueshitai |
| DingDian | Qishangquan | YiDeng |
| HuFei | Wanliduxing | HuYidao |
| HuangRong | Qianzhuwandushou | OuYangfeng |
| YueLingshang | Wanliduxing | HuYidao |
| ZhangWuji | Hamagong | HuangYaoshi |
| Xuzhu | TaiJiquan | Miejueshitai |
+--------------+------------------+--------------+
8 rows in set (0.00 sec)
n0-end--78
以下为jiaowu.sql文本内容：
-- MySQL dump 10.11
--
-- Host: localhost Database: jiaowu
-- ------------------------------------------------------
-- Server version5.5.20-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `jiaowu`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `jiaowu` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `jiaowu`;
--
-- Table structure for table `courses`
--
DROP TABLE IF EXISTS `courses`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `courses` (
`CID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`Cname` varchar(100) NOT NULL,
`TID` smallint(6) NOT NULL,
UNIQUE KEY `CID` (`CID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `courses`
--
LOCK TABLES `courses` WRITE;
/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
INSERT INTO `courses` VALUES (1,'Hamagong',2),(2,'TaiJiquan',3),(3,'Yiyangzhi',6),(4,'Jinshejianfa',1),(5,'Qianzhuwandushou',4),(6,'Qishangquan',5),(7,'Qiankundanuoyi',7),(8,'Wanliduxing',8),(9,'Pixiejianfa',3),(10,'Jiuyinbaiguzhua',7);
/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `scores`
--
DROP TABLE IF EXISTS `scores`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `scores` (
`ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`SID` smallint(6) NOT NULL,
`CID` smallint(6) NOT NULL,
`Score` float DEFAULT NULL,
UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `scores`
--
LOCK TABLES `scores` WRITE;
/*!40000 ALTER TABLE `scores` DISABLE KEYS */;
INSERT INTO `scores` VALUES (1,2,2,67),(2,2,3,71),(3,1,2,90),(4,1,7,45),(5,3,6,32),(6,3,1,99),(7,4,8,95),(8,4,10,36);
/*!40000 ALTER TABLE `scores` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `students`
--
DROP TABLE IF EXISTS `students`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `students` (
`SID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned DEFAULT NULL,
`Gender` enum('F','M') DEFAULT 'M',
`CID1` smallint(5) unsigned DEFAULT NULL,
`CID2` smallint(5) unsigned DEFAULT NULL,
`TID` smallint(6) DEFAULT NULL,
`CreateTime` datetime DEFAULT '2012-04-06 10:00:00',
UNIQUE KEY `SID` (`SID`)
) ENGINE=InnoDB AUTO_INCREMENT=3907 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `students`
--
LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (1,'GuoJing',19,'M',2,7,3,'2012-04-06 10:00:00'),(2,'YangGuo',17,'M',2,3,1,'2012-04-06 10:00:00'),(3,'DingDian',25,'M',6,1,7,'2012-04-06 10:00:00'),(4,'HuFei',31,'M',8,10,5,'2012-04-06 10:00:00'),(5,'HuangRong',16,'F',5,9,9,'2012-04-06 10:00:00'),(6,'YueLingshang',18,'F',8,4,NULL,'2012-04-06 10:00:00'),(7,'ZhangWuji',20,'M',1,7,NULL,'2012-04-06 10:00:00'),(8,'Xuzhu',26,'M',2,4,NULL,'2012-04-06 10:00:00'),(9,'LingHuchong',22,'M',11,NULL,NULL,'2012-04-06 10:00:00'),(10,'YiLin',19,'F',18,NULL,NULL,'2012-04-06 10:00:00');
/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tutors`
--
DROP TABLE IF EXISTS `tutors`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tutors` (
`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`Tname` varchar(50) NOT NULL,
`Gender` enum('F','M') DEFAULT 'M',
`Age` tinyint(3) unsigned DEFAULT NULL,
UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `tutors`
--
LOCK TABLES `tutors` WRITE;
/*!40000 ALTER TABLE `tutors` DISABLE KEYS */;
INSERT INTO `tutors` VALUES (1,'HongQigong','M',93),(2,'HuangYaoshi','M',63),(3,'Miejueshitai','F',72),(4,'OuYangfeng','M',76),(5,'YiDeng','M',90),(6,'YuCanghai','M',56),(7,'Jinlunfawang','M',67),(8,'HuYidao','M',42),(9,'NingZhongze','F',49);
/*!40000 ALTER TABLE `tutors` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2012-04-06 3:09:09
分享题目：Mysql数据库理论基础之五--SELECT单多表查询、子查询、别名
分享路径：http://azwzsj.com/article/gssesg.html
