博客备用数据库
Java EE 企业级 应用开发教程(Spring+SpringMVC+MyBatis)
/*
SQLyog Ultimate v8.32
MySQL - 5.5.40 : Database - boot_crm
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`boot_crm` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `boot_crm`;
/*Table structure for table `base_dict` */
DROP TABLE IF EXISTS `base_dict`;
CREATE TABLE `base_dict` (
`dict_id` varchar(32) NOT NULL COMMENT '数据字典id(主键)',
`dict_type_code` varchar(10) NOT NULL COMMENT '数据字典类别代码',
`dict_type_name` varchar(50) NOT NULL COMMENT '数据字典类别名称',
`dict_item_name` varchar(50) NOT NULL COMMENT '数据字典项目名称',
`dict_item_code` varchar(10) DEFAULT NULL COMMENT '数据字典项目代码(可为空)',
`dict_sort` int(10) DEFAULT NULL COMMENT '排序字段',
`dict_enable` char(1) NOT NULL COMMENT '1:使用 0:停用',
`dict_memo` varchar(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`dict_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `base_dict` */
insert into `base_dict`(`dict_id`,`dict_type_code`,`dict_type_name`,`dict_item_name`,`dict_item_code`,`dict_sort`,`dict_enable`,`dict_memo`) values ('1','001','客户行业','教育培训 ',NULL,1,'1',NULL),('10','003','公司性质','民企',NULL,3,'1',NULL),('12','004','年营业额','1-10万',NULL,1,'1',NULL),('13','004','年营业额','10-20万',NULL,2,'1',NULL),('14','004','年营业额','20-50万',NULL,3,'1',NULL),('15','004','年营业额','50-100万',NULL,4,'1',NULL),('16','004','年营业额','100-500万',NULL,5,'1',NULL),('17','004','年营业额','500-1000万',NULL,6,'1',NULL),('18','005','客户状态','基础客户',NULL,1,'1',NULL),('19','005','客户状态','潜在客户',NULL,2,'1',NULL),('2','001','客户行业','电子商务',NULL,2,'1',NULL),('20','005','客户状态','成功客户',NULL,3,'1',NULL),('21','005','客户状态','无效客户',NULL,4,'1',NULL),('22','006','客户级别','普通客户',NULL,1,'1',NULL),('23','006','客户级别','VIP客户',NULL,2,'1',NULL),('24','007','商机状态','意向客户',NULL,1,'1',NULL),('25','007','商机状态','初步沟通',NULL,2,'1',NULL),('26','007','商机状态','深度沟通',NULL,3,'1',NULL),('27','007','商机状态','签订合同',NULL,4,'1',NULL),('3','001','客户行业','对外贸易',NULL,3,'1',NULL),('30','008','商机类型','新业务',NULL,1,'1',NULL),('31','008','商机类型','现有业务',NULL,2,'1',NULL),('32','009','商机来源','电话营销',NULL,1,'1',NULL),('33','009','商机来源','网络营销',NULL,2,'1',NULL),('34','009','商机来源','推广活动',NULL,3,'1',NULL),('4','001','客户行业','酒店旅游',NULL,4,'1',NULL),('5','001','客户行业','房地产',NULL,5,'1',NULL),('6','002','客户信息来源','电话营销',NULL,1,'1',NULL),('7','002','客户信息来源','网络营销',NULL,2,'1',NULL),('8','003','公司性质','合资',NULL,1,'1',NULL),('9','003','公司性质','国企',NULL,2,'1',NULL);
/*Table structure for table `customer` */
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`cust_id` int(32) NOT NULL AUTO_INCREMENT COMMENT '客户编号(主键)',
`cust_name` varchar(50) NOT NULL COMMENT '客户名称',
`cust_user_id` int(32) DEFAULT NULL COMMENT '负责人id',
`cust_create_id` int(32) DEFAULT NULL COMMENT '创建人id',
`cust_source` varchar(50) DEFAULT NULL COMMENT '客户信息来源',
`cust_industry` varchar(50) DEFAULT NULL COMMENT '客户所属行业',
`cust_level` varchar(32) DEFAULT NULL COMMENT '客户级别',
`cust_linkman` varchar(50) DEFAULT NULL COMMENT '联系人',
`cust_phone` varchar(64) DEFAULT NULL COMMENT '固定电话',
`cust_mobile` varchar(16) DEFAULT NULL COMMENT '移动电话',
`cust_zipcode` varchar(10) DEFAULT NULL COMMENT '邮政编码',
`cust_address` varchar(100) DEFAULT NULL COMMENT '联系地址',
`cust_createtime` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8;
/*Data for the table `customer` */
insert into `customer`(`cust_id`,`cust_name`,`cust_user_id`,`cust_create_id`,`cust_source`,`cust_industry`,`cust_level`,`cust_linkman`,`cust_phone`,`cust_mobile`,`cust_zipcode`,`cust_address`,`cust_createtime`) values (14,'小张',NULL,1,'7','3','23','小雪','010-88888887','13848399998','100096','北京昌平区西三旗','2016-04-08 16:32:01'),(15,'小韩',NULL,2,'7','3','23','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:01'),(16,'小李',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:01'),(17,'小赵',NULL,4,'6','4','23','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:02'),(22,'小明',NULL,3,'6','2','23','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:03'),(24,'小伟',NULL,2,'7','5','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:03'),(25,'Tom',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:03'),(26,'jack',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:03'),(28,'Rose',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:04'),(29,'小韩',NULL,1,'7','1','23','小雪','010-88888886','13888888886','100096','北京昌平区西三旗','2016-04-08 16:32:04'),(30,'小叶',NULL,2,'6','2','23','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:04'),(31,'小韩',NULL,4,'7','1','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:04'),(33,'小海',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:04'),(34,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:05'),(35,'小姜',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:05'),(36,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:05'),(37,'小梦',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:05'),(38,'小孙',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:05'),(39,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:06'),(40,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:06'),(41,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:06'),(42,'小韩',NULL,1,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:06'),(43,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:06'),(44,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:07'),(45,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:07'),(46,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:07'),(47,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:07'),(48,'小高',NULL,5,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:07'),(49,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:07'),(50,'小钱',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:08'),(51,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:08'),(52,'小周',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:08'),(53,'小韩',NULL,1,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:08'),(54,'小丽',NULL,1,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:08'),(55,'小韩',NULL,1,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:08'),(56,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:09'),(57,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:09'),(58,'张三',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:09'),(59,'李四',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:29'),(60,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:29'),(61,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:29'),(62,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:29'),(63,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:30'),(64,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:30'),(65,'王五',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:30'),(66,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:30'),(67,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:30'),(68,'赵六',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:30'),(69,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:31'),(70,'小韩',NULL,1,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:31'),(71,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:31'),(72,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:31'),(73,'小七',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:31'),(74,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:32'),(75,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:32'),(76,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:32'),(77,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:32'),(78,'小郑',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:32'),(79,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:32'),(80,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:33'),(81,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:33'),(82,'小吴',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:33'),(83,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:33'),(144,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:44'),(145,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:44'),(146,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:44'),(147,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:45'),(148,'小韩',NULL,1,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:45'),(149,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:45'),(150,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:45'),(151,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:45'),(152,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:46'),(153,'晶晶',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:46'),(154,'倩倩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:46'),(155,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:46'),(156,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:46'),(157,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:46'),(158,'小韩',NULL,1,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:47'),(159,'小韩',NULL,2,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:47'),(160,'小韩',NULL,3,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:47'),(161,'小韩',NULL,4,'6','2','22','小雪','010-88888887','13888888888','100096','北京昌平区西三旗','2016-04-08 16:32:47'),(171,'小韩',NULL,3,'7','1','23','小雪','010-8235438','13755555555','100000','北京昌平区西三旗','2017-05-05 10:23:07'),(172,'小韩',NULL,2,'6','2','22','小雪','010-0000000','13288546521','100859','北京昌平区西三旗','2017-05-05 10:51:08'),(173,'小洛',NULL,1,'6','1','22','小雪','01062872234','13521023333','100000','北京昌平区西三旗','2017-05-05 10:57:09'),(174,'小月',NULL,1,'6','1','22','小雪','0482-8235438','13848399998','137400','内蒙古兴安盟乌兰浩特市','2017-05-05 11:33:27'),(175,'小韩',NULL,1,'6','1','22','小石','0791-88130000','15179105961','330098','江西省南昌市瑶湖高校园区','2017-05-05 13:17:11'),(176,'小智',NULL,1,'7','1','23','小黑','400-618-4000','13520203625','100091','北京市海淀区','2017-05-16 15:49:07'),(177,'小程',NULL,1,'6','1','23','小韩','010-88886616','13718026541','100000','北京市昌平区','2017-05-16 16:25:59');
/*Table structure for table `sys_user` */
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`user_id` int(32) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`user_code` varchar(32) NOT NULL COMMENT '用户账号',
`user_name` varchar(50) NOT NULL COMMENT '用户名称',
`user_password` varchar(32) NOT NULL COMMENT '用户密码',
`user_state` int(1) NOT NULL COMMENT '1:正常,0:暂停',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `sys_user` */
insert into `sys_user`(`user_id`,`user_code`,`user_name`,`user_password`,`user_state`) values (1,'m0001','小韩','123',1),(2,'m0002','小雪','123',1),(3,'m0003','小石','123',1),(4,'m0004','小陈','123',1);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
Java EE 企业级 应用开发教程 第九章备用数据库
-- phpMyAdmin SQL Dump
-- version 4.4.15.8
-- https://www.phpmyadmin.net
--
-- Host: 127.0.0.1:3306
-- Generation Time: 2018-12-26 01:26:22
-- 服务器版本： 5.7.22
-- PHP Version: 5.4.45
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `mybatis`
--
-- --------------------------------------------------------
--
-- 表的结构 `idcard`
--
CREATE TABLE IF NOT EXISTS `idcard` (
`id` int(11) NOT NULL,
`code` varchar(18) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `idcard`
--
INSERT INTO `idcard` (`id`, `code`) VALUES
(1, '213456'),
(2, '456978');
-- --------------------------------------------------------
--
-- 表的结构 `orders`
--
CREATE TABLE IF NOT EXISTS `orders` (
`id` int(32) NOT NULL,
`number` varchar(32) NOT NULL,
`user_id` int(32) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `orders`
--
INSERT INTO `orders` (`id`, `number`, `user_id`) VALUES
(1, '1000011', 1),
(2, '100022', 1),
(3, '100033', 3);
-- --------------------------------------------------------
--
-- 表的结构 `ordersitem`
--
CREATE TABLE IF NOT EXISTS `ordersitem` (
`id` int(32) NOT NULL,
`orders_id` int(32) DEFAULT NULL,
`product_id` int(32) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `ordersitem`
--
INSERT INTO `ordersitem` (`id`, `orders_id`, `product_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 3, 3);
-- --------------------------------------------------------
--
-- 表的结构 `person`
--
CREATE TABLE IF NOT EXISTS `person` (
`id` int(11) NOT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(8) DEFAULT NULL,
`card_id` int(11) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `person`
--
INSERT INTO `person` (`id`, `name`, `age`, `sex`, `card_id`) VALUES
(1, 'tom', 21, '女', 1),
(2, 'jack', 31, '女', 2);
-- --------------------------------------------------------
--
-- 表的结构 `product`
--
CREATE TABLE IF NOT EXISTS `product` (
`id` int(32) NOT NULL,
`name` varchar(32) DEFAULT NULL,
`price` double DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `product`
--
INSERT INTO `product` (`id`, `name`, `price`) VALUES
(1, 'java基础入门', 44.5),
(2, 'java web程序开发入门', 38.5),
(3, 'ssm框架整合实战', 50);
-- --------------------------------------------------------
--
-- 表的结构 `user`
--
CREATE TABLE IF NOT EXISTS `user` (
`id` int(32) NOT NULL,
`username` varchar(32) DEFAULT NULL,
`address` varchar(256) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `user`
--
INSERT INTO `user` (`id`, `username`, `address`) VALUES
(1, '詹姆斯', '湖人'),
(2, '库里', '勇士'),
(3, '欧文', '凯尔特人');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `idcard`
--
ALTER TABLE `idcard`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
ADD PRIMARY KEY (`id`),
ADD KEY `user_id` (`user_id`);
--
-- Indexes for table `ordersitem`
--
ALTER TABLE `ordersitem`
ADD PRIMARY KEY (`id`),
ADD KEY `orders_id` (`orders_id`),
ADD KEY `product_id` (`product_id`);
--
-- Indexes for table `person`
--
ALTER TABLE `person`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `card_id` (`card_id`);
--
-- Indexes for table `product`
--
ALTER TABLE `product`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `user`
--
ALTER TABLE `user`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `idcard`
--
ALTER TABLE `idcard`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
MODIFY `id` int(32) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `ordersitem`
--
ALTER TABLE `ordersitem`
MODIFY `id` int(32) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `person`
--
ALTER TABLE `person`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
MODIFY `id` int(32) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
MODIFY `id` int(32) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Mysql数据库基础与实例教程孔祥盛代码
#第二章
#字符集、字符序的查看、设置
show character set;
show variables like 'character%';
show collation;
show variables like 'collation%';
set character_set_client = gbk;
set character_set_connection = gbk;
set character_set_database = gbk;
set character_set_results = gbk;
set character_set_server = gbk;
set collation_connection = gbk_chinese_ci ;
set collation_database = gbk_chinese_ci ;
set collation_server = gbk_chinese_ci ;
show variables like 'character%';
show variables like 'collation%';
#创建数据库
create database choose;
alter database choose character set gbk;
show variables like 'datadir';
show databases;
show create database choose;
use choose;
#手动创建student数据库目录
#drop database student;
#存储引擎
show engines;
set default_storage_engine=MyISAM;
show engines;
#创建数据库表
use choose;
set default_storage_engine=InnoDB;
create table my_table(
today datetime,
name char(20)
);
alter table my_table engine=MyISAM;
show tables;
desc my_table;
show create table my_table;
#表记录的管理
use choose;
insert into my_table values(now(),'a');
insert into my_table values(now(),'a');
insert into my_table values(now(),NULL);
insert into my_table values(now(),'');
select * from my_table;
use choose;
alter table my_table engine=InnoDB;
use choose;
insert into my_table values(now(),'a');
insert into my_table values(now(),'a');
insert into my_table values(now(),NULL);
insert into my_table values(now(),'');
select * from my_table;
#InnoDB表空间
show variables like 'innodb_data_file_path';
show variables like 'innodb_file_per_table';
set @@global.innodb_file_per_table = ON;
show variables like 'innodb_file_per_table';
use choose;
alter table my_table engine=InnoDB;
create table second_table(
today datetime,
name char(20)
);
drop table second_table;
set @@global.innodb_file_per_table = OFF;
#系统变量
#show global variables;
#show session variables;
show global variables like 'innodb_data_file_path';
show session variables like 'innodb_data_file_path';
show variables like 'innodb_data_file_path';
show session variables like 'pseudo_thread_id';
show variables like 'pseudo_thread_id';
show variables like 'character_set_client';
set character_set_client = latin1;
show session variables like 'character_set_client';
show variables like 'character_set_client';
show session variables like 'character_set_client';
show variables like 'character_set_client';
show global variables like 'character_set_client';
select @@global.innodb_data_file_path;
select @@innodb_data_file_path;
#select @@session.innodb_data_file_path;
set @@global.innodb_file_per_table = ON;
set global innodb_file_per_table = ON;
set @@session.pseudo_thread_id = 5;
set session pseudo_thread_id = 5;
set @@pseudo_thread_id = 5;
set pseudo_thread_id = 5;
select @@character_set_server;
#select @@character-set-server;
flush tables with read lock;
#insert into my_table values(now(),'a');
unlock tables;
set global innodb_file_per_table = OFF;
#第三章
#数据类型
show variables like 'time_zone';
use choose;
create table today(
t1 datetime,
t2 timestamp
);
insert into today values(now(),now());
insert into today values(now(),null);
select * from today;
set time_zone='+12:00';
select * from today;
update today set t1=now();
select * from today;
select now(6),curtime(6);
select now(6),now(),microsecond(now(6));
set character_set_client = gbk;
use choose;
create table person(
sex enum('男','女'),
interest set('听音乐','看电影','购物','旅游','游泳','游戏')
);
insert into person values('男','看电影,游泳,听音乐');
select * from person;
set sql_mode = 'strict_trans_tables';
#insert into person values('男','电影,游泳,听音乐');
set sql_mode = 'ansi';
insert into person values('男','电影,游泳,听音乐');
set sql_mode = 'strict_trans_tables';
#约束
use choose;
create table nowadays (
t1 datetime,
t2 timestamp,
primary key(t1, t2)
);
select constraint_name, constraint_type
from information_schema.table_constraints
where table_schema='choose' and table_name='nowadays';
show index from nowadays\G
create table test(
test_no char(10),
test_name char(10),
constraint test_pk primary key (test_no),
constraint name_unique unique (test_name)
);
select constraint_name, constraint_type
from information_schema.table_constraints
where table_schema='choose' and table_name='test';
show index from test\G
#选课系统数据库表
use choose;
create table teacher(
teacher_no char(10) primary key,
teacher_name char(10) not null, #教师姓名不允许为空
teacher_contact char(20) not null #教师联系方式名不允许为空
)engine=InnoDB default charset=gbk;
create table classes(
class_no int auto_increment primary key,
class_name char(20) not null unique, #班级名不允许为空、且不允许重复
department_name char(20) not null #院系名不允许为空
)engine=InnoDB default charset=gbk;
create table course(
course_no int auto_increment primary key,
course_name char(10) not null, #课程名允许重复
up_limit int default 60, #课程上限设置默认值为60
description text not null, #课程的描述信息为文本字符串text，且不能为空
status char(6) default '未审核', #课程状态的默认值为“未审核”
teacher_no char(10) not null unique, #唯一性约束实现教师与课程之间1:1关系
constraint course_teacher_fk foreign key(teacher_no) references teacher(teacher_no)
)engine=InnoDB default charset=gbk;
create table student(
student_no char(11) primary key, #学号不允许重复
student_name char(10) not null, #学生姓名不允许为空
student_contact char(20) not null, #学生联系方式不允许为空
class_no int , #学生的班级允许为空
constraint student_class_fk foreign key (class_no) references classes(class_no)
)engine=InnoDB default charset=gbk;
create table choose(
choose_no int auto_increment primary key,
student_no char(11) not null, #学生学号不允许为空
course_no int not null, #课程号不允许为空
score tinyint unsigned,
choose_time datetime not null, #选课时间可由now()函数自动生成
constraint choose_student_fk foreign key(student_no) references student(student_no),
constraint choose_course_fk foreign key(course_no) references course(course_no)
)engine=InnoDB default charset=gbk;
use choose;
create table today1 like today;
show create table today1;
select * from today1;
use choose;
create table today2 select * from today;
show create table today2;
select * from today2;
#修改表结构
alter table person drop interest;
alter table person add person_no int auto_increment primary key first;
alter table person add person_name char(10) not null after person_no;
alter table person change person_name name char(20);
alter table person modify name char(30);
#修改约束条件
delete from person;
alter table person add constraint name_unique unique (name);
select constraint_name, constraint_type
from information_schema.table_constraints
where table_schema='choose' and table_name='person';
show index from person\G
alter table person drop index name_unique;
alter table person engine=MyISAM;
alter table person default charset=gb2312;
alter table person auto_increment=8;
alter table person pack_keys=1;
alter table person rename human;
#索引
create table book(
isbn char(20) primary key,
name char(100) not null,
brief_introduction text not null,
price decimal(6,2),
publish_time date not null,
unique index isbn_unique (isbn),
index name_index (name (20)),
fulltext index brief_fulltext (name,brief_introduction),
index complex_index (price,publish_time)
) engine=MyISAM default charset=gbk;
alter table course add fulltext index description_fulltext (description);
drop index complex_index on book;
#第四章
use choose;
insert into teacher values('001','张老师','11000000000');
insert into teacher values('002','李老师','12000000000');
insert into teacher values('003','王老师','13000000000');
select * from teacher;
use choose;
insert into classes(class_no,class_name,department_name) values(null,'2012自动化1班', '机电工程');
insert into classes(class_no,class_name,department_name) values(null,'2012自动化2班', '机电工程');
insert into classes(class_no,class_name,department_name) values(null,'2012自动化3班', '机电工程');
select * from classes;
use choose;
insert into course values(null,'java语言程序设计',default,'暂无','已审核','001');
insert into course values(null,'MySQL数据库',150,'暂无','已审核','002');
insert into course values(null,'c语言程序设计',230,'暂无','已审核','003');
select * from course;
#下面的两处insert语句会产生错误，但id字段也会自增
insert into course values(null,'PHP编程基础',default,'暂无','已审核','007');
insert into course values(null,'PHP编程基础',default,'暂无','已审核','002');
use choose;
set character_set_client = latin1;
insert into classes values(null,'2012计算机应用1班', '信息工程');
select * from classes;
use choose;
set character_set_client = gbk;
select * from classes;
use choose;
delete from classes where class_no=4;
select * from classes;
insert into classes values(null,'2012计算机应用1班', '信息工程');
select * from classes;
show create table classes;
use choose;
insert into student values
('2012001','张三','15000000000',1),
('2012002','李四','16000000000',1),
('2012003','王五','17000000000',3),
('2012004','马六','18000000000',2),
('2012005','田七','19000000000',2);
select * from student;
use choose;
create table new_student like student;
insert into new_student select * from student;
select * from new_student;
replace into student values ('2012001','张三丰','15000000000',1);
replace into student values ('2012001','张三','15000000000',1);
use choose;
update classes set department_name='机电工程学院' where class_no<=3;
select * from classes;
use choose;
delete from classes where class_name='2012计算机应用1班';
select * from classes;
use choose;
#delete from classes;
select * from classes;
use choose;
create table new_class like classes;
insert into new_class select * from classes;
select * from new_class;
show create table new_class;
delete from new_class;
show create table new_class;
truncate table new_class;
show create table new_class;
insert into choose values
(null,'2012001',2,40,now()),
(null,'2012001',1,50,now()),
(null,'2012002',3,60,now()),
(null,'2012002',2,70,now()),
(null,'2012003',1,80,now()),
(null,'2012004',2,90,now()),
(null,'2012005',3,null,now()),
(null,'2012005',1,null,now());
use choose;
insert into new_student values('2012006','Mar_tin', 'mar\tin@gmail.com',3);
insert into new_student values('2012007','O\'Neil', 'o_\neil@gmail.com',3);
select * from new_student;
#第五章
select version(), now(),pi(),1+2,null=null,null!=null,null is null;
select version() 版本号, now() as 服务器当前时间, pi() PI的值,1+2 求和;
select * from student;
select student_no,student_name, student_contact,class_no from student;
select student_no,student_name from student;
select student.student_no, student.student_name from student;
select distinct department_name from classes;
select * from student limit 0,3;
select * from student limit 3;
select * from choose limit 1,3;
insert into classes values(4,'2012自动化4班','机电工程学院');
insert into student values('2012006','张三丰','20000000000',null);
select student_no,student_name,student_contact,student.class_no,class_name,department_name
from student join classes on student.class_no=classes.class_no;
select student_no,student_name,student_contact,student.class_no,class_name,department_name
from classes join student on student.class_no=classes.class_no;
select student_no,student_name,student_contact,s.class_no,class_name,department_name
from classes as c join student s on s.class_no=c.class_no;
select student_no,student_name,student_contact,s.class_no,class_name,department_name
from classes c join student s on s.class_no=c.class_no;
select student_no,student_name,student_contact,student.class_no,class_name,department_name
from student left join classes on student.class_no=classes.class_no;
select student_no,student_name,student_contact,student.class_no,class_name,department_name
from student left join classes on student.class_no=classes.class_no
where classes.class_no is null;
select classes.class_no,class_name,department_name,student_no,student_name,student_contact
from student right join classes on student.class_no=classes.class_no;
select student.student_no,student_name,choose.course_no,course_name,score
from classes left join student on classes.class_no=student.class_no
join choose on student.student_no=choose.student_no
join course on course.course_no=choose.course_no
where class_name='2012自动化2班';
select student_no,student_name,student_contact,student.class_no,class_name,department_name
from student , classes
where student.class_no=classes.class_no;
select student.student_no,student_name,choose.course_no,course_name,score
from student inner join choose on student.student_no=choose.student_no
inner join course on choose.course_no=course.course_no
where score is NULL;
select 2 = 2,NULL = NULL, NULL != NULL, NULL is NULL, NULL is not NULL;
select * from course where up_limit=60;
set character_set_results = latin1;
select * from course where up_limit=60;
set character_set_results = gbk;
select * from course where course_name='java语言程序设计';
set character_set_connection = latin1;
select * from course where course_name='java语言程序设计';
set names latin1;
#select * from course where course_name='java语言程序设计';
set names gbk;
select * from course where !(up_limit=60);
select student.student_no,student_name,student_contact,choose.course_no,course_name,score
from course join choose on course.course_no=choose.course_no
join student on choose.student_no=student.student_no
where course.course_name='MySQL数据库' and score<60;
select student.student_no,student_name,choose.course_no,course_name,score
from student join choose on student.student_no=choose.student_no
join course on choose.course_no=course.course_no
where score between 80 and 100;
select *
from student
where substring(student_name,1,1)='张' or substring(student_name,1,1)='田';
select * from student where substring(student_name,1,1) in ('张' ,'田');
select * from student where student_name like '张_';
select * from student where student_name like '%三%';
select * from new_student where student_name like '%\_%';
select * from new_student where student_name like '%!_%' escape '!';
select * from choose order by score desc;
select student.student_no,student_name,course.course_no,course_name,score
from student inner join choose on student.student_no=choose.student_no
inner join course on choose.course_no=course.course_no
order by student_no asc,course_no asc;
select count(*) 学生人数 from student;
select count(choose_no) 参加考试的人数, count(choose_no)-count(score) 缺考学生人数,
(count(choose_no)-count(score))/count(choose_no)*100 缺考百分比 from choose;
select sum(score) 总成绩 from choose;
select student.student_no,student_name 姓名,sum(score) 总成绩
from student left join choose on choose.student_no=student.student_no
where student_name='张三丰';
select student.student_no,student_name 姓名,avg(score) 平均成绩
from student left join choose on choose.student_no=student.student_no
where student_name='张三';
select max(score) 最高分,min(score) 最低分 from choose;
select * from student order by class_no;
select * from student group by class_no;
select class_name,count(student_no)
from classes left join student on student.class_no=classes.class_no
group by classes.class_no;
select student.student_no,student_name,count(course_no),max(score),min(score),sum(score), avg(score)
from student left join choose on student.student_no=choose.student_no
group by student.student_no;
select choose.student_no,student_name,avg(score)
from choose join student on choose.student_no=student.student_no
group by student.student_no
having avg(score)>70;
select group_concat('java','程序','设计'),concat('java','程序','设计');
select class_name 班级名,group_concat(student_name) 学生名单, concat(student_name) 部分名单
from classes left join student on student.class_no=classes.class_no
group by classes.class_no;
select classes.class_no,count(classes.class_no)
from classes join student on student.class_no=classes.class_no
group by classes.class_no with rollup;
select student_no 账号,student_name 姓名,student_contact 联系方式
from student
union all
select teacher_no,teacher_name,teacher_contact
from teacher;
select class_name,student.student_no, student_name,course_name,score
from classes join student on student.class_no=classes.class_no
join choose on choose.student_no=student.student_no
join course on choose.course_no=course.course_no
where score>(
select avg(score)
from student,choose
where student.student_no=choose.student_no and student_name='张三'
);
select class_name,student.student_no, student_name,course_name,score
from classes join student on student.class_no=classes.class_no
join choose on choose.student_no=student.student_no
join course on choose.course_no=course.course_no
where score>(
select avg(score)
from choose
where student.student_no=choose.student_no and student_name='张三'
);
select class_name,student.student_no,student_name,course_name,avg(score)
from classes join student on student.class_no=classes.class_no
join choose on choose.student_no=student.student_no
join course on choose.course_no=course.course_no
group by student.student_no
having avg(score)>(
select avg(score)
from choose join student on student.student_no=choose.student_no
where student_name='张三'
);
select student.student_no,student_name,course_name,score
from course join choose on choose.course_no=course.course_no
join student on choose.student_no=student.student_no
where student.student_no in (
select student_no
from student join classes on student.class_no=classes.class_no
where classes.class_name='2012自动化1班'
);
select * from teacher where teacher_no
not in (
select teacher.teacher_no from course where course.teacher_no=teacher.teacher_no
);
insert into teacher values('004','马老师','10000000000');
select * from teacher
where not exists (
select * from course where course.teacher_no=teacher.teacher_no
);
select * from course
where not exists (
select * from choose where course.course_no=choose.course_no
);
select student.student_no,student_name,class_name
from student join classes on student.class_no=classes.class_no
join choose on choose.student_no=student.student_no
where class_name='2012自动化2班' and score>any(
select score
from choose join student on student.student_no=choose.student_no
join classes on classes.class_no=student.class_no
where class_name='2012自动化1班'
);
#此处PHP程序设计的id为6
insert into course values(null,'PHP程序设计',60,'暂无','已审核','004');
select student.student_no,student_name,class_name
from student join classes on student.class_no=classes.class_no
join choose on choose.student_no=student.student_no
where class_name='2012自动化2班' and score>all(
select score
from choose join student on student.student_no=choose.student_no
join classes on classes.class_no=student.class_no
where class_name='2012自动化1班'
);
#选课系统综合查询
select student_no,count(*) course_num
from choose
where student_no='2012001';
select choose.course_no, course_name,teacher_name,teacher_contact,description
from choose join course on course.course_no=choose.course_no
join teacher on teacher.teacher_no=course.teacher_no
where student_no='2012001';
select department_name,class_name,student.student_no,student_name,student_contact
from student join classes on student.class_no=classes.class_no
join choose on student.student_no=choose.student_no
where course_no=1
order by department_name,class_name,student_no;
select course.course_no,course_name,teacher_name,up_limit,description
from choose join course on choose.course_no=course.course_no
join teacher on teacher.teacher_no=course.teacher_no
group by course_no
having up_limit=count(*);
select course.course_no,course_name,teacher_name,teacher_contact,count(*) as student_num
from choose join course on choose.course_no=course.course_no
join teacher on teacher.teacher_no=course.teacher_no
group by course_no
having count(*)<30
union all
select course.course_no, course_name,teacher_name,teacher_contact,0
from course join teacher on teacher.teacher_no=course.teacher_no
where not exists (
select * from choose where course.course_no=choose.course_no
);
select course.course_no,course_name,teacher_name,
up_limit,count(*) as student_num,up_limit-count(*) available
from choose join course on choose.course_no=course.course_no
join teacher on teacher.teacher_no=course.teacher_no
group by course_no
union all
select course.course_no,course_name,teacher_name,up_limit,0,up_limit
from course join teacher on teacher.teacher_no=course.teacher_no
where not exists (
select * from choose where course.course_no=choose.course_no
);
select * from course where course_name regexp 'java';
select * from course where course_name like '%java%';
select * from course where course_name regexp '程序设计$';
select * from course where course_name regexp '^j.*程序设计$';
select * from student where student_contact regexp '^1[58][0-9]{9}';
insert into book(isbn,name,brief_introduction,price,publish_time) values
('978-7-115-25626-3','PHP Fundamentals & Practices','Web Database Applications MySQL offers web developers a mixture of theoretical and practical information on creating web database applications. ','42.0','2012-7-1'),
('978-7-115-25626-4','MySQL COOKBOOK','The MySQL database management system has become quite popular in recent years.','128.0','2008-1-1'),
('978-7-115-25626-5','Beginning MySQL',' MySQL is especially heavily used in combination with a web server for constructing database-backed web sites that involve dynamic content generation.','98.0','2008-1-1');
select * from book where match (name,brief_introduction) against ('practices')\G
select * from book where match (name,brief_introduction) against ('practices cookbook')\G
select isbn,name, match(name,brief_introduction) against ('practices cookbook') 关联度
from book;
select * from book where match (name,brief_introduction) against ('mysql')\G
select * from book where match (name,brief_introduction) against ('mysql' in boolean mode)\G
select * from book where match (name,brief_introduction) against ('php' in boolean mode)\G
show variables like 'ft_min_word_len';
show variables like 'ft_max_word_len';
repair table book quick;
select * from book where match (name,brief_introduction) against ('that' in boolean mode)\G
show variables like 'ft_stopword_file';
repair table book quick;
select * from book where match (name,brief_introduction) against ('that' in boolean mode)\G
select * from book where match (name,brief_introduction) against ('+mysql -php' in boolean mode)\G
select isbn,name,match(name,brief_introduction) against('+mysql +(cookbook php)' in boolean mode) 关联度
from book;
select isbn,name,match(name,brief_introduction) against('+mysql +(>cookbook <php)' in boolean mode) 关联度
from book;
select * from book where match (name,brief_introduction) against (' "Database Applications" popu*' in boolean mode)\G
select * from book where match (name,brief_introduction) against ('mysql')\G
alter table book engine=InnoDB;
select * from book where match (name,brief_introduction) against ('mysql')\G
#第六章
select 'I\'m a \teacher' as col1, "you're a stude\nt" as col2;
select X'41', x'4D7953514C';
select 0x41, 0x4D7953514C ;
select b'111101',b'1', b'11';
select true, false;
set @user_name = '张三';
select @user_name;
set @user_name = b'11', @age = 18;
select @user_name,@age;
set @age = @age+1;
select @user_name,@age;
select @a := 'a';
select @a='a';
select @user_name:='张三';
select 19 into @age;
select @user_name,@age;
set @student_count = (select count(*) from student);
select @student_count;
select @student_count := (select count(*) from student);
select @student_count:= count(*) from student;
select count(*) into @student_count from student;
select count(*) from student into @student_count;
set @student_no='2012001';
select * from student where student_no=@student_no;
set @num = 15;
select @num+2, @num-2, @num *2, @num/2, @num%3,@num div 3;
select '2013-01-31' + interval '22' day, '2013-01-31' - interval '22' day;
select 'ab '='ab', ' ab'='ab', 'b'>'a',NULL=NULL,NULL<=>NULL,NULL is NULL;
select 1 and 2, 2 and 0,2 and true,0 or true,not 2,not false;
select null and 2, 2 and 0.0,2 and 'true', 1 xor 2, 1 xor false;
select b'101' & b'010',5&2,5|2, ~5,5 ^2,5>>2,5<<2;
delimiter $$
select * from student where student_name like '张_'$$
delimiter ;
select * from student where student_name like '张_';
#自定义函数
delimiter $$
create function row_no_fn() returns int
no sql
begin
set @row_no = @row_no + 1;
return @row_no;
end;
$$
delimiter ;
delimiter $$
create function get_name_fn(student_no1 int) returns char(20)
reads sql data
begin
declare student_name1 char(20);
select student_name into student_name1 from student where student_no=student_no1;
return student_name1;
end;
$$
delimiter ;
delimiter $$
create function get_choose_number_fn(student_no1 int) returns int
reads sql data
begin
declare choose_number int;
select count(*) into choose_number from choose where student_no=student_no1;
return choose_number;
end;
$$
delimiter ;
select name from mysql.proc where db = 'choose' and type = 'function' ;
show create function get_name_fn\G
select * from information_schema.routines where routine_name='get_name_fn'\G
drop function get_name_fn;
delimiter $$
create function get_name_fn (no int,role char(20)) returns char(20)
reads sql data
begin
declare name char(20);
if('student'=role) then
select student_name into name from student where student_no=no;
elseif('teacher'=role) then
select teacher_name into name from teacher where teacher_no=no;
else set name='输入有误！';
end if;
return name;
end;
$$
delimiter ;
delimiter $$
create function get_week_fn(week_no int) returns char(20)
no sql
begin
declare week char(20);
case week_no
when 0 then set week = '星期一';
when 1 then set week = '星期二';
when 2 then set week = '星期三';
when 3 then set week = '星期四';
when 4 then set week = '星期五';
else set week = '今天休息';
end case;
return week;
end
$$
delimiter ;
delimiter $$
create function get_sum_fn(n int) returns int
no sql
begin
declare sum char(20) default 0;
declare start int default 0;
while start<n do
set start = start + 1;
set sum = sum + start;
end while;
return sum;
end;
$$
delimiter ;
delimiter $$
create function get_sum1_fn(n int) returns int
no sql
begin
declare sum char(20) default 0;
declare start int default 0;
add_num : while true do
set start = start + 1;
set sum = sum + start;
if(start=n) then
leave add_num;
end if;
end while add_num;
return sum;
end;
$$
delimiter ;
delimiter $$
create function get_sum2_fn (n int) returns int
no sql
begin
declare sum char(20) default 0;
declare start int default 0;
add_num: while true do
set start = start + 1;
if(start%2=0) then
set sum = sum + start;
else
iterate add_num;
end if;
if(start=n) then
leave add_num;
end if;
end while add_num;
return sum;
end;
$$
delimiter ;
delimiter $$
create function get_sum3_fn(n int) returns int
no sql
begin
declare sum char(20) default 0;
declare start int default 0;
repeat
set start = start + 1;
set sum = sum + start;
until start=n
end repeat;
return sum;
end;
$$
delimiter ;
delimiter $$
create function get_sum4_fn(n int) returns int
no sql
begin
declare sum char(20) default 0;
declare start int default 0;
add_sum : loop
set start = start + 1;
set sum = sum + start;
if (start=n) then
leave add_sum;
end if;
end loop;
return sum;
end;
$$
delimiter ;
insert into new_class values
(null,'2012软件技术1班','软件学院'),
(null,'2012软件技术2班','软件学院');
select last_insert_id(),@@last_insert_id;
delimiter $$
create function to_english_fn(s varchar(32760)) returns varchar(32760)
no sql
begin
declare start int;
declare string_length int;
declare new_string varchar(32760);
declare temp_string varchar(32760);
set start = 1;
set string_length = char_length(s);
set new_string = '';
while start<=string_length do
set temp_string = hex(substring(s,start,1));
set start = start + 1;
set new_string = concat(new_string,' H',temp_string);
end while;
return trim(new_string);
end;
$$
delimiter ;
update course set description=to_english_fn('Java是一种可以撰写跨平台应用软件的面向对象的程序设计语言。') where course_name='java语言程序设计';
update course set description=to_english_fn('MySQL是一个中型关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。') where course_name='MySQL数据库';
update course set description=to_english_fn('C语言是一种计算机程序设计语言，它既具有高级语言的特点，又具有汇编语言的特点。') where course_name='c语言程序设计';
update course set description=to_english_fn('PHP简单易学且功能强大，是开发WEB应用程序理想的脚本语言。') where course_name='PHP程序设计';
delimiter $$
create function to_chinese_fn(s varchar(32760)) returns varchar(32760)
no sql
begin
declare new_string varchar(32760);
declare temp_string varchar(32760);
set temp_string = replace(s, ' H','');
set temp_string = replace(temp_string,'H','');
set temp_string = unhex(temp_string);
return temp_string;
end
$$
delimiter ;
#第七章视图与触发器
use choose;
create view available_course_view
as
select course.course_no,course_name,teacher_name,teacher_contact,count(*) as student_num
from choose join course on choose.course_no=course.course_no
join teacher on teacher.teacher_no=course.teacher_no
group by course_no
having count(*)<30
union all
select course.course_no, course_name,teacher_name,teacher_contact,0
from course join teacher on teacher.teacher_no=course.teacher_no
where not exists (
select * from choose where course.course_no=choose.course_no
);
alter table course add available int default 0;
select course_no,course_name,up_limit,available from course;
update course set available=up_limit-(select student_num from available_course_view where available_course_view.course_no=course.course_no);
drop view available_course_view;
create view choose_1_view as select * from choose where score<60;
insert into choose_1_view values (null,'2012003',2,100,now());
select * from choose;
delete from choose where student_no='2012003' and course_no=2;
create view choose_2_view as select * from choose where score<60 with local check option;
#下面的insert语句执行时产生错误
insert into choose_2_view values (null,'2012004',2,100,now());
#触发器
delimiter $$
create trigger course_insert_before_trigger before insert on course for each row
begin
if(new.up_limit=60 || new.up_limit=150 || new.up_limit=230) then
set new.up_limit = new.up_limit;
else insert into mytable values(0);
end if;
end;
$$
delimiter ;
insert into teacher values('005','田老师','00000000000');
#下面的insert语句执行时产生错误
insert into course values(null,'高等数学',20,'暂无','已审核','005',20);
delimiter $$
create trigger course_update_before_trigger before update on course for each row
begin
if(new.up_limit!=60 || new.up_limit!=150 || new.up_limit!=230) then
set new.up_limit = old.up_limit;
end if;
end;
$$
delimiter ;
delimiter $$
create trigger choose_insert_before_trigger before insert on choose for each row
begin
update course set available=available-1 where course_no=new.course_no;
end;
$$
delimiter ;
delimiter $$
create trigger choose_delete_before_trigger before delete on choose for each row
begin
update course set available=available+1 where course_no=old.course_no;
end;
$$
delimiter ;
#InnoDB存储引擎的级联删除
alter table choose drop foreign key choose_course_fk;
alter table choose add constraint choose_course_fk foreign key (course_no) references course(course_no) on delete cascade;
create table organization(
o_no int not null auto_increment,
o_name varchar(32) default '',
primary key (o_no)
) engine=innodb;
create table member(
m_no int not null auto_increment,
m_name varchar(32) default '',
o_no int,
primary key (m_no),
constraint organization_member_fk foreign key (o_no) references organization(o_no)
) engine=innodb;
insert into organization(o_no, o_name) values
(null, 'o1'),
(null, 'o2');
insert into member(m_no,m_name,o_no) values
(null, 'm1',1),
(null, 'm2',1),
(null, 'm3',1),
(null, 'm4',2),
(null, 'm5',2);
delimiter $$
create trigger organization_delete_before_trigger before delete on organization for each row
begin
delete from member where o_no=old.o_no;
end;
$$
delimiter ;
select * from member;
delete from organization where o_no=1;
select * from member;
drop trigger organization_delete_before_trigger;
#临时表
alter table student add password char(32) not null after student_no;
alter table teacher add password char(32) not null after teacher_no;
create temporary table password_temp select student_no s_no, md5(student_no) pwd from student;
update student set password=(
select pwd
from password_temp
where student_no=s_no
);
update teacher s set s.password =(
select md5(u.teacher_no)
from
(select teacher_no from teacher) u
where s.teacher_no=u.teacher_no
);
#存储过程
delimiter $$
create procedure get_choose_number_proc(in student_no1 int,out choose_number int)
reads sql data
begin
select count(*) into choose_number from choose where student_no=student_no1;
end
$$
delimiter ;
delimiter $$
create procedure get_choose_number1_proc(inout number int)
reads sql data
begin
select count(*) into number from choose where student_no=number ;
end
$$
delimiter ;
delimiter $$
create procedure get_student_course_proc(in s_no char(11))
reads sql data
begin
select choose.course_no,course_name,teacher_name,teacher_contact,to_chinese_fn(description) description
from choose join course on course.course_no=choose.course_no
join teacher on teacher.teacher_no=course.teacher_no
where student_no=s_no;
end
$$
delimiter ;
delimiter $$
create procedure get_teacher_course_proc(in t_no char(11))
reads sql data
begin
select course_no,course_name,teacher_name,teacher_contact,status,to_chinese_fn(description) description
from teacher join course on course.teacher_no=teacher.teacher_no
where teacher.teacher_no=t_no;
end
$$
delimiter ;
delimiter $$
create procedure get_course_student_proc(in c_no int)
reads sql data
begin
select department_name,class_name,student.student_no,student_name,student_contact
from student join classes on student.class_no=classes.class_no
join choose on student.student_no=choose.student_no
where course_no=c_no
order by department_name,class_name,student_no;
end
$$
delimiter ;
delimiter $$
create procedure choose_proc(in s_no char(11),in c_no int,out state int)
modifies sql data
begin
declare s1 int;
declare s2 int;
declare s3 int;
declare status char(8);
set state= 0;
set status='未审核';
select count(*) into s1 from choose where student_no=s_no and course_no=c_no ;
if(s1>=1) then
set state = -1;
else
select count(*) into s2 from choose where student_no=s_no;
if(s2>=2) then
set state = -2;
else
select state into status from course where course_no=c_no;
select available into s3 from course where course_no=c_no;
if(s3=0 || status='未审核') then
set state = -3;
else
insert into choose values(null,s_no,c_no,null,now());
set state = last_insert_id();
end if;
end if;
end if;
end
$$
delimiter ;
set @state = 0;
call choose_proc('2012003',1,@state);
select @state;
call choose_proc('2012003',2,@state);
select @state;
call choose_proc('2012003',3,@state);
select @state;
drop procedure get_choose_number_proc;
delimiter $$
create procedure choose1_proc(in s_no char(11),in c_no int,out state int)
modifies sql data
begin
declare s1 int;
declare s2 int;
declare s3 int;
declare status char(8);
declare continue handler for 1452
begin
set @error1 = '外键约束错误!';
end;
set state= 0;
set status='未审核';
select count(*) into s1 from choose where student_no=s_no and course_no=c_no ;
if(s1>=1) then
set state = -1;
else
select count(*) into s2 from choose where student_no=s_no;
if(s2>=2) then
set state = -2;
else
select state into status from course where course_no=c_no;
select available into s3 from course where course_no=c_no;
if(s3=0 || status='未审核') then
set state = -3;
else
insert into choose values(null,s_no,c_no,null,now());
set state = last_insert_id();
set @error2 = '错误虽然发生，程序依然继续运行!';
end if;
end if;
end if;
end
$$
delimiter ;
delimiter $$
create procedure update_course_score_proc(in c_no int)
modifies sql data
begin
declare s_no int;
declare grade int;
declare state char(20);
declare score_cursor cursor for select student_no,score from choose where course_no=c_no;
declare continue handler for 1329 set state = 'error';
open score_cursor;
repeat
fetch score_cursor into s_no,grade;
set grade = grade + 5;
if(grade>100) then set grade = 100; end if;
if(grade>=55 && grade<=59) then set grade = 60; end if;
update choose set score=grade where student_no=s_no and course_no=c_no;
until state = 'error'
end repeat;
close score_cursor;
end
$$
delimiter ;
delimiter $$
create procedure get_class_proc(in class_no int)
reads sql data
begin
set @class_no = class_no;
prepare class_pre from 'select * from classes where class_no=?';
execute class_pre using @class_no;
deallocate prepare class_pre;
end
$$
delimiter ;
#事务与锁机制
create table account(
account_no int auto_increment primary key,
account_name char(10) not null,
balance int unsigned
) engine=innodb;
insert into account values(null,'甲',1000);
insert into account values(null,'乙',1000);
delimiter $$
create procedure transfer_proc(in from_account int,in to_account int,in money int)
modifies sql data
begin
update account set balance=balance+money where account_no=to_account;
update account set balance=balance-money where account_no=from_account;
end
$$
delimiter ;
call transfer_proc(1,2,800);
select * from account;
call transfer_proc(1,2,800);
select * from account;
set autocommit=0;
update account set balance=balance+800 where account_no=2;
select * from account;
commit;
set autocommit=1;
drop procedure transfer_proc;
delimiter $$
create procedure transfer_proc (in from_account int,in to_account int,in money int)
modifies sql data
begin
declare continue handler for 1690
begin
rollback;
end;
start transaction;
update account set balance=balance+money where account_no=to_account;
update account set balance=balance-money where account_no=from_account;
commit;
end
$$
delimiter ;
delimiter $$
create procedure save_point1_proc()
modifies sql data
begin
declare continue handler for 1062
begin
rollback to B;
rollback;
end;
start transaction;
insert into account values(null,'丙',1000);
savepoint B;
insert into account values(last_insert_id(),'丁',1000);
commit;
end
$$
delimiter ;
call save_point1_proc();
select * from account;
delimiter $$
create procedure save_point2_proc()
modifies sql data
begin
declare continue handler for 1062
begin
rollback to B;
commit;
end;
start transaction;
insert into account values(null,'丙',1000);
savepoint B;
insert into account values(last_insert_id(),'丁',1000);
commit;
end
$$
delimiter ;
call save_point2_proc();
select * from account;
#调课存储过程
delimiter $$
create procedure replace_course_proc(in s_no char(11),in c_before int,in c_after int,out state int)
modifies sql data
begin
declare s int;
declare status char(8);
set state = 0;
set status='未审核';
if(c_before=c_after) then
set state = -1;
else
start transaction;
select state into status from course where course_no=c_after;
select available into s from course where course_no=c_after;
if(s=0 || status='未审核') then
set state = -2;
elseif(state=0) then
update choose set course_no=c_after,choose_time=now() where student_no=s_no and course_no=c_before;
update course set available=available+1 where course_no=c_before;
update course set available=available-1 where course_no=c_after;
set state = c_after;
end if;
commit;
end if;
end
$$
delimiter ;
set @s_no = '2012002';
set @c_before = 3;
set @c_after = 1;
set @state = 0;
call replace_course_proc(@s_no,@c_before,@c_after,@state);
select @state;
#锁机制
alter table account engine=MyISAM;
lock tables account as a read,account as b write;
select * from account as a;
select * from account as b;
select * from account;
unlock tables;
insert into account values(null,'丁',1000);
#重建调课存储过程
drop procedure replace_course_proc;
delimiter $$
create procedure replace_course_proc(in s_no char(11),in c_before int,in c_after int,out state int)
modifies sql data
begin
declare s int;
declare status char(8);
set state = 0;
set status='未审核';
if(c_before=c_after) then
set state = -1;
else
start transaction;
select state into status from course where course_no=c_after;
select available into s from course where course_no=c_after for update;
if(s=0 || status='未审核') then
set state = -2;
elseif(state=0) then
update choose set course_no=c_after,choose_time=now() where student_no=s_no and course_no=c_before;
update course set available=available+1 where course_no=c_before;
update course set available=available-1 where course_no=c_after;
set state = c_after;
end if;
commit;
end if;
end
$$
delimiter ;
#重建选课存储过程
drop procedure choose_proc;
delimiter $$
create procedure choose_proc(in s_no char(11),in c_no int,out state int)
modifies sql data
begin
declare s1 int;
declare s2 int;
declare s3 int;
declare status char(8);
set state= 0;
set status='未审核';
select count(*) into s1 from choose where student_no=s_no and course_no=c_no ;
if(s1>=1) then
set state = -1;
else
select count(*) into s2 from choose where student_no=s_no;
if(s2>=2) then
set state = -2;
else
start transaction;
select state into status from course where course_no=c_no;
select available into s3 from course where course_no=c_no for update;
if(s3=0 || status='未审核') then
set state = -3;
else
insert into choose values(null,s_no,c_no,null,now());
set state = last_insert_id();
end if;
commit;
end if;
end if;
end
$$
delimiter ;
#行级锁，修改account表的存储引擎
alter table account engine=InnoDB;
insert into account values(20,'戊',5000);
drop procedure transfer_proc;
delimiter $$
create procedure transfer_proc(in from_account int,in to_account int,in money int)
modifies sql data
begin
declare continue handler for 1690
begin
rollback;
end;
declare continue handler for 1213
begin
rollback;
end;
start transaction;
update account set balance=balance+money where account_no=to_account;
update account set balance=balance-money where account_no=from_account;
commit;
end
$$
delimiter ;
insert into account values(100,'己',5000);
insert into account values(200,'庚',5000);
#第十章
create table admin(
admin_no char(10) primary key,
password char(32) not null,
admin_name char(10)
);
insert into admin values('admin',md5('admin'),'管理员');
create view course_teacher_view as
select course_no,course_name,up_limit,to_chinese_fn(description) description,teacher.teacher_no,teacher_name,teacher_contact,available,status
from course join teacher on course.teacher_no=teacher.teacher_no;
SpringMVC+Mybatis开发 从入门到项目实战
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.6.24 : Database - fruit_manage
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`fruit_manage` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;
USE `fruit_manage`;
/*Table structure for table `accessory` */
DROP TABLE IF EXISTS `accessory`;
CREATE TABLE `accessory` (
`accessoryid` varchar(50) COLLATE utf8_bin NOT NULL,
`fruitid` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`price` double DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`accessoryid`),
UNIQUE KEY `accessory_PK` (`accessoryid`),
KEY `关系4_FK` (`fruitid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `accessory` */
insert into `accessory`(`accessoryid`,`fruitid`,`name`,`price`,`createtime`) values ('4587egaskh-9j65-87s4-9245-9347ajasd','88e6ec6c-6d17-43a7-8782-38904ajskdh','包装袋',0.1,'2017-09-13 19:47:19'),('4758zhuyrg-2l4t-4d6e-1754-1342ujify','88e6ec6c-6d17-43a7-8782-38904ajskdh','纸板盒',0.3,'2017-09-13 19:50:12'),('fbfec4d6-6510-4c83-9ef2-5467ef6b07ca','88e6ec6c-6d17-43a7-8782-38904ajskdh','捆绳',0.1,'2017-09-13 21:17:39'),('sdasdugy8c-27hkj-dj9-sd-93hsd9834hk','88e6ec6c-6d17-43a7-8782-48957ajskdf','塑料袋',0.05,'2017-09-16 12:20:03');
/*Table structure for table `commodities` */
DROP TABLE IF EXISTS `commodities`;
CREATE TABLE `commodities` (
`fruitid` varchar(50) COLLATE utf8_bin NOT NULL,
`name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`price` double DEFAULT NULL,
`locality` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`fruitid`),
UNIQUE KEY `commodities_PK` (`fruitid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `commodities` */
insert into `commodities`(`fruitid`,`name`,`price`,`locality`,`createtime`) values ('88e6ec6c-6d17-43a7-8782-38904ajskdh','黄桃',2.5,'浙江省金华市','2017-09-09 09:34:12'),('88e6ec6c-6d17-43a7-8782-48957ajskdf','火龙果',6.68,'浙江省绍兴市','2017-09-09 09:35:17'),('88e6ec6c-6d17-43a7-8782-89asdjh389a','橙子',0.4,'江苏省南京市','2017-09-09 09:36:44'),('88e6ec6c-6d17-43a7-8782-9534sd23h90','柚子',0.5,'上海市金山区','2017-09-09 09:37:51'),('88e6ec6c-6d17-43a7-8782-csrjdsk8347','猕猴桃',2.2,'上海市嘉定区','2017-09-09 09:38:03'),('88e6ec6c-6d17-43a7-8782-d1eae391234','苹果',0.7,'山东省青岛市','2017-09-09 09:39:22'),('88e6ec6c-6d17-43a7-8782-d1eae394106','橘子',1.6,'上海市普陀区','2017-09-09 09:40:11'),('88e6ec6c-6d17-43a7-8782-d1eae84dj46','香蕉',1.3,'江苏省昆山市','2017-09-09 09:41:23'),('88e6ec6c-6d17-43a7-8782-dhk327894aj','柿子',0.5,'江苏省苏州市','2017-09-09 09:42:52'),('88e6ec6c-6d17-43a7-8782-dijhksd2367','榴莲',13,'江苏省盐城县','2017-09-09 09:43:31'),('88e6ec6c-6d17-43a7-8782-hc2374gasd8','葡萄',2.3,'上海市马陆镇','2017-09-09 09:43:57'),('88e6ec6c-6d17-43a7-8782-jhk8340a783','荔枝',1.8,'江苏省无锡市','2017-09-09 09:44:05'),('88e6ec6c-6d17-43a7-8782-kduidfh3435','西瓜',0.3,'江苏省常州市','2017-09-09 09:45:14'),('88e6ec6c-6d17-43a7-8782-xce3940hsd4','芒果',3.6,'浙江省杭州市','2017-09-09 09:46:34');
/*Table structure for table `contract` */
DROP TABLE IF EXISTS `contract`;
CREATE TABLE `contract` (
`contractid` varchar(50) COLLATE utf8_bin NOT NULL,
`retailerid` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`barcode` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`contractid`),
UNIQUE KEY `contract_PK` (`contractid`),
KEY `关系1_FK` (`retailerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `contract` */
insert into `contract`(`contractid`,`retailerid`,`barcode`,`type`,`createtime`) values ('5636d4e7-36c3-4f5d-b11c-049c8c1f8945','351ab130-07c4-4a82-b713-8f71328111bc','201709250001',0,'2017-09-25 19:13:20');
/*Table structure for table `middle_tab` */
DROP TABLE IF EXISTS `middle_tab`;
CREATE TABLE `middle_tab` (
`middleid` varchar(50) COLLATE utf8_bin NOT NULL,
`contractid` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`fruitid` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`number` int(11) DEFAULT NULL,
PRIMARY KEY (`middleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `middle_tab` */
insert into `middle_tab`(`middleid`,`contractid`,`fruitid`,`number`) values ('9b17fc9c-3be8-47e1-af63-d393d51dcf2b','5636d4e7-36c3-4f5d-b11c-049c8c1f8945','88e6ec6c-6d17-43a7-8782-38904ajskdh',0),('b13bc528-db03-423f-a73b-839d2bb3b880','5636d4e7-36c3-4f5d-b11c-049c8c1f8945','88e6ec6c-6d17-43a7-8782-48957ajskdf',0),('b4219c06-3f5b-464a-860e-224fbdd2a1df','5636d4e7-36c3-4f5d-b11c-049c8c1f8945','88e6ec6c-6d17-43a7-8782-d1eae84dj46',0);
/*Table structure for table `retailer` */
DROP TABLE IF EXISTS `retailer`;
CREATE TABLE `retailer` (
`retailerid` varchar(50) COLLATE utf8_bin NOT NULL,
`name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`telphone` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`address` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`retailerid`),
UNIQUE KEY `retailer_PK` (`retailerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `retailer` */
insert into `retailer`(`retailerid`,`name`,`telphone`,`address`,`status`,`createtime`) values ('351ab130-07c4-4a82-b713-8f71328111bc','刘成成','13566666666','上海市黄浦区',1,'2017-09-09 09:31:17'),('45j8r40p-4fu7-87t4-8723-sdfjh789x907','石恩华','13777777778','上海市普陀区',1,'2017-09-02 12:33:40'),('88e6ec6c-6d17-43a7-8782-d1eae394d802','蒋虎子','13888888888','上海市嘉定区',1,'2017-09-02 12:31:20'),('90h7dv5c-9j87-24r6-9087-anune089x021','胡晓丽','15522222222','上海市闵行区',1,'2017-09-04 09:14:55'),('90h7dv5c-9j87-24r6-9087-anune089x096','蒋俊佳','13666666666','上海市宝山区',1,'2017-09-03 09:14:55'),('90h7dv5c-9j87-24r6-9087-anune089x294','施俊杰','13444444444','上海市徐汇区',1,'2017-09-04 09:16:55'),('90h7dv5c-9j87-24r6-9087-anune089x325','钱晓晓','15533333333','上海市长宁区',1,'2017-09-04 09:17:55'),('90h7dv5c-9j87-24r6-9087-anune089x365','王二小','13555555555','上海市杨浦区',1,'2017-09-04 09:18:55'),('90h7dv5c-9j87-24r6-9087-anune089x476','任宇','13222222222','上海市虹口区',1,'2017-09-04 09:19:55'),('90h7dv5c-9j87-24r6-9087-anune089x734','周佳','15566666666','上海市金山区',1,'2017-09-04 09:20:55'),('90h7dv5c-9j87-24r6-9087-anune089x921','张晓冉','15511111111','上海市奉贤区',1,'2017-09-04 09:21:55'),('90h7dv5c-9j87-24r6-9087-anune089x954','牛夏利','13333333333','上海市松江区',1,'2017-09-04 09:22:55'),('90h7dv5c-9j87-24r6-9087-anune089x978','刘浩','13111111111','上海市青浦区',1,'2017-09-04 09:23:55');
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`userid` varchar(50) COLLATE utf8_bin NOT NULL,
`username` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`password` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`telphone` varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`userid`),
UNIQUE KEY `user_PK` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `user` */
insert into `user`(`userid`,`username`,`password`,`name`,`telphone`) values ('3af57d0545766ec940d2c32a6567cc06ae5','jack','1234','张三','15588888888'),('734hdak3kfd389jgh3kll4590wejrh983jk','tom','4321','张茜茜','13888888888'),('88e6ec6c-6d17-43a7-8782-d1eae394d802','jackson','2222','刘霜','13782345627');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
*******************************************************************************************************************************************************************************************
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.6.24 : Database - mybatis_test
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mybatis_test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;
USE `mybatis_test`;
/*Table structure for table `batch` */
DROP TABLE IF EXISTS `batch`;
CREATE TABLE `batch` (
`batch_id` int(11) NOT NULL AUTO_INCREMENT,
`cus_id` int(11) NOT NULL COMMENT '创建批次用户id',
`number` varchar(32) NOT NULL COMMENT '批次编码',
`createtime` datetime NOT NULL COMMENT '创建批次时间',
`note` varchar(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`batch_id`),
KEY `FK_batch_1` (`cus_id`),
CONSTRAINT `FK_batch_id` FOREIGN KEY (`cus_id`) REFERENCES `customer` (`cus_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `batch` */
insert into `batch`(`batch_id`,`cus_id`,`number`,`createtime`,`note`) values (1,1,'00001','2017-07-22 00:00:00','首次购买'),(2,3,'00002','2017-03-11 00:00:00','委托购买');
/*Table structure for table `batchdetail` */
DROP TABLE IF EXISTS `batchdetail`;
CREATE TABLE `batchdetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`batch_id` int(11) NOT NULL COMMENT '批次id',
`product_id` int(11) NOT NULL COMMENT '理财产品id',
`product_num` int(11) DEFAULT NULL COMMENT '理财产品购买数量',
PRIMARY KEY (`id`),
KEY `FK_batchdetail_1` (`batch_id`),
KEY `FK_batchdetail_2` (`product_id`),
CONSTRAINT `FK_batchdetai_1` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_batchdetai_2` FOREIGN KEY (`product_id`) REFERENCES `finacial_products` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*Data for the table `batchdetail` */
insert into `batchdetail`(`id`,`batch_id`,`product_id`,`product_num`) values (1,1,1,2),(2,1,2,1),(3,1,3,1),(4,2,1,2),(5,2,2,1);
/*Table structure for table `customer` */
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`cus_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`acno` varchar(32) DEFAULT NULL COMMENT '卡号',
`gender` varchar(4) DEFAULT NULL COMMENT '性别',
`phone` varchar(256) DEFAULT NULL COMMENT '电话',
PRIMARY KEY (`cus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `customer` */
insert into `customer`(`cus_id`,`username`,`acno`,`gender`,`phone`) values (1,'刘云','6228286666666','男','13800000000'),(2,'李健','622848111111','男','13811111111'),(3,'张丽丽','622848333333','女','13822222222');
/*Table structure for table `finacial_products` */
DROP TABLE IF EXISTS `finacial_products`;
CREATE TABLE `finacial_products` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL COMMENT '理财产品名称',
`price` float(10,1) NOT NULL COMMENT '理财产品定价',
`detail` text COMMENT '理财产品描述',
`pic` varchar(64) DEFAULT NULL COMMENT '理财产品图片',
`invasttime` datetime NOT NULL COMMENT '理财产品收益日期',
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `finacial_products` */
insert into `finacial_products`(`product_id`,`name`,`price`,`detail`,`pic`,`invasttime`) values (1,'一起富',5000.0,'投资少，风险小','img001','2017-06-21 00:00:00'),(2,'惠薪富',10000.0,'收益稳健','img002','2017-05-03 00:00:00'),(3,'安富尊容',15000.0,'年收益率提升5%','img003','2017-07-18 00:00:00'),(4,'富津利',2000.0,'企划收益率','img004','2017-04-11 00:00:00');
/*Table structure for table `gameplayer` */
DROP TABLE IF EXISTS `gameplayer`;
CREATE TABLE `gameplayer` (
`id` int(110) DEFAULT NULL,
`name` varchar(225) COLLATE utf8_bin DEFAULT NULL,
`gender` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`level` int(110) DEFAULT NULL,
`ptype` int(110) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `gameplayer` */
insert into `gameplayer`(`id`,`name`,`gender`,`level`,`ptype`) values (1,'牛牛战士','男',31,1),(2,'芙蓉净月','女',22,2);
/*Table structure for table `magician_info` */
DROP TABLE IF EXISTS `magician_info`;
CREATE TABLE `magician_info` (
`range` int(110) DEFAULT NULL,
`power` int(110) DEFAULT NULL,
`gpid` int(110) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `magician_info` */
insert into `magician_info`(`range`,`power`,`gpid`) values (213,210,2);
/*Table structure for table `pro_mapping_usr` */
DROP TABLE IF EXISTS `pro_mapping_usr`;
CREATE TABLE `pro_mapping_usr` (
`pid` int(110) DEFAULT NULL,
`uid` int(110) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `pro_mapping_usr` */
insert into `pro_mapping_usr`(`pid`,`uid`) values (1,1),(1,2),(1,3),(1,4);
/*Table structure for table `product` */
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` int(110) NOT NULL AUTO_INCREMENT,
`productName` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*Data for the table `product` */
insert into `product`(`id`,`productName`) values (1,'apple');
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(120) COLLATE utf8_bin DEFAULT NULL,
`password` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`gender` varchar(5) COLLATE utf8_bin DEFAULT NULL,
`email` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`province` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`city` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`birthday` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `user` */
insert into `user`(`id`,`username`,`password`,`gender`,`email`,`province`,`city`,`birthday`) values (1,'张三','111','男','zhangsan@126.com','河南省','郑州市','1991-04-23'),(2,'李四','222','男','2222@126.com','河北省','邯郸市','1989-10-13'),(3,'刘丽','333','女','3333@126.com','江苏省','苏州市','1994-06-09'),(4,'孙丽','444','女','4444@126.com','四川省','成都市','1992-11-07'),(6,'李磊磊','123qwe','男','lileilei@126.com','云南','大理','1992-01-01');
/*Table structure for table `users` */
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(110) NOT NULL AUTO_INCREMENT,
`username` varchar(250) DEFAULT NULL,
`gender` varchar(250) DEFAULT NULL,
`email` varchar(250) DEFAULT NULL,
`pid` int(110) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `users` */
insert into `users`(`id`,`username`,`gender`,`email`,`pid`) values (1,'jack','man','111@126.com',1),(2,'tom','man','222@126.com',1),(3,'jean','woman','333@126.com',1),(4,'blus','man','444@126.com',1);
/*Table structure for table `warrior_info` */
DROP TABLE IF EXISTS `warrior_info`;
CREATE TABLE `warrior_info` (
`svalue` int(110) DEFAULT NULL,
`power` int(110) DEFAULT NULL,
`gpid` int(110) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `warrior_info` */
insert into `warrior_info`(`svalue`,`power`,`gpid`) values (330,420,1);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
posted on 2018-11-21 20:52 Indian_Mysore 阅读(357) 评论(0) 编辑 收藏 举报
