ТВОЙ BZFAR
Главная » Статьи » Database » Relational databases
SQL queries. DDL (Data Definition Language)


11.4.2.2 compare the data definition language (DDL), and the data manipulation language (DML) 
11.4.2.3 describe the basic SQL queries for working with tables in a database: CREATE, ALTER and DROP

SQL queries. Data Definition Language.

What can we do with SQL? 

  • Create new databases
  • Create new tables
  • Change the structure of database
  • Get data from databases
  • Insert records to database tables 
  • Update records of database tables 
  • Delete records in database tables 

SQL (Structured Query Language) is a standard language for accessing and manipulating databases.

Data Definition Language (DDL) is a family of computer languages ​​used in computer programs to describe the structure of databases. 

The functions of DDL operators are defined by the first word in a sentence.

DDL is used to create the database strucrure; that is, to define which attributes belong in which tables. It also allows you to cretae users and grant access rights to users.

The SQL CREATE DATABASE Statement

Syntax Example
CREATE DATABASE databasename; CREATE DATABASE projectDB;

SQL DROP DATABASE Statement

Syntax Example
DROP DATABASE databasename; DROP DATABASE projectDB;

SQL CREATE TABLE Statement

Syntax Example
CREATE TABLE table_name 
(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
);		 CREATE TABLE users 
(
    userID int AUTO_INCREMENT,
    surname varchar(25),
    firstname varchar(25),
    address varchar(50),
    iin varchar(12) 
);

The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only one primary key, which may consist of single or multiple fields.

CREATE TABLE users
(
    userID int NOT NULL  AUTO_INCREMENT,
    surname varchar(25),
    firstname varchar(25),
    address varchar(50),
    iin varchar(12),
    PRIMARY KEY (userID)
);

Data dictionary

How to create Foreign Key on CREATE TABLE?

Syntax Example

CREATE TABLE table_name(
   ...,
   FOREIGN KEY (attribute of table_name) REFERENCES table_name(primary_key_attribute)
)

 CREATE TABLE points(
   ...,
   FOREIGN KEY (userID) REFERENCES users(userID)
)
CREATE TABLE table_name(
   ...,
   field_name int FOREIGN KEY REFERENCES table_name(primary_key_attribute)
)		 CREATE TABLE points(
   ...,
   userID int FOREIGN KEY REFERENCES users(userID)
)

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Syntax Example
ALTER TABLE table_name
ADD column_name datatype;		 ALTER TABLE users
ADD login varchar(25);
ALTER TABLE table_name
DROP COLUMN column_name;		 ALTER TABLE users
DROP COLUMN address;

How to create Foreign Key?

Syntax Example
ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES another_table(primary_key);		 ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Part 1. Создаем базу данных MySQL

Video 2. SQL запросы. Добавление записей в таблицу

Questions:

Exercises:

Ex. 1

Test (Ex. 1-7) on w3schools.com

Exam questions:

 

Категория: Relational databases | Добавил: bzfar77 (10.11.2020)
Просмотров: 7223 | Теги: query, DDL, DML, SQL | Рейтинг: 5.0/5
Всего комментариев: 0
Войдите:
avatar