轻松上手MySQL：基础操作语句详解

1. Create a database

CREATE DATABASE mydatabase;

2. Drop a database

DROP DATABASE mydatabase;

3. Create a table

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL);

4. Drop a table

DROP TABLE users;

5. Insert a record into a table

INSERT INTO users (username, email) VALUES (‘john_doe’, ‘john@example.com’);

6. Update records in a table

UPDATE users SET email = ‘new_email@example.com’ WHERE username = ‘john_doe’;

7. Delete records from a table

DELETE FROM users WHERE username = ‘john_doe’;

8. Select all records from a table

SELECT * FROM users;

9. Select specific columns from a table

SELECT username, email FROM users;

10. Select records with a condition

SELECT * FROM users WHERE id = 1;

11. Select records with multiple conditions

SELECT * FROM users WHERE username = ‘john_doe’ AND email = ‘john@example.com’;

12. Select records with pattern matching

SELECT * FROM users WHERE username LIKE ‘john%’;

13. Order records in ascending order

SELECT * FROM users ORDER BY username ASC;

14. Order records in descending order

SELECT * FROM users ORDER BY username DESC;

15. Limit the number of records returned

SELECT * FROM users LIMIT 10;

16. Offset the start of records returned

SELECT * FROM users LIMIT 10 OFFSET 20;

17. Count the number of records in a table

SELECT COUNT(*) FROM users;

18. Sum of values in a column

SELECT SUM(sales) FROM transactions;

19. Average value in a column

SELECT AVG(price) FROM products;

20. Maximum value in a column

SELECT MAX(score) FROM exam_results;

21. Minimum value in a column

SELECT MIN(age) FROM employees;

22. Group records by a column

SELECT department, COUNT(*) FROM employees GROUP BY department;

23. Join two tables

SELECT users.username, orders.order_idFROM usersINNER JOIN orders ON users.id = orders.user_id;

24. Left join two tables

SELECT users.username, orders.order_idFROM usersLEFT JOIN orders ON users.id = orders.user_id;

25. Right join two tables

SELECT users.username, orders.order_idFROM usersRIGHT JOIN orders ON users.id = orders.user_id;

26. Full outer join two tables

SELECT users.username, orders.order_idFROM usersFULL OUTER JOIN orders ON users.id = orders.user_id;

27. Create an index on a table

CREATE INDEX idx_username ON users (username);

28. Drop an index from a table

DROP INDEX idx_username ON users;

29. Grant privileges to a user

GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’;

30. Revoke privileges from a user

REVOKE SELECT, INSERT, UPDATE ON mydatabase.* FROM ‘username’@’localhost’;

#数据库##sql##程序员##开发工具##软件##数据分析##mysql#