华清远见-重庆中心-MySQL技术总结

MySQL重要技术

控制台

找到MySQL的bin目录，在地址栏输入cmd，进入后界面如下：

常用控制台语句

• 查看所有数据库

  show databases;
  

• 切换数据库

  use 数据库名;
  

• 查看当前数据库下的所有表

  show tables;
  

• 创建一个数据库

  create database 数据库名;
  

• 删除数据库

  drop database 数据库名;
  

常用SQL语句

操作数据表

1.创建数据表

2.删除数据表

-- 创建数据表
-- create table 表名(字段名 数据类型 [字段特征]，字段名 数据类型 [字段特征]...)
create table user(
	user_id int,
	user_name varchar(20),
	user_pwd varchar(20)
)

-- 删除数据表
-- drop table 表名
drop table user;

3.修改数据表

• 对表重命名

  -- 将user表重命名为userinfo
  -- alert table 旧表名 rename to 新表名;
  

• 添加新字段

  alter table 表名 add column 字段名 数据类型 字段特征;
  

• 修改字段

  alter table 表名 change 旧字段名 新字段名 数据类型 字段特征;
  

• 删除字段

  alter table 表名 drop 字段名;
  

添加约束

1.添加非空约束

alter table 表名 change 旧字段名 新字段名 数据类型 not null;

2.添加主键约束

alter table 表名 add primary key(字段名);

3.添加唯一约束

alter table 表名 add unique(字段名);

4.添加默认值约束

alter table 表名 alter 字段名 set default '默认值';

5.添加外键约束

alter table 从表表名 add foreign key(从表外键字段) reference 主表(主表主键字段)

添加约束的操作通常是对已存在的表进行修改和维护时使用。如果是一张新表，最好在创建表的时候设计好约束。

建表的同时添加约束

-- 创建数据库gamedb
create database gamedbb;
-- 切换数据库
use gamedbb;
-- 创建游戏角色表hero
create table hero(
	-- 编号  整型	非空					主键				自增
	id      int   not null   primary key   auto_increment comment '编号',
	-- 姓名    
	name varchar(20)  not null unique comment '姓名',
	-- 定位
	position varchar(20) not null comment '定位',
	-- 性别
	sex char(1) not null default '男' comment '性别',
	-- 价格
	price int default '4800' comment '价格',
	-- 上级日期
	shelf_date date comment '上架日期'
)

-- 创建战斗表battle
create table battle(
	hero_id int not null,
	position varchar(20),
	foreign key (hero_id) references hero(id)
)

-- drop table battle;

MySQL中常见的数据类型

整型
tinyint	短整型	对应java中的byte和short
int	长整型	对应java中的int
bigint	长整形	对应java中的long

浮点型
float	单精度浮点型	对应java中的float
double	双精度浮点型	对应java中的double
decimal(宽度，精度)	指定保留的小数位数和整体宽度	如decimal（4,2）3.1415926–>3.14

字符串
char(长度)	定长字符串	char(10)表示占10个字符，就算保存了3个字符，也占10个字符，对应java中的String
varchar(长度)	可变字符串	varchar(10)表示最多占10个字符，保存了3个字符，占3个字符，对应java中的String
text	文本

日期
date	日期	yyyy-MM-dd
time	时间	HH:mm:ss
datetime	日期时间	yyyy-MM-dd HH:mm:ss
timestamp(14/8)	毫秒	保存日期的毫秒数.14表示yyyyMMddHHmmss,8表示yyyyMMdd

约束

字段特征	概念	关键字
非空约束	是否允许该字段为null	null表示可以为空，not null表示不可以为空
主键约束	主键(primary key),用于区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，将该字段设置为主键字段。如果没有现成的字段可以区分每条记录时，通常会额外添加一个id字段用来设置为主键字段。通常一张表只有一个主键字段。	primary key
唯一约束	保证该字段值不能重复	unique
默认值约束	如果在不给该字段添加数据的情况下，保证该字段有一个默认值。	default
外键约束	在主从关系的表中，给从表的某个字段添加外键约束后，该字段的值就只能来自于主表中的某个主键字段	foreign key references

操作数据

数据的操作，是指数据的增加create，修改update，查询read和删除delete。

简称为CURD。

数据添加insert

数据添加时，都是一整行（一条记录）添加，不能只给一个字段添加数据。

如果只给某个字段添加数据，实际是修改。

给所有字段赋值

insert into 表名 values('值1','值2'...)

• 表名无需添加字段名，添加时保证值的顺序和字段的顺序一致
• 遇到自增字段，不能省略不写，要使用0、null或default让其自动填充
• 有默认值的字段，不能省略不写，要使用default自动填充默认值
• 遇到允许为空的字段,不能不赋值，用null让其为空

给指定字段赋值

insert into 表名(字段1,字段2...) values('值1','值2'...)

• 没有默认值的非空字段必须要写出来
• 表名后的字段顺序要和值的顺序一致

批量添加

可以用一个insert语句添加多条记录

insert into 表名[(字段1,字段2)] values
	('值1','值2'...),
	('值1','值2'...),
	...
	('值1','值2'...)

• 可以省略表名后的字段名
• 值的顺序和字段的顺序一致
• 如果一次添加多条记录时，优先使用批量添加，效率更高

数据修改update

修改单个字段的所有值

update 表名 set 字段 = 值;

修改多个字段的所有值

update 表名 set 字段1 = '值',字段2 = '值'...

根据条件修改(where子句)

update 表名 set 字段 = '值' where 条件

指定值

