MySQL基本数据操作

文章目录

---

---

一、基本数据操作（crud)

1.建库建表

数据准备（同级目录下的db.txt测试库建库脚本.sql）

-- 测试库建库脚本
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student 
(
	sid INT NOT NULL AUTO_INCREMENT COMMENT '学号',
	sname VARCHAR(40) NOT NULL COMMENT '名称',
	birthday DATE NOT NULL COMMENT '年龄',
	ssex TINYINT NOT NULL DEFAULT 1 COMMENT '1男，2女',
	PRIMARY KEY (sid)
);

DROP TABLE IF EXISTS t_teacher;
CREATE TABLE t_teacher 
(
	tid INT NOT NULL AUTO_INCREMENT COMMENT '教师ID',
	tname VARCHAR(40) NOT NULL COMMENT '教师名称',
	PRIMARY KEY (tid)
);

DROP TABLE IF EXISTS t_course;
CREATE TABLE t_course 
(
	cid INT NOT NULL COMMENT '课程ID',
	cname VARCHAR(50) COMMENT '课程名称',
	tid INT COMMENT '教师id',
	PRIMARY KEY (cid)
);

ALTER TABLE t_course ADD CONSTRAINT fk_ref_cou_tea_tid FOREIGN KEY (tid) REFERENCES t_teacher (tid) ON DELETE RESTRICT ON UPDATE RESTRICT;

DROP TABLE IF EXISTS t_score;
CREATE TABLE t_score 
(
	sid INT NOT NULL COMMENT '学号，外键',
	cid INT NOT NULL COMMENT '课程id',
	score decimal(5,2) COMMENT '成绩',
	UNIQUE KEY ak_key_sid_cid (sid, cid)
);

ALTER TABLE t_score ADD CONSTRAINT fk_ref_sco_stu_sid FOREIGN KEY (sid) REFERENCES t_student (sid) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE t_score ADD CONSTRAINT fk_ref_sco_tea_cid FOREIGN KEY (cid) REFERENCES t_course (cid) ON DELETE RESTRICT ON UPDATE RESTRICT;

一、表结构要求：

-- 1.学生表-t_student
-- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别

-- 2.教师表-t_teacher
-- tid 教师编号,tname 教师名称

-- 3.课程表-t_course
-- cid 课程编号,cname 课程名称,tid 教师名称

-- 4.成绩表-t_score
-- sid 学生编号,cid 课程编号,score 成绩





二、表数据：

-- 学生表
INSERT INTO t_student VALUES(1, '赵雷' , '1990-01-01' , 1);
INSERT INTO t_student VALUES(2 , '钱电' , '1990-12-21' , 1);
INSERT INTO t_student VALUES(3 , '孙风' , '1990-12-20' , 1);
INSERT INTO t_student VALUES(4 , '李云' , '1990-12-06' , 1);
INSERT INTO t_student VALUES(5 , '周梅' , '1991-12-01' , 2);
INSERT INTO t_student VALUES(6 , '吴兰' , '1992-01-01' , 2);
INSERT INTO t_student VALUES(7 , '郑竹' , '1989-01-01' , 2);
INSERT INTO t_student VALUES(9 , '张三' , '2017-12-20' , 2);
INSERT INTO t_student VALUES(10 , '李四' , '2017-12-25' , 2);
INSERT INTO t_student VALUES(11 , '李四' , '2012-06-06' , 2);
INSERT INTO t_student VALUES(12 , '赵六' , '2013-06-13' , 2);
INSERT INTO t_student VALUES(13 , '孙七' , '2014-06-01' , 2);

-- 教师表
INSERT INTO t_teacher VALUES(1 , '张五哥');
INSERT INTO t_teacher VALUES(2 , '李卫');
INSERT INTO t_teacher VALUES(3 , '年羹尧');

-- 课程表
INSERT INTO t_course VALUES(1 , '语文' , 2);
INSERT INTO t_course VALUES(2 , '数学' , 1);
INSERT INTO t_course VALUES(3 , '英语' , 3);

