Transcript INTRODUCTION TO SQL

DAT702


Standard Query Language
Ability to access and manipulate databases
◦ Retrieve data
◦ Insert, delete, update records
◦ Create and set permissions
 databases, tables, procedures and views
◦ Query databases

ULI101
◦ HTML
◦ Create a webpage

INT213
◦ ASP
◦ Connect to database to make webpage functional

DAT702
◦ SQL
◦ Manipulate data in database connected to webpage

Relational Database Management System
◦ SQL, MS Access, Oracle, IBM DB2, SQL Server


Data is stored in database objects called
tables
A table is a collection of related data
organized into columns (fields) and rows
(records)


Defined by SQL
Many are mandatory, but most are optional
SELECT name FROM teams WHERE id = 9


SELECT and FROM are mandatory
WHERE is optional

Names that are given to database objects
such as tables and columns
SELECT name FROM teams WHERE id = 9


“teams” is the table name
“name” and “id” are column names

Literals that represent fixed values
SELECT name FROM teams WHERE id = 9

“9” is a numeric constant

A portion of an SQL statement

The SELECT clause is “SELECT from”

The FROM clause is “FROM teams”

The WHERE clause is “WHERE id = 9”



How the clause is put together
What keywords, identifiers and constants
does it consist of
MOST IMPORTANTLY – are they in the correct
order according to SQL


Used to manage database objects such as
tables and columns
CREATE, ALTER and DROP
mysql> create table teams (id int(5) not null primary key, name
varchar(37) not null, division varchar(2));
Query OK, 0 rows affected (0.05 sec)
mysql> create table teams (id int(5) not null
primary key, name varchar(37) not null,
division varchar(2));
Query OK, 0 rows affected (0.05 sec)
+------------+-------------+-----+------+---------+-------+
| Field
| Type
| Null | Key
| Default
| Extra
|
+------------+-------------+-----+------+---------+-------+
| id
| int(5)
| NO
| PRI
| NULL
|
|
| name
| varchar(37)
| NO
|
| NULL
|
|
| division
| varchar(3)
| YES
|
| NULL
|
|
+------------+-------------+-----+------+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE teams DROP COLUMN
division;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
Check to make sure
the column no longer exists
mysql> DROP TABLE teams;
Query OK, 0 rows affected (0.03 sec)
Check to make sure
the table no longer exists

Used to manage data within tables and
columns

INSERT, UPDATE and DELETE

(RE-CREATE THE TEAMS TABLE)


mysql> insert into teams values (‘12345',
‘Toronto', ‘NE');
Query OK, 1 row affected (0.02 sec)


UPDATE teams SET name = ‘TO‘ WHERE name
= ‘Toronto';
Query OK, 1 row affected (0.02 sec)

DELETE FROM teams WHERE name = ‘TO';

Query OK, 1 row affected (0.02 sec)

load data local infile 'test.txt' into table
TEAMS lines terminated by '\r\n';