<mysql Database Operations >

<mysql Database Operations >

1. Create a database.

The CREATE DATABASE statement is created in MySQL. The format is as follows:

　　　　　　　　　　　　CREATE database db_name;

Db_name is the name of the database and must be a valid name. The provisions are as follows:

A. You cannot duplicate the name of another database.

B. The name can be any letter, Arabic numerals, underscores, or "$". You can start with any of these characters, but you can't use separate numbers, which can cause confusion with numbers.

C. Names can be up to 64 characters long (including tables, columns, and indexes), and aliases can be up to 256 characters in length.

D. You cannot use the MySQL keyword as the database name and table name.

PS: The execution process: when the database is created, the first connection to the MySQL server, the user name is root, the password is root, and then write "CREATE Database db_name;" SQL statement, the database was created successfully.

2. Select the database.

Use statements are used to select a database to become the current default database. The format is as follows:

 use db_name;

　　　　

3. Delete the database.

Delete the database using the Drop DB statement. The format is as follows:

　　　　　　drop Database db_name

PS. You should use caution for deleting databases. Once all the structures and data in the database are deleted, there is no possibility of recovery unless there is a backup in the database.

<mysql Database Tables >

Above will finish the operation of the database, the following is the database, the operation of the table. MySQL database table operations include creating, viewing, modifying, renaming, and deleting.

1. Create a table.

Create tables using the CREATE TABLE statement. The format is as follows:

 　　　　　　Create Table [If not EXISTS] data table name [(Create_definition,...)] [Table_options] [Select_statement]

A. Temporary, if you use this keyword, it means creating a temporary table.

B. If not exists, the keyword is used to avoid errors reported by MySQL when tables are created.

C. create_definition, which is the list Properties section of the table. MySQL requires at least one column to be included in the table when the table is created.

The create_deifnition format is as follows:

　　　　　　　　　　　　　　Col_name type [NOT NULL] [default Default_value] [auto_increment] [primary key] [reference_definition]

Col_name: Field name. Type: field type. Not null|null: Indicates whether null values are allowed for this column. Not NULL means null values are not allowed. Default Default_value: Represents the defaults.

Auto_increament: Represents the default value. Primary key: Indicates whether the primary key can only have one primary key in a table. If there is no primary key, and some applications require primary key,

The server returns the first unique key without a null column as primary key.

D. table_option, some characteristic parameters of the table.

E. Select_statement,select statement Description section, which allows you to quickly create a table.

2. View the table.

For a table that is created successfully, use the show columns statement or the describe statement to view the table structure of the specified data table. The format is as follows:

Show Columns statement:

 Showcolumns from data table name [from database name];                Or showcolumns from data table name　　　　　　 . database name;

Describe statement, describe can be abbreviated to DESC.

　　　　　　describe data table name;
　　　　　　　　   Or
　　　　　　describe data table name;  

3. Modify the table.

Modify the table structure by using the ALTER TABLE statement. Among other things, modifying the table structure includes adding or removing fields, modifying field names or field types, setting/canceling primary or foreign keys, setting/canceling indexes, and modifying table annotations. The format is as follows:

　　　　　　Alter table data table name Alter_spec[,alter_spec] ....

If the parameter ignore is specified, only one row is executed when duplicate rows occur, and the other duplicate rows are deleted.

Where the ALTER_SPEC clause defines what to modify, in the following format:

Alter_specification:add [column] create_definition [ first| After column_name],//Add New FieldAdd index [index_name] (Index_col_name,....),//Add index nameAdd PrimaryKey(Index_col_name,...),//Add a primary key nameAdd unique [index_name] (Index_col_name,...),//Add a unique indexalter [column] Col_name {SETDEFAULTliteral | DROPDEFAULT},//Modify Field namesChange [column] Old_col_name create_definition//modifying field TypesModify [column] create_definition,//Modify a field defined by a sentenceDrop [column] col_name,//Delete Field namesDrop PrimaryKey,//Remove primary Key nameDrop IndEX index_name,//Remove index name        Rename[ as] New_tbl_name,//Change table nameTable_options

