mysql中的用户定义表 [英] user defined tables in mysql

问题描述

大家好，
我是mysql的新手，是否可以像在sql server中一样在mysql中创建用户定义的表.
如果可以的话，你能给我一个例子吗?

这是我的桌子

Hi all,
I''m new to mysql,Can we create user defined tables in mysql as we do in sql server.
if possible can u provide me an example.

This is my table

CREATE TABLE `mstschoolinformation` (
  `Schoolid` int(11) NOT NULL,
  `SchoolName` varchar(100) CHARACTER SET utf8 NOT NULL,
  `RegnNo` varchar(30) CHARACTER SET utf8 NOT NULL,
  `Regdby` varchar(100) CHARACTER SET utf8 NOT NULL,
  `Establishedon` datetime NOT NULL,
  `address` varchar(300) CHARACTER SET utf8 NOT NULL,
  `PrimaryorHighSchool` bit(1) DEFAULT NULL,
  PRIMARY KEY (`Schoolid`)

这是我有多个联系信息的联系表

this is the contact table where i have multiple contact information

create table ContactDetails
(
Schoolid int,
contactno nvarchar(20))

在学校信息表中插入信息的存储过程如下

the stored procedure to insert the inforamtion in the schoolinformation table is the following

CREATE PROCEDURE `MstSchoolInformation_Insert_Update`(
In SchoolId int,
In SchoolName nvarchar(100),
In RegnNo nvarchar(100),
In RegdBy nvarchar(100),
In EstablishedOn datetime,
In address nvarchar(300),
In PrimaryorHighSchool bit,
In typeoffunction char
)
begin	
DECLARE MAXID INT;
SELECT ifnull(max(SchoolId),"0")+1 INTO MAXID FROM MSTSCHOOLINFORMATION;
        IF typeoffunction=''I'' THEN
                    insert into MstSchoolInformation 
                     (SchoolId,
                        SchoolName,
                        RegnNo,
                        RegdBy,
                        EstablishedOn,
                        address,
                        PrimaryorHighSchool) values 
                        (MAXID,
                        SchoolName,
                        RegnNo,
                        RegdBy,
                        EstablishedOn,
                        address,
                        PrimaryorHighSchool);		
 
/* here i will insert the contact info if can be passed as udt like sql*/
        ELSEIF typeoffunction=''U'' THEN
                /*SET TRANSACTION ISOLATION LEVEL Repeatable read
                start transaction*/
                    
                        update MstSchoolInformation  set SchoolName=SchoolName,
                            RegnNo=RegnNo,
                            RegdBy=RegdBy,
                            EstablishedOn=EstablishedOn,
                            address=address,
                            PrimaryorHighSchool=PrimaryorHighSchool 
                        where SchoolId=SchoolId;
                    
                /*commit;*/
        END IF;
end

在此先谢谢您

Thanks in advance

推荐答案

是的.搜索Google，您将看到很多示例.祝你好运！

Yes you can. Search Google and you will see a lot of examples. Good luck!

是的.. plzz分享你的数据库，我将帮助你！

yes .. plzz share ur Database nd i will help u!

这篇关于mysql中的用户定义表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！