MySQL combined with multi-Table update and deletion, mysql combined updateMulti-Table updateIn MySQL 3.23, you can use LIMIT # to ensure that only the specified number of record rows is changed.If an order by clause is used (supported since MySQL 4.0.0), the record row is up
In the program, if you want to dynamically get the specific information of a table, you use the INFORMATION_SCHEMA information database in MySQL, and it contains many tables, see below:
INFORMATION_SCHEMA SCHEMATA表INFORMATION_SCHEMA TABLES表INFORMATION_SCHEMA COLUMNS表INFORMATION_SCHEMA STATISTICS表INFORMATION_SCHEMA USER_PRIVILEGES表INFORMATION_SCHEMA SCHEMA_PRIVILEGES表INFORMATION_SCHEMA TABLE_PRI
In the program, if you want to dynamically get the specific information of a table, you use the INFORMATION_SCHEMA information database in MySQL, and it contains many tables, see below:
INFORMATION_SCHEMA SCHEMATA表INFORMATION_SCHEMA TABLES表INFORMATION_SCHEMA COLUMNS表INFORMATION_SCHEMA STATISTICS表INFORMATION_SCHEMA USER_PRIVILEGES表INFORMATION_SCHEMA SCHEMA_PRIVILEGES表INFORMATION_SCHEMA TABLE_PRI
MySQL does not currently support column default for the form of a function, such as to achieve the default value of a column for the current update date and time function, you can use the timestamp column type
The following is a detailed description of the timestamp column type
Timestamp column type
The timestamp value can be from the beginning of 1970 to 2037, with a precision of one second, with a valu
(0.00 sec) 2. Interchange the value of Original_price and priceNewbies may use the following methods to swap update product set original_price=price,price=original_price;
1
However, the result of this execution will only cause the value of original_price and price to be the value of price, because the update is ordered,Execute original_price=price First, the value of Original_price has bee
point.NUMERIC (M,D) [Zerofill]This is a synonym for decimal.DATEA date. The scope of support is'1000-01-01'To'9999-12-31'。 MySQL to'YYYY-MM-DD'Format to display the date value, but allows you to assign a value to a date column using a string or a number.DatetimeA date and time combination. The scope of support is'1000-01-01 00:00:00'To'9999-12-31 23:59:59'。 MySQL to'YYYY-MM-DD HH:MM:SS'Format to display da
Tips for converting mysql columns (Sharing)
Preface:
Many business tables use a design pattern that violates the first paradigm due to historical or performance reasons. That is, multiple attribute values are stored in the same column (the specific structure is shown in the table below ).
In this mode, the application often needs to split the Column Based on the
statements can be written as follows:Insert into users (name, age) VALUES ('Yao Ming', 25 );Insert into uses SET name = 'Yao Ming ', age = 25;MySQL has also made some changes in VALUES. If nothing is written in VALUES, MySQL inserts a new record using the default value of each column in the table.Insert into users () VALUES ();If nothing is written after the table name, it indicates that all fields in the
MySQL database update sub-query, mysql database update
For example:
UPDATE test. tb_vobileSet a. name = '20140901'WHEREA. id = (select max (id) id from test. tb_vobile)
Error:
[SQL] UPDATE test. tb_vobileSet a. name = '2014090
MySQL updates Chinese columns: 1366 Incorrect string value solution, 1366 incorrect
Environment: mysql-5.6.25-winx64, MySQL workbench
Problem: An exception occurred during MySQL update:
Warning (s): 1366 Incorrect string value:
Tags: mysql indexShow index from TABLE_NAMEThis command helps diagnose poorly performing queries, especially if the query uses an available index.The following describes the meaning of the result column that this command displays:| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |1.TableThe name of the table.2.non_unique0 if the index cannot include a repeating word
Tags: score update mys CREATE TABLE Insert name build table Create valueSET NAMES UTF8;DROP DATABASE IF EXISTS TMOOC; CREATE DATABASE TMOOC Charset=utf8; Use TMOOC;CREATE TABLE Stu ( Sid INT PRIMARY KEY auto_increment, Sname VARCHAR (+) UNIQUE, Score DOUBLE, Schooltime DATE);INSERT into Stu (sname,schooltime) VALUES ("Shu", now ());INSERT into Stu (sname,schooltime) VALUES ("EN", now ());INSERT into Stu (sname,schooltime) VALUES ("Huang", now
one row, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on tables with multiple unique keywords.
You can use the values (col_name) function in the UPDATE clause from the INSERT ... The insert portion of the UPDATE statement references the column value. In other words, if no duplicate keyword conflict occurs, value
Update statements associated with multiple tables in MSSQL
For example, table A has the following fields:Aid A1 A2 A3 A4Fields in Table B:Bid B1 B2 B3 B4
If you use all fields in Table B to update the corresponding fields in Table A, you can write the following in ms SQL Server:UpdateSet a1 = B. B1, a2 = B. B2, A3 = B. B3, A4 = B. B4From a, BWhere a. Aid = B. B
Because Oracle does not support the update or delete from statement, Oracle's Multiple Table association update and delete must be supported by subqueries, and, similarly, Oracle does not support simultaneous update or delete multiple tables, typically using the following:
This article details the update data of the update statement in mysql. If you need it, please refer to this article.
This article details the update data of the update statement in mysql. If you need it, please refer to this artic
This article mainly introduces how to update and delete multiple objects in Django. Django is the most popular among Python frameworks. For more information, see
Update multiple objects
For example, we want to change the name of Apress Publisher from "Apress" to "Apress Publishing ". If you use the save () method, for
example is negligible compared to executing 8 update statements in a loop. But think about it,When you need to update 10,0000 or more line records, you will find the benefits of this! The only thing to note is the length of the SQL statement, which takes into account the length of the string supported by the program's running environment.I am currently getting data: SQL statement length up to 1,000,960 in
records! The only thing to note is the length of the SQL statement, the length of the string that is supported by the program's running environment, the data I am currently getting: The SQL statement can still execute smoothly in PHP 1,000,960, I queried the PHP document and did not find the maximum length of the specified string. The second type of insertThe insert syntax in MySQL has a condition of duplicate KEY
Typically, we use the following SQL statement to update the field values:
Copy Code code as follows:
UPDATE mytable SET myfield= ' value ' WHERE other_field= ' other_value ';
But what would you do if you wanted to update multiple rows of data and the values of each field in each row were differe
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.