Shulou(Shulou.com)06/01 Report--

The following mainly brings you how to constrain MySQL fields. I hope these contents can bring you practical use. This is also the main purpose of this article on how to edit how to constrain MySQL fields. All right, don't talk too much nonsense, let's just read the following.

I. the use of field modifiers

1.1 null and not null modifiers

Null occupies space. This field cannot be empty.

The field set by not unll can be empty, and the card insertion control does not take up space when it is inserted with a null value.

Example: define a table definition field to compare null type with not null.

Mysql > create table myziduan (char1 varchar (18) not null,char2 varchar (18)) ENGINE=myisam;Query OK, 0 rows affected (0.01 sec) mysql > select * from myziduan;+-+-+ | char1 | char2 | +-+-+ | NULL | | A | B | | B | +-+-+ 3 rows in set (0.00 sec)

For empty fields to take up no space, null requires space.

1.2 Custom default fields in the setting table-default

If default is not set for this field in the MySQL table, MySQL will depend on whether the field is nll or otnull, or null if it can be null. An error is reported if it cannot be used for null.

If the time field defaults to the current time, and when inserted to 0, it defaults to the current time. If it is an enum (enumeration) type, it defaults to the first element.

Example

Mysql > insert into myziduan2 (id,name) values; Query OK, 1 row affected (0.01sec) mysql > select * from myziduan2 +-+ | id | name | depth | +-+ | 1 | Zhang Fei | 110 | 2 | Liu Bei | NULL | 3 | Guan Yu | | 4 | Zhuge Liang | SOS | +-+ -+ 4 rows in set (0.00 sec)

1.3 self-growing field-auto_increment

Auto_increment can only be used to modify the int field, indicating that MySQL should automatically generate a unique unused number for the field (add 1 each time based on the maximum ID value, and set the maximum ID value to 21:00, if you delete the ID at this time, and then insert id, then ID will start from 22), for the primary key, this is of great use. You can create a unique identifier for each record.

