iS0wPRKD

1Home / MySQL Cheat Sheet
2MySQL Cheat Sheet
3MySQL cheat sheet provides you with the most commonly used MySQL commands and statements that help you work with MySQL more effectively.
4
5MySQL Commands
6Access MySQL server from the mysql client using a username and password (MySQL will prompt for a password):
7
81
9mysql -u [username] -p;
10Access a specific database using a username and password:
11
121
13mysql -u [username] -p [database];
14Exit
15
161
17exit;
18Export data using mysqldump tool
19
201
21mysqldump -u [username] -p [database] > data_backup.sql;
22To clear MySQL screen console window on Linux, you use the following command:
23
241
25mysql> system clear;
26Currently, there is no command available on Windows OS for clearing MySQL screen console window.
27
28Working with Database
29Create a database with a specified name if it does not exist in database server
30
311
32CREATE DATABASE [IF NOT EXISTS] database_name;
33Use database or change current database to another database you are working with
34
351
36USE database_name;
37Drop a database with specified name permanently. All physical file associated with the database is no longer exists.
38
391
40DROP DATABASE [IF EXISTS] database_name;
41Show all available databases in the MySQL database server
42
431
44show databases;
45Working with Table
46Lists all tables in a current database.
47
481
49show tables;
50Create a new table or a temporary table
51
521
532
543
554
565
576
58CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table(
59   key type(size) NOT NULL PRIMARY KEY AUTO_INCREMENT,
60   c1 type(size) NOT NULL,
61   c2 type(size) NULL,
62   ...
63);
64Altering table structure
65There are many actions that you can use with the ALTER TABLE statement as follows:
66
67Add a new column into a table
68
691
70ALTER TABLE table ADD [COLUMN];
71Drop an existing column in a table
72
731
74ALTER TABLE table DROP [COLUMN];
75Add index with a specific name to a table on a column.
76
771
78ALTER TABLE table ADD INDEX [name](column, ...);
79Add primary key into a table.
80
811
82ALTER TABLE table ADD PRIMARY KEY (column,...)
83Remove primary key from a table.
84
851
86ALTER TABLE table DROP PRIMARY KEY
87Deleting table structure and data permanently.
88
891
902
91 DROP TABLE [IF EXISTS] table [, name2, ...]
92 [RESTRICT | CASCADE]
93Get information about th table or a column.
94
951
96DESCRIBE table [column]
97Working with Index
98Creating an index with the specified name on a table
99
1001
1012
102CREATE [UNIQUE|FULLTEXT] INDEX index_name
103ON table (column,...)
104Removing a specified index from table
105
1061
107DROP INDEX index_name
108Querying Data
109Query all data from a table
110
1111
112SELECT * FROM table
113Query specified data which is shown in the column list from a table
114
1151
1162
117SELECT column, column2….
118FROM table;
119Query unique records
120
1211
1222
123SELECT DISTINCT (column)
124FROM table;
125Query data with a filter using a WHERE clause.
126
1271
1282
1293
130SELECT *
131FROM table
132WHERE condition;
133Change the output of the column name using column alias.
134
1351
1362
137SELECT column_1 AS new_column_1, ...
138FROM table
139Query data from multiple tables using inner join or left join
140
1411
1422
1433
144SELECT * 
145FROM table_1
146INNER JOIN table_2 ON conditions
1471
1482
1493
150SELECT * 
151FROM table1
152LEFT JOIN table2 ON conditions
153Counting rows in a table.
154
1551
1562
157SELECT COUNT (*)
158FROM table
159Sorting ascending or descending based on one or more columns using ORDER BY clause.
160
1611
1622
1633
164SELECT column, column2, ...
165FROM table
166ORDER BY column ASC [DESC], column2 ASC [DESC],...;
167Group rows using GROUP BY clause.
168
1691
1702
1713
172SELECT *
173FROM table
174GROUP BY column_1, column_2, ...;
175Filter group of rows using both GROUP BY and HAVING clauses.
176
1771
1782
1793
1804
181SELECT *
182FROM table
183GROUP BY column_1
184HAVING condition;
185Modifying Data
186Insert a new row into a table
187
1881
1892
190INSERT INTO table(column1,column2,...)
191VALUES(value_1,value_2,...);
192Insert multiple rows into a table
193
1941
1952
1963
1974
198INSERT INTO table(column1,column2,...)
199VALUES(value_1,value_2,...),
200      (value_1,value_2,...),
201      (value_1,value_2,...)...
202Update data for all rows
203
2041
2052
2063
207UPDATE table
208SET column_1 = value_1,
209    ...
210Update data for a set of rows specified by a condition in WHERE clause.
211
2121
2132
2143
2154
216UPDATE table
217SET column_1 = value_1,
218    ...
219WHERE condition
220Update with join
221
2221
2232
2243
2254
226UPDATE table_1, table_2
227INNER JOIN table_1 ON table_1.column_1 = table_2.column_1
228SET column_1 = value_1,
229WHERE condition
230Delete all rows in a table
231
2321
233DELETE FROM table;
234Delete rows specified by a condition
235
2361
2372
238DELETE FROM table
239WHERE condition;
240Delete with join
241
2421
2432
2443
2454
246DELETE table_1, table2
247FROM table_1
248INNER JOIN table_2 ON table_1.column_1 = table_2.column_2
249WHERE condition;
250Search
251Search for data using LIKE operator:
252
2531
2542
255SELECT * FROM table
256WHERE column LIKE '%value%'
257Text search using a regular expression with RLIKE operator.
258
2591
2602
261SELECT * FROM table
262WHERE column RLIKE 'regular_expression'