createdb.sql

来自「学习参考,java基本语法练习,包括一些常用的技巧」· SQL 代码 · 共 42 行

--创建数据库HappyChat
CREATE DATABASE HappyChat
GO
USE HappyChat
--创建用户信息表
CREATE TABLE users
(
	U_Name VARCHAR(16) PRIMARY KEY,
	U_Pass VARCHAR(16) NOT NULL,
	U_Gender CHAR(2) NOT NULL,
	U_Age INT NOT NULL,
	U_Email VARCHAR(20) NOT NULL
)
--创建聊天记录表
CREATE TABLE history
(
	H_No INT IDENTITY(1,1),
	H_User VARCHAR(16) NOT NULL,
	H_Time DATETIME NOT NULL,
	H_Content VARCHAR(60)
)

--插入记录到Users表
INSERT users VALUES('liuzc518','liuzc518','男',33,'[e-mail]')
INSERT users VALUES('liuzc','liuzc','男',23,'[e-mail]')
INSERT users VALUES('wangym','wangym0806','女',30,'[e-mail]')
INSERT users VALUES('liujin','liujin','女',16,'[e-mail]')
--插入记录到History表
INSERT history(H_User,H_Time,H_Content) VALUES('liuzc','2006-3-3 18:00','晚上好')
INSERT history(H_User,H_Time,H_Content) VALUES('wangym','2006-3-3 18:03','你好')
INSERT history(H_User,H_Time,H_Content) VALUES('liuzc','2006-3-3 18:10','呵呵')
INSERT history(H_User,H_Time,H_Content) VALUES('liuzc','2006-3-3 19:20','是吗?挺好的')
INSERT history(H_User,H_Time,H_Content) VALUES('liuzc','2006-3-3 19:25','晚安')
INSERT history(H_User,H_Time,H_Content) VALUES('wangym','2006-3-3 19:30','88')
--创建根据输入用户名删除指定记录的存储过程
CREATE PROC delRecord
@name CHAR(16)
AS
DELETE FROM Users 
WHERE U_name=@name