SQL Part 2

1
SQL Part 2

• Chris Bailey
• Bacterial Pathogenesis Genomics Unit
• cmb036_at_bham.ac.uk

2
From construction to destruction

• Create a database
• Creating tables
• Inserting data into tables
• Editing data in tables
• Deleting data from tables
• Deleting the table
• Deleting the database

3
Creating a database

• CREATE DATABASE IF NOT EXISTS database_name
• Creates a database with the name database_name
• Will return warning if IF NOT EXISTS is not
  used and the database already exists

4
Creating a table

• More complicated than for databases
• CREATE TABLE table_name
• (
• column definitions,
• index definitions
• ) table options

5
Creating a table

• Column definitions
• Have a general format
• col_name type options
• col_name may not be more than 64 chars long

6
Number data types

• Number data types
• May be signed or UNSIGNED
• TINYINT (-127 127 or 0 255)
• SMALLINT (-32768 32768 or 0 65536)
• MEDIUMINT (-223 223 or 0 224)
• INT (-231 231 or 0 232)
• BIGINT (-263 263 or 0 264)

7
Number data types

• FLOAT ( 1.175494351E-38 to 3.402823466E38)
• DOUBLE ( 2.2250738585072014E-308 to
  1.7976931348623157E308)

8
Date/time data types

• By convention dates are in YYYY-MM-DD, times in
  HHMMSS format
• DATE (1000-01-01 to 9999-12-31)
• TIME (-8385959 to 8385959)
• DATETIME ( 1000-01-01 000000 to
  9999-12-31 235959)
• YEAR(24) (70 to 69 (1970-2069) or
• 1901 to 2155)

9
TIMESTAMP data type

• Special form of data type
• automatically changed to current time when row is
  created/inserted
• Range is from 19700101000000 to sometime in the
  year 2037
• Since MySQL version 4.1 displayed in format
  YYYY-MM-DD HHMMSS rather than YYYYMMDDHHMMSS

10
String data types

• CHAR(M) VARCHAR(M)
• M is the maximum string length
• Maximum value of M is 255
• Comparisons are case sensitive
• Strings longer then M are chopped
• Strings less than M are space padded for CHAR only

11
String data types

• TINYTEXT (0-255 bytes/charaters)
• TEXT (0-65536 bytes)
• MEDIUMTEXT (0-166777215 bytes)
• LONGTEXT (0-4294967295 bytes)
• There also equivalent Binary Large OBject (BLOB)
  columns of the same size

12
TEXT Vs BLOB

• Text Charater string
• Blob Byte string
• Blobs have no character set, texts do
• Sorting is done according to byte value for blobs
  and character order in the character set
  associated with the column

13
Arrays

• ENUM and SET
• Eg. ENUM(Yes, No, Maybe)
• SET(val1, val2, val3)
• ENUM Use one of the set values
• SET Use 0 - N members of the set

14
Choosing a data type

• Want to combine two key (but contradictory)
  features
• Maximum speed minimum size
• Maximum flexibility
• Need to choose data types that are appropriate
  for the data being stored

15
Choosing a data type - examples

• Persons name Varchar(64)
• Protein sequence Text
• Chromosomal (DNA) sequence Longtext or Longblob
• Finished genome Enum (Y, N)

16
Making fast tables

• Use fixed width columns
• Char, Numbers, Enum set only
• Makes the record length fixed
• Choosing smaller column sizes
• Correct indexing

17
Indexing

• Like creating a phone book for your table
• Can have multiple indexes for one table,
• Each index may be made from
• Part of one column
• One column
• More than one column

18
Indexing

• Only index columns you will use to conditionally
  select or group tables
• E.g.
• In JOINS
• In WHERE clauses
• In GROUP BY clauses
• Having more columns indexed than you need will
  slow things down

19
Indexing

• Can also have unique indexes and primary keys
• Very similar Only allow unique values in a
  particular column
• Primary Key Only 1 per table
• Unique Indexes 1 or more per table

20
Example Indices

• INDEX colindex (col)
• INDEX name (forename,surname)
• INDEX description (description(100))
• FULLTEXT textindex (col1,col2)
• PRIMARY KEY (accession)
• UNIQUE KEY name (name)

21
FULLTEXT Searching

• Powerful tool for natural language searching
  within text columns
• Indexes designed to allow fast return of rows
  containing particular words/phrases
• Searching FULLTEXT style
• WHERE MATCH (columns) AGAINST (words)

22
Extra stuff for creating a column

• NULL / NOT NULL
• Allows / prevents columns from storing the
  special value NULL
• default value or default 352
• Specifies a default value to use if one is not
  explicitly chosen when a new row is created

23
Example create table

• CREATE TABLE article (
• pubmed varchar(12) NOT NULL default '',
• first_author varchar(50) NOT NULL default '',
• authors text NOT NULL,
• reference varchar(255) NOT NULL default '',
• year year(4) default NULL,
• title text NOT NULL,
• UNIQUE KEY pubmed (pubmed),
• KEY yearindex (year),
• KEY authorindex (authors(200))
• ) ENGINEMyISAM DEFAULT CHARSETlatin1

24
Copying tables

