MySql Commands

Basic Tutorial Examples to Learn MySQL

Just make sure that you have installed MySQL database server with xampp and have a valid username & password to access. So developers get ready to learn MySQL within couple of hours.

End of the tutorial you can do:

• Create and Drop Database
• Create, Alter, Truncate and Drop Table
• Select, Insert, Update and Delete rows
• Group by, Order by, Having, Union, Distinct
• Add, drop index, Explain command
• database backup with mysqldump or mysqlhotcopy
• Create New user, Grant Access, Revoke Access
• Analyze, Check, Optimize, and Repair Mysql Table

---

Connect MySQL Database

Open your MySQL Client tools then copy & paste the below code to Connect to MySQL database server

On windows, you can find mysql.exe at location : c:\xampp\mysql\bin\mysql.exe
Open ms-dos prompt and just copy & paste the bellow code

 
   
   >cd c:\xampp\mysql\bin\
   >mysql -hlocalhost -uroot -p

Note: modify your host localhost with either domain name or IP, username, password accordingly.

---

Create Database

Open your MySQL Client tools then copy & paste the below code to Create MySQL database

   
   mysql>CREATE DATABASE  tutorial;

---

Drop Database

Open your MySQL Client tools then copy & paste the below code to Drop database

   mysql>Drop DATABASE  tutorial;

---

Show Database

Open your MySQL Client tools then copy & paste the below code to see all databases in MySQL

   mysql>show DATABASES;

---

Use Database

Open your MySQL Client tools then copy & paste the below code to select database

   mysql>use tutorial;

---

Create table

Open your MySQL Client tools then copy & paste the below code to Create Table

   mysql>CREATE TABLE `user` (
	  `id` bigint(20) NOT NULL AUTO_INCREMENT,
	  `name` varchar(255) DEFAULT NULL,
	  `username` varchar(255) DEFAULT NULL,
	  `password` VARCHAR(32) DEFAULT NULL,
	  `status` enum('Active','Suspended','Hold') DEFAULT NULL,
	  `recorddate` bigint(20) unsigned NOT NULL DEFAULT '0',
	  `about_me` text,
	  PRIMARY KEY (`id`),
	  UNIQUE KEY `username` (`username`),
	  KEY `recorddate` (`recorddate`)
	) ENGINE=InnoDB
	; 

note : Suppose that an application stores password in MD5() string values in a CHAR(32) column:

---

Create table with select statement

Open your MySQL Client tools then copy & paste the below code to Create Table

   mysql>create table `user_2` select * from `user`; 

   OR
    /* with selected columns */
	create table `user3` select name, username, password from `user`;

Note: Mysql will create user2 with same structure as 'user'.

---

Rename MySql Table

Open your MySQL Client tools then copy & paste the below code to rename MySql Table

   mysql>rename table user_2 to user2;

---

Show Tables

Open your MySQL Client tools then copy & paste the below code to see all tables in database

   mysql>show tables;

---

Show Table Status

Open your MySQL Client tools then copy & paste the below code to see table configuration

   mysql>show table status;

---

Show Table Status

Returns the columns and column information pertaining to the designated table.

   mysql>show columns from user;

---

Drop Table

Open your MySQL Client tools then copy & paste the below code to drop table from database

   mysql>drop table `user`;

---

Describe Table

Open your MySQL Client tools then copy & paste the below code to see all tables in database

   mysql>desc `user`;

---

Change Engine Type (Table Type)

Open your MySQL Client tools then copy & paste the below code to see all tables in database

   mysql>ALTER TABLE `user` CHANGE TYPE=MyISAM;
   OR 
   mysql>ALTER TABLE `user` ENGINE = MyISAM;

---

Change the next value for an AUTO_INCREMENT

Open your MySQL Client tools then copy & paste the below code

     /* this makes the next record have 911 in the auto increment field: */
	mysql>alter table MYTABLE auto_increment=911;

---

Alter Table : Add new column

