039 - sql逻辑操作符

倔强的小白菜

已于 2023-08-31 18:28:49 修改

阅读量1.3k

点赞数 1

分类专栏：数据库文章标签：数据库

于 2023-08-31 09:42:56 首次发布

本文链接：https://blog.csdn.net/weixin_47481102/article/details/132596261

版权

数据库专栏收录该内容

38 篇文章 0 订阅

订阅专栏

前提：

做两个表employee和movie，用来练习使用；

表一：employee

-- 创建表employee
CREATE TABLE IF NOT EXISTS employee(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  title VARCHAR(100) DEFAULT NULL,
  salary DOUBLE DEFAULT NULL,
  hire_date DATE NOT NULL,
  notes TEXT,
  PRIMARY KEY (id)
);

-- 插入数据
INSERT INTO employee (first_name, last_name, title, salary, hire_date) VALUES 
    ('Robin', 'Jackman', 'Software Engineer', 5500, '2001-10-12'),
    ('Taylor', 'Edward', 'Software Architect', 7200, '2002-09-21'),
    ('Vivian', 'Dickens', 'Database Administrator', 6000, '2012-08-29'),
    ('Harry', 'Clifford', 'Database Administrator', 6800, '2015-12-10'),
    ('Eliza', 'Clifford', 'Software Engineer', 4750, '1998-10-19'),
    ('Nancy', 'Newman', 'Software Engineer', 5100, '2007-01-23'),
    ('Melinda', 'Clifford', 'Project Manager', 8500, '2013-10-29'),
    ('Jack', 'Chan', 'Test Engineer', 6500, '2018-09-07'),
    ('Harley', 'Gilbert', 'Software Architect', 8000, '2000-07-17');

表二：movie

-- 创建表movie
CREATE TABLE IF NOT EXISTS movie(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    title_year INT NOT NULL,
    director_name VARCHAR(50) NOT NULL,
    actor_1_name VARCHAR(50) NOT NULL,
    actor_2_name VARCHAR(50) NOT NULL,
    duration INT NOT NULL,
    country VARCHAR(50) NOT NULL,
    content_rating VARCHAR(10) NOT NULL,
    gross BIGINT NOT NULL,
    imdb_score FLOAT DEFAULT 0
);

