SQL Reference for All MySQL Commands

SQL, or Structured Query Language, is the standard programming language used to manage and manipulate relational databases. It plays a crucial role in database management, enabling users to interact with databases efficiently. MySQL, one of the most popular relational database management systems (RDBMS), employs SQL as its language for managing data. This article serves as a comprehensive reference for all MySQL commands, providing examples, tables, and essential concepts tailored for beginners.

II. MySQL Data Types

Understanding data types is fundamental in MySQL, as they define the kind of data that can be stored in a database. Below are the primary data types used in MySQL:

Data Type	Description
Numeric Data Types	Includes INT, FLOAT, DOUBLE, DECIMAL, etc.
Date and Time Data Types	Includes DATE, TIME, DATETIME, TIMESTAMP, etc.
String Data Types	Includes CHAR, VARCHAR, TEXT, BLOB, etc.
Other Data Types	Includes ENUM, SET, JSON, etc.

III. MySQL Functions

MySQL comes equipped with a variety of functions to manipulate data, including:

A. String Functions

SELECT CONCAT('Hello', ' ', 'World!'); -- Output: Hello World!

B. Numeric Functions

SELECT ROUND(123.456, 2); -- Output: 123.46

C. Date Functions

SELECT NOW(); -- Returns the current date and time

D. Aggregate Functions

SELECT COUNT(*) FROM users; -- Returns the number of users

E. Control Flow Functions

SELECT IF(1=1, 'True', 'False'); -- Output: True

IV. MySQL Operators

Operators are special symbols that perform operations on one, two, or three operands, and return a result. The main types of operators in MySQL include:

Operator Type	Example
Comparison Operators	SELECT * FROM users WHERE age > 18;
Arithmetic Operators	SELECT 10 + 5 AS Sum;
Logical Operators	SELECT * FROM users WHERE age > 18 AND gender = ‘Female’;
Bitwise Operators	SELECT 5 & 3 AS Bitwise; — Output: 1
Other Operators	SELECT * FROM users WHERE name LIKE ‘A%’;

V. MySQL Statements

MySQL statements can be categorized into several types:

A. Data Query Language (DQL)

1. SELECT Statement

SELECT * FROM products;

B. Data Definition Language (DDL)

1. CREATE Statement

CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));

2. ALTER Statement

ALTER TABLE users ADD email VARCHAR(100);

3. DROP Statement

DROP TABLE users;

C. Data Manipulation Language (DML)

1. INSERT Statement

INSERT INTO users(name, email) VALUES('John Doe', 'john@example.com');

2. UPDATE Statement

UPDATE users SET email = 'john.doe@example.com' WHERE name = 'John Doe';

3. DELETE Statement

DELETE FROM users WHERE id = 1;

D. Data Control Language (DCL)

1. GRANT Statement

GRANT SELECT ON database_name.* TO 'username'@'localhost';

2. REVOKE Statement

REVOKE SELECT ON database_name.* FROM 'username'@'localhost';

VI. MySQL Joins

Joins are used to combine rows from two or more tables based on a related column. The types of joins in MySQL are:

A. INNER JOIN

SELECT orders.id, customers.name 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id;

B. LEFT JOIN

SELECT customers.name, orders.id 
FROM customers 
LEFT JOIN orders ON customers.id = orders.customer_id;

C. RIGHT JOIN

SELECT orders.id, customers.name 
FROM orders 
RIGHT JOIN customers ON orders.customer_id = customers.id;

D. FULL JOIN

SELECT customers.name, orders.id 
FROM customers 
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

E. CROSS JOIN

SELECT customers.name, orders.id 
FROM customers 
CROSS JOIN orders;

VII. MySQL Indexes

Indexes enhance the speed of data retrieval operations on a database table.

A. Creating Indexes

CREATE INDEX idx_email ON users(email);

B. Types of Indexes

B-Tree Index
Full-text Index
Hash Index

C. Dropping Indexes

DROP INDEX idx_email ON users;

VIII. MySQL Transactions

Transactions ensure that a series of SQL commands are executed in a cohesive manner, providing data integrity.

A. START TRANSACTION

START TRANSACTION;

B. COMMIT

COMMIT;

C. ROLLBACK

ROLLBACK;

IX. MySQL Views

Views are virtual tables created based on the result set of a SELECT statement.

A. Creating Views

CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

B. Updating Views

UPDATE active_users SET email = 'new_email@example.com' WHERE name = 'John Doe';

C. Dropping Views

DROP VIEW active_users;

X. Conclusion

In conclusion, SQL commands are of paramount importance in managing and manipulating data in MySQL. Mastery of these commands will empower you to handle databases effectively, facilitating a deeper understanding of relational database management. With practice, you will become proficient in using MySQL commands to manage your data.

FAQ

What is SQL?

SQL stands for Structured Query Language, which is used to communicate with databases.

What is MySQL?

MySQL is an open-source relational database management system that uses SQL to manage data.

What are the different types of SQL commands?

SQL commands are categorized into DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), and DCL (Data Control Language).

What is a primary key?

A primary key is a unique identifier for a record in a database table.

What is normalization?

Normalization is a process of organizing data to reduce redundancy and improve data integrity.

askthedev.com Latest Articles