Database 1

DATABASE MYSQL Tutorial

Database  Coherent

collection of data  Designed, built and populated with data for a specific purpose  Stores useful data for us  Collection of files

Database  Structured    

Query Language

Query refers to “ asking question” Structured refers to tables ,views or schemas Language is any type (MYSQL, SQL+ etc) Structured query language refers to querying data from the databases.

Database 

Types of query languages 

Data Retrieval Language (DRL) 



Data Definition Language (DDL) 



To define the structures

Data Manipulation Language (DML) 



Retrieve data from database

To change data internally

Data Control language (DCL) 

To control the access of user

Database  Data     

Types

Integer (integer values) Decimal (number with fraction) Char ( fixed length string) Varchar (variable length string) Date (display date)

DATA DEFINITION LANGUAGE To define or describe the structures of database.     

CREATE (to create table) ALTER (to modify table ) DROP (to delete table with data) RENAME (to change the name of table) TRUNCATE (to remove data from table)

DATA DEFINITION LANGUAGE  CREATE

CREATE TABLE table name ( column_name1 datatype , column_name2 datatype ,....... )

CREATE TABLE emp ( empid int, Name varchar(20), Fname varchar(20), Age int )

 ALTER

(To add or modify the table)

ALTER TABLE table name ADD ( column_name1 datatype , column_name2 datatype ,....... )

=> ALTER TABLE emp ADD ( sal int );

 DROP

(To drop table with its data )

=> DROP TABLE table name;

=> DROP TABLE emp;

 RENAME

(To rename the existing table )

=> RENAME TABLE ( old name ) TO (new name);

=> RENAME TABLE emp TO person;

 TRUNCATE

(To remove data from table)

=> TRUNCATE TABLE (table name);

=> TRUNCATE TABLE emp;

DATA MANIPULATION LANGUAGE Use to change data or records the internally.   

INSERT (to insert data in table) UPDATE (to modify data in table ) DELETE (to delete data in table)

INSERT (To insert data in table)

=> INSERT INTO TABLE emp SET

empid=101, Name =‘Ali’, Fname=‘Khalid’, Age = 25;

UPDATE (to modify the record)

=> UPDATE emp SET

Name =‘waseem’, WHERE empid= 101;

DELETE

DELETE FROM (table_name); DELETE FROM emp;

DATA RETRIEVEL LANGUAGE

Use to retrieve data from database. 

SELECT (to insert data in table) (only one command in DRL)

SELECT

There are many faces of select command. 1)- Global data extraction SELECT * FROM emp;

2)- Specific data extraction SELECT empid,name FROM emp;

DATA CONSTRAINTS  Rules

or checks define by users  Strictly applied on database  It restrict user from particular activities  Placed either at table or column level

Primary

Key

 Uniquely

a table.

 Consist  Must

identify each row (record) in

of one or more column

not be null

 Unique

across the column

 Multicolumn

primary key is called composite key

 Ex

: NIC number

Example: => CREATE

TABLE emp

( empid int primary key, Name varchar(20), Fname varchar(20), Age int );

DEFAULT

KEY

 Define

a default value for a particular column

 When

user leave empty then this default value is placed

Example:

=> CREATE TABLE emp ( empid int, Name varchar(20), Fname varchar(20), gender varchar(5) default ‘M’; )

 NULL

KEY



To prevent from lack of data for a particular column



NULL placed in place of value



NOT NULL is used to prevent null value

Example: => CREATE TABLE emp ( empid int not null primary key, Name varchar(20), Fname varchar(20), Age int )

CLAUSES  WHERE

 Extract

=>

or filter specific data from table

SELECT name,age FROM emp WHERE emid=12;

 DISTINCT 

CLAUSE

To remove duplication of data in output.

Example: => SELECT DISTINCT name FROM emp;

 ORDER

 To

BY CLAUSE

sort data in ascending order

Example: => SELECT name FROM emp ORDER BY name; =>

SELECT * FROM emp ORDER BY name;

AGREGATE FUNCTIONS  Functions

use to manipulate data  Result in a single value Avg  Min  Max  Sum  Count 

Example: =>

SELECT AVG(sal) FROM emp

=>

SELECT MIN(sal) FROM emp

=>

SELECT MAX(sal) FROM emp

=>

SELECT SUM(sal) FROM emp

=>

SELECT COUNT(sal) FROM emp

LOGICAL OPERATORS  AND

(all of must be included)

SELECT empid,name FROM emp WHERE empcity=‘karachi’ AND empgender=‘male’;

 OR

(any of may be included)

=> SELECT empid,name FROM emp WHERE empcity=‘karachi’ OR empgender=‘male’; => SELECT empid,name FROM emp WHERE empcity=‘karachi’ AND (empgender=‘male’ OR empdsg=‘manager’);

RANGE SEARECHING Use when find record from specific range - BETWEEN SELECT empid,name FROM emp WHERE empsal BETWEEN 10000 AND 20000

- NOT BETWEEN SELECT empid,name FROM emp WHERE empsal NOT BETWEEN 10000 AND 20000

- IN SELECT empid,name FROM emp WHERE empsal IN(10000,20000,30000)

NOT IN SELECT empid,name FROM emp WHERE empsal NOT IN(10000,20000,30000);

PATTERN MATCHING  To

match characters or string



% match any string



( _ underscore) match single character



LIKE predicate use for matching

 Example:

=> SELECT * FROM emp WHERE name LIKE ‘_a%’;

=> SELECT * FROM emp WHERE name LIKE ‘ka_s%’;

=> SELECT * FROM emp WHERE name LIKE ‘ka_ _ _ _’;

ALIAS  Temporary

name or tag assign to column

or table  AS operator use to assign alias => SELECT sal*10 AS increment FROM salary;

INDEXES  An

ordered list of a column or group of column  Use for fast searching  User cannot see indexes  Used to speed up queries  Single column is called “ simple index ”  Multi column is called “composite index”

 Example

:

=> CREATE INDEX emp_ndx ON emp(name); => CREATE INDEX emp_ndx ON emp(name,age);

End  Questions?