SQL Server基础SQL脚本之创建架构、排序

--一，创建数据库 。
create database XXXDB--创建数据库
use XXXDB--使用数据库
create table Department(--创建表
dept_id nvarchar(20) not null,--列与列之间以逗号隔开
dept_name nvarchar(20) not null,
    dept_head nvarchar(20) not null
)
select * from Department--查询表中的数据
insert into Department values('D001','Marketing','johnS')--插入数据
    ,('D002','Accounts','Tony.D')
--delete Department where dept_id='D001'--删除指定的列数据
--创建表
create table Employee(
    EMP_ID nvarchar(20) not null,
    EMP_NAME nvarchar(20) not null,
    ADDRESS nvarchar(20) not null,
    DEPT_ID nvarchar(20) not null，
--EMP_id int not null
)
--选择使用表
select * from Employee
--在表中插入数据
insert into Employee values('E001','Robert P.','Shanghai','D001')
    ,('E002','Polly W.','Beijing','D001'),
    ('E003','David J.','Nanjing','D002'),
    ('E004','Nelson G.','Shanghai','D002')
--drop table Department
--删除表

--二，创建数据库实例
create database schoolDB
--主数据库    .mdf
--日志文件    .ldf
--D盘
create database BankDB on primary(
    name='BankDB.mdf',
    filename='D:\BankDB.mdf',
    size= 5mb,
    maxsize=100mb,
    filegrowth=2mb
)
--注意：size必须是不小于5MB
log on(
    name='BankDB.ldf',
    filename='D:\BankDB.ldf',
    size= 2mb,
    maxsize=20mb,
    filegrowth=10%
)
--日志数据库 中 size没有大小要求

create database testDB on primary(
    name='testDB.mdf',
    filename='D:\testDB.mdf',
    size=5mb,
    maxsize=100mb,
    filegrowth=2mb
)
log on(
    name='testDB.ldf',
    filename='D:\testDB.ldf',
    size=2mb,
    maxsize=20mb,
    filegrowth=10%
)

use testDB
create table testTable(
    EmployeeID nvarchar(20) not null,
    Resiednce nvarchar(20),
    Office nvarchar(40),
    MobileNumber nvarchar(40)
)
insert into testTable values
('1','','945673561',''),
    ('2','23456','99991111',''),
    ('3','','','912345678'),
    ('4','','','908087657')
select *from testTable
------------------------------------------------
    --三，创建架构，创建表。
select UNICODE('b')
select CHAR(98)
go
create schema ONE--创建架构
go
create schema TWO
go
create table CLASS1(
    id int primary key,
    name nvarchar(20))

create table ONE.CLASS1(
    id int primary key,
    name nvarchar(20))

create table ONE.CLASS2(
    id int primary key,
    name nvarchar(20))
--插入数据
--output inserted. *  output deleted
create table conInfo(
    id int primary key identity(1,1),--不用考虑，直接插入三个值
rInfo nvarchar(11) null,--地址
oInfo nvarchar(11) null,
    mInfo nvarchar(11)
)
insert into conInfo output inserted. * values
(null,'45646123',null),
    (null,null,'415461231'),
    ('45415132',null,null)

delete conInfo output deleted.* where id=32 --output deleted.*必须写

update conInfo set rInfo='1234' --后果严重
update conInfo set rInfo='1234' where id=40
update conInfo output inserted.* set rInfo='1234' where id=40 --语法出错
update conInfo set rInfo='1234' output inserted.* ,'--------------',deleted.* where id=40

select *from conInfo where oInfo is null
select *from conInfo where oInfo is not null
select id,isnull(rInfo,0),isnull(rInfo,0),isnull(mInfo,0) from conInfo
select id,isnull(rInfo,0),isnull(rInfo,0)as oInfo,isnull(mInfo,0)as mInfo from conInfo
select * from conInfo




