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

Blog outline:

1. Constraint related statement Primary key constraint non-empty constraint setting uniqueness setting column default value setting self-increment 2.Using the ALTER instruction to modify the data length of the column value modify the field name to insert a new field add a constraint add a foreign key delete column delete column modify column order delete table 1, constraint-related statements 1, Primary key constraint (primary key constraint requires that the data of the primary key column be unique And null is not allowed) mysql > create database test01 Mysql > use test01;mysql > create table T1 (- > id int (10),-> name varchar (10) primary key,-> sex varchar (5),-> info varchar (200)->)

Determine whether the created is a primary key (whether the column has the word PRI):

The above method is to define the primary key while defining the column. Let's write down to specify the primary key after all the columns have been defined:

Mysql > create table T2 (- > id int (10),-> name varchar (10),-> sex varchar (5),-> primary key (id)->)

Confirm that there is a primary key:

2. Non-empty constraints (column values are not allowed to be empty) mysql > create table T3 (- > id int (6) not null,-> name varchar (10)->)

Check the table information to confirm:

3. Set the uniqueness of the value (duplicate data is not allowed, it can be empty, but there can be only one empty, otherwise it will be regarded as duplicate) mysql > create table T7 (- > id int not null unique,-> name varchar (20)->)

View its table structure:

You can see that it is identified as the primary key, but it is not specified as the primary key at the time of creation, but the properties of this column basically meet the requirements of the primary key, such as unique and cannot be empty.

4. Set the default value of the column (if the column is empty, the default value is written) mysql > create table T4 (- > id int (2) not null,-> name varchar (20),-> project varchar (20) default 'mysql'->)

View confirmation:

5. Set self-increment (generally used for id columns, self-increment columns must be set as primary keys)

Note: mysql only allows you to set initial values, but not self-increment, that is, you can set the first value to 5, and then increment it in turn, such as: 5, 6, 7. However, you cannot set it to increment 2 numbers at a time, such as 5, 7, 9.

Mysql > create table T5 (- > id int not null primary key auto_increment,-> name varchar (20)->)

Check the table structure to confirm:

Test its self-increasing effect:

Mysql > insert into T5 (name) values ('zhangsan'), (' lisi'); mysql > select * from T5 + | id | name | +-- +-+ | 1 | zhangsan | 2 | lisi | +-+-+

As you can see from the above test, only two values of name are inserted, and no values of id are inserted, but when you view the table data, id already has a value, indicating that the increment takes effect.

Set the starting value of self-increment

# define the initial value as 5mysql > create table T6 (- > id int primary key auto_increment,-> name varchar (20)->) auto_increment=5;# insert data to test mysql > insert into T6 (name) values ('zhangsan'), (' lisi')

Verify its self-added value:

Second, the use of ALTER instruction 1. Modify the data length of column values mysql > desc T1 +-+ | Field | Type | Null | Key | Default | Extra | +- -- +-+ | id | int (10) | YES | | NULL | | name | varchar (10) | NO | PRI | NULL | | sex | varchar (5) | YES | | NULL | | info | varchar | YES | | NULL | +-- -+ 4 rows in set (0.00 sec) mysql > alter table T1 modify name varchar (20)

Confirm the modified table structure:

2. Modify the field name (when you change the field name, you can also change the data type and data length of the new field name) mysql > desc T1 +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | id | int (10) | YES | | NULL | | name | varchar (20) | NO | PRI | NULL | | sex | varchar (5) | YES | | NULL | | info | varchar | YES | | NULL | + |- -+ mysql > alter table T1 change info infofo char (20)

Verify the modified results:

3. Insert a new field into the table

1) insert a new column in the last column:

Mysql > desc T3 +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | id | int (6) | NO | | NULL | name | varchar (10) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > alter table T3 add tel int (13)

View the inserted column:

2) insert a new column at the beginning of the table:

Mysql > alter table T3 add sex char (1) first

3) insert a new column after the specified column:

Mysql > alter table T3 add loc varchar (255) after name;4, add constraints mysql > alter table T3 add hobyy varchar (255) default 'work';5 when adding fields, add a foreign key

Before you do this, you need to find your own table with a primary key (here, the name column of the T1 table is the primary key).

The T1 table is now structured as follows:

The structure of the T3 table is as follows:

Now add the name column of the T3 table as the foreign key of the name column of the T1 table (where t3_t1_name is the custom constraint name):

Mysql > alter table T3 add constraint t3_t1_name foreign key (name) references T1 (name)

View the changes in the T3 table:

6. Delete foreign keys

Remove the foreign key added above, where t3_t1_name is the name of the foreign key.

Mysql > alter table T3 drop foreign key T3 roomt1name drop tel MySQL > alter table T3 drop key t3roomt1names7, delete the column mysql > alter table T3 name

Note: if the column you want to delete is associated with a column in another table, you need to delete the relationship before deleting the column. Otherwise, when a column with the same name is created later, a relationship will be established automatically.

8. Modify the order of columns mysql > alter table T3 modify name varchar (10) first;9, delete table mysql > drop table T5; Query OK, 0 rows affected (0.00 sec) mysql > drop table T5; ERROR 1051 (42S02): Unknown table 'test01.t5'mysql > drop table if exists T5; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > show warnings +-- + | Level | Code | Message | +-+ | Note | 1051 | Unknown table 'test01.t5' | + -+-- +-- + 1 row in set (0.00 sec)

Similarly, when performing a delete table operation, if there is an association relationship, you need to delete the association relationship before deleting the table.

-this is the end of this article. Thank you for reading-

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.