认识数据库 和 mysql

/**
 * 数据库简介
 */
数据库是用来保存数据的，永久化存储数据
1）实现数据共享，当然需要权限
2）减少数据冗余
3）实现集中控制数据
4）可确保数据安全
5）出现故障可恢复
/**
 * 常见数据库
 */
MySQL：     MySQL AB公司开发，开源免费，广受国内小企业的追捧，后被Oracle收购
SQL Server: 微软开发，只能在windows上
Oracle:     老牌数据库公司，推出免费和收费两种服务模式，免费版不提供任何技术支持
DB2:        IBM公司开发，只有收费版 ，国内银行比较常用
Sybase:     即将退市状态，配套工具Power Designer广受欢迎
/**
 * MySQL的服务Server和物理库Data Files
 */
MySQL数据库通常由两部分组成：服务和物理库
服务：  提供针对物理库的CRUD操作，把命令解析成实际操作
物理库： 真正保存到硬盘上的数据
/**
 * 安装MySQL连接插件
 */
插件是由Oracle公司提供的Mysql连接插件，安装完毕可以操作数据库
官方网站
https://www.mysql.com/downloads/
Community (GPL) Downloads »
MySQL on Windows (Installer & Tools)
MySQL Installer
(mysql-installer-community-5.7.18.1.msi) 405.8M
Download
No thanks, just start my download.
安装
选Server only
配置路径
端口号3306
管理员 root 密码 123456
/**
 * 配置环境变量
 */
计算机
属性
高级
环境变量
选中系统变量中的“path”，在path值添加：
C:Program FilesMySQLMySQL Server 5.7in;
注意路径的真实性
打开cmd，测试： 
mysql -u root -p
quit
/**
 * MySQL存储数据的结构
 */
库
表
字段
记录
/**
 * sql语句分类
 */
DDL: 数据库定义语言，影响数据结构，库或表的CRUD
DML: 数据库操作语言，对数据的CRUD
DCL: 数据库控制语言，用户权限和事务控制，安卓开发中用不到
//DQL: 非标准的分类，非传统分类，特指CRUD中的查询
/**
 * MySQL存储数据的数据类型
 */
//数字类
整形
    int        占4字节，最常用   
    tinyint    占1字节，相当于byte,取值范围 -128~127 0~255
    smallint   占2字节，相当于short
    mediumint  占3字节
    bigint     占8字节，相当于long
浮点型
    float      单精度4字节
    double     双精度8字节
    decimal    没有精度损失，底层是采用字符串来保存数据
//字符串类
短型字符串（255字节以内）
    char()      定长字符串
    varchar()   变长字符串
长型字符串（几乎不用，太占数据库带宽）
    text        保存字符流，存储文本
    blob        保存字节流，存储二进制
//时间类
    datatime  记录详细 2017-04-01 08:00:30，默认记录null
    timestamp 记录详细 2017-04-01 08:00:30，默认记录保存时的当前时间
    year      只记录年，2017
    date      只记录日期，2017-04-01
    time      只记录时刻，08:00:30
/**
 * 库的操作
 */
//创建库
create database if not exists d_20170401 character set utf8 collate utf8_bin;
//查看当前所有库
show databases;
//删除库
drop database d_20170401;
//使用库       
use d_20170401;
//查看当前使用库
select database();
//修改库        
alter database d_20170401 character set utf8 collate utf8_bin;
//查看表的创建语句 
show create database d_20170401;
/**
 * 约束
 */
not null       非空约束，代表值不能为空
unique         唯一约束，代表值不能重复
primary key    主键约束，代表表中的唯一标识，同一张表最多有一个主键
auto_increment 数字才能主键自增
/**
 * 表的操作
 */
//创建表
create table if not exists t_20170401 
(
    id          int(8) primary key auto_increment,
    name        varchar(20),
    sal         double(8,2),//注意左边数要大于右边
    birthday    datetime,
    changeTime  timestamp
) character set utf8 collate utf8_bin;
//查看表
show tables;
//查看表的结构
desc t_20170401;
//修改表的名称
rename table t_20170401 to t_newName;
rename table t_newName to t_20170401;
//删除表
drop table t_20170401;
//增加列
alter table t_20170401 add age int(4);
//删除列
alter table t_20170401 drop age;
//修改列的数据类型
alter table t_20170401 modify name varchar(30);
//修改列名
alter table t_20170401 change name mingzi varchar(30);
/**
 * 可视化工具
 */