-- 插入数据
INSERT INTO movie(title, title_year, director_name, actor_1_name, actor_2_name, duration, country, content_rating, gross, imdb_score) VALUES
("Avatar", 2009, "James Cameron", "CCH Pounder", "Joel David Moore", 178, "USA", "PG-13", 760505847, 7.9),
("Pirates of the Caribbean: At World's End", 2007, "Gore Verbinski", "Johnny Depp", "Orlando Bloom", 169, "USA", "PG-13", 309404152, 7.1),
("Spectre", 2015, "Sam Mendes", "Christoph Waltz", "Rory Kinnear", 148, "UK", "PG-13", 200074175, 6.8),
("The Dark Knight Rises", 2012, "Christopher Nolan", "Tom Hardy", "Christian Bale", 164, "USA", "PG-13", 448130642, 8.5),
("John Carter", 2012, "Andrew Stanton", "Daryl Sabara", "Samantha Morton", 132, "USA", "PG-13", 73058679, 6.6),
("Spider-Man 3", 2007, "Sam Raimi", "J.K. Simmons", "James Franco", 156, "USA", "PG-13", 336530303, 6.2),
("Tangled", 2010, "Nathan Greno", "Brad Garrett", "Donna Murphy", 100, "USA", "PG", 200807262, 7.8),
("Avengers: Age of Ultron", 2015, "Joss Whedon", "Chris Hemsworth", "Robert Downey Jr.", 141, "USA", "PG-13", 458991599, 7.5),
("Harry Potter and the Half-Blood Prince", 2009, "David Yates", "Alan Rickman", "Daniel Radcliffe", 153, "UK", "PG", 301956980, 7.5),
("Batman v Superman: Dawn of Justice", 2016, "Zack Snyder", "Henry Cavill", "Lauren Cohan", 183, "USA", "PG-13", 330249062, 6.9),
("Superman Returns", 2006, "Bryan Singer", "Kevin Spacey", "Marlon Brando", 169, "USA", "PG-13", 200069408, 6.1),
("Quantum of Solace", 2008, "Marc Forster", "Giancarlo Giannini", "Mathieu Amalric", 106, "UK", "PG-13", 168368427, 6.7),
("Pirates of the Caribbean: Dead Man's Chest", 2006, "Gore Verbinski", "Johnny Depp", "Orlando Bloom", 151, "USA", "PG-13", 423032628, 7.3),
("The Lone Ranger", 2013, "Gore Verbinski", "Johnny Depp", "Ruth Wilson", 150, "USA", "PG-13", 89289910, 6.5),
("Man of Steel", 2013, "Zack Snyder", "Henry Cavill", "Christopher Meloni", 143, "USA", "PG-13", 291021565, 7.2),
("The Chronicles of Narnia: Prince Caspian", 2008, "Andrew Adamson", "Peter Dinklage", "Pierfrancesco Favino", 150, "USA", "PG", 141614023, 6.6),
("The Avengers", 2012, "Joss Whedon", "Chris Hemsworth", "Robert Downey Jr.", 173, "USA", "PG-13", 623279547, 8.1),
("Pirates of the Caribbean: On Stranger Tides", 2011, "Rob Marshall", "Johnny Depp", "Sam Claflin", 136, "USA", "PG-13", 241063875, 6.7),
("Men in Black 3", 2012, "Barry Sonnenfeld", "Will Smith", "Michael Stuhlbarg", 106, "USA", "PG-13", 179020854, 6.8),
("The Hobbit: The Battle of the Five Armies", 2014, "Peter Jackson", "Aidan Turner", "Adam Brown", 164, "New Zealand", "PG-13", 255108370, 7.5),
("The Amazing Spider-Man", 2012, "Marc Webb", "Emma Stone", "Andrew Garfield", 153, "USA", "PG-13", 262030663, 7),
("Robin Hood", 2010, "Ridley Scott", "Mark Addy", "William Hurt", 156, "USA", "PG-13", 105219735, 6.7),
("The Hobbit: The Desolation of Smaug", 2013, "Peter Jackson", "Aidan Turner", "Adam Brown", 186, "USA", "PG-13", 258355354, 7.9),
("The Golden Compass", 2007, "Chris Weitz", "Christopher Lee", "Eva Green", 113, "USA", "PG-13", 70083519, 6.1),
("King Kong", 2005, "Peter Jackson", "Naomi Watts", "Thomas Kretschmann", 201, "New Zealand", "PG-13", 218051260, 7.2),
("Titanic", 1997, "James Cameron", "Leonardo DiCaprio", "Kate Winslet", 194, "USA", "PG-13", 658672302, 7.7),
("Captain America: Civil War", 2016, "Anthony Russo", "Robert Downey Jr.", "Scarlett Johansson", 147, "USA", "PG-13", 407197282, 8.2),
("Battleship", 2012, "Peter Berg", "Liam Neeson", "Alexander Skarsgård", 131, "USA", "PG-13", 65173160, 5.9),
("Jurassic World", 2015, "Colin Trevorrow", "Bryce Dallas Howard", "Judy Greer", 124, "USA", "PG-13", 652177271, 7),
("Skyfall", 2012, "Sam Mendes", "Albert Finney", "Helen McCrory", 143, "UK", "PG-13", 304360277, 7.8),
("Spider-Man 2", 2004, "Sam Raimi", "J.K. Simmons", "James Franco", 135, "USA", "PG-13", 373377893, 7.3),
("Iron Man 3", 2013, "Shane Black", "Robert Downey Jr.", "Jon Favreau", 195, "USA", "PG-13", 408992272, 7.2),
("Alice in Wonderland", 2010, "Tim Burton", "Johnny Depp", "Alan Rickman", 108, "USA", "PG", 334185206, 6.5),
("X-Men: The Last Stand", 2006, "Brett Ratner", "Hugh Jackman", "Kelsey Grammer", 104, "Canada", "PG-13", 234360014, 6.8),
("Monsters University", 2013, "Dan Scanlon", "Steve Buscemi", "Tyler Labine", 104, "USA", "G", 268488329, 7.3),
("Transformers: Revenge of the Fallen", 2009, "Michael Bay", "Glenn Morshower", "Kevin Dunn", 150, "USA", "PG-13", 402076689, 6),
("Transformers: Age of Extinction", 2014, "Michael Bay", "Bingbing Li", "Sophia Myles", 165, "USA", "PG-13", 245428137, 5.7),
("Oz the Great and Powerful", 2013, "Sam Raimi", "Tim Holmes", "Mila Kunis", 130, "USA", "PG", 234903076, 6.4),
("The Amazing Spider-Man 2", 2014, "Marc Webb", "Emma Stone", "Andrew Garfield", 142, "USA", "PG-13", 202853933, 6.7),
("TRON: Legacy", 2010, "Joseph Kosinski", "Jeff Bridges", "Olivia Wilde", 125, "USA", "PG", 172051787, 6.8),
("Cars 2", 2011, "John Lasseter", "Joe Mantegna", "Thomas Kretschmann", 106, "USA", "G", 191450875, 6.3),
("Green Lantern", 2011, "Martin Campbell", "Ryan Reynolds", "Temuera Morrison", 123, "USA", "PG-13", 116593191, 5.6),
("Toy Story 3", 2010, "Lee Unkrich", "Tom Hanks", "John Ratzenberger", 103, "USA", "G", 414984497, 8.3),
("Terminator Salvation", 2009, "McG", "Christian Bale", "Bryce Dallas Howard", 118, "USA", "PG-13", 125320003, 6.6),
("Furious 7", 2015, "James Wan", "Jason Statham", "Paul Walker", 140, "USA", "PG-13", 350034110, 7.2),
("World War Z", 2013, "Marc Forster", "Peter Capaldi", "Brad Pitt", 123, "USA", "PG-13", 202351611, 7),
("X-Men: Days of Future Past", 2014, "Bryan Singer", "Jennifer Lawrence", "Peter Dinklage", 149, "USA", "PG-13", 233914986, 8),
("Star Trek Into Darkness", 2013, "J.J. Abrams", "Benedict Cumberbatch", "Bruce Greenwood", 132, "USA", "PG-13", 228756232, 7.8),
("Jack the Giant Slayer", 2013, "Bryan Singer", "Eddie Marsan", "Ewen Bremner", 114, "USA", "PG-13", 65171860, 6.3),
("The Great Gatsby", 2013, "Baz Luhrmann", "Leonardo DiCaprio", "Elizabeth Debicki", 143, "Australia", "PG-13", 144812796, 7.3),
("Prince of Persia: The Sands of Time", 2010, "Mike Newell", "Jake Gyllenhaal", "Richard Coyle", 116, "USA", "PG-13", 90755643, 6.6),
("Pacific Rim", 2013, "Guillermo del Toro", "Charlie Hunnam", "Clifton Collins Jr.", 131, "USA", "PG-13", 101785482, 7),
("Transformers: Dark of the Moon", 2011, "Michael Bay", "Glenn Morshower", "Lester Speight", 154, "USA", "PG-13", 352358779, 6.3),
("Indiana Jones and the Kingdom of the Crystal Skull", 2008, "Steven Spielberg", "Harrison Ford", "Ray Winstone", 122, "USA", "PG-13", 317011114, 6.2),
("The Good Dinosaur", 2015, "Peter Sohn", "A.J. Buckley", "Jack McGraw", 93, "USA", "PG", 123070338, 6.8),
("Brave", 2012, "Mark Andrews", "Kelly Macdonald", "John Ratzenberger", 93, "USA", "PG", 237282182, 7.2),
("Star Trek Beyond", 2016, "Justin Lin", "Sofia Boutella", "Melissa Roxburgh", 122, "USA", "PG-13", 130468626, 7.5),
("WALL·E", 2008, "Andrew Stanton", "John Ratzenberger", "Fred Willard", 98, "USA", "G", 223806889, 8.4),
("Rush Hour 3", 2007, "Brett Ratner", "Tzi Ma", "Dana Ivey", 91, "USA", "PG-13", 140080850, 6.2),
("2012", 2009, "Roland Emmerich", "Oliver Platt", "Liam James", 158, "USA", "PG-13", 166112167, 5.8),
("A Christmas Carol", 2009, "Robert Zemeckis", "Robin Wright", "Colin Firth", 96, "USA", "PG", 137850096, 6.8),
("Jupiter Ascending", 2015, "Lana Wachowski", "Channing Tatum", "Mila Kunis", 127, "USA", "PG-13", 47375327, 5.4),
("The Legend of Tarzan", 2016, "David Yates", "Christoph Waltz", "Alexander Skarsgård", 110, "USA", "PG-13", 124051759, 6.6),
("The Chronicles of Narnia: The Lion, the Witch and the Wardrobe", 2005, "Andrew Adamson", "Jim Broadbent", "Kiran Shah", 150, "USA", "PG", 291709845, 6.9),
("X-Men: Apocalypse", 2016, "Bryan Singer", "Jennifer Lawrence", "Michael Fassbender", 144, "USA", "PG-13", 154985087, 7.3),
("The Dark Knight", 2008, "Christopher Nolan", "Christian Bale", "Heath Ledger", 152, "USA", "PG-13", 533316061, 9),
("Up", 2009, "Pete Docter", "John Ratzenberger", "Delroy Lindo", 96, "USA", "PG", 292979556, 8.3),
("Monsters vs. Aliens", 2009, "Rob Letterman", "Amy Poehler", "Rainn Wilson", 94, "USA", "PG", 198332128, 6.5),
("Iron Man", 2008, "Jon Favreau", "Robert Downey Jr.", "Jeff Bridges", 126, "USA", "PG-13", 318298180, 7.9),
("Hugo", 2011, "Martin Scorsese", "Chloe Grace Moretz", "Christopher Lee", 126, "USA", "PG", 73820094, 7.5),
("Wild Wild West", 1999, "Barry Sonnenfeld", "Will Smith", "Salma Hayek", 106, "USA", "PG-13", 113745408, 4.8),
("The Mummy: Tomb of the Dragon Emperor", 2008, "Rob Cohen", "Jet Li", "Brendan Fraser", 112, "USA", "PG-13", 102176165, 5.2),
("Suicide Squad", 2016, "David Ayer", "Will Smith", "Robin Atkin Downes", 123, "USA", "PG-13", 161087183, 6.9),
("Evan Almighty", 2007, "Tom Shadyac", "Jimmy Bennett", "Morgan Freeman", 96, "USA", "PG", 100289690, 5.4),
("Edge of Tomorrow", 2014, "Doug Liman", "Tom Cruise", "Lara Pulver", 113, "USA", "PG-13", 100189501, 7.9),
("Waterworld", 1995, "Kevin Reynolds", "Jeanne Tripplehorn", "Rick Aviles", 176, "USA", "PG-13", 88246220, 6.1),
("G.I. Joe: The Rise of Cobra", 2009, "Stephen Sommers", "Joseph Gordon-Levitt", "Dennis Quaid", 118, "USA", "PG-13", 150167630, 5.8),
("Inside Out", 2015, "Pete Docter", "Amy Poehler", "Mindy Kaling", 95, "USA", "PG", 356454367, 8.3),
("The Jungle Book", 2016, "Jon Favreau", "Scarlett Johansson", "Bill Murray", 106, "UK", "PG", 362645141, 7.8),
("Iron Man 2", 2010, "Jon Favreau", "Robert Downey Jr.", "Scarlett Johansson", 124, "USA", "PG-13", 312057433, 7),
("Snow White and the Huntsman", 2012, "Rupert Sanders", "Chris Hemsworth", "Kristen Stewart", 132, "USA", "PG-13", 155111815, 6.1),
("Maleficent", 2014, "Robert Stromberg", "Angelina Jolie Pitt", "Sharlto Copley", 97, "USA", "PG", 241407328, 7),
("Dawn of the Planet of the Apes", 2014, "Matt Reeves", "Gary Oldman", "Judy Greer", 130, "USA", "PG-13", 208543795, 7.6),
("47 Ronin", 2013, "Carl Rinsch", "Keanu Reeves", "Cary-Hiroyuki Tagawa", 128, "USA", "PG-13", 38297305, 6.3),
("Captain America: The Winter Soldier", 2014, "Anthony Russo", "Scarlett Johansson", "Chris Evans", 136, "USA", "PG-13", 259746958, 7.8),
("Shrek Forever After", 2010, "Mike Mitchell", "Jon Hamm", "Kathy Griffin", 93, "USA", "PG", 238371987, 6.4),
("Tomorrowland", 2015, "Brad Bird", "Judy Greer", "Chris Bauer", 130, "USA", "PG", 93417865, 6.5),
("Inception", 2010, "Christopher Nolan", "Leonardo DiCaprio", "Tom Hardy", 148, "USA", "PG-13", 292568851, 8.8),
("Big Hero 6", 2014, "Don Hall", "Damon Wayans Jr.", "Daniel Henney", 102, "USA", "PG", 222487711, 7.9),
("Wreck-It Ralph", 2012, "Rich Moore", "Jack McBrayer", "Sarah Silverman", 101, "USA", "PG", 189412677, 7.8),
("The Polar Express", 2004, "Robert Zemeckis", "Tom Hanks", "Eddie Deezen", 100, "USA", "G", 665426, 6.6),
("Independence Day: Resurgence", 2016, "Roland Emmerich", "Vivica A. Fox", "Sela Ward", 120, "USA", "PG-13", 102315545, 5.5),
("How to Train Your Dragon", 2010, "Dean DeBlois", "Gerard Butler", "America Ferrera", 98, "USA", "PG", 217387997, 8.2),
("Terminator 3: Rise of the Machines", 2003, "Jonathan Mostow", "Nick Stahl", "M.C. Gainey", 109, "USA", "R", 150350192, 6.4),
("Guardians of the Galaxy", 2014, "James Gunn", "Bradley Cooper", "Vin Diesel", 121, "USA", "PG-13", 333130696, 8.1),
("Interstellar", 2014, "Christopher Nolan", "Matthew McConaughey", "Anne Hathaway", 169, "USA", "PG-13", 187991439, 8.6),
("Australia", 2008, "Baz Luhrmann", "Essie Davis", "Bryan Brown", 165, "Australia", "PG-13", 49551662, 6.6),
("Warcraft", 2016, "Duncan Jones", "Dominic Cooper", "Callum Rennie", 123, "USA", "PG-13", 46978995, 7.3),
("X-Men: First Class", 2011, "Matthew Vaughn", "Jennifer Lawrence", "Michael Fassbender", 132, "USA", "PG-13", 146405371, 7.8),
("The Hobbit: An Unexpected Journey", 2012, "Peter Jackson", "Aidan Turner", "Adam Brown", 182, "USA", "PG-13", 303001229, 7.9);