四，使用子查询。
use AdventureWorks--使用数据库
go
select count(*) from HumanResources.Employee
-- 数据库.架构名.表名
go
--如何创建架构
create schema class11
create table class11.Info(
    id int primary key identity(1,1) ,
    name nvarchar(20) not null
)
go
create schema class12--创建架构的语法
go
create table class12.Info(
    id int not null,
    name nvarchar
)
select * from AdventureWorks.HumanResources.Employee
go
--查找指定的列
select EmployeeID from HumanResources.Employee
--列的值升序排列
select EmployeeID from HumanResources.Employee
order by EmployeeID asc--desc --（asc）
--查询多少条数据
select count(*) as '总条数' from HumanResources.Employee
--创建数据库--创建文件夹
create database dataTypeDB
on primary(
    name='dataTypeDB',
    filename='D:\dataDB.mdf',
    size=5mb,
    maxsize=15mb,
    filegrowth=2%
)log on(
    name='dataTypeLog',
    filename='D:\dataLog.ldf',
    size=5mb,
    maxsize=15mb,
    filegrowth=2mb
)
use dataTypeDB--使用数据库
create table demoTbl(
    birthday date  not null
)
--drop table demoTbl 删除表
--delete demoTbl 删除表中的数据
insert into demoTbl output inserted.* values('qwqw')
insert into demoTbl values(1999-01-01)
insert into demoTbl output inserted.* values('1999-01-01')
select * from demoTbl
delete demoTbl
alter table demoTbl  add  salary datetime not null
go--alter 无数据的使用
select * from demoTbl
insert into demoTbl output inserted.* values('1995/07/07','2014/10/31')
insert into demoTbl output inserted.* values('1995/07/07','2014/10/31 14:23:45')
go
declare @a datetime
set @a='2014-09-06'
select @a as 'datatime'

declare @i numeric(4,2)
set @i=12.56
select @i as [numeric]

--sql 声明变量的时候
--declare  @变量名   数据类型
--set 设置
declare @n decimal(4,2)
set @n=23.56
select @n as 'decimal'
go
create table test(
    num decimal(4,2)
)--drop table test
insert into test output inserted.* values(10.245423423)
insert into test output inserted.* values(2.2)
create table testNumeric(
    num numeric(3,2)
)
insert into testNumeric output inserted.* values(234.56)
insert into testNumeric output inserted.* values(2.34)
use AdventureWorks
go
select * from HumanResources.Employee
use dataTypeDB
create table rongYuTbl(
    id int primary key identity(1,1),
    name char(4) not null
)
insert into rongYuTbl output inserted.* values('1234'),('abcd'),('1q2w')
select * from rongYuTbl
select distinct(name) from rongYuTbl
select distinct(name) into newTbl from rongYuTbl--2.9
select * from newTbl
--drop table newTbl
delete newTbl output deleted.* where name='1234'
select * from newTbl
select * from newTbl where name='abcd'
create schema jiagouA--创建架构
create schema jiagouB
create table jiagouA.A(
    id int primary key identity(1,1) ,
    name nvarchar(20) not null
)
create table jiagouA.b(
    id int primary key
)
create table jiagouB.A(
    id int primary key identity(1,1),
    name nvarchar(20) not null
)
select * from  jiagouA.A
select * from jiagouA.b
--指定特定列
select * from AdventureWorks.HumanResources.Employee
select  e.EmployeeID,e.Title into Emp
from  AdventureWorks.HumanResources.Employee as e
select * from Emp
select EmployeeID as 'Eid',Title as 'TName' from Emp
select 'E'=EmployeeID,'T'=Title from Emp
select EmployeeID  'EEE' ,Title 'TTT'   from Emp
select EmployeeID , 'EEE' ,Title ,'TTT'  from Emp
select EmployeeID , 'EEE'as '--->' ,Title ,'TTT'as '<----'  from Emp
select EmployeeID +'EEE--->' +Title +'TTT<----' as Infomation  from Emp
select * from AdventureWorks.HumanResources.Department
select d.Name +'----->'+GroupName+'<-----'  from AdventureWorks.HumanResources.Department as d
-------------------------------------
    select * from AdventureWorks.HumanResources.EmployeeDepartmentHistory
select S.DepartmentID,S.EmployeeID into Dep from AdventureWorks.HumanResources.EmployeeDepartmentHistory as S
select * from Dep
select DepartmentID+EmployeeID  from Dep
select * from Dep
select * from Dep where DepartmentID>5
select * from Dep where DepartmentID between 5 and 10
select * from Dep where DepartmentID=2 or DepartmentID=5
select * from Dep where DepartmentID=2 or DepartmentID=50
select * from Emp
insert into Emp output inserted.* values('Tool----U')
select * from Emp where Title like'Tool%' and Title like '%r'
select * from Emp where Title like 'Tool%  ' or Title like '%U%'
select * from Emp where not  Title='Tool%'-
    -------------------
        select * from AdventureWorks.HumanResources.Employee
