华清远见-重庆中心-数据库技术总结

数据库

DataBase,简称DB，运行在操作系统上，按一定的数据结构、保存数据的仓库。

关系型数据库

数据通过行row和列column的形式(表格)保存。

数据之间有关联，能够解决数据冗余，快速查找需要的数据。

特点

优点：

• 易于维护，使用表格是存储数据，格式一致
• 使用方便，SQL语句通用，可用于不同关系型数据库
• 复杂查询：可以通过SQL语句查询多个表之间的复杂数据

缺点：

• 读写性能差，尤其是数据的高效读写
• 固定的表结构，灵活度稍欠
• 高并发读写时，硬盘I/O决定了读写速度

非关系型数据库

数据通过对象的形式保存，对象可以是键值对、文档、图片。

• 保存数据的格式多样
• 对于海量数据的读写性能更好
• 不支持复杂查询

Mysql控制台常用命令

• 查看所有数据库

show databases;

• 切换数据库

use 数据库名;

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

show tables;

• 创建数据库

create database 数据库名;

• 删除数据库

drop database 数据库名;

SQL

用于操作关系型数据库的一门语言，可以同来创建、维护数据库和数据。

操作数据库

1.创建数据库

create database 数据库名;

2.切换数据库

use 数据库名;

3.删除数据库

drop database 数据库名;

操作数据库

1.创建数据库

create table 表名(
字段名 数据类型 [字段特征],
字段名 数据类型 [字段特征],
...
字段名 数据类型 [字段特征]
)

2.删除数据表

drop table 表名;

3.修改数据表

• 对表重命名

alter table 旧表名 rename to 新表名;

• 添加新字段

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

• 修改字段

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

• 删除字段

alter table 表名 drop 字段名;

添加约束

1.添加非空约束：not null

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

2.添加主键约束:primary ker

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

3.添加唯一约束:unique

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

4.添加默认值约束:default

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

5.添加外键约束:foreign key     references

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

建议：如果创建一张新表，最好创建表的同时添加约束。

例如：

-- 创建数据库gamedb
create database gamedb;
-- 切换数据库
use gamedb;
-- 创建游戏角色表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 '性别',
-- 价格 整型 非空 默认4800
price int not null default '4800' comment '价格',
-- 上架日期
shelf_date date comment '上架日期'
)
-- 创建战斗表battle
create table battle(
hero_id int not null ,
position varchar(20),
-- 外键 hero_id参考hero表中的id字段
foreign key (hero_id) references hero(id)
)

MySQL中常见的数据类型

1.整型

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

2.浮点型

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

3.字符串

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

4.日期

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

操作数据(增删改查CURD)

添加数据insert

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

注意：

        表名后无需添加字段名，添加时保证值的顺序和字段顺序一致

        遇到自增字段时，不能省略不写，要使用0、null、default让其自动填充

        遇到有默认值的字段，不能省略不写，要使用default让其自动填充默认值

        遇到允许为空的字段，不能省略不写，要使用null让其设置为空

指定字段赋值

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

注意：没有默认值的非空字段必须写出来；表明后的字段顺序和值的顺序要一一对应。

批量添加

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

• 字段值需要和表名的字段值顺序一致

• 可以省略表名后的字段名

• 如果一次添加多条记录时，优先使用批量添加，效率更高

数据修改update

修改单个字段

update 表名 set 字段名=值;

修改多个字段的所有值

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

根据条件修改字段值

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 字段<>值;

-- 或者
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位数的所有记录

数据删除delete

删除所有

delete from 表名;

-- 或者

truncate table 表名;

• delete会保留自增列删除前的值，删除后再添加时，自动从删除前的值开始自增

• truncate会重置自增列的值，效率更高

• 如果要删除主从关系且设置了外键的表中的数据，如果从表中有数据，不能直接删除主表中相关数据，先删除从表中的数据后，才能删除主表中的数据

条件删除

delete from 表名 where 条件

数据查询

查询所有字段

select * from 表名;

查询指定字段

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

字段重命名

-- as可以省略

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

查询指定条数limit

-- 查询前n条记录
select* from book_info limit 数字n;
-- 从下标数字1开始查询到数字2条记录
select* from book_info limit 数字1,数字2;

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

去重复distinct

select distinct字段名 from 表名;

条件查询

select * from 表名 where 条件;

排序order by 

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

统计函数

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

数学相关函数

