SQLServer数据基本语句

create database Test3DB
on
(
name='test3',
filename='D:\数据库文件\test3DB.mdf',
size=10MB,
filegrowth=10%
)
log on 
(
 name='test3_log',
filename='D:\数据库文件\test3DB_log.ldf',
size=1MB,
maxsize=20MB
)
use Test3DB 

if OBJECT_ID('dbo.table1','U') is not null
drop table dbo.table1

create table table1
(
 tableId int primary key identity(1,1) not null,
 tableName varchar(50) not null,
 loginName varchar(50) not null,
 loginPwd nvarchar(16) check(len(loginPwd)>=6 and len(loginPwd)<=16) not null,
 sex char(2) default('男') check(sex in('男','女')) not null,
 email varchar(255) check(email like '%@%' and  email like '%.%'),
 addressd varchar(255),
 phone nvarchar(20)
)

--插入单条--
insert into table1
values('吴诗涵','wushihang','123456',default,'ema@qq.com','东莞','13698877855')

--插入多条--
insert  table1
select '狭隘','wushihang','123456','女','ema@qq.com','东莞','13698877855' union
select '小风扇','wushihang','123456','女','ema@qq.com','东莞','13698877855' union
select '沙马生','wushihang','123456','男','ema@qq.com','东莞','13698877855' 

--修改--
update table1 set sex='女' where tableId=1

--删除（删不掉约束）--
delete from table1 where tableId=1

--删除表中所有数据(不能加where,可一并删除对应的约束)--
truncate table table1

--新增列--
alter table table1 add score int

--给邮箱添加约束--
alter table table1 add check(email like '%@%')

--把编号为1的密码中9改为1--
update table1 set loginPwd=REPLACE(loginPwd,'9','1') where tableId=1

--查询--
select * from table1

--根据成绩--
select tableId as 编号,(score*09+5) as 综合成绩 from table1
where (score*09+5)>60
order by score

--模糊查询--
select * from table1 where tableName like '%小%'

--范围查询(60到80的数据)--
select * from table1 where scroe between 60 and 80
select * from table1 where scroe not between 60 and 80

--用in列举值--
select * from table1 where addressd in('东莞','上海','广州') order by addressd

--group by分组查询（只现在有聚合函数，有分组的列）--

--平均值--
select tableId,AVG(score) from table1 group by tableId

--总数--
select COUNT(*) as 人数,sgrade from table1 group by sgrade

--group by + order by + +desc--
select tableId,SUM(scroe) from table1
group by tableId
order by SUM(scroe) desc

--having分组筛选--
select COUNT(*),tableId from table1
group by tableId
having COUNT(*)>15

--where --> group by --> having--安装这个顺序

---每个列及格总人数，和及格平均分在80分以上的记录--
select COUNT(*) as 人数,AVG(scroe) as 平均分,tableId from table1
where scroe>=60
group by tableId
having AVG(scroe)>=80

--内联接多表查询--
select s.sname,b.courseName,c.score from student
inner join Scroe as c (s.ScodeId=c.StudentId)
inner join Course as b on (b.curseId=c.courseId)

--左外联接--
select s.sName,c.CoureseId,c.Score from Student as s
left outer join Scroe as c on s.Scode=c.StudentId

--右外联接---
right outer join  .....on ..

---更改列名，表名，数据名--

EXEC  sp_rename  ' table1.[title] ' ,  ' title2 ' ,  ' COLUMN '   --改列名

EXEC  sp_rename  ' table1 ' ,  ' table2 '   --改表名

EXEC  sp_renamedb  ' Test3DB ' ,  ' TestDB '  --改数据库名

函数：

--字符串函数
select CHARINDEX('吴诗涵','我去接吴诗涵放学',1) as 吴诗涵的起始位置
select LEN('我去接吴诗涵放学') as 长度
select upper('我去接吴诗涵放学abcd') as 转换为大写字母
select LTRIM('  吴诗涵') as 去除左边的空格
select RTRIM('吴诗涵  ') as 去除右边的空格
select RIGHT('我去接吴诗涵放学',3) as 取右边三个字符串
select REPLACE('我去接吴诗涵放学','我去','我不去') as 替换
select STUFF('我不去接吴诗涵放学',2,3,'去接') as 从第2位开始删3个字符串后更改

--日期函数
select GETDATE() as 现在时间
select DATEADD(mm,4,'2022/3/2') as 指定日期加
select DATEDIFF(mm,'2022/3/2','2022/5/2') as 两个日期差
select DATENAME(DW,'2022/3/2') as 日期星期几
select DATEPART(day,'2022/3/2')

--数学函数
select RAND() as 另到1的随机数
select ABS(-60) as 绝对值
select CEILING(546.6) as 向上取整数
select FLOOR(546.6) as 向下去整数
select POWER(5,2) as 取幂值平方
select ROUND(23.562,1) as 精确小数点的四舍五入
select SIGN(13) as 返回对应数
select SQRT(9) as 开平方

--系统函数
select CONVERT(varchar(50),123456) as 强转换类型返回字符串123456
select CURRENT_USER  as 登录的用户名
select DATALENGTH('我的世界') as 字节数