-- 成绩表
INSERT INTO t_score VALUES(1 , 1 , 80);
INSERT INTO t_score VALUES(1 , 2 , 90);
INSERT INTO t_score VALUES(1 , 3 , 99);
INSERT INTO t_score VALUES(2 , 1 , 70);
INSERT INTO t_score VALUES(2 , 2 , 60);
INSERT INTO t_score VALUES(2 , 3 , 80);
INSERT INTO t_score VALUES(3 , 1 , 80);
INSERT INTO t_score VALUES(3 , 2 , 80);
INSERT INTO t_score VALUES(3 , 3 , 80);
INSERT INTO t_score VALUES(4 , 1 , 50);
INSERT INTO t_score VALUES(4 , 2 , 30);
INSERT INTO t_score VALUES(4 , 3 , 20);
INSERT INTO t_score VALUES(5 , 1 , 76);
INSERT INTO t_score VALUES(5 , 2 , 87);
INSERT INTO t_score VALUES(6 , 1 , 31);
INSERT INTO t_score VALUES(6 , 3 , 34);
INSERT INTO t_score VALUES(7 , 2 , 89);
INSERT INTO t_score VALUES(7 , 3 , 98);

2.查询" 1 "课程比" 2 "课程成绩高的学生的信息及课程分数

代码如下（示例）：

SELECT stu.sid,stu.sname,c1.score km1,c2.score km2  FROM t_student stu
INNER JOIN (
SELECT  t1.sid,t1.cid,t1.score FROM t_score t1 WHERE t1.cid=1）c1 ON stu.sid
INNER JOIN (
SELECT  t2.sid,t2.cid,t2.score FROM t_score t2 WHERE t2.cid=2）c2 ON stu.sid
WHERE c1.score> c2.score;

3.查询同时存在" 01 "课程和" 02 "课程的情况

代码如下（示例）：

SELECT stu.sid,stu.sname,c1.score km1,c2.score km2 FROM t_student stu
INNER JOIN (
SELECT  t1.sid,t1.cid,t1.score FROM t_score t1 WHERE t1.cid=1）c1 ON stu.sid
INNER JOIN (
SELECT  t2.sid,t2.cid,t2.score FROM t_score t2 WHERE t2.cid=2）c2 ON stu.sid

4.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )。

方法1

SELECT  stu.* FROM t_student stu INNER JOIN ( SELECT t.sid,
SUM(CASE WHEN t.cid=1 THEN 1 ELSE 0 END ) km1,
SUM(CASE WHEN t.cid=2 THEN 2 ELSE 0 END ) km2
 FROM t_score t GROUP BY t.sid ) tmp ON stu.sid =tmp.sid 
 WHERE tmp.km1>0 AND tmp.km2>0;

方法2

SELECT stu.sid ,stu.sname,c1.score km1,c2.score km2  FROM t_student  stu
LEFT JOIN (
SELECT t1.sid ,t1.cid,t1.score FROM t_score t1 WHERE t1.cid =1 ON stu.sid
LEFT JOIN (
SELECT t2.sid ,t2.cid,t2.score FROM t_score t2 WHERE t2.cid =2  ON stu.sid
WHERE c1.score >0  AND c2.score IS NULL ; 

方法3

SELECT stu.* FROM  t_student stu INNER JOIN (
SELECT t.sid ,
SUM (CASE WHEN t.cid =1 THEN 1 ELSE 0 END ) km1,
SUM (CASE WHEN t.cid=2 THEN  1 ELSE 0  END ) km2
FROM t_score t GROUP BY  t.sid ) tmp  ON  stu.sid =tmp.sid
WHERE tmp.km1>0 AND tmp.km2=0;

方法4

SELECT stu.* FROM t_student stu 
WHERE stu.sid IN   ( SELECT sid FROM t_score WHERE cid=1)
AND stu.sid NOT IN (SELECT sid FROM  t_score  WHERE cid=2)

5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT stu.* FROM t_student stu (
innner JOIN (
SELECT sid ,ROUND (AVG(score),1)pjcj
 FROM t_score GROUP BY sid   HAVING  pjcj>=60) tmp
  ON stu.sid =tmp.sid;

6.查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT sid ,
MAX(score) '最高分',
MIN(score)'最低分',
ROUND (AVG(score),1) '平均分',
COUNT (sid) '选修人数'，
ROUND (SUM (CASE WHEN score >=60 THEN 1 ELSE 0 END)/COUNT (sid),2) '及格率',
ROUND (SUM (CASE WHEN score >=70 AND score <80 THEN 1 ELSE 0 END)/COUNT (sid),2) '中等率',
ROUND (SUM (CASE WHEN score >=80 AND score <90 THEN 1 ELSE 0 END)/COUNT (sid),2)  '优良率',
ROUND (SUM (CASE WHEN score >=90 THEN 1 ELSE 0 END)/COUNT (sid),2)/COUNT (sid,2) '优秀率',
FROM t_score t1 INNER JOIN t_course  t2 ON t1.cid=t2.cid 
GROUP BY t2.cid, t2.cname;
ORDER BY  COUNT(sid) DESC ,t2.cid ASC;

---