Transcript Basic SQL

PHP
Basic SQL
definition: Database
• A database which structures data in the form
of tables. Each table contains information
relevant to a particular feature, and is linked
to other tables by a common value. For
example, two attribute tables could be linked
to a spatial data table via a Geocode, such as
the postcode.
A very short explanation
• In one database. There are many tables.
• One table represents a set of entity. For example ‘People’ ,
‘Cars’ , ‘Movies’
• Table has many rows. One row in table represents instance of
entities. For example Toni Meechai’ , ‘Kobota Sedan’ ,
‘Twilight’
• Table also has many column. One column represents property
of an instance. For example Nationality’ , ‘How fast’ , ‘How
boring it is’
• When you want get or do something with Database Tables,
you can query it like ‘Please show me all people’.
Unfortunately, you have to follow SQL format.
Play with PHP MyAdmin
• Pay attention to demonstration.
• Try to understand ideas of ‘Database’ , ‘Table’,
‘Row’, ‘Basic usage of SQL’.
• Learners can follow this article:
http://www.php-editors.com/articles/sql_phpmyadmin.php
SQL : Selecting data
Selecting from 1 table
• SELECT * FROM COUNTRY;
• SELECT * FROM COUNTRY
WHERE CONTINECT=‘ASIA’;
• SELECT CODE,NAME,CONTINENT,POPULATION
FROM COUNTRY
WHERE CONTINENT=‘ASIA’;
• SELECT NAME,POPULATION
FROM COUNTRY
WHERE CONTINENT=‘ASIA’
ORDER BY POPULATION DESC;
• If we want to reverse it, change DESC to ASC
instead
• If it has many rows, we want just 10 rows
• SELECT NAME,POPULATION
FROM COUNTRY
WHERE CONTINENT=‘ASIA’
ORDER BY POPULATION DESC
LIMIT 10;
• If we want to change column name
• SELECT NAME AS “ประเทศ สุม่ ”
FROM COUNTRY
WHERE CONTINENT=‘ASIA’
ORDER BY RAND()
LIMIT 10;
• NOTE, If you want to change name to be
something like ประเทศ สุม่ , it will throws an error
• Because that word contains whitespace.
• List all countries started with ‘t’
SELECT NAME FROM COUNTRY
WHERE NAME LIKE ‘T%’;
• List all countries ended with ‘land’
SELECT NAME FROM COUNTRY
WHERE NAME LIKE ‘%land’;
• List all countries contains ‘ko’
SELECT NAME FROM COUNTRY
WHERE NAME LIKE ‘%ko%’;
Tables joining
Why joining?
• Refer to database course. It is better to store
different content into different table.
Reducing data redundant.
• MySQL, a database server we use is a
Relational engine. We can say one table can
has relationship to other tables.
• To get data across tables, we have to do
joining selection.
Relationship in our 3 tables
• Consider our 3 tables: Country, City,
CountryLanguage
• Table: Country has column ‘Code’
Table: City has column ‘CountryCode’
Table: CountryLanguage has column
‘CountryCode’
• Country.Code, City.CountryCode,
CountryLanguage.CountryCode are sharing
relationship.
• List all city in Thailand
SELECT * FROM CITY,COUNTRY
WHERE CODE=‘THA’
AND CODE=COUNTRYCODE;
• Remember. To join 2 tables we have to do 2
things.
1)Include 2 tables’name in From clause
2)Match the related columns in each 2 tables by
using equality in where clause
• If we have same columns name in 2 tables,
you will get error cause there are ambiguous
column names.
• To fix this problem, specific table name before
column name by using .(dot)
SELECT CITY.NAME,COUNTRY.NAME
FROM CITY,COUNTRY
WHERE CODE=‘THA’
AND COUNTRY.CODE=CITY.COUNTRYCODE;
Count and Sum
• Find surface area of Thailand + Malaysia
• SELECT SUM(SURFACEAREA)
FROM COUNTRY
WHERE NAME=‘THAILAND’
OR NAME=‘MALAYSIA’;
• Find how many cities located in Asia
• SELECT COUNT(CITY.NAME)
FROM CITY,COUNTRY
WHERE CONTINENT=‘ASIA’
AND COUNTRY.CODE=CITY.COUNTRYCODE;
Changing Data Using SQL
INSERT / UPDATE / DELETE
Prepare table first.
CREATE TABLE Student (
id bigint(20) auto_increment PRIMARY KEY,
email varchar(255),
firstname varchar(255),
lastname varchar(255),
nick varchar(255)
);
Inserting
• —1. Use SQL: “DESCRIBE tablename” to see the
target table’s structure.
Remember column name, type and sequence
• 2. Use SQL: “INSERT INTO VALUES(xxx,yyy,zzz)”
Replace xxx,yyy,zzz with actual values you
want to insert ordering as the table column’s
sequence.
INSERT INTO
student
values(
1,
‘[email protected]’,
‘Taw’,
‘Poldee’,
‘Tawny’
);"
Updating
• —
Updating SQL syntax is
UPDATE tablename
SET columnanme = newvalue
WHERE condition
UPDATE student
SET nick=‘Tawjung’
WHERE id=1;
Deleting
• Deleting SQL syntax is
DELETE FROM tablename WHERE condition.
DELETE FROM student
WHERE firstname=‘Taw’;
PHP interface with MySQL
<?php
mysql_connect("localhost:portnum", "user","password") or
die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());
$result = mysql_query("SELECT column1,column2 FROM
tablename") or die(mysql_error());
while($row = mysql_fetch_array($result)) {
print $row['column1'];
print $row['column2'];
}
?>