mysql 课本实训操作

菜鸟xy..

已于 2024-08-30 16:15:01 修改

阅读量1.1k

点赞数 9

文章标签： mysql android 数据库

于 2024-08-30 16:09:09 首次发布

本文链接：https://blog.csdn.net/2302_81688883/article/details/141720325

版权

bookstore 结构加实训

create database bookstore
default character set gb2312
collate gb2312_chinese_ci;

CREATE TABLE book (
图书编号 char(20) NOT NULL PRIMARY KEY,
图书类别 varchar(20) not NULL DEFAULT '计算机',
书名 varchar(40) NOT NULL,
作者 char(10) NOT NULL,
出版社 varchar(20) NOT NULL,
出版时间 date NOT NULL,
单价 float(5,2) NOT NULL,
数量 int(0) NULL DEFAULT NULL,
折扣 float(3,2) NULL DEFAULT NULL
)ENGINE=InnoDB;

CREATE TABLE members (
用户号 char(18) NOT NULL,
姓名 char(10) NOT NULL,
性别 char(2) NOT NULL,
密码 char(6) NOT NULL,
联系电话 varchar(20) NOT NULL,
注册时间 datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`用户号`)
) ENGINE = InnoDB;

CREATE TABLE sell (
订单号 int(0) NOT NULL PRIMARY KEY,
用户号 char(18) NOT NULL,
图书编号 char(20) NOT NULL,
订购册数 int(0) NOT NULL,
订购单价 float(5, 2) NOT NULL,
订购时间 datetime(0) NOT NULL,
是否发货 varchar(10) NULL DEFAULT '不确定',
是否收货 varchar(10) NULL DEFAULT '不确定',
是否结清 varchar(10) NULL DEFAULT '不确定'
) ENGINE = InnoDB ;

数据：

book表

INSERT INTO `book` (`图书编号`, `图书类别`, `书名`, `作者`, `出版社`, `出版时间`, `单价`, `数量`, `折扣`)
VALUES
('TP.00001', '网页设计', '计算机基础', '李华', '人民邮电出版社', '2012-06-26', 27, NULL, NULL),

('TP.00002', '计算机', 'JavaScript网站制作', '谢为民', '中国青年出版社', '2010-08-16', 33, 60, 0.8),

('TP.00003', '网页设计', 'PHP网站制作', '林小红', '清华大学出版社', '2011-10-16', 23.5, 3, 0.8),

('TP.00004', '计算机', '计算机应用基础', '陆大强', '清华大学出版社', '2011-10-16', 45, 45, 0.8),

('TP.00005', '计算机', '计算机网络技术', '林力辉', '清华大学出版社','2011-10-16', 25.5, 45, 0.8),

('TP.00006', '计算机', '计算机文化基础', '林华忠', '清华大学出版社', '2011-10-16', 45.5, 45, 0.8),
('TP.00007', '网页设计', 'ASP网站制作','胡莉惠', '中国青年出版社', '2010-08-16', 30.5, 50, 0.8),

('TP.00008', '网页设计', 'PHP_MySQL网站制作', '王大卫', '中国青年出版社', '2010-08-16', 33.25, 50, 0.8),

('TP.00009', '数据库技术', '网络数据库', '张小刚', '北京大学出版社', '2011-08-02', 28, NULL, NULL),

('TP.00010', '网页设计', '网页程序设计','刘辉', '清华大学出版社', '2011-02-15', 25, NULL, NULL),

('TP.00011', '数据库技术', 'MYSQL数据库','李刚', '北京大学出版社', '2013-01-26', 20, 500, 0.8),

('TP.00012', '网页设计', 'Dreamwearer_8网站制作', '鲍嘉', '中国青年出版社', '2010-08-16', 33.2, 50, 0.8);

members表

INSERT INTO `members` (`用户号`, `姓名`, `性别`, `密码`, `联系电话`, `注册时间`)
VALUES
('A0012', '赵宏宇', '男', '080100', '13601234123', '2007-03-04 18:23:45'),

('A3013', '张凯', '男', '080100', '13611320001', '2007-01-15 09:12:23'),

('B0022', '王林', '男', '080100', '12501234123', '2007-01-12 08:12:30'),

('B2023', '李小冰', '女', '080100', '13651111081', '2007-01-18 08:57:18'),

('C0132', '张莉', '女', '123456', '13822555432', '2012-09-23 00:00:00'),

('C0138', '李华', '女', '123456', '13822551234', '2013-08-23 00:00:00'),

('D1963', '张三', '男', '222222', '51985523', '2007-01-23 08:15:45');

sell表
INSERT INTO `sell` (`订单号`, `用户号`, `图书编号`, `订购册数`, `订购单价`, `订购时间`, `是否发货`, `是否收货`, `是否结清`)
VALUES
('1', 'D1963', 'TP.00001', 4, 25, '2013-08-26 12:25:03', '已发货', '已收货', '已结清'),

('10', 'C0132', 'TP.00002', 10, 27, '2013-08-01 12:13:49', NULL, NULL, NULL),

('11', 'C0132', 'TP.00003', 30, 25, '2013-08-01 12:13:49', '已发货', '已收货', '已结清'),

('12', 'C0132', 'TP.00004', 40, 28, '2013-08-01 12:13:49', NULL, NULL, NULL),

('13', 'C0132', 'TP.00005', 13, 20, '2013-08-01 12:13:49', '已发货', NULL, NULL),

('14', 'C0138', 'TP.00006', 10, 23.5, '2013-08-01 12:13:49', '已发货', '已收货', '已结清'),

('15', 'C0138', 'TP.00007', 133, 33.5, '2013-08-01 12:13:49', NULL, NULL, NULL),

('16', 'C0138', 'TP.00007', 43, 30, '2013-08-01 12:13:49', '已发货', NULL, NULL),

('2', 'D1963', 'TP.00008', 3, 31.5, '2013-08-05 12:25:12', '已发货', NULL, NULL),

('3', 'D1963', 'TP.00009', 6, 23.45, '2013-03-26 12:25:23', '已发货', '已收货', NULL),

('4', 'B2023', 'TP.00010', 7, 37.1, '2009-02-17 00:00:00', '已发货', '已收货', '已结清'),

('5', 'A3013', 'TP.00011', 7, 65.6, '2010-02-01 00:00:00', NULL, NULL, NULL),

('6', 'A3013', 'TP.00012', 4, 89, '2009-08-20 00:00:00', NULL, NULL, NULL),

('7', 'C0138', 'TP.00012', 6, 23, '2013-03-19 12:25:32', '已发货', '已收货', NULL),

('8', 'C0138', 'TP.00011', 5, 45.5, '2010-02-02 00:00:00', NULL, NULL, NULL),

('9', 'C0132', 'TP.00012', 6, 23, '2013-08-12 18:23:35', '已发货', '已收货', NULL);

商业示例 pestore 库表结构

创建数据库

create database Petstore
default character set gb2312
collate gb2312_chinese_ci;

