PHP - MySQL

01. XAMPP - Instalacion

Instalacion:

Cuando estoy instalando el xampp tengo la opcion de instalar el Apache y el mySql como servicios. El Apache es el servidor web local, es decir el que me permite "navegar" por contenidos web locales.

Si los instalo como servicios el efecto es que cada vez que el computador haga boot inmediatamente se cargas estos servicios, mientras que si no los instalo como servicios debo activarlos manualmente cada vez que vaya a usarlos.

En otras palabras a no ser que vaya a usar Apache y mySql con mucha frecuencia lo mejor es no instalarlos como servicios.

Para entrar al xampp podemos hacerlo por la opcion propia del xampp o digitando en la barra del navegador:

localhost/

Inmediatamente nos llevara al xampp y ahi llamamos al phpMyAdmin

El Link para la descarga de la aplicacion es :

https://www.apachefriends.org/index.html
-------------------------------------------------------------------------------------------------------------------------------------

ODBC:

Para activar la funcionalidad de ODBC:

1. Descargar Mysql ODBC e instalar

2. Descargar ODBC Administrator

3. Instalar Open Link ODBC Administrator

4. Abrir ODBC Administrator y crear la nueva fuente de datos (User Data Source) en la primera pestaña User DSN

5. Desde stata tener en cuenta activar la opcion Do not quote SQL table name

02. XAMPP - PHPMyAdmin

Users:

Una vez creada la base de datos (Incluso aun sin haber creado las tablas), puedo crear los nuevos usuarios para la base.

Asignadoles nombres de usuarios y passwords a cada uno.

Mientras se esta trabajando en el entorno local lo mejor es definirle el servidor al que tendra acceso como local.

-------------------------------------------------------------------------------------------------------------------------------------

New DataBase:

Para crear una nueva base de datos hay que tener en cuenta el cotejamiento o collation que no es mas que el set de caracteres que podran ser almacenados en la base.

Asi por ejemplo si deseo poder almacenar tildes y ñ por ejemplo debo escojer un collation de spanish.

Por lo general se escojen los collation tipo utf.

-------------------------------------------------------------------------------------------------------------------------------------

Upgrade:

Entro a la opcion de usuarios en algunas aplicaciones de xampp o de privilegios en otras.

Por lo general la version de phpMyAdmin del xampp no necesariamente esta actualizada, para lo cual lo ideal es actualizarla.

Para ello voy a la pagina:

www.phpmyadmin.net y de ahi descargo la nueva version.

Para instalar la nueva version:

- Bajo el archivo
- Modifico el nombre del folder de manera que solo quede nombrado phpMyAdmin
- Copio el foldes a htdocs dentro de xamppp

De esta manera quedo con dos phpMyAdmin:

1. El original al que acceso por localhost/
2. El nuevo al que acceso por localhost/phpMyAdmin

El nuevo phpMyAdmin me exige usuario y password para entrar, por lo cual si estaba usando root sin password en el anterior, debo asignarle un nuevo password.

El password para mi caso sera: Rolexbbm2010##

03. XAMPP - Engines

Federated:

- Permite manejar datos de un servidor remoto sin necesidad de replicacion o tecnologia de cluster
- Las tablas federadas locales no almacenan datos
- Cuando se necesita trabajar con la tabla local federada ella hace un query a la tabla remota.

-------------------------------------------------------------------------------------------------------------------------------------

CSV:

- Almacena la informacion en formato CSV
- Permite una facil migracion a hojas de calculo

-------------------------------------------------------------------------------------------------------------------------------------

Archive:

- Permite almacenar un gran numero de registros
- Como son para archivos, se guardan comprimidas con algoritmos tipo zip
- Las tablas archivadas solo permiten INSERT SELECT
- Las tablas archivadas no permiten indices

-------------------------------------------------------------------------------------------------------------------------------------

Memory:

- Las tablas son almacenadas en memoria, siendo mas rapidas que loas de MyISAM
- Los datos solo persisten mientas el servidor este prendido
- Se conoce formalmente como HEAP

-------------------------------------------------------------------------------------------------------------------------------------

Merge:

- Es una tabla virtual que combina multiples tablas MyISAM de similar estructura.
- La tabla virtual creada no tiene indices, en su lugar usa los indices de las tablas.
- Se usa para mejorar la velocidad de las operaciones de JOIN.
- Este motor solo permite: SELECT INSERT DELETE UPDATE
- Si se usa un comando DROP solo se elimina la estructura virtual de la tabla, no las tablas que la componen.

-------------------------------------------------------------------------------------------------------------------------------------

InnoDB:

- Total soporte de ACID compliants
- Optimizadas para el desempeño
- Soporta creacion de llaves Foraneas
- Soporta Roll Back
- Soporta Roll Forward
- Soporta Commit
- Tablas hasta de 64 Terabytes

http://blog.clustrix.com/2014/07/29/acid-compliance-means-care/

http://en.wikipedia.org/wiki/Rollback_(data_management)

http://databasemanagement.wikia.com/wiki/DIFFERENCE_BETWEEN_ROLLFORWARD_AND_ROLLBACK

http://en.wikipedia.org/wiki/Commit_(data_management)

-------------------------------------------------------------------------------------------------------------------------------------

MyISAM:

- Optimizadas para Compresion y Velocidad
- Tablas hasta de 256 Terabytes
- Cada vez que arranca chequea las tablas corruptas y las arregla
- Las tablas no son transaction-safe
- Era el motor por default antes de la version 5.5
Motores:

El motor por defecto es InnoDB, antes era MySam

03.01 - Mysql - Connect to MySQL

Description :

PHP 5 and later can work with a MySQL database using:

MySQLi extension (the "i" stands for improved)
PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Should I Use MySQLi or PDO?
If you need a short answer, it would be "Whatever you like".

Both MySQLi and PDO have their advantages:

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.

Both are object-oriented, but MySQLi also offers a procedural API.

Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

--------------------------------------------------------------------------------------------------

Open a Connection :

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:


Example (MySQLi Object-Oriented)

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



Example (MySQLi Procedural)

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

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

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>



Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
  $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

Note: In the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.

--------------------------------------------------------------------------------------------------

Close the Connection :

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

MySQLi Object-Oriented:
$conn->close();

MySQLi Procedural:
mysqli_close($conn);

PDO:
$conn = null;

03.02 - Mysql - Create a MySQL Database

A database consists of one or more tables.

You will need special CREATE privileges to create or to delete a MySQL database.

Create a MySQL Database Using MySQLi and PDO
The CREATE DATABASE statement is used to create a database in MySQL.

The following examples create a database named "myDB":


Example (MySQLi Object-oriented)

<?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);
}

// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
  echo "Database created successfully";
} else {
  echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

Note: When you create a new database, you must only specify the first three arguments to the mysqli object (servername, username and password).

Tip: If you have to use a specific port, add an empty string for the database-name argument, like this: new mysqli("localhost", "username", "password", "", port)




Example (MySQLi Procedural)

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

// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// Create database
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
  echo "Database created successfully";
} else {
  echo "Error creating database: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Note: The following PDO example create a database named "myDBPDO":




Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
  $conn = new PDO("mysql:host=$servername", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "CREATE DATABASE myDBPDO";
  // use exec() because no results are returned
  $conn->exec($sql);
  echo "Database created successfully<br>";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

Tip: A great benefit of PDO is that it has exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block. In the catch block above we echo the SQL statement and the generated error message.

03.03 - Mysql - MySQL Create Table

A database table has its own unique name and consists of columns and rows.

Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE statement is used to create a table in MySQL.

We will create a table named "MyGuests", with five columns: "id", "firstname", "lastname", "email" and "reg_date":

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Notes on the table above:

The data type specifies what type of data the column can hold. For a complete reference of all the available data types, go to our Data Types reference.

After the data type, you can specify other optional attributes for each column:

NOT NULL - Each row must contain a value for that column, null values are not allowed
DEFAULT value - Set a default value that is added when no other value is passed
UNSIGNED - Used for number types, limits the stored data to positive numbers and zero
AUTO INCREMENT - MySQL automatically increases the value of the field by 1 each time a new record is added
PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT
Each table should have a primary key column (in this case: the "id" column). Its value must be unique for each record in the table.

The following examples shows how to create the table in PHP:


Example (MySQLi Object-oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
  echo "Table MyGuests created successfully";
} else {
  echo "Error creating table: " . $conn->error;
}

$conn->close();
?>




Example (MySQLi Procedural)

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if (mysqli_query($conn, $sql)) {
  echo "Table MyGuests created successfully";
} else {
  echo "Error creating table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>



Example (PDO)

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

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // sql to create table
  $sql = "CREATE TABLE MyGuests (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  email VARCHAR(50),
  reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  )";

  // use exec() because no results are returned
  $conn->exec($sql);
  echo "Table MyGuests created successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

03.04 - Mysql - MySQL Insert Data

Insert Data Into MySQL Using MySQLi and PDO
After a database and a table have been created, we can start adding data in them.

Here are some syntax rules to follow:

The SQL query must be quoted in PHP
String values inside the SQL query must be quoted
Numeric values must not be quoted
The word NULL must not be quoted
The INSERT INTO statement is used to add new records to a MySQL table:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

To learn more about SQL, please visit our SQL tutorial.

In the previous chapter we created an empty table named "MyGuests" with five columns: "id", "firstname", "lastname", "email" and "reg_date". Now, let us fill the table with data.

Note: If a column is AUTO_INCREMENT (like the "id" column) or TIMESTAMP with default update of current_timesamp (like the "reg_date" column), it is no need to be specified in the SQL query; MySQL will automatically add the value.

The following examples add a new record to the "MyGuests" table:



Example (MySQLi Object-oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>




Example (MySQLi Procedural)

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";

if (mysqli_query($conn, $sql)) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>



Example (PDO)

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

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('John', 'Doe', '[email protected]')";
  // use exec() because no results are returned
  $conn->exec($sql);
  echo "New record created successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

03.05 - Mysql - MySQL Get Last Inserted ID

Get ID of The Last Inserted Record
If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can get the ID of the last inserted/updated record immediately.

In the table "MyGuests", the "id" column is an AUTO_INCREMENT field:

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
The following examples are equal to the examples from the previous page (PHP Insert Data Into MySQL), except that we have added one single line of code to retrieve the ID of the last inserted record. We also echo the last inserted ID:



Example (MySQLi Object-oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";

if ($conn->query($sql) === TRUE) {
  $last_id = $conn->insert_id;
  echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>




Example (MySQLi Procedural)

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";

if (mysqli_query($conn, $sql)) {
  $last_id = mysqli_insert_id($conn);
  echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>



Example (PDO)

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

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('John', 'Doe', '[email protected]')";
  // use exec() because no results are returned
  $conn->exec($sql);
  $last_id = $conn->lastInsertId();
  echo "New record created successfully. Last inserted ID is: " . $last_id;
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

03.06 - Mysql - MySQL Insert Multiple Records

Insert Multiple Records Into MySQL Using MySQLi and PDO
Multiple SQL statements must be executed with the mysqli_multi_query() function.

The following examples add three new records to the "MyGuests" table:



Example (MySQLi Object-oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";

if ($conn->multi_query($sql) === TRUE) {
  echo "New records created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Note that each SQL statement must be separated by a semicolon.




Example (MySQLi Procedural)

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";

if (mysqli_multi_query($conn, $sql)) {
  echo "New records created successfully";
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>



The PDO way is a little bit different:

Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // begin the transaction
  $conn->beginTransaction();
  // our SQL statements
  $conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('John', 'Doe', '[email protected]')");
  $conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('Mary', 'Moe', '[email protected]')");
  $conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('Julie', 'Dooley', '[email protected]')");

  // commit the transaction
  $conn->commit();
  echo "New records created successfully";
} catch(PDOException $e) {
  // roll back the transaction if something failed
  $conn->rollback();
  echo "Error: " . $e->getMessage();
}

$conn = null;
?>

03.07 - Mysql - MySQL Prepared Statements

Prepared statements are very useful against SQL injections.

Prepared Statements and Bound Parameters
A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.

Prepared statements basically work like this:

Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?"). Example: INSERT INTO MyGuests VALUES(?, ?, ?)
The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it
Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values
Compared to executing SQL statements directly, prepared statements have three main advantages:

Prepared statements reduce parsing time as the preparation on the query is done only once (although the statement is executed multiple times)
Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query
Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.
Prepared Statements in MySQLi

The following example uses prepared statements and bound parameters in MySQLi:



Example (MySQLi with Prepared Statements)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "[email protected]";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

Code lines to explain from the example above:

"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"
In our SQL, we insert a question mark (?) where we want to substitute in an integer, string, double or blob value.

Then, have a look at the bind_param() function:

$stmt->bind_param("sss", $firstname, $lastname, $email);
This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types:

i - integer
d - double
s - string
b - BLOB
We must have one of these for each parameter.

By telling mysql what type of data to expect, we minimize the risk of SQL injections.

Note: If we want to insert any data from external sources (like user input), it is very important that the data is sanitized and validated.




Prepared Statements in PDO
The following example uses prepared statements and bound parameters in PDO:

Example (PDO with Prepared Statements)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // prepare sql and bind parameters
  $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)
  VALUES (:firstname, :lastname, :email)");
  $stmt->bindParam(':firstname', $firstname);
  $stmt->bindParam(':lastname', $lastname);
  $stmt->bindParam(':email', $email);

  // insert a row
  $firstname = "John";
  $lastname = "Doe";
  $email = "[email protected]";
  $stmt->execute();

  // insert another row
  $firstname = "Mary";
  $lastname = "Moe";
  $email = "[email protected]";
  $stmt->execute();

  // insert another row
  $firstname = "Julie";
  $lastname = "Dooley";
  $email = "[email protected]";
  $stmt->execute();

  echo "New records created successfully";
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
?>

03.08 - Mysql - MySQL Select Data

Select Data From a MySQL Database
The SELECT statement is used to select data from one or more tables:

SELECT column_name(s) FROM table_name
or we can use the * character to select ALL columns from a table:

SELECT * FROM table_name
To learn more about SQL, please visit our SQL tutorial.

Select Data With MySQLi
The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:



Example (MySQLi Object-oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

Code lines to explain from the example above:

First, we set up an SQL query that selects the id, firstname and lastname columns from the MyGuests table. The next line of code runs the query and puts the resulting data into a variable called $result.

Then, the function num_rows() checks if there are more than zero rows returned.

If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.



The following example shows the same as the example above, in the MySQLi procedural way:

Example (MySQLi Procedural)

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}

mysqli_close($conn);
?>

You can also put the result in an HTML table:

Example (MySQLi Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  echo "<table><tr><th>ID</th><th>Name</th></tr>";
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
  }
  echo "</table>";
} else {
  echo "0 results";
}
$conn->close();
?>



Select Data With PDO (+ Prepared Statements)
The following example uses prepared statements.

It selects the id, firstname and lastname columns from the MyGuests table and displays it in an HTML table:

Example (PDO)
<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";

class TableRows extends RecursiveIteratorIterator {
  function __construct($it) {
    parent::__construct($it, self::LEAVES_ONLY);
  }

  function current() {
    return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
  }

  function beginChildren() {
    echo "<tr>";
  }

  function endChildren() {
    echo "</tr>" . "\n";
  }
}

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests");
  $stmt->execute();

  // set the resulting array to associative
  $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
  foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
    echo $v;
  }
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>

03.09 - Mysql - MySQL Use The WHERE Clause

Select and Filter Data From a MySQL Database
The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

SELECT column_name(s) FROM table_name WHERE column_name operator value 
To learn more about SQL, please visit our SQL tutorial.

Select and Filter Data With MySQLi
The following example selects the id, firstname and lastname columns from the MyGuests table where the lastname is "Doe", and displays it on the page:



Example (MySQLi Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>
Code lines to explain from the example above:

First, we set up the SQL query that selects the id, firstname and lastname columns from the MyGuests table where the lastname is "Doe". The next line of code runs the query and puts the resulting data into a variable called $result.

Then, the function num_rows() checks if there are more than zero rows returned.

If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.




The following example shows the same as the example above, in the MySQLi procedural way:

Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}

mysqli_close($conn);
?>

You can also put the result in an HTML table:

Example (MySQLi Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  echo "<table><tr><th>ID</th><th>Name</th></tr>";
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
  }
  echo "</table>";
} else {
  echo "0 results";
}
$conn->close();
?>




Select Data With PDO (+ Prepared Statements)
The following example uses prepared statements.

It selects the id, firstname and lastname columns from the MyGuests table where the lastname is "Doe", and displays it in an HTML table:

Example (PDO)
<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";

class TableRows extends RecursiveIteratorIterator {
  function __construct($it) {
    parent::__construct($it, self::LEAVES_ONLY);
  }

  function current() {
    return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
  }

  function beginChildren() {
    echo "<tr>";
  }

  function endChildren() {
    echo "</tr>" . "\n";
  }
}

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'");
  $stmt->execute();

  // set the resulting array to associative
  $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
  foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
    echo $v;
  }
}
catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>

03.10 - Mysql - MySQL Use The ORDER BY

Select and Order Data From a MySQL Database
The ORDER BY clause is used to sort the result-set in ascending or descending order.

The ORDER BY clause sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC 
To learn more about SQL, please visit our SQL tutorial.

Select and Order Data With MySQLi
The following example selects the id, firstname and lastname columns from the MyGuests table. The records will be ordered by the lastname column:



Example (MySQLi Object-oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>
Code lines to explain from the example above:

First, we set up the SQL query that selects the id, firstname and lastname columns from the MyGuests table. The records will be ordered by the lastname column. The next line of code runs the query and puts the resulting data into a variable called $result.

Then, the function num_rows() checks if there are more than zero rows returned.

If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.





The following example shows the same as the example above, in the MySQLi procedural way:

Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}

mysqli_close($conn);
?>

You can also put the result in an HTML table:

Example (MySQLi Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  echo "<table><tr><th>ID</th><th>Name</th></tr>";
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
  }
  echo "</table>";
} else {
  echo "0 results";
}
$conn->close();
?>




Select Data With PDO (+ Prepared Statements)
The following example uses prepared statements.

Here we select the id, firstname and lastname columns from the MyGuests table. The records will be ordered by the lastname column, and it will be displayed in an HTML table:

Example (PDO)
<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";

class TableRows extends RecursiveIteratorIterator {
  function __construct($it) {
    parent::__construct($it, self::LEAVES_ONLY);
  }

  function current() {
    return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
  }

  function beginChildren() {
    echo "<tr>";
  }

  function endChildren() {
    echo "</tr>" . "\n";
  }
}

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname");
  $stmt->execute();

  // set the resulting array to associative
  $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
  foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
    echo $v;
  }
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>

