【MySQL】上来就干!

CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.插入数据

INSERT INTO table_name (name, age, email) VALUES ('John Doe', 30, 'john.doe@example.com');

5.查询数据

SELECT * FROM table_name;
SELECT name, email FROM table_name WHERE age > 25;
SELECT * FROM table_name ORDER BY created_at DESC;

6.更新数据

UPDATE table_name SET age = 31 WHERE name = 'John Doe';

7.删除数据

DELETE FROM table_name WHERE name = 'John Doe';

8.删除表

DROP TABLE table_name;

9.删除数据库

DROP DATABASE database_name;

10.条件查询

SELECT * FROM table_name WHERE age BETWEEN 20 AND 40;
SELECT * FROM table_name WHERE name LIKE 'J%';
SELECT * FROM table_name WHERE email IS NOT NULL;

11.聚合函数

SELECT COUNT(*) FROM table_name;
SELECT AVG(age) FROM table_name;
SELECT MAX(age) FROM table_name;

12.分组查询

SELECT age, COUNT(*) FROM table_name GROUP BY age;
SELECT age, COUNT(*) FROM table_name GROUP BY age HAVING COUNT(*) > 1;

13.连接查询

SELECT a.name, b.order_id 
FROM customers a 
JOIN orders b ON a.id = b.customer_id;

三.多表查询语法

多表查询通常涉及JOIN操作或子查询，以下是几种常见的多表查询语法示例。

1.内连接（INNER JOIN）

返回两个表中匹配的行。

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;

2.左连接（LEFT JOIN）

返回左表的所有行，即使右表没有匹配。

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;

3.右连接（RIGHT JOIN）

返回右表的所有行，即使左表没有匹配。

SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_column = b.common_column;

4.全外连接（FULL OUTER JOIN）

返回左右表的所有行，没有匹配的显示为NULL。

SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_column = b.common_column;

5.交叉连接（CROSS JOIN）

返回两个表的笛卡尔积。

SELECT a.column1, b.column2
FROM table1 a
CROSS JOIN table2 b;

6.自连接（SELF JOIN）

同一表连接自身。

SELECT a.column1, b.column2
FROM table1 a, table1 b
WHERE a.common_column = b.common_column;

7.子查询

在WHERE或FROM子句中使用子查询。

SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

SELECT a.column1
FROM (SELECT column1 FROM table1 WHERE condition) a;

8.多表连接

连接三个或更多表。

SELECT a.column1, b.column2, c.column3
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column
INNER JOIN table3 c ON b.common_column = c.common_column;

9.使用UNION合并结果集

合并多个SELECT的结果（列数和类型需匹配）。

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

10.使用EXISTS子查询

四. MySQL完整性约束类型语法

MySQL提供了多种完整性约束类型，用于确保数据库中数据的准确性和一致性。以下是常见的完整性约束类型及其语法：

1.主键约束（PRIMARY KEY）

主键约束用于唯一标识表中的每一行记录，不允许重复且不能为NULL。

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

或者使用复合主键：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    PRIMARY KEY (column1, column2)
);

2.外键约束（FOREIGN KEY）

外键约束用于确保表之间的引用完整性，一个表中的列值必须匹配另一个表的主键值。

CREATE TABLE table_name1 (
    column1 datatype PRIMARY KEY,
    column2 datatype
);

CREATE TABLE table_name2 (
    column3 datatype,
    column4 datatype,
    FOREIGN KEY (column3) REFERENCES table_name1(column1)
);

3.唯一约束（UNIQUE）

唯一约束确保列中的所有值都是唯一的，但允许NULL值。

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype
);

或者对多列设置唯一约束：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    UNIQUE (column1, column2)
);

4.非空约束（NOT NULL）

非空约束确保列中的值不能为NULL。

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype
);

5.检查约束（CHECK）

检查约束用于限制列中的值必须满足特定条件。

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    CHECK (column1 > 0)
);

6.默认约束（DEFAULT）

默认约束为列指定默认值，当插入数据时未提供该列的值时使用默认值。

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype
);

7.自增约束（AUTO_INCREMENT）

自增约束用于自动为列生成唯一的递增值，通常用于主键。

CREATE TABLE table_name (
    column1 INT AUTO_INCREMENT PRIMARY KEY,
    column2 datatype
);

8.添加约束到现有表