函数名	作用
abs(值或字段)	绝对值
pow(值或字段)	次幂
sqrt(值或字段)	开平方
round(值或字段)	四舍五入
ceil(值或字段)	向上取整
floor(值或字段)	向下取整

字符串相关函数

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

例如：

-- 获取hello的长度
-- 英文字母一个长度，汉字三个长度
SELECT LENGTH('hello');
select LENGTH(book_name) FROM book_info;

-- 去首尾空格
SELECT TRIM('  hello 你好  ');
-- 去首空格
SELECT LTRIM('  hello 你好  ');
-- 去尾空格
SELECT RTRIM('  hello 你好  ');

-- 从左/有开始截取n个字符
SELECT LEFT(book_name,2),RIGHT(book_author,1) FROM book_info;

-- 从第三个字符开始截取至末尾
SELECT SUBSTR('hello world',3);
-- 从第三个字符开始截取5个字符
SELECT SUBSTR('hello world',3,5);

-- 转换成小写
SELECT LCASE('Hello World');
-- 转换成大写
SELECT UCASE('Hello World');
-- 得到第二个参数字符串在第一个参数字符串中出现的位置
SELECT INSTR('hello world','he');
-- 得到第一个参数字符串在第二个参数字符串中出现的顺序
SELECT LOCATE('he','hello world');

-- 翻转
SELECT REVERSE(book_name) FROM book_info;

-- 拼接a,b,c
SELECT CONCAT('a','b','c');
-- 根据书名关键词模糊查询
SELECT * FROM book_info WHERE book_name LIKE CONCAT('%','龙','%');
-- 给书名添加书括号
SELECT CONCAT('《',book_name,'》') FROM book_info

-- 替换旧字符串
SELECT REPLACE('hello world','world','你好');

时间相关函数

函数	作用
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之间相隔的指定时间单位

分组group by

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

如果判断条件为统计函数，那么就需要使用having

group_concat()函数

作用：将分组后的数据拼接成字符串

group_ concat (字段1或字符串,字段2或字符串...)
select group-concat(字段1 ,字段2...) from 表名 group by 分组字段

连接查询

交叉连接、笛卡尔积

将两张表中的数据两两组合，得到的结果就是交叉连接的结果，也称为笛卡尔积

将两张表中的数据互相组合成一张新表，很多的数据都是无效的

select * from 表1,表2;
select * from 表1 cross join 表2;
select * from 表1 inner join 表2;

内连接

SELECT * FROM 表1 ,表2 WHERE 表1.字段 = 表2.字段;
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;

• 通常是通过主表的主键字段关联从表的外键字段

• 如果两张表中关联的字段名一致，一定要通过''表名.字段名''进行区分，通常给表重命名。

• 如果使用inner join 带条件时，需要加入where子句，如果使用,隔开各个表，带条件时讲条件使用and拼接。

• 内连接只会显示两张表中有关联的数据

外连接

左外连接：能够完全显示表1中的数据

-- 在保证左表数据显示完整的情况下，关联右表中的数据，没有关联的数据用null表示
select * from 表1 left join 表2 on 表1.字段 = 表2.字段;

右外连接：能够显示表2中的数据

-- 在保证右表数据显示完整的情况下，关联左表中的数据，没有关联的数据用null表示
select * from 表2 left join 表1 on 表2.字段 = 表1.字段;

嵌套查询

将查询出的结果嵌套到另一个查询中

-- 根据类型分类，查询每组图书中大于等于平均价格的图书
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 book_num>avg_num;

JDBC

jdbc称为Java数据库连接，用于Java程序连接不同的数据库。

使用前需要先导入.jar文件

核心接口

以下接口都来自于.jar文件

• Connection：用于设置连接的数据库的地址、密码和账号

• PrepareStatement：用于预处理、执行SQL语句

• ResultSet：用于接收查询后的数据

查询的步骤

1.获取连接对象：Connection

2.构造sql语句：String

3.预处理sql语句：pst=conn.prepareStatement(sql)

4.给sql语句中的?赋值：pst.set数据类型(顺序，值);

5.调用executeQuery()得到ResultSet结果集

6.遍历结果集：set.get数据类型(字段顺序/字段名)

7.释放资源

增删改步骤

1.获取连接对象：Connection

2.构造sql语句：String

3.预处理sql语句：pst=conn.prepareStatement(sql)

4.给sql语句中的?赋值：pst.set数据类型(顺序，值);

