MySQL语句大全及用法

MySQL语句大全及用法

目录

1. 数据库操作
   • 创建数据库
   • 删除数据库
   • 选择数据库
2. 表操作
   • 创建表
   • 删除表
   • 修改表结构
3. 数据操作
   • 插入数据
   • 更新数据
   • 删除数据
4. 查询操作
   • SELECT语句
   • WHERE子句
   • ORDER BY子句
   • GROUP BY子句
   • HAVING子句
5. 聚合函数
   • COUNT()
   • SUM()
   • AVG()
   • MAX()
   • MIN()
6. 连接查询
   • INNER JOIN
   • LEFT JOIN
   • RIGHT JOIN
   • FULL JOIN
7. 子查询
8. 索引
9. 事务处理
10. 存储过程和函数
11. 触发器
12. 视图
13. 权限管理
14. 备份与恢复

示例代码

1. 数据库操作

创建数据库

CREATE DATABASE mydb;

删除数据库

DROP DATABASE mydb;

选择数据库

USE mydb;

2. 表操作

创建表

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

删除表

DROP TABLE users;

修改表结构

ALTER TABLE users
ADD COLUMN address VARCHAR(255);

3. 数据操作

插入数据

INSERT INTO users (id, name, age, email)
VALUES (1, 'John', 25, 'john@example.com');

更新数据

UPDATE users
SET age = 26, email = 'john_doe@example.com'
WHERE id = 1;

删除数据

DELETE FROM users
WHERE id = 1;

4. 查询操作

SELECT语句

SELECT * FROM users;

WHERE子句

SELECT * FROM users
WHERE age > 25;

ORDER BY子句

SELECT * FROM users
ORDER BY age DESC;

GROUP BY子句

SELECT age, COUNT(*) FROM users
GROUP BY age;

HAVING子句

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

5. 聚合函数

COUNT()

SELECT COUNT(*) FROM users;

SUM()

SELECT SUM(age) FROM users;

AVG()

SELECT AVG(age) FROM users;

MAX()

SELECT MAX(age) FROM users;

MIN()

SELECT MIN(age) FROM users;

6. 连接查询

INNER JOIN

SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN

SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

RIGHT JOIN

SELECT users.name, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

FULL JOIN

SELECT users.name, orders.order_id
FROM users
FULL JOIN orders ON users.id = orders.user_id;

7. 子查询

SELECT * FROM users
WHERE age = (SELECT MAX(age) FROM users);

8. 索引

CREATE INDEX index_name ON table_name (column_name);

9. 事务处理

START TRANSACTION;
-- SQL statements here...
COMMIT; -- or ROLLBACK; in case of errors

10. 存储过程和函数

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    -- SQL statements here...
END //
DELIMITER ;

11. 触发器

DELIMITER //
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN
    -- SQL statements here...
END //
DELIMITER ;

12. 视图

CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name;

13. 权限管理

创建用户并授权

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

撤销用户权限

REVOKE ALL PRIVILEGES ON database_name.* FROM 'newuser'@'localhost';
FLUSH PRIVILEGES;

删除用户

DROP USER 'newuser'@'localhost';

14. 备份与恢复

数据备份

mysqldump -u username -p database_name > backup.sql

数据恢复

mysql -u username -p database_name < backup.sql

通过以上示例代码，您应该能够掌握MySQL的基本操作和常用语句。请注意，实际使用时需要根据您的数据库结构和需求进行相应的调整。

文档下载