MySQL基本语法、高级语法知识总结以及常用语法案例

MySQL基本语法总结

MySQL是一种广泛使用的关系型数据库管理系统，其基本语法涵盖了数据库和数据表的创建、查询、修改和删除等操作。

一、数据库操作

1. 创建数据库（CREATE DATABASE）
   • 语法：CREATE DATABASE [IF NOT EXISTS] database_name;
   • 例如：CREATE DATABASE my_database; 如果数据库my_database不存在则创建它。这在防止因数据库已存在而导致错误时非常有用。使用IF NOT EXISTS可以避免在重复创建数据库时出现错误信息。
2. 修改数据库（ALTER DATABASE）
   • 可以用于修改数据库的一些属性，如字符集等。语法：ALTER DATABASE database_name [options];，例如ALTER DATABASE my_database CHARACTER SET utf8; 将my_database的字符集修改为utf8。
3. 查看数据库（SHOW DATABASES）
   • 语法：SHOW DATABASES [LIKE 'pattern'];。如果不使用LIKE子句，将显示所有数据库。例如，SHOW DATABASES LIKE 'my%'; 会显示所有以my开头的数据库名称。
4. 删除数据库（DROP DATABASE）
   • 语法：DROP DATABASE [IF EXISTS] database_name;。例如DROP DATABASE my_database; 将删除名为my_database的数据库。使用IF EXISTS可防止在数据库不存在时出现错误。

二、数据表操作

1. 创建表（CREATE TABLE）

   • 语法：CREATE TABLE [IF NOT EXISTS] table_name (column1 datatype [constraints], column2 datatype [constraints],...);。例如：

   CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT, gender ENUM('male', 'female')
   ); 
   

   • 这里创建了一个名为students的表，包含id（自动递增的整数且为主键）、name（最大长度为50的可变长字符串）、age（整数）和gender（枚举类型，取值为male或female）等列。

2. 修改表（ALTER TABLE）

   • 可以用于添加、修改或删除表中的列等操作。例如添加列：ALTER TABLE students ADD COLUMN email VARCHAR(100); 在students表中添加一个名为email，最大长度为100的可变长字符串类型的列。
   • 修改列的数据类型：ALTER TABLE students MODIFY COLUMN age SMALLINT; 将students表中的age列的数据类型修改为SMALLINT。
   • 删除列：ALTER TABLE students DROP COLUMN email; 从students表中删除email列。

3. 查看表（SHOW TABLES、DESC等）

   • SHOW TABLES; 用于显示当前数据库中的所有表。
   • DESC table_name;（或者DESCRIBE table_name;、EXPLAIN table_name;、SHOW COLUMNS FROM table_name;）用于查看表的结构，如列名、数据类型、是否为主键等信息。例如DESC students; 会显示students表的结构信息。

4. 删除表（DROP TABLE）

   • 语法：DROP TABLE [IF EXISTS] table_name;。例如DROP TABLE students; 将删除名为students的表。使用IF EXISTS可避免表不存在时的错误。

三、数据操作

1. 插入数据（INSERT INTO）

   • 有两种形式。第一种形式无需指定要插入数据的列名：INSERT INTO table_name VALUES (value1, value2, value3,...);。例如：

   INSERT INTO students VALUES (1, 'John', 20,'male'); 
   

   • 第二种形式需要指定列名及被插入的值：INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);。例如：

   INSERT INTO students (name, age, gender) VALUES ('Jane', 22, 'female'); 
   

2. 查询数据（SELECT）

   • 基本查询：SELECT column_name, column_name FROM table_name; 例如SELECT name, age FROM students; 将从students表中查询name和age两列的数据。
   • 查询所有列：SELECT * FROM table_name; 例如SELECT * FROM students; 将查询students表中的所有列数据。
   • 去重查询：SELECT DISTINCT column_name FROM table_name; 例如SELECT DISTINCT gender FROM students; 将查询students表中不同的gender值。
   • 带条件查询（WHERE子句）：SELECT column_name FROM table_name WHERE condition;。例如SELECT name FROM students WHERE age > 20; 将查询students表中年龄大于20岁的学生姓名。

3. 更新数据（UPDATE）

   • 语法：UPDATE table_name SET column1 = value1, column2 = value2,... WHERE some_column = some_value;。例如：

   UPDATE students SET age = 21 WHERE name = 'John'; 
   

   • 这将把名为John的学生的年龄更新为21岁。注意，如果不使用WHERE子句，将会更新表中的所有行，所以要谨慎使用。

4. 删除数据（DELETE）

   • 语法：DELETE FROM table_name WHERE some_column = some_value;。例如：

   DELETE FROM students WHERE name = 'Jane'; 
   

   • 这将删除名为Jane的学生记录。如果省略WHERE子句，将删除表中的所有数据：DELETE FROM table_name;。

