As mentioned, SQL is the most commonly used language for manipulating and retrieving data from database tables. It was originally proposed by IBM researchers in the 1970s, and several companies produced commercial relational databases using their own custom versions of SQL. It was not until 1986 that the language was formally standardized by ANSI, the American National Standards Institute. Several “dialects” of SQL exist today, most of which adhere to the core standard, but which also extend the language in an effort to maintain an edge against competitors.
The core function of SQL is to allow database users to:
SQL is a deceptively simple language that can be used to create very sophisticated database queries. When you are working with existing databases the following four commands will do almost everything you need. (SQL is case-insensitive, but language elements are shown in ALL CAPS by convention, to differentiate them from the variable data.)
SELECT fields FROM tableName WHERE field=value;
INSERT INTO tableName (field1, field2, field3, etc) VALUES (value1, value2, value3, etc);
UPDATE tableName SET field1=value1,field2=value2,etc WHERE field=value;
DELETE from tableName WHERE field=value;
MySQL Database | SQLite Database |
---|---|
(Note: In order to do this command line exercise the MySQL server administrator must grant the dight310 user elevated permission levels on the database. These elevated permissions were only available during the class period, since leaving them in place would expose other databases to inadvertent damage. See the instructor if you want to try this outside of class.) In this exercise we will use a Unix command line program—called mysql—to create and populate a database table.
|
|
|
|
|
|
|
|
Now create a table in your new database using the CREATE TABLE command. When you create a table you also create one or more fields in the table. For each field you must specify the type of data that the field will contain and the maximum length of the data. See this link for a summary of the most common data types.
CREATE TABLE people (person_id int(4), fname varchar(32), lname varchar(32), hire_year year, phone varchar(14));
You can verify that the table was created by using:
sqlite> .tables
mysql> SHOW TABLES;
Add a couple of records to the new people
table using the SQL INSERT command (make up your own names for the records you're inserting):
INSERT INTO people (person_id,fname,lname,hire_year,phone) VALUES (1357,'Jack','Black','1999','123-456-7890');
Use a SELECT statement to make sure it worked.
SELECT * FROM people;
You should see all of the records you just added.
You can continue on in this way to create all of your tables and inserting records into them. To save time, let's switch to an already-completed database before we continue working.
SQLite: Download the file "acmeco.sqlite" from the DigHT 310 server under lesson_materials > Databases and save it to your Desktop. Now, in the terminal type:
.exit
This will quit sqlite3. Immediately re-enter sqlite3 using the path to the newly-downloaded file:
sqlite3 /path/to/desktop/acmeco.sqlite
Of course, using the proper file path for your computer. This will open SQLite with the new database.
MySQL: Normally when using MySQL you would need to coordinate with the server administrator at this point to set up access permissions to your new database. To save time, for the next several items we will use an already existing database. Switch to the acmeco database using
USE acmeco;
Now add several records to the new table using the INSERT command. Note that you don't have to fill every field for each record:
INSERT INTO people (fname,lname,phone) VALUES ('Milton','Chilton','123-456-7890');
Use a SELECT statement to make sure it worked.
SELECT * FROM people;
You can change the contents of a field in a record using the UPDATE command.
First try it with no WHERE clause. What happens?
UPDATE people SET phone = '801-123-4567'
WARNING! This will change the phone field for every record!
Now try it using a WHERE clause. (Format: WHERE fieldname = 'value from an existing record'
;)
UPDATE people SET phone = '801-123-4567' WHERE lname = 'Chilton'
You can delete a record using the DELETE command
DELETE FROM tablename WHERE fieldname = 'an existing value';
WARNING! If you don't limit the DELETE with a WHERE clause, you will delete every record in the table!
To exit type:
sqlite> .exit
mysql> exit;
While a MySQL database can be managed from a comand line in this way, it quickly becomes tedious and cumbersome as the database grows larger. Fortunately there are a number of graphical interfaces available for working with both MySQL and SQLite.
There are a number of GUI's available for working with SQLite databases. For example there is a good, open source GUI called DB Browser for SQLite that can be downloaded from http://sqlitebrowser.org. There is even a low-cost commercial SQLite GUI, called SQLiteAdmin, which was created in LiveCode.
For working with MySQL, one of the most widely used GUI's is phpMyAdmin, a web-based graphical interface to MySQL databases that allows us to visualize the data in a much more user-friendly format. Our MySQL server has phpMyAdmin installed, and it can be reached at http://humacademic.byu.edu/phpmyadmin. You can log in to this interface using the login credentials that I gave you in class.
Try the following in your MySQL or SQLite GUI:
Figure out SQL SELECT statements that do things like the following. The SELECT tutorial we did earlier will give you ideas on how to create these.
Create a query that will show all of the people in a certain department.
Create a query that limits the results to only people whose names start with a certain letter.
Create a query that gives you the number of people in a certain department. Use the SQL count()
function.
There are many sources on the web for learning about SQL in general and MySQL in particular. Here is a list of some good sites.
There used to be an entire CHum (precursor of the DigHT program) course devoted exclusively to the art and science of databases. There are a number of pages on the site for CHum 240 that may be useful.
The CREATE TABLE statement (including a summary of the most common data types)
The INSERT statement
The SELECT statement
The UPDATE statement
The DELETE statement
http://www.w3schools.com/sql/default.asp.
This utility lets you import, edit, and export SQLite databases in a web browser. There are also some good SQL documentation links on the page.
Return to the Databases Lessons Outline and complete the two assignments under Part 2—The SQL SELECT Tutorial and Assignment Part 2 - Creating your own database.