sql server建庫、建表、建束縛技能

sql server建庫、建表、建束縛技能。本站提示廣大學習愛好者：（sql server建庫、建表、建束縛技能）文章只能為提供參考，不一定能成為您想要的結果。以下是sql server建庫、建表、建束縛技能正文

---

公司一個項目須要上傳圖片,一開端同事將圖片上傳後聯合以後主機拼成了一個相對的URL(http://192.168.1.1:888/m/getimg?filename=xxx.jpg因為同時給手機終端供給接口,在手機終端會惹起一些bug,改完代碼後請求將之前的uri調換成絕對的URL(/m/getimg?filename=xxx.jpg),因為圖片是用img標簽嵌入到內容同時用a標簽括起顯示年夜圖的,所以須要讀取數據庫並對內容停止調換,

劇本內容以下:

#!/usr/bin/env python
#-*- coding:utf-8 -*-
#
#
# author : cold night
# email  : [email protected]
#

import pymongo
import re
from StringIO import StringIO

conn = pymongo.Connection()
db = conn.test

def replace_url():
  regex = re.compile(r'([href|src])=["|\']http://.*?(/m/getimg\?.*?)["|\']')
  results = db['test'].find()
  db_coll = db['test']
  def replace(r):
    content = r.get('content')
    if not content: return
    content = StringIO(content)
    content.seek(0)
    result = StringIO()
    for line in content.readlines():
      t = regex.sub(r'\1="\2"', line)
      result.write(t)

    result.seek(0)
    content = result.read()
    if content:
      r['content'] = content
    _id = r.get('_id')
    db_coll.update({'_id':_id}, r)

  results = [replace(i) for i in results]

if __name__=="__main__":replace_url()

20MB, maxsize = 100MB, filegrowth = 1MB ) log on ( --第一個日記文件-- name = 'employee_log1', filename = 'D:\project\employee_log1.ldf', size = 10MB, filegrowth = 1MB ), ( --第二個日記文件-- name = 'employee_log2', filename = 'D:\project\employee_log2.ldf', size = 10MB, maxsize = 50MB, filegrowth = 1MB )

---------------------------------查詢已存在的數據庫信息---------------------------

select * from sysdatabases

---------------------------------刪除數據庫------------------------------------

drop database School

---------------------------------創立Student數據庫表----------------------------

--1、選擇操作的數據庫--
use School
go

--斷定表能否存在--

if exists(select * from sysobjects where name = 'Student')
drop table Student

--2、創立表---

create table Student
(
--詳細的列名 數據類型 列的特點(能否為空)--
StudentNo int identity(2,1) not null,
LoginPwd nvarchar(20) not null,
StudentName nvarchar(20) not null,
Sex int not null,
GradeId int not null,
phone nvarchar(50) not null,
BornDate datetime not null,
Address nvarchar(255),
Email nvarchar(50),
IDENTITYcard varchar(18)
)
go

---檢查一切數據庫對象(數據庫表)---

select * from sysobjects
drop table Student

----------------------創立subject課程表-------------------

-----1、斷定表能否存在；若存在則刪除再創立，若不存在則直接創立--------
if exists(select * from sysobjects where name = 'subject')
drop table subject

use School
go

---創立subject課程表--
create table subject
(
SubjectNo int not null identity(1,1),
SubjectName nvarchar(50),
ClassHour int,
GradeID int
)

----------------------------------------創立Result成就表-------------------

-----1、斷定表能否存在；若存在則刪除再創立，若不存在則直接創立--------
if exists(select * from sysobjects where name = 'Result')
drop table Result
use School
go

---創立Result成就表--

create table Result
(
StudentNo int not null,
SubjectNo int not null,
ExamDate Datetime not null,
StudentResult int not null
)

-----------------------------------------創立Grande年級表-------------------

-----1、斷定表能否存在；若存在則刪除再創立，若不存在則直接創立--------
if exists(select * from sysobjects where name = 'Grade')
drop table Grade
use School
go

---創立Grande年級表--

create table Grade
(
GradeId int not null,
GrandeName nvarchar(50)
)

-----------------------------------------T-SQL添加束縛-------------------------

--給StudentNo添加主鍵束縛---
alter table Student
add constraint pk_StuNo primary key(StudentNo)

--給身份證添加獨一束縛--

alter table Student
add constraint uq_StuIdcard unique(IDENTITYcard)

---給地址address添加默許束縛--

alter table Student
add constraint df_stuaddress default('地址不詳') for Address

---刪除地址address默許束縛---

alter table Student
drop constraint df_stuaddress

----------出身日期添加檢討束縛--------

alter table Student
add constraint ck_stuBorndate check(Borndate > '1980-01-01')

---------與Grand（年級表）樹立主外鍵關系--------

--1、添加Grade主鍵(操作Grade)---

alter table Grade
add constraint pk_graid primary key(GradeId)

--2、添加Grade外鍵(操作Student)--

alter table Student
add constraint fk_stuGradeID foreign key(GradeId) references Grade(GradeId)

-------------------給subject課程表添加束縛-----------------------

----給subjectNo列添加主鍵束縛------
alter table subject
add constraint pk_SubID primary key(SubjectNo)

------給課程稱號subjectName添加非空束縛；-----

-----with nocheck：曾經存在數據欠亨過check束縛-------
alter table subject with nocheck
add constraint ck_subName check(SubjectName is not null)

-----學時必需年夜於0-----

alter table subject with nocheck
add constraint ck_ClassHour check(ClassHour > 0)

-----與Grade年級表添加主外鍵束縛----

alter table subject with nocheck
add constraint fk_GradeID foreign key(GradeID)
references Grade(GradeID)

----------給result成就表添加束縛------------

-------添加多個束縛---------

alter table Result
add
constraint pk_No_subID_date primary key(StudentNo,SubjectNo,ExamDate),
constraint df_examdate default(getdate()) for ExamDate,
constraint ck_StudentResult check(StudentResult between 0 and 100),
constraint fk_StuNo foreign key(StudentNo) references Student(StudentNo),
constraint fk_subNo foreign key(SubjectNo) references Subject(SubjectNo)

--刪除多個束縛--

alter table Result
drop constraint pk_No_subID_date,fk_subNo,fk_StuNo,ck_StudentResult,df_examdate

--------更改列的數據類型----------

alter table Result
alter column StudentResult int

以上就是本文全體內容，願望年夜家愛好。