SqlServer数据库常用sql语句

一、数据库的创建、重命名、删除

/*简单的创建数据库*/
create database MyDataBase;
/*重命名数据库*/
exec sp_renamedb MyDataBase, 我的数据库;
create database MyD2;
--删除数据库
drop database 我的数据库;
drop database 我的数据库,MyD2;

--利用sql语句创建数据库
create database JackDatabase
on primary --数据库主文件(*.mdf)
(
--数据库逻辑名称
name=jackdatabase_on_name,
--数据库文件
filename='d:\temp\jackdatabse_on_filename.mdf',
--数据库文件初始大小,单位:M
size=3,
--数据库文件最大大小,单位:M
maxsize=12,
--数据库文件增长类型
filegrowth=10%
)
log on --数据库日志文件
(
--日志文件逻辑名称
name=jackdatabase_logon_name,
--日志文件
filename='d:\temp\jackdatabase_logon_filename.ldf',
--日志文件初始值大小,单位:M
size=1,
--日志文件最大大小,单位;M
maxsize=12,
--日志文件增长类型
filegrowth=10%
)
--/利用sql语句创建数据库
--sqlserver以tcp:1433默认端口号对外提供服务

二、表的创建、数据增删改

use master
go
create database MyShop
go
use JackDatabase --选择使用哪个数据库作为操作的对象
--创建表
create table tbl_student
(
	--StuID列,int类型,自动增长(种子为2011,增量为1),主键
	StuID int identity(2011,1) primary key,
	StuName varchar(50) not null,
	StuDesc varchar(1000),
	stuGender bit default(1),
	StuPhoneNO varchar(20) unique
)
--/创建表
--插入多行数据
insert into tbl_student(StuName) 
values
('xiaoming'),
('xiaohong'),
('xiaogang')
--/插入多行数据
 use MyShop
 go
 --快速将JackDatabase数据库里的表tbl_student的表结构复制到数据库MyShop里,注意,没有主键、约束等,但是有数据
 --跨数据库访问:数据库..表
 select * into tbl_student from JackDatabase..tbl_student

select* from dbo.tbl_student
delete from [dbo].[tbl_student] where StuID='2015'
insert into [dbo].[tbl_student](StuName) values('tom')
truncate table tbl_student

use MyShop
go
create table TeacherInfo(
	Tid varchar(20) primary key,
	Tno varchar(20) not null,
	Tname varchar(20) not null,
	Temail varchar(100) not null
)
insert into TeacherInfo(Tid,Tno ,Tname,Temail) values('2011','3','jim','jim@163.com')
insert into TeacherInfo(Tid,Tno ,Tname,Temail) values('2012','4','jack','jack@163.com')
insert into TeacherInfo(Tid,Tno ,Tname,Temail) values('2005','2','tom','tom@163.com')
insert into TeacherInfo(Tid,Tno ,Tname,Temail) values('2003','1','lisa','lisa@163.com')
insert into TeacherInfo(Tid,Tno ,Tname,Temail) values('2014','5','bob','bob@163.com')

select * from TeacherInfo

三、表结构修改以及六种约束管理

--六种类型的约束:
--主键约束、唯一约束、外键约束(级联更新、级联删除、设为空)
--默认约束、检查约束、非空约束

--手动删除约束
ALTER TABLE TeacherInfo DROP CONSTRAINT CK_TeacherInfo_TGender
--手动删除一列
ALTER TABLE TeacherInfo DROP COLUMN TGender
--手动增加一列
ALTER TABLE TeacherInfo ADD TGender BIT DEFAULT(1) NOT NULL
--手动修改列名
sp_rename 'F_SZRX.AJBH','CASENO','column' --注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
--手动删除默认约束
ALTER TABLE TeacherInfo DROP CONSTRAINT DF__TeacherIn__TGend__09DE7BCC
--手动修改一列的数据类型
ALTER TABLE TeacherInfo ALTER COLUMN TGender VARCHAR(50)
--手动增加主键约束
ALTER TABLE TeacherInfo ADD CONSTRAINT PK_TeacherInfo_TGender PRIMARY KEY(TGender)
--手动增加唯一约束
ALTER TABLE TeacherInfo ADD CONSTRAINT UQ_TeacherInfo_Temail UNIQUE (Temail)
--手动增加默认约束
ALTER TABLE TeacherInfo ADD CONSTRAINT DF_TeacherInfo_TGender DEFAULT (1) FOR TGender
--手动增加检查约束
ALTER TABLE TeacherInfo ADD CONSTRAINT CK_TeacherInfo_Tname CHECK(Tname<>'jack')
--手动增加外键约束
ALTER TABLE TeacherInfo ADD CONSTRAINT FK_TeacherInfo_Tno FOREIGN KEY(Tno) REFERENCES dbo.tbl_student(Tno) ON DELETE CASCADE
--手动删除多个约束
ALTER TABLE TeacherInfo DROP CONSTRAINT CK_TeacherInfo_Tname,DF_TeacherInfo_TGender
--手动增加多个约束
ALTER TABLE dbo.TeacherInfo ADD CONSTRAINT CK_TeacherInfo_Tname CHECK(Tname<>'jack'),CONSTRAINT UQ_TeacherInfo_Tno UNIQUE(Tno)

