2-Database-Software

basesprocketΔιαχείριση Δεδομένων

31 Οκτ 2013 (πριν από 3 χρόνια και 8 μήνες)

74 εμφανίσεις

Database Software



James Brucker

Free Relational Databases

MySQL

-

one of the most popular


the "M" in "LAMP" and "XAMP" stacks


"free" for non
-
commercial use only


many useful tools:


MySQL Administrator (Windows or Linux)


MySQL Workbench & Query Browser


phpMyAdmin


Owned by
Oracle


http://www.mysql.com

Free Relational Databases

PostgreSQL



Based on Berkeley Postgres


Open Source, can be used in commercial apps without
a licence


Reputation as very stable and reliable


Included with Linux distros


Has some O
-
O features


http://www.postgresql.org

Client
-

Server Databases


Database
Server

is a separate
process

running on a
host.


Clients

can run on
any machine
.


Many programs

may be
clients

using a
standard API.


Server
(mysqld)

"mysql"
utility

Java app

using JDBC

Excel
client


Server side

Client side

server controls
access to
database

Why the "
d
" in
mysqld


The MySQL server is named "mysql
d
".


Other programs ending in "
d
":


ftpd
-

ftp server


httpd
-

HTTP server


sshd
-

Secure Shell server


Why "d"?



Server
(
mysqld
)

database

Lightweight & Embedded Databases

"
Light
"
-

they don't consume much cpu or memory.

"
Embedded
"
-

database manager is included
(embedded) into your application.



Called "embedded mode".


MyApp


Connection

derby.jar



include the database
software (jar) with
your application.

Software in JAR manages
database as plain files.

No server process.

Hypersonic SQL

HSQLDB

-

lightweight,
fast

database written in Java


database can be stored in
memory

or
on disk
.


embed

in Java app
-

no separate server


don't need to install database server or disk
-
based
database


can also run in
client
-
server mode


useful for development and "demo" systems


http://hsqldb.org

Derby

Derby

-

lightweight, pure Java database


formerly "Cloudscape", donated to Apache foundation


only 1 user can connect to database at a time


embed in Java applications
-

no separate server


similar to HSQLDB


can also run in client
-
server mode


included with JavaEE as "Java DB"


http://db.apache.org/derby


SQLite

World's most widely distributed database


written in C


very small: 350KB binary


used on Android


3rd party JDBC drivers:


http://code.google.com/p/sqlite
-
jdbc/



http://www.ch
-
werner.de/javasqlite/



http
://
www
.
xerial
.
org
/
trac
/
Xerial
/
wiki
/
SQLiteJDBC

Berkeley DB

Berkeley DB

-

sleepycat.com

(RIP)


libraries

for
embedded

database using the OS's file system.


No db manager, No network access, No query language.


used as data tier for LDAP, sendmail, and many other apps


very small and
fast

--

faster than any relational DB w/ manager


C

and
pure Java

version


language bindings

for C++, Perl, Python, Ruby, and more


bought by Oracle

in 2006:
http://www.oracle.com/database/berkeley
-
db/index.html


still Open Source under the "Sleepycat Public License" and
"Sleepycat Commercial License",


not required to distribute the source code with your app.

"Community Edition" Databases

IBM DB2 Express
-
C

-

relational DB with XML support


free edition of IBM DB2


good documentation and learning tools:


http://www.ibm.com/university


http://www.ibm.com/db2/express

Oracle 11g Express Edition

(XE)


leading market share among commercial databases


XE is easier to administer than full Oracle


http://www.oracle.com

Commercial Databases

Databases ranked by 2006 revenue (million US$).

Source: Gartner Research (www.gartner.com)

Install and Admin MySQL

Getting MySQL

http://dev.mysql.com/downloads/

Server and client

Query Browser,

Admin Tool

Java, ODBC, .Net, PHP

Many platforms:

Windows, Linux,
Solaris, Mac OS
-
X

MySQL Software

Server and Client


Download MySQL "Community Edition" from www.mysql.com


Ubuntu can install using package manager or Synaptic

GUI Tools


mysql
-
gui
-
tools
-
5.2r6
-
platform


mysql
-
query
-
browser
-
1.1.17
-
win.msi

Connectors


Connector/J


Java JDBC

For CPE, download from http://se.cpe.ku.ac.th/download/mysql

How to Administer MySQL

To manage a MySQL server, you need an
administrator
account

(root) and administration tool:


mysqladmin

-

command line tool (included)


MySQL Administrator

-

part of MySQL Workbench


Other Tools:


phpMyAdmin

-

Web
-
based admin tool, open source


Webmin

-

another Web
-
based admin tool, for Linux

MySQL Administrator

Connection Dialog

Main Window, "Catalogs" view

Easy to use GUI interface.

mysqladmin and mysql

Useful command line tools.


Change MySQL administrator password.


Create new database.


Import data / export data. Backup a database.


Modify privilege tables.

cmd>
mysqladmin

Usage: mysqladmin [OPTIONS] command command....

Where command is a one or more of:


create

databasename Create a new database


drop

databasename Delete a database and all its tables


flush
-
tables

Flush all tables


password

new
-
password Change old password to new
-
password


reload

Reload grant tables


shutdown

Take server down


status

Gives a short status message from server


version

Get version info from server

Create a database

cmd>
mysql
-
h
hostname

-
u root
-
p

Password:
********

mysql>

create database Students;

Query OK, 1 row affected


mysql>

use Students;

Database changed

mysql>

show tables;

Empty set

