Summary of SQL statements on table operations in MySQL database

--Create a database

Create DATABASE School

--Open 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, add column (field)

ALTER TABLE student

Add Tel varchar (20)

--Delete column (field)

ALTER TABLE student

Drop Column Bak

--Property modification

--Modify Column name (field name)

exec sp_rename ' student.sex ', ' sex2 '

--Modify Type

ALTER TABLE student

Alter Column Age char (20)

--Delete Table

DROP TABLE Student

-------------------------------table (structure):--------------------------------------------

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

---data integrity: PRIMARY KEY constraints, UNIQUE constraints, CHECK constraints, default constraints, FOREIGN KEY constraints

CREATE TABLE Biao

(

ID int PRIMARY KEY,

Name varchar (20),

Sex char (2) Check (sex= ' man ' or sex= ' female '),

Age int,

Date datetime,

Info text,

Tel Char Unique,

Bak varchar (+) 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 primary key

ALTER TABLE Grade

Add constraint AA primary key (ID)

---add a uniqueness Constraint

ALTER TABLE Grade

Add constraint BB Unique (tel)

---view constraints

EXEC sp_helpconstraint Grade

---add a check constraint

ALTER TABLE Grade

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

---add a DEFAULT constraint

ALTER TABLE Grade

Add constraint CCC Default ' I'm a good student ' for Bak

---delete constraint

ALTER TABLE Grade

Drop constraint CCC

-----------------------------formatting to add constraints------------------------------------------

---ALTER TABLE name

---add constraint constraint name (alias (optional)) constraint keyword

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

CREATE TABLE SHOP_JB

(

ID int PRIMARY KEY,

Namel varchar (20),

Spec varchar (20),

The stock int,

Price float,

Datel datetime default ' 2010-7-6 '

)

CREATE TABLE Shop_yw

(

Ywid int PRIMARY KEY,

name2 varchar (20),

Sex char (2) Check (sex= ' man ' or sex= ' female '),

Age int,

Tel varchar () Unique,

Address varchar (20)

)

CREATE TABLE Shop_xs

(

ID int NOT NULL,

Sale char (20),

Quantity char (20),

Date2 datetime default ' 2010-5-3 ',

Ywid int

FOREIGN key (ID) references SHOP_JB,

Foreign KEY (Ywid) references Shop_yw

)

For example:

Modify the field birth in the table Expert_info to allow it to be empty

>alter table Expert_info Change birth birth varchar (a) null;

1. Add one field (one column)

ALTER TABLE table_name ADD COLUMN column_name type default value; Type refers to the kind of field that is the default value of the field

For example: ALTER TABLE mybook add column publish_house varchar (a) default ';

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

ALTER TABLE table_name change SORCE_COL_NAME dest_col_name type default value; Source_col_name refers to the original field name, Dest_col_name

The name of the field after the change

For 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 column_name type;

For example: ALTER TABLE userinfo change column username 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;

For example, ALTER TABLE book add column ID int (a) auto_increment PRIMARY KEY;

6. Backup of a table in the database, enter at the command line:

MYSQLDUMP-U ROOT-P database_name table_name > Bak_file_name

For example: Mysqldump-u root-p f_info user_info > User_info.dat

7. Export Data

Select_statment into outfile "Dest_file";

For example: Select Cooperatecode,createtime from publish limit to 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. Insert the data from the two table to the other table. The following example shows the values of the COM1 fields in the COM2 and T2 tables in the T1 table are spliced and inserted into the TX table corresponding to the

In the field.

For example: INSERT INTO TX select T1.com1,concat (T1.COM2,T2.COM1) from T1,t2;

10, delete the field

ALTER TABLE Form1 drop column name;

Add one:

PHP operations MySQL Add a column to the table

In a database that has already been established, a new field in a table that already has a field can be used in the following ways:

mysql_query ("ALTER table" table name ' ADD ' field ' field type ') or Die (Mysql_error ());

For example, add a field to a table article keywords

Code:

code is as follows	&nbs P;
<?php $link = mysql_connect ($servername, $dbusername, $dbpassword ); if (mysql_select_db ($dbname)) { if ($link) { echo ' connect succeed '; mysql_query (' ALTER TABLE ' article ' A DD ' keywords ' varchar (m) Not NULL default "") or Die (Mysql_error ()); echo "Add succeed"; } else { echo "connect Failed"; } Mysql_close ($link); }