create table student (
no INT(10),
name VARCHAR(20),
sex VARCHAR(20),
birthday datetime not null,class int
);
create table score (
no INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
cno VARCHAR(20),
degree INT(10));
Insert into score(no,cno,degree) values(5001,’3-105’,69);
Insert into score(no,cno,degree) values(5001,’5-102’,55);
Insert into score(no,cno,degree) values(503,’4-108’,85);
Insert into score(no,cno,degree) values(5004,’3-105’,77);
Insert into score(no,cno,degree) values(5005,’3-245’,100);
Insert into score(no,cno,degree) values(5006,’3-105’,53);
Insert into score(no,cno,degree) values(5003,’4-109’,45);
Insert into score(no,cno,degree) values(5008,’3-105’,98);
Insert into score(no,cno,degree) values(5004,’4-109’,68);
Insert into score(no,cno,degree) values(5010,’3-105’,88);
Insert into score(no,cno,degree) values(5003,’3-105’,98);
Insert into score(no,cno,degree) values(5005,’4-109’,68);
Insert into score(no,cno,degree) values(5002,’3-105’,88);
Insert into score(no,cno,degree) values(107,’3-105’,98);
Insert into score(no,cno,degree) values(108, ’4-109’,68);
Insert into score(no,cno,degree) values(109,’3-105’,88);
Insert into score(no,cno,degree) values(109,’4-109’,80);
Insert into score(no,cno,degree) values(107,’3-111’,88);
Insert into score(no,cno,degree) values(5003,’3-111’,80);
Insert into student(no,name,sex,birthday,class) values(5001,’李白’,’男’,’1987-7-22 00:00:00’,95001);
Insert into student(no,name,sex,birthday,class) values(5002,’夏洛特’,’女’,’1987-11-15 00:00:00’,95002);
Insert into student(no,name,sex,birthday,class) values(5003,’王昭君’,’女’,’1987-10-05 00:00:00’,95001);
Insert into student(no,name,sex,birthday,class) values(5004,’李信’,’男’,’1987-9-25 00:00:00’,95003);
Insert into student(no,name,sex,birthday,class) values(5005,’百里守约’,’男’,’1987-7-17 00:00:00’,95004);
Insert into student(no,name,sex,birthday,class) values(5006,’虞姬’,’女’,’1987-6-18 00:00:00’,95005);
Insert into student(no,name,sex,birthday,class) values(5007,’孙尚香’,’女’,’1987-8-29 00:00:00’,95006);
Insert into student(no,name,sex,birthday,class) values(5008,’马可波罗’,’男’,’1987-6-15 00:00:00’,95007);
Insert into student(no,name,sex,birthday,class) values(5009,’牛魔’,’女’,’1987-6-23 00:00:00’,95008);
Insert into student(no,name,sex,birthday,class) values(5010,’张飞’,’女’,’1987-6-24 00:00:00’,95002);
Insert into student(no,name,sex,birthday,class) values(108,’廉颇’,’男’,’1987-6-15 00:00:00’,95007);
Insert into student(no,name,sex,birthday,class) values(109,’韩信’,’男’,’1987-6-23 00:00:00’,95008);
Insert into student(no,name,sex,birthday,class) values(107,’云中君’,’男’,’1987-6-24 00:00:00’,95001);
#以class降序输出student的所有记录(student表全部属性)select*from student ORDER BY class DESC;#列出student表中所有记录的name、sex和class列select name,sex,classfrom student;#输出student中不姓王的同学的姓名。SELECT*from student WHERE name not like '王%';#输出成绩为85或86或88或在60-80之间的记录(no,cno,degree)select*from score where degree=85 or degree=86 or degree=88 or degree BETWEEN 60 and 80;#输出班级为95001或性别为‘女’ 的同学(student表全部属性)select*from student where class=95001 OR sex='女';#以cno升序、degree降序输出score的所有记录。(score表全部属性)select*from score order by cno,degree DESC;#输出男生人数及这些男生分布在多少个班级中 select COUNT(*),count(DISTINCT class)FROM student where sex='男';#列出存在有85分以上成绩的课程编号。select distinct cno from score where degree>85;#输出95001班级的学生人数select COUNT(*)from student where class=95001;#输出‘3-105’号课程的平均分select AVG(degree)from score where cno="3-105";#输出student中最大和最小的birthday日期值select MIN(birthday),MAX(birthday)FROM student;#显示95001和95004班全体学生的全部个人信息(不包括选课)。(student表全部属性)select*from student where class=95001 OR class=95004;
create table student ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , sex VARCHAR(4) , birth YEAR, department VARCHAR(20) , address VARCHAR(60) ); create table score ( id INT(10) N.