1、equal 和 not equal （= 、≠）

-- 查询全部数据
SELECT * FROM employee

-- 查询表中工资等于8000的数据
SELECT * FROM employee WHERE salary = 8000
SELECT * FROM employee WHERE NOT salary != 8000

-- 查询表中工资不等于8000的数据
SELECT * FROM employee WHERE salary != 8000
SELECT * FROM employee WHERE NOT salary = 8000

1.1FALSE 和 TRUE

-- FALSE 和 TRUE:FALSE 状态是1，TRUE状态是0

SELECT 1=1
SELECT 1!=1

1.2练习：= 和!=的练习

-- 查询不是2008年发布的电影
SELECT * FROM movie WHERE title_year != 2008
SELECT * FROM movie WHERE NOT title_year = 2008

2、like 和 not like

-- SELECT * FROM employee

-- 查询first_name第一个字母是R的数据
-- SELECT * FROM employee WHERE first_name LIKE 'R%'

-- 查询first_name第二个字母是a的数据
-- SELECT * FROM employee WHERE first_name like '_a%'

-- 查询first_name第一个字母是R的数据
-- SELECT * FROM employee WHERE first_name NOT LIKE 'R%'

-- 查询first_name第二个字母不是a的数据
-- SELECT * FROM employee WHERE first_name NOT like '_a%'

