MySQL21-11-09

CREATE DATABASE IF NOT EXISTS Studxpm;
USE Studxpm;
ALTER DATABASE Studxpm DEFAULT CHARACTER SET utf8;

CREATE TABLE StudentInfo (
	StudentId VARCHAR(6) NOT NULL PRIMARY KEY,
	NAME VARCHAR(8) NOT NULL,
	sex VARCHAR(2) NOT NULL DEFAULT '男',
	Birthday DATE NOT NULL,
	Speciality VARCHAR(20),
	Address VARCHAR(20)
);

INSERT INTO StudentInfo VALUES
	('181001','成志强','男','19980817','计算机','北京市海定区'),
	('181002','孙红梅','女','19971123','计算机','成都市锦江区'),
	('181003','朱丽','女','19980219','计算机',NULL),
	('184001','王智勇','男','19971205','电子信息工程','北京市海定区'),
	('184002','周潞潞','女','19980224','电子信息工程','上海市浦东区'),
	('184004','郑永波','男','19990919','电子信息工程','上海市浦东区');
	
CREATE TABLE ScoreInfo(
	StudentId VARCHAR(6) NOT NULL,
	CourseId VARCHAR(4) NOT NULL,
	Grade INT NULL,
	 PRIMARY KEY(StudentId,CourseId)
);

INSERT INTO ScoreInfo VALUES
	('181001','1004','95'),
	('181002','1004','85'),
	('181003','1004','91'),
	('184001','4002','93'),
	('184002','4002','76'),
	('184004','4002','88'),
	('184001','8001','85'),
	('184002','8001',NULL),
	('184004','8001','94'),
	('181001','1201','92'),
	('181002','1201','78'),
	('181003','1201','94'),
	('181001','8001','94'),
	('181002','8001','89'),
	('181003','8001','86'),
	('184001','1201','85'),
	('184002','1201','79'),
	('184004','1201','94');
	
SELECT StudentId,NAME,sex,Birthday,Speciality,Address FROM StudentInfo;
SELECT * FROM StudentInfo;

SELECT * FROM ScoreInfo;

SELECT * FROM ScoreInfo WHERE Grade < 90;

SELECT * FROM StudentInfo WHERE Address IN ('上海市浦东区','成都市锦江区');
SELECT * FROM StudentInfo WHERE Address LIKE '上海市浦东区%' OR Address LIKE '成都市锦江区%';

SELECT * FROM ScoreInfo WHERE Grade BETWEEN 90 AND 95;
SELECT * FROM ScoreInfo WHERE Grade >= 90 AND Grade <= 95; 

select StudentId,Name,sex,Birthday,Speciality,Address
from studentinfo;

select * from studentinfo;

select * from scoreinfo;

select DISTINCT s.*
from studentinfo s,scoreinfo score,courseinfo cours
where s.studentId=score.studentId and cours.cId=score.CourseID and score.grade<90;

select s.*
from studentinfo s
where s.Address in('上海市浦东区','成都市锦江区');

select s.*
from studentinfo s
where s.Address='上海市浦东区' or s.Address='成都市锦江区';

select DISTINCT b.`Name`,a.*
from scoreinfo a,studentinfo b
where a.StudentId=b.StudentId and a.Grade BETWEEN 90 and 95;

select DISTINCT b.`Name`,a.*
from scoreinfo a,studentinfo b
where a.StudentId=b.StudentId and Grade>=90 and Grade<=95;

select *
FROM studentinfo
WHERE  Address LIKE '北京%';

select *
FROM studentinfo
WHERE  Address REGEXP '^北京';

select Speciality as '专业',count(StudentId) as '人数'
from studentinfo
group by(Speciality);

select b.cName '课程',avg(a.Grade) '平均分',max(a.Grade) '最高分',min(a.Grade) '最低分'
from scoreinfo a,courseinfo b
GROUP BY b.cId
HAVING b.cName='高等数学';

select b.cName as '课程',a.Grade as '成绩'
from scoreinfo a,courseinfo b
WHERE a.CourseID='1201' and b.cId=a.CourseID
order by a.Grade desc;

select b.cName as '课程',a.Grade as '成绩'
from scoreinfo a,courseinfo b
WHERE a.CourseID='1201' and b.cId=a.CourseID
order by a.Grade desc
LIMIT 3,5;

select b.cName as '课程',a.Grade as '成绩'
from scoreinfo a,courseinfo b
WHERE a.CourseID='1201' and b.cId=a.CourseID
order by a.Grade desc
LIMIT 5 OFFSET 3;

select *
from studentinfo,scoreinfo;

select studentinfo.`Name`, scoreinfo.CourseID
from studentinfo inner join scoreinfo on studentinfo.StudentId = scoreinfo.StudentId;

select studentinfo.`Name` as '姓名', scoreinfo.CourseID as '课程号'
from studentinfo,scoreinfo 
where studentinfo.StudentId = scoreinfo.StudentId;

select studentinfo.`Name` as '姓名', scoreinfo.CourseID as '课程号'
from studentinfo NATURAL join scoreinfo;

select DISTINCT s.`Name`,s.sex,o.Grade
from scoreinfo sc join scoreinfo o join studentinfo s on sc.Grade=o.Grade and sc.CourseID != o.Grade;

select DISTINCT s.`Name`,s.sex,o.Grade
from scoreinfo sc,scoreinfo o,studentinfo s
where sc.Grade=o.Grade and sc.CourseID != o.Grade;

select st.`Name`,st.sex,sc.Grade
from studentinfo st,scoreinfo sc
where sc.CourseID = '1004' and st.StudentId = sc.StudentId;

select st.`Name`,st.sex,sc.Grade
from studentinfo st,scoreinfo sc
where sc.CourseID = '8001' and st.StudentId = sc.StudentId and st.Speciality='计算机'
order by sc.Grade desc;

select st.`Name`,st.Speciality,sc.CourseID,sc.Grade
from studentinfo st,scoreinfo sc
where st.Address='上海市浦东区' and st.StudentId = sc.StudentId;

select DISTINCT st.*
from studentinfo st,scoreinfo sc
where st.StudentId in 
(	select StudentId
	from scoreinfo
	where CourseID='8001' and Grade > any (select Grade from scoreinfo where CourseID='4002')
);


CREATE OR REPLACE VIEW V_StudentInfoScoreInfo AS SELECT
a.StudentId,NAME,Sex,Birthday,Speciality,Address,GourseId,Grade
FROM StudentInfo a,ScoreInfo b WHERE a.StudentId=b.StudentId
WITH CHECK OPTION;

SELECT * FROM V_StudentInfoScoreInfo;

SELECT StudentId,NAME,Sex,Address FROM V_StudentInfoScoreInfo;

ALTER VIEW V_StudentInfoScoreInfo AS SELECT
a.StudentId,NAME,Sex,Birthday,Speciality,Address,GourseId,Grade
FROM StudentInfo a,ScoreInfo b 
WHERE a.StudentId=b.StudentId AND Speciality='计算机'
WITH CHECK OPTION;

DROP VIEW V_StudentInfoScoreInfo;