sql语言

发表于 更新于 分类于 阅读次数：

DDL与DML

DDL: maintaining structure of database. (CREATE, DROP, ALTER, RENAME)

DML: maintaining contents of database. (SELECT, INSERT, DELETE, UPDATE)

sql language:

1578825616684

INSERT

1578900159995

注：字符为单引号

INSERT INTO tablename {column1,column2,…}values(exp1,exp2,…); //插入

DELETE

1578900315795

UPDATE

1578900364614

CREATE

1578899962419

CREATE的时候，先CREATE没有外键的表，最后CREATE有外键的表

假设我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。这个表的建表语句是：

1
2
3
4
5
6
create table T (
    id int primary key,
    k int not null,
    name varchar(16),
    index (k)
) ENGINE=InnoDB;

DROP

1578900950689

DROP的时候，先DROP有外键的表，最后DROP没有外键的表

ALTER

例如增加一列

1578901179360

RENAME

1578901685944

语法顺序与执行顺序

1578825674160
1578825736061

相当于就是 SELECT [DISTINCT] 的顺序换了一下。

Aggregate Functions

1596791792580

操作符

1596791382343

找最大值

例一：哪一个物品有最高的cost?

1596791476146

If two different items have highest price, 第一个query会把两个都返回，第二个query只会返回一个

例二：找出有最多空房间的楼层

http://sqlfiddle.com/#!9/492887/22

1597394612354 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
  blockfloor as "Floor",
  count(*) AS  "No of available rooms"
FROM room
WHERE unavailable='0'
GROUP BY blockfloor
HAVING count(*) =
  (SELECT max(zz) AS highest_total
   FROM
     ( SELECT blockfloor ,
              count(*) AS zz
      FROM room
      WHERE unavailable='0'
      GROUP BY blockfloor ) AS t );

注：以下写法错误：

1
2
3
4
5
6
7
8
9
SELECT BlockFloor, COUNT(*) AS "maximum number of rooms available"
FROM Room
WHERE Unavailable=0
GROUP BY BlockFloor
HAVING COUNT(*) = 
	(SELECT MAX(COUNT(*))
	FROM Room
	WHERE Unavailable = 0
	GROUP BY BlockFloor);
1597394696134

例三：找出第二高的薪水

https://leetcode.com/problems/second-highest-salary/

1599193947629

如何避免有多个最大值的情况：使用DISTINCT：Sort the distinct salary in descend order，再用LIMIT和OFFSET

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 法一：Using sub-query and LIMIT clause
SELECT
    (SELECT DISTINCT Salary
     FROM Employee
     ORDER BY Salary DESC
     LIMIT 1 OFFSET 1) AS SecondHighestSalary;

-- 法二：Using IFNULL and LIMIT clause: 
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
       LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary;

注：如果直接写：

1
2
3
4
5
6
SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

this solution will be judged as 'Wrong Answer' if there is no such second highest salary since there might be only one record in this table.

1599194162675

例四：找出每门课分数都大于75的学生中，分数最高的那条记录

http://sqlfiddle.com/#!9/ee6ee5/1

1600917047680

输出：

1600917082998 
1
2
3
4
5
6
7
8
9
SELECT *
FROM table1
WHERE score = 
  (SELECT max(max_score)
  FROM 
    (SELECT max(Score) AS max_score
    FROM table1
    GROUP BY name
    HAVING min(score)>75) p);

(👆不确定是否是正确答案)

注：

1
2
3
4
SELECT max(Score)
FROM table1
GROUP BY name
HAVING min(score)>75

输出的结果是满足条件的每个学生各自的最高分数：

1600917356859

所以以下写法错误：

1
2
3
4
5
6
7
SELECT *
FROM table1
WHERE score in #这里更不能用等于
    (SELECT max(Score)
    FROM table1
    GROUP BY name
    HAVING min(score)>75);
1600917489922
1600917511295