update 表名 set 字段 = '值' where 字段 = '值'

指定范围

• 使用>、<、>=、<=表示范围，使用and、or、&&、||将多个条件关联

  update 表名 set 字段 = '值' where 字段
  

• 使用"字段 between 值1 and 值2"表示字段在闭区间[值1，值2]

  update 表名 set 字段='值' where 字段 between 值1 and 值2
  

• 使用!=或<>表示不等于

  update 表名 set 字段='值' where 字段<>值
  

指定集合

• 在某个集合中 in

  update 表名 set 字段='值' where 字段 in ('值1','值2'...)
  

• 不在某个集合中 not in

  update 表名 set 字段='值' where 字段 not in ('值1','值2'...)
  

空值

• 使用is null表示空

  update 表名 set 字段 = '值' where 字段 is null
  

• 使用is not null表示非空

  update 表名 set 字段 = '值' where 字段 is not null
  

模糊查询

-- 字段 like '%娜%'
-- 带有'娜'字

-- 字段 like '张%'
-- ‘张’字开头

-- 字段 like '%儿'
-- ‘儿’字结尾

-- 字段 like '%瑞_'
-- 倒数第二个字为‘瑞’

-- 字段 like '___'
-- 3个字

update 表名 set 字段 = '值' where 字段 like ‘%文字%’

数据删除delete

数据删除是删除一条或多条记录。

删除所有

delete from 表名;
-- 或
truncate table 表名;

• delete会保留自增列删除前的值，删除后再添加时，自动从删除前的值开始自增
• truncate会重置自增列的值。效率更高
• 如果要删除主从关系且设置了外键的表中的数据，如果从表中有数据，不能直接删除主表中相关数据，先删除从表中的数据后，才能删除主表中的数据

条件删除

delete from 表 where 条件

删除时的条件同修饰时的条件语句

数据查询select

查询所有字段

select * from 表名;

查询指定字段

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

字段重命名

select 字段1 as '重命名',字段2 '重命名'... from 表名;

查询指定条数

-- 查询前N条记录
select * from 表名 limit N;

-- 查询从索引N开始的M条记录
select * from 表名 limit N,M;

-- 每页显示size条，第page页
select * from 表名 limit (page-1)*size,size

去重复

select distinct 字段名 from 表名;

条件查询

where子句，语法同修改、删除时的where

select * from 表名 where 条件;

排序

select * from 表名 where 条件 order by 排序字段 [ASC/DESC],排序字段 [ASC/DESC]...

• 排序可以是升序或降序
  • 默认不写是升序asc
  • 降序需要写desc
• 排序时如果有条件，where条件写在表名之后，排序之前
• 多字段排序时，在order by之后写多个字段及排序规则，用逗号隔开
  • 按字段顺序优先排序

统计函数(聚合函数)

select 统计函数(字段名) from 表名;

函数名
count(字段名)	统计数量
sum(字段名)	求和
avg(字段名)	平均
max(字段名)	最大
min(字段名)	最小

字符串相关函数

函数
length(字符串或字段)	得到字符串长度
trim(字符串或字段)/ltrim(字符串或字段)/rtrim(字符串或字段)	去除字符串首尾/首/尾空格
left(字符串或字段,n)/right(字符串或字段,n)	从字符串左/右开始截取n个字符
substr(字段或字符串,start)	从start开始截取至末尾
substr(字段或字符串,start,length)	从start开始截取length个字符
lcase(字符串或字段)/ucase(字符串或字段)	转换小写/大写
instr(字符串1,字符串2)/locate(字符串2,字符串1)	得到字符串2在字符串1中出现的顺序
reverse(字符串或字段)	翻转字符串
concat(字符串1,字符串2…)	拼接所有字符串
replace(字符串或字段,旧字符串,新字符串)	将字符串中的旧字符串替换为新字符串

时间相关函数

函数
now()	当前日期时间
current_date()/curdate()	当前日期
current_time()/curtime()	当前时间
year(日期)/month(日期)/day(日期)	得到年/月/日部分
datediff(时间1,时间2)	计算时间1与时间2相隔的天数
timediff(时间1,时间2)	计算时间1与时间2相隔的时分秒
TIMESTAMPDIFF(时间单位,时间1,时间2)	计算时间1与时间2之间相隔的指定时间单位

分组

select 分组字段,统计函数 from 表名 group by 分组字段

按指定的字段进行分组，会将该字段值相同的记录归纳到同一组中。

分组通常配合统计函数使用。

如果统计函数作为条件，不能写在where之后，要写在having之后，

having子句放在分组之后。

-- 按图书作者分组，查询平均价格大于50的信息
select book_author,avg(book_price) from book_info 
group by book_author
having avg(book_price)>50

group_concat()函数

将分组后的数据拼接成字符串。

group_concat(字段1或字符串,字段2或字符串...)

select group_concat(字段1,字段2...) from 表名 group by 分组字段
-- 根据图书类型分组，查看每组下的图书名和作者
select group_concat(book_name,'--',book_author) from book_info group by type_id

实际业务对应sql

## 删除

### 逻辑删除

不删除数据，只是不显示数据

可以给表中添加"是否删除"字段，用0表示未删除，用1表示已删除。

如果删除数据时，执行修改操作，将"是否删除"字段的值为1。

