Module INST2005 Database Systems SQL Structured Query Language

1
Module INST2005Database Systems SQL
Structured Query Language

• Andy Dawson
• Department of Information Studies, UCL

2
What were going to be looking at today

• SQL!
• What is it?
• How does it work?
• (in very basic fashion only!)
• Next 3 weeks MySQL practicals in HM1

3
What is SQL?

• A standard language for relational systems
• Originally a proprietary language developed by
  IBM in the 70s (Structured Query Language or
  Sequel)
• Now an international standard
• Supported to some extent by the great majority of
  database products

4
What is SQL?

• Originally intended to be a data sublanguage
• Effectively a programming language for defining
  and manipulating databases
• A very long and complex standard
• Generally not fully implemented by individual
  applications
• May be supplemented by proprietary extensions
• Nevertheless a very sound, de facto basis

5
What is SQL

• Interestingly SQL never mentions the word
  Database!
• SQL commands define and manipulate data
• An SQL Catalog is a collection of descriptors,
  and effectively the definition of the database
• In simple terms, SQL allows us to put data into
  tables and then view those tables and/or
  subsets/manipulations of them

6
MySQL

• Many proprietary (and open source) forms of SQL
• We will use MySQL 5 as our example and working
  standard
• MySQL is commonly available on public ISPs and is
  available on DISs own servers

7
MySQL

• Freely downloadable as open source from
  http//dev.mysql.com/downloads/mysql/5.1.htm
• Also commercial enterprise versions etc from
  http//www.mysql.com/(and much other
  information!)
• We will only be doing some very basic things with
  MySQL theres much more to it !

8
How does SQL work?

• Set up as a server on the host machine
• SQL functions as (R)DBMS
• Data is also held on the server
• Accounts set up to segregate user data
• Commands need to be sent to the server to be
  processed

9
How does SQL work?

• Server can be accessed and instructions given
  directly via a comms program e.g. Putty(this is
  how we will use it)
• Can also be done controlled via scripts, e.g.
  using PHP for the web

10
The MySQL Monitor

• MySQL has a text-based interface the MySQL
  Monitor
• Runs in a (Unix) terminal window
• Provides direct connection to a MySQL server
• Allows you to define structures, input/upload
  data, run queries and view results

11
For those unfamiliar with Unix

• Why Unix and not Windows?
• Need to run our own servers due to IS
  restrictions
• Open source software
• Unix much more robust and easier to maintain
• CLI based which is tricky as unfortunately
  most of you are not familiar with it!
• and its not as user-friendly as Windows
• but it is more powerful and flexible.

12
Some common (and potentially useful) Unix commands
13
Some common (and potentially useful) Unix commands
14
Getting started with MySQL

• (To do this in practice, use the personal sheet
  you will be given with your own individual
  details)
• Establish connection with server and login
• Our server is located at www.imb.ucl.ac.uk
• We will use Putty as our comms program
• Log into MySQL
• You can then look at what databases (tables)
  exist, load them for use and/or create your own

15
Some notes on structure within MySQL

• You will all have a personal database
  (technically a schema within the catalog!)
• You can set up various tables within your
  database
• These tables can be linked together as long as
  they have common fields
• Entry of new databases is restricted, but you
  can create multiple table sets within your space

16
The MySQL command line

• Prompt takes the form mysqlgt
• Type in your commands at the prompt
• Commands can go over more than one line
• Must be terminated with a semicolon

17
Initial MySQL commands

• SHOW DATABASES
• USE database
• SHOW TABLES
• DESCRIBE table
• CREATE, LOAD DATA, INSERT
• SELECT, FROM, WHERE

18
Creating a table in MySQL

• To set up a structure (table) to populate we use
  CREATE TABLE tablename (fieldname-1 type
  (length), fieldname-n type (length) )
• Table can contain any number of fields
• Use multi-line input and/or paste from elsewhere
  (notepad) for complex structures

19
Identifying keys

• Type can be identified as keys, or specified
  separately by PRIMARY KEY (field)
• Composite keys can be identified by including
  multiple fields separated by commas
• Auto-incrementing fields can be set up to provide
  numbering for surrogate keys, e.g.idnumber
  INTEGER (4) PRIMARY KEY AUTOINCREMENT

20
Data typing

• Data types (and lengths) must be defined for all
  fields in the table structure
• Very wide range of data types available see
  chapter 10 of the reference manual for list
• Numeric types, e.g. INT, FLOAT, DOUBLE
• Date/Time types, e.g. DATE, TIME, YEAR
• String types, e.g. CHAR, VARCHAR

21
Inputting data

• Data can be added to tables in a number of ways
• One record at a time using direct input
• In bulk by uploading structured data from an
  existing source file
• NB structure of such a file must match the
  structure of the table being added to!

22
Adding a row

