Blog

Various of MySQL statements

 MySQL syntax format emphasizes consistency, readability, and maintainability. Here are detailed guidelines and examples for formatting various MySQL statements:

1. SELECT Statement

sql
SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1
HAVING condition
ORDER BY column1 [ASC|DESC]
LIMIT number;

Example:

sql
SELECT first_name, last_name, birth_date
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department_id
HAVING COUNT(*) > 5
ORDER BY last_name ASC
LIMIT 10;

2. INSERT Statement

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

Example:

sql
INSERT INTO employees (first_name, last_name, birth_date, hire_date)
VALUES ('Jane', 'Smith', '1990-02-15', '2023-06-01');

3. UPDATE Statement

sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

sql
UPDATE employees
SET hire_date = '2023-07-01'
WHERE employee_id = 1;

4. DELETE Statement

sql
DELETE FROM table_name
WHERE condition;

Example:

sql
DELETE FROM employees
WHERE employee_id = 1;

5. CREATE TABLE Statement

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

Example:

sql
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    hire_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

6. ALTER TABLE Statement

sql
ALTER TABLE table_name
ADD column_name datatype [constraints];

Example:

sql
ALTER TABLE employees
ADD email VARCHAR(100) NOT NULL;

7. DROP TABLE Statement

sql
DROP TABLE table_name;

Example:

sql
DROP TABLE employees;

8. CREATE INDEX Statement

sql
CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example:

sql
CREATE INDEX idx_last_name
ON employees (last_name);

9. DROP INDEX Statement

sql
DROP INDEX index_name
ON table_name;

Example:

sql
DROP INDEX idx_last_name
ON employees;

10. CREATE VIEW Statement

sql
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

sql
CREATE VIEW active_employees AS
SELECT first_name, last_name
FROM employees
WHERE status = 'active';

11. DROP VIEW Statement

sql
DROP VIEW view_name;

Example:

sql
DROP VIEW active_employees;

12. Transaction Statements

  • START TRANSACTION: Begins a new transaction.
  • COMMIT: Saves the changes made in the transaction.
  • ROLLBACK: Reverts the changes made in the transaction.

Example:

sql
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

13. GRANT Statement

sql
GRANT privilege_name
ON object_name
TO user [WITH GRANT OPTION];

Example:

sql
GRANT SELECT, INSERT ON employees TO 'user'@'localhost';

14. REVOKE Statement

sql
REVOKE privilege_name
ON object_name
FROM user;

Example:

sql
REVOKE SELECT, INSERT ON employees FROM 'user'@'localhost';

Formatting Guidelines

  1. Consistent Capitalization: Use uppercase for SQL keywords (SELECT, FROM, WHERE) and lowercase for column and table names (table_name, column_name).
  2. Indentation: Use indentation to align SQL statements and enhance readability, especially in complex queries.
  3. Line Breaks: Place each clause (SELECT, FROM, WHERE, JOIN, ORDER BY) on a new line.
  4. Comments: Use comments to explain complex logic or important details within the code. Use -- for single-line comments and /* ... */ for multi-line comments.

By following these guidelines and using the provided formats, you can write MySQL statements that are clear, efficient, and easy to maintain.

Leave a Reply