1. Introduction to MySQL
In my previous tutorial, I discussed about installation of MySQL server and getting started with the use of MySQL database. In this article we would be discussing in detail about using the MySQL command line to perform various operations like select, insert, update, delete, database administration, authorization management and several other operations necessary while using MySQL database.
The pre-requisite for the tutorial is to have the MySQL server pre-installed. The article guides you well for progressing with the installation of the same.
Table Of Contents
2. Connecting to MySQL command line
Once the database server is up and running, you could login to the MySQL command line using the below command.
$ mysql -u root -p
Once connected, the command prompt or terminal displays mysql> acknowledging that you are using the MySQL command line now. In case you see an error like mysql command not found, it would be because of missing MySQL path variable. You could resolve this by following the below process:
Windows
In case of Windows, right click the My Computer/This PC icon and select System Properties. In the system properties, select Advanced tab and click Environment Variables. In the environment variables, look for the PATH variable and double click it to edit. Append the MySQL installation path to the variable after a semicolon at the end. Apply the changes and try to execute the above command in the command prompt once again.
Linux/MacOS
In Linux as well as MacOS variants, the process remains similar. In order to enable direct detection of mysql command, execute the below command after replacing the MySQL installation path in the command.
export PATH=$PATH:
Once connected, the MySQL command line can be used for executing any SQL queries whether it is a CRUD operation or a data definition or administration query. To start with, let us check the list of available databases using the below command:
mysql> show databases;
In case of a fresh installation, it would just display the list of MySQL default databases that are used for administration purpose.
3. Creating database and tables
Let us proceed with the creation of database. A database in MySQL is known as schema. In order to create a schema, execute the below command:
mysql> create schema tutorial;
Ensure the semicolon at the end. In case you miss the semicolon, the statement is not considered to be complete and you would be prompted for further instruction as shown below.
mysql> create schema tutorial
-> ;
Query OK, 1 row affected (0.09 sec)
As soon as a semicolon is entered, it executes the queries written before it. Now you could check the list of databases at your end to verify that the database is created.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | tutorial | +--------------------+ 5 rows in set (0.01 sec)
Now let us use the database and create tables in it. Let us create a simple table of student details.
mysql> use tutorial Database changed mysql> create table student_details (id int, name varchar(50), class varchar(3)); Query OK, 0 rows affected (0.07 sec)
The commands above first selects the tutorial database and later creates table with three columns. Let us create two more tables to work with further queries.
mysql> create table subjects (id int, name varchar(50)); Query OK, 0 rows affected (0.11 sec) mysql> create table marks (id int, subject_id int, student_id int, marks int); Query OK, 0 rows affected (0.05 sec)
Now, let us check the tables are created in the database.
mysql> show tables; +--------------------+ | Tables_in_tutorial | +--------------------+ | marks | | student_details | | subjects | +--------------------+ 3 rows in set (0.00 sec)
4. Inserting records in tables
MySQL command line supports the standard SQL command for insertion of records. To insert records into the created tables, execute the below commands.
Insert into student_details
mysql> mysql> insert into student_details values (1,'Abhishek','XII'); Query OK, 1 row affected (0.10 sec)
The above statement inserts a single record into the student_details table. In order to insert multiple records, you could either join the value sets together in a single line or add the values into separate lines without a semicolon. Both the methods are shown below.
Adding one record per line
mysql> insert into student_details values (2,'John','XII')
-> ,(3,'Ted','XI')
-> ,(4,'Tim','XII');
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
Adding records in a single line
mysql> insert into student_details values (2,'John','XII'),(3,'Ted','XI'),(4,'Tim','XII'); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0
5. View records and format output
Now, we have got few records in the student_details table. Let us view the records now.
5.1 View all the records
In order to view all the records, all you need to do is to execute the select query on the student_details table.
mysql> select * from student_details; +------+----------+-------+ | id | name | class | +------+----------+-------+ | 1 | Abhishek | XII | | 2 | John | XII | | 3 | Ted | XI | | 4 | Tim | XII | +------+----------+-------+ 4 rows in set (0.00 sec)
5.2 View formatted output
By default, the records are displayed in a tabular console output as shown above. MySQL also provides facility to format the console output using formatting commands. For instance, in order to get a row-wise output, one can use \G as shown below:
mysql> select * from student_details \G; *************************** 1. row *************************** id: 1 name: Abhishek class: XII *************************** 2. row *************************** id: 2 name: John class: XII *************************** 3. row *************************** id: 3 name: Ted class: XI *************************** 4. row *************************** id: 4 name: Tim class: XII 4 rows in set (0.00 sec)
5.3 View limited records
Occasionally, there might be a need where you would like to see a limited number of records like when the number of records in a table is huge or when you try to implement pagination. In such a case, you need to limit the number of records that are being returned. In MySQL, this is made possible by the keyword limit. It basically limits the number of records being fetched from the table. For instance,
mysql> select * from student_details limit 2; +------+----------+-------+ | id | name | class | +------+----------+-------+ | 1 | Abhishek | XII | | 2 | John | XII | +------+----------+-------+ 2 rows in set (0.04 sec)
In this manner, it is possible to limit the number of records that are being returned in the result set.
5.4 Offsetting the results
In case of a pagination scenario, the need comes for offsetting. Offsetting is basically a mechanism to skip a certain number of records and fetch the next ones. For instance, if a table contains 40 records and we create a table where we display 10 records per page. In such a case, in the second page, we need to fetch 10 records after skipping the first 10. This is possible using the keywords offset. An example of the same is displayed below.
mysql> select * from student_details limit 2 offset 2; +------+------+-------+ | id | name | class | +------+------+-------+ | 3 | Ted | XI | | 4 | Tim | XII | +------+------+-------+ 2 rows in set (0.00 sec)
6. Update & Delete records
The next task that we usually need to do after inserting the records is to correct or delete them. As discussed above, MySQL command handles all the SQL queries just like a normal Database client. Let us try to update the record of student with id-3. The class is set to XI instead of XII. Let us rectify the same using an update query.
mysql> update student_details set class='XII' where id= 3; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0
As it can be seen, the command line displays a summary of records that were matched and changed. It also counts the number of warnings that might be raised during update for several possible reasons. Now, let us try to check update with an invalid data. For instance,
mysql> update student_details set class='XIIII' where id= 3; ERROR 1406 (22001): Data too long for column 'class' at row 3
As it can be seen, whenever you supply an invalid value an error occurs. Every error is assigned a relevant error code rather than just display the message. This error code is used by the database clients to display a sensible error with better details.
Now, consider the case when Tim leaves the school. The record of Tim is no longer required and hence needs to be deleted. In order to delete the record, let us execute the delete script as shown below:
mysql> delete from student_details where id = 4; Query OK, 1 row affected (0.03 sec)
The count of deleted rows is displayed by the number of rows that were affected. Thus, it can be understood that one record has been deleted successfully.
7. Toggle Safe update and Autocommit
Consider a scenario where you erroneously execute a delete query without a where clause. If there are no restrictions on execution of such a query, it would result in loss of records. There are two possible ways to stop this.
- Stop the query execution without a key field condition
- Disable auto-commit to allow the user to rollback
7.1 Enable/Disable safe update
By default, the safe update is turned off in MySQL command line. This allows the user to execute a query shown below.
mysql> delete from student_details;
Such a query would delete all the existing records from the table resulting in loss of data. In order to disable execution of such a query, you can mandate the where clause containing a key field condition. For instance, you can mandate a where clause with id field in the condition. This can be done using the below query.
mysql> set sql_safe_updates=1; Query OK, 0 rows affected (0.01 sec)
Now, if we try to execute the above delete query, the output as shown below will be displayed.
mysql> delete from student_details; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
With the safe update mode turned on, you cannot execute a delete or update query without id field in the where clause. Notice here that since there is no key field in the student_details table currently, we would not be able to delete or update any record. In order to do so, you would need to alter the table and add a primary key. This process is covered further in the article. For now, you can disable the safe update mode using the below statement.
mysql> set sql_safe_update = 0;
7.2 Disable auto commit
Currently, if a delete query is executed by mistake, there is no way to get the data back. This is because MySQL has its auto commit feature turned on by default for the command line. Auto commit basically means that it automatically applies all the changes to the actual database.
On disabling the auto commit, the changes being done using the command line will be cached until we ask the database to commit it explicitly. Once the auto-commit is disabled, if a query is executed, you can rollback the changes by executing a simple command – rollback. In order to disable auto commit, use the below statement.
mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)
Once the auto-commit is turned off, execute the below mentioned delete query with safe update mode turned off.
mysql> delete from student_details; Query OK, 4 rows affected (0.00 sec) mysql> select count(*) from student_details; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec) mysql> rollback; Query OK, 0 rows affected (0.06 sec) mysql> select count(*) from student_details; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)
As it can be seen in the above process of execution of delete query, after the execution of delete query, if you select the data from the table, there are no records available. However, after rollback, you can see that the 4 records are intact and the delete action has been reverted. Thus, turning off auto-commit can have a significant impact in the regular use of MySQL command line.
8. MySQL administration
Once we have understood the MySQL database creation and update, it is time to look into managing the database. Database administration is an important task when it comes to a large project. Every database has its own way of storing administrative data. MySQL stores the administrative data in two different databases. The first database being used for administrative purpose is the database named mysql. This section covers only the tables of primary importance for the beginner.
Let us select the database and check the list of available tables.
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | password_history | | plugin | | procs_priv | | proxies_priv | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 33 rows in set (0.01 sec)
As it can be seen, this database alone contains a bunch of 33 tables. Of these, the tables of primary importance are user & global_grants. The user table contains the list of users that have been created so far. On viewing the records, you would find three additional users in addition to the default root user. These users are created by default during the installation and should not be updated unless necessary.
mysql> select * from user \G;;
*************************** 1. row ***************************
Host: localhost
User: mysql.infoschema
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2018-05-13 14:47:36
password_lifetime: NULL
account_locked: Y
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
*************************** 2. row ***************************
Host: localhost
User: mysql.session
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2018-05-13 14:47:36
password_lifetime: NULL
account_locked: Y
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
*************************** 3. row ***************************
Host: localhost
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2018-05-13 14:47:36
password_lifetime: NULL
account_locked: Y
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
*************************** 4. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
password_expired: N
password_last_changed: 2018-05-13 14:47:39
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
4 rows in set (0.00 sec)
As it can be seen here, the \G comes in handy to view the records in a better formatted manner. Let us now create an user and check the records.
mysql> create user 'abhishek'@'localhost' identified by 'password'; Query OK, 0 rows affected (0.10 sec)
Now, you can check for the user with a where clause specifying the user attribute as shown below.
mysql> select * from user where User='abhishek' \G;
*************************** 1. row ***************************
Host: localhost
User: abhishek
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
password_expired: N
password_last_changed: 2018-05-30 21:22:45
password_lifetime: NULL
account_locked: N
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
1 row in set (0.01 sec)
Currently, the user has not been assigned any grants. Hence, the user grants are all marked with N in the above output. Let us provide the grant to the user for selecting and viewing the records of tutorial schema.
mysql> GRANT SELECT ON *.* TO 'abhishek'@'localhost';
Query OK, 0 rows affected (0.06 sec)
mysql> select * from user where User='abhishek' \G;
*************************** 1. row ***************************
Host: localhost
User: abhishek
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
password_expired: N
password_last_changed: 2018-05-30 21:22:45
password_lifetime: NULL
account_locked: N
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
1 row in set (0.00 sec)
Once the select grant has been given, you could see that the first grant attribute select_priv turns to Y.
The global_grants table contain the list of global grants assigned to each user. You could check the same by directly viewing the records from the table. Currently, it will list only the global grants for the root user since it is the only user with global grants at this point of time.
The next database that is being used by MySQL to store primary administrative data is the sys database. This database contains live data of the database like the details of connected users, details of active sessions, details of running threads and several other parameters like the latency, server status, server logs, server encoding configurations and others. The tables of primary importance include session, host_summary & user_summary.
The details that each of these tables hold are mentioned below:
- session: Contains details about the currently active sessions. It displays the details about current active thread, last executed query as well as latency time. This tables could be used to identify the processes in case a locking or high latency query execution takes place.
- host_summary: This table carries the details of the hosts being served via this server. It is possible to connect to the server via multiple host domains. This table helps in identifying the host details of the active connections
- user_summary: Contains details of the connected users. In case of multiple sessions per user, this table displays a single records with the number of active connections while the session table displays multiple records indicating each connection thread.
9. Conclusion
This article explores all the major tasks that would be done using MySQL command line starting from the connection to creation and update of database as well as administration of it. Further, you could explore details about altering the tables, available datatypes and functions too.
