Mysql modifier _ MySQL

Mysql modifier bitsCN.com

Mysql modifier

Not null

If not null is used for modification, when the default value is added to the table:

The value type is 0.

The string type is null.

If the data type is timestamp, the default value is the current time.

Enumeration type. The first pre-defined value is added.

Verification example:

Mysql> create table t6 (a int not null, B char (10) not null, c timestamp not null, d enum ('Y', 'n') not null );

Mysql> insert into t6 values (); // add the default value (that is, values is left blank and nothing is written)

Mysql> select * from t6;

+ --- + ------------------- + --- +

| A | B | c | d |

+ --- + ------------------- + --- +

| 0 | 15:42:17 | y |

+ --- + ------------------- + --- +

Bytes ----------------------------------------------------------------------------------------------------------------------------

Default

The default modifier specifies a default value for the field.

Example:

> Create table t2 (id int, name varchar (10), dep varchar (10) default "HR ");

> Insert into t2 set id = 1, name = "Lili ";

> Insert into t2 set id = 2, name = "Anna ";

> Insert into t2 set id = 3, name = "Hebe", dep = "MIS ";

> Select * from t2;

+ ------ +

| Id | name | dep |

+ ------ +

| 1 | Lili | HR |

| 2 | Anna | HR |

| 3 | Hebe | MIS |

+ ------ +

Auto_increment

The auto_increment modifier is only applicable to the INT field, indicating that MySQL should automatically generate a number for this field (add 1 to the previous value each time ).

MySQL tables can only have one auto_increment field, which must be defined as a key.

> Create table t3 (id int not null auto_increment primary key, name varchar (10) not null );

> Insert into t3 (name) values ("Anna ");

> Insert into t3 (name) values ("Nana ");

Mysql> select * from t3;

+ ---- + ------ +

| Id | name |

+ ---- + ------ +

| 1 | Anna |

| 2 | Nana |

+ ---- + ------ +

Bytes -----------------------------------------------------------------------------------

Unique

UNI

Indicates that the record cannot be repeated.

Example:

> Create table user (name char (10), email varchar (20) unique );

> Insert into user values ("Anna", "anna@163.com ");

> Insert into user values ("Nana", "anna@163.com ");

ERROR 1062 (23000): Duplicate entry 'Anna @ 163.com 'for key 1

The unique field null is allowed.

Mysql> insert into user values ();

Mysql> insert into user values ();

Mysql> select * from user;

+ ------ + -------------- +

| Name | email |

+ ------ + -------------- +

| Anna | anna@163.com |

| NULL |

| NULL |

+ ------ + -------------- +

Delete unique

> Alter table user drop index email;

You can set the unique attribute in either of the following ways:

> Create table t10 (name char (10), email varchar (20) unique );

> Create table t11 (id int, name char (10), unique (id ));

Bytes ------------------------------------------------------------------------------------------------------------

Index

MUL

To speed up the search and reduce the query time, MySQL allows us to set indexes for specific fields in a table.

Index disadvantage: it occupies a certain amount of disk space and affects the execution time of INSERT, UPDATE, and DELETE operations.

Add index