```sql
update 表 set deleted=1 where 用户编号=?;

最终在查询所有时，加上"是否删除"=0条件

select * from 表 where deleted=0;

物理删除

将数据真正删除

delete from 表 where 用户编号=?

嵌套查询

将查询出的结果继续嵌套使用在另一个查询语句中

-- 根据类型分组，查询每组图书中大于等于平均价格的图书
select * from book_info bi,
(select type_id,avg(book_price) avg_price from book_info group by type_id) t
where bi.type_id=t.type_id and book_price>avg_price;

-- 根据作者分组，查询每组图书中大于平均库存的图书
select * from book_info bi,
(select book_author,avg(book_num) avg_num from book_info group by book_author)t
where bi.book_author=t.book_author and bi.book_num>avg_num

-- 查询大于平均价格的图书
select * from 表
where price >(select avg(price) from 表)
-- 根据作者分组，查询每组大于平均价格的图书
select *
from 表 t1,(select author,avg(price) avg_price from 表)t2
where t1.author=t2.author and price>avg_price

Jar

以.jar为后缀的文件，称为Java归档文件，保存的是Java的字节码.class文件。

在Java程序中导入某个.jar文件，就能使用其中的.class文件

在Java程序中使用jar文件

1.先在根目录创建lib文件夹，将.jar文件导入lib文件夹

2.点击jar文件，鼠标右键单击，点击“add as library”

JDBC

Java Database Connectivity Java数据库连接

用于Java程序连接不同的数据库。

实际在Java中定义的相关数据库连接时所需的接口，不同的数据库对其进行了实现。

核心接口

• Connection 用于设置连接的数据库地址、账号、密码
• PrepareStatement 用于预处理、执行sql语句
• ResultSet 用于接收查询后的数据

以上接口都代来自于java.sql包中

数据查询

import java.sql.*;
public class JDBCTest {
public static void main(String[] args) throws SQLException,
    ClassNotFoundException {
    //连接mysql数据库,实现查询功能
    //连接对象
    //Connection conn;
    //sql预处理对象
    //PreparedStatement pst;
    //结果集对象
    //ResultSet rs;
        
    //查询的流程
    //1.加载mysql驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2.连接指定的mysql
    String url="jdbc:mysql://localhost:3306/gamedb?
    serverTimezone=Asia/Shanghai";
    String username="root";
    String password="root";
    Connection conn=DriverManager.getConnection(url,username,password);
    //3.构造sql语句
    String sql="select * from hero";
    //4.预处理sql语句
    PreparedStatement pst=conn.prepareStatement(sql);
    //5.执行查询的方法
    ResultSet rs =pst.executeQuery();
    //6.循环读取数据
    while(rs.next()){//rs.next()方便表示读取且判断是否还有后续数据
        //获取读取到的数据 rs.get数据类型(int columnIndex)根据字段索引获取值
        rs.get数据类型(String columnName)根据字段名获取值
        int id = rs.getInt(1);
        String name = rs.getString("name");
        String sex = rs.getString("sex");
        String position = rs.getString("position");
        int price = rs.getInt("price");
        String shelf_date = rs.getString(6);
        System.out.println(id+"--"+name+"--"+sex+"--"+position+"--"+price+"-
        -"+shelf_date);
    }
    //7.释放与mysql的连接资源
    rs.close();
    pst.close();
    conn.close();
    }
}

数据增删改

public void addHero(Hero hero) throws SQLException, ClassNotFoundException {
    //1.加载驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2.获取连接对象
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gamedb?
    serverTimezone=Asia/Shanghai", "root", "root");
    //3.构造sql,参数使用?占位
    String sql = "insert into hero values(null,?,?,?,?,curdate())";
    //4.预处理sql
    pst = conn.prepareStatement(sql);
    //给?赋值
    pst.setString(1,hero.getName());
    pst.setString(2,hero.getPosition());
    pst.setString(3,hero.getSex());
    pst.setInt(4,hero.getPrice());
    //5.调用更新的方法
    int i = pst.executeUpdate();
    //6.判断执行结果
    if(i>0){
    	System.out.println("添加成功");
    }else{
    	System.out.println("添加失败");
    }
    //7.释放资源
    pst.close();
    conn.close();
}

SQL注入

在构造sql语句时，如果使用字符串拼接的方式构造动态sql，可能会造成sql注入的风险，导致执行不是预期的sql语句

-- 如删除的sql：String sql = "delete from 表 where id="+参数;
-- 实际传值时，参数为 ''or 1=1 会导致sql变成
delete from battle where id= ''or 1=1
-- 这样就会删除所有数据
-- 查询时的sql: "select * from 表 where name="+参数+" and pwd= "+参数
-- 实际传值时，第一个参数为 'or 1=1 -- 会导致sql变成
select * from hero where id=''or 1=1 -- ' and name='亚索'
-- 这样会查询出所有数据

使用数据库帮助类简化JDBC操作

DBUtil类

package com.hqyj.util;
import java.sql.*;
/*
* 数据库工具类
* 连接方法
* 释放资源方法
* */
public class DBUtil {
    //连接数据库所需字符串
    private static String url = "jdbc:mysql://localhost:3306/bookdb?
    serverTimezone=Asia/Shanghai";
    private static String username = "root";
    private static String password = "root";
    /*
    * 静态代码块
    * 加载驱动
    * */
    static {
        try {
        	Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
        	System.out.println("加载驱动异常" + e);
        }
    }
    /*
    * 静态方法
    * 获取连接
    * */
    public static Connection getConn() {
        Connection connection = null;
        try {
       		connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
        	System.out.println("连接数据库异常" + e);
        }
        return connection;
    }
    /*
    * 静态方法
    * 释放资源
    * */
    public static void release(Connection conn, PreparedStatement pst, ResultSet
    rs) {
        try {
            if(conn!=null){
            	conn.close();
            }
            if(pst!=null){
            	pst.close();
            }
            if(rs!=null){
            	rs.close();
            }
        }catch (SQLException e){
        	System.out.println("释放资源异常"+e);
        }
    }
}

实体类BookInfo

package com.hqyj.entity;

public class BookInfo {
    private int bookId;
    private String bookName;
    private String bookAuthor;
    private int bookPrice;
    private int bookNum;
    private String publisherDate;

    @Override
    public String toString() {
        return "BookInfo{" +
                "bookId=" + bookId +
                ", bookName='" + bookName + '\'' +
                ", bookAuthor='" + bookAuthor + '\'' +
                ", bookPrice=" + bookPrice +
                ", bookNum=" + bookNum +
                ", publisherDate='" + publisherDate + '\'' +
                '}';
    }

    public BookInfo(String bookName, String bookAuthor, int bookPrice, int bookNum) {
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
        this.bookPrice = bookPrice;
        this.bookNum = bookNum;
    }

    public int getBookId() {
        return bookId;
    }

    public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }

    public int getBookPrice() {
        return bookPrice;
    }

    public void setBookPrice(int bookPrice) {
        this.bookPrice = bookPrice;
    }

    public int getBookNum() {
        return bookNum;
    }

    public void setBookNum(int bookNum) {
        this.bookNum = bookNum;
    }

    public String getPublisherDate() {
        return publisherDate;
    }

    public void setPublisherDate(String publisherDate) {
        this.publisherDate = publisherDate;
    }

    public BookInfo() {
    }

    public BookInfo(int bookId, String bookName, String bookAuthor, int bookPrice, int bookNum, String publisherDate) {
        this.bookId = bookId;
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
        this.bookPrice = bookPrice;
        this.bookNum = bookNum;
        this.publisherDate = publisherDate;
    }
}

数据操作类BookInfoDao

package com.hqyj.dao;

import com.hqyj.entity.BookInfo;
import com.hqyj.util.DBUtil;

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

/*
*book_info表的操作
* */
public class BookInfoDao {
    //定义成员变量，用于连接数据库的3个接口
    Connection conn;
    PreparedStatement pst;
    ResultSet rs;

    public List<BookInfo> queryAll(){
        //创建集合
        List<BookInfo> list = new ArrayList<>();
        try {
            //通过数据库工具类获取连接
            conn = DBUtil.getConn();
            //预处理sql
            pst = conn.prepareStatement("select * from book_info");
            //调用查询
            rs = pst.executeQuery();
            //遍历结果集
            while (rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(3);
                String author = rs.getString(4);
                int price = rs.getInt(5);
                int num = rs.getInt(6);
                String time = rs.getString(7);
                BookInfo bookInfo = new BookInfo(id,name,author,price,num,time);
                list.add(bookInfo);
            }
        }catch (Exception e){
            System.out.println("查询所有异常" + e);
        }finally{
            DBUtil.release(conn,pst,rs);
        }
        return list;
    }

    /*
    * 根据id查询
    * */
    public BookInfo queryById(int bookId){
        try {
            //通过数据库工具类获取连接
            conn = DBUtil.getConn();
            //预处理sql
            pst = conn.prepareStatement("select * from book_info where book_id=?");
            //给？赋值
            pst.setInt(1,bookId);
            //调用查询
            rs = pst.executeQuery();
            //遍历结果集
            if (rs.next()){
                String name = rs.getString(3);
                String author = rs.getString(4);
                int price = rs.getInt(5);
                int num = rs.getInt(6);
                String time = rs.getString(7);
                BookInfo bookInfo = new BookInfo(bookId,name,author,price,num,time);
                return bookInfo;
            }
        }catch (Exception e){
            System.out.println("根据id查询异常"+e);
        }finally{
            DBUtil.release(conn,pst,rs);
        }
        return null;
    }

    /*
    * 添加
    * */
    public boolean insert(BookInfo bookInfo){
        conn=DBUtil.getConn();
        try {
            pst = conn.prepareStatement("insert into book_info values(null,1,?,?,?,?,curdate())");
            pst.setString(1,bookInfo.getBookName());
            pst.setString(2,bookInfo.getBookAuthor());
            pst.setInt(3,bookInfo.getBookPrice());
            pst.setInt(4,bookInfo.getBookNum());
            return pst.executeUpdate() > 0;
        }catch (SQLException e){
            System.out.println("添加异常"+e);
        }finally {
            DBUtil.release(conn,pst,rs);
        }
        return false;
    }

    /*修改*/
    public boolean update(int bookId,int newPrice,int newNum){
        conn=DBUtil.getConn();
        try {
            pst = conn.prepareStatement("update book_info set book_price=?,book_num=? where book_id=?");
            pst.setInt(1,newPrice);
            pst.setInt(2,newNum);
            pst.setInt(3,bookId);
            return pst.executeUpdate() > 0;
        }catch (SQLException e){
            System.out.println("修改异常"+e);
        }finally {
            DBUtil.release(conn,pst,rs);
        }
        return false;
    }

    /*删除*/
    public boolean delete(int bookId){
        conn=DBUtil.getConn();
        try {
            pst = conn.prepareStatement("delete from book_info where book_id=?");
            pst.setInt(1,bookId);
            return pst.executeUpdate() > 0;
        }catch (SQLException e){
            System.out.println("删除异常"+e);
        }finally {
            DBUtil.release(conn,pst,rs);
        }
        return false;
    }
}

Main类

package com.hqyj;

import com.hqyj.dao.BookInfoDao;
import com.hqyj.entity.BookInfo;

import java.util.List;
import java.util.Scanner;

public class Main {

    public static void main(String[] args) {
        Main mn = new Main();
        while (true){
            mn.menu();
        }
    }

    public void menu(){
        System.out.println("欢迎进入图书管理系统");
        System.out.println("【1】查看所有书籍【2】根据id查询【3】添加【4】修改【5】删除【6】退出");
        Scanner sc = new Scanner(System.in);
        BookInfoDao bid = new BookInfoDao();
        switch (sc.nextInt()){
            case 1:
                List<BookInfo> list = bid.queryAll();
                for (BookInfo bookInfo : list) {
                    System.out.println(bookInfo);
                }
                break;
            case 2:
                System.out.println("请输入id");
                System.out.println(bid.queryById(sc.nextInt()));
                break;
            case 3:
                System.out.println("请输入图书名");
                String bookName=sc.next();
                System.out.println("请输入作者");
                String bookAuthor=sc.next();
                System.out.println("请输入价格");
                int bookPrice= sc.nextInt();
                System.out.println("请输入库存");
                int bookNum=sc.nextInt();
                BookInfo bookInfo = new BookInfo(bookName,bookAuthor,bookPrice,bookNum);
                bid.insert(bookInfo);
                break;
            case 4:
                System.out.println("请输入修改的编号");
                int bookId = sc.nextInt();
                System.out.println("请输入修改后的价格");
                int newPrice = sc.nextInt();
                System.out.println("请输入修改后的库存");
                int newNum = sc.nextInt();
                bid.update(bookId,newPrice,newNum);
                break;
            case 5:
                System.out.println("请输入删除的编号");
                int deleteBId = sc.nextInt();
                bid.delete(deleteBId);
                break;
            case 6:
                System.exit(0);
            default :
            	System.out.println("数字输入错误");
                menu();
        }
    }
}

实体关系模型（ER模型）

实体Entity：一个表就是一个实体。

关系Relationship： 实体与实体之间的关系

用图形表示ER模型时，这个图形就称为ER图

用矩形表示实体，用椭圆表示实体的属性，用菱形表示实体之间的关系，用直线连接各个图形。

实体之间的关系

一对一

实体A与实体B之间唯一对应。

如一个国家有一个领导人。一个人对应一个配偶等。

ER图

在数据库创建表的过程

可以使用一张表实现，但如果使用一张表保存时，后期维护和扩展时较为不便。

最好使用两张表实现。

id	国家
1	A
2	B
3	C

id	姓名	国家编号
1	qwe	1
2	SDF	2
3	qwe	3

• 创建国家表

  create table country(
  	country_id int not null primary key auto_increment,
      country_name varchar(20) not null,
      country_population bigint not null,
      country_area int not null
  )
  

• 创建领导人表

  create table leader(
  	leader_id int not null primary key auto_increment,
      leader_name varchar(20) not null,
      leader_birthday date not null
  )
  

  这时两张表是独立的，并不能体现一对一的管理关系，可以通过以下方式体现一对一。

  • 方式一：

  创建一个管理表，保存两张表中的主键，添加唯一约束

  create table relarion(
  	relation_no int not null primary key auto_increment,
      leader_id int not null unique,
      country_id int not null unique,
      foreign key (leader_id) reference leader(leader_id),
      foreign key (country_id) reference country(country_id)
  )
  

  • 方式二(建议使用)：

  在以上的两个实体表中，选择任意一张表中添加一列，保存零一张表的主键，添加唯一约束

  create table leader(
  	leader_id int not null primary key auto_increment,
      leader_name varchar(20) not null,
      leader_birthday date not null,
      country_id int not null unique,
      foreign key (country_id) reference country(country_id)
  )
  

一对多/多对一

一对多：一个实体A对应多个实体B，一个实体B不能对应过个实体

多对一：多个实体B对应一个实体A，多个实体A不能对应多个实体B

如一个人有多辆车，一辆车不能对应多个人；

一个教官训练多个学员，一个学员不能对应多个教官

一个宿舍管理员管理多个宿舍等。

ER图

在数据库中创建表的过程

• 先创建主表（一）

  create table coach(
  	coach_id int not null primary key auto_increment,
      coach_name varchar(20) not null,
      coach_leave varchar(20) not null
  )
  

• 创建从表（多），在表中添加字段关联主表中的主键字段

  create table student(
  	stu_id int not null primary key auto_increment,
      stu_name varchar(20) not null,
      stu_phone varchar(20) not null,
      coach_id int not null,
      foreign key (coach_id) reference coach(coach_id)
  )
  

多对多

个实体A可以对应多个实体B，一个实体B也可以对应多个实体A。

如一个学生可以学习多门课程，一门课程也可以对应多个学习它的学生。

一个医生可以有多个病人，一个病人也可以对应多个医生。

ER图

在数据库中创建表的过程

• 创建课程表

  create table course(
  	course_id int not null primary key auto_increment,
      course_name varchar(20) not null,
      course_score int not null
  )
  

• 创建学生表

  create table student(
  	stu_id int not null primary key auto_increment,
      stu_name varchar(20) not null,
      stu_phone varchar(20) not null
  )
  

• 体现多对多关系的表：成绩表

  该表中必须要包含以上两个实体表中的主键字段

  create table score(
      s_id int not null,
      course_id int not null,
      stu_id int not null,
      cj int not null,
      -- 可以选择添加外键约束
      foreign key (course_id) references course(course_id),
      foreign key (stu_id) references student(stu_id)
  )
  

总结

一对一：创建各自的实体表，在任意一张表中添加另一张表的主键字段，并添加唯一约束

一对多/多对一：先创建主表（一），再创建从表（多），在从表中添加主表中的主键字段，外键可选

多对多：创建各自的实体表，再创建第三张表：“联系表”，在“联系表”中添加两个实体表中的主键字段，外键可选

数据库设计规范

数据库设计的规范，简称为范式(NF)。

范式分为第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、BC范式(BCNF)、第四范式(4NF)和第五范式(5NF)共六种。

这六种范式的界别越高，表示数据库设计的结果越规范，每一个高级版的范式都包含来了低级别的范式。

通常设计数据库时，只需满足3NF即可。

如有如下数据，黄色背景为联合主键，暂时不满足任何范式。

当前表如果要做增删改查的操作，会有如下的问题：

• 如果要加入一个新的系别，就要添加学生，系主任等数据
• 如果要删除“刘定宇”，他所在的系也会被删除
• 如果要将“王海”的系别改为会计，相应的系主任也要修改
• 当前表中有大量冗余数据

第一范式1NF

数据表中的每一列都是不可再分的原子项。

关系型数据库起码要满足1NF，才能创建表。

上表中的联系方式列，可以再分为手机和QQ两列，所以它不满足1NF。根据1NF的要求，修改后

第二范式2NF

在满足1NF的基础上，消除部分依赖。

对于联合主键而言，每一个非主属性字段都需要完全依赖于主属性，而不是依赖其中一部分。

在上图中，无法用学号做主键，需要将学号和科目组合为联合主键，通过联合主键才能得到分数。

出学号和科目外，其他字段都是非主键字段，分数完全依赖于联合主键，其他字段不满足，所以进行如下修改。

第三范式3NF

在满足2NF的基础上，消除传递依赖。

在上图中，系主任是通过学号->系别->系主任获取，系主任传递依赖于学号，消除这个传递依赖

最终根据实体关系模型进行优化，体现对应关系。

名词解释

主键/主码/主属性

用于唯一区分每条记录的一个字段，称为主键字段，如学号、身份证号。

通常选择能唯一且几乎不会改变的某个字段作为主键。如果没有，自定义一个id列作为主键。

联合主键

如果一个字段不能唯一区分每条记录，而是需要多个字段才能唯一区分时，这些字段一起组成了联合主键。

完全依赖

如果能通过A和B得到C，A和B都不能单独得到C时，称为C完全依赖于A和B。

如(学号+科目)联合主键->分数，分数完全依赖于联合主键。

其他字段完全依赖于学号。

部分依赖

通过A和B可以得到C，单独通过A或B也可以得到C，称为C部分依赖于A和B。

如(学号+科目)联合主键->姓名，其实只通过学号也可以得到姓名，称为姓名部分依赖于联合主键。

除了成绩外，其余字段都是部分依赖于(学号+科目)联合主键

2NF就是消除这种部分依赖，只保留完全依赖。

传递依赖

如果通过A可以得到B，通过B得到C，称为C传递依赖于A。

如学号->系->系主任，其实只通过系也能得到系主任，称为系主任传递依赖于学号。

3NF就是消除这种传递依赖。

通过MySQL的bin目录cmd进入后找数据库：

命令运行窗口

行列转换

如现有表score

转换为

select stu_name,
sum(if(c_name='高等数学',cj,null)) as '高等数学',
sum(if(c_name='思修',cj,null)) as '思修',
sum(if(c_name='大学英语',cj,null)) as '大学英语',
sum(if(c_name='大学体育',cj,null)) as '大学体育'
from score 
group by stu_id;

视图View

视图可以当做数据库中的一个临时表，保存一些较为复杂的查询后的结果，

之后可以直接通过该视图查询数据，不需要再次编写复杂的sql语句。

视图同时可以隐藏一些查询细节，定制查询数据。

创建视图

create view 视图名 as
查询的sql语句;

使用视图

select * from 视图名

修改视图中的数据，会直接修改原始表中的数据

删除视图

drop view 视图名;

-- 查询平均分最高的课程名及其授课教师

-- 创建视图
create view myview as
select s.c_id, avg(cj) avg_cj from score s,course c where s.c_id= c.c_id
group by s.c_id

-- 视图可以当做表使用
select  c_name,t_name from teach t2,teacher t1,course c,
(select c_id from myview where avg_cj=(select max(avg_cj) from myview))t
where t1.t_id=t2.t_id  and t.c_id=t2.c_id and c.c_id=t2.c_id

-- 删除视图
drop view myview;

事务transaction

事务是由一组sql语句组成的执行单元。这些sql之间一般都相互依赖。

这个执行单元要么全部执行，要么全都不执行。

转账

1.select * from 表 where id=1 and money>=1000

2.update 表 set money=money-1000 where id=1

3.update 表 set money=money+1000 where id=2

以上的所有sql组成了一个转账的事务

事务的特性ACID

原子性Atomicity

事务是最小的执行单元。

一致性Consistency

事务执行前后，必须要让所有数据保持一致状态。（总体数据守恒）

隔离性Isolation

事务并发时相互隔离，互不影响

持久性Durability

事务一旦提交，对数据的改变是永久的

事务的使用

提交：commit

回滚：rollback

mysql中事务是默认自动提交的。

查看事务自动提交开启状态：select @@autocommit 1标号四开启自动提交 0表示关闭自动提交

设置事务不自动提交状态：set @@autocommit=0

如果关闭了事务自动提交，在执行某个事务途中，入股出错，可以使用rollback进行回滚，让数据回倒事务执行之前的状态。

如果不出错，通过commit提交事务，一旦提交事务，无法进行回滚。

例如，转账。现有如下customer表：

id=1给id=2转1000：

SELECT * FROM `customer`

-- 查询事务自动提交状态
select @@autocommit
-- 将默认自动提交关闭
set @@autocommit=0

-- 开启事务
start transaction;
-- 将id为1的客户给id为2的客户转账1000
select * from customer where id=1 and balance>=10000;
-- 扣除id为1的余额
update customer set balance=balance-1000 where id=1;
-- 这里会报错，可以通过backroll回滚
sdf 
-- 添加id为2的余额
update customer set balance=balance+1000 where id=2;

-- 事务在未提交前，可以通过rollback回滚到执行sql前的状态
rollback;

-- 手动提交
commit;

手动提交/回滚事务

1.关闭事务自动提交：set @@autocommit=0

2.开启事务：start transaction;

3.事务要执行的sql；

4.如果提交之前报错，要回滚，使用rollback;

5如果要提交，使用commit；一旦提交成功，无法rollback。

事务并发可能出现的问题

在同一时刻同时执行多个事务时，称为事务并发。

事务并发会有可能出现以下问题

问题	描述
脏读	事务A读取到了事务B未提交的数据
不可重复读	事务A中如果要读取两次数据，在这期间，事务B对数据进行了修改并提交，导致事务A读取两次的情况不一致
幻读	事务A读取1-10之间的数据，加入只有id为2和5的数据，在读取期间，事务B添加了一条id为3的数据，导致事务A多读到了事务B中的数据

事务隔离级别

为了防止事务并发时出现以上各种问题，数据库中设计了几种事务与事务之间的隔离级别。

隔离级别	能否出现脏读	能否出现不可重复读	能否出现幻读
Read Uncommitted未提交读，简称RU	会	会	会
Read Committed已提交读RC（Oracle默认）	不会	会	会
Repeatable Read可重复读RR（MySQL默认）	不会	不会	会
Serializable可序列化	不会	不会	不会

查看事务隔离级别

select @@transaction_isolation;

设置事务隔离级别

set session|gloable transaction isolation level read uncommitted|read committed|repeatable read|serializable

触发器trigger

如果要在更新某张表之前或之后，自动执行另一组sql时，可以使用触发器实现。

如表A是客户表，表B是操作日志表，对表A进行更新操作时，将操作的记录保存到表B中。

慎用触发器，因为如果有10000条记录，在修改所有记录时，触发器就会执行10000次，会花费很多时间。

创建触发器

create trigger 触发器名
触发时机 触发操作 on 表 for each row
begin
	满足触发时机时自动执行的sql;
end
-- 创建操作日志表
create table log(
	id int not null primary key auto_increment,
	log_opt varchar(20) not null,
	log_time datetime not null
)
create trigger mytrigger
after insert on customer for each row
begin
	insert into log values(null,'添加了数据',now());
end

使用触发器

一旦成功创建触发器，无需刻意调用，在执行相应的操作时，自动执行触发器

-- 只要对customer表做插入操作，就会自动向log表中添加记录
insert into customer values(null,'测试插入2','123123',0,null);

删除触发器

drop trigger 触发器名;

存储过程

类似于java中的方法，定义一组用于完成特定功能的sql语句。

定义存储过程后，通过调用存储过程名，就可以执行定义时的内容。

存储过程可以有参数。

调用存储过程

-- 调用无参数的存储过程
call 存储过程名();
-- 调用输入型参数的存储过程
call 存储过程名('实参');
-- 调用输出型参数的存储过程
call 存储过程名(@变量);
-- 调用输入输出型参数的存储过程
set @变量
call 存储过程名(@变量)

定义存储过程

create procedure 存储过程名([参数类型 参数名 参数数据类型]) -- 参数类型分为输入型/输出型/输入输出型
begin
	sql语句;
end

无参数

create procedure 存储过程名()
begin
	sql语句;
end

-- 创建存储过程，查询每本图书的书名、作者、类型
CREATE PROCEDURE myproc1 () BEGIN
	SELECT
		book_name,
		book_author,
		type_name 
	FROM
		book_info bi,
		book_type bt 
	WHERE
		bi.type_id=bt.type_id;
END
-- 调用存储过程
call myproc1();

输入型参数

create procedure 存储过程名(in 形参名 数据类型)
begin
	sql语句;
end

-- 根据图书类型查询该类型下的所有图书
create procedure myproc2(IN lx varchar(20))
begin
select * from book_info bi,book_type bt where bi.type_id = bt.type_id and type_name= lx;
END

-- 调用
call myproc2('漫画');

输出型参数

类似于Java中有返回值的方法

create procedure 存储过程名(out 形参名 数据类型)
begin
	sql语句;
	-- 通常需要将查询出的结果通过into赋值给形参
end

-- 根据作者查询图书数量
create procedure myproc3(in zz varchar(20),out count int)
begin
-- 将查询的结果into到参数count中
select count(book_id) into count from book_info where book_author=zz;
end
-- 调用存储过程,@XXX表示将存储过程的输出型参数保存到变量XXX中，这里的变量可以是任意名字
call myproc3('天蚕土豆',@count)
-- 查询参数中保存的数据
select @count
-- 一个@：自己定义的变量；两个@：系统定义的变量 

输入输出型参数

create procedure 存储过程名(inout 形参名 数据类型)
begin
	sql语句;
end

-- 查询书名中带有指定文字的图书名、作者、和类型
create procedure myproc4(inout keyword varchar(20))
begin
	SELECT
		book_name,
		book_author,
		type_name
	FROM
		book_info bi,
		book_type bt 
	WHERE
		bi.type_id = bt.type_id 
		AND book_name LIKE concat('%',keyword,'%');
end
-- 调用存储过程
-- 存储过程外定义变量
set @keyword='龙';
call  myproc4(@keyword);
select @keyword;

删除存储过程

drop procedure 存储过程名;

MySQL编程

在定义存储过程中，可以定义变量，使用流程控制语句等。

定义变量

create procedure 存储过程名()
begin
	-- declare 变量名 数据类型;
	declare num int;
	declare name varchar(20);
end

给变量赋值

create procedure 存储过程名()
begin
	declare num int;
	-- 给num赋值
	-- select 字段/值 into 变量 [from 表];
	select 123 into num;
	select book_name into name from book_info where book_id=1;
end

读取变量的值

create procedure 存储过程名()
begin
	declare num int;
	declare name varchar(20);
	select 123 into num;
	select book_name into name from book_info where book_id=1;
	
	-- select 变量;
	select num;
	select name;
end

-- 创建存储过程，查询所有图书总数，保存到变量中
create procedure myproc5()
begin
	-- 定义变量
	declare sum_num int;
	-- 给变量赋值
	select sum(book_num) into sum_num from book_info;
	-- 打印变量的值
	select sum_num;
end
-- 调用存储过程
call myproc5();

条件语句

单分支if语句

if 条件
then
	满足条件时执行的sql;
end if;

双分支if语句

if 条件
then
	满足条件时执行的sql;
else
	不满足条件时执行的sql;
end if;

-- 根据图书类型查询图书数量，如果不足3，输出'该类型不足3种图书'，如果足够3，输出详情
create procedure myproc7(in lx varchar(20))
begin
	-- 定义变量保存图书数量
	declare num int;
	select count(book_id) into num 
	from book_info bi,book_type bt 
	where bi.type_id=bt.type_id and type_name=lx;
	if num<3
	then
		select '该类型不足3种图书';
	else
		select * from book_info bi inner join book_type bt on bi.type_id=bt.type_id where type_name=lx;
	end if;
end

-- 调用存储过程
call myproc7('小说');

case语句

CASE 变量
	WHEN 值 THEN
		满足该值时执行的sql语句;
	WHEN 值 THEN
		满足该值时执行的sql语句;
	ELSE
		没有任何满足时sql语句
END CASE;

create procedure myproc8(in num int)
begin
	CASE num
	WHEN 1 THEN
		select '1';
	WHEN 5 THEN
		select '5';
	ELSE
		select '都不是';
	END CASE;
end

call myproc8(2)

循环语句

while循环

while 条件 do
	满足条件时执行的内容;
end while;

-- 添加10个客户
create procedure myproc9()
begin 
	-- 定义循环变量
	declare num int;
	-- 初始化循环变量
	select 1 into num;
	-- while循环
	while num<=10 do
		insert into customer values(null,concat('测试用户',num),'123123',0,null);
		-- 更新循环变量
		set num=num+1;
	end while;
end

call myproc9();

repeat循环

repeat
	循环体;
until 条件 end repeat;

-- repeat循环
create procedure myproc10()
begin
	declare num int;
	select 50 into num;
	repeat
		insert into customer values(null,concat('测试用户',num),'123123',0,null);
		set num=num+1;
	until num=60 end repeat;
end
call myproc10();

loop循环

循环名:loop
	循环体;
	if 条件 then leave 循环名;
	end if;
end loop;

-- loop循环
create procedure myproc11()
begin
	declare num int;
	select 100 into num;
	test:loop
		insert into customer values(null,concat('测试用户',num),'123123',0,null);
		set num=num-1;
		if num=90 then leave test;
		end if;
	end loop;
end
-- 调用存储过程
call myproc11()
select * from customer;

MySQL核心内容

SQL语句

• 建库建和数据表的创建、修改、删除
• 数据完整性（约束）
• 单表增删改查CURD
• 函数
• 多表查询、嵌套查询

数据库设计

• 实体关系模型（ER）
• ER图
• 范式

JDBC

• 连接MySQL所需jar文件

  • 普通java工程需要手动导入jar文件
  • maven项目需要使用依赖自动导入jar文件

• MySQL驱动名

  // mysql5.5之前版本
  Class.forName("com.mysql.jdbc.Driver");
  //mysql8之后版本
  Class.forName("com.mysql.cj.jdbc.Driver");
  

• 连接数据库的字符串

  String url="jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai";
  String username="root";
  String password="密码";
  

事务

• 事务的概念和特性
• 事务的隔离级别
• 事务并发时出现的问题
  • 脏读
  • 不可重复读
  • 幻读
• 事务的隔离级别
  • read uncommitted 可能会出现脏读、不可重复读和幻读问题，效率最高，适合单线程使用
  • read committed 解决了脏读问题，可能会出现不可重复读和幻读问题
  • repeatable read MySQL默认 解决了脏读和不可重复读问题，可能会出现幻读问题
  • Serializable 解决了脏读、不可重复读和幻读问题，效率最低

存储引擎

• MySQL5.5版本之前，默认使用Myisam存储引擎
• MySQL5.5版本之后，默认使用InnoDB存储引擎，支持事务