(Handy) 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.