可以通过ALTER TABLE语句向现有表添加约束。

ALTER TABLE table_name ADD PRIMARY KEY (column1);
ALTER TABLE table_name ADD FOREIGN KEY (column2) REFERENCES other_table(column1);
ALTER TABLE table_name ADD UNIQUE (column3);
ALTER TABLE table_name MODIFY column4 datatype NOT NULL;
ALTER TABLE table_name ADD CHECK (column5 > 0);
ALTER TABLE table_name ALTER column6 SET DEFAULT default_value;

9.删除约束

可以通过ALTER TABLE语句删除约束。

ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;
ALTER TABLE table_name DROP INDEX constraint_name;
ALTER TABLE table_name MODIFY column1 datatype NULL;
ALTER TABLE table_name ALTER column2 DROP DEFAULT;

注意以下属于拓展知识点（比较难，且使用场景比较少，但是流程控制和事务控制，需要掌握）

五，数据库索引和视图

1.创建索引的基本语法如下，适用于大多数关系型数据库（如MySQL、PostgreSQL、Oracle等）：

CREATE INDEX index_name ON table_name (column1, column2, ...);

2.创建唯一索引（确保列中的值唯一）：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

3.删除索引：

DROP INDEX index_name ON table_name;  -- MySQL语法
DROP INDEX index_name;                -- PostgreSQL/Oracle语法

1.创建视图的基本语法：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

2.创建可更新视图（某些数据库支持）：

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition
WITH CHECK OPTION;

3.删除视图：

DROP VIEW view_name;

1.具体语法

1.创建复合索引：

CREATE INDEX idx_customer_name ON customers (last_name, first_name);

2.创建基于多表的视图：

CREATE VIEW order_details AS
SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

2.注意事项

索引会增加写入操作的开销，但能显著提高查询性能。视图不存储实际数据，只是保存的查询语句，每次访问视图时都会执行底层查询。

3.数据备份与恢复

定期备份是数据安全的重要措施：

mysqldump -u root -p test > backup.sql
mysql -u root -p test < backup.sql

六.MySQL 流程控制语句语法

MySQL 提供了多种流程控制语句，用于在存储过程、函数和触发器中实现条件判断和循环控制。以下是常见的流程控制语句及其语法。

1. IF 语句

IF 语句用于条件判断，语法如下：

IF condition THEN
    statements;
ELSEIF condition THEN
    statements;
ELSE
    statements;
END IF;

示例：

IF score > 90 THEN
    SET grade = 'A';
ELSEIF score > 80 THEN
    SET grade = 'B';
ELSE
    SET grade = 'C';
END IF;

2. CASE 语句

CASE 语句用于多分支条件判断，语法如下：

CASE case_value
    WHEN value1 THEN statements;
    WHEN value2 THEN statements;
    ...
    ELSE statements;
END CASE;

或者：

CASE
    WHEN condition1 THEN statements;
    WHEN condition2 THEN statements;
    ...
    ELSE statements;
END CASE;

示例：

CASE grade
    WHEN 'A' THEN SET remark = 'Excellent';
    WHEN 'B' THEN SET remark = 'Good';
    ELSE SET remark = 'Average';
END CASE;

3. WHILE 循环

WHILE 循环用于在条件为真时重复执行语句块，语法如下：

WHILE condition DO
    statements;
END WHILE;

示例：

WHILE counter < 10 DO
    SET counter = counter + 1;
END WHILE;

4. REPEAT 循环

REPEAT 循环用于重复执行语句块，直到条件为真，语法如下：

REPEAT
    statements;
UNTIL condition
END REPEAT;

示例：

REPEAT
    SET counter = counter + 1;
UNTIL counter >= 10
END REPEAT;

5. LOOP 循环

LOOP 循环用于无限循环，通常需要配合 LEAVE 语句退出循环，语法如下：

[loop_label:] LOOP
    statements;
    IF condition THEN
        LEAVE loop_label;
    END IF;
END LOOP;

示例：

loop1: LOOP
    SET counter = counter + 1;
    IF counter >= 10 THEN
        LEAVE loop1;
    END IF;
END LOOP;

6. ITERATE 语句

ITERATE 语句用于跳过当前循环的剩余部分，进入下一次循环，语法如下：

ITERATE label;

示例：

