题目:有如下这样一个cinema表,写一个sql查询语言输出ID为奇数,且description列取值不是boring的项。
- 1、指定当前所用数据库(此处用xs)
USE xs
- 2、创建cinema表
CREATE TABLE cinema
(id INT(100) PRIMARY KEY,
movie CHAR(100),
description CHAR(100),
rating FLOAT)
- 3、插入数据
INSERT INTO cinema
VALUES(1,'War','great 3D','8.9') ,
(2,'Science','fiction',8.5),
(3,'irish','boring',6.2),
(4,'Ice song','Fantacy',8.6),
(5,'House card','Interesting',9.1)
至此,cinema表的创建完成,下面开始写sql查询,在这里利用mod()函数找出id为奇数的项:
- 4、写sql查询语句
SELECT * FROM cinema
WHERE MOD(id,2)=1 AND description!='boring'
ORDER BY rating DESC
还可以利用嵌套子查询来写:
SELECT * FROM cinema
WHERE MOD(id,2)=1 AND id NOT in
(SELECT id FROM cinema
WHERE description='boring')
ORDER BY rating DESC