MySQL server variable data operations DML-View

MySQL server variable data operations DML-View Components of the SQL language

Common categories:

DDL: Data Definition Language DCL: data control language, for example, DML authorization: data operation language

Other categories:

Integrity definition language: some functional constraints of DDL: including the primary key, foreign key, unique key, condition, non-null, transaction, and other constraints view definition: virtual table, is the stored SELECT statement transaction control Embedded SQL and dynamic SQL

Data Dictionary: System catalog)

Function: Save the metadata on the database server

Database for saving metadataInclude:

information_schemamysqlperformance_schema

Metadata includes:

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

Data types and attribute modifiers of the SQL language

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

# Corresponding available property modifier: not null: non-empty constraint NULL: allow null default 'string': DEFAULT value, NOT applicable to the text character set 'character set ': set the character set to view the current MySQL character set: show variables like '% char %'; view the applicable character set: show character set; COLLATION: set the character set sorting rules to view available sorting rules: show collation;

Balanced type 2: Binary, varbinary, blob, etc.

# Corresponding available property modifier not null, NULLDEFAULT: NOT applicable to blob type

Integer

# Corresponding available attribute modifier: AUTO_INCREMENT: automatic growth (premise: non-empty, unique, index supported, non-negative value) UNSIGNED: not null, NULLDEFAULT

Floating point type

# Available attribute modifier: not null, NULLDEFAULTUNSIGNED

Datetime type

# Available property modifier: not null, NULLDEFAULT

Built-in type

# Differences between ENUM and set enum: Enumeration, which is generally used to store strings. only one of them can be used to use SET: SET. generally, a single character is stored, can be used to combine various characters to form a string # corresponding available attribute modifier: not null, NULLDEFAULT

Server variables in MySQL

Server variables that support modification

# General Classification dynamic variables: you can modify static variables when MySQL is running: modify the value in the configuration file to take effect after it is restarted # classify global variables from the effective scope: server Level. after modification, only valid session variables for the newly established session: Session level, valid only for the current session. when the session is created, all variables are inherited from the global

View Server variables

# General Classification dynamic variables: you can modify static variables when MySQL is running: modify the value in the configuration file to take effect after it is restarted # classify global variables from the effective scope: server Level. after modification, only valid session variables for the newly established session: Session level, valid only for the current session. when the session is created, all variables are inherited from the global

Modify server variables

# Premise: by default, only the administrator has the permission to modify the global variable set {global | session} Variable_Name = 'value'; # Note: whether it is global or session-level dynamic variable modification, it will expire after mysqld is restarted. if you want to take effect permanently, you can define the corresponding section in the configuration file [mysqld]

Data operations in MySQL

Database operations

Table operations

Create a table

Delete table

Modify table structure

Data operations

IncludingInsert),Replace),Update),Delete)Operation

MySQL view

Nature: View is the stored select statement.

Create View

Create view View_Name as select_clause # for example, create view stu as select Name, Age, ClassID from students;

Display view creation attributes

show create view stu;

Use: When authorizing a user, only the view is specified, the user can only view the content in the view, but not all the data.