Mysql数据库理论基础之五--SELECT单多表查询、子查询、别名

发布时间：2020-08-29 15:53:29 作者：风过_无痕
来源：网络阅读：4697

一、简介

由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子句：

布尔关系表达式 >= 、<= 、> 、< 、= 表示大于等于，小于等于，大于，小于，等于。

Mysql数据库理论基础之五--SELECT单多表查询、子查询、别名

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;

+-------+-------+------+-----+---------+------------+

+-------+-------+------+-----+---------+------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

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

+-------+----------------------+------+-----+---------+----------------+

+-------+----------------------+------+-----+---------+----------------+

| CID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

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

+------------+----------------------+------+-----+---------------------+----------------+

+------------+----------------------+------+-----+---------------------+----------------+

| SID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

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

+--------+----------------------+------+-----+---------+----------------+

+--------+----------------------+------+-----+---------+----------------+

| TID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| 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

/*!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单多表查询、子查询、别名

一、简介

二、MySQL 查询引擎

相关阅读