Basic Mysql table operations and basic mysql operations

Basic Mysql table operations and basic mysql operations

1. Create a table

Syntax: create table Name (

Complete constraints on the Data Type of the attribute name,

Complete constraint for data type entry of attribute name,

.........

Attribute name Data Type

);

(1) Example:
1 create table example0 (2 id int, 3 name varchar (20), 4 sexboolean );

 

Table integrity constraints

Constraints	Description
(1) primary key	This field is the primary key of the table,
(2) foreign key	Identifies this field as a foreign key of the table and is connected to a base table.
(3) not null	The value that identifies this attribute cannot be blank.
(4) unique	The property value is unique.
(5) auto_increment	Automatic addition of the attribute value
(6) default	Set the default value for this property value

 

 

 

 

 

 

 

 

 

1.1 set the table's primary key

A primary key is a special field of a table. This field uniquely identifies each piece of information in the table. The relationship between the primary key and record is the same as that of the person and ID card. The name can be the same, but the ID card number is definitely not the same. The primary key is used to identify each record, and the primary key value of each record is different.

The primary key can help Mysql find every piece of information in the table at the fastest speed. The condition that the primary key must meet is its uniqueness. The primary key values of any two records in the table cannot be the same, otherwise, a primary key value conflict occurs. The primary key value cannot be blank. It can be a single field or a combination of multiple fields.

Primary key conflict:
Single primary key: The primary key cannot be the same
Multi-primary key: When a table has multiple primary keys, as long as the primary keys are not completely the same, the primary key conflicts only when all primary keys are identical.

1 create table sxkj (2 User_id int primary key, 3 user_name varchar (20), 4 user_sexchar (7 ));Primary Key

 

1.2 set multiple fields as primary keys

1 Example: 2 create table sxkj2 (3 user_id int, 4 user_name float, 5 grade float, 6 primary key (user_id, user_name ));Multiple Primary keys

 

1.3 set the foreign key of the table

A foreign key is A special field in A table. If aa is an attribute of Table B and depends on the primary key of Table A, Table A is called the parent table and table B is called the child table.

Syntax:

Constraint foreign key alias foreign key (foreign key field 1, foreign key field 2) references table name (associated primary key field 1, primary key field 2 );

 

1.4 set a non-null table Value

Syntax: attribute name data type NOT NULL

1 Example: 2 create table C (3 user_id int not null );

 

1.5 set table uniqueness constraints

Uniqueness means that this field cannot appear repeatedly in all records.

Syntax: attribute name data type unique

1 Example: 2 create table D (3 user_id int unique );

 

1.6 set the attribute value of the table to automatically increase

Auto_increment is a special constraint in the Mysql database. It is used to automatically generate a unique ID when inserting data into the table. A table can only have one field using the auto_increment constraint and must be unique.

Syntax: property name data type auto_increment

* The default field value starts from 1.

1 Example: 2 create table F (3 user_id int primary key auto_increment );

1. insert an empty message. 2. insert into F values (). 3. View table F. 5. select * from F. 6. 7. Check that user_id is 1.

 

1.7 set the table Default Value

When creating a table, you can specify the default value of the field in the table. If a new record is inserted but this field is not copied, the database automatically inserts a default value for this field, the default value of the field is set by default.

Syntax: property name data type default Value

Example: create table G (user_id int primry key auto_increment, user_name varchar (20) default 'zero '); insert NULL data to the table: insert into G values (); // by default, the first field is automatically increased to 1, and the second field is the default value zero.

 

2. view the table structure

Mysql DESCRIBE can view the basic definitions of the table, including the field name, Data Type of the field, whether it is a primary key and default value ..

 

2.1 Syntax: describe table name; can be abbreviated as desc

Example:

Desc B;

 

2.2 show create table query detailed structure statements of a table

 

3. Table modification methods

Extra: Modify the default table auto-increment, starting from 1000.

Alter table F auto_increment = 1000;

 

3.1 Modify Table Name

Syntax: alter table old table name rename new table name;

For example, alter table A rename zyA;

 

3.2 modify the table data type

Syntax: alter table name modify attribute name data type;

Example: alter table A modify user_name double;

 

3.3 modify the table field name

Syntax: alter table name change old attribute new data type;

Example: alter table A change user_name user_zyname float;

 

3.4 modify and add a field

Alter table name ADD attribute name 1 Data Type [integrity constraints] [FIRST | AFTER attribute name 2]

Add fields without constraints:
Alter table A add phone varchar (20)

Add A field with complete constraints: alter table A add age int (4) not null;

 

Add a field at the first position of the table

By default, fields are added each time. All are at the end of the table.

alter table tt add num int(8) primary key first; 

Insert a new field at that position and add a new field after phone.

alter table A add address varchar(30) not null after phone;

 

Summary:

(1) add a field at the end by default.

(2) If you want to add a field to the front of the table, use the first keyword.

(3) If you want to add a new field after a field, use the after keyword

 

3.5 delete a field

Alter table Name drop attribute name;

Example: Delete the age field alter table A drop age of table;

 

3.6 change the storage engine of a table

Alter table name engine = storage engine

alter table A engine=MYISAM

 

3.7 Delete the foreign key constraint of a table

Alter table Name drop foreign key alias;

 

Iv. Table deletion Methods

4.1 delete an unassociated common table

Drop table name;

 

4.2 Delete the parent table associated with another table

Some associations are established between some tables in the database. It is not that simple to delete a parent table that becomes a parent table and is associated with its child tables.

Delete method: First Delete the foreign key of the associated sub-table, and then delete the primary table.

1. First Use show create table sub-table name \ G; to view the foreign key alias of the sub-table
2. Then alter table sub-table Name drop foreign key alias; Delete foreign key alias
3. You can use the drop table parent table name to delete the parent table.