• Use INSERT INTO table VALUES (value-1,
  value-n)
• NB values must match number and type of table
  definition, although NULL values can be included
  (no quotes)

23
Importing data

• UseLOAD DATA LOCAL INFILE sourcefile INTO
  TABLE tableFIELDS TERMINATED BY delimiter
• Assuming source in your root, e.g. /file.txt
• Delimiter as present in file, e.g. ,

24
Correcting an entry

• Entries can be changed using UPDATE table SET
  fieldvalue WHERE condition
• NB care when selecting condition (fieldx )!
• Can be easier to edit dataset and reload edited
  version as a replacement
• DELETE FROM table
• then LOAD DATA

25
Deleting a row

• UseDELETE FROM table WHERE condition
• As with correction, be careful in selecting your
  condition!
• Similarly, may be safer to do it in source and
  reload all the data.

26
Interrogating your data

• Once youve established a structure and populated
  it with data, you can retrieve from it
• An SQL statement which does this is called a
  Query (surprise!)
• Querys can be simple or very complicated, and can
  exploit the relational nature of data stored, and
  incorporate processing
• SQL Querys can also incorporate many of the
  search features we looked at last week

27
SELECT, FROM, WHERE

• A basic SQL Query has three key parameters
• SELECT what elements, i.e. which fields to show
• FROM what datasets, i.e. which tables to search
• WHERE condition, i.e. which values match

28
SELECT, FROM, WHERE

• E.g.
• SELECT from pets
• Returns any (all) fields from the table PETS with
  no conditions, i.e. shows all records
• SELECT name from pets where sexf
• Returns the name field from all the records in
  the table PETS where the field SEX contains the
  value f, i.e. list all female pets names

29
Pattern matching

• Two options exist for text pattern matching
• Underscore (_) is used for single characters
• Percent () is used for any number of
  characters (including zero)
• Both are incorporated into SELECT statements
  using LIKE or NOT LIKE rather than or ltgt

30
Pattern matching

• E.g. SELECT FROM table
• WHERE field LIKE b returns all records
  beginning with b
• WHERE field NOT LIKE b returns all entries
  not beginning with b
• NB by default SQL pattern matching is case
  insensitive.

31
Counting occurrences in MySQL

• To find how often values occur in a table, we can
  use COUNT and GROUP BY e.g.
• SELECT COUNT () FROM tablereturns the number
  of records (rows) in table
• SELECT field COUNT () FROM table GROUP BY
  fieldreturns a list of values found in field
  together with a count of how often each is present

32
Using more than one table

• Data from more than one table can be combined in
  a query (SELECT statement)
• This enables us to exploit the relational nature
  of a multi-table set of data
• Information from different tables can be joined
  by matching some aspect of the data in the
  separate tables, e.g. by their having a common
  field

33
Using more than one table

• The structure of the statement isFROM table1
  JOIN table2ON (table1.field1 table2.field2)
• The JOIN links the tables, the ON shows which
  fields match up
• Always specify table field you are referring to
  ( table.field) as different tables may have
  fields of the same name!

34
Different kinds of JOIN

• The tutorial uses examples with INSIDE JOIN
• Other forms of JOIN (e.g.OUTSIDE, LEFT) also
  exist
• Dont worry about the differences for now!
• The syntax is a little complex, see section
  3A.3.4.9 in the tutorial practical for examples
• You can actually substitute JOIN for INSIDE JOIN
  in most cases the latter is a subtype

35
JOINing a table to itself

• You can also use JOIN to compare records in a
  table to others in the same table
• This requires the generation of aliases which
  represent the different copies of the table,
  e.g.SELECT p1.field, p2.fieldFROM table AS p1
  JOIN table AS p2(Again, see full example in the
  tutorial)

36
Other aspects of MySQL

• are many and varied!
• We have only scratched the surface of basic input
  and output here
• Many more complex aspects of every kind of
  operation are possible
• Think of SQL as a programming language to build
  database management systems
• You will have the chance to experiment with it in
  the next few weeks practical sessions

37
For the practical sessions.

• Rob Miller has customised a tutorial for you
  athttp//www.ucl.ac.uk/infostudies/rob-miller/lec
  tures/INSTG033/refman-5.0-en.html-chapter/tutorial
  _imb.html
• You should start on this today and work through
  it in the next few weeks
• We will demo some tips and hints to get you
  started at the beginning of todays session

38
Getting help in MySQL

• sqlzoo.net/ ( reassuringly titled A gentle
  introduction to SQL ? ) - packed with tutorials,
  help and reference material!
• For quick ref, MySQL also has a standard help
  function accessed by typing help at the mysqlgt
  prompt
• This is hierarchical so you can specify
  commands/subsections (e.g. help select)

39
Thats it for today!

• Any questions?
• See you for the practical in HM1!