MySQL复习

1基本操作复习

1.1数据库创建

创建数据库create database 数据库名;判断再创建数据库create database if not exists 数据库名;创建数据库指定字符集create database 数据库名 character set 字符集;创建数据库指定排序方式create database 数据库名 collate 排序方式;创建数据库指定字符集和排序方式create database 数据库名 character set 字符集 collate 排序方式;查询数据库的字符集和排序方式mysql8: 默认 utf8mb4 utf8mb4_0900_ai_cishow variables like 'character_set_database';show variables like 'collation_database';

1.2数据库查看

查看所有库show databases;查看当前使用库select database();查看库下所有表show tables from 数据库名;查看创建库的信息和语句show create database 数据库名;选中和切换库;use 数据库名;

1.3数据库修改

修改字符集alter database 数据库名 character set 字符集;修改排序方式alter database 数据库名 collate 排序方式;修改字符集和排序方式alter database 数据库名 character set 字符集 collate 排序方式;

1.4数据库删除

直接删除drop database 数据库名;判断删除drop database if exists 数据库名;

1.5表相关语法

建表语法总结create table [if not exist] 表名(# 列的信息列名 类型 [列的约束] [列的注释],列名 类型 [列的约束] [列的注释],...列名 类型 [列的约束] [列的注释])[描述][注释]建表事项1. 表名 列名 列类型必须填写的2. 推荐使用if not exists 3. 注释不是必须得,但是是很有必要的!4. 列之间使用,隔开,最后一列没有,

修改和删除表修改表中列添加列alter table 表名 add 列名 类型 [first|alter 列名] ;修改列名alter table 表名 change 原列名 新列名 新类型 [first|alter 列名] ;修改列类型alter table 表名 modify 列名 新类型 [first|alter 列名] ;删除列alter table 表名 drop 列名;修改表名alter table rename [to] 新表名;删除表drop table [if exists ] 表名;清空表数据truncate  table 表名;

 数据操作语言[插入]语法全列插入[不推荐]insert into 表名 values | value (值,值,值...) 值的数量要等于表的所有列的数量值的类型和顺序要和表的类的类型和顺序一一对应指定列插入[推荐]insert into 表名 (列名,列名...) values | value (值,值,值...) 值的数量要等于表的指定的列的数量值的类型和顺序要和[指定列的]顺序一一对应多行插入insert into 表名 (列名,列名...) values | value (值,值,值...) ,(值,值,值...),(值,值,值...)insert into 表名 values | value (值,值,值...) ,(值,值,值...),(值,值,值...)注意1. values 或者 value 推荐使用values2. 插入的是字符串或者时间类型 ''3. 值的顺序和类型要和表的列名或者指定的列名一一对应

数据操作语言[修改]语法全表修改(全行修改)update 表名 set 列名 = 新值 , 列名 = 值 , 列名 = 值 ...条件修改(条件筛选行) update 表名 set 列名 = 新值 , 列名 = 值 , 列名 = 值 ... where 条件注意1. 不添加where,代表修改一个表中的所有行的数据,反之,只修改符合where条件的2. 如果修改多个列 set 列名 = 值 , 列名 = 值

数据操作语言[删除]语法全表删除 delete from  表名;条件行删除 delete from  表名 where 条件;注意1. 开发中很少使用全表删除2. delete删除和清空表truncate 删除,都会删除表中的全部数据,truncate 不仅删除表数据,会删除数据库id的最大记录值!

1.6建表类型

整数类型(类型,占有空间,范围)

标准sql:
int / integer 4字节无符号 0 - 2/32-1 有符号 -2 31 / 2 / 31 -1
smallint 2字节无符号 0 - 2/16-1 有符号 -2 17 / 2 / 17 -1

mysql方言:

tinyint 1字节无符号 0 - 2/8 -1 有符号 -2 7 / 2/7-1
mediumint 3字节无符号 0 - 2/24 -1 有符号 -2 23 / 2/23-1
bigint 8字节无符号 0 - 2/64 -1 有符号 -2 63 / 2/63-1

有符号: 列名整数类型 -> 有符号| 有符号有负值和正值
列名整数类型 unsigned -> 无符号|无符号没有负值,都是正值,将负值部分,绝对值后,加入正值部分!

注意: 选合适范围,范围合适先占有空间最小的!

浮点类型(类型,M,D)

float(m,d) 4字节 m 24 d 8
double(m,d) 8字节 m 53 d 30
定值类型(类型,M,D)
decimal(m,d) 动态占有 m 65 d 30
使用对比:
精度要求不高,例如:身高,体重 float / double
精度要求特别高,钱工资,价格 decimal

字符串类型

char 固定长度类型一旦声明固定占有对应的空间 M 最大255 [性能较好]
varchar 可变长度类型一旦声明,可以插入小于的长度,自动进行伸缩 M 占有的空间不能超过一行的最大显示65535字节 [性能一般]
text 大文本类型,声明不要指定长度,有固定的大小限制, text [65535] , 不占有一行的最大限制空间
细节理解
1. char声明的时候可以不写m char = char(1)
2. char声明了最大长度限制,输入的文本小于长度限制,会在右侧补全空格 char(5) -> 'abc' -> 'abc '
3. char类型在读取的时候,会自动去掉右侧的空格 'abc ' -> 'abc'
4. varchar声明的时候,必须添加m限制 varchar(m)
5. mysql4.0以下版本 varchar(20) -> 20字节限制 mb3 -> 6
6. mysql5.0以上版本 varchar(20) -> 20字符限制
7. varchar类型中识别空格,插入空格读取也是有空格
演示varchar最大限制
前提: mysql中一行数据最大的占有空间是65535字节,除了TEXT or BLOBs类型的列(不占有65535限制法外狂徒)
一行-> name1列 -> name1列占有的最大空间65535字节
varchar类型默认会使用1字节标识是否为null -> 65535-1 = 65534字节
字符集utf8mb4 1个字符 = 4个字节 65534 / 4 = 16383
解决方案 :
1. 缩小字符大小限制 m变小 [不合理]
2. 可以修改字符集 [不合理]
3. 可以将字符串类型变成TEXT,不占有一行的限制

时间类型

year 1 yyyy | yy '1910' | 1910
time 3 HH:MM:SS '10:10:10'
date 3 YYYY-MM-DD '1910-10-10'
datetime 8 YYYY-MM-DD HH:MM:SS '1910-10-10 10:10:10'
timestamp 4 YYYY-MM-DD HH:MM:SS '1970-10-10 10:10:10'
注意情况
1. year可以写两位年 00 - 99 00-69 =2000 - 2069 70 - 99 = 1970 - 1999 推荐四位的年
2. 时间类型,要遵循他们的格式插入,插入的时候就是按字符插入,时间默认不会自动赋值
扩展自动填写时间:
1.插入默认添加时间
datatime | timestamp default current_timestamp
2.修改默认更改时间
datatime | timestamp default current_timestamp on update current_timestamp

1.7总结

CREATE DATABASE IF NOT EXISTS blog_platform CHARACTER SET utf8mb4;USE  blog_platform;SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';ALTER DATABASE blog_platform COLLATE utf8mb4_0900_as_cs;DROP DATABASE IF EXISTS  blog_platform ;SHOW DATABASES;

CREATE DATABASE IF NOT EXISTS book_libs CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;USE book_libs;CREATE TABLE IF NOT EXISTS books (book_name VARCHAR(20) COMMENT '图书名',book_price DOUBLE COMMENT '图书价格',book_num INT COMMENT '图书数量'
) CHARSET=utf8mb4 COMMENT='图书表';SHOW TABLES;
DROP DATABASE IF EXISTS   book_libs ;

2约束复习

MySQL中的约束（Constraints）是一种数据完整性规则，用于限制进入表中的数据类型，保证数据的准确性和可靠性。

2.1约束类型

约束类型分为以下几种：

非空约束：NOT NULL - 指示某列不能存储 NULL 值
唯一约束：UNIQUE - 保证某列的每行必须有唯一的值
默认约束：DEFAULT - 规定没有给列赋值时的默认值
主键约束：PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录
外键约束：FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性
检查约束：CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句

引用自:MySQL：约束

2.2非空约束

确保列不能有NULL值。

指定非空约束，创建表时在对应的字段后加上not null即可。

示例：email VARCHAR(100) NOT NULL;

应用：如注册登录信息的数据表时候，不可以没有密码和账户

2.3唯一约束

保证表中所有数据在某一列或列组合上是唯一的。可以有多个唯一约束。

指定唯一约束，创建表时在对应的字段后加上unique即可。

示例：username VARCHAR(50) NOT NULL UNIQUE;

应用：如注册登录信息的数据表时候，每个人的账户都是不同的

（再如每个人的学号和身份证号）

2.4默认约束

为列提供一个默认值，当插入记录时如果没有指定该列的值，则使用默认值。

指定默认，创建表时在对应的字段后加上default即可。

示例：age INT;(为null)

name varchar(20) default 'myname'; (为myname)

只有当列没有给出指定值时，当前列才会用默认约束的默认值来填充，但是一旦我们对列手动给出指定值后，列的值就为用户指定的值（即使手动指定的值为null，列的值也会为null）

2.5主键约束

在MySQL中，主键约束（PRIMARY KEY）用于确保表中每一行的唯一性。主键可以是单个列或多个列的组合，这些列的值组合在表中必须是唯一的，并且不允许包含NULL值。每个表只能有一个主键，通常主键列会有自动增长（AUTO_INCREMENT）属性，以便自动为新行生成唯一的序列号。

（确保表中每行数据的唯一性。一个表只能有一个主键。主键列不能包含NULL值。）

主键约束即primary key，是not null和unique的结合，也就是说被主键约束指定的列既是非空的也是唯一的。

注意：primary key（主键约束）效果和not null unique约束相同，但是本质不同，主键约束除了可以做到not null unique之外，还会默认添加"索引——index"。

引用自：https://blog.csdn.net/2401_83595513/article/details/140967539

示例：id INT AUTO_INCREMENT PRIMARY KEY,

创建主键约束

创建主键约束通常在创建表时进行，可以直接在列定义中指定PRIMARY KEY，或者在CREATE TABLE语句的末尾单独指定。例如：

CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(40),password VARCHAR(255),email VARCHAR(255));

