数据库学习入门

MySQL基础

SQL 是用于访问和处理数据库的标准的计算机语言。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

数据库简介

SQL (Structured Query Language) 是具有数据操纵和数据定义等多种功能的数据库语言，这种语言具有交互性特点，能为用户提供极大的便利，数据库管理系统应充分利用SQL语言提高计算机应用系统的工作质量与效率。SQL语言不仅能独立应用于终端，还可以作为子语言为其他程序设计提供有效助力，该程序应用中，SQL可与其他程序语言一起优化程序功能，进而为用户提供更多更全面的信息。

创建数据库

• create database

创建表

• CREATE TABLE table_name (column_name column_type(,column_name column_type));
  

• DROP TABLE table_name ;删除数据表

• INSERT INTO table_name ( field1, field2,...fieldN )
                         VALUES
                         ( value1, value2,...valueN );
  

• alter table TABLE_NAME add column NEW_COLUMN_NAME column_type 。增加一列到最后

• alter table TABLE_NAME add column NEW_COLUMN_NAME column_type first增加一列到最前面

• alter table TABLE_NAME add column NEW_COLUMN_NAME column_type after COLUMN_NAME 增加一列到指定位置后面

数据的增删改查

修改列表

• 增加一列ALTER TABLE table_name ADD column_name datatype

• 删除一列ALTER TABLE table_name DROP COLUMN column_name

插入数据

INSERT INTO *table_name* (*column1*,*column2*,*column3*,...) VALUES (*value1*,*value2*,*value3*,...);

修改数据

UPDATE *table_name* SET *column1*=*value1*,*column2*=*value2*,... WHERE *some_column*=*some_value*;

删除数据

DELETE FROM *table_name* WHERE *some_column*=*some_value*;

查询数据

SELECT *column_name*,*column_name* FROM *table_name*;

约束

• 非空约束

  • NOT NULL

• 唯一约束 UNIQUE

• 主键约束

  • PRIMARY KEY
  • auto_increment 自动增长（当列是数字类型且唯一）

• 检查约束

  • CHECK

• 默认约束 DEFAULT

• 外键约束

  • 外键用来让两个表之间数据建立链接，保证数据的一致性和完整性

  • 一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY

  • [CONSTRAINT <外键名>] FOREIGN KEY （被约束列名）PEFERENCES another_table_name (列名)

  • alert table tablename dorp FOREIGN KEY 外键名称

多表查询

• select * from table1,table2;

  • 获得笛卡儿积

• 链接查询

  • 内链接：查询A、B交集数据
    • 隐式内连接 select 列 from 表1、表2... where 条件
    • 显式内连接 select 列 from 表1 [inner] join 表2 on 条件;
  • 外连接：
    • 左外链接：查询A表所有和交集部分
    • select 列 from 表1 left join 表2 on 条件;包含表1所有数据
    • 右外链接：查询B表所有和交集部分
    • select 列 from 表1 right join 表2 on 条件;右外连接。

• 子查询

  • 嵌套查询
  • SELECT col FROM table WHERE 字段名 = (子查询)
  • SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询)
  • SELECT 字段列表 FROM (子查询) WHERE 条件

事务

• 事务是一组不可分割的操作命令

• 开启事务，做完命令后提交事务。出现错误则回滚事务

• BEGIN;
  
  sql...
  sql...
  (临时操作)
  
  COMMIT; # 提交事务
  ROLLBACK;/*回滚事务*/
  

• 事务的四大特征 ACID

  • 原子性
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性
  • 持久性

JDBC

JDBC简介

• JDBC是用Java语言操作关系型数据库的一套API

• 官方(sun)定义的一套接口

• 各个数据库厂商实现接口，提供驱动jar包

• 实现类 = 驱动

JDBC快速入门

1. 导入驱动jar包
2. 注册驱动 Class.forName("com.mysql.jdbc.driver")
3. 获取连接 Connection conn = DriverManager.getConnection(url,username,passwd)
4. 定义SQL语句 String sql=".."
5. 获取执行SQL对象Statement a = conn.createStatement();
6. 执行SQLa.executeUpdate(sql);
7. 处理返回结果
8. 释放资源

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

public class JDBCDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        /**
         * 注册驱动
         */
        Class.forName("com.mysql.cj.jdbc.Driver");

        /**
         * 获取连接
         */
        String url= "jdbc:mysql://127.0.0.1:3306/dbname";//连接本地数据库 dbname
        String username="root";
        String password = "123456";
        Connection conn=DriverManager.getConnection(url,username,password);

        /**
         * SQL语句
         */
        String sql = "update account set money = 2000 where id = 1";

        /**
         * 获取sql对象
         */
        Statement stmt = conn.createStatement();

        /**
         * 执行sql语句
         */
        int count =  stmt.executeUpdate(sql);//受影响行数
        /**
         * 处理结果
         */
        System.out.println(count);

        /**
         * 释放
         */
        stmt.close();
        conn.close();
    }
}

JDBC API

DriverManager

• 驱动管理类
  • 注册驱动 (可以省略，会自动注册)
  • 获取数据库连接Connection DriverManager.getConnection(url,name,passwd)
  • jdbc:mysql://ip:端口号/数据库名称?参数

Connection

