mysql-table Operation

What is a table

A table is a structured file that can be used to store data of a particular type. A record in the table has a corresponding caption, which is called a table field.

　　

Two Create a table

1 CREATE Table table name (2 field name 1 type [(width) constraint],3 field name 2 Type [(width) constraint],4  Field Name 3 Type [(width) constraint]5 ) engine=innodb DEFAULT CHARSET UTF8;

CREATE TABLE student (ID int notNULL auto_increment primary key, name varchar (250) notNULL, age int notnull, sex enum ('male','female') notNull default'male', Salary double (10,2) notnull) engine=innodb default charset=Utf8;ps: notnull: Indicates that this column cannot be empty auto_increment: represents self-growth, each growth by default+1Note: Self-growth can only be added on primary key or unique index field primary key: Indicates primary key (unique and not empty) engine=InnoDB: Indicates that the storage engine for the current table is default charset UTF8: Sets the set of defaults for the table

Create a table

primary key, a special unique index that does not allow null values, and if the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique.            CREATE TABLE tb1 (                not  null auto_increment primary KEY,                num int null            )            or            CREATE TABLE tb1 (                not  null,                not  null,                primary KEY (Nid,num)            )

PRIMARY Key

self-increment, if you set the self-increment column for a column, you do not have to set this column when inserting data, and the default will be self-increment (only one self-increment in the table) CREATE TABLE tb1 (nid int notNULL auto_increment PRIMARY KEY, num int null) or CREATE TABLE tb1 ( Nid int notnull auto_increment, num int null, index (NID)) Note:1, for self-increment columns, must be an index (with a primary key). 2, for self-increment, you can set the step and start values show session variables like'auto_inc%'; Set Session Auto_increment_increment=2; Set Session Auto_increment_offset=10; ShowGlobalVariables like'auto_inc%'; SetGlobalauto_increment_increment=2; SetGlobalauto_increment_offset=10;

Self-incrementThree. Querying table data

1 #Querying table Data2Select field (multiple","Interval fromtable name;3Example: Select Name,sex fromstudent;4OR: SELECT * fromstudent;5    6 #View Table Structure7 desc table name;8 Example: DESC student;9  Ten #View CREATE TABLE information OneShow create table student;

Four. Modify the table structure

1 #Add a table field2 ALTER TABLE name add field name type constraint;3Example: ALTER TABLE student add age int notNULL default 0 after name;4 Ps:after name indicates that the field is added after the Name field , age.5   6 #Modify a table field7Mode one: ALTER TABLE student modify field varchar (100) null;8Mode two: ALTER TABLE student change old field new field int notnull default 0;9 PS: The difference between the two:Ten change changes the field name and attributes One Modify can only change the properties of a field A      - #To delete a table field: - ALTER TABLE student drop field name; the    - #Update table name: -Rename table name to new table name;

#ALTER TABLE student Add primary key (field," multiple ", " interval "  # not  

Update primary KEY operation

#ALTER TABLE from table add CONSTRAINT fk_test foreign key from table (field) REFERENCES Main Table (field); #  ALTER TABLE Table drop FOREIGN key foreign key name; PS: If you want to modify the foreign key settings, then you can only delete and add

FOREIGN Key Update operation

#ALTER TABLE table ALTER FIELD set default; # Delete defaults:ALTER TABLE table ALTER field drop default;

Default value Update actionFive. Delete a table

1 # Delete a table 2 drop table name; 3  4 # Clear Table 5 TRUNCATE table name;

Six. Copying a table

1 # Copy only the table structure and the data in the table 2 CREATE TABLE tb2 SELECT * from tb1; 3 PS: primary key auto-increment/index/trigger/ foreign key will not be copied 4    5# copy table structure only 6 CREATE table tb2 like tb1; 7 PS: Data/Trigger/foreign key is not copied

Seven Database storage engine

Detailed reference: http://www.cnblogs.com/wangfengming/p/7930333.html

Eight. Data type

MySQL supports multiple types and can be broadly divided into four categories: numeric, String type, date/time, and other types.

