牛客SQL刷题总结（一）

牛客SQL刷题总结

1. 使用SELECT查询创建表
   

   CREATE TABLE actor_name
   SELECT first_name,last_name FROM actor;
   

2. 创建索引
   

   CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
   CREATE INDEX idx_lastname ON actor(last_name);
   

3. 创建视图
   

   -- 方法一：
   CREATE VIEW actor_name_view AS
   SELECT first_name AS fist_name_v, last_name AS last_name_v
   FROM actor 
   
   -- 方法二：
   CREATE VIEW actor_name_view (fist_name_v, last_name_v) AS
   SELECT first_name, last_name FROM actor 
   

4. 添加列
   

   ALTER TABLE actor ADD COLUMN create_date 
   datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
   

5. 创建触发器
   

   CREATE TRIGGER audit_log AFTER INSERT ON employees_test
   BEGIN
       INSERT INTO audit VALUES (NEW.ID, NEW.NAME);
   END;
   
   /* 
   构造触发器时注意以下几点：
   1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
   2、触发器执行的内容写出 BEGIN与END 之间
   3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录
   */
   

6. 删除重复记录
   

   -- 方法一：
   DELETE FROM titles_test WHERE id NOT IN 
   (SELECT MIN(id) FROM titles_test GROUP BY emp_no)
   
   -- 方法二：使用子查询
   DELETE FROM titles_test
   WHERE emp_no IN (SELECT tt.emp_no
         FROM titles_test tt
         WHERE tt.emp_no = titles_test.emp_no 
         AND titles_test.id < tt.id);
   

7. 更新表
   

   UPDATE titles_test 
   SET to_date = NULL,
       from_date = '2001-01-01'
   WHERE to_date = '9999-01-01';
   

8. 修改表名
   

   ALTER TABLE titles_test RENAME TO titles_2017;
   

9. replace()函数
   

   -- REPLACE(str,old_string,new_string)
   -- REPLACE()函数有三个参数，它将string中的old_string替换为new_string字符串
   UPDATE titles_test 
   SET emp_no = REPLACE(emp_no,10001,10005) 
   WHERE id = 5;
   

10. substr()函数
    

    -- substr(字符串，起始位置，长度）
    -- 起始位置:截取的子串的起始位置（注意:字符串的第一个字符的索引是1）。值为正时从字符串开始位置 开始计数，值为负时从字符串结尾位置开始计数。
    -- 长度:截取子串的长度
    SELECT first_name FROM employees
    ORDER BY substr(first_name,length(first_name)-1,2)
    

11. group_concat()函数
    

    -- 使用 GROUP_CONCAT（）函数必须对源数据进行分组，否则所有数据会被合并成一行
    SELECT dept_no, group_concat(emp_no) AS employees
    FROM dept_emp 
    GROUP BY dept_no;
    

12. 关于IN或NOT IN的易错题
    

    -- 错误写法
    SELECT AVG(salary) AS avg_salary FROM salaries 
    WHERE to_date ='9999-01-01' 
    AND salary NOT IN (SELECT MAX(salary),MIN(salary) FROM salaries WHERE to_date = '9999-01-01')；
    -- 错因：SELECT MAX(salary),MIN(salary) FROM salaries WHERE to_date = '9999-01-01'这个语句最后输出的是两列，但是not in的话，只对一列的值进行操作，所以会报错。
    
    -- 正确写法：
    SELECT AVG(salary) AS avg_salary FROM salaries 
    WHERE to_date ='9999-01-01' 
    AND salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
    AND salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')；
    

13. 累计问题
    

    -- 方法一：使用窗口函数
    SELECT emp_no, salary,
    SUM(salary) OVER (ORDER BY emp_no) AS running_total
    FROM salaries
    WHERE to_date = '9999-01-01';
    
    -- 方法二：(注意看题:(啦)
    SELECT s2.emp_no,s2.salary,SUM(s1.salary) AS running_total
    FROM salaries AS s1 INNER JOIN salaries AS s2 
    ON s1.emp_no <= s2.emp_no
    WHERE 
    s1.to_date = "9999-01-01"
    AND s2.to_date = "9999-01-01"
    GROUP BY s2.emp_no;
    

14. 排名问题一
    

    -- 方法一：使用窗口函数
    
    -- 方法二：
    SELECT e1.first_name 
    FROM employees e1
    WHERE(SELECT count(*) FROM employees e2 
          WHERE e1.first_name <= e2.first_name) % 2 = 1;      
    

15. 排名问题二
    

    -- 方法一：自连接（注意对GROUP BY p1.id 的理解）
    SELECT p1.id, p1.number,
    	COUNT( DISTINCT p2.number ) rank
    FROM passing_number p1
    INNER JOIN passing_number p2 
    ON p1.number <= p2.number 
    GROUP BY p1.id 
    ORDER BY rank, p1.id;
    
    -- 方法二：使用窗口函数
    SELECT id, number,
    DENSE_RANK() OVER(ORDER BY number DESC) rank
    FROM passing_number
    ORDER BY rank, id;
    

16. 异常邮件概率问题
    
    

    SELECT date,
    ROUND(
        SUM(CASE WHEN email.type = 'completed' THEN 0 ELSE 1 END)*1.0 / COUNT(email.type),3) AS p
    FROM email
    WHERE send_id IN (SELECT id FROM user WHERE is_blacklist = 0)
    AND receive_id IN (SELECT id FROM user WHERE is_blacklist = 0)
    GROUP BY date;