建表：

CREATE TABLE account
(
userid char(6) NOT NULL,
fullname   varchar(10) NOT NULL,
passward   varchar(20) NOT NULL,
sex   char(2) NOT NULL,
   address    varchar(40) DEFAULT NULL,
   email    varchar(20) DEFAULT NULL,
   phone     varchar(11) NOT NULL,
PRIMARY KEY ( userid )
)

CREATE TABLE category
(
catid   char(10) NOT NULL,
catname   varchar(20) DEFAULT NULL,
cades   text,
PRIMARY KEY ( catid )
)

CREATE TABLE   lineitem (
orderid int(11) NOT NULL,
   itemid char(10) NOT NULL,
quantity int(11) NOT NULL,
   unitprice decimal(10,2) NOT NULL,
PRIMARY KEY ( orderid , itemid )
)

CREATE TABLE orders
(
   orderid int(11) NOT NULL AUTO_INCREMENT,
   userid char(6) NOT NULL,
orderdate datetime NOT NULL,
totalprice decimal(10,2) DEFAULT NULL,
   status tinyint(1) DEFAULT NULL,
PRIMARY KEY ( orderid )
)

CREATE TABLE product
(
   productid char(10) NOT NULL,
   catid char(10) NOT NULL,
   name varchar(30) DEFAULT NULL,
descn   text,
   listprice   decimal(10,2) DEFAULT NULL,
   unitcost decimal(10,2) DEFAULT NULL,
   qty int(11) NOT NULL,
PRIMARY KEY ( productid )
)

数据插入:

/* `account` */

INSERT INTO account VALUES ('u0001', '刘晓和', '123456', '男', '广东深圳市', 'liuxh@163.com', '13512345678');
INSERT INTO account VALUES ('u0002', '张嘉庆', '123456', '男', '广东深圳市', 'zhangjq@163.com', '13512345679');
INSERT INTO account VALUES ('u0003', '罗红红', '123456', '女', '广东深圳市', 'longhh@163.com', '13512345689');
INSERT INTO account VALUES ('u0004', '李昊华', '123456', '女', '广东广州市', 'lihh@163.com', '13812345679');
INSERT INTO account VALUES ('u0005', '吴美霞', '123456', '女', '广东珠海市', 'wumx@163.com', '13512345879');
INSERT INTO account VALUES ('u0006', '王天赐', '123456', '男', '广东中山市', 'wangtc@163.com', '13802345679');

/* `category` */

INSERT INTO category VALUES ('01', '鸟类', '');
INSERT INTO category VALUES ('02', '猫', '');
INSERT INTO category VALUES ('03', '狗', '');
INSERT INTO category VALUES ('04', '鱼', '');
INSERT INTO category VALUES ('05', '爬行类', '');

/* `lineitem` */

INSERT INTO lineitem VALUES (20130411, 'FI-SW-01', 10, 18.50);
INSERT INTO lineitem VALUES (20130411, 'FI-SW-02', 12, 16.50);
INSERT INTO lineitem VALUES (20130412, 'K9-BD-01', 2, 120.00);
INSERT INTO lineitem VALUES (20130412, 'K9-PO-02', 1, 220.00);
INSERT INTO lineitem VALUES (20130413, 'K9-DL-01', 1, 130.00);
INSERT INTO lineitem VALUES (20130414, 'RP-SN-01', 2, 125.00);
INSERT INTO lineitem VALUES (20130415, 'AV-SB-02', 2, 50.00);

/* `orders` */

INSERT INTO orders VALUES (20130411, 'u0001', '2013-04-11 15:07:34', 500.00, 0);
INSERT INTO orders VALUES (20130412, 'u0002', '2013-04-09 15:08:11', 305.60, 0);
INSERT INTO orders VALUES (20130413, 'u0003', '2013-04-15 15:09:00', 212.40, 0);
INSERT INTO orders VALUES (20130414, 'u0003', '2013-04-16 15:09:30', 120.45, 1);
INSERT INTO orders VALUES (20130415, 'u0004', '2013-04-02 15:10:05', 120.30, 0);

/* `product` */
INSERT INTO product VALUES ('AV-CB-01', '05', '亚马逊鹦鹉', '75 岁以上高龄的好伙伴', 50.00, 60.00, 100);
INSERT INTO product VALUES ('AV-SB-02', '05', '燕雀', '非常好的减压宠物', 45.00, 50.00, 98);
INSERT INTO product VALUES ('FI-FW-01', '01', '锦鲤', '来自日本的淡水鱼', 45.50, 45.50, 300);
INSERT INTO product VALUES ('FI-FW-02', '01', '金鱼', '来自中国的淡水鱼', 6.80, 6.80, 100);
INSERT INTO product VALUES ('FI-SW-01', '01', '天使鱼', '来自澳大利亚的海水鱼', 10.00, 10.00, 100);
INSERT INTO product VALUES ('FI-SW-02', '01', '虎鲨', '来自澳大利亚的海水鱼', 18.50, 20.00, 200);
INSERT INTO product VALUES ('FL-DLH-02', '04', '波斯猫', '友好的家居猫, 像公主一样高贵', 1000.00, 1200.00, 15);
INSERT INTO product VALUES ('FL-DSH-01', '04', '马恩岛猫', '灭鼠能手', 80.00, 100.00, 40);
INSERT INTO product VALUES ('K9-BD-01', '02', '牛头犬', '来自英格兰的友好的狗', 1350.00, 1500.00, 5);
INSERT INTO product VALUES ('K9-CW-01', '02', '吉娃娃犬', '很好的陪伴狗', 180.00, 200.00, 120);
INSERT INTO product VALUES ('K9-DL-01', '02', '斑点狗', '来自消防队的大狗', 3000.00, 3000.00, 1);
INSERT INTO product VALUES ('K9-PO-02', '02', '狮子犬', '来自法国的可爱的狗', 2000.00, 2000.00, 3);
INSERT INTO product VALUES ('K9-RT-01', '02', '金毛猎犬', '大家庭的狗', 300.00, 300.00, 200);
INSERT INTO product VALUES ('K9-RT-02', '02', '拉布拉多猎犬', '大猎狗', 800.00, 800.00, 30);
INSERT INTO product VALUES ('RP-LI-02', '03', '鬣蜥', '友好的绿色朋友', 60.00, 78.00, 40);
INSERT INTO product VALUES ('RP-SN-01', '03', '响尾蛇', '兼当看门狗', 200.00, 240.00, 10);

实战演练 schoolDB 库表数据

建库

create database schooldb
default character set gb2312
collate gb2312_chinese_ci;

建表：

CREATE TABLE class (
班级编号 char(6) NOT NULL PRIMARY KEY,
班级名称 varchar(20) NOT NULL,
院系 varchar(30) NOT NULL,
年级 int NULL ,
人数 int NULL
) ;

