php mysql
MySQL is one of the most popular database system used with PHP. The data in a MySQL database are stored in tables. A table is a collection of related data and it consists of columns and rows. Databases are useful for storing information categorically.
Database Queries
Structured Query Language (SQL) is a standard language that allows us to access and manipulate databases. SQL can execute queries, retrieve data, insert records, update records, delete records, create new databases, create new tables, etc.
Creating Database
You can manually create a new database in your db management tool with the file menu options available. Create database tables according to your need. Here, we're gonna discuss about how to manually insert, display, update and delete data from such database tables first.
Click on the new option on the left side panel or database menu on the top to get this window.
Give a tablename and collation to your database and click on create. Now you can click on the dbname that appears on the left panel and get inside the db. Create data tables according to your need. Rest will be taken care below.
While creating a data table you need to keep in mind a few things, that is listed below.
You'll need an id for each contents in a table and that id needs to be auto incremented with each data inserted. For this, you need to define an id for the table then the type should be selected as integer (INT) and the auto increment option A_I should be checked. This makes the table id auto increment along with the number of data inserted into that table. The id will be helpful while sorting out the latest data from that table and display in the frontend. You should define the length of the data too in characters in the table and any data that exceeds that value will be auto rejected. For id it's enough if you give it a length of 10 or 11. The standard length value is 11 though.
Not just for the id, you need to define the character type for each data column while creating a table. Generally, all data will be defined as varchar in types if the data length is not much long. To insert huge images or huge texts you can use long text with length of 99999999 as the maximum length value for varchar would be 255 only.
Database Tables
A database contains one or more tables that contain data records organized in rows. Each table is identified by a unique name. Here is an example of a database table named 'books'.
| bookid | name | price | |
| 1 | Harry Potter and the Cursed Child, Parts 1 & 2 | J.K. Rowling | $320 |
| 2 | When Breath Becomes Air | Paul Kalanithi | $170 |
| 3 | The Whistler | John Grisham | $175 |
The table above contains 3 record rows (one for each book) and 4 columns namely bookid, name, author and price.
SQL Statements
Most of the operations on a database are done via SQL statements. There are a number of specific statements to execute specific tasks in SQL. Some of the most important statements are listed below.
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT - inserts new data into a database
CREATE - creates a new database or a new table or an index (search key)
ALTER - modifies a database or a table
DROP - deletes a database or a table or an index
SELECT
SELECT statement is used to select data from a database. Any data returned is stored in a table called result-set. SELECT is the most used statement and can be used along with multiple clauses.
SELECT ALL DATA
SELECT * FROM table_name;
Selects all the data stored in the defined table.
SELECT * FROM BOOKS
Selects all records from books table.
SELECT DEFINED DATA
SELECT columnName1,columnName2 FROM table_name;
Selects all data from defined columns of the table.
Example
SELECT name,author from books;
Selects all values from name and author column present in books table.
WHERE
Where clause is used to filter records. It only selects those data that meet the stated condition.
SELECT column1, column2 FROM table_name WHERE condition;
Example
SELECT name FROM books WHERE author = 'John Grisham'; SELECT * FROM books WHERE bookid = 1;
First statement selects only those name values from books table where author is John Grisham while the second one selects all data from the row where bookid is 1.
Operators that can be used with WHERE clause are listed below with their description.
| Operator | Description |
|---|---|
| = | Equal |
| <> | Not equal. Note: In some versions of SQL this operator may be written as != |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| BETWEEN | Between an inclusive range |
| LIKE | Search for a pattern |
| IN | To specify multiple possible values for a column |
AND, OR, NOT
AND / OR operators can be combined with WHERE caluse to filter records based on multiple conditions. AND is used to display records when all stated conditions are true while OR is used to display records when one of the stated conditions are true. Not operator displays all records when stated condition is false. We can combine these operators in a single statement as well.
AND
SELECT * FROM table_name WHERE condition1 AND condition2;
Example
SELECT * FROM books WHERE author = 'John Grisham' AND price = '$175';
Selects all records from books table where author is John Grisham and price is $175.
OR
SELECT * FROM table_name WHERE condition1 OR condition2;
Example
SELECT * FROM books WHERE author = 'John Grisham' OR price = '$175';
Selects all records from books table where author is John Grisham or price is $175.
NOT
SELECT * FROM table_name WHERE NOT condition;
Example
SELECT * FROM books WHERE NOT author = 'John Grisham';
Selects all records from books table where author isn't John Grisham.
ORDER BY
ORDER BY is used to sort the result-set in ascending or descending order. Ascending order is default. We need to use DESC to sort the records in descending order.
SELECT * FROM table_name ORDER BY columnName ASC/DESC;
Example
SELECT * FROM books ORDER BY 'price' DESC;
Selects all records from books table and displays them in descending order by price.
INSERT
INSERT is used to insert new records in a table.
INSERT INTO tblName(columnName,columnName,columnName) VALUES ('columnValue','columnValue','columnValue');
Example
INSERT INTO books (name,author,price) VALUES ('Calumet K','Merwit Webster','$230');
Inserts corresponding records in books table.
UPDATE
Update is used to modify the existing record-set.
UPDATE table_name SET columnName1 = columnValue, columnName2 = columnValue2 WHERE condition;
Example
UPDATE books SET name = 'The Road Builders', price = '$130' WHERE bookid = 4;
Replaces existing name and price with the new one where bookid is 4.
DELETE
DELETE is used to delete existing records from a table.
DELETE FROM table_name WHERE condition;
Example
DELETE FROM books WHERE id = 1;
Deletes the record of row with bookid 1 from books table.
DELETE ALL RECORDS FROM A TABLE
DELETE * FROM table_name;
LIMIT
LIMIT allows us to limit the number of records being returned.
SELECT * FROM table_name LIMIT 5;
Example
SELECT * FROM books LIMIT 3;
Selects and returns only the first 3 records from books table.
LIKE
LIKE is used to search for a specified character pattern in a table.
SELECT * FROM table_name WHERE columnName LIKE pattern;
Some of the LIKE patterns are listed below.
| Pattern | Description |
|---|---|
| 'a%' | Searches for values that start with "a" |
| '%a' | Searches for values that end with "a" |
| '%a%' | Searches for values that have "a" in any position |
| 'a%o' | Searches for values that start with "a" and end with "o" |
Example
SELECT * FROM books WHERE name LIKE '%harry%';
Selects all records from books table where "harry" is in the name column.
IN
IN allows us to add multiple values in a WHERE statement.
SELECT * FROM table_name WHERE columnName IN (columnValue1, columnValue2);
Example
SELECT * FROM books WHERE bookid IN (1,2,3);
Selects all records from books table where bookid is 1,2 and 3. It can be used to delete multiple record sets at once as well.
BETWEEN
BETWEEN allows us to select values within a defined range.
SELECT * FROM table_name WHERE columnName BETWEEN value1 AND value2;
Example
SELECT * FROM books WHERE price BETWEEN $100 AND $200;
Selects all records from books table whose value is between $100 and $200.
AS
AS can be used to give a temporary name to any column or a table to make it more reasonable, shorthand and readable. It is mostly used with JOIN when column name of two tables are identical.
SELECT column_name AS alias_name FROM table_name AS alias_name;
Example
SELECT name AS book_name FROM books AS tbl_books;
JOIN
JOIN is used to retrieve record-set from two tables with a single query when the tables have relationship established using reference.
SELECT tblName1.columnName1,tblName1.columnName2,tblName2.columnName1 FROM tblName1 JOIN tblName2 ON tblName1.columnName1 = tblName2.columnName2
Let's create a table name customers that has a relation to the table named books.
| customerid | name | orderid |
| 1 | Jonjo Shelvey | 2 |
| 2 | Danny Drinkwater | 3 |
| 3 | Ashley Barnes | 2 |
Here, orderid of table named customers is bookid of table named books. Now, we can use the JOIN statement to join both tables with reference to the bookid and orderid and retrieve required data.
SELECT books.name AS books_name, customers.name AS customers_name FROM books JOIN customers ON books.bookid = customers.customerid;
Result
| books_name | customers_name |
| When Breath Becomes Air | Jonjo Shelvey |
| The Whistler | Danny Drinkwater |
| When Breath Becomes Air | Ashley Barnes |
Here, all the matched records are displayed. If you need to display unmatched data too, instead of JOIN, you need to use one of the following statements.
LEFT JOIN Returns all records from the left table and the matched ones from the right.
RIGHT JOIN Returns all records from the right table and the matched ones from the left.
FULL JOIN Returns all records when there is a match in either left or right table
CREATE DATABASE
CREATE DATABASE creates new database.
CREATE DATABASE databasename;
DROP DATABASE
DROP DATABASE deletes a database.
DROP DATABASE databasename
CREATE TABLE
CREATE TABLE creates a new table.
CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype);
Datatype defines the type of data such as varchar, integer, date, etc. as well as he string length it can hold. Constraints are used behing datatypes to specify rules for data in each columns.
Example
CREATE TABLE books (
bookid int unsigned NOT NULL AUTO_INCREMENT,
name varchar(255),
author varchar(255),
price varchar(255),
);
A table named books will be created after this statement is executed where bookid needs to be an integer that has a positive value (unsigned), column can't be empty and the id value will increase along with the record count. Columns named name, author and price can hold variable characters where the maximum character count can't exceed 255.
DROP TABLE
DROP TABLE deletes an existing table.
DROP TABLE table_name;
ALTER TABLE
ALTER TABLE can be used to add, delete or modify columns or add and drop various constraints in an existing table.
ADD COLUMN
ALTER TABLE table_name ADD column_name datatype;
DROP COLUMN
ALTER TABLE table_name DROP COLUMN column_name;
SQL CONSTRAINTS
Constraints are used to limit the type of data that can be inserted into a table to ensure accuracy and reliability of the data. Violation of the constraint rules aborts the related database operations. Common constraints used in SQL are listed below.
NOT NULL - column cannot have a NULL value
UNSIGNED - only positive value
UNIQUE - all values in a column are unique
PRIMARY KEY - uniquely identifies each record. can't have more than one primary key
FOREIGN KEY - Uniquely identifies a record in another table
INDEX - Used to create and retrieve data from the database very quickly
AUTO_INCREMENT - increments primary key with each new record
FOREIGN KEY
CREATE TABLE customers (
customerid int NOT NULL UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name varchar(255) INDEX,
bookid int FOREIGN KEY REFERENCES books(bookid)
);
Leave a comment