Cheat Sheet MySQL
The MySQL database management system is an open-source database management system. There are a lot of features in MySQL, which makes it more flexible and extensible than most other databases. MySQL is a free database, so there is no need to pay anything to use it. It is able to handle a large set of functionality that is normally associated with expensive and powerful database packages of its own. There are a number of things that make it work well and quickly with a large set of data. One of the best features of cheat sheet MySQL is the fact that it is fully customizable, so it can be modified to fit into specific environments.
In general, MySQL works well with a number of programming languages, such as C, PHP, Perl, and so on. It is mainly used with MySQL that PHP is used. There is no doubt that it is one of the most reliable databases out there, and it gives great performance and ensures the security of data in the process of connecting to different programming languages through different modes. The MySQL database management system is referred to as a relational database management system (RDBMS). One of the main disadvantages of MySQL is that it is primarily only available in a single language, which is the English language. The application was developed by Oracle Corporation and was released in 1995 as a result of its development. Multiple operating systems are supported, such as Windows, Mac OS, Linux, Solaris, and Free BSD. MySQL was developed using the C and C++ programming languages.
Commands and Content on Cheat Sheet MySQL
An important feature of MySQL is that it has a large library, which is scalable, extendable, interactive, and contains many in-built methods for computing common operations. For performing command-line operations, there are different commands available that can be used, and each command has both a short-form and a long-form corresponding to it. Unlike the long-form, the short-form is case-sensitive, whereas the long-form is not case-sensitive. Here are the MySQL commands mentioned in the Cheatsheet that perform different types of operations.
| COMMAND | DESCRIPTION |
| help, \h, \? | This will display a list of all the MySQL commands that are available |
| Clear, \c | As a result, the user input is cleared |
| Connect,\r | Make sure that you are connected to the server. |
| Delimiter, \d | As a result, the statement delimiter was set. |
| Edit, \e | With the help of $EDITOR, you can edit the command. |
| Ego,\G | The result is displayed vertically on the screen. |
| Exit, \q | As a result, MySQL will be exited, quit, or closed. |
| Go,\g | A command will be sent to the SQL server as a result of this. |
| Nopager, \n | The pager is displayed, and the printout is sent to the standard output. |
| Notee, \t | It is not a good idea to write to the outfile. |
| Pager,\P | Set up a pager and print the results using the pager. |
| Print, \p | As a result, the current command will be printed out |
| Prompt, \R | The MySQL prompt needs to be changed. |
| Rehash, \# | The completion hash needs to be rebuilt. |
| Tee,\T | Create an outfile and append it to the outfile |
| Source, \. | An SQL script file needs to be executed. |
| Status, \s | From the server, it will get the information about the status of the application |
| System, \! | It is necessary to execute the following command in the shell of the system |
| Use, \u | With the help of the DB name, you can use another database as an argument. |
| Charset, \C | Defining the charset is the next step. |
| Warnings, \W | Each statement will be accompanied by a warning at the end of it. |
| Nowarning,\w | There will be no warning displayed after every statement that is made. |
| Resetconnection, \x | As a result, the session context will be cleaned. |
Data types: When inserting specific values into the columns of a database, MySQL has different types of data that can be used. You can use ‘int’ for integers. Other values such as float, double, timestamp, char, varchar, blob, enum are also being used for inserting specific values into the database.
String functions:The following string functions are also available for the use of the user:
| String function | DESCRIPTION |
| strcmp(s1, s2) | A comparison of the strings is carried out |
| Lower(s) | Change the capitalization to lowercase |
| Upper(s) | Please convert the lower case letters to upper case letters |
| Substring(s, index1, index2) | The substring of the string is taken as input |
| Ltrim(s) | Left-trim |
| Curdate() | Find out what the date is today |
| Curtime() | Find out what time it is. |
| Dayname(s) | In the event that we have to extract the name of the day from the data. |
| Dayofweek(s) | Find out what the particular day number is for that particular day. |
| Monthname(s) | Please let me know if you need to extract a month. |
Mathematical Functions: – The different functions available in MySQL’s math module are:
| FUNCTION | DESCRIPTION |
| COUNT(*) | Count the rows per group |
| AVG(column) | The average value of a group |
| MIN(column) | A minimum value of a group |
| MAX(column) | A maximum value of a group |
| SUM(column) | Sum values in group |
| Abs(number) | Absolute value |
| Round(number) | Rounding number |
| Floor(number) | The number is the largest integer but not greater |
| Ceiling(number) | Smallest integer not smaller |
| Sqrt(number) | Square root |
| Pow(base, exponent) | nth power |
| Rand() | Random number |
| Sin(number) | Sin function |
| Cos(number) | Cos function |
Free Tips and Tricks of using Cheatsheet MySQL Commands
- The command ‘SHOW DATABASES;’ can be used in MySQL cheat sheet to obtain a list of all available databases in case a list of all databases is required.
- A new database is created by using the ‘CREATE DATABASE dbname;’ command in order to create a new database.
- In order to use the particular database, the command ‘USE database;’ must be used.
- It is absolutely necessary to use the list of all tables in the MySQL database in order to use the ‘SHOW TABLES;’ command.
- In order to display the structure of the table, the commands ‘Describe table; Show columns from a table’ are used.
- It is possible to delete a database by using the ‘DROP DATABASE dbname;’ command. It is important to take care when using this command, as after it has been executed, a rollback cannot be performed.
- It is recommended to use the DISTINCT keyword when selecting data from a table if duplicate data is not required. This will prevent the data from being repeated. You can use a query such as: Select a DISTINCT column from a table;
- Additionally, there are other queries that are available for inserting, updating, deleting, ordering, grouping the columns, as well as joining two or more tables together. In this way, the data is processed faster and can be retrieved more quickly.
- Inserting values into the table: Insert values into the table (v1, v2);
- Inserting column: Add a column columntype options to the table tablename;
- Updating: Update table to set column1=x where 1=1 (conditions);
- Deleting: Remove the row from the table where 1=1(conditions);
- Deleting the column: Alter the table tablename and drop the column columnname;
9. The following commands can be used if you have forgotten the root password:
$ /etc/init.d/mysql stop $ mysqld_safe –skip-grant-tables & $ mysql # on another terminal mysql> UPDATE mysql.user SET password=PASSWORD(‘P@SSW0rd’) WHERE user=’root’; $ /etc/init.d/mysql start
10. In the event that the table needs to be repaired after a shutdown that was not done properly:
mysqlcheck –all-databases –fast11. In case the data needs to be loaded from the file, then use the following command to do so:
mysql> SOURCE input_file $ mysql database < filename.sql $ cat filename.sql | mysql database