CREATE TABLE course (
课程号 char(6) NOT NULL PRIMARY KEY,
课程名 varchar(20) NOT NULL,
学分 int NOT NULL,
学时 int NOT NULL,
学期 char(2) NULL ,
前置课 char(6) NULL
);

CREATE TABLE score (
学号 char(10) NOT NULL,
课程号 char(6) NOT NULL,
成绩 float(5,2) NULL,
PRIMARY KEY (学号, 课程号)
);

CREATE TABLE student (
学号 char(10) NOT NULL PRIMARY KEY ,
姓名 varchar(20) NOT NULL,
性别 char(2) NOT NULL,
出生日期 date NULL,
地区 varchar(20) NULL ,
民族 varchar(10) NULL DEFAULT '汉',
班级编号 char(6) NULL
);

数据插入：
-- ----------------------------
-- Records of class
-- ----------------------------

INSERT INTO `class` VALUES ('AC1301', '会计13-1班', '会计学院', 2013, 35);
INSERT INTO `class` VALUES ('AC1302', '会计13-2班', '会计学院', 2013, 35);
INSERT INTO `class` VALUES ('CS1401', '计算机14-1班', '计算机学院', 2014, 35);
INSERT INTO `class` VALUES ('IS1301', '信息系统13-1班', '信息学院', 2013, NULL);
INSERT INTO `class` VALUES ('IS1401', '信息系统14-1班', '信息学院', NULL, 30);
-- ----------------------------
-- Records of crouse
-- ----------------------------
INSERT INTO `course` VALUES ('11003', '管理学', 3,32, 2,null);
INSERT INTO `course` VALUES ('11005', '会计学', 3,48, 2,null);
INSERT INTO `course` VALUES ('21001', '计算机基础', 3,48, 1,null);
INSERT INTO `course` VALUES ('21002', 'office高级应用', 3,48, 2,21001);
INSERT INTO `course` VALUES ('21004', '程序设计', 4,64, 2,21001);
INSERT INTO `course` VALUES ('21005', '数据库', 4,64,4,'21004');
INSERT INTO `course` VALUES ('21006', '操作系统', 4,64,5,'21001');
INSERT INTO `course` VALUES ('31001', '管理信息系统', 3,48,3,'21004');
INSERT INTO `course` VALUES ('31002', '信息系统_分析与设计', 2,32,4,'31001');
INSERT INTO `course` VALUES ('31005', '项目管理', 3,48,5,'31001');

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('2013110101', '11003', 90.00);
INSERT INTO `score` VALUES ('2013110101', '21001', 86.00);
INSERT INTO `score` VALUES ('2013110103', '11003', 89.00);
INSERT INTO `score` VALUES ('2013110103', '21001', 86.00);
INSERT INTO `score` VALUES ('2013110201', '11003', 78.00);
INSERT INTO `score` VALUES ('2013110201', '21001', 92.00);
INSERT INTO `score` VALUES ('2013110202', '11003', 82.00);
INSERT INTO `score` VALUES ('2013110202', '21001', 85.00);
INSERT INTO `score` VALUES ('2013310101', '21004', 83.00);
INSERT INTO `score` VALUES ('2013310101', '31002', 68.00);
INSERT INTO `score` VALUES ('2013310103', '21004', 80.00);
INSERT INTO `score` VALUES ('2013310103', '31002', 76.00);
INSERT INTO `score` VALUES ('2014210101', '21002', 93.00);
INSERT INTO `score` VALUES ('2014210101', '21004', 89.00);
INSERT INTO `score` VALUES ('2014210102', '21002', 95.00);
INSERT INTO `score` VALUES ('2014210102', '21004', 88.00);
INSERT INTO `score` VALUES ('2014310101', '21001', 79.00);
INSERT INTO `score` VALUES ('2014310101', '21004', 80.00);
INSERT INTO `score` VALUES ('2014310102', '21001', 91.00);
INSERT INTO `score` VALUES ('2014310102', '21004', 87.00);
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('2013110101', '张晓勇', '男', '1997-12-11', '山西', '汉', 'AC1301');
INSERT INTO `student` VALUES ('2013110103', '王一敏', '女', '1996-08-01', '河北', '汉', 'AC1301');
INSERT INTO `student` VALUES ('2013110201', '江山', '女', '1996-09-17', '内蒙古', '锡伯', 'AC1302');
INSERT INTO `student` VALUES ('2013110202', '李明', '男', '1996-01-14', '广西', '壮', 'AC1302');
INSERT INTO `student` VALUES ('2013310101', '黄菊', '女', '1995-09-30', '北京', '汉', 'IS1301');
INSERT INTO `student` VALUES ('2013310103', '吴昊', '男', '1995-11-18', '河北', '汉', 'IS1301');
INSERT INTO `student` VALUES ('2014210101', '刘涛', '男', '1997-04-03', '湖南', '侗', 'CS1401');
INSERT INTO `student` VALUES ('2014210102', '郭志坚', '男', '1997-02-21', '上海', '汉', 'CS1401');
INSERT INTO `student` VALUES ('2014310101', '王林', '男', '1996-10-09', '河南', '汉', 'IS1401');
INSERT INTO `student` VALUES ('2014310102', '李怡然', '女', '1996-12-31', '辽宁', '汉', 'IS1401');

综合实训 library库表结构

建库：

create database LibraryDB
default character set gb2312
collate gb2312_chinese_ci;

建表：

CREATE TABLE 读者表
(
读者编号 char(6) NOT NULL PRIMARY KEY,
姓名 char(10) NOT NULL,
类别号 char(2) NOT NULL,
单位 varchar(20) NULL,
有效性 char(10) NULL
);

CREATE TABLE 读者类型表
(
类别号 char(2) NOT NULL PRIMARY KEY ,
类名 char(10) NOT NULL,
可借数量 int NULL,
可借天数 int NULL
);

CREATE TABLE 库存表
(
条码 char(20) NOT NULL PRIMARY KEY,
书号 char(10) NOT NULL,
位置 varchar(20) NOT NULL,
库存状态 char(10) NULL
);

CREATE TABLE 借阅表
(
借阅号 int NOT NULL AUTO_INCREMENT PRIMARY KEY,
条码 char(20) NOT NULL,
读者编号 char(6) NOT NULL,
借阅日期 date NULL,
还书日期 date NULL,
借阅状态 char(6) NULL
);

CREATE TABLE 图书表
(
书号 char(10) NOT NULL PRIMARY KEY ,
书名 varchar(20) NOT NULL,
类别 char(10) NOT NULL,
作者 varchar(20) NOT NULL,
出版社 varchar(20) NOT NULL,
单价 float(5, 2) NULL,
数量 int NULL
);

-- ----------------------------
-- Records of 借阅表
-- ----------------------------

