- Mysql Commands Pdf
- Mysql Command Line Cheat Sheet Free
- Mysql 8.0 Command Line Client
- Mysql Command Line Cheat Sheet Pdf
- Mysql Cheat Sheet Pdf
- Mysql Command Line
- Mysql Command Line Cheat Sheet Printable
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.
Handy MySQL Commands: Description: Command: To login (from unix shell) use -h only if needed. mysql dir/bin/mysql -h hostname -u root -p: Create a database on the sql server. Create database databasename; List all databases on the sql server. Show databases; Switch to a database. Use db name; To see all the tables in the db. MySQL Command Line Cheat Sheet Updated April 1, 2021 1 Comment Originally posted October 7, 2015. MySQL cheat sheets SQL queries. These are my most often used MySQL.
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.
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.
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.
MySQL command-line client commands
This command allows us to connect with MySQL Server with a username and passwords using below syntax.
- Comments # /. -;%00 Version SELECT VERSION; SELECT @@VERSION; SELECT @@GLOBAL.VERSION; User details user currentuser systemuser session.
- Mysql -user=username-password dbname. In this case, you'll need to enter your password in response to the prompt that mysql displays: Enter password: yourpassword. Then type an SQL statement, end it with;, g, or G and press Enter. Typing Control+C interrupts the current statement if there is one, or cancels any partial input line otherwise.
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:
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:
Mysql Commands Pdf
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:
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:
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.
Mysql Command Line Cheat Sheet Free
The revoke statement enables system administrators to revoke privileges and roles to the user accounts on the database in the past.
This document is designed to be a cheat-sheet for MySQL. I don’t plan to cover everything, just most things that a novice MySQL DBA is likely to need often or in a hurry.
If you are going to provide a database service to other machines edit /etc/mysql/my.cnf and set the bind-address parameter to a suitable value. A value of 0.0.0.0 will cause it to accept connections on any of the server’s addresses. I recommend using a private address range (10.0.0.0/8, 192.168.0.0/16, or 172.16.0.0/12) for such database connections and ideally a
back-end VLAN or Ethernet switch that doesn’t carry any public data.
For the purpose of this post let’s consider the MySQL server to have a private IP address of 192.168.42.1. So you want the my.cnf file to have bind-address = 192.168.42.1
To start mysql administration use the command mysql -u root. In Debian the root account has no password by default, on CentOS 5.x starting mysql for the first time gives a message:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h server password ‘new-password’
That is wrong, for the second mysqladmin command you need a “-p” option (or you can reverse the order of the commands).
There is also the /usr/bin/mysql_secure_installation script that has an interactive dialog for locking down the MySQL database.
If you lose the administration password the recovery process is as follows:
- Stop the mysqld, this may require killing the daemon if the password for the system account used for shutdown access is also lost.
- Start mysqld with the --skip-grant-tables option.
- Use SQL commands such as “UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';” to recover the passwords you need.
- Use the SQL command “FLUSH PRIVILEGES;“
- Restart mysqld in the normal manner.
For an account to automatically login to mysql you need to create a file named ~/.my.cnf with the following contents:
[client]
user=USERNAME
password=PASSWORD
database=DBNAME
Replace USERNAME. PASSWORD, and DBNAME with the appropriate values. They are all optional parameters. This saves using mysql client parameters -u parameter for the username, “-p for the password, and specifying the database name on the command line. Note that using the “-pPASSWORD” command-line option to the mysql client is insecure on multi-user systems as (in the absence of any security system such as SE Linux) any user can briefly see the password via ps.
Note that the presence of the database= option in the config file breaks mysqlshow and mysqldump for MySQL 5.1.51 (and presumably earlier versions too). So it’s often a bad idea to use it.
To grant all access to a new database:
CREATE DATABASE foo_db;
USE foo_db;
GRANT ALL PRIVILEGES ON foo_db.* to 'user'@'10.1.2.3' IDENTIFIED BY 'pass';
Where 10.1.2.3 is the client address and pass is the password. Replace 10.1.2.3 with % if you want to allow access from any client address.
Note that if you use “foo_db” instead of “foo_db.*” then you will end up granting access to foo_db.foo_db (a table named foo_db in the foo_db database) which generally is not what you want.
To grant read-only access replace “ALL PRIVILEGES” with “SELECT“.
To show what is granted to the current user run “SHOW GRANTS;” .
To show the privs for a particular user run “SHOW GRANTS FOR ‘user’@’10.1.2.3’;”
To show all entries in the user table (user-name, password, and hostname):
USE mysql;
SELECT Host,User,Password FROM user;
To do the same thing at the command-line:
echo “SELECT Host,User,Password FROM user;” | mysql mysql
To revoke access:
REVOKE ALL PRIVILEGES ON foo_db.* FROM user@10.1.2.3 IDENTIFIED BY ‘pass’;
To test a user’s access connect as the user with a command such as the following:
mysql -u user -h 10.1.2.4 -p foo_db
Then test that the user can create tables with the following mysql commands:
CREATE TABLE test (id INT);
DROP TABLE test;
To list all databases that are active on the selected server run “mysqlshow“, it uses the same methods of determining the username and password as the mysql client program.
To list all tables in a database run “SHOW TABLES;” . For more detail select from INFORMATION_SCHEMA.TABLES or run “SHOW TABLE STATUS;”
For example to see the engine that is used for each table you can use the command echo “SELECT table_schema, table_name, engine FROM INFORMATION_SCHEMA.TABLES;” |mysql.
But INFORMATION_SCHEMA.TABLES is only in Mysql 5 and above, for prior versions you can use mysqldump -d to get the schema, or “SHOW CREATE TABLE table_name;” at the command-line.
Also the mysqldump program can be used to display the tables in a database via “mysqlshow database” or the columns in a table via “mysqlshow database table“.
To list active connections: “SHOW PROCESSLIST;”
Mysql 8.0 Command Line Client
The program mysqldump is used to make a SQL dump of the database. EG: “mysqldump mysql” to dump the system tables. The data compresses well (being plain text of a regular format) so piping it through “gzip -9” is a good idea. To backup the system database you could run “mysqldump mysql | gzip -9 > mysql.sql.gz“. To restore simply run “mysql -u user database < file“, in the case of the previous example “zcat mysql.sql.gz | mysql -u root database“.
To dump only selected tables you can run “mysqldump database table1 [table2]“.
The option --skip-extended-insert means that a single INSERT statement will be used for each row. This gives a bigger dump file but allows running diff on multiple dump files.
Mysql Command Line Cheat Sheet Pdf
The option --all-databases or -A dumps all databases.
The option --add-locks causes the tables to be locked on insert and improves performance.
Note that mysqldump blocks other database write operations so don’t pipe it through less or any other process that won’t read all the data in a small amount of time.
mysqldump -d DB_NAME dumps the schema.
The option --single-transaction causes mysqldump to use a transaction for the dump (so that the database can be used in the mean time). This only works with INNODB. To convert a table to INNODB the following command can be used:
ALTER TABLE tablename ENGINE = INNODB;
Mysql Cheat Sheet Pdf
To create a slave run mysqldump with the --master-data=1.
Mysql Command Line
When a master has it’s binary logs get too big a command such as “PURGE MASTER LOGS BEFORE ‘2008-12-02 22:46:26’;” will purge the old logs. An alternate version is of the form “PURGE MASTER LOGS TO ‘mysql-bin.010’;“. The MySQL documentation describes how to view the slave status to make sure that this doesn’t break replication.
Mysql Command Line Cheat Sheet Printable
No related posts.