MySql concept (2): mysql Concept

MySql concept (2): mysql Concept

I. SQL specifications?

SQL is the abbreviation of Structured Query Language (Structured Query Language. SQL is a set of operation commands for databases. It is a fully functional database language.

When using it, you only need to issue the "what to do" command, and the "how to do" command does not need to be considered by the user. SQL is powerful, easy to learn, and easy to use. It has become the foundation for database operations, and almost all databases now support SQL.

<1> in the database system, SQL statements are case-insensitive (uppercase is recommended ). However, string constants are case sensitive. We recommend that you use upper-case commands and lower-case table names;

<2> An SQL statement can be written in a single row or multiple rows, ending. The keyword cannot be cross-line or abbreviated.

<3> space and indentation are used to improve statement readability. The clause is usually located in an independent row, which facilitates editing and improves readability.

12	SELECT * FROM tb_table            WHERE NAME="YUAN";

<4> comment: single line comment :--

Multi-line comment :/*......*/

<5> SQL statements can be broken down

Ii. SQL data type:

1. Value Type:

 

2. What is the unsigned type?

　　

========= Signed and unsigned tinyint ==========## tinyint is a signed MariaDB [db1]> create table t1 (x tinyint ); # The default value is signed, that is, MariaDB [db1]> desc t1; MariaDB [db1]> insert into t1 values-> (-129 ), -> (-128),-> (127),-> (128); MariaDB [db1]> select * from t1; + ------ + | x | + ------ + |-128 | #-129 is saved as-128 |-128 | # signed, minimum value:-128 | 127 | # signed, maximum Value: 127 | 127 | #128 saved as 127 + ------ + # Set the unsigned tinyintMariaDB [db1]> create table t2 (x tinyint unsigned ); mariaDB [db1]> insert into t2 values-> (-1),-> (0),-> (255),-> (256 ); mariaDB [db1]> select * from t2; + ------ + | x | + ------ + | 0 |-1 is saved as 0 | 0 | # unsigned, the minimum value is 0 | 255 | # unsigned. The maximum value is 255 | 255 | #256 is saved as 255 + ------ +

　　3. Display length and storage byte?

mysql> create table test(id int);Query OK, 0 rows affected (0.01 sec)mysql> desc test;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(11) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+row in set (0.00 sec)

　　

4. decimal type?

Float: float type. The value ranges from-3.4E38 to-32, including 4 bytes ~ 3.4E38 (7 valid bits) double: double precision, containing 8 bytes, 64bit value range-1.7E308 ~ 1.7E308 (15 valid digits) decimal: Number type, 128 value range: + 1. 0 × E28 to ± 7. the accuracy of the 9 × E28 (28 valid bits) decimal is greater than that of the double type, but the maximum number of values that can be stored is smaller than that of the double type. Decimal has a loss of precision, but it is only small!

Decimal has a higher precision than double, but the maximum number of data stored is smaller than double. Decimal has a loss of precision, but it is only small!

 

V. BIT

BIT (M) can be used to store multiple binary numbers. M ranges from 1 ~ 64. If this parameter is not specified, the default value is 1 bit.
Note: function reading is required for bit fields.
Bin () is displayed as binary
Hex () is displayed as hexadecimal

 

mysql> create table t(id bit);Query OK, 0 rows affected (0.03 sec)mysql> desc t;+-------+--------+------+-----+---------+-------+| Field | Type   | Null | Key | Default | Extra |+-------+--------+------+-----+---------+-------+| id    | bit(1) | YES  |     | NULL    |       |+-------+--------+------+-----+---------+-------+row in set (0.00 sec)mysql> insert t values (1);Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+| id   |+------+|      |+------+row in set (0.00 sec)mysql> select bin(id) from t;+---------+| bin(id) |+---------+| 1       |+---------+row in set (0.00 sec)

　　

mysql> alter table t modify id bit(5);Query OK, 1 row affected (0.02 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> insert into t values(8);Query OK, 1 row affected (0.00 sec)mysql> select bin(id),hex(id) from t;+---------+---------+| bin(id) | hex(id) |+---------+---------+| 1       | 1       || 1000    | 8       |+---------+---------+rows in set (0.00 sec)

　　

6. string type?

　　Storage string:

CHAR series: CHAR VARCHAR
TEXT series: TINYTEXT TEXT MEDIUMTEXT LONGTEXT

Store binary data:

BINARY series: BINARY VARBINARY
BLOB series: TINYBLOB BLOB MEDIUMBLOB LONGBLOB

 

/* The length of the char (m) CHAR column is fixed to the length declared during table creation: 0 ~ 255. M indicates the length of the string. PS: even if the data length is smaller than m, it will take up m length. The value in the varchar (m) VARCHAR column is a variable length string with a length: 0 ~ 65535. M indicates the maximum length of the string that can be saved by the data type. Strings whose length is smaller than the maximum value can be saved in the data type. Note: Although varchar is more flexible to use, from the perspective of system performance, char data processing speed is faster, sometimes even 50% faster than varchar processing speed. Therefore, when designing a database, users should consider various factors in order to achieve the best balance between the text and text data types for saving long strings, it can contain up to 65535 (2*16-1) characters. Mediumtext a text column with a maximum length of 16,777,215 (2 ** 24 − 1) characters. longtext a text column with a maximum length of 4,294,967,295 or 4 GB (2 ** 32 −1) characters. */

　　

During query, spaces at the end of the CHAR column are deleted, while those at VARCHAR are retained. Mysql> create table t1 (x char (5), y varchar (5); mysql> insert into t1 values (' ', ' '); mysql> select x, length (x), y, length (y) from t1; + -------- + ----------- + ---------- + ----------- + | x | length (x) | y | length (y) | + -------- + ----------- + ---------- + ----------- + | | 9 | | 11 | + -------- + ----------- + ---------- + ----------- +

　　

 VII. date type

The DATE and TIME types of the TIME value are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a valid value range and a "zero" value. It is used when an invalid MySQL value cannot be expressed.

(1), year

　　　　

============year===================create table t_year(born_year year);insert into  t_year values (1901),                           (2155);select * from t_year;+-----------+| born_year |+-----------+|      1901 ||      2155 |+-----------+rows in set (0.00 sec)

　　(2) date time datetime

Mysql> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 19:38:54 |
+ --------------------- +
1 row in set (0.00 sec)

(3) timestamp

/* In many practical scenarios, the two date types of MySQL can meet our needs, and the storage accuracy is in seconds, but in some cases, demonstrate their respective advantages and disadvantages. Next we will summarize the differences between the two date types. 1. The DATETIME date range is 1001--9999 years, And the TIMESTAMP time range is 1970--2038 years. 2. The DATETIME storage time has nothing to do with the time zone. The TIMESTAMP storage time is related to the time zone, and the displayed value also depends on the time zone. On the mysql server, the operating system, and client connection Have Time Zone settings. 3. DATETIME uses an 8-byte storage space, and the TIMESTAMP storage space is 4 bytes. Therefore, the TIMESTAMP has higher space utilization than DATETIME. 4. the default value of DATETIME is null; the field of TIMESTAMP is not null by default, and the default value is current time (CURRENT_TIMESTAMP). If no special processing is performed, if the update value of this column is not specified in the update statement, it is automatically updated to the current time. */Datetime and timestamp

　　8. Enumeration type and set type

The field value can only be selected in a given range, such as a single region. If you select multiple boxes, you can only select one value within the specified range, for example, gender sex male/female femaleset multiple options can select one or more values within a given range (hobby 1, hobby 2, hobby 3 ...) resolution:

　　

/* Enum type (ENUM) An enum column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000 .) example: create table shirts (name VARCHAR (40), size ENUM ('x-small', 'small', 'medium ', 'large ', 'X-large'); insert into shirts (name, size) VALUES ('dress shirt', 'large'), ('t-shirt', 'medium '), ('polo shirt', 'small'); set type (SET) A set column can have a maximum of 64 distinct members. example: create table myset (col SET ('A', 'B', 'C', 'D'); INSERT INTO myset (col) VALUES ('a, d'), ('d, A'), ('a, d, A'), ('a, d, D'), ('d,, D ');*/