MySQL cheat sheet provides a single page that contains all of the most commonly used commands and statements. This one-page cheat sheet helps us to work with MySQL database more effectively and quickly.
MySQL is an open-source widely used RDBMS database that provides high performance and scalable web-based and embedded database applications for the customers. It has many useful commands and statements to work with web-based and embedded database applications. The command in MySQL is a powerful directive used to perform a specific task or service. They are very helpful for every developer to know and use these queries for interacting with the MySQL databases in real-time. MySQL commands are based on a structured query language (SQL) which can support various operating systems such as Linux, UNIX, macOS, and Windows.
MySQL command-line client Commands. Connect to MySQL server using mysql command-line. $ mysql # on another terminal mysql UPDATE mysql.user SET password=PASSWORD('newpass') WHERE user='root'; ## Switch back to the mysqldsafe terminal and kill the process using Control + $ /etc/init.d/mysql start Your commands may vary depending on your OS. 0 Comments for this cheatsheet.
This MariaDB and MySQL cheat sheet covers useful commands for connecting to servers, analyzing data, and other key activities. The commands on this cheat sheet are valid for the interactive prompt and SQL scripts, but much can be extrapolated for use with programming libraries as well.
We can see a list of all commands in MySQL using the below query:
Let us see the most commonly used commands and statements that we need to create and manage the database in MySQL.
MySQL command-line client commands
This command allows us to connect with MySQL Server with a username and passwords using below syntax.
If you want to connect with a particular database, use this syntax:
If you want to set a new password, use this syntax:
We can use the 'exit' command to quit the MySQL command-line client.
We can clear the console window in Linux using the below command:
It is to be noted that there is no command available for Windows to clear the console window of MySQL.
Working with Database
A database stores the organized collection of records that can be accessed and manage by the user very easily. It holds the data into tables, rows, columns, and indexes that help us to find the relevant information quickly. We can create a database using the below syntax. It also checks the database name, whether it already exists or not.
If you want to change the current database with another database on which you are working, use the below syntax:
We can delete a particular database along with its associated files permanently using the below syntax:
To show all databases in the current server, use this syntax:
Working with tables
A table is a collection of related data stored in a row and column format within a database. We can create a new table using the below syntax. It also checks the table name, whether it already exists or not.
We can use the INSERT statement to add a record into the table, which is given below:
We can delete a particular table along permanently using the below syntax:
To show all tables in the current database, use this syntax:
We can add a new column to the table or can drop any column, use the following statements:
We can add a primary key into the table, or can remove it from the table, use the following statements:
If you want to add an index with a specific name on a column, use this syntax:
We can show the structure of a table using the below statement:
We can show the column information of a table using the below statement:
Working with Indexes
An index is a data structure that allows us to the faster retrieval of records on a database table. If we want to add an index in the table, we will use the statement as follows:
To drop an index, use this statement:
Working with View
The View in MySQL is used to create a virtual table by joining one or more tables. We can create or replace a VIEW by using SELECT statements as follows:
We can remove a view using the below statement:
We can rename a view using the below statement:
If you want to show all the views available in the database, use the below statement:
Oracle Sql Cheat Sheet
Working with Stored Procedure
A stored procedure is a group of SQL statements with a specific name that accepts some inputs and performs a certain task. It saves time to write the same code again and again in a database. We can create a stored procedure in MySQL using the below statements:
If you want to remove an existing stored procedure from the database, use the below statement:
If you want to show all procedures available in the database, use the below statement:
Working with Triggers
A trigger is a procedural code in a database that automatically executed whenever certain events on a particular table or view in the database occur. We can create a new trigger using the below statements:
Mysql Cheat Sheet Mosh
If you want to remove an existing trigger, use the below statement:
If you want to show all triggers available in the database, use the below statement:
Working with Stored Functions
A stored function in MySQL is a set of SQL statements that perform some task/operation and return a single value. It is one of the types of stored programs in MySQL. When you will create a stored function, make sure that you have a CREATE ROUTINE database privilege. The syntax of creating a stored function in MySQL is given below:
If you want to remove an existing trigger, use the below statement:
If you want to show all triggers available in the database, use the below statement:
Popular queries based on tables
Here, we will see some of the essential queries executed on tables to find the relevant information.
If we want to show all the table data, use this syntax:
If we want to show the data of some selected columns from a table, use this syntax:
Mysql Cheat Sheet Pdf
We can get filtered result from the table using the below statement:
Sometimes our tables contain duplicates rows. But we want to show only the unique(distinct) rows, in that case, the below statements will be executed:
Sometimes, the column name of a table is not appropriate for the output. In that case, we can use the below statement to change the column name in the result:
If we want to get the output from more than one table, we need to use the JOIN operation. The syntax for joining two tables is given below:
We can count the number of rows with this statement:
We can sort the result set using the following statement:
If we want to group rows into the result set, use the below syntax:
If we want to update the rows of the table, use the below syntax:
We can delete all records from the table using the below syntax:
Searching data from the table
We can search for data using the LIKE clause, as shown below:
We can also search for text using a regular expression with RLIKE operator, as shown below:
Control Option to the Administrator
Here, we will see the features that provide control options to the administrators and users on the database.
The grant statement enables system administrators to assign privileges and roles to the MySQL user accounts for accessing the database.
The revoke statement enables system administrators to revoke privileges and roles to the user accounts on the database in the past.
- MySQL Cheat Sheet
MySQL is the most popular, Oracle backed, open-source Structured Query Language. It is a Relational Database Management System. It is a component of the LAMP [Linux – Apache – MySQL – PHP/Python/Perl] Application Stack. Various other database-driven applications also implement it. MySQL 8.0 is the latest version available now. This MYSQL cheat sheet assumes that MySQL is already installed, and there is a MySQL Server accessible for connection.
MySQL Cheat Sheet
Mysql Cheat Sheet With Examples Pdf
1. MySQL Prompts
Mysql> | This prompt indicates ready for a new query. |
-> | Next line of a multi-line query |
`> | Continues to wait for identifier completion beginning with.` |
‘> | Continues to wait to the end of the string that was begun with ‘ |
“> | Continues to wait to the end of the string that was begun with “ |
/*> | Waits for the completion of a comment begun with /* |
2. Data Types
VARCHAR | A string of variable length up to 65535. |
CHAR | A fixed-length character variable. T is declared with the number of characters. Ex. CHAR(15) |
TEXT | Used to store long-form text strings. Generally used to store article bodies. |
TINY TEXTTINYTEXT | It is used to store short strings of information. It stores up to 255 bytes or 255 characters along with 1 byte as overhead. |
MEDIUMTEXT | It is useful for storing larger text strings like whitepapers, etc. These data objects can be of 16MB size. |
LONGTEXT | It is used for storing extreme long text strings. This can be of 4GB size. |
BLOB | These are binary strings which are treated as numeric values. They are used to store datafiles like images and videos. |
BIT | Stores bit values. |
BOOL | BOOLEAN | It holds either a 0 or a nonzero value. The value 0 is considered false, a non-zero value is considered TRUE. |
TINYINT | It holds an integer value. The range of a signed small integer is -128 to 127, The unsigned field is 0-255. |
SMALLINT | It holds an integer value. The range of a signed small integer is -32768 to 32767, The unsigned range is 0-65535 |
MEDIUMINT | It holds an integer value. The range of a signed small integer is -8388608 to 8388607, The unsigned range is 0-16777215 |
INT | Used to store an integer value. The range of a signed small integer is -2147483648 to 2147483647, The unsigned range is 0-4294967295 |
BIGINT | It is used to hold a big integer. Its signed range is -2E63 to 2E63 -1. The unsigned range is 0 to 2E64-1. |
FLOAT | It is a single precision floating-point number. Its permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. |
DOUBLE | It is a double precision floating-point number. Its permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. |
DECIMAL | Used to store an exact fixed-point decimal value. The maximum number of digits is 65, while the maximum number of the decimal is 30. |
ENUM | It is a string object. Its value is selected from a list of benefitsvalues. It uses numeric indexes to represent string values. |
JSON | Defines JSON datatype to store JSON documents. |
SET | It is a string object that enables us to store zero or more values from a list of pre-specified values when the table is created. |
DATE | Renders a datatype as a date value. – YYYY-MM-DD |
DATETIME | It is a combination of datetime. It displays datetime values in YYYY-MM-DD hh:mm:ss format. |
TIMESTAMP | This datatype holds a combination date and time within the range 1970-01-01 00:00:00 UTC to 2038-01-19 03:14:07 UTC |
TIME | Returns the current time. |
3. Date – Time Functions
Mysql Cheat Sheet Commands
TIMEDIFF() | Calculates the difference between two in terms of time between two time or datetime values. |
TIMESTAMPDIFF() | Returns the difference between two date or date-time values. |
CURDATE() | Gives the current date. |
NOW() | Returns the date and time of statement execution. |
DATADIFF() | Returns the number of days between the two dates |
DATE_FORMAT() | Reformats a date value based on the specified format. |
DAY() | Returns the day of the specified date. |
DAYNAME() | Returns the day name for the specified date. |
DAYOFWEEK() | Returns the day of the week index for a specified date. |
MONTH() | Returns the month number of the specified date. |
STR_TO_DATE() | Converts a string into date-time based on a specified format. |
SYSDATE() | Returns the system configured date. |
WEEK() | It returns the week number of a specified date. |
WEEKDAY() | Returns the index of the weekday of a specified date. |
YEAR() | Returns the year from a specified date. |
4. Working with Tables
CREATE | Used to create a new table |
INSERT | This command is used to insert one or more rows of data into the table. |
UPDATE | Used to modify existing data in a table. The WHERE command here is optional here. |
SELECT | Used to select list values from a table. |
SELECT DISTINCT | Used to select only unique values from a list. |
WHERE | The WHERE clause allows to select values based on specified conditions. |
ORDER BY | The ORDER BY clause is used to sort the queried result set in either ascending or Descending Order. By default, it will be Ascending Order. |
AND | This is a logical operator. It is mainly used with the WHERE clause. Here, the query statement is executed only when both the expressions are true. |
OR | This is a logical operator. It is mainly used with the WHERE clause. Here, the query statement is executed only when one or both the expressions are true. |
IN | The IN operator is used with the WHERE clause. It enables to determine if a specified value in a list matches in another set of values. |
BETWEEN | This operator is also used with the WHERE clause. It is used to specify whether a value is in a specified range. |
LIMIT | This clause is used with SELECT statement to specify the number of rows to be returned. |
IS NULL | It is used to test if a value is NULL or not. If it is NULL, the expression returns true, else false. |
INNER JOIN | This is a filter clause that matches each row in one table with every row in the other table thus enabling to query only those rows that have corresponding columns from both tables. |
LEFT JOIN | It allows you to query data from multiple tables. It matches each row from the first table to each row in the second table on the join_condition |
RIGHT JOIN | Same as LEFT_JOIN except that the table manipulation is in reverse order. It matches each row from the second table to each row in the first table on the join_condition |
CROSS JOIN | It returns the cartesian product of rows from the joined tables. |
GROUP BY | This command enables to group rows into subgroups based on column or expression values. |
HAVING | It is generally used with the GROUP BY clause. It is used to specify filter conditions for group of rows. |
ROLL UP | Used to generate the subtotals as well as grandtotals of fieldvalues. |
EXISTS | It is a Boolean operator that returns either true or false. It is generally used to determine if a query/subquery has returned any number of rows. |
INTERSECT | This is a set operator which returns only distinct rows of two or more queries. |
UNION | This command combines two or more result sets from multiple SELECT queries and returns a single result set. |
UPDATE JOIN | JOIN clauses when used with the UPDATE statement is called as UPDATE JOIN. |
DELETE | This command is used to delete data from table. |
DELETE JOIN | This command is used to delete data from multiple tables using the JOIN statement. |
ON DELETE CASCADE | This command enables deleting data from child table automatically when data from master table is deleted. |
REPLACE | This command is used to inert or update data in a table. |
Mysql Cheat Sheet Pentestmonkey
5. String Functions
ASCII() | Returns the ASCII value of the left most character of the string passed as an argument. |
BIN() | Returns the string representation of the binary value of the number passed as an argument. |
CHARACTER_LENGTH() | CHAR_LENGTH() | Returns the length of the string passed as an argument. |
LOWER() | Changes all the characters of the argument string into lower case. |
UPPER() | Changes all the characters of the argument string into lower case. |
CONCAT() | Appends one string at the end of the other string. Any number of string’s can be passed as an argument. If any argument is NULL, then the function will return NULL. |
LENGTH() | Returns the length of the string. |
LEFT() | Returns a specified number of characters from left most side of the argument string. |
RIGHT() | Returns a specified number of characters from right most side of the argument string. |
TRIM() | Removes all leading and trailing spaces from a string. |
LTRIM() | Removes all leading space from a character string. |
RTRIM() | Removes all trailing space from a character string. |
FORMAT() | Formats a number as ‘#,###,###.##’, rounded to a specified number of decimal places and returns the result as a string. |
SUBSTRING() | SUBSTR() | Returns a substring from an argument string, starting from a specified position, of a specified length. |
SUBSTRING_INDEX() | Returns a substring from an argument string before a ‘count’ number of occurrences of a specified delimiter. If the ‘count’ specified in the argument is positive, then all characters left of the string is returned, if it is negative, then all characters right of the string is returned. Further, it performs a case sensitive search for the specified delimiter. |
STRCMP() | It compares two strings passed in the argument and returns 0 if both are equal, -1 if the first string is smaller than the second and 1 if the first string is greater than the second. |
LIKE() | This command is used to match a string with a pattern on a per character basis. It returns 1 if there is a pattern, and 0 if there is no pattern. |
POSITION() |LOCATE() | Returns the position of a substring in a string. |
REVERSE() | Reverses the order of the string argument. |
REGEXP() | Returns 1 if the string argument matches a specified pattern, else returns 0. |
LOAD_FILE() | Returns the contents of a file as a string. |
6. Math Functions
ABS() | Returns the absolute value of a number passed to it as an argument | |
TRUNCATE() | TRUNCATE(number_to be truncated, number of decimal places to be truncated to) | It trims a number to a specified number of decimal places passed to it as an argument. |
ROUND() | It rounds a number to a specified number of decimal places passed to it as an argument. | |
MOD() | Returns the remainder of a number after dividing it with another number. | |
CEIL() | CEILING() | It returns the integer value which is equal to or is the next greater integer value of the specified number. Ex CEIL(3.5) – Returns 4. | |
CONV() | Converts a number of one base to a number of another base. | |
FLOOR() | It returns the integer value which is equal to or is the next smaller integer value of the specified number. Ex FLOOR(3.15) – Returns 3. FLOOR(-3.15) – Returns -4. | |
PI() | Returns the value of PI. | |
RAND() | Returns a random floating-point number within the range 0 - 1. | |
SIGN() | Returns the sign of a number passed as an argument. | |
SQRT() | Returns the square root of a number passed as an argument. | |
RADIANS() | Returns an argument number in terms of radians | |
MIN() | Returns the minimum value in a set of values. Here, ‘marks’ refers to the field name. | |
MAX() | Returns the maximum value in a set of values. Here, ‘marks’ refers to the field name. |
7. Information Functions
CONNECTION_ID() | Returns the Connection ID for a particular connection |
CURRENT_USER() | It returns the combination of the username and the hostname used by the MySQL account server to authenticate the current user. |
DATABASE() | Returns the default database name. |
SCHEMA() | Same as DATABASE() |
FOUND_ROWS() | Returns the number of rows found by a query without actually running the query. In order to refer to this value, later on, it needs to be saved. |
ROW_COUNT() | It returns the number of affected rows following a statement execution. |
LAST_INSERT_ID() | It returns a 64-bit value which is an automatically generated value that has been successfully inserted for an AUTO_INCREMENT column. This value remains unchanged if no new rows are inserted successfully. |
USER(), SESSION_USER() SYSTEM_USER | Returns the MySQL username and Hostname. |
VERSION() | Returns the MySQL Version number. |
CHARSET() | Returns the character set of the string argument. |
ENCRYPT() | Returns a binary string. |
DECODE() | Decodes or decrypts an encrypted string. |
MD5() | It calculates an MD5 128-bit checksum for the string and returns a string of 32 hexadecimal digits. |
PASSWORD() | Uses a cleartext password str to return a hashed password string. |
COMPRESS() | This command is used to compress a string and returns a binary string. |
UNCOMPRESS() | This command is used to uncompress a string that is compressed using the COMPRESS() command. |
SLEEP() | This command is used to pause for a specified number of seconds in the argument. |
DEFAULT() | Returns the default value for a field. If no default value is specified, it returns an error. |
8. Working With Databases
Show databases; | To list all the existing databases |
Create Database <database_name> | Creates a database of name <database_name> |
Use <database_name> | Selects the <database_name> to be used. |
Grant all on <database_name> to <mysql_user> @ <client_host> | Used to grant all permission on <database_name> to the mysql_user and the host_server |
Drop <database_name> | Delete database_name |
Mysql Cheat Sheet With Examples
9. Database Connection
mysql -h host_name -u user_name -p | Here the host_name refers to the name of the host where MySQL server is running. User_name is the user name of the MySQL account. |
QUIT | q | Disconnects from the server. |
10. PHP -> MYSQL
Included in the dbconfig.php file. It contains all the configured parameters for database configuration. |
This file can be named as phpmysqlconnect.php. Here the code for establishing database connection has been included. More code can be included here in order to handle exceptions in case the connection is not successful. |
11. Python – MySQL
Pip | Enables installation of MySQL Python connector on any Operating System. This includes Linux, Unix, macOS & Windows. |
This command will enable you to uninstall the current MySQL Connector/Python. | |
Connect() | Here the mysql.connector and Error objects are first imported from the Python package. The connect() function is used to connect to the MySQL server. The connect() function needs specification for 4 parameters. They are:
The is_connected() function is used to check if the connection has been established successfully. Close() function is used to close the database connection. |
12. Node.js – MySQL
Imports the mysql module | |
Connects to the MySQL Database by calling the creatconnection() method. | |
The end() method allows the execution of all remaining queries before closing the database connection. | |
Connection.destroy() | This method is used to close the connection immediately. It further does not allow any triggers for the connection. |
13. JDBC – MySQL
The three classes Connection, Driver Manager and SQL Exception need no be imported into the from the java.sql.*package. |
The getConnection() method is required to get the Connection Object. |
Download MySQL Cheat Sheet from Here
Conclusion
Today, MySQL is the most reliable and largely used database in the market. MySQL 8.0 is the latest version available now. It has designed improvements for Database Administrators and developers to develop and deploy high-end applications on highly powerful frameworks and hardware platforms. You can download MySQL 8.0 from the following link: https://www.mysql.com/downloads/. Also, find more information from its official site: https://www.mysql.com/.
People are also reading: