SQL and MySQL tutorial

<pageby nominor="false" comments="false"/>

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

• PHP-MySQL tutorial -basics
• See web technology tutorials

Level and target population

• Beginners

Remarks

• This is an first version made from teaching slides ...

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

SQL language elements that compose a single statement. (author: [1]

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)

http://tecfa.unige.ch/guides/php/examples/mysql-demo/main.html

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.

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

Operator	explanation
simple comparison operators
=	equal
<> or !=	not equal
<	Less Than
>	Greater Than
<=	Less Than or Equal To
>=	Greater Than or Equal To
combination operators
AND	both propositions need to be true
OR	one proposition needs to be true
special operators
expr IN (..., ...)	value is in a list
expr NOT IN (..., ..., ...)	not in a list ....
expr BETWEEN min AND max	value is between
expr NOT BETWEEN ...	no in between ....
comparison operators for strings
expr1 LIKE expr2	x is like y wildcards: %=several characters, _=1 char
expr NOT LIKE expr2	not like...
expr REGEXP pattern	x is like (using regular expressions)
expr NOT REGEXP pattern	not like
STRCMP(exp1, exp2)	string comparison (C/PHP like).
Control flow
IF (expr1, expr2, expr3)	If expr1 is true, return expr2, else expr3
IfNull (expr1, expr2)	If expr1 is true, return expr1, else expr2
Mathematical functions
see the manual ...

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

Type	explanation	range	SQL example	example data
NUMBERS
TinyInt[(M)][UNSIGNED] [ZEROFILL]	tiny integer	-128 to 127 SIGNED or 0 to 255 UNSIGNED	TinyInt(2)	2
SmallInT[(M)]...	small integer	-32768 to 32767 SIGNED or 0 to 64K UNSIGNED		20001
MediumINT[(M)]...	integer	-8388608 to 8388607 or 0 to 16777215		-234567
INT[(M)] ...	integer	-2147483648 to 2147483647 or 0 to 4294967295.
BigINT[(M)]...	big integer	63bits
FLOAT[(M,D)]...	floating point	-3.402823466E+38 to to -1.175494351E-38	float (5.2)	12.3
DOUBLE[(M,D)]...	big floating point
DATES
DATE	date	YYYY-MM-DD	3000-12-31
DateTime		YYYY-MM-DD HH:MM:SS
TimeStamp[(M)]
TIME
YEAR
STRINGS
Char(M) [character set charset_name]	fixed-length string (makes comparison difficult) case insensitive (except binary)	M = 1 to 255 chars	char(4)	'ab '
VarChar(M)[character set charset_name]	variable length string	M = 1 to 255 chars (up to 65535 in MysQL 5.03)	login(8) character set binary	'schneide'
Texts and blobs
BINARY(M)	fixed-length bit string
VARBINARY(M)	variable-length binary. Equivalent to VARCHAR (M) CHARACTER SET BINARY
TINYBLOB	small binary texts	255 chars
BLOB		65535 chars
MEDIUMBLOB		16777215 chars
BLOB	big binary text	4294967295 chars
TINYTEXT	small texts	255 simple onebyte(!) chars
TEXT		65535 chars
MEDIUMTEXT		16777215 chars
LONGTEXT	big text	4294967295 chars
Enumeration
Enum('val1', 'val2',...)	member of a list of strings or NULL	65535 distinct values	'toto'
Set('val1', 'val2', ...)	on or more strings	64 members	('toto', 'blurp')

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 Jermann','http://tecfa.unige.ch/',1,2,1,3,4)
 INSERT INTO demo1 VALUES (5,'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....

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

• 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 corresponds to exercise.student_id

Below we present two versions. A version without foreign keys to be used with the fast MyISAM engine. File student_exercise.sql (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)

Modeling relations between entities - example notations

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

Wordpress 2.7 Entity-relationship diagram. Source: Database Description

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

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.

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"

Create a new database

Database User creation

• Click on the home button
• Click on privileges
• Click on "add new user"

Add a 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)

Change user privileges

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...

---