38数据库

 

# 第一套
CREATE TABLE celebrity (
   sid INT(11) AUTO_INCREMENT,
   sname VARCHAR(255),
   sage INT(11),
   ssex VARCHAR(5),
  PRIMARY KEY (sid)
)

INSERT  INTO celebrity VALUES 
(1,'李白',34,'男'),
(2,'杜甫',24,'男'),
(3,'白居易',31,'男'),
(4,'李商隐',40,'女'),
(5,'苏轼',26,'男'),
(6,'辛弃疾',22,'男');

CREATE TABLE summary (
  sid INT(11),
  wid INT(11),
  sassess VARCHAR(50)
)

INSERT  INTO summary VALUES 
(1,1,'天生我材必有用'),
(1,4,'举头望明月'),
(6,8,'明月几时有'),
(3,6,'乱花渐欲迷人眼'),
(4,3,'却话巴山夜雨时'),
(5,7,'大江东去'),
(2,5,'一览众山小'),
(1,4,'举头望明月');

CREATE TABLE works (
  wid INT(11) AUTO_INCREMENT,
  wwork VARCHAR(60),
  wdynasty VARCHAR(50),
  PRIMARY KEY (wid)
)

INSERT  INTO works VALUES 
(1,'将进酒','唐代'),
(2,'蜀道难','唐代'),
(3,'夜雨寄北','唐代'),
(4,'静夜思','唐代'),
(5,'望岳','唐代'),
(6,'钱塘江春行','南宋'),
(7,'念奴娇赤壁怀古','北宋'),
(8,'水调歌头','唐代');

SELECT sname FROM celebrity WHERE ssex ='男';
SELECT c.sname,w.wwork FROM celebrity c JOIN summary s ON c.sid=s.sid JOIN works w ON s.wid=w.wid WHERE wwork='静夜思';
SELECT sname,sage FROM celebrity WHERE sname IN ('李白','杜甫');
SELECT COUNT(ssex),ssex FROM celebrity GROUP BY ssex;
SELECT sname,s.sassess,w.wwork FROM celebrity c JOIN summary s ON c.sid=s.sid JOIN works w  ON s.wid=w.wid WHERE c.sname='白居易';
SELECT COUNT(sage) FROM celebrity  WHERE sage IN (SELECT sage FROM celebrity WHERE sage BETWEEN 25 AND 30);
SELECT * FROM celebrity ORDER BY sage LIMIT 0,2;
SELECT wwork,sassess,sage FROM celebrity c JOIN summary s ON c.sid=s.sid JOIN works w ON s.wid=w.wid WHERE sname='李白';
UPDATE works SET wdynasty='北宋' WHERE wwork='望岳';
SELECT * FROM works;
INSERT INTO celebrity VALUE(7,'王维',25,'男');
SELECT * FROM celebrity;

 

# 第二题
CREATE TABLE course (
  cno INT(11) AUTO_INCREMENT,
  cname VARCHAR(255),
  cteacher VARCHAR(255),
  PRIMARY KEY (cno)
)

INSERT  INTO course VALUES 
(1,'java','何昊'),
(2,'php','李美军'),
(3,'android','王超');

CREATE TABLE student (
  sno INT(11) AUTO_INCREMENT,
  sname VARCHAR(50),
  age INT(11),
  sex VARCHAR(5),
  PRIMARY KEY (sno)
)

INSERT  INTO student VALUES 
(1,'张三',18,'男'),
(2,'李四',20,'女'),
(3,'王五',23,'男'),
(4,'赵六',22,'女');

CREATE TABLE studentcourse (
  sno INT(11),
  cno INT(11),
  scgrade VARCHAR(255)
)

INSERT  INTO studentcourse VALUES
(1,1,'50'),
(2,1,'66'),
(3,1,'90'),
(1,1,'50'),
(2,1,'66'),
(3,1,'90'),
(4,3,'55'),
(1,1,'50'),
(2,1,'66'),
(3,1,'90'),
(4,3,'55');

SELECT * FROM studentcourse sc JOIN course c  ON  sc.cno=c.cno JOIN student s ON  sc.sno=s.sno WHERE c.cteacher='何昊' AND sex='女' GROUP BY s.sname;
SELECT s.sname FROM studentcourse sc JOIN course c  ON  sc.cno=c.cno JOIN student s ON  sc.sno=s.sno WHERE  c.cteacher!='何昊' GROUP BY s.sname;
SELECT s.sname FROM studentcourse sc JOIN course c ON  sc.cno=c.cno JOIN student s ON sc.sno=s.sno WHERE scgrade<60 GROUP BY s.sname;

# 第三题
USE day03;
CREATE TABLE student (
  id INT(11) AUTO_INCREMENT,
  NAME VARCHAR(255),
  score VARCHAR(255),
  PRIMARY KEY (id)
)

 

INSERT  INTO student VALUES 
(1,'张三','80'),
(2,'李四','56'),
(3,'王五','72'),
(4,'赵六','30'),
(5,'孙七','66');

CREATE TABLE teacher (
  id INT(11) AUTO_INCREMENT,
  NAME VARCHAR(255),
  calss VARCHAR(255),
  classroom VARCHAR(255),
  student_id INT(11),
  PRIMARY KEY (id)
)

INSERT  INTO teacher VALUES 
(1,'刘良誉','测试','2102A',1),
(2,'刘良誉','测试','2011A',2),
(3,'樊庆晓','android','2012A',3),
(4,'孙文龙','PHP','2101A',4),
(5,'徐腾升','JAVA','2013B',5);

SELECT s.name,calss FROM teacher t JOIN student s ON s.id=t.student_id WHERE t.name='刘良誉';
SELECT s.name FROM teacher t JOIN student s ON s.id=t.student_id WHERE score>60;

SELECT t.name,calss FROM teacher t JOIN student s ON s.id=t.student_id WHERE s.name='赵六';