The ALTER TABLE statement allows multiple actions to be made, separated by commas, and each action represents a modification to the table. Modifying a table column by using ALTER requires that all data in the table be deleted.

　　　　

4. Rename the table.

Rename the table using the Rename Table statement. The format is as follows:

Rename　　　　  to data table name 2; // The Rename Table statement can rename multiple data tables at the same time, separating multiple tables with commas.    

　　　

5. Delete a table

Delete the data table using the Statement drop table. The format is as follows:

drop table data table name;    or   if exists// prevents deletion of tables that do not exist, resulting in an error.

Deleting a data table will also cause data that is not backed up to be unrecoverable.

PS: When performing any of the operations in Create Table,alter table and drop table, you must first select the database or you will not be able to manipulate the data table.

<mysql data in Data sheets >

The data in the data table is summarized below. How to better manipulate and use this data is fundamental to using MySQL database.

1. Add (insert) data.

After you have created the database and the data table, you add data to the table. There are three main ways to add data:

A. List all the values for all newly added data.

　　　　　　Insert into Values (Value1,value2,.....); /Statement disadvantage when there are too many columns, it is not easy to know the matching values clearly.

B. Give the column to which you want to assign the value, and then give the value.

　　　　　　Insert into Values (Value1,value2,....); /shortcomings Ibid.

C. Use the form of col_name =value to give the dequeue and value.

　　　　　　Insert into set column_name1 = value1, column_name2 = value2, ....//compensate for the above shortcomings, but cause the statement to be too long.

　　

/***** adding data in bulk. begin****/

Bulk additions to the data are implemented using the load data and Mysqlimport statements.

Load data can be added to a database by reading a large number of files on the local file system. The format is as follows:

Load Data local infile into table table_name;

The Mysqlimport Statement implementation program reads bulk data directly from a file. It is equivalent to an interface of the load data statement. The format is as follows:

　　%mysqlimport-local table_name filename.txt;

Mysqlimport can automatically generate a load data statement that loads the data from the Filename.txt file into the table_name table. Mysqlimport the character in the file name before the first dot in the filename as the new table name, and import the data from the file into the new table. such as the file name Com.youfilename.txt. The data will then be imported into the table COM.

/***** adding data in bulk. end****/

　　

2. Modify the data.

Modify the data using the UPDATE statement. The format is as follows:

　　　　Update Set where condition;

Condition is a conditional statement, such as User_name= ' Zhang San '. The accuracy of the condition condition must be guaranteed, or it will cause the data in the table to be destroyed.

　　3. Delete the data.

Delete data using the DELETE statement. The format is as follows:

　　　　Delete from where condition;

PS. A. When you delete a piece of data, you typically select the data ID as a condition to avoid unnecessary errors.

B. Delete operations are not recommended when the data for the entire table is deleted due to efficiency issues. You can use the TRUNCATE statement, which can quickly delete everything in the table.

4. Query the data.

Querying data from a database table is an important part of manipulating data, as well as displaying it. This will be explained in detail below.

First, use the SELECT statement for data queries on MySQL database tables. The format is as follows:

SelectSelection_list//to query the content, select the column for the query. fromTable_list//from what table, where to select the row.wherePrimaryz_constraint//the condition that needs to be met when querying.Group  byGrouping_columns//How query results are grouped.Order bySorting_columns//How to sort the results havingSecondary_constraint//the second condition that is met when querying.Limit Count //The result of qualifying the output.

A. select_list indicates what to query. If you want to query all the columns in the table, you can use "*". If you query multiple columns, you can enter the column names directly and use the "," delimited.

B. table_list queries from the specified table. You can query from a table, you can query from multiple tables, a multi-table query uses ",", and a join operation symbol in the WHERE clause to determine the relationship between the table and the table. When using multiple table queries, if the table has the same fields, To tell the server the field information in the table that you want to display, you need to include the table name in front of the field. The format is as follows:

// table name. Field name .

Use the "=" symbol to concatenate a table, called an equivalent connection. For example, tb_student.name = Tb_gradeone.name; If you do not use the equals sign connection, the result will be a Cartesian product of two tables called full join.

