数据库常用语句

CREATE DATABASE school2;/*创建学生库*/

/*创建学生表*/
CREATE TABLE student (
  studNo INT NOT NULL ,
  studName VARCHAR(12) NOT NULL,
  studSex VARCHAR(8) NOT NULL,
  studBirthday VARCHAR(10),
  studClass VARCHAR(10)
 
)
/*学生表添加主键*/
ALTER TABLE student ADD PRIMARY KEY(studNo);
/*创建课程表*/
CREATE TABLE course(
 courseNo INT NOT NULL PRIMARY KEY,
 courseName VARCHAR(10) NOT NULL ,
 tearchar INT NOT NULL
)
ALTER TABLE course CHANGE tearchar teacherNo INT;
/*创建成绩表*/
CREATE  TABLE score (
 studNo INT NOT NULL ,
 courseNo INT NOT NULL ,
 degree INT
)
/*创建教师信息表*/
CREATE TABLE teacher(
 teacNo INT NOT NULL PRIMARY KEY ,
 teacName VARCHAR(4) NOT NULL ,
 teacSex VARCHAR(2) NOT NULL,
 teacBirthday VARCHAR(10),
 teacProf VARCHAR(6),
 teacDepart VARCHAR(10) NOT NULL
)
/*添加外键*/
ALTER TABLE score ADD FOREIGN KEY(studNo)REFERENCES student(studNo);
ALTER TABLE score ADD FOREIGN KEY(courseNo)REFERENCES course(courseNo);
ALTER TABLE course ADD FOREIGN KEY(teacherNo)REFERENCES teacher(teacNO);
/*添加数据*/
INSERT INTO student (studNo,studName,studSex,studBirthday,studClass)VALUES (108,'曾华','男',1977-09-01,95033);
INSERT INTO student (studNo,studName,studSex,studBirthday,studClass)VALUES (105,'匡明','男',1975-10-02,95031);
INSERT INTO student (studNo,studName,studSex,studBirthday,studClass)VALUES (107,'王丽','女',1976-01-23,95033);
INSERT INTO student (studNo,studName,studSex,studBirthday,studClass)VALUES (101,'李军','男',1976-02-20,95033);
INSERT INTO student (studNo,studName,studSex,studBirthday,studClass)VALUES (109,'王芳','女',1975-02-10,95031);
INSERT INTO student (studNo,studName,studSex,studBirthday,studClass)VALUES (103,'陆君','男',1974-06-03,95031);

INSERT INTO teacher(teacNo,teacName,teacSex,teacBirthday,teacprof,teacDepart)VALUES(804,'李诚','男',1958-12-02,'副教授','计算机系');
INSERT INTO teacher(teacNo,teacName,teacSex,teacBirthday,teacprof,teacDepart)VALUES(856,'张旭','男',1969-03-12,'讲师','电子工程系');
INSERT INTO teacher(teacNo,teacName,teacSex,teacBirthday,teacprof,teacDepart)VALUES(825,'王萍','女',1972-05-05,'助教','计算机系');
INSERT INTO teacher(teacNo,teacName,teacSex,teacBirthday,teacprof,teacDepart)VALUES(831,'刘冰','女',1977-08-14,'助教','电子工程系');

/*1、 查询student表中的所有记录的studName、studSex和studClass列。*/
SELECT studName,studSex,studClass FROM student ;
/*3、 查询student表的所有记录。*/
SELECT * FROM student;
/*4、 查询score表中成绩在60到80之间的所有记录。*/
SELECT *FROM score WHERE degree>60 OR degree<80 ;
/*5、 查询score表中成绩为85，86或88的记录。*/
SELECT *FROM score WHERE degree=85 OR degree=86 OR degree=88;
/*6、 查询student表中“95031”班或性别为“女”的同学记录。*/
SELECT *FROM  student WHERE studClass=95031 OR studSex='女';
/*7、 以class降序查询Student表的所有记录。*/
SELECT * FROM student ORDER BY studClass DESC;
/*8、 以couseNo升序、degree降序查询score表的所有记录。*/
SELECT * FROM score ORDER BY courseNo ,degree DESC;
/*9、 查询“95031”班的学生人数。*/
SELECT COUNT(*)FROM student WHERE studClass=95031;
/*10、查询score表中的最高分的学生学号和课程号。（子查询或者排序）*/
SELECT studNo ,courseNo FROM score WHERE degree=(SELECT MAX(degree) FROM score);
/*11、查询‘3-105’号课程的平均分。*/
SELECT AVG(degree)FROM score WHERE courseNo=105;
/*12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。*/
SELECT AVG (degree) FROM score WHERE courseNo LIKE '3%' GROUP BY courseNo HAVING COUNT(*)>5;
/*13、查询最低分大于70，最高分小于90的studNo列。*/
SELECT studNo FROM score GROUP BY studNo  HAVING MIN(degree)>70 AND MAX(degree)<90;
/*14、查询所有学生的studName、courseNo和degree列。*/
SELECT a.studName ,b.courseNo,b.degree FROM student a,score b WHERE a.studNo=b.studNo;
/*15、查询所有学生的studNo、courseName和degree列。*/
SELECT a.studNo,b.courseName,a.degree FROM score a ,course b WHERE a.courseNo=b.courseNo;
/*16、查询所有学生的studName、courseName和degree列。*/
SELECT a.studName ,c.courseName, b.degree FROM student a, score b,course c WHERE a.studNo=b.studNo AND b.courseNo=c.courseNo;
/*17、查询“95033”班所选课程的平均分。*/
SELECT AVG(degree) FROM student a,score b WHERE a.studNo=b.studNo AND a.studClass=95503 ;
/*18、 假设使用如下命令建立了一个grade表：
 create table grade(low  int(3),upp  int(3),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’)
 现查询所有同学的Sno、Cno和rank列。
*/

/*19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。*/
SELECT * FROM score WHERE courseNo=3105 AND degree>(SELECT degree FROM score WHERE studNo=109 AND courseNo=3105);
/*20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。*/
/*21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。*/
/*22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。*/

/*23、查询“张旭“教师任课的学生成绩。*/
SELECT degree FROM teacher a,course b,score c WHERE a.teacNo=b.teacherNo AND b.courseNo=c.courseNo AND teacName='张旭' ;
/*24、查询选修某课程的同学人数多于5人的教师姓名。*/
SELECT
/*25、查询95033班和95031班全体学生的记录。*/

/*26、查询存在有85分以上成绩的课程Cno.*/
SELECT courseName FROM course a,score b WHERE a.courseNo=b.courseNo AND degree>85;
/*27、查询出“计算机系“教师所教课程的成绩表。*/
SELECT teacDepart,degree FROM teacher,score WHERE
SELECT studNo,courseNo,degree WHERE degree =(SELECT MAX(degree) FROM score);

转载于:https://my.oschina.net/u/2353689/blog/480144