• CREATE TABLE new_table SELECT from
  old_table
• Creates a duplicate of old_table called new_table

25
Creating new data

• The INSERT command
• General form
• INSERT INTO table (col_names) VALUES (values)

26
Insert example

• INSERT INTO contacts (name,address,phone) VALUES
• (Chris, anywhere, 234 5678)
• If name, address and phone where the only columns
  in the table then could also use
• INSERT INTO contacts VALUES (Chris, anywhere,
  234 5678)

27
Multiple rows at a time

• INSERT INTO contacts VALUES
• (ben, somewhere, 255 3525),
• (dave, nowhere, 414 2625),
• (tom, anywhere, 843 5678)

28
Multiple rows at a time

• LOAD DATA INFILE
• Convenient way of getting data from a flat file
  into a database table

29
LOAD DATA syntax

• LOAD DATA LOCAL INFILE filename
• IGNORE REPLACE
• INTO TABLE tbl_name
• import_options
• IGNORE n LINES
• (column_list)

30
LOAD DATA

• File can exist on server or client
• On server LOAD DATA INFILE /home/cmb036/data.cs
  v
• On client LOAD DATA LOCAL INFILE ./mydata.tab

31
LOAD DATA

• IGNORE REPLACE
• Defines behaviour for duplicate values in unique
  rows
• IGNORE Keep existing data
• REPLACE Overwrite existing data
• Neither Error and stop reading file

32
LOAD DATA

• import_options
• FIELDS
• TERMINATED BY string
• OPTIONALLY ENCLOSED BY char
• ESCAPED BY char
• LINES TERMINATED BY string

33
Import_options - examples

• Tab file
• FIELDS
• TERMINATED BY \t
• ENCLOSED BY
• ESCAPED BY \\
• LINES TERMINATED BY \n

34
Import_options - examples

• Excel csv file (Windows)
• FIELDS
• TERMINATED BY ,
• ENCLOSED BY \
• ESCAPED BY \\
• LINES TERMINATED BY \r\n

35
LOAD DATA

• IGNORE n LINES
• Ignores the first n lines of the file
• Useful if there are header rows in the file
• (column_list)
• Needed if the number of cols in file ? number of
  cols in the table
• List the columns you want the data fed into

36
INSERT SELECT

• You can copy selected rows from one table to
  another by the INSERT SELECT command
• E.g.
• INSERT INTO cds SELECT FROM oldcds WHERE
  accessionU00096

37
Conflicting primary keys

• If you try and insert data with a primary key
  that already exists, operation will fail.
• But we can add the following
• ON DUPLICATE KEY UPDATE col_nameexpr

38
Conflicting primary keys

• Eg. If column a is the primary key, and already
  has a value 1 in it
• INSERT (a,b,c) VALUES (1,2,3)
• will fail
• But
• INSERT (a,b,c) VALUE (1,2,3) ON DUPLICATE KEY
  UPDATE aa1
• Sets the existing value of a to a1 and inserts
  the new row.

39
Updating rows

• UPDATE command
• UPDATE table SET column1expr, column2expr
• Will update all rows in the table updating the
  relevant columns.
• To selectively update
• UPDATE table SET column1expr WHERE
  column2this

40
REPLACE

• Same syntax as INSERT. E.g.
• REPLACE INTO table VALUES (
• Will insert, unless a row with the same
  primary/unique key is found, in which case the
  row is updated

41
Deleting rows

• DELETE FROM table
• Is equivalent to
• TRUNCATE TABLE table
• Can also conditionally delete
• E.g.
• DELETE FROM orders WHERE created lt 2004-06-01
  000000

42
Removing tables/databases

• DROP TABLE table_name
• DROP DATABASE database_name

43
Transactional tables

• When creating a table the default engine in
  MyISAM
• MyISAM does not support transactions
• Can use InnoDB (Included in default binary), or
  BerkleyDB (Not included in default binary)
• What are transactions?

44
Transactions

• Allows for multiple users to edit the same data.
• Why?
• Consider the following example

45
Transactions

• 2 people manipulating the same data at once
• Salesman 1 sells 3 shirts
• Salesman 1 retrieves current shirt count (47)
• select number from stock where itemshirt
• Salesman 2 sells 2 shirts
• Salesman 2 retrieves current shirt count (47)
• select number from stock where itemshirt
• Salesman 1 updates the stock level to 47-3 (44)
• update stock set number44 where itemshirt
• Salesman 2 updates the stock level to 47-2 (45)
• update stock set number45 where itemshirt

46
Transactions

• So weve sold 5 shirts, but the stock level is
  still 45!
• Transactions mean that only one person can get
  the data at one time
• Also means you can rollback to the original state
  before the transaction started

47
Transactions

• Start a transaction START TRANSACTION
• Execute your commands
• Then COMMIT keep your data
• Or ROLLBACK forget the changes

48
Tomorrow

• Creating your own database and adding data to it,
  manipulating it etc.

49
In a couple of weeks

• Using the DBI module to interface with MySQL
• Final Practical Integrating CGI and DBI to
  create a dynamic database driven web page. (and
  SQL/DBI Multiple Choice)