如果主键由多个列组成，称为复合主键，应在CREATE TABLE语句的末尾指定：

CREATE TABLE user_roles (user_id INT NOT NULL,role_id INT NOT NULL,PRIMARY KEY(user_id, role_id),FOREIGN KEY(user_id) REFERENCES users(user_id),FOREIGN KEY(role_id) REFERENCES roles(role_id));

修改表添加主键约束

对于已存在的表，如果需要添加主键约束，可以使用ALTER TABLE语句：

ALTER TABLE t1 ADD PRIMARY KEY(id);

主键与其他键的区别

主键（PRIMARY KEY）与唯一键（UNIQUE KEY）和索引键（KEY）有所不同。UNIQUE KEY确保列上的值唯一，但允许NULL值，一个表可以有多个UNIQUE KEY。而KEY是INDEX的同义词，用于创建非主键和非唯一索引的列或列组合。

删除主键约束

如果需要删除表的主键约束，可以使用以下语句：

ALTER TABLE table_name DROP PRIMARY KEY;

主键约束是数据库设计中的一个重要概念，它确保了数据的完整性和一致性。在实际应用中，合理使用主键约束可以提高数据库操作的效率和准确性。

在一个表中只能用一个主键，不允许有两个或多个主键

2.6外键约束

用于在两个表之间建立链接。确保一个表中的数据值在另一个表中存在。