loop1: LOOP
    SET counter = counter + 1;
    IF counter MOD 2 = 0 THEN
        ITERATE loop1;
    END IF;
    IF counter >= 10 THEN
        LEAVE loop1;
    END IF;
END LOOP;

7. LEAVE 语句

LEAVE 语句用于退出循环或程序块，语法如下：

LEAVE label;

示例：

loop1: LOOP
    SET counter = counter + 1;
    IF counter >= 10 THEN
        LEAVE loop1;
    END IF;
END LOOP;

8.注意事项

流程控制语句通常用于存储过程、函数或触发器中，不能在普通 SQL 查询中直接使用。
使用循环时需确保有退出条件，避免无限循环。
变量需提前声明，才能在流程控制语句中使用。

以上是 MySQL 中常用的流程控制语句语法和示例，可根据实际需求灵活组合使用。

七. MySQL权限管理概述

MySQL权限管理用于控制用户对数据库、表、列等对象的访问权限，主要包括用户创建、权限分配及权限回收等操作。权限系统基于账户和权限表（如mysql.user、mysql.db等）实现。

1.用户管理

创建用户
语法如下：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username：用户名。
host：允许访问的主机（%表示任意主机，localhost表示本地）。
password：用户密码（可选，但建议设置）。

删除用户

DROP USER 'username'@'host';

修改密码

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

2.权限分配

基本语法

GRANT permission_type ON database.object TO 'username'@'host';

permission_type：如SELECT、INSERT、ALL PRIVILEGES等。
database.object：权限作用范围（如*.*表示所有库表，mydb.*表示特定库）。

常见权限类型

数据操作：SELECT、INSERT、UPDATE、DELETE。
结构操作：CREATE、ALTER、DROP。
管理权限：GRANT OPTION、PROXY。

示例
授予用户对mydb库的所有权限：

GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';

3.权限回收

撤销权限

REVOKE permission_type ON database.object FROM 'username'@'host';

示例：

REVOKE SELECT ON mydb.* FROM 'user'@'localhost';

4.查看权限

查看用户权限

SHOW GRANTS FOR 'username'@'host';

查询权限表
直接查看系统表：

SELECT * FROM mysql.user WHERE User='username';

5.权限生效

刷新权限
修改权限后需执行：

FLUSH PRIVILEGES;

6.安全建议

最小权限原则：仅授予必要的权限。
限制主机范围：避免使用%允许所有远程连接。
定期审计：通过SHOW GRANTS检查权限分配。
避免使用root：日常操作使用普通账户。

7.其他注意事项

权限层级：全局（*.*）、库级（db.*）、表级（db.table）、列级。
权限继承：全局权限覆盖库级权限。
权限表结构：mysql.user存储全局权限，mysql.db存储库级权限。

八. MySQL事务的基本概念

事务是数据库操作的最小逻辑单元，保证一组操作要么全部成功，要么全部失败。事务的四大特性（ACID）：

原子性（Atomicity）：事务是一个不可分割的整体，要么全部执行，要么全部回滚。
一致性（Consistency）：事务执行前后，数据库从一个一致状态转变为另一个一致状态。
隔离性（Isolation）：多个事务并发执行时，事务之间相互隔离，互不干扰。
持久性（Durability）：事务提交后，其对数据库的修改是永久性的。

1.MySQL事务的实现

通过以下语句控制事务：

START TRANSACTION;  -- 开启事务
COMMIT;            -- 提交事务
ROLLBACK;          -- 回滚事务

默认情况下，MySQL的自动提交（autocommit）是开启的，每条SQL语句都会自动提交。可通过以下命令关闭：

SET autocommit = 0;  -- 关闭自动提交

2.事务的隔离级别

MySQL支持四种隔离级别，解决并发事务引发的数据一致性问题：

读未提交（Read Uncommitted）：事务可以读取其他事务未提交的数据，可能导致脏读、不可重复读、幻读。
读已提交（Read Committed）：事务只能读取其他事务已提交的数据，避免脏读，但可能出现不可重复读和幻读。
可重复读（Repeatable Read）（MySQL默认级别）：确保同一事务内多次读取同一数据的结果一致，避免脏读和不可重复读，但可能存在幻读。
串行化（Serializable）：最高隔离级别，事务串行执行，避免所有并发问题，但性能最低。

设置隔离级别：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3.并发控制问题及解决方案

