mysql

MySQL Quick Reference – Basic

Tested on MySQL 5.5

Syntax

1. Open phpMyAdmin and select a database
2. TOP MENU> localhost> lucedigi_test
3. TOP LABELS> SQL
4. BOTTOM MENU ‘SELECT*’ button

Input:

SELECT * FROM `Persons`;

– SQL ARE NOT CASE SENSITIVE
– USE SEMICOLON ‘;’ to to separate each SQL statement
– `Persons` is not ‘Persons’ -> ` is dfifferent of ‘

FROM MySQL to PHP MYSQL

1. Open phpMyAdmin and select a database
2. TOP MENU> localhost> lucedigi_test
3. TOP LABELS> SQL
4. Input your code
5. BOTTOM RIGHT ‘Esegui’ Button
6. In the next window on the right [Crea codice PHP]

SELECT

Table name: Persons
Columns: PID, First Name, Last Name, Age.

/* Comments */ 

/* Select All */ 
SELECT * FROM `Persons`;

/* Select only specified columns */ 
SELECT `PID`, `FirstName`, `LastName`, `Age` FROM `Persons`;

/* In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. */ 
SELECT DISTINCT `FirstName`, `LastName`
FROM Persons;

SELECT … WHERE

/* The WHERE clause is used to extract only those records that fulfill a specified criterion. */ 
/* Operators = <> > < >= <= BETWEEN LIKE IN */ 
SELECT * FROM Persons
WHERE FirstName='Erica';

/* Operators for multiple values START */ 
/* ################################### */

SELECT * FROM Persons
WHERE FirstName='Erica'
AND LastName='Tonin';

SELECT * FROM Persons
WHERE FirstName='Erica'
OR LastName='Tonin';

SELECT * FROM Persons
WHERE FirstName='Erica'
AND (LastName='Tonin' OR LastName='Santato');

SELECT * FROM Customers
WHERE City IN ('Paris','London');

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

/* ################################### */
/* Operators for multiple values END */ 

ORDER

/* The ORDER BY keyword is used to sort the result-set */ 
/* Default is ASC - ascending */ 
SELECT * FROM Persons
ORDER BY LastName; 

/* DESC - descending */
SELECT * FROM Persons
ORDER BY LastName DESC;

/* In ordine avremo Erica Santato poi Erica Tonin - a parità di FirstName si classifica con il LastName */
SELECT * FROM Persons
ORDER BY FirstName,LastName;

INSERT – UPDATE

/* Insert New Records in a table */ 
INSERT INTO Persons (FirstName, LastName)
VALUES ('Ivan', 'Danko');

/* Update records in a table */ 
/* ATTENZIONE!!!!  Se si omette WHERE tutti i record saranno sovrascritti! */ 
UPDATE Persons
SET FirstName='Sylvester', LastName='Stallone'
WHERE FirstName='Ivan';

DELETE

/* Delete records */ 
/* ATTENZIONE!!!!  Se si omette WHERE tutti i record saranno cancellati! */ 
DELETE FROM Persons
WHERE FirstName='Sylvester' AND LastName='Stallone';

/* Delete ALL DATA */
DELETE FROM Persons;

LIMIT

/* LIMIT Specifies the number of records to return */
SELECT LastName
FROM Persons
LIMIT 3;

Wildcards

A wildcard character can be used to substitute for any other character(s) in a string.

/* LIKE searches for a specified pattern in a column */
/* The Result is Tonin - Zonin - Ponin - Conin etc... */
SELECT * FROM Persons
WHERE LastName LIKE '%oni%';

/* The Result is Tonin - Tonib - Toniv - Tonis etc... */
SELECT * FROM Persons
WHERE LastName LIKE 'Toni%';

/* The following SQL statement selects all customers with a City 
starting with any character, followed by "erlin": */
SELECT * FROM Customers
WHERE City LIKE '_erlin';

/* The following SQL statement selects all customers with a City starting with "L", 
followed by any character, followed by "n", followed by any character, followed by "on" */
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

/*The following SQL statement selects all customers with a City starting with "b", "s", or "p":*/
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

/*The following SQL statement selects all customers with a City starting with "a", "b", or "c":*/
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

/*The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p":*/
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

INNER JOINT

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Il render sarà:

1.Tabella Orders -> OrderID
2.Tabella Customers -> CustomerName -> al verificarsi della condizione -> Orders.CustomerID=Customers.CustomerID
3.Tabella -> OrderDate

UNION

/* UNION operator combines the result of two or more SELECT statements 
(only distinct values) */
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

/* UNION ALL operator combines the result of two or more SELECT statements 
(duplicate values also) */
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

/* With WHERE */
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SELECT INTO – INSERT INTO (create a copy)

The SELECT INTO statement copies data from one table and inserts it into a new table.

MySQL does not support SELECT INTO statement!!!

You can fix the problem:

/* Create a new table, with same structure, where you want to take backup */
CREATE TABLE destination_table_name LIKE source_table_name;

/* After then you can use this command to copy those data */
INSERT INTO destination_table_name 
SELECT * FROM source_table_name;

/* If you already have previous data in your Destination table , Firstly you can use this command */
TRUNCATE TABLE destination_table_name;

/* Copy only the columns FirstName, LastName */
INSERT INTO PersonsBackUp (FirstName, LastName)
SELECT FirstName, LastName FROM Persons;

CREATE DB

NOTICE: if you work with shared hosting services, the best way is CPanel Admin to create new Databases

CREATE DATABASE dbname;

CREATE TABLE

phpMyAdmin> LEFT COLUMN> ‘Crea tabella’

or

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

INDEX

Indexes allow the database application to find data fast; without reading the whole table.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

CREATE INDEX PIndex
ON Persons (LastName)

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

DROP

It removes indexes, tables, and databases

/* Delete index */
ALTER TABLE table_name DROP INDEX index_name

/* Delete table */
DROP TABLE table_name

/* Delete database */
DROP DATABASE database_name

/* Delete table content only, preserve table */
TRUNCATE TABLE table_name

ALTER

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Statement:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

ALTER TABLE Persons
DROP COLUMN DateOfBirth

AUTO INCREMENT

Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

/* Let the AUTO_INCREMENT sequence start with another value */
ALTER TABLE Persons AUTO_INCREMENT=100