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 条评论
登录 后参与评论

相关文章

来自专栏恰同学骚年

轻量级ORM框架初探-Dapper与PetaPoco的基本使用

  EF是传统的ORM框架,也是一个比较重量级的ORM框架。这里仍然使用EF的原因在于为了突出轻量级ORM框架的性能,所谓有对比才有更优的选择。

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

Silverlight:用Enter键替换Tab键切换焦点

业务系统中,很多录入人员习惯于用Enter键来代替Tab键切换控件焦点(虽然我个人并不觉得这样录入速度会变得有多高效,呵呵),有需求了,自然就得想办法满足。 思...

17510
来自专栏分布式系统进阶

Librdkafka的Transport层

rd_kafka_recv按kafka的协议来收包, 先收4字节,拿到payload长度, 再根据这个长度收够payload内容, 这样一个完整的respons...

531
来自专栏大内老A

我的WCF之旅(4):WCF中的序列化[下篇]

XMLSerializer 提到XMLSerializer,我想绝大多数人都知道这是asmx采用的Serializer。首先我们还是来看一个例子,通过比较Ma...

17610
来自专栏jessetalks

由浅入深表达式树(完结篇)重磅打造 Linq To 博客园

  一个多月之后,由浅入深表达式系列的最后一篇终于要问世了。想对所有关注的朋友说声:“对不起,我来晚了!” 希望最后一篇的内容对得起这一个月时间的等待。在学习完...

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

Silverlight Telerik控件学习:数据录入、数据验证

相信很多人都听说过这句名言:garbage in ,garbage out ! 数据录入不规范(或错误)就象一颗定时炸弹,迟早会给系统带来麻烦,所以在数据录入时...

2366
来自专栏岑玉海

WF追忆

  前一阵子学习了一下工作流,现在写个总结记录一下这个过程。要弄工作流,首先就要有个界面来画图,做web的,没办法,只能选择javascript和silverl...

2686
来自专栏张善友的专栏

性能计数器数据收集服务

本文演示了一个Windows服务收集性能计数器的数据,将性能计数器数据写入数据库。Windows服务中调用WebAPI服务中。下面简要介绍下我的改造,项目虽小,...

1948
来自专栏木宛城主

曾今的代码系列——自己的分页控件+存储过程实现分页

项目里面的测试代码,仅供参考 LoginByAjax <title>Ajax登陆</title> <script src="Scripts/c...

1625
来自专栏ASP.NET MVC5 后台权限管理系统

构建ASP.NET MVC4+EF5+EasyUI+Unity2.x注入的后台管理系统(8)-MVC与EasyUI DataGrid 分页

前言 为了符合后面更新后的重构系统,文章于2016-11-1日重写 EasyUI Datagrid在加载的时候会提交一些分页的信息到后台,我们需要根据这些信...

2407

扫描关注云+社区