【SQL开源代码栏目提醒】:网学会员鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“CH12.sql”一文,供大家参考学习
USE 练习01
-- p12-2a
CREATE TABLE 员工薪资
(
编号 int IDENTITY PRIMARY KEY,
薪资 smallmoney,
CHECK (薪资 > 0 AND 薪资 <= 50000)
)
--p12-2b
CREATE TABLE 经销商
(
编号 int IDENTITY PRIMARY KEY,
char(20) NOT NULL,
地址 char(50),
电话 char(13),
CHECK (地址 IS NOT NULL OR 电话 IS NOT NULL)
)
-- P12-4a
CREATE RULE Price_rule
AS @price >= 1 AND @price <= 50000
-- P12-4b
CREATE RULE charset_rule
AS @charset LIKE 'F0[1-9][1-9]-[A-E]_'
-- P12-4c
CREATE RULE Gender_rule
AS @gender IN ('男', '女')
-- P12-5
CREATE RULE payday_rule
AS @payday >= getdate()
-- P12-6
EXEC sp_bindrule Gender_rule, '员工.性别'
-- P12-7
EXEC sp_unbindrule '员工.性别'
-- P12-8
DROP RULE Price_rule, Gender_rule
-- P12-10a
CREATE DEFAULT 性别_df
AS '男'
-- P12-10b
CREATE DEFAULT 地点_df
AS '台湾地区'
-- P12-11a
EXEC sp_bindefault 性别_df, '员工.性别'
-- P12-11b
EXEC sp_unbindefault '员工.性别'
-- P12-12
DROP DEFAULT 地点_df
-- P12-15a
EXEC sp_addtype Phone, 'char(12)', 'NOT NULL'
-- P12-15b
EXEC sp_addtype PayDay, datetime, 'NULL'
-- P12-18a
EXEC sp_droptype Phone
-- P12-18b
EXEC sp_droptype PayDay