数据库基础

创建表

creat table Student{

id int（10）primary key，

name char(10) ,

age int(5);

}

创建索引

唯一索引

creat unique index on Student（id）；

修改

添加

alter table Student add sourse char ;

删除

alter table Student drop column source;

修改

alter table Student modify age int(10) default(18);

查询

select * from Student；

去重

select distinct name

from Student

where id in（18,60）；

Like

SELECT Sname, Sno
FROM Student
WHERE Sno LIKE ‘%1_’;

聚集函数

COUNT([DISTINCT|ALL] *) 统计元组个数；
COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数；
SUM([DISTINCT|ALL] <列名>) 计算一列值的总和(此列必须是数值型) ；
AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值(此列必须是数值型)；
MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值；
MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值。
如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL为缺省值)，则表示不取消重复值。

查询总人数

select count（*） from Student

查询学生平均年龄

select avg（age）from Student

查询平均分在80分以上的学生的学号及其选课数。

select sno,Count(cno)

from SC

group by sno // 将学生数据按照姓名分成多组，每组是一个学生全部数据，再对每组数据分别进行操作

having avg（grade）>80;

SQL中AVG()、COUNT()、SUM()等函数对NULL值处理

1. AVG()求平均值

注意AVE()忽略NULL值，而不是将其作为“0”参与计算

2. COUNT() 两种用法

(1) COUNT(*)
对表中行数进行计数
不管是否有NULL

(2) COUNT(字段名)
对特定列有数据的行进行计数
忽略NULL值

3. SUM()

可以对单个列求和，也可以对多个列运算后求和

忽略NULL值，且当对多个列运算求和时，如果运算的列中任意一列的值为NULL，则忽略这行的记录。

例如： SUM(A+B+C)，A、B、C 为三列，如果某行记录中A列值为NULL，则不统计这行。

连接查询

175. 组合两个表（left join、using）

表1: Person

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键
表2: Address

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息(提示用left join)，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

select FirstName, LastName, City, State
from Person p left join Address a 
on p.PersonId = a.PersonId;

#等值连接且用using关键字进行简化。
#1.查询必须是等值连接。
#2.等值连接中的列必须具有相同的名称和数据类型。
select FirstName,LastName,City,state from Person left join Address USING(PersonId);

176. 第二高的薪水（ifnull (判空) ，distinct（去重），limit（输出指定行））

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1,1     #前一个1：从第二行开始（第一行从零开始） 后一个1：输出一行
                      #  或者写 LIMIT 1 OFFSET 1
        ), NULL) AS SecondHighestSalary

1179. 重新格式化部门表（聚集函数，case ）

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

查询结果格式如下面的示例所示：

Department 表：
±-----±--------±------+
| id | revenue | month |
±-----±--------±------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
±-----±--------±------+

查询得到的结果表：
±-----±------------±------------±------------±----±------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | … | Dec_Revenue |
±-----±------------±------------±------------±----±------------+
| 1 | 8000 | 7000 | 6000 | … | null |
| 2 | 9000 | null | null | … | null |
| 3 | null | 10000 | null | … | null |
±-----±------------±------------±------------±----±------------+

注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

代码：

SELECT id,

  MAX(CASE month WHEN 'Jan' THEN revenue END) AS Jan_Revenue, #max,min,avg 聚合函数都可以

  MIN(CASE month WHEN 'Feb' THEN revenue END) AS Feb_Revenue,

  AVG(CASE month WHEN 'Mar' THEN revenue END) AS Mar_Revenue,

  MIN(CASE month WHEN 'Apr' THEN revenue END) AS Apr_Revenue,

  MIN(CASE month WHEN 'May' THEN revenue END) AS May_Revenue,

  MIN(CASE month WHEN 'Jun' THEN revenue END) AS Jun_Revenue,

  MIN(CASE month WHEN 'Jul' THEN revenue END) AS Jul_Revenue,

  MIN(CASE month WHEN 'Aug' THEN revenue END) AS Aug_Revenue,

  MIN(CASE month WHEN 'Sep' THEN revenue END) AS Sep_Revenue,

  MIN(CASE month WHEN 'Oct' THEN revenue END) AS Oct_Revenue,

  MIN(CASE month WHEN 'Nov' THEN revenue END) AS Nov_Revenue,

  MIN(CASE month WHEN 'Dec' THEN revenue END) AS Dec_Revenue

FROM

  Department

GROUP BY id


#case 用法
#case exp
#when exp1 then ...A
#when exp2 then ...B
#when exp3 then ...C
#...
#else ...D
#end
#当exp的值为exp1时，则A; exp的值为exp2时，则B，exp的值为exp3是，则C......
#exp的值不满足以上条件是，则D

184. 部门工资最高的员工（in 子查询）

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
±—±------±-------±-------------+
Department 表包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+
编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
±-----------±---------±-------+
解释：

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)

177. 第N高的薪水()

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;   #提前写好 limit不支持表达式 N-1
  RETURN (
      # Write your MySQL query statement below.
      select Salary 
      from Employee 
      group by Salary
      order by Salary desc
      limit N,1
  );
END