The C.where conditional statement is used to obtain the corresponding information by the appropriate conditions. The format is columns_name comparison operator value.

D. Group by implements grouping queries by dividing and grouping the data that is being queried. At query time, the queried columns must be included in the grouped columns in order to make the queried data non-contradictory. When used with the AVG () or sum () function, the GROUP by Statement can play the most important role.

E. Use distinct to remove duplicate rows in the results.

F.order by. Use order by to sort the results in ascending and descending order (DESC), and by default, order by will output the results in ascending order. If you want to descending, you can use DESC. When sorting columns that contain null values, the null value is ranked first, descending , and Null is the last.

G.like fuzzy query. Like is a more commonly used operator, through which you can implement a fuzzy query, he has two wildcard characters, "%" and an underscore "_". " % "can be implemented to match one or more characters, while" _ "matches only one character.
H. Using concat to combine multiple columns. Using the CONCAT function, you can federate several fields to form a total string. For example, merge the title with the price and use as as an alias for the field.

Select  as BookInfo form Tb_mkbook;

I. Use limit to qualify the result function. Using the limit sentence allows you to limit the number of records in the result to control the number of rows it outputs. For example: Limit 3--Indicates that 3 data is displayed. The Limit 5,10---means starting with the record numbered 5 and reading down to 10 data for display.

J. function expressions. Commonly used statistical functions are:

AVG (columns_name);//Gets the average of the specified column.

Count (columns_name);//Statistics The number of columns that make a non-empty record of the column. Add distinct to limit keywords, the number of different values is counted, and the same value is considered a record. COUNT (*), the count contains the number of empty records.

Max (columns_name) or min (columns_name);//Gets the maximum/minimum value of the specified field.

STD (columns_name) or Stdtev (columns_name);//The standard deviation value of the specified field.

SUM (columns_name);//Specify the sum of the values for all records in the field.

PS. Common data types are numeric types, string types, time and date types. For details, please refer to PHP related knowledge and documentation.

<mysql database Operation Steps .>

The following is a summary of the MySQL database structure hierarchy, which describes the procedures for the database.

1. Connect to the MySQL server.

Use the mysql_connect () function to create a connection to the MySQL server. The method format is as follows:

<? PHP     $conn mysql_connect (' hostname ', ' username ', ' password ') or Die ("Database server Connection Failed". Mysql_error ());                 // Hostname:mysql the host name or IP of the server. If the port number is omitted, the default is 3306;                Username: Log on to the MySQL database server name                //password:mysql The user password for the server.
The return value of the function changes the connection to the database and, if successful, returns a resource .
if ($conn) {
//Server Connection Successful
       }

?>            

As can be known from the above, you can specify a non-native machine name as a database server, which provides security for offsite storage of databases and secure isolation of databases. Outside users often through the WWW server directly access permissions, if the database is placed directly on the WWW server, will bring a security risk to the MySQL database, if you install a firewall for the database system, then PHP can access the database directly over the LAN, and the local area network computer is not visible to the outside, so that the S database is not subject to foreign attacks.

To facilitate querying database connection errors, you can add the die () function to mask the error handling mechanism. MYSQL_ERROR () is used to extract the error text information, and if there is no error, the null character is returned, and an error message is displayed on the Mr Lanci if there is an error.

PS. For users, it is recommended to add the @ symbol in front of mysql_connect () to mask the error message in order to let the user see a bunch of inexplicable error messages. But for developers, not using @ During debugging allows us to quickly locate error messages.

2. Select MySQL database.

Use the mysql_select_db () function to select the database on the MySQL database server and create a connection to the database. The format is as follows:

mysql_select_db (string database name [,resource  link_identifier]);         // String database name: The name of the MySQL database to select        //resource the connection identity of the Link_identifier:mysql server.
example, the previous step.

<?php
if ($conn) {
$selected = sql_select_db ("Db_webbookstore", $conn);
if ($selected) {
　　　database connection was successful;
}
}
?>

3. Execute the SQL statement.

Execute the SQL statement using the mysql_query () function. The SQL statement operation has been described earlier and is not elaborated here. The main introduction to the mysql_query () function, in the following format:

