sql:Mysql create view,function,procedure

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270

create database Liber;   use Liber;   #顯示數据庫 20150210 Geovin Du 涂聚文 SHOW DATABASES;   drop table BookKindList; #书目录 create table BookKindList (     BookKindID INT NOT NULL AUTO_INCREMENT, #自动增加     BookKindName nvarchar(500) not null,     BookKindParent int null,    PRIMARY KEY(BookKindID)  #主键 );   #这样也可以 create table BookKindList (     BookKindID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, #自动增加#主键     BookKindName nvarchar(500) not null,     BookKindParent int null    );   #书位置 create table BookPlaceList (     BookPlaceID INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,     BookPlaceName nvarchar(500) not null,       BookPlaceParent int null       );   #书系列Series或套名称（一本的0.无，有分上下本） drop table BookSeriesList;   create table BookSeriesList (     BookSeriesID INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,     BookSeriesName nvarchar(500) not null ); #職位Position create table PositionList (     PositionID INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,     PositionName nvarchar(500) not null );   #部門Department  ShortPY create table DepartmentList (     DepartmentID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     DepartmentName nvarchar(500) not null );   insert into DepartmentList(DepartmentName) values ('行政部'); insert into DepartmentList(DepartmentName) values ('资讯部');   select * from DepartmentList;   #語种 Language create table LanguageList (     LanguageID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     LanguageName nvarchar(500) not null );   #出版社Press #拼音索引 create table PressList (     PressID INT  NOT NULL PRIMARY KEY AUTO_INCREMENT,     PressName nvarchar(500) not null       );   #作家Author create table AuthorList (     AuthorID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     AuthorName nvarchar(500) not null );   #BookStatus 书藉存在状态（1，在用，2，报废，3。转移） create table BookStatusList (     BookStatusID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     BookStatusName nvarchar(500) not null );   #借阅状态：借出，续借，归还，预借Lend, Renewal, Restitution,Reservations create table LendStatusList (     LendStatusID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     LendStatusName nvarchar(500) not null );       #书信息 create table BookInfoList (     BookInfoID  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,#自动增加#主键     BookInfoKind int not null,                                      #书籍类型外键     BookInfoPlace int not null,                                 #放置位置外键   BookInfoISBN varchar(50) not null,                #书籍ISBN编码     BookInfoBarCode varchar(60) not null,           #管理条码(barcode)     BookInfoName nvarchar(500) not null,            #书名     BookInfoSeries  int default 1,                  #书系列 ,0為無係列     BookInfoAuthor int null,                                #作者     BookInfoPress int null,                                 #出版社     BookInfoLanguage int null,                              #语种     BookInfoPublish datetime ,                  #出版时间     BookInfoImage text null,                                #封面图片     BookInfoStatus int default 1 not null,          #书藉状态（1，在用（在库），2，报废，3。转移）     BookInfoRemarks text null,                              #备注     BookInfoOperatorId int null,                        #操作人员ID     BookInfoAddDate datetime not null,      #添加時間 可不以默认时间DEFAULT CURDATE()     BookInfoPrice float default 1.00,                   #书价格   BookUseCode varchar(100)       );   desc BookKindList;#查询表结构   show tables;#查询所有表     select * from BookKindList; #查询   insert into BookKindList(BookKindName,BookKindParent)values('六福书目录',0);   insert into BookKindList(BookKindName,BookKindParent)values('文学',1);   insert into BookKindList(BookKindName,BookKindParent)values('科学技术',1);   /*自定义函数*/ #部门函数 DELIMITER $$ DROP FUNCTION IF EXISTS `geovindu`.`f_GetDepartmentName` $$ CREATE FUNCTION `geovindu`.`f_GetDepartmentName` (did int) RETURNS varchar(100) BEGIN declare str varchar(100); return(select DepartmentName from DepartmentList where DepartmentID=did); END $$ DELIMITER ;   #使用函数 select f_GetDepartmentName(1);   select * from BookInfoList; #作家函数   DELIMITER $$ DROP FUNCTION IF EXISTS `geovindu`.`f_GetAuthorName` $$ CREATE FUNCTION `geovindu`.`f_GetAuthorName` (did int) RETURNS varchar(400) BEGIN    declare str varchar(100); return(select AuthorName from AuthorList where AuthorID=did); END $$ DELIMITER ;   /*视图*/ select * from geovindu.views;   desc View_BookInfoList;   show create view View_BookInfoList;     select * from View_BookInfoList;   CREATE VIEW `geovindu`.`View_BookInfoList` AS   select BookInfoID , BookInfoKind , BookInfoPlace ,BookInfoSeries , BookInfoAuthor , BookInfoPress , BookInfoLanguage , BookInfoStatus , BookInfoOperatorId ,  BookInfoISBN , BookInfoBarCode , BookInfoName ,    BookInfoRemarks ,BookInfoAddDate,BookInfoPublish ,BookInfoPrice, BookKindList.BookKindName,BookPlaceList.BookPlaceName,f_GetAuthorName(BookInfoAuthor) from BookInfoList,BookKindList,BookPlaceList where BookInfoList.BookInfoKind=BookKindList.BookKindID  and BookInfoList.BookInfoPlace=BookPlaceList.BookPlaceID;       /*储存过程 解决方案的思维模式基本相同，只是一些指令不同*/   #IN 表示输入参数 #OUT表示输出参数 #INOUT:表示即可以输入参数也可以输出参数 #存储过程 利用mysql-query-browser创建存储过程和函数   #删除 DELIMITER $$ DROP PROCEDURE IF EXISTS `geovindu`.`DeleteBookKind` $$ CREATE PROCEDURE `geovindu`.`DeleteBookKind` (IN param1 INT) BEGIN          Delete From bookkindlist WHERE BookKindID  = param1; END $$ DELIMITER ;   #查询所有 DELIMITER $$ DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindListAll()` $$ CREATE PROCEDURE `geovindu`.`proc_Select_BookKindListAll` () BEGIN     SELECT * FROM bookkindlist; END $$ DELIMITER ;     select * from  `geovindu`.`bookkindlist`; SELECT * FROM bookkindlist;   #统计 DELIMITER $$ DROP PROCEDURE IF EXISTS `geovindu`.`BookKindCount` $$ CREATE PROCEDURE `geovindu`.`BookKindCount` (OUT param1ID INT) BEGIN         select COUNT(*) into param1ID  From bookkindlist; END $$ DELIMITER ;   #更新 DELIMITER $$ DROP PROCEDURE IF EXISTS `geovindu`.`proc_Update_BookKindList` $$ CREATE PROCEDURE `geovindu`.`proc_Update_BookKindList` (IN param1ID Int,IN param1Name NVarChar(1000),IN param1Parent Int) BEGIN IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then #如果存在相同的记录，不更新名称 UPDATE BookKindList     SET         BookKindName=param1Name ,         BookKindParent=param1Parent     where         BookKindID=param1ID; ELSE     UPDATE BookKindList     SET BookKindParent=param1Parent     where         BookKindID=param1ID; END IF; END $$ DELIMITER ;     #查询一条 DELIMITER $$ DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindList` $$ CREATE PROCEDURE `geovindu`.`proc_Select_BookKindList` (IN param1 INT) BEGIN         SELECT * FROM BookKindList WHERE BookKindID = param1; END $$ DELIMITER ;   #插入一条 DELIMITER $$ DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindList` $$ CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindList` (IN param1Name NVarChar(1000),IN param1Parent Int) BEGIN         insert into BookKindList(BookKindName,BookKindParent) values(param1Name,param1Parent); END $$ DELIMITER ;   #插入一条返回值 DELIMITER $$ DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindOut` $$ CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindOut` (IN param1Name NVarChar(1000),IN param1Parent Int,OUT ID INT) BEGIN      IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then   #如果存在相同的记录，不添加         INSERT INTO BookKindList (BookKindName,BookKindParent)VALUES(param1Name ,param1Parent);         #set ID=Last_insert_id()         SELECT LAST_INSERT_ID() into ID;       end if; END $$ DELIMITER ;