22.02.14 - SQL

SQL

SQL is a language based on the relational model

• Implementation is provided by a DBMS

SQL Case

Written in BOLD COURIER FONT SQL keywords are not case-sensitive Table names, column names are case sensitive ANSI standard for strings are single quotes

Relations	E/R Diagrams	SQL
Relation	Entity	Table
Tuple	Instance	Row
Attribute	Attribute	Column or field
Foreign Key	M:1 Relationship	Foreign Key
Primary Key	Attribute	Primary Key

Implementing E/R Diagrams

• Entities become SQL tables
• Attributes of an entry become columns
• Can approximate the domains of the attributes by assigning types to each column
• Relationships may be represented by foreign keys

Relationships

Represented in SQL using Foreign Keys

• 1:1 are not used, or can be treated as a special case of M:1
• M:1 are represented as a foreign key from the M-side to the 1
• M:M are split into two M:1 relationships

Foreign Keys

Defined as constraints, need to provide:

• Columns which make up the foreign key
• The reference table
• The columns which are referenced by the foreign key

Primary Keys

Defined through a constraint Automatically adds UNIQUE and NOT NULL to relevant column definition

Storage Engines

• Can specify the engine used to store files onto disk
• Type of storage engine will have a large effect on the operation of the database
• Engine should be specified when a table is created Storage Engines:
• MyISAM - Default, very fast. Ignores all foreign key constraints
• InnoDB - Offers transactions and foreign keys (We use this one)
• Memory - Stored in RAM

Deleting Tables

Can delete table by using DROP Need to be extremely careful, all rows in the table will be deleted and wont normally be asked to confirm deletion

Changing Tables

Sometimes want to change the structure of an existing table

• Could drop the table but its better to ALTER TABLE instead as no data would be lost

• ALTER TABLE can:

  • Add/remove columns
  • Add/remove existing constraints
  • Change column name or definition

• INSERT - add a new row to a table

  • Inserts rows into the database with the specified values
  • Number of columns and values must be the same

• UPDATE - change row(s) in a table

  • Changes values in specified rows based on WHERE conditions
  • If no condition is given all rows are changed

• DELETE - Remove row(s) from a table

  • Removes all rows, or those which satisfy a condition. If no condition given, ALL rows deleted.

• Should use WHERE to ensure only the required data is deleted, not lots of it

Select

The type of query you will use most often Queries one or more tables and returns the result as a table Queries can be achieved in a number of ways