SELECTING

SELECTing single columns

格式:

select xx from xx

SELECT不区分大小写

select name from people;

SELECTing multiple columns

格式：

select A,B,C.. from xx;

SELECT title, release_year, country

FROM films;

select all

选择数据库里全部的列

SELECT *

FROM films;

SELECT DISTINCT

often your results will include many duplicate values. If you want to select all the unique values from a column, you can use the DISTINCT keyword.

SELECT DISTINCT language

FROM films;

Learning to COUNT

统计“记录”

What if you want to count the number of employees in your employees table? The COUNT statement lets you do this by returning the number of rows in one or more columns.

统计所有记录用*

How many records are contained in the reviews table?

SELECT COUNT(*)

FROM xx;

统计某项记录有多少条

SELECT COUNT(xx)

FROM xx;

SELECT COUNT(birthdate)

FROM people;

COUNT和DISTINCT搭配使用效果更佳，统计不重复的记录

Count the number of unique birth dates in the people table.

SELECT COUNT(DISTINCT birthdate)

FROM people;

FILTERING

WHERE

换句话说，按条件筛选，但是条件要后写

In SQL, the WHERE keyword allows you to filter based on both text and numeric values in a table. There are a few different comparison operators you can use:

• = equal
• <> not equal
• < less than
• /> greater than 就是大于号，加上一个转义字符/
• <= less than or equal to
• />= greater than or equal to

SELECT *

FROM films

WHERE budget > 10000; 看这里，条件要后写

select title, release_year from films

where release_year>2000

Simple filtering of text

Remember, the WHERE clause can also be used to filter text results, such as names or countries.

SELECT title

FROM films

WHERE country = 'China';

注意就是一定要用单引号

select * from films

where certification = 'R';

WHERE AND

Often, you'll want to select data based on multiple conditions. You can build up your WHERE queries by combining multiple conditions with the AND keyword.

SELECT title

FROM films

WHERE release_year > 1994 AND < 2000;

select * from films

where language='Spanish' and release_year > 2000 AND release_year< 2010;

上面这句这么写可能比较麻烦了，回头改下

WHERE AND OR

What if you want to select rows based on multiple conditions where some but not all of the conditions need to be met? For this, SQL has the OR operator.

SELECT title

FROM films

WHERE (release_year = 1994 OR release_year = 1995)

AND (certification = 'PG' OR certification = 'R');

SELECT title, release_year

FROM films

WHERE (release_year >= 1990 AND release_year < 2000)

AND (language = 'French' OR language = 'Spanish')

AND gross > 2000000;

BETWEEN

位于。。。之间

As you've learned, you can use the following query to get titles of all films released in and between 1994 and 2000:

SELECT title

FROM films

WHERE release_year >= 1994

AND release_year <= 2000;

Checking for ranges like this is very common, so in SQL the BETWEEN keyword provides a useful shorthand for filtering values within a specified range. This query is equivalent to the one above:

SELECT title

FROM films

WHERE release_year

BETWEEN 1994 AND 2000;

Similar to the WHERE clause, the BETWEEN clause can be used with multiple AND and OR operators, so you can build up your queries and make them even more powerful!

select title,release_year from films

where (release_year between 1990 and 2000)

and budget>100000000

and (language='Spanish' or language='French')

WHERE IN

Enter the IN operator! The IN operator allows you to specify multiple values in a WHERE clause, making it easier and quicker to specify multiple OR conditions! Neat, right?

可以在多个条件下过滤

select title,release_year from films

where release_year in (1990 ,2000)

and duration>120;

SELECT title, certification

FROM films

WHERE certification IN ('NC-17', 'R');

NULL and IS NULL

in SQL, NULL represents a missing or unknown value. You can check for NULL values using the expression IS NULL. For example, to count the number of missing birth dates in the people table:

SELECT COUNT(*)

FROM people

WHERE birthdate IS NULL;

LIKE and NOT LIKE