03.11 - Mysql - MySQL Delete Data

Delete Data From a MySQL Table Using MySQLi and PDO
The DELETE statement is used to delete records from a table:

DELETE FROM table_name
WHERE some_column = some_value
Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

To learn more about SQL, please visit our SQL tutorial.

Let's look at the "MyGuests" table:

id	firstname	lastname	email	reg_date
1	John	Doe	[email protected]	2014-10-22 14:26:15
2	Mary	Moe	[email protected]	2014-10-23 10:22:30
3	Julie	Dooley	[email protected]	2014-10-26 10:48:23

The following examples delete the record with id=3 in the "MyGuests" table:




Example (MySQLi Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// sql to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>






Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// sql to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";

if (mysqli_query($conn, $sql)) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>





Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // sql to delete a record
  $sql = "DELETE FROM MyGuests WHERE id=3";

  // use exec() because no results are returned
  $conn->exec($sql);
  echo "Record deleted successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

After the record is deleted, the table will look like this:

id	firstname	lastname	email	reg_date
1	John	Doe	[email protected]	2014-10-22 14:26:15
2	Mary	Moe	[email protected]	2014-10-23 10:22:30

03.12 - Mysql - MySQL Update Data

Update Data In a MySQL Table Using MySQLi and PDO
The UPDATE statement is used to update existing records in a table:

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value 
Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

To learn more about SQL, please visit our SQL tutorial.

/*
Let's look at the "MyGuests" table:

id	firstname	lastname	email	reg_date
1	John	Doe	[email protected]	2014-10-22 14:26:15
2	Mary	Moe	[email protected]	2014-10-23 10:22:30

The following examples update the record with id=2 in the "MyGuests" table:

*/



Example (MySQLi Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . $conn->error;
}

$conn->close();
?>






Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if (mysqli_query($conn, $sql)) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>





Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

  // Prepare statement
  $stmt = $conn->prepare($sql);

  // execute the query
  $stmt->execute();

  // echo a message to say the UPDATE succeeded
  echo $stmt->rowCount() . " records UPDATED successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

After the record is updated, the table will look like this:

id	firstname	lastname	email	reg_date
1	John	Doe	[email protected]	2014-10-22 14:26:15
2	Mary	Doe	[email protected]	2014-10-23 10:22:30

03.13 - Mysql - MySQL Limit Data Selections

Limit Data Selections From a MySQL Database
MySQL provides a LIMIT clause that is used to specify the number of records to return.

The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables. Returning a large number of records can impact on performance.

Assume we wish to select all records from 1 - 30 (inclusive) from a table called "Orders". The SQL query would then look like this:

$sql = "SELECT * FROM Orders LIMIT 30";
When the SQL query above is run, it will return the first 30 records.

What if we want to select records 16 - 25 (inclusive)?

Mysql also provides a way to handle this: by using OFFSET.

The SQL query below says "return only 10 records, start on record 16 (OFFSET 15)":

$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";
You could also use a shorter syntax to achieve the same result:

$sql = "SELECT * FROM Orders LIMIT 15, 10";
Notice that the numbers are reversed when you use a comma.

03.14 - Mysql - Tables

Copy Table:

Si deseo copiar tablas puedo emplear varias tecnicas:

- Copiando la Tabla Completa:

CREATE TABLE new_table 
SELECT *  FROM existing_table


- Copiando datos Parciales:

CREATE TABLE new_table 
SELECT *  FROM existing_table 
WHERE conditions

-------------------------------------------------------------------------------------------------------------------------------------

ALTER TABLE:

Este comando permite alterar la estructura de una tabla:

ALTER TABLE table_name action1[,action2,…]

Se pueden definir simultaneamente multiples cambios en la tabla.

- Creando PK

ALTER TABLE tasks
CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;

- Adicionando una columna:

ALTER TABLE tasks 
ADD COLUMN complete DECIMAL(2,1) NULL
AFTER description;

- Eliminando una columna:

ALTER TABLE tasks
DROP COLUMN description;

- Cambiando el nombre de una tabla:

ALTER TABLE tasks
RENAME TO work_items;

-----------------------------------------------------------------------------------------------------------------------------------

TIMESTAMP:

Es un tipo de dato que combina la fecha y la hora incluyendo segundos.

Primero que todo hay que fijar el uso horario en que se esta trabajando:

SET time_zone='+00:00';

INSERT INTO test_timestamp 
VALUES('2008-01-01 00:00:01');

Ahora si cambio el uso horario automaticamente se ajuste el valor almacenado de time_stamp

SET time_zone ='+03:00';
 
SELECT t1
FROM test_timestamp;

Time_Stamp es de gran utilidad para las columnas auto inicializadas y auto actualizadas:

CREATE TABLE ts(
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  created_on TIMESTAMP DEFAULT 0,
  changed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP
);

Cada vez que creo un nuevo registro la columna created_on automaticamente registra el Time_Stamp

Cada vez que actualizo el registro se guarda el Time_Stamp en la columna changed_on

------------------------------------------------------------------------------------------------------

DROP TABLE:

Para eliminar tablas:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] ...
[RESTRICT | CASCADE]