Open your MySQL Client tools then copy & paste the below code to modify table structure

   mysql>alter table `user` add column age smallint(2) unsigned NOT NULL default '0' after  `username`;
   OR 
	mysql>alter table `user` add column age smallint(2) unsigned NOT NULL default '0' before  `username`;
	OR 
	/* Add multiple columns */
	mysql>alter table `user` add column country varchar(255) NOT NULL default '' after `status`,
				add column state varchar(255)  NOT NULL default '' after `country`;	

---

Alter Table : Drop column

Open your MySQL Client tools then copy & paste the below code to modify table structure

   mysql>alter table `user` drop column state;
   /* Drop multiple columns */
	mysql>alter table `user` drop column country, drop column state;

---

Alter Table : Rename column name

Open your MySQL Client tools then copy & paste the below code to modify table structure

   mysql>alter table `user` change username  user_name varchar(255);

---

Alter Table : Change column data type

Open your MySQL Client tools then copy & paste the below code to modify table structure

   
	mysql>alter table `user` modify username  varchar(35) ;

---

Show Index

Open your MySQL Client tools then copy & paste the below code to see index created on table

   mysql>show index from  `user` ;

---

Add Index

Open your MySQL Client tools then copy & paste the below code to add new index (Key)

   mysql>alter table `user` add index username(`username`);

	OR

	mysql>create index `username` on `user`(`username`);

---

Check Index

Open your MySQL Client tools then copy & paste the below code to check usage of index

   
   mysql>explain select * from user where username = 'user1';

---

Drop Index

Open your MySQL Client tools then copy & paste the below code to modify table structure

   
   mysql>alter table `user` drop index `username`;

   OR
   
   mysql>drop index `username` on `user`;

---

Insert Single Row In MySQL Table

Open your MySQL Client tools then copy & paste the below code to Insert row in Table

   mysql>INSERT INTO user(`id`,`name`,`username`,`password`,`status`,`recorddate`,`about_me`)
		VALUES(NULL, 'my name', 'user123', 'myPassword', 'Active', 20120303124500, 'Some thing about me')
		;

		OR

  mysql>INSERT INTO user VALUES(NULL, 'my name', 'user123', 'myPassword', 'Active', 20120303124500, 'Some thing about me')
		;

Note : When you do not specify column name in insert command, mysql will require value for each column of table. When you insert with selected columns, mysql will insert default value for non specified column.

---

Insert Multiple Rows In MySQL Table

Open your MySQL Client tools then copy & paste the below code to Insert row in Table

   mysql>INSERT INTO user(`id`,`name`,`username`,`password`,`status`,`recorddate`,`about_me`)
		VALUES
		(NULL, 'my name', 'user1', 'myPassword1', 'Active', 20120303124500, 'Some thing about me2'),
		(NULL, 'my name1', 'user2', 'myPassword2', 'Active', 20120303124500, 'Some thing about me2')
		;

---

Insert Rows from other Table

Open your MySQL Client tools then copy & paste the below code to Insert row in Table

   mysql>INSERT INTO user(`id`,`name`,`username`,`password`,`status`,`recorddate`,`about_me`) SELECT NULL ,`name`,`username`,`password`,`status`,`recorddate`,`about_me` from user2 

Note : Use NULL in select for autoincrement ID, else it will tale id from user2 table and may create problem.

---

Insert Row : Ignore

Open your MySQL Client tools then copy & paste the below code to Insert row in Table

   mysql>INSERT IGNORE INTO user(`id`,`name`,`username`,`password`,`status`,`recorddate`,`country`,`about_me`)
		VALUES(NULL, 'my name', 'user123', 'myPassword', 'Active', 20120303124500, 'Some thing about me')
		;

Note : Use IGNORE when you want to skip duplicate entries that would otherwise cause failure, MySQL does NOT raise a warning!

---

Insert Row : Delayed

Open your MySQL Client tools then copy & paste the below code to Insert row in Table

   mysql>INSERT DELAYED INTO user(`id`,`name`,`username`,`password`,`status`,`recorddate`,`about_me`)
		VALUES(NULL, 'my name', 'user3', 'myPassword', 'Active', 20120303124500, 'Some thing about me')
		;