一张表（子表）中某个列（外键）的值，必须是另一张表（主表）中的主键列或唯一约束列的值，也就是子表中的值在主表中必须存在，且满足主键或唯一约束。

子表中的这个列就称为外键，与主表中的主键或唯一键就建立起了主外键关系。

引用自：https://blog.csdn.net/2401_83595513/article/details/140967539

示例：username VARCHAR(50) NOT NULL UNIQUE,

ALTER TABLE 从表名 ADD [外键名字] FOREIGN KEY(外键字段名);
REFERENCES 主表名(主键字段名);

完整版

ALTER TABLE 从表名 
ADD CONSTRAINT 外键名字 
FOREIGN KEY (外键字段名) 
REFERENCES 主表名 (主键字段名)
[ON DELETE 引用操作]
[ON UPDATE 引用操作];

ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) 
REFERENCES customers (id)
ON DELETE CASCADE
ON UPDATE RESTRICT;

从表名 - 包含外键的表
外键名字 - 给外键约束命名的标识符（可选但推荐）
外键字段名 - 从表中引用主表的字段
主表名 - 被引用的主表
主键字段名 - 主表中被引用的主键或唯一键

可选部分：

ON DELETE - 指定当主表记录被删除时的操作（CASCADE, SET NULL, RESTRICT等）
ON UPDATE - 指定当主表键值更新时的操作

