MySQL Create modify Delete datasheet Example

When you apply a database in PHP, you typically use DDL statements to create databases, datasheets, and modify table structures in your Web site in a MySQL client's console, and then apply them in your PHP scripts. It is rare to execute DDL statements dynamically in PHP to create databases, datasheets, or modify tables, and it is usually done only when the installed version of the site is made.
1. Create TABLE
After the database is created, you can continue to build the data tables it contains by using the use command to select the newly created database as the default (current) database. The creation of a datasheet is a prerequisite for using a table, which is primarily defined as the structure of the data table, including the name of the datasheet, field name, field type, constraint, and its index. Its basic syntax looks like the following:

code is as follows

copy code

create TABLE [ IF not EXISTS] Table name (       #创建带给定名称的表, must have table Create permission     field name 1 column type [Properties] [ Index],     #声明表中第一个字段, must have field name and column type     field name 2 column type [properties] [index],     ...     field name n column type [properties] [index]      #每个字段的属性和索引都是可选的, set as needed ] [table type] [table character set];           #在创建表时也可以指定可选的表类型和字符集的设置

where "[]" is optional, a table can consist of one or more fields (columns), and be sure to indicate the data type of the field after the field. Each field can also be restricted by using attributes, but the property is optional and is declared according to the needs of the table, such as the auto_increment, not NULL, default property, and so on, as described earlier. You can also define indexes for each field by PrimaryKey, UNIQUE, index, and key clauses. Indexes can be declared after each field, or in the form of a clause after a field declaration. If you have more than one column, separate them with commas. For example, to create a user information to store users, the table is specifically designed as shown in the following illustration.

When you create a table users, you need to specify a default table type of MyISAM, in addition to specifying the properties and indexes for each field, and to specify that the default creation table character set (character set) is UTF8, and the school team rule (collation) is Utf8_general_ Ci. Enter the following statement in the MySQL console to create the datasheet users.

code is as follows

copy code

CREATE TABLE IF not EXISTS users (     ID INT (a) UNSIGNED not NULL auto_increment,     user Name VARCHAR NOT NULL,     userpass VARCHAR (x) not NULL,     telno VARCHAR (20) Not null UNIQUE,     sex ENUM (' male ', ' female ') not null DEFAULT ' man ',     brithday DATE not NULL DEFAULT ' 0000-00-00 ',     PRIMARY KEY (ID),     INDEX users_username ( Username,userpass) ) engine=myisam DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

Once the datasheet is successfully created, you can view it in the MySQL console using the show TABLES command. You can also use the describe table name or DESC table name command in the MySQL console to display the table's creation structure, as shown below.
The default scenario is that the table is created into the current database. If the table already exists, or if there is no current database, or if the database does not exist, an error occurs. The table name can also be specified as the database name. Table name to create a table in a particular database. You can create a table this way, regardless of whether there is a current database. If you use the distinguished name in quotation marks, you should enclose the database and table names separately. For example, ' mydb '. ' Mytbl ' is legal, but ' mydb.mybl ' is not legal. If the table already exists, use the keyword if not exists to prevent an error from occurring.
2. Modify table (ALTER TABLE)
Modifying a table means modifying the structure of a table, in practice, when you find that a table's structure does not meet the requirements, you can use ALTER TABLE to modify the table's structure, including adding new fields, deleting existing fields, modifying column types, attributes and indexes, and even modifying table names. The syntax for modifying the table is as follows:

ALTER table name ACTION; #修改表的语法格式

Where action is the clause of ALTER TABLE, including adding a new column for the specified table, adding an index to the table, changing the specified default value, changing the column type, deleting a column, deleting the index, changing the table name, and so on. Here's a brief introduction to several common ways.
★ Add a new field for the specified datasheet, which can be implemented in the action clause using the Add keyword, which is shown in the following syntax format:
ALTER table name ADD field name < CREATE TABLE statement > [first| After column name] #为指定的表添加新列

If optional first or after is not specified, a column is added at the end of the column, otherwise a new column is added to the specified column. For example, if you add an e-mail field to the end of the users table that is created above, the command entered in the MySQL console looks like this:
Mysql>alter TABLE Users ADD email VARCHAR () not NULL;
If you want to user table users, add a new column with the real name (name) in front of the first column, the column type is a string, and the property is set to Non-null. and add a new column of height (height) after the original field Userpass, the column type is double, the property is Non-null, and the default value is 0.00. The commands entered in the MySQL console are as follows:

The code is as follows	Copy Code
Mysql>alter TABLE Users ADD name VARCHAR (a) not NULL A; Mysql>alter TABLE users ADD height DOUBLE not NULL DEFAULT ' 0.00′after userpass;

★ for the specified datasheet to change the type of the existing field, you can use the changing or modify clause. If the name of the original column is the same as the name of the new column, the change and modify function the same. The syntax format looks like this:
ALTER Table name change (MODIFY) List < build statement > #为指定的表修改列类型
If you need to modify the phone number field Telno in the user table users, change the column type varchar (20) to a numeric type int and set the default value to 0. The commands that can be entered in the MySQL console are as follows:

The code is as follows	Copy Code
Mysql>alter TABLE users MODIFY telno INT unsighed DEFAULT ' 0′; Mysql>alter teble users Change Telno telno INT UNSIGNED DEFAULT ' 0′;

The column name Telno appears in the change command two times, because the change also changes the column name in addition to changing the type, which is not possible in modify. If you want to rename Telno as phone when you change the type, you can do so by following the command.
Mysql>alter TABLE users Change Telno phone INT UNSIGNED DEFAULT ' 0′;
Changing the definition of a column with change, and explaining a complete definition that includes a column name, requires that you include the corresponding column name in the definition, even if you do not change the column name.
★ If you need to rename the specified datasheet, you can use the rename as clause to give the old table name and the new table name. The syntax format looks like this:
ALTER table Old table name RENAME as new table name #为指定的数据表重新命名
3. Delete table (drop table)
When a data table is no longer needed, you can delete it by using the SQL DROP TABLE statement. Deleting a table is much easier than creating and modifying a table by simply specifying the table name. The syntax looks like the following:

drop table [IF EXISTS] Table name # Delete data tables that are no longer in use

If you cannot determine whether a datasheet exists, delete it if it exists, or if it does not exist, you can add an if EXISTS to the droptable statement if you do not want the error to occur. As with CREATE TABLE, if exists statements are commonly used in SQL scripts that contain drop tables, and if there is no table to be deleted, the script continues to execute downward without throwing an error.