mysql与sqlserver 数据库的编写
来源:互联网 发布:淘宝网经营模式 编辑:程序博客网 时间:2024/05/21 11:00
sqlserver:
BEGIN
DECLARE @db NVARCHAR(255);
SET @db = N'db_bbs';
USE master;
-- Create the database if it doesn't exist.
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db)
EXECUTE('CREATE DATABASE ' + @db);
-- Uncomment to create with case-sensitive collation
--IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db)
-- EXECUTE('CREATE DATABASE ' + @db + ' COLLATE Latin1_General_BIN');
-- Make sure columns default to NULL-able.
EXECUTE('ALTER DATABASE ' + @db + ' SET ANSI_NULL_DEFAULT ON');
END;
GO
USE db_bbs;
GO
--
-- Drop existing tables.
--
BEGIN
-- First, drop foreign key constraints, so we can drop the tables.
DECLARE fkcursor CURSOR FOR
SELECT fk.name, parent.name parent
FROM dbo.sysobjects fk JOIN dbo.sysobjects parent ON fk.parent_obj = parent.id
WHERE OBJECTPROPERTY(fk.id, N'IsForeignKey') = 1 AND parent.name LIKE 'Ae%'
DECLARE @name NVARCHAR(255)
DECLARE @parent NVARCHAR(255)
OPEN fkcursor
FETCH NEXT FROM fkcursor INTO @name, @parent
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('ALTER TABLE ' + @parent + ' DROP CONSTRAINT ' + @name)
FETCH NEXT FROM fkcursor INTO @name, @parent
END
CLOSE fkcursor
DEALLOCATE fkcursor
-- Now drop the tables.
DECLARE tbcursor CURSOR FOR
SELECT name
FROM dbo.sysobjects
--WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 AND name LIKE 'Ae%'
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
OPEN tbcursor
FETCH NEXT FROM tbcursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP TABLE ' + @name)
FETCH NEXT FROM tbcursor INTO @name
END
CLOSE tbcursor
DEALLOCATE tbcursor
END
GO
create table tb_user(
id int IDENTITY(1,1) primary key,
username varchar(20) not null,
password varchar(20) not null,
sex varchar(2) not null,
email varchar(50) not null,
oicq varchar(20) default null,
signature varchar(300) default null,
grade varchar(20) default null,
lxdz varchar(50),
tx varchar(30),
grzy varchar(50),
realname varchar(30)
);
insert into tb_user(username,password,sex,email,oicq,signature,grade,lxdz,tx,grzy,realname) values('TSoft','111','1','xiaoyu*****@sina.com','123','test','admin','test','2.gif','test','test');
create table tb_forum(
id int IDENTITY(1,1) primary key,
forumname varchar(20) not null,
manager varchar(100) default null,
createtime timestamp
);
insert into tb_forum(forumname,manager)values('ASP','fish');
insert into tb_forum(forumname,manager)values('PHP','fish');
insert into tb_forum(forumname,manager)values('C#','fish');
insert into tb_forum(forumname,manager)values('.NET','fish');
insert into tb_forum(forumname,manager)values('VB','fish');
insert into tb_forum(forumname,manager)values('JSP','fish');
create table tb_topic(
id int IDENTITY(1,1) primary key,
content text,
author varchar(20) not null,
submittime timestamp ,
forumid int default 0,
title varchar(300) not null,
xq varchar(30) not null,
rq int default 0,
forumname varchar(20)
);
create table tb_response(
id int IDENTITY(1,1) primary key,
title varchar(300) not null,
content text,
author varchar(20) not null,
submittime timestamp ,
topicid int not null,
topicname varchar(100),
xq varchar(20) not null
);
mysql:
create database db_bbs;
use db_bbs;
#用户信息表
create table tb_user(
id int(11) auto_increment primary key not null,
username varchar(20) not null,
password varchar(20) not null,
sex varchar(2) not null,
email varchar(50) not null,
oicq varchar(20) default null,
signature varchar(300) default null,
grade varchar(20) default null,
lxdz varchar(50),
tx varchar(30),
grzy varchar(50),
realname varchar(30)
);
insert into tb_user(username,password,sex,email,oicq,signature,grade,lxdz,tx,grzy,realname) values('TSoft','111','1','xiaoyu*****@sina.com','123','test','admin','test','2.gif','test','test');
#论坛信息表
create table tb_forum(
id int(11) auto_increment primary key not null,
forumname varchar(20) not null,
manager varchar(100) default null,
createtime timestamp default current_timestamp
);
insert into tb_forum(forumname,manager)values('ASP','fish');
insert into tb_forum(forumname,manager)values('PHP','fish');
insert into tb_forum(forumname,manager)values('C#','fish');
insert into tb_forum(forumname,manager)values('.NET','fish');
insert into tb_forum(forumname,manager)values('VB','fish');
insert into tb_forum(forumname,manager)values('JSP','fish');
#主题信息表
create table tb_topic(
id int(4) auto_increment primary key not null,
content text,
author varchar(20) not null,
submittime timestamp(8) default current_timestamp,
forumid int(4) default 0,
title varchar(300) not null,
xq varchar(30) not null,
rq int(4) default 0,
forumname varchar(20)
);
#insert into tb_topic(content,author,submittime,forumid,title)values('测试','快饿死的鱼','2007-08-20','1','测试');
#回复信息表
create table tb_response(
id int(11) not null auto_increment primary key,
title varchar(300) not null,
content text,
author varchar(20) not null,
submittime timestamp default current_timestamp,
topicid int(4) not null,
topicname varchar(100),
xq varchar(20) not null
);
- mysql与sqlserver 数据库的编写
- 连接SqlServer数据库与mysql数据库的区别
- SqlServer 与MySQL的区别?
- Mysql与Sqlserver的区别
- Mysql与SqlServer的不同
- SQLServer与MySQL的比较
- mysql sqlserver oracle数据库的关键字处理
- 连接mysql,oracle,sqlServer数据库的方式
- MYSQL同步Sqlserver数据库数据的方法
- MySQL、SqlServer、Oracle数据库之间的差异
- mysql、sqlServer、hsql、oracle、db2各数据库支持的字段类型与最大精度
- 常见数据库Oracle,sqlserver,mysql字段类型与java.sql.Types的对应
- sqlserver oracle mysql数据库
- SQLServer数据库的备份与还原
- SQLServer数据库的备份与还原
- SQLServer与Access数据库的判别方法
- sqlserver数据库的备份与还原
- SqlServer数据库的备份与还原
- 错序排列公式
- 数据库和表
- 归并排序的实现
- 颠倒栈
- 自己实现的动态数组(java.util.Vector)
- mysql与sqlserver 数据库的编写
- 关于native方法
- 写给技术合伙人
- 全组合(可包含重复字符)
- -------汇编语言--标志寄存器--转移指令---
- java回车换行说明
- 设计模式概论
- [心情]毕业那几天
- CListCtrl派生类