Essential MySQL commands and tools

Actually SQL queries are not case sensitive. But it is a standard if you use SQL commands in Upper case and others in Lower case. 
While working with CMD or DOS, ALWAYS USE a SEMICOLON at the end of the command. A semi colon is used to combine commands or provide multiple commands.

msql --user=root -p

SHOW DATABASES; # the semicolon is important

CREATE DATABASE dbname; # create the the database with the name dbname

USE dbname; # Use a particaular database before using any command like creating tables...

CREATE TABLE  table1(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30) NOT NULL,
password VARCHAR(20),
PRIMARY KEY(id)
) # Create a table, specify the primary key, this should be unique

INSERT INTO table1(id,cost,name) VALUES('103','9.95','carrots')

SELECT * FROM table1; #Displaying all the columns from the table table1

SELECT c_id, regkey FROM table1; #Displaying multiple columns from the table table1

SELECT DISTINCT c_id FROM table1;  #Displaying distinct c_id from table1, no duplicates

SELECT c_id FROM table1 LIMIT 5; #Displaying first 5 in column c_id 

SELECT c_id, address FROM table1 ORDER BY c_id; #Sorting with respect to c_id


SELECT c_id, address FROM table1 ORDER BY c_id, id; #Sorting with respect to c_id, id # Sorting first based on c_id, then by id


SELECT c_id, address FROM table1 ORDER BY id; # Sorting with respect to a column not choosen

SELECT table1.c_id FROM table1; # Actual fully qualified name of a column


SELECT c_id, address FROM table1 ORDER BY id DESC; # DESC is used to change the sort order to descending. Works both in numbers adn alphabets.

UPDATE table1 SET name='xyz' WHERE id=100; #Editing or updating a column of a particular row with a specific value

UPDATE table1 SET name='xyz',bids='10' WHERE id=100; #Editing or updating multiple columns of a particular row with a specific value 

DELETE FROM table1 WHERE id=106; #Deleting a particular row completely

ALTER TABLE table1 ADD sample_column varchar(10); #Adding a new column, Altering the table1

ALTER TABLE table1 MODIFY COLUMN sample_column varchar(30); # Modifying an existing column datatype

ALTER TABLE table1 DROP COLUMN sample_column; #Deleting a particulat column

DROP TABLE table1; #Entire table is deleted 

RENAME TABLE table1 TO table2