Loading...

Useful MySQL commands

MySQL is a widely used database for most of the applications. We need some MySQL related commands that we often find on Google.

 

Here I provide all daily use commands.

 

1) Login to MySQL server

mysql -u username -p

Type the MySQL password, and then press Enter.

 

2) Create a new database.

mysql> create database [database_name];

 

3) Show all databases on the server.

mysql> show databases;

 

4) Select a database.

mysql> use [database_name];

 

5) Drop full database

mysql> drop database [database_name];

 

6) Find all the tables in that database.

mysql> show tables;

7) View Mysql Table’s Full Details

mysql> describe [table_name];

 

8) Drop a table

mysql> drop table [table_name];

 

9) Truncate a table

mysql> TRUNCATE [table_name];

 

10) Get column name of a table

mysql> show columns from [table_name];

 

11) Import database

mysql -u username -p database_name < file.sql
Type the MySQL password, and then press Enter.

 

12) Export database

mysqldump -u username -p database_name > file.sql
Type the MySQL password, and then press Enter.

 

13) To Remove Sql_mode=Only_full_group_by From SSH Command

SELECT @@sql_mode;

set global sql_mode=’NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

set session sql_mode=’NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

SELECT @@sql_mode;
exit;

 

14) Check duplicate value in a column for a table

SELECT column_name FROM `my_table` GROUP BY `column_name` HAVING count( * ) >=2

 

15) To Save Emoji in table

Set collation of that column to utf8mb4_unicode_ci
and when display use utf8_decode

 

16) How Mysql Data Travels

SET profiling=1;
SELECT `id` FROM `table_name`;
SHOW profile;

 

17) MySQL Error 2006: Mysql Server Has Gone Away
I’ve normally found the answer to be a very low default setting of max_allowed_packet.
Increasing it in my.cnf to 8 or 16M usually fixes it.

max_allowed_packet=16M

 

18) Copy Data From One Table To Other Table

UPDATE table_1 INNER JOIN table_2 ON table_1.id = table_2.id

SET table_1.`column_to_copy` = table_2.`column_to_copy`

WHERE table_2.id = table_1.id

 

19) Select All Mysql Column Except One Or Two

SET @@group_concat_max_len = 2048;
SET @database = ‘database_name’;
SET @tablename = ‘table_name’;
SET @cols2delete = ‘field1,field2’;

SET @sql = CONCAT(
‘SELECT ‘,
(
SELECT GROUP_CONCAT( IF(FIND_IN_SET(COLUMN_NAME, @cols2delete), NULL, COLUMN_NAME ) )
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename AND TABLE_SCHEMA = @database
),
‘ FROM ‘,
@tablename);

SELECT @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

20) Concate Two Column Values In Mysql

UPDATE table_name SET coumn_name = CONCAT(column1,column2)

 

21) Remove Any Key From A Mysql Table’s Column

ALTER TABLE table_name
DROP INDEX column_name

 

 

Follow my other blogs here

 

 

 

One thought on “Useful MySQL commands

Comments are closed.