JavaEE_day08_MySQL单表&JDBC

一、对数据库的操作：

进入sql：

打开命令提示符，输入以下命令：

mysql  -u [username] -p [password]
  1. 创建一个库：
//方式一
create database 库名;
//方式二
create database 库名 character set 编码;
  1. 查看数据库：
//查看所有数据库
show databases;
//查看带有编码的数据库
show create database web08_01;
Paste_Image.png
  1. 删除一个库：
drop database 库名；
Paste_Image.png
  1. 使用库：
use 库名；
  1. 查看当前正在操作的库：
select database（）；
Paste_Image.png

二、对数据库表的操作：

  1. 创建一张表：
create table 表名(
    字段名   类型(长度)    [约束],
    字段名   类型(长度)    [约束],
    字段名   类型(长度)    [约束]
);
Paste_Image.png
  1. 查看数据库表：
show tables；
Paste_Image.png
  1. 查看表的结构：
desc 表名；
Paste_Image.png
  1. 删除一张表：
drop table 表名；
  1. 修改表：
  • 添加一列：
alter  table  表名  add  字段名   类型(长度)   [约束] ；
  • 修改类的类型（长度、约束）：
alter  table  表名  modify  要修改的字段名    类型(长度)    [约束]；
  • 修改列的列名：
alter   table   表名   change   旧列名   新列名   类型(长度)    [约束] ；
Paste_Image.png
  • 删除表的列：
alter   table   表名   drop   列名 ；
Paste_Image.png
  • 修改表名：
rename   table   旧表名   to  新表名 ；
Paste_Image.png
  • 修改表的字符集：
alter   table   表名   character   set   编码 ；
Paste_Image.png
Paste_Image.png

三、对数据库表记录进行操作：

  1. 插入记录：
    方式 1：
insert  into  表名(列名1,列名2,列名3……)   values(值1,值2,值3……) ；

方式 2：

insert into 表名 values(值1,值2,值3……)；
Paste_Image.png

解决中文乱码的方法：

set names gbk ；
  1. 修改表记录：
  • 不带条件的：
update 表名 set 字段名=值, 字段名=值, 字段名=值……
  • 带条件的：
update 表名 set字段名=值, 字段名=值, 字段名=值…… where 条件
Paste_Image.png
  1. 删除表记录：
    • 不带条件的：
delete from 表名；
  • 带条件的：
delete from 表名 where 条件；

此时删除后，uid不会重置。


Paste_Image.png

说说delete与truncate的区别？
delete删除的时候是一条一条的删除记录，它配合事务，可以将删除的数据找回。
truncate删除，它是将整个表摧毁，然后再创建一张一模一样的表。它删除的数据无法找回。

start transaction;
delete from 表名；
rollback；

通过开启一个事物， 可以把delete的表回滚找回来，id不会重置
而truncate无法找回，id会重置。

start transaction;
truncate table 表名；
rollback；

四、查询：

  1. 查询所有商品：
select * from product ；
  1. 查询商品名和商品价格:
select pname,price from product;
  1. 查询所有商品信息使用表别名:
select * from product as p;

as可以省略。


  1. 查询商品名，使用列别名：
select pname as p from product;
  1. 去掉重复值(按照价格)：
select distinct(price) from product;
  1. 将所有的商品的价格+10进行显示:
select pname,price+10 from product;

7.条件查询：

  • 查询商品名称为"左慈"的商品信息：
select * from product where pname='左慈';
  • 查询价格>60元的所有商品信息:
select * from product where price>60;
  • 查询商品名称含有"士"字的商品信息:
select * from product where pname like '%士%';
  • 查询商品id在(3,6,9)范围内的所有商品信息:
select * from product where pid in(3,6,9);
  • 查询商品名称含有"士"字并且id为6的商品信息:
select * from product where pname like '%士%' and pid=6;
  • 查询id为2或者6的商品信息:
select * from product where pid=2 or pid=6;

