MySQL

1
MySQL

• November 4, 2005
• By Jarret Hagen

2
Creating tables

• CREATE TABLE tablename
• (
• fieldname1 dataType1(size) null not null
• fieldname2 dataType2(size) null not null
• )
• NULL is assumed unless specified so that a field
  can be left empty.

3
Samples of Data Types

• CHAR(size) -up to 255.
• VARCHAR(size) -minimum 4 and max 255
• TEXT -65,535 characters.
• MEDIUMTEXT -16,777,215 characters.
• LONGTEXT -4,294,967,295 characters.
• DATETIME(2-14) -keeps track of time in order
  YYMMDDHHMMSS.

4
Samples of Data Types Contd

• INT (UNSIGNED)-signed -2B to 2B.
• unsigned INT are 0-4B.
• INT(ZEROFILL) used to left pad int with zeros.
• eg fieldname INT UNSIGNED AUTO_INCREMENT PRIMARY
  KEY.
• This would be used to create an auto number for
  the primary key.

5
Samples of Data Types(again)

• DATE stored in format YYY-MM-DD.
• TIME stores in HHMMSS.
• TINYINT signed -128 to 127 unsigned 0 to 255.
• MEDIUMINT default is signed (0 to 1,677,215 or
  -8,388,6608 to 8,388,6608).
• BIGINT 0 to 18Qt or -9Qt to 9Qt.
• Yes..QUINTILLION! Default (signed).

6
Example for proper INSERT

• INSERT INTO search (Category,        Page,     
    Directory,        LinkName,       
  Keywords,        Description) VALUES
  ('ASP',        'cookies.html',       
  'http//www.juicystudio.com/tutorial/asp/',     
    'Reading Cookies',        'request,cookies,key,
  subkey,domain',        'Using the Request object
  to read cookies in ASP')INSERT INTO search
  (Category,        Page,        Directory,     
    LinkName,        Keywords,       
  Description) VALUES ('PHP',       
  'cookies.html',        'http//www.juicystudio.co
  m/tutorial/php/',        'Using Cookies',     
    'cookies,setting,retrieving,reading,setcookie,is
  set',        'Using cookies in PHP')

7
Differences between Oracle and MySQL (DESCRIBE)
8
Differences between Oracle and MySQL (Showing
tables)
9
Differences between Oracle and MySQL (running
scripts)

• \. scriptName.sql is used instead of start.
• source works as well.
• .out files are described on Kristins website.

10
CREATING a table
11
CREATING a table(contd)
12
Performance Comparisons

• Throughput is in returned Web pages per second
  from the application server. Number of users is
  number of concurrent Webclients driving the load.
  Response time is the time to complete the six
  bookstore user action sequences, weighted by
  frequency of each sequence in the mix. All tests
  were conducted on an HP NetServer LT6000r with
  four 700MHz Xeon CPUs, 2GB of RAM, a Gigabit
  Ethernet Intel Corp.Pro/1000F Server Adapter and
  249.1GB Ultra3 SCSI hard drives used for database
  storage.