SQL and MySQL tutorial

The educational technology and digital learning wiki
Jump to navigation Jump to search

This article or section is currently under construction

In principle, someone is working on it and there should be a better version in a not so distant future.
If you want to modify this page, please discuss it with the person working on it (see the "history")

Really! ... ignore for now please

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

Introduction

Learning goals
  • Learn some introductory relational database principles
  • Learn the most important SQL commands
Prerequisites
  • None
  • But being able to install a AMP combo on your computer should help.
Moving on
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).
Usually, the first field is used to insert a unique identifier for a record.
Some tables include relations (i.e a column includes an identifier that corresponds to an identifier in an other table)

Fields contain different data types

E.g. integer numbers (int) or character strings (char).

Information retrieval from a table

To retrieve or update information in a table, you need:

the name ame of the table
Names of columns and search criteria, e.g. "I want all records from table test where age > 20".

We shall see how this is formally done.

The SQL language

Most relational databases are implement with a Relational Database Management System (RDBMS) using SQL, the Structured Query Language. SQL is a language to request data from a database, to add, update, or remove data within a database, or to manipulate the metadata of the database.

SQL is a sort of database programming language that allows you to

formulate queries, i.e. find stuff (SELECT)
manipulate records (UPDATE, INSERT, DELETE)
define tables and columns, as well as redefine and remove (CREATE, ALTER, DROP)
define access rights to database users (GRANT, REVOKE)

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:
  • Clauses, which are in some cases optional, constituent components of statements and queries.[1]
  • Expressions which can produce either scalar values or tables consisting of columns and rows of data.
  • Predicates which specify conditions that can be evaluated to SQL three-valued logic (3VL) Boolean 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.
  • 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.
  • Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.

Let's now have a look at the definition of simple SQL table

http://tecfa.unige.ch/guides/php/examples/mysql-demo/main.html (access restricted for the moment)
Database: demo  Table: demo1  Rows: 1
+----------+-----------+------+-----+---------+----------------+
| Field    | Type      | Null | Key | Default | Extra          |
+----------+-----------+------+-----+---------+----------------+
| id       | int(10)   |      | PRI | 0       | auto_increment |
| login    | char(10)  |      | MUL |         |                |
| password | char(100) | YES  |     |         |                |
| fullname | char(40)  |      |     |         |                |
| url      | char(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 datatype, e.g. "int(10)". We shall come back later to table definition and datatype issues.

Retrieving data

Let's now see how we can retrieve data from a table with an SQL command.

Simple queries (selection)

SELECT allows to retrieve records from one or more tables:

Here is a rough summary of its 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 ....
opérateurs de comparaison pour strings seulement
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 regula 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, sinon expr3
IfNull (expr1, expr2)
Si expr1 est vraie, return expr1, sinon expr2
Mathematical functions
see the manual ...

Notes:

Priorities in a longer expression: use parenthesis 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.*’;

Result: 

+--------+------------------+
| 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
  • Select in 2 tables, (see also Relational tables below)
 SELECT demo1.fullname FROM demo1, test WHERE demo1.login = test.login

+-------------+
| fullname    |
+-------------+
| Tester Test |

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

CREATE TABLE [IF NOT EXISTS] table_name (create_definition1,...) [table_options] [select_statement]

1create_definition:

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

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

Data types

Not all RDMS implement all data types, MySQL implements the most important ones.

Strings
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
ZEROFILL : inserts 0s, ex. 0004)

Optional parameters (see next slides)

M : display size
D (floating point numbers): digits after the "."

The NULL value

Values can be NULL (means “empty”, not zero or empty string "" !!)

Data types summary table

Type
explanation
range
example
NUMBERS
TinyInt[(M)][UNSIGNED] [ZEROFILL]
tiny integer
-128 à 127 (0 à 255)
TinyInt(2)
9
SmallInT[(M)]...
small integer
-32768 à 32767 (0 à 64K)
20001
MediumINT[(M)]...
integer
-8388608 to 8388607
-234567
INT[(M)] ...
integer
-2147483648 to 2147483647
BigINT[(M)]...
big integer
63bits
FLOAT(precision)
floating point
12.3
FLOAT[(M,D)]...
floating point
-3.402823466E+38
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) [binary]
fixed-length string
M = 1 à 225 chars
case insensitive (except binary)
char(4)'ab '
VarChar(M)[binary]
variable length string
M = 1 à 225 chars
login(8)[binary]
'schneide'
Texts and blobs
BINARY(M)
fixed-length binary
VARBINARY(M)
variable-length binary
TINYBLOB
small binary texts
255 chars
BLOB
65535 chars
MEDIUMBLOB
16777215 chars
BLOB
big binary text
4294967295 chars
TINYTEXT
small texts
255 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 regard a BLOB column as a 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

Simple column keys (KEY)