5.调用executeUpdate()得到受影响的行数

6.释放资源

JDBC案例：

DBUtil类：数据库连接

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 pwd="密码";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (ClassNotFoundException e){
            System.out.println("加载驱动异常"+e);
        }
    }

    public static Connection getConn(){
        Connection conn = null;
        try {
           conn = DriverManager.getConnection(url, username, pwd);
        }catch (SQLException e){
            System.out.println("连接数据库异常" + e);
        }
        return conn;
    }
    //释放资源
    public static void release(Connection conn, PreparedStatement pst, ResultSet set){
        try {
            if (conn!=null){
                conn.close();
            }
            if (pst!=null){
                pst.close();
            }
            if (set!=null){
                set.close();
            }
        }catch (Exception e){
            System.out.println("释放资源异常" + e);
        }
    }
}

BookInfo实体类

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

    //全参数构造方法：用于查询时创建对象
    public BookInfo(int bookId, String bookName, String bookAuthor, int bookPrice, int bookNum, String publisherDate) {
        this.bookId = bookId;
        this.bookName = bookName;
        BookAuthor = bookAuthor;
        this.bookPrice = bookPrice;
        this.bookNum = bookNum;
        this.publisherDate = publisherDate;
    }

    //不含id和出版时间的构造方法：用来添加时创建对象
    public BookInfo(String bookName, String bookAuthor, int bookPrice, int bookNum) {
        this.bookName = bookName;
        BookAuthor = bookAuthor;
        this.bookPrice = bookPrice;
        this.bookNum = bookNum;
    }

    @Override
    public String toString() {
        return "编号:"+bookId+"\t书名:"+bookName+"\t作者:"+BookAuthor+"\t价格:"+bookPrice+"\t库存:"+bookNum+"\t出版时间:"+publisherDate;
    }
    //省略了setter/getter方法

BookDao类：方法类

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.util.ArrayList;
import java.util.List;

public class BookInfoDao {
    //定义成员变量，用于操作数据库的三个接口
    Connection conn;
    PreparedStatement pst;
    ResultSet set;

    //查询
    public List<BookInfo> selectAll(){
        List<BookInfo> list = new ArrayList<>();
        try {
            //创建数据库工具类获取连接
            conn = DBUtil.getConn();
            //预处理sql语句
            pst = conn.prepareStatement("select * from book_info");
            //调用查询
            set = pst.executeQuery();
            //遍历结果集
            while (set.next()) {
                int id = set.getInt(1);
                String name = set.getString(3);
                String author = set.getString(4);
                int price = set.getInt(5);
                int num = set.getInt(6);
                String time = set.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,set);
        }
        return list;
    }

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

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

    //修改
    public boolean updateBook(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 (Exception e){
            System.out.println("修改异常" + e);
        }finally {
            DBUtil.release(conn,pst,set);
        }
        return false;
    }

    //删除
    public boolean deleteBook(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 (Exception e){
            System.out.println("删除异常" + e);
        }finally {
            DBUtil.release(conn,pst,set);
        }
        return false;
    }
}

Main类：实现类

package com.hqyj.Main;

