MySQL数据库基础

1.数据库的操作命令：

1. show databases ; 显示当前有的数据库
2. create database if not exists db_test1 创建db_test1的数据库（如果系统没有 db_test1的数据库，则创建一个名叫 db_test1 的数据库，如果有则不创建）
3. use db_test1 使用db_test1 数据库
4. drop database db_test1 删除db_test1 数据库

2.常见数据类型

3. 表操作

1. use de_test1 ; 先进入库
   desc 表命 ; 再查看表
2. create table …建表

4. Workbench 创建一个图书管理系统 BMS

1. 创建库
   
   生成的SQL语句：

CREATE SCHEMA `test_4_19` DEFAULT CHARACTER SET utf8mb4 ;

2. 进入数据库 ：
   第一种方法 ： 在Workbench中选中库后，右键设置默认库
   第二种方法 ： use test_4_19;
3. 创建表： 管理系统包含3张表 ， 学生，图书，借阅记录

CREATE TABLE `students` (
    `sid` INT NOT NULL AUTO_INCREMENT,
    `sn` INT NOT NULL COMMENT '学号，不会重复',
    `name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`sid`),
    UNIQUE INDEX `sn_UNIQUE` (`sn` ASC)
);

CREATE TABLE `books` (
    `bid` INT NOT NULL AUTO_INCREMENT,
    `isbn` INT NOT NULL COMMENT '书的唯一编号',
    `name` VARCHAR(45) NOT NULL,
    `total` INT NOT NULL COMMENT '总量',
    `stock` INT NOT NULL COMMENT '当前存量',
    `published_at` DATE NOT NULL COMMENT '出版时间',
    PRIMARY KEY (`bid`),
    UNIQUE INDEX `isbn_UNIQUE` (`isbn` ASC)
);

CREATE TABLE `borrowed_records` (
  `brid` int(11) NOT NULL AUTO_INCREMENT,
  `sid` int(11) NOT NULL,
  `bid` int(11) NOT NULL,
  `borrowed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_returned` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0: 未归还\n1: 已归还',
  `returned_at` datetime DEFAULT NULL,
  PRIMARY KEY (`brid`),
  UNIQUE KEY `borrowed_at_UNIQUE` (`sid`,`bid`,`borrowed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='借阅记录';

4. 在表中进行增删查改 (CRUD)

-学生信息
insert into students (sn, name) values
	(202004001, "A"),
    (202004002, "B"),
    (202004003, "C"),
    (202004004, "D"),
    (202004005, "E"),
    (202004006, "F");

-- 书籍上架
-- 输入：  <isbn: 21935876>  <name: Java 核心技术> <total: 10>
--    if (表中已经有书籍) {
update books set total = total + 10, stock = stock + 10 where isbn = 21935876;
--    } else {
insert into books (isbn, name, total, stock)
     values (21935876, "Java 核心技术", 10, 10);
-- }

-- 书籍下架：
-- 输入：  <isbn: 21935876>  <name: Java 核心技术> <total: 10>
update books set total = total - 10, stock = stock - 10 where isbn = 21935876;

-- 借书
-- 输入: 谁<sid> 借了 哪本书 <bid>
-- 表现为 事务(Transaction)
update books set stock = stock - 1 where isbn = 21935876;
insert into borrowed_records (sid, bid) values (1, 1);

-- 还书
-- 输入： 借阅记录
-- 表现为 事务
UPDATE books 
SET 
    stock = stock + 1
WHERE
    isbn = 21935876;
    
UPDATE borrowed_records 
SET 
    is_returned = 1,
    returned_at = '2021-04-18 14:50:38'
WHERE
    brid = 1;
    
-- 检索
select bid, isbn, name, stoc, total, published_at
from books where name like '%Java%'
order by published_at desc
limit 20 offset 40;

select bid, isbn, name, stoc, total, published_at
from books where name like '%Java%' and stock > 0
order by published_at desc
limit 20 offset 40;