Basic MySQL queries

Basic MySQL queries

• 1.
  MySQL Queries DDL (Datadefinition language) DATABASE TABLE Create database CREATE DATABASE<database_name>; Show databases list SHOW DATABASES; Select/Use database USE<database_name>; Delete/Drop database DROP DATABASE<database_name>; Create table CREATE TABLE <table_name> (< column_name1><datatype> [<constraint>], < column_name2><datatype> [<constraint>],…); Show tables list SHOW TABLES; Describe table(table structure) DESCRIBE<table_name>; Delete/Drop table DROP TABLE<table_name>; Rename table ALTER TABLE<old_table_name> RENAME TO<new_table_name>; Add a column ALTER TABLE <table name> ADD <new_column_name><datatype> [<constraint>]; Rename a column ALTER TABLE <table name> CHANGE <old_column_name><new_column_name><datat ype>[<constraint>]; Delete a column ALTER TABLE <table name> DROP <column_name>; Modify datatype or size ALTER TABLE <table name> MODIFY <column_name><datatype> [<constraint>]; COLUMN CONSTRAINTS Constrain ts Add constraint Drop constraint Not null ALTER TABLE <table name> MODIFY <column_name><datatype>NOT NULL; ALTER TABLE <table name> MODIFY <column_name><datatype>NUL
• 2.
  DML (Data Manipulationlanguage) INSERT Insert into all columns INSERT INTO <table_name> VALUES(<column1_value>,< column2_value>,……); Insert into specific columns INSERTINTO<table_name> (<column1>, <column2>,…) VALUES(<column1_value>, <column2_value>,……); SELECT Select all data (all rows all columns – full table) SELECT * FROM<table_name>; Select specific columns SELECT<column1, column2,….> FROM <table_name>; Select all data based on condition SELECT * FROM<table_name> WHERE <condition>; Select data in ascending/descending order SELECT * FROM<table_name> ORDER BY <column1><ascending/descending>; UPDATE Update values which satisfy condition UPDATE<table_name> SET<column_name>=<new_value>; Update all values of a specific column(same values for a particular column) UPDATE<table_name> SET<column_name>=<new_value> WHERE<condition>; L; Unique ALTER TABLE <table name> ADD CONSTRAINT <constraint_name> UNIQUE (<column_name>); ALTER TABLE <table name> DROP INDEX <constraint_name>; Check ALTER TABLE <table name> ADD CONSTRAINT <constraint_name> CHECK (<column_name><condition>); ALTER TABLE <table name> DROP CHECK <constraint_name>; Primary key ALTER TABLE <table name> MODIFY <column_name><datatype> PRIMARY KEY; ALTER TABLE <table name> DROP PRIMARY KEY; Default ALTER TABLE <table name> ALTER <column_name> SET DEFAULT<value>; ALTER TABLE <table name> ALTER <column_name> DROP DEFAULT; Foreign key ALTER TABLE <table name> ADD CONSTRAINT <constraint_name> FOREIGN KEY (<column_name>) REFERENCES <table name1>(<column_name>); ALTER TABLE <table name> DROP FOREIGN KEY <constraint_name>;
• 3.
  DELETE Delete specific rowsbased on condition DELETE FROM <table_name> WHERE <condition>; Clear all data of a table(delete all rows) DELETE FROM <table_name>; Identifiers- An Identifier is essentially a name of a database_name, table_name, column_name or constraint_name. Rules- 1. First letter must be alphabet (a-z, A-Z). 2. Second and other letters can be numbers (0-9), alphabet (a-z, A-Z) and _ (underscore). 3. Must not be a keyword.