CHARSETS AND COLLATIONS

Standard

a character set is a set of symbols and encodings
a collation is a set of rules for comparing characters in a character set

suppose that you have an alphabet with four letters:
‘A’, ‘B’, ‘a’, ‘b’
giving each letter a number:
‘A’ = 0, ‘B’ = 1, ‘a’ = 2, ‘b’ = 3
the letter ‘A’ is a symbol, the number 0 is the encoding for ‘A’, and the combination of all four letters and their encodings is a character set

suppose that you want to compare two string values, ‘A’ and ‘B’
the simplest way to do this is to look at the encodings, so 0 for ‘A’ and 1 for ‘B’
because 0 is less than 1, we say ‘A’ is less than ‘B’
what you’ve just done is apply a collation to our character set: the collation is a set of rules (only one rule in this case): “compare the encodings”
this is the simplest of all possible collations a binary collation

and if you want to say that the lowercase and uppercase letters are equivalent?
then you would have at least two rules:
(1) treat the lowercase letters ‘a’ and ‘b’ as equivalent to ‘A’ and ‘B’
(2) then compare the encodings
this is a case-insensitive collation

in real life, most character sets have many characters: not just ‘A’ and ‘B’ but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks
in real life, most collations have many rules: not just case insensitivity but also accent insensitivity and multiple-character mappings (like the rule that ‘ö’ = ‘OE’ in one of the two German collation)

to sum up, a character encoding is a way to encode characters so that they fit in memory
if the charset is ISO-8859-15, the euro symbol, €, will be encoded as 0xa4, but, if the charset is UTF-8, it will be 0xe282ac
the collation is how to compare characters

MYSQL METADATA DATABASES

Standard

metadata are data about the data, such as the name of a database or table, the data type of a column, or access privilege
mySQL metadata databases are special databases that hold informations about other databases

they can be listed, giving the normal mySQL commands to list existing databases
mysql > show databases;
mysql metadata databases
in my snapshot, the metadata databases present are information_schema, mysql and performance_schema

mysql > use information_schema;
mysql > show tables;
information_schema tables

information_schema database of mySQL is where mySQL saves information about all the other databases maintains
contains information about database or table names, the data type of a column, or access privileges

these tables are read-only views: it’s not possible to change its structure or modify its data, their purpose is to provide information about the database system
all tables in the information_schema database are stored directly in memory as memory storage engine tables: when mysqld is shutdown, all information_schema tables are dropped, when mysqld is restarted, all information_schema tables are recreated as temporary tables and repopulated with metadata for every table

when querying the information_schema database, you’ll only be interested in your own databases, not the metadata ones
to remove them from query results, include this filter in the where clause:

AND information_schema.TABLES.table_schema
NOT IN (“information_schema”, “
mysql”, “performance_schema”);

normally, the describe and show commands are used to get metadata information
however using the information_schema views, you can execute the standard select sql command to access metadata informations

example 1
query to display tables with more than 100 rows using table: information_schema.tables
to see the content of this table
mysql > select * from information_schema.tables limit 10
mysql > select concat(table_schema,’.’,table_name) as table_name, table_rows
-> from information_schema.tables where table_rows > 100
-> order by table_rows desc;
information_schema query 1
example 2
query to list top 5 largest tables in the database
gives the top 5 largest space occupying tables in the database along with it’s size in MB
mysql> select concat(table_schema,’.’,table_name) as table_name,
-> concat(round(data_length/(1024*1024),2),’M’) as data_length
-> from information_schema.tables
-> order by data_length desc
-> limit 5;
information_schema query 2

QUERY SYNTAX

Standard

DATA SELECTION
SELECT column_name,column_name
FROM table_name;

SELECT DISTINCT column_name,column_name
FROM table_name;

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
// (city = “trieste”)

SELECT * FROM Customers
WHERE Country=’Germany’
AND City=’Berlin’;
// the AND operator shows records where both conditions are matched

SELECT * FROM Customers
WHERE City=’Berlin’
OR City=’München’;
// the OR operator shows records when at least one of the conditions is matched

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
// to order records

