Loading...

(Handy) Useful MySQL commands

Useful MySQL commands

MySQL is the open-source database system initially developed for use in the University of Cambridge’s Computer Laboratory (aka, CERN). Since then, MySQL has grown into an open-source database software program that is used for a number of different purposes. For example, MySQL is widely used by e-commerce and online stores, online businesses and web hosts, e-mail services, and social media sites. We need some MySQL related commands that we often find on Google. The basic set up of MySQL is quite simple; however, with the addition of additional features, MySQL can grow to be extremely powerful and flexible. MySQL database software is also an extremely versatile solution that can be used for virtually any purpose.

 

Here we 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 strict mysql mode

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

 

 

It is always advisable to read a MySQL tutorial, and understand the database itself before trying to install MySQL on your own. The most important features of MySQL are that MySQL can be used for just about any purpose, and that the database is highly flexible, and easy to use. MySQL is a great choice for both web development and for running MySQL databases on their own servers, for both small and large businesses.

 

 

 

One thought on “(Handy) Useful MySQL commands

Comments are closed.