浅学sql

企鹅吧
51 阅读10分钟

关系型数据库和非关系型数据库

简单来说：

关系型：

  1. 以表结构管理数据，数据存在行和列中
  2. 通过外键建立表之间的联系
  3. 使用 sql 语言操作
  4. mysql 是常见的关系型数据库

非关系型：

  1. 无固定的结构，数据可以是键值对（Key-Value）、文档（JSON、BSON）等形式
  2. 不强调数据的关联
  3. 无固定操作语言，可以是 JSON 风格的查询语法（MongoDB）或简单的命令行（Redis）等等
  4. MongoDB 是常见的非关系型数据库

本文是关系型数据库的学习记录

表

关系型数据库通过一张张的表格管理数据，通过主键唯一标识一行数据，通过外键建立表之间的关联

主键外键

主键： 一行数据的唯一标识

外键： 通过关联另一张表或自身表的主键，建立表之间的联系

一个表可以有多个主键（当一个属性不能唯一标识数据时，可以设置多个主键）

一个表可以有多个外键

案例

通过案例能清晰理解表、主键、外键的关系 image.png

该示例图中，橘色为主键，绿色为外键，可以看到主键是唯一标识数据的

表格通过外键关联，外键可以关联自身主键，如 sup_id

Works_With 表中 员工id 和 客户id 同时是主键和外键。

常用操作

sql 语言操作指令非常语义化，刚开始不熟悉时用到再搜也不是不行，主要是要有了操作的思路就行，之后再搜命令或直接问 gpt

数据库和表操作

create database mydatabase; -- 创建数据库
show databases; -- 显示数据库
drop database sql_tutorial; -- 删除数据库
use mydatabase; -- 选中数据库，需要先选中某个数据库，再对其进行相关操作

/** 
创建表，可以给属性设置条件 
not null 非空
unique 唯一
default 默认值
auto_increment 自增
unsigned 无符号，一般用于限制不能是负数
check 值检查
*/
create table student(  -- 创建表格
	student_id int primary key auto_increment comment '学生ID', -- 主键
	name varchar(20) not null comment '名称', -- 非空
	major varchar(20) unique comment '学科', -- 唯一
	age int default 8 comment '年龄', -- 默认值
	phone varchar(20) comment '电话',
  sex char(1) not null, check (sex in ('M', 'F')) comment '性别',
  teacher_id int comment '老师ID',
  constraint chk_age check (age >= 8), -- check 的另一种写法，年龄必须大于等于8
	foreign key (teacher_id) references teacher(teacher_id) on delete set null -- teacher_id 外键，关联 teacher 表的 teacher_id 
);
-- 增加表格列
alter table student add gpa decimal(3,2); -- decimal(3,2) 意思是总共三位数字，其中两位是在小数点后的，也即 `-9.99` 到 `9.99` 这个范围
-- 删除表格列
alter table student drop column gpa; 


-- 若创建时没有指定主键，可这样设置
alter table student add primary key (student_id);
/** 
若创建时没有设置外键，可这样设置
on delete set null：意思是当 student 关联的 teacher 删掉后，将其 teacher_id 设置成 null
on delete cascade：意思是当 student 关联的 teacher 删掉后，将此学生也删掉
*/
alter table student add  foreign key(teacher_id) references teacher(teacher_id) on delete set null

show tables; -- 显示所有表
describe student -- 显示某个表详情
DROP TABLE student; -- 删除表

查

以上面的公司数据库为例

常规查询

-- 查询员工表中所有数据
select * from employee;
-- 只查询某些列
select name, salary from employee;
-- 去重
select distinct sex from employee 
-- 排序，默认是升序
select * from employee order by salary asc; -- 反回员工根据工资排序，默认升序 asc 可省略
select * from employee order by salary desc;  -- 降序
select * from employee order by salary, sex; -- 先根据 salary 排序，若相同，再根据 sex 排序
-- 限制数量
select * from employee limit 3; -- 返回3条员工数据
-- 筛选
select * from employee where sex = 'F'; -- 返回所有女员工
select * from employee where sex = 'F' and name = '小黄'; -- 返回性别为女，名字为小黄的员工
select * from employee where salary > 40000; -- 返回工资大于40000的员工
select * from employee where birth_date > '1970-01-01'; -- 返回出生日期在1970-01-01之后的员工
-- 注意：不等于是 <> 不是 !=
select * from employee where `name` <> '小黑'; -- 返回名字不为小黑的员工
-- in 
select * 
from employee
where name in('小黑', '小黄'); -- 相当于 `name` = '小黑' or `name` = '小黄'，返回名字为小黑或小黄的员工
-- 条件都可以并用
select *
from employee
where sex = 'F'
order by `score`
limit 2;

聚合函数

