MyBatis is recognized and used by everyone because of its simplicity and ease of use.
But in the batch update operation, the online introduction of seemingly incomplete, just do a record today, we progress together
In the actual project development process, there is often such a requirement: According to the IDs update the table of a field value, the SQL statemen
The following is a test example.1. Create two temporary tables and input test data:Copy codeThe Code is as follows:Create table # temptest1(Id int,Name1 varchar (50 ),Age int)Create table # temptest2(Id int,Name1 varchar (50 ),Age int)
The following table data is queried:
# Temptest1 # temptest2
2. Now we need to update the age in # temptest2 to the corresponding age in # temptest1.
In fact, the age of ID 1 in [Table 1] is changed to 19, and the ag
name/Password switch users; for example, conn scott/tiger. Note: No semicolon ends during logon. 3) tables in oracle are user-defined sys Super Users (with the highest permissions) system Database Administrators (who perform most management operations) to test users. 3. common oracle commands and knowledge point commonly used commands: select * from tabs: queries the tables of the current user (multiple queries to the system table); select table_name
The SQL scripts recommended in this article are all very practical. I learned how to study them together.
The SQL scripts recommended in this article are all very practical. I learned how to study them together.
Exchange of rows and columns
The Code is as follows:
Create table test (id int, name varchar (20), quarter int, profile int)
Insert into test values (1,
Because the project requires that multiple different records be updated at the same time, we have discussed a good solution with our colleagues.
The following is a test example.
1. Create two temporary tables and input test data:View code
1 create table #temptest1 2 ( 3 id int, 4 name1 varchar(50), 5 age int 6 ) 7 8 create table #temptest2 9 (10 id int,11 name1 varchar(50),12 age int13 )
The following table data is queried:
Exchange of rows and columns
CopyCode The Code is as follows: Create Table Test (ID int, name varchar (20), quarter int, profile INT)
Insert into test values (1, 'A', 1,1000)
Insert into test values (1, 'A', 2,2000)
Insert into test values (1, 'A', 3,4000)
Insert into test values (1, 'A', 4,5000)
Insert into test values (2, 'B', 1,3000)
Insert into test values (2, 'B', 2,3500)
Insert into test values (2, 'B', 3,4200)
Insert into test values (2, 'B',
30 women 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 female 7934 MILLER Clerk 7782 1982/1/23 1300.00 10 male Rows selected
Second, row and column conversion methodRequirements: Check the total salary for different positions in each department.1, the most traditional SQL solution is as follows:
Sql> Select D.dname, E.job, sum (SAL) as Sum_sal2 from EMP E, Dept D3 Where E.dep
Exchange of rows and columnsCopy codeThe Code is as follows:Create table test (id int, name varchar (20), quarter int, profile int)Insert into test values (1, 'A', 1,1000)Insert into test values (1, 'A', 2,2000)Insert into test values (1, 'A', 3,4000)Insert into test values (1, 'A', 4,5000)Insert into test values (2, 'B', 1,3000)Insert into test values (2, 'B', 2,3500)Insert into test values (2, 'B', 3,4200)Insert into test values (2, 'B', 4,5500)Select * from test-- Row-to-column ConversionSele
Http://u.115.com/file/f23bcc71be demo
Demonstration highlightsAdd the expression attribute to the columnattribute attribute of the object class column member,Then, it indicates the computed columns corresponding to the data table.This attribute is of the string type,The value assigned to this attribute is the expression of the computed column in the data table.For example, expression = "[unitprice] * [unitsinstock]"By default, the O/R designer does no
bulk update, make an internal connection, update the condition table by updating the primary Table So_master Table Association T1, and then T2 get the updated value, T1 and update the main table So_master through the Foreign Key Association, T2 is T1 according to the T2 Ordinal Association, and then add all the query criteria , The main is the column with the up
SQL Application and Development: (5) connecting multiple data tables and multiple SQL tables
Different tables in the database store different data. Users often need to use the data in multiple tables to combine and extract the required information. If a query needs to operat
Today, I investigated a defect in the project and found that SQL Server sorts the guid column andClusterSpecial processing of index. Two knowledge points are involved. One is how SQL Server sorts guid columns.AlgorithmImplementation. The second is the difference between cluster index and noncluster index. First, let's talk about how SQL Server sorts the guid
SQL Server IDs are often used to increase by itself, that is, the identification column to act as the primary key of a table. There are two concepts to be clarified:
1. ID column: it can be created using the identity attribute so that application developers can specify the identity number (identity seed attribute) in the first row inserted to the table ), determine the increment (identity increment attribute) to be added to the seed to determine
in (the SELECT MIN (ID) from dbo. Member GROUP by Name)Execution Result:Explanation: The above example only holds the minimum value for the respective name.Second, the use of DISTINCTWarm reminder:Multi-column statistics not supportedOracle and DB2 databases are also availableUse the DISTINCT keyword to return a unique different value(1) Querying a column for non-repeating dataStatement: SELECT DISTINCT Name from dbo. MemberResult set:(2) DISTINCT query mu
in the process of reporting to the Personnel Bureau, a difficult problem was encountered, and the customer requested that a column of data in the database be divided into multiple columns according to the conditions. For example, there are provinces in the database that have such a column of dataCustomer requirements according to the province classification, Hebei province a column, Beijing City, a column o
SQL: insert all other columns of rows with the same primary key updated.
In SQL writing, we often encounter such problems. During each insert operation, you must determine whether the data table already exists. For the same data, all the columns except the primary key are the same. If no data exists, the data is inser
Original: Oracle\ms SQL Server Update Multiple Table association updateA single update UPDATE statement is not able to update multiple tables unless you use triggers to suppress updates
Delete data columnsThe data columns that are built, wrong, or duplicated in the development or production process need to be deleted.Use the SSMS database management tool to delete data column mode one1. Open database--Select data Table-"Expand Data Sheet-" Expand Data Column-"Select the data column to delete-" Right click-"SELECT Delete-" in the Pop-up box click OK.Way Two1. Open Database-"Open data Sheet-" Right click-"SELECT Design.2, in the Design
: This article mainly introduces the SQL statement for the same table. PHP queries the sum of two columns of data and then outputs the results. if you are interested in the PHP Tutorial, you can refer to it. // Calculate the total order quantity
$query_num = "SELECT SUM(order_num) from hcf_order_db where order_id = XXX";$arr_qn = mysqli_query($dbc,$query_num);list($sum)=mysqli_fetch_row($arr_qn);
// Calcul
replacement string substring replace
Copy Code code as follows:
--Example 1:
Update Tbpersonalinfo Set truename = replace (truename,substring (truename,2,4), ' * * ') where ID = 1
--Example 2:
Update Tbpersonalinfo Set Mobile = replace (mobile,substring (mobile,4,11), ' ******** ') where ID = 1
--Example 3:
Update Tbpersonalinfo Set Ema
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.