启动数据库
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 复制表的结构创建新的表
