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。