select A.* from A, (select * from B where 条件B) as T where 条件(A,B)
--以下语句都在三个数据表中完成:student(学生表),course(课程表),score(成绩表) --创建学生表 create table student ( studentNo char(30) primary key, studentName char(30), studentSex char(10), birthday datatime, polity char(20) ) --创建课程表 create table course ( courseNo char(30) primary key, courseName char(30) ) --创建成绩表 create table score ( studentNo char(30), courseNo char(30), score real, constraint pk1 primary key(studentNo,courseNo) ) --(1)查询学生表中所有的记录 select studentNo,studentName,studentSex, birthday,polity from student select * from student --(2)查询前三条学生的学号,姓名,年龄 select studentNo,studentName,birthday from student order by studentNo desc --(3)查询学生表所有性别,并去掉重复值 select distinct studentSex from student --(4)查询学生studentNo(并起别名学号) select studentNo as 学号,studentName as 姓名 from student select 学号=studentNo,姓名=studentName from student select studentNo 学号,studentName 姓名 from student --(5)查询每个学生的年龄 select studentName,2006-year(birthday) as 年龄 from student --(6)统计男同学的人数 select count(*) as 男生人数 from student where studentSex=\\\'男\\\' --(7)将查得的数据查入一个新表当中 select top 10 * into newTable from student select * from newTable --from子句 --(8) 查找学生表和成绩表中的数据 select s.studentNo,s.studentName,s.studentSex,s.birthday,s.polity,sc.courseNo,sc.score from student s,score sc where s.studentNo=sc.studentNo --(9)数据查询举例—联合查询 select distinct s.studentName,sc.score from student s,score,score sc where s.studentNo=sc.studentNo and sc.courseNo=\\\'101\\\' select s.studentName,sc.score from student s inner join score sc on s.studentNo=sc.studentNo and sc.courseNo=\\\'101\\\' --where子语 --(10)查找成绩表中不及格记录 select * from score where score<60 --(11)从学生表中查找是党员的男生 select * from student where studentSex=\\\'男\\\' and polity=\\\'党员\\\' --(12)查找语文分数在75到90分之间的学生姓名和成绩 select s.studentName,sc.score from student s,course c,score sc where (s.studentNo=sc.studentNo and c.courseNo=sc.courseNo and c.courseName=\\\'语文\\\') and sc.score between 75 and 90 order by sorce desc --(13)查找姓张的学生基本信息 select * from student where studentName like \\\'张%\\\' --(14)查找政治面貌不为空的学生记录 select * from student where polity is null
--any或者inselect * from A表 where id in(select aid from B表 where ......)select * from A表 where id = any(select aid from B表 where ......)--连接查询 select a.* from A表 a,B表 b where a.id=b.aid and ......B表的条件
假如:a.bid与b.aid相关联 select * from a where a.bid in ( select aid from b where (......) ) and .....
create table student
(
studentNo char(30) primary key,
studentName char(30),
studentSex char(10),
birthday datatime,
polity char(20)
) --创建课程表
create table course
(
courseNo char(30) primary key,
courseName char(30)
) --创建成绩表
create table score
(
studentNo char(30),
courseNo char(30),
score real,
constraint pk1 primary key(studentNo,courseNo)
)
--(1)查询学生表中所有的记录
select
studentNo,studentName,studentSex,
birthday,polity
from student
select * from student
--(2)查询前三条学生的学号,姓名,年龄
select studentNo,studentName,birthday
from student
order by studentNo desc
--(3)查询学生表所有性别,并去掉重复值
select distinct studentSex
from student
--(4)查询学生studentNo(并起别名学号)
select studentNo as 学号,studentName as 姓名
from student select 学号=studentNo,姓名=studentName
from student select studentNo 学号,studentName 姓名
from student --(5)查询每个学生的年龄
select studentName,2006-year(birthday) as 年龄
from student --(6)统计男同学的人数
select count(*) as 男生人数
from student
where studentSex=\\\'男\\\'
--(7)将查得的数据查入一个新表当中
select top 10 * into newTable
from student select * from newTable --from子句
--(8) 查找学生表和成绩表中的数据
select s.studentNo,s.studentName,s.studentSex,s.birthday,s.polity,sc.courseNo,sc.score
from student s,score sc
where s.studentNo=sc.studentNo
--(9)数据查询举例—联合查询 select distinct s.studentName,sc.score
from student s,score,score sc
where s.studentNo=sc.studentNo and sc.courseNo=\\\'101\\\' select s.studentName,sc.score
from student s inner join score sc
on s.studentNo=sc.studentNo and sc.courseNo=\\\'101\\\' --where子语 --(10)查找成绩表中不及格记录
select * from score
where score<60 --(11)从学生表中查找是党员的男生
select *
from student
where studentSex=\\\'男\\\' and polity=\\\'党员\\\'
--(12)查找语文分数在75到90分之间的学生姓名和成绩
select s.studentName,sc.score
from student s,course c,score sc
where (s.studentNo=sc.studentNo and c.courseNo=sc.courseNo and c.courseName=\\\'语文\\\') and sc.score between 75 and 90 order by sorce desc
--(13)查找姓张的学生基本信息
select * from student
where studentName like \\\'张%\\\' --(14)查找政治面貌不为空的学生记录
select * from student
where polity is null
where id in(select aid from B表 where ......)select * from A表
where id = any(select aid from B表 where ......)--连接查询
select a.*
from A表 a,B表 b
where a.id=b.aid and ......B表的条件
select * from a where a.bid in
(
select aid from b where (......)
)
and .....
mysql中可能只支持最后一种