, . , . SQL (Structured Query Language ).
SQL , 70- IBM System R. SQL . 1986 . ANSI, 1987 . (ISO). ; 2008 . SQL ISO/IEC 9075 Database Language SQL.
, SQL, . SQL, . : , SQL/Foundation, , Core, . .
, SQL . , . SQL , , , .
, SQL , :
(Data Definition Language DDL)
o CREATE
o ALTER
o DROP
(Data Manipulation Language DML)
o SELECT ,
o INSERT
o UPDATE
o DELETE
(Data Control Language DCL)
o GRANT ()
o REVOKE
o DENY ,
(Transaction Control Language TCL)
o COMMIT .
o ROLLBACK , .
|
|
|
o SAVEPOINT .
, , .
USE master
IF DB_ID (N' ') IS NOT NULL
DROP DATABASE [ ]
GO
CREATE DATABASE [ ]
GO
USE [ ]
CREATE TABLE S
(
Sno INT IDENTITY(1,1) NOT NULL,
Sname VARCHAR(20) NOT NULL,
Status INT,
City VARCHAR(15)
)
CREATE TABLE P
(
Pno INT IDENTITY(1,1) NOT NULL,
Pname VARCHAR(20) NOT NULL,
Color CHAR(10),
Weight REAL,
City VARCHAR(15)
)
CREATE TABLE SP
(
Sno INT NOT NULL,
Pno INT NOT NULL,
Qty INT
)
ALTER TABLE S
ADD
CONSTRAINT PK_S PRIMARY KEY (Sno),
CONSTRAINT UK_S UNIQUE (Sname)
ALTER TABLE P
ADD
CONSTRAINT PK_P PRIMARY KEY (Pno)
ALTER TABLE SP
ADD
CONSTRAINT PK_SP PRIMARY KEY (Sno, Pno),
CONSTRAINT FK_SP_S FOREIGN KEY (Sno) REFERENCES S (Sno),
CONSTRAINT FK_SP_P FOREIGN KEY (Pno) REFERENCES P (Pno)
.
1) SQL Microsoft ( Microsoft SQL Server), Transact-SQL (T-SQL).
2) . GO.
3) USE .
4) NULL, . IF.
5) IDENTITY .
6) CREATE TABLE ALTER TABLE, , NULL, (PRIMARY ), (UNIQUE), (FOREIGN ).
7) , (DEFUULT), (CHECK) (BULK INSERT).
SELECT, . SELECT , :
[ WITH __ ]
SELECT [ DISTINCT | ALL ] [ TOP [ PERCENT ] ] { * | _ } [ INTO _ ]
[ FROM __ ]
[ WHERE _ ]
[ GROUP BY group_by_ ]
[ HAVING _ ]
[ ORDER BY order_by_ [ ASC | DESC ] ]
, SELECT, :
WITH , .
SELECT , .
DISTINCT , .
ALL , . ALL .
|
|
|
TOP , .
* , FROM .
INTO .
FROM , SELECT.
WHERE , .
GROUP BY , .
HAVING .
ORDER BY , SELECT.
SELECT SELECT, FROM WHERE. SELECT, :
SELECT { * | _ }
FROM __
WHERE _
, , . , .
1) , 2.
SELECT DISTINCT S.Sname
FROM S
WHERE EXISTS (SELECT *
FROM SP
WHERE SP.Sno = S.Sno AND SP.Pno = 2)
2) , , , .
SELECT DISTINCT S.Sname
FROM S
WHERE S.Sno IN (SELECT SP.Sno
FROM SP
WHERE SP.Pno IN (SELECT P.Pno
FROM P
WHERE P.Color = ''))
3) , .
SELECT DISTINCT S.Sname
FROM S
WHERE NOT EXISTS (SELECT *
FROM P
WHERE NOT EXISTS (SELECT *
FROM SP
WHERE SP.Sno = S.Sno AND SP.Pno = P.Pno))
4) , , , , 2.
5) ,
SELECT A.Sno AS SA, B.Sno AS SB
FROM S AS A, S AS B
WHERE A.City = B.City AND A.Sno < B.Sno
6) , 2.
SELECT DISTINCT S.Sname
FROM S
WHERE NOT EXISTS (SELECT *
FROM SP
WHERE SP.Sno = S.Sno AND SP.Pno = 2)
