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

相关文章

来自专栏杨建荣的学习笔记

关于exp/imp的总结学习(r4笔记第26天)

关于exp/imp,是很常用的数据导出导入工具,在10g开始推出的数据泵datapump相当于是exp/imp的补充和升级版本。在后续章节再做一个总结。 exp...

2987
来自专栏大内老A

WCF版的PetShop之二:模块中的层次划分[提供源代码下载]

上一篇文章主要讨论的是PetShop的模块划分,在这一篇文章中我们来讨论在一个模块中如何进行层次划分。模块划分应该是基于功能的,一个模块可以看成是服务于某项功能...

24510
来自专栏乐沙弥的世界

SQLplus 下行预取特性

   通常情况下数据库引擎每访问一个数据块将产生至少一个逻辑读。而行预取与逻辑读息息相关。行预取是指当客户端从数据库获取数据时 可以采用单行也可以采用多行方式返...

642
来自专栏乐沙弥的世界

执行计划中各字段各模块描述

      在SQL语句的执行计划中,包含很多字段项和很多模块,其不同字段代表了不同的含义且在不同的情形下某些字段、模块显示或不显示,下 面的描述给出了执行计划...

794
来自专栏Hadoop数据仓库

Oracle 索引的分类

看到 itpub 论坛上的一个帖子,对 Oracle 的索引分类总结得言简意赅,于是收藏过来。又补充了一点RAC用到的反向索引的东西。 逻辑上:  Single...

1809
来自专栏乐沙弥的世界

ORA-39126 KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS]错误

--======================================================= -- ORA-39126 KUPW$WOR...

1314
来自专栏数据小魔方

时间&日期函数

今天要跟大家简要介绍一下excel中经常会用到的日期与时间函数! 日期与时间类的函数虽然算所有函数中最难掌握的,但是因为格式众多,形式多样,而且作为其他高级函数...

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

mybatis 使用经验小结

一、多数据源问题 主要思路是把dataSource、sqlSesstionFactory、MapperScannerConfigurer在配置中区分开,各Map...

2826
来自专栏java达人

Oracle执行计划详解

简介: 本文全面详细介绍oracle执行计划的相关的概念,访问数据的存取方法,表之间的连接等内容。 并有总结和概述,便于理解与记忆! +++ 目录 ...

1767
来自专栏数据和云

12c RMAN新特性之Recover Table

周玉其 云和恩墨南区技术顾问,曾在电信、物流等行业从事多年数据库开发、管理工作。

742

扫码关注云+社区