JAVA开发之旅（十六）###数据库（MySql）###

###数据库（MySql）###

 

安装MySql：

   至官网下载mysql：https://www.mysql.com/downloads/

   安装完成后，运行 ---> cmd ---> 切换当前目录至安装bin所在目录下

   运行：mysqld --initialize --console

             记住初始密码

            net start mysql 启动服务

            mysql -uroot -p(输入你的初始密码)

            当成功进入后通过（mysql> alter user 'root'@'localhost' identified by 'new_password';）来修改密码

 

1. SQL语言介绍

    SQL：关系型数据库、非关型数据库

 

2. SQL语句类型

    DDL   数据定义语言

    TPL   事务处理语言

    DCL   数据控制语言

    DML   数据操作语言（重点）：

                                               select   insert   update   delete

    正确使用SQL的重要性：

        增加数据库处理效率，减少应用相应时间

        减少数据库服务器负载，增加服务器稳定性

        减少服务器间通讯的网络流量

 

3. 语法

    建库：create database 数据库名 [character set 字符集];

    查看库：show databases;

    切换库：use 数据库名

    创建表：

             create table [if not exists] 表名(
                 列名1 类型 [约束],
                 列名2 类型 [约束],
                  ...
                 列名n 类型 [约束],
                 [主键约束],
                 [外键约束],
                 [索引]
                 ... );

    插入数据：insert into 表名(列1, 列2...列n) values (值1，值2... 值n);

    查询数据：select 列1，列2....列n from 表名;

    整数类型：int -2^31 ~ 2^31-1   四个字节

                    tinyint 一个字节

                    smallint 两个字节

    删除表：drop table 表名;

    约束： unique 唯一约束，一个表中可以有多个唯一约束

               not null 非空约束, 让列的取值不能为空

              primary key 主键约束, 效果上综合的唯一和非空, 一个表只能有一个主键

              foreign key 外键约束, 检查两表的关系列是否合法, 被引用的列必须是唯一的

              check （mysql不支持）

              auto_increment 自增

    默认值 (default)

    修改：update 表 set 列名=新值, 列2=值2 ...;  -- 修改表中所有的行

              update 表 set 列名=新值, 列2=值2 ... where 列名=条件;   -- 仅更新符合条件的记录 

    删除：delete from 表名; -- 删除这个表中所有记录，但表的定义不动

              delete from 表名 where 列名=条件; -- 仅删除符合条件的记录

              drop table 表名; -- 删除这个表，连同里面的数据

    导入数据：mysql > source 路径\sql文件的名称;

                    mysql -uroot -p 数据库的名字 < 路径\sql文件的名称;

    导出数据：在cmd窗口运行    ---->    mysqldump -uroot -p 数据库的名字 > 表格名字;

    

### select 查询：select 列名... from 表名 where 条件;

条件

比较运算：

  表示相等的 =

  大于 >

  大于等于 >=

  小于  <

  小于等于 <=

  不等于 !=

逻辑运算 and（并且） or（或者） not(取反)

模糊查询：like （eg.  select * from emp where ename like 'S%';  --->查询以 ‘S’字母打头的员工）

范围查询：between ... and ...

 in 求这一列是否在一个值列表内

限制返回的结果数： limit n（n表示最多返回几条记录）

                              limit m, n（m代表偏移量（下标） 注意偏移量从0开始）

排序：order by 列名 asc|desc, 列名 asc|desc, ...

select 语句的顺序：select ... from 表 where 条件 order by 排序 limit 限制

 

### 函数

常用的函数 :

时间加减；date_add(原始时间, 时间间隔)

提取时间的某个部分：extract(时间部分 from 原始时间)

拼接字符串：concat(值1, 值2, ... 值n)

求长度的函数：char_length 按字符为单位统计长度

                       length      按字节为单位统计长度

                       utf8mb4编码下，汉字一个字符占三个字节，英文一个字符占一个字节

标准sql：

