Database - mysql

1. Database- mysql

2. Contents • Database • DBMS • Relational model • SQL • MySQL

3. Database • Database • a collection of data • Entity, relationship • DBMS • Database management system

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

5. 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

6. Students(sid:string, sname:string, snum:integer) Fields (attributes, columns) Field names Tuples (records, rows)

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

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

9. MySQL • 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;

10. MySQL • 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];

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

12. MySQL • ALTER TABLE • ALTER TABLE table_name [RENAME new_table_name]/ [ADD field_name type]/ [DROP field_name]/ [CHANGE name new_name new_type];

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

14. 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)

15. MySQL Data Types

16. 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)

17. Primary Key • 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.

18. 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

19. 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 them: SELECT * FROM t1, t2 WHERE t1.id = t2.id;

20. Create table with foreign keys employee2 • create table employee2( • 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 employee2(empno) • on delete cascade) type=innodb; employer

21. MySQL Table Types • If we want to use Foreign Key • InnoDB tables • Otherwise • Default table type, MyISAM • In SQL queries you can freely mix InnoDB type tables with other table types of MySQL, even within the same query.

22. 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)

23. 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)

24. 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)

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

26. Using Connector/J • Download the software from • http://dev.mysql.com/downloads/connector/j/5.0.html • MySQL 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 Files\Java\jdk1.5.0_07\jre\lib\ext • or similar directory on your machine

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

28. Accessing mySQL from a servlet • package dbpackage; • import java.io.*; • import java.sql.*; • import javax.servlet.*; • import javax.servlet.http.*; • public class StudentList extends HttpServlet { • public void doGet(HttpServletRequest request, • HttpServletResponse response) • throws ServletException, IOException { • response.setContentType("text/html"); • PrintWriter out = response.getWriter(); • String docType = • "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " + "Transitional//EN\"\n"; • String title = "Student List in mySQL"; • out.print(docType + • "<HTML>\n" + • "<HEAD><TITLE>" + title + "</TITLE></HEAD>\n" + • "<BODY BGCOLOR=\"#FDF5E6\"><CENTER>\n" + • "<H1>Database Results</H1>\n");

29. Accessing mySQL 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;"; • showTable(driver, url, username, password, query, out); • out.println("</CENTER></BODY></HTML>"); • } • public void showTable(String driver, String url, String username, String password, • String query, PrintWriter out) { • try { • // Load database driver if it's not already loaded. • Class.forName(driver); • // Establish network connection to database. • Connection connection = DriverManager.getConnection(url, username, password); • // Look up info about the database as a whole. • DatabaseMetaData dbMetaData = connection.getMetaData(); • out.println("<UL>"); • String productName = dbMetaData.getDatabaseProductName(); • String productVersion = dbMetaData.getDatabaseProductVersion(); • out.println(" <LI><B>Database:</B> " + productName + " • <LI><B>Version:</B> " + productVersion + "</UL>");

30. Accessing mySQL from a servlet • Statement statement = connection.createStatement(); • // Send query to database and store results. • ResultSet resultSet = statement.executeQuery(query); • // Print results. • out.println("<TABLE BORDER=1>"); • ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); • int columnCount = resultSetMetaData.getColumnCount(); • out.println("<TR>"); • // Column index starts at 1 (a la SQL), not 0 (a la Java). • for(int i=1; i <= columnCount; i++) { • out.print("<TH>" + resultSetMetaData.getColumnName(i)); • } • out.println(); • // Step through each row in the result set. • while(resultSet.next()) { • out.println("<TR>"); • // Step across the row, retrieving the data in each • // column cell as a String. • }

31. Accessing mySQL from a servlet • for(int i=1; i <= columnCount; i++) { • out.print("<TD>" + resultSet.getString(i)); • } • out.println(); • } • out.println("</TABLE>"); • connection.close(); • } catch(ClassNotFoundException 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); • } • } • }

32. Accessing mySQL from a servlet • private static void showResults(ResultSet results) • throws SQLException { • while(results.next()) { • System.out.print(results.getString(1) + " "); • } • System.out.println(); • } • private static void printUsage() { • System.out.println("Usage: PreparedStatements host " + • "dbName username password " + • "vendor [print]."); • } • public void doPost(HttpServletRequest inRequest, • HttpServletResponse outResponse) • throws ServletException, IOException { • doGet(inRequest, outResponse); • }