SQL Server基本语句

----------创建数据库(属性为默认值)
create database ShoolInformation

-----------删除数据库
drop database ShoolInformation

----------创建数据库并 设置属性
create database ShoolInformation
on primary
(
name=‘ShoolInformation’,
filename=‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ShoolInformation.mdf’,
size=5mb,
maxsize=20mb,
filegrowth=5mb
)
log on
(
name=‘ShoolInformation_log’,
filename=‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ShoolInformation.ldf’,
size=5mb,
maxsize=10mb,
filegrowth=5%

)

----------创建表 在数据库里创建

use ShoolInformation

create table Students --创建表 的时候 必须要有 一 列
(
SdId int identity(1,1) ,
SdName nvarchar(10),
SdSex nchar(1),
SdStudy nvarchar(10),
SdClass nvarchar(10)
)
------------删除表
drop table Students

------------插入数据 在表里插入数据
insert into Students(SdName,SdSex,Sdclass,SdStudy)
values(‘勇’,‘男’,‘软件3班’,‘软件技术’)

----------插入多条数据
insert into Students(SdName,SdSex,Sdclass,SdStudy)
values
(‘勇’,‘男’,‘软件3班’,‘软件技术’),
(‘勇’,‘男’,‘软件3班’,‘软件技术’),
(‘勇’,‘男’,‘软件3班’,‘软件技术’)

-----------删除数据 没加wher表示删除全部数据

delete from Students
where SdId=2

---------删除所有数据(truncate)
—truncate特点
–1>truncate语句不能跟wher语句(只能全部删除数据)
–2>同时自动编号恢复初始值
–3>使用truncate删除表中所有数据要比dalete效率高的多
–4>truncate删除数据，不触发delete触发器
truncate table Students

----------数据库更新（改）
-----更改表中的数据
update Students
set
SdStudy=‘啊啊’
where
SdId=1

------更改表的列名

–百度的不过不可以该用户
alter table Student rename column SdSex to Sd

------删除 一列 列
alter table Students drop column SdSex
------增加 一列
alter table Students add SdSex nchar(1)
------修改列的 数据类型
alter table Students alter column SdSex int

----------增表 列的 约束
alter table Students add
constraint UQ_Employees_EmpName unique(SdSex) —增加一个唯一的约束

------------数据 查询
select * from Students where SdId=1

select SdId as 编号 from Students

select SdId=‘a’

-----------获取 前面几条数据Top
select top 2 SdId from Students

------------清除重复Distinct
select distinct * from Students

--------------排序(order by)
select * from Students order by SdId desc —降序
select * from Students order by SdId asc -------升序
select * from Students order by SdId -----默认是升序

--------------集合函数 函数不计算空值 null、
------max() min() count() sum() avg()
select MAX(SdId) from Students
select Min(SdId) from Students
select count(SdId) from Students
select sum(SdId) from Students
select avg(SdId) from Students

-----------模糊 查询

----— _ % [] * ^

----- _ 表示任意单个字符

select * from Students where SdName like ‘魏_’
select * from Students where SdName like ‘魏__’

----- %匹配任意多个字符
select * from Students where SdName like ‘魏%’

-----[] 范围 [0-9][a-z]等 用在[^] 的^ 代表非
select * from Students where SdName like ‘魏[0-9]%’
select * from Students where SdName like ‘魏[^0-9]%’

—自己定义转义符
select * from Students where SdName like ‘魏/%_’ ESCAPE ‘/’

----------查询条件
–查询条件 运算符

–比较 =， >,<,<=,>= ,!= ,<>,!>,!<
–确定范围 between and ,not between and
– SELECT * FROM Student WHERE Sage BETWEEN 20 AND 23;
–确定集合 IN ,NO IN
– SELECT * FROM Student WHERE Sdept IN(‘CS’, ‘MA’);//查询Sdept=CS 和 MA
–字符匹配 like ,not like
–空值 is null,is not null
–多重条件 and,or, not,

----數據庫完美備份

BACKUP DATABASE login
TO disk = ‘F:\database\name.bak’
WITH FORMAT,NAME = ‘name’

—數據庫差异备份

backup database t

 to disk='E:\dataBak\log\bak_t_differ.bak'

with differential,noinit,name=‘bak_t_differ’

------數據庫恢復
restore database login from disk= ‘f:\database\name.bak’