MySQL is a popular relational database management system that allows us to create, manipulate, and query data using SQL commands. In this blog post, we will learn most comman MySQL commands that are required from beginner to experienced developers.
How to connect MySQL using command prompt or terminal?
To connect to MySQL using the command prompt or terminal, we can use the MySQL command-line client. To do so, you need to type the following command, replacing the values in brackets with your own:
mysql -h <hostname> -P <port> -u <username> -p
Example:
mysql -u myuser -h my_host_or_ip -P 3306 -pYou will be prompted to enter your MySQL password. Type the password and press Enter. Example:
Enter password: your_password
How to list or show all databases in MySQL?
To list or show all databases in MySQL, we can use the command below:
SHOW DATABASES;This command lists all the databases that exist on the server.
Sample output:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
How to create new database in MySQL?
To create a new database in MySQL, we can use the following command:
CREATE DATABASE database_name;This command creates a new database with the specified name. You can replace database_name with your database name.
How to delete database in MySQL?
To delete database in MySQL, we can use the following command:
DROP DATABASE database_name;This command deletes the database and all its contents.
How to create table in MySQL?
To create a table in MySQL, you can use the CREATE TABLE command.
Syntax:
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2)
);
In this example, we created a table named employees with columns for employee_id, first_name, last_name, and salary.
How to list all table in MySQL database?
To list all tables in MySQL database we can use the show tables command. This command lists all the tables that exist in the current database.
Example:
mysql> show tables;
+----------------------+
| Tables_in_codersathi |
+----------------------+
| employees |
+----------------------+
1 row in set (0.00 sec)How to see the detail of table in MySQL?
To see the detail of table in MySQL, we can use the describe command. This command shows the structure of the table, including the column names, data types, and constraints.
Syntax:
DESCRIBE table_name;Example:
mysql> DESCRIBE employees;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)We can also use desc instead of describe:
Example:
mysql> DESc employees;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)Alternatively, we can also use the SHOW COLUMNS command.
Example:
mysql> SHOW COLUMNS FROM employees;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)How to delete table in MySQL?
To delete table in MySQL, we can use the following command:
DROP TABLE table_name;This command deletes the table and all its data.
How to insert data in MySQL?
To insert data into a table, we can use the INSERT INTO statement followed by the table name and the values we want to insert. This command inserts a new row into the table with the specified values for each column.
Syntax:
INSERT INTO your_table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example:
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'Coder', 'Sathi', 50000);
How to select all record from table in MySQL?
To select all record from table we can use the select command:
Syntax:
select * from table_name;Example:
mysql> select * from employees;
+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+----------+
| 1 | Coder | Sathi | 50000.00 |
+-------------+------------+-----------+----------+
1 row in set (0.00 sec)How to select specific columns in SQL query?
To select specific column we can use following syntax:
select comumn1, column 2 from table_name;This command retrieves data from the table and displays the specified columns.
Example:
mysql> select first_name from employees;
+------------+
| first_name |
+------------+
| Coder |
+------------+
1 row in set (0.00 sec)How to modify data in MySQL table?
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;This command modifies the data in the table by changing the values of the specified columns for the rows that match the condition.
Example:
mysql> update employees set salary=60000 where employee_id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0Now, let’s verify the data with select query:
mysql> select * from employees;
+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+----------+
| 1 | Coder | Sathi | 60000.00 |
+-------------+------------+-----------+----------+
1 row in set (0.00 sec)We can see that, the salary is increased from 50000 to 60000 becasue we executed the update query.
How to delete or remove daa from MySQL table?
To delete specific data from a table, use the DELETE statement with a WHERE clause to specify the conditions for deletion:
DELETE FROM table_name WHERE condition;
Example:
mysql> delete from employees where employee_id = 1;
Query OK, 1 row affected (0.00 sec)Now when we verify the data using select query:
mysql> select * from employees;
Empty set (0.00 sec)There is no data in the table because we deleted the data using the delete query.
How to add new column in MySQL table?
Adding a new column in MySQL involves using the ALTER TABLE statement to modify the structure of an existing table by introducing a new field. This operation is useful for accommodating additional data or enhancing the database schema.
Syntax:
ALTER TABLE table_name ADD column_name datatype;This command adds a new column to an existing table with the specified data type.
Example:
ALTER TABLE employees ADD COLUMN email VARCHAR(100);In this example, the ALTER TABLE statement is employed to append a column named email with a data type of VARCHAR(100) to the pre-existing employees table. This adjustment enables the storage of email addresses associated with each employee in the table.
How to delete column in MySQL table?
To delete column in MySQL table we can use the following:
ALTER TABLE table_name DROP column_name;This command deletes a column from an existing table.
Example:
ALTER TABLE employees DROP email;How to rename table name in MySQL?
To rename table name in MySQL we can use the following command:
ALTER TABLE table_name RENAME TO new_table_name;This command renames an existing table to a new name.
How to rename column name in MySQL table?
To rename column name in MySQL table, we can use the following command:
ALTER TABLE table_name CHANGE old_column_name new_column_name data_type(size);Example:
ALTER TABLE employees CHANGE last_name lastname varchar(20);This ranames the employee table’s column name from last_name to lastname along with the size of data type.
How to create user in MySQL?
To create a user in MySQL, we can use the CREATE USER statement. Following is the syntax and an example query:
Syntax:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username: The name of the user you want to create.host: The host from which the user is allowed to connect. Use ‘%’ for any host.password: The password for the user.
Example:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
This example creates a user named myuser with the password mypassword and allows them to connect from the local machine (localhost).
After creating the user, you might want to grant specific privileges to the user. For example, to grant all privileges on a specific database:
GRANT ALL PRIVILEGES ON your_database.* TO 'myuser'@'localhost';
Replace your_database with the actual name of your database.
Finally, don’t forget to run the FLUSH PRIVILEGES; command to apply the changes:
FLUSH PRIVILEGES;
Creating a user and granting privileges should be done with caution, and it’s recommended to provide the minimum necessary permissions for security reasons.
How to show list of users in MySQL?
The easiest way to show list of users in MySQL is using the query below:
SELECT User, Host FROM mysql.user;This query list all the users and host details.
Example:
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)To view all the detail we can use following query:
SELECT * FROM mysql.user;
This will list all the detail and this is not possible to show here. You can try in your machine and see the detail.
How to see currently logged in user in MySQL?
To see currently logged in user in MySQL, we can use the following command:
SELECT current_user();
Example:
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)To view more detail including the status of user activity and many more, we can execute the following command:
SHOW PROCESSLIST;
This query will provide a list of currently active threads, including information such as the thread ID, user, host, database, command, and more.
The “user” column in the result set corresponds to the MySQL username of the connected user, and the “host” column indicates the host from which the user is connected.
Keep in mind that you might need appropriate privileges to execute this command. Also, be cautious when using this information, especially in production environments, as it reveals details about the current state of MySQL connections.
How to grant privileges to a MySQL User?
To grant privileges to a MySQL user, we can use the following command:
GRANT privileges ON database.table TO 'username'@'host';
- Replace
privilegeswith the specific privileges you want to grant (e.g., SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES). - Replace
'username'with the actual username you want to grant privileges. - Replace
'host'with the host from which the user is allowed to connect. Use'%'for any host. - Specify the database and table to which the privileges should apply.
Example:
GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'coder'@'localhost';
This example grants the SELECT, INSERT, and UPDATE privileges on all tables in the ‘mydatabase’ database to the user ‘coder’ connecting from ‘localhost’.
There are many privileges provided by MySQL. You can visit there for more detail.
How to view MySQL user permission?
To view MySQL user permission or privileges, we can use the following command:
SHOW GRANTS FOR “username”@”host” ;
Example:
mysql> SHOW GRANTS FOR 'coder'@'localhost';
+-----------------------------------------------------------------------+
| Grants for coder@localhost |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `coder`@`localhost` |
| GRANT SELECT, INSERT, UPDATE ON `mydatabase`.* TO `coder`@`localhost` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)How to remove privileges from a MySQL User?
To remove privileges from a MySQL user, you can use the REVOKE statement. Following is the general syntax and an example:
Syntax:
REVOKE privileges ON database.table FROM 'username'@'host';
Example:
REVOKE SELECT ON mydatabase.* FROM 'coder'@'localhost';This will remove the SELECT permission on the database name mydatabase
To remove all privileges we can use the following command:
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';
The above command removes all privileges from all database and its tables from given host and for the given username.
Cheers! 🙂