Sunday, October 18, 2015

SQL DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value
 

Example

DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders'; 
 

Delete All Data

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
DELETE FROM table_name;

or

DELETE * FROM table_name;
 
 

SQL INSERT INTO, and Update Statements

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.
The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,...);
 
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,...);
 
"Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90
Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91
Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
 

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway'); 
 
The selection from the "Customers" table will now look like this:
CustomerID CustomerName ContactName Address City PostalCode Country
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90
Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91
Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway
 
 

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.
The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field will of course also be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
 
The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
 
"Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

SQL UPDATE Example

Assume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.
We use the following SQL statement:

Example

UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
 

SQL WHERE AND & OR Operators Syntax ORDER BY Keyword

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;


Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden


WHERE Clause Example

The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:

Example

SELECT * FROM Customers
WHERE Country='Mexico';


CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Ana Trujillo Emparedados y helados  Ana Trujillo  Avda. de la Constitución 2222  México D.F.  05021  Mexico 
Antonio Moreno Taquería  Antonio Moreno  Mataderos 2312  México D.F.  05023  Mexico 

AND & OR Operators

Example

SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin'; 
Number of Records: 1
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209  Germany 

OR Operator Example

The following SQL statement selects all customers from the city "Berlin" OR "München", in the "Customers" table: 

Example

SELECT * FROM Customers
WHERE City='Berlin'
OR City='München'; 

Number of Records:
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209  Germany 








Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).
The following SQL statement selects all customers from the country "Germany" AND the city must be equal to "Berlin" OR "München", in the "Customers" table:

Example

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

SQL ORDER BY Syntax

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;


ORDER BY Example

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:

Example

SELECT * FROM Customers
ORDER BY Country;


ORDER BY DESC Example

The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

Example

SELECT * FROM Customers
ORDER BY Country DESC;


ORDER BY Several Columns Example

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column:

Example

SELECT * FROM Customers
ORDER BY Country, CustomerName;
 

ORDER BY Several Columns Example 2

The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

Example

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
 

SQL SELECT DISTINCT Syntax

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name,column_name
FROM table_name;
 
"Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

Example

SELECT DISTINCT City FROM Customers;
 

SQL Quereis SELECT * FROM Customers;

"Customers" table:

CustomerID CustomerName ContactName Address City PostalCode Country
1
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
The table above contains five records (one for each customer) and seven columns (CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country).

Some of The Most Important SQL Commands

  • SELECT - extracts data from a database
  • UPDATE - updates data in a database
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index 
The following SQL statement selects all the records in the "Customers" table:

 SELECT * FROM Customers; 

SQL is NOT case sensitive: select is the same as SELECT

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

SQL SELECT Syntax

SELECT column_name,column_name
FROM table_name;
 
SELECT * FROM table_name;
 

SELECT Column Example

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:

Example

SELECT CustomerName,City FROM Customers;
 

SELECT * Example

The following SQL statement selects all the columns from the "Customers" table:

Example

SELECT * FROM Customers;

Monday, March 2, 2015

PHP MySQL HTML Code: Create database & Create Table, Add, edit Search...

PHP MySQL HTML Code: Create database & Create Table, Add, edit Search...: create database DBname; CREATE TABLE people (name VARCHAR(30), age INTEGER, height FLOAT, date DATETIME); INSERT INTO people VALUES (...

Create database & Create Table, Add, edit Search Delete in MySQL

create database DBname;
CREATE TABLE people (name VARCHAR(30), age INTEGER, height FLOAT, date DATETIME);

INSERT INTO people VALUES ( "Jim", 45, 1.75, "2006-02-02 15:35:00" ), ( "Peggy", 6, 1.12, "2006-03-02 16:21:00" )

UPDATE people SET age = 7, date = "2006-06-02 16:21:00", height = 1.22 WHERE name = "Peggy"
SELECT name FROM people WHERE age < 12
DELETE FROM people WHERE age < 12


Friday, February 6, 2015

Open a Connection to MySQL

<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

echo "Connected successfully";
?>

Thursday, January 29, 2015

HTML Comment

<!--This is a comment. Comments are not displayed in the browser-->

<p>This is a paragraph.</p>

Wednesday, January 21, 2015

PHP with HTML

<html> 
    <title>HTML with PHP</title> 
<body> 
      <h1>My Example</h1> 
            <?php //your php code here ?> 
            <b>Here is some more HTML</b> 
            <?php //more php code ?> 
 </body> 
</html>
_________________________________________________________________
<?php  Echo "<html>"; 
Echo "<title>HTML with PHP</title>"; 
Echo "<b>My Example</b>"; 
 //your php code here 
 Print "<i>Print works too!</i>";  ?>




PHP Sessions

A session is a way to store information (in the form of variables) to be used across multiple pages. Unlike a cookie, specific variable information is not stored on the users computer. It is also unlike other variables in the sense that we are not passing them individually to each new page, but instead retrieving them from the session we open at beginning of each page.

Call this code mypage.php

<?php  // this starts the session  session_start(); 
 // this sets variables in the session  
$_SESSION['color']='red';  
$_SESSION['size']='small';
$_SESSION['shape']='round'; 
 print "Done"; ?>

Now we are going to make a second page. We again will start with session_start() (we need this on every page) - and we will access the session information we set on our first page. Notice we aren't passing any variables, they are all stored in the session.
Call this code mypage2.php
<?php  // this starts the session  session_start(); 
 // echo variable from the session, we set this on our other page 
 echo "Our color value is ".$_SESSION['color'];
 echo "Our size value is ".$_SESSION['size']; 
 echo "Our shape value is ".$_SESSION['shape'];  ?>

All of the values are stored in the $_SESSION array, which we access here. Another way to show this is to simply run this code:

<?php  session_start();  Print_r ($_SESSION); ?>

Modify or Remove a Session

<?php  
// you have to open the session to be able to modify or remove it  session_start(); 
 // to change a variable, just overwrite it
 $_SESSION['size']='large'; 
 //you can remove a single variable in the session  unset($_SESSION['shape']);
 // or this would remove all the variables in the session, but not the session itself 
 session_unset(); 
 // this would destroy the session variables 
 session_destroy();  ?>