INSERT INTO `借阅表` VALUES (100001, '123413', '0001', '2020-11-05', NULL, '借阅');
INSERT INTO `借阅表` VALUES (100002, '223411', '0002', '2020-09-28', '2020-10-13', '已还');
INSERT INTO `借阅表` VALUES (100003, '321123', '1001', '2020-07-01', NULL, '过期');
INSERT INTO `借阅表` VALUES (100004, '321124', '2001', '2020-10-09', '2020-10-14', '已还');
INSERT INTO `借阅表` VALUES (100005, '321124', '0001', '2020-10-15', NULL, '借阅');
INSERT INTO `借阅表` VALUES (100006, '223411', '2001', '2020-10-16', NULL, '借阅');
INSERT INTO `借阅表` VALUES (100007, '411111', '1002', '2020-09-01', '2020-09-24', '已还');
INSERT INTO `借阅表` VALUES (100008, '411111', '0001', '2020-09-25', NULL, '借阅');
INSERT INTO `借阅表` VALUES (100009, '411111', '1001', '2020-10-08', NULL, '借阅');

-- ----------------------------
-- Records of 图书表
-- ----------------------------

INSERT INTO `图书表` VALUES ('A0120', '庄子', '文学', '庄周', '吉林大学出版社', 18.50, 5);
INSERT INTO `图书表` VALUES ('A0134', '唐诗三百首', '文学', '李平', '安徽科学出版社', 28.00, 10);
INSERT INTO `图书表` VALUES ('B1101', '西方经济学史', '财经', '莫竹芩', '海南出版社', 39.80, 8);
INSERT INTO `图书表` VALUES ('B2213', '商业博弈', '财经', '孔英', '北京大学出版社', 39.00, 15);
INSERT INTO `图书表` VALUES ('C1269', '数据结构', '计算机', '李刚', '高等教育出版社', 29.00, 20);
INSERT INTO `图书表` VALUES ('C3121', '品牌策划与推广', '计算机', '张晓红', '人民邮电出版社', 42.00, 6);
INSERT INTO `图书表` VALUES ('C3182', 'C语言程序设计', '计算机', '李学刚', '高等教育出版社', 36.80, 11);
INSERT INTO `图书表` VALUES ('C3256', 'MySQL数据库', '计算机', '孙季红', '电子工业出版社', 29.00, 9);

-- ----------------------------
-- Records of 库存表
-- ----------------------------

INSERT INTO `库存表` VALUES ('123412', 'A0120', '1-A-56', '在馆');
INSERT INTO `库存表` VALUES ('123413', 'A0120', '1-A-57', '借出');
INSERT INTO `库存表` VALUES ('223410', 'A0134', '2-B-01', '在馆');
INSERT INTO `库存表` VALUES ('223411', 'A0134', '2-B-02', '借出');
INSERT INTO `库存表` VALUES ('311231', 'B1101', '2-C-23', '在馆');
INSERT INTO `库存表` VALUES ('321123', 'C1269', '3-A-12', '丢失');
INSERT INTO `库存表` VALUES ('321124', 'C1269', '3-A-13', '借出');
INSERT INTO `库存表` VALUES ('411111', 'C3256', '3-B-01', '借出');
INSERT INTO `库存表` VALUES ('411112', 'C3256', '3-B-02', '借出');
INSERT INTO `库存表` VALUES ('411113', 'C3256', '3-B-03', '在馆');

-- ----------------------------
-- Records of 读者类型表
-- ----------------------------

INSERT INTO `读者类型表` VALUES ('1', '学生', 10, 30);
INSERT INTO `读者类型表` VALUES ('2', '教师', 20, 60);
INSERT INTO `读者类型表` VALUES ('3', '职工', 15, 20);

-- ----------------------------
-- Records of 读者表
-- ----------------------------

INSERT INTO `读者表` VALUES ('0001', '张小东', '1', '软件学院', '有效');
INSERT INTO `读者表` VALUES ('0002', '苏明', '1', '财经学院', '有效');
INSERT INTO `读者表` VALUES ('1001', '梁小红', '2', '软件学院', '有效');
INSERT INTO `读者表` VALUES ('1002', '赵明敏', '2', '传媒学院', '有效');
INSERT INTO `读者表` VALUES ('2001', '李丰年', '3', '计财处', '有效');

实战演练 schooldb 3-9单元

/*第三章习题*/
/*二*/
/*1. */
create database schooldb;
/*2.*/
Use schooldb;
CREATE TABLE class (
班级编号 char(6) NOT NULL PRIMARY KEY,
班级名称 varchar(20) NOT NULL,
院系 varchar(30) NOT NULL,
年级 int NULL ,
人数 int NULL
) ;
CREATE TABLE course (
课程号 char(6) NOT NULL PRIMARY KEY,
课程名 varchar(20) NOT NULL,
学分 int NOT NULL,
学时 int NOT NULL,
学期 char(2) NULL ,
前置课 char(6) NULL
);
CREATE TABLE score (
学号 char(10) NOT NULL,
课程号 char(6) NOT NULL,
成绩 float(5,2) NULL,
PRIMARY KEY (学号, 课程号)
);
CREATE TABLE student (
学号 char(10) NOT NULL PRIMARY KEY ,
姓名 varchar(20) NOT NULL,
性别 char(2) NOT NULL,
出生日期 date NULL,
地区 varchar(20) NULL ,
民族 varchar(10) NULL DEFAULT '汉',
班级编号 char(6) NULL
);
/*3.*/
/*(1)*/
ALTER TABLE student
ADD FOREIGN KEY (班级编号) REFERENCES class (班级编号);
/*(2)*/
ALTER TABLE course
ADD FOREIGN KEY (前置课) REFERENCES course (课程号)
ON DELETE SET NULL ON UPDATE CASCADE;
/*(3)*/
ALTER TABLE score
ADD FOREIGN KEY (学号) REFERENCES student (学号)
ON DELETE RESTRICT ON UPDATE RESTRICT;
/*(4)*/
ALTER TABLE score
ADD FOREIGN KEY (课程号) REFERENCES course (课程号)
ON DELETE CASCADE ON UPDATE CASCADE;
/*4.*/
alter table score
add check(成绩>=0 and 成绩<=100);
/*5.*/
alter table score
add check(成绩>=0 and 成绩<=100);

/*第四章：习题*/

/*二、*/
/*1. */
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('AC1301', '会计13-1班', '会计学院', 2013, 35);
INSERT INTO `class` VALUES ('AC1302', '会计13-2班', '会计学院', 2013, 35);
INSERT INTO `class` VALUES ('CS1401', '计算机14-1班', '计算机学院', 2014, 35);
INSERT INTO `class` VALUES ('IS1301', '信息系统13-1班', '信息学院', 2013, NULL);
INSERT INTO `class` VALUES ('IS1401', '信息系统14-1班', '信息学院', NULL, 30);
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('AC1301', '会计13-1班', '会计学院', 2013, 35);
INSERT INTO `class` VALUES ('AC1302', '会计13-2班', '会计学院', 2013, 35);
INSERT INTO `class` VALUES ('CS1401', '计算机14-1班', '计算机学院', 2014, 35);
INSERT INTO `class` VALUES ('IS1301', '信息系统13-1班', '信息学院', 2013, NULL);
INSERT INTO `class` VALUES ('IS1401', '信息系统14-1班', '信息学院', NULL, 30);
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('2013110101', '11003', 90.00);
INSERT INTO `score` VALUES ('2013110101', '21001', 86.00);
INSERT INTO `score` VALUES ('2013110103', '11003', 89.00);
INSERT INTO `score` VALUES ('2013110103', '21001', 86.00);
INSERT INTO `score` VALUES ('2013110201', '11003', 78.00);
INSERT INTO `score` VALUES ('2013110201', '21001', 92.00);
INSERT INTO `score` VALUES ('2013110202', '11003', 82.00);
INSERT INTO `score` VALUES ('2013110202', '21001', 85.00);
INSERT INTO `score` VALUES ('2013310101', '21004', 83.00);
INSERT INTO `score` VALUES ('2013310101', '31002', 68.00);
INSERT INTO `score` VALUES ('2013310103', '21004', 80.00);
INSERT INTO `score` VALUES ('2013310103', '31002', 76.00);
INSERT INTO `score` VALUES ('2014210101', '21002', 93.00);
INSERT INTO `score` VALUES ('2014210101', '21004', 89.00);
INSERT INTO `score` VALUES ('2014210102', '21002', 95.00);
INSERT INTO `score` VALUES ('2014210102', '21004', 88.00);
INSERT INTO `score` VALUES ('2014310101', '21001', 79.00);
INSERT INTO `score` VALUES ('2014310101', '21004', 80.00);
INSERT INTO `score` VALUES ('2014310102', '21001', 91.00);
INSERT INTO `score` VALUES ('2014310102', '21004', 87.00);
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('2013110101', '张晓勇', '男', '1997-12-11', '山西', '汉', 'AC1301');
INSERT INTO `student` VALUES ('2013110103', '王一敏', '女', '0000-00-00', '河北', '汉', 'AC1301');
INSERT INTO `student` VALUES ('2013110201', '江山', '女', '1996-09-17', '内蒙古', '锡伯', 'AC1302');
INSERT INTO `student` VALUES ('2013110202', '李明', '男', '1996-01-14', '广西', '壮', 'AC1302');
INSERT INTO `student` VALUES ('2013310101', '黄菊', '女', '1995-09-30', '北京', '汉', 'IS1301');
INSERT INTO `student` VALUES ('2013310103', '吴昊', '男', '1995-11-18', '河北', '汉', 'IS1301');
INSERT INTO `student` VALUES ('2014210101', '刘涛', '男', '1997-04-03', '湖南', '侗', 'CS1401');
INSERT INTO `student` VALUES ('2014210102', '郭志坚', '男', '1997-02-21', '上海', '汉', 'CS1401');
INSERT INTO `student` VALUES ('2014310101', '王林', '男', '1996-10-09', '河南', '汉', 'IS1401');
INSERT INTO `student` VALUES ('2014310102', '李怡然', '女', '1996-12-31', '辽宁', '汉', 'IS1401');
/*2.*/
/*(1).*/
insert into student values('502001', '王晓林', '男', '2000-02-10', '广东' , '汉','IS2020');
/*(2).*/
insert into student set 学号='500102',姓名='林丽',性别='女';
/*(3)*/
update student set 地区=concat(地区,'（省或市）');
/*(4）*/
update student set 出生日期='1998-02-10',班级编号='AC1302' where 姓名='王一敏';
/*(5) */
delete from student where 出生日期<'1997-01-01';

单元五

1.
（1）
select 姓名 as name,(year(now())-year(出生日期)) as age from student;

（2）
select *,
    case
     when 成绩>=90 then '优'
    when 成绩>=75 and 成绩<90 then '良'
    when 成绩>=60 and 成绩<75 then '及格'
    else '不及格'
    end as 成绩档
from score;

（3）
select 课程名,学分 from course where 学时>=48;

（4）
select 课程名,学期 from course where 前置课 is null;

（5）
select * from student where 姓名 like '王__';

2.
（1）
select student.学号,姓名,score.课程号,成绩 from student,course,score where student.学号=score.学号 and course.课程号=score.课程号;

（2）
select 学号,姓名,班级名称 from student,class where student.班级编号=class.班级编号 and 院系 ='会计学院';

（3）
select score.学号,姓名,成绩 from student,score where student.学号=score.学号 and 成绩>90;

（4）
select course.课程号,课程名,学号,成绩 from course left join score on course.课程号=score.课程号;

（5）
select 学号,姓名 from student where 班级编号 in (select 班级编号 from class where 班级名称='计算机14-1班');

3.
（1）
select 性别,count(*) from student group by 性别;

（2）
select 学号,count(课程号),avg(成绩),max(成绩) from score group by 学号;

（3）
select 学号,count(课程号),avg(成绩),max(成绩) from score group by 学号 having avg(成绩)>=80;

（4）
select 性别,民族,count(*) from student group by 性别,民族 order by count(*) ;

单元六

1.
create view v_score as
select student.学号,姓名,民族,课程号,成绩
from student,score
where student.学号=score.学号;

2.
select 学号,姓名,课程号,成绩
from v_score
where 民族!='汉';

3.
create view v_avg(num,score_avg) as
select 学号,avg(成绩) from score group by 学号;

4.
select * from v_avg where score_avg>80;

5.
create view v_student as
select * from student where 民族='汉' with check option;
insert into v_student values('2020410001','李牧','男','1998-10-21','广东','汉',NULL);

6.
delete from v_student where 性别='女';

单元七

create index I_kc
on course(课程名(3) ASC);

2.
alter table class
add index I_cx(院系,年级);

3.
alter table student
add unique (姓名);

4.
alter table class
add primary key (课程号);

5.
alter table score
add primary key(学号,课程号),
add index (成绩);

6.
alter table course drop primary key ;

7.
alter table course
partition by Key(学分) partitions 4;

单元八

1.
Set @x=12.54;Set @y=-10.63456;
（1）Select floor(@x),floor(@y),round(@x),round(@y);
(2) SELECT TRUNCATE(@y, 2),TRUNCATE(@y, 4);

2.
SET @s1='ABCDEFG';SET@s2=' XYZ ';
    (1)SELECT LEFT(@s1, 3) ,right(@s1,3);
    (2) SELECT LTRIM(@s2),RTRIM(@s2),TRIM(@s2);
    (3)    select substring(@s1,3,4);
    (4)    SELECT STRCMP(@s1, @s2);

select now(),CURTIME(),CURDATE(),YEAR(now()),MONTHNAME(now()),
DAYNAME(now()),DATE_ADD(now(), INTERVAL -10 DAY);

4.
(1)

DELIMITER $$
    CREATE PROCEDURE show_jj
            ( OUT season VARCHAR(8) )
    BEGIN
        CASE
            WHEN month(now()) in (1,2,3) THEN SET season ='春季';
            WHEN month(now()) in (4,5,6) THEN SET season ='夏季';
            WHEN month(now()) in (7,8,9) THEN SET season ='秋季';
            WHEN month(now()) in (10,11,12) THEN SET season ='冬季';
        END CASE;
    END$$
DELIMITER ;
call show_jj(@a);
select @a;

（2）

DELIMITER $$
create procedure sum_n(in n int,out rs int)
begin
Set rs=0;
WHILE n > 0 DO
Set rs=rs+n;
SET n = n1;
END WHILE;
End $$
DELIMITER ;
call sum_n(10,@a);
select @a;
call sum_n(100,@a);
select @a;

(3)

delimiter $$
create procedure kc_xg(in xq int)
    begin
    declare kch char(6);
    declare xs int;
    declare state char(10) default 'ok';
    declare xg_c cursor for select 课程号,学时 from course where 学期=xq;
    declare continue handler for 1329 set state='error';
    open xg_c;
    repeat
      fetch xg_c into kch,xs;
set xs=xs+5;
      if(xs>65) then set xs=65;
      end if;
      If state='ok' then update course set 学时=xs where 课程号=kch;
      end if;
    until state='error'
    end repeat;
    close xg_c;
    end $$
delimiter;
call kc_xg(2);

Create function F_kc( kch char(6))
Returns char(20)
DETERMINISTIC
Return (select 课程名 from course where 课程号=kch);
select F_kc('11003');

6.
（1）

DELIMITER $$
CREATE TRIGGER c_dl AFTER DELETE
ON course FOR EACH ROW
BEGIN
DELETE from score WHERE 课程号=OLD.课程号;
END$$
DELIMITER ;

（2）

DELIMITER $$
CREATE TRIGGER s_ins AFTER INSERT
ON student FOR EACH ROW
BEGIN
UPDATE class set 班级人数=班级人数+1 WHERE 编辑编号=NEW.班级编号;
END$$
DELIMITER ;

（3）

DELIMITER $$
CREATE TRIGGER s_up AFTER UPDATE
ON student FOR EACH ROW
BEGIN
If new.民族!='汉' then
UPDATE score set 成绩=成绩+1 WHERE 学号=NEW.学号;
End if;
END$$
DELIMITER ;

7
（1）

CREATE EVENT event_up ON SCHEDULE EVERY 1 MINUTE
STARTS CURDATE() + INTERVAL 1 MINUTE
DO
UPDATE course set 学分=学分+1 where 课程号= '11003';

（2）

Select @@EVENT_SCHEDULER;
SET GLOBAL EVENT_SCHEDULER=1;

(3)

DROP EVENT event_up;

单元九

CREATE USER
king1@localhost IDENTIFIED BY 'ken1',
king2@localhost IDENTIFIED BY 'ken2';

2.
GRANT SELECT
ON schooldb.student
TO king1@localhost;

3.
GRANT SELECT,update
ON schooldb.class
TO king2@localhost;

4.
GRANT SELECT
ON schooldb.*
TO king1@localhost;

5.
GRANT ALL
ON schooldb.*
TO king1@localhost;

6.
REVOKE DELETE
ON schooldb.class
FROM king2@localhost;

7.
SELECT * FROM course INTO OUTFILE 'D:/c1.txt'
    FIELDS TERMINATED BY ','    OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '?';
LOAD DATA INFILE 'D:/c1.txt'    INTO TABLE backup_c FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '?';

综合实训librarydb 3-9单元

单元三

/*实训3代码*/
/*1．使用命令行方式完成以下操作*/
/*(1)*/
create database LibraryDB;
create database MyTest;
/*(2)*/
use librarydb;
/*(3)*/
CREATE TABLE 读者表 (
读者编号 char(6) NOT NULL PRIMARY KEY,
姓名 char(10) NOT NULL,
类别号 char(2) NOT NULL,
单位 varchar(20) NULL,
有效性 char(10) NULL
);
CREATE TABLE 读者类型表 (
类别号 char(2) NOT NULL PRIMARY KEY ,
类名 char(10) NOT NULL,
可借数量 int NULL,
可借天数 int NULL
);
CREATE TABLE 库存表 (
条码 char(20) NOT NULL PRIMARY KEY,
书号 char(10) NOT NULL,
位置 varchar(20) NOT NULL,
库存状态 char(10) NULL
);
CREATE TABLE 借阅表 (
借阅号 int NOT NULL AUTO_INCREMENT PRIMARY KEY,
条码 char(20) NOT NULL,
读者编号 char(6) NOT NULL,
借阅日期 date NULL,
还书日期 date NULL,
借阅状态 char(6) NULL
);
CREATE TABLE 图书表 (
书号 char(10) NOT NULL PRIMARY KEY ,
书名 varchar(20) NOT NULL,
类别 char(10) NOT NULL,
作者 varchar(20) NOT NULL,
出版社 varchar(20) NOT NULL,
单价 float(5, 2) NULL,
数量 int NULL
);
/*(4)*/
show databases;
/*(5)*/
use librarydb;
show tables;
/*(6)*/
drop database MyTest;
/*3．建立数据完整性约束*/
/*(1)*/
ALTER TABLE 读者表
ADD PRIMARY KEY (读者编号);
/*(2)*/
ALTER TABLE 读者表
ADD FOREIGN KEY (类别号) REFERENCES 读者类型表 (类别号)
ON DELETE CASCADE
ON UPDATE CASCADE;
/*(3)*/
ALTER TABLE 借阅表
ADD FOREIGN KEY (读者编号) REFERENCES 读者表 (读者编号)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
/*(4)*/
ALTER TABLE 借阅表
ADD FOREIGN KEY (条码) REFERENCES 库存表 (条码)
ON DELETE CASCADE
ON UPDATE CASCADE;
/*(5)*/
alter table 读者类型表
add check(可借数量>=0 and 可借数量<=30);
/*(6)*/
alter table 库存表
add check(库存状态 in ( '在馆','借出','丢失'));

单元四

