2-16 mysql field constraint-index-FOREIGN key

One: Field modifier

1:null and NOT NULL modifiers

Let's take a look at this example.

Mysql> CREATE TABLE worker (ID int not null,name varchar (8) Not null,pass varchar (a) not null);

Mysql> INSERT into worker values (1, ' HA ', ' 123456 ');

Mysql> INSERT into worker values (1, ' LB ', null);

ERROR 1048 (23000): Column ' pass ' cannot be NULL cannot be null

Mysql> INSERT INTO worker values (2, ' HPC ', ');

Note: A field that is not NULL cannot be inserted with "null", and only "null" can be inserted.

We may have these questions <null and not null differences >

1, the field type is not NULL, why you can insert a null value

2. Why the efficiency of NOT NULL is higher than null

3, when the judgment field is not empty, in the end to select * FROM table where column <> "or to use the SELECT * from table where the column is not NULL.

What's the difference between "null" and "null"?

1, empty value is not space-consuming

2. The null in MySQL is actually occupied space, the following is the explanation from the official MySQL

"NULL columns require additional space in the row to record whether their values is null. For MyISAM tables, each of the NULL column takes one bit extra, rounded up to the nearest byte. "

# "The empty column requires extra space in the row to record whether its value is empty. For the MyISAM table, each null column requires an extra bit, rounded to the nearest byte.

For example: A cup, empty value ' means the cup is a vacuum, null represents the cup filled with air, although the cup seems to be empty, but there is air.

For question 2, why is the efficiency of NOT NULL higher than NULL?

Null is not actually a null value, but to occupy space, so when MySQL compares, NULL participates in field comparisons, so there is a part of the effect on efficiency.

And the index does not store null values, so if the indexed field can be null, the efficiency of the index will be much lower.

-mysql It is difficult to optimize a reference nullable column query, which makes indexes, index statistics, and values more complex. Empty columns require more storage space and special processing is required within MySQL. After an empty column is indexed, each record requires an extra byte and can cause a fixed-size index in MyISAM to become a variable-size index--------This is also explained in "high-performance MySQL second Edition": "Nullable columns require more storage space" : Requires an extra byte as the flag bit to determine if NULL "requires special processing within MySQL"

Therefore, using NOT NULL is more efficient than null

For question 3. When judging the field not to be empty, exactly select * from table where column <> ' ' or use SELECT * from table where column was not null let's take a look at

Mysql> CREATE TABLE Test (col1 varchar () NOT NULL, col2 varchar (TEN) null) Engine=myisam;

mysql> INSERT INTO test values (", null);

mysql> INSERT into test values (' 1 ', ' 2 ');

mysql> INSERT into test values (', ' 1 ');

I'll use these two statements to find out

Empty indicates no space, null occupies space

2:default Setting the default value for a field

Specify a default value for a field

Mysql> CREATE TABLE Test2 (name varchar (8) Not null,dept varchar (+) Default ' SOS ');

mysql> INSERT into TEST2 (name) VALUES (' Kko ');

Summarize:

If the field is not set to default, MySQL depends on whether the field is null or NOT NULL, or null if it is nullable. If it is not possible to NULL, error:

If the Time field, the default is the current time, insert 0 o'clock, the default is the current time.

If it is an enum type, the default is the first element.

3:auto_increment field constraints

Automatic growth

You can only modify the Int field. Indicates that MySQL should automatically generate a unique number for the field (each time the maximum ID value is added 1. Exception: If the current maximum ID is 34, then delete 34, the newly added will be 35.). This is useful for primary keys. You can create a unique identifier for each record

Mysql> CREATE table items (ID int not NULL auto_increment primary key, label varchar (a) not null);

Mysql> INSERT into items values (' Aaba ');

Mysql> INSERT INTO items values (9, ' Aaba ');

How many more IDs will be inserted

Mysql> INSERT into items values (' abc ');

ID is 10

Mysql> INSERT INTO items values (9, ' ADL ');

ERROR 1062 (23000): Duplicate entry ' 9 ' for key ' PRIMARY '

Insert into items values (' ABCs '); Idmax =11 max=11

Delete from items where label= ' ABCs '; idmax=10 max=11

Insert into items values (' ABCSW '); Idmax=11 max=12

PRIMARY KEY constraint Unique

Second: Clear the records in the table

Clears all records in the table

Method One: Delete Does not add a where condition, emptying all table records. But delete does not clear 0 auto_increment value

Mysql> Delete from items;

Mysql> INSERT INTO items values ("AAAA");

Method Two: Delete all records in the table, clear the Auto_increment value.

Truncate

Function: Delete all records of the table and clear 0 auto_increment value. The newly inserted record starts at 1.

Syntax: TRUNCATE TABLE name;

mysql> TRUNCATE TABLE items;

Mysql> INSERT INTO items values (NULL, ' ABV ');

Mysql> INSERT into items values (' Hkuyb ');

Three: Index

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. More generally, the database index is like a directory in front of a book, which can speed up the database query.

Pros: To speed up search, reduce query time.

Disadvantages:

1 The index is stored as a file. If the index is too large, it takes up disk space. And he affects: Insert, UPDATE, delete execution time.

2 The data in the index must be synchronized with the data table data: If there are too many indexes, the indexes will be updated synchronously when the data in the table is updated, which reduces the efficiency.

Type of index

1. General Index

2. Uniqueness Index

3. Primary key index (primary index)

4. Composite Index

Normal index

The most basic index, not uniqueness, is to speed up the query speed

To create a normal index:

Method One: Add an index when creating a table

CREATE TABLE Table name (

Column definition

Index index name (field)

Index name (field));

Note: You can use key, or you can use index. The index name (field), the index name, can be added or not, and the field name is not used as the indexed name.

Mysql> CREATE TABLE demo (ID int (4), name varchar (), PWD varchar, index (PWD));

Note: Index and key are the same

Mysql> CREATE TABLE demo1 (ID int (4), name varchar (), PWD varchar, key (PWD));

Mysql> CREATE TABLE Demo2 (ID int (4), name varchar, pwd varchar, key index_pwd (PWD)); #加上名称

Method Two: When the table is created, use ALTER to add an index to the table:

ALTER TABLE name add index index name (field 1, Field 2 ...). ）;

View Index

Note: If key is Mul, then the value of the column can be repeated, which is a leading column of a non-unique index (the first column) or a component of a uniqueness index but can contain null values. is to indicate that it is a normal index.

Let's delete the index first

Mysql> ALTER TABLE demo drop key pwd; Note that the PWD here refers to the name of the index, not the field of the PWD in the table.

Then use ALTER to add

Mysql> ALTER TABLE demo add key (PWD);

Unique index

Basically the same as a normal index, but with one difference: all the values of an indexed column can only occur once, that is, must be unique to constrain the content, and the field value can only occur once. A unique index should be added. Uniqueness allows null values < allow NULL >.

To create a unique index:

Method One: Add a unique index when creating a table

CREATE TABLE Table name (

Column definition:

Unique key index name (field);

)

Note: Commonly used in fields where values cannot be duplicated, such as user name, phone number, and social security number.

Mysql> CREATE TABLE Demo3 (ID int (4) Auto_increment primary key, uName varchar, upwd varchar, unique index (UN AME));

Method Two: Add a unique index when modifying a table

ALTER TABLE name add unique index name (field);

mysql> ALTER TABLE DEMO3 drop key uName;

Mysql> ALTER TABLE DEMO3 add unique (uName);

Primary key Index

Querying the database, by primary key query is the fastest, each table can have only one primary key column, you can have multiple ordinary index columns. The primary key column requires that all the contents of the column must be unique, and the indexed column does not require that the content be unique, not allowed to be empty

Create a primary key index

Method One: Create a table to create a primary key index

Mysql> CREATE TABLE Demo4 (ID int (4) NOT NULL Auto_increment primary key,name varchar (4) is not NULL);

Mysql> CREATE TABLE Demo5 (ID int (4) NOT NULL auto_increment, name varchar () default null,primary key (ID));

Mysql> Show CREATE TABLE Demo5;

Show index from Demo5 \g

Method Two: Create a table after adding < not recommended >

Delete Test first

Remove the cause of this condition being auto_increment

mysql> ALTER TABLE DEMO5 change ID ID int (4) is not null;

mysql> ALTER TABLE DEMO5 drop PRIMARY key;

Add again

mysql> ALTER TABLE DEMO5 change ID ID int (4) is not null primary key auto_increment;

Summary: Primary key index, uniqueness Index difference: Primary key index cannot have null, uniqueness index can have null value

Composite Index

An index can consist of one, two, or more columns. An index on two or more columns is called a composite index

Example: Create a table to hold the IP and port that the server allows or deny, to record the IP and port to be unique.

Mysql> CREATE table Firewall (a host varchar () not NULL, port smallint (4) is not NULL, Access enum (' Deny ', ' allow ') is not Nu ll, primary key (Host,port));

Mysql> INSERT into firewall values (' 10.96.52.46 ', ' deny ');

Mysql> INSERT into firewall values (' 10.96.52.46 ', +, ' allow ');

Mysql> INSERT into firewall values (' 10.96.52.46 ', +, ' allow ');

ERROR 1062 (23000): Duplicate entry ' 10.96.52.46-21 ' for key ' PRIMARY '

Insert the same error, the only

Summarize:

When you build a table, add various indexes, in the following order:

CREATE TABLE table name (field definition, PRIMARYKEY (' bId '), UNIQUE key ' bi ' (' bimg '), Key ' bn ' (' bname '), key ' Ba ' (' author '))

Full-text index (fulltext index)

Full-text indexing (also known as full-text search) is a key technology used by search engines at present. It can use "word segmentation technology" and other algorithms to intelligently analyze the text of the key words in the frequency and importance, and then follow certain algorithm rules to intelligently filter out the search results we want

MySQL in case of large data volume, high concurrent connection.

Select statement where bname like '% net% '

Use the% _ wildcard character, not through the index, direct full table scan.

Absuwu like '%u_u '

Database pressure is high.

MySQL Solution: Full-text index: 3.2 starts to support full-text indexing. Chinese is not supported correctly.

Full-text indexing can only be used in varchar text

To create a full-text index:

Method One: Create the table when you create it

CREATE TABLE Table name (

Column definition,

Fulltext Key index name (field);

）

Method Two: Add when modifying a table

ALTER TABLE name ADD Fulltext index name (field);

ALTER TABLE ' books ' ADD Fulltext Index name (' author ')

Strong note: MySQL comes with a full-text index only for data tables that are MyISAM by the database engine, and if it is a different data engine, the full-text index does not take effect

MySQL's own full-text index can only be full-text search in English, currently cannot be full-text retrieval of Chinese.

Generally submitted to third-party software for full-text indexing

http://sphinxsearch.com/

Index Design principles:

Not as many indexes as possible.

The amount of data does not need to be indexed

The value in the column does not change much. Do not need to index the row ID

Columns that are often sorted (order by) and grouped (group by) need to be indexed

Uniqueness constraints correspond to the use of uniqueness indexes

Four: FOREIGN KEY constraints

What is a FOREIGN KEY constraint:

Foreign key is a kind of contract between table and table, because of the existence of this kind of relationship, we can make the data between table and table more complete, more connected.

About integrity, connectivity Let's give an example.

There are two tables, one is the user table, one is the order form:

1 "If I delete the user table user, then the order form inside the root of this user-related data, it becomes headless data, incomplete."

2 If I insert a piece of data in the order form, the order is in the user table and there is no corresponding user. So the data is incomplete.

If there is a foreign key, it is more convenient, you can not let the user delete data, or delete the user, through the foreign key also delete the data in the order table, this can also make the data complete.

To create a FOREIGN KEY constraint:

FOREIGN key: The integrity of the data is checked each time it is inserted or updated.

Method One: Creating a foreign key from create table

Grammar:

CREATE table data table name (

...,

[CONSTRAINT [constraint name]] FOREIGN key [foreign key field]
REFERENCES [foreign key table name] (Foreign key field, foreign key field 2 ...)
[on DELETE CASCADE]
[on UPDATE CASCADE]

)

Explanation of the parameters:

RESTRICT: denies deletion or update of the parent table.

CASCADE: Deletes or updates from the parent table and automatically deletes or updates the matching rows in the child table. On DELETE Cascade and on UPDATE Cascade are available

NOTE: On UPDATE cascade is a cascade update meaning that the on DELETE cascade is a cascade delete meaning that when you update or delete the primary key table, the foreign key table is also updated or deleted.

The simplified syntax:

Syntax: Foreign key The field of the current table references the external table name (The associated field) Type=innodb

Note: The following 4 conditions must be met for a successful creation:

1. Ensure that the referenced tables and fields exist.

2. The fields that make up the foreign key are indexed.

3. The storage engine must be specified with type: InnoDB.

4. Foreign key fields and associated fields must be of the same data type.

Example: we create a database that contains user information tables and order tables

Mysql> CREATE DATABASE market;

Mysql> CREATE table ' user ' (id int (one) not NULL auto_increment, name varchar (+) NOT null default ' ', sex int (1) not nul L Default ' 0 ', primary key (ID)) Engine=innodb;

#创建时, if the table name is a SQL keyword, use the anti-quote '

Mysql> CREATE TABLE ' order ' (o_id int (one) auto_increment, u_id Int (one) default ' 0 ', username varchar (+), Money Int (11), Primary KEY (O_ID), index (U_ID), foreign key Order_f_key (u_id) references user (ID) on the DELETE cascade on UPDATE cascade) EN Gine=innodb;

Note:

1:on DELETE CASCADE on UPDATE CASCADE add cascade deletions and updates:

2:: Ensure that the ID field exists in the referenced table user. The field u_id that makes up the foreign key is indexed. The storage engine must be specified with type: InnoDB.

Foreign key fields and associated fields must be of the same data type.

Inserting test data

mysql> INSERT INTO User (Name,sex) VALUES (' HA ', 1), (' LB ', 2), (' HPC ', 1);

mysql> insert INTO ' order ' (U_id,username,money) VALUES (1, ' HA ', 234), (2, ' LB ', 146), (3, ' HPC ', 256);

Test Cascade Delete:

mysql> Delete from user where id=1; To delete data with ID 1 in the user table

View the Order table again

Test cascading updates:

Pre-update data status

mysql> Update user set id=6 where id=2;

Test data integrity

FOREIGN KEY constraint, the order table is constrained by the user table

Insert a data in order u_id for 5 users, there is no in the user table, so insert does not go in

mysql> INSERT INTO user values (5, ' Find ', 1);

mysql> insert INTO ' order ' (U_id,username,money) VALUES (5, ' Find ', 346);

Method Two: Create a foreign key and cascade update via ALTER TABLE, CASCADE Delete

Grammar:

ALTER TABLE data table name add

[CONSTRAINT [constraint name]] FOREIGN key (foreign key field,..) References data table (reference field,...)

[ON update cascade|set null|no action]

[ON delete cascade|set null|no action]

)

Mysql> CREATE TABLE Order1 (o_id int (one) auto_increment, u_id Int (one) default ' 0 ', username varchar (+), Money Int (11), Primary KEY (O_ID), index (u_id)) Type=innodb;

Mysql> ALTER TABLE Order1 add foreign key (u_id) references user (ID) on the DELETE cascade on update Cascade,type=innodb;

Mysql> ALTER TABLE ORDER1 add constraint ' BK ' foreign Key (u_id) references user (ID) on DELETE cascade on UPDATE cascade,  Type=innodb; Specify a foreign key name

You must remember to bring InnoDB.

Mysql> Show CREATE TABLE order1;

To delete a foreign key:

Grammar

ALTER TABLE data table name drop FOREIGN KEY constraint (foreign key) name

mysql> ALTER TABLE order1 drop foreign key order1_ibfk_1;

Mysql> Show CREATE TABLE order1;

Five: View

What is a view

A view is a virtual table that exists in a database.

The view itself has no data, but only by executing the corresponding SELECT statement to get the corresponding data.

How we use it in the scenario, why use the view

If the result of a query is very frequent, that is, to often take this query results to do subqueries this

View simplifies user operations

View mechanism users can focus on the data they care about. If the data is not directly from the base table, you can define the view to make the database look simple, clear, and simplify the user's data query operations

The view is that the user can look at the same data in different ways.

For a fixed set of basic tables, we can create different views for different users so that different users can see the information they need.

Views provide a degree of logic to the refactoring database.

For example, the original a table is divided into B and C tables, we can still build a view on the basis of B and C tables, and the program using the data table can be unchanged.

Views can provide security for confidential data

For example, the results of each course constitute a basic table, but for each classmate can only view their own results of this course, so you can create a view for each student, hide other students ' data, only show the students own

The proper use of views can express query data more clearly.

Sometimes querying with existing views can greatly reduce the complexity of query statements.

Create a View

Syntax: CREATE view name (that is, virtual table name) as SELECT statement.

We operate in the book database

mysql> CREATE view BC as select B.bname, B.price, c.btypename from books as B left join category as C on B.btypeid=c. Btypeid;

can be accessed according to the normal table.

In addition, the data in the chart is synchronized with the data in the original data table.

To view the View creation information:

Mysql> show CREATE View BC \G

Querying the data in a view

Update or modify a view

Grammar:

Alter VIEW view name (that is, virtual table name) as SELECT statement.

Update view name (that is, virtual table name) set

mysql> ALTER view BC AS SELECT B.bname, b.publishing, C.btypeid from books as B left join category as C on b.btypeid=c . Btypeid;

Update

mysql> update BC set bname= ' HA ' where price=34;

Delete a view

Drop view name.

mysql> drop View BC;

2-16 mysql field constraint-index-FOREIGN key