Simple use of MySQL (ii)

Connect to the above simple use of MySQL (i )

The field parameters are "(Field name 1 data type 1, field name 2 data type 2, ...). ) "In the form of construction.

About MySQL commonly used data types, a few of the more commonly used, want to check the more detailed information can be self-searching for online search.

————————————————————————————————————————————————————————————————————

Type description

Char 1~255 a fixed-length string of characters, whose length must be specified at creation time, otherwise MySQL is assumed to be char (1)

varchar variable length, up to 255 bytes, if you specify varchar (n) at creation time, you can store a variable-length string of 0~n characters

Text variable length 64K maximum

Longtext with text, maximum length 4GB (plain text, usually not to 4G)

ENUM accepts a string of a predefined collection of up to 64K strings

Set accepts 0 or more strings of a predefined set consisting of up to 64K strings

int[(M)] 4 bytes (integer value, signed value: 2147683648 to 2147683647 (-2^31 to 2^31-1) unsigned value: 0 to 4294967295 (0 to 2^32–1))

Date 4 bytes (dates are displayed in month-date format, range is 1000-01-01--9999-12-31)

Time 3 bytes (times in minutes and seconds, range is -838:59:59--838:59:59)

DateTime 8 Bytes (Displays the date and time, the range is 1000-01-01 00:00:00--9999-12-31 23:59:59)

BLOB stores a binary data type with a maximum length of 64KB.

Description: (1) The time range is so large, especially when you can take a negative value, which is MySQL in order to meet the two date time subtraction in this design.

(2) The BLOB binary data type is an important data type because all the images, videos, and audio files are stored in binary binary.

——————————————————————————————————————————————————————————————————————

After you create a new table, you can see the structure of the table roughly, with the command:> desc table name ; or >show columns from table name ;

The structure of the table includes the property name, the data type, whether it is a null value, whether the default value is set, and so on.

——————————————————————————————————————————————————————————————————————

You can also fine-tune the data in the table, using the name:> select field Name 1, field name 2, field name 3 .... From table name ;

And we actually in order to lazy often use the following several commands to view its content, in fact, they are the above command deformation or expansion , so understand the previous line of command, understand the following several commands is very easy.

such as this command:> select * from table name ;

See table N to M rows:> select * from table name order by field name limit n-1,m;

——————————————————————————————————————————————————————————————————————

To add data to the table, use the following command:>insert into table name (field name 1, field name 2, field name 3 ...) Values (the value offield name 1, the value of field name 2, the value of field Name 3 ...);

——————————————————————————————————————————————————————————————————————

Added, it is certainly necessary to delete the data in the table, using the command:>delete from table name where expression ;

Where the table name is anchored to the table, and the expression is positioned to the exact location to be deleted, the expression is in the form of an equation with respect to the field name and the data in it;

For example, in the Name= "Bob", age=12,high=180 are field names related.

——————————————————————————————————————————————————————————————————————

Deleting the destruction is often the easiest, so how do you modify the content? For example, if you are careless in typing and enter a mistake, you will need to modify it after you have found it:

Please use the command: >update table name set field name = "New value" where expression ;

The age field that modifies the name "Ason" is displayed, and the value 15 is modified to 13;

——————————————————————————————————————————————————————————————————————

Well! It seems that you can begin to learn to manipulate the contents of the database, do not worry, there are many things to learn, learning, to keep a thirsty heart is the most important.

Now we see three fields in the table above, namely name (name), age, height, perfect is always modified in order to perfect, if you need to add/delete an address (home address) field.

To add a field, use the command:>alter table name add field name data type other ; ( other settings including default initial values, etc.)

To delete a field, use the command:>alter table name drop field name ;

Similar to the structure of the following: Increase the index, add the main keyword index, delete the index etc, please try not to repeat each other;

Add index command:>alter table name Add index index name (field name 1, field name 2, field name 3 ...). );

Add primary key sub-index command:>alter table name Add primary key (field name);

Delete Index command:>alter table Name drop index name;

For specific use of the index, refer to someone else's article: MySQL index type summary and usage tips

——————————————————————————————————————————————————————————————————————

A simple name to modify the table, use the command: >rename table name to the new table name ;

——————————————————————————————————————————————————————————————————————

A table in which the content of the clutter is often devoid of aesthetics, and for large tables in large databases it is a needle in the haystack to find what is in it, so sorting the contents of the table is more of a multiplier to finding data.

please use the command:>select Field name 1, field Name 2... from table name order by field name 1, field name 2 ... ;

( The first field is the field content that needs to be displayed, and the second field is the field to sort, so don't confuse it!) ）

——————————————————————————————————————————————————————————————————————

All right! In the next section, you will merge and back up the table.

Simple use of MySQL (ii)