MySQL table operations

I. Introduction of the Table

Table is equivalent to a file, a record in the table is equivalent to a row of the file, but a record in the table has a corresponding title, called the table field

Second, create a table

1. Basic grammar

Syntax:CREATE TABLE table name (field name 1 type [(width) constraint], field name 2 Type [(width) constraint], field name 3 Type [(width) constraint]);
Explain
Type: What data type must be used to pass the value of the restriction field
Constraint: A constraint is an additional restriction that is added outside of a type
Note: 1. In the same table, the field names cannot be the same as 2. Width and Constraints optional 3. Field names and types are required
4. The last field in the table cannot be added with a comma ', '

2. Data table operation

Select the database use DB1 before viewing the table; View all tables under the current library show tables; View a table's information show create tables  t1; View the structure of the table desc T1; Creating tables CREATE table T1 (field name data type [Completeness constraints]); Modify table name alter tables t1 rename T2; drop table T1;

3. View table structure

Mysql> desc T2

Third, the data type

1. Type of shaping (generally used int)

1, Function: ID number, various numbers, age, rank2, Category: tinyint----tinyint[(m)] [unsigned] [Zerofill]
Signed:-128 ~ 127
Unsigned:0 ~ 255
int--------int[(M)][unsigned][zerofill]
Signed:-2147483648 ~ 2147483647
Unsigned: 0 ~ 4294967295
bigint-----[(M)][unsigned][zerofill]
Signed:-9223372036854775808 ~ 9223372036854775807
Unsigned:0 ~ 18446744073709551615 3, test: The default integer is a signed CREATE table T1 (x tinyint); insert into T1 values (128), (-129CREATE TABLE t2 (x tinyint unsigned); insert into T2 values (-1), (256CREATE table t3 (x int unsigned);#4294967295INSERT into T3 values (4294967296), CREATE table t4 (x int (12) unsigned); INSERT into T4 values (4294967296123);4, emphasize: For the integer type, the width of the data type is not the storage limit, but the display limit, so in the creation of the expression, if the field is of an integral type, there is no need to specify the display width, the default display width, sufficient to display the entire original stored data  #when displayed, not enough 8 bits are filled with 0, if more than 8 bits are displayed normallyCREATE table T5 (x int (8) unsigned zerofill); INSERT into T5 values (4294967296123) insert into T5 values (1);

2, float type (generally used float)

function: storage height, weight, salary Category: Float (*****) Double (**) Decimal (**) Test:#same Point#1, for the three, can store 30 decimal places,#different points:1, the accuracy of the sort from low to High: Float,double,decimal2 , float and double type can hold the whole number more than decimal CREATE table T9 (x float (255,30); CREATE table T10 (x double (255,30); CREATE table T11 (x decimal (65,30) ; insert into T9 values (1.111111111111111111111111111111) insert into T10 values (1.111111111111111111111111111111) insert into T11 values (1.111111111111111111111111111111); MySQL> select * fromT9;+----------------------------------+| X |+----------------------------------+| 1.111111164093017600000000000000 |+----------------------------------+1 rowinchSet (0.00sec) MySQL> select * fromT10;+----------------------------------+| X |+----------------------------------+| 1.111111111111111200000000000000 |+----------------------------------+1 rowinchSet (0.00sec) MySQL> select * fromT11;+----------------------------------+| X |+----------------------------------+| 1.111111111111111111111111111111 |+----------------------------------+1 rowinchSet (0.00 sec)

3. Character type

1, Function: Name, address, description class information2, Category: Char fixed length varchar variable length char#Cons: Wasted space#Advantage: Fast access speedvarchar#Cons: Access speed is slow#Advantages: Space Saving
3, test: The character's width limit is the number of characters create table T12 (x char (4));#More than 4 characters will be error, not enough 4 characters to fill in 4 characters with a spaceCREATE table t13 (y varchar (4));#more than 4 characters is an error, not enough 4 characters so the characters have a few to save a fewINSERT into T12 values ('Hello') insert into t13 values ('Hello') insert into T12 values ('a');#' A 'INSERT into t13 values ('a');#' A 'SetGlobalSql_mode="Strict_trans_tables,pad_char_to_full_length"; Select Char_length (x) fromT12;#4Select Char_length (y) fromT13;#1#Note:for the char type, MySQL stores the data in a blank space on the hard disk while it is stored, but automatically removes the trailing space when the result is read, because the trailing space is useless in the following scenario MySQL> select * fromT14 where name="LXX";#The Name field is explicitly equal to a value after which padding spaces are not usedMySQL> select * fromT14 where name like"LXX";#The name field blurs to match a value after which padding spaces are useful

4. Date type

1, function: Time-related2, Category: Date:1999-01-27Time :11:11:11datetime:1999-01-27 11:11:11Year :19993, test CREATE TABLE student (id int, name char (16), Born_year year, birth date, Class_time time, Reg_time datetime); INSERT into student values (1,'Egon',' -','2000-01-27','08:30:00','2013-11-11 11:11:11');

5. Enumerations and collection types

function and classification: enum enum, multiple Select a set set, multi-select multiple test CREATE TABLE teacher (id int, name char (16), sex enum ('male','female','others'), Hobbies set ('Play','Read','Music','Piao'));

MySQL table operations