sql常用方法

• 判断是否存在数据库.表.字段

--判断是否存在数据库
SELECT  * FROM    sys.databases WHERE   name = 'LeetCode'
--判断是否存在表
SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('LeetCode.dbo.Person');
--判断是否存在字段
SELECT * FROM syscolumns WHERE name ='name' AND id=OBJECT_ID('Person')

• 创建数据库

USE LeetCode;
GO
--delete the LeetCode database if it exists
IF EXISTS ( SELECT  * FROM    sys.databases WHERE   name = 'LeetCode' )
    BEGIN   
        DROP    DATABASE LeetCode;--如果存在则删除
        CREATE DATABASE LeetCode;--重新创建数据库
    END;    

• 创建表

--sqlServer版
USE LeetCode
--delete the Person datatable if it exists
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('LeetCode.dbo.Person'))
BEGIN
     DROP TABLE Person
END

CREATE TABLE Person
(
    id INT PRIMARY KEY IDENTITY(1,1),--主键,自增
    name NVARCHAR(20) NOT NULL DEFAULT '',--默认值为''
    email NVARCHAR(30) DEFAULT '' UNIQUE,--唯一
    age INT NOT NULL DEFAULT 0 CHECK(age>0 AND age<200), --age只能时0-200之间
    sex BIT NOT NULL DEFAULT 1
)