select e.EmployeeID,e.Title,e.LoginID into Emp   from AdventureWorks.HumanResources.Employee e
select * from Emp
--存储过程--
exec sp_rename 'Emp.EmployeeID','EID','column'
update Emp   set Title ='A' output inserted.* where EID=1
update Emp   set Title ='B' output deleted.* where EID=3
----------------------------
    update Emp   set Title ='C'
output inserted.* ,'-----' , deleted.*  where EID=9
--------
    select * from Emp where Title in ('Buyer','Stocker','Recruiter')
select * from Emp where Title in('A','B','C')
select * from Emp where Title not in('A','B','C')
---------------
    select * from AdventureWorks.HumanResources.EmployeeDepartmentHistory
select  E.EmployeeID,E.EndDate,E.StartDate,E.ModifiedDate
into EDH   from  AdventureWorks.HumanResources.EmployeeDepartmentHistory E
select * from EDH where EndDate is not null
select * from EDH where EndDate is  null
select  EmployeeID,isnull(EndDate,0)  as EDate   from  EDH
----------
    select EmployeeID,coalesce(EndDate,StartDate,ModifiedDate) as C from EDH
---------------------------------
    select top 3 * from AdventureWorks.HumanResources.Employee
go
select  e.BirthDate,e.HireDate,e.EmployeeID from AdventureWorks.HumanResources.Employee e
where HireDate>='1998-01-01' and HireDate<='1999-12-31'
order by HireDate desc--ASC
--------
    select e.EmployeeID,e.NationalIDNumber,e.ContactID,e.HireDate
into EmpNew
from AdventureWorks .HumanResources.Employee e
go
select *  from EmpNew  order by EmployeeID --desc
offset 20 rows
fetch next 10 rows only
------
    select * from AdventureWorks .HumanResources.Department
order by DepartmentID desc
offset 5 rows
fetch  next 10 rows only
go
----
    五，ASCII表
练习使用
--row_number()
--rank()
--dense_rank()
--ntile()
函数。

select 'UPPER'=upper('sfgjsghd')
select ascii('A')
select UNICODE('A')
select DIFFERENCE('Hello','Hello')
select char(65)
select CHARINDEX('E','Hello')
select left('richard',4)
select RIGHT('richard',4)
select len('how are you')
select lower('HHGGF')


select DATEDIFF(year ,convert(datetime,'1990-02-05'),
    convert(datetime,'2014-01-01'))
select DATEDIFF(day ,convert(datetime,'1990-02-05'),
    convert(datetime,'2014-01-01'))
-------------------
    --row_number()
--rank()
--dense_rank()
--ntile()
select h.EmployeeID,h.Rate,rank() over (order by rate desc) as RANKDemo
from AdventureWorks.HumanResources.EmployeePayHistory h

select h.EmployeeID,h.Rate,row_number() over (order by rate desc) as RANKDemo
from AdventureWorks.HumanResources.EmployeePayHistory h

select h.EmployeeID,h.Rate,dense_rank() over (order by rate desc) as RANKDemo
from AdventureWorks.HumanResources.EmployeePayHistory h
go
select h.EmployeeID,h.Rate,dense_rank() over (partition by EmployeeID order by rate desc) as RANKDemo
from AdventureWorks.HumanResources.EmployeePayHistory h
order by EmployeeID asc
offset 15 rows
fetch next 10 rows only
-------------------------
select 'UPPER'=upper('sfgjsghd')
select ascii('A')
select UNICODE('A')
select DIFFERENCE('Hello','Hello')
select char(65)
select CHARINDEX('E','Hello')
select left('richard',4)
select RIGHT('richard',4)
select len('how are you')
select lower('HHGGF')

select DATEDIFF(year ,convert(datetime,'1990-02-05'),
    convert(datetime,'2014-01-01'))
select DATEDIFF(day ,convert(datetime,'1990-02-05'),
    convert(datetime,'2014-01-01'))
-------------------
--row_number()
--rank()
--dense_rank()
--ntile()
select h.EmployeeID,h.Rate,rank() over (order by rate desc) as RANKDemo
from AdventureWorks.HumanResources.EmployeePayHistory h

select h.EmployeeID,h.Rate,row_number() over (order by rate desc) as RANKDemo
from AdventureWorks.HumanResources.EmployeePayHistory h

select h.EmployeeID,h.Rate,dense_rank() over (order by rate desc) as RANKDemo
from AdventureWorks.HumanResources.EmployeePayHistory h
go
select h.EmployeeID,h.Rate,dense_rank() over (partition by EmployeeID order by rate desc) as RANKDemo
from AdventureWorks.HumanResources.EmployeePayHistory h
order by EmployeeID asc