MySQL数据库中常考的SQL语句有哪些？它们如何与MySQL模式兼容？

一、基本查询

1、查询所有字段：SELECT * FROM table_name;

2、查询指定字段：SELECT column1, column2 FROM table_name;

3、消除重复行：SELECT DISTINCT column_name FROM table_name;

4、使用别名：SELECT column_name AS alias_name FROM table_name;

5、多表连接查询：

内连接：SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;

左连接：SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

右连接：SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

6、排序：

升序：SELECT * FROM table_name ORDER BY column_name ASC;

降序：SELECT * FROM table_name ORDER BY column_name DESC;

7、聚合函数：

总数：SELECT COUNT(*) FROM table_name;

最大值：SELECT MAX(column_name) FROM table_name;

最小值：SELECT MIN(column_name) FROM table_name;

求和：SELECT SUM(column_name) FROM table_name;

平均值：SELECT AVG(column_name) FROM table_name;

四舍五入保留小数：SELECT ROUND(AVG(column_name), 2) FROM table_name;

8、分组：

按性别分组查询人数：SELECT gender, COUNT(*) FROM table_name GROUP BY gender;

分组查询每种性别的平均年龄：SELECT gender, AVG(age) FROM table_name GROUP BY gender;

分组查询每种性别的人数（大于2）：SELECT gender, COUNT(*) FROM table_name GROUP BY gender HAVING COUNT(*) > 2;

汇归纳果（with rollup）：SELECT gender, COUNT(*) FROM table_name GROUP BY gender WITH ROLLUP;

9、分页：

查询前n个数据：SELECT * FROM table_name LIMIT n;

分页显示（每页显示m个）：SELECT * FROM table_name LIMIT start, m;

10、子查询：

标量子查询：SELECT * FROM table_name WHERE column_name = (SELECT value FROM another_table);

列子查询：SELECT name FROM table_name WHERE id IN (SELECT id FROM another_table);

行子查询：SELECT * FROM table_name WHERE (column1, column2) = (SELECT column1, column2 FROM another_table);

二、数据操作

1、插入数据：

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

REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);（如果存在则替换）

2、更新数据：UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

3、删除数据：

删除指定条件的数据：DELETE FROM table_name WHERE condition;

删除所有数据但保留表结构：DELETE * FROM table_name;

三、数据库管理

1、创建数据库：CREATE DATABASE db_name;

2、删除数据库：DROP DATABASE [IF EXISTS] db_name;

3、选择数据库：USE db_name;

4、查看所有数据库：SHOW DATABASES [LIKE wild];

5、创建表：

   CREATE TABLE table_name (
       column1 datatype constraints,
       column2 datatype constraints,
       ...
   );

示例：创建一个学生表，包含学号（主键）、姓名、性别、年龄，性别默认为男，年龄在18-25之间。

     CREATE TABLE student (
         student_id VARCHAR(5) NOT NULL UNIQUE,
         name VARCHAR(8) NOT NULL,
         gender CHAR(2) DEFAULT '男',
         age INT CHECK (age >= 18 AND age <= 25),
         PRIMARY KEY (student_id)
     );

6、修改表结构：

添加列：ALTER TABLE table_name ADD column_name datatype constraints;

删除列：ALTER TABLE table_name DROP COLUMN column_name;

修改列类型：ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;

重命名表：ALTER TABLE old_table RENAME TO new_table;

修改列名称：ALTER TABLE table_name CHANGE old_column_name new_column_name datatype constraints;

7、索引管理：

创建索引：CREATE INDEX index_name ON table_name (column_name);

删除索引：DROP INDEX index_name ON table_name;

四、事务管理

1、开始事务：START TRANSACTION;

2、提交事务：COMMIT;

3、回滚事务：ROLLBACK;

五、用户与权限管理

1、创建用户：

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

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

FLUSH PRIVILEGES;（刷新权限）

2、删除用户：DROP USER 'username'@'host';

3、修改用户密码：ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

六、常见问题FAQs

1、如何查询表中的所有列名？：使用SHOW COLUMNS FROM table_name; 或DESCRIBE table_name;

2、如何查看当前数据库中的所有表？：使用SHOW TABLES;

3、如何在MySQL中使用正则表达式进行查询？：使用REGEXP 关键字，查询以“A”开头的用户名：SELECT * FROM users WHERE username REGEXP '^A';

七、小编有话说

掌握上述SQL语句是学习MySQL的基础，但实践出真知，建议在学习过程中多动手操作，通过实际案例加深理解，注意SQL注入等安全问题，编写代码时要养成良好的安全习惯，希望本文能为你提供有益的参考，祝你在学习MySQL的道路上越走越远！