In SQL, the LIKE operator can be used in a WHERE clause to search for a pattern in a column. To accomplish this, you use something called a wildcard as a placeholder for some other values. There are two wildcards you can use with LIKE:

The % wildcard will match zero, one, or many characters in text

/% 表示任何以及多个字符

For example, the following query matches companies like 'Data', 'DataC' 'DataCamp', 'DataMind', and so on:

SELECT name

FROM companies

WHERE name LIKE 'Data%';

The _ wildcard will match a single character. For example, the following query matches companies like 'DataCamp', 'DataComp', and so on

/_表示单个任意字符

SELECT name

FROM companies

WHERE name LIKE 'DataC_mp';

Aggregate functions

Often, you will want to perform some calculation on the data in a database. SQL provides a few functions, called aggregate functions, to help you out with this.

• MAX()
• MIN()
• SUM()
• AVG()

SELECT AVG(duration)

FROM films;

SELECT sum(duration)

FROM films;

SELECT min(duration)

FROM films;

SELECT max(duration)

FROM films;

emmm..不区分大小写

Combining aggregate functions with WHERE

其实就是筛选条件多了。。。

SELECT max(gross)

FROM films

WHERE release_year between 2000 and 2012;

个人感觉这些简单的语句是基础，复杂的语句就看能否拆成简单的语句了。。

A note on arithmetic

In addition to using aggregate functions, you can perform basic arithmetic with symbols like +, -, *, and /.

就是将函数换成运算符号表示

It's AS simple AS aliasing

给某个对象临时指定别名

SELECT MAX(budget) AS max_budget,

       MAX(duration) AS max_duration

FROM films;

select title,gross-budget as net_profit

from films

Even more aliasing

最新和最旧用大和小来表示

SELECT MAX(release_year) - MIN(release_year)

AS difference

FROM films;

ORDER BY

按照顺序排列,等价于R arrange

In SQL, the ORDER BY keyword is used to sort results in ascending or descending order according to the values of one or more columns

By default ORDER BY will sort in ascending order. If you want to sort the results in descending order, you can use the DESC keyword. For example,

SELECT title

FROM films

ORDER BY release_year DESC;

星星老是忘记select多个变量的时候加上逗号，是不是有点太蠢了

SELECT name,birthdate

from people

order by birthdate;

Sorting single columns

select title,gross

from films

where title like  'M%'

order by title;

Sorting single columns (DESC)

To order results in descending order, you can put the keyword DESC after your ORDER BY. For example, to get all the names in the people table, in reverse alphabetical order:

SELECT imdb_score, film_id

FROM reviews

ORDER BY imdb_score DESC;

Sorting multiple columns

ORDER BY can also be used to sort on multiple columns. It will sort by the first column specified, then sort by the next, then the next, and so on. For example

SELECT birthdate, name

FROM people

ORDER BY birthdate, name;

GROUP BY

Now you know how to sort results! Often you'll need to aggregate results. For example, you might want to count the number of male and female employees in your company. Here, what you want is to group all the males together and count them, and group all the females together and count them. In SQL, GROUP BY allows you to group a result by one or more columns, like so:datacamp group by

一般面板数据的一些模型需要提前分组求和，不过这个得对模型有充分的了解之后在处理数据的时候进行分析

SELECT sex, count(*)

FROM employees

GROUP BY sex;

select release_year,country,max(budget)

from films

group by release_year,country

order by release_year,country;

HAVING a great time

having 出现的原因是where语句中不能出现运算

SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross

FROM films

WHERE release_year > 1990

GROUP BY release_year

HAVING AVG(budget) > 60000000

order by avg(gross) desc;

practice

-- select country, average budget, average gross

SELECT country, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross

-- from the films table

FROM films

-- group by country

GROUP BY country

-- where the country has more than 10 titles

HAVING COUNT(title) > 10

-- order by country

ORDER BY country

-- limit to only show 5 results

LIMIT 5;