Si uso TEMPORARY solo puedo eliminar tablas temporales

-------------------------------------------------------------------------------------------------------------------------------------

TEMPORARY TABLE:

Puedo crear tablas temporales para almacenar resultados intermedios o para uso dentro de los TRIGGERS Y FUNCTIONS, la tabla temporal existe mientras la sesion del servidor este abierta.

Las tablas temporales solo pueden ser accedidas por el usauario que las creo.

CREATE TEMPORARY TABLE top10customers
SELECT p.customerNumber, 
       c.customerName, 
       FORMAT(SUM(p.amount),2) total
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY total DESC
LIMIT 10

Para eliminar una tabla temporal:

DROP TEMPORARY TABLE top10customers

---------------------------------------------------------------------------------------------------------------------------------------

UNION:

Permite combinar el resultado de dos o mas tablas en una sola tabla. Para ello hay que tener en cuenta:

- El numero de columnas de las tablas de los resultados a combinar debe ser igual
- Las columnas deben tenr el mismo tipo de datos

Por definicion este comando elimina las filas resultantes duplicadas

Cuando se usan alias en el primer select, estos se usan como los labels del conjunto resultante

SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNumber id,firstname name
FROM employees

Se puede asimilar como a un Append.

(SELECT customerNumber id,contactLastname name
FROM customers)
UNION
(SELECT employeeNumber id,firstname name
FROM employees)
ORDER BY name,id

03.15 - Mysql - Indices

Auto Increment:

CREATE TABLE employees(
 emp_no INT(4) AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name  VARCHAR(50)
)ENGINE = INNODB;

- Por definicion la columna escogida como AUTOINCREMENT es la definida como PRIMARY KEY

- Con LAST_INSERT_ID() se obtiene el numero del ultimo registro realizado

http://www.mysqltutorial.org/mysql-sequence/

-------------------------------------------------------------------------------------------------------------------------------------

Primary Key:

- La(s) columna(s) que compongan la definicion de PRIMARY KEY no pueden contener NULL, por lo cual debe(n) declararse como NOT NULL

- Las tablas trabajan mas rapido con enteros, por lo cual lo ideal es definir PK con datos tipo INT or BIGINT

CREATE TABLE users(
   user_id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40),
   password VARCHAR(255),
   email VARCHAR(255)
);

Tambien se puede hacer la declaracion al final de la tabla:

CREATE TABLE roles(
   role_id INT AUTO_INCREMENT,
   role_name VARCHAR(50),
   PRIMARY KEY(role_id)
);

Si la definicion de PK contiene multiples columnas, la declaracion se hace al final:

CREATE TABLE userroles(
   user_id INT NOT NULL,
   role_id INT NOT NULL,
   PRIMARY KEY(user_id,role_id),
   FOREIGN KEY(user_id) REFERENCES users(user_id),
   FOREIGN KEY(role_id) REFERENCES roles(role_id)
);

En caso de tener que definir un PK sobre una tabla que ya existe y a la cual no se le definio originalmente el PK:

ALTER TABLE table_name
ADD PRIMARY KEY(primary_key_column);

------------------------------------------------------------------------------------------------------------------

Unique Key:

- Cuando deseo añadir un indice de tipo unico a una columna uso:

ALTER TABLE users
ADD UNIQUE INDEX username_unique (username ASC) ;

ALTER TABLE users
ADD UNIQUE INDEX  email_unique (email ASC) ;

- La columna definido como un indice unico permite valores nulos
- Se pueden definir multiples indices unicos en una tabla

-------------------------------------------------------------------------------------------------------------------------------------

Foreign Key:

Las FK se usan para establecer las relaciones de las tablas. 

- En una relacion One to Many existe una Relacion Padre-Hijo

- La tabla One o Padre es la tabla referenced

- La tabla Many o Hija es la tabla referencing

En una tabla puede haber multiples FK

Una tabla puede refernciarse asi misma, como el caso de una tabla de empleados en donde se define quien le reporta a quien. Este tipo de relaciones se denominan recursivas o Self Referencing.

Cuando hay FK se pueden usar cascade para borrar o actualizar. Es decir que si se borra el padre se borran todos los registros en las tablas hijas, en el caso de actualizar el padre se actualizan todos los registros de las tablas hijas.

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

El concepto de action tanto para ON DELETE como para ON UPDATE hace referencia a las acciones que se tomaran cuando se borren o se actualicen los datos de la tabla padre:

CASCADE: si se borra el registro en la tabla padre se borran todos los registros relacionados en las tablas hijas.

SET NULL: si se borra el registro en la tabla padre todos los registros relacionados en las tablas hijas se colocan como null.

RESTRICT: no se permitira la operacion de borrado de registros en la tabla padre

--------------------------------------------------------------------------------------------------------------------------------------

Index:

Los indices son bastante utiles para las columnas que frecuentemente son usadas en los querys ya que definen el lugar fisico en donde se almacenaron los registros.

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
USING [BTREE | HASH | RTREE] 
ON table_name (column_name [(length)] [ASC | DESC],...)

CREATE INDEX officeCode ON employees(officeCode)

Eliminando Indices:

DROP INDEX index_name ON table_name

DROP INDEX officeCode ON employees

03.16 - Mysql - DataBase

Create Database:

CREATE DATABASE [IF NOT EXISTS] database_name;

CREATE DATABASE classicmodels;

CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;

CREATE TABLE [IF NOT EXISTS] table_name(
        column_list
        ) engine=table_type
        