Indexed columns will improve database performance
Each table can include 16 indexed columns
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
KEY index_name (col_name)
KEY index_name (char_col_name(M))
Note: INDEX 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
PRIMARY KEY (index_col_name, index_col_name)
id int(10) NOT NULL auto_increment,
PRIMARY KEY (id),

Definition of fields

Note: See the complete example in section Table creation (CREATE)

Columns of demo1
  id int(10) NOT NULL auto_increment,
  login char(10) DEFAULT  NOT NULL,
  password char(100),
  url char(60) DEFAULT  NOT NULL,
  food int(11) DEFAULT '0' NOT NULL,

Minimalist definition of a column:

name type

Ex: id int

Some field types require length, e.g. VarChar and Char !! 

Ex: login varchar(10)

Typical definition of a column

name type (size) DEFAULT 'value_default' NOT NULL,

Ex: login char(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 char(10) DEFAULT '' NOT NULL,
   password char(100),
   fullname char(40) DEFAULT '' NOT NULL,
   url char(60) DEFAULT '' NOT NULL,
   food int(11) DEFAULT '0' NOT NULL,
   work int(11) DEFAULT '0' NOT NULL,
   love int(11) DEFAULT '0' NOT NULL,
   leisure int(11) DEFAULT '0' NOT NULL,
   sports int(11) DEFAULT '0' NOT NULL,
   PRIMARY KEY (id),
   KEY login (login)
 );

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 “

Relational 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

Most frequent relations are of type “1-to-N”. In this case:
The primary key is on the “1” side
and it is inserted on the “N” as so-called foreign key.

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 for the grades
Each student ("1") can turn in several exercises ("N")
exercise.student_id corresponds to student.id
[[Image:]]
File student_exercise.sql:
 DROP TABLE IF EXISTS student;
 DROP TABLE IF EXISTS exercise;

 CREATE TABLE student (
   id int(10) DEFAULT ’0’ NOT NULL auto_increment,
   name char(40) DEFAULT ’’ NOT NULL,
   first_name char(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) DEFAULT ’0’ NOT NULL auto_increment,
   title char(40) DEFAULT ’’ NOT NULL,
   student_id int(10) NOT NULL,
   comments varchar(128),
   url char(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 examples

You can copy/paste SQL instructions into any kind of SQL tool, such as the popular PhPMySQL web application. Alternatively, use the command line interpreter, for example: 

mysql -u schneide -p demo < student_exercise.sql

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;
+---------+------------+------------+------------------------+
| name    | first_name | title      | url                    |
+---------+------------+------------+------------------------+
| Testeur | Bill       | exercise 1 | http://tecfa.unige.ch/ |
| Testeur | Bill       | exercise 2 | http://tecfa.unige.ch/ |
+---------+------------+------------+------------------------+
Insertion and updates
Insert new records
INSERT allows to insert new lines (record) in one or more tables.
INSERTION of a complete new line:
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);
INSERTION of a new line but specifying only a few values.
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. In this case 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
Kill lines
To kill all lines (be careful !)
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 ....
DROP TABLE [IF EXISTS] table
ex: DROP TABLE demo2
ex: 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;
Permissions - Grants
In an RDBMS you can assign different rights to different users for each database or even each table.
In most context, 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...
Command line use of MySQL
in case you like it “the old way” ....
Command line interface
Remember that all SQL instructions must be separated by ";" (!!!)
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

mysql -h tecfasun5 -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    | char(10)  |      | MUL |         |                |
| password | char(100) | YES  |     | NULL    |                |
| fullname | char(40)  |      |     |         |                |
| url      | char(60)  |      |     |         |                |
| food     | int(11)   |      |     | 0       |                |
| work     | int(11)   |      |     | 0       |                |
| love     | int(11)   |      |     | 0       |                |
| leisure  | int(11)   |      |     | 0       |                |
| sports   | int(11)   |      |     | 0       |                |
+----------+-----------+------+-----+---------+----------------+

= Using the command line tool

Open a terminal window (command, cmd or whatever it is called). Then type: 

mysql -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 the ’mysqldump’ utility: 

Ex: mysqldump -u schneide -p demo > save.mysql
List database, tables, etc.

... 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 tecfa -u vivian -p

List tables of a database 

mysqlshow -u vivian -p data_base_name

List table definition 

mysqlshow -h tecfa -u vivian -p vivian test

MySQL with the phpMyAdmin application

phpMyAdmin is the most popular web-based MySQL administration tool

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:
importer 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)
  1. ↑ ANSI/ISO/IEC International Standard (IS). Database Language SQL—Part 2: Foundation (SQL/Foundation). 1999.
Retrieved from "https://edutechwiki.unige.ch/mediawiki/index.php?title=SQL_and_MySQL_tutorial&oldid=23020"