(1000, N '', N '', N ' ..'),
(1001, N '', N '', N ' ..'),
(1002, N '', N '', N ' ..'),
(1003, N ' ', N '', N ' ..')
, :
CREATE TABLE Employees(
ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, --
Name nvarchar (30) NOT NULL,
Birthday date,
Email nvarchar (30),
Position nvarchar (30),
Department nvarchar (30)
)
, ( PK__Employee__3214EC278DA42077):
CREATE TABLE Employees(
ID int NOT NULL,
Name nvarchar (30) NOT NULL,
Birthday date,
Email nvarchar (30),
Position nvarchar (30),
Department nvarchar (30),
PRIMARY KEY (ID)
)
:
CREATE TABLE Employees(
ID int NOT NULL PRIMARY KEY,
Name nvarchar (30) NOT NULL,
Birthday date,
Email nvarchar (30),
Position nvarchar (30),
Department nvarchar (30)
)
, .. , , :
ALTER TABLE Employees DROP CONSTRAINT PK_Employees
, , ( # ##), .
:
CREATE TABLE _ ( , ) ;
DROP TABLE _ ;
ALTER TABLE _ ALTER COLUMN _ ( NULL NOT NULL);
ALTER TABLE _ ADD CONSTRAINT _ PRIMARY KEY (1, 2,) ;
ALTER TABLE _ DROP CONSTRAINT _ .
MSDN. MS SQL Server : (#) (##). SQL Server, . SQL Server. , , , SQL Server.
tempdb, .. , , DROP TABLE. (#) .
|
|
CREATE TABLE:
CREATE TABLE #Temp(
ID int,
Name nvarchar (30)
)
MS SQL , DROP TABLE:
DROP TABLE #Temp
( ) SELECT INTO:
SELECT ID,Name
INTO #Temp
FROM Employees
. , ORACLE Firebird CREATE GLOBAL TEMPORARY TABLE , .
()
Employees , Position Department , , һ, , , -, IT. , , .. , 3 ? , , - , , .
, .. , .
, - , , , . , .
, , , . , .
2 , Positions, Departments:
CREATE TABLE Positions(
ID int IDENTITY (1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY,
Name nvarchar (30) NOT NULL
)
CREATE TABLE Departments(
ID int IDENTITY (1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY,
Name nvarchar (30) NOT NULL
)
, IDENTITY, , ID , 1, 1, .. 1, 2, 3, .. . IDENTITY , , .
|
|
. MySQL, , AUTO_INCREMENT. ORACLE Firebird (SEQUENCE). ORACLE GENERATED AS IDENTITY.
, Position Department Employees:
-- Name Positions, Position Employees
INSERT Positions(Name)
SELECT DISTINCT Position
FROM Employees
WHERE Position IS NOT NULL --
Departments:
INSERT Departments(Name)
SELECT DISTINCT Department
FROM Employees
WHERE Department IS NOT NULL
Positions Departments, ID:
SELECT * FROM Positions
ID | Name |
SELECT * FROM Departments
ID | Name |
. . Employees :
-- ID
ALTER TABLE Employees ADD PositionID int
-- ID
ALTER TABLE Employees ADD DepartmentID int
, , int.
, :
ALTER TABLE Employees ADD PositionID int, DepartmentID int
( FOREIGN KEY) , , , ID .
ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID
FOREIGN KEY (PositionID) REFERENCES Positions(ID)
:
ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID
FOREIGN KEY (DepartmentID) REFERENCES Departments(ID)
ID . , , . .. , , .
, , FK_, , -.
(ID) , , , , , , .
:
ALTER TABLE ADD CONSTRAINT _
FOREIGN KEY (1,2,) REFERENCES _(1,2,)
_ (1, 2,).
, PositionID DepartmentID ID . DML UPDATE:
|
|
UPDATE e
SET
PositionID=(SELECT ID FROM Positions WHERE Name = e. Position),
DepartmentID=(SELECT ID FROM Departments WHERE Name = e. Department)
FROM Employees e
, , :
SELECT * FROM Employees
ID | Name | Birthday | Position | Department | PositionID | DepartmentID | |
.. | NULL | NULL | |||||
.. | NULL | NULL | |||||
.. | NULL | NULL | |||||
.. | NULL | NULL |
, PositionID DepartmentID Position Department Employees , :
ALTER TABLE Employees DROP COLUMN Position,Department
:
SELECT * FROM Employees
ID | Name | Birthday | PositionID | DepartmentID | |
.. | NULL | NULL | |||
.. | NULL | NULL | |||
.. | NULL | NULL | |||
.. | NULL | NULL |
.. . , , -:
SELECT e. ID, e. Name, p. Name PositionName, d. Name DepartmentName
FROM Employees e
LEFT JOIN Departments d ON d. ID = e. DepartmentID
LEFT JOIN Positions p ON p. ID = e. PositionID
ID | Name | PositionName | DepartmentName |
.. | |||
.. | |||
.. | |||
.. |
, . , .
, , .. . ManagerID, , . :
ALTER TABLE Employees ADD ManagerID int
NULL, , , , .
FOREIGN KEY Employees:
ALTER TABLE Employees ADD CONSTRAINT FK_Employees_ManagerID
FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
, , :
( Employees Positions Depertments, ):
, ON DELETE CASCADE ON UPDATE CASCADE, , , -. , ID , , , , , .
ON DELETE CASCADE FK_Employees_DepartmentID:
|
|
DROP TABLE Employees
CREATE TABLE Employees(
ID int NOT NULL,
Name nvarchar (30),
Birthday date,
Email nvarchar (30),
PositionID int,
DepartmentID int,
ManagerID int,
CONSTRAINT PK_Employees PRIMARY KEY (ID),
CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY (DepartmentID) REFERENCES Departments(ID)
ON DELETE CASCADE,
CONSTRAINT FK_Employees_PositionID FOREIGN KEY (PositionID) REFERENCES Positions(ID),
CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
)