SQL查询练习<1>

准备数据:

CREATE TABLE Student(
	Sno			INT(11) auto_increment PRIMARY KEY,
	Sname   	VARCHAR(20),
	Ssex		VARCHAR(20),
	Sage		INT(3),
	Sdept		VARCHAR(20));

CREATE TABLE Course(
	Cno			INT(11)	auto_increment PRIMARY KEY,
	Cname		VARCHAR(20),
	Cpno		INT(11) REFERENCES Course(Cno),
	Ccredit	INT(2));

CREATE TABLE SC(
	Sno		INT(11)	REFERENCES Student(Sno),
	Cno		INT(11)	REFERENCES Course(Cno),
	Grade	FLOAT(3),
	PRIMARY KEY(Sno,Cno));
	
INSERT INTO Student VALUES(95001,"李勇","男",20,"CS");
INSERT INTO Student VALUES(95003,"王名","男",20,"MA");
INSERT INTO Student VALUES(95002,"刘晨","男",19,"IS");
INSERT INTO Student VALUES(95004,"张立","男",18,"IS");
INSERT INTO Student VALUES(95006,"牛德华","女",20,"IS");
INSERT INTO Student VALUES(95005,"聂小轩","男",28,"CS");
INSERT INTO Student VALUES(95007,"张三三","男",18,"CS");
INSERT INTO Student VALUES(95008,"李思思","女",16,"MA");
INSERT INTO Student VALUES(95009,"张呜呜","男",19,"MA");

INSERT INTO Course VALUES(1,"数据库",5,4);
INSERT INTO Course VALUES(2,"数学",null,2);
INSERT INTO Course VALUES(3,"信息系统",1,4);
INSERT INTO Course VALUES(4,"操作系统",6,3);
INSERT INTO Course VALUES(5,"数据结构",7,4);
INSERT INTO Course VALUES(6,"数据处理",null,2);
INSERT INTO Course VALUES(7,"PASCAL语言",6,4);
INSERT INTO Course VALUES(8,"C_C++语言",null,4);

INSERT INTO SC VALUES(95001,1,60);
INSERT INTO SC VALUES(95002,1,90);
INSERT INTO SC VALUES(95001,2,80);
INSERT INTO SC VALUES(95002,2,99);
INSERT INTO SC VALUES(95002,3,45);
INSERT INTO SC VALUES(95001,4,40);
INSERT INTO SC VALUES(95002,4,88);
INSERT INTO SC VALUES(95004,4,null);
INSERT INTO SC VALUES(95005,4,45);
INSERT INTO SC VALUES(95002,5,88);
INSERT INTO SC VALUES(95003,5,89);
INSERT INTO SC VALUES(95005,5,54);
INSERT INTO SC VALUES(95001,6,82);
INSERT INTO SC VALUES(95004,6,null);
INSERT INTO SC VALUES(95005,6,99.5);
INSERT INTO SC VALUES(95003,7,99);
INSERT INTO SC VALUES(95004,7,45);
INSERT INTO SC VALUES(95005,7,75);

INSERT INTO SC VALUES(95006,1,60);
INSERT INTO SC VALUES(95006,4,90);

练习题

# 查询与“刘晨”同一个系学习的学生
SELECT sno,sname 
FROM student 
WHERE sdept = (SELECT sdept FROM student WHERE sname = "刘晨");


SELECT s1.sno,s1.sname 
FROM student s1 JOIN student s2 
    ON  s1.Sdept = s2.Sdept
WHERE s2.sname="刘晨";


SELECT s1.sno,s1.sname 
FROM student s1 ,student s2 
WHERE    
    s1.Sdept = s2.Sdept
    AND
    s2.sname="刘晨";

# 查询选修了课程名为‘信息系统’的学生学号和姓名

SELECT student.sno,sname 
FROM student, sc, course 
WHERE student.sno = sc.sno 
    AND course.cno = sc.cno
  AND course.cname = '信息系统';