select count(*) from employee; -- 返回员工数量
select count(sup_id) from employee; -- 返回有主管的员工的数量（sup_id有值的）
select count(*) from employee where salary > 40000; -- 返回工资大于40000的员工的数量

select avg(salary) from employee; -- 返回所有员工的平均工资
select sum(salary) from employee; -- 返回所有员工的工资总合
select max(salary) from employee; -- 返回员工表中最高的工资
select min(salary) from employee; -- 返回员工表中最低的工资

万用字符

-- “%” 代表多个字符   “_” 代表一个字符
select * from client where phone like '020%'; -- 返回电话是 020 开头的客户
select * from client where phone like '%368'; -- 返回电话是 268 结尾的客户
select * from client where phone like '%111%'; -- 返回电话中间有 111 的客户
select * from client where name like '陈%'; -- 返回姓陈的客户
select * from employee where birth_date like '_____08%'; -- 返回8月份生日的客户，前面五个下划线代表年和中划线

union

合并多个查询

select name from employee
union
select client_name from client
union
select branch_name from branch; -- 将这三个查询结果合并返回，返回员工名字、客户名字、部门名字

-- 统一属性名
select emp_id as total_id, name as total_name
from employee
union
select client_id, client_name from client; -- 将这两个查询结果合并返回，并且id统一为total_id，名字统一为total_name

-- 需要注意查询的列数目需要一致
select name, sex from employee
union
select client_name from client; -- 错误：数目不一致

join

连接多个表格

以上面的 branch 表为例，可以看到表中只有 manager_id，若这时想查询部门经理的名字，发现是没有这一列的，这时可以将 branch 表和 employee 表链接起来，这样连接后，不就有 id 又有名字了吗，由于 manager_id 是外键，关联 employee 表的 emp_id ，连接这两个表时可以以此为关联依据

select * 
from employee join branch
on emp_id = manager_id; -- 合并这两个表，并返回所有数据

-- 可以只返回某些列
select employee.emp_id, employee.name, branch.branch_name
from employee join branch
on emp_id = manager_id;

/**
以上两个语句只会查出三条，因为满足 emp_id 与 manager_id 相等的只有三条
如果需要把其他不相等的也返回，可以使用 left join 或 right join
 - left join 左边的表格无论是否满足条件都返回，右边的则需要满足条件
 - right join 右边的表格无论是否满足条件都返回，左边的则需要满足条件
**/
select employee.emp_id, employee.name, branch.branch_name
from employee left join branch
on emp_id = manager_id; -- 这里左边的表格是 employee

子查询

以某个查询的结果作为另一个查询的依据

/** 
查询研发部门的经理名字
思路：先在 branch 表中查出研发部门的经理 id，再拿此 id 在 employee 表中查询，就能拿到 name 属性
**/
select name from employee where emp_id = (
    select manager_id from branch where branch_name = '研发'
);

/** 查询销售金额超过 50000 的员工的名字 **/
select name from employee 
where emp_id in( 
    select emp_id from work_with
    where total_sales > 50000 
); -- 由于销售额大于 50000 的员工可能不止一位，所以用 in

视图

简单理解为封装一个查询语句

# 创建
CREATE VIEW view_non_null_sup_employees AS 
SELECT *
FROM employees 
WHERE sup_id IS NOT NULL;

# 执行
SELECT * FROM view_non_null_email_employees;

储存过程

简单理解为封装一个语句，可以是增删改查，可以定义参数

# 创建
DELIMITER $$

CREATE PROCEDURE update_salary(
    IN emp_id INT,          -- 员工ID（输入参数）
    IN increment_percent DECIMAL(5, 2) -- 增长百分比（输入参数）
)
BEGIN
    -- 更新员工工资
    UPDATE employees
    SET salary = salary + (salary * increment_percent / 100)
    WHERE id = emp_id;
    
    -- 检查是否更新成功
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Employee not found';
    END IF;
END $$

DELIMITER ;

# 执行
CALL update_salary(1, 10);

事务管理

事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。

假设需要实现一个转账操作：

  • 从 Alice 的账户扣除 200 元。
  • 向 Bob 的账户增加 200 元。
  • 如果任意一步失败，则回滚整个操作。
-- 开始事务
START TRANSACTION;

-- 从 Alice 的账户扣除 200 元
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

-- 检查是否成功
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    LEAVE;
END IF;

-- 向 Bob 的账户增加 200 元
UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

-- 检查是否成功
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    LEAVE;
END IF;

-- 提交事务
COMMIT;

打游戏时，一般会有一些保留点，当失败后，可以从保留点重新开始，事务管理也类似，可以创建保留点，当某个步骤遇到错误可以回退到此安全状态

假设有一个表 orders 和一个表 order_items：

  • orders：记录订单信息。
  • order_items：记录订单的商品信息。

