:
1) : =, <, >,! =, < >, >=, <=,!>,!<.
2) (between not between).
3) (in, not in).
4) (like, not like).
5) (is null, is not null).
6) : and, or, not.
7) .
8) .
:
where <1> < > <2>
<> , , , , . :
select * from titleauthor where royaltyper < 50
between , (). between :
<1> [not] between <2> and <3>
, <1> , <2> , <3>. not, , <1> <2> <3>. :
select title_id, total_sales from titles where total_sales between 4095 and 12000
in . in :
<> [not] in (< >)
, <> , < >. not, <>. :
select au_name, state from authors where state in (CA, IN, MD)
, .
like , . like :
< > [not] like < >
, < > . , < > :
1) % - , 0 ;
2) _ () - ;
3) [<>] - , ;
4) [^<>] - , .
<> :
) : r1-r2, : [a-f]
) : r1r2, : [abcdef]
not like , like.
|
|
|
:
1) like M% - ;
2) like %er - er;
3) like %en% - en ;
4) like ___ryl - 6 ryl;
5) like [CK]ars[eo]n - 6 , , K, - e, o;
6) like [M-Z]ing - 4 , ing, , M Z;
7) like M[^C]% - M, () , C.
null , , null . null :
< > is [not] null
where and, or not. and() , , :
select * from quotations where qonorder > 0 and suppno = 54
or() , , , :
select * from authors where au_fname = Anne or au_fname = Ann
not , :
select * from authors where not state = CA
, :
select * from quotations where suppno = 61 and (purtno = 221 or purtno = 222)
group by . () 16. . group by . , count(*) . - null , , .
:
select nazvanie_goroda from vuz_gorod where cod_stran = 01 group by nazvanie_goroda
having , , . . , < >. Having - group by, where select.
:
select nomer from poss group by nomer having count(*) > 1
order by, , SELECT. asc , desc - . , .
:
select cod_vuza,nazvanie_vuza from vuz_gorod where cod_stran = 01 order by 1
compute , . compute , compute . compute : sum, avg, min, max, count (sum avg ). , , .
|
|
|
avg . distinct. null .
sum . , distinct null .
max . null .
min , null .
count :
n count(distinct < >) , , ;
n count(*) , , .
compute:
1) distinct;
2) compute ;
3) select, compute, into;
4) compute by order by, compute by order by, , - .
: order by a, b, c, :
compute <. > (< >) by a, b, c
compute <. > (< >) by a, b
compute <. > (< >) by a
:
compute <. > (< >) by b,c
compute <. > (< >) by a, c
compute <. > (< >) by c
5) compute by.
:
1) compute by , :
select type, pub_id, price from titles
order by type, pub_id, price
compute sum(price) by type, pub_id
2) , compute :
select type, pub_id, price from titles
order by type, pub_id, price
compute sum(price) by type, pub_id
compute sum(price) by type
, compute , .
3) , , compute by:
select type, price,advance from titles
where price > $20
compute sum(price), sum(advance)
union
union ,
order by compute. :
<1>
[union [all] < N>]
[< order by>]
[< compute>]
<1>: select < >
[< into>]
[< from]
[< where>]
[< group by>]
[< having>]
< N>: select < >
[< from]
|
|
|
[< where>]
[< group by>]
[< having>]
:
select * from t1 union select * from t2
union, , . all, .
union:
1) union ( );
2) ;
3) into ;
4) order by compute ;
5) group by having ;
6) union insert, :
insert into tour
select city, state from stores
union
select city, state from authors
7) union create view;
8) browse select union.
, where . :
- , ;
- , : char, nchar, varchar, nvarchar, binary, varbinary;
- , text image;
- (, .);
- , datetime smalldatetime;
- .
. :
select < > (<[]>)
1
| () | ||
| 1) col_name | (<. >, <. >) | |
| 2) col_length | (< >, < >) | |
| 3) data_pgs | (<. >, {doampg | ioampg}) | , ( , ) |
| 4) datalength | (<>) | |
| 5) db_id | (< >) | |
| 6) db_name | (< . >) | |
| 7) host_id | () | |
| 8) host_name | () | |
| 9) index_col | (< >, <. >, <>) | |
| 10) isnull | (<>, <>) | null |
| 11) object_id | (< >) | |
| 12) object_name | (<. >) | |
| 13) reserved_pgs | (<. >, {doampg | ioampg}) | , ( , |
| 14) rowcnt | (doampg) | |
| 15) sused_id | ([< >]) | |
| 16) sused_name | ([<. >]) | |
| 17) tsequal | (<. >, <. 2>) | ; <. > - ; <. 2> - |
| 18) used_pgs | (<. >, doampg, ioampg) | , ( , ) |
| 19) user_id | ([< .>]) | |
| 20) user_name | ([<.>]) | |
| 21) valid_name | (<>) | 0, <> 30 , 0 - |
|
|
|
:
1) select x = col_length (titles, title)
2) select length = datalength (pub_name), pub_name from publishers
3) select name from sysusers where name = user_name(1)
.
:
select < > (<>)
, :
select (<> + <> [ + <>])
2:
1) char_expr - char, varchar, nchar nvarchar;
2) expression - char_expr binary varbinary;
3) pattern - char_expr, ;
4) approx_numeric - float, real double precition;
5) integer_expr - tinyint, smallint int;
6) start - integer_expr;
7) length - integer_expr.
2
| () | ||
| 1) ascii | (char_expr) | ASCII |
| 2) char | (integer_expr) | , 1 1 |
| 3) charindex | (expression1, expression2) | 1 2 ; , 0 |
| 4) char_length | (char_expr) | |
| 5) difference | (char_expr1, char_expr2) | soundex (. ) |
| 6) lower | (char_expr) | |
| 7) ltrim | (char_expr) | |
| 8) patindex | (%pattern%, char_expr [using {bytes | chars | characters}]) | pattern char_expr 0, |
| 9) replicate | (char_expr, integer_expr) | char_expr, 1 <2> (max 255 ) |
| 10) reverce | (char_expr) | , : abcd - dcba |
| () | ||
| 11) right | (char_expr, integer_expr) | , <2> , |
| 12) rtrim | (char_expr) | |
| 13) soundex | (char_expr) | , |
| 14) space | (integer_expr) | |
| 15) str | (approx_numeric [, length [, decimal]]) | ; length , decimal - ; length decimal , length=10, decimal=0 |
| 16) stuff | (char_expr1, start, length, char_expr2) | length expr1, start, expr2 expr1, start; expr2=null, |
| 17) substring | (expression, start, length) | expression length, start |
| 18) upper | (char_expr) | |
| 19) + | expression + expression |
:
1) select au_lname, substring (au_fname, 1, 1) from authors
2) select charindex(wonderful, notes), patindex(wonderful, notes) from titles
where title_id = TC3218
3) select stuff(abc, 2, 3, xyz)
4) select (abc + def)
.
5) select substring(pub_id + title_id, 1, 6) from titles where price > $20
text image.
3
| () | ||
| 1) patindex | (%pattern%,char_expr [using {bytes | chars | characters}]) | pattern 0 - pattern |
| 2) textptr | (< >) | (16 ) |
| 3) textvalid | (< >..< >, < >) | 1, 0 - |
| 4) set textsize | {n | 0} | max text/image select-; 0, max 32 K |
:
|
|
|
declare @val varbinary(16)
select @val = textptr(blurb) from texttest
:
< > (<>)
:
1) approx_numeric - float, real double precition;
2) integer - tinyint, smallint int;
3) numeric - approx_numeric, numeric, dec, decimal, integer money;
4) power - numeric, approx_numeric money.
4
| () | ||
| 1) abs | (numeric) | |
| 2) acos | (approx_numeric) | ( ) |
| 3) asin | (approx_numeric) | ( ) |
| 4) atan | (approx_numeric) | ( ) |
| 5) atn2 | (approx_numeric1, approx_numeric2) | 1 2 |
| 6) ceiling | (numeric) | , |
| 7) cos | (approx_numeric) | ( ) |
| 8) cot | (approx_numeric) | ( ) |
| 9) degrees | (numeric) | |
| 10) exp | (approx_numeric) | e |
| 11) floor | (numeric) | , |
| 12) log | (approx_numeric) | |
| 13) log10 | (approx_numeric) | |
| 14) pi | () | |
| 15) power | (numeric, power) | numeric power |
| 16) radians | (numeric) | |
| 17) rand | ([integer]) | random [0;1] integer |
| 18) round | (numeric, integer) | numeric integer |
| 19) sign | (numeric) | |
| 20) sin | (approx_numeric) | ( ) |
| 21) sqrt | (approx_numeric) | |
| 22) tan | (approx_numeric) | ( ) |
:
1) select ceiling(123.45) => 124.
2) select round(123.4545,2) => 123.4500
()
5
| 1) | 1) yy | 1) 1753¸9999 |
| 2) | 2) qq | 2) 1¸4 |
| 3) | 3) mm | 3) 1¸12 |
| 4) | 4) dy | 4) 1¸366 |
| 5) | 5) dd | 5) 1¸31 |
| 6) | 6) wk | 6) 1¸54 |
| 7) | 7) dw | 7) 1¸7 (1-Sunday) |
| 8) | 8) hh | 8) 0¸23 |
| 9) | 9) mi | 9) 0¸59 |
| 10) | 10) ss | 10) 0¸59 |
| 11) | 11) ms | 11) 0¸999 |
6
| () | ||
| 1) getdate | () | |
| 2) datename | (datepart, date) | ASCII |
| 3) datepart | (datepart, date) | |
| 4) datediff | (datepart, date, date) | 2- |
| 5) dateadd | (datepart, number, date) | , |
:
1) select getdate();
2) select datediff(month, pubdate, Nov 30 1985) from titles;
3) select dateadd(day, 3, pubdate) from titles