import com.hqyj.dao.BookInfoDao;
import com.hqyj.entity.BookInfo;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class Main {
    public static void main(String[] args) {
        System.out.println("欢迎进入图书馆");
        menu();
    }
    public static void menu(){
        BookInfoDao bookInfoDao = new BookInfoDao();
        Scanner sc = new Scanner(System.in);
            System.out.println("【1】添加图书【2】删除图书【3】修改图书【4】查看所有图书【5】根据id查询【6】退出");
            System.out.print("请选择:");
            int i = sc.nextInt();
            switch (i){
                case 1:
                    System.out.print("请输入添加的图书名称:");
                    String name = sc.next();
                    System.out.print("请输入添加的图书作者:");
                    String author = sc.next();
                    System.out.print("请输入添加的图书价格:");
                    int price= sc.nextInt();
                    System.out.print("请输入添加的图书库存:");
                    int number = sc.nextInt();
                    BookInfo bookInfo1 = new BookInfo(name, author, price, number);
                    bookInfoDao.insertBook(bookInfo1);
                    break;
                case 2:
                    System.out.print("请输入想要删除的图书编号:");
                    int id = sc.nextInt();
                    bookInfoDao.deleteBook(id);
                    break;
                case 3:
                    System.out.print("请输入需要修改的图书编号:");
                    int bookId = sc.nextInt();
                    System.out.print("请输入图书的新价格:");
                    int bookPrice = sc.nextInt();
                    System.out.print("请输入图书的新库存:");
                    int bookNum = sc.nextInt();
                    bookInfoDao.updateBook(bookId,bookPrice,bookNum);
                    break;
                case 4:
                    //查询
                    List<BookInfo> list = bookInfoDao.selectAll();
                    for (BookInfo bookInfo : list) {
                        System.out.println(bookInfo);
                    }
                    break;
                case 5:
                    //根据id查询
                    System.out.print("请输入想要查看到图书编号:");
                    int num = sc.nextInt();
                    BookInfo bookInfo = bookInfoDao.selectById(num);
                    System.out.println(bookInfo);
                    break;
                case 6:
                    try {
                        Thread.sleep(2000);
                        System.out.println("退出成功");
                        System.exit(0);
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                    break;
                default:
                    System.out.println("输入错误，没有该选项");
                    break;
            }
            menu();
        }
}

数据库设计

实体关系模型(ER模型)

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

关系：实体和实体之间的关系

实体之间关系

一对一

实体A于实体B之间唯一对应

例如:创建国家表和领导人表

-- 国家表
create table country(
	country_id int not null primary key auto_increment,
	country_name varchaer(20) not null,
    country_popolation bigint not null,
    country_area int not null
)
-- 领导人表
create table leader(
	leader_id int not null primary key auto_increment,
	leader_name varchaer(20) not null,
    leader_birthday date not null
)

两张表是独立的，不能体现一对一管理关系，如果想要实现一对一关系，有如下方法：

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

create table relation(
	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) references leader(country_id),
    foreign key (country_id) references country(country_id)
)

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

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

一对多/多对一

一对多：一个实体A对应多个实体B

多对一：多个实体B对应一个实体A

例题：

-- 创建主表
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,
    foriegn key (coach_id) references coach(coach_id)
)

多对多

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

例题：

-- 创建课程表
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)共六种。

第一范式

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

第二范式

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

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

第三范式

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

名词解释

主键/主码/主属性

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

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

联合主键

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

完全依赖

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

部分依赖

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

传递依赖

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

视图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 * FROM myview;
-- 查询平均分最高的课程名及其授课教师
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

表格列转行

SELECT stu_id, 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 test GROUP BY stu_name
ORDER BY stu_id
-- 不只是可以使用sum聚合函数，所有的聚合函数都可使用

事务transaction

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

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

事务的特性ACID

原子性Atomicity

事务是最小的执行单元。

一致性Consistency

事务执行前后，必须要所有的数据保持一致状态。

隔离性Isolation

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

持久性Durability

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

事务的使用

提交：commit

回滚：rollback

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

设置事务不自动提交:set @@autocommit=0;

设置事务自动提交:set @@autocommit=1;

如果管不了十五自动提交，再执行事务中出现错误，可以使用rollback回滚，让数据回到之前的状态，如果不出错，通过commit提交事务，一旦提交事务，无法进行回滚

手动提交/回滚事务

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

2.开启事务：start transation

3.事务要执行sql：

4.如果要回滚，使用rollback

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

事务并发可能出现的问题

事务并发

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

问题

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

事务隔离级别

作用：防止事务并发时出现的问题而提供的隔离级别

隔离级别	是否出现脏读	是否出现不可重复读	是否出现幻读
Read Uncommitted未提交读RU	会	会	会
Read committed已提交读RC(Oracle默认)	不会	会	会
Repeatable Read可重复读RR(MySQL默认)	不会	不会	会
Serializable可序列化	不会	不会	不会

查看事务隔离级别

select @@transatcion_isolation

设置事务隔离级别

set [session] | g1obal] transaction isolation level [Read uncommitted |read committed |repeatable read | serializable]

触发器trigger

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

创建触发器

create trigger 触发器名
触发时机 触发操作 on 表 for each row
BEGIN
	满足触发时机时自动执行的sq1;
end

-- 例如
-- 创建操作日志表
CREATE TABLE log(
log_id INT NOT NULL PRIMARY KEY auto_increment,
log_opt VARCHAR(20) NOT NULL,
log_time datetime NOT NULL
)
-- 在向customer表中添加一条记录后，自动在1og表中添加一条记录
CREATE TRIGGER mytrigger
AFTER INSERT ON customer FOR EACH ROW
BEGIN
	INSERT INTO log VALUES(null,'插入一条数据',NOW());
