创建表
学生表:(序号,姓名,年龄,专业)
create table s(
sno INT(11) auto_increment,
sname varchar(125),
sage INT(11),
sdept varchar(125),
primary key(sno)
);
课程表:(序号,课程名)
create table c(
cno INT(11) auto_increment,
cname varchar(255),
primary key(cno)
);
学生课程关系表:(序号,学生序号,课程序号,分数)
create table sc(
scno INT(11) auto_increment,
sno INT(11),
cno INT(11),
grade INT(255),
primary key(scno)
);
常用单表查询
limit
一个参数是查询条数
select sno,sname from s limit 2
两个参数n,m是(1.从第n+1行开始2.查询m条数)
select sno,sname from s limit 0,2
between
select sno,sname,sage from s where sage between 18 and 20
distinct
不重复查询
select distinct sdept from s
like
通配符查询
select sname from s where sname like "小%"
多表查询
in
1.查询修读“软件工程”的学生姓名(使用in嵌套子查询)
select sname from s where sno in
(select sno from sc where cno in (
select cno from c where cname="软件工程"
))
2.查询至少修读“软件工程”与“c语言”的学生姓名(在1中使用or)
select sname from s where sno in
(select sno from sc where cno in
(select cno from c where cname="软件工程" or cname="c语言"))
3.查询不修“软件工程”的学生姓名(在2中使用not)
select sname from s where sno in
(select sno from sc where cno not in
(select cno from c where cname="软件工程" ))