SQL primer

home / how-to / SQL

alter table | change password | create database | create table | delete | describe | drop | export | import | insert | join | login | output to file | rename table | select | show databases | show tables | transactions | update | use

About SQL

SQL, (or Structured Query Language, also pronounced as "S-Q-L", or like the word "sequel" for short) is the collection of commands that are used to manipulate a database. There have been efforts to standardize these commands across each DBMS (Database Management System), though each still retains some commands that are unique to that environment. Each command heading below states which DBMS works with the command. Most modern DBMS support relational tables, meaning that information in one cell can tie to information in another. These are referred to as RDBMS, or a Relational DBMS.

It is customary that SQL commands are typed in CAPS, although most DBMS clients are case-insensitive. Also, most commands will not execute until you complete the statement with a semi-colon ';' and hit return, therefore SQL statements may be several lines long before they are executed.

A database table is very similar to a spreadsheet. It has columns (the table fields), and rows (each individual record). However, the main differences lay in how the information is stored, retrieved (with filters or joins), and sorted.

This summary was written to be a guide for those new to working with SQL, and a reference for those who are still learning. Not all details to features are discussed here. We leave the exercise of researching those that fill special needs to the reader. The first challenge is simply selecting which RDBMS will be best for your application.

Additional SQL reference resources:

• The MySQL Reference Manual
• A Gentle Introduction to SQL
• Philip Greenspun's SQL for Web Nerds, and
  Choosing a Database Management System

About this document:

Note: items in bold are not to be entered literally, but what is appropriate for your situation. For example, username should not be entered as 'username', but as your username, such as 'fred'.

If a section has a heading with a DBMS name such as mysql:, then the command works only in that system. If there is no heading, then it should be a universally accepted command. Of course, many of these commands may fail at any time, depending on how permissions are set. The prompt before the example should also give a clue where it is used - not all commands are intended to be used within the DBMS client, some are done from the unix command line, expressed with a 'unix%' prompt. Otherwise, the 'sql>' prompts are generic SQL, and should work in all mentioned systems.

login

mysql, oracle

To begin working with your database, you'll need to use a client program to login. (You can use 'exit' to quit)

mysql:

unix% mysql -u username -h hostname -p databasename
Enter password:

Depending on the security settings of your system, the '-u username -p' portion may be optional, which means "login with this username, and I'll specify a password". Also, specifying the hostname and name of the database that you're going to use is optional.

All of the above options can be used in combination with many of the external mysql command-line tools mentioned in this tutorial. They should be included before any additional commands.

oracle:

unix% sqlplus username@hostname.world
Enter password:

Note - the 'hostname.world' part is optional if you are logged into the machine that is hosting the database.

change password

mysql

Changing your password is a wise thing to do when you're assigned a new account, or every few months, or if you suspect that someone else has access.

mysql:

short version:

mysql> set password = password('new-password');

explicit version:

mysql> select current_user();
mysql> set password for username@`hostname` = password('new-password');

Note - the hostname parameter should be the name of the host from which you'll be connecting.

