The fifth theoretical basis of Mysql database-- SELECT single-multi-table query, sub-query, alias 04/27 Update SLTechnology News&Howtos

The fifth theoretical basis of Mysql database-- SELECT single-multi-table query, sub-query, alias

2025-04-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database server is fast, reliable and easy to use.

5. MySQL server works in client / server mode, or in embedded system.

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

2. MySQL query engine

Database experimental environment: for example, attachment jiaowu.sql, the following is the import method

[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: query

SELECT select-list FROM tb WHERE qualification looks up the corresponding row according to the standard qualification

Query statement type: qualification conditional field domain distinct unique, no duplicates

Simple query:

Multi-table query:

Subquery:

SELECT * FROM tb_name: # query all the information of the tb_name table

SELECT field1,field2 FROM tb_name: # projection shows the domain entries (field) set. A field is a column.

SELECT [DISTINCT] * FROM tb_name WHERE qualification;# selects unique, non-repeating entries from the table that meet the criteria

FROM clause:

Tables, multiple tables, other SELECT statements

WHERE clause:

Boolean relation expression > =,

< 、= 表示大于等于，小于等于，大于，小于，等于。

The usage of SELECT is summarized:

2.2. Logical relationship:

AND (with & &) OR (or | |) NOT (not!) XOR (XOR)

Mysql > SELECT Name,Age,Gender FROM students WHERE NOT Age > 20 AND NOT Gender='M'

# Select data that are not older than 20 and whose gender is not male, you can also write the following conditions

Mysql > SELECT Name,Age,Gender FROM students WHERE NOT (Age > 20 OR Gender='M')

2.3. Special relationship: BETWEEN. AND... Somewhere in between.

2.4. LIKE''

%: any length, any character

_: any single character

Usage:

SELECT Name FROM students WHERE Name LIKE'Y% data; look for data that starts with Y in the Name field.

Fields that must include ing in the SELECT Name FROM students WHERE Name LIKE'% ing%'; lookup Name

SELECT Name FROM students WHERE Name LIKE'Y. look for Y in Name followed by at least 4 characters

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 'Yellowstone'

+-+

| | Name |

+-+

| | YiLin |

+-+

1 row in set (0.00 sec)

2.5. Support for regular expressions: REGEXP or RLIKE

Usage:# looks for data in the Name field where the line ends with g and begins with X or Y

Mysql > SELECT Name,Age FROM students WHERE Name RLIKE'. * Gmail'

+-+ +

| | 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. Search for discrete conditions: IN

Usage: find relevant data whose Age field is 18 or 20 or 25 years old

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. When there is a null value, compare: IS NULL, NOT NULL ORDER BY. With. Sort a field in ascending order

Usage: finding data with null Name fields

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. Sort the data to be found in ascending or descending order: ORDER BY field_name {ASC | DESC}

ASC ascending sort (default) DESC descending sort

SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name

# find the data in which the CID field is not empty and sort it in ascending order of field Name

SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name DESC

# find the data in which the CID field is not empty and sort it in descending order by field 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. Field alias: 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 alias is Student_Name |

+-+

| | Li Lianjie |

| | Cheng Long |

| | Yang Guo |

| | Guo Jing |

+-+

4 rows in set (0.00 sec)

2.10.LIMIT clause: how much is the offset of LIMIT [offset,] Count offset and how much does Count display?

Usage: SELECT Name AS Student_Name FROM student LIMIT 3

# search shows that the Name alias is Student_Name, and only the first three values are displayed

Usage: SELECT Name AS Student_Name FROM student LIMIT 2,2

# find and display Name alias as Student_Name, offset the first two data and do not display, display the two values after the second

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

+-+

| | Student_Name |

+-+

| | Yang Guo |

| | Guo Jing |

+-+

2 rows in set (0.00 sec)

2.11. Aggregate operation:

Summation of SUM (), minimum of MIN (), maximum of MAX ()

The average value of AVG (), the number of the same numeric values in the COUNT () statistical field

Create a new table:

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. Grouping: GROUP BY... HAVING qualification is based on. Grouping and satisfying the condition qualification

The conditional selection of GROUP BY needs to be filtered by HAVING instead of WHERE.

Mysql > SELECT Age,Gender FROM class1 GROUP BY Gender; # grouped 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; # asks for the average age of male and female students.

+-+ +

+-+ +

| | 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.girls'

# find the age flat value of the Gender group, and only display the average and name of the Name field that begins with Y.

+-+ +

| | Name | AVG (Age) |

+-+ +

| | Yang Guo | 34.0000 | |

+-+ +

1 row in set (0.00 sec)

2.13. Multi-table query (compound query): join:

2.13.1. Cross connect: (Cartesian product)

SELECT * FROM students,course; # query the contents of students and course tables

2.13.2. Natural connection:

Query options for students table and courses table CID1=CID, and display names, leaving only those with equivalent relationship

Mysql > select s.Name, c.Cname FROM students AS s 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. External connection:

Left outer join: left table LEFT JOIN right table ON condition

Right outer connection: left table RIGHT JOIN right table ON condition

Show all the students in the table on the left and check the names of the courses they take. There is no direct display of NULL for elective courses.

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)

