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