目录

• SELECTING
  • SELECTing single columns
  • SELECTing multiple columns
  • select all
  • SELECT DISTINCT
  • Learning to COUNT
• FILTERING
  • WHERE
  • Simple filtering of text
  • WHERE AND
  • WHERE AND OR
  • BETWEEN

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;

Introduction to SQL

标签：cert   provides   字符   als   get   iso   tin   and   OWIN