溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
由MySQL AB公司開發，是最流行的開放源碼SQL數據庫管理系統，主要特點：
1、是一種數據庫管理系統
2、是一種關聯數據庫管理系統
3、是一種開放源碼軟件，且有大量可用的共享MySQL軟件
4、MySQL數據庫服務器具有快速、可靠和易于使用的特點
5、MySQL服務器工作在客戶端/服務器模式下，或嵌入式系統中
InnoDB存儲引擎將InnoDB表保存在一個表空間內，該表空間可由數個文件創建。這樣，表的大小就能超過單獨文件的最大容量。表空間可包括原始磁盤分區，從而使得很大的表成為可能。表空間的最大容量為64TB。
數據庫實驗環境：如附件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
/*!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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
