Useful MySQL commands and queries

We all know that MySQL database is widely used open source database. So lets see few simple examples like how to start and stop MySQL service, creating database and few simple query for create, select, update, delete and alter tables. First lets see how to start and stop MySQL server along with few other commands in Unix machines. 


START MYSQL:
# /etc/init.d/mysqld start


STOP MYSQL:
# /etc/init.d/mysqld stop


CHECK MYSQL STATUS:
# /etc/init.d/mysqld status


RESTART MYSQL:
# /etc/init.d/mysqld restart


Login to MySQL database:
# mysql -u root -p <ENTER>
Enter password: <ENTER ROOT PASSWORD>
mysql>


LIST AVAILABLE DATABASES:
mysql>show databases;


CHANGE ROOT USER PASSWORD:
mysql> use mysql;
Database changed
mysql> update user set password=PASSWORD("mypassword") where User='root';
mysql> flush privileges;

NOTE: Once we change password need to flush privileges or need to restart MySQL database.


CREATE DATABASE:
mysql> create database <DATABASE_NAME>;


DROP DATABASE:
drop database <DATABASE_NAME>;


SELECT DATABASE:
mysql> use <DATABASE_NAME>;
Database changed
mysql> 


LIST AVAILABLE TABLES:
mysql>show tables;


SHOW TABLE STRUCTURE:
mysql> desc <TABLE_NAME>;


CREATE TABLE (showing sample tables):
CREATE TABLE tbl_master (
        user_id int not null primary key AUTO_INCREMENT,
user_name varchar(100) not null                
);

CREATE TABLE tbl_details (
id int not null primary key AUTO_INCREMENT,
user_id int not null,
project_name varchar(100) not null,
project_start datetime,
project_end datetime,
FOREIGN KEY (user_id) REFERENCES tbl_user_master(user_id)
);  


INSERT QUERY:
INSERT INTO tbl_master (user_name) VALUES("Rex");
INSERT INTO tbl_details (user_id, designation, date_of_birth) VALUES(1, "APAC-Mono", "2012-10-25", "2013-01-30");


SELECT QUERY:
SELECT * FROM tbl_master;
SELECT user_name, project_name, project_start, project_end FROM tbl_details as D, tbl_master as M WHERE D.user_id = M.user_id;   


UPDATE QUERY:
UPDATE tbl_details SET project_name = "APAC-Teli" WHERE project_name = "APAC-Mono";


ALTER QUERY:
ALTER TABLE tbl_master DROP PRIMARY KEY, ADD PRIMARY KEY(user_id, user_name);
ALTER TABLE tbl_master MODIFY user_name VARCHAR(150);
ALTER TABLE tbl_master DROP COLUMN user_name;
ALTER TABLE tbl_master ADD COLUMN user_name VARCHAR(150);


DELETE TABLE:
DELETE TABLE tbl_details WHERE user_id = 1;
DELETE TABLE tbl_details;


TRUNCATE TABLE:
TRUNCATE tbl_details;


DROP TABLE:
DROP TABLE tbl_details;