8.排序：

  • 查询所有的商品，按价格进行排序(升序、降序):
    asc：升序
    desc：降序
select * from product order by price asc;
  • 查询名称有"士"的商品信息并且按照价格降序排序:
select * from product where pname like '%士%' order by price desc;

9.聚合函数：

  • 获得所有商品的价格的总和：
select sum(price) from product;
  • 获得所有商品的平均价格:
select avg(price) from product;
  • 获得所有商品的个数:
select count(*) from product;

10.分组操作：
准备工作：


  • 根据cid字段分组，分组后统计商品的个数：
select cid,count(*) from product group by cid;
  • 根据cid分组，分组统计每组商品的平均价格，并且平均价格大于20000元：
select cid,avg(price) from product group by cid having avg(price)>20000;
  • 使用Limit关键字进行查询操作：
    limit有两个参数：
    第一个参数：（要查询第几页 -1）*第二个参数
    第二个参数：每页显示的数目

例：每页显示3条记录，要查询第三页。

select * from product limit 6,3;

五、JDBC：

  • 概念：
    JDBC（Java DataBase Connectivity）就是Java数据库连接，说白了就是用Java语言来操作数据库。

  • JDBC核心类介绍：
    DriverManager、Connection、Statement、ResultSet

1.DriverManager：
两个作用：
(1)注册驱动：

//可能出现ClassNotFoundException异常，原因：没导jar包、类名书写错误
Class.forName("com.mysql.jdbc.Driver");

(2)获取连接：

//web08是数据库的名称，root是数据的用户名，a12345是数据库的密码
//出现SQLException异常
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web08","root","a12345");

2.Connection:
主要用来获取Statement:

Statement stmt = conn.createStatement();

3.Statement:
有三个常用方法：
(1) int executeUpdate(String sql)：
执行更新操作，即执行insert、update、delete语句；
(2) ResultSet executeQuery(String sql):
执行查询操作，执行查询操作会返回ResultSet，即结果集；
(3) boolean execute():
用来执行增、删、改、查所有SQL语句；
注：
如果使用execute()方法执行的是更新语句，那么还要调用int getUpdateCount()来获取insert、update、delete语句所影响的行数；
如果使用execute()方法执行的是查询语句，那么还要调用ResultSet getResultSet()来获取select语句的查询结果。

4.ResultSet:
获取结果集代码如下：

ResultSet rs = stmt.executeQuery(sql);

方法：next（）表示查看结果集是否为空

//表示结果集中已经有查询到的结果
rs.next();

读取数据的方法如下：
根据列数：

String getString(int columnIndex)：获取指定列的String类型数据；
int getInt(int columnIndex)：获取指定列的int类型数据；
double getDouble(int columnIndex)：获取指定列的double类型数据；
boolean getBoolean(int columnIndex)：获取指定列的boolean类型数据；
Object getObject(int columnIndex)：获取指定列的Object类型的数据。

根据名称：

String getString(String columnName)：获取名称为columnName的列的String数据；
int getInt(String columnName)：获取名称为columnName的列的int数据；
double getDouble(String columnName)：获取名称为columnName的列的double数据；
boolean getBoolean(String columnName)：获取名称为columnName的列的boolean数据；
Object getObject(String columnName)：获取名称为columnName的列的Object数据；

PreparedStatement：

预编译声明，是Statement的子接口。
优点：
（1）防止SQL攻击；
（2）提高代码的可读性，以可维护性；
（3）提高效率。
使用模板如下：