DATA INSERT
INSERT INTO table_name (column1,column2,column3,…)
VALUES (value1,value2,value3,…);

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’,’Tom B. Erichsen’,’Skagen 21′,’Stavanger’,’4006′,’Norway’);

DATA UPDATE
UPDATE table_name
SET column1=value1,column2=value2,…
WHERE some_column=some_value;

UPDATE Customers
SET ContactName=’Alfred Schmidt’, City=’Hamburg’
WHERE CustomerName=’Alfreds Futterkiste’;
// to update already existent records

DATA DELETE
DELETE FROM table_name
WHERE some_column=some_value;
DELETE FROM Customers
WHERE CustomerName=’Alfreds Futterkiste’ AND ContactName=’Maria Anders’;

LIKE
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

SELECT * FROM Customers
WHERE City LIKE ‘s%’;
// selects all customers where city name starts by “s”

SELECT * FROM Customers
WHERE City LIKE ‘%s’;
// selects all customers where city name ends by “s”

SELECT * FROM Customers
WHERE Country NOT LIKE ‘%land%’;
selects all customers where city name contains “land”

IN
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…);

SELECT * FROM Customers
WHERE City IN (‘Paris’,’London’);
// selects all customers where city name is paris or london

BETWEEN
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
// selects all products whose price is between the two values

LIMIT
SELECT column_name(s)
FROM table_name
LIMIT number
// show the first 100 rows that match the queries

ALIAS
SELECT column_name AS alias_name
FROM table_name;
SELECT column_name(s)
FROM table_name AS alias_name;
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName=”Around the Horn” AND c.CustomerID=o.CustomerID;
// an alias is another name (easier) to select a table_name.column_name

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName=”Around the Horn” AND Customers.CustomerID=Orders.CustomerID;

SQL INNER JOIN
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
// joins are used to combined results from two table, based on a common field value

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
// INNER JOIN is the same as JOIN

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
// the field used to bind the tables is CustomerID
// resulting records are only those where CustomerID values are the same in both tables

SQL LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
// 
returns all the rows of table1 (the left table) and the table2 (right table) rows that matches, in value, on the specified common column_name (if a row has no match with right table, value is NULL)

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
// LEFT JOIN is the same as LEFT OUTER JOIN

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
// select all customers name from Customers table and return the OrderID from Orders, only when CustomersID from Customers and CustomersID from Orders have the same value (if the value is different, all customers name are printed, but the OrdersID will be NULL

FULL OUTER JOIN
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
// returns all the rows from table 1 and all the rows from table2

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
// select all records from both table, in any case of matching

UNION
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
// UNION is used to combine the results from two or more SELECT

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

CREATE [OBJECT] [OBJECT_NAME]
CREATE DATABASE my_db;
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
….
);

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

SQL VIEW
a VIEW is a virtual table, that is based on a result set from another SQL query
it contains rows and columns, like a real table (columns come from one or more tables of the database), so data can be presented like they would be from a single table
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

SELECT * FROM [Current Product List]
// querying a VIEW like a normal table

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName=’Beverages’
// adding conditions to the VIEW

PHP AND MYSQL

Standard

l’accesso a un database mysql può essere fatto tramite delle semplici funzioni: fino alla versione 4 di php, esse erano integrate nel linguaggio, mentre dalla versione 5 bisogna installare mysql e il suo modulo a parte.

$host= “localhost”;
$user = “admin”;
$password = “admin”;
$db = “rubrica”;

$conndb = mysql_connect($host, $user, $password) or die(“impossibile connettersi al server $host”);

con la funzione mysql_connect è possibile connettersi all’host che ospita il database, passandogli come parametri il nome dell’host, il nome dell’utente con cui ci si connette all’host e la sua password. la connessione all’host viene salvata come variabile

mysql_select_db($db, $conndb);
con la funzione mysql_select_db ci si connette all’host che ospita il database ed è possibile connettersi all’istanza del database corretta. questo è essenziale perché un database server può contenere al suo interno diverse istanze database

$query = “select * from rubrica”;
dopo aver selezionato il database, si definisce una variabile che contiene la query che si vuole effettuare

$dbresult = mysql_query($query, $conndb);
attraverso la funzione mysql_query, che prende come argomento la variabile che contiene la query e la connessione al database, si applica la query al database e si inserisce il risultato in una variabile

$affectedrows = mysql_affected_rows($conndb);
con la funzione mysql_affected_rows si ottiene il numero dei record che sono interessati dalla precedente esecuzione della query
print(“numero di record trovati: $affectedrows”);

mysql_close($conndb);
con la funzione mysql_close viene rilasciata la connessione al database di modo che un altro client possa usarla

query di estrazione
una delle operazioni che si possono compiere su un database è l’estrazione dei dati da una tabella.
print (“<table border=\”1\” >\n”);
\\ è necessario inserire un escape sequence
while ($line = mysql_fetch_array($dbresult, MYSQL_NUM))
{
print(“\t<tr>\n”);
foreach ($line as $colvalue)
{
print(“\t\t<td>$colvalue</td>\n”);
}
print (“\t<\tr>\n”);
}
print (“<\table>\n”);
mysql_free_result($dbresult);
mysql_close($conndb);

la situazione è la stessa dell’esempio precedente, ma cambia quando si tratta di usare i dati estratti dalla query
la funzione mysql_fetch_array prende come argomento la variabile che contiene il risultato della query e il tipo_array, caricando una riga (row) del risultato come un array associativo: è usata per mettere i dati estratti da un database in un array

l’array può avere chiavi* diverse a seconda del valore della costante tipo_array:
MYSQL_ASSOC = la chiave dell’array è il nome delle colonne della tabella
MYSQL_NUM = la chiave dell’array è un numero intero
MYSQL_BOTH = la chiave dell’array è sia il nome delle colonne della tabella sia un numero intero
\\ il ciclo while continua finché ci sono record nel risultato della query che possono essere espressi come array

* un array è un insieme formato dalle coppie chiave-valore. la chiave può essere un numero o una stringa. quando si istanzia un array, di default il tipo di array è numerico: associa ad ogni valore che viene inserito nell’array un numero.
$colori = array(‘viola’, ‘bianco’, ‘blu’);
per avere delle stringhe come indici bisogna usare l’apposita sintassi.
$persona[‘nome’] = ‘mario’;
$persona[‘cognome’] = ‘rossi’;

inserimento di un record
l’inserimento di un record in una tabella attraverso un’interfaccia web prevede l’utilizzo di un form html che passi i valori da inserire a uno script in grado di inserirli fisicamente all’interno del database.
<table>
<form method=“post” action=”8-4.php”>
<tr><td>cognome</td>
<td><input type=“text” name=”cognome” size=”20” maxlenght=”20”</td></tr>
<tr><td>azienda</td>
<td><input type=”text” name=”azienda” size=”20” maxlenght=”20”</td></tr>
<tr><td colspan=\”2\”><input type=”submit” value=”inserisci”></td></tr>
</form>
</table>
sapendo in anticipo la struttura del database, si può usare un form html statico componendolo in modo da avere i nomi dei campi di input coincidenti con i nomi dei campi della tabella del database che si andrà a popolare.
$host = “localhost”;
$user= “admin”
$password = “admin”
$db = “rubrica”;
$conndb =mysql_connect($host, $user, $password) or die(“impossibile connettersi al server $host!”);
mysql_select_db($db, $conndb) or die(“impossibile connettersi al database $db!”);
$query = “insert into rubrica” . “(cognome, azienda)” . ”VALUES(‘” . $_REQUEST[‘cognome’] . ”’,’” . $_REQUEST[‘azienda’] . ”’);
if (!mysql_query($query, $conndb))
{
print(“attenzione, impossibile inserire il record!”);
}
else
{
print(“il record è stato inserito.”);
}
mysql_close($conndb);

la composizione di una query standard per l’inserimento di record è:
insert into tabella (campo1, campo2, campo3) VALUES (“valore1″,”alore2″,”valore3”)
l’esecuzione della query di inserimento record avviene ancora tramite la funzione mysql_query, che restituisce valore TRUE se il record viene correttamente inserito, o un valore FALSE in caso di errore.