---

Insert Row : From File

Open your MySQL Client tools then copy & paste the below code to Insert row in Table

   mysql>LOAD DATA INFILE 'user.csv' 
				INTO TABLE user 
				FIELDS TERMINATED BY ','   
				ENCLOSED BY '"' 
				IGNORE 0 LINES
				(`id`,`name`,`username`,`password`,`status`,`recorddate`,`about_me`);
				;

---

Insert Row : Restore Dump

Open your MySQL Client tools then copy & paste the below code to Insert row in Table

   /mysql/bin>mysql -uroot -p tutorial < tutorial_user_dump.sql

---

Select : All rows

Open your MySQL Client tools then copy & paste the below code to fetch all rows from table

   mysql>select * from `user`;

---

Select : Using Like, LIKE BINARY

Open your MySQL Client tools then copy & paste the below code to fetch rows that start name with 'mike'

   mysql>select * from `user` where name like 'mike%';
	
	OR

   mysql>select * from `user` where name like binary 'mike%';

Note: with LIKE BINARY, sorting and comparison are based on the numeric values of the bytes in the values.

---

Select : Using Between

Open your MySQL Client tools then copy & paste the below code to fecth data with between clause

   mysql>select * from `user` where recorddate between 20120201000000 and 20120229235959;

---

Select : Using in

Open your MySQL Client tools then copy & paste the below code to modify table structure

   mysql>select * from `user` where status in ('Active', 'suspended') ;

---

Select : Using Limit

Open your MySQL Client tools then copy & paste the below code to fetch limited records from table

   mysql>select * from `user` limit 20;

   OR 
	
	mysql>select * from `user` limit 11, 20;

---

Select : Using DISTINCT

Open your MySQL Client tools then copy & paste the below code to fetch distinct records from table

   mysql>select DISTINCT name  from `user`;

---

Select : Using ORDER BY

Open your MySQL Client tools then copy & paste the below code to fetch records in asc/desc order

  /*  Latest record first */
   mysql>select * name  from `user` ORDER BY id DESC;
   OR
   /* First record first */
   mysql>select * name  from `user` ORDER BY id ASC;

---

Select : Random row

Open your MySQL Client tools then copy & paste the below code to fetch random record from table

   mysql>select * from user ORDER BY RAND() LIMIT 1;

---

Select : Using COUNT, MAX, MIN, SUM, AVG

Open your MySQL Client tools then copy & paste the below code to fetch records in asc/desc order

    /* Returns number of rows in table */
    mysql>select count(*) name  from `user`;

    /* Returns last record date from table */
    mysql>select max(recorddate) from `user` ;
	
	/* Returns minimum (first) record date from table */
    mysql>select miin(recorddate) from `user` ;

	/* Returns average age of users in table */
    mysql>select avg(age) from `user` ;

	/* Returns coount using subquery */
    mysql>select count(id) from (select * from user) as o;

Tip: use count on indexed column to get faster response.

---

Select : Using GROUP BY, HAVING

Open your MySQL Client tools then copy & paste the below code

    /* Returns count for each status in table */
    mysql>SELECT status, count(*) FROM `user` GROUP BY status;

	/* Returns count for each status only if it is > 10 */
    mysql>SELECT status, count(*) as cnt FROM `user` GROUP BY status having cnt > 10;

---

Select : Using CONCAT, SUBSTRING, CAST, ROUND, INSTR, TRIM, LTRIM, RTRIM, LENGTH, REPLACE

Open your MySQL Client tools then copy & paste the below code

    /* Combine together (concatenate) the results from several different fields */
    mysql>SELECT CONCAT(country, state ) FROM `user`;

	/* Returns first 8 digit s from recorddate */
    mysql>SELECT substring(recorddate, 1, 8 ) as date_Ymd FROM `user`;
	OR
	mysql>SELECT substr(recorddate, 1, 8 ) as date_Ymd FROM `user`;

	/* Replace In username, find where user occurs, and replace it with member. */
    mysql>SELECT Replace(username, 'user', 'member') FROM `user`;
	/* Note: replace does not update the actual record, it updates the content */