SELECT sno,sname 
FROM student 
WHERE sno IN 
    (SELECT sno FROM sc WHERE cno IN 
            (SELECT cno FROM course WHERE cname='信息系统'));

# 查询其他系中比IS系任一学生年龄小的学生名单。
SELECT sname,sage 
FROM student 
WHERE Sdept <> 'IS' 
    AND Sage < ANY(SELECT sage FROM student WHERE Sdept = 'IS');

SELECT sname,sage 
FROM student 
WHERE Sdept <> 'IS' 
    AND Sage < (SELECT MAX(sage) FROM student WHERE Sdept = 'IS');

# 查询其他系中比IS系所有学生年龄都小的学生名单。
SELECT sname,sage
FROM student 
WHERE Sdept <> 'IS'
    AND Sage < ALL(SELECT sage FROM student WHERE Sdept = 'IS');


# 组函数比ANY ALL要高效。
SELECT sname,sage
FROM student 
WHERE Sdept <> 'IS'
    AND Sage < (Select MIN(Sage) FROM student WHERE Sdept = 'IS');


转载于:https://www.cnblogs.com/niewj/archive/2013/05/10/4041193.html

好的,下面是这些常用动态SQL标签的用法: 1. `<if>` 标签 `<if>` 标签用于在 SQL 语句中添加条件判断。例如: ```xml <select id="getUserList" resultType="User"> SELECT * FROM user <where> <if test="name != null"> AND name = #{name} </if> <if test="age != null"> AND age = #{age} </if> </where> </select> ``` 上面的 SQL 查询语句中,如果传入了 `name` 和 `age` 参数,则会根据这两个参数进行查询,否则不会加入对应的条件。 2. `<where>` 标签 `<where>` 标签用于动态生成 SQL 语句中的 WHERE 子句。例如: ```xml <select id="getUserList" resultType="User"> SELECT * FROM user <where> <if test="name != null"> AND name = #{name} </if> <if test="age != null"> AND age = #{age} </if> </where> </select> ``` 上面的 SQL 查询语句中,如果传入了 `name` 和 `age` 参数,则会根据这两个参数进行查询,否则不会加入 WHERE 子句。 3. `<set>` 标签 `<set>` 标签用于动态生成 SQL 语句中的 SET 子句,通常用于更新操作。例如: ```xml <update id="updateUser" parameterType="User"> UPDATE user <set> <if test="name != null"> name = #{name}, </if> <if test="age != null"> age = #{age}, </if> </set> WHERE id = #{id} </update> ``` 上面的 SQL 更新语句中,如果传入了 `name` 和 `age` 参数,则会根据这两个参数更新对应的字段,否则不会加入 SET 子句。 4. `<trim>` 标签 `<trim>` 标签用于动态生成 SQL 语句中的任何部分,可以用于去除生成 SQL 语句中的不必要的逗号或 AND/OR 等关键字。例如: ```xml <select id="getUserList" resultType="User"> SELECT * FROM user <where> <trim prefix="AND" prefixOverrides="OR"> <if test="name != null"> OR name = #{name} </if> <if test="age != null"> AND age = #{age} </if> </trim> </where> </select> ``` 上面的 SQL 查询语句中,`<trim>` 标签会将生成的 SQL 语句中的 `OR` 关键字前缀去除,同时去除不必要的逗号。 5. `<foreach>` 标签 `<foreach>` 标签用于动态生成 SQL 语句中的 IN 子句,通常用于查询或删除操作。例如: ```xml <select id="getUserList" resultType="User"> SELECT * FROM user WHERE id IN <foreach collection="ids" item="id" open="(" close=")" separator=","> #{id} </foreach> </select> ``` 上面的 SQL 查询语句中,`<foreach>` 标签会根据传入的 `ids` 参数生成对应的 IN 子句,例如 `WHERE id IN (1, 2, 3)`。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值