Mysql Sample Queries

Mysql Sample Queries:

1) Create a duplicate table:
CREATE TABLE DUPLICATE_TABLE LIKE BANK_ID;   
//Will create duplicate empty table with same version of original table storage format. Indexes/Constraints will be copied.

CREATE TABLE DUPLICATE_TABLE as SELECT * FROM BANK_ID;  
//Will create table with data, Indexes/Constraints will not be copied. Need to re-create indexes/constraints.

INSERT into NEW_TABLE SELECT * from OLD_TABLE where Id=999999;

2) Dumping databases:
mysqldump --lock-all-tables -u root -p --all-databases > filename.sql     //For all Databases
mysqldump -u root dbname > filename.sql                                   //For one database without locking tables.

3) Install new version of mysql:
sudo apt-get install mysql-client-5.6 mysql-client-core-5.6
sudo apt-get install mysql-server-5.6

4) Import the dump file to mysql:
mysql -u root -p < filename.sql

5) Stopping/Starting mysql in unix:
service mysql status
service mysql stop
service mysql start
service mysql restart

or

/etc/init.d/mysql status
/etc/init.d/mysql start
/etc/init.d/mysql stop

6) Mysql config file is found in /etc directory in Unix: /etc/my.cnf 

7) Create Databse:
Create database NEW_Database;
use NEW_Database;
show tables;

8) Show variables in mysql:
show variables;
show variables where variable_name like '%connectio%';
show variables where variable_name like '%table_names%';  (lower_case_table_names=1 means case in-sensitive).

9) Setting variable value:
set global max_connections=200;

10) Create new user:
Create user 'root'@'X.X.X.54' identified by 'root';
Grant all privileges on *.* to 'root'@'X.X.X.54';
Grant all privileges on *.* to 'root'@'X.X.X.54' identified by 'root';
Select Host, User, Password from mysql.user;

11) Alter table:
alter table TABLE_NAME add NEW_COLUMN_NAME int not null;
alter table TABLE_NAME add foreign key(NEW_COLUMN_NAME) references OTHER_TABLE(Id);
alter table TABLE_NAME change COL_NAME NEW_COL_NAME float not null;
alter table TABLE_NAME modify COL_NAME bigint not null;
alter table TABLE_NAME add (TimeZoneId varchar(32) not null, TimeOffsetFromGMT int not null);

CREATE TABLE TABLE_NAME (Id int(11) NOT NULL auto_increment, TxnSetId int(11) NOT NULL, PRIMARY KEY (Id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
---------------END---------------------