MySQL Basics (MySQL from getting started to mastering feedback)

17/7/91. The master-slave architecture (Client-server model) or the client-server (CLIENT-SERVER) structure is called C/s structure, is a network architecture, usually in this network architecture software is divided into clients and servers. 2. The master-slave architecture is applied to many different types of applications in different ways, such as when customers want to buy books on Dangdang, customers ' computers and Web browsers as clients, make up Dangdang's computers, databases and applications as servers. 3. (1) Using PRIMARY KEY constraints

• A primary key, also known as a master code, is a combination of one or more columns in a table. The PRIMARY KEY constraint (PRIMARY KEY constraint) requires that the data for the primary key column be unique and cannot be empty.
• Single Field primary key
• • • Specifying a primary key while defining a column
    • Specify a primary key after all columns have been defined
• Multi-field Primary key

PRIMARY KEY [Field 1, Field 2, .......... ...]

• Using FOREIGN KEY constraints
• • Foreign keys are used to establish a connection between data in two tables, which can be a column and multiple columns. A table can have one or more foreign keys, and a foreign key can be a null value
  • Syntax: [CONSTRAINT < foreign key name;] FOREIGN key field name 1 [Field 2, ...] Refetences < Main Table name > Primary key column 1 [primary key column 2 .... ]
• Use a non-null constraint NOT NULL;
• Use UNIQUE constraint unique
• • Syntax: contraint < constraint name > UNIQUE (< field name >)

6. Default default value 7. Set the table's property value to automatically increase auto_increment4. View Table Basic Structure Statement desctibe DESCRIBE table name; DESC table name; 5. View table Detail Structure Statement SHO W Create Tabel uses the show CREATE TABLE statement to view not only detailed statements when the table is created, but also the storage engine and character encoding; statements: SHOW CREATE table < table name \g>. 6.ALTER

• Modify Table Name
• • Statement: ALTER table < old table name > RENAME [to] < new table name >;
• Modify the data type of a field
• • Statement: ALTER table < table name > MODIFY < Field name > < data type >;
• Modify field names
• • Statement: ALTER table < table name > change < old field name > < new field name > < new data type >;
• Add Field
• • Statement: ALTER table < table name > ADD < new field name > < data type > [constraint] [first | After existing field name];
• Delete a field
• • Statement: ALTER table < table name > DROP < field name >;
• Modify the arrangement position of a field
• • Statement: ALTER table < table name > MODIFY < Field 1> < data type > first| After < field 2>;
• Change the storage engine for a table
• • Statement: ALTER TABLE < table name > engine=< changed storage engine name >;
• Delete a foreign KEY constraint for a table
• • Statement: ALTER table < table name > DROP FOREIGN key < foreign KEY constraint name >

7. Delete the table statement that is not associated: drop table [IF EXISTS] table 1, table 2, .... The argument [if EXISTS] is used to execute the SQL statement without the existence of the drop-down table before the deletion. 8. Delete the primary table associated with the other table first to disassociate the table from the table, and then delete the table. MySQL Data type introduction

Date and time data types

MySQL data type	Meaning
Date	3 bytes, date, format: 2014-09-18
Time	3 bytes, time, format: 08:42:30
Datetime	8 bytes, date time, format: 2014-09-18 08:42:30
Timestamp	4 bytes, automatically stores the time the record was modified
Year	1 bytes, year

Numeric data type

Integral type

MySQL data type	Meaning (Signed)
tinyint	1 bytes, range ( -128~127)
smallint	2 bytes, range ( -32768~32767)
Mediumint	3 bytes, range ( -8388608~8388607)
Int	4 bytes, range ( -2147483648~2147483647)
bigint	8 bytes, Range (18 +-9.22*10)

The above definitions are signed, of course, you can also add the unsigned keyword, defined as unsigned type, then the corresponding value range will be overturned, such as:

The value range of tinyint unsigned is 0~255.

Floating point Type

MySQL data type	Meaning
Float (M, D)	4 bytes, single-precision floating-point type, m total number, D-Decimal
Double (M, D)	8 bytes, double-precision floating-point, m total number, D-Decimal
Decimal (M, D)	Decimal is a floating-point number stored as a string

I built a table in MySQL with a column named float (5, 3), and the following tests were done:

1. Insert 123.45678, the last query results are 99.999;
2. Insert 123.456, the last query result is 99.999;
3. Insert 12.34567, the last query result is 12.346;

Therefore, in the use of floating-point type, but also to pay attention to the trap, to insert the actual results in the database will prevail.

String data type

MySQL data type	Meaning
CHAR (n)	Fixed length, up to 255 characters
VARCHAR (n)	Variable length, up to 65,535 characters
Tinytext	Variable length, up to 255 characters
Text	Variable length, up to 65,535 characters
Mediumtext	Variable length, up to 2 of 24 square-1 characters
Longtext	Variable length, up to 2 of 32 square-1 characters

The 1.char (n) and varchar (n) brackets mean the number of characters, not the number of bytes, so when Chinese is used (UTF8) means you can insert M Chinese, but it actually consumes m*3 bytes.
2. At the same time char and varchar The biggest difference is that char regardless of the actual value will occupy n characters space, and varchar will only occupy the actual character should occupy +1 of the space, and the actual space +1<=n.
3. After the n setting of char and varchar is exceeded, the string is truncated.
The upper limit of 4.char is 255 bytes, the upper bound of varchar is 65535 bytes, and the text is capped at 65535.
5.char will truncate the trailing spaces when stored, varchar and text will not.
6.varchar uses 1-3 bytes to store the length, text does not.

Other types

1.enum ("Member1″," Member2″, ... "Member65535″)
The enum data type defines an enumeration with a maximum of 65,535 distinct members. When a column of an enum is defined, the value of the column is limited to the value declared in the column definition. If the column declaration contains a null attribute, then NULL is considered to be a valid value and is the default value. If not NULL is declared, the first member of the list is the default value.

2.set ("member", "Member2″, ... The Member64″ set data type provides a way to specify 0 or more values in a predefined set of values, which includes a maximum of 64 members.  The selection of a value is limited to the value declared in the column definition. Summary: A good book is worth a look .... Kay

MySQL Basics (MySQL from getting started to mastering feedback)