SQL and MySQL tutorial
Introduction
- Learning goals
- Learn some introductory relational database principles
- Learn the most important SQL commands
- Prerequisites
- None regarding databases
- Know how to install a LAMP/WAMP combo on your computer or have access to a database system with a web hosting service. If you plan to play just a little bit on your PC, we suggest installing a Modular Webserver System (MOWES) server since MOWES doesn't make any changes to the system. It can even run from a memory stick. Make sure to include the PhPMyAdmin package in your "mix".
- Moving on
- Level and target population
- Beginners
- Remarks
- This is an first version made from teaching slides ... certainly could be improved
“In computer science, a database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. The records retrieved in answer to queries are information that can be used to make decisions. The computer program used to manage and query a database is known as a database management system (DBMS).” (Wikipedia, retrieved 22:30, 12 September 2007 (MEST)).
See the database article for a list of relevant Wikipedia entries that define various kinds of databases. In this tutorial we will look at so-called relational databases.
What is a relational database ?
A relational database contains one or more or more 'tables' that in turn contain 'records' also called lines.
- Each record is made of 'fields' also called 'columns'.
- Each record (line) represent an information entity (an object described by attributes or a relation hold together by a unique key).
- Usually, the first field is used to insert the unique identifier for a record.
- Some tables include relations (e.g. a column includes an identifier that corresponds to an identifier for the whole record of an other table). E.g. person A lives in city X. A table defining a list of addresses may include a field "town" that links to a record of another table describing the town.
Columns/fields contain different data types
- E.g. integer numbers (int) or character strings (varchar).
Data in a table can be retrieved or updated
To retrieve or update information in a table, you need:
- the name of the table
- Names of columns and search criteria, e.g. "I want all records from table test where age > 20".
To sum it up shortly, persistent storage implies Create, read, update and delete, known under the acronym CRUD. We shall see how this is formally done with the SQL language.
The SQL language
Most relational databases are implement with a Relational Database Management System (RDBMS) using SQL, the Structured Query Language invented in the 1970s. SQL has been revised 6 times, the latest version is SQL:2008. Many implementations only implement a subset of the language, others add their own extensions. Wikipedia states that “Common criticisms of SQL include a perceived lack of cross-platform portability between vendors”(retrieved 19:46, 26 January 2010 (UTC)). The MySQL RDBMS version 5x supports a broad subset of ANSI SQL:1999. In addition, MySQL allows to use different storage engines for different tables within a single database. E.g. InnoDB can support transactions which the faster default MyISAM engine can't.
SQL is a language to request data from a database, to add, update, or remove data within a database (Create, Read, Update, Delete), or to manipulate the metadata of the database. More precisely, SQL allows to:
- formulate queries, i.e. Read or find stuff in a database (SELECT)
- manipulate records (UPDATE, INSERT, DELETE). This commands are part of the so-called Data Manipulation Language (DML)
- define tables and columns, as well as redefine and remove both (CREATE, ALTER, DROP). These commands are part of the so-called Data Definition Language (DDL).
- define access rights to database users (GRANT, REVOKE)
- control transactions, e.g. ensure that a series of transaction is completed (only available in larger implementations)
SQL instructions are given in the form of commands, consisting of a specific SQL command and additional parameters and operands that apply to that command. The simplest example of how to retrieve all of the rows and columns of a table named Students is:
SELECT * FROM Students
Wikipedia includes good SQL syntax overviews, see our SQL article for the most important entries. According to the Wikipedia contributors, the SQL language includes:
The SQL language is sub-divided into several language elements, including:
- Statements which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
- SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.
- Whitespaces are generally ignored in SQL statements and queries, making it easier to format SQL code for readability.
- Clauses, which are in some cases optional, constituent components of statements and queries.
- Expressions which can produce either scalar values (i.e. just a number) or tables consisting of columns and rows of data.
- Predicates which specify conditions that can be evaluated to truth values and which are used to limit the effects of statements and queries, or to change program flow.
- Queries which retrieve data based on specific criteria.
That may sound a little bit too abstract (and it is). In this little tutorial, we will introduce simple forms of the most important SQL instructions and mostly use examples for that.
Let's now have a look at the definition of simple SQL table, i.e. the kind of tables you will learn how to create during and after reading this tutorial. Table definitions can be shown in several forms. Below we show an SQL "CREATE TABLE" instruction and that we will introduce and explain later. It will create a table with a few fields:
CREATE TABLE demo1 (
id int(10) NOT NULL auto_increment,
login varchar(10) NOT NULL default '',
password varchar(100) default NULL,
fullname varchar(40) NOT NULL default '',
url varchar(60) NOT NULL default '',
food int(11) NOT NULL default '0',
work int(11) NOT NULL default '0',
love int(11) NOT NULL default '0',
leisure int(11) NOT NULL default '0',
sports int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY login (login)
);
The same formal SQL instruction can be represented as a table:
Database: demo Table: demo1 Rows: 1 +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(10) | | PRI | 0 | auto_increment | | login | varchar(10) | | MUL | | | | password | varchar(100) | YES | | | | | fullname | varchar(40) | | | | | | url | varchar(60) | | | | | | food | int(11) | | | 0 | | | work | int(11) | | | 0 | | | love | int(11) | | | 0 | | | leisure | int(11) | | | 0 | | | sports | int(11) | | | 0 | | +----------+--------------+------+-----+---------+----------------+
Ignore the details for the moment. As you may guess, in the "Field column" you see the various field (column) names, i.e. from "id" to "sports". Each field then is defined as a data type, e.g. "int(10)" means integer of 11 digits. We shall come back later to table definition and data types. Let's now first have a look at retrieving data that already exists in a database.
Life example (access restricted because of spamming)
In general, SQL isn't case sensitive and ignores excess whitespace characters, except for the contents of an alphanumeric string. Upper case is frequently used for SQL commands as a matter of style, i.e. they will stand out.
One may group SQL instructions into the following categories:
- Data Query/Retrieval
- SELECT - Used to retrieve certain records from one or more tables.
- Data Manipulation
- INSERT - Used to create a record.
- UPDATE - Used to change certain records.
- DELETE - Used to delete certain records.
- Data Definition
- CREATE - Used to create a new table, a view of a table, or other object in database.
- ALTER - Used to modify an existing database object, such as a table.
- DROP - Used to delete an entire table, a view of a table or other object in the database.
- Data Control Language
- GRANT - Used to give a privilege to someone.
- REVOKE - Used to take back privileges granted to someone.
Below we shall introduce most of these, but with a slightly different organization.
SQL syntax principles
We already introduced the SQL language elements above, in particular the so-called statements that always look like:
COMMAND something CLAUSEs ;
Let's now just talk about identifiers that you will create, e.g. names for tables and fields and comments that you may insert into your SQL code.
Identifiers
Here are some general rules that concern database names, tables, columns, etc.
- Keep the name below 30 characters
- Authorized characters* letters, numbers, #, $, _
- First character must be a letter
- Don't use any accents, e.g. decision is ok, décision is not !
- Don't use any SQL keywords, e.g. do not use SELECT, WHERE, etc.
- Note* SQL is not case sensitive ...
- Tables and fields
- You may use the same field name in different tables
- Complete field (column) name: database.table.column
ex: demo.demo1.login ex: demo1.login
Reserved words
Don't use reserved words
Let's create a table called create (don't worry if you don't understand this instruction yet, we just would like to show an error message).
CREATE TABLE create (begin INT, end INT);
You would get an error message like this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE ( BEGIN INT, END INT )' at line 1
Can't use names for tables and fields that used in the SQL language.
The list of reserved words, i.e. words that are part of MySQL is rather long, have a look at the [manual] if needed.
Comments
MySQL supports three styles of comments:
(1) From a # character to the end of the line.
(2) From a "-- " sequence to the end of the line. Notice that there is a blank after the two dashes !
(2) From a /* sequence to the following */ sequence, as in PHP. This syntax allows to add a comment that extends overall several lines
Here is an example taken from the manual (retrieved 1/2010)
mysql> SELECT 1+1; # This comment continues to the end of line mysql> SELECT 1+1; -- This comment continues to the end of line mysql> SELECT 1 /* this is an in-line comment */ + 1; mysql> SELECT 1+ /* this is a multiple-line comment */ 1;
Time now to see some action, i.e. we will see how we can retrieve data from a table.
Creating, listing and selecting databases and creating users
To create a database:
CREATE DATABASE db_name;
- or if you want to test if it already exists
CREATE DATABASE IF NOT EXISTS demo;
- Creation of an example database called demo:
CREATE DATABASE demo;
Usually, i.e. if you are not on your own development server, you cannot create database just like that. You do need administrator rights. Before creating database table with some script, you would connect to the database server as administrator and create a database (either by filling in a form or by executing the SQL statement.
To list all databases in your database server, type:
SHOW DATABASES;
Usually, since one needs the permission to use a database, databases are selected through a client interface or via a connect statement if you use a program like PHP. However, you can explicitly select a database with the USE command, e.g.
USE demo;
Finally, you could create a user that is allowed to make changes to the database. Like database creation, this is often done through the GUI of a database client. Below is an example SQL code that creates a user called "demouser" with password "some_password" and that will have all privileges on a database called "demo".
use DEMO;
CREATE USER 'demouser@localhost' IDENTIFIED by 'some_password';
GRANT ALL PRIVILEGES ON demo TO 'demouser@localhost';
FLUSH PRIVILEGES;
Retrieving data
Let's now see how we can retrieve data from a table with the SELECT SQL command.
Simple queries (selection)
The SELECT statement allows to retrieve records from one or more tables:
Here is a rough summary of its complex syntax:
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO OUTFILE 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
In this course we will work with simpler statements like:
SELECT select_expression1
FROM table_references WHERE where_definition2 ORDER BY col_name
Here are two very simple select statements
SELECT field1,field2 FROM table1
SELECT * FROM table1
In the example below we will retrieve the fields id,login,fullname,love,sports for all records in table demo1.
SELECT id,login,fullname,love,sports FROM demo1
+----+-----------+------------------+------+--------+ | id | login | fullname | love | sports | +----+-----------+------------------+------+--------+ | 1 | test | Tester Test | 3 | 3 | | 34 | colin2 | Patrick Jermann2 | 1 | 4 | ....
Let's retrieve all fields from table demo1.
SELECT * FROM demo1
+----+-----------+---------+------------------+------------------------+------+--..... | id | login | password| fullname | url | food | w..... +----+-----------+---------+------------------+------------------------+------+---. | 1 | test | 098cd4c | Tester Test | http://tecfa.unige.ch | 3 | ... | 34 | colin2 | b9hhhex | Patrick Jermann2 | http://tecfa.unige.ch/ | 1 | ...
Conditional selection (SELECT .... WHERE)
The general syntax pattern is the following:
SELECT .... FROM table WHERE condition
Overview table of condition operators
Remarks:
- Use parenthesis in longer version to make sure to get what you want
- Strings should be included in straight quotes or double-quotes '...' or "..."
Below we will illustrate how to use these operators with a few examples:
- Simple 'Select ... where'
Retrieve parts of the records where love is bigger than 4
SELECT id,login,fullname,love,sports FROM demo1 WHERE love>4
+----+----------+------------------+------+--------+ | id | login | fullname | love | sports | +----+----------+------------------+------+--------+ | 3 | colin | Patrick Jermann | 6 | 4 | | 4 | schneide | Daniel Schneider | 6 | 6 | +----+----------+----------------+------+--------+
- 'Select ... where'
SELECT * from demo1 WHERE login = 'colin' AND food < 6
- 'Select ... where ... IN'
Return the fullname of all records where login is either 'colin' or 'blurp'
SELECT fullname from demo1 WHERE login in ('colin', 'blurp')
- 'Select ... where ... BETWEEN'
SELECT * from demo1 WHERE food BETWEEN 3 AND 5
SELECT fullname from demo1 WHERE food BETWEEN 3 AND 5 AND love > 2
- 'Select ... where ... LIKE'
- Find all records that include 'Patrick' in the fullname field. We use the LIKE clause with the % wildcard operator.
SELECT id,login,fullname,love,sports FROM demo1 WHERE fullname LIKE '%Patrick%';
Results:
+----+----------+------------------+------+--------+ | id | login | fullname | love | sports | +----+----------+------------------+------+--------+ | 3 | colin | Patrick Jermann | 6 | 4 | | 93 | michelon | Michelon Patrick | 6 | 6 | +----+----------+------------------+------+--------+
- Select ... where ... REGEXP
SELECT * from demo1 WHERE fullname REGEXP 'P.*J.*'
SELECT login,fullname from demo1 WHERE fullname REGEXP 'P.*J.*';
Results:
+--------+------------------+ | login | fullname | +--------+------------------+ | colin2 | Patrick Jermann2 | | blurp | Patrick Jermann2 |
Result sorting (SELECT ... ORDER)
Select all records (lines) and sort according to the id field
SELECT * from demo1 ORDER ''by id''
- Same thing, but DESC = will sort in reverse order
SELECT * from demo1 ORDER ''by id DESC''
Count records
- Count all lines (not null)
SELECT COUNT(*) FROM demo1
- Return counts of records having the same login
SELECT login, COUNT(*) FROM demo1 GROUP BY login;
Use of more than one table
Fields are identified with the following syntax: name_table.name_column
Example that select in 2 tables (see also Relational tables below):
SELECT demo1.fullname FROM demo1, test WHERE demo1.login = test.login
+-------------+ | fullname | +-------------+ | Tester Test | +-------------+
The next exemple use the syntax "Union" to join two tables with differents conditions.
(SELECT name_client FROM clients_swiss)
UNION
(SELECT name_client FROM clients_french)
+-------------+ | name_client | +-------------+ | JeanRoule | | JuleMent | +-------------+
The two differents queries use two differents tables "cleints_swiss" and "clients_french". The number and the name of the fields must be the same in the two differents query. In this example there is one field with the same name "name_client"
Defining database tables
Creation of an SQL table implies
- give it a name
- define fields (columns): type, size, default values, ...
- add other constraints to fields
- grant permissions (sometimes)
More or less complete syntax of the Create Table command:
CREATE TABLE [IF NOT EXISTS] table_name (create_definition1,...) [table_options] [select_statement]
create_definition is formally defined as:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] KEY(index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) [reference_definition]
or CHECK (expr)
... ignore for now, we will introduce the basics through the next slides...
Data types
Not all RDBMS implement all data types, MySQL implements the most important ones.
- Strings must be enclosed with delimiters and escape special characters.
- delimiters: '....' or " ....."
- Special characters need to be quoted with \: \n (newline), \r (CR), \t = (tab), \', \", \\, \%, \_
- Quotes can be included within other quotes, e.g. ' "hello" ', " 'hello' " (no spaces)
Optional attributes (see next slides)
- UNSIGNED : only positive numbers
- SIGNED (the default): its opposite, e.g. a number from -N to +N
- ZEROFILL : inserts 0s, ex. 0004)
Optional parameters
- M : display size
- D (floating point numbers): digits after the "."
In the MySQL 5 Manuel, Data type descriptions use the following conventions:
- M indicates the maximum display width for integer types. For floating-point and fixed-point types, M is the total number of digits that can be stored. For string types, M is the maximum length. The maximum allowable value of M depends on the data type.
- D applies to floating-point and fixed-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M–2.
- Square brackets ([ and ]) indicate optional parts of type definitions.
The NULL value
- Values can be NULL (means “empty”, not zero or empty string "" !!)
Data types summary table
TinyInt[(M)][UNSIGNED] [ZEROFILL]
|
||||
SmallInT[(M)]...
|
||||
MediumINT[(M)]...
|
||||
INT[(M)] ...
|
||||
BigINT[(M)]...
|
||||
FLOAT[(M,D)]...
|
||||
DOUBLE[(M,D)]...
|
||||
DATE
|
||||
DateTime
|
||||
TimeStamp[(M)]
|
||||
TIME
|
||||
YEAR
|
||||
Char(M) [character set charset_name]
|
||||
VarChar(M)[character set charset_name]
|
||||
BINARY(M)
|
||||
VARBINARY(M)
|
||||
TINYBLOB
|
||||
BLOB
|
||||
MEDIUMBLOB
|
||||
BLOB
|
||||
TINYTEXT
|
||||
TEXT
|
||||
MEDIUMTEXT
|
||||
LONGTEXT
|
||||
Enum('val1', 'val2',...)
|
||||
Set('val1', 'val2', ...)
|
||||
Binary and blobs vs. char and text: The first will story data in binary format. Char and text will store text with a given character encoding and you can edit these fields with a database tool.
In most respects, you can consider that a BLOB column is like VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column.
Below is an example of a very simple table definition:
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE);
Keys
There are two sorts of keys. Firstly, keys are used to index column data, so that retrieval will not have to search item by item through the whole table. Second, so-called primary keys identify unique records and foreign keys allow to create relations between tables.
Simple column keys (KEY)
- Indexed columns will improve database performance
- Each table can include 16 indexed columns (or more depending on the product)
- All types (except blob and text) can be indexed, but must have non-NULL values !!
- Indexing of CHAR and VARCHAR can be reduced to first few characters
Syntax:
KEY index_name (col_name) KEY index_name (char_col_name(M))
Note: INDEX is synonymous of KEY
Primary KEYs
- Primary keys uniquely identify a record (line)
- Therefore you can't use a same value in more than one record, you cannot define a default value either...
- Most often, integers are used for this
- Most often, these are automatically generated
- Syntax: PRIMARY KEY (index_col_name, index_col_name)
Example:
id int(10) NOT NULL auto_increment,
PRIMARY KEY (id),
Foreign KEYs
- Foreign keys allow to say that a key in given table is identical to a primary key in an other table
FOREIGN KEY (some_id) REFERENCES other_table(id)
Example:
FOREIGN KEY (student_id) REFERENCES student(id)
An example with a foreign key can be found in the discussion about relations between tables (below).
Definition of fields
Note: See the complete example in section Table creation (CREATE)
In our demo1 example, the columns are defined like this:
id int(10) NOT NULL auto_increment,
login varchar(10) DEFAULT '' NOT NULL,
password varchar(100),
url varchar(60) DEFAULT '' NOT NULL,
food int(11) DEFAULT '0' NOT NULL,
A minimalist definition of a column is:
name type Ex: id int
For some field types you should define a length, e.g. for VarChar and Char:
Ex: login varchar(10)
The pattern for a typical definition of a column is:
name type (size) DEFAULT 'value_default' NOT NULL,
Example
login varchar(10) DEFAULT NOT NULL,
Definition of a primary key:
name type [size)] NOT NULL [auto_increment], Ex: name int(10) NOT NULL auto_increment,
Keys are always defined with a separate statement, e.g.
PRIMARY KEY (id), KEY login (login)
Table creation (CREATE)
- CREATE TABLE table (column1 spec1, column2 spec2, keys, )
The demo1 table is defined as follows:
CREATE TABLE demo1 (
id int(10) NOT NULL auto_increment,
login varchar(10) NOT NULL default '',
password varchar(100) default NULL,
fullname varchar(40) NOT NULL default '',
url varchar(60) NOT NULL default '',
food int(11) NOT NULL default '0',
work int(11) NOT NULL default '0',
love int(11) NOT NULL default '0',
leisure int(11) NOT NULL default '0',
sports int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY login (login)
);
If you wish you now can copy/paste the above CREATE TABLE definition into a SQL management tool and the try out some SELECT instructions.
A few hints:
- Make sure to separate each column or key definition with a comma
- End the whole CREATE statement with a ;
- Make sure that your quotes are straight !!!
- GOOD: ' and "
- BAD: ’ and ‘ and ” and “
Insertion and updates
Insertion
INSERT allows to insert new lines (record) in one or more tables. There exist two methods as shown in the two examples below.
(1) Insertion of a new line by specifying all values for all fields in the right order:
INSERT INTO demo1 VALUES (NULL,'colin', 'b9hhhfa9347all893u483', 'Patrick Hero','http://tecfa.unige.ch/',1,2,1,3,4);
INSERT INTO demo1 VALUES (NULL,'colin2', 'b9hhhfa9347all893u483', 'Patrick AntiHero','http://tecfa.unige.ch/',5,2,1,3,4);
INSERT INTO demo1 VALUES (NULL,'user12','098f6bcd4621d373cade4e832627b4f6','Testuser','www.mysql.com',1,4,5,2,1);
(2) INSERTION of a new line but specifying only values for a few fields. In this case you first must provide the list of fields for which values are entered.
INSERT INTO demo1 (login, fullname, food) VALUES ('test2', 'Patrick Test',4)
Attention: this can only work if a field is defined with default values (and not null)
food int(11) DEFAULT '0' NOT NULL,
If a field is minimally defined, NULL will be inserted (something you should avoid)
fun int(11)
You will get an error:
- if you try to enter a new id (primary key) that is already in the database
- if you don't enter data for fields that require NOT NULL , but have no default value defined.
Updating
UPDATE allows to update several fields for a selection (one or more lines !)
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,... [WHERE where_definition]
UPDATE examples:
Update of the ('sports') field for user ('michelon'):
UPDATE demo1 SET sports=3 WHERE login='michelon';
Update of two fields ('love' et 'leisure') :
UPDATE demo1 SET love=5, leisure=4 WHERE login='michelon';
Update with some math (add 3 to sports)
UPDATE demo1 SET sports=sports+3 WHERE login='test2'
If you only want to update a precise record:
- Always use the "primary key" !!
- You can't rely on names and such. In the above example 'michelon' is a primary key....
In order to update all records (be careful)
UPDATE demo1 SET love=4;
Killing a record
- To kill all lines (be careful, this will destroy all data in a table !)
DELETE FROM people;
- To kill a single line using a primary key:
DELETE FROM people WHERE Id=1;
Modification or deletion of a table
Note: To do this, you need special administrators rights over the database or the table.
Destruction of a table
- Think, before you do this ....
Syntax: DROP TABLE [IF EXISTS] table
Examples
DROP TABLE demo2
DROP TABLE IF EXISTS demo2
Changing the structure of a table
See the manual for details
- ALTER TABLE table ......
To add a column:
- ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
ex: ALTER TABLE demo2 ADD COLUMN fun int(11) DEFAULT '0' NOT NULL AFTER love;
To kill a column:
- DROP [COLUMN] column_name
ex: ALTER TABLE demo2 DROP fun;
Relations between tables
This is a more difficult chapter. We just provide some basics here !
Usually databases contain several tables and that are related. Each table represents an entity and its columns represent attributes.
Usually, databases contain several tables and that are related through use of keys. Each table represents an entity, (i.e. a thing) and its columns represent its attributes or properties. The tricky thing is to figure what is "entity" (i.e. a table) and what is a property (i.e. a table column or field). If properties can be shared with others, or if one of a kind can be multiple, then they should be modeled as a kind of entity, i.e. a new table. E.g. in a students table, one could add some columns that describe the school he attends, but since other students may attend the same school, one rather would create a new table describing schools using various properties. One then could just insert a number in the students table that represents the number of the school in the schools table.
Remark: Serious database designers may model these entities and relationships before they start thinking about tables. Something that we will not really do here (see further below for a few hints).
1-N relations
Frequently, relations are of type “1-to-N”. In this case one entity relates to 1 or more entities of a different kind.
Here is a simple example: A simple application to register exercise grades for a group of students. Each student can do between 0 and N exercises.
- We use 2 tables: One to register students and the other one for the exercises
- Each student ("1") can turn in several exercises ("N")
- The primary key is on the “1” side ("id" in the "student" table)
- and it is inserted on the “N” as so-called foreign key. ("student_id" in the exercise table)
student.id(primary key) corresponds toexercise.student_id(foreign key)
Below we present two versions. A first version without SQL foreign keys definitions, to be used with the fast MyISAM engine.
File student_exercise.sql - version without foreign keys
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS exercise;
CREATE TABLE student (
id int(10) NOT NULL auto_increment,
name varchar(40) DEFAULT '' NOT NULL,
first_name varchar(40) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO student VALUES (NULL,'Testeur','Bill');
INSERT INTO student VALUES (NULL,'Testeur','Joe');
INSERT INTO student VALUES (NULL,'Testeuse','Sophie');
CREATE TABLE exercise (
id int(10) NOT NULL auto_increment,
title varchar(40) DEFAULT '' NOT NULL,
student_id int(10) NOT NULL,
comments varchar(128),
url varchar(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id),
KEY student_id (student_id)
);
INSERT INTO exercise VALUES (NULL,"exercise 1",'1',"pas de commentaire",'http://tecfa.unige.ch/');
INSERT INTO exercise VALUES (NULL,"exercise 2",'1',"pas de commentaire",'http://tecfa.unige.ch/');
- Playing with this example
You can copy/paste SQL instructions into any kind of SQL tool, such as the popular PhPMySQL web application. In other words, you just can copy/paste the code above. Else, see the command line interface explained below.
Some queries:
List exercises turned in for all students
select * FROM student,exercise WHERE student.id = exercise.student_id;
List only a few columns:
select student.name, student.first_name, exercise.title, exercise.url FROM student,exercise WHERE student.id = exercise.student_id;
Result:
+---------+------------+------------+------------------------+ | name | first_name | title | url | +---------+------------+------------+------------------------+ | Testeur | Bill | exercise 1 | http://tecfa.unige.ch/ | | Testeur | Bill | exercise 2 | http://tecfa.unige.ch/ | +---------+------------+------------+------------------------+
'File student_exercise.sql - with foreign keys
-- MySQL example with foreign keys.
-- Needs the InnoDB engine (see the table definitions)
--
-- Table 'student'
--
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student (
id int(10) NOT NULL AUTO_INCREMENT,
name char(40) NOT NULL DEFAULT '',
first_name char(40) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Data for table 'student'
--
INSERT INTO student (id, name, first_name) VALUES
(1, 'Testeur', 'Bill'),
(2, 'Testeur', 'Joe'),
(3, 'Testeuse', 'Sophie'),
(4, 'Player', 'Jim');
--
-- Table 'exercise'
--
DROP TABLE IF EXISTS exercise;
CREATE TABLE IF NOT EXISTS exercise (
id int(10) NOT NULL AUTO_INCREMENT,
title char(40) NOT NULL DEFAULT '',
student_id int(10) NOT NULL,
comments varchar(128) DEFAULT NULL,
url char(60) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY student_id (student_id),
FOREIGN KEY (student_id) REFERENCES student(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table 'exercise'
--
INSERT INTO exercise (id, title, student_id, comments, url) VALUES
(1, 'Exercise 1', 1, 'small comment', 'http://tecfa.unige.ch/'),
(2, 'Exercise 2', 1, 'no comment', 'http://tecfa.unige.ch/'),
(3, 'exe four', 2, 'No comment', 'http://localhost/'),
(4, 'exe four', 2, 'No comment', 'http://localhost/');
Exercise (for readers that are a little bit familiar with PHP):
- Download the SQL Maestro PHP Generator (A feature restricted version is free)
- Generate the code for a PHP interface. You will get better results if you declare foreign keys.
N-N relations
Let’s look at the same example. Some may argue that this model is not good enough, since some students could do exercises together. In that case we would need three tables: one that includes student information, one that defines exercises and one that relates the two:
Modeling relations between entities
It may be a good idea to represent graphically relationships between entities before you start coding SQL. Before we show a few ways to draw these relationships, let’s quote from Wikipedia's Entity relationship model article:
An entity may be defined as a thing which is recognized as being capable of an independent existence and which can be uniquely identified. An entity is an abstraction from the complexities of some domain. When we speak of an entity we normally speak of some aspect of the real world which can be distinguished from other aspects of the real world. An entity may be a physical object such as a house or a car, an event such as a house sale or a car service, or a concept such as a customer transaction or order.
Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical theorem. Entities are represented as rectangles. A relationship captures how two or more entities are related to one another. Relationships can be thought of as verbs, linking two or more nouns. Examples: an owns relationship between a company and a computer, a supervises relationship between an employee and a department, a performs relationship between an artist and a song, a proved relationship between a mathematician and a theorem. Relationships are represented as diamonds, connected by lines to each of the entities in the relationship.
Entities and relationships can both have attributes. Examples: an employee entity might have a Social Security Number (SSN) attribute; the proved relationship may have a date attribute. Attributes are represented as ellipses connected to their owning entity sets by a line.
Every entity must have a minimal set of uniquely identifying attributes, which is called the entity's primary key.(retrieved, jan 2010, rearranged, some contents omitted)
A real world example - Wordpress
“The diagram below provides a visual overview of the Wordpress database and the relations between the tables (WP Version 2.5+) created during the WordPress standard installation. The Table Overview below includes additional details on the tables and columns.” (Database Description, Wordpress Codex, retrieved 20:46, 26 January 2010 (UTC).
Look up the Wordpress installation and configuration article if you want to see the SQL definitions for all the tables plus a different diagram made by someone else.
Permissions - Grants
Standard rights
In an RDBMS you can assign different rights to different users for each database or even each table. In most contexts we deal with in educational technology and other simple web applications, it's enough to define rights at database level (not at table level)
Most often you assign these rights through the database administration interface.
Types of rights
- Read Data (SELECT)
- Write Data (INSERT, UPDATE, DELETE) records
- Structure Administration (CREATE, DROP, ALTER) of tables
- Database administration (GRANT, SUPER, RELOAD, SHUTDOWN etc ....)
Typically, to install web applications, a database user must have the first three types of rights, to use an application the first two are enough.
SQL statements:
GRANT SELECT, UPDATE ON my_table TO some_user, another_user REVOKE ....
- See manuals, as we said, you usually do this through the admin interfaces at the database (and not table) label.
Other rights
By default, mysql either cannot write to files or only to a given directory.
E.g. under Ubuntu 16.x / Mysql 14.x (5.7.x), the following does work, if you run the mysql client as root
USE phpwiki14; SELECT content FROM `version` WHERE id = 5265 into outfile '/var/lib/mysql-file/t2.text' lines terminated by '\n$_version_$\n';
Using MySQL
MySQL is a very popular RDBMS tool and it is free.
The easiest way to install MySQL is:
- using a package manager under Linux to install MySQL (and other needed LAMP elements.
- installing a WAMP combo under Windows.
In addition you should install a MySQL/database management tool so that you can play around with SQL commands.
- A good choice is phpMyAdmin and it is usually included in a WAMP package, i.e. you don't need to download/install it apart.
Command line use of MySQL
In case you like it “the old way”, you can interact with MySQL through the command line. Actually sometimes you must, e.g. when you move really large databases from one system to another. Web-based tools do have limitations regarding file size for upload/download !
- Connection to a MySQL server
mysql -u user -p [data_base]
- Connection to a MySQL server on a different machine
mysql -h host_machine -u user -p [data_base] -h: type the name of the server (if needed) -u: MySQL user (not the unix login !) -p: will prompt for a password
Remember that all SQL instructions must be separated by ";" (!)
Example:
mysql -h myserver -u schneide -p Enter password: ********
- use/change of database (USE)
mysql> USE demo;
or alternatively:
mysql -u user -p demo
- List tables (SHOW)
mysql> SHOW TABLES; +----------------+ | Tables in demo | +----------------+ | demo1 | | test |
- Describe structure of a table (DESCRIBE)
mysql> DESCRIBE demo1; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(10) | | PRI | 0 | auto_increment | | login | varchar(10) | | MUL | | | | password | varchar(100) | YES | | NULL | | | fullname | varchar(40) | | | | | | url | varchar(60) | | | | | | food | int(11) | | | 0 | | | work | int(11) | | | 0 | | | love | int(11) | | | 0 | | | leisure | int(11) | | | 0 | | | sports | int(11) | | | 0 | | +----------+--------------+------+-----+---------+----------------+
Under Windows, using the command line tool can be more difficult. Firstly the terminal window is called command prompt or Power Shell (Vista); you can get it with the following procedure:
- click Start, click Run, type cmd, and then click OK.
However, depending on your WAMP installation, Windows may not recognize the "mysql" command and you must redefine the PATH environment variable (read the Setting a value in windows section of the Environment variable article.
- Using the command line tool to read SQL files
In your Unix (or well installed Windows) terminal type:
mysql -u user -p demo < test.sql
Under Windows - if didn't redefine the PATH - you may have to type the full path, e.g.
c:\wamp\mysql\bin\mysql.exe -u user -p demo < test.sql
- Content of file test.sql is piped into SQL
- Don't forget to include the name of the database (“demo” in the above example) !
Note: if a table already exists you can't define a new one with the same name. Kill it with “DROP TABLE if exists” (or use ALTER to change its structure)
DROP TABLE [IF EXISTS] table Ex: DROP TABLE demo2 Ex: DROP TABLE if exists demo4
- Backups
If you “dump” a database you will create all the necessary SQL instructions to create it again (including all the INSERTs)
Use of the 'mysqldump' utility:
Ex: mysqldump -u schneide -p demo > save.mysql
- List database, tables, etc. explained with a few examples
Note: You may not be allowed to list all databases...
List all databases on the same machine or a server:
mysqlshow -u vivian -p mysqlshow -h yourhost.org -u vivian -p
List tables of a database
mysqlshow -u vivian -p data_base_name
List table definition
mysqlshow -h yourhost.org -u vivian -p vivian test
MySQL with the phpMyAdmin application
PhpMyAdmin is the most popular web-based MySQL administration tool. Below is a synopsis of operations. Since this is very popular tool under active development, the interface changes quite often, i.e. our screen captures may be outdated ...
- Database creation
- Fill in the create new database field with a database name and hit return or click on "Create"
- Database User creation
- Click on the home button
- Click on privileges
- Click on "add new user"
- Rights
To give a user full rights on a database (something that you will need to do when you install portalware.
- Click on the home button
- Click on privileges
- Then edit priviledges of your new user
- Check most boxes (adminstration is not needed)
- Create a user + a database for the user
- The faster way to create a database plus a user with full rights is to create the database and then tick "create a user with the same name".
- Database selection
- Select your database from the pull-down menu to the left
- Table names are shown below
- The main window allows you to make changes in tables and also to execute general SQL queries.
- Create tables with the online form
- There is a table creation tool
- However, we suggest to create tables with SQL instructions. This way you will have a trace.
- Create tables from an SQL instructions file
- Click on the SQL (in the menu bar). Now you can either:
- import a file with SQL instructions
- Copy/paste SQL instructions
- Other features
- You can
- create and destroy tables (if you have appropriate user rights)
- create, modify table definitions
- view and edit tables (records)
Links
- Other tutorials
See the MySQL article. It points to other (more complete) MySQL tutorials.
- Example - wordpress
- DataBase Description (codex.wordpress.org)
- Wordpress Database Schema by Jaymin Patel (2008) Useful to see another kind of drawing...