• 获取执行sql的对象

  • Statement createStatement();

  • 预编译SQL的执行SQL对象：防止SQL注入

    • PreparedStatement prepareStatement(sql);

    • 用问号当占位符

    • String sql = "select * from table where username = ? and password = ?"

    • PreparedStatement pstmt = conn.prepareStatement(sql);

    • 设置参数值setXxx(参数1，参数2)。参数1为位置编号，从1开始

    • 如

    •         String sql = "select * from user where username = ? and password = ?";
              PreparedStatement prst = conn.prepareStatement(sql);
              prst.setString(1,'张三');
              prst.setInt(2,123456);
              ResultSet rs= prst.executeQuery();
      

    • 执行

    • executeQuery();

    • executeUpdate();

  • 执行存储过程的对象

    • CallableStatement prepareCall(sql);

• 事务管理

  • MySQL事务管理

    • BEGIN
    • COMMIT
    • ROLLBACK

  • JDBC事务管理

    • setAutoCommit(boolean autoCommit)true为自动提交事务，false为手动提交事务

    • commit()提交事务

    • rollback()

    •         try {
                  conn.setAutoCommit(false);//开启事务
                  
                  int count1 =  stmt.executeUpdate(sql1);//受影响行数
                  int count2 =  stmt.executeUpdate(sql2);//受影响行数
                  /**
                   * 处理结果
                   */
                  System.out.println(count1);
                  System.out.println(count2);
                  
                  conn.commit();
              }
              catch (Exception throwables){
                  conn.rollback();
                  throwables.printStackTrace();
              }
      

Statement

• int executeUpdate(sql)执行DML、DDL语句
• ResultSet executeQuery(sql)执行DQL语句

ResultSet

• 封装了DQL查询语句的结果

  ResultSet stmt.executeQuery(sql)

• boolean next()将光标从当前位置向前移动一行，并判断当前行是否有效

  • true 有效
  • false 无效

• getXxx(参数) 获取数据

  • 如getInt(),getString()

  • 参数

    • int:列的编号，从1开始
    • String:列的名称

  •         ResultSet rs = stmt.executeQuery("select * from account") ;
            while(rs.next())
            {
                System.out.println(rs.getString(2)+" "+rs.getInt(3));
            }
    
    
    

数据库连接池

• 负责分配管理数据库连接
• 标准接口：DataSource

增删改查示例

• 生成数据库

  use tb;
  drop table if exists tb_brand;
  create table tb_brand
  (
  
      id int primary key auto_increment,
      brand_name varchar(20),
      company_name varchar(20),
      ordered int,
      description varchar(100),
      status int
  );
  
  insert into tb_brand(brand_name,company_name,ordered,description,status)
  values('三只松鼠','三只松鼠公司',5,'不好吃',0),
  ('华为','华为公司',100,'中华有为',1),
  ('小米','小米公司',50,'are you ok',1);
  
  SELECT * FROM tb_brand;
  

• 开始操作

  • package demo;
    
    import pojo.Brand;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    public class Test {
    
        public static void select() throws SQLException {
            String url = "jdbc:mysql://127.0.0.1:3306/tb";
            String username = "root";
            String password = "123456";
            Connection conn = DriverManager.getConnection(url, username, password);
    
            String sql = "select * from tb_brand";
    
            PreparedStatement pstmt = conn.prepareStatement(sql);
    
            ResultSet rs = pstmt.executeQuery();
    
            List<Brand> brands = new ArrayList<>();
            Brand brand = null;
            while (rs.next()) {
                int id = rs.getInt("id");
                String brand_name = rs.getString("brand_name");
                String company_name = rs.getString("company_name");
                int ordered = rs.getInt("status");
                String description = rs.getString("description");
                int status = rs.getInt("status");
                brand = new Brand();
                brand.setId(id);
                brand.setBrand_name(brand_name);
                brand.setCompany_name(company_name);
                brand.setOrdered(ordered);
                brand.setDescription(description);
                brand.setStatus(status);
                brands.add(brand);
            }
            rs.close();
            pstmt.close();
            conn.close();
            System.out.println(brands);
        }
        public static  void insert() throws SQLException {
            String brand_name = "Dota2";
            String company_name = "V社";
            int ordered = 1223;
            String description = "SB";
            int status = 1;
    
    
            String url = "jdbc:mysql://127.0.0.1:3306/tb";
            String username = "root";
            String password = "123456";
            Connection conn = DriverManager.getConnection(url, username, password);
    
            String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";
    
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,brand_name);
            pstmt.setString(2,company_name);
            pstmt.setInt(3,ordered);
            pstmt.setString(4,description);
            pstmt.setInt(5,status);
    
            int count = pstmt.executeUpdate();
    
            System.out.println(count>0);
            pstmt.close();
            conn.close();
        }
    
        /**
         *
             update tb_brand
                set brand_name = ?
            where
                id=?
         * @throws SQLException
         */
        public static  void modify() throws SQLException {
            int id = 4;
    
    
            String url = "jdbc:mysql://127.0.0.1:3306/tb";
            String username = "root";
            String password = "123456";
            Connection conn = DriverManager.getConnection(url, username, password);
    
            String sql = "        delete from tb_brand\n" +
                    "        where\n" +
                    "            id=?";
    
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
    
            int count = pstmt.executeUpdate();
    
            System.out.println(count>0);
            pstmt.close();
            conn.close();
        }
        public static void main(String[] args) throws SQLException {
            modify();
            select();
        }
    }