SQL Basic Concepts

Introduction and Show Command

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):

Red Hat Linux release 7.1 (Seawolf) Kernel 2.4.20-20.7 on an i686 login: wsanders Password: Last login: Thu Nov 6 12:20:11 from JAS59190 [wsanders@localhost wsanders]$ mysql -h127.0.0.1 -uwsanders -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 154109 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

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.

mysql> show databases;
+----------+
| 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.)


Table of Contents NEXT  > >   Data, Create Tables

Developed with HTML-Kit
Sandersongs Web Tutorials
Contact the Webmasterwith comments.
©2017, by Bill Sanders, all rights reserved.
This domain had 4,000 different visits in the last 30 days.
435,566 hits on this domain since 24 Nov 2006.
http://www.sandersongs.com/PHPsqlCourse/SQL01.php
This page was last modified on our server on 4 Jun 2008
and last refreshed on our server at 3:56 am, MST
This file took 0.01312 seconds to process.