四、查询语句

--查询语句,起别名
SELECT 
	t.Tid AS '编号',--qi别名
	t.Tname AS 名字,--起别名
	t.Temail 邮箱,--起别名
	性别=t.TGender--起别名
FROM dbo.TeacherInfo t
--select直接常量
select 学生人数=100,学生总分=7000,是否团员='是'
--函数(获取当前时间)
select GETDATE()
--获取前几条记录(top)
select TOP 5 * FROM dbo.TeacherInfo ti
--获取查询结果的前百分比
SELECT TOP 20 PERCENT * FROM dbo.TeacherInfo ti
--去除重复的结果,注意:指的是记录而不是某一列DISTINCT
select distinct Tname,Temail from dbo.TeacherInfo

五、查询语句之聚合函数

--聚合函数max、min、sum、avg、count,返回结果是汇总之后的一条
SELECT MAX(t.StuID) AS 数学最高分 from  tbl_student t
SELECT MIN(t.StuID)	AS 数学成绩最低分 from tbl_student t
SELECT sum(t.StuID)	AS 数学成绩最低分 from tbl_student t
SELECT avg(t.StuID)	AS 数学成绩最低分 from tbl_student t
SELECT count(t.StuID) AS 数学成绩最低分 from tbl_student t
--聚合函数对null值不做处理
--select count(fmath) avg(fmath) sum(fmath),如果fmath为null则不参与到函数中计算
--但是select count(*),null是参与了的
--注意:between 值1 and值2 相当于 x>=值1 and x<=值2
--select * from student where no in(1,2,3)

六、查询语句之通配符

--========================通配符,都是针对字符串的操作的===================
--%:表示任意多个任意字符
--_:表示任意的单个字符
--[ ]:中括号中的任意一个字符,如:[abcde]
--[^]:不属于中括号中的任意一个字符[^abcde]
--将通配符作为普通字符进行匹配操作,将通配符放在[]中,
--	如:
--		'5[%]':'5%'
--		'[[]':	'['
--		']':	']'
--		'[_]':	'_'
--使用自己只能的转义字符:ESCAPE,下面使用!作为转义字符
SELECT * FROM dbo.tbl_student t WHERE t.StuName LIKE '%xiao!%!_![%'  ESCAPE '!'
--在sql语句中''表示一个单引号,如:
select '123''321'
--================================================================

七、查询语句之NULL处理

--==================================null:不知道===========================
--空值null:null和null比较的结果也是null,null表示不知道,不知道在where字句中就是false,不返回值
--null进行任何运算结果都是null,因为'不知道'进行任何的计算结果都是'不知道'
SELECT 0*NULL
SELECT 0/NULL
SELECT  NULL AS 数学成绩
SELECT 'jack' + NULL
--函数ISNULL:判断是否是null,然后用另外的值替代
SELECT 数学成绩 = ISNULL(cast(fmath as varchar(50)),'缺考') from...
--=======================================================================

八、查询语句之group by

--=======================================================================
--查询的数据源是集合,一旦在select语句中使用order by语句之后,那查询结果就变成游标了,而游标是不能被查询的。
--但是在select中加入top之后就又变成集合了,就又可以作为数据源了
--order by之后也可以是一个表达式
--group by字句中的列可以和聚合函数一起放在select中,或者是查询的列也放在聚合函数中,如:
select 性别=t.TGender, COUNT(t.Tid), COUNT(*) as 数量 from dbo.TeacherInfo t order by t.TGender

--select 语句的执行顺序
--注意select语句的执行顺序,having字句之后再执行select句
---========================================================

九、查询语句之转换函数

--转换函数
SELECT convert(VARCHAR(50),GETDATE(),101)

十、查询语句之union和union all

--======================union 和union all=================
--一般建议使用union all因为union会有一个去处重复数据的算法,但是一般不需要
--常用于底部汇总,如:
SELECT 统计='最高分',实际得分=MAX(fmax) from tbl_student
UNION ALL
SELECT '最低分',MIN(fmax) from tbl_student
UNION ALL
SELECT '平均分',AVG(fmax) from tbl_student
--==========================================================

十一、字符串函数、日期函数

--============字符串函数====================================
SELECT len('汤姆')--返回字符数
SELECT DATALENGTH('汤姆')--返回字节数
--还有ltrim、rtrim、upper、lower、left、right、substring
SELECT STUFF('abcdef', 2, 3, 'ijklmn'); --将abcdef字符串从索引2位置数3个长度替换成'ijklmn',结果为:aijklmnef
--=========================================================

