1. Create Database: (To create)
CREATE DATABASE database_name;
2. Drop Database: (To delete)
DROP DATABASE database_name;
3. Create Table:
CREATE TABLE table_name(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
column_name4 data_type,
column_name5 data_type...
);
4. Alter Table:
# ALTER TABLE table_name ADD column_name data_type;
# ALTER TABLE table_name DROP COLUMN column_name;
# ALTER TABLE table_name ALTER COLUMN column_name data_type;
# ALTER TABLE table_name DROP COLUMN column_name;5. Drop Table:
DROP TABLE table_name;
6. Insert Data:
INSERT INTO table_name (column1, column2...) VALUES (value1, value2...);
7. Update Data:
# UPDATE table_name SET column1 = value1, column2 = value2... WHERE condition;
8. Select Data:
# SELECT column1, column2... FROM table_name;
# SELECT * FROM table_name;
# SELECT DISTINCT column1, column2... FROM table_name; (returns without duplicate)
# SELECT column1, column2... FROM table_name WHERE condition;
# SELECT * FROM table_name WHERE condition and condition; # SELECT * FROM table_name WHERE condition or condition;
# SELECT * FROM table_name WHERE not condition;
# SELECT * FROM table_name WHERE column_name BETWEEN 50 and 60; (for range)
9. Delete Data:
# DELETE FROM table_name WHERE condition;
# DELETE FROM table_name; (to delete all the data)
_______________________________________________________
The following constraints are commonly used in SQL:
- NOT NULL - Ensures that a column cannot have a NULL value...
- UNIQUE - Ensures that all values in a column are different...
- PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table...
- FOREIGN KEY - Prevents actions that would destroy links between tables...
- CHECK - Ensures that the values in a column satisfies a specific condition...
- DEFAULT - Sets a default value for a column if no value is specified...
- CREATE INDEX - Used to create and retrieve data from the database very quickly...
- AUTO_INCREMENT - allows a unique number to be generated automatically...
MySQL comes with the following data types for storing a date or a date/time:
- DATE - format YYYY-MM-DD
- DATETIME - format: YYYY-MM-DD HH:MI:SS
- TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
- YEAR - format YYYY or YY
_______________________________________________________
10. Create View:
CREATE VIEW [view_name] AS SELECT column1, column2 FROM table_name WHERE condition;
11. Read View:
SELECT * FROM [view_name];
12. Update View:
CREATE OR REPLACE VIEW [view_name] AS SELECT column1, column2... FROM table_name WHERE condition;
13. Drop View:
DROP VIEW [view_name];