PHP Learning DAY10---Database Basics 2

Today is our knowledge of data related to the next day.

First, the field type of the database　1. Overview

In MySQL, the main types of data are three kinds:

Digital, character type, time type

The primary function of the data type is to set the type of the field when creating a data table.

　　　　　　　Digital type:

1. Integer type: tinyint,smallint,mediumint,int,bigint

2. Float type: floating point float,double

Fixed-point decimal

　　　　　　　　Character type:

Set,enum,blob,text,varchar,char

　　　　　　　　Time Type:

Year, Timestam,time,date,datetime

2. Numerical type

　　　　field settings for integer types

Mainly include: Tinyint,smallint,mediumint,bigint

The space occupied by the integer type:

int: takes four bytes, or 32 bits

Tinyint: Takes one byte, or 8 bits, to store up to 256 digits

BigInt: 8 bytes, 64 bit

　　　　　　

　　　　　1. set form for integer type fields:

　　　　　Type name [M][unsigned][zerofill]

Description

　　　　　　　　1.M indicates the display length of the integer, which is the output, if M is 6, it may be displayed as 000123.

2.unsigned is used to set the integer number unsigned number, in fact, there is no negative.

3.zerofill is used to set whether to fill ' 0 ' to the left of a number, at which point it needs to be used in conjunction with M

4. If Zerofill is set, the unsigned adornment is also indicated by the automatic.

2. Decimal type

Float,double,decimal

float: single-precision floating-point type with a precision of about 6--7-bit valid digits

Double: double-precision floating-point type with a precision of approximately 15 significant digits .

Decimal : fixed-point decimal type, integer part can have a maximum of 65 bits, decimals can have up to 30 bits, the general format is: decimal (total number of digits, number of decimal places)

　　　　　　　　

　　　　　　　　　

　　　　

　　　　　　　　　　

　　3. String type

The string in MySQL should be caused by the use of "single quotes".

The main string types are:char,varchar,enum,set,text,binary,varbinary,blob

　　　　　varchar Type: variable length string , using is we must set the length, the maximum length is 65,535, the character encoding is different, there may be less, here the storage limit is actually the source of a table row of data storage maximum capacity limit: 65535.

　　　　　char Type: Fixed length string , when used usually need to set its length, if not set, the default is 1, the maximum theoretical length of 255, fixed length string is suitable for storing data can be clearly fixed pin length characters, such as mobile phone number, China postcode, etc., the actual storage, If it is less than the set length, it will fill in the blanks.

　　　　　　　　

　　　　　enum Type: Single option string data type. The comparison is suitable for storing a single option in the form interface, which needs to be given a fixed number of options at the time of setting, then storing a value for the value:

　　　　　　　　Enum ("Option 1", "Option 2", "option 3" ...) ）

The actual internal counterpart is the numeric value: 1,2,3,4,5,6 ... up to 65535 items;

The written form can also use the string itself, or the corresponding array;

　　　　　　set Type: Multi-option string data type . It is suitable for storing multiple options for the form interface, with the same usage as the enum:

　　　　　　　　　　Set ("Option 1", "Option 2", "option 3" ...) ）

The corresponding numeric subscripts for these string selections are: 1,2,4,8,16 .... up to 64 options.

The write form can be separated by commas with the option string, or by the corresponding number.

　　　　　　　　

　　　　　　　　　　

　　　　　　　　text type: a long text character type in which the data stored does not account for the data capacity limits in the table. can store 65,535 characters, others of the same type are: Smalltext,tinytext,longtext.

　4. Time and Date type

　　　　The time types are set up in the following categories:

　　　　　DateTime type: Time Date type

Date: Day type

Time: Type

Year: Years type

Timestamp: timestamp, equivalent now ()

A common time type is a single quotation mark that is used in the same way as a string, given our own data.

　　　　　　　　

Second, the table definition statement　　1. Create a table

The syntax for creating a table is:

　　　　　　Create Table " If not exists " Table name (Field list , index or constraint list ) " Table Options List ";

Or:

　　　　　　Create Table " If not exists " table name ( field 1, Field 2, ... .) ", index 1, index 2, ...., constraint 1, constraint 2, ... . " ) " table option 1, table option 2, .... "

The format of the field:

Field Name Type field Property 1 Field Property 2

Field type We've already talked about it in the previous section, and today we'll focus on the properties of the field;

Auto_increment: Used only for integer types, so that the value of the field automatically grows, usually the first field of a table, and is when the master key primary key;

Primary key: Used to set the field as the primary key, at which point the value of the field can uniquely determine a row of data;

Unique key: Sets the field to be unique and cannot be duplicated

Not NULL: Used to set the field cannot be empty, if not set by default can be empty;

