case sensitive check
1. return names contain upper case
select id, name from a where name<>lower(name) collate sql_latin1_general_cp1_cs_as
2. return same name but diff case
select id, a.name, b.name from a inner join b on a.name=b.name where a.name<>b.name collate sql_latin1_general_cp1_cs_as
case when
1. calculate sum of different group
国家（country） 人口（population）
中国 600
美国 100
加拿大 100
英国 200
法国 300
日本 250
德国 200
墨西哥 50
印度 250
result:
洲 人口
亚洲 1100
北美洲 250
其他 700
select sum(population),
case country
when '中国' then '亚洲'
when '印度' then '亚洲'
when '日本' then '亚洲'
when '美国' then '北美洲'
when '加拿大' then '北美洲'
when '墨西哥' then '北美洲'
else '其他' end
from table_a
group by case country
when '中国' then '亚洲'
when '印度' then '亚洲'
when '日本' then '亚洲'
when '美国' then '北美洲'
when '加拿大' then '北美洲'
when '墨西哥' then '北美洲'
else '其他' end;
2. calculate total amount of different pay level
select
case when salary <= 500 then '1'
when salary > 500 and salary <= 600 then '2'
when salary > 600 and salary <= 800 then '3'
when salary > 800 and salary <= 1000 then '4'
else null end salary_class,
count(*)
from table_a
group by
case when salary <= 500 then '1'
when salary > 500 and salary <= 600 then '2'
when salary > 600 and salary <= 800 then '3'
when salary > 800 and salary <= 1000 then '4'
else null end;
3. calculate sum of different group with multiple columns
国家（country） 性别（sex） 人口（population）
中国 1 340
中国 2 260
美国 1 45
美国 2 55
加拿大 1 51
加拿大 2 49
英国 1 40
英国 2 60
result:
国家 男 女
中国 340 260
美国 45 55
加拿大 51 49
英国 40 60
select country,
sum( case when sex = '1' then
population else 0 end), --male population
sum( case when sex = '2' then
population else 0 end) --female population
from table_a
group by country;
cast & convert (change data type)
cast is compatible to both sql server and mysql, convert is designed for sql server, and it can have more styles and specially useful for datetime (check datetime part)
select cast(1.23 as int) --return 1 select convert( int,1.23) --return 1
create a column of numbers (usually ids)
declare @startnum int=1000 --start
declare @endnum int=1020 --end
;
with gen as (
select @startnum as num
union all
--change number+ i to adjust gap i
select num+3 from gen where num+1<=@endnum
)
select * from gen
option (maxrecursion 10000)
num
1000
1003
1006
1009
1012
1015
1018
1021
create a column of strings from one long string
;with split(stpos,endpos)
as(
select 0 as stpos, charindex(',','alice,jack,tom') as endpos
union all
select endpos+1, charindex(',','alice,jack,tom',endpos+1) from split where endpos > 0
)
--ltrim rtrim to get rid of white space before start or after end of str
select rtrim(ltrim(substring('alice,jack,tom',stpos,coalesce(nullif(endpos,0),len('alice,jack,tom')+1)-stpos))) as name into #temp
from split
name
alice
jack
tom
create a table of lots of strings in same column
select * into #temp from (values (1,'alice'),(2,'jack'),(3,'tom')) as t(id,name)
id name
1 alice
2 jack
3 tom
create a temp table (copy a table)
1. from a existing table, no need create table (not copy indexing or primary key)
select id, name, 'placeholder' as sex into #temp from a
trick to copy a table structure(cols and datatype) but not content
--0=1 to not copy any rows select id, name into #temp from a where 0=1 --the above query equals to select id, name into #temp from #temp1 delete from #temp1
2. create temp table (lifespan: current session, drop on close tab)
create table #tmpstudent(id int identity(1,1) primary key,name varchar(50),age int) insert into #tmpstudent select id,name,age from #tmpstudent
3. global temp table (##temp, can visit from other tab, drop on close tab where it is created)
4. using table variable (lifespan: current transaction, drop after running query block)
declare @temp table ( id int, name varchar(20), age int )
datetime
1. current date/datetime/utc date, convert datetime to date only
select getdate() select getutcdate() select cast(getdate() as date) --date only select convert(date, getdate() ) --date only
2. tomorrow/yesterday, next/last hour (simple nearby datetime)
-- add or minus is on day basis select getdate()+1 --tomorrow select getdate()-1 --yesterday -- need to be 24.0 to return float select getdate()+1.0/24 --next hour select getdate()-1.0/24/2 --last 30 min
3. add/minus any period for a date (use with 4.datediff)
--result is already datetime select dateadd(yy,-2,'07/23/2009 13:23:44') --2 years ago select dateadd(mm,5, dateadd(dd,10,getdate())) --5 month and 10 days later
the datepart can be ‘year’ or ‘yy’ or ‘yyyy’, all same
4. datediff of 2 datetime ( =2nd-1st, result is + or – interger)
select datediff(mi,getdate()+1.0/24, getdate()-1.0/24) -- return -120 select datediff(dd,'2019-11-23', '2019-12-23') --return 30
5. generate any datetime
select cast('2019-10-23 23:30:59:883' as datetime) --'yyyy-mm-dd'
select cast('2019/10/23 23:30:59:883' as datetime) --'yyyy/mm/dd' use ':' for ms
select cast('10-23-2019 23:30:59.883' as datetime) --'mm-dd-yyyy' use '.' for ms
select cast('10/23/2019 23:30:59.883' as datetime) --'mm/dd/yyyy'
--same to use convert
select convert(date, '07/23/2009' )
6. get day/week/month/year part of a datetime
--these pairs are same to get dd,mm,yy part of a datetime, return integer select datepart(dd,getdate()),day(getdate()) select datepart(mm,getdate()),month(getdate()) select datepart(yyyy,getdate()),year(getdate()) select datepart(dy,'2019-08-11') --get day of year: 223 select datename(mm,'2000-5-17') --return 'may' select datename(weekday,'2000-5-17') --return 'wednesday'
7. convert datetime format (input need to be datetime only, result is a string)
-- not working!!!! return '2019-05-17', as it detect input is string, 103 is ignored
select convert(varchar, '2019-05-17', 103)
--input is datetime, reutrn formatted string '17/05/2019'
select convert(varchar, cast('2019-05-17' as datetime), 103)
for a full list of datetime format code (smilar to 103)
| date only formats | ||
| format # | query | sample |
|---|---|---|
| 1 | select convert(varchar, getdate(), 1) | 12/30/06 |
| 2 | select convert(varchar, getdate(), 2) | 06.12.30 |
| 3 | select convert(varchar, getdate(), 3) | 30/12/06 |
| 4 | select convert(varchar, getdate(), 4) | 30.12.06 |
| 5 | select convert(varchar, getdate(), 5) | 30-12-06 |
| 6 | select convert(varchar, getdate(), 6) | 30 dec 06 |
| 7 | select convert(varchar, getdate(), 7) | dec 30, 06 |
| 10 | select convert(varchar, getdate(), 10) | 12-30-06 |
| 11 | select convert(varchar, getdate(), 11) | 06/12/30 |
| 12 | select convert(varchar, getdate(), 12) | 061230 |
| 23 | select convert(varchar, getdate(), 23) | 2006-12-30 |
| 101 | select convert(varchar, getdate(), 101) | 12/30/2006 |
| 102 | select convert(varchar, getdate(), 102) | 2006.12.30 |
| 103 | select convert(varchar, getdate(), 103) | 30/12/2006 |
| 104 | select convert(varchar, getdate(), 104) | 30.12.2006 |
| 105 | select convert(varchar, getdate(), 105) | 30-12-2006 |
| 106 | select convert(varchar, getdate(), 106) | 30 dec 2006 |
| 107 | select convert(varchar, getdate(), 107) | dec 30, 2006 |
| 110 | select convert(varchar, getdate(), 110) | 12-30-2006 |
| 111 | select convert(varchar, getdate(), 111) | 2006/12/30 |
| 112 | select convert(varchar, getdate(), 112) | 20061230 |
| time only formats | ||
| 8 | select convert(varchar, getdate(), 8) | 00:38:54 |
| 14 | select convert(varchar, getdate(), 14) | 00:38:54:840 |
| 24 | select convert(varchar, getdate(), 24) | 00:38:54 |
| 108 | select convert(varchar, getdate(), 108) | 00:38:54 |
| 114 | select convert(varchar, getdate(), 114) | 00:38:54:840 |
| date & time formats | ||
| 0 | select convert(varchar, getdate(), 0) | dec 12 2006 12:38am |
| 9 | select convert(varchar, getdate(), 9) | dec 30 2006 12:38:54:840am |
| 13 | select convert(varchar, getdate(), 13) | 30 dec 2006 00:38:54:840am |
| 20 | select convert(varchar, getdate(), 20) | 2006-12-30 00:38:54 |
| 21 | select convert(varchar, getdate(), 21) | 2006-12-30 00:38:54.840 |
| 22 | select convert(varchar, getdate(), 22) | 12/30/06 12:38:54 am |
| 25 | select convert(varchar, getdate(), 25) | 2006-12-30 00:38:54.840 |
| 100 | select convert(varchar, getdate(), 100) | dec 30 2006 12:38am |
| 109 | select convert(varchar, getdate(), 109) | dec 30 2006 12:38:54:840am |
| 113 | select convert(varchar, getdate(), 113) | 30 dec 2006 00:38:54:840 |
| 120 | select convert(varchar, getdate(), 120) | 2006-12-30 00:38:54 |
| 121 | select convert(varchar, getdate(), 121) | 2006-12-30 00:38:54.840 |
| 126 | select convert(varchar, getdate(), 126) | 2006-12-30t00:38:54.840 |
| 127 | select convert(varchar, getdate(), 127) | 2006-12-30t00:38:54.840 |
delete duplicate rows (entire same or partialy same)
1. select duplicate rows based on 1 column
select * from students where id in (
select id from students
group by id having count(*)>1
)
2. select duplicate rows based on multiple columns
select * from students a
right join (
select firstname, lastname from students
group by firstname, lastname having count(*)>1
) b
on a.firstname=b.firstname and a.lastname=b.lastname
3. select rows that has unique combination of colums(filter out all duplicate rows)
select * from students except(
select a.id --need to select all columns here
,a.firstname
,a.lastname
,a.dob from students a
right join (
select firstname, lastname from students
group by firstname, lastname having count(*)>1
) b
on a.firstname=b.firstnameand a.lastname =b.lastname
)
4. select/delete rows of totally identical values
select distinct * from tablename --save the result equals to delete duplicated rows already
5. delete duplicate rows in table which has unique id
delete from #temp
where id not in(
select max(id) from #temp
group by col1, col2 --the columns used when checking duplicate
having count(*)>1
)
6. delete duplicate rows in table which does not have id
6.1 delete directly from original table by “partition” keyword
with tempvw as (
select
*,
row_number() over ( --over() is required for row_number()
partition by --this reset the rownumber to 1 for different group
col1, col2 --which used as identifier to check duplicate
order by --order by is required in over()
col1, col2 --keep same as above
) row_num
from
yourtable
)
delete from tempvw where row_num > 1
select * from yourtable --duplicated rows should be removed in original table
6.2 add unique id first so it is similar as point 5
--use views to add rowid for table without unique id
with tempvw as(
select row_number() over (order by surveytypeid, surveyid ) as rowid,*
from yourtable
)
--define 2 views together, tempvw2 is all duplicated rows
,tempvw2 as (
select rowid,a.col1,a.col2
from tempvw a
right join (
select col1, col2 from tempvw
group by col1, col2 having count(*)>1
) b
on a.col1=b.col1 and a.col2=b.col2
)
--query after view, delete rows in view will delete original table
delete from tempvw where rowid in (
--return all duplicated rows except 1 row for each group that we will keep
select rowid from tempvw2 where rowid not in (
--return 1 row for each identifier of duplicated rows
select min (rowid) from tempvw2 group by col1, col2 having count(*)>1
)
)
select * from yourtable --duplicated rows should be removed in original table
except (check difference between 2 tables of same colums) & intersect
1. rows which included in a but not b
select * from a except select * from b
2. return any diff bewteen a and b
select * from a except select * from b union all select * from b except select * from a
3. return duplicated rows between a and b
select * from a intersect select * from b
exec output to variable
declare @temp table(id int,name varchar(50),sex varchar(10)) declare @sql varchar(max)= 'select id,name,''male'' from student where id<3' insert into @temp exec (@sql)
exists
1. to add any condition for the select (same as if)
select col1, col2 from a where exists (select 1 from b where id=99) --inside exists you can select 1 or anything, it will return true equally
2. to select new user in a but not in b
select id, name from a where not exists (select 1 from b where b.id=a.id) --this equals to use in keyword select id, name from a where id not in (select id from b)
group by (only work with count(), avg(), max(), min(), sum() )
--student number for each class select class,count (*) as total from student group by class --average score for each class select class,avg(score) as avgscore from student group by class --highest score for each class select class,max(score) as highestscore from student group by class --total donation for each class select class,sum(donation) as totaldonation from student group by class
to get top x rows or the xth place in each group, use row_number()
import data from excel
select * --into #cars
from openrowset('microsoft.ace.oledb.12.0',
'excel 12.0 xml;hdr=yes;database=c:\cars.xlsx','select * from [sheet1$]');
insert into
1. mutiple rows with values
insert into #temp(id,name) values (1,'alice'),(2, 'jack')
2. from existing tables
insert into #temp(id,name, sex) select id, name,'male' from students where sex=1
3. from exec (assign exec output to variable)
declare @temp table(id int,name varchar(50),sex varchar(10)) declare @sql varchar(max)= 'select id,name,''male'' from student where id<3' insert into @temp exec (@sql)
join
1. cross join
()
select * from a cross join b select * from a,b --same as above
2. left join, right join, inner join
left join: contains all rows from left table a, if a.key=b.key, return result in new table, if multiple b.key match a.key, return multiple rows, if no b.key match, return row with null values
inner join: only return if a.key=b.key, can be one to one or one to many
like and regex
()
- the percent wildcard (%): any string of zero or more characters.
- the underscore (_) wildcard: any single character.
- the [list of characters] wildcard: any single character within the specified set.
- the [character-character]: any single character within the specified range.
- the [^]: any single character not within a list or a range.
not start with special symbol, 3rd character is number or letter
select * from where name like ' [^.$#@-]_ [a-z0-9]%'
login history delete for ssms
c:\users\*********\appdata\roaming\microsoft\sql server management studio\18.0\usersettings.xml
- open it in any texteditor like notepad++
- ctrl+f for the username to be removed
- then delete the entire
<element>.......</element>block that surrounds it.
random id (guid), string, number
1. random guid
select newid() --315fc5a3-be07-41bb-be4f-75055729fa5b
2. random string
select convert(varchar(255), newid())
3. random number (round to integer)
select rand() -- 0<=decimal<1 select rand()*15+5; -- 5<=decimal<20 (if include 20 need *16) select floor(22.6) --22 select ceiling(22.6) --23 select round(22.6,0) -- 23.0 select round(22.6,-1) --20.0
row_number(), rank() and dense_rank() (must use with over(order by …) )
0. create table
create table #student (id int, class int, score int ) insert into #student values(1,1,88) insert into #student values(2,1,66) insert into #student values(3,2,30) insert into #student values(4,2,70) insert into #student values(5,2,60) insert into #student values(6,3,70) insert into #student values(7,3,80)
1. add row id by row_number()
select *,row_number() over(order by class) rowid from #student
2. if there is identiacal value for the colomn used for order by: rank() and dense_rank()
select *,rank() over(order by class) rowid from #student --if 1st has 2 pp, next is 3rd select *,dense_rank() over(order by class) rowid from #student --if 1st has 2 pp, next is 2nd
3. partition by: assign row id for different group, each group start with 1
select *,row_number() over(partition by class order by class) rowid from #student
4. select top 2, the 2nd second place for each group
select * from (
select *,row_number() over(partition by class order by class) rowid from #student
)a where rowid<2
select * from (
select *,row_number() over(partition by class order by class) rowid from #student
)a where rowid=2
short keys for text selection
()
1. using shift+alt+(arrow key or cursor) to select block of values among multiple rows
2. using ctrl+shift+end to select text till end (ctrl+ end can move cursor to end)
3. using ctrl+shift+home to select text till start (ctrl+home can move cursor to end)
4. user ctrl+ arrow key can move cursor jump between words not letters
string edit
note: sql index start from 1 not 0
1. left and right
select left('hello world',5) --return: hello
select right('hello world!',6) --return:world!
2. substring
select substring('hello world',7,5) --return: world
3. replace (by expression or by index)
select replace('123456','34','new') --return 12new56
select stuff('123456',3,2,'new') --same as above, start index=3, length=2
4. split (not exist in sql, need use left+ right + charindex)
--split 'hello world' by space
select left('hello world',charindex(' ','hello world')-1)
select right('hello world',len('hello world')-charindex(' ','hello world'))
5. delete white space
select ltrim(' sample '); --return 'sample '
select rtrim(' sample '); --return ' sample'
6. delete enter, tab, space
--char(13)+char(10) = enter
print 'first line'+char(13)+char(10)+'second line' --2 lines
--char(9) is tab, the outsode replace delete all space
print replace(replace(replace(replace('first line
second line',char(13),''),char(10),''),char(9),''),' ','')
7. search a regex in string
select patindex('%[mo]%', 'w3schools.com'); --return m or o which appear first
8. repeat string a few times
select replicate('hello world ',3) --return: hello world hello world hello world
9. revers a string by characters
select reverse('1234567') --return 7654321
10. create an empty fixed length string (only contains spaces)
select 'a'+space(5)+'b' --return a b
top
1. select rows between m and n place of highest score
select top 2 * from ( --between 4 and 5, 5-4+1=2
select top 5 * from #student order by score desc)a
order by score
2. select 2nd second place by add row_number()
-- if there are multiple highest score, will select highest score
select * from (
select *,row_number() over( order by score desc) rowid from #student
)a where rowid=2
--if there are multiple highest score, still select second highest score
select * from (
select *,rank() over( order by score desc) rowid from #student
)a where rowid=2
-- rowid between m and n -- rows between order of the m place to n place
transaction
1. begin, rollback,commit tran
declare @isdebug bit=1
begin tran
-- insert/update/delete queries
if @isdebug=1 --test run
begin
rollback tran
end
else -- prod run
begin
commit tran
end
2. trasaction with try/catch
declare @isdebug bit=1
begin try
begin tran
if @isdebug=0 --test run
begin
-- insert/update/delete queries
end
else --prod run
begin
-- insert/update/delete queries
end
commit tran --commit if above code has no error
end try
begin catch
rollback tran --if any error jump to this to rollback
select error_number() as errornumber, error_message() as errormessage, error_procedure() as errorprocedure
end catch
union, union all
1. union not return duplicated rows (by duplicated mean all the values are exactly same)
2. union all return all rows include duplicated rows
3. both union and union all need to have exactly same number of total columns (col name can be diff but type need to be same)
4. union all is much faster than union
update one colomn from column in another table
update a
set a.marks = b.marks
from tempdataview a
inner join tempdata b
on a.name = b.name
view (with … as )
**delete or update view will influence original table, delete or update or insert values will influence on view
with studentvw as(
select top 100 row_number() over (order by surveytypeid, surveyid ) as rowid,*
from ##temp order by channelid -- if use order by must have top keyword
)
select * from studentvw --must come with a query and only 1 query