SQL Tutorial

1
SQL Tutorial

• MIE456 - Information Systems Infrastructure II
• Vinod Muthusamy
• October 28, 2004

2
Relational database

• Structured information stored in tables
• Well defined types
• Column different attributes of an entity
• Row one entity
• Primary key unique value in a column

3
Basic operations

• Create table
• Insert new data
• Retrieve data
• Change existing data
• Delete data

4
Create table

• CREATE TABLE IF NOT EXISTS users( userid
  VARCHAR(8) NOT NULL, firstname
  VARCHAR(20), lastname VARCHAR(20), password
  VARCHAR(8) NOT NULL, type INTEGER, PRIMARY
  KEY(userid) )

5
Insert new data

• INSERT INTO users VALUES('Bob', 'Bob', 'King',
  'cat', 0)

6
Retrieve data

• SELECT lastname FROM users WHERE userid
  'Bob
• ? returns King

7
Change existing data

• UPDATE users SET lastname 'Queen' WHERE
  userid 'Bob

8
Delete data

• DELETE FROM users WHERE userid 'Bob

9
MySQL

• To run MySQL locally
• Download from http//dev.mysql.com/downloads/
• Start MySQL server (default userroot, no
  password)
• start \bin\mysqld-nt
• Start MySQL client
• \bin\mysql -u root
• Exit MySQL client
• quit
• Stop MySQL server
• \bin\mysqladmin -u root shutdown
• Use MySQL Query Browser to interactively test SQL
  statements in a GUI
• http//dev.mysql.com/downloads/query-browser/