Default: Used to set the value of the field;

Comment ' field description ';

　　　　　　

　　　　　　

Index

What is an index: An index is a hidden data table that is automatically maintained inside the system, and its function is to greatly speed up the search for data.

The so-called indexing, in fact, is the formulation of a table of some or some of the fields as "Index data field" on it, in the form of:

Normal index: key (field name )

It's just an index, which can speed up the search.

Unique index: unique key (field name)

is an index, and you can also set the value of its field cannot be duplicated;

Primary KEY index: primary key (field name)

is an index, and it also has the effect of distinguishing any row of data in the table, and the primary key cannot be empty.

Full-text index: fulltext (field name )

FOREIGN key index: foreign (field name) references other tables (corresponding to field names in other tables)

Method of index creation:

　　　　FOREIGN KEY index:

Form: Foreign key (field name) References other table (field)

Foreign key definition: refers to the setting of a table of a field, its data value, must be another table in a field;

·

　　　　　Constraints:

　　　　Definition of constraints: a provision that requires data to meet what conditions

The following are the main types of constraints:

　　　　　　　PRIMARY KEY constraint: Primary key (field name)

So that the value of the field can be used to uniquely determine a row of data, is actually the primary key meaning;

　　　　　　　Unique constraint: Unique key (field name)

So that the value of the field is unique, can be better differentiated;

　　　　　　　FOREIGN KEY constraint: foreign key (field name) References table name (field name)

The value of the Set field, which must be in the field of the corresponding table in which it is defined;

　　　　　　　Non-empty constraint: NOT NULL

The Notnull property that is written when a field is set

　　　　　　　Default constraint: Defaults value

is to set that property on the field.

　　　　　　　　Check constraint: Check (some kind of judgment statement)

In fact, the primary KEY constraint, the unique constraint, the foreign KEY constraint, and the index no difference is the same thing at different angles of the argument.

　　　　List of options

The list of options is the overall setting for the table when you create a table:

CharSet = the character encoding to use;

Engine = the storage engine to be used;

Auto_increment= sets the initial value of the self-growing field of the current table, which defaults to 1;

Comment = ' Descriptive text '

Description

1, the character encoding is set to be different from the database settings. If you do, you don't need to set it up: because it automatically uses database-level settings;

2,engine(storage engine) at the code level, is a noun:InnoDB, MyIsam, BDB, archive, memory. The default is InnoDB.

What is called a storage engine?

The storage engine is the "mechanism" for storing data to the hard disk . In fact, there are several mechanisms (as mentioned in the name above); different storage engines, in fact, the main thing is to design a storage mechanism from 2 large layers:

1, speed as fast as possible;

2, as many functions as possible;

choosing a different storage engine is a "tradeoff" between the performance and functionality described above .

Broadly as follows:

　　　　　　　　　　

　　　　　　　　　　

2. Modify the table

　　　A few notes:

Modify table refers to the format of the modified table;

Create a table can do things to modify the table can almost do ———— comparison is not recommended to modify the table

You can manipulate a field or work with an index

Table options, which are usually modified even if you don't have any table options, they are the default values.

Modifying the statement of a table more often than not, here are a few more common:

　　　　　　　　

To Add a field: ALTER TABLE name add [column] new field name field type [ field attribute list ];

to modify a field (and rename it): ALTER TABLE name change [column] old field name new field name new field type [ new field Property list ] ;

To delete a field: ALTER TABLE name drop [column] field name;

To add a normal index : ALTER TABLE name add key [ index name ] ( field name 1[, field name 2,.. .]) ;

Add a unique index ( constraint ) : ALTER TABLE name add unique key ( field name 1[, field name 2,...]) ;

Add primary key index ( constraint ) : ALTER TABLE name Add primary key ( field name 1[, field name 2,...]) ;

　　　　　　　　

　3. Delete a table

　　　drop table if exists name;

　4. Other related statements

　　　　Show all tables in the current database: show tables;

Show the structure of a table: Desc table name; or: describe table name;

Show creation statement for a table: Show create table table name;

Rename table: Rename table name to new table name;

Copy table structure from existing table: CREATE TABLE [if not EXISTS] new table name like original table name;

Third, the definition of the view　1. What is a view

is a SELECT statement, we give one name (the view name), and later, it is convenient to use (execute) The SELECT statement: Use this view to

2. Creation of views

Create View name (Field 1, Field 2, Field 3 ...). ) "AS Select

Distance: Create View V as

Select Id,fl,name,age,email from table 1 where id>7 and id<100

　　　　　　　　

Basically you can use it as a table:

　　　　　　　　

　　　3. Delete a view

　　　　

　　　　　Drop View "if exists" view name;

　　

PHP Learning DAY10---database Basics 2