CREATE TABLE IF NOT EXISTS tasks (
  task_id int(11) NOT NULL AUTO_INCREMENT,
  subject varchar(45) DEFAULT NULL,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  description varchar(200) DEFAULT NULL,
  PRIMARY KEY (task_id)
) ENGINE=InnoDB

Cada vez que se crea una tabla se puede especificar el motor en que se desea utilizarla. De hecho en una misma base de datos podemos tener multiples tablas usando diferentes motores por tabla.

------------------------------------------------------------------------------------------------

Drop Database:

DROP DATABASE [IF EXISTS] database_name;

CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;

------------------------------------------------------------------------------------------------------------------------------------

Use Database:

USE database_name;

USE classicmodels;

03.17 - Mysql - Join

INNER JOIN:

Se usa con el fin de encontrar filas que coincidan en dos tablas para realizar el query sobre la(s) fila(s) encontrada(s)

En operaciones de conjuntos equivale a la interseccion de dos conjuntos (A ∩ B)

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;


SELECT productCode,
       productName,
       textDescription
FROM products T1
INNER JOIN productlines T2 ON T1.productline = T2.productline;


SELECT T1.orderNumber,
       status,
       SUM(quantityOrdered * priceEach) total
FROM orders AS T1
INNER JOIN orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber


En otras palabras el INNER JOIN es acerca de los registros en la tabla A que tienen registros correspondientes en la tabla B

----------------------------------------------------------------------------------------------------------------------

LEFT JOIN:

SELECT T1.c1, T1.c2,... T2.c1,T2.c2
FROM T1
LEFT JOIN T2 ON T1.c1 = T2.c1...

En operaciones de conjuntos equivale a la diferencia de dos conjuntos: A \ B que contiene todos los elementos de A que no pertenecen a B.

SELECT c.customerNumber,
       c.customerName,
       orderNumber, 
       o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber

Puede ser muy util para encontrar filas del conjunto de la izquierda que no hacen match con filas del conjunto de la derecha.

SELECT c.customerNumber,
       c.customerName,
       orderNumber, 
       o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE orderNumber IS NULL


En otras palabras el LEFT JOIN es acerca de los registros en la tabla A que NO tienen registros correspondientes en la tabla B

--------------------------------------------------------------------------------------------------------------------

SELF JOIN:

Esta operacion se realiza a traves de un join de la tabla consigo misma. Es decir que deseo compbinar registros de la tabla con otros registros de la tabla misma.

SELECT CONCAT(m.lastname,', ',m.firstname) AS 'Manager',
       CONCAT(e.lastname,', ',e.firstname) AS 'Direct report' 
FROM employees e
INNER JOIN employees m ON m.employeeNumber = e.reportsto
ORDER BY manager


SELECT c1.city,
       c1.customerName,
       c2.customerName
FROM customers c1
INNER JOIN customers c2 
ON c1.city = c2.city AND
   c1.customername <> c2.customerName 
ORDER BY c1.city

03.18 - Mysql - Operaciones

Prepare Statement:

El uso de Prepared statement esta fundamentado en:

- Incrementar la velocidad de los Querys ya que no hay necesidad de pasar siempre que se requiera el query el texto completo, solo los parametros del mismo.

- Ayuda a evitar muchas variantes de SQL injection haciendo la aplicacion mas segura.

SELECT * 
FROM products 
WHERE productCode = ?


PREPARE stmt1 FROM 'SELECT productCode, productName
                    FROM products
                    WHERE productCode = ?';
 
SET @pc = 'S10_1678';
EXECUTE stmt1 USING @pc;
 
DEALLOCATE PREPARE stmt1;

------------------------------------------------------------------------------------------------------------------

INSERT:

Para insertar datos dentro de una tabla:

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...)

INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('Learn MySQL INSERT','2010-01-01','2010-01-02','Start learning..')


Insertando multiples registros:

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...),
       (value1,value2,...),
...


INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('Task 1','2010-01-01','2010-01-02','Description 1'),
       ('Task 2','2010-01-01','2010-01-02','Description 2'),
       ('Task 3','2010-01-01','2010-01-02','Description 3');
       

Si voy a insertar en estricto orden los datos, no es necesario especificar el nombre de las columnas:

INSERT INTO table
VALUES (value1,value2,...)

INSERT INTO table
VALUES (value1,value2,...),
       (value1,value2,...),
…

--------------------------------------------------------------------------------------------------------------

UPDATE:

Con Update se pueden actualizar los valores de un registro o de multiples registros en una tabla, o incluso en varias tablas de la base de datos.

UPDATE [LOW_ PRIORITY] [IGNORE] table_name [, table_name...] 
 SET column_name1 = expr1 
     [, column_name2=expr2 ...] 
[WHERE condition]

Los valores a actualizar pueden ser valores en si, expresiones o incluso subquerys.

Si no se especifica un filtro con WHERE entonces se actualiza la totalidad de la tabla.

Si se usa LOW_PRIORITY la actualizacion solo se realiza cuando no se estan desarrollando consultas sobre la tabla.


UPDATE employees 
SET email = '[email protected]'  
WHERE employeeNumber = 1056


UPDATE employees
SET lastname = 'Hill',
    email = '[email protected]'
WHERE employeeNumber = 1056;


UPDATE customers
SET salesRepEmployeeNumber = 
 (
   SELECT employeeNumber 
   FROM employees
   WHERE jobtitle = 'Sales Rep'
   LIMIT 1
 )
WHERE salesRepEmployeeNumber IS NULL;

---------------------------------------------------------------------------------------------------------------

DELETE:

Para remover registros de una tabla o de multiples tablas usamos DELETE:

DELETE FROM table   
[WHERE conditions] [ORDER BY ...] [LIMIT rows]


DELETE FROM employees 
WHERE officeCode = 4


Para borrar todos los registros de una tabla:

DELETE FROM employees


Para borrar registros de multiples tablas:

DELETE table_1, table_2,...
FROM table-refs   
[WHERE conditions]
 
DELETE FROM table_1, table_2,...
USING table-refs
[WHERE conditions]


Para borrar todos los registros de la tabla de oficinas de una oficina que ha cerrado y adicionalmente borrar del maestro de oficinas esa en particular:

DELETE employees, 
    offices 
FROM employees, 
 offices 
WHERE employees.officeCode = offices.officeCode  AND 
      offices.officeCode = 1

----------------------------------------------------------------------------------------------------------------------

DELETE:

Para remover registros de una tabla o de multiples tablas usamos DELETE:

DELETE FROM table   
[WHERE conditions] [ORDER BY ...] [LIMIT rows]


DELETE FROM employees 
WHERE officeCode = 4


Para borrar todos los registros de una tabla:

DELETE FROM employees


Para borrar registros de multiples tablas:

DELETE table_1, table_2,...
FROM table-refs   
[WHERE conditions]
 
DELETE FROM table_1, table_2,...
USING table-refs
[WHERE conditions]


Para borrar todos los registros de la tabla de oficinas de una oficina que ha cerrado y adicionalmente borrar del maestro de oficinas esa en particular:

DELETE employees, 
    offices 
FROM employees, 
 offices 
WHERE employees.officeCode = offices.officeCode  AND 
      offices.officeCode = 1

----------------------------------------------------------------------------------------------------------------------

TRUNCATE :

