Database mysql Contents Database DBMS Relational model SQL

Database - mysql

Contents Database – DBMS – Relational model SQL – My. SQL

Database – a collection of data – Entity, relationship DBMS – Database management system

DBMS Why use a DBMS? – efficient access – reduced application development time – data integrity and security – concurrent access – recovery from system crash

Relational model Most widely used data model Relation – A set of records – Schema • Name of a relation, name of each fields – Instance • A table with rows and columns

Students(sid: string, sname: string, snum: integer) Fields (attributes, columns) Field names Tuples (records, rows) sid sname snum euny Chang Eun Young 99 cavalist Ryu Han Seung 99 netj 00 Jaeho Shin

SQL Structured query language Standard language for interacting with a DBMS Data definition Manipulation

My. SQL Connect – mysql [–h host] [–u user] [–p[password]] [dbname] >mysql -u root -p test Enter password: **** Welcome to the My. SQL monitor. Commands end with ; or g. Your My. SQL connection id is 14 to server version: 3. 23. 34 a Type 'help; ' or 'h' for help. Type 'c' to clear the buffer mysql>

My. SQL Data definition – CREATE/DROP DATABASE dbname; – SHOW DATABASES; – USE dbname; – CREATE TABLE table_name (field_name type, . . , constraints, . . ); – SHOW TABLES; – SHOW COLUMNS FROM table_name; – DROP TABLE table_name;

My. SQL Data manipulation – INSERT INTO table_name [(field_name, . . )] VALUES (value, . . ); – DELETE FROM table_name WHERE condition; – UPDATE table_name SET field_name=value, . . [WHERE condition];

My. SQL Data manipulation(2) – SELECT field_name [as field_name], . . FROM table_name [WHERE condition] [ORDER BY field_name]; – =, <, >, AND, OR, NOT (field_name LIKE “_%…. ”)

My. SQL ALTER TABLE – ALTER TABLE table_name [RENAME new_table_name]/ [ADD field_name type]/ [DROP field_name]/ [CHANGE name new_type];

mysql> show databases; +-----+ | Database | +-----+ | mysql | | test | +-----+ 2 rows in set (0. 00 sec) mysql> use test; Database changed mysql> create database test 2; Query OK, 1 row affected (0. 00 sec)

mysql> CREATE TABLE Students( -> sid VARCHAR(8) NOT NULL, -> sname VARCHAR(20), -> snum INT, -> PRIMARY KEY(sid)); Query OK, 0 rows affected (0. 01 sec)

My. SQL Data Types Type Size int 4 bytes tinyint, smallint, mediumint, big 1, 2, 3 and 8 bytes int Description 2^32 values float double 4 bytes 8 bytes Date 3 bytes In the format of YYYY-MM-DD Time 3 bytes In the format of HH: MM: SS char[length] varchar[length] fi String length + 1 byte Fixed length string Variable length from 0 to 255 characters long

mysql> show tables; +--------+ | Tables_in_test | +--------+ | students | +--------+ 1 row in set (0. 00 sec) mysql> show columns from students; +-------------+-----+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----+-------+ | sid | varchar(8) | | PRI | | sname | varchar(20) | YES | | NULL | | | snum | int(11) | YES | | NULL | | +-------------+-----+-------+ 3 rows in set (0. 01 sec)

Primary Key is a column or set of columns Uniquely identifies the rest of the data in any given row. For Example: in the employee table, employee number is the primary key.

Foreign Key A foreign key is a column in a table This column is a primary key of another table Any data in a foreign key column must have corresponding data in the other table

Foreign Key The goal of using foreign keys is that tables can be related without repeating data Note that foreign keys in SQL are used to check and enforce referential integrity, not to join tables. If you want to get results from multiple tables from a SELECT statement, you do this by performing a join between SELECTthem: * FROM t 1, t 2 WHERE t 1. id = t 2. id;

Create table with foreign keys employee 2 create table employee 2 ( empno smallint(4) not null, salary float, primary key (empno) ) type = innodb; create table employer ( id smallint(4), employee_no smallint(4), index employ_ind (employee_no), foreign key(employee_no) references employee 2(empno) on delete cascade) type=innodb; empno (PK) salary 100 200. 85 200 129. 54 300 98. 17 employer id employee_no(FK) 51 100 52 100 53 200 54 300

My. SQL Table Types If we want to use Foreign Key – Inno. DB tables Otherwise – Default table type, My. ISAM In SQL queries you can freely mix Inno. DB type tables with other table types of My. SQL, even within the same query.

mysql> INSERT INTO Students -> VALUES ('euny', 'Chang Eun-young', 99); Query OK, 1 row affected (0. 00 sec) … mysql> SELECT * FROM Students; +-----------------+ | sid | sname | snum | +-----------------+ | euny | Chang Eun-young | 99 | | cavalist | Ryu Han Seung | 99 | | netj | Jaeho Shin | 2000 | +-----------------+ 3 rows in set (0. 00 sec) mysql> DELETE FROM Students -> WHERE sid='euny'; Query OK, 1 row affected (0. 00 sec)

