【MySQL】逻辑运行符和CASE...WHEN

-- -- != 不等于-- --


SELECT title FROM books WHERE released_year = 2017;
 
SELECT title FROM books WHERE released_year != 2017;
 
SELECT title, author_lname FROM books;
 
SELECT title, author_lname FROM books WHERE author_lname = 'Harris';
 
SELECT title, author_lname FROM books WHERE author_lname != 'Harris';


-- -- NOT LIKE -- --
 
SELECT title FROM books WHERE title LIKE 'W%';
 
SELECT title FROM books WHERE title NOT LIKE 'W%';


-- -- > < 大于小于 -- --


SELECT title, released_year FROM books 
WHERE released_year >= 2000 ORDER BY released_year;


SELECT title, stock_quantity FROM books WHERE stock_quantity <= 100;


SELECT 'a' > 'b';
-- false 0
 
SELECT'A' > 'a';
-- false 0
 
SELECT'A' >=  'a';
-- true 1


SELECT 'h' < 'p';
-- true 1


-- -- AND -- -- 和


SELECT  
    title, 
    author_lname, 
    released_year FROM books
WHERE author_lname='Eggers' 
    AND released_year > 2010;
    
-- -- OR -- -- 或者


SELECT 
    title, 
    author_lname, 
    released_year 
FROM books
WHERE author_lname='Eggers' OR released_year > 2010;


-- -- BETWEEN -- -- 之间


SELECT title, released_year FROM books 
WHERE released_year BETWEEN 2004 AND 2015;


-- -- IN, NOT IN -- --


SELECT 
    title, 
    author_lname 
FROM books
WHERE author_lname='Carver' OR
      author_lname='Lahiri' OR
      author_lname='Smith';
 
SELECT title, author_lname FROM books
WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');


SELECT title, released_year FROM books
WHERE released_year != 2000 AND
      released_year != 2002 AND
      released_year != 2004 AND
      released_year != 2006 AND
      released_year != 2008 AND
      released_year != 2010 AND
      released_year != 2012 AND
      released_year != 2014 AND
      released_year != 2016;
 
SELECT title, released_year FROM books
WHERE released_year NOT IN 
(2000,2002,2004,2006,2008,2010,2012,2014,2016);


-- -- CASE...WHEN语句 -- --


SELECT title, released_year,
       CASE 
         WHEN released_year >= 2000 THEN 'Modern Lit'
         ELSE '20th Century Lit'
       END AS GENRE
FROM books;
 
SELECT title, stock_quantity,
    CASE 
        WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
        WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
        ELSE '***'
    END AS STOCK
FROM books;