SQLyog
安装啥的，不细述
/**
 * 出现乱码
 */
显示目前的编码规范
show variables like "%character%";
character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system
character_sets_dir
设置编码
set character_set_client = gbk;
set chaaracter_set_results = gbk;
/**
 * 操作数据
 */
//插入记录
insert into t_20170401 (name,sal) values ("wewezhang",888.88);
//修改记录
update t_20170401 set name = "wemax" where id = 1;
//删除记录
delete from t_20170401 where name = "wewezhang";
truncate table t_20170401;
//查询记录
select id,name from t_20170401 where name="wewezhang";
select id,name from t_20170401 where name in ("we","zhang");
select id,name from t_20170401 where not name in ("we","zhang","wewezhang");
select id,name from t_20170401 where name is null;
select id,name from t_20170401 where age between 20 and 40;
//通配符
注意：不常用，通配符需要底层运算，效率较低
select * from t_20170401;
 /**
  * 模糊查询
  */
 _ 表示匹配一个任意字符
 % 表示匹配n个任意字符
 select id,name from t_20170401 where name like "%zhang";
 /**
  * 去重复
  */
 select distinct age from t_20170401;
 /**
  * 计算
  */
 select name,sal,comm, ifnull(sal,0)+ifnull(comm,0) from t_20170401;
 /**
  * 别名
  */
 select name,sal,comm, ifnull(sal,0)+ifnull(comm,0) as "总收入" from t_20170401;
 /**
  * 排序
  */
 asc    升序
 desc   降序
 select * from t_20170401 order by age;
 select * from t_20170401 order by age asc;
 select * from t_20170401 order by age desc;
 select * from t_20170401 order by sal desc,name asc;
/**
 * 聚合函数
 */
count() 计算指定列不为null的总列数
max()   计算指定列最大值
min()   计算指定列最小值
sum()   计算指定列和
avg()   计算指定列平均值
/**
 * 分组查询 group by
 */
where查询分组结果前的操作
having查询分组结果后的操作，较耗性能，不常用
select bumen sum(sal) from t_20140405 group by bumen having sum(sal)>1000;
/**
 * limit
 */
mySql独有
限定查询的起始行和总行数
limit 0,5;
/**
 * JDBC
 */
Java Database connectivity
JDBC是java的研发公司(Sun公司)提供的访问数据库的API
统一众多数据库的接口规范，方便程序开发
/**
 * 导入jdbc的mysql开发包
 */
https://www.mysql.com/products/connector/
JDBC Driver for MySQL (Connector/J)
Platform Independent (Architecture Independent), ZIP Archive
解压
mysql-connector-java-5.1.41-bin.jar
粘贴到工程下的lib文件夹下
.jar 右键
Build Path
Add to Build Path
/**
 * 部署单元测试的环境jUnit
 */
工程
右键
Properties
Java Build Path,libraries,Add Library...
JUnit,Next
JUnit4,Finish,OK
/**
 * 单元测试写法：加注解@Test、public 、void、无参数
 * 单元测试运行：右键,Run As，JUnitTest，选择方法
 */
新建Java工程
新建Java Class文件
import org.junit.Test;
public class Demo {
    @Test
    public void funAny() {
    }
}
/**
 * JDBC实现思路
 */
package com.wewezhang.jdbc_hello;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
import org.junit.Test;
 