脏读（Dirty Read）：事务读取了其他事务未提交的数据。通过读已提交隔离级别解决。
不可重复读（Non-Repeatable Read）：同一事务内多次读取同一数据，结果不一致。通过可重复读隔离级别解决。
幻读（Phantom Read）：同一事务内多次查询同一范围的数据，结果集不一致（新增或删除行）。通过串行化或**间隙锁（Gap Lock）**解决。

4.锁机制

MySQL通过锁实现并发控制，主要分为两类：

共享锁（Shared Lock, S锁）：允许事务读取数据，其他事务可以加共享锁但不能加排他锁。
```
SELECT ... LOCK IN SHARE MODE;
```
排他锁（Exclusive Lock, X锁）：允许事务修改数据，其他事务不能加任何锁。
```
SELECT ... FOR UPDATE;
```

5.锁的粒度：

表锁：锁定整张表，开销小但并发度低。
行锁：锁定单行数据，开销大但并发度高（InnoDB支持）。

6.死锁与处理

死锁指多个事务互相等待对方释放锁，导致无限阻塞。MySQL通过以下方式处理：

死锁检测：自动检测并回滚其中一个事务。
设置超时：通过innodb_lock_wait_timeout参数控制锁等待超时时间。
避免死锁的方法：
- 按固定顺序访问表和行。
- 减少事务持有锁的时间。
- 使用较低的隔离级别（如读已提交）。

7.事务的保存点（Savepoint）

保存点允许事务部分回滚到指定点，而非全部回滚。

SAVEPOINT savepoint_name;  -- 创建保存点
ROLLBACK TO savepoint_name; -- 回滚到保存点
RELEASE SAVEPOINT savepoint_name; -- 释放保存点

8.性能优化建议

尽量缩短事务的执行时间，减少锁的持有时间。
避免长事务，必要时拆分大事务为多个小事务。
根据业务需求选择合适的隔离级别，避免过度使用串行化。
合理使用索引，减少锁冲突。

九. MySQL 数据库备份与还原

1.备份方法

逻辑备份（导出数据为 SQL 文件） 使用 mysqldump 工具进行逻辑备份，适合小型数据库或需要跨版本迁移的场景。
基本语法：

mysqldump -u [用户名] -p[密码] [数据库名] > [备份文件路径].sql

备份所有数据库：

mysqldump -u root -p --all-databases > alldb_backup.sql

备份指定数据库的特定表：

mysqldump -u root -p dbname table1 table2 > tables_backup.sql

物理备份（直接复制数据文件） 直接复制 MySQL 的数据目录（如 /var/lib/mysql），适用于大型数据库，但需确保数据库服务已停止或处于锁定状态。
锁定表后备份：

FLUSH TABLES WITH READ LOCK;

备份完成后解锁：

UNLOCK TABLES;

二进制日志备份 启用二进制日志（binlog）可实现增量备份。需在 my.cnf 中配置：

log_bin = /var/log/mysql/mysql-bin.log

通过 mysqlbinlog 工具解析 binlog：

mysqlbinlog mysql-bin.000001 > binlog_backup.sql

2.还原方法

逻辑备份还原 通过 mysql 客户端导入 SQL 文件：

mysql -u root -p [数据库名] < [备份文件路径].sql

若备份包含创建数据库语句，可省略数据库名：

mysql -u root -p < alldb_backup.sql

物理备份还原 停止 MySQL 服务后，将备份的数据文件复制回原目录，并确保权限正确：

systemctl stop mysql
cp -R /backup/mysql /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

基于时间点恢复 结合全量备份和 binlog 实现精确恢复：

mysqlbinlog --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-02 00:00:00" mysql-bin.000001 | mysql -u root -p

3.自动化备份脚本示例

使用 Shell 脚本定时备份（需配置 crontab）：

#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/path/to/backup"
MYSQL_USER="root"
MYSQL_PASSWORD="yourpassword"

mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases > $BACKUP_DIR/alldb_$DATE.sql
find $BACKUP_DIR -type f -mtime +7 -delete

4.注意事项

备份前验证磁盘空间是否充足。
定期测试备份文件的可用性。
敏感数据备份建议加密存储。
大型数据库可结合 --single-transaction 参数避免锁表（仅限 InnoDB）。

ok你学废了吗哈哈哈哈哈哈哈哈哈.................................................................................................

没有就再学点

十. 数据库编程基础概念

