MySQL Basics

I will briefly take you through creating a table in Mysql (using MySQL prompt).

To start the MySQL prompt

c:\>mysql -u root -p

Enter the password and access the MySQL prompt.

mysql>

1. Create new database:

SYNTAX: create database databasename;

mysql> create database emp_db;

To see the created database

mysql>Show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| emp_db            |
| mysql              |
| test               |
+--------------------+


To use the database you created

SYNTAX: use databasename;

mysql> use emp_db;


2. Create a table

SYNTAX: create table tablename (columnname1 datatype(size), columnname2 datatype(size) , columnname3 datatype(size),PRIMARY KEY (columname));

mysql>Create table employee (userid INT NOT NULL AUTO_INCREMENT , firstname varchar (50), lastname varchar (50) , PRIMARY KEY (userid));


3. Insert values into the table

SYNTAX: Insert into tablename (columname1,columname2…..) values (‘value1’,’value2’,…..);

mysql>Insert into employee(userid , firstname ,lastname) values ('1068' , 'JHON' , 'R');


4. Select table data

SYNTAX: Select * from tablename;

mysql>Select * from employee;

Result:
+--------+-----------+----------+
| userid | firstname | lastname |
+--------+-----------+----------+
|   1068 | JHON      | R        |
+--------+-----------+----------+


5. Update data in a table

SYNTAX: Update tablename SET column1='newvalue' where column1="oldvalue";

or

SYNTAX: Update tablename SET column1='newvalue' where column2="referencevalue";

mysql> Update employee SET lastname='Robert' where column1="R";


6. Select data by joining two tables

SYNTAX: SELECT table1.column1 ,table1.column2, FROM table1 INNER JOIN table2 ON table1.column1=table2.column1;

mysql> SELECT employee.userid , employee.firstname, emp_details.city ,emp_details.country  FROM employee  INNER JOIN emp_details  ON employee. userid = emp_details. userid;

mysql> select * from employee;
+--------+-----------+----------+
| userid | firstname | lastname |
+--------+-----------+----------+
|   1068 | JHON      | R        |
|   1069 | MICKEL    | C        |
|   1070 | ADAM      | S        |
|   1071 | BELL      | D        |
|   1072 | DANIEL    | A        |
+--------+-----------+----------+

mysql> select * from emp_details;
+--------+-----------+-----------+
| userid | city      | country   |
+--------+-----------+-----------+
|   1068 | newyork   | USA       |
|   1069 | london    | UK        |
|   1070 | paris     | Germeny   |
|   1071 | new delhi | india     |
|   1072 | sydney    | Australia |
+--------+-----------+-----------+

mysql> SELECT employee.userid , employee.firstname, emp_details.city ,emp_details.country  FROM employee  INNER JOIN emp_details  ON employee. userid = emp_details. userid;
+--------+-----------+-----------+-----------+
| userid | firstname | city      | country   |
+--------+-----------+-----------+-----------+
|   1068 | JHON      | newyork   | USA       |
|   1069 | MICKEL    | london    | UK        |
|   1070 | ADAM      | paris     | Germeny   |
|   1071 | BELL      | new delhi | india     |
|   1072 | DANIEL    | sydney    | Australia |
+--------+-----------+-----------+-----------+


7. Delete table data

SYNTAX: DELETE FROM table_name WHERE columnname=value;

mysql> FROM employee WHERE lastname=’Robert’;


8. Delete all rows in a table

SYNTAX: DELETE * FROM table_name ;

mysql> DELETE * FROM employee;


9. Delete a table

SYNTAX: DROP TABLE table_name;

mysql> DROP TABLE employee;


10. Delete a database

SYNTAX: DROP DATABASE database_name;

mysql> DROP DATABASE employee;

For more visit Collegelabs