CREATE DATABASE MyQQ
ON PRIMARY
(NAME=MyQQ_data,
FILENAME='E:\Professional flie\SQL file\database\MyQQ\MyQQ_data.mdf',
SIZE=5MB,
MAXSIZE=15MB,
FILEGROWTH=20%)
LOG ON
(NAME=MyQQ_log,
FILENAME='E:\Professional flie\SQL file\database\MyQQ\MyQQ_log.ldf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
go
CREATE TABLE Users
(
Id int constraint pk_users Primary key,
LoginPWD char(6) not null,
NickName char(20) ,
Sex char(2),
Star char(6),
BloodTypeId int not null,
)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(1,'0000','豆豆','男','白羊座',1)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(2,'00A0','小强','男','双子座',4)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(3,'0000','静静','女','双子座',2)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(4,'00B0','.NET','男','巨蟹座',3)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(5,'0CC0','bobo','女','狮子座',1)
CREATE TABLE Blood
(
id int,
BloodType char(4),
)
insert into Blood
(id,BloodType)
values
(1,'A型')
insert into Blood
(id,BloodType)
values
(1,'B型')
insert into Blood
(id,BloodType)
values
(1,'O型')
insert into Blood
(id,BloodType)
values
(1,'AB型')
SELECT NickName 姓名, LoginPWD 密码
FROM Blood INNER JOIN Users
ON blood.id = Users.BloodTypeId
WHERE Star = '狮子座' AND BloodType = 'A型' AND Sex = '女'
SELECT NickName 姓名, Sex 性别
FROM Blood INNER JOIN Users
ON Blood.id = Users.BloodTypeId
WHERE BloodType='A型'
CREATE PROC Star
AS
SELECT *
FROM Blood INNER JOIN Users
ON Blood.id=Users.Id
WHERE BloodType='A型 '
EXEC dbo.Star
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name ='proc_star1')
DROP PROC proc_Star
ALTER PROCEDURE proc_Star
@star VARCHAR(50)
AS
SELECT NickName 姓名,Sex 性别
FROM Users
WHERE Star IN (SELECT Star
FROM Users
WHERE Star=@star)
EXEC proc_Star '双子座'