Mysql commands for Ubuntu

1. To install Mysql on ubuntu   

sudo apt-get install mysql-server  

2. To access or login to MySql the below command need to run from terminal. 

mysql -u root -p 

and it asks for the database password and we need to enter the password .  Where "root" is user-name of database   Two points to keep in mind:  Every  MySql commands must  end with a semicolon; if the command does not  end with a semicolon, the command will not execute and it prompt to next line without execution. 

3. To see all databases  

SHOW DATABASES;

e.g :  mysql> SHOW DATABASES;

4. To create a new database in MySql

 CREATE DATABASE database name;

e.g : mysql> CREATE DATABASE letsknowit;

You can check the database is created or not by running SHOW DATABASES; command and the new

database created will show in the listing .

5. To delete a database

DROP DATABASE database name;

6. To Access a  Database

   To open/access a database we can use  

mysql>USE databse_name;

 e.g. mysql> letsknowit;

7. To view the tables present in an existing database

 mysql>SHOW TABLES;

This will list all tables available in the present database, however if the database have no tables then a message will prompt "Empty set".

8. Create A MySql Table

   Let's create a table of student that contains StudentId, Studentname and StudentShoolname. 

CREATE TABLE student (StudentId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  Studentname VARCHAR(20), StudentShoolname VARCHAR(30);

To view the table use SHOW TABLES; command

mysql>SHOW TABLES;

9. To see the table complete structure we use

 mysql>DESCRIBE student;

10. Add a column to existing Table

 ALTER  TABLE [TABLE NAME] ADD [COLUMN NAME] AFTER [COLUMN NAME];

e.g. Mysql> ALTER  TABLE  student ADD  email VARCHAR(55) AFTER Studentname;

11. Delete a column from existing table

ALTER  TABLE [TABLE NAME] DROP [COLUMN NAME];

Mysql> ALETR TABLE student DROP email;

12. Add  values to an existing table

To add values to the existing table ‘student’ we will use below format

INSERT INTO  [TABLE NAME] ([COLUMN NAME1], [COLUMN NAME2], [COLUMN NAME3] ) VALUES ([VALUE1], [VALUE2], [VALUE3]);

INSERT INTO `student` (`StudentId`,`Studentname`,`StudentSchoolname`) VALUES (NULL, "Jack", "RLB School");

INSERT INTO `student` (`StudentId`,`Studentname`,`StudentSchoolname`) VALUES (NULL, "Tom", "RLB School");

To check the above values in our table student

Mysql>SELECT * FROM student;

 13. Update values of table

 UPDATE  [TABLE NAME] SET [COLUMN NAME]= [NEW VALUE] WHERE [COLUMN NAME]= [FIELD VALUE]

e.g. Mysql> UPDATE student SET Studentname=’tim’ WHERE student.StudentId = 1;

 14. Delete row from a table

DELETE FROM [TABLE NAME] WHERE [COLUMN NAME]= [FIELD VALUE]

Mysql >DELETE FROM Student WHERE StudentId = 1;

15. Assign User to Database

GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'host_name';