SQL 基础

SQL 基础

文章目录

学习资源

SQL 在线学习
掘金 - mysql 排它锁之行锁、间隙锁、后码锁
cnblogs - 史上最全的select加锁分析(Mysql)

基本语句

查询（query）

select <col_name> from <table_name> where <cond>

  • col_name 可以是多个，逗号隔开
  • cond 逻辑表达式，可以是多个 用 and/or 连接，表达式可以是数值比较，或者文本匹配
select * from table <==> df
select id, name from table <==> df[["id","name"]]
select * from table where age > 10 <==> df.loc[df["age"]>10]
  • 排序 order by
  • 限制行数 limit n offset m
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;
  • Select query with unique results
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

由于DISTINCT关键字会盲目地删除重复的行，因此我们将在以后的课程中学习如何使用分组和GROUP BY子句基于特定的列丢弃重复项

数据库连接 join

  • inner join
  • left join
  • right join
  • full join
select col_name1, col_name2 from table1 [inner] join table2 on id1=id2

判断空值

... where col_name is [not] null

含有表达式的查询（select/where）

  • as 用于起别名
SELECT title, (Domestic_sales+International_sales)/1000000 as sales FROM movies join Boxoffice on id=movie_id
SELECT title as rating FROM movies join Boxoffice on id=movie_id where year%2==0

分组聚合

  • having <cond> 对分组的结果进行筛选
  • count() 统计行数
SELECT building, sum(years_employed) as sumed FROM employees group by building
SELECT count(*) FROM employees where role = "Artist"
select role, sum(years_employed) from employees group by role having role="Engineer"

查询总结

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

执行顺序：

  • from and join
  • where
  • group by
  • having
  • select
  • distinct
  • order by
  • limit/offset

插入（insert）

insert into table
(col1, col2)
values (v1, v2), 
(v3, v4)

更新数据（update）

update table
set col1=v1,
col2=v2
where cond

删除

删除行

delete from table
where cond

创建

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

修改（结构）

ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;
-------------------------------------------
ALTER TABLE mytable
DROP column_to_be_deleted;
-------------------------
ALTER TABLE mytable
RENAME TO new_table_name;

删除表

DROP TABLE IF EXISTS mytable;

索引

create index index_name on T (name);  允许重复
create unique index index_name on T (name);  不允许重复  
drop index index_name on T;  删除 index

子查询

SELECT *, …
FROM mytable
WHERE column
    IN/NOT IN (SELECT another_column
               FROM another_table);

集合运算

SELECT column, another_column
   FROM mytable
UNION / UNION ALL / INTERSECT / EXCEPT
SELECT other_column, yet_another_column
   FROM another_table
ORDER BY column DESC
LIMIT n;

日期函数

select now();  -- 2020-08-07 09:17:34
select date(now());  -- 2020-08-07
select time(now());  -- 09:20:38
select year(now(); -- 2020
select month(now());  -- 8
selct day(now());  -- 7
selct hour(now());  -- 9

case 语句

case 实现条件映射

select *, (case gender when "F" then 1 else 2 end) as sex from students;
select *, (case when score>90 then "good" else "bad" end) as mea from students;

Case 语句只返回第一个符合条件的结果，剩下的条件会被自动忽略

窗口函数

-- 聚合函数作为窗口函数
select *, sum(score) over (partition by class_id) as "xxx" from students;
select *, sum(score) over (partition by class_id order by id) as "xxx" from students;
select *, sum(score) over () as "xxx" from students;

-- 专用 窗口函数 rank()
select *, rank() over (partition by class_id order by id) as "xxx" from students;
select *, rank() over (order by id desc) as "xxx" from students;

  • 专用窗口函数，比如 rank, dense_rank, row_number 等

  • 聚合函数，如 sum. avg, count, max, min 等

其它

-- leetcode 第二高薪水
select (select distinct Salary 
from Employee 
order by Salary desc
limit 1,1) as SecondHighestSalary;

-- leetcode 第 N 高薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select (
      select distinct Salary from Employee
      order by Salary desc
      limit N,1
      ) as theNthHighestSalary
      
  );
END

-- leetcode 分数排名 采用 多重 select 子句
select Score, ( select count(distinct score) from Scores where score >= s.score ) as "Rank"
from Scores as s
order by Score desc;
-- 或者采用窗口函数
select Score, dense_rank() over (order by Score desc) as "Rank" from Scores;

MySQL

  • 基本命令
-- 启动服务
sudo service mysql start
-- 停止服务
sudo service mysql stop
-- 进入交互命令行
mysql -u root -p
-- 执行脚本
mysql -u root -p < init-test-data.sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS test;
-- 切换到test数据库
USE test;
-- 创建表
CREATE TABLE classes (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL, 
PRIMARY KEY (id)
) ENGINE InnoDB DEFAULT CHARSET=utf8;
-- 删除classes表和students表（如果存在）：
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS students;
-- 显示表的列
show columns from students;
  • 主键与外键
  • 索引
  • 数据库事务 ACID 特性
    • automic 原子性
    • consistent 一致性
    • isolation 隔离性
    • duration 持久性
  • 隔离级别 (从低到高)

  • 大多数的数据库系统的默认事务隔离级别都是：Read committed, 而MySQL的默认事务隔离级别是：Repeatable Read

  • 修改默认隔离级别: SET session TRANSACTION ISOLATION LEVEL Serializable;

    • read uncommitted 提交没有缓冲机制，事务中每条语句都是直接生效。会发生脏读（读取到已经 rollback 的数据）
    • read committed 相对与上一种来说，事务提交之后才生效。但是不可以重复读, 因为两次读取中间另一个事务可能提交
    • repeatable read 相对上一种而言，保证一个事务期间多次读取的数据不改变（应该是在事务开始的时候对数据做了缓存，之后读的是缓存），但是会发生幻读
    • serializable 所有事务只能顺序执行，不能并发。因此脏读、不可重复读、幻读都不会发生

    不可重复读发生在“快照读” （select）；而幻读发生在“当前读”（insert/update/delete）

  • mysql 查看和修改默认隔离级别

    -- 查看当前session默认级别
select @@transaction_isolation;
-- 查看全局默认级别
select @@global.transaction_isolation;

-- 修改
set [ session | global ] transaction isolation level [ read uncommitted | read committed | repeatable read | serializable ]

三大范式

  • 字段原子
  • 无部分依赖
  • 无传递依赖