2016-2-14 notes

Data type: shape, float, character, time

1, Number:

The integer types are as follows:

tinyint 1B

1b=8bit 1 bytes = 8 bits for tinyint type Max storage 255, if inserting in database

255, display 127 in the database, because the number has positive and negative numbers, will use 1b to store positive and negative numbers

Only 7 bytes to hold the data, so it's 127.

Unsigned

CREATE TABLE cc (age tinyint unsigned); ----Create a table cc and specify type tinyint, positive negative numbers are not stored

When you use the Zerofill property, you typically automatically unsigned the property

smallint occupies 2B

Range of data stored: (+-) 0-2^15

int occupies 4 b

BigInt occupies 8B

Integer use rules, can be used small, not big, the advantages: small footprint, fast query speed

Floating-point data is as follows

Float (x.y) occupies 4b,x is the total length of the data (including integers and decimals) y is the length after the specified decimal point

Eg:float (3,1)----11.2 This data can satisfy this type, indicating that the data must be 3 bits long, after the decimal point

The length is 1.

Float is sometimes not precise enough to be rounded up.

Decimal (x, y) type occupies x+2b bytes

Similar to float usage, just more precise

Double takes 8 bytes. High occupancy space

2, character type

char (x)-------a fixed-length string type, the longest is the X-value inside, where X is

is not the meaning of the byte, but the number of characters, such as the input "Alexander the Great" for 6 characters,

If X is 10, the part with less than 10 characters is padded with spaces, and if it exceeds 10 characters, only

Take 10, more than the partial is discarded.

Char type characteristics: because it is fixed length, so it will be quite a waste of space, because it is fixed length, the operation of the

, there is no need to calculate the length of the table separately, so the operation will be quick.

Recommendation: If your data is small and your data engine is MyISAM, it is recommended to use Char

If it is a innodb engine, it is recommended to use varchar

varchar (x)-----A variable-length string type with a maximum of x characters. such as varchar (10), if the value entered is less than 10,

So how much to use, if greater than 10, then discard the excess part

VARCHAR Features: Saves space, slows down, or is displayed if there are spaces in the inserted string

Enum-----If you have a limited number of data inserted, consider using an enum or set type, such as gender, for which you would like to use enum

Type.

3. Date format

Year Time range: 1901-2155

CREATE TABLE B1 (Column1 year);-----defines the column type as year type

Normally, we insert the value range of the data year

00-69 means 2000-2069.

70-99 means 1970-1999.

Date format 3B range 1000-01-01 to 9999-12-31

CREATE TABLE B2 (column1 date),----definition column as Date type data

DateTime 8B range, accurate to seconds

CREATE TABLE B3 (Column1 datetime);

Operation of the table

To create a basic command format for a table

CREATE TABLE TableName (

Column1 int,

Column2 varchar (10)

);

CREATE table newtablename like oldtablename;----creates a new table that is the same as the old table.

Copy the structure of the old table, do not copy the data, but some properties can not be copied

Common Properties for fields

Primary key

Auto_increment----Self-growth by default from 1 to 1 values, if you set a value, you can specify the value from which to start increasing

AUTO_INCREMENT=100----specified self-growth value starting from 100

Not NULL----The specified value cannot be null, it is best to set the field to not NULL when creating the table

Default----defaults, setting a default value for the field

Unique-----Uniqueness Constraint, duplicate values are not tolerated if a column has a unique attribute

Properties of the table

Engine=myisam----Change the engine of a table

Charset=utf8----Modifying the encoding

CREATE TABLE A1 (id int) Engine=myisam Charset=utf8;

Delete a table

drop table A1; ----Delete A1 table

Temp table (Temporary)

For example, write a more complex SQL statement, the result can be temporarily placed in a table, and then to reference

Change tables, often need to add, delete, or change the properties of a field for a table

Alter

ALTER TABLE table name action (add|drop|modify|change) modified value;

For example

ALTER TABLE A1 add age tinyint unsigned; ----Adds a column of age fields to the A1 table,

This column is added at the end of the column, and if you want to assign to the relevant location, you can use the

(first|after column)

such as: ALTER TABLE A1 add age1 tinyint unsigned first; ----Add the Age1 column to the first column of the table

ALTER TABLE A1 add age2 tinyint unsigned after name; ---Add the age2 field column to the Name field after the

Delete Column (drop)

ALTER TABLE A1 DROP column name------delete a column

Modified (Modify|change)

ALTER TABLE A1 Modify column list property value; -----Modifying the property values of a column

ALTER TABLE A1 Modify Column Name property value first;----Modify tables to column one

Show CREATE TABLE tablename\g; ------View the properties of a table command

Show CREATE Database databasename\g; -----View the properties of a database

Alter to modify the properties of a table

ALTER TABLE TableName CHARSET=UTF8;----Modify the table's encoding properties with alter

ALTER DATABASE DatabaseName Charset=utf8; -----Modify the properties of a database with alter

Repair table------The command used to fix the table, and if the table is faulty, you can use this command to

Check table tablename; -----command to check if the table is normal

Repair table TableName quick extended;----Repair table with this command if the repair is unsuccessful

Repair table TableName use_frm; ----Repairing table Data

This article is from the "Cary_qin blog" blog, make sure to keep this source http://xpqinqun.blog.51cto.com/2136/1741960

2016-2-14 notes