案例技巧---mysql连表查询

mysql> drop table student;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table student(

    -> Sno int(10) NOT NULL COMMENT '学号',

    -> Sname varchar(16) NOT NULL COMMENT '姓名',

    -> Ssex char(2) NOT NULL COMMENT '性别',

    -> Sage tinyint(2)  NOT NULL default '0' COMMENT '学生年龄',

    -> Sdept varchar(16)  default NULL  COMMENT '学生所在系别', 

    -> PRIMARY KEY  (Sno) ,

    -> key index_Sname (Sname)

    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.02 sec)

 

mysql> 

mysql> create table course(

    -> Cno int(10) NOT NULL COMMENT '课程号',

    -> Cname varchar(64) NOT NULL COMMENT '课程名',

    -> Ccredit tinyint(2) NOT NULL COMMENT '学分',

    -> PRIMARY KEY  (Cno) 

    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.00 sec)

 

mysql> 

mysql> 

mysql> CREATE TABLE `SC` (

    ->    SCid int(12) NOT NULL auto_increment COMMENT '主键',

    ->   `Cno` int(10) NOT NULL COMMENT '课程号',

    ->   `Sno` int(10) NOT NULL COMMENT '学号',

    ->   `Grade` tinyint(2) NOT NULL COMMENT '学生成绩',

    ->   PRIMARY KEY  (`SCid`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.01 sec)

 

mysql> 

mysql> show tables;

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

| Tables_in_oldboy |

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

| course           |

| sc               |

| student          |

| test             |

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

4 rows in set (0.00 sec)

 

mysql> INSERT INTO student values(0001,'宏志','男',30,'计算机网络');

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO student values(0002,'王硕 ','男',30,'computer application');

Query OK, 1 row affected, 1 warning (0.00 sec)

 

mysql> INSERT INTO student values(0003,'oldboy','男',28,'物流管理');

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO student values(0004,'脉动','男',29,'computer application');

Query OK, 1 row affected, 1 warning (0.00 sec)

 

mysql> INSERT INTO student values(0005,'oldgirl','女',26,'计算机科学与技术');

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO student values(0006,'莹莹','女',22,'护士');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from student;

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

| Sno | Sname   | Ssex | Sage | Sdept                    |

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

|   1 | 宏志    | 男   |   30 | 计算机网络               |

|   2 | 王硕    | 男   |   30 | computer applica         |

|   3 | oldboy  | 男   |   28 | 物流管理                 |

|   4 | 脉动    | 男   |   29 | computer applica         |

|   5 | oldgirl | 女   |   26 | 计算机科学与技术         |

|   6 | 莹莹    | 女   |   22 | 护士                     |

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

6 rows in set (0.00 sec)

 

mysql> INSERT INTO course values(1001,'Linux中高级运维',3);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO course values(1002,'Linux高级架构师',5);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO course values(1003,'MySQL高级Dba',4);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO course values(1004,'Python运维开发',4);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO course values(1005,'Java web开发',3);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from course;                               

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

| Cno  | Cname                | Ccredit |

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

| 1001 | Linux中高级运维      |       3 |

| 1002 | Linux高级架构师      |       5 |

| 1003 | MySQL高级Dba         |       4 |

| 1004 | Python运维开发       |       4 |

| 1005 | Java web开发         |       3 |

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

5 rows in set (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0001,1001,4);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade) values(0001,1002,3);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0001,1003,1);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0001,1004,6);

Query OK, 1 row affected (0.00 sec)

 

mysql> ---

    -> INSERT INTO SC(Sno,Cno,Grade)  values(0002,1001,3);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-

INSERT INTO SC(Sno,Cno,Grade)  values(0002,1001,3)' at line 1

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0002,1002,2);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0002,1003,2);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0002,1004,8);

Query OK, 1 row affected (0.00 sec)

 

mysql> 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0003,1001,4);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0003,1002,4);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0003,1003,2);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0003,1004,8);

Query OK, 1 row affected (0.00 sec)

 

mysql> 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0004,1001,1);

Query OK, 1 row affected (0.01 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0004,1002,1);

Query OK, 1 row affected (0.01 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0004,1003,2);

Query OK, 1 row affected (0.01 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0004,1004,3);

Query OK, 1 row affected (0.00 sec)

 

mysql> 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0005,1001,5);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0005,1002,3);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0005,1003,2);

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0005,1004,9);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from sc;

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

| SCid | Cno  | Sno | Grade |

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

|    1 | 1001 |   1 |     4 |

|    2 | 1002 |   1 |     3 |

|    3 | 1003 |   1 |     1 |

|    4 | 1004 |   1 |     6 |

|    5 | 1002 |   2 |     2 |

|    6 | 1003 |   2 |     2 |

|    7 | 1004 |   2 |     8 |

|    8 | 1001 |   3 |     4 |

|    9 | 1002 |   3 |     4 |

|   10 | 1003 |   3 |     2 |

|   11 | 1004 |   3 |     8 |

|   12 | 1001 |   4 |     1 |

|   13 | 1002 |   4 |     1 |

|   14 | 1003 |   4 |     2 |

|   15 | 1004 |   4 |     3 |

|   16 | 1001 |   5 |     5 |

|   17 | 1002 |   5 |     3 |

|   18 | 1003 |   5 |     2 |

|   19 | 1004 |   5 |     9 |

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

19 rows in set (0.00 sec)

 

mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno;

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

| Sno | Sname   | Cname                | Grade |

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

|   1 | 宏志    | Linux中高级运维      |     4 |

|   3 | oldboy  | Linux中高级运维      |     4 |

|   4 | 脉动    | Linux中高级运维      |     1 |

|   5 | oldgirl | Linux中高级运维      |     5 |

|   1 | 宏志    | Linux高级架构师      |     3 |

|   2 | 王硕    | Linux高级架构师      |     2 |

|   3 | oldboy  | Linux高级架构师      |     4 |

|   4 | 脉动    | Linux高级架构师      |     1 |

|   5 | oldgirl | Linux高级架构师      |     3 |

|   1 | 宏志    | MySQL高级Dba         |     1 |

|   2 | 王硕    | MySQL高级Dba         |     2 |

|   3 | oldboy  | MySQL高级Dba         |     2 |

|   4 | 脉动    | MySQL高级Dba         |     2 |

|   5 | oldgirl | MySQL高级Dba         |     2 |

|   1 | 宏志    | Python运维开发       |     6 |

|   2 | 王硕    | Python运维开发       |     8 |

|   3 | oldboy  | Python运维开发       |     8 |

|   4 | 脉动    | Python运维开发       |     3 |

|   5 | oldgirl | Python运维开发       |     9 |

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

19 rows in set (0.00 sec)

 

参考信息：

 其他查询：子查询，join,union,多表关联查询，分组，having。