Transcript PowerPoint 프레젠테이션 - City University of New York

Database
- mysql
Contents
Database
– DBMS
– Relational model
SQL
– MySQL
Database
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
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>
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;
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];
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 “_%….”)
MySQL
ALTER TABLE
– ALTER TABLE table_name
[RENAME new_table_name]/
[ADD field_name type]/
[DROP field_name]/
[CHANGE name new_name new_type];
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)
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)
MySQL Data Types
Type
Size
Description
int
tinyint,smallint,mediumint,
bigint
4 bytes
1,2,3 and 8 bytes
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
Primary Key is a column or set of columns
Uniquely identifies the rest of the data in any giv
en 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 co
rresponding 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 c
heck and enforce referential integrity, not
to join tables. If you want to get results
from multiple tables from a SELECT stat
ement, you do this by performing a join b
etween
them:
SELECT
* FROM t1, t2 WHERE t1.id = t2.id;
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;
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
MySQL Table Types
If we want to use Foreign Key
– InnoDB tables
Otherwise
– Default table type, MyISAM
In SQL queries you can freely mix
In
noDB type tables with other table
ty
pes of MySQL, 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
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.

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

Accessing mySQL from a servle
t
<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>
Accessing mySQL from a servle
t
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");
Accessing mySQL from a servle
t
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>");
Accessing mySQL from a servle
tStatement 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.
Accessing mySQL from a servle
tfor(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);
}
Accessing mySQL from a servle
tprivate 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);
}