一些常用的sql语句

 

create datebase datebasename  创建数据库
create table tablename(col type [ not null][primary key],....) 创建表
lter table stu_info alter column stu_add varchar(50)  修改列字段的长度
alter table tablename add constraint primarykeyname primary key(col)  添加主键
alter table tablename drop constraint primarykeyname  删除主键
create index indexname on tablename(col)  创建索引
create view viewname as select col from tablename 创建视图
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

 

建存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pra_cc]
@name nvarchar(100),
@uid int
AS
BEGIN
 SET NOCOUNT ON;
 SELECT * FROM tb_menu where menu_name=@name
 return
END

建触发器

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[stu_taigger]
   ON  [dbo].[stu_info]
   AFTER insert AS
BEGIN
 select * from stu_info
 SET NOCOUNT ON;
END
GO