1000 Line MySQL study notes (iii)

/* Column property (column constraint)/*----------
1. Primary key

-a field that uniquely identifies the record, which can be used as the primary key.
-a table can have only one primary key.
-The primary key is unique.
-When declaring a field, it is identified with Primary key . You can also declare after the field list

Example:CREATE TABLE tab (ID int, stu varchar(Ten), primary key (ID));
-The value of the primary key field cannot be null.
-The primary key can be composed of several fields together. The method that you want to declare after the field list is now required.

Example: CREATE TABLE tab (ID int, stu varchar(Ten), age int, primary key (Stu, age));

2. unique unique index (UNIQUE constraint)

So that the value of a field cannot be duplicated.

3. Null constraint

Null is not a data type and is a property of a column.

Indicates whether the current column can be null, indicating nothing.
null, allowed to be empty. Default.
Not null, it is not allowed to be empty.
insert into tab values (null, ' Val ');
--This indicates that the value of the first field is set to null, depending on whether the field is allowed to be null

4. Default Defaults property

The default value for the current field.
insert into tab values (default, ' Val '); --This means that the default value is enforced.

CREATE Table tab (add_time timestamp default current_timestamp );
--Indicates that the timestamp of the current time is set to the default value.

current_date, current_time

5. Auto_increment Automatic growth constraint

Auto-Grow must be index (primary key or unique)

Only one field can exist for autogrow.

The default is 1 to start auto-grow. Can be set through the Table property Auto_increment = x, or ALTER TABLE TBL auto_increment = x;

6. Comment Notes

Example:CREATE TABLE tab (ID int ) Comment ' comment content ';

7. FOREIGN key foreign KEY constraint

Used to limit the integrity of the primary table and the data from the table.
ALTER TABLE t1 add constraint ' T1_T2_FK ' foreign key (t1_id) references T2 (ID);
--Associates the T1_ID foreign key of the table T1 to the ID field of the table T2.

--Each foreign key has a name that can be specified by constraint

A table that has a foreign key, called a table (a child table), a foreign key, is called the primary table (the parent table).

Role: Maintain data consistency, integrity, the primary purpose is to control the data stored in the Foreign key table (from the table). in MySQL, foreign key constraints can be used on the InnoDB engine: Syntax:
FOREIGN Key (Foreign key field) references primary table name (associated field) [Action when primary table record is deleted] [action when primary table record is updated] This requires that an existing value from the table's foreign KEY constraint be constrained to be the primary table. Foreign keys can be set to NULL if they are not associated. If the foreign key column is not null.

You can not specify the action of the primary table record when the change or update occurs, and the operation of the primary table is rejected at this time.

If you specify on update or on delete: When deleting or updating, there are several actions to choose from:
1. Cascade, cascade operation. The primary table data is updated (the primary key value is updated), and the table is updated (foreign key value updates). The primary table record is deleted and the related records from the table are also deleted.
2. Set null, setting to NULL. The primary table data is updated (the primary key value is updated), and the foreign key from the table is set to NULL. The primary table record is deleted, and the foreign key from the table-related record is set to NULL. Note, however, that the foreign key column is required and there is no not NULL property constraint.
3. restrict, reject parent table deletion and update.

Note that foreign keys are only supported by the INNODB storage engine. Other engines are not supported.

/* Build Table Specification */--------------------Normal Format, NF

-Save one Entity information per table
-each with an ID field as the primary key
-ID PRIMARY key + atomic table

--1NF, first paradigm
Fields can no longer be divided to meet the first paradigm.

--2NF, second paradigm
Under the premise of satisfying the first paradigm, partial dependence cannot occur. Elimination of partial dependencies can be avoided by eliminating compliance with the primary key. Add single-column keywords.

--3NF, third paradigm
In the premise of satisfying the second paradigm, transitive dependence cannot occur. A field depends on the primary key, and there are other fields that depend on the field. This is the transitive dependency. The data for an entity information is implemented within a table.

/* SELECT */------------------

Select [All|distinct] select_expr from, where , group by [aggregate functions] C9>order by, limit

A. select_expr

--all fields can be represented by *.

Select * from TB;

--You can use an expression (calculation formula, function call, field is also an expression)

Select Stu, 29+25, now () from TB;

--You can use aliases for each column. Ideal for simplifying column identities, avoiding duplication of multiple column identifiers.

-Use the AS keyword or omit as.
Select stu+10 as Add10 from TB;

B. FROM clause

Used to identify the source of the query.
--You can alias the table. Use the AS keyword.

Select * from TB1 as TT, TB2 as BB;
--from clause, multiple tables can appear at the same time.

--Multiple tables are stacked horizontally together, and the data forms a Cartesian product.

Select * from tb1, TB2;

C. WHERE clause
--filtering from the data source obtained from the From. --Integer 1 is true, 0 means false. --The expression consists of an operator and an operand.

--Operand: variable (field), Value, function return value

--Operator:

=, <=>, <>,! =, <=, <, >=, A;,!, &&, | |, in (not) null, (not) like, (not) in, (NO) T) between and, is (not), and, or, not, XOR
is /i s not plus ture/false/unknown to verify that a value is true or false

<=> Same as <> function,<=> can be used for null comparisons

D. GROUP BY clauses, grouping clauses
group by Field/alias [Sort by]

Sorting is done after grouping. Ascending:ASC, descending:DESC

The following [aggregate functions] need to be used with group by:

count Returns the number of different non-null values count(*),count(field)
sum sum
Max asks for maximum value
min to find minimum value
avg averaging

Group_concat returns a string result with a non-null value for a connection from a group. Intra-group string connections.

E. HAVING clause, conditional clause

Same as where function, usage, execution time is different.
where at the beginning, the detection data is executed and the original data is filtered.
having to filter out the filtered results again.
The Having field must be a query, and thewhere field must be a data table.
where you cannot use the alias of a field. Because the where code is executed, the column values may not have been determined.
where you cannot use aggregate functions. Aggregate functions are generally required to use the having

The SQL standard requires having to refer to a column in a GROUP BY clause or to a column in an aggregate function.

F. order by clause, sort clause
order by sort field/alias sort by [, sort field/alias Sort method]...

Ascending:ASC, descending:DESC

Supports sorting of multiple fields.

G. limit clause, limiting the number of results clause

Limit the number of processed results only. Treat the result as a collection, in the order in which the records appear, the index starts at 0.

Limit start position, get the number of bars

Omit the first argument, which means starting at index 0.

Limit gets the number of bars

H. distinct, all options
distinct removal of repetitive notes

Default to all, record all

(not to be continued)
(Shocker Source: http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html)

1000 Line MySQL study notes (iii)