----------创建数据库(属性为默认值) 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'
