MySQL experience 3 -- command line operation table data _ MySQL

MySQL experience 3-bitsCN.com 1. compared with Interface Operations, SQL statement operations are more flexible and powerful. Set character_set_client = gb2312; set character_set_results = gb2312; 2. once the database and table are created, the next step is to insert data into the table. Insert or replace statements can be used to insert one or more rows of data into a table. (Replace statements are used basically the same as insert statements. replace can be used to delete old records that conflict with the new record before data is inserted, so that the new record can be inserted normally.) insert into table name (field name 1, field name 2 ....) Values (value 1, value 2 ....); Example: Insert into xs (student ID, name, professional name, total credits, photos, remarks) values ('20170101', 'wanglin', 'computer ', 50, null, null ); or: (if the number and sequence of the provided values are the same as those in the table, you can omit the field name.) insert into xs values ('20170101', 'wanglin', 'put', 50, null, null); or: (If you only insert a value for some fields in the table, specify the field name) insert into xs (student ID, name, professional name, date of birth, total credits) values ('20140901', 'Wang line', 'computer ', '1970-02-10', 50); if you only insert data to some columns of the table, you need to specify these columns. For columns that are not pointed out, their values are determined based on the column default values or related attributes. the principles of MySQL processing are as follows: 3. insert syntax format: Insert [low_priority | delayed | high_priority] [ignore] [into] table name [(field name,...)] values ({expr | default },...), (...),... | SET col_name = {expr | default },... [on duplicate keyupdate = expr,...] (1) if the column has the identity attribute, the system generates the sequence number value to uniquely identify the column. (2) If a column has a default value, its value is the default value. (3) a column without a default value. if it is allowed to be null, its value is null. if it is not allowed to be null, an error occurs. (4) columns of the timestamp type are automatically assigned values. Values clause: contains the list of data to be inserted in each column. the data sequence corresponds to the column sequence. If no column name is given after the table name, the value of each column (except the identity and timestamp columns) is given in the values clause. if the column value is empty, the value must be set to null. Otherwise, an error occurs. Value in the values clause: (1) expr: it can be a constant, variable, or expression, or a null value. The data type of the value must be the same as that of the column. For example, if the data type of a column is int and the inserted data is 'AAA', an error occurs. When the data type is dense, it must be enclosed in single quotes. (2) default: specifies the default value for this column. The premise is that the default value has been specified for this column. If the column list and the values list are empty, insert creates a row, and each column is set to the default value. The Inert statement supports the following modifier: low_priority: it can be used in insert, delete, update, and other operations. when the original client is reading data, the operation is delayed, until no other clients read from the table. Delayed: if this keyword is used, the server puts the row to be inserted into a buffer, and the client sending the insert delayed statement continues to run. If the table is in use, the server retains the rows. When the table is idle, the server inserts rows and regularly checks whether there are new read requests (only for myisam, memory, and archive tables ). High_priority: it can be used in selectT and insert operations to give priority to operations. Ignore: when this keyword is used, errors that occur during statement execution are treated as warnings. Onduplicate key update...: When this option is used to insert rows, if the uniqe key or primary key has duplicate values, modify the old row according to the updated statement (delayed is ignored when this option is used ). Set clause: the set clause is used to specify a value for a column. when the set clause is used, the column names are omitted after the table name. The column name to insert data is specified in the set clause, col_name is the specified column name, and the equal sign is followed by the specified data. the column value is specified as the default value. 4. you can use the insert statement to insert a row of data to a table or multiple rows of data. the inserted row can give the values of each column or only the values of some columns, you can also insert data from other tables into the table. Use insert... Select ..., You can quickly insert multiple rows into a table from one or more tables. Syntax format: Insert [low_priority | delayed | high_priority] [ignore] [into] table name [(field name,...)] select... [on duplicate key update = expr,...] the Select statement returns a query result set. the insert statement inserts the result set into the specified table, however, the number of fields in each row of the result set and the data type of the field must be exactly the same as that in the operated table. MySQL also supports image storage. images can generally be stored as paths, that is, they can be directly inserted into the storage path. You can also directly insert the image itself, as long as you use the load_file function. For example: Insert into XS values ('20170101', 'chengming', 'computer ', 1, '2017-02-01', 50, 'd:/IMAGE/picture.jpg ', null); the following statement directly stores the image itself: Insert into XS values ('000000', 'chengming', 'computer ', 1, '2017-02-01', 50, load_file ('d:/IMAGE/picture.jpg), null); after the table is created, set the primary key, duplicateentry 'student id 'for key 1; 5. delete record: delete from table name // delete all records in the table. This table is changed to an empty table: delete from table name where condition // delete records that meet the conditions in the table, for example: delete from xs where student ID = '20140901'; drop table xs ;// Delete the entire table, structure and record truncate table name // quickly delete all records in the table from a single table, syntax format: delete [low_priority] [quick] [ignore] from table name [where where_definition] [orderby...] [limit row_count] description: ● quick modifier: this can accelerate the deletion of some types. ● From clause: used to describe where data is deleted, followed by the name of the table to be deleted. ● Where clause: the content in where_definition is the specified deletion condition. If the where clause is omitted, all rows in the table are deleted. The where clause is described in detail. ● order by clause: all rows are deleted in the order specified in the clause, this clause takes effect only when it is used with limit. ● Limit clause: used to notify the server of the maximum number of rows 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] description: for the first syntax, only the corresponding rows in the table prior to the from clause are deleted. For the second syntax, only the rows in the table listed in the from clause (before the using clause) are deleted. The function is to delete rows from multiple tables at the same time and search for other tables. Example: assume that three tables t1, t2, and t3 contain id columns. To delete all rows whose id value is equal to the id value of t2 in t1 and all rows whose id value is equal to the id value of t3 in t2, use the following statement: delete t1, t2 from t1, t2, t3 where t1.id = t2.id and t2.id = t3.id; or: Delete from t1, t2 using t1, t2, t3 where t1.id = t2.id and t2.id = t3.id; 7. the truncate table statement is used to delete all data in the specified table. Therefore, it is also called the clear table data statement. Syntax format: truncate 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. The truncate table function is the same as the delete statement without the where clause. Both delete all rows in the table. However, truncate table is faster than delete and uses less system and transaction log resources. The delete statement deletes a row at a time and records one row in the transaction log. The truncate table deletes data by releasing the data pages used to store table data, and only records the release of pages in transaction logs. With the truncate table, the auto_increment counter is reset to the initial value of the column. Note: For tables involved in indexes and views, you must use the delete statement instead of truncate table to delete data. 8. to modify a record (update record), you can use the update statement to modify a 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 data rows that meet 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... Expr1, expr2... It can be a constant, variable, or expression. You can modify the values of multiple columns in 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 union of multiple tables, separated by commas. 10. imported in the SQL package format: source SQL package path; displays the table Record: select * from table name; when inserting a record, no quotation marks are required except for the value type, other types must be enclosed in single quotes (such as char and date) 11. show statement show tables or show tables from database name: displays the names of all tables in the current database. Show databases: displays the names of all databases in MySQL. Show columns from table name from database name or show columnsfrom database name. table name: displays the name of the column in the table. Show grants for user_name: displays the permissions of a user. The result is similar to the grant command. Show index from table_name: displays the table index. Show staus: displays information about specific system resources, such as the number of running threads. Show variables: displays the name and value of the system variable. Show processlist: displays all processes running in the system, that is, the query currently being executed. Most users can view their own processes, but if they have the process permission, they can view all 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 different permissions supported by the server. Show create database name: displays the create database statement for creating a database. Show create table: displays the create table statement for creating a table. Show events: displays a list of all events. Show innoDB status: displays the InnoDB storage engine status. Show logs: displays the logs of the BDB storage engine. Show warnings: displays the errors, warnings, and notifications generated by the last executed statement. Show errors: displays only the errors generated by the last statement. Show [storage] engines: displays the available storage engines and default engines after installation. Show procedure status: displays basic information about all stored procedures in the database, including the database, stored procedure name, and creation time. Show create procedure sp_name: displays detailed information about a stored procedure. 12. The describe statement (desc) describe statement is used to display the information of each column 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 short for describe, which is used in the same way. Col_name can be a column name or a string containing the wildcards '%' and '_'. it is used to obtain the output of each column with the name matching the string. It is not necessary to include a string in quotation marks unless it contains spaces or other special characters. For example, display the student table: desc | describe xs; display the student table student ID column: desc xs student ID; Note: When using the graphic interface, avoid unnecessary spaces when entering data, otherwise, a vulnerability may occur during data retrieval. Author tianyazaiheruanbitsCN.com