> Create table sales (name char (4), price float (4, 2), date, index name_index (name); // add an index to the field name, the index name is name_index. If no index name is defined, the field name is used as the index name.

View indexes

> Show index from sales;

Add an index for multiple fields

> Create table sales2 (name char (4), price float (4, 2), date, index name_index (name), index (price ));

> Create table sales3 (name char (4), price float (4, 2), date, index (name, price ));

Use the create index command to add an index to an existing table

> Create index id_index on xueke (id );

Delete index

> Drop index id_index on xueke;

------------------------------------------------------------------

Primary key

Primary key

You can set a primary key by using the following methods:

> Create table pri (id tinyint not null unique, name char (10); // not null unique

> Create table pri (id tinyint primary key, name char (10 ));

> Create table pri (id tinyint, name char (10), primary key (id ));

Primary key combination

> Create table firewall (host varchar (20), port smallint (4), access enum ('Deny', 'allow'), primary key (host, port ));

> Insert into firewall values ('1970. 168.10.1 ', 21, 'deny ');

> Insert into firewall values ('1970. 168.10.1 ', 80, 'deny ');

> Insert into firewall values ('1970. 168.10.2 ', 80, 'deny ');

> Insert into firewall values ('1970. 168.10.2 ', 80, 'deny ');

ERROR 1062 (23000): Duplicate entry '192. 168.10.2-80' for key 1

Mysql> select * from firewall;

+ -------------- + ------ + -------- +

| Host | port | access |

+ -------------- + ------ + -------- +

| 192.168.10.1 | 21 | deny |

| 192.168.10.1 | 80 | deny |

| 192.168.10.2 | 80 | deny |

+ -------------- + ------ + -------- +

In this case, the host or port can be repeated, but not at the same time.

-------------------------------------------------------------------------

Foreign key

Create a foreign key:

Foreign key... references...

> Create table score (id int, name char (10) primary key, math int, phy int) type = innodb;

> Create table addr (id int primary key, aname char (10) not null, addr char (255), tel int, index (aname), foreign key (aname) references score (name) type = innodb;

Constraints:

1. all tables in the link must be InnoDB tables.

2. the referenced table and field (must be the primary key; otherwise, the MySQL-errno: 150 error will occur) exist.

3. the fields that make up the foreign key are indexed

4. in the foreign key relationship, the data type of the field must be similar, which is important to the integer type that the size and symbol must match.

Mysql> insert into score values (1, "Anna",), (1, "John",), (3, "Lisa", 50, 60 );

Mysql> select * from score;

+ ------ +

| Id | name | math | phy |

+ ------ +

| 1 | Anna | 70 | 80 |

| 1 | John | 80 | 90 |

| 3 | Lisa | 50 | 60 |

+ ------ +

Once a foreign key is created, MySQL only allows the addr. aname field to input values that also exist in the score. name field.

Mysql> insert into addr values (1, "Anna", "addr...", 4334543 );

Mysql> insert into addr values (2, "Lucy", "addr...", 8774366 );

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('db1/addr ', CONSTRAINT 'addr _ ibfk_1' foreign key ('aname ') REFERENCES 'score '('name '))

Mysql> select * from addr;

+ ---- + ------- + --------- +

| Id | aname | addr | tel |

+ ---- + ------- + --------- +

| 1 | Anna | addr... | 4334543 |

+ ---- + ------- + --------- +

Delete foreign key:

1. delete a table with foreign keys

> Drop table addr;

Query OK, 0 rows affected (0.00 sec)

2. delete foreign keys from the table

Syntax:

Alter table name drop foreign key name;

Show create table name;

Mysql will report the following error:

> Alter table addr drop foreign key aname;

ERROR 1025 (HY000): Error on rename of './db1/addr' to './db1/# sql2-5258-7' (errno: 152)

On delete cascade clause

Cascade deletes all records that have reference relationships with deleted key values.

Create a table

> Create table score (id int, name char (10) primary key, math int) type = innodb;

> Create table addr (id int primary key, aname char (10), addr char (255), tel int, index (aname), foreign key (aname) references score (name) on delete cascade) type = innodb;

Insert record

> Insert into score values (1, "Nana", 50), (2, "Hebe", 70 );

> Insert into addr values (1, "Nana", "addr...", 6668787), (2, "Hebe", "addr...", 8989666 );

> Select * from score;

+ ------ +

| Id | name | math |

+ ------ +

| 1 | Nana | 50 |

| 2 | Hebe | 70 |

+ ------ +

> Select * from addr;

+ ---- + ------- + --------- +

| Id | aname | addr | tel |

+ ---- + ------- + --------- +

| 1 | Nana | addr... | 6668787 |

| 2 | Hebe | addr... | 8989666 |

+ ---- + ------- + --------- +

Delete Hebe records in the score table. The Hebe records in the addr table are automatically deleted.

> Delete from score where name = "Hebe ";

> Select * from score;

+ ------ +

| Id | name | math |

+ ------ +

| 1 | Nana | 50 |

+ ------ +

> Select * from addr;

+ ---- + ------- + --------- +

| Id | aname | addr | tel |

+ ---- + ------- + --------- +

| 1 | Nana | addr... | 6668787 |

+ ---- + ------- + --------- +

BitsCN.com