创建表
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)
-> );
Query OK, 0 rows affected (0.01 sec)
查看表结构
mysql> desc student;
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)
-> );
Query OK, 0 rows affected (0.01 sec)
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));
Query OK, 0 rows affected (0.00 sec)
插入测试数据
INSERT into student VALUES (001,'张三','女',24,'网络技术');
INSERT into student VALUES (002,'李四','男',20,'软件技术');
INSERT into student VALUES (003,'王五','女',24,'JAVA编程');
INSERT into student VALUES (004,'马六','男',21,'网络安全');
INSERT into sc VALUES (9006,1001,1,9);
INSERT into sc VALUES (9002,1002,1,3);
INSERT into sc VALUES (9003,1003,1,4);
INSERT into sc VALUES (9004,3001,1,2);
INSERT into sc VALUES (9005,3002,1,3);
INSERT into sc VALUES (9006,3003,1,4);
进行多表查询测试
SELECT student.Sname,course.Cname,sc.Grade FROM student,course,sc WHERE student.Sno=sc.Sno AND course.Cno=sc.Cno;
转播
