“Here is Cloud Source IT, helping you learn IT easily”
Hi~ How are you doing today?
The process of getting better is not very comfortable.
Try harder
– 2023.11.03 –
DDL (Data Definition Language) is a language used to describe real-world entities to be stored in a database.
Previously, we introduced the related concepts and basic knowledge of database and SQL language. In this article, we will focus on the relevant content of DDL (grammar format of data definition language) and the common statements of DDL.
1. Introduction to DDL
Here we first review the concept of SQL language mentioned earlier: SQL (Structured Query Language), that is, structured query language, is a standardized language for related operations in relational databases (such as Mysql, SQL Server, Oracle, etc.). It can be divided into the following types according to the role of sql:
Let’s take a look at what the DDL language is:
DDL stands for Data Definition Language, which is data definition language. It is an important part of the SQL language and is mainly used to define and manage the structure of the database.
2. What can DDL language do?
Through DDL, we can create, modify and delete databases, tables, views and other objects.
-
Create a database: Using the CREATE DATABASE statement, we can create a new database.
-
Delete database: Using the DROP DATABASE statement, we can delete an existing database.
-
Create table: Using the CREATE TABLE statement, we can create a new table in the database.
-
Dropping a table: Using the DROP TABLE statement, we can delete an existing table.
-
Modify the table structure: Using the ALTER TABLE statement, we can modify the structure of an existing table, such as adding, deleting or modifying fields, etc.
3. What is a database object
Database objects are components of the database. Common ones include the following:
1. Table
The tables in the database are similar to the tables used in our daily life. They are also composed of rows and columns.
Columns are composed of similar information. Each column is also called a field, and the title of each column is called the field name. Rows include several columns of information items. A row of data is called a record or a record, which expresses a meaningful combination of information. A database table consists of one or more records. A table without records is called an empty table. There is usually a primary key in each table that uniquely identifies a record.
2. Index (Index)
Indexes are built in order based on specified database table columns. It provides fast access to data and monitors the table’s data so that there is no duplication of data in the columns pointed to by its index.
3. View
A view looks exactly like a table, with a set of named fields and data items, but it is actually a virtual table that does not actually exist in the database. Views are generated by querying database tables and limit the data that users can see and modify.
It can be seen that views can be used to control users’ access to data and simplify the display of data, that is, only the required data information is displayed through views.
4. Diagram
A chart is actually a diagram of the relationship between database tables. Use it to edit relationships between tables.
5. Default value (Default)
The default value is to assign a preset value to a column or column data item whose specific value is not specified when a column is created or data is inserted into the table.
6. Rules
Rules are restrictions on data information in database tables, and they limit the columns of the table.
7. Trigger
A trigger is a user-defined collection of SQL transaction commands. This set of commands will be automatically executed when inserting, changing, or deleting a table.
8. Stored Procedure
A stored procedure is a set of SQL program statements that are gathered together to complete a specific function. They are compiled and stored in the database as a SQL program.
9. User
The so-called user is a person who has permission to access the database.
4. Common DDL statements
4.1 Database related
1) View all databases
Format: show databases;
2) Create database
Format: create database database name charset=utf8;
Example:
#Create a database named test #create database library name; create database test; #Create a database named test and specify the character set and encoding format create database test default charset utf8 collate utf8_general_ci;
3) View database information
Format: show create database library name;
4) Delete database
Format: drop database database name;
Example:
#Delete test database drop database test;
5) Use database
A database must be used before executing table-related and database-related SQL statements.
Format: use database name;
Example:
use test;
4.2 Table related
1) Create table
Format: create table table name (field 1 name type, field 2 name type,…)
Example:
create table person(name varchar(50),age int); create table person(name varchar(50),age int); create table stydent(name varchar(50),chinese int,math int, english int)charset=utf8; Create an employee table emp to save name, salary and job create table emp(name varchar(50),salary int,job varchar(20));
2) Query all tables
Format: show tables;
3) Query table information
Format: show create table table name;
Example:
show create table emp;
4) Query table fields
Format: desc table name; (description)
5) Modify the table name
Format: rename table original name to new name;
Example:
rename table stydent to stu;
6) Delete table
Format: drop table table name;
4.3 Related to alter table operations
1) Add table fields
Format (added at the end): alter table table name add field name type;
Format (add at the front): alter table table name add field name type first;
Add after the xxx field: alter table table name add field name type after field name;
Example:
alter table emp add gender gender varchar(5); alter table emp add id int first; alter table emp add dept varchar(20) after name;
2) Delete table fields
Format: alter table table name drop field name;
Example:
alter table emp drop dept;
3) Modify table fields
Format: alter table table name change original name new name new type;
Example:
alter table emp change job dept varchar(10);
4) Modify column attributes
Format: alter table table name modify column name new column attributes
For example (only MySQL is written this way):
alter table student modify age int;
So much about common DDL statements. Although there are many graphical tools that can replace traditional SQL statements for operations, and databases can also be used in languages such as Java, it is still very important to understand various SQL statements.
See you next time!
Copy Editor|Cloud Senior
Copywriting and pictures|Cloud Senior
Content by: Yunduanyuan wants to share