SQL

未分类
4k 词

查缺补漏

刷题

从第三行数据开始，只取一行
1
2
SELECT * FROM employees ORDER BY hire_date DESC
LIMIT 1 OFFSET 2
左连接
1
2
3
4
select last_name,first_name,dept_no
from employees s
left join dept_emp d
on s.emp_no = d.emp_no
  • 获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列
1
2
3
4
5
6
7
8
9
10
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_emp d
JOIN salaries s ON d.emp_no = s.emp_no
WHERE s.salary = (
    SELECT MAX(s1.salary)
    FROM dept_emp d1
    JOIN salaries s1 ON d1.emp_no = s1.emp_no
    WHERE d1.dept_no = d.dept_no
)
ORDER BY d.dept_no ASC;
  • 可以连续left join
1
2
3
4
SELECT e.last_name, e.first_name, d.dept_name
FROM employees e
LEFT JOIN dept_emp de ON e.emp_no = de.emp_no
LEFT JOIN departments d ON de.dept_no = d.dept_no;
获取第二多

  • 用avg之类的函数，需要聚类

  • 请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。

1
2
3
4
5
6
7
8
9
10
11
12
13
- 一个要小心的点就是：可能第一个有多个，所以要先聚合分组

select emp_no,salary
from salaries
where salary = 
(
select salary
from salaries
group by salary
order by salary desc
limit 1 offset 1
)
order by emp_no asc
  • 不能使用order by的情况下思路：先选出小于最大值的，再在其中选出最大值进行匹配
mysql窗口函数

参考CSDN

博客园 个人感觉会讲的更清楚

注意：窗口函数在mysql8.0以上支持

  • 对所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列
1
2
3
4
5
6
7
8
- 使用窗口函数

select 
    emp_no,
    salary,
    DENSE_RANK() over(order by salary desc) t_rank
from salaries
order by t_rank asc,emp_no asc
1
2
3
4
5
6
7
8
9
10
11
12
- 不使用窗口函数

SELECT 
    s1.emp_no, 
    s1.salary,
    (
        SELECT COUNT(DISTINCT s2.salary)
        FROM salaries s2
        WHERE s2.salary >= s1.salary
    ) AS t_rank
FROM salaries s1
ORDER BY s1.salary DESC, s1.emp_no ASC;

补充：在运行顺序中，select字句是最后被运行的。
所以，如果有想要先执行的可以放在子查询当中，再来用它。
alt text

各种连接的区别和选择

详见链接

该链接讲的很好，用文氏图更易懂。

mysql8.0

上 下

mysql最新为8.0版本，我们来看下有哪些需要了解的实用特性。大多数是取数人员用不到的，我只看关于查询、建表会用到的。

  • group by不再隐式排序，需要人为加order by
  • 加入窗口函数
  • 在 MySQL 8.0.29 之前，一列只能作为表的最后一列添加。不支持将列添加到其他列中的任何其他位置。从 MySQL 8.0.29 开始，可以将即时添加的列添加到表中的任何位置。
concat

CONCAT(str1,str2,…)字符串连接

1
2
3
4
5
mysql> SELECT CONCAT('张三','李四','王五');
result> 张三李四王五
//——————————————————————————————————————————————
mysql> SELECT CONCAT('张三','李四',NULL);
result> NUll
建表

我的天 一直忘记

1
2
3
4
5
6
7
8
9
10
CREATE TABLE actor
(
    actor_id smallint(5) not null PRIMARY KEY,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update date not null
);

-- 判断有无表，防止报错
CREATE TABLE IF NOT EXISTS actor
修改（ALTER）

详见

alter table

略见

alt text

建表和修改默认值（DEFAULT）

  • default 用于没有指定数值的情况下，设置默认值。ALTER 和 CREATE 都可以用，SELECT 不能用。
    参考示例

  • after 用于指定在某个后面

1
2
3
alter table actor
add column create_date datetime not null 
default '2020-10-01 00:00:00' after last_update
日期

alt text

DATETIME、 TIMESTAMP、DATE、TIME、YEAR

DATETIME：表示年月日时分秒，记录年份长，DATE和TIME的组合
TIMESTAMP：表示年月日时分秒，记录年份短；会根据所在时区进行切换，表中新插入的日期自动设置为当前系统时间，第二个列默认值为0000-00-00 00:00:00
DATE：表示年月日
TIME：表示时分秒
YEAR：年份，默认四位，用1bytes足够。

mysql一些莫名的规范
1
2
3
4
-- 需要有分号

insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33');
insert into actor values(2,'NICK','WAHLBERG','2006-02-15 12:34:33');
  • 转义字符 \
插入数据
1
2
3
4
5
6
7
# mysql中常用的三种插入数据的语句: 
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错； 
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样； 
# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");
删除数据
  • 删除emp_no重复的记录，只保留最小的id对应的记录
1
2
3
4
5
6
7
8
9
DELETE FROM titles_test
WHERE id NOT IN (
SELECT * FROM
(
    SELECT
    MIN(id)
    FROM titles_test
    GROUP BY emp_no
) as temp);
创建索引

索引

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

alt text

所以，索引一般针对where内的，需要order by或者group by的。但也小心需要经常增删改的数据，这会导致索引的维护。

使用索引
  • 使用强制索引
1
2
3
4
select *
from salaries
force index (idx_emp_no)
where emp_no=10005

使用强制索引的原因：因为MYSQL优化器优化后使用的索引未必是最优的，当优化器指定的索引影响查询速度时用强制索引可以用来提高查询速度。

示例文档

创建视图

视图创建后，可以像操作表一样操作视图，主要是查询操作。对应的表被称作基表。

1
2
3
CREATE VIEW actor_name_view AS
SELECT first_name first_name_v,last_name last_name_v
FROM actor
触发器

我服了，全忘光了。。真是的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- mysql的触发器创建方式略微不同

CREATE TRIGGER audit_log
AFTER INSERT ON employees_test
FOR EACH ROW
BEGIN
    INSERT INTO audit (EMP_no, NAME)
    VALUES (NEW.ID, NEW.NAME);
END;

# 具体地：

# 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
# 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
# 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
# 使用方法： NEW.columnName （columnName 为相应数据表某一列名）

# 触发器有三种：INSERT、UPDATE、DELETE，还分为BEFORE和AFTER
留言