2.7检查约束

限制列中的值必须满足一定的条件。在MySQL 8.0.16及更高版本中，CHECK约束被实现为表达式索引。

创建表时设置检查约束的语法规则如下：

CHECK(<检查约束>)

修改表时设置检查约束的语法规则如下：

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

修改表时删除检查约束的语法规则如下：

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

引用自：MySQL检查约束（CHECK）

2.8总结

CREATE DATABASE bms CHARACTER SET utf8mb4;USE bms;CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE book(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,price DECIMAL(6,2) NOT NULL,upload_time DATETIME NOT NULL,borrower_id INT,borrower_time DATETIME ,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE record(id INT PRIMARY KEY AUTO_INCREMENT,book_id INT NOT NULL,borrower_id INT NOT NULL,borrower_time DATETIME NOT NULL,remand_time DATETIME NOT NULL);DESC book;DROP DATABASE bms;

3单表查询与多表操作

CREATE DATABASE bms CHARACTER SET utf8mb4;USE bms;CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE book(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,price DECIMAL(6,2) NOT NULL,upload_time DATETIME NOT NULL,borrower_id INT,borrower_time DATETIME ,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE record(id INT PRIMARY KEY AUTO_INCREMENT,book_id INT NOT NULL,borrower_id INT NOT NULL,borrower_time DATETIME NOT NULL,remand_time DATETIME NOT NULL);DESC book;INSERT INTO book(NAME,price,upload_time,state)VALUE ('Java基础入门（第三版）',59.00,CURRENT_TIMESTAMP,0);INSERT INTO USER(NAME,state) VALUES('张三',0),('李四',0);INSERT INTO book(NAME,price,upload_time,state)VALUES('三国演义',69.00,CURRENT_TIMESTAMP,2),('MySQL数据库入门',40.00,CURRENT_TIMESTAMP,0),('Java Web 程序开发入门',40.00,CURRENT_TIMESTAMP,0),('西游记',59.00,CURRENT_TIMESTAMP,2),('水浒传',33.00,CURRENT_TIMESTAMP,2),('红楼梦',66.66,CURRENT_TIMESTAMP,2);UPDATE book SET state=0 WHERE NAME='西游记';UPDATE book SET price=66.00 WHERE NAME='水浒传';UPDATE book SET price=price*0.9;DELETE FROM book WHERE NAME='红楼梦';UPDATE book SET borrower_id=1,borrow_time=CURRENT_TIMESTAMP,state=1WHERE NAME='MySQL数据库入门';DROP DATABASE bms;

4索引与视图

普通索引：基础索引类型，允许重复值和 NULL。

唯一索引：确保数据唯一，允许 NULL，表中可以有多个唯一索引。

主键索引：唯一标识一行数据，不允许 NULL 和重复。
全文索引：适合文本内容搜索，通过分词技术实现。

空间索引：专门用于加速地理空间数据的查询速度。

复合索引：在多个列上同时创建的索引，也被称作联合索引。

原文链接：MySQL数据库——索引介绍

CREATE DATABASE bms CHARACTER SET utf8mb4;USE bms;CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE book(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,price DECIMAL(6,2) NOT NULL,upload_time DATETIME NOT NULL,borrower_id INT,borrower_time DATETIME ,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE record(id INT PRIMARY KEY AUTO_INCREMENT,book_id INT NOT NULL,borrower_id INT NOT NULL,borrower_time DATETIME NOT NULL,remand_time DATETIME NOT NULL);DESC book;INSERT INTO book(NAME,price,upload_time,state)VALUE ('Java基础入门（第三版）',59.00,CURRENT_TIMESTAMP,0);INSERT INTO USER(NAME,state) VALUES('张三',0),('李四',0);INSERT INTO book(NAME,price,upload_time,state)VALUES('三国演义',69.00,CURRENT_TIMESTAMP,2),('MySQL数据库入门',40.00,CURRENT_TIMESTAMP,0),('Java Web 程序开发入门',40.00,CURRENT_TIMESTAMP,0),('西游记',59.00,CURRENT_TIMESTAMP,2),('水浒传',33.00,CURRENT_TIMESTAMP,2),('红楼梦',66.66,CURRENT_TIMESTAMP,2);UPDATE book SET state=0 WHERE NAME='西游记';UPDATE book SET price=66.00 WHERE NAME='水浒传';UPDATE book SET price=price*0.9;DELETE FROM book WHERE NAME='红楼梦';UPDATE book SET borrower_id=1,borrow_time=CURRENT_TIMESTAMP,state=1WHERE NAME='MySQL数据库入门';DROP DATABASE bms;

5事务

事务的隔离级别

READ UNCOMMITTED（未提交读）：

在这个级别下，事务可以读取到其他事务未提交的修改。这可能会导致“脏读”（Dirty Read）、“不可重复读”（Non-repeatable Read）和“幻读”（Phantom Read）的问题。

READ COMMITTED（提交读）：

事务只能读取到已经提交的数据。这避免了“脏读”，但仍然可能发生“不可重复读”和“幻读”。

REPEATABLE READ（可重复读）：

这是MySQL的默认事务隔离级别。在该级别下，确保在同一个事务中多次读取同样记录的结果是一致的，避免了“不可重复读”。但是，仍然可能发生“幻读”。

SERIALIZABLE（可串行化）：

这是最高的隔离级别，通过完全串行化事务的执行来避免并发事务带来的问题，从而防止脏读、不可重复读和幻读。这种方式通过锁定读取的行来实现，可能会严重影响性能和并发性。

CREATE DATABASE bms CHARACTER SET utf8mb4;USE bms;CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE book(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,price DECIMAL(6,2) NOT NULL,upload_time DATETIME NOT NULL,borrower_id INT,borrower_time DATETIME ,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE record(id INT PRIMARY KEY AUTO_INCREMENT,book_id INT NOT NULL,borrower_id INT NOT NULL,borrower_time DATETIME NOT NULL,remand_time DATETIME NOT NULL);DESC book;INSERT INTO book(NAME,price,upload_time,state)VALUE ('Java基础入门（第三版）',59.00,CURRENT_TIMESTAMP,0);INSERT INTO USER(NAME,state) VALUES('张三',0),('李四',0);INSERT INTO book(NAME,price,upload_time,state)VALUES('三国演义',69.00,CURRENT_TIMESTAMP,2),('MySQL数据库入门',40.00,CURRENT_TIMESTAMP,0),('Java Web 程序开发入门',40.00,CURRENT_TIMESTAMP,0),('西游记',59.00,CURRENT_TIMESTAMP,2),('水浒传',33.00,CURRENT_TIMESTAMP,2),('红楼梦',66.66,CURRENT_TIMESTAMP,2);UPDATE book SET state=0 WHERE NAME='西游记';UPDATE book SET price=66.00 WHERE NAME='水浒传';UPDATE book SET price=price*0.9;DELETE FROM book WHERE NAME='红楼梦';UPDATE book SET borrower_id=1,borrower_time=CURRENT_TIMESTAMP,state=1WHERE NAME='MySQL数据库入门';SELECT u.id,u.name borrower,b.name bookname,b.borrower_time FROM book b,USER u;SELECT NAME,price FROM book WHERE price>(SELECT price FROM book WHERE NAME='西游记');SELECT NAME,price FROM book WHERE price<(SELECT AVG(price) FROM book);SELECT NAME,price,state FROM bookWHERE state=(SELECT state FROM book WHERE NAME='三国演义');SELECT NAME,price,state FROM bookWHERE state=1 AND price<ANY(SELECT price FROM book WHERE state=0);SELECT NAME,price,state FROM bookWHERE price>=ALL(SELECT price FROM book WHERE state=1);ALTER TABLE book ADD CONSTRAINT fk_id FOREIGN KEY(borrower_id) REFERENCES USER(id);ALTER TABLE book ADD INDEX index_bookname(NAME);ALTER TABLE book ADD INDEX index_bookname_state(NAME(20),state(1));ALTER TABLE book DROP INDEX index_bookname_state;CREATE VIEW view_book_state(图书名字,上架时间,状态)ASSELECT NAME,upload_time,state FROM book;CREATE VIEW view_book_borrower(图书名字,借阅者,借阅时间)AS SELECT b.name,u.name,borrower_time FROM book b,USER u WHERE b.borrower_id=u.id;DROP VIEW view_book_borrower;#开启事务
START TRANSACTION;
#删除数据
DELETE FROM book;
INSERT INTO book (NAME,price,upload_time,borrower_id,borrower_time,state)VALUE('Java基础入门',59.00,CURRENT_TIMESTAMP,NULL,NULL,0),('三国演义',69.00,CURRENT_TIMESTAMP,NULL,NULL,0),('MySQL数据库入门',40.00,CURRENT_TIMESTAMP,1,'2021-08-06 11:16:05',1),('Java Web 程序开发入门',49.00,CURRENT_TIMESTAMP,NULL,NULL,0),('西游记',59.00,CURRENT_TIMESTAMP,NULL,NULL,0),('水浒传',66.66,CURRENT_TIMESTAMP,NULL,NULL,0),('唐诗三百首',39.00,CURRENT_TIMESTAMP,NULL,NULL,0),('Python数据可视化',49.80,CURRENT_TIMESTAMP,NULL,NULL,0);SELECT *FROM book;DROP DATABASE bms;

6数据库编程（储存）

CREATE DATABASE bms CHARACTER SET utf8mb4;USE bms;CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE book(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,price DECIMAL(6,2) NOT NULL,upload_time DATETIME NOT NULL,borrower_id INT,borrower_time DATETIME ,state CHAR(1) NOT NULL DEFAULT 0
);CREATE TABLE record(id INT PRIMARY KEY AUTO_INCREMENT,book_id INT NOT NULL,borrower_id INT NOT NULL,borrower_time DATETIME NOT NULL,remand_time DATETIME NOT NULL);DESC book;INSERT INTO book(NAME,price,upload_time,state)VALUE ('Java基础入门（第三版）',59.00,CURRENT_TIMESTAMP,0);INSERT INTO USER(NAME,state) VALUES('张三',0),('李四',0);INSERT INTO book(NAME,price,upload_time,state)VALUES('三国演义',69.00,CURRENT_TIMESTAMP,2),('MySQL数据库入门',40.00,CURRENT_TIMESTAMP,0),('Java Web 程序开发入门',40.00,CURRENT_TIMESTAMP,0),('西游记',59.00,CURRENT_TIMESTAMP,2),('水浒传',33.00,CURRENT_TIMESTAMP,2),('红楼梦',66.66,CURRENT_TIMESTAMP,2);UPDATE book SET state=0 WHERE NAME='西游记';UPDATE book SET price=66.00 WHERE NAME='水浒传';UPDATE book SET price=price*0.9;DELETE FROM book WHERE NAME='红楼梦';UPDATE book SET borrower_id=1,borrower_time=CURRENT_TIMESTAMP,state=1WHERE NAME='MySQL数据库入门';SELECT u.id,u.name borrower,b.name bookname,b.borrower_time FROM book b,USER u;SELECT NAME,price FROM book WHERE price>(SELECT price FROM book WHERE NAME='西游记');SELECT NAME,price FROM book WHERE price<(SELECT AVG(price) FROM book);SELECT NAME,price,state FROM bookWHERE state=(SELECT state FROM book WHERE NAME='三国演义');SELECT NAME,price,state FROM bookWHERE state=1 AND price<ANY(SELECT price FROM book WHERE state=0);SELECT NAME,price,state FROM bookWHERE price>=ALL(SELECT price FROM book WHERE state=1);ALTER TABLE book ADD CONSTRAINT fk_id FOREIGN KEY(borrower_id) REFERENCES USER(id);ALTER TABLE book ADD INDEX index_bookname(NAME);ALTER TABLE book ADD INDEX index_bookname_state(NAME(20),state(1));ALTER TABLE book DROP INDEX index_bookname_state;CREATE VIEW view_book_state(图书名字,上架时间,状态)ASSELECT NAME,upload_time,state FROM book;CREATE VIEW view_book_borrower(图书名字,借阅者,借阅时间)AS SELECT b.name,u.name,borrower_time FROM book b,USER u WHERE b.borrower_id=u.id;DROP VIEW view_book_borrower;#开启事务
START TRANSACTION;
#删除数据
DELETE FROM book;
INSERT INTO book (NAME,price,upload_time,borrower_id,borrower_time,state)VALUE('Java基础入门',59.00,CURRENT_TIMESTAMP,NULL,NULL,0),('三国演义',69.00,CURRENT_TIMESTAMP,NULL,NULL,0),('MySQL数据库入门',40.00,CURRENT_TIMESTAMP,1,'2021-08-06 11:16:05',1),('Java Web 程序开发入门',49.00,CURRENT_TIMESTAMP,NULL,NULL,0),('西游记',59.00,CURRENT_TIMESTAMP,NULL,NULL,0),('水浒传',66.66,CURRENT_TIMESTAMP,NULL,NULL,0),('唐诗三百首',39.00,CURRENT_TIMESTAMP,NULL,NULL,0),('Python数据可视化',49.80,CURRENT_TIMESTAMP,NULL,NULL,0);SELECT *FROM book;DELIMITER //
#创建储存过程proc_1CREATE PROCEDURE proc_1()
BEGIN
SELECT b.name,b.price,IF(borrower_id IS NULL,'未借阅',u.name)
FROM book b LEFT JOIN USER u ON b.borrower_id=u.id;
END //DELIMITER ;
CALL proc_1();DELIMITER //
#创建存储过程proc_2;CREATE PROCEDURE proc_2()
BEGIN
SELECT NAME,price,upload_time FROM book WHERE state=0;
END //DELIMITER ;
CALL proc_2();DELIMITER //
#创建存储函数func_1;
CREATE FUNCTION func_1(uname VARCHAR(20))RETURNS VARCHAR(20)DETERMINISTIC READS SQL DATABEGINRETURN (SELECT b.name FROM USER u,book b WHERE u.id=b.borrower_id AND u.name=uname);END //DELIMITER;
SELECT func_1('张三');DELIMITER //
#创建存储函数func_2;
CREATE FUNCTION func_2(bname VARCHAR(255))RETURNS VARCHAR(20)READS SQL DATABEGIN DECLARE blevel VARCHAR(20);DECLARE bprice DECIMAL(6,2);SELECT price INTO bprice FROM book WHERE NAME = bname;IF bprice>60THEN SET blevel='高价格';ELSEIF bprice<=60 AND bprice>40THEN SET blevel='主流价格';ELSEIF empsal<=40THEN SET blevel='平民价格';END IF;RETURN blevel;END //DELIMITER ;SELECT func_2('西游记');SHOW PROCEDURE STATUS LIKE 'proc_1' 
SHOW FUNCTION  STATUS LIKE 'func_1'DROP PROCEDURE IF EXISTS proc_1 ;
DROP FUNCTION IF EXISTS func_1 ;CREATE TRIGGER trig_bookAFTER INSERT ON record FOR EACH ROWUPDATE book SET borrower_id=NULL,borrow_time=NULL,state=0WHERE book.id=new.book_id;SELECT * FROM  information_schema.triggers WHERE trigger_name='trig_book'DROP DATABASE bms;