SQL创建，查询

一、创建一个表 

1.Alter

ALTER TABLE celebs 
ADD COLUMN twitter_handle TEXT;

1. ALTER TABLE is a clause that lets you make the specified changes.
2. celebs is the name of the table that is being changed.
3. ADD COLUMN is a clause that lets you add a new column to a table:

• twitter_handle is the name of the new column being added
• TEXT is the data type for the new column

2.update

UPDATE celebs 
SET twitter_handle = '@taylorswift13' 
WHERE id = 4; 

1. UPDATE is a clause that edits a row in the table.
2. celebs is the name of the table.
3. SET is a clause that indicates the column to edit.

• twitter_handle is the name of the column that is going to be updated
• @taylorswift13 is the new value that is going to be inserted into the twitter_handle column.

4. WHERE is a clause that indicates which row(s) to update with the new column value. Here the row with a 4 in the id column is the row that will have the twitter_handle updated to @taylorswift13.

 3.delete

DELETE FROM celebs 
WHERE twitter_handle IS NULL;

4.Constraints

CREATE TABLE celebs (
   id INTEGER PRIMARY KEY, 
   name TEXT UNIQUE,
   date_of_birth TEXT NOT NULL,
   date_of_death TEXT DEFAULT 'Not Applicable'
);

1. PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

2. UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.

3. NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.

4. DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

二、查询

1.选择多行

SELECT name, genre, year
FROM movies;

2.AS

AS is a keyword in SQL that allows you to rename a column or table using an alias. The new name can be anything you want as long as you put it inside of single quotes. Here we renamed the name column as Titles.

• When using AS, the columns are not being renamed in the table. The aliases only appear in the result.

SELECT imdb_rating AS 'IMDb'
FROM movies;

3.distinct 

SELECT DISTINCT tools 
FROM inventory;

 4.Where

SELECT *
FROM movies
WHERE imdb_rating > 8;

5.Like

SELECT * 
FROM movies
WHERE name LIKE 'Se_en';

• LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

• name LIKE 'Se_en' is a condition evaluating the name column for a specific pattern.

• Se_en represents a pattern with a wildcard character.

The _ means you can substitute any individual character here without breaking the pattern. The names Seven and Se7en both match this pattern.

The percentage sign % is another wildcard character that can be used with LIKE.

• A% matches all movies with names that begin with letter ‘A’
• %a matches all movies that end with ‘a’

SELECT * 
FROM movies 
WHERE name LIKE '%man%';

名字中包含man的电影 

 LIKE is not case sensitive.

看个例子：选择以单词the为开头的电影 selects all the information about the movie titles that begin with the word ‘The’.

SELECT * 
FROM movies
WHERE name LIKE 'THE %';

而不是：

SELECT * 
FROM movies
WHERE name LIKE 'THE%';

注意空格的区别，第二种可能选择以They开头的电影。

6.is Null

SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;

7.Between

between and运用在不同类型的数据上时，其开闭区间是不一样的

this statement filters the result set to only include movies with years from 1990 up to, and including 1999(双闭区间)

这里没有单引号

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

In this statement, BETWEEN filters the result set to only include movies with names that begin with the letter ‘A’ up to, but not including ones that begin with ‘J’.

这里有单引号

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';

包括以A开头的，比如“Ab，Ac”，但是不包括以J开头的，如“Ja”，只包含J本身，就是说如果一个电影叫J，是在返回的结果中的。

8.and

SELECT *
FROM movies
WHERE year BETWEEN 1970 AND 1979
  AND imdb_rating > 8;

 9.or

SELECT *
FROM movies
WHERE year > 2014
   OR genre = 'action';

 10.order by

We can sort the results using ORDER BY, either alphabetically or numerically.

• DESC is a keyword used in ORDER BY to sort the results in descending order (high to low or Z-A).

• ASC is a keyword used in ORDER BY to sort the results in ascending order (low to high or A-Z).

eg:Write a query that retrieves the name, year, and imdb_rating columns of all the movies, ordered highest to lowest by their ratings. 按评分从高到低排列

SELECT name, year,imdb_rating
FROM movies
ORDER BY imdb_rating desc;

11.limit

LIMIT is a clause that lets you specify the maximum number of rows the result set will have.

eg: write a query that returns the top 3 highest rated movies.

select *
from movies
order by imdb_rating desc
limit 3

12.case (if)

Suppose we want to condense the ratings in movies to three levels:

• If the rating is above 8, then it is Fantastic.
• If the rating is above 6, then it is Poorly Received.
• Else, Avoid at All Costs.

SELECT name,
 CASE
  WHEN imdb_rating > 8 THEN 'Fantastic'
  WHEN imdb_rating > 6 THEN 'Poorly Received'
  ELSE 'Avoid at All Costs'
 END
FROM movies;

• case必须以end结尾
• 实际上是创造了一个新的视图（这个语句会选出评分在8分以上的电影的名字，在后面加上一行fantastic）
• 加上的这一行的名称默认为这个选择语句

 如果想要美观，就用as重新命名一下：

SELECT name,
 CASE
  WHEN imdb_rating > 8 THEN 'Fantastic'
  WHEN imdb_rating > 6 THEN 'Poorly Received'
  ELSE 'Avoid at All Costs'
 END AS 'Review'
FROM movies;

13.review

• SELECT is the clause we use every time we want to query information from a database.
• AS renames a column or table.
• DISTINCT return unique values.
• WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.
• LIKE and BETWEEN are special operators.
• AND and OR combines multiple conditions.
• ORDER BY sorts the result.
• LIMIT specifies the maximum number of rows that the query will return.
• CASE creates different outputs.