Before you start creating an SQL table, you must define a database model. Design an ER diagram in which to define entities, attributes and relationships.
Basic concepts
Entities - objects or facts about which information must be stored. For example, an employee of a company or projects implemented by an enterprise. Attributes - a component that describes or qualifies an entity. For example, the entity attribute “employee” is the salary, and the entity attribute “project” is the estimated cost. Connections are associations between two elements. It can be bidirectional. There is also a recursive connection, that is, the connection of an entity with itself.
It is also necessary to determine the keys and conditions under which the integrity of the database is maintained. What does it mean? In other words, constraints that help keep the databases in the right and consistent way.
ER-
:
- .
- , .
- .
- .
- SQL.
MySQL. "", "", MySQL MySQL Server, MySQL-Command-Line-Client.
Create Database. :
CREATE DATABASE __.
:
: (delimiter). SQL .
, . USE. : USE __.
SQL
, , , , . SQL. (DDL). MS SQL, . DDL .
SQL Server
DDL, , . SQL Create Table. tt :
CREATE TADLE _, (_1 [DEFAULT ] [_], _2 [DEFAULT ] [_],[_]).
:
- 30 . , , «_», «$» «#». . , , Column, Table, Index . .
- . , . , Char, Varchar, Number, Date, Null . .
- Default . , . ? , , . , , , .
- . . , .
, . , . . , :
- SHOW DATABASES – SQL;
- SHOW TABLES – , USE;
- DESCRIBE _ – .
- ALTER TABLE – .
:
: ALTER TABLE _ { [ADD _ ] | [MODIFY __] | [DROP __()] | [DROP _] | [{ENABLE | DISABLE} CONSTANT _ ] | }.
:
- RENAME – .
- TRUNCATE TABLE - . , , .
, . DROP. , , , .
: DROP TABLE _.
SQL Access , .
CREATE TABLE . . . ? . AS.
:
- CREATE TABLE _ [(_)] AS ;
- _ – , ;
- – , .
, , , .
- , . . . , . SQL , CREATE TABLE. , , GLOBAL TEMPORARY.
ON COMMIT :
- DELETE ROWS – ( ) . .
- PRESERVE ROWS – . , . . (ROLLBACK), .
: CREATE [GLOBAL TEMPORARY] TABLE _, (_1 [DEFAULT ] [_], _2 [DEFAULT ] [_], [_]).