253 lines
9.2 KiB
Markdown
253 lines
9.2 KiB
Markdown
## MySQL数据库基础
|
||
|
||
### 创建数据库和表
|
||
|
||
创建数据库BookDB。为数据库创建两张表：图书类型表BookType和图书信息表Book，并添加相应约束。见表上机2.1、表上机2.2。
|
||
|
||
表上机2.1 BookType图书类型表
|
||
|
||
|
||
|
||
表上机2.2 Book图书信息表
|
||
|
||
|
||
|
||
```sql
|
||
#创建数据库和表
|
||
CREATE DATABASE bookdb;#创建bookdb数据库 可视化操作已经执行
|
||
|
||
#当前查询使用的数据库是bookdb
|
||
#USE 数据库名;
|
||
USE bookdb;#使用刚刚创建的数据库
|
||
|
||
#创建BookType图书类型表
|
||
#创建 表
|
||
#PRIMARY KEY 主键约束 主键指的是当前字段用来唯一标识每一行 并且不能重复和为空
|
||
#NOT NULL 非空约束 代表该字段不能为空
|
||
DROP TABLE BookType;#删除指定表 DROP TABLE 表名; 表要存在才能删除
|
||
#在创建表时设置主键
|
||
CREATE TABLE BookType(
|
||
type_id INT(4) PRIMARY KEY COMMENT '编号，主键',
|
||
type_name VARCHAR(20) NOT NULL COMMENT '类型',
|
||
type_remark VARCHAR(50) COMMENT '备注'
|
||
);
|
||
|
||
CREATE TABLE 表名(
|
||
列名 数据类型(长度) 约束 COMMENT '备注',
|
||
列名 数据类型(长度) 约束 COMMENT '备注'
|
||
);
|
||
DROP TABLE Book;
|
||
CREATE TABLE Book(
|
||
book_id INT(4) PRIMARY KEY COMMENT '编号，主键',
|
||
book_name VARCHAR(20) NOT NULL COMMENT '书名',
|
||
book_author VARCHAR(20) NOT NULL COMMENT '作者',
|
||
book_date VARCHAR(20) NOT NULL COMMENT '发布时间',
|
||
book_type INT(4) NOT NULL COMMENT '图书类型',
|
||
book_remark VARCHAR(50) COMMENT '备注'
|
||
);
|
||
```
|
||
|
||
|
||
|
||
### 表的数据操作
|
||
|
||
向数据库中的BookType和Book表插入数据，效果如图上机2.4和图上机2.5所示。由于录入数据出现错误，现在需要如下的修改：将《杜拉拉升职记》的发布时间改为“2010-07-2”。
|
||
|
||
|
||
图上机2.4 BookType表的数据
|
||
|
||
|
||
|
||
图上机2.5 Book表的数据
|
||
|
||
|
||
|
||
```sql
|
||
INSERT INTO booktype()
|
||
VALUES
|
||
(1, '文学类', '增加文学常识'),
|
||
(2, '计算机类', '学习计算机'),
|
||
(3, '小说类', '动听的故事');
|
||
|
||
INSERT INTO 表名(字段列表) #如果字段列表为空则所有字段都需要填写记录值
|
||
VALUES
|
||
(数值1, 数值2, 数值3),
|
||
(数值1, 数值2, 数值3),
|
||
(数值1, 数值2, 数值3);
|
||
|
||
INSERT INTO book()
|
||
VALUES
|
||
(1, '就算天空再深', '林夕', '2010-07-01', 1, '中国现当代随笔'),
|
||
(2, '烟花那么凉', '雪小禅', '2010-06-01', 1, '最值得珍藏的女性随笔集'),
|
||
(3, '系统集成项目管理工程师教材', '柳纯录', '2009-03-01', 2, '全国计算机技术与软件专业技术资格(水平)考试指定用书'),
|
||
(4, '杜拉拉升职记', '李可', '2007-09-01', 3, '中国白领必读的职场修炼小说');
|
||
```
|
||
|
||
**删除图书信息**
|
||
|
||
由于业务的需要，发布时间在2010年以前的不再出售，需要全部清理。
|
||
|
||
**删除图书类型**
|
||
|
||
由于业务变化，图书馆不再需要文学类的图书，现要求删除类型文学类产，并将对应的图书一并删除。
|
||
|
||
```sql
|
||
/*
|
||
DELETE FROM 表名
|
||
WHERE 条件;
|
||
*/
|
||
#由于业务的需要，发布时间在2010年以前的不再出售，需要全部清理。
|
||
DELETE FROM book
|
||
WHERE YEAR(book_date) < 2010;
|
||
#YEAR(日期) 获取日期的年份
|
||
|
||
#由于业务变化，图书馆不再需要文学类的图书，现要求删除类型文学类产，并将对应的图书一并删除。
|
||
#1.先删除文学类的图书 2.还需要删除文学类这个图书类
|
||
DELETE FROM book
|
||
WHERE book_type = 1;
|
||
|
||
DELETE FROM booktype
|
||
WHERE type_id = 1;
|
||
```
|
||
|
||
**更新图书**
|
||
|
||
将图书表的图书备注更新成作者名 备注的组合
|
||
|
||
```sql
|
||
/*
|
||
更新
|
||
UPDATE 表名
|
||
SET 字段名=新值
|
||
WHERE 条件;
|
||
*/
|
||
#将图书表的图书备注更新成作者名 备注的组合
|
||
UPDATE book
|
||
SET book_remark = CONCAT(book_author,' ',book_remark);
|
||
```
|
||
|
||
## MySQL查询
|
||
|
||
### 使用内连接显示图书详细信息
|
||
|
||
使用内连接，查询出图书编号、图书名、作者、出版时间、图书类别名称、图书备注，查询结果如图上机3.1所示。
|
||
|
||
图上机3.1 图书详细信息
|
||
|
||
|
||
|
||
```sql
|
||
/*
|
||
SELECT 字段1, 字段2, 字段3
|
||
FROM 表1,表2
|
||
WHERE 表1.主键 = 表2.外键;
|
||
|
||
学生表(学号、学生姓名、班级号)、班级表(班级号,班级名称)
|
||
查询出学生信息以及所在班级名称
|
||
SELECT 学号,学生姓名,班级名称
|
||
FROM 学生表, 班级表
|
||
WHERE 学生表.班级号 = 班级表.班级号;
|
||
|
||
员工表(工号, 员工姓名,部门号)、部门表(部门号, 部门名称)
|
||
查询出员工的信息以及所在部门的名称
|
||
SELECT 工号, 员工姓名,员工表.部门号, 部门名称
|
||
FROM 员工表, 部门表
|
||
WHERE 员工表.部门号 = 部门表.部门号;
|
||
*/
|
||
#使用内连接，查询出图书编号、图书名、作者、出版时间、图书类别名称、图书备注
|
||
SELECT book_id, book_name, book_author, book_date, type_name, book_remark
|
||
FROM book, booktype
|
||
WHERE booktype.type_id = book.book_type;
|
||
#以图书表中的图书类别ID到图书类别表查询对应的记录值 和原来的图书的记录拼起来
|
||
|
||
```
|
||
|
||
### 使用子查询显示计算机类的图书详细信息
|
||
|
||
先使用内连接，查询出图书编号、图书名、作者、出版时间、图书类别名称、图书备注等信息；然后根据计算机类的图书类别编号，筛选出计算机类的图书，查询结果如图上机3.2所示。
|
||
|
||
图上机3.2 计算机类的图书详细信息
|
||
|
||
|
||
|
||
### 使用集合显示图书编号为2和3的图书信息
|
||
|
||
根据客户要求，现在需要查看图书编号为2和3的图书名称和作者，查询结果如图上机3.3所示。
|
||
|
||
图上机3.3 指定图书信息
|
||
|
||
|
||
|
||
### 数据的高级查询
|
||
|
||
查询出类型为文学类的所有图书。查询出李可所有发布时间大于2010年1月1日的小说书集。查询出所有的文学书和计算机书并追加备注说明是翻新书。
|
||
|
||
## 存储过程
|
||
|
||
### 创建存储过程完成图书添加
|
||
|
||
创建存储过程，完成对上机2中book表的数据添加。
|
||
|
||
传入图书相关信息后，执行INSERT 语句将值插入的表中，并自动生成主键值
|
||
|
||
### 创建存储过程完成图书修改
|
||
|
||
创建存储过程，完成对上机2中book表的数据修改。
|
||
|
||
传入图书相关信息，实现对指定编号的图书进行修改
|
||
|
||
### 创建存储过程完成图书删除
|
||
|
||
创建存储过程，根据图书名称将图书信息删除。
|
||
|
||
传入图书编号，实现对指定编号的图书进行删除
|
||
|
||
## 视图
|
||
|
||
### 查询book表数据
|
||
|
||
为上机2中book表建立视图，查询book表中所有数据，视图名为v_book。
|
||
|
||
### 根据条件查询book表数据
|
||
|
||
在上一题的基础上，传入查询条件进行查询,例如查询指定指定作者、类型的图书
|
||
|
||
## 触发器
|
||
|
||
### 记录添加总字符数
|
||
|
||
首先建立两个单域的表格。一个表格中为姓名列表（表格名：data），另一个表格中是所插入字符的字符数（表格名：chars）。希望在data表格中定义一个触发器，每次在其中插入一个新姓名时，chars表格中运行的总数就会根据新插入记录的字符数目进行自动更新。
|
||
|
||
分析：要记录总共插入了的多少字符，先要得到每次插入的字符数和原字符数。
|
||
|
||
### 记录数据的插入和更新时间
|
||
|
||
记录表category中数据的插入和更新时间。category表的内容如下：编号、名称、创建时间、修改时间。
|
||
|
||
分析：创建两个触发器，一个管理添加，一个管理修改。
|
||
|
||
（1） 创建表catagory。
|
||
|
||
（2） 创建触发器create_time用于记录用户往catagory表插入数据的时间
|
||
|
||
（3） 创建触发器update_time用于记录用户更新catagory表数据的时间
|
||
|
||
### 修改记录时，检查是否备注，没有自动添加当前时间
|
||
|
||
book表修改记录时，检查在修改时是否有备注，如果没有自动添加当前时间为备注。
|
||
|
||
## 存储函数
|
||
|
||
### 使用函数根据用户传入的类型名称和类型备注获取刚插入的类型编号
|
||
|
||
使用函数根据用户传入的类型名称和类型备注。该函数包含类型名称和类型备注2个输入参数，类型编号为函数的返回值，函数名命名为AddType
|
||
|
||
分析：在函数中根据用户传入的类型名称和类型备注进行插入数据。再通过系统函数得到最后插入的id值将其返回。最后调用函数，测试函数是否正确。
|
||
|
||
### 使用函数根据用户传入图书信息获取刚插入的图书编号
|
||
|
||
创建一个函数实现图书作者的显示。该函数有一个输入参数，根据用户传入的图书名字，可以返回查询到图书作者信息。
|
||
|
||
### 使用函数根据用户传入图书名称，查询图书的作者信息
|
||
|
||
图书管理员想要得到刚刚插入的图书编号。现要求系统实现一个图书添加的功能：使用函数，根据用户传入的图书名称、作者、出版日期、类型编号、图书备注，得到现在最新的图书id 下面创建函数来实现该查询功能。 |