$result mysql_query (string SQL [,resource  link_identifier]);     // if the SQL statement is a query statement, SUCCESS returns the result set, otherwise it returns false    //If the SQL statement is inserted, deleted, or updated, success returns True, otherwise False.//ps is returned. The change function can also be used to select a database, and to set the database encoding format. such as: mysql_query (' User db_database13 ', $conn);//    mysql_query (' Set names UTF8 ');

Increase: Mysql_unbuffered_query (), as the name implies, change the function to not cache the result query. It sends only one SQL query statement to the server, but does not fetch and cache the resulting rows. It's not like mysql_query. The result set is automatically fetched and cached. The advantage is that When working with large result sets, you save objective memory, and on the other hand, you can manipulate the result set as soon as you get the first row of data without waiting for the entire SQL statement to complete.

The Mysql_fetch_array () function is used to return the result collection to the array. The format is:

$array Mysql_fetch_array (resource result [,int result_type]);     // resource Result: The parameter of the resource type to pass in the resource data pointer returned by mysql_query ().    int result_type: Optional, the integer parameter to pass in, can be: MYSQL_ASSOC (associative index), mysql_num (numeric index), Mysql_both (both of which contain the first two). The default is Mysql_both.
Note that the field names returned by this function are case-sensitive.

The Mysql_fetch_row () function, which gets a row from the result set as an enumerated array. The format is:

$array Mysql_fetch_row (resource  result);     // generates an array based on the row data obtained. Returns false    if there are no more rows. The array offset subscript starts at 0.    The field names returned by this function are case sensitive.

This function can use only numeric indexes, and mysql_fetch_array () can both be used. For example: Numeric index: $array [0], Associated index: $array [Type];

The mysql_num_rows () function, which gets the number of records in the query result set. The format is:

int  mysql_num_rows(resource result);
Returns the number of rows in the result set. This command is valid only for SELECT statements. To get the number of data set rows affected by the operation of the other SQL statements, you need to use Mysql_affected_rows ()

4. After the data is executed, you need to close the result set to release the resource. The syntax is as follows:

Mysql_free_result (resource result);

If you are accessing the database frequently in a Web page, you can increase efficiency by creating a persistent connection to the server database. This avoids the long requests and the large resource overhead that each connection server requests. Persistent connection database using Mysql_pconnect () function to replace the mysql_connect () function. Creates a persistent connection that will not call Mysql_close () to close the database request until the program ends. When you call Mysql_pconnect () again to connect to the database, Instead of recreating the database connection, the server returns the ID number of the persistent connection that was created.

5. Close the MySQL server.

system resources are consumed every time you use Mysql_connect () and mysql_query (). When a small number of users visit the Web site is not a problem, but when a large number of users connected more than a certain amount, it will cause system performance degradation, or even panic. To avoid this situation, After you have completed the database operation, you should call the change function to close the connection to the MySQL server to conserve system resources. The format is as follows:

Mysql_close ($conn);

The connection to the database in the ps.php is non-persistent, and the system is automatically recycled, generally without setting the shutdown. However, if the result set returned at one time is larger, or if there is more site traffic, it is best to manually release it using the change function.

　　

Improve:

Database garbled problem, use the mysql_query () function to set the database encoding. The encoding format suggests using UTF-8. If you use gbk2312, if the user does not have Chinese encoding installed (such as some Americas, European users of the Machine View Chinese site), the output will result in garbled characters. Utf-8 The use of a wider, more portable, and more international support.

　　

/************************************ I'm a sexy split-line ******************************/

Ps:

Ah, finally completed the change part of the study. The intention is to use weekends to learn part of the week, and the results are always delayed due to their inertia. Tomorrow to take a holiday, ready to attend a high school classmate wedding, had to use such a little time to complete. Think about this year, the wedding is really a lot of. We are all at this age, or a lonely wretch (^.^).

See you in the evening. < social network >,facebook The birth of the original process is like this. Really feel like doing one thing, you need to focus. One more things, the thought of the immediate to do, we often for ourselves to find such an excuse, so as to escape the trouble, It is difficult to turn back. But the key to success is often a bit of action.

<mysql Database Operations >