Sql Server基本操作对数据库 表 视图

一.对数据库的操作
1.查询系统所有数据库
select * from sysdatabases
2.删除某个数据库
drop database dbtest
3.创建某个数据库
create database dbtest
ON PRIMARY
( NAME = ‘dbtest’,
FILENAME = ‘F:\dbtest.mdf’ ,
SIZE = 5312KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = ‘dbtest_log’,
FILENAME = ‘F:\dbtest.ldf’ ,
SIZE = 1024KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%
)
二.对表的操作
1.创建表
use dbtest
create table ClassInfo
(
cID int not null primary key identity(1,1),–identity唯一标识 增量
ctitle nvarchar(10)
)

create table StudentInfo
(
sID int not null primary key identity(1,1),
sName nvarchar(10) not null,
sGender bit default(0),
sBirthday date ,
sphone char(11),
semail varchar(20),
cid int not null,
foreign key(cid) references ClassInfo(cID)–外键
)
create table UserInfo
(
userID int not null primary key identity(1,1),
username nvarchar(50),
userpwd varchar(20)
）
2.查询所有表
select * from sysobjects where xtype=‘U’

3.往表中插入记录
（1）插入一条记录
insert UserInfo(username,userpwd)
values(‘小包’,‘21232f297a57a5a74’)
（2）插入多条记录
insert into ClassInfo
values(‘青龙’),(‘白虎’),(‘朱雀’),(‘玄武’)
4.更新update
update UserInfo
set userpwd=‘21232f297a57a5a74’
where userID>1
5.删除
delete ClassInfo where cID>6
6.清空
truncate table Userinfo
7.top n +order by(查询前几条记录)
select top 1 *
from StudentInfo

select top 2 percent *
from StudentInfo
order by cid desc,sID asc
8.消除重复行distinct
select distinct cid
from StudentInfo
9.模糊查询 （ like %：0+个任意字符 _：1个任意字符 ） like %0+ _1
select * from StudentInfo
where sName like’%三%’;–名字中包含三

select * from StudentInfo
where sName like ‘张%’–查询姓张

select * from StudentInfo
where sName like ‘张_’–查询姓名为2个字且姓黄

select * from StudentInfo
where sphone like ‘1[^5-9]%’–以1开头，第二位不在5-9里面
10.连接查询
select * from StudentInfo as S
inner join ClassInfo as C on S.cid=C.cID
11.子查询in或者 exists
select * from StudentInfo
where sID in(
select distinct stuid from ScoreInfo)

select * from StudentInfo
where exists(
select * from ScoreInfo where stuid=sid)
12.分页
select * from
(select *,row_number() over(order by sid desc) as rowindex
from StudentInfo
where sGender=1) as t1
where rowindex between 5 and 8
13.case语句判断
select *,
case
when scoreValue>=60 and scoreValue<80 then ‘中’
when scoreValue>=90 then ‘优’
when scoreValue>=80 then ‘良’
else ‘差’ end as 等级
from ScoreInfo

三.视图
1.创建视图 视图中存储select语句，而不是结果
create view Student_Class
as
select S.*,C.ctitle from StudentInfo as S
inner join ClassInfo as C on S.cid=C.cID
2.删除视图
drop view Student_Class