TRUNCATE permite borrar todos los registros de una tabla, es como un DELETE sin WHERE.


TRUNCATE TABLE table_name


Si estamos usando en motor InnoDB TRUNCATE revisa si existen restricciones del tipo DELETE CASCADE caso en el cual TRUNCATE borra automaticament los registros correspondientes a las tablas hijas.

TRUNCATE es muy util sobre todo cuando se trata de tablas muy grandes ya que lo hace mas rapido que con DELETE
REPLACE:

REPLACE trabaja de forma similar a INSERT solo que con algunas variaciones:

- Si el registro que trato con REPLACE no existe simplemente se inserta el nuevo registro en la tabla
- Si el registro que trato con REPLACE existe entonces se borra el registro anterio y luego se crea el nuevo registro que lo sustituye.

REPLACE INTO table_name(column_name1,column_name2,…)
VALUES(value1,value2,…)


Insertando un nuevo registro:

REPLACE INTO offices(officecode,city)
VALUES(8,'San Jose')

Actualizando el registro:

REPLACE INTO offices(officecode,city)
VALUES(8,'San Mateo')


Actuando como un Update:

REPLACE INTO table_name
SET column_name1 = value1 AND
    column2 = value2
    
REPLACE INTO offices
SET officecode = 8 and
    city = 'Santa Cruz’

---------------------------------------------------------------------------------------------------------------------

TRANSACTIONS:

Normalmente en las bases de datos se requieren realizar de manera exitosa conjuntos de operaciones, que si fallan parcialmente en realidad fallan en conjunto.

Este conjunto de operaciones que requieren total integridad se denominan Transacciones, si alguna de las operaciones incluidas en la transaccion falla, todas las operaciones realizadas exitosamente dentro de la transaccion son reversadas para no obtener resultados parciales es decir hace ROLL BACK, solo se registran los resultados de las operaciones definidas en la transaccion si todas ellas fueron exitosas, es decir si hubo exito en la transaccion se hace COMMIT a la base de datos.

Como Mysql automaticamente hace COMMIT de las operaciones a la base de datos se requiere primero que todo cambiar el status del COMMIT a manual:

SET autocommit = 0;

03.19 - Mysql - Querys

Select:

La sintaxis general del comando de seleccion es:

SELECT column_1,column_2...
FROM table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE conditions
GROUP BY group 
HAVING group_conditions
ORDER BY column_1 [ASC | DESC]
LIMIT offset, row_count


- Para seleccionar todas las columnas de una tabla:

SELECT * FROM employees


- Para seleccionar columnas especificas:

SELECT lastname,
       firstname,
       jobtitle
FROM employees


- Para filtrar la seleccion:

SELECT customerName, city
FROM customers
WHERE country = 'USA';

SELECT customerName, city
FROM customers
WHERE country = 'USA' AND 
      city    = 'NYC';
      
SELECT customerName, creditlimit
FROM customers
WHERE creditlimit > 200000;

--------------------------------------------------------------------------------------------

Subquery:

Un subquery es un query que se realiza sobre otro query. Tambien se le conoce como inner query sobre el cual se realiza otro query denominado outer query.

Determinando clientes con los maximos pagos:

SELECT customerNumber,
       checkNumber,
       amount
FROM payments
WHERE amount = (
 SELECT MAX(amount) 
        FROM payments
)


Determinando pagos mayores al promedio:

SELECT customerNumber,
       checkNumber,
    amount
FROM payments
WHERE amount > (
 SELECT AVG(amount) 
    FROM payments
)

03.20 - Mysql - Tricks & Tips 1

Presentacion de NULLS:

En caso de querer reemplazar el NULL para efectos de presentacion:

SELECT customername, 
       IF(state IS NULL,"N/A",state) state, 
       country
FROM customers
ORDER BY country


O puedo usar directamente:

IFNULL(exp,exp_result)


SELECT customername, 
       IFNULL(state,"N/A") state, 
       country
FROM customers
ORDER BY country

----------------------------------------------------------------------------------------

Comparacion de Tablas:

Si deseo saber si dos tablas son identicas:

- Primero creo una sola tabla a partir de las dos tablas a comparar:

- Las agrupo por PK y columna a comparar

- Cuento las ocurrencias


SELECT pk, c1
FROM
 (
   SELECT t1.pk, t1.c1
   FROM t1
   UNION ALL
   SELECT t2.pk, t2.c1
   FROM t2
)  t
GROUP BY pk, c1
HAVING COUNT(*) = 1
ORDER BY pk

Si las dos tablas son iguales no obtengo ningun valor, si son diferentes obtengo el numero de registros diferentes entre las dos tablas:

SELECT id,title
FROM (
SELECT id, title FROM t1
UNION ALL
SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;

-----------------------------------------------------------------------------------

Auto Increment Reset:

Para que la secuencia autoincremental pueda ser reinicializada:

ALTER TABLE table_name AUTO_INCREMENT = value;

El parametro value debe ser igual o mayor al actual valor de la secuencia de Auto Increment.

Puede ser muy util por ejemplo para la numeracion automatica de la facturacion:

1. Creo un autoincremental
2. Le hago reset a la nueva numeracion
3. Borro el primer registro con la numeracion anterior

----------------------------------------------------------------------------------

N-esimo Elemento:

Para obtener el n-esimo elemento de la tabla:

SELECT * 
FROM table_name
ORDER BY column_name ASC
LIMIT n


SELECT *
FROM table_name
ORDER BY column_name DESC
LIMIT n - 1, 1


Por ejemplo si quiero saber cual es el segundo producto mas caro:

SELECT productCode, productName, buyPrice
FROM products 
ORDER BY buyPrice desc 
LIMIT 1, 1

---------------------------------------------------------------------------------

Regular Expression:

Las Expresiones Regulares se usan para reconocer patrones:

SELECT column_list 
FROM table_name 
WHERE column REGEXP pattern

Por ejemplo si quiero seleccionar los empleados cuyo apellido empiece por B,T o M:

SELECT lastname, firstname 
FROM employees
WHERE lastname REGEXP  '^(M|B|T)’;

03.21 - Mysql - Tricks & Tips 2

Random Records:

Si quiero seleccionar registros aleatoreamente:

SELECT * FROM table ORDER BY RAND()  LIMIT 1


Si quiero 5 registros aleatorios:

SELECT productName, productLine 
FROM products
ORDER BY RAND()
LIMIT 5;

-----------------------------------------------------------------------------------------------------------------------------------

Row Difference:

Si quiero calcular la diferencia entre filas:

SELECT 
    g1.item_no,
    g1.c_date from_date,
    g2.c_date to_date,
    (g2.qty - g1.qty) AS receipt_qty
FROM
    grs g1
        INNER JOIN
    grs g2 ON g2.id = g1.id + 1
WHERE
    g1.item_no = ‘A'
    
-------------------------------------------------------------------------------------------------------------------------------------

Export:

Para exportar un archivo en formato CSV:

En el caso de exportar el resultado de un Query:

SELECT orderNumber, status, orderDate, requiredDate, comments 
FROM orders
WHERE status = 'Cancelled'
INTO OUTFILE 'C:/tmp/cancelled_orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Si voy a exportar datos que contienen Time_Stamp:

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
 
SET @FOLDER = 'c:/tmp/';
SET @PREFIX = 'orders';
SET @EXT    = '.csv';
 
SET @CMD = CONCAT("SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
    "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
    "  LINES TERMINATED BY '\r\n';");
 
PREPARE statement FROM @CMD;
 
EXECUTE statement;

Si voy a exportar datos con encabezados:

(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n’);

----------------------------------------------------------------------------------------------------------------------------------

LOAD DATA:

Usar LOAD DATA INFILE puede cargar muy rapidamente en una tabla dato contenidos en un archivo tipo CSV.

Si en la primera fila del CSV estan los nombres de las variables, la sintaxis de importacion seria:

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Adicionalmente puedo transformar los datos durante la importacion, asi por ejemplo si dentro de los datos que voy a importar existe una fecha en la forma M-D-Y, requiero transformar ese dato al formato de fecha de mySql:

LOAD DATA INFILE 'c:/tmp/discounts_2.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title,@expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');

