# Unit 2. Queries

## 2.3 like的使用

2.like后面的模式串中可以使用通配符。可以使用下滑线_和%。

% is a wildcard character（通配符） that matches zero or more missing letters in the pattern.%代表0个或多个字符。
A% matches all movies with names that begin with "A"
%a matches all movies that end with "a"
SELECT * FROM movies WHERE name LIKE '%man%';
You can use % both before and after a pattern. Here, any movie that contains the word "man" in its name will be returned in the result set. Notice, that LIKE is not case sensitive. "Batman" and "Man Of Steel" both appear in the result set. like对大小写不敏感。

## 2.4 between and的使用

select * from movies where year between 1990 and 2000;

## 2.5 AND 与OR运算

说明：AND and OR operator can be used with the WHERE clause，大小写皆可。

## 2.7 limit的使用

要求：write a query that only returns the three lowest rated movies.

示例: select * from movies order by imdb_rating asc limit 3;

说明：Sometimes even filtered results can return thousands of rows in large databases. In these situations it becomes important to cap the number of rows in a result set.
LIMIT is a clause that lets you specify the maximum number of rows the result set will have. Here, we specify that the result set can not have more than three rows.

# Unit 3. Aggregate Functions

## 3.1 count()的使用

COUNT() is a function that takes the name of a column as an argument and counts the number of rows where the column is not NULL. Here, we want to count every row so we pass * as an argument.

# Unit4. Multiple Tables

The data in these tables are related to each other. Through SQL, we can write queries that combine data from multiple tables that are related to one another. This is one of the most powerful features of relational databases.

Imagine a database with two tables, artists and albums. An artist can produce manydifferent albums, and an album is produced by an artist.

## 4.1 主键

示例：create table artists(id integer primary key, name text);

说明：A primary key serves as a unique identifier for each row or record in a given table. The primary key is literally an id value for a record. We're going to use this value to connect artists to the albums they have produced.

By specifying that the id column is the PRIMARY KEY, SQL makes sure that:(1) None of the values in this column are NULL (2) Each value in this column is uniqueA table can not have more than one PRIMARY KEY column.

## 4.4 left join的使用

The left table is simply the first table that appears in the statement. Here, the left table is albums. Likewise, the right table is the second table that appears. Here, artists is the right table.

## 4.5 使用as重命列名

It is important to note that the columns have not been renamed in either table. The aliases only appear in the result set.

# 补充

## 1. having的使用

create TABLE Table1 (
ID int identity(1,1) primary key NOT NULL,
classid int,
sex varchar(10),
age int)；

select COUNT(*) as '>20岁人数',classid  from Table1 where sex='男' group by classid,age having age>20 ；

--需要注意说明：当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：
--执行where子句查找符合条件的数据；
--使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
--having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
--having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
--having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。