Some concepts of the database

Database principles, SQL (DDL, DML)

• Databases (database, abbreviated DB) are organized according to data structures,
  A warehouse where data is stored and managed.

• Database Management system, short
  DBMS): The software that manages the database.

• Database engine, database paradigm (Learn about the encyclopedia)

• The concept of a table
  A relational database consists of multiple data tables (Tabe), and the data table is a relational
  Basic storage structure of the database
  Tables are two-dimensional and consist of rows and columns
  Table rows are horizontal data, also known as Records (record)
  The columns of a table are columnar data, also known as Fields (field)
  There is an association between tables and tables

• Mainstream relational database

• Oracle is a well-known Oracle (Oracle) company's database product
  DB2 is IBM's relational database management system
  Sybase is a relational database system for Sybase, USA
  Microsoft SQL Server is a Microsoft product that runs on a windowsnt server
  MySQL is a small relational database management system that develops source code and is widely
  Applications in small and medium-sized Web sites, (currently also belonging to Oracle)

• SQL Overview
  SQL: Structured Query Language
  SQL is the execution of data operations, retrieval, and maintenance on a relational database.
  The standard language that can be used to query data, manipulate data, define data,
  Control data
  All databases use the same or similar language

• SQL can be divided into:

  • Data definition Language (DDL):D ATA definition Language
  • Data manipulation Language (DML):D ATA manipulation Language
  • Transaction Control Language (TCL): Transaction control Language
  • Data Query Language (DQL):D ATA query Language
  • Data Control Language (DCL):D ATA control Language

• Ddl
  Data Definition Language
  Used to create, modify, and delete database objects
  Including:

  • Create: Creating a structure for a table or other object
  • Alter: Modify the structure of a table or other object
  • Drop: Delete the structure of a table or other object
  • TRUNCATE: Deleting table data, preserving table structure
    DDL: Data Definition language
    DDL is used to maintain database objects using
    Database objects: Tables, views, indexes, sequences.

• Dml
  Data Manipulation Language
  Used to change data in a data table
  and transaction-related, after execution, after the transaction control statement is submitted before
  Real changes are applied to the database
  Including:

  • Insert: Inserting data into a data table
  • Update: Updating data that already exists in the data table
  • Delete: Deleting data from a data table

• Tcl
  Transaction Control Language
  Statements to maintain data consistency
  Including:

  • Commit: Commit, confirm the data changes that have been made
  • ROLLBACK: Rollback, cancellation of data changes already in progress
  • SavePoint: Saves the point so that the current transaction can be retired to the specified
    Save points for easy partial change cancellation

• DQL
  Data Query Language
  Used to query the required data
  SELECT statement

• DCL
  Data Control Language
  Grant and retract operations for performing permissions
  Including:

  • Grant: Granted to grant permissions to a user or role
  • REVOKE: Used to reclaim permissions from a user or role
  • Create User: Creating users

• Oracle data types
  Number indicates a numeric type
  is often defined as number (p,s) Form, where:

  • P represents the total number of digits
  • S represents the number of digits after the decimal point
    Char represents a fixed-length character type
    is often defined as a char (n) Form, n indicates the number of bytes occupied
    The maximum length is 2000 bytes
    VARCHAR2 indicates a variable-length character type
    The definition format is VARCHAR2 (n) and n indicates the maximum number of bytes that can be consumed
    The maximum length is 4000 bytes
    Date data used to define the datetime
    Length is 7 bytes
    The default format is: DD-MON-RR, for example: 11-apr-71

• Create a table
  Create statement
  CREATE TABLE name ()
  Desc statement (View the structure of the table)
  DESC table_name
  Default statement
  You can specify a default value for a column by using the default clause
  Assign the default value ' M ' to the gender column, if there are no employees of the specified gender,
  The default is male.
  Not NULL
  Non-NULL is a constraint that ensures that the field value is not empty
  By default, any column is allowed to have a null value
  When a field is set to a non-null constraint, a valid value must exist in this field
  When you perform an operation that inserts data, you must provide the data for that column
  You cannot set the value of this column to NULL when you perform an update operation

• Modify Table
  Modify Table Name
  If you want to modify the table name after you build the table, you can use the Rename statement to implement
  Add columns
  Add column to table can be implemented using ALTER TABLE's ADD clause
  Syntax: ALTER TABLE table_name ADD
  Columns can only be added at the end and cannot be inserted into an existing column
  Delete Column
  Use ALTER TABLE table_name DROP (column);
  Deleting a field requires removing the length and data occupied by the field from each row,
  and frees up space in the data block, deleting the field may take a long time if the table records are larger.
  modifying columns
  After you build the table, you can change the data type, length, and default values of the columns in the table
  Modification is valid only for later inserted data
  If the length is changed from large to small, it may not be successful
  Syntax: ALTER TABLE table_name MODIFY

• DML statements
  Insert statement: Adding records to a data table
  The syntax is as follows:
  INSERT into table_name[(Column[,column ...])
  VALUES (Value[,value ...]);
  After performing the DML operation, the commit statement needs to be executed before the actual
  Confirmed this action
  If the inserted column has a date field, you need to consider the format of the date
  You can customize the date format and use the To_date function to convert to date type data
  UPDATE statement
  Update records in a table
  The syntax is as follows: UPDATE table_name
  SET column = Value[,column=value]..
  [Whrer condition];
  If there is no WHERE clause, the data for the entire table is updated, so be careful
  Delete statement
  Delete a record in a table
  The syntax is as follows: Delete[from] table_name[where condition];
  If there is no WHERE clause, the data for the entire table will be deleted!

Some concepts of the database