mysql3108_mysql 常用命令实例

-- 完成项目素材中的sql练习，并将编写的sql文件和运行截图保存,打包后作为作业提交。

-- 设计一个学生成绩数据库，该库包含学生，老师，课程和成绩等信息并完成后面的练习(注意主外键关系)。

-- 学生：学号(SNO)、姓名(SNAME)、性别(SSEX)、生日(SBIRTHDAY )、所属班级(CLASS )

CREATE DATABASE IF NOT EXISTS sss;

USE sss;

CREATE TABLE IF NOT EXISTS student(

id TINYINT UNSIGNED AUTO_INCREMENT KEY ,

sno INT UNSIGNED NOT NULL ,

sname VARCHAR(20) NOT NULL ,

ssex ENUM('男','女','保密'),

sbirthday DATE ,

class INT UNSIGNED NOT NULL

)ENGINE = INNODB;

-- 课程：课程编号(CNO)、课程名(CNAME)、授课老师(TNO)

CREATE TABLE IF NOT EXISTS course(

id TINYINT UNSIGNED AUTO_INCREMENT KEY ,

cno INT UNSIGNED UNIQUE NOT NULL ,

cname VARCHAR(50) UNIQUE NOT NULL ,

tno VARCHAR(20) NOT NULL

)ENGINE = INNODB;

-- 成绩：学号(SNO)、课程编号(CNO)、得分(DEGREE)

CREATE TABLE IF NOT EXISTS score(

id TINYINT UNSIGNED AUTO_INCREMENT KEY ,

sno INT UNSIGNED UNIQUE NOT NULL ,

cno INT UNSIGNED NOT NULL ,

degree INT UNSIGNED

);

-- 老师：教师编号(TNO)、教师姓名(TNAME)、性别(TSSEX)、生日(TBIRTHDAY)、职称(TITLE)、单位科室(DEPART)

CREATE TABLE IF NOT EXISTS teacher(

id TINYINT UNSIGNED AUTO_INCREMENT KEY ,

tno INT UNSIGNED UNIQUE ,

tname VARCHAR(20) NOT NULL,

tssex ENUM('男','女','保密'),

tbirthday DATE,

title VARCHAR(20),

depart VARCHAR(40)

);

-- 要求：

-- 一、每张表使用sql语句插入至少10条数据

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (1,1011,'张三','男','1991-10-10',95301);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (2,1031,'李四','男','1992-11-11',95303);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (3,1021,'王二','女','1991-10-10',95302);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (4,1012,'李磊','男','1991-10-10',95301);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (5,1032,'韩梅梅','女','1991-10-10',95303);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (6,1022,'王明','女','1991-10-10',95302);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (7,1013,'白杨','男','1991-10-10',95301);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (8,1033,'熊八','男','1991-10-10',95303);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (9,1023,'杨一','女','1991-10-10',95302);

INSERT student(id,sno,sname,ssex,sbirthday,class) VALUES (10,1014,'胡五','女','1991-10-10',95301);

INSERT course(cno,cname,tno) VALUES (3105,'计算机导论','张旭');

INSERT course(cno,cname,tno) VALUES (3106,'语文','熊涛');

INSERT course(cno,cname,tno) VALUES (3107,'数学','廖凡');

INSERT course(cno,cname,tno) VALUES (3108,'英语','王勃');

INSERT course(cno,cname,tno) VALUES (1101,'高等数学','李晨');

INSERT course(cno,cname,tno) VALUES (1102,'通信原理','霍思燕');

INSERT course(cno,cname,tno) VALUES(1103,'大学物理','阿凡达');

INSERT course(cno,cname,tno) VALUES (2101,'微积分','爱因斯坦');

INSERT course(cno,cname,tno) VALUES (2102,'数据结构','乔布斯');

INSERT course(cno,cname,tno) VALUES (2103,'电路理论','爱迪生');

INSERT course(cno,cname,tno) VALUES (2104,'电磁感应','爱迪生');

INSERT score(sno,cno,degree) VALUES (1011,3105,85);

INSERT score(sno,cno,degree) VALUES (1031,3105,86);

INSERT score(sno,cno,degree) VALUES (1021,3105,88);

INSERT score(sno,cno,degree) VALUES (1012,2102,65);

INSERT score(sno,cno,degree) VALUES (1032,2102,70);

INSERT score(sno,cno,degree) VALUES (1022,2102,75);

INSERT score(sno,cno,degree) VALUES (1013,3105,90);