四、逻辑运算与特殊条件判断

1. 逻辑运算
   • AND（与）：同时满足两个条件的值。例如SELECT * FROM students WHERE age > 20 AND gender ='male'; 查询年龄大于20岁且为男性的学生记录。
   • OR（或）：满足其中一个条件的值。例如SELECT * FROM students WHERE age > 20 OR gender = 'female'; 查询年龄大于20岁或者为女性的学生记录。
   • NOT（非）：满足不包含该条件的值。例如SELECT * FROM students WHERE NOT age = 20; 查询年龄不等于20岁的学生记录。
2. 特殊条件判断
   • 空值判断（IS NULL）：例如SELECT * FROM students WHERE email IS NULL; 查询students表中email列为空值的记录。
   • BETWEEN… AND…（在之间的值）：例如SELECT * FROM students WHERE age BETWEEN 20 AND 25; 查询年龄在20到25岁之间（包括20和25岁）的学生记录。
   • IN（指定针对某个列的多个可能值）：例如SELECT * FROM students WHERE age IN (20, 22); 查询年龄为20岁或22岁的学生记录。
   • LIKE（搜索某种模式）：例如SELECT * FROM students WHERE name LIKE 'J%'; 查询students表中姓名以J开头的学生记录。其中%表示多个字符，_表示一个字符。例如name LIKE '%a_' 表示查询姓名倒数第二个字符为a的学生记录。

五、排序（ORDER BY）

• 语法：ORDER BY column_name [ASC|DESC];。例如SELECT * FROM students ORDER BY age ASC; 将按照年龄升序查询students表中的所有记录。如果使用DESC则为降序排列，例如SELECT * FROM students ORDER BY age DESC;。如果ORDER BY多个列，例如SELECT * FROM students ORDER BY gender, age ASC;，将先按照gender排序，再按照age升序排序。

MySQL高级语法知识汇总

MySQL的高级语法在基本语法的基础上，进一步提升了数据处理和管理的能力。

一、索引相关

1. 创建索引（CREATE INDEX）
   • 语法：CREATE [UNIQUE] INDEX index_name ON table_name (column1 [(length)], column2 [(length)],...);。例如，在students表的name列上创建普通索引：CREATE INDEX name_index ON students (name);。如果加上UNIQUE关键字，如CREATE UNIQUE INDEX email_index ON students (email);，则创建唯一索引，确保email列的值在表中是唯一的。
2. 索引分析与优化
   • 可以使用EXPLAIN语句来分析查询语句如何使用索引。例如EXPLAIN SELECT * FROM students WHERE age = 20;，它会返回一个结果集，显示查询执行计划，包括是否使用了索引等信息。通过分析这些信息，可以优化查询语句或者表结构以提高查询性能。
   • 索引优化的一些原则包括：避免在大表上创建过多的索引，因为索引也需要占用存储空间并且会影响插入、更新和删除操作的性能；对于经常用于查询条件、连接条件和排序的列创建索引等。

二、联结（JOIN）操作

1. 内联结（INNER JOIN）

   • 语法：SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column = table2.column; 。例如，假设有students表和classes表，students表中有class_id列，classes表中有id列，要查询学生及其所在班级的信息，可以使用内联结：

   SELECT students.name,  classes.class_name 
   FROM students
   INNER JOIN classes ON students.class_id  = classes.id;  
   

   • 内联结会返回两个表中满足联结条件的行的组合。

2. 外联结（LEFT JOIN、RIGHT JOIN）

   • 左外联结（LEFT JOIN）：语法：SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column = table2.column; 。例如SELECT students.name, classes.class_name FROM students LEFT JOIN classes ON students.class_id = classes.id; ，左外联结会返回左表（students表）中的所有行，以及右表（classes表）中与左表满足联结条件的行，如果右表中没有匹配的行，则相应的列值为NULL。
   • 右外联结（RIGHT JOIN）：与左外联结类似，只是以右表为主表。语法：SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; 。例如SELECT students.name, classes.class_name FROM students RIGHT JOIN classes ON students.class_id = classes.id; ，右外联结会返回右表中的所有行，以及左表中与右表满足联结条件的行，如果左表中没有匹配的行，则相应的列值为NULL。

三、子查询

1. 概念与使用场景

   • 子查询是一个嵌套在其他查询中的查询。例如，要查询年龄大于平均年龄的学生，可以先计算平均年龄：SELECT AVG(age) FROM students;，然后将这个结果作为子查询嵌套在主查询中：SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);。
   • 子查询可以出现在SELECT、FROM、WHERE等子句中。在WHERE子句中的子查询通常用于过滤数据，根据子查询的结果确定哪些行应该被选择。在FROM子句中的子查询可以将子查询的结果作为一个临时表来使用。

