Introduction
SQL stands for Structured Query Language. Many servers use this language to access databases, such as Oracle, DB2, Sybase, Informix, PostgreSQL, Microsoft SQL, mSQL and MySQL. These lessons will focus on MySQL, but SQL is similar for all.
Against a database, SQL can execute queries, retrieve data, insert new records, delete records, update records, create and delete tables.
Databases contain objects called Tables. If you have ever used a spreadsheet program (like Microsoft Excel), you already know about tables. Tables are identified by names (like "People", "Offices" and the like). Tables contain Columns and Rows with data stored at the intersections of each column and row.
A record is a set of the data stored in one row. If you have a table that lists personal data of everyone in your office, each person has a record. A field is a set of the data stored in one column. In your personal data table, you might list names, phone numbers, office numbers, position, etc. Each of these is a separate field.
Here is a graphic depiction of a sample table you might find in MySQL (of course, the data is fictitious):
| People Table | ||||
|---|---|---|---|---|
| ID | NAME_LAST | NAME_FIRST | MI | POSITION |
| rjones | Jones | Robert | T | Manager |
| tjones | Jones | Tyler | R | Technician |
| bsmith | Smith | Barbara | N | Clerk |
| nsmith | Smith | Nancy | B | Manager |
Each row is another record. For instance, one record (highlighted above in red) has ID = bsmith, NAME_LAST = Smith, NAME_FIRST = Barbara, MI = N and POSITION = Clerk. Each column is another field. NAME_FIRST and MI are examples of fields.
Fields can have the same data in different records. For instance, NAME_LAST has "Jones" (and "Smith") listed twice. POSITION has "Manager" listed twice. When doing searches on a table, it is a good idea to have at least one field with unique data. That field is the Primary Key. In the People Table above, ID is the Primary Key. No two records can have the same ID.
Open the command line in MySQL (or access it by your software program). If you are in the command line, you should now have a screen that looks something like this (some software programs save you the burden of seeing all this, and just give you a blank space to start typing commands):
This is the MySQL command prompt. It is your friend. Everything happens from here. Note that all MySQL commands end with a semi-colon, and can be entered in either upper-case or lower-case.
SHOW Command
To see what databases are on the server, type SHOW databases; and hit enter (don't forget the semi-colon). Your screen may other databases than these.
+----------+ | Database | +----------+ | wsanders | | mysql | | test | +----------+3 rows in set (0.00 sec) mysql>
MySQL tells you how many rows are in the Result Set and how much time it took (to the nearest hundredth of a second) to complete the task. In this Result Set, there are three records (rows) with only one field (column). (Get used to this concept, you will apply it when using PHP to access MySQL.)
