SQL常用语句记录

– 数据库链接字符串

-- MySQL
server=127.0.0.1;database=dbname;uid=sa;pwd=sa
-- Sql Server
Data Source=127.0.0.1; Initial Catalog=dbname; User Id=sa; Password=sa;
-- 信任的链接
Data Source=127.0.0.1; Initial Catalog=dbname; Integrated Security=SSPI;
-- Sqlite
Data Source=|DataDirectory|\dbname.db
Data Source=dbname.db

1. 清空表并重置自增id为1

-- 方法1:直接删除并整表，重构id
TRUNCATE TABLE table_name;
-- 方法2:删除表数据，修改auto_increment为1
DELETE FROM table_name;
ALTER TABLE table_name AUTO_INCREMENT=1;
-- Sqlite
DELETE FROM sqlite_sequence WHERE name='table_name';

2. Sql Server定位字符串替换

STUFF ( character_expression , start , length ,character_expression )

3. 判断字段是否为空

-- SQL Server
isnull(a,"")
-- Oracle/MariaDB
nvl(a,"")
-- MySql
ifnull(a,"")