Sunday, October 18, 2015

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";
?>