2.1关于like大小写敏感问题

关于like大小写敏感的问题
CREATE TABLE test_like(name VARCHAR(10),age INT)
INSERT INTO test_like (name,age)VALUES('TOM',9),('tim',10)

-- 此时，like模糊查询时不区分大小写
SELECT * FROM test_like WHERE name LIKE 'T%'

-- 检索时区分like大小写可以在like后面添加binary
SELECT * FROM test_like WHERE name LIKE BINARY 'T%'

-- 或者在创建表时，表明大小写敏感词汇
CREATE TABLE test_like1(name VARCHAR(10) BINARY,age INT)
INSERT INTO test_like1 (name,age)VALUES('TOM',9),('tim',10)
SELECT * FROM test_like1 WHERE name LIKE 'T%'

3、Greater Then 和 Less Then

大于、小于、大于等于、小于等于

-- SELECT * FROM employee

-- SELECT * FROM employee WHERE salary >8000 OR salary = 8000
-- SELECT * FROM employee WHERE salary >=8000

-- SELECT * FROM employee WHERE salary < 8000 OR salary = 8000
-- SELECT * FROM employee WHERE salary <= 8000

3.1练习

-- 练习：
SELECT * FROM movie

SELECT * FROM movie WHERE imdb_score <8.0

SELECT * FROM movie WHERE imdb_score >= 8.0