/*实训4*/
/*1*/
-- ----------------------------
-- Records of 借阅表
-- ----------------------------
INSERT INTO `借阅表` VALUES (100001, '123413', '0001', '2020-11-05', NULL, '借阅');
INSERT INTO `借阅表` VALUES (100002, '223411', '0002', '2020-09-28', '2020-10-13', '已还');
INSERT INTO `借阅表` VALUES (100003, '321123', '1001', '2020-07-01', NULL, '过期');
INSERT INTO `借阅表` VALUES (100004, '321124', '2001', '2020-10-09', '2020-10-14', '已还');
INSERT INTO `借阅表` VALUES (100005, '321124', '0001', '2020-10-15', NULL, '借阅');
INSERT INTO `借阅表` VALUES (100006, '223411', '2001', '2020-10-16', NULL, '借阅');
INSERT INTO `借阅表` VALUES (100007, '411111', '1002', '2020-09-01', '2020-09-24', '已还');
INSERT INTO `借阅表` VALUES (100008, '411111', '0001', '2020-09-25', NULL, '借阅');
INSERT INTO `借阅表` VALUES (100009, '411111', '1001', '2020-10-08', NULL, '借阅');
-- ----------------------------
-- Records of 图书表
-- ----------------------------
INSERT INTO `图书表` VALUES ('A0120', '庄子', '文学', '庄周', '吉林大学出版社', 18.50, 5);
INSERT INTO `图书表` VALUES ('A0134', '唐诗三百首', '文学', '李平', '安徽科学出版社', 28.00, 10);
INSERT INTO `图书表` VALUES ('B1101', '西方经济学史', '财经', '莫竹芩', '海南出版社', 39.80, 8);
INSERT INTO `图书表` VALUES ('B2213', '商业博弈', '财经', '孔英', '北京大学出版社', 39.00, 15);
INSERT INTO `图书表` VALUES ('C1269', '数据结构', '计算机', '李刚', '高等教育出版社', 29.00, 20);
INSERT INTO `图书表` VALUES ('C3121', '品牌策划与推广', '计算机', '张晓红', '人民邮电出版社', 42.00, 6);
INSERT INTO `图书表` VALUES ('C3182', 'C语言程序设计', '计算机', '李学刚', '高等教育出版社', 36.80, 11);
INSERT INTO `图书表` VALUES ('C3256', 'MySQL数据库', '计算机', '孙季红', '电子工业出版社', 29.00, 9);
-- ----------------------------
-- Records of 库存表
-- ----------------------------
INSERT INTO `库存表` VALUES ('123412', 'A0120', '1-A-56', '在馆');
INSERT INTO `库存表` VALUES ('123413', 'A0120', '1-A-57', '借出');
INSERT INTO `库存表` VALUES ('223410', 'A0134', '2-B-01', '在馆');
INSERT INTO `库存表` VALUES ('223411', 'A0134', '2-B-02', '借出');
INSERT INTO `库存表` VALUES ('311231', 'B1101', '2-C-23', '在馆');
INSERT INTO `库存表` VALUES ('321123', 'C1269', '3-A-12', '丢失');
INSERT INTO `库存表` VALUES ('321124', 'C1269', '3-A-13', '借出');
INSERT INTO `库存表` VALUES ('411111', 'C3256', '3-B-01', '借出');
INSERT INTO `库存表` VALUES ('411112', 'C3256', '3-B-02', '借出');
INSERT INTO `库存表` VALUES ('411113', 'C3256', '3-B-03', '在馆');
-- ----------------------------
-- Records of 读者类型表
-- ----------------------------
INSERT INTO `读者类型表` VALUES ('1', '学生', 10, 30);
INSERT INTO `读者类型表` VALUES ('2', '教师', 20, 60);
INSERT INTO `读者类型表` VALUES ('3', '职工', 15, 20);
-- ----------------------------
-- Records of 读者表
-- ----------------------------
INSERT INTO `读者表` VALUES ('0001', '张小东', '1', '软件学院', '有效');
INSERT INTO `读者表` VALUES ('0002', '苏明', '1', '财经学院', '有效');
INSERT INTO `读者表` VALUES ('1001', '梁小红', '2', '软件学院', '有效');
INSERT INTO `读者表` VALUES ('1002', '赵明敏', '2', '传媒学院', '有效');
INSERT INTO `读者表` VALUES ('2001', '李丰年', '3', '计财处', '有效');

/*2*/
/*(1)*/
insert into 借阅表
set 条码='223410',
借阅日期=curdate(),
读者编号='2001',
借阅状态='借阅';
update 库存表 set 库存状态='借出' where 条码='223410';
/*(2)*/
insert into 图书表
value('C3325','计算机基础','计算机','陈焕东','高等教育出版社',38.6,2);
insert into 库存表
values('331122','C3325','3-B-01','在馆'),
('331132','C3325','3-B-02','在馆');
/*(3)*/
delete from 读者表,借阅表
Using(读者表,借阅表)
where 姓名='苏明' and
读者表.读者编号=借阅表.读者编号;

单元五

/*实训5*/
/*1．单表查询*/
/*(1)*/
select distinct 书号,库存状态 from 库存表;
/*(2)*/
select 姓名 as name ,单位 as college from 读者表;
/*(3)*/
select 书名,数量*单价 as 金额 from 图书表;
/*(4)*/
select 条码,库存状态,
    case
    when 库存状态='在馆' then '1'
    when 库存状态='借出' then '0'
    when 库存状态='丢失' then '-1'
    end as 库存状态1
     from 库存表;

/*2．条件查询*/
/*(1)*/
select 书名,数量,出版社 from 图书表 where 数量>=10;
/*(2)*/
select * from 库存表 where 库存状态='借出' and 位置 like '%A%';
/*(3)*/
select * from 图书表 where (类别='财经' or 类别='文学') and 数量>5;
/*(4)*/

select * from 借阅表 where 还书日期 is null;
/*3．多表查询*/
/*(1)*/
select * from 读者表,借阅表 where 读者表.读者编号=借阅表.读者编号 and 姓名='张小东';
/*(2)*/
select 书号,借阅表.条码 from 库存表,借阅表 where 库存表.条码=借阅表.条码 and 借阅状态='借阅';
/*(3)*/
select 姓名,单位,可借天数,可借数量 from 读者表,读者类型表 where 读者表.类别号=读者类型表.类别号;
/*(4)*/
select 姓名,书名,借阅日期,借阅状态 from 借阅表,读者表,图书表,库存表 where 库存表.书号=图书表.书号 and 借阅表.读者编号=读者表.读者编号 and 库存表.条码=借阅表.条码;
/*(5)*/
select 库存表.条码,位置,读者编号 from 库存表 left join 借阅表 on 库存表.条码=借阅表.条码;

/*4．分类汇总与排序*/
/*(1)*/
select 单位,count(*) from 读者表 group by 单位;
/*(2)*/
select 单位,count(*) from 读者表 group by 单位 having count(*)>=2;
/*(3)*/
select 出版社,avg(单价) ,sum(数量*单价)from 图书表 group by 出版社 ;
/*(4)*/
select 读者编号,条码,count(*) from 借阅表 group by 读者编号,条码 with rollup ;
/*(5)*/
select * from 图书表 order by 数量 desc;
/*(6)*/
select * from 借阅表 order by 借阅状态,借阅日期;
/*(7)*/
select 类别号,单位,count(*) from 读者表,借阅表 where 读者表.读者编号=借阅表.读者编号 group by 类别号 ,单位 order by count(*) desc;

单元六

/*实训6*/
/*1*/

create view L_view1 as
    select 读者编号,姓名,类名,可借天数,可借数量
    from 读者表,读者类型表
    where 读者表.类别号=读者类型表.类别号;
/*2*/
select 读者编号,姓名,类名,可借天数,可借数量
    from l_view1
    where 类名='学生';
