1.建库

create database database_name

2.使用该数据库

use database_name

3.建表

3.1普通建表

create table if not exists actor(

        actor_id smallint(5) not null primary key comment "主键id",

        first_name varchar(45) not null comment "名字",

        last_name varchar(45) not null comment "姓氏",

        last_update date not null comment "日期"

)

该建表命令创建的表结构如图所示：

3.2 复制建表

3.2.1复制表结构+数据

create table new_table select * from old_table

3.2.2复制表结构

CREATE TABLE new_table LIKE old_table;

4.插入&删除&更新&&事务

插入数据：

insert into actor values
(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),
(2,"NICK","WAHLBERG","2006-02-15 12:34:33")

删除数据：

删除特定行

DELETE FROM table_name WHERE condition;

删除所有行

DELETE FROM table_name;

tips:在mysql中不允许在子查询的同时删除原表数据，但是sqllite可以这样做

更新数据：

假设有一个名为 employees 的表，该表有 id, name, 和 salary 三个列。

更新特定行

--ID 为 10 的员工的薪水更新为 5000。
UPDATE employees
SET salary = 5000
WHERE id = 10;

更新多个列/字段

UPDATE employees
SET salary = 5000, position = 'Manager'
WHERE id = 10;

更新多行

UPDATE employees
SET salary = salary + 500
WHERE salary < 3000;

tips：在不带 WHERE 子句的情况下使用 UPDATE 会更新表中的所有行。

事物

定义：将一系列操作作为一个单一的、不可分割的工作单元来处理。

在事务中，要么所有的操作都成功执行，要么所有的操作都不执行。事务的这种特性确保了数据库的完整性和一致性

四个基本特性（acid）：

1. 原子性（Atomicity）：事务是数据库操作的最小单位，它们要么全部完成，要么全部不执行。如果事务的一部分操作失败，整个事务将回滚到开始状态。

2. 一致性（Consistency）：事务必须使数据库从一个一致的状态转换到另一个一致的状态。这意味着事务执行的结果必须满足所有的数据库约束。

3. 隔离性（Isolation）：数据库系统提供了一定级别的隔离，以防止多个事务并发执行时相互干扰。这意味着一个事务的操作和中间状态对其他事务是不可见的。

4. 持久性（Durability）：一旦事务提交，其对数据库所做的更改就是永久性的，即使系统发生故障也不会丢失。

事务处理例子：

        假设你在银行应用中处理一笔转账，需要从账户 A 向账户 B 转移资金。这个过程可以分为两个步骤：从账户 A 扣除相应金额，向账户 B 添加相应金额。这两个步骤应该作为一个事务来处理。
      

START TRANSACTION;

-- 从账户 A 扣款
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'A123';

-- 向账户 B 存款
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'B456';

-- 检查是否有错误，如果没有则提交事务
COMMIT;

    START TRANSACTION; 语句标志着一个新事务的开始。在执行这个语句后，你可以执行一个或多个数据库操作（如 INSERT、UPDATE、DELETE 等），这些操作作为事务的一部分。在 START TRANSACTION; 之后执行的操作不会立即反映在数据库中。这些变更暂时保持在事务的上下文中，直到事务被提交或回滚。这种机制允许你在事务中执行多步操作，同时保持数据的一致性和完整性。

        COMMIT; 语句用于提交事务。这意味着自从上一个 START TRANSACTION; 以来所有的数据库更改都将被永久保存到数据库中。只有在执行 COMMIT; 之后，这些更改才对其他用户可见。

5.mysql中常用的4种插入数据的方式

5.1 insert into

insert into table1 values（）

表示插入数据，数据库会检查主键，如果出现重复会报错；

5.2 replace into

replace into table1 values（）

表示插入替换数据，需求表中有PrimaryKey，或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；

使用方式：

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

REPLACE INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

5.3 insert ignore into

insert ignore into table1 values（）

表示，如果中已经存在相同的记录，则忽略当前新数据；eg:

insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33")

5.4 从其他表导入数据

INSERT INTO new_table SELECT * FROM old_table;

SELECT 本身是一个查询语句，用于从数据库中检索数据。但是，当 SELECT 语句与 INSERT INTO 语句结合使用时，它的功能就扩展了，不仅仅是检索数据，还包括将检索到的数据插入到另一个表中。——在 SQL 中，INSERT INTO ... SELECT ... 语句允许您将一个查询的结果直接插入到一个表中。

标准操作：

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

6.数据库索引

MySQL中的索引是数据库表中一种特殊的数据结构，它可以加快数据检索速度。索引存储在磁盘上，它们包含指向表中数据行的指针。

索引一共有五种：主键索引（primary key）、唯一索引（unique）、普通索引、全文索引、组合索引。

主键（PRIMARY KEY）索引

• 每个表只能有一个主键。
• 主键列不能包含NULL值。
• 主键保证了表中每行数据的唯一性。

1.创建新表时创建主键，略。

2.已有表时，想设置某个列为主键，可以使用alter table

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

唯一（UNIQUE）索引

• 保证列中每个值的唯一性。
• 与主键不同，唯一索引可以有多个，并且可以包含NULL值（但只能有一个NULL值）。

1.创建新表时，创建唯一索引

CREATE TABLE table_name (
    column1 VARCHAR(255),
    column2 VARCHAR(255),
    UNIQUE INDEX (column1)
);

2.已有表时，想为某个列创建唯一索引

ALTER TABLE table_name
ADD UNIQUE index_name (column_name);

普通索引

• 最基本的索引，没有任何唯一性的限制。
• 可以帮助加快查询速度。

1.在建表时创建普通索引

CREATE TABLE table_name (
    column1 VARCHAR(255),
    column2 VARCHAR(255),
    INDEX (column1)
);

2.已有表时，想为某个字段创建普通索引

CREATE INDEX index_name ON table_name (column_name);

3.举例说明，普通索引与唯一索引使用情况

首先是区别，就是某个字段的唯一索引是不允许重复的❎，也就是说该字段下每一行的index值都是唯一的；

但是某个字段的普通索引是允许重复的✅，也就是说该字段下每一行的index值是可以不唯一的；什么情况下需要不唯一的索引呢？

——比如，多个员工可能属于同一个部门，因此 department 列中会有重复的值。普通索引会索引这些值，使得我们可以快速根据部门检索员工，但它并不强制每个部门值都是唯一的；相比之下，如果是唯一的索引，那么两个员工是两行数据，同属一个部门，但是部门的索引由于唯一性的限制，却是不同的，那么数据库就没法根据这个索引来加快查找同一部门的员工了，就失去了意义。

全文（FULLTEXT）索引

• 专门用于全文搜索。
• 只有Char、VarChar和Text类型的列才能创建全文索引。
• 全文索引通过创建一个特殊的索引类型，其中包含文本数据的所有单词的列表，以及它们在文本数据中出现的位置。当执行全文搜索查询时，数据库可以快速查找包含特定单词或短语的行。

1.创建表时创建全文索引

CREATE TABLE table_name (
    column1 TEXT,
    FULLTEXT (column1)
);

2.已经有表时，创建全文索引

ALTER TABLE table_name
ADD FULLTEXT index_name (column_name);

3.使用全文索引进行搜索

要利用全文索引进行搜索，可以使用 MATCH ... AGAINST 语法：这个查询会在 articles 表的 content 列中搜索包含 'some search term' 的记录。

SELECT title, content FROM articles
WHERE MATCH(content) AGAINST('some search term');

组合索引

• 在表的多个列上建立的索引。
• 可以覆盖查询中涉及的多个列。

1.创建表时创建组合索引

CREATE TABLE table_name (
    column1 VARCHAR(255),
    column2 VARCHAR(255),
    column3 DATE,
    INDEX (column1, column2)
);