我们需要执行以下操作：

  1. 创建订单。
  2. 插入多个商品到此订单。
  3. 如果某个订单项插入失败，回滚到订单创建后的状态，而不是回滚整个事务。
START TRANSACTION;

-- 插入订单记录
INSERT INTO orders (order_id, order_date, customer_name)
VALUES (1, '2024-12-30', 'John Doe');

-- 创建保存点
SAVEPOINT AfterOrderCreated;

-- 插入第一个订单项
INSERT INTO order_items (order_id, product_name, quantity, price)
VALUES (1, 'Product A', 2, 20.00);

-- 检查是否出错
IF ROW_COUNT() = 0 THEN
    ROLLBACK TO AfterOrderCreated;
    -- 可以选择直接回滚整个事务
    ROLLBACK;
    LEAVE;
END IF;

-- 插入第二个订单项（故意出错，例如违反约束）
INSERT INTO order_items (order_id, product_name, quantity, price)
VALUES (1, NULL, 1, 15.00); -- NULL 不允许，导致失败

-- 检查是否出错
IF ROW_COUNT() = 0 THEN
    ROLLBACK TO AfterOrderCreated;
    -- 此时事务状态恢复到插入订单后的状态
END IF;

-- 插入另一个订单项（继续操作）
INSERT INTO order_items (order_id, product_name, quantity, price)
VALUES (1, 'Product B', 3, 30.00);

-- 检查是否出错
IF ROW_COUNT() = 0 THEN
    ROLLBACK TO AfterOrderCreated;
    ROLLBACK;
    LEAVE;
END IF;

-- 提交事务
COMMIT;

游标

简单理解为循环处理数据

举例：根据以下规则调整工资：

  1. 如果绩效评分大于 80，加薪 10%。
  2. 如果评分在 60 到 80 之间，加薪 5%。
  3. 如果评分低于 60，不加薪。
  4. 记录每次加薪的日志到 salary_log 表。
# 创建
DELIMITER $$

CREATE PROCEDURE AdjustSalaries()
BEGIN
    -- 声明变量
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10, 2);
    DECLARE emp_score INT;
    DECLARE new_salary DECIMAL(10, 2);
    DECLARE done INT DEFAULT 0;

    -- 声明游标
    DECLARE cursor_emp CURSOR FOR 
    SELECT e.id, e.salary, p.score 
    FROM employees e
    JOIN performance p ON e.id = p.emp_id;

    -- 声明结束标志处理器：当游标到达结果集末尾时，将 `done` 设置为 `1`，用来结束循环。
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 打开游标
    OPEN cursor_emp;

    -- 游标循环
    read_loop: LOOP
        FETCH cursor_emp INTO emp_id, emp_salary, emp_score;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- 根据评分计算新工资
        IF emp_score > 80 THEN
            SET new_salary = emp_salary * 1.1;
        ELSEIF emp_score >= 60 THEN
            SET new_salary = emp_salary * 1.05;
        ELSE
            SET new_salary = emp_salary; -- 无变化
        END IF;

        -- 更新工资
        UPDATE employees 
        SET salary = new_salary 
        WHERE id = emp_id;

        -- 记录日志
        INSERT INTO salary_log (emp_id, old_salary, new_salary, change_date)
        VALUES (emp_id, emp_salary, new_salary, NOW());
    END LOOP;

    -- 关闭游标
    CLOSE cursor_emp;
END $$

DELIMITER ;

# 执行
CALL AdjustSalaries();

增 改 删

以此表为例

image.png

create table student(
    id int unsigned primary key auto_increment comment 'ID',
    name varchar(20) comment '名称',
    major varchar(20) comment '科目',
    score decimal(3,1) comment '分数';
)

-- 增
insert into student values(6, '张三', '数学', '100');
-- 由于 id 是自增的，所以插入时可以不传值 
insert into student (name, major, score) values ('李四', '语文', 88); 
insert into student (name, major, score) values ('王五', null, 99);

-- 改
update student
set major = '英语口语'
where major = '英语'; -- 将 英语 的改成 英语口语

update student 
set major = '生物'
where student_id = 3; -- 将 student_id 为 3 的学生的科目改成 生物

update student 
set major = '生化'
where major = '生物' or major = '化学'; -- 将 生物 和 化学 改成 生化

-- 可改多个属性
update student 
set name = '小灰', major = '物理'
where student_id = 3; -- 将 student_id 为 3 的学生的名字改成小灰，科目改成物理

-- 可以不限制条件
update student
set major = '数理化'; -- 将所有科目改成数理化

-- 删除
delete from student
where student_id = 4; -- 删除 student_id 为 4 的学生

delete from student
where score <= 60; -- 删除分数小于等于 60 的学生

参考

www.youtube.com/watch?v=gvR…

《sql必知必会》

avatar
文章
阅读
粉丝