--=====================日期函数============================
--在设计器里设计某一列的默认值,设置为getdate()函数,就可以实现每次插入数据的时候自动插入当前时间
PRINT CONVERT(date, GETDATE(),111)--打印当前日期
select GETDATE()--选择当前日期时间
PRINT CONVERT(date, DATEADD(DAY,2,GETDATE()))--在当前日期上加上两天
PRINT DATEDIFF(YEAR,'1999-8-6',GETDATE())--计算1999年距现在的年数
SELECT YEAR('2011-07-01')--计算日期的年部分
SELECT CAST('1999-02-03' AS datetime)--将字符串转化为时间
SELECT DATEPART(DAY,GETDATE())--获取日期的部分数值
--==========================================================

十二、output子句(可以输出刚刚insert、update、delete的数据)

--执行插入语句,并且返回刚刚插入的值
declare @id table(a int,b int, c int, d int)
insert into test output inserted.a, inserted.b,inserted.c,inserted.d into @id
values(20,20,30,40)
select * from @id

在这里插入图片描述

十三、case函数用法

--=======================case函数使用方法-区间判断(注意then后面的数据类型必须一致)-未测试===========================================
select 
姓名,
评级=
	case score
		when score>95 then '优'
		when score>80 then '良'
	else '缺考'
end
from student
--======================case函数使用方法二-等值判断-未测试=====================================
select 
姓名,
评级=
	case score
		when 100 '优秀'
		when 90 then '良'
	end
from student
--======================case函数举例--重要===========================
create table test(a int,b int)
insert test values(10,20),(100,30)

select 
a,
b,
max_a_b=
	case
		when a>b then a
		else b
	end
from test

十四、创建索引

--===========================创建索引的语法==============
---创建非聚集索引
create NONCLUSTERED INDEX IX_test_c_d ON test(c,d)
--删除索引
DROP INDEX IX_test_c ON test
DROP INDEX test.IX_test_d
--在没有主键或聚集索引的时候手动创建一个聚集索引
CREATE CLUSTERED INDEX IX_test_c ON test(c)
--创建唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX IX_test_a ON test(a)
--创建唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX IX_test_d ON test(d)  

十五、子查询及exists

--=====================子查询(独立子查询、相关子查询)============================
SELECT 
	最高分=(select MAX(a) from test),
	最低分=(select MIN(a) from test),
	平均分=(select AVG(a) from test)
	
--exist的使用
IF(EXISTS(SELECT * FROM test t WHERE 4>5))
	BEGIN
		PRINT '有结果'
	END
ELSE
	BEGIN
		PRINT '无结果'
	END
--相关子查询,也就是子查询里用到了父查询里的表
--自己的理解:先扫描父查询中的每一行,根据父查询中的每一行去扫描子查询中的表中的每一行,
--再根据子查询中的任意一行去扫描孙查询中的每一行,其中后代可以引用先代中的行记录数据.
SELECT * FROM test t
WHERE 
EXISTS(
	SELECT * 
	FROM dbo.tbl_student AS t2 
	WHERE t.a=t2.StuID
)

十六、分页语句之top(sqlserver 2000)

--===================分页查询(利用top关键字实现-sqlserver2000)==============================
-->全部数据
--1>第一页
SELECT TOP 2 * FROM test t
--2>第二页
SELECT TOP 2 *FROM test t
WHERE t.a NOT IN (SELECT TOP (1*2) a FROM test )

十七、分页语句之row_number over(sqlserver2005及以上)

--==================sqlserver 2005的分页查询(利用row_number函数实现)========================
--2>第二页
SELECT * FROM
(SELECT * ,ROW_NUMBER() OVER(order BY a) AS rownumber FROM test WHERE 1=1) AS t
WHERE t.rownumber BETWEEN (1*2+1) AND (2*2)
--注意:over是开窗函数,它与聚合函数一起使用的时候可以让聚合函数对每一条数据都计算一次
SELECT *,COUNT(*) OVER() AS 总数 FROM test t

十八、分页语句之fetch(sqlserver 2012及以上)

SELECT * FROM dbo.T_Message ORDER BY Id OFFSET 10 ROWS FETCH NEXT 10 ROWS only

十九、sqlserver中的系统架构(sys)

-- 获取所有的数据库
SELECT * FROM SYS.databases
-- 获取当前库中的所有表
SELECT * FROM sys.tables
-- 获取当前库中的所有视图
SELECT * FROM sys.views
-- 获取当前库中所有表的所有字段
SELECT * FROM sys.columns
-- 获取当前库中的所有存储过程
SELECT * FROM sys.procedures
-- 获取当前库中的所有触发器
SELECT * FROM sys.triggers
-- 获取当前库的所有文件
SELECT * FROM sys.database_files

未完待续。。。(进行到笔记335行)

  • 2
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

jackletter

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值