基本查询练习--mysql学习笔记

数据准备

学生表student

学号
姓名
性别
出生日期
班级

CREATE database selecttest;
use selecttest;
CREATE TABLE student(
	sno VARCHAR(20) primary key,
	sname VARCHAR(20) not null,
	ssex VARCHAR(10) not null,
	sbirth DATE,
	class VARCHAR(20)
);

教师表teacher

教师编号
教师姓名
教师性别
出生日期
职称

CREATE TABLE teacher(
	tno VARCHAR(20) primary key,
	tname VARCHAR(20) not null,
	tsex VARCHAR(10) not null,
	tbirth DATETIME,
	prof VARCHAR(20),
	depart VARCHAR(20) not null
);

课程表course

课程号
课程名称
教师编号

CREATE TABLE course(
	cno VARCHAR(20) primary key,
	cname VARCHAR(20) not null,
	tno VARCHAR(20) not null,
	foreign key(tno) references teacher(tno)
);

成绩表score

学号
课程号
成绩

CREATE TABLE score(
	sno VARCHAR(20) not null,
	cno VARCHAR(20) not null,
	degree DECIMAL,
	primary key(sno,cno),
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno)
);

向学生表添加数据

INSERT INTO student values('1','李华','男','1999-1-1','1001');
INSERT INTO student values('2','张三','男','1999-2-1','1001');
INSERT INTO student values('3','赵四','男','1999-3-1','1002');
INSERT INTO student values('4','王五','男','1999-4-1','1002');
INSERT INTO student values('5','李六','男','1999-5-1','1003');
INSERT INTO student values('6','刘七','男','1999-6-1','1003');
INSERT INTO student values('7','胡八','男','1999-7-1','1004');
INSERT INTO student values('8','孙九','男','1999-8-1','1004');
INSERT INTO student values('9','马二','男','1999-9-1','1005');
INSERT INTO student values('0','杨一','男','1999-9-2','1005');

向教师表添加数据

INSERT INTO teacher values('101','张老师','男','1960-1-1','教授','建筑系');
INSERT INTO teacher values('102','王老师','男','1961-1-1','教授','数学系');
INSERT INTO teacher values('103','李老师','男','1962-1-1','教授','计算机系');
INSERT INTO teacher values('104','刘老师','女','1963-1-1','教授','化学系');

向课程表添加数据

insert into course values('1001','数据结构','103');
insert into course values('1002','建筑艺术','101');
insert into course values('1003','应用数学','102');
insert into course values('1004','有机化学','104');

向成绩表添加数据

insert into score values('0','1001',92);
insert into score values('1','1001',90);
insert into score values('2','1002',97);
insert into score values('0','1002',95);
insert into score values('4','1002',98);
insert into score values('5','1003',96);
insert into score values('6','1003',93);
insert into score values('7','1004',95);
insert into score values('4','1004',94);
insert into score values('9','1004',91);

1.查询student表的所有记录

select * from student;

2.查询student表中所有记录的sname、ssex、class列

select sname,ssex,class from student;

3.查询教师单位中不重复的depart列

select distinct depart from teacher;

4.查询score表中成绩在90到92之间的所有记录

select * from score where degree between 90 and 92;（闭区间）
select * from score where degree>90 and degree<92;（开区间）

5.查询score表中成绩为90，92或95的记录（或者关系）

select * from score where degree in(90,92,95);

6.查询student表中“1002”班或性别为男的同学记录

select * from student where class=‘1002’ or ssex=‘男’;

7.以class降序查询student表的所有记录

select * from student order by class desc;

8.以cno升序、degree降序查询score表中的所有记录

select * from score order by cno asc,degree desc;

9.查询“1003”班的学生人数

select count(*) from student where class=‘1003’;

10.查询score表中的最高分的学生学号和课程号

select sno,cno from score where degree=(select max(degree) from score);

查询有限条
select sno,cno,degree from score order bt degree desc limit 0,1;
limit后的第一个数字表示从多少开始
第二个数字表示查多少条