数据库编程涉及通过编程语言与数据库交互，执行增删改查（CRUD）操作。核心知识点包括数据库连接、SQL语句执行、事务管理、数据安全等。

1.常见数据库类型

关系型数据库（如MySQL、PostgreSQL）使用表格结构，支持SQL语言。非关系型数据库（如MongoDB、Redis）以键值对、文档等形式存储数据，适合灵活结构。

2.SQL基础语法

SQL是数据库编程的核心语言，基本操作包括：

创建表：CREATE TABLE users (id INT, name VARCHAR(100));
插入数据：INSERT INTO users VALUES (1, 'Alice');
查询数据：SELECT * FROM users WHERE id = 1;
更新数据：UPDATE users SET name = 'Bob' WHERE id = 1;
删除数据：DELETE FROM users WHERE id = 1;

3.数据库连接方法

不同编程语言提供特定库实现数据库连接：

Python使用pymysql或sqlalchemy：

import pymysql
conn = pymysql.connect(host='localhost', user='root', password='you password', database='test')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Java通过JDBC连接：

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "you password");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

4.参数化查询与防注入

直接拼接SQL语句存在注入风险，应使用参数化查询：

Python示例：

cursor.execute("SELECT * FROM users WHERE name = %s", ("Alice",))

Java示例：

PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
pstmt.setString(1, "Alice");

5.事务管理

事务确保操作的原子性，典型流程包括开始、提交或回滚：

try:
    conn.begin()
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")
    conn.commit()
except:
    conn.rollback()

6.ORM框架使用

对象关系映射（ORM）框架如SQLAlchemy、Django ORM可简化数据库操作：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()
user = session.query(User).filter_by(name='Alice').first()

7.性能优化技巧

合理使用索引能显著提升查询速度：

CREATE INDEX idx_name ON users(name);

批量操作减少IO开销：

data = [(2, 'Bob'), (3, 'Charlie')]
cursor.executemany("INSERT INTO users VALUES (%s, %s)", data)

8.连接池管理

高频访问场景建议使用连接池：

Python的DBUtils：

from dbutils.pooled_db import PooledDB
pool = PooledDB(pymysql, 10, host='localhost', user='root', database='test')
conn = pool.connection()

openvela

openvela 操作系统专为 AIoT 领域量身定制，以轻量化、标准兼容、安全性和高度可扩展性为核心特点。openvela 以其卓越的技术优势，已成为众多物联网设备和 AI 硬件的技术首选，涵盖了智能手表、运动手环、智能音箱、耳机、智能家居设备以及机器人等多个领域。

更多推荐

Arm Ethos‑U65：YOLOv8n 模型准备与 Vela 编译

openvela

开源新纪元：深度解析小米 MiMo Code 的技术架构与实战应用

openvela

UniAD_v2 模型NPU适配

参考github网址：https://github.com/OpenDriveLab/UniAD/tree/v2.0参考readme：https://gitcode.com/Ascend/DrivingSDK/blob/master/model_examples/UniAD/README.md。

openvela

所有评论(0)

查看更多评论

觉不度SUKI

@2301_78540522

已为社区贡献1条内容

【MySQL】上来就干!

觉不度SUKI

一. MySQL简介

1.核心特点

2.典型应用场景

3.基础架构

二. MySQL基础语法

三.多表查询语法

四. MySQL完整性约束类型语法

注意以下属于拓展知识点（比较难，且使用场景比较少，但是流程控制和事务控制，需要掌握）

五，数据库索引和视图

1.具体语法

2.注意事项

3.数据备份与恢复

六.MySQL 流程控制语句语法

8.注意事项

七. MySQL权限管理概述

1.用户管理

2.权限分配

3.权限回收

4.查看权限

5.权限生效

6.安全建议

7.其他注意事项

八. MySQL事务的基本概念

1.MySQL事务的实现

2.事务的隔离级别

3.并发控制问题及解决方案

4.锁机制

6.死锁与处理

7.事务的保存点（Savepoint）

8.性能优化建议

九. MySQL 数据库备份与还原

十. 数据库编程基础概念

1.常见数据库类型

2.SQL基础语法

3.数据库连接方法

4.参数化查询与防注入

5.事务管理

6.ORM框架使用

7.性能优化技巧

8.连接池管理

所有评论(0)

温馨提示：您尚未绑定手机号

觉不度SUKI