Summary of common operation instructions for MySQL additions and deletions

Summary: 1, database operation: Create library: Creation database db_name; query library: show databases; Show all databases show create databases db_name;    To display a specific database deletion library: Drop db db_name; Modify library: Alter DATABASE db_name [Modify command];    ALTER DATABASE db_name character set gbk;2, table operation: CREATE TABLE: Use Db_name; CREATE TABLE Tbl_name (column_structure) [tbl_option]; For example: Create TABLE Tbl_name (variable_name1 varchar) Variab       le_name2 int), or CREATE TABLE db_name.tbl_name (variable_name1 varchar) variable_name2 int); query table: See which tables are available: Show tables; or show tables like ' pattern_% '; View table creation information: show create TABLE tbl_name; Or show create TABLE Tbl_name \g View the structure of the table: describe tbl_name; or desc tbl_name;] Delete tables: drop table [if exists] tbl_name; Modify table: Modify Table Name: Single: Rename table Old_tbl_name to new_tbl_name; multiple: Rename table Old_tbl_name 1 to New_tbl_name1, old_tbl_name2 to new_tbl_name2; cross-database: Rename table Old_tbl_name to Db_name.new_tbl_name;  Modify Column Definition: Add new Column definition: Add ALTER TABLE tbl_name add New_column_name data_type; such as ALTER TABLE Exam_student add height int; Modify the definition of a column (new property or data type): Modify ALTER TABLE tbl_name modify column_name new_data_type; Delete a column: Drop ALTER TABLE tbl_name drop column_name; Rename a column: Change ALTER TABLE tbl_name change Old_column_name new_column_name new_d    Ata_type; Modify table options: Alter-table Tbl_name new_tbl_option; ALTER TABLE Tbl_name Character set utf8;3, data manipulation: Create data: INSERT into Tbl_name (fields_list) VALUES (values_list), such as: Insert I Nto exam_student (Stu_name, Stu_no) VALUES (' Xiaoming ', ' php030_01 '); Insert all field values: INSERT into exam_student values (' Xiaoming ', ' php030_01 ', 98); Query data: View field information: SELECT * from Tbl_name; see specific field List information: Select Fields_list from tbl_name where condition ; For example: Select Stu_name, stu_no from Exam_student, or: Select Stu_name, Stu_no from exam_student where 1; conditional query: SELECT * FROM  Tbl_name where condition, such as SELECT * from Exam_student where Fenshu >= 60; Delete data: Delete from tbl_name condition, such as delete From Exam_student where Fenshu <= 50; Modify data: Update tbl_name SET field = New_value WHere condition: Update exam_student set Fenshu =100 where Fenshu >= 97;4, PRIMARY key: Primary such as: Create TABLE teacher (t_id int Primary key [Auto_increment],t_name varchar (5), class_name varchar (6), t_days tinyint unsigned); Law II: Create TABLE Teacher ( t_id int,t_name varchar (5), class_name varchar (6), T_day tinyint unsigned,primary key (t_id)); INSERT into teacher values (1 , ' Miss Wang ', ' 0225 ', 23); autogrow: auto_increment ALTER TABLE Tbl_name auto_increment initial_value;5, foreign key: foreign key: Main Table: Create Table Itcast_class (class_id int primary key auto_increment,class_name varchar (TEN) NOT null default ' itcast_php ' Comment ' class Class name ') character set utf8; from table: Create TABLE itcast_student (stu_id int primary key auto_increment,stu_name varchar) not nul L Default ', class_id int,foreign key (class_id) references Itcast_class (class_id)) character set UTF8; Insert data in order: Guthrie table, after table in SERT into itcast_class values (null, ' php0331 '); insert into itcast_student values (null, ' Zhang San ', 1); Delete operation: ALTER TABLE Itcast_ Student Drop FOREIGN Key ItcasT_student_ibfk_1;alter table itcast_student Add foreign key (class_id) references Itcast_class (class_id) on delete set Nu ll;/* The main table to delete a field, from the table if the foreign key is changed to null*/or on delete cascade;/* main tables Delete a field, the foreign key from the table will delete the corresponding value */or on update restrict;/* does not allow the main table for update operations */delete from itcast_class where class_id = 1;

　　

Summary of common operation instructions for MySQL additions and deletions