Para importar los datos a un servidor remoto simplemente agregamos la opcion LOCAL:

LOAD DATA LOCAL INFILE  'c:/tmp/discounts.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Tambien es posiple hacer la importacion desde phpMyAdmin y desde mySql WorkBench

03.22 - Mysql - Stored Procedures

Concepto:

Un procedimiento almacenado es una secuencia de codigo que puede ser invocada por los TRIGGERS, otros procedimientos almacenados o JAVA, PHP, etc.

Los procedimientos almacenados funcionan muy bien cuando se invocan varias veces durante la sesion del servidor, si solo se invocan una vez funcionan como cualquier query.

Estos procedimientos ayudan a disminuir el trafico con el servidor, ya que solo se envia el nombre del procedimiento y no todo el codigo que lo compone.

Los procedimientos almacenados pueden ser usados por cualquier aplicacion que los invoque, reduciendo tiempo de programacion.

Son bastante seguros.

- No obstante:

Si se usan demasiados procedimientos almacenados se puede sobre cargar al servidor.

Pueden ser dificiles de desarrollar si la logica de negocio es compleja.

Es dificil hacerles debug

Son dificiles de mantener y desarrollar

Pueden ser creados por codigo o con mySql WorkBench

La estructura es:

DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
 DELIMITER ;
 

Se llaman mediante:

CALL STORED_PROCEDURE_NAME()
Variables:

Todas las definiciones realizadas dentro de los procedimientos almacenados son de caracter local. Se declaran de la siguiente manera:

DECLARE variable_name datatype(size) DEFAULT default_value;

DECLARE total_sale INT DEFAULT 0

DECLARE x, y INT DEFAULT 0

Asignandole valores a las variables:

DECLARE total_count INT DEFAULT 0
SET total_count = 10;

Asignandole el resultado de un query a una variable:

DECLARE total_products INT DEFAULT 0
 
SELECT COUNT(*) INTO total_products
FROM products

Todas las variables existen hasta llegar al final del procecimeinto almacenado. Si se preceden con (@) entonces las variables comienzan a regir en el inicio de la cesion y mueren el final de la cesion del usuario.
Parametros:

Los parametros de los procecimientos almacenados pueden ser de tres tipos:

IN: es el modo empleado por default y se caracteriza porque todo procecimiento es llamado con un parametro que esta protegido, es decir aunque al interior del procedimiento almacenado se le cambie el valor, este continua con su valor original. En otras palabras bajo este modo el procedimiento almacenado trabaja en realidad sobre una copia del parametro enviado.

OUT: en este modo el parametro puede ser cambiado al interior del procedimiento almacenado y enviarlo hacia afuera del mismo con su nuevo valor. Obviamente el procedimiento almacenado no puede accesar el valor del parametro out al inicio del mismo.

INOUT: este modo es una combinacion de los modos anteriores por lo cual se pueden recibir paramtros externos, modificarlos al interior del procedimiento almacenado y enviar los nuevos valores al exterior.

Se definen de la siguiente manera:

MODE param_name param_type(param_size)

- Parametro tipo IN:

DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
 BEGIN
 SELECT * 
 FROM offices
 WHERE country = countryName;
 END //
DELIMITER ;


- Paramtros IN y OUT:

DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),
 OUT total INT)
BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
END$$
DELIMITER ;
Multiple Respuesta:

Las funciones almacenadas solo devuelven un valor, mientras que los procedimientos almacenados pueden devolver multiples valores, obviamente para ello hay que haber definido los parametros como OUT o como INOUT.

DELIMITER $$
 
CREATE PROCEDURE get_order_by_cust(IN cust_no INT,
 OUT shipped INT,
 OUT canceled INT,
 OUT resolved INT,
 OUT disputed INT)
BEGIN
 -- shipped
 SELECT
            count(*) INTO shipped
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Shipped';
 
 -- canceled
 SELECT
            count(*) INTO canceled
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Canceled';
 
 -- resolved
 SELECT
            count(*) INTO resolved
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Resolved';
 
 -- disputed
 SELECT
            count(*) INTO disputed
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Disputed';
 
END
IF:

La sintaxis del condicional es:

IF if_expression THEN commands
   [ELSEIF elseif_expression THEN commands]
   [ELSE commands]
   END IF;

DELIMITER $$
 
CREATE PROCEDURE GetCustomerLevel(
    in  p_customerNumber int(11), 
    out p_customerLevel  varchar(10))
BEGIN
    DECLARE creditlim double;
 
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
 
    IF creditlim > 50000 THEN
 SET p_customerLevel = 'PLATINUM';
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = 'SILVER';
    END IF;
 
END$$
CASE:

La sintaxis de este condicional es:

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE

DELIMITER $$
 
CREATE PROCEDURE GetCustomerShipping(
 in  p_customerNumber int(11), 
 out p_shiping        varchar(50))
BEGIN
    DECLARE customerCountry varchar(50);
 
    SELECT country INTO customerCountry
 FROM customers
 WHERE customerNumber = p_customerNumber;
 
    CASE customerCountry
 WHEN  'USA' THEN
    SET p_shiping = '2-day Shipping';
 WHEN 'Canada' THEN
    SET p_shiping = '3-day Shipping';
 ELSE
    SET p_shiping = '5-day Shipping';
 END CASE;
 
END$$

- Para Invocar el procedimiento:

SET @customerNo = 112;
 
SELECT country into @country
FROM customers
WHERE customernumber = @customerNo;
 
CALL GetCustomerShipping(@customerNo,@shipping);
 
SELECT @customerNo AS Customer,
       @country    AS Country,
       @shipping   AS Shipping;
LOOP:

Loop tipo WHILE:

WHILE expression DO
   Statements
END WHILE

DELIMITER $$
 DROP PROCEDURE IF EXISTS WhileLoopProc$$
 CREATE PROCEDURE WhileLoopProc()
       BEGIN
               DECLARE x  INT;
               DECLARE str  VARCHAR(255);
               SET x = 1;
               SET str =  '';
               WHILE x  <= 5 DO
                           SET  str = CONCAT(str,x,',');
                           SET  x = x + 1; 
               END WHILE;
               SELECT str;
       END$$
   DELIMITER ;
Lista de Procedimientos:

Para determinar los procedimientos almacenados que existen en la base de datos:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];

SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS WHERE db = 'classicmodels';

SHOW PROCEDURE STATUS WHERE name LIKE '%product%'

- Para visualizar el codigo de un procedimiento almacenado:

SHOW CREATE PROCEDURE stored_procedure_name

SHOW CREATE PROCEDURE GetAllProducts