--mysql 版
CREATE TABLE  `test`.`user_info` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` NVARCHAR(45) NULL,
  `age` int(11) default NULL,
  primary key(`id`),
  Key `name_index`(`name`)
  )engine=InnoDb default charset=utf8

删除数据库表

• 查看

use test;
show Databases;
show tables;
DROP TABLE table_name ; --删除数据表
alter table test drop column testcol;-- 删除表字段
alter table test add COLUMN new2 VARCHAR(20) NOT NULL;--新增字段
insert into user_info (name,age) values('a',20);--插入数据
CREATE INDEX index_name ON table_name (column_name);--创建索引
select * from user_info where name like '%wmin';--模糊查询
select name,count(*) from user_info group by name;--分组查看name个数
select age ,count(age) as ageCount from user_info group by age;--给count另起名
select coalesce(name,'总数'),sum(age) as sumAge from user_info group by name with rollup;--如果名字为空,用总数代替,查询age总年龄
select count(*) as count,name,age from user_info group by name,age;--查询name和age都重复的个数

• 对新建的表进行联合查询

SELECT * FROM dbo.aTable right JOIN dbo.bTable ON dbo.aTable.id = dbo.bTable.aid

• 对联合查询的结果新建视图

CREATE VIEW Sys_ProjectDetailView as
SELECT  * FROM dbo.aTable right JOIN dbo.bTable ON dbo.aTable.id = dbo.bTable.aid

• 批量加主健，过滤表已存在的主健

DECLARE @Sql NVARCHAR(max)=''
SELECT  @Sql=@Sql+'ALTER TABLE ' + b.name + ' ADD Constraint PK_' + b.name
        + ' PRIMARY KEY (' + a.name + ');'
FROM    sys.columns AS a
        INNER JOIN sys.tables AS b ON b.object_id = a.object_id
WHERE   a.is_identity = 1
        AND NOT EXISTS ( SELECT 1
                         FROM   sys.key_constraints
                         WHERE  object_id = a.object_id
                                AND b.type = 'PK' );
EXEC(@Sql)

• 导入导出

1. 在源数据库生成表脚本，再导入数据就行了
2. 打开SQL按F7，调出“对象资源管理器详细信息”，在左侧选中“存储过程“或”Stored Procedures“目录，到右侧窗口，全选所有的要导入的sp，在选中的sp上鼠标右键。”生成存储过程脚本为”->“Drop和Create到”->“File”，这样就可以导出所有sp，将保存后的file到产品环境下运行一下就可以了。

• 关于创建索引

--直接创建索引(length表示使用名称前1ength个字符)  
CREATE INDEX index_name ON table_name(column_name(length))  
--修改表结构的方式添加索引  
ALTER TABLE table_name ADD INDEX index_name ON (column_name)  
--创建表的时候同时创建索引  
CREATE TABLE `table_name` (  
`id` int(11) NOT NULL AUTO_INCREMENT ,  
`title` char(255) NOT NULL ,  
PRIMARY KEY (`id`),  
INDEX index_name (title)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;  
--删除索引  
DROP INDEX index_name ON table_name;  
  
--建立复合索引 
CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);  
--注意命名时的习惯了吗？使用"表名_字段1名_字段2名"的方式  
SHOW INDEX FROM table_name;--显示索引

• 更新数据

SET SQL_SAFE_UPDATES=0; //关闭安全模式
update user_info set name='liyue' where age = 15;
delete from user_info where name='h';

• 事务,用 BEGIN, ROLLBACK, COMMIT来实现

begin;--开启事务
insert into user_info (name,age) value('www',11);--插入数据
commit;--提交事务
rollback; -- 回滚

• ALTER命令

show columns from user_info;--显示table字段
alter table user_info add testColumn int;--添加字段
alter table user_info modify testColumn char(20) first name;--在name字段前添加,after相反
ALTER TABLE tableName MODIFY j BIGINT NOT NULL DEFAULT 100;--修改默认字段
alter table user_info drop testColumn;--删除字段
show columns from user_info;--查看字段
alter table user_info modify testColumn char(20);--修改字段类型及名称
alter table tableName engine=myisam;--修改存储引擎
alter table tableName drop foreign key keyName;--删除外键约束：keyName是外键别名
ALTER TABLE tableName ALTER colName SET DEFAULT 1000;--修改字段默认值
ALTER TABLE tableName RENAME TO tableName2;--修改表名

• 将数据库表的字段变成自增方法：

--将表中字段删除
alter table [dbo].[MemberInfo] drop column MemberID 
--添加表中字段并使其自增
alter table [dbo].[MemberInfo] add MemberID int identity(1,1) primary key

• 复制表

show create table user_info; --显示创建表的结构语句,然后修改表明执行语句
INSERT INTO clone_tbl (runoob_id,
    ->                        runoob_title,
    ->                        runoob_author,
    ->                        submission_date)
    -> SELECT runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> FROM runoob_tbl;--复制数据

• 获取服务器元数据

SELECT VERSION(); --服务器版本信息
SELECT DATABASE(); -当前数据库名 (或者返回空)
SELECT USER(); --当前用户名
-- SHOW STATUS; --服务器状态
-- SHOW VARIABLES;--服务器配置变量

• 重复数据处理

select count(*) as count,name,age from user_info group by name,age;--name和age都重复的个数
--linq 写法

select age from user_info group by age;--读取不重复数据 with group by
select distinct age from user_info;--读取不重复数据 with distinct

• 重复数据处理 linq 方式

//单个group by 情况
 var names = from p in details
                    group p by p.name into g
                    select new {g.Key, nameCount = g.Count() };
//多个group by 情况
var names = from p in details
                    group p by new  { p.entryName,p.amount } into g
                    select new {g.Key.entryName, nameCount = g.Count() };

• 导出数据

SELECT * FROM runoob_tbl   INTO OUTFILE  '/tmp/tutorials.txt';

• 查询数据库mdf文件存放位置

select   filename   from   master.dbo.sysdatabases   where   name   LIKE  '%'

• 分页查询方法(row_number)

//c#
//对应的linq表达式 
Sys_UserInfo.Skip(20).Take(10).ToList();

-- Region Parameters
DECLARE @p0 INT = 20;
DECLARE @p1 INT = 10;
---- EndRegion
SELECT  [t1].[UserID] ,
        [t1].[LoginName] ,
        [t1].[LoginPass] ,
FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY [t0].[UserID], [t0].[LoginName], [t0].[LoginPass] ) AS [ROW_NUMBER] ,
                    [t0].[UserID] ,
                    [t0].[LoginName] ,
                    [t0].[LoginPass] ,
          FROM      [sys_UserInfo] AS [t0]
        ) AS [t1]
WHERE   [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER];

• cross/outer apply 用法
  将左表的每项值作为右输入参数,简单的说 apply 允许我们将前面结果集每一行的数据作为参数，传递到后面的表达式，后面的表达式可以是一个表值函数，或者select结果集。outer apply 与left join 类似,cross apply 与inner join 类似

-- Region Parameters
DECLARE @p0 NVARCHAR(100) = '';
-- EndRegion
SELECT  [t3].[value]
FROM    ( SELECT    [t0].[CompanyId]
          FROM      [sys_UserInfo] AS [t0]
          GROUP BY  [t0].[CompanyId]
        ) AS [t1]
        OUTER APPLY ( SELECT    COUNT(*) AS [value]
                      FROM      [sys_UserInfo] AS [t2]
                      WHERE     ( [t2].[UserRealName] <> @p0 )
                                AND ( [t1].[CompanyId] = [t2].[CompanyId] )
                    ) AS [t3];

• linq to sql 表达式语句
  下面用一个表格总结一下LINQ to SQL语句

Where       过滤；延迟
Select      选择；延迟
Distinct    查询不重复的结果集；延迟
Count       返回集合中的元素个数，返回INT类型；不延迟
LongCount   返回集合中的元素个数，返回LONG类型；不延迟
Sum         返回集合中数值类 型元素之和，集合应为INT类型集合；不延迟
Min         返回集合中元素的最小值；不延迟
Max         返回集合中元素的最大值；不延迟
Average     返回集合中的数值类型元素的平均 值。集合应为数字类型集合，其返回值类型为double；不延迟
Aggregate   根据输入的表达式获取聚合值；不延迟

• 存储过程-- 1.无参数简单存储过程

SET ANSI_NULLS ON -- value 符合null值规则
GO
SET QUOTED_IDENTIFIER ON --标识符可以用,且需用双引号引起来
GO
CREATE PROCEDURE user_proc
AS
BEGIN
    SET NOCOUNT ON; --不返回计数
    SELECT * FROM users;
    SET NOCOUNT OFF;  --打开返回计数
END
GO
-- EXEC dbo.user_proc    --执行存储过程

第一次创建存储过程后下次修改需将CREATE PROCEDURE改成ALTER PROCEDURE

• 存储过程-- 2 带参数存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[user_proc]
 @sname NVARCHAR(100)
AS
BEGIN
SELECT * FROM dbo.Users WHERE Name = @sname
END
GO  
-- exec user_proc ''

• 存储过程-- 3 输出变量

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[user_proc]
 @sname NVARCHAR(100),
 @emptyNameCount INT OUTPUT  --传出参数
AS
BEGIN
SET @emptyNameCount =( SELECT COUNT(*) FROM dbo.Users WHERE Name = @sname)
END
GO  

DECLARE @emptyNameCount int
exec user_proc '',@emptyNameCount OUTPUT
SELECT @emptyNameCount AS emptyNameCount

• 游标

DECLARE cursor_user CURSOR  FOR SELECT * FROM dbo.Users
OPEN cursor_user 
FETCH NEXT FROM cursor_user
while @@fetch_status=0
BEGIN
    UPDATE dbo.Users SET Name = 'wwmin' WHERE CURRENT OF cursor_user
    FETCH   NEXT FROM cursor_user   
END
CLOSE cursor_user
DEALLOCATE cursor_user

-- 601. 体育馆的人流量 解法用到的游标示例
DECLARE @sid INT;
DECLARE @sdate DATE;
DECLARE @speople INT;
DECLARE @startdate DATE;
DECLARE  @nextdate Date;
DECLARE  @thisN INT =0;
DECLARE my_coursor CURSOR FOR SELECT id,date,people FROM stadium WHERE people >=100 --定义游标
OPEN my_coursor --打开游标
FETCH NEXT FROM my_coursor INTO @sid ,@sdate,@speople; --获取游标值
SET @startDate=@sdate;
SET @nextDate=@sdate;
WHILE @@FETCH_STATUS=0 --循环遍历游标 当等于0时结束
BEGIN
IF(@sdate = DATEADD(DAY,1,@nextDate))
BEGIN
    SET @nextDate=@sdate;
    SET @thisN=@thisN+1;
END
ELSE 
BEGIN
    SET @startDate=@sdate;
    SET @nextDate=@sdate;
    SET @thisN=0;
END
FETCH NEXT FROM my_coursor INTO @sid,@sdate,@speople; --获取下一个游标值
END
CLOSE my_coursor; --关闭
DEALLOCATE  my_coursor;--删除游标引用
IF(@thisN >= 3)
BEGIN
    SELECT * FROM dbo.stadium WHERE date >= @startDate AND date <= @nextDate
END

• 回滚操作 transaction

BEGIN TRY   
    BEGIN TRANSACTION; --开启事务
    INSERT  INTO dbo.Users
            ( Name, Banned, Role )
    VALUES  ( N'a', -- Name - nvarchar(50)
              N'1', -- Banned - nvarchar(10)
              N'guest'  -- Role - nvarchar(50)
              );
    COMMIT TRANSACTION; -- 提交事务
END TRY
BEGIN CATCH
    SELECT  ERROR_NUMBER() AS errornumber; --显示错误数
    ROLLBACK TRANSACTION;   --回滚事务
END CATCH;

• 触发器

IF(OBJECT_ID('tri_user') IS NOT NULL)
DROP TRIGGER tri_user --如果已存在则删除触发器
GO
CREATE TRIGGER  tri_user  --创建触发器
ON dbo.Users -- 在哪个表上创建
FOR delete  --delete 触发器 ,相应的还有insert update触发器
AS
DECLARE @p_Id INT
SELECT @p_Id = Users_Id FROM deleted --删除的数据,该数据存在deleted表中,相应的还有inserted临时表
--但是没有updated临时表,而是用deleted和inserted两个临时表代替
DELETE  dbo.Person  WHERE user_id = @p_Id --做相应的操作
GO

-- instead of 触发器,instead of 触发器并不执行其定义的操作（insert、update、delete）
--而仅是执行触发器本身，并且会覆盖触发语句的操作
CREATE TRIGGER tri_user_insteadof
ON dbo.Users
INSTEAD OF UPDATE,INSERT,DELETE
AS 
DECLARE @count1 INT;
DECLARE @count2 INT;

SELECT @count1 = COUNT(1) FROM Deleted
SELECT @count2 = COUNT(1) FROM Inserted
IF(@count1>0 AND @count2>0)
BEGIN
    SELECT 'update 操作'
END
ELSE IF(@count1>0)
BEGIN
    SELECT 'delete 操作'
END
ELSE IF(@count2>0)
BEGIN
    SELECT 'insert 操作'
END
GO  

DISABLE TRIGGER tri_user ON dbo.Users;-- 关闭触发器
ENABLE TRIGGER tri_user ON dbo.Users; -- 启用触发器

select * from sys.triggers; --查询已存在的触发器
select * from sys.objects where type = 'TR';--查询已存在的触发器
select * from sys.trigger_events --查询触发器事件对象视图
exec sp_helptext 'tri_user' --查询创建触发器的 T-SQL 文本

• 定时器
  在规定的时间执行相应操作
  1.首先开启sql server 代理(SQL Server Agent),双击开启即可
  2.管理-->维护计划-->右键新建维护计划-->子计划的计划添加执行时机-->在工具箱里面选择维护计划任务,将任务拖到任务面板里面,经常用到的时备份数据库和执行T-SQL语句任务,双加任务编写语句
  3.保存之后在管理-->维护计划-->右键该任务选择执行即可生效