Основы SQL #1

Показать описание

Основные элементы языка SQL (SQL Server)

-- Одиночное добавление
-- insert into tst_products (name, price) values ('prod 1', 20)

--массовое добавление
--insert into tst_products (name, price)
--select name + '-p', price*4 from tst_products

/*update tst_customers
set created = dateadd(day, -10, getdate()) , name = 'cust 5'
where id = 1
*/

/*delete tst_customers
where id = 1

--where id in (select id from ....)
*/

--select * from tst_customers
-- select * from tst_products

/*select *
from tst_orders
where customerID IN (2,4)
and created больше dateadd(week, -2, getdate())

order by [productID] desc, [count] desc
*/

/*
select Заказчик, [Количество заказов] from (
select customerID,
(select name from tst_customers where id = customerID) Заказчик,
count(*) 'Количество заказов'
from tst_orders
group by customerID
having count(*) больше 1
) t1
order by Заказчик
*/

/*
select name from tst_customers
where id in (select customerID from tst_orders group by customerID having sum([count]) больше 6)
*/

/*
*/
/*
select
from
where
group by
having
order by
*/

--select distinct customerID, productID from tst_orders
/*
select name from tst_customers
union all
select name from tst_products
*/

-- date
/*select dateadd(month, 2, getdate())
select datepart(year, getdate())
select datediff(day, getdate(), dateadd(day, 60, getdate()))
select convert(nvarchar, getdate(), 120) -- 105
*/

-- приведение типов
--select 'sa 5' + cast(4 as nvarchar)

-- null
--select * from tst_orders where customerID is not null
declare @s int
select @s = 0

-- временные таблицы
/*
select * into #temp from tst_customers
select * from #temp

declare @t table (id int)
insert into @t (id) values (1)
*/
-- хранимые процедуры

-- триггеры

-- функции
/*declare @s1 nvarchar(256) = '1,2,4,5,6'

*/