MYSQL Cheatsheet a quick and easy way to memorize MYSQL code
These are the most commonly used MySQL commands and statements that will help you work with MySQL more effectively
While I was coding a SaaS , I got myself thinking if there was a quick way to memorize Mysql commands just like I do with games like GT5 , well here is a snippet of what I found at Mysqltutirial.org a pretty cool website by the way.
MySQL command-line client Commands
Connect to database using a specified username and password:
mysql -u [username] -p [database];
Exit mysql command-line client:
exit;
To clear MySQL screen console window on Linux, you use the following command:
mysql> system clear;
There is no command on Windows OS for clearing MySQL screen console window.
Working with databases
Create a database with a specified name if it does not exist in the database server
CREATE DATABASE [IF NOT EXISTS] database_name;
Use a database or change the current database to another database that you are working with:
USE database_name;
Drop a database with a specified name permanently. All physical files associated with the database will be deleted.
DROP DATABASE [IF EXISTS] database_name;
Show all available databases in the current MySQL database server
SHOW DATABASE;
Working with tables
Show all tables in a current database.
SHOW TABLES;
Create a new table
CREATE TABLE [IF NOT EXISTS] table_name(
column_list
);
Add a new column into a table:
ALTER TABLE table
ADD [COLUMN] column_name;
Drop a column from a table:
ALTER TABLE table_name
DROP [COLUMN] column_name;
Add index with a specific name to a table on a column:
ALTER TABLE table
ADD INDEX [name](column, ...);
Add primary key into a table:
ALTER TABLE table_name
ADD PRIMARY KEY (column_name,...);
Remove the primary key of a table:
ALTER TABLE table_name
DROP PRIMARY KEY;
Drop a table:
DROP TABLE [IF EXISTS] table_name;
Show the columns of a table:
DESCRIBE table_name;
Show the information of a column in a table:
DESCRIBE table_name column_name;
Querying data from tables
Query all data from a table:
SELECT * FROM table_name;
Query data from one or more column of a table:
SELECT
column1, column2, ...
FROM
table_name;
Remove duplicate rows from the result of a query:
SELECT
DISTINCT (column)
FROM
table_name;
Query data with a filter using a WHERE
clause:
SELECT select_list
FROM table_name
WHERE condition;
Query data from multiple tables using inner join:
SELECT select_list
FROM table1
INNER JOIN table2 ON condition;
Query data from multiple tables using left join:
SELECT select_list
FROM table1
LEFT JOIN table2 ON condition;
Query data from multiple tables using right join:
SELECT select_list
FROM table1
RIGHT JOIN table2 ON condition;
Make a Cartesian product of rows:
SELECT select_list
FROM table1
CROSS JOIN table2;
Counting rows in a table.
SELECT COUNT(*)
FROM table_name;
Sorting a result set:
SELECT
select_list
FROM
table_name
ORDER BY
column1 ASC [DESC],
column2 ASC [DESC];
Group rows using the GROUP BY
clause.
SELECT select_list
FROM table_name
GROUP BY column_1, column_2, ...;
Filter group using the HAVING
clause:
SELECT select_list
FROM table_name
GROUP BY column1
HAVING condition;
Modifying data in tables
Insert a new row into a table:
INSERT INTO table_name(column_list)
VALUES(value_list);
Insert multiple rows into a table:
INSERT INTO table_name(column_list)
VALUES(value_list1),
(value_list2),
(value_list3),
...;
Update all rows in a table:
UPDATE table_name
SET column1 = value1,
...;
Update data for a set of rows specified by a condition in WHERE
clause.
UPDATE table_name
SET column_1 = value_1,
...
WHERE condition
Update with join
UPDATE
table1,
table2
INNER JOIN table1 ON table1.column1 = table2.column2
SET column1 = value1,
WHERE condition;
Delete all rows in a table
DELETE FROM table_name;
Delete rows specified by a condition:
DELETE FROM table_name
WHERE condition;
Delete with join
DELETE table1, table2
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2
WHERE condition;
Searching
Search for data using the LIKE
operator:
SELECT select_list
FROM table_name
WHERE column LIKE '%pattern%';
Text search using a regular expression with RLIKE
operator.
SELECT select_list
FROM table_name
WHERE column RLIKE 'regular_expression';