SQL Server学习笔记

create database mydata;
use mydata;

-- 创建表
create table stu(
	name varchar(20),
	age int,
	score decimal(10,2)
)

create table tea(
	id int primary key,
	name varchar(20),
	score decimal(10,2)
)

-- 创建表后添加列名
alter table stu add id int primary key;

-- 修改某一列的列名
exec sp_rename 'stu.age','age1','column';

-- 删除某一个列名
alter table stu drop column age1;

-- 修改列的类型
alter table stu alter column name varchar(20) not null;

-- 查看表所有的约束
exec sp_helpconstraint @objname=stu;

-- 给列名添加主键(必须是非空字段)
alter table teacher add constraint pk_tea primary key (id);

-- 删除主键约束
alter table teacher drop constraint pk_tea;


select * from stu;

select d.info,s.code from student s, desc d where s.code = d.st_code
select * from student s inner join desc d on(s.code = d.st_code)

select * from student s left join desc d on(s.code = d.st_code)

select * from student s right join desc d on(s.code = d.st_code)

select * from student s full join desc d on(s.code = d.st_code)

select * from student cross join desc

select * from student ,chs where s.id in (chs.scode)

select row_number() over(order by id) row from stu
select top 1 * from stu

-- 左模糊
select * from student where name like '%张'

-- 右模糊
select * from student where name like '三%'

-- 模糊查询
select * from student where name like '%张三%'

-- 分组查询
select * from student group by sex

-- 排序查询
select * from student order by [asc,desc]

-- 求和 sum
select sum(score) from student;

-- 平均数 avg
select avg(score) from student;

-- 数量 count
select count(score) from student;

-- 最大值 max
select max(score) from student;

-- 最小值 min
select min(score) from student;

distinct 字段

cast(函数(字段) as decimal(n,y) )

create view 视图名称 as select 语句

if (exists (select * from sys.objects where name = 'v_stu'))
    drop view v_stu

select * into 新表 from 旧表

update stu set score = (select score from st_back sk where sk.id = stu.id)

GO
create trigger trigger_name
on 表名
[WITH ENCRYPTION]
for [DELETE, INSERT, UPDATE]
as
	T-SQL语句


--创建insert触发器
create trigger trig_insert
on student
for insert
as
begin
    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
    declare @stuNumber int;
    select @stuNumber = count(*)from student;
    if not exists (select * from student_sum)--判断表中是否有记录
        insert into student_sum values(0);
    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end

-- 修改触发器语法
ALTER TRIGGER  trigger_name 
ON  -- 表名 
FOR [DELETE][,][INSERT][,][UPDATE]
AS
sql_statement;

--语法格式:
	DROP  TRIGGER   [ trigger_name ] [ ,...n ]
参数:
 trigger: 要删除的触发器名称
 n:表示可以删除多个触发器的占位符  

create trigger tr_DDL on database
for DROP_TABLE,ALTER_TABLE
as
begin
	print '别想着删库！好好打你的代码'
 	rollback -- 回滚
end

定义事务：begin [tran|transaction] transacction_name
回滚事务：rollback [tran|transaction] transacction_name
提交事务：commit  [tran|transaction] update_0

declare @var int
set @var = 10 -- 赋值
select @var -- 使用