Create table myziduan3 (uuid int (10) auto_increment primary key,name varchar (48) not null,address varchar (48); rchar (48)); Query OK, 0 rows affected (0.02 sec) mysql > insert into myziduan3 (name,address) values ('Sun WuKong', 'Huaguoshan'), ('Zhu Bajie', 'Gao Jia'), ('Sha Wujing', 'Liushahe'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from myziduan3 +-+ | uuid | name | address | +-+ | 1 | Sun WuKong | Huaguoshan | | 2 | Zhu Bajie | Gaojia | | 3 | Sha Wujing | Liushahe | +-- -+ 3 rows in set (0.00 sec) mysql > mysql > insert into myziduan3 (uuid Name,address) values (10) 'Little White Dragon', 'Donghai') Query OK, 1 row affected (0.01 sec) mysql > insert into myziduan3 (name,address) values ('Tang Sanzang', 'Chang'an'); Query OK, 1 row affected (0.00 sec) mysql > select * from myziduan3 +-+ | uuid | name | address | +-+ | 1 | Sun WuKong | Huaguoshan | | 2 | Zhu Bajie | Gaojia | | 3 | Sha Wujing | Liushahe | | 10 | Xiao Bailong | East China Sea | | 11 | Tang Sanzang | Chang'an | +-+ mysql > delete from myziduan3 where uuid=11 Mysql > insert into myziduan3 (name,address) values ('Tathagata', 'Xitian'); Query OK, 1 row affected (0.00 sec) mysql > select * from myziduan3 +-+ | uuid | name | address | +-+ | 1 | Sun WuKong | Huaguoshan | | 2 | Zhu Bajie | Gaojia | | 3 | Sha Wujing | Liushahe | | 12 | Tathagata | Xitian | +-+ 4 rows in set (0.00 sec)

How to empty the size of the self-growing field and reset the maximum value of uuid.

Truncate clears the table records and resets the auto_increment setting to 0

Mysql > truncate table myziduan3;Query OK, 0 rows affected (0.03 sec) mysql > select * from myziduan3;Empty set (0.00 sec) mysql > insert into myziduan3 (name,address) values ('Tathagata', 'Xitian'); Query OK, 1 row affected (0.01 sec) mysql > select * from myziduan3 +-+ | uuid | name | address | +-+ | 1 | Tathagata | Xitian | +-+ 1 row in set (0.00 sec)

? The difference between delete and truncate?

Both delete and truncate can delete the data in the table, but delete does not delete the auto_increment record about the table, while truncate table name can reset the initial value of auto_increment.

II. Index

An index is a special file that, as an index on an innoDB data table, is a part of the tablespace. They contain reference pointers to all records of the data table. generally speaking, the database number index is like the directory of books, which can speed up the query speed of the database.

Advantages: speed up retrieval and reduce query time

Disadvantages: indexes are stored in files, and if there are too many indexes, there will be more disk space. In the second phase, he affects the execution time of insert,update,delete.

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

Index types can be divided into the following types: general index, unique index, primary key index and compliance index and so on.

Index creation principles:

The more indexes, the better. It is not recommended to build indexes when the data is not bright and the values in the columns do not change much, but use unique indexes for columns that need to be sorted frequently and for columns that require unique constraints.

2.1 General Index

The most basic index, which is not unique, can speed up the query speed.

Syntax:

Create table tb_name (field definition, index index name (field), index index name (field))

Note: when creating an index, you can use key or index, its index index name (field), the index name is not required, if not added, use the field as the index name.

Example: when creating a table, create a normal index

Mysql > create table suoyin1 (id int (5), name varchar (20), pwd varchar (28), index (pwd) +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (5) | YES | | NULL | name | varchar (20) | YES | | NULL | | pwd | varchar (28) | YES | MUL | NULL | | +-+-+ 3 rows in set (0.01sec)

2.1.1 add Index-alter

Syntax: alter table tb_name add index index name (field 1, field 2...)

Mysql > alter table suoyin2 add tel varchar (13); Query OK, 0 rows affected (0.29 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table suoyin2 add index index_tel (tel); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc suoyin2 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (5) | YES | | NULL | name | varchar (20) | YES | | NULL | | pwd | varchar (28) | YES | MUL | NULL | | tel | varchar (13) | YES | MUL | NULL | | +-- -+

2.1.2 View Index:

Mysql > desc suoyin2 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (5) | YES | | NULL | name | varchar (20) | YES | | NULL | | pwd | varchar (28) | YES | MUL | NULL | | +-+-+ 3 rows in set (0.00 sec)

The key type of MUL represents a normal index and allows repeating values.

2.1.3 Index deletion

Mysql > alter table suoyin2 drop key index_tel;Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc suoyin2 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (5) | YES | | NULL | name | varchar (20) | YES | | NULL | | pwd | varchar (28) | YES | MUL | NULL | | tel | varchar (13) | YES | | NULL | | +-- -+-+ 4 rows in set (0.00 sec)

2.2 unique Index

Unique index name, the value of the index column can not be repeated, can only appear once, must be unique, used to constrain content, fields can only appear once, used to constrain content, unique index allows null value.

Syntax: create table tb_name (field definition: unique key index name (field))

Note: it is commonly used in fields whose values cannot be repeated, such as user name, phone number and × × number.

Mysql > create table suoyin3 (uuid int (10) auto_increment primary key,Name varchar (18), Pwd varchar (15), unique index (Name); mysql > desc suoyin3 +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | uuid | int (10) | NO | PRI | NULL | auto_increment | | Name | varchar (18) | YES | UNI | NULL | | Pwd | varchar (15) | YES | | NULL | | +-+ -+

2.2.1 modify unique index

Delete the index:

Alter table tb_name drop key key_name;mysql > alter table suoyin3 drop key Name

Add an index:

Alter table tb_name add unique (name); mysql > alter table suoyin3 add key name (Name)

2.3 Primary key Index:

In the query database, the search speed according to the primary key index is the fastest, each table can only have one primary key column, can have multiple ordinary index columns, the primary key column requires that all the contents of the column must be unique, while the index column does not require that the content must be unique and is not allowed to be empty.

2.3.1 to create the primary key is the index:

Syntax: create table tb_name (column definition,)

Mysql > create table primary1 (uuid int (10) not null auto_increment primary key,name varchar (18) not null); Query OK, 0 rows affected (0.16 sec) mysql > desc primary1 +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | uuid | int (10) | NO | PRI | NULL | auto_increment | | name | varchar (18) | NO | | NULL | | +-+- -+ 2 rows in set (0.00 sec)

2.3.1 to delete the key value of the primary key index, you must first modify the modifier of the corresponding field of the index.

Mysql > alter table primary1 change uuid uuid int (10) not null;Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table primary1 drop primary key;Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0

Add an index:

Mysql > alter table primary1 change uuid uuid int (14) not null primary key;ormysql > alter table primary1 change uuid uuid int (10) auto_increment primary key

3. Composite index:

When more than 2 indexes are created in a table, it is called a conforming index.

Example: create a table to store the IP and port allowed or denied by the CVM. The IP and Port in the record need to be unique.

Mysql > create table firewall (host varchar (15) not null,port smallint (4) not null,access enum ('deny','allow') not null,primary key (host,port)); Query OK, 0 rows affected (0.13 sec)

Data insertion effect

Mysql > insert into firewall values; Query OK, 1 row affected (0.01 sec) mysql > insert into firewall values; ERROR 1062 (23000): Duplicate entry '192.168.31.101-56' for key 'PRIMARY'mysql > insert into firewall values (192.168.31.102) Query OK, 1 row affected (0.00 sec) mysql > insert into firewall values; Query OK, 1 row affected (0.01 sec) mysql > insert into firewall values; ERROR 1062 (23000): Duplicate entry '192.168.31.102-56' for key 'PRIMARY'

An error is reported when inserting the same IP and port.

When you create a table, the order in which various indexes are added is as follows:

Create table tb_name (field definition, primarykey ('key field'), unique key' BI' ('ukey'), key (' key_word'), key (other))

IV. Full-text index

MySQL full-text indexing is a key technology used by search engines at present. It can use word segmentation technology and other algorithms to intelligently analyze the frequency and importance of keywords in text, and then intelligently select search results according to certain algorithm rules.

Before MySQL version 5.7, full-text indexing was only supported by MISAM storage engine, and in later versions, InnoDB engine also introduced full-text indexing.

For example: MySQL in the case of large amount of data and high concurrent links

Select statement where bName like'% net%'

Where% represents wildcard characters here and scans tokens directly without passing the index.

The use of full-text indexing puts great pressure on MySQL databases.

Create a full-text index:

Method 1:

Create table tb_name (field definition, fulltext key index name (field))

Method 2:

Alter table tb_nameadd fulltext index name (field)

V. Foreign key constraint

The foreign key constraint, hence the name thought, is to establish a certain relationship between the table and the table. It is precisely because of the existence of this relationship that the data between the table can be associated, and the table can be made more complete by using the foreign key constraint. Make the table more relevant, so as to ensure the integrity of the list.

5.1 create foreign key syntax:

Create table tb_name (... [constraint [constraint name] foreign key [foreign key field]] references [foreign key table name] (foreign key field 1, foreign key field 2) [on

Delete cascade] [on update cascade])

Note: on update cascade is a cascading update operation and on delete cascade is a cascading deletion. That is, when you update or delete the primary key table, the foreign key table will be updated and deleted along with it.

The following points are required for the successful creation of a foreign key:

1. Make sure that the referenced fields and tables really exist

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

3. The storage engine specified by type must be innodb.

4. The data types of foreign key fields and associated fields must be the same.

Example: create a user table and a product table and associate them by introducing a foreign key foreign key

Mysql > create table USER (uid int (10) auto_increment,uname varchar (18) not null,sex ENUM ('male', 'female') default 'female', Tel varchar (14), address varchar (60), primary key u_id (uid)) Query OK, 0 rows affected (0.09 sec) mysql > create table order (oid int (10) auto_increment,uid int (10) not null,goods varchar (48) not null,gid int (18) not null,money int (10) not null,primary key o_id (oid), index g_name (goods), foreign key order_f_key (uid) references USER (uid) on delete cascade on update cascade) Query OK, 0 rows affected (0.18 sec) insert data mysql > insert into USER (uname,sex) values ('Zhang Fei', 'male'), ('Guan Yu', 'male'), ('Xiao Qiao', 'female'); mysql > insert into order (oid,uid,goods,gid,money) values (0000001pint 1 minus' Shuanghui Beef', 10000000001mrm56), (0000002p3pr 'Bangjie Beef', 100000011LING 54'); Query OK, 2 rows affected (0.08 sec)

Query:

Mysql > select u.uiddir u.unamememo.giddir o.goodsmemo.money from USER as u left join order as o on u.uid=o.uid +-+ | uid | uname | gid | goods | money | +-+ | 1 | Zhang Fei | 100000001 | Shuanghui Beef | 56 | 3 | Xiao Qiao | 100000011 | Bangjie Beef | 54 | 2 | Guan Yu | NULL | +-+ 3 rows in set (sec)

Delete the test:

Delete the user of the table 'user' uid=1

Mysql > delete from table USER where uid=1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table USER where uid=1' at line 1

Test updates:

Mysql > update USER set uid=6 where uname=' Zhang Fei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

Query update results

Mysql > select u.uiddir o.giddir o.goodsdir o.moneymoney from USER as u left join order as o on u.uid=o.uid +-+ | uid | gid | goods | money | +-+ | 6 | 100000001 | Shuanghui Beef | 56 | | 3 | 100000011 | Bangjie cattle Meat | 54 | | 2 | NULL | +-+ 3 rows in set (0.00 sec)

The above results show that the introduction of foreign keys effectively preserves the integrity of the data table.

? How to create a foreign key after creating a table

Username varchar (18), money int (11), primary key (oid), index (uid), mysql > create table order1 (oid int (10) auto_increment,uid int (11), index (uid)); mysql > alter table order1 add foreign key (uid) references USER (uid) on delete cascade on update cascade;Query OK, 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0

Custom foreign key name:

Mysql > alter table order1 add constraint `fk_ name` foreign key (uid) references USER (uid) on delete cascade on update cascade;Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0

Delete the foreign key:

Mysql > alter table order1 drop foreign key order1_ibfk_1

View foreign keys:

Mysql > show create table order\ G * * 1. Row * * Table: order Create Table: CREATE TABLE `order` (`oid` int (10) NOT NULL AUTO_INCREMENT, `uid` int (10) NOT NULL, `goods` varchar (48) NOT NULL, `gid` int (18) NOT NULL, `money` int (10) NOT NULL, PRIMARY KEY (`oid`) KEY `gname` (`goods`), KEY `order_f_ key` (`uid`), CONSTRAINT `order _ ibfk_ 1` FOREIGN KEY (`uid`) REFERENCES `USER` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf81 row in set (0.00 sec) ERROR: No query specified

Mysql >

VI. View:

Mysql > create view bc as select b.bNameJournal b.pricedomc.bTypeName from books as b left join category as c on b.bTypeId=c.bTypeId

View creation information

Mysql > show create view bc\ gateway * 1. Row * * View: bc Create View: CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost` SQL SECURITY DEFINER VIEW `bc`AS select `bName` AS `bName`, `b`.`price` AS `price` `c`.`bTypeName` AS `bTypeName` from (`books`b` left join `roomy``c` on ((`b`.`bTypeId` = `c`.`bTypeId`)) character_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec) View mysql > select * from bc\ G

For the above about how to constrain the MySQL field, you do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.