MySQL数据库SQL语句详解
MySQL作为当前最流行的开源关系型数据库管理系统之一，广泛应用于各种类型的应用程序中，无论你是初学者还是经验丰富的开发者，掌握MySQL的基本SQL语句对于有效地操作数据库至关重要，本文将详细介绍MySQL数据库的各类SQL语句，包括数据定义语言（DDL）、数据操纵语言（DML）、数据查询语言（DQL）和数据控制语言（DCL），并附上相关案例和常见问题解答。
目录
1、[DDL: 数据定义语言](#dl-数据定义语言)
2、[DML: 数据操纵语言](#dml-数据操纵语言)
3、[DQL: 数据查询语言](#dql-数据查询语言)
4、[DCL: 数据控制语言](#dcl-数据控制语言)
5、[事务管理](#事务管理)
6、[索引管理](#索引管理)
7、[FAQs](#faqs)
8、[小编有话说](#小编有话说)
DDL: 数据定义语言
DDL用于定义和修改数据库的结构，包括创建、修改和删除数据库及表结构。
创建数据库
CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS mydatabase;
选择数据库
USE 数据库名;
USE mydatabase;
创建表
CREATE TABLE 表名 (
列名1 数据类型 [约束],
列名2 数据类型 [约束],
...
);创建一个名为students的表，包含学号、姓名和年龄字段。
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT
);修改表结构
添加列
ALTER TABLE 表名 ADD 列名 数据类型;
向students表中添加一个性别字段。
ALTER TABLE students ADD gender VARCHAR(10);
删除列
ALTER TABLE 表名 DROP COLUMN 列名;
从students表中删除age字段。
ALTER TABLE students DROP COLUMN age;
修改列的数据类型
ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型;
将students表中的name字段类型改为VARCHAR(100)。
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);
删除表
DROP TABLE 表名;
删除students表。
DROP TABLE IF EXISTS students;
DML: 数据操纵语言
DML用于插入、更新和删除表中的数据。
插入数据
插入单条记录
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
向students表中插入一条记录。
INSERT INTO students (student_id, name, age, gender) VALUES (1, 'John Doe', 20, 'Male');
插入多条记录
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...), (值3, 值4, ...), ...;
向students表中插入多条记录。
INSERT INTO students (student_id, name, age, gender) VALUES (2, 'Jane Smith', 22, 'Female'), (3, 'Alice Johnson', 19, 'Female');
更新数据
UPDATE 表名 SET 列1 = 新值1, 列2 = 新值2, ... WHERE 条件;
更新students表中John Doe的年龄为21。
UPDATE students SET age = 21 WHERE name = 'John Doe';
删除数据
DELETE FROM 表名 WHERE 条件;
删除students表中John Doe的记录。
DELETE FROM students WHERE name = 'John Doe';
DQL: 数据查询语言
DQL用于查询数据库中的数据，最常用的关键字是SELECT。
基本查询
SELECT 列1, 列2, ... FROM 表名;
查询students表中的所有记录。
SELECT * FROM students;
带条件的查询
SELECT 列1, 列2, ... FROM 表名 WHERE 条件;
查询年龄大于20的学生。
SELECT * FROM students WHERE age > 20;
排序查询结果
SELECT 列1, 列2, ... FROM 表名 ORDER BY 列1 [ASC|DESC];
按年龄升序查询学生记录。
SELECT * FROM students ORDER BY age ASC;
聚合函数
常用的聚合函数包括COUNT,SUM,AVG,MAX,MIN。
SELECT COUNT(*) FROM 表名; SELECT SUM(列名) FROM 表名; SELECT AVG(列名) FROM 表名; SELECT MAX(列名) FROM 表名; SELECT MIN(列名) FROM 表名;
统计学生总数。
SELECT COUNT(*) FROM students;
计算学生的平均年龄。
SELECT AVG(age) FROM students;
分组查询
SELECT 列1, 聚合函数(列2) FROM 表名 GROUP BY 列1;
按性别分组统计学生人数。
SELECT gender, COUNT(*) FROM students GROUP BY gender;
DCL: 数据控制语言
DCL用于控制不同用户对数据库的访问权限。
创建用户
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
创建一个新用户newuser。
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
授权用户权限
GRANT ALL PRIVILEGES ON 数据库.* TO '用户名'@'主机';
授予newuser对所有数据库的全部权限。
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
查看用户权限
SHOW GRANTS FOR '用户名'@'主机';
查看newuser的权限。
SHOW GRANTS FOR 'newuser'@'localhost';
撤销用户权限
REVOKE ALL PRIVILEGES ON 数据库.* FROM '用户名'@'主机';
撤销newuser的所有权限。
REVOKE ALL PRIVILEGES ON *.* FROM 'newuser'@'localhost';
删除用户
DROP USER '用户名'@'主机';
删除用户newuser。
DROP USER 'newuser'@'localhost';
事务管理
事务是一组可以一起成功或失败的操作单元，用于保证数据的一致性和完整性，MySQL默认采用自动提交模式，可以通过以下命令显式地开始、提交或回滚事务。
开始事务
START TRANSACTION;
或者使用简写形式：
BEGIN;
开始一个新的事务。
START TRANSACTION;
提交事务：确认事务中的所有操作并永久保存更改，如果事务中的任何操作失败，可以使用ROLLBACK命令撤销所有更改，这确保了数据库状态的一致性和完整性，在复杂的业务逻辑中，事务管理尤为重要，因为它可以防止数据不一致的情况发生，通过合理使用事务，可以确保多个操作要么全部成功，要么全部失败，从而维护数据的可靠性和准确性，事务还支持保存点，允许部分回滚，提供更灵活的错误恢复机制，掌握事务的使用对于开发可靠的数据库应用系统至关重要。