mysql> UPDATE Students -> SET sid='asdf' -> WHERE sid='euny'; Query OK, 1 row affected (0. 00 sec) mysql> SELECT * FROM Students; +-----------------+ | sid | sname | snum | +-----------------+ | asdf | Chang Eun-young | 99 | | cavalist | Ryu Han Seung | 99 | | netj | Jaeho Shin | 2000 | +-----------------+ 1 row in set (0. 00 sec)

mysql> SELECT * FROM Students ORDER BY sname; +-----------------+ | sid | sname | snum | +-----------------+ | asdf | Chang Eun-young | 99 | | netj | Jaeho Shin | 2000 | | cavalist | Ryu Han Seung | 99 | +-----------------+ 3 rows in set (0. 02 sec) mysql> SELECT sname FROM Students WHERE snum=99; +---------+ | sname | +---------+ | Chang Eun-young | | Ryu Han Seung | +---------+ 1 row in set (0. 00 sec)

Using Connector/J My. SQL Connector/J is a native Java driver that converts JDBC (Java Database Connectivity) calls into the network protocol used by the my. SQL database. n It lets developers working with the Java programming language easily build programs and applets that interact with My. SQL and connect all corporate data, even in a heterogeneous environment. n My. SQL Connector/J is a Type IV JDBC driver and has a complete JDBC feature set that supports the capabilities of My. SQL. n

Using Connector/J n Download the software from http: //dev. mysql. com/downloads/connector/j/5. 0. html n My. SQL Connector/J is distributed as a zip or. tar. gz archive containing the sources, the class files, and the JAR archive named mysql-connector-java-[version]-bin. jar Install this file in C: Program FilesJavajdk 1. 5. 0_07jrelibext or similar directory on your machine n

Accessing my. SQL from a servlet <HTML> <BODY> <TITLE>See Account Information</TITLE> Enter account number to view: <BR> <form action="Student. List" method="post"> <input name="account"> <input type="submit" name="submit" value="submit"> </form> </BODY> </HTML>

Accessing my. SQL from a servlet package dbpackage; import java. io. *; import java. sql. *; import javax. servlet. http. *; public class Student. List extends Http. Servlet { public void do. Get(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { response. set. Content. Type("text/html"); Print. Writer out = response. get. Writer(); String doc. Type = "<!DOCTYPE HTML PUBLIC "-//W 3 C//DTD HTML 4. 0 " + "Transitional//EN"n"; String title = "Student List in my. SQL"; out. print(doc. Type + "<HTML>n" + "<HEAD><TITLE>" + title + "</TITLE></HEAD>n" + "<BODY BGCOLOR="#FDF 5 E 6"><CENTER>n" + "<H 1>Database Results</H 1>n");

Accessing my. SQL from a servlet String driver = "com. mysql. jdbc. Driver"; String url = "jdbc: mysql: //localhost/newdb"; String username = "ira"; String password = "mypassword"; String query = "SELECT * from student; "; show. Table(driver, url, username, password, query, out); out. println("</CENTER></BODY></HTML>"); } public void show. Table(String driver, String url, String username, String password, String query, Print. Writer out) { try { // Load database driver if it's not already loaded. Class. for. Name(driver); // Establish network connection to database. Connection connection = Driver. Manager. get. Connection(url, username, password); // Look up info about the database as a whole. Database. Meta. Data db. Meta. Data = connection. get. Meta. Data(); out. println("<UL>"); String product. Name = db. Meta. Data. get. Database. Product. Name(); String product. Version = db. Meta. Data. get. Database. Product. Version(); out. println(" <LI><B>Database: </B> " + product. Name + " <LI><B>Version: </B> " + product. Version + "</UL>");

Accessing my. SQL from a servlet Statement statement = connection. create. Statement(); // Send query to database and store results. Result. Set result. Set = statement. execute. Query(query); // Print results. out. println("<TABLE BORDER=1>"); Result. Set. Meta. Data result. Set. Meta. Data = result. Set. get. Meta. Data(); int column. Count = result. Set. Meta. Data. get. Column. Count(); out. println("<TR>"); // Column index starts at 1 (a la SQL), not 0 (a la Java). for(int i=1; i <= column. Count; i++) { out. print("<TH>" + result. Set. Meta. Data. get. Column. Name(i)); } out. println(); // Step through each row in the result set. while(result. Set. next()) { out. println("<TR>"); // Step across the row, retrieving the data in each // column cell as a String. }

Accessing my. SQL from a servlet for(int i=1; i <= column. Count; i++) { out. print("<TD>" + result. Set. get. String(i)); } out. println("</TABLE>"); connection. close(); } catch(Class. Not. Found. Exception cnfe) { System. err. println("Error loading driver: " + cnfe); } catch(SQLException sqle) { System. err. println("Error connecting: " + sqle); } catch(Exception ex) { System. err. println("Error with input: " + ex); } } }

Accessing my. SQL from a servlet private static void show. Results(Result. Set results) throws SQLException { while(results. next()) { System. out. print(results. get. String(1) + " "); } System. out. println(); } private static void print. Usage() { System. out. println("Usage: Prepared. Statements host " + "db. Name username password " + "vendor [print]. "); } public void do. Post(Http. Servlet. Request in. Request, Http. Servlet. Response out. Response) throws Servlet. Exception, IOException { do. Get(in. Request, out. Response); }