Shulou(Shulou.com)06/01 Report--

Insert data

(1) INSERT INTO Persons VALUES ('Gates',' Bill', 'Xuanwumen 10,' Beijing')

(2) INSERT INTO Persons (LastName, Address)

VALUES ('Wilson',' Champs-Elysees'), ('Wilson2',' Champs-Elysees2')

(3) INSERT INTO Persons select * from Persons2

(4) SELECT LastName,FirstName INTO Persons_backup FROM Persons

2. Delete data

(1) DELETE FROM Person WHERE LastName = 'Wilson'

(2) drop Person

3. Modify data

(1) UPDATE Person SET Address = 'Zhongshan 23, City =' Nanjing'

WHERE LastName = 'Wilson'

(2) update p set p.Address = 'Zhongshan 23'

From Person p inner join City c on p.cityId = c.id where c.name = 'Beijing'

IV. Query statement

1. General query:

(1) SELECT LastName,FirstName FROM Persons WHERE City='Beijing'

ORDER BY Company DESC, OrderNumber ASC

(2) SELECT DISTINCT Company FROM Orders

(3) SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')

AND LastName='Carter'

(4) SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

(5) SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND' Carter'

(6) SELECT TOP 2 * FROM Persons (sqlserver only)

(7) SELECT * FROM Persons LIMIT 5 (mysql only)

(8) SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p

Product_Orders AS po WHERE p.LastNameplate Adams' AND p.FirstNameplate John'(alias)

(9) SELECT column_name (s) FROM table_name1 UNION ALL SELECT column_name (s) FROM table_name2 (merge result set, union all is directly connected, get all values

Records may have duplicates; union is the only value, records are not duplicated)

2. Like query

(1) SELECT * FROM Persons WHERE City LIKE'%'+'N' +'%'

(2) SELECT * FROM Persons WHERE City LIKE'N%'(indexing may be used in this way)

(3) SELECT * FROM user where userName like 'send _ 1q2' (_ replaces only one character)

(4) SELECT top 10 * FROM user where userId like'[23]%'

(any single character in the [charlist] character column, only sqlserver is useful)

(5) SELECT top 10 * FROM user where userId like'[^ 23] 0%'

([^ charlist] any single character not in the character column, only sqlserver is useful)

3. Association query

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons

INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName

(1) JOIN: returns a row if there is at least one match in the table

(2) LEFT JOIN: returns all rows from the left table even if there is no match in the right table

(3) RIGHT JOIN: returns all rows from the right table even if there is no match in the left table

(4) FULL JOIN: returns a row as long as there is a match in one of the tables

4. Paging query

(1) SELECT * FROM ceshi limit 0J10 (only mysql, starting with the first data, take 10 pieces of data)

(2) select top 10o.* from (select row_number () over (order by userId) as rowNumber,* from user) as o where rowNumber > 0 (sqlserver only, starting with the first data, take 10 pieces of data)

(3) SELECT * FROM user order by userId OFFSET 0 ROW FETCH NEXT 10 ROWS ONLY

(only sqlserver, the new version of sqlserver is available, which is more efficient than the previous one)

IV. Problems that need to be paid special attention to when writing database statements in java

1. SqlServer limits the number of statements and the number of parameters, which are 1000 and 2100 respectively.

2. Mysql limits the length of statements. The default is 4m.

3. In the where condition, there can be no null values on both sides of = and! =, otherwise the judgment will be wrong. When you need to compare fields with null values, sqlserver needs to use isnull (null,'default_value') to convert null values to default values for comparison; mysql needs to use ifnull (null,'default_value') to convert null values to default values for comparison.

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.