2. 相关子查询与不相关子查询

   • 不相关子查询：子查询可以独立于外部查询运行，外部查询的结果不会影响子查询的执行。例如上面查询年龄大于平均年龄的学生的例子就是不相关子查询。
   • 相关子查询：子查询依赖于外部查询的结果。例如，要查询每个班级中年龄最大的学生，可以使用相关子查询：

   SELECT name, age, class_id
   FROM students s1
   WHERE age = (SELECT MAX(age) FROM students s2 WHERE s2.class_id  = s1.class_id);  
   

   • 这里内部子查询中的class_id是依赖于外部查询中的class_id的。

四、事务管理

1. 事务的概念与特性（ACID）
   • 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么全部执行，要么全部不执行。例如在银行转账操作中，从一个账户扣款和向另一个账户存款这两个操作必须作为一个整体来执行，如果其中一个操作失败，整个转账事务就应该回滚，即两个操作都不生效。
   • 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。例如在转账操作中，无论转账是否成功，账户余额的总和应该保持不变。
   • 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不能被其他事务干扰。例如，有两个用户同时对同一个账户进行操作，数据库系统应该保证每个事务都感觉不到其他事务的存在，按照顺序独立执行。
   • 持久性（Durability）：一个事务一旦被提交，它对数据库中数据的改变就应该是永久性的。例如，一旦转账事务被提交，账户余额的改变就会永久保存到数据库中。
2. 事务操作语句（COMMIT、ROLLBACK等）
   • BEGIN：开始一个事务。例如BEGIN;。
   • COMMIT：提交事务，使事务中的所有操作生效。例如在一系列更新操作后使用COMMIT;来保存更改。
   • ROLLBACK：回滚事务，撤销事务中的所有操作。如果在事务执行过程中出现错误，可以使用ROLLBACK;来恢复到事务开始前的状态。

五、视图（VIEW）

1. 创建视图（CREATE VIEW）
   • 语法：CREATE VIEW view_name AS SELECT column1, column2,... FROM table_name WHERE condition;。例如，创建一个视图来显示年龄大于20岁的学生信息：CREATE VIEW old_students AS SELECT name, age, gender FROM students WHERE age > 20;。视图本质上是一个虚拟的表，它的数据来源于一个或多个实际的表。
2. 视图的用途与优点
   • 简化复杂查询：对于复杂的查询，如多表联结、嵌套子查询等，可以创建视图来简化查询操作。例如，如果经常需要查询学生及其班级信息，可以创建一个视图，以后直接查询视图即可，而不需要每次都编写复杂的联结查询语句。
   • 数据安全性与权限控制：可以通过视图来限制用户对表中数据的访问。例如，只允许用户通过视图查看某些列的数据，而不能直接访问表中的所有数据。这样可以保护敏感数据，提高数据安全性。
     

MySQL常用语法案例解析

一、数据查询案例

1. 简单查询与条件查询
   • 查询特定列：假设我们有一个employees表，包含id、name、department、salary等列。要查询员工的姓名和部门，可以使用SELECT name, department FROM employees;。
   • 条件查询：如果要查询工资大于5000元的员工姓名，可以使用SELECT name FROM employees WHERE salary > 5000;。这里的WHERE子句用于筛选满足条件（工资大于5000元）的行。
2. 模糊查询（LIKE）
   • 例如，要查询姓名以J开头的员工，可以使用SELECT * FROM employees WHERE name LIKE 'J%';。如果要查询姓名中包含a的员工，可以使用SELECT * FROM employees WHERE name LIKE '%a%';。如果要查询姓名倒数第二个字符为e的员工，可以使用SELECT * FROM employees WHERE name LIKE '%e_';。
3. 多条件查询（AND、OR、NOT）
   • AND条件：查询工资大于5000元且部门为IT的员工，可以使用SELECT * FROM employees WHERE salary > 5000 AND department = 'IT';。
   • OR条件：查询工资大于5000元或者部门为HR的员工，可以使用SELECT * FROM employees WHERE salary > 5000 OR department = 'HR';。
   • NOT条件：查询不是IT部门的员工，可以使用SELECT * FROM employees WHERE NOT department = 'IT';。
4. 排序查询（ORDER BY）
   • 按照工资升序查询员工信息：SELECT * FROM employees ORDER BY salary ASC;。如果要按照工资降序查询，可以使用SELECT * FROM employees ORDER BY salary DESC;。如果要先按照部门排序，再按照工资升序排序，可以使用SELECT * FROM employees ORDER BY department, salary ASC;。
5. 分组查询（GROUP BY）与聚合函数（AVG、SUM、COUNT等）
   • 计算每个部门的平均工资：SELECT department, AVG(salary) FROM employees GROUP BY department;。这里GROUP BY用于按照部门对员工进行分组，然后AVG函数计算每个组（即每个部门）的平均工资。