Home>

Continue aboveSimple tutorial on using mysql under linux system (1)

Field parameters are constructed in the form "(field name 1 data type 1, field name 2 data type 2, ...)".

About common data types in mysql,Here are some of the more commonly used ones,For more detailed information, you can search online.

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

Type Description

char 1 ~ 255 characters fixed length string,Its length must be specified at creation time,Otherwise mysql is assumed to be char (1)

varchar variable length,No more than 255 bytes. If you specify varchar (n) when creating, it can store variable length strings of 0 ~ n characters

text Variable-length text with a maximum length of 64k

longtext Same as text, the maximum length is 4gb (plain text, generally not more than 4g)

enum accept a string of a predefined set of up to 64k strings

set accepts zero or more strings of a predefined set 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 (display the date in the format of year, month, day,The range is 1000-01-01-9999-12-31)

time 3 bytes (display time in hours, minutes and seconds,The range is -838:59:59-838:59:59)

datetime 8 bytes (display date and time,The range is 1000-01-01 00:00:00——9999-12-31 23:59:59)

blob stores binary data types,The maximum length is 64kb.

Note:(1) The time range has such a large range.In particular, time can take a negative value,This is how mysql was designed to meet the subtraction of two datetimes.

(2) The blob binary data type is a more important data type.Because of all the pictures,Video and audio files are stored in binary.

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

After creating a new table,You can roughly view the structure of the table,The command is:>Desc table name;or>show columns from table name;

The structure of the table includes attribute names,type of data,Whether it is null,Whether to set the default value and other information.

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

You can also view the data in the table in detail.The names used are:>select field name 1, field name 2, field name 3 ..... from table name;

And we actually use the following commands to view its contents in order to be lazy,In fact, they are all deformations or extensions of the above commands.So I understood the command on the previous line,It is easy to understand the following commands.

Such as this command:>select * fromTable name;

If you look at rows n to m in the table:>select * fromTable nameorder by field name limit n-1, m;

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

Add insert data to the table,The command used is:>insert into the table name (field name 1, field name 2, field name 3 ...) values ​​(value of field name 1, value of field name 2, value of field name 3. .....);

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

Increased,Then it must be necessary to delete the data in the table,Use the command:>delete fromTable namewhereexpression;

Which table the table name locates,While the expression is targeted to the specific location to be deleted,The form of the expression is an equation related to the field name and the data in it;

For example, name="bob", age=12, high=180 in the figure below are related to the field name.

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

Destroying is often the easiest,So how to modify the content?For example, you are careless and make mistakes when typing,After discovering it, you have to modify it:

Please use the command:>Update table name set field name="new value" where expression

The following figure shows the modification of the age field named "ason";the value 15 is changed to 13;

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

Yep! It looks like you can slowly start learning to manipulate the contents of the database,Do n’t worry, there is still much to learn.Life-long learning,It is most important to maintain a hungry heart.

Now we see that there are three fields in the table above,They are name (name), age (age), high (height), perfection is always constantly modified,Only to become perfect,Suppose you need to add/delete an address field.

To add fields, use the command:>Alter table table name add field name data type other;(Other settings include default initial values, etc.)

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

A structure similar to this:adding indexes,Add the main keyword index,Delete the index etc, please try not to repeat them;

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

Add main key sub-index command:>Alter table table name add primary key (field name);

Delete index command:>Alter table table name drop index index name;

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

A simple modification table name,Please use the command:>rename table original table name to new table name;

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

A table in which the content is disorderly and often lacks beauty,For a large table in a large database, what i want to find in it is simply a haystack,So sorting the contents of the table is more effective at 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 content of the field that needs to be displayed.And the second field is the field to be sorted,Please do n’t be confused! )

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

Alright! In the next section,Merge and backup of the following table will be said.

  • Previous Summary of common methods of CI framework
  • Next Problem with normal distribution sort of javascript array