MYSQL数据库面试题以及答案（50+例题）

一，数据表说明

1. 学生表student(sid,sname,age,sex)参数说明：

sid：学生编号#，sname：学生姓名，age：学生年龄，sex：学生性别

2. 课程表course(cid,cname,tid)参数说明：

cid：课程编号#，cname：课程名字，tid：教师师编号#

3. 教师表teacher(tid,tname)参数说明：

tid：教师编号#，tname：教师姓名

4. 成绩表grade(sid,cid,score)参数说明：

sid：学生编号#，cid：课程编号#，score：课程成绩

5. 总览：
/*student(学号#,姓名,性别,年龄)
course(课程号#，课程名，教师号#)
teacher(教师号#，教师名)
score(学号#，课程号#，成绩) */

二，mysql建库，建表语句

• 1，建库school

CREATE DATABASE school  CHARSET utf8;

• 2，#学生表

CREATE TABLE student
(
sid CHAR(20) NOT NULL, 
sname VARCHAR(20) NOT NULL DEFAULT '',
age INT NOT NULL CHECK(age>12 AND age<120) ,
sex CHAR(2) NOT NULL DEFAULT '女'  CHECK( sex IN (‘男’,’女’)),
PRIMARY KEY(sid)
)CHARACTER SET utf8;

• 3，#课程表

CREATE TABLE course
(
cid VARCHAR(20) NOT NULL,
cname VARCHAR(20) NOT NULL,
tid VARCHAR(20) NOT NULL,
PRIMARY KEY(cid, tid)
)CHARACTER SET utf8;

• 4，#教师表

CREATE TABLE teacher(
    tid VARCHAR(20) NOT NULL,
    tname VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(tid)
)CHARACTER SET utf8;

-5，#成绩表

CREATE TABLE score
(
sid CHAR(20) NOT NULL ,
cid CHAR(20) NOT NULL,
score INT NOT NULL,
PRIMARY KEY (sid, cid)
)CHARACTER SET utf8;

三，插入表数据

• 1，#插入学生表测试数据

INSERT INTO student VALUES('001' , '亚瑟' , '24' , '男');
INSERT INTO student VALUES('002' , '后弈' , '22' , '男');
INSERT INTO student VALUES('003' , '张飞' , '17' , '男');
INSERT INTO student VALUES('004' , '李白' , '23' , '男');
INSERT INTO student VALUES('005' , '梦奇' , '18' , '女');
INSERT INTO student VALUES('006' , '虞姬' , '19' , '女');
INSERT INTO student VALUES('007' , '露娜' , '22' , '女');
INSERT INTO student VALUES('008' , '貂蝉' , '16' , '女');
INSERT INTO student VALUES('009' , '韩信' , '26' , '男');
INSERT INTO student VALUES('010' , '张良' , '31' , '男');
INSERT INTO student VALUES('011' , '妲己' , '18' , '女');
INSERT INTO student VALUES('012' , '小乔' , '22' , '女');
INSERT INTO student VALUES('013' , '关羽' , '21' , '男');
INSERT INTO student VALUES('014' , '小乔' , '19' , '女');
INSERT INTO student VALUES('015' , '王昭君' , '23' , '女');
INSERT INTO student VALUES('016' , '甄姬' , '15' , '女');

• 2，#课程表测试数据

INSERT INTO course VALUES('01' , '语文' , '0001');
INSERT INTO course VALUES('02' , '数学' , '0002');
INSERT INTO course VALUES('03' , '英语' , '0003');
INSERT INTO course VALUES('04' , '物理' , '0004');

• 3，#教师表测试数据

INSERT INTO teacher VALUES('0001' , '老夫子');
INSERT INTO teacher VALUES('0002' , '墨子');
INSERT INTO teacher VALUES('0003' , '诸葛亮');
INSERT INTO teacher VALUES('0004' , '庄周');
INSERT INTO teacher VALUES('0005' , '姜子牙');

• 4，#成绩表测试数据

INSERT INTO score VALUES('001' , '01' , 80);
INSERT INTO score VALUES('001' , '02' , 90);
INSERT INTO score VALUES('001' , '03' , 99);
INSERT INTO score VALUES('001' , '04' , 92);
INSERT INTO score VALUES('002' , '01' , 70);
INSERT INTO score VALUES('002' , '02' , 59);
INSERT INTO score VALUES('002' , '03' , 80);
INSERT INTO score VALUES('002' , '04' , 95);
INSERT INTO score VALUES('003' , '01' , 59);
INSERT INTO score VALUES('003' , '02' , 57);
INSERT INTO score VALUES('003' , '03' , 81);
INSERT INTO score VALUES('003' , '04' , 79);
INSERT INTO score VALUES('004' , '01' , 50);
INSERT INTO score VALUES('004' , '02' , 30);
INSERT INTO score VALUES('004' , '03' , 45);
INSERT INTO score VALUES('004' , '04' , 62);
INSERT INTO score VALUES('005' , '01' , 76);
INSERT INTO score VALUES('005' , '02' , 87);
INSERT INTO score VALUES('005' , '03' , 69);
INSERT INTO score VALUES('005' , '04' , 59);
INSERT INTO score VALUES('006' , '01' , 31);
INSERT INTO score VALUES('006' , '02' , 24);
INSERT INTO score VALUES('006' , '03' , 69);
INSERT INTO score VALUES('006' , '04' , 49);
INSERT INTO score VALUES('007' , '01' , 59);
INSERT INTO score VALUES('007' , '02' , 69);
INSERT INTO score VALUES('007' , '03' , 79);
INSERT INTO score VALUES('007' , '04' , 85);
INSERT INTO score VALUES('008' , '01' , 52);
INSERT INTO score VALUES(&#