Shulou(Shulou.com)05/31 Report--

This article mainly introduces how MySQL manipulates table data on the command line, which can be used for reference by interested friends. I hope you will gain a lot after reading this article.

1. Compared with the interface operation, the operation through SQL statement is more flexible and powerful.

In order to enable the customer service to recognize Chinese characters:

Set character_set_client=gb2312

In order to keep the results from garbled:

Set character_set_results=gb2312

Www.2cto.com

two。 Insert record command

Once the database and table are created, the next step is to insert data into the table. You can insert one or more rows of data into a table through insert or replace statements. (the use of the replace statement is basically the same as insert. Using replace, you can delete the old record that conflicts with the new record before inserting the data, so that the new record can be inserted normally.)

Insert into table name (field name 1, field name 2.)

Values (value 1, value 2.)

For example:

Insert into xs (student number, name, major name, total credits, photos, remarks) values ('081101 recording' Wang Lin', 'computer', 50 null null)

Or: (if the number and order of values provided are the same as the fields in the table, you can omit the field name)

Insert into xs values (081101 recording 'Wang Lin', 'computer', 50 recording nullpencil null)

Or: (if you insert values into only some of the fields in the table, specify the field name)

Insert into xs (student number, name, major name, date of birth, total credits)

Values ('081101 recording journal' Wang Lin', 'computer', '1990-02-10 recording journal 50)

If you insert data into only some of the columns of the table, you need to specify those columns. For columns that are not indicated, their values are determined based on column default values or related properties, and the principle of MySQL processing is:

3. Insert syntax format:

Insert [low_priority | delayed | high_priority] [ignore]

[into] Table name [(field name,...)] Www.2cto.com

Values ({expr | default},...), (...),...

| SET col_name= {expr | default},...

[on duplicate keyupdate=expr,...]

(1) for a column with an identity attribute, the system generates an ordinal value to uniquely identify the column.

(2) columns with default values, whose values are default values.

(3) if a column without a default value is allowed to be null, its value is null; if null is not allowed, an error occurs.

(4) for columns of type timestamp, the system automatically assigns values.

Values clause: contains the list of data that each column needs to insert, and the order of the data corresponds to the order of the columns. If the column name is not given after the table name, the value of each column (except for columns of type identity and timestamp) should be given in the values clause. If the column value is empty, the value must be set to null, otherwise an error will occur. The value in the values clause:

(1) expr: it can be a constant, a variable, or an expression, or a null value null, whose data type should be the same as that of the column. For example, an error occurs if the data type of the column is int and the inserted data is' aaa'. Enclose the data in single quotation marks when the data is character type.

(2) default: specified as the default value for this column. Provided that the column has previously specified a default value. If both the list and the values list are empty, insert creates a row, with each column set to the default value.

The Inert statement supports the following modifiers: www.2cto.com

Low_priority: can be used in operations such as insert, delete, and update to delay the execution of the operation when the original client is reading data until no other client reads from the table.

Delayed: if you use this keyword, the server will put the rows to be inserted into a buffer, and the client that sends the insert delayed statement will continue to run. If the table is in use, the server retains these rows. When the table is idle, the server starts inserting rows and periodically checks for new read requests (for myisam, memory, and archive tables only).

High_priority: can be used in selectT and insert operations to give priority to operations.

Ignore: using this keyword, errors that occur during the execution of the statement are treated as warnings.

Onduplicate key update... After inserting a row using this option, if it causes duplicate values in uniqe key or primary key, the old row is modified according to the statement after update (delayed is ignored when using this option).

Set clause: the set clause is used to assign a value to a column, and the column name is omitted after the table name when using the set clause. The column name to insert data is specified in the set clause, col_name is the specified column name, the equal sign is followed by the specified data, the unspecified column, and the column value is specified as the default value.

4. Using the insert statement, you can insert a row of data into a table, or you can insert multiple rows of data, and the inserted rows can give values for each column or only part of the columns, and you can also insert data from other tables into the table.

Use insert into... Select... You can quickly insert multiple rows into a table from one or more tables The syntax format is as follows:

Insert [low_priority | delayed | high_priority] [ignore]

[into] Table name [(field name,...)]

Select...

[on duplicate key update=expr,...]

What is returned in the Select statement is a query result set, and the insert statement inserts the result set into the specified table, but the number of fields in each row and the data type of the field must be exactly the same as the table being manipulated.

MySQL also supports the storage of pictures, which can generally be stored in the form of a path, that is, inserting a picture can take the storage path of inserting a picture directly. Of course, you can also insert the picture itself directly, as long as you use the load_file function.

For example:

Insert into XS values ('081102,' Cheng Ming', 'computer', 1, '1991-02-01) 50,' D:\ IMAGE\ picture.jpg', null)

The following statement stores the picture itself directly:

Insert into XS

Values ('081102,' Cheng Ming', 'computer', 1, '1991-02-01) 50, load_file (' D:\ IMAGE\ picture.jpg'), null)

Set the primary key after the table is built, duplicateentry 'student number' for key 1

5. Delete record: www.2cto.com

Delete from Table name / / Delete all records in the table, this table becomes empty

Delete from Table name where condition / / Delete eligible records in the table

For example: delete from xs where student number = '081102'

Drop table xs; / / Delete the entire table, structure and records

Truncate table table name / / quickly delete all records in the table

Remove from a single table, syntax format:

Delete [low_priority] [quick] [ignore] from table name

[where where_definition] [orderby...] [limit row_count]

Description:

● quick modifier: can speed up some kinds of delete operations.

