MySQL server variable data operation dml-view

components of the SQL language

Common categories:

DDL: Data Definition Language DCL: Data Control language, such as authorization DML: Data Manipulation language

Other categories:

Integrity Definition Language:    part of the DDL feature constraint    constraints: Including primary key, foreign key, unique key, condition, non-null, transaction and other constraints view definition: That is, the virtual table is the storage of the SELECT statement transaction control Embedded SQL and dynamic SQL

Data dictionary : Systems Catalog (System catalog)

role : Saving metadata on the database server

the database where the metadata is stored is:

Information_schemamysqlperformance_schema

meta data includes :

Relationship name relationship The name of each field in each field and the length of the constraint relationship on the view name and the definition of the view define the authorization of the user name and the account information how to store the number of rows in a data relationship field in the statistics class

Data types and property modifiers for SQL language

character type 1: Char,varchar,text, etc.

# corresponding to available property modifiers: NOT null: non-NULL constraint null: Allow null default ' string ': Default value, not applicable to text type character set ' character set ': Set character set    view current MySQL character set: show variables like '%char%';    View available character sets: show character set; COLLATION: Set collation for character Set    view available collations: show COLLATION;

character Type 2: Binary,varbinary,blob, etc.

# corresponds to the available property modifier not Null,nulldefault: does not apply to BLOB types

Integral type

# corresponding to available property modifiers: auto_increment: Auto-Grow (premise: non-null, unique, support index, non-negative) UNSIGNED: unsigned not Null,nulldefault

Floating point Type

# corresponding to the available property modifiers: not null,nulldefaultunsigned

Date-Time Type

# corresponding to the available property modifiers: not Null,nulldefault

Built-in types

# enum and SET differences    Enum: Enumeration, general storage string, can only take one use    set: Set, generally store a single character, you can combine each character to form a string using # corresponding to the available property modifiers: not Null,nulldefault

Server variables in MySQL

Server variables that support modification

# General Classification    dynamic variable: You can modify the static variable at MySQL runtime    : Modify its value in the configuration file, and it will take effect after restarting # from its effective scope to classify    global variables: server level, only valid    for newly established session after modification Session variables: Session level, valid only for the current session, and when session is established, inherit variables from the global

View server Variables

# General Classification    dynamic variable: You can modify the static variable at MySQL runtime    : Modify its value in the configuration file, and it will take effect after the restart # from its effective scope to classify    global variables: server level, modified only for the newly established session valid    session variables: Session level , valid only for the current session, and when the session is established, the variables are inherited from the global

modifying server variables

# Premise: Default only administrator has permission to modify global variable set {global|session} Variable_name=' Value '; # Note: Changes to dynamic variables, whether global or session-level, are invalidated after restarting Mysqld, and can be defined in the corresponding segment in the configuration file if they are to be permanently active [mysqld]

Data manipulation in MySQL

Database operations

Table Operations

Create a table

Delete a table

Modify Table Structure

Data manipulation

Include insert, replace, Update (update), delete action

View of MySQL

Essence : A view is a stored SELECT statement

Create a View

Select Name,age,classid from students;

To display the creation properties of a view

Show CREATE View Stu;

use : When authorizing user permissions, only the view is specified, the user can only view the contents of the view, and cannot view all the data