2.已经有表时，创建组合索引

CREATE INDEX index_name ON table_name (column1, column2);

索引声明写法辨析

在创建表时，创建索引的写法并不局限于将索引定义放在表定义的最后。实际上，可以在定义每个列的同时直接指定某些类型的索引，尤其是主键（PRIMARY KEY）和唯一约束（UNIQUE）。这通常用于确保数据的完整性和唯一性。例如：

CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100)
);

对于普通索引、全文索引、组合索引，则是一般要在最后声明

索引意义

        给一个字段加了索引之后，查询操作不需要任何改变；仍然像之前一样编写 SELECT 语句。索引是在后台工作的，用户通常不会直接与其交互。数据库管理系统会自动决定是否使用索引来优化查询。

数据库优化器会根据查询条件、使用的索引类型、表中数据的统计信息以及可能还有其他因素，来决定是否使用索引以及如何使用索引。如果优化器认为使用索引可以提高查询效率，它会自动应用索引来检索数据。

索引原理

1.数据结构

数据库索引通常使用高效的数据结构，如 B-树（最常见）、B+树、哈希表等。这些数据结构优化了数据检索的速度。在B+树中，所有的数据都存储在叶子节点（通常包含指向实际数据的指针），而内部节点只存储键值，这使得B+树更适合用于数据库索引。

2.提高检索效率

索引大大减少了数据库必须检查的数据量。没有索引，DBMS 可能必须执行全表扫描，即逐行查看表中的每一行，直到找到所需的数据。索引使得DBMS可以快速定位到表的特定部分，从而只检查少量数据。

3.维护成本和插入、删除和更新成本变高

虽然索引可以显著提高查询效率，但它们也有维护成本。当表中的数据被添加、删除或修改时，索引也必须更新。这意味着写操作（INSERT、DELETE、UPDATE）可能会因为索引而变慢。

同时，索引还需要额外的存储空间。

强制索引*

在MySQL中，强制索引（Force Index）是一种优化技术，可以在查询时指示数据库使用特定的索引。这在数据库查询优化器未能选择最佳索引时非常有用。使用FORCE INDEX可以指导查询优化器使用特定索引，这可能会提高查询性能。（可能是因为mysql优化器可能在有多个索引时选择的索引不是最优的，是否最优需要通过在执行缓慢的sql前加上 explain 关键字，查看执行计划）

强制索引写法：

如果有一个名为table_a的表和一个索引名为my_index，这样使用FORCE INDEX：

SELECT * 
FROM table_a FORCE INDEX (my_index)  --注意是在from表的时候就需要指定index
WHERE column_name = 'value';

这将强制MySQL使用my_index索引来执行查询。这种方法应谨慎使用，因为它会覆盖MySQL优化器的索引选择，只有在你确信某个索引比优化器自动选择的更有效时才推荐使用。

7.视图（view）

什么是视图

视图（View）是基于SQL查询的虚拟表，主要用于简化复杂的SQL查询和提高数据访问的安全性。视图不直接存储数据，而是存储SQL查询。它看起来和实际的表一样，可以包含特定的行和列，但这些数据来自于基础表。

视图的作用

视图的主要目的包括：

• 简化查询：对于复杂的SQL查询，可以创建一个视图，这样用户就可以通过简单查询视图来获取数据（像查询普通表一样查询视图），而不需要每次都执行复杂的查询。
• 安全性：视图可以用来限制对基础数据的访问，确保用户只能看到他们被授权访问的数据。
• 数据抽象：用户可以使用视图而不需要关心数据是如何存储和计算的。

查看方式

既然view是一种虚拟表，那么也就意味着，使用sql命令时，使用show tables的命令时，结果是基础表和视图的综合

show tables

如果要准确区分基础表和视图需要用如下命令：

show full tables    

