SQL2012_创建数据库，创建表，表的内外链接
create table a1(
aid int constraint PK_a primary key,
age int check(age between 27 and 80),
sex int check(sex in('M','F')),
tel nvarchar(20) check(tel like'1[3,5,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
create table b2(
f nvarchar(10)
)
create rule R as @f like 'Y%'
sp_bindrule 'R','b2.f'
insert into b2 values('you')
select * from b2
sp_unbindrule 'b2.f'
drop rule R
create type HAHA from int not null
create table F(hj HAHA )
insert into F values(23)
create synonym HIHI for AdventureWorks.HumanResources.Department
select * from HIHI
create partition function GG(int) as range left for values(30,70,90)
create partition function JK(date) as range right for values('2001-09-09','2003-06-09')
---------------------------------
if exists(select * from sys.databases where name='SchDB')
drop database SchDB
create database SchDB on primary(
name='S_DB',filename='D:\S_DB.mdf',size=5mb,maxsize=100mb,filegrowth=10%
),( name='S_DB_N_1',filename='D:\S_DB_N_1.ndf',size=1mb,maxsize=50mb,filegrowth=1mb)
,( name='S_DB_N_2',filename='D:\S_DB_N_2.ndf',size=1mb,maxsize=50mb,filegrowth=1mb)
,filegroup XXX( name='S_DB_N_3',filename='D:\S_DB_N_3.ndf',size=1mb,maxsize=50mb,filegrowth=1mb)
,( name='S_DB_N_4',filename='D:\S_DB_N_4.ndf',size=1mb,maxsize=50mb,filegrowth=1mb)
,( name='S_DB_N_5',filename='D:\S_DB_N_5.ndf',size=1mb,maxsize=50mb,filegrowth=1mb)
go
alter database HEIHEI add filegroup FG_New
alter database HEIHEI add file(name='S_DB_N_7',filename='D:\S_DB_N_7.ndf')to filegroup FG_New
alter partition scheme SC next used FG_New
alter partition function GG() split range(90)
use AdventureWorks
select * from HumanResources.Employee
select e.EmployeeID,e.Title,m.ManagerID,m.Title
from HumanResources.Employee e join HumanResources.Employee m
on e.EmployeeID=m.EmployeeID
select e.EmployeeID,e.Title,m.ManagerID,m.Title
from HumanResources.Employee e , HumanResources.Employee m
where e.EmployeeID=m.EmployeeID
select * from HumanResources.Employee
select * from HumanResources.EmployeeDepartmentHistory
select * from HumanResources.Department
select e.BirthDate,h.PayFrequency,d.Name
from HumanResources.Employee e join HumanResources.EmployeeDepartmentHistory g
on e.EmployeeID=g.EmployeeID join HumanResources.Department d on
g.DepartmentID=d.DepartmentID join HumanResources.EmployeePayHistory h
on d.DepartmentID=h.EmployeeID
---!eid-20 ?eid
SELECT DISTINCT DepartmentID FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN--返回多个结果的时候我们使用 in
(
select EmployeeID from HumanResources.Employee where Title =--返回一个结果的时候 使用的是=
(
SELECT TITLE FROM HumanResources.Employee WHERE EmployeeID=20
)
)
select EmployeeID eid ,Title T into Emp from HumanResources.Employee
select * from Emp
select eid ,T from Emp r
cross apply
(
select * from HumanResources.Employee f where r.eid=f.EmployeeID
) h
------
create table D_tbl(
c_name nvarchar(20) not null,
A_num int not null
)
insert into D_tbl output inserted.* values('Nora',101),
('Bobin',103),('James',107),('Jennifer',109)
create table B_tbl(
c_name nvarchar(20) not null,
L_num int not null
)
insert into B_tbl output inserted.* values('Nora',301),
('Bobin',305),('James',306),('Jenn',308)
select d.c_name,d.A_num,bt.L_num from D_tbl d
cross apply(select * from B_tbl b where d.c_name=b.c_name) bt
select d.c_name,d.A_num,bt.L_num from D_tbl d
outer apply(select * from B_tbl b where d.c_name=b.c_name) bt
select * from D_tbl
union all
select * from B_tbl
select * from D_tbl
union
select * from B_tbl
select * from D_tbl
except
select * from B_tb
-----------------
.mdf n----.ndf -----3--filegroup