4、and 和 or

-- 查询所有数据
SELECT * FROM employee 

-- 查询工资大于等于8000并且first_name 中第二个字母是a的数据
SELECT * FROM employee WHERE salary >= 8000 AND first_name LIKE '_a%'

-- 查询工资大于等于8000 或者title是Software Architect的数据
SELECT * FROM employee WHERE salary >= 8000 OR title = 'Software Architect'

-- 查询工资大于等于8500 或者工资小于7300，且工资小于7300中的last_name中有字母d的数据
SELECT * FROM employee WHERE salary >= 8500 OR salary < 7300 AND last_name = '%d%'

4.1练习

-- 查询表数据中director_name, imdb_score两列数据
SELECT director_name, imdb_score FROM movie 

-- 查询表中作者是Christopher Nolan，且评分大于等于8
-- SELECT director_name ,imdb_score FROM movie WHERE director_name = 'Christopher Nolan' AND imdb_score >= 8

-- 查询director_name是Joss Whedon和Christopher Nolan，并且评分大于等于8
SELECT * FROM movie WHERE director_name = 'Joss Whedon' AND imdb_score >= 8 OR director_name = 'Christopher Nolan' 

-- 查询director_name是Joss Whedon或者Christopher Nolan，并且Christopher Nolan的电影评分但与等于8
SELECT * FROM movie WHERE director_name = 'Joss Whedon'  OR director_name = 'Christopher Nolan' AND imdb_score >= 8