String sql = “select * from tab_student where s_number=?”;
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, “S_1001”);
ResultSet rs = pstmt.executeQuery();
rs.close();
pstmt.clearParameters();
pstmt.setString(1, “S_1002”);
rs = pstmt.executeQuery();
JDBC的开发步骤：
public void login(String username, String password) throws ClassNotFoundException, SQLException {
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2.获取连接
        Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/web08", "root",
                "a12345");
        // 3.获取执行sql语句的对象
        Statement stmt = (Statement) conn.createStatement();
        // 4.书写一个sql语句
        String sql = "select * from tbl_user where uname='" + username + "' and upassword='" + password + "'";
        // 5.执行sql语句
        ResultSet rs = stmt.executeQuery(sql);
        // 6.对结果集进行处理
        if (rs.next()) {
            System.out.println("恭喜您，" + username + ",登录成功!");
            System.out.println(sql);
        } else {
            System.out.println("账号或密码错误!");
        }
        // 7.釋放資源
        rs.close();
        stmt.close();
        conn.close();
    }
规范化代码如下：
    @Test
    public void query() {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            stmt = con.createStatement();
            String sql = "select * from user";
            rs = stmt.executeQuery(sql);
            while(rs.next()) {
                String username = rs.getString(1);
                String password = rs.getString(2);
                System.out.println(username + ", " + password);
            }
        } catch(Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if(rs != null) rs.close();
                if(stmt != null) stmt.close();
                if(con != null) con.close();
            } catch(SQLException e) {}
        }
    }
下面用PreparedStatement完成，没有sql注入问题
public void login1(String username, String password) throws ClassNotFoundException, SQLException {
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2.获取连接
        Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/web08", "root",
                "a12345");
        // 3.编写sql语句
        String sql = "select * from tbl_user where uname=? and upassword=?";
        // 4.创建预处理对象
        PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
        // 5.设置占位符
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        // 6.执行查询操作
        ResultSet rs = pstmt.executeQuery();
        // 7.对结果集进行处理
        if (rs.next()) {
            System.out.println("恭喜您，" + username + ",登录成功!");
            System.out.println(sql);
        } else {
            System.out.println("账号或密码错误!");
        }
        //8.释放资源
        rs.close();
        pstmt.close();
        conn.close();
    }

使用JDBC完成增删改查 的实例：

db.properties文件：版本2和版本3需要用上。

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/web08?useUnicode=true&characterEncoding=utf8
username=root
password=a12345

JDBCUtils_V1类：

 package cn.zl.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
  * 提供获取连接和资源的方法 
  * @author ZL
  *
  */

public class JDBCUtils_V1 {
    //获取连接方法
    public static Connection getConnection(){
        Connection conn=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/web08","root","a12345");
            
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;    
    }
    
    //释放资源
    public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

JDBCUtils_V2类：

 package cn.zl.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

/**
  * 提供获取连接和资源的方法 
  * @author ZL
  *
  */

public class JDBCUtils_V2 {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    
    //静态代码块加载配置文件信息
    static{
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        username=bundle.getString("username");
        password=bundle.getString("password");
        
    }
    //获取连接方法
    public static Connection getConnection(){
        Connection conn=null;
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(url,username,password);
            
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;    
    }
    
