Relational Databases

Document Sample
Relational Databases Powered By Docstoc
					Relational Databases
   Intro to SQL and MySQL
 SQL  stands for
  Structured Query Language
      Pronounced es-cue-ell or sequel
 Itprovides a (somewhat) standard
  interface for interacting with a database
  through a series of statements.
      It defines what you get from a table or set of
       tables when making queries.
                In General ...
      be using MySQL but learning about
 We'll
 SQL in general as well.
     There are differences between them.
     There are also significant differences between
      versions of MySQL.
 Wewill be just scratching the surface of
     For more, there's the Advanced SQL course
      in Year 3.
             General SQL Stuff
   A client (or application using an API)
    communicates with an SQL server
   Everything is done with SQL statements
   Statements end in semicolons
   It is common to put keywords in upper case
   Strings are generally delimited with single
   There are a lot of "depending on the
    vendor/version/implementation" situations. :-)
          Defining A Database
 Startwith CREATE DATABASE name;
 Delete with DROP DATABASE name;
             Defining Tables
 CREATE      TABLE is used
    Can specify a column (field) list with data types.
    e.g.
     CREATE TABLE tblCustomer
     (id INT,
     name VARCHAR(40) DEFAULT „Mr. Unknown‟,
     city VARCHAR(40) DEFAULT 'Unknown');
 Canalso specify that a column needs to
 have a value
     NULL is equivalent to empty
     e.g.
      CREATE TABLE tblCustomer
      (id INT,
      name VARCHAR(40) DEFAULT „Unknown‟,
      city VARCHAR(40) DEFAULT 'Unknown',
      classification VARCHAR(2) NOT NULL);
 Canspecify PRIMARY KEY by putting
 keywords followed by key in brackets
     e.g.
      PRIMARY KEY (id)
    Finding Out About Tables
 Can see table definitions using
 Can see more information about the
  database and tables using SHOW
             Table Constraints
 Done   using FOREIGN KEY clauses.
     e.g.
      CREATE TABLE tblPreferredCustomer
      ( id INT,
      discount DOUBLE DEFAULT 0.05,
      description VARCHAR(78),
      PRIMARY KEY (id),
      FOREIGN KEY (id) REFERENCES tblPerson (id),
      CHECK (discount BETWEEN 0 and 0.25));
 Theseare Table Constraints and are
 placed after the definitions of the columns
     PRIMARY KEY clause
     UNIQUE clause requires a unique value of
      column or columns in all rows
     FOREIGN KEY clause, in combination with
       • e.g. FOREIGN KEY (officeID) REFERENCES
     CHECK clause provides limited validation
 ColumnConstraints are placed
 immediately after a column definition
     e.g. UNIQUE
     e.g. PRIMARY KEY
     e.g. REFERENCES tblOffice
     e.g. REFERENCES tblOffice (id)
     e.g. CHECK position = 1
      Changing Table Definitions
    can add columns by using ALTER
 You
     e.g. ALTER TABLE tblOrderHeader ADD
      salespersonID INTEGER REFERENCES
 Set   default:
     e.g. ALTER TABLE tblPerson ALTER name
      SET DEFAULT “Unknown”;
     tables can be dropped by using
 Entire
 DROP TABLE tblTableName;
     all relationships involving the table must be
      deleted first
         Adding Data to Tables
 Every  table starts out empty.
 The INSERT INTO command is used to
  insert data into tables as new records.
     If columns are not specified, the values are
      inserted in order.
 e.g.  INSERT INTO tblCustomer VALUES
  (1, 'Fred', 'Flintstone', '1313 Mockingbird
  Lane', 'Bedrock', 'ON', 'N2M 4M1', '(519)
 Adding Data to Selected Fields
 Your  new record can have just certain
  fields filled in if you want.
     The fields can be in any order but the values
      are put in using the order of the fields.
 e.g.INSERT INTO tblCustomer
  (CustomerID, FirstName, LastName, City)
  VALUES (2, 'Barney', 'Rubble', 'Bedrock');
 Done  using the SELECT statement.
 We'll be doing a lot with queries over the
  next few weeks.
 Very Simple Query Examples
 SELECT    name FROM tblCustomer;
 SELECT name, city FROM tblCustomer;
 SELECT * FROM tblCustomer;
 SELECT * FROM tblCustomer WHERE
  city = 'Toronto';
                SQL Data Types
 Each column needs a data type.
 There are many, many data types in SQL.
 There are also many, many data types in
     Found at:
    Some of the More Useful MySQL
          Integer Datatypes
       8-bit integer, signed or unsigned
   BOOL
       TRUE or FALSE in an 8-bit integer
       16-bit integer, signed or unsigned
   INT
       32-bit integer, signed or unsigned
       64-bit integer, signed or unsigned
 Some of the More Useful MySQL
    Floating-Point Datatypes
 FLOAT(similar to C's float)
 DOUBLE (similar to C's double)
 Some of the More Useful MySQL
   Character/String Datatypes
    one character
 CHAR(m)
    fixed-length string where m represents the
    max. 255 characters
    right-padded with spaces when stored (but
     removed when retrieved)
    variable-length string
    m represents max. length
      • 0 to 255 before MySQL 5.0.3
      • 0 to 65535 after that
    string with maximum length 65535
     have different lengths
A   BLOB is a "binary large object".
 It varies in maximum size depending on
  the actual datatype:
     TINYBLOB (255)
     BLOB (65535)
     MEDIUMBLOB (16,777,215)
     LONGBLOB (4GB)
   Date and Time Datatypes

Name       Meaning     Zero Value
datetime   date/time   '0000-00-00
           with 1-     00:00:00'
date       date        '0000-00-00'
time       time        '00:00:00'
year       year        0000
 The DATETIME data type has a range from
  '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
 The default behaviour of the MySQL server is to
  do range checking on the year, month, and day
  independent of one another.
       e.g. 2005-02-30 would be valid.
       This behaviour can be changed by the administrator
        of the SQL server.
               Date Format
 MySQLrequires YYYY-MM-DD format or
    For two-digit years, 70-99 are converted into
     1970 to 1999 and all others are 2000 to 2069.
Using MySQL
 MySQL    allows you to set up and use a
  relational database.
 Two parts: server and client
     Server holds the database.
     Client uses the database.
                    First ...
 Open    a console window:
     Under Start Menu, use Run and type cmd
 Make      sure the PATH is set up to include
  C:\Program Files\MySQL\MySQL Server 6.0\bin
  (or whereever the MySQL files are installed)

 Otherwise, you'll have to precede your Console commands with c:\mysql\bin.
 Verifythat the MySQL client is installed by
 entering mysql:

           (You might need to enter mysql –uusername –p)
          Is the Server Running?
 If   you get an error message like this:

  Then, the server isn't running on your local machine.
Getting the Server Running Locally
   a console prompt, enter mysqld --
 At
        Running as a Service
 You  can also get the server installed as a
  service using mysqld --install.
 This way, you don't have to manually start
  up the server every time you log in.
 Ifyou got an error message before, you
  should be able to now connect.
 Depending on how it is set up, you might
  need to log in as a specific user. On the
  computers in the lab, use:
      mysql --user=root -p
      It will prompt you for a password. Enter
  Connecting to Other Servers
 Tostart up with a connection to a remote
 server, use:
     mysql -h
       • Where is the IP address of the
         remote server
            GUI Components
 Many modern databases use a GUI to
  administer and maintain databases
 For MySQL:
     MySQL Administrator
     MySQL Browser
     Available as downloads (bundled or separate)
 Tutorials:
  for MySQL
  for SQL