MySQL Basic

Introduction

    MySQL is a database management system and SQL stands for the Structured Query Language. It defines how to insert, retrieve, modify and delete data. MySQL is a relational database management systemused to store and manage huge volume of data.

• How to work with MySQL

    We can use Wamp Server,  Xwamp Server etc.

First of all,

Open wamp server à Select MySQL à Select MySQL console

• Find out the databases currently existing on the server.

    mysql> SHOW DATABASES; 

• Find out the tables currently existing on database.

    mysql> SHOW TABLES; 

• Cancelling a command

     mysql>dnflsfnl

         -> \c

   DROP DATABASE <database_name>;

•   Creating a database.

   mysql> CREATE DATABASE database_name;         

• Use a database.

   mysql> USE database_name;

• Creating a Table

  mysql>CREATE TABLE table_name
  mysql>(
  mysql>column_name1  data_type,
  mysql>column_name2  data_type,
  mysql>column_name3  data_type
  mysql>) ;

• Describing Tables

   mysql>descPerson;

• Searcing data.

   mysql> SELECT * FROM table_name;

• To rename a table, use the RENAME option of the ALTER TABLE statement

Syntax

                        ALTER TABLE old_name RENAME TO new_name;

 Eg:

                        CREATE TABLE Orders (FirstName   VARCHAR(15)

                        );

                        ALTER TABLE Orders RENAME TO Quiz;

• Insert Data Into a Database Table

1)The first form doesn't specify the column names where the data will be inserted, only their values:

 INSERT INTO table_name
VALUES (value1, value2, value3,...) ;

2)The second form specifies both the column names and the values to be inserted:

  INSERT INTO table_name (column1, column2, column3,...)

     VALUES (value1, value2, value3,...) ;

Example

Question

Database: Company

Table: Employee

Name	Age	DateOfBirth	Salary	Time
Anuhas	28	1986-03-18	70000.00	8.00
Denuwan	30	1984-09-20	60000.00	8.00
Madushani	26	1988-10-10	60000.00	8.00

Answer

mysql> CREATE DATABASE Company;      

mysql> USE Company;

mysql>CREATE TABLE Employee

mysql> (

mysql>Name VARCHAR(10),

mysql>Age INT(3),

mysql>DateOfBirth DATE,

mysql>City VARCHAR(45),

mysql>Salary DOUBLE(5 ,2 ),

mysql>Time TIME

mysql>);

INSERT INTO Employee

VALUES

(“Anuhas”, “28”, “1986-03-18”, “70000.00”, “8.00”),

(“Denuwan”, “30”, “1984-09-20”, “60000.00”, “8.00”),

(“Madushani”, “26”, “1988-10-10”, “60000.00”, “8.00”)

);

MySQL CONSTRAINT

1) NOT NULL

2) UNIQUE

3) PRIMARY KEY

4) FOREIGN KEY

5) DEFAULT 


1)      NOT NULL

u  Allows to specify that a column can not contain any NULL value.

Example:

CREATE TABLE Student

            (

            index_no INT(15) NOT NULL,

            name VARCHAR(50) NOT NULL

            ); 


2)      PRIMARY KEY

Method 1:

CREATE TABLE New_author1

(

aut_id INT(15) NOT NULL ,
aut_name VARCHAR(50) NOT NULL,
PRIMARY KEY (aut_id)

);

Method 2:

CREATE TABLE New_author2

(

aut_id INT(15) NOT NULL PRIMARY KEY,
aut_name VARCHAR(50) NOT NULL

); 


3)      UNIQUE

Does not allow to insert a duplicate value in a column

More than one UNIQUE column can be used in a table

Example:

CREATE TABLE Author

(

aut_id INT(15) NOT NULL PRIMARY KEY ,
aut_name VARCHAR(50)NOT NULL UNIQUE );

4)      FOREIGN KEY

Creates a link between two tables by one specific column of both table

The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY

CREATE TABLE Author

(

aut_id INT(15) NOT NULL PRIMARY KEY,

index_num INT(15)

);

CREATE TABLE Orders

(
O_Id INT(15) NOT NULL,
Order_No INT(15) NOT NULL,
aut_id INT(15),
PRIMARY KEY (O_Id),
FOREIGN KEY (aut_id) REFERENCES Author(aut_id)

);

5)      DEFAULT

While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT

Example:

CREATE TABLE Products

(

p_ID INT(15) PRIMARY KEY NOT NULL ,

p_Code CHAR(3) NOT NULL DEFAULT “”,

name VARCHAR(30) NOT NULL DEFAULT “saman”,

quantity INT(15) NOT NULL DEFAULT 0,

price DECIMAL(7,2) NOT NULL DEFAULT 99.99

);

DROP CONSTRAINT

Drop Foreign Key

ALTER TABLE Orders DROP FOREIGN KEY fk_cus_id;

Drop Primary Key

 ALTER TABLE Customers DROP PRIMARY KEY;

Drop Unique Key

ALTER TABLE Customers DROP INDEX Tele_no;

Drop Default

ALTER TABLE Customers ALTER City DROP DEFAULT;

ALTER TABLE table DROP INDEX new_column_name;

AUTO INCREMENT

• Creating a table with auto_increment

       Often you'll want to be able to automatically assign a sequential value to a column:

 CREATE TABLE example_autoincrement

            (

            id INT(15) NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100)

            );

MySQL  ALTER TABLE

 

        Used  to change the structure of an existing table
      ALTER TABLE helps to ,

            - add or drop a column,

            - change a column definition,

            - adding or dropping table constraints

• Used to add a newcolumn in a existing table

Syntax :

                        ALTER TABLE <table_name> ADDCOLUMN

                        <column_namedata_type(size)> ;

Example :

                        ALTER TABLE Suppliers ADDCOLUMNGender

                        VARCHAR(7) ;

•   Used to delete a column in a existing table

Syntax :

    ALTER TABLE <table_name> DROP COLUMN

<column_name>;

Example :

   ALTER TABLE Suppliers DROP COLUMN Gender;

•   Used to delete a column in a existing table

Syntax :

    ALTER TABLE <table_name> DROP COLUMN

<column_name>;

•   Used to change the data size of a column  and data type in a table

Syntax :

    ALTER TABLE <table_name> MODIFY COLUMN           <column_name><data_type>;

•   Used to change the existing field name in a table

Syntax :

                        ALTER TABLE <table_name> CHANGE

                        <old_colum_name  newcolum_name

            datatype(size)> ;

•  Used to update existing records in a table

Syntax :

                        UPDATE <table_name>

                        SET <column1=value, column2=value2,...>

                        WHERE <some_column=some_value>;

UPDATE Customers

  SET CUS_name=“Saroja”,Age=“30”

  WHERE CUS_name=“Saman”;

 UPDATE Customers

  SET City=“A”      

  WHERE Age<=“18”;

• Used to delete the data and all the table definitions

Syntax :

            DROP TABLE <table_name>;

•   MySQL DELETE statement removes records or rows from the table based on some given conditions

Syntax :

            DELETE FROM <table_name> WHERE <condition>;

DELETE FROM Subjects WHERE Sub_id=“002”;

MySQL SELECT STATEMENT

•   The simplest form of SELECT retrieves everything from a table

u       Syntax

                        SELECT * FROM table_name;

•   Select only particular rows from  table

u        Syntax

                        SELECT *

                        FROM table_name

                        WHERE column_name=“data_name”; 

• Select detail of the employee who lives in Colombo from the Employees table

                                    SELECT *

                                    FROM Employees

                                    WHERE City=“Colombo”;

 

If want to know which employees age are Greater than or equal to 40.

                        SELECT *

                        FROM Employees

                        WHERE Age>=“40”; 

Select  the employee who are female and from Kandy

                                    SELECT *

                                    FROM Employees

                                    WHERE City=“Kandy” AND Gender=“Female”;

Select the female employee  who got salary more than 30000 and are from Colombo

                                    SELECT *

                                    FROM Employees

                                    WHERE Gender=“Female” AND (City=“Colombo” 

                                              AND Salary>“30000”);

Select the employees name and age

                                    SELECT Name,Age

                                    FROM Employees; 

Select different city

                        SELECT DISTINCT City

                        FROM Employees;

 

Select male employees name and age, for city not equal Colombo

                                    SELECT Name,Age

                                    FROM Employees

                                    WHERE City!=“Colombo” AND Gender=“Male”;

PATTERN MATCHING

         MySQL pattern matching allows to use , “_” character to match any single character and “%” character to match an arbitrary number of characters (including zero characters)

u     MySQL patterns are use the LIKE or NOT LIKE comparison operators instead 

u     Syntax

                                    SELECT *

                                    FROM which_table

                                    WHERE column_name LIKE pattern

u     The underscore character can be used as a placeholder

u     Eg:

1) To find names start with “R” and containing exactly five characters

                                    SELECT *

                                    FROM Employees

                                    WHERE Name LIKE “R_ _ _ _”;

2) To find names containing exactly FOUR characters

                                    SELECT *

                                    FROM Employees

                                    WHERE Name LIKE “_ _ _ _”; 

u  Used to define  wildcards(missing letters in the pattern)  both before and after the Pattern

u  Eg:

1) To find names beginning with ‘R’:

                                    SELECT *

                                    FROM Employees

                                    WHERE Name LIKE “R%”;

2) To find employees  who's name end  with a ‘n’:

                                    SELECT *

                                    FROM Employees

                                    WHERE Name LIKE “%n”;

3) To find employees  who's name that contain  the pattern “ha”

                                    SELECT *

                                    FROM Employees

                                    WHERE Name LIKE “%ha%”;

• Retrieves the list of all records where the Designation is either Project Manager or Assistant in the Employees table

           

                        SELECT *

                        FROM Employees

                        WHERE Designation IN (“Project Manager”,

                        “Assistant”);

• Retrieves the list of all records where the Designation is not equal to Project Manager or Assistant in the Employees table

                        SELECT *

                        FROM Employees

                        WHERE Designation NOT IN (“Project Manager”,

                        “Assistant”);

• Retrieve all fields Employees table where the Salary is between 20000 AND 40000

                        SELECT *

                        FROM Employees

                        WHERE Salary BETWEEN 20000 AND 40000;

• Retrieve all fields Employees table where the Salary is not between 20000 AND 40000

                                    SELECT *

                                    FROM Employees

                                    WHERE Salary NOT BETWEEN 20000 AND 40000;

COUNT( ) 
 Syntax:-

                        SELECT COUNT(column_name)
                       FROM table_name;

SUM( ) 
 Syntax:-

                        SELECT SUM(column_name)
                        FROM table_name;

MAX( )
Syntax:-

                        SELECT MAX(column_name)
                        FROM table_name;

MIN( ) 
Syntax:-

                        SELECT MIN(column_name)
                        FROM table_name;

AVG( ) 
Syntax:-

                        SELECT AVG(column_name)
                       FROM table_name;

u  Used with MySQL SELECT statement to  group selected records into a set of summary records by the one or more column's value. 
            Eg:-

                        SELECT P_City

                        FROM Publisher GROUP BY P_City;