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(sidstring, snamestring, snuminteger)
Fields (attributes, columns)
sid sname snum
euny Chang Eun Young 99
cavalist Ryu Han Seung 99
netj Jaeho Shin 00
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 ppassword dbname

gtmysql -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 mysqlgt
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_namevalue,..
• WHERE condition

11
MySQL

• Data manipulation(2)
• SELECT field_name as field_name,..
• FROM table_name
• WHERE condition
• ORDER BY field_name
• , lt, gt, 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
mysqlgt show databases ---------- Database
---------- mysql test
---------- 2 rows in set (0.00 sec) mysqlgt
use test Database changed mysqlgt create
database test2 Query OK, 1 row affected (0.00
sec)
14
mysqlgt CREATE TABLE Students( -gt sid
VARCHAR(8) NOT NULL, -gt sname VARCHAR(20),
-gt snum INT, -gt PRIMARY KEY(sid)) Query OK,
0 rows affected (0.01 sec)
15
MySQL Data Types
Type Size Description
int tinyint,smallint,mediumint,bigint 4 bytes 1,2,3 and 8 bytes 232 values
float double 4 bytes 8 bytes
Date 3 bytes In the format of YYYY-MM-DD
Time 3 bytes In the format of HHMMSS
charlength varcharlength fi String length 1 byte Fixed length string Variable length from 0 to 255 characters long
16
mysqlgt show tables ----------------
Tables_in_test ---------------- students
---------------- 1 row in set (0.00
sec) mysqlgt 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
empno (PK) salary
100 200.85
200 129.54
300 98.17

• 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) typeinnodb

employer
id employee_no(FK)
51 100
52 100
53 200
54 300
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
mysqlgt INSERT INTO Students -gt VALUES
('euny', 'Chang Eun-young', 99) Query OK, 1 row
affected (0.00 sec) mysqlgt 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) mysqlgt DELETE FROM
Students -gt WHERE sid'euny' Query OK, 1 row
affected (0.00 sec)
23
mysqlgt UPDATE Students -gt SET sid'asdf'
-gt WHERE sid'euny' Query OK, 1 row affected
(0.00 sec) mysqlgt 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
mysqlgt 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) mysqlgt SELECT sname FROM
Students WHERE snum99 -----------------
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.htm
  l
• 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

• ltHTMLgt
• ltBODYgt
• ltTITLEgtSee Account Informationlt/TITLEgt
• Enter account number to viewltBRgt
• ltform action"StudentList" method"post"gt
• ltinput name"account"gt
• ltinput type"submit" name"submit"
  value"submit"gt
• lt/formgt
• lt/BODYgt
• lt/HTMLgt

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
• "lt!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML
  4.0 " "Transitional//EN\"\n"
• String title "Student List in mySQL"
• out.print(docType
• "ltHTMLgt\n"

29
Accessing mySQL from a servlet

• String driver "com.mysql.jdbc.Driver"
• String url "jdbcmysql//localhost/newdb"
• String username "ira"
• String password "mypassword"
• String query "SELECT from student"
• showTable(driver, url, username, password,
  query, out)
• out.println("lt/CENTERgtlt/BODYgtlt/HTMLgt")
• 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.getCon
  nection(url, username, password)
• // Look up info about the database as a whole.
• DatabaseMetaData dbMetaData
  connection.getMetaData()
• out.println("ltULgt")

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("ltTABLE BORDER1gt")
• ResultSetMetaData resultSetMetaData
  resultSet.getMetaData()
• int columnCount resultSetMetaData.getColum
  nCount()
• out.println("ltTRgt")
• // Column index starts at 1 (a la SQL), not
  0 (a la Java).
• for(int i1 i lt columnCount i)
• out.print("ltTHgt" resultSetMetaData.getCo
  lumnName(i))
• out.println()
• // Step through each row in the result set.
• while(resultSet.next())
• out.println("ltTRgt")
• // Step across the row, retrieving the
  data in each
• // column cell as a String.

31
Accessing mySQL from a servlet

• for(int i1 i lt columnCount i)
• out.print("ltTDgt" resultSet.getString(i
  ))
• out.println()
• out.println("lt/TABLEgt")
• 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)