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 DISTINCT CID2 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

;

;

;

;

;

;

;

;

;

;

--

-- Current Database: `jiaowu`

--

CREATE DATABASE `jiaowu` ;

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;

;

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);

;

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;

;

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);

;

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;

;

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');

;

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;

;

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);

;

UNLOCK TABLES;

;

;

;

;

;

;

;

;

-- Dump completed on 2012-04-06  3:09:09