PHP Tutorial - Databases in PHP, SQL Basics
Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js
Date: 1/31/2018 @ 2 p.m. ET
In the last part of this tutorial series we talked about the basic structure of databases in modern database management systems (DBMS) and mentioned the structured language used in accessing those databases. The language is the Structured Query Language, SQL, which is commonly pronounced as if it was a word - "Seekul". Now, before we get into coding specific commands in PHP, it is very worthwhile to get an overview of the basic elements of the language and the purpose of those elements.
In SQL everything is done in a "query". Personally I think this must be because somebody got the idea that since it's a query language, everything must be a query, and made it so even if it's an instruction like "add this record". That would be an insert query! Ah well, if I want to be a master of SQL I will have to toe the line an accept the expanded notion of "queries"! More about them later -- first, lets take a look at the most common commands you'll see.
It seems logical to begin with the create command, since it is used to create databases and the tables within them. In practice, databases are usually created outside an application like a PHP program. They are most frequently created using a management tool of the DBMS. For this reason, you probably won't write many database create commands. It is also used to create new tables, however. You might write a few of those commands!
The opposite of create, drop is used to delete databases and tables. For the same reasons stated for create, you'll probably write more table drops than database drops.
The first of the record level commands, insert is used to create a record in a table. Most of the work you do in database systems will involve operations on records. Remember that a record corresponds to a row in a table (the columns are the data elements, or fields, within a record.)
At the record level, delete is the opposite of insert. It is used to delete records in a table.
The other thing you'll want to do to a record -- change some of the information in it! Update is used to update, or rewrite, a record.
The select instruction picks a record, or records, from a table or tables. Select is the command that provides the real "query" in SQL. the select command enables you to, for example, select all addresses where the city is Atlanta and the TV Brand Installed is Sony. In this manner you are querying the database for all records that match some specific criteria. You will spend lots of time writing select queries. Options in select commands allow you to create a special view of the information in a database and sequence the record however you wish.
These few simple commands provide the basis of all the power that a database management system can bring to your application. It seems too simple to be true, but it is that very simplicity which makes the Structured Query Language such a valuable tool. There are other commands available, especially in some DBMS implementations, but these basic commands are always available. Remembering these few command will help you very much as you progress with your DBMS coding education.