Create a Table


------SQL Server：----
select * into temp_Org_employee from org_employee

------excel import:----

SELECT * INTO #temp
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\temp\******.xlsx','SELECT * FROM [sheet1$]');

Always Use

insert into table(field1,field2) values(value1,value2)

delete from table where ...

select * from table where ...

select * from table where field like ’%value%’    

select count(1) as totalcount from table

select max(field) as maxv from table

select min(field) as minv from table

select sum(field) as sumv from table

select avg(field) as avgv from table

---Order by 和 Group by:

---Order by按照条件来进行正序和逆序排序。

---根据group by中条件来分成若干组，然后进行统计，必须带分组统计函数：count,sum,max,min,avg等。

---where  1=1，1=2，1<>1

---“1=1” 是表示选择全部， “1=2”全部不选， “1<>1”全部不选


---- union all 
SELECT 'GUID-EW83-4724-A454',tabletemp.v,'amy.ye@test.com',GETDATE(),1
FROM (select 'temp1' union all 
select 'temp2' union all 
select 'temp3') tabletemp(v)

select list.DangerWords into #tempdangerwords
		FROM (
		select 'bullying' union all 
		select 'bullied' union all
		select 'bully'
		) list (DangerWords)	
select * from  #tempdangerwords

---1、子查询(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b ) 或 select a,b,c from a where a IN (...)

--2、between,between查询数据范围内的结果集，查询数据范围时包括了边界值,not between不在数据范围内的结果集。

select * from table where a between '值1' and  '值2'

select * from table where a not between ‘数值1‘ and ‘数值2’

---3、in，表是在数据范围内的结果集， not in 反之。

select * from table where a  in (‘值1’,’值2’,’值3’,’值4’,’值5’)

---4、两张关联表，删除表1中已经在表2中没有的信息

delete from table1 where not exists (select * from table2 where table1.field1=table2.field1 )

---5、多表内连接查询公共数据：

select * from A  join B on A.a=B.a  join C on A.a=C.a where...

---6、复制表(只复制表结构,源表名：a 新表名：b)

select * into b from a where 1<>1(仅用于SQL Server)

or  select top 0 * into b from a

---7、拷贝表(拷贝数据,源表名：a 目标表名：b) 

insert into b(a, b, c) select d,e,f from b;

---8、跨数据库之间表的拷贝(具体数据使用绝对路径) 

insert into b(a, b, c) select d,e,f from bin ‘具体数据库’ where ...

---例子：..from b in'"&Server.MapPath(".")&"\data.mdb"&"' where...

---9、前n条记录

select top n * form table where...

---10、删除表或者表数据

TRUNCATE TABLE tablename  --清空表，不留痕迹     Delete from TABLE tablename  --仅仅删除数据       drop tabel tablename --删除表，包括表结构

---11、选择从第m到第n条记录的结果集

select top (n-m) * from (select top n * from tablename order by id asc)  table order by id desc

---12、随机选择记录

select newid()

---13、随机取出n条数据

select top n * from tablename order by newid()

---14、删除重复记录，请用查询分析器分析性能开销！

delete from tablename where id not in (select max(id) from tablename group by column1,column2,column3...)

select distinct * into temp from tablename;

delete from tablename;

insert into tablename select* from temp;

---15、case when then使用

select type,(case columnname when 'xx' then 'oo'  when 'xxx' then 'ooo' else 'over' end) FROM tablename

Date Format

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type. With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

-------convert----------
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
--------format----------
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date
SELECT FORMAT (getdate(), 'MM-dd-yy') as date
--------yesterday-------
SELECT cast(dateadd(d,-1,getdate()) as date)

http://www.sql-server-helper.com/tips/date-formats.aspx

https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

Explicit transactions

When a DELETE or UPDATE statement it is recommanded to explicitly use BEGIN TRAN to make sure the statement is correct and you can get the correct number of results returned.

begin tran

insert into tablename values ('e@123.com.au',2,3,GETUTCDATE(),null)

rollback tran


begin tran

update tablelist set [To]= REPLACE([To],'123@example.com.au', '456@example.com.au') where (AreaCode LIKE 'W%')

rollback tran

Search the procs

----search by name
SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
WHERE m.definition Like '%Whatever%';

------search the update procs
SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC

http://www.sql-server-helper.com/tips/date-formats.aspx

https://www.w3schools.com/sql/

https://www.hocode.com/OrgTec/DB/0006.html

https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/