Database Software Free Relational Databases My SQL one
Database Software
Free Relational Databases My. SQL - one of the most popular n the "M" in "LAMP" and "XAMP" stacks n "free" for non-commercial use only n many useful tools: n My. SQL Administrator (Windows or Linux) n My. SQL Workbench & Query Browser n php. My. Admin n Owned by Oracle n http: //www. mysql. com SO LD
Free Relational Databases Postgre. SQL n n Based on Berkeley Postgres Open Source, can be used in commercial apps without a licence n Reputation as very stable and reliable n Included with Linux distros n Has some O-O features n http: //www. postgresql. org
Client - Server Databases n n n 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. "mysql" utility Java app using JDBC Excel client Client side Server (mysqld) server controls access to database Server side
Why the "d" in mysqld n n n The My. SQL server is named "mysqld". Other programs ending in "d": n ftpd - ftp server n httpd - HTTP server n 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. n Called "embedded mode". My. App Connection include the database software (jar) with your application. derby. jar Software in JAR manages database as plain files. No server process.
Hypersonic SQL HSQLDB - lightweight, fast database written in Java n database can be stored in memory or on disk. n embed in Java app - no separate server n don't need to install database server or disk-based database n can also run in client-server mode n useful for development and "demo" systems n http: //hsqldb. org
Derby - lightweight, pure Java database n formerly "Cloudscape", donated to Apache foundation n only 1 user can connect to database at a time n embed in Java applications - no separate server n similar to HSQLDB n can also run in client-server mode n included with Java. EE as "Java DB" n http: //db. apache. org/derby
SQLite World's most widely distributed database n written in C n very small: 350 KB binary n used on Android n 3 rd party JDBC drivers: n http: //code. google. com/p/sqlite-jdbc/ n http: //www. ch-werner. de/javasqlite/ n http: //www. xerial. org/trac/Xerial/wiki/SQLite. JDBC
Berkeley DB - sleepycat. com (RIP) n libraries for embedded database using the OS's file system. n No db manager, No network access, No query language. n used as data tier for LDAP, sendmail, and many other apps n very small and fast -- faster than any relational DB w/ manager n C and pure Java version n n language bindings for C++, Perl, Python, Ruby, and more bought by Oracle in 2006: http: //www. oracle. com/database/berkeley-db/index. html SO still Open Source under the "Sleepycat Public License" and "Sleepycat Commercial License", not required to distribute the source code with your app. LD
"Community Edition" Databases IBM DB 2 Express-C - relational DB with XML support n free edition of IBM DB 2 n good documentation and learning tools: http: //www. ibm. com/university n http: //www. ibm. com/db 2/express Oracle 11 g Express Edition (XE) n leading market share among commercial databases n XE is easier to administer than full Oracle n http: //www. oracle. com
Commercial Databases ranked by 2006 revenue (million US$). Source: Gartner Research (www. gartner. com)
Install and Admin My. SQL
Getting My. SQL http: //dev. mysql. com/downloads/ Server and client Query Browser, Admin Tool Java, ODBC, . Net, PHP Many platforms: Windows, Linux, Solaris, Mac OS-X
My. SQL Software Server and Client Download My. SQL "Community Edition" from www. mysql. com Ubuntu can install using package manager or Synaptic GUI Tools mysql-gui-tools-5. 2 r 6 -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 My. SQL To manage a My. SQL server, you need an administrator account (root) and administration tool: n mysqladmin - command line tool (included) n My. SQL Administrator - part of My. SQL Workbench Other Tools: n php. My. Admin - Web-based admin tool, open source n Webmin - another Web-based admin tool, for Linux
My. SQL Administrator Easy to use GUI interface. Connection Dialog Main Window, "Catalogs" view
mysqladmin and mysql Useful command line tools. n Change My. SQL administrator password. n Create new database. n Import data / export data. Backup a database. n Modify privilege tables. cmd> mysqladmin Usage: mysqladmin [OPTIONS] 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 It is easier and more repeatable to put SQL commands in a text file and process the file using a My. SQL client. (1) create a text file ("student-schema. sql") containing commands: 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=utf 8; (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 'UTF 8' 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=utf 8;
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 Comma Separated Values (CSV) is a common interchange format for text data. Used by Excel, Yahoo Address. Book, . . . many apps. "James", "Brucker", "jb@yahoo. com", 1234 "George", "Bush", "president@whitehouse. gov", 1111 "Santa", "claus@northpole. org", 001 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
My. SQL Users What identifies a user? n n "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. My. SQL identifies users as: username@hostname student@localhost student@% (any host) hacker@10. 1. 2. 3
Creating a User n Easy way: use My. SQLAdmin or php. My. Admin. n Command line (My. SQL 5. 0): mysql> CREATE USER 'user 1'@'localhost' IDENTIFIED BY 'secret'; mysql> CREATE USER 'user 1'@'%. ku. ac. th' IDENTIFIED BY 'secret 2'; mysql> CREATE USER 'user 1'@'%' IDENTIFIED BY 'hackme';
Managing Users and Permissions n Give "guest" permission to view the World database: sql> GRANT SELECT ON world. * TO guest; n Allow "student" to insert/edit records in the City table: sql> GRANT INSERT, UPDATE ON world. City TO student; n All "student" to modify the population field of existing countries (but not add new countries): sql> GRANT UPDATE(population) on world. Country TO student; n Deny all privileges to everything to "hacker": sql> REVOKE ALL on *. * TO Hacker;
GRANT / REVOKE Syntax GRANT privilege[(column_list)] [, . . . ] ON { table_name | *. * | db_name. * } TO user [IDENTIFIED BY [PASSWORD] 'password'] [, . . . ] [WITH with_option. . . ] 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 / REVOKE Example GRANT select, insert, update, delete ON world. * TO student@'%' IDENTIFIED BY 'secret' ; 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 / REVOKE Example GRANT ALL ON wiki. * TO 'wikiadmin'@'localhost' IDENTIFIED BY 'secret' ; 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.
Privileges you can GRANT and REVOKE Type of Operation View table data Add rows to a table Modify data in a table Delete rows Reference a table from another Drop tables Create or Alter tables Index a table by an expression All privileges Statement SELECT INSERT UPDATE DELETE REFERENCES DROP CREATE, ALTER INDEX ALL
Exercise n n n Create a user named "hacker". Give hacker permission to view data in world. Country and world. City, but not world. Country. Language. Give hacker permission to view, insert, update, and delete rows in all tables in the test database.
Resources My. SQL n http: //dev. mysql. com/tech-resources/articles/dotnet/ Learning SQL n http: //www. w 3 schools. com/sql/ nice tutorial and command reference
- Slides: 32