END

使用触发器

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

删除触发器

drop trigger 触发器名;

存储过程

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

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

调用存储过程

-- 调用无参数的存储过程
call 存储过程名();
--调用输入型参数的存储过程
call 存储过程名('实参');
--调用输c出型参数的存储过程
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 形参名 int)
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('小说')

输出型参数

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

-- 调用,@x表示将存储过程的输出型参数保存到变量x中
CALL myproc3('金庸',@count)
-- 查询参数中保存的数据
SELECT @count

输入输出型参数

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

--例题

-- 查询书中带有指定文字的图书名、作者和类型
CREATE PROCEDURE myproc4(INOUT keyword VARCHAR(20))
BEGIN
	SELECT * 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编程

定义变量

 declare 变量名 数据类型;

给变量赋值：

select 字段/值 into 变量 [form 表]

读取变量的值

select 变量

综合例题：

create procedure 存储过程名()
begin
	-- declare 变量名 数据类型;
	declare num int;
	declare name carchar(30);
	-- select 字段/值 into 变量 [from 表]；
	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_count INT;
	-- 给变量赋值
	SELECT SUM(book_num) INTO sum_count FROM book_info;
	-- 打印变量的值
	SELECT sum_num;
END
-- 调用
CALL myproc5()

条件语句

单分支if语句

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

-- 例
-- 根据作者查询图书库存，如果不足100,输出库存不足100
CREATE PROCEDURE myproc6(IN zz VARCHAR(20))
BEGIN
	-- 定义变量保存根据作者查询到的图书总数
	DECLARE num int;
	-- 查询SQL，讲解国际保存到变量中
	SELECT SUM(book_num) INTO num FROM book_info GROUP BY book_author=zz;
	-- 判断变量num
	IF num<100
	THEN
		SELECT '库存不足100';
	END IF;
END
-- 调用
CALL myproc6('金庸');

双分支if语句

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


-- 例
-- 根据图书类型查询图书数量，如果不足5，输出不足5种图书，如果足够5，输出详情、
CREATE PROCEDURE myproc7(IN lx VARCHAR(50))
BEGIN
	-- 定义变量
	DECLARE num INT;
	-- 查询sql语句
	SELECT COUNT(book_id) INTO num FROM book_info bi 
	INNER JOIN book_type bt ON bi.type_id=bt.type_id 
	WHERE type_name=lx;
	-- 判断
	IF num<5
	THEN
		SELECT '不足5种图书';
	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;


-- 例
-- 输入数字不同，输出不同的内容，相当于java中的switch语句
CREATE PROCEDURE myproc8(IN num INT)
BEGIN
	DECLARE num INT;
	SELECT 5 INTO num;
	CASE num
		WHEN 1 THEN
			SELECT '1';
		WHEN 2 THEN
			SELECT '2';
		ELSE
			SELECT '都不是';
	END CASE;
END
-- 调用
CALL myproc8(6)

循环语句

while循环

WHILE 条件 DO
	满足条件时执行的内容;
END WHILE;

-- 例题

-- 添加10个客户
DROP PROCEDURE myproc9;-- 删除存储过程
CREATE PROCEDURE myproc9()
BEGIN
	-- 定义循环变量
	DECLARE num INT;
	-- 变量初始化
	SELECT 1 INTO num;
	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;


--例题

-- 添加10个客户
DROP PROCEDURE myproc10
CREATE PROCEDURE myproc10()
BEGIN
	DECLARE num INT;
	SELECT 50 INTO num;
	REPEAT
		INSERT INTO customer VALUES(null,CONCAT('测试用户',num),'123456',0,NULL);
		set num = num+1;
	UNTIL num=61 END REPEAT;
END
-- 调用
CALL myproc10()

loop循环

循环名: LOOP
	循环体;
	IF 条件 THEN
		LEAVE 循环名; 
	END IF; 
END LOOP label;


-- 例题

-- 添加10个客户
DROP PROCEDURE myproc11
CREATE PROCEDURE myproc11()
BEGIN
	DECLARE num INT;
	SELECT 100 INTO num;
	test: LOOP
		INSERT INTO customer VALUES(null,CONCAT('测试用户',num),'123456',0,NULL);
		set num = num-1;
	IF num=90 THEN
		LEAVE test; 
	END IF; 
END LOOP ;
END
-- 调用
CALL myproc11()