How MySQL operates data tables-Mysql Tutorial-php.cn

How MySQL operates data tables

醉折花枝作酒筹

Release： 2021-06-29 09:22:07

forward

1853 people have browsed it

The method of operating data tables in MySQL is not complicated. The following will introduce you in detail to the implementation methods of adding fields, modifying fields, deleting fields, and obtaining table names in MYSQL. I hope it will be helpful for you to learn to add fields in MySQL. .

How MySQL operates data tables

The method of adding fields in MySQL is not complicated. The following will introduce you in detail to the implementation methods of adding fields and modifying fields in MYSQL. I hope it will help you learn about adding fields in MySQL. will help.

1Add table fields

alter table table1 add transactor varchar(10) not Null;
alter table   table1 add id int unsigned not Null auto_increment primary key

Copy after login

Examples of statements added after specific fields:

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
ALTER TABLE MyTableName ADD newDBField varchar(30) NULL AFTER existDBField;
ALTER TABLE tableName001 ADD WebAdminPass varchar(30) NULL AFTER Result;

Copy after login

2. Modify the field type of a table and specify it as empty or non-empty

alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

Copy after login

3. Modify the field name of a table and specify it as empty or non-empty

alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空

Copy after login

4 If you want to delete a field, you can use the command:

ALTER TABLE mytable DROP 字段名;

Copy after login

mysql SQL to obtain Query statement for table name & field name

1: Query all table names in the database

select table_name
　　from information_schema.tables
　　where table_schema=&#39;csdb&#39; and table_type=&#39;base table&#39;;
table_schema：数据库名称     
information_schema 表示系统库。 　　
table_type=&#39;base table‘：限定只查询基表。

Copy after login

2: Query all field names column_name

 　 select column_name
     from information_schema.columns
     where table_schema=&#39;csdb&#39; and table_name=&#39;users&#39;；
　　table_schema：数据库名 　　
　　table_name：表名

Copy after login

# of the specified table in the specified database ##Work examples:

select count(*) from information_schema.columns where table_schema=&#39;yanfa&#39; and table_name=&#39;tableName001&#39; and column_name=&#39;Result1&#39;;
 #select table_name from information_schema.tables where table_schema=&#39;yanfa&#39; and table_type=&#39;base table&#39;;

Copy after login

Related learning recommendations:

mysql tutorial(Video)

The above is the detailed content of How MySQL operates data tables. For more information, please follow other related articles on the PHP Chinese website!