数据库操作

以前看过的数据库操作,都是走马观花,今天重新敲了一遍,方便以后复习. 代码如下:

import java.sql.*;

public class delete {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/runoob";
    static final String USER = "root";
    static final String PASS = "admin";


    public static void main(String[] args) {
        delete t = new delete();
        try {
            t.delete();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public void delete() throws ClassNotFoundException {



        Class.forName(JDBC_DRIVER);
        System.out.println("成功加载驱动");

        Connection conn;
        try {
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            Statement stmt = conn.createStatement();
            System.out.println("成功连接数据库");
            //显示
            String sql = "select * from websites";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                System.out.print(rs.getInt(1)+" ");
                System.out.print(rs.getString(2)+" ");
                System.out.print(rs.getString(3)+" ");
                System.out.println(rs.getString(4));
            }
            //修改
            String sql2 = "update websites set name=? where id = ?";
            PreparedStatement pst = conn.prepareStatement(sql2);
            pst.setString(1,"菜鸟");
            pst.setInt(2,3);
            pst.executeUpdate();
            //删除
            String sql3 = "delete from websites where id =?";
            pst = conn.prepareStatement(sql3);
            pst.setInt(1,5);
            pst.executeUpdate();


            rs.close();
            stmt.close();
            conn.close();

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




    }
}

2.修改已存在的表

alter table newpic change id id int(10) auto_increment primary key;

3.删除数据后让id从1开始增长

truncate table 表名;

4.查看表结构

desc tableName;

5.查看之前操作的信息

desc select * from tableName where i = 3 \G//\G是转置,后面不需要分号

6.添加普通索引

later table t2 add index In_name(name);

7.删除普通索引

alter table t2 drop index in_named;//对表结构的操作都是alter

8.查看表中所有的索引

show index from t2;

9.后期维护数据表字段添加字段

alter table t1 add age int ;

10.修改字段

alter table t1 modify age int not null default 20;

11.删除字段

alter table t1 drop age;

12.修改列名

alter table t1 change name username varchar(30);

13.修改表名

alter table t1 rename t2

14.多表联查

select * form t_person p ,t_address,d where p.pid = a.pid and p.pid = 1;//起别名,取交集