资讯详情

深入了解 · 专业解读

【MySQL】——语法清单

发布时间：2026/9/8 20:25:19
【MySQL】——语法清单 目录一. MySQL简介1.核心特点2.典型应用场景3.基础架构二. MySQL基础语法1.创建数据库2.使用数据库3.创建表4.插入数据5.查询数据6.更新数据7.删除数据8.删除表9.删除数据库10.条件查询11.聚合函数12.分组查询13.连接查询三.多表查询语法1.内连接INNER JOIN2.左连接LEFT JOIN3.右连接RIGHT JOIN4.全外连接FULL OUTER JOIN5.交叉连接CROSS JOIN6.自连接SELF JOIN7.子查询8.多表连接9.使用UNION合并结果集10.使用EXISTS子查询四. MySQL完整性约束类型语法1.主键约束PRIMARY KEY2.外键约束FOREIGN KEY3.唯一约束UNIQUE4.非空约束NOT NULL5.检查约束CHECK6.默认约束DEFAULT7.自增约束AUTO_INCREMENT8.添加约束到现有表9.删除约束注意以下属于拓展知识点比较难且使用场景比较少但是流程控制和事务控制需要掌握五数据库索引和视图1.具体语法2.注意事项3.数据备份与恢复六.MySQL 流程控制语句语法1. IF 语句2. CASE 语句3. WHILE 循环4. REPEAT 循环5. LOOP 循环6. ITERATE 语句7. LEAVE 语句8.注意事项七. MySQL权限管理概述1.用户管理2.权限分配3.权限回收4.查看权限5.权限生效6.安全建议7.其他注意事项八. MySQL事务的基本概念1.MySQL事务的实现2.事务的隔离级别3.并发控制问题及解决方案4.锁机制6.死锁与处理7.事务的保存点Savepoint8.性能优化建议九. MySQL 数据库备份与还原1.备份方法2.还原方法3.自动化备份脚本示例4.注意事项一. MySQL简介MySQL是一种开源的关系型数据库管理系统RDBMS采用结构化查询语言SQL进行数据操作。它由瑞典公司MySQL AB开发现属于Oracle旗下产品。MySQL以其高性能、可靠性和易用性成为最流行的数据库之一广泛应用于Web应用、企业级系统及嵌入式场景。1.核心特点开源免费社区版可免费使用支持商业许可。跨平台支持兼容Windows、Linux、macOS等操作系统。高扩展性支持垂直与水平扩展适应不同规模的数据需求。多存储引擎如InnoDB支持事务、MyISAM读密集型场景等可按需选择。事务支持通过ACID原子性、一致性、隔离性、持久性特性确保数据完整性。2.典型应用场景Web应用与PHP、Java等语言集成支撑动态网站。数据分析结合工具如MySQL Workbench进行数据挖掘与报表生成。嵌入式系统轻量级版本适用于物联网设备等资源受限环境。3.基础架构MySQL采用客户端-服务器模型包含以下核心组件连接池管理客户端连接提升并发性能。SQL接口解析并执行SQL语句。查询优化器优化查询路径以提高效率。存储引擎负责数据的存储与检索。二. MySQL基础语法1.创建数据库CREATE DATABASE database_name;2.使用数据库USE database_name;3.创建表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.doeexample.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 usernamehost IDENTIFIED BY password;username用户名。host允许访问的主机%表示任意主机localhost表示本地。password用户密码可选但建议设置。删除用户DROP USER usernamehost;修改密码ALTER USER usernamehost IDENTIFIED BY new_password;2.权限分配基本语法GRANT permission_type ON database.object TO usernamehost;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 userlocalhost;3.权限回收撤销权限REVOKE permission_type ON database.object FROM usernamehost;示例REVOKE SELECT ON mydb.* FROM userlocalhost;4.查看权限查看用户权限SHOW GRANTS FOR usernamehost;查询权限表直接查看系统表SELECT * FROM mysql.user WHERE Userusername;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 ReadMySQL默认级别确保同一事务内多次读取同一数据的结果一致避免脏读和不可重复读但可能存在幻读。串行化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工具解析 binlogmysqlbinlog mysql-bin.000001 binlog_backup.sql2.还原方法逻辑备份还原通过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-datetime2023-10-01 00:00:00 --stop-datetime2023-10-02 00:00:00 mysql-bin.000001 | mysql -u root -p3.自动化备份脚本示例使用 Shell 脚本定时备份需配置crontab#!/bin/bash DATE$(date %Y%m%d) BACKUP_DIR/path/to/backup MYSQL_USERroot MYSQL_PASSWORDyourpassword mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases $BACKUP_DIR/alldb_$DATE.sql find $BACKUP_DIR -type f -mtime 7 -delete4.注意事项备份前验证磁盘空间是否充足。定期测试备份文件的可用性。敏感数据备份建议加密存储。大型数据库可结合--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或sqlalchemyimport pymysql conn pymysql.connect(hostlocalhost, userroot, passwordyou password, databasetest) 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_keyTrue) name Column(String) engine create_engine(sqlite:///test.db) Session sessionmaker(bindengine) session Session() user session.query(User).filter_by(nameAlice).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的DBUtilsfrom dbutils.pooled_db import PooledDB pool PooledDB(pymysql, 10, hostlocalhost, userroot, databasetest) conn pool.connection()

相关新闻