MS-SQL 숫자,텍스트,문법 [ in , like , Math ]

--distinct
중복제거

select distinct job
from emp

--exec sp_help
시스템 내장변수
테이블정보.
exec sp_help emp

비트 연산
2진수변환

--in
Select ename, sal, deptno, job
from emp
where deptno in(10,20)

10보다 크고 20보다 적다.

--like
select ename, sal
from emp
where ename like '%e%'

문자열에서 시작하는 위치

where ename like '_a%' 두번째가 a인사람
where ename like '[as]%' a나 s로 시작하는 사람.
where ename like '[^s]%' s로 시작하지 않는 사람.

 

select reverse('abcdef') as reverse; // 역방향정렬
select replace('abcdef','bc','kkk'); // bc를 kkk로 대체
select STUFF('abcdef',2,3,'kkk'); // 2~3번까지를 kkk로 대체
select round(123.456,1); // 소수점이하 1자리까지 출력
select round(123.456,-1); // 1번자리까지 출력
select power(2,3); // 제곱
select ceiling(123.456);//124 가로안에 있는 숫자보다 큰 정수 출력.
select ceiling(-123.456); //-123가로안에 있는 숫자보다 큰 정수 출력.
select floor(123.456);// 123 // 보다 작은 최대정수
select floor(-123.456);//-124 // 보다 작은 최대정수
select sqrt(9);//제곱근함수(루트)
select SQUARE(9);//81
select abs(100);//절대값
select abs(-100);//절대값

select reverse('abcdef') as reverse;
select replace('abcdef','bc','kkk') as replace;
select STUFF('abcdef',2,3,'kkk') as STUFF;
select round(123.456,1) as round;
select round(123.456,-1) as round;
select power(2,3) as power;
select ceiling(123.456) as ceiling;
select ceiling(-123.456) as ceiling;
select floor(123.456) as floor;
select floor(-123.456) as floor;
select sqrt(9) as sqrt;
select SQUARE(9) as SQUARE;
select abs(100) as abs;
select abs(-100) as abs;

//소스
--select * from emp

/*select empno, ename
from emp
go*/
/*
use master

select *
from test408.dbo.emp
*/

/*select 7+5
select 7*4
select 'tom' + 'bill'*/

select ename as [사원이름], deptno as [부서이름]
from emp

select distinct job
from emp

exec sp_help emp

Select empno, sal, sal*12 As [연봉]
From emp

Select ename, sal, comm, sal+isnull(comm,0)
From emp

Select ename, sal, deptno, job
from emp
where job = 'salesman'

Select ename, sal, deptno, job
from emp
where sal >= 1500 and sal < 3000

Select ename, sal, deptno, job
from emp
where deptno in(10,20)
--where sal between 1500 and 3000

select ename, sal
from emp
where ename like '[s]%'