case
    when 条件1 then 值1
    when 条件2 then 值2
    ...
    else 值n
end

组函数：最大值 max(列)、最小值 min(列)和sum(列)、个数 count(列)会排除null值、count(*)   针对这次查询，看看一共有多少行、平均值 avg(列)

去除重复：distinct

查询帮助：? contents   总目录

分组语法：select ... from 表 group by 列1,列2... having 分组条件

 

分组后 select，order by 的限制：1) select,order by 中的列必须和group by中的列一样
                                                 2) 其它没有包含在 group by语句中的列，必须和组函数一起用

执行从先到后：where 进行一遍条件过滤 , 再执行 group by 分组,  再执行 having中的条件, 再执行select, 再执行order by, 执行limit

 

### 连接查询

select ... from 表1 inner join 表2 on 连接条件 WHERE ... group by ... HAVING ... ORDER BY ... limit ... (重点)

select ... from 表1 left [outer] join 表2 on 连接条件 (重点)

select ... from 表1 right [outer] join 表2 on 连接条件

select ... from 表1 full join 表2 on 连接条件 (mysql不支持全连接)

PS：

    1) 内连接是将两张表中所有符合连接条件的数据列入结果，不符合连接条件的结果中没有，例如40号部门
    2) 如果连接的两表中有同名的列，列前面要加表名(或表别名)来区分（否则会报歧义错误）
    3) inner join ... on 的写法是符合SQL-92标准写法,其实还有一种内连接的写法：select ... from 表1, 表2 where 连接条件;    

多表连接：

    select * from 表1 
          inner join 表2 on 连接条件
          inner join 表3 on 连接条件
          ...

 

### 子查询：把某个select结果当做一个值，或一张表做进一步的查询

### 事务：

start transaction 开始事务 (begin)

commit 提交事务

rollback 回滚事务

 

事务有四大特性：ACID

A 原子性, 指事务内多条sql是作为一个整体执行

C 一致性, 事务开始前后，整个数据的状态应当一致

I 隔离性, 指事务的隔离级别(未提交读，提交读，可重复读，序列化读)

D 持久性, 事务中做的更改必须在事务结束后永久生效

 

增删改查(insert update delete select)：

    CRUD   c  insert 插入
          r  select 查询
          u  update 更新
          d  delete 删除

 

 

### JDBC

1）加载驱动(在新版的jdbc中可以省略此步)-->只需执行一次

     Class.forName("com.mysql.cj.jdbc.Driver");//mysql 8.8

2）创建连接，创建connection对象

     String url = "jdbc:mysql://localhost:3306/test2?" +
                "serverTimezone=GMT%2B8&useSSL=false"; //数据库连接字符串

连接协议  jdbc:mysql

3306: 连接端口号

test: 数据库名称

/serverTimezone=GMT%2B8 设置连接时区与数据库服务器一直(8.8新增)

3）创建Statement

     Statement stmt = conn.createStatement();

4）执行sql语句

     executeUpdate方法返回的整数,代表增删改影响记录行数

     结果集对象

     ResultSet rs = stmt.executeQuery("select deptno,dname,loc from dept"); 

     遍历结果集  rs.next() -->返回值为boolean 表示是否有下一条记录

 while (rs.next()){
        //通过getXXX方法的参数,代表第几列,从1开始
        int deptno = rs.getInt("deptno");
        String dname = rs.getString("dname");
        String loc = rs.getString("loc");
        System.out.println(deptno+"\t"+dname+"\t"+loc);
 }

5）关闭资源,先打开的资源要最后关闭

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

 

例： 用户登录的操作

package ex.classjdbc;

import java.sql.*;
import java.util.Scanner;

public class JdbcDemo4 {
    //用户登陆操作
    static final String url = "jdbc:mysql://localhost:3306/test2?serverTimezone=GMT%2B8&useSSL=false";

