Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "case analysis of MySQL structure". The editor shows you the operation process through the actual case, and the operation method is simple, fast and practical. I hope this article "case analysis of MySQL structure" can help you solve the problem.

Constraint condition

The function is to restrict how to assign values to fields

View constraints

Mysql > desc library name. Table name

Mysql > desc db1.t9

/ / as shown in the figure, each column represents the meaning of: field name | Type | Null | key value | default value | additional setting

Mysql > insert into T9 values (null,null,null)

/ / as shown in the figure above, the constraint condition is allowed to be NULL, so the assignment here can be empty, and null is case-insensitive.

Mysql > select * from db1.t9

Mysql > insert into db1.t9 (name,sex) values ("lucy", "girl"); mysql > select * from db1.t9

/ / in the constraint condition of table T9, the default value of the third field is NULL, that is, when no value is assigned to the third field, the table will be filled with the default value, that is, NULL will be entered by default as shown in the following figure.

Set constraints

Null / / allowed is empty (default)

Not null / / is not allowed to be null (empty)

Key / / key value type

Default / / sets the default value, which defaults to NULL

Extra / / additional settings

Mysql > create database db2;mysql > create table db2.t1 (- > name char (10) not null default "",-> age tinyint unsigned default 19,-> sex enum ("m", "w") not null default "m"->)

/ / create a table with the type of db2.t1,name field as fixed length char, the constraint condition is that it is not allowed to be empty, and the default value is 0 characters, and the display effect is shown in the following figure; the type of age field is a small integer tinyint, the constraint condition is not allowed to be a negative number (unsigned), and the default value is set to 19. Note that the default setting here must not exceed the range of tinyint type, that is, the setting of default value cannot exceed 255mm. The type of sex field is enumerated, enum is selected, the constraint condition is that null is not allowed, and the default value is set to m

Mysql > desc db2.t1

Mysql > insert into db2.t1 (name) values ("bob")

/ / only assign values to name fields, and the remaining fields are assigned by default values

Mysql > select * from db2.t1

Mysql > insert into db2.t1 values ("lucy", "21", "w"); / / if you assign a value to the field, you will not assign mysql > select * from db2.t1 to the default value

Mysql > insert into db2.t1 values (null,null,null); / / error message: name field is not allowed to be empty Neither name nor sex fields can be empty ERROR 1048 (23000): Column 'name' cannot be nullmysql > insert into db2.t1 values ("null", null, "w"); / / "null" means no longer empty, just the character null, so you can assign a value of mysql > insert into db2.t1 values ("", null, "w") to the field. / / "" is 0 characters, which is different from the empty space. You can also assign a value of mysql > select * from db2.t1 to the field.

Summary

Constraint condition

Types

Null (whether empty null is allowed)

Null is allowed by default

Empty not null is not allowed

Key (key value)

General index index

Unique index unique

Primary key primary key

Foreign key foreign key

Full-text index fulltext

Default (default value: no field assignment using default value assignment)

Null when not defined by default

Defined as default (to match the field type)

Extra (extra settings: no extra settings by default)

Modify table structure

Grammatical structure

Usage

Mysql > alter table library name. Table name execution action

Perform an action

Add add Field

Modify modifies the field type

Change modifies the field name

Drop delete field

Rename modifies the table name

Add a new field

Usage

-- A new field is added at the end of the field by default

Mysql > alter table library name. Table name add field name type (width) constraint

Add field name type [constraint] after field name

Add field name type [constraint] first

Add fields without specifying a location

Mysql > alter table db2.t1 add email varchar (50); / / do not specify the location of the field to be added. By default, it is at the end of mysql > desc db2.t1.

Add a field after a field

Mysql > alter table db2.t1 add hobby set ("eat", "drink", "play", "happy") not null default "eat,drink" after age; / / specifies that the add location is after the field age, the condition setting is not allowed to be empty, and the default value is set to eat,drinkmysql > desc db2.t1.

Mysql > select * from db2.t1; / / check the contents of the table and find that the default value is automatically added to the hobby field

Add fields to the front

Mysql > alter table db2.t1 add class char (7) default "B180601" first; / / add field class to the front mysql > desc db2.t1

The mysql > select * from db2.t1; / / class field automatically adds default values

Modify field type

Basic usage

The modified field type cannot conflict with the stored data

Mysql > alter table library name. Table name modify field name type (width) constraint

Modify field name type [constraint] after field name

Modify field name type [constraint] first

Note:

If the field below this field type in the table already has a value, then the modified type and constraint cannot conflict with the data already stored in the field, and modification is not allowed if a conflict occurs. For example, if there is a field name in which a data is stored as bob, then changing the type (width) of the field to char (1) will cause the modification to fail. Because the width of bob is 3, if you change the type (width) of the field to char (1), the source data bob cannot be saved. Because the stored data takes precedence, it cannot be modified.

When you modify the type of a field, you should note that the parts that are not modified should be copied as is. If the parts that are not modified are not copied as is, it is tantamount to restoring them even with the default configuration. For example, there is a field name, its type is char (10), the constraint condition is not allowed to be empty, the default value is "", now you want to change its type width to char (20), and the rest of the unmodified part is not copied as is in the command, then the output result of the field's constraints will become the default, that is, null is allowed, and the default value is NULL. The same is true when changing the position of a field.

Mysql > desc db2.t1

Modify the location of field age to the end of field class

Mysql > alter table db2.t1 modify age tinyint unsigned default 19 after class; / / except for modifying the position of field age, other areas that are not modified, such as type (width) and constraints, are copied as-is.

Mysql > desc db2.t1

Change the type of field name to varchar (15)

Mysql > alter table db2.t1 modify name varchar (15) not null default ""; mysql > desc db2.t1

Modify field name

Basic usage

-- can also be used to modify field types

Mysql > alter table library name. Table name change Source Field name New Field name Type (width) constraint

Note: it can also be used to modify types and constraints, just write new types and constraints.

Mysql > desc db2.t1

Change the field name email to mail

Mysql > alter table db2.t1 change email mail varchar (50)

Mysql > desc db2.t1

Change the field name mail to email and modify the constraint

Mysql > alter table db2.t1 change mail email varchar (50) not null default "njw@163.com"; / / an error occurred because the value in the mail field in the source data is NULL, and if the constraint is changed to not null, it will conflict with the source data, so the modification failed ERROR 1138 (22004): Invalid use of NULL valuemysql > alter table db2.t1 change mail email varchar (50) default "njw@163.com"; mysql > desc db2.t1

Delete a field

Basic usage

Mysql > alter table library name. Table name drop field name

Mysql > alter table db2.t1 drop email; / / Delete the field of table T1 in the library db2 emailmysql > desc db2.t1; / / No field email, has been deleted

Mysql > select * from db2.t1; / / Field email and its data have been deleted

Modify table name

Basic usage

Mysql > alter table library name. Table name rename new table name

Name of the library. New table name

Mysql > use db2

Mysql > show tables

Mysql > alter table db2.t1 rename stuinfo; / / change the table name to stuinfo

Mysql > show tables

Mysql > select * from stuinfo

This is the end of the introduction of "case Analysis of MySQL structure". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.