public class Demo {
    @Test
    public void funZeng() throws Exception {
        //注册驱动方法1，不推荐此方法，原因是Driver类的静态代码块中已经调用过此方法
//      DriverManager.registerDriver(new Driver());
        //注册驱动方法2，推荐，手动加载类即可
        Class.forName("com.mysql.jdbc.Driver");
        //获得连接对象
        Connection weMysqlConnection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/t_20170401?characterEncoding=utf8&useSSL=true","root","123456");
        //普通的连接代理对象
//      Statement weMysqlStatement = weMysqlConnection.createStatement();
        //支持反向修改数据库的连接代理对象
        Statement weMysqlStatement = weMysqlConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        //增删改用executeUpdate
        String weZeng = "insert into t_20170401 (id ,name,sal) values (null,'wewezhang',888.88)";
        int weZengResult = weMysqlStatement.executeUpdate(weZeng);
        if(weZengResult==0){
            throw new RuntimeException("weZeng失败");
        }
        //查用executeQuery
        String weCha = "select * from t_20170401";
        ResultSet weChaResultSet = weMysqlStatement.executeQuery(weCha);
        //正序
        weChaResultSet.beforeFirst();
        while (weChaResultSet.next()) {
            int weId = weChaResultSet.getInt("id");
            String weName = weChaResultSet.getString("name");
            System.out.println("正序"+" id~"+weId+" name~"+weName);
        }
        //倒序
        weChaResultSet.afterLast();
        while (weChaResultSet.previous()) {
            int weId = weChaResultSet.getInt("id");
            String weName = weChaResultSet.getString("name");
            System.out.println("倒序"+" id~"+weId+" name~"+weName);
        }
        //反向修改数据库
        weChaResultSet.beforeFirst();
        weChaResultSet.next();
        weChaResultSet.updateString("name", "weTom");
        weChaResultSet.updateRow();
        //关闭数据库
        weMysqlStatement.close();
        weMysqlConnection.close();
    }
}
/**
 * 配置properties文件
 */
New
File
jdbc.properties
Source
driver   = com.mysql.jdbc.Driver
url      = jdbc:mysql://127.0.0.1:3306/t_20170401?characterEncoding=utf8&useSSL=true
user     = root
password = 123456
/**
 * 封装JDBC工具类
 */
package com.wewezhang.jdbc_hello;
 
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
 
public class JDBCUtils {
     
    private static String driver;
    private static String url;
    private static String user;
    private static String password;
    
    /*
     * 静态代码块
     * 类加载时只执行一次，适合创建单例对象
     */
    static {
        try{
            //读取properties文件
            Properties weProperties = new Properties();
            InputStream weInputStream = new FileInputStream("src/jdbc.properties");
            weProperties.load(weInputStream);
            weInputStream.close();
            //读取properties里的数据
            driver = weProperties.getProperty("driver");
            url = weProperties.getProperty("url");
            user = weProperties.getProperty("user");
            password = weProperties.getProperty("password");
            //推荐注册数据库驱动，类加载时自动调用静态代码
            Class.forName(driver);
        } catch(Exception error) {
            error.printStackTrace();
            throw new RuntimeException("加载jdbc.properties失败~~~");
        }
    }
    /*
     * 连接数据库并返回连接
     */
    public static Connection getConnection() {
        Connection weConnection  = null;
        try{
            //连接数据库
        	weConnection = DriverManager.getConnection(url,user,password);
        } catch(Exception error) {
            error.printStackTrace();
            throw new RuntimeException("数据库连接失败~~~");
        }
        return weConnection;
    }
    /*
     * 释放数据库
     */
    public static void closeConnection(Connection weConnection, Statement weStatement,ResultSet weResultSet) {
        try{
            if(weResultSet!=null) weResultSet.close();
        } catch(Exception error) {
            error.printStackTrace();
        } finally {
            try{
                if(weStatement!=null) weStatement.close();
            } catch(Exception error) {
                error.printStackTrace();
            } finally {
                try{
                    if(weConnection!=null) weConnection.close();
                } catch(Exception error) {
                    error.printStackTrace();
                } finally {
                }
            }
        }
    }
    /*
     * 测试，右键，Run As,Application
     */
//  public static void main(String[] args) {
//      System.out.println(getConnection());
//  }
}
/**
 * sql注入问题
 */
普通sql条件语句在开发中有很大的安全漏洞
"select * from t_20170401 where name='"+name+"';"
假设用户输入的是: zhang' or true or 'baby
就变成了
select * from t_20170401 where name='zhang' or true or 'baby';
那么，数据库就存在安全隐患了
/**
 * sql注入的解决方案
 */ 