---

Select : Using UNION

Open your MySQL Client tools then copy & paste the below code

	/* fetch data from user and user2 table	*/
	mysql>SELECT name FROM user
					UNION
				SELECT name FROM user2;
		
		OR

	mysql>SELECT name FROM user
					UNION ALL
				SELECT name FROM user2;
	

Note : The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.

---

Select : Using INTERSECT

Open your MySQL Client tools then copy & paste the below code

	/* fetch data from user and user2 table	*/
	mysql>SELECT name FROM user
					INTERSECT
				SELECT name FROM user2;
	

Note: The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements). Please note that the INTERSECT command will only return distinct values.

---

Update Record

Open your MySQL Client tools then copy & paste the below code to update MySql table

   mysql>update `user` set name = 'first name' ;

---

Update Record: Specific row

Open your MySQL Client tools then copy & paste the below code to update specific rown in MySql table

   mysql>update `user` set name = 'first name' where id=1;

---

Update Record: with LIMIT clause

Open your MySQL Client tools then copy & paste the below code to update limited rows in MySql table

   mysql>update `user` set status = 'Suspended' order by id desc limit 1;

---

Delete Record

Open your MySQL Client tools then copy & paste the below code to delete rows from MySql table

   mysql>delete from `user`;

---

Delete Record: specific row

Open your MySQL Client tools then copy & paste the below code to delete specific rows from MySql table

   mysql>delete from `user` where status = 'Hold';

---

Delete Record: with Limit Clause

Open your MySQL Client tools then copy & paste the below code to delete only 1 row from MySql table

   mysql>delete from `user` where status = 'Hold' limit 1;

---

Truncate MySql Table

Open your MySQL Client tools then copy & paste the below code to truncate MySql table

   mysql>truncate table `user`;

Note: truncate deletes all rows from table, you can not apply where clause or limit clause with this command.

---

Database Backups

In this section, two methods for making backups of MySQL data and database structures are discussed, namely mysqldump and mysqlhotcopy.

mysqldump

Backing up both the structure and data found within the widgets database would be accomplished as follows:

	mysql/bin>mysqldump -u root -p --opt tutorial > tutorial-backup.sql

Alternatively, perhaps just a backup of the data is required. This is accomplished by including the option --no-create-info, which means no table creation data:

	mysql/bin>mysqldump -u root -p --no-create-info tutorial > tutorial-data-backup.sql

Another variation is just to backup the table structure. This is accompished by including the option --no-data, which means no table data:

	mysql/bin>mysqldump -u root -p --no-data tutorial > tutorial-structure-backup.sql

Restore database (or database table) from backup.

	mysql/bin>mysql -u username -p tutorial< tutorial-data-backup.sql

Note : If you are planning on using mysqldump for reason of backing up data so it can be moved to another MySQL server, it is recommended that you use the option '--opt'. This will give you an optimized dump which will result in a faster read time when you later load it to another MySQL server.

---

mysqlhotcopy

The mysqlhotcopy utility is a perl script that uses several basic system and SQL commands to backup a database. More specifically, it will lock the tables, flush the tables, make a copy, and unlock the tables. Although it is the fastest method available for backing up a MySQL database, it is limited to backing up only those databases residing on the same machine as where it is executed.

	mysql/bin>mysqlhotcopy -u root -p tutorial /usr/mysql/backups 

---

Lock All Tables in MySql database

This closes all open tables and locks all tables for all databases with a read lock until you execute UNLOCK TABLES. This is very convenient way to get backups.

	
	mysql>FLUSH TABLES WITH READ LOCK;
	
	/* alternatively lock a single table:
		LOCK TABLES user READ; 
	*/
	
	/* running my backup */
	
	mysql>UNLOCK TABLES;

---

MySQL Administration

Creating a new user.

Login as root. Switch to the MySQL db. Make the user. Update privileges.

		mysql/bin> mysql -u root -p
		mysql> use mysql;
		mysql> INSERT INTO user (Host,User,Password) VALUES('%','manish',PASSWORD('pass000'));
		mysql> flush privileges;

Change MySql user password

The password field has to be updated using the password() function which encrypts the string else mysql will give access denied error.

		mysql/bin>mysqladmin -u root -p  'new-password'
		
		OR
		mysql> use mysql;
		mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('new-password');
		mysql> flush privileges;

Update a root password

		/* if there is on root password */
		mysql/bin> mysqladmin -u root -p oldpassword newpassword
		
		OR

		/* update a root password */
		mysql/bin> mysqladmin -u root -p oldpassword newpassword

Check Grants

	mysql>SHOW GRANTS FOR manish;

Grant

Allow the user "manish" to connect to the server from localhost using the password "pass000".

		mysql/bin> mysql -u root -p
		mysql> use mysql;
		/* On all databases in MySql */
		mysql> grant ALL on *.* to 'manish'@'localhost' identified by 'pass000';
		/* Only connect tutorial database */
		mysql> grant ALL on tutorial.* to 'manish'@'localhost' identified by 'pass000';
		/* Only select option on tutorial database */
		mysql> grant SELECT on tutorial.* to 'manish'@'localhost' identified by 'pass000';

		mysql> flush privileges;

Revoke

If you want to revoke all privileges of user manish, you can do it as follows:

		mysql/bin> mysql -u root -p
		mysql> use mysql;
		mysql>REVOKE ALL PRIVILEGES FROM 'manish'@'localhost';
		mysql> flush privileges;

---

Table Maintenance Statements

ANALYZE TABLE

This sql command is used to analyze and store the key distribution to a table.
During the analyzing process, table is locked with write lock.

		mysql> ANALYZE TABLE Emp;		

Note : If you are using this on master replication server then this statement is written to binary log, means this statement will execute on slave server as well. If you do not want to write in binlog then use optional keyword NO_WRITE_TO_BINGLOG.

CHECK TABLE

CHECK TABLE statement is used for checking a table for errors. This statement works for InnoDB, MyISAM and ARCHIVE tables.

		mysql> CHECK TABLE user;
		
		/* Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent. 
		     This is a very slow operation.  */
		mysql> CHECK TABLE user EXTENDED;

		/* QUICK doesn’t check the rows for invalid links.  */
		mysql> CHECK TABLE user QUICK;

		/* CHANGED checks for tables that wern’t closed properly or that have been changed since the last time you ran a check.  */
		mysql> CHECK TABLE user CHANGED;

		/* FAST only checks tables which have not been closed properly. */
		mysql> CHECK TABLE user FAST;

CHECKSUM TABLE

CHECKSUM TABLE statement is used to report a table checksum.

		/* By using the QUICK option the live table checksum is reported when it is available else its reported NULL.*/
		mysql> CHECKSUM TABLE user QUICK;

		/* By using EXTENDED option the table reads row by row and checksum is calculated. It is very slow for large tables.*/
		mysql> CHECKSUM TABLE user EXTENDED ;

Note: If any option is not specified then MySQL returns a live checksum if table storage engine supports it else it scans the table.

OPTIMIZE TABLE

OPTIMIZE TABLE is used to remove overhead, sort indexes for better access, and generally keep your tables in good order

		mysql> OPTIMIZE TABLE user;

Note : If you are using a replication setup, specify NO_WRITE_TO_BINLOG to avoid logging the statement and having it spread to your slaves.

REPAIR TABLE

REPAIR TABLE statement is used for repairing the possible corrupted table and it is worked only for MyISAM and ARCHIVE tables

		 /* QUICK option repair only the index tree */
		mysql> REPAIR TABLE user QUICK 

		 /* With EXTENDED option, MySQL creates the index row by row */
		mysql> REPAIR TABLE user EXTENDED