-- 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: