Transcript mysql

SQL
What is a database?
 a collection of data
 Usually consists of entities and
relations
 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
(DBMS)
 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
model.
 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
tables
 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
Tables and Keys
Primary
Keys
Foreign
Key
image_id
1
...
image_type
gif
...
image_type
gif
decoder_program
c:\gifdecoder
filename
Image1
url
…
args
…
Why use a DBMS
 Data independence and efficient




access.
Reduced application development
time.
Data integrity and security.
Uniform data administration.
Concurrent access, recovery from
crashes.
Example
 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
information?
 How would you connect the two tables?
Example
Fred
1234567
Mark
2345678
George
3456789
Quinn
4567890
ID
Credit Card
1234567
44444444
4567890
55555555
How to use MySQL
 Connect to MySQL Server
shell> ~snell/mysqlrun/bin/mysql -h paintball -u CS360 -p
<Enter>
password: passwd
Welcome to the MySQL monitor.
Type 'help' for help.
mysql>
How to use MySQL
 Data Definition 1
mysql> SHOW DATABASES;
Database
mysql
test
tmp
How to use MySQL

Data Definition 2
mysql> CREATE DATABASE sequences-yourname;
mysql> USE sequences-yourname
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql>
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
rows
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
 Data definition
mysql> create table seqs (title varchar(20),
-> accession varchar(20),
-> sequence varchar(20))
-> ;
How to use MySQL
 Data Manipulation 1
insert into seqs
-> values('Human','u235','cgatcagt');
mysql>
Values must be in the right order and fill all columns
 INSERT INTO Image
( image_id, image_type, filename, url)
VALUES
( 1, ‘jpg’, ‘image1’, ‘http://host/dir/image1’)
Values must be the order specified.
But, you don’t need to fill all columns.
How to use MySQL
mysql> select * from seqs where accession='u235';
+-------+-----------+----------+
| title | accession | sequence |
+-------+-----------+----------+
| Human | u235
| cgatcagt |
+-------+-----------+----------+
mysql> select sequence from seqs
-> ;
+----------+
| sequence |
+----------+
| cgatcagt |
| ccgtacgt |
+----------+
2 rows in set (0.00 sec)
Selecting Rows
 SELECT image_type from Image
WHERE filename=‘image1’
 SELECT Image_Decoder.decoder_program FROM
Image_Decoder, Image
WHERE Image.filename=‘image1’
AND
Image.image_type=Image_Decoder.image_type
 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
table
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:
database=sequences;
host=paintball:1236;",
"phylo","")
or die("Couldn't connect");
$SQL= "select * from seqs";
$Select = $dbh->prepare($SQL);
$Select->execute();
while($Row=$Select->fetchrow_hashref)
print "title $Row->{title}, sequence $Row->{sequence} \n";
$dbh->disconnect();
What Is the Perl DBI?
 The standard Database Interface for Perl
 “A perl module and specification that
defines a consistent database interface
independent of the actual database being
used”
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
drivers
 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
A Picture is Worth...
Perl Application
DBI Module
DBD::Oracle
Oracle Server
DBD::Informix
Informix Server
DBD::Other
Other Server
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
future