數(shù)據(jù)庫(kù)

關(guān)系型數(shù)據(jù)庫(kù) <---> NoSQL

1.集合論+關(guān)系代數(shù)
2.用二維表保存數(shù)據(jù)

• 一行是一條記錄
• 一列是一個(gè)字段

關(guān)系型數(shù)據(jù)庫(kù)產(chǎn)品

• MySQL(LAMP = Linux + Apache + MySQL +PHP)
• Oracle
• DB2
• SQL Server

關(guān)系型數(shù)據(jù)庫(kù)編程語(yǔ)言 - SQL - 結(jié)構(gòu)化查詢語(yǔ)言

DDL（數(shù)據(jù)定義語(yǔ)言）create/drop/alter
DML（數(shù)據(jù)操作語(yǔ)言）insert/delete/update
DQL（數(shù)據(jù)查詢語(yǔ)言）select
DCL（數(shù)據(jù)控制語(yǔ)言）grant/revoke

MySQL客戶端GUI工具

• Navicat for MySQL
• Toad for MySQL
• SQLyog

SQL命令:

顯示所有數(shù)據(jù)庫(kù) show databases;
創(chuàng)建數(shù)據(jù)庫(kù) create database house;
使用數(shù)據(jù)庫(kù) use house;
顯示所有表 show tables;
創(chuàng)建表

create table tb_user`
    (
    userid integer primary key auto_increment,
    username varchar(20) not null unique,
    password varchar(20) not null,
    tel char(11),
    realname varchar(50) default '無(wú)名氏'
    );

描述表 desc tb_user;
看一下怎么建表 show create table tb_user;
新增(插入)記錄

insert into tb_user values (default,'admin','123456','13124355678','Kygo');

insert into tb_user (username,password) values ('kygo','123123');

查詢所有列 select * from tb_user;
刪除表中的記錄(行) delete from tb_user where username='kygo';
更新表中的記錄

update tb_user set password='654321',tel='18912344564' where 
username='admin';

刪除表 drop table tb_user;

JDBC - Java DataBase Connectivity

0.將驅(qū)動(dòng)程序放置到類路徑中 - JDBC規(guī)范針對(duì)某種特定數(shù)據(jù)庫(kù)的實(shí)現(xiàn)版本
1.加載驅(qū)動(dòng) - Class.forName(String)
2.建立連接 - DriverManager.getConnection(url, uid, pwd)
3.發(fā)SQL語(yǔ)句 - createStatement() / prepareStatement(String)

• executeQuery() / - executeUpdate()

4.處理結(jié)果集（對(duì)游標(biāo)進(jìn)行迭代）- next() / getXXX(String)
5.釋放連接 - close()

例子1:

        Connection con = null;
        try {
            // 1.加載驅(qū)動(dòng)
            Class.forName("com.mysql.jdbc.Driver");
            // 2.建立連接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/house", 
                    "root", "123456");
            // 3.發(fā)SQL語(yǔ)句
            Statement stmt = con.createStatement();
            // 如果執(zhí)行查詢將得到結(jié)果集(游標(biāo))對(duì)象
            ResultSet rs = stmt.executeQuery("select userid,username, password from tb_user");
            // 4.對(duì)結(jié)果集進(jìn)行迭代從每一行中取出對(duì)應(yīng)的列
            while (rs.next()) {
                System.out.print(rs.getInt("userid") + "\t");
                System.out.print(rs.getString("username") + "\t");
                System.out.println(rs.getString("password"));
                System.out.println("==================");
            }
            // 說(shuō)明: 如果Connection對(duì)象需要保留繼續(xù)使用 此處應(yīng)該先關(guān)閉ResultSet和Statement
            // 如果Connection對(duì)象不需要使用了那么就直接關(guān)閉Connection對(duì)象
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5.釋放連接
            try {
                if (con != null && !con.isClosed()) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

例子2:

        Scanner sc = new Scanner(System.in);
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/house?useUnicode=true&characterEncoding=utf-8", 
                    "root", "123456");
            String username = sc.next();
            String password = sc.next();
            String tel = sc.next();
            String realname = sc.next();
            PreparedStatement stmt = con.prepareStatement(
                    "insert into tb_user values (default,?,?,?,?)");
            stmt.setString(1, username);
            stmt.setString(2, password);
            stmt.setString(3, tel);
            stmt.setString(4, realname);
            if (stmt.executeUpdate() == 1) {
                System.out.println("新增用戶成功！");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            sc.close();
        }

跳轉(zhuǎn)和重定向

服務(wù)器端跳轉(zhuǎn)
req.getRequestDispatcher("add.jsp").forward(req, resp);

重定向(發(fā)送一個(gè)新的URL給瀏覽器讓瀏覽器重新請(qǐng)求新頁(yè)面)
resp.sendRedirect("add.jsp");