NULL , , :
SELECT BonusPercent FROM Employees ORDER BY ISNULL (BonusPercent,100)
ORACLE 2 NULLS FIRST NULLS LAST ( ). :
SELECT BonusPercent FROM Employees ORDER BY BonusPercent DESC NULLS LAST
.
TOP
MSDN. TOP , . TOP ORDER BY, N . N .
ORDER BY , N- .
ORDER BY , , , , , 10 , 2:
SELECT TOP 2
*
FROM Employees
PERCENT, :
SELECT TOP 25 PERCENT
*
FROM Employees
.
TOP WITH TIES, , .. , , TOP N, N. 1500:
INSERT Employees(ID, Name,Email,PositionID,DepartmentID,ManagerID,Salary)
VALUES (1004, N ' ..','[email protected]',3,3,1003,1500)
2000:
INSERT Employees(ID, Name,Email,PositionID,DepartmentID,ManagerID,Salary)
VALUES (1005, N ' ..','[email protected]',NULL,NULL,1000,2000)
WITH TIES , 3- , ( , ):
SELECT TOP 3 WITH TIES
ID, Name,Salary
FROM Employees
ORDER BY Salary
TOP 3, 4 , .. Salary TOP 3 (1500 2000) 4- . :
.
TOP , MySQL LIMIT, .
|
|
ORACLE 12c, TOP LIMIT ORACLE OFFSET FETCH. 12c ROWNUM.
DISTINCT TOP? , . , , .. . SELECT , DISTINCT, TOP, .. -, , TOP. - , :
SELECT DISTINCT TOP 2
Salary
FROM Employees
ORDER BY Salary
Salary |
.. 2 . - (NULL), .. . NULL ORDER BY, , Salary NULL, WHERE.
WHERE
. , һ ( ID=3):
SELECT ID,LastName,FirstName,Salary
FROM Employees
WHERE DepartmentID=3 --
ORDER BY LastName,FirstName
ID | LastName | FirstName | Salary |
NULL | NULL | ||
WHERE ORDER BY.
Employees :
1. WHERE , Employees
2. DISTINCT ,
3. ORDER BY ,
4. TOP ,
:
SELECT DISTINCT TOP 1
Salary
FROM Employees
WHERE DepartmentID=3
ORDER BY Salary
:
, NULL , IS NULL IS NOT NULL. , NULL = ( ) , .. NULL.
, , (.. DepartmentID IS NULL):
SELECT ID,Name
FROM Employees
WHERE DepartmentID IS NULL
ID | Name |
.. |
BonusPercent (.. BonusPercent IS NOT NULL):
SELECT ID, Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE BonusPercent IS NOT NULL
, , , BonusPercent (0), , .
, , , (BonusPercent<=0 BonusPercent IS NULL), . , , , OR NOT:
|
|
SELECT ID, Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE NOT (BonusPercent<=0 OR BonusPercent IS NULL)
.. . (BonusPercent<=0 OR BonusPercent IS NULL) , NOT , .. .
, (BonusPercent>0 BonusPercent IS NOT NULL):
SELECT ID, Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE BonusPercent>0 AND BonusPercent IS NOT NULL
WHERE . , , ISNULL:
SELECT ID, Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE ISNULL (BonusPercent,0)>0
, , .
SQL 3 AND, OR NOT:
AND | . (1 AND 2). True, , |
OR | . (1 OR 2). True, , |
NOT | /_. (NOT _) True, _ = False False, _ = True |
, NULL:
, :
= | |
< | |
> | |
<= | |
>= | |
<>!= |
2 / NULL:
IS NULL | NULL |
IS NOT NULL | NULL |
: 1) ; 2) NOT; 3) AND; 4) OR.
:
((1 AND 2) OR NOT(3 AND 4 AND 5)) OR ()
, .
. , , (AND, OR NOT) , .
SELECT , , , BETWEEN, IN LIKE.
BETWEEN
:
_ [NOT] BETWEEN _ AND _
.
:
SELECT ID, Name,Salary
FROM Employees
WHERE Salary BETWEEN 2000 AND 3000 -- 2000-3000
ID | Name | Salary |
.. | ||
.. | ||
.. |
, BETWEEN :
SELECT ID, Name,Salary
FROM Employees
WHERE Salary>=2000 AND Salary<=3000 -- 2000-3000
|
|
BETWEEN NOT, :
SELECT ID, Name,Salary
FROM Employees
WHERE Salary NOT BETWEEN 2000 AND 3000 -- NOT(Salary>=2000 AND Salary<=3000)
, BETWEEN, IN, LIKE AND OR:
SELECT ID, Name,Salary
FROM Employees
WHERE Salary BETWEEN 2000 AND 3000 -- 2000-3000
AND DepartmentID=3 -- 3
IN
:
_ [NOT] IN (1, 2, )
, :
SELECT ID, Name,Salary
FROM Employees
WHERE PositionID IN (3,4) -- 3 4
ID | Name | Salary |
.. | ||
.. | ||
.. |
.. :
SELECT ID, Name,Salary
FROM Employees
WHERE PositionID=3 OR PositionID=4 -- 3 4
NOT ( , 3 4):
SELECT ID, Name,Salary
FROM Employees
WHERE PositionID NOT IN (3,4) -- NOT(PositionID=3 OR PositionID=4)
NOT IN AND:
SELECT ID, Name,Salary
FROM Employees
WHERE PositionID<>3 AND PositionID<>4 -- PositionID NOT IN(3,4)
, NULL IN , .. NULL=NULL NULL, True:
SELECT ID, Name,DepartmentID
FROM Employees
WHERE DepartmentID IN (1,2,NULL) -- NULL
:
SELECT ID, Name,DepartmentID
FROM Employees
WHERE DepartmentID IN (1,2) -- 1 2
OR DepartmentID IS NULL -- NULL
- :
SELECT ID, Name,DepartmentID
FROM Employees
WHERE ISNULL (DepartmentID,-1) IN (1,2,-1) -- , ID=-1
, , . , , .
, NULL, NOT IN. , , , 1 , .. NULL. :
SELECT ID, Name,DepartmentID
FROM Employees
WHERE DepartmentID NOT IN (1,NULL)
, , :
ID | Name | DepartmentID |
.. | ||
.. | ||
.. | ||
.. |
NULL .
. AND:
SELECT ID, Name,DepartmentID
FROM Employees
WHERE DepartmentID<>1
AND DepartmentID<>NULL -- - NULL - NULL
(DepartmentID<>NULL) , .. NULL. AND, (TRUE AND NULL) NULL. .. (DepartmentID<>1) - (DepartmentID<>1 AND DepartmentID<>NULL), .
|
|
:
SELECT ID, Name,DepartmentID
FROM Employees
WHERE DepartmentID NOT IN (1) -- DepartmentID<>1
AND DepartmentID IS NOT NULL -- NOT NULL
IN , , .
LIKE
, SQL. , .
:
_ [NOT] LIKE _ [ESCAPE _]
_ :
1. _ ,
2. % , ,
% ( , ):
SELECT ID,Name
FROM Employees
WHERE Name LIKE '%' -- ""
SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '%' -- ""
SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '%%' -- ""
_:
SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '_' -- ""
SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '____' -- ""
ESCAPE , _ %. , .
ESCAPE :
UPDATE Employees
SET
FirstName='_, %'
WHERE ID =1005
, :
SELECT *
FROM Employees
WHERE FirstName LIKE '%!%%' ESCAPE '!' -- "%"
SELECT *
FROM Employees
WHERE FirstName LIKE '%!_%' ESCAPE '!' -- "_"
, , LIKE =:
SELECT *
FROM Employees
WHERE FirstName=''
.
MS SQL LIKE , , , .
ORACLE REGEXP_LIKE.
Unicode , N, .. N''. Unicode ( nvarchar), . :
SELECT ID,Name
FROM Employees
WHERE Name LIKE N '%'
SELECT ID,LastName
FROM Employees
WHERE LastName= N ''
, varchar (ASCII) '', nvarchar (Unicode) N''. , . (INSERT) (UPDATE).
, (collation), , - ( ''=''), - ( ''<>'').
- , , , , :
SELECT ID,Name
|
|
FROM Employees
WHERE UPPER (Name) LIKE UPPER (N '%') -- LOWER(Name) LIKE LOWER(N'%')
SELECT ID,LastName
FROM Employees
WHERE UPPER (LastName)= UPPER (N '') -- LOWER(LastName)=LOWER(N'')
, , ''.
MS SQL 'YYYYMMDD' (, , ). MS SQL :
SELECT ID, Name,Birthday
FROM Employees
WHERE Birthday BETWEEN '19800101' AND '19891231' -- 80-
ORDER BY Birthday
, DATEFROMPARTS:
SELECT ID, Name,Birthday
FROM Employees
WHERE Birthday BETWEEN DATEFROMPARTS (1980,1,1) AND DATEFROMPARTS (1989,12,31)
ORDER BY Birthday
DATETIMEFROMPARTS, ( datetime).
CONVERT, date datetime:
SELECT
CONVERT (date,'12.03.2015',104),
CONVERT (datetime,'2014-11-30 17:20:15',120)
104 120, . MSDN MS SQL CONVERT.
MS SQL , ms sql .
. SQL .
-. , , , - , .. SQL, .
CAST, CONVERT , (). , . CONVERT date datetime.
CAST, CONVERT MSDN CAST CONVERT (Transact-SQL): msdn.microsoft.com/ru-ru/library/ms187928.aspx
Transact-SQL DECLARE SET.
, ( , ), , .. ( ):
DECLARE @min_int int SET @min_int=-2147483648
DECLARE @max_int int SET @max_int=2147483647
SELECT
-- (-2147483648)
@min_int, CAST (@min_int AS float), CONVERT (float,@min_int),
-- 2147483647
@max_int, CAST (@max_int AS float), CONVERT (float,@max_int),
-- numeric(16,6)
@min_int/1., -- (-2147483648.000000)
@max_int/1. -- 2147483647.000000
, (1.), .. , . , , numeric, , MS SQL (1., 1.0, 1.00 .):
DECLARE @int int SET @int=123
SELECT
@int*1., -- numeric(12, 0) - 0
@int*1.0, -- numeric(13, 1) - 1
@int*1.00, -- numeric(14, 2) - 2
--
CAST (@int AS numeric(20, 0)), -- 123
CAST (@int AS numeric(20, 1)), -- 123.0
CAST (@int AS numeric(20, 2)) -- 123.00
, .. , , , (varchar). money float varchar:
-- money varchar
DECLARE @money money
SET @money = 1025.123456789 -- 1025.1235, .. money 4
SELECT
@money, -- 1025.1235
-- CAST CONVERT (.. 0)
CAST (@money as varchar(20)), -- 1025.12
CONVERT (varchar(20), @money), -- 1025.12
CONVERT (varchar(20), @money, 0), -- 1025.12 ( 0 - 2 ( ))
CONVERT (varchar(20), @money, 1), -- 1,025.12 ( 1 - 2 )
CONVERT (varchar(20), @money, 2) -- 1025.1235 ( 2 - 4 )
-- float varchar
DECLARE @float1 float SET @float1 = 1025.123456789
DECLARE @float2 float SET @float2 = 1231025.123456789
SELECT
@float1, -- 1025.123456789
@float2, -- 1231025.12345679
-- CAST CONVERT (.. 0)
-- 0 - 6 .
-- varchar
CAST (@float1 as varchar(20)), -- 1025.12
CONVERT (varchar(20), @float1), -- 1025.12
CONVERT (varchar(20), @float1, 0), -- 1025.12
CAST (@float2 as varchar(20)), -- 1.23103e+006
CONVERT (varchar(20), @float2), -- 1.23103e+006
CONVERT (varchar(20), @float2, 0), -- 1.23103e+006
-- 1 - 8 . .
-- float
CONVERT (varchar(20), @float1, 1), -- 1.0251235e+003
CONVERT (varchar(20), @float2, 1), -- 1.2310251e+006
-- 2 - 16 . .
--
CONVERT (varchar(30), @float1, 2), -- 1.025123456789000e+003 - OK
CONVERT (varchar(30), @float2, 2) -- 1.231025123456789e+006 - OK
, float, real , ( , , , ).
, 4-, , decimal/numeric. 4- , money numeric(20,4).
-- decimal numeric
DECLARE @money money SET @money = 1025.123456789 -- 1025.1235
DECLARE @float1 float SET @float1 = 1025.123456789
DECLARE @float2 float SET @float2 = 1231025.123456789
DECLARE @numeric numeric(28,9) SET @numeric = 1025.123456789
SELECT
CAST (@numeric as varchar(20)), -- 1025.12345679
CONVERT (varchar(20), @numeric), -- 1025.12345679
CAST (@money as numeric(28,9)), -- 1025.123500000
CAST (@float1 as numeric(28,9)), -- 1025.123456789
CAST (@float2 as numeric(28,9)) -- 1231025.123456789
.
MS SQL 2008, :
DECLARE @money money
SET @money = 1025.123456789
:
DECLARE @money money = 1025.123456789
, , . , SQL.