david lawrence, jlab · 7/8/05 mysql david lawrence 20 permissions/access control • permissions...
TRANSCRIPT
![Page 1: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/1.jpg)
7/8/05 MySQL David Lawrence 1
David Lawrence, JLab
An introduction for the novice
![Page 2: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/2.jpg)
7/8/05 MySQL David Lawrence 2
What is a Database?
• Store information(data) in a reliable, accessible way
• Allow access to data over a network to multiple users
• Provide easy way to select a specific “view” of the data
![Page 3: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/3.jpg)
7/8/05 MySQL David Lawrence 3
Relational Database
• A relational database is a collection of tables that can be dynamically (and temporarily) combined into a single table. Columns of the contributing tables can be related to one another.
• In a non-relational database, the tables are always separate entities
![Page 4: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/4.jpg)
7/8/05 MySQL David Lawrence 4
A Few Terms:
• Server - Program that accepts connections and implements the database
• Database - A collection of tables on a single server. More than one “database”can exist on a single server
• Table - A set of column definitions
![Page 5: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/5.jpg)
7/8/05 MySQL David Lawrence 5
A Few Terms:
• Column - Provides a name and data type
• Row - A single entry in a table. It contains one value for every column (possibly NULL)
• Query - A command in SQL syntax for the database. It can insert, modify, or extract data.
![Page 6: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/6.jpg)
7/8/05 MySQL David Lawrence 6
tructured
uery
anguage
ANSI/ISOStandard:
199219992003
![Page 7: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/7.jpg)
7/8/05 MySQL David Lawrence 7
SQL: Structured Query Language
• SQL is a syntax for probing and manipulating a database.
![Page 8: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/8.jpg)
7/8/05 MySQL David Lawrence 8
Creating a tableCREATE TABLE IF NOT EXISTS friends(
id int PRIMARY KEY AUTO_INCREMENT,firstname char(255) NOT NULL,lastname char(255),pets_name char(255),age int,status ENUM("like","hate") default 'like',created datetime,modified timestamp
) TYPE=MyISAM;
![Page 9: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/9.jpg)
7/8/05 MySQL David Lawrence 9
The INSERT statement
INSERT INTO friends(firstname,lastname,pets_name,age,created)
VALUES("Amelia", "Lawrence", "Star", 8, NOW());
![Page 10: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/10.jpg)
7/8/05 MySQL David Lawrence 10
The SELECT statement
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 11: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/11.jpg)
7/8/05 MySQL David Lawrence 11
The SELECT statement
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
SELECT can specify both columns and rows…
![Page 12: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/12.jpg)
7/8/05 MySQL David Lawrence 12
The SELECT statementSELECT can limit the number of rows returned…
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 13: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/13.jpg)
7/8/05 MySQL David Lawrence 13
The SELECT statement
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
SELECT can also be used with functions…
![Page 14: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/14.jpg)
7/8/05 MySQL David Lawrence 14
The SELECT statementSELECT can do math …
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 15: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/15.jpg)
7/8/05 MySQL David Lawrence 15
UPDATEing table data
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 16: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/16.jpg)
7/8/05 MySQL David Lawrence 16
The DELETE statement
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 17: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/17.jpg)
7/8/05 MySQL David Lawrence 17
Database Table Design is an Art!
# ---- BAD -----CREATE TABLE IF NOT EXISTS tagger_t_cal_dave3(
tid int,toffset_l int,toffset_r int
);
CREATE TABLE IF NOT EXISTS tagger_table_names(run int,tagger_t_cal_table char(255),tagger_e_cal_table char(255)
);
“If you have to do more than one query to get the data you want out of the database, you have not done a good job designing your tables.”
-R. Chapman, Professional Database Designer
![Page 18: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/18.jpg)
7/8/05 MySQL David Lawrence 18
Database Table Design is an Art!
# ---- GOOD -----CREATE TABLE IF NOT EXISTS tagger_t_cal(
run int NOT NULL,tid int NOT NULL,toffset_l int,toffset_r int,PRIMARY KEY(run,tid)
);
![Page 19: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/19.jpg)
7/8/05 MySQL David Lawrence 19
Client/Server Model
Server
Client
server listens …
… client connectsClientClient
Client
Many clients can Connect to the server at once
![Page 20: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/20.jpg)
7/8/05 MySQL David Lawrence 20
Permissions/Access Control• Permissions are kept by server independent
of any Unix system• Accounts are determined by the host you
connect from and the username you supply• Permissions can be granted at the global,
database, table and column levels• Permissions can be granted for all or only a
partial set of commands
![Page 21: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/21.jpg)
7/8/05 MySQL David Lawrence 21
Permissions/Access Control
• Use the GRANT and REVOKE commands to set permissions:
GRANT ALL PRIVILEGES ON test.* TOjoebob@'%.jlab.org' IDENTIFIED BY "tutu”
REVOKE ALL PRIVILEGES ON test.* FROMjoebob@'%.jlab.org' IDENTIFIED BY "tutu”
![Page 22: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/22.jpg)
7/8/05 MySQL David Lawrence 22
The mysql command-line tool
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 23: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/23.jpg)
7/8/05 MySQL David Lawrence 23
API: Application Programming Interface
• The API provides the means to access the database from your language of choice– C– Perl– PHP– Java (JDBC)– Python– Tcl– Eiffel
![Page 24: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/24.jpg)
7/8/05 MySQL David Lawrence 24
C API• Very useful for adding database access to C
programs, but most jobs are better done in scripting languages– mysql_init()– mysql_real_connect()– mysql_query()– mysql_store_result()– mysql_fetch_row()– mysql_free_result()– mysql_close()
![Page 25: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/25.jpg)
7/8/05 MySQL David Lawrence 25
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 26: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/26.jpg)
7/8/05 MySQL David Lawrence 26
Compiling the C program
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 27: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/27.jpg)
7/8/05 MySQL David Lawrence 27
Perl API
• Uses the DBI and DBD-mysql modules– DBI->connect()– prepare()
• execute()• fetchrow_arrayref()• fectrow_hashref()
– disconnect()
![Page 28: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/28.jpg)
7/8/05 MySQL David Lawrence 28
Perl API
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 29: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/29.jpg)
7/8/05 MySQL David Lawrence 29
PHP API
• The PHP interface is extremely valuable for interfacing a MySQL database with a web page– mysql_connect()– mysql_select_db()– mysql_query()
• mysql_fetch_array()– mysql_close()
![Page 30: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/30.jpg)
7/8/05 MySQL David Lawrence 30
PHP API
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 31: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/31.jpg)
7/8/05 MySQL David Lawrence 31
Java API
• Java uses JDBC to interface to MySQL– Class.forName()– getConnection()– createStatement()– executeQuery()
• next()• getString()• getInt()
![Page 32: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/32.jpg)
7/8/05 MySQL David Lawrence 32
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 33: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/33.jpg)
7/8/05 MySQL David Lawrence 33
Backing Up: mysqldump
• The mysqldump command line utility can print the entire contents of a database to the screen.– -d option says don’t include table data– -t option says don’t include table
definitions– Individual tables can be specified
![Page 34: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/34.jpg)
7/8/05 MySQL David Lawrence 34
QuickTime™ and aTIFF (LZW) decompressor
are needed to see this picture.
![Page 35: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/35.jpg)
7/8/05 MySQL David Lawrence 35
Supported Platforms• AIX• Amiga• BSDI• Digital Unix• FreeBSD• HP-UX• Linux• SunOS• True64
• Mac OS X• NetBSD• Novell Netware• OpenBSD• OS/2 Warp• SCO Unix• SGI• True64• MS Windows
![Page 36: David Lawrence, JLab · 7/8/05 MySQL David Lawrence 20 Permissions/Access Control • Permissions are kept by server independent of any Unix system • Accounts are determined by](https://reader034.vdocuments.us/reader034/viewer/2022050208/5f5b48381c7e9f287a26d0cb/html5/thumbnails/36.jpg)
7/8/05 MySQL David Lawrence 36
Summary
• MySQL is a popular, free database well suited to most applications
• There are APIs supporting many programming languages including PHP which makes a powerful combination for generating web pages
See more at http://www.mysql.com