Database syntax (by chatGPT)
Tuesday, August 13, 2024 10:08 PM
Data Querying
• SELECT: Retrieve data from a table.
SELECT column1, column2 FROM table_name;
• WHERE: Filter records based on a condition.
SELECT column1, column2 FROM table_name WHERE condition;
• ORDER BY: Sort the result set by one or more columns.
SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];
• GROUP BY: Group rows that have the same values in specified columns.
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;
• HAVING: Filter groups based on a condition (used with GROUP BY).
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;
• JOIN: Combine rows from two or more tables based on a related column.
○ INNER JOIN: Only return matching rows.
SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;
○ LEFT JOIN (or LEFT OUTER JOIN): Return all rows from the left table, and the matched rows
from the right table.
SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;
○ RIGHT JOIN (or RIGHT OUTER JOIN): Return all rows from the right table, and the matched
rows from the left table.
SELECT table1.column1, table2.column2
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;
○ FULL JOIN (or FULL OUTER JOIN): Return all rows when there is a match in either left or
right table.
SELECT table1.column1, table2.column2
FROM table1
FULL JOIN table2 ON table1.common_column = table2.common_column;
Database Page 1
FULL JOIN table2 ON table1.common_column = table2.common_column;
2. Data Manipulation
• INSERT INTO: Insert new data into a table.
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• UPDATE: Modify existing data in a table.
UPDATE table_name SET column1 = value1 WHERE condition;
• DELETE FROM: Remove data from a table.
DELETE FROM table_name WHERE condition;
3. Data Definition
• CREATE TABLE: Create a new table.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
• ALTER TABLE: Modify an existing table.
○ Add a column:
ALTER TABLE table_name ADD column_name datatype;
○ Drop a column:
ALTER TABLE table_name DROP COLUMN column_name;
○ Modify a column's datatype:
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
• DROP TABLE: Delete a table.
DROP TABLE table_name;
• TRUNCATE TABLE: Remove all data from a table (but keep the structure).
TRUNCATE TABLE table_name;
4. Constraints
• PRIMARY KEY: Uniquely identifies each record in a table.
CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
...
);
Database Page 2
• FOREIGN KEY: Ensures referential integrity by linking two tables.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
FOREIGN KEY (column1) REFERENCES other_table(column)
);
• UNIQUE: Ensures all values in a column are unique.
CREATE TABLE table_name (
column1 datatype UNIQUE,
...
);
• NOT NULL: Ensures a column cannot have a NULL value.
CREATE TABLE table_name (
column1 datatype NOT NULL,
...
);
• CHECK: Ensures that values in a column satisfy a specific condition.
CREATE TABLE table_name (
column1 datatype CHECK (column1 > 0),
...
);
• DEFAULT: Sets a default value for a column.
CREATE TABLE table_name (
column1 datatype DEFAULT value,
...
);
5. Transactions
• START TRANSACTION: Begins a transaction.
START TRANSACTION;
• COMMIT: Save changes made in the transaction.
COMMIT;
• ROLLBACK: Revert changes made in the transaction.
ROLLBACK;
6. Views
• CREATE VIEW: Create a virtual table based on the result of a SELECT query.
Database Page 3
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;
• DROP VIEW: Delete a view.
DROP VIEW view_name;
7. Indexes
• CREATE INDEX: Create an index on a table for faster retrieval.
CREATE INDEX index_name ON table_name (column1, column2);
• DROP INDEX: Remove an index.
DROP INDEX index_name ON table_name;
8. Aggregate Functions
• COUNT(): Returns the number of rows.
SELECT COUNT(*) FROM table_name;
• SUM(): Returns the sum of a numeric column.
SELECT SUM(column_name) FROM table_name;
• AVG(): Returns the average of a numeric column.
SELECT AVG(column_name) FROM table_name;
• MAX(): Returns the maximum value in a column.
SELECT MAX(column_name) FROM table_name;
• MIN(): Returns the minimum value in a column.
SELECT MIN(column_name) FROM table_name;
Database Page 4