Java给出了sql注入的解决办法
prepareStatement对象将不带参数的sql语句预先进行编译
prepareStatement对象再将参数传递，这样就避免出现sql注入问题
/**
 * 应用JDBCUtils的案例
 */
package com.wewezhang.jdbc_hello;

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

import org.junit.Test;

public class Demo {
	@Test
	public void funZeng() throws Exception {
		//连接数据库
		Connection weConnection = JDBCUtils.getConnection();
		//支持反向修改数据库的连接代理对象
        Statement weStatement = weConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        //查用executeQuery
        String weCha = "select * from t_20170401";
        ResultSet weResultSet = weStatement.executeQuery(weCha);
        //正序
        weResultSet.beforeFirst();;
        while (weResultSet.next()) {
            int weId = weResultSet.getInt("id");
            String weName = weResultSet.getString("name");
            System.out.println("Go"+" id~"+weId+" name~"+weName);
        }
        //倒序
        weResultSet.afterLast();
        while (weResultSet.previous()) {
            int weId = weResultSet.getInt("id");
            String weName = weResultSet.getString("name");
            System.out.println("Back"+" id~"+weId+" name~"+weName);
        }
        //关闭数据库
        JDBCUtils.closeConnection(weConnection, weStatement, weResultSet);
	}
}
/**
 * 存储大文本,字符流
 */
//先建数据库的表
Create table if not exists t_BigText
(
    id   int  primary key  auto_increment,
    text text
);
//测试代码 - 大文本(需要额外注意文件的乱码问题)
@Test
public void funDaWenBen() throws Exception {
	//获得连接
    Connection weConnection = JDBCUtils.getConnection();
    //创建prepareStatement管理对象
    String weSql = "insert into t_BigText values(null,?)";
    PreparedStatement weStatement = weConnection.prepareStatement(weSql);
    //准备参数
    File weFile = new File("src/HongLouMeng.txt");
    FileReader weFileReader = new FileReader(weFile);
    //参数索引号，流，流的长度
    weStatement.setCharacterStream(1, weFileReader, (int)weFile.length());
    weFileReader = null;
    weFile = null;
    //查用executeUpdate
    int iUpdate = weStatement.executeUpdate();
    System.out.println("操作完成"+iUpdate);
    //关闭数据库
    JDBCUtils.closeConnection(weConnection,weStatement,null);
}
/**
 * 存储二进制字节流（不常用）
 */
//先建数据库的表
Create table if not exists t_blob
(
    id    int    primary key  auto_increment,
    file  blob
);
//测试代码 - 二进制
@Test
public void funErJinZhi() throws Exception {
	//获得连接
    Connection weConnection = JDBCUtils.getConnection();
    //创建prepareStatement管理对象
    String weSql = "insert into t_blob values(null,?)";
    PreparedStatement weStatement = weConnection.prepareStatement(weSql);
    //准备参数
    File weFile = new File("src/honghaier.jpg");
    InputStream weInputStream = new FileInputStream(weFile);
    //参数索引号，流，流的长度
    weStatement.setBinaryStream(1, weInputStream,(int)weFile.length());
    weInputStream = null;
    weFile = null;
    //查用executeUpdate
    int iUpdate = weStatement.executeUpdate();
    System.out.println("操作完成"+iUpdate);
    //关闭数据库
    JDBCUtils.closeConnection(weConnection,weStatement,null);
}
/**
 * 批量执行sql语句
 */
注意不能执行查询
@Test
public void funZhuRu() throws Exception {
    //获得连接
    Connection weConnection = JDBCUtils.getConnection();
    //创建Statement管理对象
    Statement weStatement = weConnection.createStatement();
    //添加多条sql语句
    weStatement.addBatch("insert into t_BigText values(null,'jack')");
    weStatement.addBatch("insert into t_BigText values(null,'rose')");
    //执行sql
    int[] weResultSet = weStatement.executeBatch();
    System.out.println(Arrays.toString(weResultSet));
    //关闭数据库
    JDBCUtils..closeConnection(weConnection,weStatement,null);
}