    //释放资源
    public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

JDBCUtils_V3类：

package cn.zl.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;

/**
 * 提供获取连接和资源的方法
 * 
 * @author ZL
 *
 */

public class JDBCUtils_V3 {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    // 静态代码块加载配置文件信息
    static {
        try {
            // 1/通过当前类获取类加载器
            ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader();
            // 2.通过类加载器的方法获得一个输入流
            InputStream is = classLoader.getResourceAsStream("db.properties");
            // 3.创建一个properties对象
            Properties props = new Properties();
            // 4.加载输入流
            props.load(is);
            // 5.获取相关参数的值
            driver = props.getProperty("driver");
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    // 获取连接方法
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    // 释放资源
    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

TestUtils类：

package cn.zl.jdbc.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import cn.zl.jdbc.JDBCUtils_V1;
import cn.zl.jdbc.JDBCUtils_V2;
import cn.zl.jdbc.JDBCUtils_V3;

/**
 * 测试工具类
 * @author ZL
 *
 */
public class TestUtils {
    
    /**
     * 根据id更新用户信息方法
     */
    @Test
    public void testUpdateById(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 1.获取连接
            conn = JDBCUtils_V3.getConnection();
            // 2.编写sql语句
            String sql = "update tbl_user set upassword=? where uid=?";
            // 3.获取执行sql语句对象
            pstmt = conn.prepareStatement(sql);
            // 4.设置参数
            pstmt.setString(1,"555");
            pstmt.setInt(2,2);
            // 5.执行更新操作
            int row = pstmt.executeUpdate();
            if (row > 0) {
                System.out.println("更新成功!");
            } else {
                System.out.println("更新失败!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            JDBCUtils_V3.release(conn, pstmt, null);
        }
    }
    
    
    /**
     * 根据id删除信息
     */
    @Test
    public void testDeleteById(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 1.获取连接
            conn = JDBCUtils_V3.getConnection();
            // 2.编写sql语句
            String sql = "delete from tbl_user where uid=?";
            // 3.获取执行sql语句对象
            pstmt = conn.prepareStatement(sql);
            // 4.设置参数
            pstmt.setInt(1,3);
            // 5.执行删除操作
            int row = pstmt.executeUpdate();
            if (row > 0) {
                System.out.println("删除成功!");
            } else {
                System.out.println("删除失败!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            JDBCUtils_V3.release(conn, pstmt, null);
        }
    }
    
    /**
     * 添加用户信息
     */
    @Test
    public void testAdd() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 1.获取连接
            conn = JDBCUtils_V2.getConnection();
            // 2.编写sql语句
            String sql = "insert into tbl_user values(null,?,?)";
            // 3.获取执行sql语句对象
            pstmt = conn.prepareStatement(sql);
            // 4.设置参数
            pstmt.setString(1, "wangwu");
            pstmt.setString(2, "999");
            // 5.执行插入操作
            int row = pstmt.executeUpdate();
            if (row > 0) {
                System.out.println("添加成功!");
            } else {
                System.out.println("添加失败!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            JDBCUtils_V2.release(conn, pstmt, null);
        }
    }

    //根据id查询用户信息
    @Test
    public void testFindUserById(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            //1.获取连接
            conn= JDBCUtils_V1.getConnection();
            //2.编写sql语句
            String sql="select * from tbl_user where uid=?";
            //3获取执行sql语句对象
            pstmt = conn.prepareStatement(sql);
            //4.设置参数
            pstmt.setInt(1, 2);
            //5.执行 查询操作
            rs = pstmt.executeQuery();
            //6.结果集处理
            while(rs.next()){
                System.out.println(rs.getString(2)+"----"+rs.getString("upassword"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtils_V1.release(conn, pstmt, rs);
        }
    }

}
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,324评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,303评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,192评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,555评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,569评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,566评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,927评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,583评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,827评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,590评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,669评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,365评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,941评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,928评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,159评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,880评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,399评论 2赞 342

推荐阅读更多精彩内容

  • html_day8 MySQL与JDBC回顾
    一、cmd操作数据库1.创建一个库create database 库名create database 库名 cha...
    路人爱早茶阅读 326评论 0赞 1
  • sql
    50个常用的sql语句Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname...
    哈哈海阅读 1,224评论 0赞 7
  • 《致解忧杂货店朗沙先生的一封信》
    朗沙先生: 你好！ 昨晚无意间从网上听到了关于《解忧杂货店》的故事，心中动起了想给你写一封信，说说我内...
    微笑达达阅读 728评论 0赞 2
  • 你那么不好时，还有人把你当宝
    看到一个故事，有一个姑娘和闺密一起翻当初的老照片，在看到初中时的一张照片时，自己都忍不吐糟:"简直丑爆了，好想自戳...
    纤陌颜阅读 775评论 0赞 2
  • 大学的感悟
    我们是初出社会的大学生，对梦想和未来怀有很高的期待，而当前的就业压力和我们自身的实践经验匮乏促使我们必须从基层做起...
    92b70021cc01阅读 244评论 0赞 0