Binary Type: bit[(M)] bits (101001), m represents the length of the bits (1-64), the default M=1integer type: tinyint[(m)] [unsigned] [zerofill] small integer, data type used to hold some range of integer numeric ranges: Signed:-128 ~ 127. Unsigned:255Special: No boolean value in MySQL, use tinyint (1) constructs. int[(M)][unsigned][zerofill] integer, data type is used to hold some range of integer numeric ranges: Signed:-2147483648 ~ 2147483647Unsigned :4294967295bigint[(M)][unsigned][zerofill] Large integer, data type used to hold some range of integer numeric ranges: Signed:-9223372036854775808 ~ 9223372036854775807Unsigned :18446744073709551615function: Storage age, rank, id, various numbers, etc. note: When specifying a width for this type, only the display width of the query result is specified, regardless of the storage range, so we use the default to be able to have signed and unsigned maximum number of the display width is 10, and for the smallest signed The value requires 11 bits to display completely, so the default display width of int type is 11 is very reasonable decimal type: decimal[(M[,d])] [unsigned] [Zerofill] Accurate small value, M is the total number of digits (minus sign), D is the decimal point Number.        The M maximum value is 65,d maximum of 30.    Special: The reason why this type of decaimal is needed for accurate numerical calculations is that it stores the exact value internally as a string.        float[(m,d)] [UNSIGNED] [Zerofill] single-precision floating-point number (not accurate decimal value), M is the sum of numbers, D is the number after the decimal point. Signed: (-3.402823466E+38 to-1.175494351e-38), 0, (1.175494351E-38 to 3.402823466E+38) Unsigned: 0, (1.175 494 351 e-38,3.402 823 466 e+38)        The larger the number, the less accurate * * *double[(m,d)] [UNSIGNED] [Zerofill] double-precision floating-point number (not accurate decimal value), M is the total number of numbers, D is the number of decimal places. Signed: (-1.797 693 134 862 315 7 e+308,-2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308) Unsigned: 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308)                The larger the number, the less accurate * * *function: Store salary, height, weight, physique parameters, etc. character type: char (m) the char data type is used to represent fixed-length strings and can contain up to 255 characters.                where m represents the length of the string.  PS: Even if the data is less than m length, it will also occupy the m length, but in the query, the detected results will automatically delete the trailing space features: fixed length, simple rough, wasted space, access speed varchar (m) varchar        The data type is used for variable-length strings that can contain up to 65,535 characters (theoretically, but in fact, after exceeding 21845 length, MySQL automatically helps you convert the data type to text type).        where m represents the maximum length of a string that is allowed to be saved by the data type, as long as a string that is less than the maximum value can be saved in that data type. The Ps:varchar type stores the real content of the data, for example: if'AB', the trailing spaces will be saved. Emphasize: varchar type will add 1 before real data-2bytes prefix that is used to represent the number of bytes bytes of real data (1-2Bytes Maximum represents 65,535 digits, exactly as MySQL has a maximum byte limit on row, which is sufficient to use if the actual dataThe <255bytes requires a 1Bytes prefix (1bytes=8bit 2**8 The maximum representation of the number is 255) if the real dataThe >255bytes requires a 2Bytes prefix (2bytes=16bit 2**16 maximum representation of 65535) features: variable length, precision, space saving, Slow access SQL optimization: When you create a table, the fixed-length type goes forward, the longer the back, like sex. For example, address or description information PS: Although varchar is more flexible to use, the char data type can be processed faster, and sometimes even beyond the varchar processing speed, from the overall system performance perspective%. Therefore, users should consider all aspects of the database design, in order to achieve the best balance of the text text data type is used to save the variable length of large strings, can be up to 65535 (2**16? 1) characters. Enum type (Understanding): Enum An enum column can has a maximum of65,535 distinct elements. (The practical limit isLess than 3000.) Example: CREATE TABLE User (name VARCHAR (40), Sex ENUM ('male','female','Unknown')            ); INSERT into user (name, sex) VALUES ('Person 1','male'), ('Person 2','female'),                                                ('Person 3','Unknown'),('Person 4','Shemale');        PS: Human 4 will be inserted successfully?? Collection type (understanding): Set a set column can have A maximum of64distinct. Example: CREATE TABLE myset (Col SET ('a','b','C','D')); INSERT into MySet (col) VALUES ('a,d'), ('D,a'), ('A,d,a'), ('a,d,d'), ('d,a,d'); Date/Time Type: Date datetime value YYYY-MM-DD (1000-01-01/9999-12-31) time value or Duration HH:MM:SS ('-838:59:59'/'838:59:59') year value YYYY (1901/2155) datetime Mixed date and time value YYYY-mm-dd HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59) TIMESTAMP timestamp YYYYMMDD HHMMSS (1970-01-01 00:00:00/2037Time of year) role: Store user registration times, article release time, employee entry time, birth time, expiration time, etc.

Data Type

Detailed reference:

• Http://www.runoob.com/mysql/mysql-data-types.html
• Http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html

mysql-table Operation