Installation of XAMPP
XAMPP
• XAMPP is an abbreviation for cross-platform, Apache,
MySQL, PHP and Perl
• It allows to build WordPress site offline, on a local web
server on your computer.
• Simple and lightweight solution
• Works on Windows, Linux, and Mac – hence the “cross-
platform” part.
• Since WordPress isn’t a stand-alone application, XAMPP
provides two essential components for its installation –
Apache and MySQL
• Apache, which is used to create the local server
• MySQL which you can use as a database for your website
Download Link
• [Link]
ml
Running MySQL using Command line
Online SQL
• [Link]
ename=trysql_op_in
Introduction to SQL
• [Link]
What is SQL?
• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL became a standard of the American
National Standards Institute (ANSI) in 1986,
and of the International Organization for
Standardization (ISO) in 1987
What Can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and
views
SQL is a Standard
• Although SQL is an ANSI/ISO standard, there
are different versions of the SQL language.
• However, to be compliant with the ANSI
standard, they all support at least the major
commands (such as SELECT, UPDATE, DELETE,
INSERT, WHERE) in a similar manner.
Using SQL in Your Web Site
• To build a web site that shows data from a
database, you will need:
• An RDBMS database program (i.e. MS Access,
SQL Server, MySQL)
• To use a server-side scripting language, like
PHP or ASP
• To use SQL to get the data you want
• To use HTML / CSS to style the page
RDBMS
• RDBMS stands for Relational Database
Management System.
• RDBMS is the basis for SQL, and for all modern
database systems such as MS SQL Server, IBM
DB2, Oracle, MySQL, and Microsoft Access.
• The data in RDBMS is stored in database
objects called tables. A table is a collection of
related data entries and it consists of columns
and rows.
• Every table is broken up into smaller entities called
fields. i.e. CustomerID, CustomerName, ContactName,
Address, City, PostalCode and Country.
• A field is a column in a table that is designed to
maintain specific information about every record in the
table.
• A record, also called a row, is each individual entry that
exists in a table. i.e. there are 91 records in the above
Customers table
• A record is a horizontal entity in a table.
• A column is a vertical entity in a table that contains all
information associated with a specific field in a table.
Database Tables
• A database most often contains one or more
tables.
• Each table is identified by a name (e.g.
"Customers" or "Orders").
• Tables contain records (rows) with data.
Example Table
• five records and seven columns
SQL Statements
• Most of the actions you need to perform on a
database are done with SQL statements.
• The following SQL statement selects all the
records in the "Customers" table:
SELECT * FROM Customers;
• SQL keywords are NOT case sensitive: select is
the same as SELECT
Semicolon after SQL Statements?
• Some database systems require a semicolon at
the end of each SQL statement.
• Semicolon is the standard way to separate
each SQL statement in database systems that
allow more than one SQL statement to be
executed in the same call to the server.
Some of The Most Important SQL
Commands
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table SELECT - extracts data
from a database
• INSERT INTO - inserts new data into a database
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index
CREATE DATABASE Statement
• The CREATE DATABASE statement is used to
create a new SQL database.
CREATE DATABASE databasename;
i.e. CREATE DATABASE testDB;
DROP DATABASE Statement
• The DROP DATABASE statement is used to
drop an existing SQL database.
DROP DATABASE databasename;
i.e. DROP DATABASE testDB;
BACKUP DATABASE Statement
• The BACKUP DATABASE statement is used in
SQL Server to create a full back up of an
existing SQL database.
BACKUP DATABASE databasename
TO DISK = 'filepath';
i.e. BACKUP DATABASE testDB
TO DISK = 'D:\backups\[Link]';
BACKUP WITH DIFFERENTIAL Statement
• A differential back up only backs up the parts of
the database that have changed since the last full
database backup.
BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;
i.e. BACKUP DATABASE testDB
TO DISK = 'D:\backups\[Link]'
WITH DIFFERENTIAL;
CREATE TABLE Statement
• The CREATE TABLE statement is used to create a new table in a
database.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
i.e. CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Create Table Using Another Table
• A copy of an existing table can also be created using CREATE TABLE.
• The new table gets the same column definitions. All columns or
specific columns can be selected.
• If you create a new table using an existing table, the new table will
be filled with the existing values from the old table.
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;
i.e. CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;
DROP TABLE Statement
• The DROP TABLE statement is used to drop an
existing table in a database.
DROP TABLE table_name;
i.e. DROP TABLE Shippers;
TRUNCATE TABLE
• The TRUNCATE TABLE statement is used to
delete the data inside a table, but not the
table itself.
TRUNCATE TABLE table_name;
i.e. TRUNCATE TABLE Persons;
ALTER TABLE Statement
• The ALTER TABLE statement is used to add,
delete, or modify columns in an existing table.
• The ALTER TABLE statement is also used to add
and drop various constraints on an existing table.
ALTER TABLE table_name
ADD column_name datatype;
i.e. ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
• To delete a column in a table, use the
following syntax (notice that some database
systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name;
i.e. ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - ALTER/MODIFY COLUMN
• To change the data type of a column in a table
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
i.e. ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
SELECT Statement
• The SELECT statement is used to select data from a
database.
• The data returned is stored in a result table, called the
result-set.
• SELECT Syntax
SELECT column1, column2, ...
FROM table_name;
• Here, column1, column2, ... are the field names of the table
you want to select data from.
• If you want to select all the fields available in the table, use
the following syntax:
SELECT * FROM table_name;
SELECT CustomerName, City FROM Customers;
SELECT * FROM Customers;
SELECT DISTINCT Statement
• The SELECT DISTINCT statement is used to
return only distinct (different) values.
• Inside a table, a column often contains many
duplicate values; and sometimes you only
want to list the different (distinct) values.
SELECT DISTINCT column1, column2, ...
FROM table_name;
Example
SELECT Country FROM Customers;
SELECT DISTINCT Country FROM Customers;
Output
SELECT Country FROM Customers;
Output
SELECT DISTINCT Country FROM Customers;
Output
SELECT COUNT(DISTINCT Country) FROM Customers;
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);