数据库的基本操作及JDBC

一、数据库的基本操作

　　1、登录数据库

　　　　登录数据库有两种方式：

　　　　　　方式一：mysql   -u用户名  -p密码

　　　　　　方式二：mysql   --host=ip地址   --user=用户名   --password=密码

　　2、SQL分类：

　　　　1>数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等 

　　　　2>数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等
　　　　3>数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。
　　　　4>数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

　　3、数据库的操作：database

　　　　1>创建数据库

　　　　      格式：

　　　　　　　　create database 数据库名;

   　　　　　　　create database 数据库名 character set 字符集;

　　　　2>查看数据库　　　　　

　　　　　　　　查看数据库MySQL服务器中的所有的数据库:

　　　　　　　　show databases;

　　　　　　　　查看某个数据库的定义的信息:

　　　　　　　　show create database 数据库名;

　　　　　　　　例如：

　　　　　　　　show create database day21_1;

　　　　3>删除数据库

　　　　　　drop database 数据库名称;
　　　　　　例如：
　　　　　　drop database day21_2;

　　　　4>其它的数据库操作命令

　　　　　　切换数据库：

　　　　　　use    数据库名；

　　　　　　例如：

　　　　　　use day21_1;

　　4、表结构相关的语句

　　　　1>创建表　　　　

　　　　　　create table 表名(

   　　　　　　字段名 类型(长度) 约束,

   　　　　　　字段名 类型(长度) 约束

　　　　　　);

　　　　　　例如：

　　　　　　###创建分类表

　　　　　　CREATE TABLE sort (

 　　　　　　　　sid INT, #分类ID

 　　　　　　 sname VARCHAR(100) #分类名称

　　　　　　);

　　　　2>主键约束及删除主键

　　　　　　方式一：在字段后面加上primary   key

　　　　　　　　　例如：id   Int     primary  key,

　　　　　　方式二：在表的结尾进行标注

　　　　　　　　　例如：primary   key(id)

　　　　　　删除主键：alter   table   表名    drop   primary   key;

　　　　3>　查看表及删除表

　　　　　　　　查看数据库中所有表

　　　　　　　　格式：show    tables;

　　　　　　　　查看表结构

　　　　　　　　格式：desc    表名；

　　　　　　　　删除表

　　　　　　　　格式：drop   table   表名；

　　　　4>修改表结构格式　　　　　

　　　　　　　　alter table 表名 add 列名 类型(长度) 约束;
　　　　　　　　　　作用：修改表添加列.
　　　　　　　　　　例如：
　　　　　　　　　　#1，为分类表添加一个新的字段为 分类描述 varchar(20)
　　　　　　　　　　ALTER TABLE sort ADD sdesc VARCHAR(20);

　　　　　　　　alter table 表名 modify 列名 类型(长度) 约束;
　　　　　　　　　　作用：修改表修改列的类型长度及约束.
　　　　　　　　　　例如：
　　　　　　　　　　#2, 为分类表的分类名称字段进行修改，类型varchar(50) 添加约束 not null
　　　　　　　　　　ALTER TABLE sort MODIFY sname VARCHAR(50) NOT NULL;

　　　　　　　　alter table 表名 change 旧列名 新列名 类型(长度) 约束;
　　　　　　　　　　作用：修改表修改列名.
　　　　　　　　　　例如：
　　　　　　　　　　#3, 为分类表的分类名称字段进行更换 更换为 snamesname varchar(30)
　　　　　　　　　　ALTER TABLE sort CHANGE sname snamename VARCHAR(30);

　　　　　　　　alter table 表名 drop 列名;
　　　　　　　　　　作用：修改表删除列.
　　　　　　　　　　例如：
　　　　　　　　　　#4, 删除分类表中snamename这列
　　　　　　　　　　ALTER TABLE sort DROP snamename;

　　　　　　　　rename table 表名 to 新表名;
　　　　　　　　　　作用：修改表名
　　　　　　　　　　例如：
　　　　　　　　　　#5, 为分类表sort 改名成 category
　　　　　　　　　　RENAME TABLE sort TO category;

