❶ 谁能用sql语句检索下 在testdb2数据库的choose2表中检索考试超过三门(包括3门)的男生个数。
select count(*)
from (select distinct 学号 from choose2 group by 学号 having count(课程)>=3)) t1
join 学生表 t2 on t1.学号=t2.学号
where t2.性别='男'
❷ plsql中all rows,rule,first rows,Choose有何区别及相应的功能是什么oracle中10g,11g
除了rule是关键字之外 ，其他三个 不能再PL/SQL中使用 ，没有什么功能！
❸ “sqlserver”三表联如何查询“sql”语句
假设学生表叫student,课程表叫class,选课表叫choose
1.三层嵌套的问题
select student.name from student where student.id IN
(select choose.sid from choose where choose.cid NOT IN
(select class.id from class where class.teacher='李明'))
2.一个内连接，一个嵌套
select student.name,avg(choose.score) from
student inner join choose on student.id=choose.sid
where student.id IN
(select choose.sid from choose
where choose.score<'60'
group by choose.sid
having count(choose.sid)>=2)
gruop by student.id
3.一个联合查询，一个嵌套查询
select student.name from student
where student.id IN
(select c1.sid from choose c1 where choose.cid='1'
union
select c2.sid from choose c2 where choose.cid='2'
on c1.sid=c2.sid
)
4.其实就是自连接查询和行列交换的问题：
select student.id,
(case choose.id when '1' then choose.score end) as 1号课成绩,
(case choose.id when '2' then choose.score end) as 2号课成绩,
from student inner join choose on student.id=choose.sid sc1,
student inner join choose on student.id=choose.sid sc2
where sc1.id='1'
and sc2.id='2'
and sc1.score>sc2.score
❹ 动态sql if 和choose有什么意思
1.精简mybaties工程如下
2.StudentMapper 新建两个查询接口，传入参数都是Map
3.StudentMapper.xml配置如下，
代码解读：入参Map如果包含stuName的话，就新增一段查询条件，如果包含age的话，就再增一段
解决多参数，不固定查询条件
4.测试查询方法
5.StudentMapper.xml配置方法2如下
代码解读：
当入参searchBy为gradeId 或者stuName的时候，对应的查询条件，否则还有默认按age查询
解决参数固定，条件不定的情况
6.测试方法如下
7.注：chooese也可以换成if条件来编写，看个人编码习惯即可
❺ 动态实现sql查询条件替换
<select id="queryEmp" resultType="cn.test.entity.Emp">
select * from emp where 1=1
<if test="deptNo!=null">
and deptno=#{deptNO}
</if>
<if test="deptName!=null">
and deptno=#{deptName}
</if>
</select>
注：<if test="deptNo!=null">中 的deptNo是指实体类中的属性或字段；
choose：
<select id="queryEmp" resultType="cn.test.entity.Emp">
select * from emp where 1=1
<choose>
<when test="deptNo!=null">
and deptno=#{deptNo}
</when>
<when test="deptName!=null">
and deptname=#{deptName}
</when>
<otherwise>
and personnum>#{personNum}
</otherwise>
</choose>
</select>
❻ SQL数据库高手来~~悬赏
--1.新建一个名为school的数据库。
create database school
go
use school
go
create table student--学生信息表(student)
(
stu_no varchar(50) primary key,
stu_name varchar(100) ,
stu_sex varchar(2),
stu_phone varchar(200),
stu_email varchar(500)
)
go
insert into student values('001','张三','男','111','[email protected]')
insert into student values('002','李四','男','222','[email protected]')
insert into student values('003','王五','女','333','[email protected]')
insert into student values('004','赵六','女','444','[email protected]')
insert into student values('005','钱七','男','555','[email protected]')
go
create table teacher
(
tea_no varchar(50) primary key,
tea_name varchar(100),
tea_sex varchar(2),
tea_phone varchar(200),
tea_email varchar(500)
)
go
insert into teacher values('001','冯老师','女','123','[email protected]')
insert into teacher values('002','刘老师','男','321','[email protected]')
go
create table course--课程表(course)
(
cor_id int primary key,
cor_name varchar(100),
tea_no varchar(50) foreign key references teacher(tea_no)
)
go
insert into course values(991,'数据库','001')
insert into course values(992,'操作系统','002')
go
create table choose--选课表(choose)
(
ch_id int identity primary key,
stu_no varchar(50) foreign key references student(stu_no),
cor_id int foreign key references course(cor_id)
)
go
insert into choose values('001',991)
insert into choose values('001',992)
insert into choose values('002',991)
insert into choose values('002',992)
insert into choose values('003',991)
insert into choose values('004',992)
go
--1.选出教师信息表(teacher)所有数据。（10分）
select * from teacher
--2.选出前三条的学生信息数据。（10分）
select top 3* from student order by stu_no
--3.选出张三同学选修的所有课程名称。（10分）
select cor_name as '张三同学选修的所有课程名称' from course where cor_id in (select cor_id from choose where stu_no=(select stu_no from student where stu_name = '张三'))
--4.选出没有选课的学生。（10分）
select * from student where stu_no not in(select stu_no from choose)
--5.选出每个学生及他们选课的门数。（10分）
select student.stu_no,student.stu_name,student.stu_sex,student.stu_phone,student.stu_email,isnull(coun,0) from student left join (select stu_no,count(*)as coun from choose group by stu_no) as nes on student.stu_no = nes.stu_no
select stu_no,count(*) from choose group by stu_no
--6.选出冯老师所教的所有学生。（10分）
select * from student where stu_no in(select stu_no from choose where cor_id in(select cor_id from course where tea_no=(select tea_no from teacher where tea_name = '冯老师')))
可以复制到数据库 运行
❼ mybatis中动态sql语句有哪些
mybatis的动态sql是基于ognl表达式的，它可以帮助我们方便的在sql语句中实现某些逻辑。
mybatis中用于实现动态sql的元素主要有：
if
choose（when，otherwise）
trim
where
set
foreach
http://www.open-open.com/lib/view/open1326100765125.html
详细可以参考这个，有图解教程，希望可以帮到你
❽ 三表联查的SQL语句
这问题交给我吧，假设学生表叫student,课程表叫class,选课表叫choose
1.三层嵌套的问题
select student.name from student where student.id IN
(select choose.sid from choose where choose.cid NOT IN
(select class.id from class where class.teacher='李明'))
2.一个内连接，一个嵌套
select student.name,avg(choose.score) from
student inner join choose on student.id=choose.sid
where student.id IN
(select choose.sid from choose
where choose.score<'60'
group by choose.sid
having count(choose.sid)>=2)
gruop by student.id
3.一个联合查询，一个嵌套查询
select student.name from student
where student.id IN
(select c1.sid from choose c1 where choose.cid='1'
union
select c2.sid from choose c2 where choose.cid='2'
on c1.sid=c2.sid
)
4.好吧，看起来很难，其实就是自连接查询和行列交换的问题
select student.id,
(case choose.id when '1' then choose.score end) as 1号课成绩,
(case choose.id when '2' then choose.score end) as 2号课成绩,
from student inner join choose on student.id=choose.sid sc1,
student inner join choose on student.id=choose.sid sc2
where sc1.id='1'
and sc2.id='2'
and sc1.score>sc2.score
5.至于你说的insert报错的问题，我想可能是因为学生ID和课程ID这两个外键有重复的值，
你可以检查下，实在不行删除外键，插入数据，在这里外键对你最后要的结果影响不大。
纯手工打造~有帮助记得给分哈
❾ sqlserver三表联查sql语句
假设学生表叫student,课程表叫class,选课表叫choose
1.三层嵌套的问题
select student.name from student where student.id IN
(select choose.sid from choose where choose.cid NOT IN
(select class.id from class where class.teacher='李明'))
2.一个内连接，一个嵌套
select student.name,avg(choose.score) from
student inner join choose on student.id=choose.sid
where student.id IN
(select choose.sid from choose
where choose.score<'60'
group by choose.sid
having count(choose.sid)>=2)
gruop by student.id
3.一个联合查询，一个嵌套查询
select student.name from student
where student.id IN
(select c1.sid from choose c1 where choose.cid='1'
union
select c2.sid from choose c2 where choose.cid='2'
on c1.sid=c2.sid
)
4.其实就是自连接查询和行列交换的问题：
select student.id,
(case choose.id when '1' then choose.score end) as 1号课成绩,
(case choose.id when '2' then choose.score end) as 2号课成绩,
from student inner join choose on student.id=choose.sid sc1,
student inner join choose on student.id=choose.sid sc2
where sc1.id='1'
and sc2.id='2'
and sc1.score>sc2.score