facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
in Basic DB Commands - 05 Dec, 2013
by Zafar Malik - no comments
Basic Mysql DB Commands

DB Schema Related Commands

# Database Creation:
MySQL> CREATE DATABASE TEST_DB; — Where TEST_DB is my database name.

# Table Creation (under TEST_DB):
MySQL> USE TEST_DB;
MySQL> CREATE TABLE TEST_TABLE(cust_id int, cust_name varchar(50), cust_address varchar(100));

# Table creation (Including all possible options related with table creation like primary key, index, unique index, relation ship etc.)
MySQL> CREATE TABLE TEST_DB.customer_table(auto_id int not null auto_increment, cust_id int not null, cust_name varchar(50), cust_address varchar(100), is_active enum(‘Y’,'N’) not null default ‘N’, modidate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(auto_id), UNIQUE KEY Uk_cust_id(cust_id), KEY idx_cust_name(cust_name), CONSTRAINT `FK_customer_table_cust_id` FOREIGN KEY (cust_id) REFERENCES order_table (cust_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
Where auto_id is a primary key and in auto_increment mode, modidate columns has timestamp data type, is_active columns has enum data type, engine is innodb, auto_increment value will be started from 1, and character set is latin1.

# Table Creation same like as another table:
MySQL> CREATE TABLE mytable1 LIKE mytable2;
Note: It will create keep all options like as main table but will not create relation ships means foreign keys.

# Rename Database is not possible.

# Rename Table:
MySQL> RENAME TABLE my_table1 TO my_table2;
OR
MySQL> ALTER TABLE my_table1 RENAME my_table2;

# Drop Table:
MySQL> DROP TABLE my_table1;

# Truncate Table (delete all rows on schema level):
MySQL> TRUNCATE TABLE my_table1;

# New Column Addtion:
MySQL> ALTER TABLE my_table ADD COLUMN new_column varchar(25) NOT NULL COMMENT ‘this columns will store new value’ AFTER column5; — where you can store description under comment and it is optional.

# New Column Addtion as a first column:
MySQL> ALTER TABLE my_table ADD COLUMN new_column varchar(25) NOT NULL FIRST; — where you can store description under comment and it is optional.

# New Column Addtion as a first column and as a primary key (if table does not have primary key):
MySQL> ALTER TABLE my_table ADD COLUMN auto_id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

# Column Name Change:
MySQL> ALTER TABLE my_table CHANGE COLUMN old_column new_column varchar(25) not null;

# Modify column:
MySQL> ALTER TABLE my_table MODIFY COLUMN product_type char(1) not null DEFAULT ‘C’; — here adding DEFAULT value as ‘C’.

# Column deletion:
MySQL> ALTER TABLE my_table DROP COLUMN my_column;

# Index creation:
MySQL> ALTER TABLE my_table ADD INDEX idx_cust_name(cust_name);
OR
MySQL> CREATE INDEX idx_cust_name ON my_table(cust_name);

# Unique Index creation:
MySQL> ALTER TABLE my_table ADD UNIQUE Uk_cust_name(cust_name);

# Foreign Key Addition:
MySQL> ALTER TABLE my_table ADD CONSTRAINT fk_customer_table_cust_id FOREIGN KEY(cust_id) REFERENCES order_table(cust_id) ON UPDATE NO ACTION ON UPDATE NO ACTION;
Note:CASCADING is optional as per your requirement you can set its property no action or cascade etc.

# Key deletion (index, unique index, foreign key etc.):
MySQL> ALTER TABLE my_table DROP KEY idx_cust_id;

# Primary Key deletion:
MySQL> ALTER TABLE my_table DROP KEY `PRIMARY`;

# Multiple alterations in single command:
MySQL> ALTER TABLE my_table ADD COLUMN new_column varchar(25) NOT NULL AFTER column5, DROP INDEX idx_cust_id, ADD CONSTRAINT fk_customer_table_cust_id FOREIGN KEY(cust_id) REFERENCES order_table(cust_id) ON UPDATE NO ACTION ON UPDATE NO ACTION;

# Index modification: We can not modify any key, for this first we have to drop that key then we can again create modified key. Also we can not drop and add same key in single command we have to execute 2 commands for that.
MySQL> ALTER TABLE my_table DROP KEY idx_cust_id; ALTER TABLE my_table ADD INDEX idx_cust_id(cust_id,cust_name);

# Index on partial part of field:
MySQL> ALTER TABLE my_table ADD INDEX idx_cust_name(cust_name(10));

# Composit key:
MySQL> ALTER TABLE my_table ADD UNIQUE Uk_cust_id_prod_id(cust_id,prod_id)

# Change DB Engine:
MySQL> ALTER TABLE my_table ENGINE=INNODB;

# How can check table structure:
MySQL> DESC mytable;

# How can get table structure with complete characteristic:
MySQL> SHOW CREATE TABLE mytable;
Note: It will give you complete table structure even db engine, auto_increment pointer, default character set, foreign keys etc.

# How can find single column quickly of a big structure table:
MySQL> SHOW COLUMNS FROM mytable LIKE ‘%cust_id%’;

# How can find key details (index, primary, foreign keys) on a table:
MySQL> SHOW INDEX FROM mytable;

# How can get all databases:
MySQL> SHOW DATABASES;

# How can get all tables from a database:
MySQL> SHOW TABLES FROM mydb;

# How can search a tables from a database:
MySQL> SHOW TABLES FROM mydb LIKE ‘%mytable%’;

# How can get table engine, data length, index length etc:
MySQL> SHOW TABLE STATUS FROM mydb;
OR
MySQL> SHOW TABLE STATUS FROM mydb LIKE ‘%mytable%’;

# If it is not known that a table exist or not on server then you can check by below query but we should avoid any query on table_schema in mysql as it slow to db.
MySQL> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like “mytable%”

# How can check triggers:
MySQL> SHOW TRIGGERS; — All triggers from server.
MySQL> SHOW TRIGGERS FROM mydb; — All triggers from mydb database.

# How can check user created functions:
MySQL> SHOW FUNCTION STATUS; — All user created functions on server.
OR
MySQL> SELECT * FROM MYSQL.PROC WHERE TYPE=’FUNCTION’;
OR
MySQL> SELECT * FROM MYSQL.PROC WHERE TYPE=’FUNCTION’ AND DB=’mydb’;

# How can check user created stored procedures:
MySQL> SHOW PROCEDURE STATUS; — All user created stored procedures on server.
OR
MySQL> SELECT * FROM MYSQL.PROC WHERE TYPE=’PROCEDURE’;
OR
MySQL> SELECT * FROM MYSQL.PROC WHERE TYPE=’PROCEDURE’ AND DB=’mydb’;

# SP created by specific definer:
MySQL> SELECT * FROM MYSQL.PROC WHERE TYPE=’PROCEDURE’ AND DEFINER=’root’;

# How can get stored procedure structure (also get table/view/function structure):
MySQL> SHOW CREATE PROCEDURE mydb.myproc;

# How can generate schema report (db/table structure, columns, keys etc) of a complete db:
Right Click on desired DB > Choose ‘Create Schema for Database in Html’ > choose all tables or select desired tables > click on create > save at desired path
Data Manipulation/Data Fetching commands

# How can insert data into a table;
MySQL> INSERT INTO mytable (cust_id,cust_name,cust_address) values(1,’zafar’,'delhi’);
OR
MySQL> INSERT INTO mytable (cust_id,cust_name,cust_address) values(1,’zafar’,'delhi’), (2,’Amit’,'Noida’); — for multiple row insertion.

# How can insert data from one table to another table;
MySQL> INSERT INTO mytable1 select * from mytable2;
Note: Columns must be same in both tables.

# How can insert data from one table (specific columns) to another table with condition;
MySQL> INSERT INTO mytable1(cust_id,cust_name) select cust_id,cust_name from mytable2 WHERE cust_id<3000;

# How can insert data from a table to a new table.
MySQL> CREATE TABLE mytable1 select cust_id,cust_name from mytable2 WHERE cust_id<4000;
Note: New table will create only with 2 columns with same column data type and data will be inserted as per given condition.

# Data Insertion after ignoring duplicate rows:
MySQL> INSERT IGNORE INTO mytable1 select * from mytable2;
Note: If there will be any unique key in mytable1 table and mytable2 contains duplicate rows for that key then only first row will be inserted and after that duplicate rows will be skipped.

# Data Insertion with replacing duplicate rows:
MySQL> REPLACE INTO mytable1 select * from mytable2;
Note: If there will be any unique key in mytable1 table and mytable2 contains duplicate rows for that key then last row will be replaced with earlier duplicate row.

# How can insert data in bulk means bulk upload:
MySQL> LOAD DATA LOCAL INFILE ‘C:\\Temp\\myfile.csv’ INTO TABLE mydb.mytable fields escaped by ‘\\’ terminated by ‘,’ enclosed by ‘”‘ lines terminated by ‘\r\n’ (`i_autoId`, `i_p4id`, `i_stateId`, `i_cntyId`, `i_groupid`, `i_subgrpid`, `i_formatid`, `i_entrytyp`, `c_docutype`, `i_order`, `c_ruledesc`);

# How can export data from a table:
MySQL> SELECT * INTO OUTFILE ‘c:\\temp\\unv_doctype_matrix.csv’ fields terminated by ‘,’ optionally enclosed by ‘”‘ lines terminated by ‘\n’ FROM mydb.mytable;

# How can check query execution pan before executing it or how we can examine a query impact, index usage etc.
MySQL> EXPLAIN SELECT * FROM mydb.mytable;

# How can fetch data from a table:
MySQL> SELECT * FROM mytable;

# How can fetch data of selected columns from a table:
MySQL> SELECT cust_id,cust_name, cust_address FROM mytable;

# How can fetch data of selected columns from a table with condition:
MySQL> SELECT cust_id,cust_fname, cust_address FROM mytable where cust_fname=’zafar’;

# How can fetch data of selected columns from a table with condition if exact value is not known:
MySQL> SELECT cust_id,cust_fname, cust_address FROM mytable where cust_fname like ‘zaf%’;

# How can fetch data of selected columns from a table and ascending order based on fname:
MySQL> SELECT cust_id,cust_fname, cust_address FROM mytable where cust_fname like ‘zaf%’ order by cust_fname;

# Row count:
MySQL> SELECT count(*) from mytable where salary>10000;

# Use of Group By:
MySQL> SELECT cust_id,COUNT(order_id) FROM order_table GROUP BY cust_id;

# Use of sum function and how can give a name to a column:
MySQL> SELECT cust_id AS ‘Customer ID’,COUNT(order_id) AS ‘Total Order’, SUM(order_price) AS ‘Order Value’ FROM order_table GROUP BY cust_id;

# How can join two columns:
MySQL> SELECT CONCAT(cust_fname,”,cust_lname) FROM customer_table;

# Example of simple join: Will return common rows from both tables based on prod_id.
MySQL> SELECT cust.cust_name AS ‘Customer Name’, prd.prod_name AS ‘Product Name’, prd.price AS ‘Product Price’ FROM customer AS cust, product AS prd WHERE cust.prod_id=prd.prod_id;
OR
MySQL> SELECT cust.cust_name AS ‘Customer Name’, prd.prod_name AS ‘Product Name’, prd.price AS ‘Product Price’ FROM customer AS cust JOIN product AS prd ON cust.prod_id=prd.prod_id;

# Example of left join: Will return all rows from customer table but only corresponding rows from product table, rest rows from customer table will have NULL values in product table columns.
MySQL> SELECT cust.cust_name AS ‘Customer Name’, prd.prod_name AS ‘Product Name’, prd.price AS ‘Product Price’ FROM customer AS cust LEFT JOIN product AS prd ON cust.prod_id=prd.prod_id;

# Example of right join: Will return all rows from product table but only corresponding rows from customer table, rest rows from product table will have NULL values in customer table columns.
MySQL> SELECT cust.cust_name AS ‘Customer Name’, prd.prod_name AS ‘Product Name’, prd.price AS ‘Product Price’ FROM customer AS cust RIGHT JOIN product AS prd ON cust.prod_id=prd.prod_id;

# How can update a row:
MySQL> UPDATE mytable SET cust_name=’zafar’ WHERE cust_name=’Amit’;

# How can replace some value from a column:
MySQL> UPDATE mytable SET cust_name=REPLACE(cust_name,’malik’,'mohd. malik’) WHERE cust_name=’zafar malik’; — it will change column cust_name from ‘zafar malik’ to ‘zafar mohd. malik’

# Delete row from a table:
MySQL> DELETE FROM mytable WHERE cust_id=5;
Note: Without where clause all rows will be deleted from table.

# Deletion based join on multiple tables under multiple databases:
MySQL> USE mydb1;DELETE b.* from mydb2.mytable2 a, mydb1.mytable1 b WHERE a.FOREIGN_KEY=b.PRIMARY_KEY AND a.c_sqlstts=’D’ AND a.c_movstts=’D’ AND a.c_delstts=’F’ AND a.i_statusid=1;

# To get only duplicate records and their count:
MySQL> SELECT cust_id ‘Customer ID’, count(order_id) AS ‘Total Order’ from order_table GROUP BY cust_id having count(order_id) > 1;

# Having clause must come after group by and before order by:
MySQL> SELECT cust_id,COUNT(order_id) FROM order_table WHERE order_value>5000 GROUP BY cust_id HAVING COUNT(order_id)>1 ORDER BY order_id DESC;

# How can get sub-total as well grand total by a query:
MySQL> SELECT cust_id, SUM(price) FROM order_table GROUP BY cust_id WITH ROLLUP;

# How we can do force index:
MySQL> SELECT COUNT(cust_id) FROM customer FORCE INDEX (idx_cust_id) WHERE cust_id>=5000;

# Use of transaction: We can use both statement with condition that if success then commit or rollback.
MySQL> START TRANSACTION; UPDATE customer SET cust_name=’zafar’ WHERE cust_name=’Amit’; DELETE FROM customer WHERE cust_id=2;COMMIT; — If every thing is fine and you want to save in database.
MySQL> START TRANSACTION; UPDATE customer SET cust_name=’zafar’ WHERE cust_name=’Amit’; DELETE FROM customer WHERE cust_id=2;ROLLBACK; — If every thing is not fine and you want to discard these changes and don’t want to save in DB.

# Use of Max/Min function:
MySQL> SELECT MAX(salary) FROM employee;

# How can get a column values in single row with comma separated:
MySQL> SELECT GROUP_CONCAT(id) FROM mytable;
DB Management Command

# How can get dependant table (child table) details:
MySQL> SELECT * FROM information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA =’mydb’ and REFERENCED_TABLE_NAME =’myparenttable’;

# How can get cross database constraint details:
MySQL> select * from information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA<>UNIQUE_CONSTRAINT_SCHEMA

# How we can get free table space from table or how we can re-build index (innodb_file_per_table variable should be enable):
MySQL> ALTER TABLE mytable ENGINE=INNODB;
OR
MySQL> OPTIMZE TABLE mytable;

# How can delete rows from master table even corresponding rows exist in child table and ON DELETE NO ACTION but it is not recommended instead either remove cascading or use ON DELETE CASCADE with action:
MySQL> SET FOREIGN_KEY_CHECKS=0;DELETE FROM mytable WHERE id=5;SET FOREIGN_KEY_CHECKS=1;

# How can check binary logs from server (we can also check at the path where binlog are stored if have rights on server).
MySQL> SHOW BINARY LOGS;

# How can purge old binary logs:
MySQL> PURGE BINARY LOGS TO ‘mysql-bin.000010′; — based on file name.
OR
MySQL> PURGE BINARY LOGS BEFORE ’2008-04-02 22:46:26′; — based on log date.