Language Type
- DDL (Data Definition Language)
Data Definition Language is used to define database objects such as databases, tables, columns, etc.
- DML (Data Manipulation Language)
Data Manipulation Language is used to manipulate database records.
- DCL (Data Control Language)
Data Control Language is used to define access permissions and security levels.
- DQL (Data Query Language)
Data Query Language is used to query data from the database.
- TPL (Transaction Processing Language)
Transaction Processing Language is used to manage transactions in the database.
DDL (Data Definition Language)
DB
Explanation: Used to create and delete databases.
Example:
CREATE DATABASE my_database;
DROP DATABASE my_database;
Table
Explanation: Used to create, modify, or delete tables.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);
ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);
DROP TABLE employees;
Data Type
Explanation: Defines the type of data that can be stored in a table’s column.
int: Integer type.
double: Floating-point type. For example, double(5,2) means a maximum of 5 digits, with 2 of those digits after the decimal point. The maximum value would be 999.99.
decimal: Precision type, commonly used in financial or form data because it avoids precision loss.
char: Fixed-length string type. (If the input string is shorter than the defined length, spaces are added to fill the remaining space.)
varchar: Variable-length string type.
text: String type for larger amounts of text.
blob: Binary large object type, used to store binary data (such as files or images).
date: Date type, formatted as yyyy-MM-dd.
time: Time type, formatted as hh:mm:ss.
timestamp: Timestamp type, used to store both date and time.
Example:
CREATE TABLE products (
product_id INT,
product_name VARCHAR(100),
price DECIMAL(10, 2),
created_at TIMESTAMP
);
DML (Data Manipulation Language)
Insert
Explanation: Used to add records to a table.
Example:
INSERT INTO employees (id, name, department) VALUES (1, 'John Doe', 'HR');
Update
Explanation: Used to update existing records in a table.
Example:
UPDATE employees SET department = 'Finance' WHERE id = 1;
Delete
Explanation: Used to delete records from a table.
Example:
DELETE FROM employees WHERE id = 1;
DQL (Data Query Language)
Select
Explanation: Used to query data from a database.
Example:
SELECT column_name(s) -- Columns you want to query
FROM table_name -- Table from which you want to query data
WHERE condition -- Conditions to filter rows
GROUP BY group_column -- Columns used to group the results
HAVING group_condition -- Conditions for grouped results
ORDER BY sort_column -- Columns used to sort the results
LIMIT start_row, row_count -- Limit the result set with a starting row and row count
Key words
-
Explanation:
-
SELECT column_name(s): Specifies the columns to retrieve. -
FROM table_name: Specifies the table from which to retrieve the data. -
WHERE condition: Filters rows based on the specified condition. -
GROUP BY group_column: Groups rows that have the same values in specified columns. -
HAVING group_condition: Filters groups based on a condition applied to the grouped data. -
ORDER BY sort_column: Sorts the result set by specified columns. -
LIMIT start_row, row_count: Limits the number of rows returned by the query starting fromstart_row.
-
Detailed Example
Example Table
employees
| id | name | department | salary | hire_date | age |
|---|---|---|---|---|---|
| 1 | John Doe | HR | 60000 | 2015-03-25 | 34 |
| 2 | Jane Smith | IT | 75000 | 2018-07-12 | 29 |
| 3 | Bob Johnson | Finance | 80000 | 2017-09-30 | 41 |
| 4 | Alice Brown | IT | 70000 | 2019-11-01 | 31 |
| 5 | Mary Davis | HR | 62000 | 2020-02-15 | 28 |
| 6 | James White | Finance | 85000 | 2016-12-10 | 38 |
1. Select Specific Columns
SELECT column_name(s)
-
Input:
Query to select the employee name and salary from the
employeestable.
SELECT name, salary FROM employees;
- Output:
| name | salary |
|---|---|
| John Doe | 60000 |
| Jane Smith | 75000 |
| Bob Johnson | 80000 |
| Alice Brown | 70000 |
| Mary Davis | 62000 |
| James White | 85000 |
2. From a Specific Table
FROM table_name
-
Input:
Query to select all columns from the
employeestable.
SELECT * FROM employees;
- Output:
| id | name | department | salary | hire_date | age |
|---|---|---|---|---|---|
| 1 | John Doe | HR | 60000 | 2015-03-25 | 34 |
| 2 | Jane Smith | IT | 75000 | 2018-07-12 | 29 |
| 3 | Bob Johnson | Finance | 80000 | 2017-09-30 | 41 |
| 4 | Alice Brown | IT | 70000 | 2019-11-01 | 31 |
| 5 | Mary Davis | HR | 62000 | 2020-02-15 | 28 |
| 6 | James White | Finance | 85000 | 2016-12-10 | 38 |
3. Filter Rows Using Conditions
WHERE condition
- Input: Query to select employees from the IT department.
SELECT * FROM employees WHERE department = 'IT';
- Output:
| id | name | department | salary | hire_date | age |
|---|---|---|---|---|---|
| 2 | Jane Smith | IT | 75000 | 2018-07-12 | 29 |
| 4 | Alice Brown | IT | 70000 | 2019-11-01 | 31 |
4. Group Results
GROUP BY group_column
- Input: Query to group employees by department and count the number of employees in each department.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
- Output:
| department | employee_count |
|---|---|
| HR | 2 |
| IT | 2 |
| Finance | 2 |
5. Filter Grouped Results
HAVING group_condition
- Input: Query to group employees by department and show departments with more than 1 employee.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
- Output:
| department | employee_count |
|---|---|
| HR | 2 |
| IT | 2 |
| Finance | 2 |
6. Sort the Results
ORDER BY sort_column
- Input: Query to select all employees and sort them by salary in descending order.
SELECT * FROM employees
ORDER BY salary DESC;
- Output:
| id | name | department | salary | hire_date | age |
|---|---|---|---|---|---|
| 6 | James White | Finance | 85000 | 2016-12-10 | 38 |
| 3 | Bob Johnson | Finance | 80000 | 2017-09-30 | 41 |
| 2 | Jane Smith | IT | 75000 | 2018-07-12 | 29 |
| 4 | Alice Brown | IT | 70000 | 2019-11-01 | 31 |
| 5 | Mary Davis | HR | 62000 | 2020-02-15 | 28 |
| 1 | John Doe | HR | 60000 | 2015-03-25 | 34 |
7. Limit the Results
LIMIT start_row, row_count
- Input: Query to select the first 3 employees from the result.
SELECT * FROM employees
LIMIT 0, 3;
- Output:
| id | name | department | salary | hire_date | age |
|---|---|---|---|---|---|
| 1 | John Doe | HR | 60000 | 2015-03-25 | 34 |
| 2 | Jane Smith | IT | 75000 | 2018-07-12 | 29 |
| 3 | Bob Johnson | Finance | 80000 | 2017-09-30 | 41 |
DCL (Data Control Language)
Create User
Explanation: Used to create a new database user.
Example:
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
Delete User
Explanation: Used to delete a database user.
Example:
DROP USER 'new_user'@'localhost';
Grant Authorisation to User
Explanation: Used to give a user specific permissions.
Example:
GRANT SELECT, INSERT ON my_database.* TO 'new_user'@'localhost';
Cancel Authorisation
Explanation: Used to revoke a user's permissions.
Example:
REVOKE INSERT ON my_database.* FROM 'new_user'@'localhost';
Show Authorisation
Explanation: Used to display the permissions granted to a user.
Example:
SHOW GRANTS FOR 'new_user'@'localhost';
TPL (Transaction Processing Language)
ACID
- Explanation: ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the properties that guarantee database transactions are processed reliably.
- Example:
- Atomicity: Ensures that all operations within a transaction are completed; otherwise, the transaction is aborted.
- Consistency: Ensures that the database remains in a valid state before and after the transaction.
- Isolation: Ensures that transactions do not affect each other.
- Durability: Ensures that the result of a transaction is permanent, even in the case of a failure.
Start Transaction
Explanation: Begins a new transaction.
Example:
START TRANSACTION;
Commit
Explanation: Saves the changes made by the transaction.
Example:
COMMIT;
Autocommit
Explanation: Enables or disables the automatic commit of SQL statements.
Example:
SET autocommit = 0; -- Disable autocommit
Rollback
Explanation: Undoes the changes made by the transaction.
Example:
ROLLBACK;
Full Transaction Example
Example Table
accounts
| id | username | balance |
|---|---|---|
| 1 | Alice | 1000.00 |
| 2 | Bob | 500.00 |
| 3 | Charlie | 750.00 |
START TRANSACTION;
-- Attempt to transfer $200 from Alice to Bob
UPDATE accounts SET balance = balance - 200 WHERE username = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE username = 'Bob';
-- Check if Bob's balance goes negative (for illustration)
SELECT balance FROM accounts WHERE username = 'Bob'; -- Assume this returns 700.00, no issue.
-- If all looks good, commit
COMMIT;
If an error occurs (e.g., if an unexpected condition arises), you might instead issue a ROLLBACK to ensure the accounts remain unchanged:
ROLLBACK;
Top comments (0)