　　　　　　　alter table 表名 character set 字符集;
　　　　　　　　　　作用：修改表的字符集
　　　　　　　　　　例如：
　　　　　　　　　　#6, 为分类表 category 的编码表进行修改，修改成 gbk
　　　　　　　　　　ALTER TABLE category CHARACTER SET gbk;

　　　5>插入表记录　　　　　

　　　　　　 语法：

　　　　　　　　insert into 表 (列名1,列名2,列名3..) values  (值1,值2,值3..); -- 向表中插入某些列

　　　　　　　　insert into 表 values (值1,值2,值3..); --向表中插入所有列

　　　6>更新表的记录

        　　　 语法：

　　　　　　　　update 表名 set 字段名=值,字段名=值;

　　　　　　　　update 表名 set 字段名=值,字段名=值 where 条件;

　　　7>删除记录：delete

　　　        语法：

　　　　　　　　delete from 表名 [where 条件];

　　　　　　　　或者

　　　　　　　　truncate table 表名;

　　　　　

         　　面试题：

　　　　　　　　删除表中所有记录使用delete from 表名; 还是用truncate table 表名;

　　　　　　　　删除方式：delete 一条一条删除，不清空auto_increment记录数。

　　　　　　　　truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。

　　　　8>SQL查询语句　　　　　

         　　　　查询指定字段信息

　　　　　　　　select 字段1,字段2,...from 表名;

　　　　        查询表中所有字段

　　　　　　　　select * from 表名; 

　　　　       distinct用于去除重复记录

　　　　　　　　select distinct 字段 from 表名;

　　　　       别名查询，使用的as关键字，as可以省略的.

　　　　　　　　别名可以给表中的字段，表设置别名。 当查询语句复杂时，使用别名可以极大的简便操作。

　　　　　　　　表别名格式:

　　　　　　　　select * from 表名 as 别名;

　　　　　　　　或

　　　　　　　　select * from 表名 别名;

　　　　　　　　列别名格式：

　　　　　　　　select 字段名 as 别名 from 表名;

　　　　　　　　或

　　　　　　　　select 字段名 别名 from 表名;　　　　　

　　　　where语句表条件过滤。满足条件操作，不满足不操作，多用于数据的查询与修改。

　　　　　　　　格式 :select 字段  from 表名  where 条件;  　　　　　　　

　　　　　　　　例如：
　　　　　　　　查询所有吃饭支出记录
　　　　　　　　SELECT * FROM zhangwu WHERE name = '吃饭支出';

　　　　　　　　查询出金额大于1000的信息
　　　　　　　　SELECT * FROM zhangwu WHERE money >1000;

　　　　　　　　查询出金额在2000-5000之间的账务信息
　　　　　　　　SELECT * FROM zhangwu WHERE money >=2000 AND money <=5000;
　　　　　　　　或
　　　　　　　　SELECT * FROM zhangwu WHERE money BETWEEN 2000 AND 5000;

　　　　　　　　查询出金额是1000或5000或3500的商品信息
　　　　　　　　SELECT * FROM zhangwu WHERE money =1000 OR money =5000 OR money =3500;
　　　　　　　　或
　　　　　　　　SELECT * FROM zhangwu WHERE money IN(1000,5000,3500);

　　　　　　　　查询出账务名称包含”支出”的账务信息。
　　　　　　　　SELECT * FROM zhangwu WHERE name LIKE "%支出%";

　　　　　　　　查询出账务名称中是无五个字的账务信息
　　　　　　　　SELECT * FROM gjp_ledger WHERE ldesc LIKE "_____"; -- 五个下划线_

　　　　　　　　查询出账务名称不为null账务信息
　　　　　　　　SELECT * FROM zhangwu WHERE name IS NOT NULL;
　　　　　　　　SELECT * FROM zhangwu WHERE NOT (name IS NULL);

二、JDBC

　　1、JDBC开发步骤　　　

　　　　　　1> 注册驱动.
　　　　　　2.>获得连接.
　　　　　　3>获得语句执行平台
　　　　　　4>执行sql语句
　　　　　　5> 处理结果
　　　　　　6>释放资源.

