SQL Server/T-SQL Tutorial/Query/Comparison operators
Содержание
- 1 A SELECT statement that retrieves rows with non-zero values
- 2 Bankers located in Iowa
- 3 Bankers with names from A to L
- 4 Billings on or after a specified date
- 5 The syntax of the WHERE clause with comparison operators
- 6 use a comparison operator to compare any two expressions that result in like data types.
- 7 Use the operator <> (instead of !=) to remain consistent with the SQL standard.
A SELECT statement that retrieves rows with non-zero values
4>
5> create table Billings (
6> BankerID INTEGER,
7> BillingNumber INTEGER,
8> BillingDate datetime,
9> BillingTotal INTEGER,
10> TermsID INTEGER,
11> BillingDueDate datetime ,
12> PaymentTotal INTEGER,
13> CreditTotal INTEGER
14>
15> );
16> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3> SELECT *
4> FROM Billings
5> WHERE BillingTotal <> 0
6> GO
BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal
----------- ------------- ----------------------- ------------ ----------- ----------------------- ------------ -----------
1 1 2005-01-22 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321
2 2 2001-02-21 00:00:00.000 165 1 2002-02-22 00:00:00.000 123 321
3 3 2003-05-02 00:00:00.000 165 1 2005-04-12 00:00:00.000 123 321
4 4 1999-03-12 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321
5 5 2000-04-23 00:00:00.000 165 1 2005-04-17 00:00:00.000 123 321
6 6 2001-06-14 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321
7 7 2002-07-15 00:00:00.000 165 1 2005-04-19 00:00:00.000 123 321
8 8 2003-08-16 00:00:00.000 165 1 2005-04-20 00:00:00.000 123 321
9 9 2004-09-17 00:00:00.000 165 1 2005-04-21 00:00:00.000 123 321
0 0 2005-10-18 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321
(10 rows affected)
1>
2> drop table Billings;
3> GO
1>
Bankers located in Iowa
18>
19>
20> create table Bankers(
21> BankerID Integer,
22> BankerName VARCHAR(20),
23> BankerContactLName VARCHAR(20),
24> BankerContactFName VARCHAR(20),
25> BankerCity VARCHAR(20),
26> BankerState VARCHAR(20),
27> BankerZipCode VARCHAR(20),
28> BankerPhone VARCHAR(20)
29> )
30> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2> select * from Bankers WHERE BankerState = "IA"
3> GO
BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
(0 rows affected)
1>
2> drop table Bankers;
3> GO
1>
Bankers with names from A to L
4>
5>
6> create table Bankers(
7> BankerID Integer,
8> BankerName VARCHAR(20),
9> BankerContactLName VARCHAR(20),
10> BankerContactFName VARCHAR(20),
11> BankerCity VARCHAR(20),
12> BankerState VARCHAR(20),
13> BankerZipCode VARCHAR(20),
14> BankerPhone VARCHAR(20)
15> )
16> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3> select * from Bankers WHERE BankerName < "M"
4> GO
BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1 ABC Inc. Joe Smith Vancouver BC 11111 111-111-1111
2 DEF Inc. Red Rice New York DE 22222 222-222-2222
3 HJI Inc. Kit Cat Paris CA 33333 333-333-3333
9 DFG Inc. Sad Lee Wisler PL 99999 999-999-9999
0 GHJ Inc. Bit Lee Ticker MN 00000 000-000-0000
(5 rows affected)
1> drop table Bankers;
2> GO
Billings on or after a specified date
5>
6>
7> create table Billings (
8> BankerID INTEGER,
9> BillingNumber INTEGER,
10> BillingDate datetime,
11> BillingTotal INTEGER,
12> TermsID INTEGER,
13> BillingDueDate datetime ,
14> PaymentTotal INTEGER,
15> CreditTotal INTEGER
16>
17> );
18> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2> select * from Billings WHERE BillingDate >= "5/1/02"
3>
4>
5> --Billings with credits that don"t equal zero
6>
7> select * from Billings WHERE CreditTotal <> 0
8> GO
BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal
----------- ------------- ----------------------- ------------ ----------- ----------------------- ------------ -----------
1 1 2005-01-22 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321
3 3 2003-05-02 00:00:00.000 165 1 2005-04-12 00:00:00.000 123 321
7 7 2002-07-15 00:00:00.000 165 1 2005-04-19 00:00:00.000 123 321
8 8 2003-08-16 00:00:00.000 165 1 2005-04-20 00:00:00.000 123 321
9 9 2004-09-17 00:00:00.000 165 1 2005-04-21 00:00:00.000 123 321
0 0 2005-10-18 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321
(6 rows affected)
BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal
----------- ------------- ----------------------- ------------ ----------- ----------------------- ------------ -----------
1 1 2005-01-22 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321
2 2 2001-02-21 00:00:00.000 165 1 2002-02-22 00:00:00.000 123 321
3 3 2003-05-02 00:00:00.000 165 1 2005-04-12 00:00:00.000 123 321
4 4 1999-03-12 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321
5 5 2000-04-23 00:00:00.000 165 1 2005-04-17 00:00:00.000 123 321
6 6 2001-06-14 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321
7 7 2002-07-15 00:00:00.000 165 1 2005-04-19 00:00:00.000 123 321
8 8 2003-08-16 00:00:00.000 165 1 2005-04-20 00:00:00.000 123 321
9 9 2004-09-17 00:00:00.000 165 1 2005-04-21 00:00:00.000 123 321
0 0 2005-10-18 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321
(10 rows affected)
1>
2> drop table Billings;
3> GO
1>
2>
The syntax of the WHERE clause with comparison operators
WHERE expression_1 operator expression_2
The comparison operators
= Equal
> Greater than
< Less than
<= Less than or equal to
>= Greater than or equal to
<> Not equal
use a comparison operator to compare any two expressions that result in like data types.
5>
6>
7> create table Bankers(
8> BankerID Integer,
9> BankerName VARCHAR(20),
10> BankerContactLName VARCHAR(20),
11> BankerContactFName VARCHAR(20),
12> BankerCity VARCHAR(20),
13> BankerState VARCHAR(20),
14> BankerZipCode VARCHAR(20),
15> BankerPhone VARCHAR(20)
16> )
17> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2> select * from Bankers WHERE BankerState = "IA"
3> GO
BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
(0 rows affected)
1>
2> drop table Bankers;
3> GO
1>
Use the operator <> (instead of !=) to remain consistent with the SQL standard.
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> SELECT *
4> FROM employee
5> WHERE id <> 2
6>
7>
8>
9> drop table employee;
10> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(8 rows affected)