+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 1 | War | great 3D | 8.9 | | 2 | Science | fiction | 8.5 | | 3 | irish | boring | 6.2 | | 4 | Ice song | Fantacy | 8.6 | | 5 | House card| Interesting| 9.1 | +---------+-----------+--------------+-----------+
查找 id 为奇数,并且 description 不是 boring 的电影,按 rating 降序。效果如下
1 2 3 4 5 6
+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | +---------+-----------+--------------+-----------+
解:
1 2 3 4 5 6 7 8 9
SELECT * FROM cinema WHERE id % 2 = 1 AND description != 'boring' ORDERBY rating DESC;
+---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+------------+
查找有五名及以上 student 的 class。效果如下
1 2 3 4 5
+---------+ | class | +---------+ | Math | +---------+
对 class 列进行分组之后,再使用 count 汇总函数统计每个分组的记录个数,之后使用 HAVING 进行筛选。HAVING 针对分组进行筛选,而 WHERE 针对每个记录(行)进行筛选。
解:
1 2 3 4 5 6 7 8
SELECT class FROM courses GROUPBY class HAVING count( DISTINCT student ) >= 5;
DROPTABLE IF EXISTS Person; CREATETABLE Person ( PersonId INT, FirstName VARCHAR ( 255 ), LastName VARCHAR ( 255 ) ); DROPTABLE IF EXISTS Address; CREATETABLE Address ( AddressId INT, PersonId INT, City VARCHAR ( 255 ), State VARCHAR ( 255 ) ); INSERTINTO Person ( PersonId, LastName, FirstName ) VALUES ( 1, 'Wang', 'Allen' ); INSERTINTO Address ( AddressId, PersonId, City, State ) VALUES ( 1, 2, 'New York City', 'New York' );
Person表:
1 2 3 4 5 6 7 8
+-------------+---------+ | Column Name | Type | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId is the primary key column for this table.
Address 表:
1 2 3 4 5 6 7 8 9
+-------------+---------+ | Column Name | Type | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId is the primary key column for this table.
查找 FirstName, LastName, City, State 数据,而不管一个用户有没有填地址信息。
涉及到 Person 和 Address 两个表,在对这两个表执行连接操作时,因为要保留 Person 表中的信息,即使在 Address 表中没有关联的信息也要保留。此时可以用左外连接,将 Person 表放在 LEFT JOIN 的左边。
解:
1 2 3 4 5 6 7 8 9
SELECT FirstName, LastName, City, State FROM Person P LEFTJOIN Address A ON P.PersonId = A.PersonId;
SELECT D.NAME Department, E.NAME Employee, E.Salary FROM Employee E, Department D, ( SELECT DepartmentId, MAX( Salary ) Salary FROM Employee GROUPBY DepartmentId ) M WHERE E.DepartmentId = D.Id AND E.DepartmentId = M.DepartmentId AND E.Salary = M.Salary;