03.23 - Mysql - Stored Functions

Una funcion es un codigo encapsulado que devuelve un unico resultado.

CREATE FUNCTION function_name(param1,param2,…)
    RETURNS datatype
   [NOT] DETERMINISTIC
 statements
 
DELIMITER $$
 
CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
    DETERMINISTIC
BEGIN
    DECLARE lvl varchar(10);
 
    IF p_creditLimit > 50000 THEN
 SET lvl = 'PLATINUM';
    ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
        SET lvl = 'GOLD';
    ELSEIF p_creditLimit < 10000 THEN
        SET lvl = 'SILVER';
    END IF;
 
 RETURN (lvl);
END

03.24 - Mysql - Triggers

Tareas Programadas:

Las tareas programadas son especialmente utiles para:

- Optimizar las tablas de la base de datos
- Limpiar los Logs
- Archivar Datos
- Generar Reportes Complejos en horas valle del servidor

Para ver el status de ejecucion de las tareas programadas:

SHOW PROCESSLIST;

Para activar la ejecucion de tareas programadas:

SET GLOBAL event_scheduler = ON;

Para desactivar la ejecucion de tareas programadas:

SET GLOBAL event_scheduler = OFF;

Estructura de Creacion:

CREATE EVENT [IF NOT EXIST]  event_name
ON SCHEDULE schedule
DO
event_body

CREATE EVENT IF NOT EXISTS test_event_01
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
  INSERT INTO messages(message,created_at)
  VALUES('Test MySQL Event 1',NOW());
Creacion:

Estructura de Creacion:

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END 

DELIMITER $$
CREATE TRIGGER before_employee_update 
    BEFORE UPDATE ON employees
    FOR EACH ROW BEGIN
 
    INSERT INTO employees_audit
    SET action = 'update',
     employeeNumber = OLD.employeeNumber,
        lastname = OLD.lastname,
        changedon = NOW(); 
END$$
DELIMITER ; 
Implementacion:

En mySql se pueden usar hasta un maximo de 6 Triggers por tabla:

BEFORE INSERT

AFTER INSERT

BEFORE UPDATE

AFTER UPDATE

BEFORE DELETE

AFTER DELETE

- Son del Formato:

(BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)

- Los Triggers se almacenan en /data/classicmodels/ en dos archivos:

tablename.TRG : 	en dode se referencian los Triggers para cada tabla
triggername.TRN:	en donde se almacena la definicion de codigo del Trigger

En general los Triggers pueden hacer cualquier cosa en SQL, no obstante no pueden usar:

SHOW
LOAD DATA
LOAD TABLE
BACKUP DATABASE
RESTORE
FLUSH
RETURN
COMMIT
ROLLBACK
START TRANSACTION
LOCK/UNLOCK TABLES
ALTER
CREATE
DROP
RENAME


Concepto:

Un Trigger es un conjunto de sentencias SQL que se ejecutan cuando sucede un evento asociado con una tabla como INSERT UPDATE DELETE

En otras palabras es un tipo especial de procedimiento almacenado, solo que no puede ser llamado de la manera tradicional, ya que se llama automaticamente cuando ocurre el evento asociado.

Son una alternativa para chequear la integridad de la base.

Sirven para hacer error catch

Son una alternativa para correr tareas programadas

Son muy utiles para auditar cambios en las tablas de la base

- No obstante:

Pueden ejecutar algunas validaciones pero no todas

Como se llaman automaticamente y son invisibles, no se puede saber si se estan ejecutando o que estan haciendo.

Pueden incrementar la carga sobre el servidor

03.25 - Mysql - Vistas

Management:

Para ver la definicion de la vista:

SHOW CREATE VIEW [database_name].[view_ name];

Tambien puede consultarse en: \data\classicmodels\organization.frm

Modificando las Vistas:

ALTER
 [ALGORITHM =  {MERGE | TEMPTABLE | UNDEFINED}]
  VIEW [database_name].  [view_name]
   AS 
 [SELECT  statement]
 
 ALTER VIEW organization
  AS 
  SELECT CONCAT(E.lastname,E.firstname) AS Employee,
         E.email AS  employeeEmail,
         CONCAT(M.lastname,M.firstname) AS Manager
  FROM employees AS E
  INNER JOIN employees AS M
    ON M.employeeNumber = E.ReportsTo
  ORDER BY Manager
  
Para eliminar las Vistas:

DROP VIEW [IF EXISTS] [database_name].[view_name]

DROP VIEW IF EXISTS organization
Creacion Actualizable:

Para crear vistas actualizables hay que tener en cuenta:

- El SELECT solo se puede referir a una sola tabla en la base de datos
- El SELECT no puede usar HAVING ni GROUP BY
- El SELECT no puede usar DISTINCT
- EL SELECT no puede referirse a otras vistas
- El SELECT no puede contener funciones

Creando la vista:

CREATE VIEW officeInfo
 AS 
   SELECT officeCode, phone, city
   FROM offices

Consultando la vista:

SELECT * FROM officeInfo

Actualizando la vista:

UPDATE officeInfo
SET phone = '+33 14 723 5555'
WHERE officeCode = 4
Creacion:

La estructura de creacion de las vistas es:

 CREATE 
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
 VIEW [database_name].[view_name] 
 AS
 [SELECT  statement]
 
 - Vista que representa las ventas totales por orden:
 
 CREATE VIEW SalePerOrder
   AS 
  SELECT orderNumber,
  SUM  (quantityOrdered * priceEach) total
  FROM orderDetails
  GROUP by orderNumber
  ORDER BY total DESC
  
Ahora cada vez que desee saber el total de ventas de una orden en particular, hago un query sobre la vista:

SELECT total 
FROM salePerOrder
WHERE orderNumber = 10102

- Creando una vista con INNER JOIN:

CREATE VIEW customerOrders AS
 SELECT  D.orderNumber,
         customerName,
         SUM(quantityOrdered * priceEach) total
 FROM orderDetails D
 INNER JOIN orders O ON O.orderNumber = D.orderNumber
 INNER JOIN customers C ON O.customerNumber =  C.customerNumber  
 GROUP BY D.orderNumber 
 ORDER BY total DESC

Concepto:

Una vista es una tabla virutal creada a traves de SELECT y JOIN, como la vista tambien tiene la estructura de una tabla puedo ejecutar querys sobre la vista.

Las vistas son dinamicas, de manera que cuando cambian los datos de las tablas reales que la conforman se actualizan las vistas que los usen.

Las vistas permiten simplificar el trabajo con querys complejos ya que los usuarios simplemente tienen que llamar la vista y no modificar, usar o mantener el query complejo de la vista

Limita el acceso a los datos, pues los usuarios solo pueden hacer querys sobre las vistas y no sobre los demas datos.

Mejoran la seguridad con la posibilidad de construir vistas tipo read only

Las vistas permiten contar con columnas calculadas, en las tablas reales es imposible contar con columnas calculadas

Permite limitar el impacto de cambios en el diseño de la base de datos, ya que ante un rediseño simplemente se altera la estructura de las vistas y los usuarios las siguen llamando como siempre, siendo transparente el rediseño para los usuarios.

-  No Obstante:

El uso de vistas puede hacer lento el desempeño de la base de datos, en especial si la vista usa otras vistas.

Si se cambia la estructura de relaciones de las tablas fisicas, estos cambios deben ser actualizados en la vista.