数据库常用语句

 --数据库初始化 --显示数据库中有记录的表名 select distinct o.name from sysobjects o left join sysindexes i on o.id =i.id where o.type='u' and i.rowcnt>0 --1.删除数据库中所有数据 EXEC sp_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL' EXEC SP_MSFOREACHTABLE 'DELETE FROM ?' EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL' --查询所有表 exec sp_MSFOREACHTABLE 'SELECT * FROM ?' --2. 导入数据 --2.1 导入权限 --手动导入:QX_功能，QX_角色，QX_用户，QX_数据类型 --再导入：QX_角色_权限，QX_用户_角色 --2.2 导入基础数据 insert into 分店(分店编号,分店名称,联系电话,地址)values('ZB','总部','','') insert into 部门(部门ID,部门编号,部门名称,上级编号,当前编号,层次) values('B7715CFB-F96E-4DDB-B773-660B7A61DC92','01','人事部','','01',0) insert into 就职类型(就职类型编号,就职类型名称) values ('01','离职') insert into 就职类型(就职类型编号,就职类型名称) values ('02','在职') insert into 就职类型(就职类型编号,就职类型名称) values ('03','停薪留职') insert into CP_产品单位(单位ID,单位名称,是否明细单位) select 'E2DBE9C7-DADB-4A73-AD42-0958B745B4E6','勺',1 union select '1CBD7DE4-19A3-4B4B-B8DA-124654831C11','套',1 union select '6C581DB3-D5EE-4B57-9E58-197F66FD197C','份',1 union select '23851F53-9187-44BA-85F6-26ABE4769561','打',0 union select '37C45461-3A05-49E1-A76E-3C7FF1E5B2B1','把',0 union select 'C05B2FA3-C294-4FE6-A1C8-5B1A531FFA6A','堆',0 union select '38ABBF23-6500-44ED-9208-5EF3C3257D23','包',1 union select '9DE60A9B-30A8-4091-BA21-7B8F65A49BE1','kg',1 union select '687C3432-C955-433B-8604-83E53D7B02B3','条',1 union select 'F0761DF2-D195-4FF6-9075-856CEA04FC61','桶',0 union select 'E20FEF68-0C3F-40E8-8947-8DAB3A32B456','ML',1 union select '7C5CB3A4-DD12-4B6E-A42B-8F0B9DC4BB50','个',0 union select '033E3C0D-6FDB-42A4-A7A5-A234FD7ED9C9','支',1 union select '3C07FDFD-57EC-4F4E-AFFA-A5E01F89A65C','罐',0 union select '496A550E-518C-477C-BC9F-BB95091C6CEE','瓶',0 union select '06A08E06-97C6-45A4-961A-BC882A447BD6','枚',0 union select '331D637C-626D-4A29-82E7-E55D945A9631','克',1 union select '922C4FFC-20EB-4582-98D9-EBDFA9B6304B','粒',1 union select 'C0F7F6E6-4179-43F5-856A-F14EB3B84E1B','件',1 union select '91AD1135-EF18-4201-A9E7-F8E3C8BD2F50','盒',0 insert into CP_产品类别(产品类别ID,类别名称,当前编号,层次) values('649CE0F7-82AF-453B-9910-0FE478C6042E','所有类别','1',0) insert into XM_项目_类别(项目类别ID,类别名称,项目分类全称) values('3576DEA8-BB6F-4E17-A818-52FF91C70861','所有类别','所有类别') insert into XM_项目_类型(ID,类型名称,PID) select '07A51287-2EFC-4FAF-9D03-9155F36D98B6','所有类别',NULL union select 'B3B6D862-05D3-4772-9827-15A0BB1D4802','产品','07A51287-2EFC-4FAF-9D03-9155F36D98B6' union select '3E0BD82A-1DC7-41F6-9B2E-84E50D45EBD1','项目','07A51287-2EFC-4FAF-9D03-9155F36D98B6' insert into TC_套餐_类别(套餐类别ID,类别名称,套餐分类全称) values('C4E52E88-11D2-4734-A711-38BDCD6AC3C6','所有类别','所有类别') insert into 付款方式(付款方式名称,BuyPackage,Deposit,BuWei,DingJin,TuiKa,QianKuan) select '现金',1,1,1,1,1,1 union select '银行刷卡',1,1,1,1,1,1 union select '现金券',0,0,0,0,1,0 union select '免单',1,0,0,0,0,0 union select '订金',1,1,1,0,1,1 union select '挂房账',1,0,0,1,0,0 union select '欠款',1,0,0,0,0,0 union select '员工垫付',1,1,1,1,0,1 union select '会员卡',1,0,0,0,0,0 union select '内部消费',1,0,0,0,0,0 union select '外币卡',1,1,1,1,1,1 insert into 籍贯(籍贯编号,籍贯名称) select '01','广东' union select '02','广西' union select '03','台湾' union select '04','香港' union select '05','湖北' union select '06','湖南' union select '07','河北' union select '08','河南' union select '09','安徽' union select '10','江苏' union select '11','山东' union select '12','浙江' union select '13','青海' union select '14','新疆' union select '15','福建' union select '16','内蒙古' union select '17','江西' union select '18','云南' union select '19','贵州' union select '20','陕西' union select '21','山西' union select '22','海南' union select '23','西藏' union select '24','重庆' union select '25','四川' union select '26','甘肃' union select '27','宁夏' union select '28','澳门' insert into 民族(民族编号,民族名称) select '01','汉族' union select '02','蒙古族' union select '03','满族' union select '04','朝鲜族' union select '05','赫哲族' union select '06','达斡尔族' union select '07','鄂温克族' union select '08','鄂伦春族' union select '09','回族' union select '10','东乡族' union select '11','土族' union select '12','撒拉族' union select '13','保安族' union select '14','裕固族' union select '15','维吾尔族' union select '16','哈萨克族' union select '17','柯尔克孜族' union select '18','锡伯族' union select '19','塔吉克族' union select '20','乌孜别克族' union select '21','俄罗斯族' union select '22','塔塔尔族' union select '23','藏族' union select '24','门巴族' union select '25','珞巴族' union select '26','羌族' union select '27','彝族' union select '28','白族' union select '29','哈尼族' union select '30','傣族' union select '31','僳僳族' union select '32','佤族' union select '33','拉祜族' union select '34','纳西族' union select '35','景颇族' union select '36','布朗族' union select '37','阿昌族' union select '38','普米族' union select '39','怒族' union select '40','德昂族' union select '41','独龙族' union select '42','基诺族' union select '43','苗族' union select '44','布依族' union select '45','侗族' union select '46','水族' union select '47','仡佬族' union select '48','壮族' union select '49','瑶族' union select '50','仫佬族' union select '51','毛南族' union select '52','京族' union select '53','土家族' union select '54','黎族' union select '55','畲族' union select '56','高山族' insert into 学历(学历编号,学历名称) select '01','本科' union select '08','博士' union select '09','博士后' union select '04','初中' union select '02','大专' union select '06','高中' union select '05','小学' union select '07','研究生' union select '10','中技' union select '03','中专' insert into 职位(职位编号,职位名称) select '01','美疗师' union select '02','顾问' union select '03','技术主管' union select '04','店经理' union select '05','客服' union select '06','工程师' union select '07','行政主管' union select '08','稽查会计' union select '09','后勤' union select '10','收银员' union select '11','助理' union select '12','美容导师' union select '13','管理员' union select '14','统计' union select '15','发型师' union select '16','发型助理' union select '17','瑜伽老师' union select '18','销售主管' union select '19','公司经理' union select '20','财务经理' union select '21','行政财务' insert into 专业(专业编号,专业名称) select '01','计算机科学' union select '02','营养学' union select '03','美学医疗' union select '04','计算机软件' union select '05','IT-管理' union select '06','金融' union select '07','采购' union select '08','广告' union select '09','娱乐' union select '10','贸易' union select '11','美术' union select '12','法律' union select '13','财会' union select '14','经济管理' union select '15','机电一体化' union select '16','土木工程' union select '17','英语' union select '18','文秘' union select '19','物流' union select '20','平面设计' insert into 员工档案(员工ID,姓名,员工编号,部门ID,就职类型编号,职位编号,分店编号,性别) values('1E338D99-0C5F-4970-80D3-7F0C54C86719','管理员','admin','B7715CFB-F96E-4DDB-B773-660B7A61DC92', '02','06','ZB','男') 

--查询数据库中有标识列的表
declare
@TableName varchar(500),@result int
set @TableName=''
set @result=0

DECLARE  my_cursor CURSOR FOR  
            select distinct o.name from sysobjects o left join  sysindexes i         
on o.id =i.id         
where o.type='u'   
OPEN my_cursor  
FETCH NEXT FROM my_cursor INTO @TableName  
  WHILE @@FETCH_STATUS = 0  
    BEGIN  
        
  Select @result= OBJECTPROPERTY(OBJECT_ID(@TableName),'TableHasIdentity')
  if @result=1
	select @TableName
      
      FETCH NEXT FROM my_cursor INTO @TableName  
    END  
CLOSE my_cursor   
DEALLOCATE my_cursor