0x01
Two directions of MySQL:
Development DBA: Database Design (e-r diagram), SQL development, built-in functions, storage history (stored procedures and storage functions), triggers, Time Scheduler (event Scheduler)
Operations----> Management DBA: Installation, upgrade, backup, recovery, user management, rights management, monitoring, analysis, benchmarking, statement Optimization (SQL statements), data dictionary, configuring servers as needed (server variables: myisam,innodb, cache, log)
SQL Language components:
DDL: Data Definition language
DML: Data Manipulation language
Integrity definition language, part of the DDL feature
Primary KEY constraints, FOREIGN KEY constraints, unique key constraints, conditional constraints, non-null constraints, transaction constraints
View definition: Virtual table, stored SELECT statement
Transaction control:
Embedded SQL and dynamic sql:
DCL: Data Authorization language
The function of the data type:
1. Stored value types
2. Amount of storage space occupied
3. Fixed length, variable length
4. How to be indexed and sorted
5. Is it possible to be indexed
Data dictionary: Dependent systems Catalog (System catalog)
To save metadata on the database server
Meta Data:
The name of the relationship
The names of each field in each relationship
Data type and length of each field
Constraints
The name of the view and the definition of the view on each relationship
Name of authorized user
User's authorization and account information
Data for Statistics classes
Number of each relationship field
Number of rows per relationship
Storage methods for each relationship
The database that holds the metadata
Information_schema
Mysql
Performance_schema
Data type:
Character type
Numeric type
Precise numerical type
Integral type
Decimal Data: Decimal
Approximate numerical type
Single-precision floating-point type
Double-precision floating-point type
Date-Time Type
Date type
Time Type
Date-Time Type
Time stamp
Year
Boolean type
Built-in type
Enum (enum)
Set (SET)
Numeric type:TINYINT SMALLINT mediumint INT BIGINT DECIMAL FLOAT Doubal
Character type: char 255 characters varchar 65,535 characters tinytext text mediumtext longtext binary varbinary tinyblob
BLOB Mendiumblob Longblob ENUM SET
DateTime type: Date time DATETIME TIMESTAMP Year
0x02
Character types (char, varchar, and text) are commonly used property modifiers:
NOT NULL: non-null constraint
Null: Allowed to be empty
Default ' string ': defaults, not for text type
CHARACTER Set Character Set
Show variables like '%char% ' view the default character set
Show Character Set view the character set supported by the database
Show collation sorting rules
Collation ' rules ': modifying collations
Binary,varbinaray and Blob characters common property modifiers
Not NULL
Null
Default does not apply with BLOB
Common property modifiers for shaping
Auto_increment: Automatic growth
Prerequisite: Non-null, and unique, support index
LAST_INSERT_ID (): The last increment can be viewed, when multiple rows are inserted, only the first row of select LAST_INSERT_ID () is recorded;
UNSIGNED: Unsigned
Null
Not NULL
DEFAULT
TRUNCATE is used to clear the data in the table TRUNCATE
Common modifiers for floating-point characters:
Null
Not NULL
Unsignsd
DEFAULT
Use G,f to define how many numbers are in total and how many digits after the decimal point
Date time type commonly used modifiers
Null
Not NULL
DEFAULT
Modifiers for enum and set
Null
Not NULL
DEFAULT ' '
0x03
MySQL has built-in SQL schema Sql_mode: Used to define the operation of the character beyond and to mimic the type of the other database by modifying the global variable
MySQL default mode intercepts data that exceeds the character type char (3) Insert Hello---The end result is that Hel is intercepted
The three commonly used modes of Sql_mode are:
traditional using traditional mode
strict_trans_tables Strict mode only for tables that support transactions
strict_all_tables Use strict mode for all tables
Show global variables like ' sql_mode ';
Set the value of a server variable: typically used only to support dynamic variables
Server variables that support modification
Dynamic variables: Can be modified at MySQL runtime
Static variables: Modify their values in the configuration file and reboot to take effect
Server variables in terms of their effective scope, there are two types of
Global variables: server level, modified only for newly established sessions
Reply variable: Session level, valid only for the current session
When a session is established, the variables are inherited from the global
View server Variables
Format:
Mysql> show [{global|session}] variables [like '];
Mysql> Select @@{globa|session}.variable_name
Instance:
Mysql> mysql> SELECT * from INFORMATION_SCHEMA. Global_variables where variable_name= ' some_variable_name '; View global Variables
Mysql> mysql> SELECT * from INFORMATION_SCHEMA. Session_variables where variable_name= ' some_variable_name '; viewing session Variables
To modify a variable:
Premise: Default only Administrators have permission to modify global variables
Mysql> set {global|session} variable_name= ' value ';
set session sql_mode= ' Strict_all_tables '; ------Change the Sql_mode mode to Strict_all_tables
Change to the above mode and deny write directly to data with more than one character type.
SELECT @ @session. sql_mode;
View:
SELECT * FROM INFORMATION_SCHEMA. Session_variables where variable_name= ' Sql_mode ';
SELECT * FROM INFORMATION_SCHEMA. Global_variables where variable_name= ' Sql_mode ';
Note: Both global and session-level dynamic variable modifications are invalidated after restarting MySQL and want to be permanently valid and can be defined in the Response section in the configuration file [Mysqld]
See what command line arguments are available for the MySQL server variable mysqld--help--verbose
MySQL Basics-data types and SQL mode-Learning (iii)