-- 查询director_name是Christopher Nolan的电影
SELECT director_name FROM movie WHERE director_name = 'Christopher Nolan'

5、between

-- 查询所有数据
SELECT * FROM employee 

-- 使用逻辑操作符and查询8000到9000的数据（包括8000和9000）
SELECT * FROM employee WHERE salary >= 8000 AND salary <=9000

-- 使用逻辑操作符between查询8000到9000的数据（包括8000和9000）
SELECT * FROM employee WHERE salary BETWEEN 8000 AND 9000

5.1练习

--  查询电影在1990到2000年份的数据（包括1990和2000）
SELECT  * FROM movie WHERE title_year BETWEEN 1990 AND 2000

-- 查询电影小于1990或者大于2000年份的数据（不包括1990和2000）
SELECT * FROM movie WHERE title_year > 2000 OR title_year < 1990

6、in 和 not in

-- 查询所有数据
SELECT * FROM employee

-- 使用逻辑操作符检索数据5000、6000、7000、8000
SELECT * FROM employee WHERE salary = 5000 OR salary = 6000 OR salary = 7000 OR salary = 8000

-- 使用逻辑操符in检索数据5000、6000、7000、8000
SELECT * FROM employee WHERE salary IN (5000, 6000, 7000, 8000)

-- 使用逻辑操作符not in 检索除了数据5000、6000、7000、8000以外的数据
SELECT * FROM employee WHERE salary NOT IN (5000, 6000, 7000, 8000)

6.1练习

-- 使用逻辑操作符in检索评分是8和9的数据
SELECT * FROM movie WHERE imdb_score IN (8, 9)

-- 使用逻辑操作符ont in检索评分不是8和9的数据
SELECT * FROM movie WHERE imdb_score NOT IN (8, 9)

-- 使用逻辑操作符in检索director_name是'Sam Raimi', 'John Lasseter'并且评分是7 的数据
SELECT director_name, imdb_score FROM movie WHERE director_name IN ('Sam Raimi', 'John Lasseter') AND imdb_score <= 7

7、Case Statement

注意：CASE statement 需要注意：case前面需要加逗号，case后面是when...then...语句和esle语句，case以end...语句结束，后面是from...等内容

-- 查询 工资大于8000时是high，工资在6000-8000时是medium，6000以下是low，且降序排列
SELECT * ,
 	CASE 
 		WHEN salary > 8000 THEN 'high'
   	    WHEN salary BETWEEN 6000 AND 8000 THEN 'medium'
 		ELSE 'low'
 	END AS tag
 FROM employee ORDER BY salary DESC


-- 模糊查询title中含有Engineer是1，含有Architect是2，其他是3，并且升序排列
SELECT first_name, last_name, title, 
 	CASE 
 		WHEN title LIKE '%Engineer%' THEN 1
		WHEN title LIKE '%Architect%' THEN 2
 		ELSE 3
 	END AS tag
FROM employee ORDER BY tag

7.1练习

-- 1.按票房多->少排序，找出美国在2000-2010年期间票房超过1亿美元的电影，
-- （打印出电影名称，导演，上映时间，票房，IMDB评分)
select 
 	title,director_name,title_year,gross,imdb_score 
from movie 
where 
 	country='USA' and title_year between 2000 and 2010 and gross > 100000000 
order by gross desc;


-- 2.给电影加一个star评分，按imdb评分多->少排序，评分规呗则为
-- -MDB评分8分以及8分以上的，标记为五星*****
-- -IMDB评分7-8分（包括7分，但不包括8分），标记为四星****
-- -IMDB评分6-7分（包含6分，但不包含7分），标记为三星***
-- -IMDB评分5-6分（包含5分，但不包含6分)，标记为二星**
-- -IMDB评分低于5分，标记为一星*
select 
	imdb_score ,
  case
      when imdb_score >= 8 then '*****'
      when imdb_score >= 7 and imdb_score <8 then '****'
      when imdb_score >= 6 and imdb_score < 7 then '***'
      when imdb_score >= 5 and imdb_score < 6 then '**'
      else '*'
   end as star
from movie order by imdb_score desc;