Look at all the elective courses in the table on the right, and see which students have taken the elective. The NULL of the elective course is not directly displayed.

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. Self-join: join queries for multiple fields of a table itself

Look at the same fields of TID and SID in the students table and display their student and teacher names. AS aliases

Mysql > SELECT c.Name AS student,s.Name AS teacher FROM students AS c. C. 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. Subquery:

2.14.1. Other subqueries are nested in the query statement. Subqueries are used in the comparison operation, and the subqueries can only return a single value.

Query the names of students who are older than the average age

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 (): use subqueries

To query the name of a student whose age is the same as that of the teacher, IN indicates that the subquery can be a set of values.

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)

Subqueries can also be inserted in 2.14.3.FROM:

Query other eligible data from the table of the structure of the SELECT query

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 query: UNION: join two tables into one table query display

Combine the CID and Couse fields in the courses table with the Name and Age fields in the class table to display as one table

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. Example:

Mysql > desc courses

+-- +

+-- +

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

| | TID | smallint (6) | NO | | NULL |

+-- +

3 rows in set (0.01sec)

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 ('Flying Magazine 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 ('Flying Magazine 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. Select the course names of the courses in the courses table that are not studied by the CID2 in the students table

Find out the rows in which CID2 is not empty and do not repeat from the students table, and then find the CID values in the rows that CID is not in the rows you just looked up from the courses table

Mysql > SELECT Cname FROM courses WHERE CID NOT IN (SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL); # DISTINCT remove duplicates

+-+

| | Cname |

+-+

| | TaiJiquan |

| | Qianzhuwandushou |

| | Qishangquan |

| | Wanliduxing |

+-+

4 rows in set (0.01sec)

3.2. Find out the course name of the same course that CID2 has two or more students in the students table.

Look for at least 2 students in a course and display the course name.

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.01sec)

3.3. Show each course and its associated teachers, and courses that are not taught by teachers show their teachers as empty.

Msyql > SELECT t.TameCo.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. Show each teacher and the courses they teach, and the courses that are not taught remain null

Msyql > SELECT t.TameCo.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. Display the name of each student's CID1 course and the name of the teacher who taught the relevant course

Mysql > SELECT Name,Cname,Tname FROM students,courses,tutors WHERE students.CID1=courses.CID AND courses.TID=tutors.TID

+-+

+-+

+-+

8 rows in set (0.00 sec)

N0-end--78

The following is the jiaowu.sql text content:

-- 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), (2) (2), (3), (3), (3), (4), (5), (6), (7), (8), (9), (9), (10), (10)

/ *! 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 (1meme 2mine2djor67), (2pje 2pje 3jpj71), (3pje 1pje 2pas 90), (4mie 1pr 7pr 45), (5pr 3pr 6pr 32), (6pr 3pr 1pr 99), (7pr 4pr 85e 95), (8pje 4pl 10pr 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 ('Flying Magazine 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 10purl 0000'

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 (1pyrrhenium Guozhong pr é cor 19phe Menzhiro 2pyrrine 3pyrrh 3pyrus 2012-04-06 10purr 0000'), (2pyrrine Yangoyuzhuangliao 2pyrrine 2pyrus 0000'), (3pyrrine DingDianzhong pr 25pr paraphrase 6pr 7pr 2012-04-0610purr 0000'), (4pari HuFeiMong 31mrmlmlmpr 105g), (5) 'HuangRong',16,'F',5,9,9,'2012-04-06 10 HuangRong',16,'F',5,9,9,'2012-04-06 10)), (6 paraphernalia YueLingshangmu), (6 paraphernalia YueLingshangmu)), (6) (6pc YueLingshangjia), (6) (6pc YueLingshangpai), (6pc YueLingshangmu), (6pr YueLingshangzhong), (6pr YueLingshangzhong), (6pc YueLingshangzhong), (6pr YueLingshangzhong), (6pr YueLingshangmu), (6pc YueLingshangzhong pals), (6pc YueLingshangzhong pr é cs), (6pc YueLingshangzhong pr é s), (6pc YueLingshangzhong pr é cs), (6pc YueLingshangzhong par 18pi), (6pc YueLingshangmong pr é s 18pi). 'Masters, 11 null recorders, null recorders, 2012-04-06, 10-04-06, 10-14, 10, 12, 4, 4, 6, 10, 12, 4, 6, 12, 4, 6, 10, 12, 4, 6, 12, 4, 6, 10, 12, 4, 6, 12, 4, 6, 10, 12, 4, 6, 10, 12, 4, 6, 10, 12, 4, 6, 10, 12, 4, 6, 10, 10, 12, 4, 6, 10, 12, 4, 6, 10, 10, 12, 4, 6, 10, 10, 12, 4, 6, 10, 10, 12, 4, 6, 10, 12, 4, 6, 10, 12, 4, 6, 10, 12, 4, 6, 10, 12, 4, 6, 10,

/ *! 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 ('Flying Magazine 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), (2) (2), (3), (3), (3), (3), (4), (7), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (7), (6), (7), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (6), (7), (7), (6), (7), (7), (7), (7), (6), (7), (7), (7), ((2)), ((3)), ((3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (INSERT INTO `tutors`

/ *! 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.