启动数据库

D:\soft\mysql-5.7.24-winx64\mysql-5.7.24-winx64\bin>mysql -u root -p

创建数据库

CREATE DATABASE 数据库名;

创建表

CREATE TABLE table_name (column_name column_type);

mysql> use testdata;
Database changed
mysql> CREATE TABLE runoob_tbl(
    ->  runoob_id INT NOT NULL AUTO_INCREMENT,
    -> runoob_title VARCHAR(100) NOT NULL,
    ->  runoob_author VARCHAR(40) NOT NULL,
    -> PRIMARY KEY ( runoob_id )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.21 sec)

删除表

DROP TABLE table_name

插入数据

mysql> use testdata;
Database changed
mysql> INSERT INTO runoob_tbl
    ->  (runoob_id, runoob_title, runoob_author)
    -> VALUES
    ->  (1, "学习 PHP", "菜鸟教程");
Query OK, 1 row affected (0.01 sec)

查询数据

SELETE * from runoob_tbl; 查询整个表
SELETE * from runoob_tbl WHERE runoob_author LIKE '%COM';查询author字段包含com的项
SELECT * from runoob_tbl ORDER BY submission_date ASC; 查询结果按照date的正序

修改数据

mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

将id=3的那一项的title改成“学习c++”

删除数据

mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
Query OK, 1 row affected (0.01 sec)

将id=3的那一行删除

java连接数据库操作

package iOSAT.common;
import java.sql.*;

public class DataBase {
    static Statement statement;
    static Connection connection;
    static  PreparedStatement pst;
    static ResultSet ret;
    static int rett;
    //用于连接数据库,db_name为表名，sql为操作语句，field为需要获取的字段名称
//查询数据
    public static String connect(String db_name, String sql, String field)
    {
        String vcode=null;
        try
        {
            try
            {
                Class.forName( "com.mysql.cj.jdbc.Driver");//加载数据库驱动
                System.out.println("数据库驱动加载成功");

            }catch(ClassNotFoundException e){}
            //建立连接，需要内网
            connection= DriverManager.getConnection(String.format("jdbc:mysql://localhost:3306/%s",db_name),"database name","password");
            statement=connection.createStatement();
            //pst = connection.prepareStatement(sql);
            ret=statement.executeQuery(sql);
            ret.beforeFirst();
            ret.next();
            vcode=ret.getString(field);
            //System.out.println(vcode);
            /*while (ret.next()){
                String vcode=ret.getString("f_vcode");
                System.out.println(vcode);
            }*/
            statement.close();
            connection.close();
            //return vcode;
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("sorry,连接失败");
        }
        finally {
            return vcode;
        }
    }
//删除数据新增数据
    public static void update(String db_name, String sql){
        try
        {
            try
            {
                Class.forName( "com.mysql.cj.jdbc.Driver");//加载数据库驱动
                System.out.println("数据库驱动加载成功");

            }catch(ClassNotFoundException e){}
            //建立连接，需要内网
            connection= DriverManager.getConnection(String.format("jdbc:mysql://localhost:3306/%s",db_name),"database name","password");
            statement=connection.createStatement();
            rett=statement.executeUpdate(sql);
            statement.close();
            connection.close();
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("sorry,连接失败");
        }
    }
}

过滤重复数据

1、SELECT DISTINCT last_name
2、SELECT last_name GROUP BY last_name

复制表

1、SHOW CREATE TABLE 查看表的结构
2、CREATE TABLE 复制表的结构创建新的表

mysql常用语句学习