一、创建一个表
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 addedTEXT
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 thetwitter_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 theWHERE
clause to search for a specific pattern in a column. -
name LIKE 'Se_en'
is a condition evaluating thename
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 year
s 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 name
s 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 inORDER BY
to sort the results in descending order (high to low or Z-A). -
ASC
is a keyword used inORDER 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.