One weakness to this method of changing your password in mysql is that your command is written in plain-text to your ~/.mysql_history file. You should change the permissions on that file (or, if it's in AFS put it into a Private directory, and place a symlink to it). It's also a good idea to remove that line when you're done changing your password.

In addition, we host an instance of phpMyAdmin which can be used to change a mysql password.

oracle:

sqlplus> alter user username identified by newpassword;

show databases

mysql

mysql:

mysql> SHOW DATABASES;
+----------------+
| Database       |
+----------------+
| alpha          |
| beta           |
| mysql          |
+----------------+

create database

mysql

mysql:

If your database does not exist, or if you need to create another, you can make a new one:

mysql> CREATE DATABASE mydatabasename;

show tables

mysql, oracle

mysql:

This allows you to see which tables have been created inside the database selected.

mysql> SHOW TABLES;
+------------------------+
| Tables in databasename |
+------------------------+
| people                 |
| states                 |
+------------------------+
2 rows in set (0.00 sec)

oracle:

Oracle doesn't support this command, though an equivalent would be to use this sql statement:

sqlplus> SELECT * FROM tab;

This command may offer more information by also listing loaded sequences, and other entities.

sqlplus> SELECT * FROM cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
PEOPLE                         TABLE
PERSON_NUM_SEQ                 SEQUENCE
STATES                         TABLE

3 rows selected.

use

mysql

mysql:

If you did not specify a databasename when you logged in, or if you want to change which database you're using mid-session, then start working the the use command is for you.

mysql> USE databasename;
Database changed

create table

mysql, oracle

To create a new table, you'll need to know what fields you'll be using and their data type. Table definitions look like this:

CREATE TABLE people
(
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    age INT
);

It is possible to create tables manually, typing in each element at the prompt. Most systems allow this process to be automated by creating a text file and adding this table definition. Generally, these scripts are given a descriptive filename with the .sql extension to differentiate them from data, or other types of files. Note: - the final field definition does not end with a comma.

Many times fields will be marked as 'not null' - this is to prevent this information from being skipped. This would be used if that piece of information is essential to the meaning of the record.

Once your sql statement is ready to go, you can load those table entries into your database like this:

mysql:

unix% mysql databasename < create_people.sql

Note: be careful with this command, if you make a mistake and switch your < with a >, then your sql script can be lost.

oracle:

sqlplus> @create_people.sql
Table created.

describe

mysql, oracle

Once you've created your table, you'll want to check that it was made the way you wanted. This can also be used to reference how someone else created their tables.

sql> DESC people;

The command desc is simply a short version of describe. Below are example results to show how different DBMS display their output:

mysql:

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(30) |      |     |         |       |
| last_name  | varchar(30) |      |     |         |       |
| age        | int(11)     |      |     | 0       |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Note: in mysql, the describe keyword is an alias to SHOW FIELDS FROM tablename and both are identical to SHOW COLUMNS FROM tablename.

oracle:

 Name                            Null?    Type
 ------------------------------- -------- ----
 FIRST_NAME                      NOT NULL VARCHAR2(30)
 LAST_NAME                       NOT NULL VARCHAR2(30)
 AGE                             NOT NULL NUMBER(38)

In Oracle, there is much more information about each table stored in the USER_TAB_COLUMNS table. An alternate way to grab the information about each field would be to use this query:

sqlplus> SELECT column_name, data_type FROM
user_tab_columns WHERE table_name='tablename';

insert

mysql, oracle

This is used to insert new data into a table, it creates a new row with the information you provide. You can insert information into each field like this:

sql> INSERT INTO people VALUES ('Bob', 'Smith', '30');

You can also limit the values that are inserted by specifying which ones will receive the corresponding content. Also notice that the order is not dependent on the organization of the table, but the content being inserted:

sql> INSERT INTO people (age, first_name)
VALUES('24', 'Alice');

Otherwise, if you need to skip a field that has been marked as 'not null', a null value can be entered by using empty quotes in place of content for that field.

select

mysql, oracle

Retrieve information from one or more tables. Select can be customized in various ways to fit your needs. The most general select statement is:

sql> SELECT * FROM tablename;

The below example output is from mysql, though each DBMS is very similar.

mysql> SELECT * FROM people;
+------------+-----------+-----+
| first_name | last_name | age |
+------------+-----------+-----+
| Bob        | Smith     |  30 |
| Alice      | NULL      |  24 |
+------------+-----------+-----+
2 rows in set (0.00 sec)

The select statement is a command with many options to give granularity to the desired results.

Examples:

Find people with the last name of Reid:

sql> SELECT * FROM people WHERE last_name='Reid';

Find all last names that occur once or more in the table:

sql> SELECT last_name FROM people GROUP BY last_name;

Count number of unique names in table: (or how many people have the same first & last name)

sql> SELECT last_name, first_name, COUNT(*) AS instances FROM people GROUP BY last_name, first_name;

Combine records from two related databases:

sql> SELECT first_name, state FROM people, locations WHERE people.city = locations.city;

Select a limited number of rows (useful when working with a large amount of information)

mysql:

mysql> SELECT * FROM people LIMIT 3;

oracle:

sqlplus> SELECT * FROM people WHERE ROWNUM < 10;

These rownums auto-update, so if you delete a row, they shift to re-occupy that space

delete

mysql, oracle

This is used to delete records, and not the table structured that hold them.

sql> DELETE FROM tablename;

The above statement will empty a table. Selective deletion requires the proper selection of a filter, because it will delete any rows that match. Good practice dictates that you use the select command first to try out the pattern so as to avoid deleting important information.

sql> DELETE FROM tablename WHERE column='filter';

Using the table example illustrated above, I can drop Bob's record with the below command.

sql> DELETE FROM people WHERE first_name='Bob';

A downside to this would be that it would delete all records of people in my table that have the first name of 'Bob'. This is easily dealt with by creating a column of unique identifiers, also referred to as a primary key.

drop

mysql, oracle

One of the more dangerous procedures, this deletes an entire table. Typically, this is used when the data is no longer needed, or the table has recently been created from a script, and needs to be changed.

sql> DROP TABLE tablename;

export

mysql, oracle

Exporting information from a database is useful for many purposes. Two of the most common are backups and relocation to either another RDBMS or a different machine.

mysql:

To export from mysql, you'll want to use the unix 'mysqldump' program. It dumps the data into a plain-text format with SQL statements, so that the database can be easily recreated. Binary data is preserved, though represented in ASCII text.

The data is sent to STDOUT, so it may be easier dealt with by redirecting to a file.

unix% mysqldump databasename > dump-file.sql

oracle:

Oracle's data exporting procedure is a little more specialized. Exporting is done with the unix 'exp' program.

unix% exp username@hostname.world

The 'exp' program will walk you through a series of questions to customized the exported dump for your needs. This data can be imported using the unix 'imp' function.

output to file

mysql, oracle

Sometimes you'll want to write your results to a text file, instead of exporting it. This can be useful for capturing summaries of information for generating reports.

mysql:

mysql> SELECT * INTO OUTFILE '/path/to/filename' FROM tablename;

This command can only be used to create new files on the server host, and not for writing over existing files - as a security measure. Also - the user issuing the request must have the 'File_Priv' privilege set.

oracle:

sqlplus> spool filename;
sqlplus> SELECT * FROM tablename; (any commands)
sqlplus> spool off;

With sqlplus, you can begin spooling to a file, issue multiple commands, and then turn spooling off.

import

mysql, oracle

Your tables will likely have much more data than is feasible to enter manually. For that reason, most database systems will support the ability to easily export and import data from a delimited text file (tab, pipe '|', or comma are common examples of delimiters).

mysql:

You must be using mysql version 3.22.15 or greater in order to use this feature. In mysql, the filename specified must match the table that you're importing into.

unix% mysqlimport --local databasename tablename.txt

You can also import a SQL file. This is commonly created by mysqldump, but can also be made by hand.

unix% mysql -u username -p databasename < dump_file.sql.txt

oracle:

Oracle's data importing procedure is a little more complicated, as more options are presented. The unix application 'sqlldr' is used for importing delimited text into a table, and 'imp' pulls in data, creating tables from an exported data file.

sqlldr

Instructions for importing data into an empty table follow, if you're interested in loading data into a table that already has data, it may be helpful to mention that by typing sqlldr with no parameters, it will list all available options.

Assuming your table is currently empty, you may begin by making a control file. This example can be custom-tailored to fit your needs.

LOAD DATA infile 'people.txt' INTO TABLE tablename 
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
( first_name, last_name, age )

The last phrase tells the sqlldr which order the fields are listed in. The terminated keyword tells how the data fields are delimited, in this case the pipe character was used. The file 'people.txt' was used to contain the pipe delimited information, as shown here:

jackie|chan|48
bruce|lee|32
jet|li|39

Once your control and data files are ready, use this line to tell sqlldr which control file to run. If you're importing many records at once, it may pause briefly to report that it's reached a commit point:

unix% sqlldr userid=username control=controlfilename.ctl
Commit point reached - logical record count 84
Commit point reached - logical record count 123

imp

The 'imp' program works just like the 'exp' program. Upon login, it will prompt you for the exported dump filename, which tables to import, and many other preferences. This will also ask if you want to be warned if you're about to overwrite something that already exists.

unix% imp username@hostname.world

Tip - be sure you know the path of the exported data file.

You can easily import data that has been exported from another user, by using a parfile. This file should have the following pieces of information:

userid=importing-username
fromuser=exporting-username@hostname.world
touser=importing-username@hostname.world
buffer=10000000
file=exported-data.dmp
ignore=y
log=logfile.log

To import using this parfile, issue a command like this:

unix% imp parfile=my.parfile

update

mysql, oracle

Change an item in a record, or set of records without having to delete the old and re-insert the new.

sql> UPDATE tablename SET changethisfield='newvalue'
    WHERE existingfield='currentvalue';

Caution - an inadequate WHERE clause can change every row. This is not reversible in a system that doesn't support transactions. In those cases, it can be wise to test out your results with a select statement before messing with your data.

An example might be to assume that Alice has a birthday, and becomes one year older, you'll want to change her age. To do this, we'd find the record that has Alice stored in it, and change her age.

sql> UPDATE people SET age='25' WHERE first_name='Alice';

After noticing that it's a lot of work to update someone's age every time they have a birthday, you might want to rethink your data model, and instead store their original birthdate. Then, you can subtract that from the current date to find her age. (See the description for the alter table command.)

alter

mysql, oracle

This will change an existing table structure without having to dump the information, and read it in again. This command can take some rather complicated forms, as it can accmplish many tasks. The most generic form is below.

sql> ALTER TABLE tablename modifyaction;

Perhaps I want to start tracking which city these people live in, I could simply add a new column to keep track of that information:

sql> ALTER TABLE people ADD ( city varchar(40) );

Or perhaps you want to drop a column that was an error, or has grown obsolete.

sql> ALTER TABLE tablename DROP column columnname;

More examples of the "ALTER TABLE" syntax can be found in the mysql manual pages.

rename table

mysql, oracle

You can also rename a table, although the commands are slightly different in mysql and oracle:

mysql:

mysql> rename table tablename to newtablename;

oracle:

sqlplus> rename tablename to newtablename;

join

sql, oracle

A join is accomplished by tying together information from two different tables in a FROM clause. There are many types of joins, and even unions. A basic example of a join is illustrated below.

Let's say that we want to get a list of everyone who is from the state of Michigan, but we only know their city (assuming that you altered the table as described above, and entered some city names into your data). We can build a table called 'locations' that connects city and state. This separate table comes in handy because it isolates the location data, and allows for one centralized place to make edits of the data.

Let's create a table called locations:

CREATE TABLE locations
(
    city VARCHAR(40) NOT NULL,
    state VARCHAR(40) NOT NULL,
);

INSERT INTO locations VALUES( 'Ann Arbor', 'Michigan' );
INSERT INTO locations VALUES( 'San Francisco', 'California' );
        

A quick review of the data that we currently have entered:

sql> SELECT * FROM people;
+------------+-----------+-----+-----------+
| first_name | last_name | age | city      |
+------------+-----------+-----+-----------+
| Alice      | Reid      |  24 | Ann Arbor |
| Gail       | Reid      |  55 | Dallas    |
| Joe        | Blow      |  45 | Ann Arbor |
| Bob        | Smith     |  31 | Detroit   |
+------------+-----------+-----+-----------+
4 rows in set (0.00 sec)

sql> SELECT * FROM locations;
+---------------+------------+
| city          | state      |
+---------------+------------+
| Ann Arbor     | Michigan   |
| San Francisco | California |
| Dallas        | Texas      |
| Detroit       | Michigan   |
+---------------+------------+
4 rows in set (0.00 sec)

Now we can perform some joins on our data. (This example was done in mysql, though it should work in any RDBMS).

sql> SELECT first_name, last_name, people.city, state
    -> FROM people, locations
    -> WHERE people.city = locations.city AND 
    -> locations.state='Michigan';
+------------+-----------+-----------+----------+
| first_name | last_name | city      | state    |
+------------+-----------+-----------+----------+
| Alice      | Reid      | Ann Arbor | Michigan |
| Joe        | Blow      | Ann Arbor | Michigan |
| Bob        | Smith     | Detroit   | Michigan |
+------------+-----------+-----------+----------+
3 rows in set (0.00 sec)

transactions

oracle

Many modern database management systems support transactions that pass the ACID test. (Atomic, Consistent, Isolated, and Durable) These attributes prevent concurrency issues, such as a series of events that all need to take place at once. If for some reason, the events don't complete but instead leave some pieces of information changed but not others it could cause for some serious problems in the future.

An easy example of this could be a bank transfer. If you transfer $1000 from your savings account to your checking account, the bank's computer will first remove the $1000 from your savings account, but what happens if the power goes out before it's deposited in your checking account? You could have just lost your money if the transaction wasn't able to be rolled-back.

MySQL does support transactions as of version 4, though it has only recently has begun to provide support for ACID-compliant transactions. If you will be writing an application that depends upon the condition of your data, it may be wise to use an ACID-compliant DBMS.

Because of transactions, many commands in Oracle will need to be committed, meaning that the full transaction should be acted on, written to disk, and made permanent. This command is very simple, but should be used with care to ensure that your last few moves were correct.

oracle:

sqlplus> commit;

alter table | change password | create database | create table | delete | describe | drop | export | import | insert | join | login | output to file | rename table | select | show databases | show tables | transactions | update | use