    public static boolean login(String username,String password) throws SQLException {
        Connection conn = DriverManager.getConnection(url, "root", "root");
        String sql = "select * from user where username=? and password=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1,username);
        stmt.setString(2,password);
        ResultSet rs = stmt.executeQuery();
        boolean r = rs.next();
        rs.close();
        stmt.close();
        conn.close();
        return r;
    }
}

    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.print("用户名：");
        String username = sc.next();
        System.out.println();
        System.out.print("密  码：");
        String password = sc.next();
        boolean login = login(username,password);
        if (login == true){
            System.out.println("登陆成功");
        }else{
            System.out.println("用户名或密码错误");
        }
    }

 

例：创建一个学生表，包含学生编号 姓名 生日 性别并增加增删改查的工具类

package day24;

import java.util.Date;

public class Student {

    private int sid; // 学生编号
    private String sname; // 学生姓名
    private Date birthday; // 生日
    private String sex; // 性别

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                '}';
    }
}

ps：为了节省代码我们设置了关闭服务的工具类

package day24;

import java.sql.*;

public class Utils {
    static final String URL = "jdbc:mysql://localhost:3306/test3?serverTimezone=GMT%2B8&useSSL=false";
    static final String USERNAME = "root";
    static final String PASSWORD = "root";

    public static Connection getConnection() throws SQLException {
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return conn;
    }

    public static void close(ResultSet rs, PreparedStatement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(PreparedStatement stmt, Connection conn) {
        close(null, stmt, conn);
    }
}

package day24;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

// Data Access Object 数据访问对象 （包含CRUD方法）
public class StudentDAO {

    // 新增方法
    public void insert(Student stu){
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            String sql = "insert into student(sname,birthday,sex)values(?,?,?)";
            System.out.println(sql);
            stmt = conn.prepareStatement(sql);
            // 用 set方法给sql语句中的？占位符赋值
            stmt.setString(1, stu.getSname());
            Date birthday = stu.getBirthday();
            stmt.setDate(2, new java.sql.Date(birthday.getTime()));
            stmt.setString(3, stu.getSex());
            stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            Utils.close(stmt,conn);
        }
    }
    // 修改方法
    public void update(Student stu){
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            String sql = "update student set sname=?, birthday=?, sex=? where sid=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, stu.getSname());
            stmt.setDate(2, new java.sql.Date(stu.getBirthday().getTime()));
            stmt.setString(3, stu.getSex());
            stmt.setInt(4, stu.getSid());
            stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.close(stmt, conn);
        }
    }
    // 删除方法
    public void delete(int sid) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            String sql = "delete from student where sid=?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, sid);
            stmt.executeUpdate();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            Utils.close(stmt, conn);
        }
    }
    // 按id查询
    public Student findById(int sid) { // sid 1012
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            String sql = "select * from student where sid = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, sid);
            rs = stmt.executeQuery();
            // 因为sid是唯一的，因此要么查到一条记录，要么没有
            if(rs.next()) { // 查到了
                // 这些数据保存起来, 将各个列的值存入学生对象的属性中
                Student s = new Student();
                s.setSid( rs.getInt("sid") );
                s.setSname( rs.getString("sname"));
                s.setBirthday(rs.getDate("birthday"));
                s.setSex(rs.getString("sex"));
                return s;
            } else { // 没查到
                return null;
            }
        } catch(Exception e) {
            throw new RuntimeException(e);
        } finally {
            Utils.close(rs, stmt, conn);
        }
    }

    // 查询所有
    public List<Student> findAll() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            String sql = "select * from student";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            List<Student> list = new ArrayList<>();
            while(rs.next()) {
                Student s = new Student();
                s.setSid(rs.getInt("sid"));
                s.setSname(rs.getString("sname"));
                s.setBirthday(rs.getDate("birthday"));
                s.setSex(rs.getString("sex"));
                list.add(s);
            }
            return list;
        } catch(Exception e) {
            throw new RuntimeException(e);
        } finally {
            Utils.close(rs, stmt, conn);
        }
    }

 

 

 

 

 

 

####END####