Create a table in batch mode

DROP TABLE IF EXISTS 'Students';

CREATE TABLE 'Students' (


`ID` int(11) PRIMARY KEY NOT NULL auto_increment,


`Name` varchar(60) NOT NULL DEFAULT '',


`Telephone` char(12),


`Birthday` date

) DEFAULT CHARSET=utf8;

It is easier and more
repeatable

to put SQL commands in a text file
and process the file using a MySQL client.

(1) create a text file ("student
-
schema.sql") containing commands:

(2) use
mysql

command tool to source the text file:


cmd>
mysql
-
u root
-
p


mysql>

use Student;


mysql>
source student
-
schema.sql;

Another Batch Example

--

create table for student data

--

use the 'UTF8' character set for Thai names

--

Jim Brucker, Jan 2006

USE test;

--

this will discard any existing data!!!

DROP TABLE IF EXISTS students;


CREATE TABLE students (


id CHAR(8) PRIMARY KEY NOT NULL,


prefix VARCHAR(24) NOT NULL DEFAULT '',


firstname VARCHAR(40) NOT NULL DEFAULT '',


lastname VARCHAR(40) NOT NULL DEFAULT '',


enfirstname VARCHAR(40) NOT NULL DEFAULT '',


enlastname VARCHAR(40) NOT NULL DEFAULT ''

) DEFAULT CHARSET=utf8;

Adding Data to Tables

1.
Text file containing SQL "INSERT" commands.


2.
Comma
-
delimited (CSV) file. Can be created using Excel.

Comma Separated Values files

cmd>
mysql
-
h
hostname

-
u root
-
p

Password:
********

mysql>

LOAD DATA INFILE '
/path/filename
'


INTO TABLE
tbl_name



FIELDS TERMINATED BY ','


OPTIONALLY ENCLOSED BY '"' ESCAPED BY '
\
\
' ;

Query OK, 499 rows affected


"James","Brucker","jb@yahoo.com",1234

"George","Bush","president@whitehouse.gov",1111

"Santa","","claus@northpole.org",001

Comma Separated Values (CSV) is a common interchange format
for text data. Used by Excel, Yahoo AddressBook, ... many apps.

MySQL Users

What identifies a user?


"student" on local machine may not be the same
person as "student" on
pirates.com


even if "student" is the same, you may want to assign
different privileges to local and network logins.

MySQL identifies users as:
username@hostname


student@localhost

student@% (any host)

hacker@10.1.2.3

Creating a User


Easy way: use MySQLAdmin or phpMyAdmin.


Command line (MySQL 5.0):

mysql>
CREATE USER '
user1
'@'
localhost
'


IDENTIFIED BY '
secret
';

mysql>
CREATE USER '
user1
'@'
%.ku.ac.th
'


IDENTIFIED BY '
secret2
';

mysql>
CREATE USER '
user1
'@'
%
'



IDENTIFIED BY 'hackme';

Managing Users and Permissions


Give "guest" permission to view the World database:


sql>

GRANT SELECT ON world.* TO guest;


Allow "student" to insert/edit records in the City table:


sql>

GRANT INSERT,UPDATE ON world.City TO student;


All "student" to modify the population field of existing
countries (but not add new countries):


sql>

GRANT UPDATE(population) on world.Country




TO student;


Deny all privileges to
everything

to "hacker":


sql>

REVOKE ALL on *.* TO Hacker;


GRANT

/
REVOKE

Syntax

privilege:


SELECT, INSERT, UPDATE, DELETE, REFERENCES,

CREATE, ALTER, DROP, INDEX,

CREATE_VIEW, SHOW_VIEW

with_option


GRANT OPTION

MAX_USER_CONNECTIONS

count

MAX_CONNECTIONS_PER_HOUR

count

MAX_QUERIES_PER_HOUR

count

GRANT

privilege
[
(
column_list
)
] [, ... ]


ON

{
table_name

|
*

|
*.*

|
db_name
.*

}


TO

user

[
IDENTIFIED

BY

[
PASSWORD
]
'password'
]


[, ... ]


[
WITH

with_option

... ]

GRANT

/
REVOKE

Example

student
can query, insert, update, and delete records in
the world database, but he can't change the database
schema or indexing, can't grant privileges to others.

This command also creates a
student

user with
password
secret
.

GRANT

select,insert,update,delete

ON

world.*


TO

student@'%'

IDENTIFIED

BY

'secret'
;



GRANT

/
REVOKE

Example

Create an admin user for the "wiki" database so that you
can create tables, indices, etc.

Access allowed only on local machine, not over network.

Typical way of setting database permissions for a web
application.

GRANT

ALL

ON

wiki.*


TO

'
wikiadmin'@'localhost'



IDENTIFIED

BY

'secret'
;

Privileges you can
GRANT

and
REVOKE

Type of Operation

Statement

View table data

SELECT

Add rows to a table

INSERT

Modify data in a table

UPDATE

Delete rows

DELETE

Reference a table from another

REFERENCES

Drop tables

DROP

Create or Alter tables

CREATE
,
ALTER

Index a table by an expression

INDEX

All privileges

ALL

Exercise


Create a user named "hacker".


Give hacker permission to view data in world.Country
and world.City, but not world.CountryLanguage.


Give hacker permission to view, insert, update, and
delete rows in all tables in the test database.

Resources

MySQL


http://dev.mysql.com/tech
-
resources/articles/dotnet/

Learning SQL


http://www.w3schools.com/sql/

nice tutorial and command reference