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

代码420行左右 本系列,几乎都是代码,记得当时写的时候用的是微软的官方实例数据库AdventureWorks_Data.mdf、AdventureWorks_Log.ldf来运行的。 下载链接:链接: https://pan.baidu.com/s/1pMdLz6N 密码: xvhu 或者回复“AdventureWorks”来获取链接。


--一,创建数据库 。
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

原文发布于微信公众号 - 数据库SQL(SQLdba)

原文发表时间:2018-02-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏肖洒的博客

【爬虫】(二)爬取西电教务处成绩

1453
来自专栏进击的程序猿

orm 系列 之 常用设计模式 The Repository Pattern

本文是orm系列的第一篇,内容来自github上的一个Markdown,清晰的讲述了一些数据库设计上常用的设计模式,并且阐述了orm是什么?

913
来自专栏技术之路

整理sqlserver 级联更新和删除 c#调用存储过程返回值

整理一下级联更新和删除 c#调用返回值 use master go IF exists(select 1 from sysdatabases where...

1789
来自专栏数据之美

Hive & Performance 学习笔记

注:本文来源于 Hortonworks 的 Adam Muise 在 July 23 2013 日的 Toronto Hadoop User Group 大会...

2625
来自专栏岑玉海

RavenDb学习(五)结果转换

1)Result Transformers public class Order { public DateTime OrderedAt { get;...

2579
来自专栏c#开发者

MVC 5 Scaffolder + EntityFramework+UnitOfWork Pattern 代码生成工具集成Visual Studio 2013

MVC 5 Scaffolder + EntityFramework+UnitOfWork Pattern 代码生成工具 经过一个多星期的努力总算完成了单表,多...

37413
来自专栏菩提树下的杨过

无限级分类(非递归算法/存储过程版/GUID主键)完整数据库示例_(4)显示记录

显示记录,其实再简单不过了,一条sql语句即可 Select * From T_Class order By F_RootID,F_Orders 下面给出一个A...

17910
来自专栏Jerry的SAP技术分享

如何处理用代码创建SD Sales order时遇到的错误消息KI 180

错误消息KI 180:You must enter a company code for transaction Create sales document

1244
来自专栏乐沙弥的世界

Oracle SGA 自动管理特性(sga_target参数)

    最近有网友对Oracle SGA内存自动管理特性不是很清楚,可能是由于当时翻译的 Oracle 10g SGA 的自动化管理 比较生涩,下面依旧是针对这...

753
来自专栏PPV课数据科学社区

【学习】七天搞定SAS(三):基本模块调用

搞定基本的函数之后,开始鼓捣SAS里面的模型。也就是说,要开始写PROC了。说实话,越学SAS,越觉得SAS像Stata...无论是从输出的样式,还是语法。好不...

2865

扫码关注云+社区