　　2、导入驱动jar包

　　　　创建lib目录，用于存放当前项目需要的所有jar包

　　　　选择jar包，右键执行build path / Add to Build Path

　　3、代码展示：　　　

　　　　

package it.cast_01;

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

public class demo01 {
    public static void main(String[] args) throws Exception {
        
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //2.获得连接
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybase","root", "root");
        
        //3.获得statement对象
        Statement stat = con.createStatement();
        
        /*  查询表中的数据信息
         *  ResultSet executeQuery(String sql) 执行给定的 SQL 语句，该语句返回单个 ResultSet 对象。 
         *  
         *  更新或者修改表中的数据
          *    int executeUpdate(String sql) 执行给定 SQL 语句，该语句可能为 INSERT、UPDATE 或 DELETE 语句，
          *   或者不返回任何内容的 SQL 语句（如 SQL DDL 语句）。 
         *
         * */
        
        //4.操作sql，修改表中的数据信息，将表中id=2的，姓名修改成wangwu

        String sql = "update info set name='wangwu' where id=2";
        int result = stat.executeUpdate(sql);
        System.out.println(result);
        
        //5.查询sql,查询表中的数据信息
        sql = "select * from info";
        ResultSet rs = stat.executeQuery(sql);        
        while(rs.next()){
            System.out.println(rs.getInt("id")+"	"+rs.getString("name"));
        }
    }
}

　　　　这里Statement对象，存在SQL注入问题，所以，更多的情况下，我们使用PreparedStatement预处理对象，代码如下：　　

　　　　

package it.cast_01;

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

public class demo02 {
    public static void main(String[] args) throws Exception {
        
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //2.得到连接
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybase", "root", "root");
        
        //3.sql语句，将表中id=2的，姓名改成lisi
        String sql = "update info set name=? where id=2";
        
        //4.得到预处理对象
         PreparedStatement pstmt = con.prepareStatement(sql);
         
         //setString()函数中第一个参数是占位符，第二个是内容
         pstmt.setString(1, "lisi");
         
         //5.执行
         int result = pstmt.executeUpdate();
         System.out.println(result);
         
         //6.sql语句，查询表中数据信息
         sql = "select * from info";
         pstmt = con.prepareStatement(sql);
         ResultSet rs = pstmt.executeQuery();         
         while(rs.next()){
             System.out.println(rs.getInt("id")+"	"+rs.getString("name"));
         }
    }
}

　　4、properties配置文件　　　　

　　　　1、在实际开发中，为了后期维护，我们通常使用properties配置文件，此文件我们将做如下要求：　　　　　

　　　　　　1>文件位置：任意，建议src下

　　　　　　2>文件名称：任意，扩展名为properties

　　　　　　3>文件内容：一行一组数据，格式是“key=value”.

　　　　　　　　a)  key命名自定义，如果是多个单词，习惯使用点分隔。例如：jdbc.driver

　　　　　　　　b)  value值不支持中文，如果需要使用非英文字符，将进行unicode转换。

　　　2、创建配置文件　　　　　

　　　　　　在项目跟目录下，创建文件，输入“db.properties”文件名。

         　　　　文件中的内容

　　　　　　driver=com.mysql.jdbc.Driver

　　　　　　url=jdbc:mysql://localhost:3306/mydb

　　　　　　user=root

　　　　　　password=root

　　　3、代码展示　　　

　　　　

package it.cast_01;

import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class demo03 {
    public static void main(String[] args) throws Exception {
        Properties props = new Properties();
        Reader is = new FileReader("db.properties");
        props.load(is);
        
        String driver=props.getProperty("driver");
        String url = props.getProperty("url");
        String username = props.getProperty("username");
        String password = props.getProperty("password");
        
        
        Class.forName(driver);
        
        Connection con = DriverManager.getConnection(url, username, password);
        
        Statement stat= con.createStatement();
        
        String sql = "select * from info";
        
        ResultSet rs = stat.executeQuery(sql);
        
        while(rs.next()){
            System.out.println(rs.getInt("id")+"	"+rs.getString("name"));
        }
        
    }
}