DDL Syntax

DDL Syntax

1. Data Definition Language

   • A Data Definition Language has a pre-defined syntax for describing data. For example, to build a new table using SQL syntax, the CREATE command is used, followed by parameters for the table name and column definitions. The DDL can also define the name of each column and the associated data type. Once a table is created, it can be modified using the ALTER command.

2. Syntax to create database

   • To begin, create a database to ourselves, and call it "test". To create a database used a sentence CREATE DATABASE : mysql> CREATE DATABASE test; Query OK, 1 row affected (0.03 sec) mysql>

3. Create a table

   • In its simplest form, the statement CREATE TABLE create a table with columns that indicate. We will create, as an example, a table that will allow us to store names and their birth dates. We shall indicate the name of the table and the names and types of columns: mysql> USE test Database changed mysql> CREATE TABLE people (name VARCHAR (40), date DATE); Query OK, 0 rows affected (0.53 sec) mysql>

4. Create Table.

   • In its simplest form, the statement CREATE TABLE create a table with columns that indicate. We will create, as an example, a table that will allow us to store names and their birth dates. We shall indicate the name of the table and the names and types of columns: mysql> USE test Database changed mysql> CREATE TABLE people (name VARCHAR (40), date DATE); Query OK, 0 rows affected (0.53 sec) mysql>

5. ALTER TABLE

   • ALTER TABLE "table_name" [alter specification]; [Alter specification] it depends on the type of change we wish to do. For the uses mentioned above, the instructions [they modify specification] are: Add a column: ADD "column 1" "data types for column 1" Remove a column: DROP "column 1" Rename a column: CHANGE "old column name" "new column name" "data types for the new column". Change the data type for a column: MODIFY "column 1" "new data type"

6. DROP TABLE

   • The syntax for DROP TABLE is DROP TABLE "table_name"; So if we want to delete a table called customer that we created in the section CREATE TABLE , simply entered DROP TABLE Customer;

7. key constraints

   • Primary Key
     • A table usually has a column or combination of columns whose values ​​uniquely identify each row of the table. A table has a column Typically combination of columns or That Contain That uniquely identify identity values ​​each row in the table. These columns are called primary keys of the table and require the entity integrity of the table. Because the primary key constraints guarantee unique data, often defined in an identity column. the columns ProductID and VendorID of the table Purchasing.ProductVendor form a composite primary key constraint for this table. Thus, it is guaranteed that all rows of the table ProductVendor have a combination of ProductID and VendorID . This prevents insertion of duplicate rows.
   • Foreign Key
     • In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table. For example, a table called Employee has a primary key called employee_id. Another table called Employee Details has a foreign key which references employee_id in order to uniquely identify the relationship between both the tables. ALTER TABLE <table identifier> ADD [ CONSTRAINT <constraint identifier> ] FOREIGN KEY ( <column expression> {, <column expression>}... ) REFERENCES <table identifier> [ ( <column expression> {, <column expression>}... ) ] [ ON UPDATE <referential action> ] [ ON DELETE <referential action> ]

8. Integrity Constraints

   • SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement). CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... ); NOT NULL - Indicates that a column cannot store NULL value