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

The following to understand the basic methods of operating mysql database tables, I believe that you will benefit a lot after reading, the text is not much in the essence, hope to operate the basic methods of mysql database tables this short article is what you want.

Operation of the table

Represents the basic unit of data stored in a database, composed of several fields, mainly used to store data records, and the operation of the table is to add, delete, modify and query, which is the most basic operation and the most important operation.

Create a tabl

Syntax create table table name

(field 1 type [(width) constraint]

Field n type [(width) constraint]

);

Field names cannot be the same in the same table

Width and constraints are optional

Field name and type are required

Example:

Use school

Create table student

(name char (10)

Sex char (8)

Age int (3)

Password varchar (50)

);

View table structure

Describe table name

Desc table name

Show create table table name; / / View table details

Table integrity constraints to ensure the integrity, consistency and accuracy of data

Keyword primkary key primary key, a record that is uniquely identified

Foreign key foreign key, making association

No null is not empty

Unique key unique constraint, which itself is an index

Default default value

Auto_increment self-growth

Example:

Use school

Create table student

(name char (10) not null

Sex enum ('male','female'), default' male' not null

Age int unsigned not null default 18, / / unsigned modifier value must be positive

Hobby set ('music','book','disc') default' book,disc'

);

Set unique constraint instance: unique

Create table departtment (

Dept_id int

Dept_name varchar (30) unique

Comment varchar (50)

);

Set primary key constraint instance: primary key is very important, unique identifier, which itself is an index

Use a single column as the primary key

Create table departtment (

Dept_id int primary key

Dept_name varchar (30)

Comment varchar (50)

);

Use multiple columns as primary keys, that is, compound primary keys, that is, the two cannot have the same data together.

Create table departtment (

Dept_id int

Dept_name varchar (30)

Comment varchar (50)

Constraint pk_id_name primary key (dept_id,dept_name)

);

Set self-growth auto_increment self-growth is usually used in conjunction with the primary key, and this field must be an integer type

Create table student (

Dept_id int primary auto_increment

Dept_name varchar (30)

Comment varchar (50)

);

Setting the foreign key constraint foreign key mainly does the table association. The concept of parent-child table is involved here. The foreign key must be supported by the storage engine, so you must make sure that the storage engine is innodb.

Example

Parent table

Create table empolyees (

Name varchar (20) not null

Mail varchar (20)

Primary key (name)

) engine=innodb

Child table

Create table pryroll (

Id int (5) not null

Name varchar (20) not null

Payroll float (10dint 2) no null

Primary key (id)

Constraint fk_name foreign key (name) references employees (name)

On udate cascade

The name in the on delete cascade / / word table is associated with the name field of the employees table, and updates are deleted synchronously. Only the person in the parent table can be inserted in the child table, otherwise it will not work.

) engine=innodb

Modify the table

Modify table name

Alter table old table name rename new table name

Example:

Alter table stu rename student; / / rename the stu table to student

Alter table student engine=myisam; / / modify the storage engine of student table to myisam

Add field

Alter table table name

Add field name data type [integrity constraint.]

Add field name data type [integrity constraint.]

By default, newly added fields are placed after all fields.

Example:

Alter table student

Add name varchar (30) not null

Add age int not null

Alter table table name

Add field name data type [integrity constraint.] First

Example:

Add stu_num varchar (30) not null first

Alter table table name

Add field name data type [integrity constraint.] After field name

Example:

Add passord varchar (30) not null after name

Delete a field

Alter table table name drop field name

Example:

Alter table student drop stu_num

Modify field

Alter table table name modify field name data type [integrity constraint.]; / / can only be renamed for old field operations

Alter table table name change old field name new field name old data type [integrity constraint.]

Usually use this.

Example:

Alter table student change name stu_name varchar (30)

Alter table table name change old field name new field name new data type [integrity constraint.]

Example:

Alter table student change stu_name name varchar (50) not null

Delete tabl

Drop table table name

Example:

Drop table student

After reading this article on how to manipulate mysql database tables, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.