SHOW FULL TABLES命令提供了更多的信息，包括每个表是视图还是真实的表。在结果中，它添加了一个TABLE_TYPE字段，这个字段的值可以是'BASE TABLE'（对于普通表）或者'VIEW'（对于视图）。这使得用户能够区分显示的列表中哪些是实际的表格，哪些是视图。如果只使用SHOW TABLES，这个区分是不可见的。

查询基础表：

SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';

查询视图：
 

SHOW FULL TABLES WHERE Table_type = 'VIEW';

创建视图

CREATE VIEW employee_view AS
SELECT emp_id, name, position
FROM employees
WHERE department = 'Sales';

查询视图

SELECT * FROM employee_view;

修改或更新视图

CREATE OR REPLACE VIEW employee_view AS
SELECT emp_id, name
FROM employees;

删除视图

DROP VIEW employee_view;

8.修改表结构

新增字段/列

在表最后一列后面增加

alter table table_name
add column column_name ...

--例如，增加create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'
alter table actor
add column create_date datetime not null default "2020-10-01 00:00:00"

在指定位置增加列

alter table table_name
add column column_name ... after [列名]

在第一列增加

alter table table_name
add column column_name ... first

创建外键约束

ALTER TABLE table_a
ADD CONSTRAINT FOREIGN KEY (column_in_a)
REFERENCES table_b（column_in_b）

外键类型说明：

现在的这个例子只是给table_a的一个字段加上了外键约束，并且引用了table_b的一个字段，

1.如果再给table_b的这个字段加上外键约束，并且引用table_a的那个字段且两字段都唯一，那么创建的这两个外键约束就会使得这个关系变成一对一关系

2.如果table_b中的这个字段是唯一的话，就已经创建了一对多的关系

3.实现多对多关系：假设有两个表 students 和 courses，表示学生和课程，一个学生可以注册多门课程，一门课程可以被多个学生注册。为了表示这种关系，可以创建一个名为 student_courses 的关联表。

第一步：创建原始表

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

第二步：创建关联表

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

说明：在 student_courses 表中

• student_id 和 course_id 是外键，分别指向 students 和 courses 表。
• (student_id, course_id) 对作为主键，确保每个学生和课程的组合是唯一的。

常见修改表结构语句

ALTER TABLE 表名 ADD 列名/索引/主键/外键等；
ALTER TABLE 表名 DROP 列名/索引/主键/外键等；
ALTER TABLE 表名 ALTER 仅用来改变某列的默认值；
ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名；
ALTER TABLE 表名 RENAME TO/AS 新表名;
ALTER TABLE 表名 MODIFY 列的定义但不改变列名；
ALTER TABLE 表名 CHANGE 列名和定义都可以改变。

9.触发器

触发器的定义

触发器是一种特殊类型的存储过程，它不同于存储过程，它主要是通过事件触发而被执行的，即不是主动调用而执行的；它在满足某种条件下会自动执行。

触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变的时候，系统会自动触发代码并执行；触发器可以在下列情况发生时被触发执行：

• INSERT：当数据被插入到表中时。
• UPDATE：当表中的数据被更新时。
• DELETE：当表中的数据被删除时。
   

创建触发器的基本语法：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

示例：

假设有两个表：orders（订单表）和order_audit（订单审计表）。每当orders表中插入新的订单时，我们想在order_audit表中添加一条记录来记录这个事件。（如果触发器只包含一条语句，可以省略 BEGIN 和 END，但为了代码的清晰和一致性，即使只有一条语句，使用 BEGIN 和 END 也是一个好习惯。）

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders FOR EACH ROW
BEGIN
    --触发逻辑
    INSERT INTO order_audit (order_id, audit_action)
    VALUES (NEW.id, NEW.action);    --注意这个地方要加封号
END; --这个地方也要加封号

⚠️注意⚠️

• 在 INSERT 触发器中，NEW 用于访问新插入行的列。
• 在 UPDATE 触发器中，NEW 用于访问更新后的行的列，而 OLD 用于访问更新前的行的列。

x