Document Sample
mysql Powered By Docstoc
          What is a database?
 a collection of data
 Usually consists of entities and
   An entity is an individual “object” that exists and is
    distinguishable from other individuals.
      Example: specific person, company, event, plant
   Entities have attributes
      Example: people have names and addresses
   A relationship is an association among several entities
Database Management System
 A computerized record-keeping system
 Allows operations such as:
   Adding new files
   Inserting data into existing files
   Retrieving data from existing files
   Changing data
   Deleting data
   Removing existing files from the database
               Data Models
 A data model is a collection of concepts for
  describing data.
 A schema is a description of a particular
  collection of data, using the given data
 The relational model of data is the most
  widely used model today.
   Main concept: relation, basically a table with
    rows and columns.
   Every relation has a schema, which describes
    the columns, or fields.
Levels of Abstraction
      Relational Databases
 Data is logically perceived as a two-
  dimensional table
 Relational databases are sets of tables
 tables are relations
Example Table
  Relational Database Query
 A relational database query is a question
  about the data, and the answer consists of a
  new relation containing the result.
 Queries are one part of the Data
  Manipulation Language of a DBMS (we also
  need to create, update, insert data)
 Language: Structured Query Language (SQL)
       Example SQL query
 Select G.Accession, G.Medline
 From Genebank G
 Where G.source=`baker‟s yeast‟;
   No explicit links between
 Of course, there may be implicit links
  in that two tables share the same
  attribute (like the accession number)
 In fact, in a relational DB, this is the
  only way to connect distinct tables, at
  the logical level anyway
 A link between one table and another
  is called a foreign key
           Why use a DBMS
 Data independence and efficient
   Reduced application development
   Data integrity and security.
   Uniform data administration.
   Concurrent access, recovery from
 Suppose you created a file to hold names, ID
  numbers and faculty/student status
 This was a flat file that resembled a table in
  a database
 What if you wanted to now add new data for
  some of the faculty with credit card
 How would you connect the two tables?
Fred         1234567
Mark         2345678
George       3456789
Quinn        4567890

   ID                  Credit Card
   1234567             44444444
   4567890             55555555
              How to use MySQL
 Connect to MySQL Server
shell> ~clement/mysqlbin/bin/mysql -h –u
  cs360 <Enter>

Welcome to the MySQL monitor.
Type 'help' for help.
           How to use MySQL
 Data Definition 1

          Creating Tables
 CREATE TABLE image (
  image_id INT,
  image_type CHAR(3),
  filename CHAR(40),
  url CHAR(128),
  Primary Key(image_id));
   creates a table with 4 columns and no
          Another table
 create table image_decoder
 (image_type CHAR(3),
 decoder_program varchar(20),
 args varchar(20));
               Basic Data Types
 INT - signed integer value. Implementation-dependent
  # bits
 NUMERIC(total length, number of decimal places)
     NUMERIC(8,4) - 3 digits, a decimal point, 4 decimal places
   REAL - floating point number
   BIT - single boolean value
   DATE - year, month, day
   TIME
   TIMESTAMP - date/time
   VARCHAR(length) - variable length string <= length
   BLOB - Binary Large Object
              How to use MySQL

Values must be in the right order and fill all columns

    INSERT INTO image
      ( image_id, image_type, filename, url)
      ( 1, „jpg‟, „image1‟, „http://host/dir/image1‟)

               Values must be the order specified.
               But, you don‟t need to fill all columns.
 insert into image_decoder
              Selecting Rows
 SELECT image_type from image
   WHERE filename=„image1‟
 SELECT image_decoder.decoder_program FROM
  image_decoder, image
   WHERE image.filename=„image1‟
   The Join operation can be viewed as creating a virtual table
    on the fly from rows in two or more tables
 SELECT * from image GROUP by image_type
        Basic Where Clauses
 Operators
   =, <, >, <=, >=, != (or <>)
      WHERE image_id = 2
   LIKE - wildcard comparison
      WHERE decoder_program LIKE „c:%‟
   ISNULL - checks for null value
   IN - contained in a set (usually for subqueries)
      WHERE image_id IN (1,2)
      WHERE image_id IN
       SELECT image_id FROM Image
            Updating Rows
 UPDATE Image
  SET url=„http://newhost/image1‟
  WHERE filename=„image1‟

 The where clause may select multiple rows
  e.g. WHERE image_id < 50
 If the WHERE clause is excluded, the SET
  operation is applied to every row in the
           Deleting Rows
 DELETE from Image
  WHERE image_id=2
   Entire row is removed from the table

 DELETE from Image
   Every row is removed from the table!!!
             How to use MySQL
 Data manipulation 2
mysql> SELECT * FROM seqs;
| title | accession | sequence |
| Human | u235      | cgatcagt |
mysql> insert into seqs
-> values('Dog','u222','ccgtacgt');
mysql> SELECT * FROM seqs;
| title | accession | sequence |
| Human | u235      | cgatcagt |
| Dog   | u222      | ccgtacgt |
         Add data from file
 mysql> load data local infile
  ‟/users/faculty/snell/CS360/sample.txt' into
  table seqs;
 Delete it
   mysql> delete from seqs
 Redo load with up arrow
 select title, accession from seqs;
 update seqs set accession = 'H0794' where
  title = 'Human-01';
 select * from seqs order by title;
         More commands
 mysql> select * from seqs where title
 like 'Human%';
         More commands
 use mysql;
 show tables;
 describe db;
                        PERL DBI
$dbh = DBI->connect("dbi:mysql:
  or die("Couldn't connect");
$SQL= "select * from seqs";

$Select = $dbh->prepare($SQL);

print "title $Row->{title}, sequence $Row->{sequence} \n";
      What Is the Perl DBI?
 The standard Database Interface for
 “A perl module and specification that
  defines a consistent database interface
  independent of the actual database being
           Why the Perl DBI?
 Once upon a time…
   One language, many database interfaces

 Perl 5 - A new way
   Modules and Objects. The DBI is born.

 The future is now…
   ODBC, Oracle, Informix, Ingres, mSQL, mysql, DB2, Solid,
    Sybase, Postgress,   Quickbase,   Empress, Fulcrum, ...

 The same database interface
       Making simple things easy
      and difficult things possible
 Goals
   Be simple to use for simple applications
   Have sufficient flexibility to accommodate unusual
    functionality and non-SQL databases
   Conform to applicable standards (ODBC etc.)
   Enable the creation of database-independent Perl scripts
    without being limited to the lowest functionality
   Be free.

 A „higher-level‟ interface than ODBC/JDBC
               Under the Hood
 DBI defines and implements an interface
 Driver modules do much of the real work
 DBI provides default methods, functions, tools etc for
 Not limited to the lowest common denominator -
  mechanism provided for driver specific extensions
 Designed and built for speed
 Valuable detailed call tracing/debugging built-in
    So why use the Perl DBI?
 Because...
   It delivers what it promises
   It‟s here, there and everywhere
   It‟s fast, flexible and well proven
   It‟s free, with source
   Commercial support is available
   It has a large user base and a strong

Shared By: