mysql创建sc表_mysql数据库-用户、库、表创建

创建表

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;