PHP110
Main Page >> Web Application Development >> Workbook >> Simple Databases Concepts
Simple Database Concepts & SQL
The following sections on databases are brief, as they are considered merely to be a simple overview of a topic well-covered in other modules.
Database Concepts
The following definitions are courtesy of www.webopedia.com
- Server
- A server is a computer or device on a network that manages network resources. For example, a file server is a computer and storage device dedicated to storing files. Any user on the network can store files on the server. A print server is a computer that manages one or more printers, and a network server is a computer that manages network traffic. A database server is a computer system that processes database queries.
- Servers are often dedicated, meaning that they perform no other tasks besides their server tasks. On multiprocessing operating systems, however, a single computer can execute several programs at once. A server in this case could refer to the program that is managing resources rather than the entire computer.
- Databases
- A collection of information organized in such a way that a computer program can quickly select desired pieces of data. You can think of a database as an electronic filing system.
- Traditional databases are organized by fields, records, and files. A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records. For example, a telephone book is analogous to a file. It contains a list of records, each of which consists of three fields: name, address, and telephone number.
- Tables
- Refers to data arranged in rows and columns. A spreadsheet, for example, is a table. In relational database management systems, all information is stored in the form of tables.
- Records / Rows
- A record is a complete set of information. Records are composed of fields, each of which contains one item of information. A set of records constitutes a file. For example, a personnel file might contain records that have three fields: a name field, an address field, and a phone number field.
- Fields
- A space allocated for a particular item of information. A tax form, for example, contains a number of fields: one for your name, one for your Social Security number, one for your income, and so on. In database systems, fields are the smallest units of information you can access. In spreadsheets, fields are called cells.
- Most fields have certain attributes associated with them. For example, some fields are numeric whereas others are textual; some are long, while others are short. In addition, every field has a name, called the field name.
- In database management systems, a field can be required, optional, or calculated. A required field is one in which you must enter data, while an optional field is one you may leave blank. A calculated field is one whose value is derived from some formula involving other fields. You do not enter data into a calculated field; the system automatically determines the correct value.
- A collection of fields is called a record.
If you want to create your own database on MI-LINUX, you might like to take a look at Registering to use MySQL on MI-LINUX Server
Using SQL
In order to retrieve and manipulate data from a database, instructions must be sent to the database server. In this case the server is the MySQL server running on MI-LINUX. Instructions are sent as part of the PHP scripts you write.
Each command is called a query. It is called a query because you are enquiring from the database. The queries are written in a language that the server can understand – Structured Query Language (SQL). You might be familiar with the nature of SQL from other modules you have studied.
Retrieving Information from a MySQL Table
You are referred to the local on-line documentation. In particular, the section on retrieving information from a MySQL table which is at:
https://dev.mysql.com/doc/refman/8.0/en/retrieving-data.html
SQL Example
SQL statements can take many forms. The following is a brief overview. You are encouraged to read further yourself.
All examples are based on the following table, please create it by running the following SQL statements in phpmyadmin (in the SQL tab):
CREATE TABLE IF NOT EXISTS `pet` ( `name` varchar(20) NOT NULL, `species` varchar(20) NOT NULL, `owner` varchar(20) NOT NULL, `age` tinyint(4) NOT NULL )
Insert some records by running the following statement a few times with different values:
INSERT INTO `pet` (`name`, `species`, `owner`, `age`)
VALUES ('Chomper', 'Rabbit', '2', 3);
SELECT
To retrieve, or read, information from a database we use a SELECT statement
- To read just the name of every animal:
SELECT name FROM pet;
- To read all the information of every animal
SELECT name, species, age, owner FROM pet;
- or
SELECT * FROM pet;
INSERT
To add information to a database is known as INSERT-ing
- To INSERT a record/row into our "pet" table, we use the following statement:
INSERT INTO pet VALUES
("Chomper", "Rabbit", 2, "Hannah");
UPDATE
To amend a record, we use the UPDATE statement. The UPDATE statement must be used very carefully, as it is possible to amend many records at once:
UPDATE pet SET age=3;
- To specify a particular record, we use a WHERE clause
UPDATE pet SET age=3 WHERE name="Flopsy";
DELETE
To remove a record, we use the DELETE statement. The DELETE statement must be used very, very carefully, as it is possible to delete many records at once, and there is no UNDO:
DELETE FROM pet;
- To specify a particular record, or records, we use a WHERE clause
DELETE FROM pet WHERE name="Flopsy";
