MySQL table operations and field data types

1. View all tables in the current database

Mysql> Show tables;

2, create the table, the ID field is an integer unsigned, can not have a negative number and is the primary key (the primary key is unique), the engine is InnoDB, the character set is UTF8, the annotation information is "Student table", the Int bracket 3 indicates the field width is 3, starts with 100 self-increment, zerofill means auto-complement 0

Mysql> CREATE TABLE Student (

ID int (3) unsigned zerofill NOT NULL auto_increment,

Name varchar (+) is not NULL,

Primary KEY (ID)

) Engine=innodb auto_increment=100 charset=utf8 comment= "Student table";

3. Delete a table

Mysql> drop table if exists student;

4. View table structure

mysql> desc Student;

5. View the building code

Mysql> Show create table student;

6. Inserting data

mysql> INSERT into student (name) VALUES ("Jack"), ("Harry"), ("Zhao Liu");

7. Simple query data

Mysql> select * from student;

8, display all the columns of the table, than DESC to detail

Mysql> Show full columns from student;

9, delete multiple tables, table names separated by commas

Mysql> drop table if exists stu,a,b;

field data type

1> integer: Tinyint (1 bytes, 0-255), smallint (2 bytes, 0-65535), Mediumint (3 bytes, 0-16777215), int (4 bytes, 0-4294967295), bigint (5 bytes, 0-18446744073709551615)

tinyint: Up to 255, such as when creating a table with tinyint, when the 256 will be error, this type of storage age, department number is more appropriate, such as:

Mysql> CREATE TABLE Stu (

ID int (3) unsigned auto_increment,

Name varchar (30),

-Age tinyint (2) Zerofill,

-primary key (ID)

) Engine=innodb default Charset=utf8;

Query OK, 0 rows affected (0.02 sec)

mysql> insert INTO Stu (name,age) VALUES ("Zhang San", 256); If the inserted data exceeds the tinyint range, an error will be made, and the decimal will be rounded

ERROR 1264 (22003): Out of Range value to column ' age ' at row 1

2> Decimal Type: decimal (p,s), numeric (p,s), two select one, for example

Mysql> CREATE TABLE Stu (

ID int (3) unsigned auto_increment,

Name varchar (30),

Money Decimal (4,2), the//money field is a decimal type, (4,2) represents a 4-digit number, where 2-bit integers and 2-bit decimals, which means up to 99.99

Primary KEY (ID)

) Engine=innodb default Charset=utf8;

mysql> insert INTO Stu (Name,money) VALUES ("John Doe", 200);

ERROR 1264 (22003): Out of Range value for column ' Money ' at row 1

Mysql> Show errors; viewing errors

3> String Type: char (), varchar (), text (), Longtext ()

char () is a fixed-length string (if 1 characters inode 5 0), the maximum is 255, the mobile number, the social security number can use the char type, for example

Mysql> CREATE TABLE Stu (

-ID int unsigned NOT NULL Auto_increment primary key,

-Name char (6)//If the char range exceeds 255 error, (6) represents 6 characters, exceeding 6 will error

) Engine=innodb default Charset=utf8;

varchar () is a variable-length string with a maximum of 65535, for example:

Mysql> CREATE TABLE Stu (

ID int unsigned NOT NULL Auto_increment primary key,

Name varchar (2)//with varchar type

) Engine=innodb default Charset=utf8;

mysql> insert into Stu (name) VALUES ("Zhang San");

Query OK, 1 row Affected (0.00 sec)

mysql> insert into Stu (name) VALUES ("Franzia Tianda");

ERROR 1406 (22001): Data too long for column ' name ' at row 1

Note: The above inserted data four words will also error, variable length string is not to say how long to change, such as the definition of char (3), a character A, the system will automatically fill two empty space, the result is a and two spaces filled with three characters, and the definition of varchar (3), after depositing a, The empty space is not automatically filled, that is, the actual deposit of a character, but the maximum number of bytes deposited is 3, the deposit 4 characters will be error. Attention is paid to two concepts.

Text () is also variable-length, up to 65535;longtext () maximum of 4G, stored articles can be used ...

Simply say the following enum parameter, which represents an enumeration, such as I want to save a gender, except the male is the female, for example:

Mysql> CREATE TABLE Stu (

ID int unsigned NOT NULL Auto_increment primary key,

Name varchar (5) is not NULL,

Gender enum ("Male", "female")

) Engine=innodb default Charset=utf8;

mysql> insert INTO Stu (Name,gender) VALUES ("Zhang San", "Medium"); The option defined within the enum () must be saved

ERROR 1265 (01000): Data Truncated for column ' Gender ' at row 1

mysql> insert INTO Stu (Name,gender) VALUES ("Zhang San", "male");

4> binary type: Blob, Longblob, such as I want to save a MP3 format file, you need to use this type, generally do not go to the database directly stored these things, the database is only the address of these files.

5> datetime type: Date, datetime,date can only be stored date, and datetime can be stored date and minute seconds

Mysql> CREATE TABLE Stu (

ID int unsigned NOT NULL Auto_increment primary key,

Name varchar (5) Not null,gender enum ("Male", "female"),

Birthday datetime//Date of birth with datetime type

) Engine=innodb default Charset=utf8;

mysql> insert INTO Stu (Name,gender,birthday) VALUES ("Zhang San", "Male", "2018-02-26 00:00:00");

What if the field type has registration time and we want to add it automatically? As follows:

Mysql> CREATE TABLE Stu (

ID int unsigned NOT NULL Auto_increment primary key,

Name varchar (5) is not NULL,

Gender enum ("Male", "female"),

Registered datetime default Now ()

) Engine=innodb default Charset=utf8;

mysql> insert INTO Stu (Name,gender) VALUES ("Zhang San", "male"); When you add data, you don't need to add registered field information anymore.

Note: When data is modified for a datetime type, the time does not change, and the timestamp type changes, for example:

Mysql> CREATE TABLE Stu (

ID int unsigned NOT NULL Auto_increment primary key,

Name varchar (5) is not NULL,

Gender enum ("Male", "female"),

Registered timestamp//timestamp type, when using the UPDATE statement to modify the data, the timestamp will change, if you do not want it to change, should be written registered timestamp default Current_tim Estamp ()

) Engine=innodb default Charset=utf8;

MySQL table operations and field data types