MySQL data types and Sql_mode

#####################################################################

Development DBA: Database Design (e-r diagram), SQL development, built-in functions, storage routines (stored procedures and stored functions), triggers, Event Scheduler (Events Scheduler)

Manage DBAs: Install, upgrade, backup, restore, 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:

Dml:

Integrity Definition Language: Part of the DDL feature

Primary key, foreign key, unique key, condition, non-null, transaction

View definition: Virtual table, stored SELECT statement

Transaction control:

Embedded SQL and dynamic sql:

DCL: Authorization

The function of the data type:

1, the stored value type;

2, occupy the reef storage space;

3, fixed length, variable length;

4, how to be indexed and sorted;

5, whether can be indexed;

Data dictionary: 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 the statistics class:

The number of each relationship field;

Number of rows per relationship;

The storage method of each relationship;

To save the metadata database:

Information_schema

Mysql

Performance_shcema

Data type:

Character type

Char

varchar

Binary

varbinary

Text

Blob

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

Boolean type

Built-in type

ENUM, SET

Numeric type:

TINYINT

SMALLINT

Mediumint

Int

BIGINT

DECIMAL

FLOAT

Doubal

BIT

Character type:

CHAR

VARCHAR

Tinytext

TEXT

Mediumtext

Longtext

BINARY

VARBINARY

Tinyblob

Blob

Mediumblob

Longblob

Enum

SET

Date and Time type:

DATE

Time

Datetime

TIMESTAMP

Year

char, varchar, and text are commonly used property modifiers for several character types:

NOT NULL: non-null constraint

Null: Allowed to be empty

Default ' string ': defaults, not for text type

CHARACTER set ' character set '

mysql> SHOW VARIABLES like '%char% ';

mysql> SHOW CHARACTER SET

COLLATION ' rules ': Sorting rules

Mysql> SHOW COLLATION;

BINARY, varbinary, and BLOB property modifiers commonly used in several character types:

Not NULL

Null

DEFAULT: Not applicable to BLOBs

Common property modifiers for integral types:

Auto_increment: Automatic growth

Premise: Non-null, and unique; Support index, non-negative value;

UNSIGNED: Unsigned

Null

Not NULL

DEFAULT

Common modifiers for floating-point characters:

Not NULL

Null

DEFAULT

UNSIGNED

Modifiers for datetime type:

Not NULL

Null

DEFAULT

Modifiers for enum and set:

Not NULL

Null

DEFAULT ' '

MySQL sql_mode:sql Mode

Traditional, strict_trans_tables, or Strict_all_tables

Set the value of the server variable: (only for dynamic variables)

Server variables supported for modification:

Dynamic variables: Can be modified at MySQL runtime

Static variable: Modify its value in the configuration file and restart it to take effect;

There are two types of server variables in terms of their effective scope:

Global variables: server level, only valid for newly established sessions after modification;

Session variable: Session level, valid only for the current session;

When a session is established, the variables are inherited from the global;

To view server variables:

Mysql> SHOW [{global| SESSION}] VARIABLES [like ' '];mysql> SELECT @@{global| SESSION}. Varilable_name;mysql> SELECT * from INFORMATION_SCHEMA. Global_variables WHERE variable_name= ' some_variable_name ';mysql> SELECT * from INFORMATION_SCHEMA. Session_variables WHERE variable_name= ' some_variable_name ';

modifying variables

Premise: Default only Administrators have permission to modify global variables

Mysql> SET {global| SESSION} variable_name= ' VALUE ';

Note: Both global and session-level dynamic variable modifications are invalidated after restarting Mysqld, and are permanently valid and can be defined in the corresponding section of the configuration file [mysqld];

Create a database

mysql> CREATE DATABASE mydb;mysql> use mydb;

CREATE TABLE: Reshape non-null unique non-negative auto_increment

UNSIGNED unsigned just does not support negative numbers

mysql> create table t1  (id int unsigned auto_increment primary  key,name char () mysql> desc t1;+-------+------------------+------+-----+---------+ + ---------------+| field | type              | Null | Key | Default | Extra           |+-------+------------------+------+-----+---------+----------------+|  id    | int (Ten)  unsigned | no   | pri |  null    | auto_increment | |  name  | char ()          | YES   |     | NULL    |                 |+-------+------------------+------+-----+---------+----------------+mysql> select * from t1;mysql>  insert into t1  (Name)  VALUES  (' TOM '), (' Jerry '); mysql> select *  from t1;+----+-------+| id | name  |+----+-------+|  1 |  tom   | |   2 | jerry |+----+-------+2 rows in set  (0.00 sec)

Show last modified record

Mysql> SELECT last_insert_id (); +------------------+|                last_insert_id () |+------------------+| 1 |+------------------+1 row in Set (0.02 sec) mysql> insert into T1 (Name) VALUES (' Lily '), (' haha ');mysql> SELECT LA ST_INSERT_ID (); +------------------+|                last_insert_id () |+------------------+| 3 |+------------------+

Clear table

Mysql> DELETE from t1;mysql> inserts into T1 (Name) VALUES (' Lucy '); Query OK, 1 row affected (0.11 sec) mysql> SELECT * from t1;+----+------+| ID |  Name |+----+------+| 5 | Lucy |+----+------+mysql> SELECT last_insert_id (); +------------------+|                last_insert_id () |+------------------+| 5 |+------------------+1 row in Set (0.00 sec)

LAST_INSERT_ID () records only the last inserted data, even if the Delete purge table record exists

##########################################################

TRUNCATE Empty

mysql> help truncatemysql> truncate t1; query ok, 0 rows affected  (0.06 SEC) mysql> select last_insert_id (); +------------------+| last_insert_id ()  |+------------------+|                 5 |+------------------+1 row in  set  (0.00 sec) mysql> select * from t1; empty set  (0.00 sec) mysql> insert into t1  (Name)  VALUES  (' Lucy ‘); query ok, 1 row affected  (0.07 sec) mysql> select * from  t1;+----+------+| id | name |+----+------+|  1 | lucy |+----+---- --+1 row in set  (0.00 SEC) mysql> select last_insert_id (); +-------------- ----+| last_insert_id ()  |+------------------+|                1 |+------------------+1 row in  set  (0.00 SEC)

########################################################

exceeds the character length and is truncated by default

mysql> create table t2  (name char  (3)); query ok, 0 rows affected  (0.14 sec) mysql> insert into t2  VALUES  (' Jerry '); query ok, 1 row affected, 1 warning  (0.10 sec) Mysql> SHOW  warnings;+---------+------+-------------------------------------------+| level   |  Code | Message                                     |+---------+------+-------------------------------------------+| warning |  1265 | Data truncated for column  ' name '  at row 1 |+--- ------+------+-------------------------------------------+mysql> select * from t2;+---- --+| name |+------+| jer  |+------+ 

###############################################

Mysql> SHOW GLOBAL VARIABLES like ' sql_mode '; +---------------+-------+| variable_name | Value |+---------------+-------+|       Sql_mode | |+---------------+-------+

MySQL data types and Sql_mode