MySQL data type (chubby teacher)

Like statements and wildcard characters

INSERT into teacher_1 values (' chubby ', ' male ', 18),
(' xiaoming ', ' male ', 19),
(' Zhang San ', ' Male ', 30),
(' John Doe ', ' Male ', 27),
(' spanned ', ' male ', 21),
(' Three fat ', ' female ', 31),
(' Zhang Sanfeng ', ' Male ', 66),
(' Zhang ', ' Male ', 11),
(' King _ White ', ' male ', 14),
(' Wang Dabai ', ' male ', 19);

Demand one: Find out the person surnamed Zhang;
SELECT * from teacher_1 where name is like ' Zhang% '; --% means 0 or more arbitrary characters are configured

Demand two: Query out the name of the second character is ' fat ' person;
SELECT * from teacher_1 where name like ' _ Fat% ';
PS:% is configured with 0 or more arbitrary characters, _ indicates the configuration of an arbitrary character

Requirement Three: Query out the first and second characters in the name are ' king ' and ' _ ' that person;
SELECT * from teacher_1 where name like ' King/_% ' escape '/';
SELECT * from teacher_1 where name is like ' King \_% ';

*************************************************************************************************************** ********

Data type
1: Numeric type
2: Date Type
3: String type
*************************************************************************************************************** ********

1: Shaping
tinyint type
tinyint if the signed range is (-128, 127), if the unsigned range is (0, 255);

You can use the unsigned identity to define whether the symbol, plus the unsigned identifier, is unsigned, and the default is a signed

CREATE TABLE T_1 (
A tinyint,
b tinyint unsigned
);
Example: INSERT into t_1 values (-128, 127);
Example: INSERT into t_1 values (-128,-1); --Error
Example: INSERT into t_1 values (255, 255); --Error
Example: INSERT into t_1 values (-128, 255);

Define the display width of the data
CREATE TABLE T_2 (
A tinyint (2)
);
PS: Type (2), such as tinyint (2), (2) does not mean that the maximum value can only be entered in 2 digits;
Instead, it indicates how much the minimum width is displayed, and if you want to achieve the purpose of displaying the minimum width, you also need to use Zerofill (leading 0 padding) to identify
Example: INSERT into t_2 values (123);
Example: INSERT into t_2 values (1);

ALTER TABLE t_2 add B tinyint (2) Zerofill;
Example: INSERT into t_2 (b) VALUES (2);
Example: INSERT into t_2 (b) VALUES (123);
Example: INSERT into t_2 (b) VALUES (-1); --Error, Mark the Zerofill will also default add a unsigned;

2: Decimal Type
Floating-point types
Float (single precision), double (dual precision)
CREATE TABLE T_3 (
A float,
b Double
);
Example: INSERT into T_3 (A, B) values (1234567890.123456789,1234567890.123456789);
+------------+--------------------+
| A | B |
+------------+--------------------+
| 1234570000 | 1234567890.1234567 |
+------------+--------------------+
Ps:float and double types can represent a large number of decimals, but if the value exceeds the precision digits, the precision is lost here
Float The default precision bit is about 6 bits, double the default precision bit number is about 17;

CREATE TABLE T_4 (
A float (4) Zerofill
);
INSERT into T_4 values (12);
INSERT into T_4 values (12345);


CREATE TABLE T_5 (
A float (6, 2)
);
PS: Float (m,d) such as float (6, 2) where 6 represents the maximum number of digits (not including symbols and decimal numbers), and 2 represents the largest number of digits
Example: INSERT into t_5 values (1234.56);
Example: INSERT into t_5 values (123.56);
Example: INSERT into t_5 values (123.5);
Example: INSERT into t_5 values (12345.6); --Error
Example: INSERT into t_5 values (1234.128); --no error, but the extra decimals will be truncated in a rounded manner.


Fixed-point number type (decimal)
Ps:decimal (M,D) The fixed-point number depends on M and D, the default value of M is ten, and the default value of D is 0; m represents total digits, and D represents the total number of decimal digits

CREATE TABLE T_6 (
A decimal (2) Zerofill
);
Example: INSERT into t_6 values (123456789123456789.12);
+------------------------+
| A |
+------------------------+
| 0123456789123456789.12 |
+------------------------+
*************************************************************************************************************** ********

Date type
DateTime (Month-date time and minute) and timestamp type (timestamp)
CREATE TABLE t_7 (
A datetime,
b Timestamp
);
Example: INSERT into t_7 values (' 2012-08-08 08:08:08 ', 54389867575365); --Error, time stamp is stored in shape, but is date
Example: INSERT into t_7 values (' 2012-08-08 08:08:08 ', ' 2012-08-08 08:08:08 ');
Example: Select A+0, b+0 from t_7; --If column +0 is retrieved, the format of the time will be shaped to display
+-----------------------+----------------+
| a+0 | b+0 |
+-----------------------+----------------+
| 20120808080808.000000 | 20120808080808 |
+-----------------------+----------------+
Example: INSERT into t_7 values (' 2012-08-08 08:08:08 ', ' 2038-01-20 '); --Error
Example: INSERT into t_7 values (' 2012-08-08 08:08:08 ', ' 2038-01-19 '); -No error
Example: INSERT into t_7 values (' 2012*08*08 ', ' 2038*01*19 '); --Support for any delimiter date
Example: INSERT into t_7 values (' 20120808 ', ' 20380119 '); --can also not write delimiter
Example: INSERT into t_7 values (' 20128808 ', ' 20380119 '); --Error


Date type
CREATE TABLE t_t (
A date,
b datetime
);
Example: INSERT into t_t values (' 2012-08-08 08:08:08 ', ' 2012-08-08 08:08:08 ');
The Ps:date type displays the date in the form of YYYY-MM-DD, DateTime displays the date in days and minutes, and seconds.


Time Type (d HH:MM:SS) d denotes days
Ps:time represents the day in the daytime
CREATE TABLE T_8 (
A time
);
Example: INSERT into t_8 values (' 2 08:08:08 ');
Example: INSERT into t_8 values (' 99:08:08 ');
Example: INSERT into t_8 values (' 990808 ');
Example: INSERT into t_8 values (' 99-08-08 '); --Error

Year Type
CREATE TABLE T_9 (
Year A
);
Example: INSERT into t_9 values (' 1901 ');
Example: INSERT into t_9 values (' 1900 ');
*************************************************************************************************************** ********

3: String type
Char (m) m denotes strictly defined length
varchar (m) m indicates the maximum allowable length;
CREATE TABLE T_10 (
C Char (4),
V varchar (4)
);
Example: INSERT into t_10 values (' AB ', ' ab ');
Example: INSERT into t_10 values (' ab ', ' abcde '); --Error
Example: INSERT into t_10 values (' ABCDE ', ' ab '); --Error

Text type (long text type)--and Tinytext, Longtext
CREATE TABLE T_11 (
A text,
b tinytext
);


enum Enum type
CREATE TABLE T_12 (
Sex enum (' Male ', ' female ', ' confidential ')
);
Example: INSERT into T_12 values (' No male or female ');
Example: INSERT into T_12 values (' Male ');


Set Collection type
CREATE TABLE T_13 (
Hobby set (' Eat ', ' drink ', ' sleep ')
);
Example: INSERT into t_13 values (' Eat,sleep ');
Example: INSERT into t_13 values (' Eat,sleep,drink ');
Example: INSERT into t_13 values (' Eat,sleep, drink '); --No spaces for error
Example: INSERT into t_13 values (' eat,sleep, Chubby '); --Chubby

MySQL data type (chubby teacher)