● from clause: used to indicate where to delete the data, followed by the name of the table from which you want to delete the data.

● where clause: the content in where_definition is the specified deletion condition. If you omit the where clause, all rows of the table are deleted, as described later in the where clause

● order by clause: lines are deleted in the order specified in the clause, which works only when used in conjunction with limit.

● limit clause: tells the server the maximum value of the row that is deleted before the control command is returned to the client.

6. Delete rows from multiple tables, syntax format:

Delete [low_priority] [quick] [ignore] Table name [. *] [, table name [. *].] From table_references [wherewhere_definition]

Or:

Delete [low_priority] [quick] [ignore]

From tbl_name [. *] [, tbl_name [. *].]

Using table_references [where where_definition]

Note: for the first syntax, only the corresponding rows in the table listed before the from clause are deleted. For the second syntax, only the corresponding rows in the table listed in the from clause (before the using clause) are deleted. The effect is that rows in multiple tables can be deleted at the same time and searched using other tables.

Example:

Suppose you have three tables T1, T2, and T3, all of which contain id columns. To delete all rows in T1 where the id value is equal to the id value of T2 and all rows in T2 where the id value is equal to the id value of T3, use the following statement:

Delete t1, t2

From t1, t2, t3

Where t1.id=t2.id and t2.id=t3.id

Or: www.2cto.com

Delete from t1, t2

Using t1, t2, t3

Where t1.id=t2.id and t2.id=t3.id

7. Using the truncate table statement deletes all data in the specified table, so it is also called a purge table data statement.

Syntax format:

Truncate table table name

Note: because the TRUNCATETABLE statement deletes all data in the table and cannot be recovered, you must be very careful when using it.

Truncate table is functionally the same as a delete statement without a where clause, and both delete all rows in the table. However, truncate table is faster than delete and uses fewer system and transaction log resources. The delete statement deletes one row at a time and records one entry for each row deleted in the transaction log. Truncate table deletes the data by releasing the data pages used to store the table data, and only records the release of the pages in the transaction log. Using the truncate table,auto_increment counter is reset to the initial value of the column.

Note: for tables that participate in indexes and views, you cannot use truncate table to delete data, but instead use the delete statement.

8. Modify record (update record)

To modify a row of data in a table, you can use the update statement, and update can be used to modify one table or multiple tables. Brief format:

Update table name

Set field name 1 = value 1 [, field name 2 = value 2.]

Where condition

Modify a single table, syntax format:

Update [low _ priority] [ignore] table name

Set col_name1=expr1 [, col_name2=expr2...]

[where where_definition]

[order by...] [limit row_count]

Description:

Set clause: modifies qualified rows of data according to the conditions specified in the where clause. If the where clause is not set in the statement, all rows are updated. Col_name1, col_name2... To modify the column name of the column value, expr1, expr2... Can be constant, variable, or expression. You can modify multiple column values of the data row at the same time, separated by commas.

9. Modify multiple tables, syntax format:

Update [low_priority] [ignore] table_references

Set col_name1=expr1 [, col_name2=expr2...]

[where where_definition]

Description: table_references contains the federation of multiple tables, separated by commas.

10. Import in the format of SQL package: source SQL package path

Show the record of the table: select * from table name; www.2cto.com

When inserting records, except for numeric types without quotation marks, all other types should be enclosed in single quotation marks (such as char, date, etc.)

11. Show statement

Show tables or show tables from library name: displays the names of all tables in the current database.

Show databases: displays the names of all databases in MySQL.

Show columnsfrom table name from library name or show columnsfrom library name. Table name: displays the names of the columns in the table.

Show grants for user_name: displays the permissions of a user, which is similar to the grant command.

Show index from table_name: displays the index of the table.

Show staus: displays information about some system-specific resources, such as the number of threads running.

Show variables: displays the name and value of the system variable.

Show processlist: displays all the processes running on the system, that is, the currently executing query. Most users can view their own processes, but if they have process privileges, they can view everyone's processes, including passwords.

Show table status: displays information about each table in the currently used or specified database. The information includes the table type and the latest update time of the table.

Show privileges: displays the different permissions supported by the server.

Show create database library name: displays the create database statement that creates a database.

Show create table table name: displays the create table statement that creates a table.

Show events: displays a list of all events.

Show innoDB status: displays the status of the InnoDB storage engine.

Show logs: displays the logs of the BDB storage engine.

Show warnings: displays errors, warnings, and notifications from the last executed statement.

Show errors: only the error resulting from the last execution statement is displayed.

Show [storage] engines: displays the available storage engine and default engine after installation.

Show procedure status: displays the basic information of all stored procedures in the database, including the database, the name of the stored procedure, the time it was created, and so on.

Show create procedure sp_name: displays the details of a stored procedure.

12. Describe statement (that is, desc)

The describe statement is used to display information about the columns in the table, and the result is equal to the showcolumns from statement.

Syntax format:

{describe | desc} tb1_name [col_name | wild]

Description:

Desc is the abbreviation of describe, and they are used the same way.

Www.2cto.com

Col_name can be a column name, or a string containing wildcards of'% 'and' _'to get output for columns with names that match the string. There is no need to include a string in quotation marks unless it contains spaces or other special characters.

For example:

Display student table: desc | describe xs

Show student form student number column: desc xs student number

Note: when using a graphical interface, avoid unnecessary spaces when entering data, otherwise it may appear when retrieving data.

Thank you for reading this article carefully. I hope the article "how to manipulate table data on the command line by MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.