mysql数据库基础操作记录

安装mysql

Mac使用homebrew安装mysql，命令行执行以下命令：brew install mysql

启动mysql服务

安装完成后执行start 命令。

➜  ~ mysql.server start
Starting MySQL
. SUCCESS!

连接数据库

命令行方式

默认用户是root，密码为空。

➜  ~ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 Homebrew

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

JDBC方式

导入JDBC jar包

通过maven方式引入

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>

下载后，add进项目

可以直接在mysql官网下载jar包，复制在项目里面，然后添加进依赖即可。

连接mysql

package com.test.jdbc;

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

public class DBhelper{
    public static void main (String[] args){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
            conn.close();
        } catch(SQLException e){
            e.printStackTrace();
        }
    }
}

数据库操作

数据库操作指令

1. 创建：create database test_db;
2. 查询数据库：show databases;
3. 删除数据库：drop test_db;

增删改查操作指令

命令行方式

插入数据
insert into 表名 (列名....) values (数据....);

删除数据,不添加where条件会清楚整个表的数据
delete from 表名 where 条件;

更新数据,修改id为9的内容name为空
update 表名 set name='' where id=9;

查询数据
select * from table;

select 列名 from table;

select * from table where id = 9;

select * from table order by id;

连表查询
select t.id, tt.name from table1 as t inner join table2 as tt on t.id = tt.u_id;

JDBC操作增删改查

更新数据

插入，更新和删除都是对数据的更新操作，用相同的方法，只是传入的sql语句不同。

package com.test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TransactionTest {
    public static String url = "jdbc:mysql://localhost:3306/test";
    public static String user = "root";
    public static String password = "";
    public static String driverName = "com.mysql.jdbc.Driver";

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void update(String sql) {
        Connection conn = getConnection();
        try {
            Statement st = conn.createStatement();
            int count = st.executeUpdate(sql);
            System.out.println("更新了 " + count + " 条数据");
            st.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

查询数据

package com.test.jdbc;

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

public class DBHelper {
    public static final String url = "jdbc:mysql://127.0.0.1/test";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "root";
    public static final String password = "";

    Connection conn;
    PreparedStatement pst;
    ResultSet ret;

    private void ConnectionDB() {
        try {
            Class.forName(name);
            conn = DriverManager.getConnection(url,user,password);
            pst = conn.prepareStatement("select * from myclass;");
            ret = pst.executeQuery();
            int col = ret.getMetaData().getColumnCount();
            while (ret.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(ret.getString(i) + "\t");
                    if ((i == 2) && (ret.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                 }
                System.out.println("");
            }
            ret.close();
            pst.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        DBHelper dbHelper = new DBHelper();
        dbHelper.ConnectionDB();
    }
}

JDBC处理事务

事务是为了保证数据的一致性，要么事务中的数据操作语句都成功，失败的话立即回滚。

package com.test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TransactionTest {
    public static String url = "jdbc:mysql://localhost:3306/test";
    public static String user = "root";
    public static String password = "";
    public static String driverName = "com.mysql.jdbc.Driver";

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void update(String sql) {
        Connection conn = getConnection();
        try {
            Statement st = conn.createStatement();
            int count = st.executeUpdate(sql);
            System.out.println("更新了 " + count + " 条数据");
            st.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void rollbackTest() {
        Connection conn = getConnection();
        try {
            conn.setAutoCommit(false);
            Statement st = conn.createStatement();
            st.executeUpdate("insert into Websites values (8,'tutu','url',99,'CN')");
            st.executeUpdate("insert into access_log values ()");
            conn.commit();
            st.close();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        rollbackTest();
    }

}