INSERT score(sno,cno,degree) VALUES (1033,3105,55);

INSERT score(sno,cno,degree) VALUES (1023,2104,45);

INSERT score(sno,cno,degree) VALUES (1014,2104,45);

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (01,'张旭','男','1991-10-09','教授','110');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (02,'熊涛','女','1991-10-09','教授','110');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (03,'廖凡','男','1991-10-09','教授','110');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (04,'王勃','女','1991-10-09','学士','111');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (05,'李晨','男','1991-10-09','学士','111');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (06,'霍思燕','女','1991-10-09','学士','111');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (07,'阿凡达','男','1991-10-09','学士','111');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (08,'爱因斯坦','女','1991-10-09','科学家','112');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (09,'乔布斯','男','1991-10-09','科学家','112');

INSERT teacher(tno,tname,tssex,tbirthday,title,depart) VALUES (010,'爱迪生','男','1991-10-09','科学家','112');

-- 二、完成以下查询题目：

-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。

SELECT sname,ssex,class FROM student;

-- 2、 查询教师所有的单位即不重复的Depart列。

SELECT DISTINCT depart FROM teacher;

-- 3、 查询Student表的所有记录。

SELECT * FROM student;

-- 4、 查询Score表中成绩在60到80之间的所有记录。

SELECT degree FROM score WHERE degree BETWEEN 60 AND 80;

-- 5、 查询Score表中成绩为85，86或88的记录。

SELECT * FROM score WHERE degree IN (85,86,88);

-- 6、 查询Student表中“95301”班或性别为“女”的同学记录。

SELECT * FROM student WHERE ssex='女' OR class="95301";

-- 7、 以Class降序查询Student表的所有记录。

SELECT * FROM student ORDER BY class desc;

-- 8、 以Cno升序、Degree降序查询Score表的所有记录。

SELECT * from score ORDER BY cno asc,degree desc;

-- 9、 查询“95031”班的学生人数。

SELECT COUNT(*) FROM student WHERE class="95301";

-- 10、查询Score表中的最高分的学生学号和课程号。

SELECT * FROM score WHERE degree=(SELECT MAX(degree) FROM score);

-- 11、查询‘3-105’号课程的平均分。

SELECT AVG(degree) FROM score WHERE cno=3105;

-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT AVG(degree) from score WHERE cno LIKE '3%'

HAVING COUNT(*) >=5;

-- 13、查询所有学生的Sname、Cno和Degree列。

SELECT s.sname,sc.cno,sc.degree

FROM student AS s

INNER JOIN score AS sc

ON s.sno=sc.sno;

-- 14、查询“95303”班所选课程的平均分。

SELECT AVG(sc.degree) FROM score AS sc

INNER JOIN student AS s

ON s.sno=sc.sno

WHERE s.class=95303;

-- 15、假设使用如下命令建立了一个grade表：

create table grade(low int,upp int,rank char(1));

insert into grade values(90,100,'A');

insert into grade values(80,89,'B');

insert into grade values(70,79,'C');

insert into grade values(60,69,'D');

insert into grade values(0,59,'E');

commit;

-- 现查询所有同学的Sno、Cno和rank列。

select sno,cno,rank from Score,grade where Degree between low and upp;

-- 16、查询"张旭"教师任课的学生成绩。

SELECT s.sname ,sc.degree

FROM student AS s

INNER JOIN score AS sc

ON s.sno=sc.sno

INNER JOIN course AS c

ON sc.cno=c.cno

WHERE c.tno='张旭';

-- 17、查询选修某课程的同学人数多于5人的教师姓名。

SELECT c.tno FROM course AS c

INNER JOIN score AS sc

ON c.cno=sc.cno

HAVING COUNT(*)>=5;

-- 18、查询所有教师和同学的name、sex和birthday.

SELECT tname,tssex,tbirthday FROM teacher UNION SELECT sname,ssex,sbirthday FROM student;

-- 20、查询至少有2名男生的班号。

SELECT class FROM student WHERE ssex='男'

HAVING COUNT(*)>=2;

-- 21、查询Student表中不姓“王”的同学记录。

SELECT * FROM student WHERE sname NOT LIKE '王%';

-- 22、查询所有选修“计算机导论”课程的“男”同学的成绩表。

SELECT s.sname,sc.degree,c.cname FROM score AS sc

INNER JOIN course AS c

ON sc.cno=c.cno

INNER JOIN student AS s

ON sc.sno=s.sno

WHERE c.cname='计算机导论'AND s.ssex='男';