mysql安装，java调用使用记录

mysql安装，java调用使用记录

1.安装

https://www.jianshu.com/p/07a9826898c0

#登入：
mysql -uroot -p338202aA

mysql workbench可以直接查看表

2.使用

常见命令

#查询所有数据库
show data s; 
use data Name;
#查询该数据库中所有的表
show tables; 
show tables from mysql;
#选择CHARACTER_SETS这一个表中的数据
select * from CHARACTER_SETS;

3.java链接mysql,创建，插入，查询表

3.1 maven

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.6</version>
</dependency>
#https://crunchify.com/java-mysql-jdbc-hello-world-tutorial-create-connection-insert-data-and-retrieve-data-from-mysql/

3.2 java链接mysql，创建表，插入查询表。

package TestMysql1;

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



public class MySqlTest {
    // JDBC driver name and data  URL
    static final String JDBC_DRIVER = \"com.mysql.jdbc.Driver\";
    static final String DB_URL = \"jdbc:mysql://localhost:3306/mysql\";

    //  Data  credentials
    static final String USER = \"****\";
    static final String PASS = \"****\";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
            //STEP 2: Register JDBC driver
            Class.forName(\"com.mysql.jdbc.Driver\");

            //STEP 3: Open a connection
            System.out.println(\"Connecting to a selected data ...\");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println(\"Connected data  successfully...\");

            //STEP 4: Execute a query
            System.out.println(\"Creating table in given data ...\");
            stmt = conn.createStatement();
            //创建

//            String sql = \"CREATE TABLE REGISTRATION \" +
//                    \"(id INTEGER not NULL, \" +
//                    \" first VARCHAR(255), \" +
//                    \" last VARCHAR(255), \" +
//                    \" age INTEGER, \" +
//                    \" PRIMARY KEY ( id ))\";
//
//            stmt.executeUpdate(sql);
//            System.out.println(\"Created table in given data ...\");


              //插入
//            String sql = \"INSERT INTO Registration \" +
//                    \"VALUES (100, \'Zara\', \'Ali\', 18)\";
//            stmt.executeUpdate(sql);
//            sql = \"INSERT INTO Registration \" +
//                    \"VALUES (101, \'Mahnaz\', \'Fatma\', 25)\";
//            stmt.executeUpdate(sql);
//            sql = \"INSERT INTO Registration \" +
//                    \"VALUES (102, \'Zaid\', \'Khan\', 30)\";
//            stmt.executeUpdate(sql);
//            sql = \"INSERT INTO Registration \" +
//                    \"VALUES(103, \'Sumit\', \'Mittal\', 28)\";
//            stmt.executeUpdate(sql);
//            System.out.println(\"Inserted records into the table...\");

            //查询
            String sql = \"SELECT id, first, last, age FROM Registration\";
            ResultSet rs = stmt.executeQuery(sql);
            //STEP 5: Extract data from result set
            while(rs.next()){
                //Retrieve by column name
                int id  = rs.getInt(\"id\");
                int age = rs.getInt(\"age\");
                String first = rs.getString(\"first\");
                String last = rs.getString(\"last\");

                //Display values
                System.out.print(\"ID: \" + id);
                System.out.print(\", Age: \" + age);
                System.out.print(\", First: \" + first);
                System.out.println(\", Last: \" + last);
            }
            rs.close();


        }catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                    conn.close();
            }catch(SQLException se){
            }// do nothing
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println(\"Goodbye!\");
    }//end main
}
//创建表，http://wiki.jikexueyuan.com/project/jdbc/create-tables.html