MySQL notes 2_ data sheet

Field data type (3 class ： The number 、 date / Time and string )：

1、 value type

MySql Supporting all standards SQ Numerical data type .

keyword int yes integer A synonym for , keyword dec yes decimal A synonym for

bit Data type saves field values , And support MyISAM、MEMORY、InnoDB and BDB surface

type	size ( byte )	Range （ A signed ）	Range （ Unsigned ）	purpose
tinyint	1	(-128, 127)	(0, 255)	Small integer value
smallint	2	(-32768,32767)	(0, 65535)	Large integer value
mediumint	3	(-8388608, 8388607)	(0, 16777215)	Large integer value
int or integer	4	(-2147483648, 2147483647)	(0, 4294967295)	Large integer value
bigint	8	(-9223372036854775808, 9223372036854775807)	(0,18 446 744 073 709 551 615)	Maximum integer value
float	4 byte	……	……	Single precision floating point values
double	8 byte	……	……	Double precision floating point value
decimal	Yes decimal(M,D) If M>D, by M+2 Otherwise D+2	Depend on M and D Value	Depend on M and D Value	Small value

2、 Date and time type

The date and event type representing the time value are datatime、date、timestamp、time and year

Each time type has a valid range and a " zero " value , When the designation is illegal MySQL Use... When the value cannot be represented " zero " value .

timestamp Type has a proprietary automatic update feature

type	size ( byte )	Range	Format	purpose
date	3	1000-01-01~ 9999-12-31	YYYY-MM-DD	Date value
time	3	'-838:59:59'~ '838:59:59'	HH:MM:SS	The value or duration of time
year	1	1901~2155	YYYY	The year is worth
datetime	8	1000-01-01 00:00:00~ 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	Mix date and time values
timestamp	4	1970-01-01 00:00:00~2038 The end time is the 2147483647 second , Beijing time. 2038-1-19 11:14:07, Greenwich mean time 2039 year 1 month 19 Early morning 03:14:07	YYYYMMDD HHMMSS	Mix time and time values , Time stamp

3、 String type

char and varchar Similar type , But they are stored and retrieved in different ways . Their maximum length and whether trailing spaces are preserved are also different . No case conversion during storage or retrieval .

binary and varbinary Be similar to char and varchar, The difference is that they contain binary strings, not binary strings . in other words , They contain byte strings instead of byte strings . That means they don't have character sets , And sort and compare values based on column value bytes

blob Is a binary large object , Can hold a variable amount of data . Yes 4 Kind of bolb type ：tinyblob、blob、medumblob and longbolb. The difference is that they can hold different storage ranges .

Yes 4 in text type ：tinytext、text、mediumtext and longtext. Corresponding 4 in blob type , The maximum length that can be stored is different , You can choose... According to the actual situation

type	size （ byte ）	purpose
char	0~255	Fixed length string
varchar	0~65535	Fixed length string
tinyblob	0~255	No more than 200 Binary string of bytes
tinytext	0~255	Text string
blob	0~65535	Text character data in binary form
text	0~65535	Long text data
mediumblob	0~16777215	Medium length text data in binary form
mediumtext	0~16777215	Medium length text data
longblob	0~4294967295	Maximum text data in binary form
longtext	0~4294967295	Large text data

1、 Create data table

create table < The name of the data table > ([ Table definition options ]……)[ Table options ][ Partition options ]
 among [ Table definition options ] The format is ：
< Name 1> < data type 1> ....
mysql> create table employee(
    -> _id int,
    -> name varchar(20),
    -> age int,
    -> salary double,
    -> sex varchar(5),
    -> department varchar(25)
    -> );
Query OK, 0 rows affected (0.04 sec)

2、 Look at all the data sheets

show tables;


mysql> show tables;
+-------------------------+
| Tables_in_personal_info |
+-------------------------+
| employee                |
+-------------------------+
1 row in set (0.00 sec)

3、 Check the code of the data table (charset: Character set / Encoding set )

show create table < Data table name >


mysql> show create table employee;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                           |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `department` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4、 Modify the code of the data table

alter table < Data table name > character set  Coding format 


mysql> alter table employee character set gbk;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

5、 Look at the structure of the data table 【desc（description： describe , Describe ; type ）】

Field： Field Null： Whether it can be null Key： constraint Defaul： The default value is Extra： Expand , Meaning of remarks

desc < The name of the data table >


mysql> desc employee;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| _id        | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| age        | int(11)     | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| sex        | varchar(5)  | YES  |     | NULL    |       |
| department | varchar(25) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

6、 Modify the structure of the table

1、 Modify the name of the table

alter table < The old name of the table > rename < The new name of the table >


mysql> alter table employee rename info_employee;
Query OK, 0 rows affected (0.01 sec)

2、 Change the name of the field

alter table < Table name > change < Old field name > < new field name > < type >( length );
 
mysql> alter table employee change name username varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、 Add fields

alter table < Data table name > add column < Field name > < Field type > [ constraint condition ] [first|after  Existing field name ]
first:（ Optional ） The function is to set the newly added field as the first field of the table 
alter:（ Optional ） The function is to add the newly added field to the specified existing field name 
 example ： Use alter table  Modify table info_employee Structure ,
 Add a... In the first column of the table int Type field coll, Input SQL The statement and result are as follows ：


mysql> alter table info_employee
    -> add column coll int first;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table info_employee
    -> add column name varchar(32) after _id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

4、 Delete column ( Field )

alter table < Data table name > drop < Field name >


mysql> alter table employee drop address;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

”First or after Existing field name “ Used to specify the position of the new field in the table , If SQL There are no these two parameters in the statement , Add... To the last default field

5、 Modify the length of the field / type

alter table < The name of the data table > modify column < Field name > < New type ( New length )>


mysql> alter table employee modify column department varchar(200);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0