/*3*/
create view L_view2 as
    select 借阅号,书号,姓名,借阅日期,还书日期
    from 读者表,借阅表,库存表
    where 读者表.读者编号=借阅表.读者编号 and 库存表.条码=借阅表.条码;
/*4*/
select 借阅号,书号,姓名,借阅日期,还书日期
    from l_view2
    where 还书日期 is null;
/*5*/
create view L_view3 as
     select * from 借阅表
    where 借阅状态='借阅' or 借阅状态='已还'
    WITH CHECK OPTION;
/*6*/
insert into l_view3 values(100010,'411112','2001','2020-10-18',Null,'借阅');
/*7*/
update l_view2 set 借阅日期=curdate() where 借阅号=100001;
/*8*/
delete from l_view3 where 还书日期 is not Null;
/*9*/
drop view l_view2,l_view3;

单元七

/*实训7*/
/*1*/
/*(1)*/
CREATE INDEX I_bm ON 读者表(单位 DESC);
/*(2)*/
CREATE INDEX I_tr ON 借阅表(条码,读者编号);
/*(3)*/
CREATE UNIQUE INDEX U_wz ON 库存表(位置);
/*2*/
/*(1)*/
alter table 图书表
    add unique index (书名),
    add index(作者,出版社);
/*(2)*/
alter table 读者类型表
    add primary key (类别号);
/*3*/
create table cpk(
产品编号 char(6) not null,
产品名称 char(20) not null,
单价 float(5,2),
库存量 int,
primary key(产品编号),
index(库存量,单价));
/*4*/
show index from 图书表;
/*5*/
alter table 借阅表
    partition by hash(借阅号) partitions 3;

单元八

/*实训8*/
/*1*/
/*（1）*/
delimiter $$
create procedure tj_b(in c_sh char(20))
    begin
    declare sl int;
    select count(*) into sl from 库存表 group by 书号 having 书号=c_sh;
    update 图书表 set 数量=sl where 书号=c_sh;
    end $$
delimiter;

call tj_b('A0120');

/*（2）*/
delimiter $$
create procedure jy_b(in c_dz char(10),out c_qk varchar(100))
    begin
    declare tm char(20);
    declare rq1,rq2 date;
    declare ts int;
    declare state char(10) default 'ok';
    declare zt_c cursor for select 条码,借阅日期,还书日期 from 借阅表 where 读者编号=c_dz;
    declare continue handler for 1329 set state='error';
    set c_qk='';
    open zt_c;
    repeat
      fetch zt_c into tm,rq1,rq2;
      if(rq2 is NULL) then set ts=TIMESTAMPDIFF(day,rq1,curdate());
      else
      set ts=TIMESTAMPDIFF(day,rq1,rq2);
      end if;
   If state='ok' then
      begin
      if(ts<=15) then set c_qk= CONCAT_WS('#',c_qk,tm,'正常');
      end if;
     if(ts>15 and ts<=30) then set c_qk=CONCAT_WS('#',c_qk,tm,'通知还书');
      end if;
      if(ts>30) then set c_qk=CONCAT_WS('#',c_qk,tm,'逾期');
      end if;
      end ;
    end if;
    until state='error'
    end repeat;
    close zt_c;
    end $$
delimiter;

call jy_b('0001',@a);
select @a;
/*2*/
/*（1）*/
CREATE FUNCTION SP_NUM()
RETURNS float(7,2)
DETERMINISTIC
RETURN ( SELECT sum(单价*数量) FROM 图书表);

select sp_num();
/*（2）*/
DELIMITER $$
CREATE FUNCTION SP_dz (xm char(10))
    RETURNS int
DETERMINISTIC
BEGIN
    DECLARE lp char(2);DECLARE ts int;
    SELECT 读者类型表.类别号, 可借天数 INTO lp,ts
       FROM 读者类型表,读者表
       WHERE 读者类型表.类别号=读者表.类别号 and 姓名=xm;
    IF lp='1' THEN RETURN ts;
    ELSE
        RETURN -1;
    END IF;
END $$
DELIMITER ;
/*3*/
/*（1）*/
DELIMITER $$
CREATE TRIGGER dz_dl AFTER DELETE
    ON 读者表 FOR EACH ROW
BEGIN

        DELETE from 借阅表 WHERE 读者编号=OLD.读者编号;

END$$
DELIMITER ;

/*（2）*/
DELIMITER $$
CREATE TRIGGER jy_ins AFTER INSERT
    ON 借阅表 FOR EACH ROW
BEGIN

        UPDATE 库存表 set 库存状态='借出' WHERE 条码=NEW.条码;

END$$
DELIMITER ;
/*（3）*/
DELIMITER $$
CREATE TRIGGER jy_up AFTER UPDATE
    ON 借阅表 FOR EACH ROW
BEGIN

        UPDATE 库存表 set 库存状态='在馆' WHERE 条码=NEW.条码;

END$$
DELIMITER ;
/*4*/
/*（1）*/
CREATE EVENT event_up ON SCHEDULE EVERY 1 MINUTE
STARTS CURDATE() + INTERVAL 1 MINUTE
DO
UPDATE 图书表 set 数量=数量+1 where 书名= 'MySQL数据库';

/*（2）*/
select 数量,now() from 图书表 where 书名= 'MySQL数据库';
/*(3)*/
ALTER EVENT event_up DISABLE;

单元九

/*第9章*/
/*1*/
/*(1)*/
CREATE USER
    user1@localhost IDENTIFIED BY '123',
    user2@localhost IDENTIFIED BY '123',
    user3@localhost IDENTIFIED BY '123';
/*(2)*/
SET PASSWORD FOR user3@localhost= '123456';
/*(3)*/
DROP USER user3@localhost;
/*(4)*/
DROP USER user3@localhost;
/*(5)*/

USE librarydb;
GRANT SELECT
ON 读者表
TO user1@localhost;

/*(6)*/
USE librarydb;
GRANT SELECT,update,delete
    ON 借阅表
        TO user1@localhost;
/*(7)*/
GRANT ALL
    ON librarydb.*
        TO user1@localhost;
/*(8)*/
GRANT SELECT
    ON Bookstore.sell
    TO user2@localhost
    WITH GRANT OPTION;
/*(9)*/
REVOKE SELECT
    ON librarydb.读者表
        FROM user1@localhost;
/*2*/
/*(1)*/
SELECT * FROM 库存表 INTO OUTFILE 'D:/kc.txt'
    FIELDS TERMINATED BY ','    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '?';
/*(2)*/
CREATE TABLE c_kc LIKE 库存表 ;
LOAD DATA INFILE 'D:/kc.txt'    INTO TABLE c_kc
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '?';

菜鸟xy..

关注

9
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
mysql 课本实训操作

INSERT INTO `student` VALUES ('2013110201', '江山', '女', '1996-09-17', '内蒙古', '锡伯', 'AC1302');INSERT INTO `student` VALUES ('2013110201', '江山', '女', '1996-09-17', '内蒙古', '锡伯', 'AC1302');
复制链接

扫一扫