Summary of SQL statements for table operations in Mysql database

-- Create database createdatabaseschool -- Open Database useschool -- Create Table createtablestudent (idint, namevarchar (20), sexchar (2), ageint, datedatetime, infotext, bakvarchar (500 )) -- View table structure execsp_helpstudent -- modify and add columns (fields)

-- Create database school -- Open database use school -- create table student (id int, name varchar (20), sex char (2), age int, date datetime, info text, bak varchar (500) -- View table structure exec sp_help student -- modify and add columns (fields)

-- Create a database

Create database school

-- Open the database

Use school

-- Create a table

Create table student

(

Id int,

Name varchar (20 ),

Sex char (2 ),

Age int,

Date datetime,

Info text,

Bak varchar (500)

)

-- View table structure

Exec sp_help student

-- Modify and add columns (fields)

Alter table student

Add tel varchar (20)

-- Delete a column (field)

Alter table student

Drop column bak

-- Attribute Modification

-- Modify the column name (field name)

Exec sp_rename 'student. sex ', 'sex2'

-- Modify type

Alter table student

Alter column age char (20)

-- Delete a table

Drop table student

----------------------------- Table (structure ):--------------------------------------------

---- Create a table, view the table structure, modify (add columns, delete columns, attributes (such as name and age), and delete a table)

--- Data Integrity: primary key constraint, unique constraint, check constraint, default constraint, foreign key constraint

Create table biao

(

Id int primary key,

Name varchar (20 ),

Sex char (2) check (sex = 'male' or sex = 'female '),

Age int,

Date datetime,

Info text,

Tel char (16) unique,

Bak varchar (500) default 'I am a student'

)

Create table grade

(

Id int not null,

Name varchar (20 ),

Sex char (2 ),

Age int,

Date datetime,

Info text,

Bak varchar (500)

)

Alter table grade

Add tel char (16)

--- Add a primary key

Alter table grade

Add constraint aa primary key (id)

--- Add uniqueness Constraint

Alter table grade

Add constraint bb unique (tel)

--- View Constraints

Exec sp_helpconstraint grade

--- Add check Constraints

Alter table grade

Add constraint sex check (sex = 'male' or sex = 'female ')

--- Add default Constraints

Alter table grade

Add constraint ccc default 'I am a good student' for bak

--- Delete Constraints

Alter table grade

Drop constraint ccc

----------------------------- Format of adding constraints ------------------------------------------

--- Alter table name

--- Add constraint name (alias (any) constraint keyword

---- Homework, 7.28 -----

Create table shop_jb

(

Id int primary key,

Namel varchar (20 ),

Spec varchar (20 ),

Stock int,

Price float,

Datel datetime default '2017-7-6'

)

Create table shop_yw

(

Ywid int primary key,

Name2 varchar (20 ),

Sex char (2) check (sex = 'male' or sex = 'female '),

Age int,

Tel varchar (18) unique,

Address varchar (20)

)

Create table shop_xs

(

Id int not null,

Sale char (20 ),

Quantity char (20 ),

Date2 datetime default '2017-5-3 ',

Ywid int

Foreign key (id) references shop_jb,

Foreign key (ywid) references shop_yw

)

For example:

Modify the birth field in Table expert_info. It is allowed to be empty.

> Alter table expert_info change birth varchar (20) null;

1. Add a field (one column)

Alter table table_name add column column_name type default value; type indicates the type of the field, and value indicates the default value of the field.

Example: alter table mybook add column publish_house varchar (10) default '';

2. Change a field name (you can also change the type and default value)

Alter table table_name change sorce_col_name dest_col_name type default value; source_col_name indicates the original field name, dest_col_name

Indicates the name of the modified field.

Example: alter table Board_Info change IsMobile IsTelphone int (3) unsigned default 1;

3. Change the default value of a field

Alter table table_name alter column_name set default value;

For example, alter table book alter flag set default '0 ';

4. Change the Data Type of a field

Alter table table_name change column column_name type;

For example, alter table userinfo change column username varchar (20 );

5. Add a column to a table as the primary key.

Alter table table_name add column column_name type auto_increment primary key;

Example: alter table book add column id int (10) auto_increment primary key;

6. For backup of a table in the database, enter:

Mysqldump-u root-p database_name table_name> bak_file_name

Example: mysqldump-u root-p f_info user_info> user_info.dat

7. Export data

Select_statment into outfile "dest_file ";

Example: select cooperatecode, createtime from publish limit 10 into outfile "/home/mzc/temp/tempbad.txt ";

8. Import Data

Load data infile "file_name" into table table_name;

For example, load data infile "/home/mzc/temp/tempbad.txt" into table pad;

9. splice the data in the two tables and insert them into the other table. The following example shows how to splice the values of the com2 and com1 fields in Table T1.

Field.

Example: insert into tx select t1.com1, concat (t1.com2, t2.com1) from t1, t2;

10. delete a field

Alter table form1 drop column name;

Add one:

Add a column to the table using PHP for MySQL operations

To add a new field to a table with fields in a database that has been created, you can use the following methods:

Mysql_query ("alter table" TABLE name 'add' field 'field type ") or die (mysql_error ());

For example, add the field keywords to the table article.

Code:

The Code is as follows: