SQL database. Please ask an expert to answer the question. I really don’t know how to do it. I am a beginner

They are basically very basic sentences. As for the final difference, there are many articles on the Internet that specifically introduce these.

1. select a.userid, a.username, isnull(b.warename, 'No item') as warename

from u_user a left join u_ware b on a.userid = b.userid

2. select b.warename, isnull(a.username, 'Unclaimed') as username

from u_user a right join u_ware b on a.userid = b.userid

3. select isnull(a.sex, 'Unknown'), sum(b.amt) as sum_amt

from u_user a right join u_ware b on a.userid = b.userid

group by isnull(a.sex, 'Unknown')

4. insert into u_user values ??('105', 'Xiao Zhang', 'Male')

Repeat the above three steps and directly use the above three statements to query.

5. 1) delete from u_ware where warename = 'cup'

2) update u_ware set warename = 'combination bed' where warename = 'bed'

6. select warename, amt * 0.1 as keep_amt

from u_ware

union all

select 'total', sum(amt * 0.1) from u_ware

7. select substring(convert(varchar(10), getdate(), 120), 1, 4) 'Year' substring(convert(varchar(10), getdate(), 120), 6, 2) 'Month' substring(convert(varchar(10), getdate(), 120), 9, 2) 'Day'

8. Using the rand() function, this function randomly generates 0 to 1 A floating point number, then there are 7 days in a week, multiplied by 7, and a floating point number between 1 and 7 is obtained.

If it is two days a week, you can divide the 7 days a week into two parts, the first four days and the last three days. Then there are two multipliers for this random function, one is 4 and the other is 3.

select cast(ceiling(rand() * 4) as int) as num1, 4 cast(ceiling(rand() * 3) as int) as num2

9. Assumptions Someone's birthday is 1989-12-10

select datediff(d, convert(datetime, '1989-12-10', 120), getdate())

10.

CHAR/VARCHAR: Both of these are character types. The difference is that the CHAR type is fixed length, while the VARCHAR type is variable length. CHAR(10) and VARCHAR(10), such fields store data. The field length of CHAR(10) is fixed at 10, while for VARCHAR(10), the actual length is determined based on the data. The length of 10 only stipulates the maximum length. Can be 10.

CHAR/NCHAR The CHAR type is stored in bytes, while the NCHAR type is stored in characters. For example, for Chinese, CHAR(10) can only store up to 5 Chinese characters, while NCHAR(10) can store up to 10 Chinese characters.

LEN/DATALENGTH: Just look at the explanation of the two in the database.

LEN: Returns the number of characters (not bytes) in the given string expression, excluding trailing spaces.

DATALENGTH: Returns the number of bytes occupied by any expression.

Table/View: The data in the database is stored in the table, and the view is a collection created based on the table data. The creation of the view comes from the table and view, which is actually the SELECT statement. The query is solidified.

Triggers/Stored Procedures:

Triggers are automatic: they are activated immediately after any modification is made to the table's data.

A stored procedure is a set of SQL statements that are compiled to complete a specific function and are stored in the database. The user executes the stored procedure by specifying its name and giving parameters. The stored procedure is not automatic and needs to be called by the user.

Stored procedures/functions:

Functions are limited to returning only one scalar, while stored procedures can return multiple. And functions can be embedded in SQL and can be called in SQL statements such as SELECT, but stored procedures cannot.

LEFT JOIN/OUT JOIN

LEFT JOIN/INNER JOIN: left join is a left outer join, connecting the table on the left as the main table, the result of the statement query, the result of the main table will be displayed, but the data of the table connected on the right will be filled in if the connection condition is not met. Refer to statements 1, 2, and 3 above.

Inner join is a full join and can only query all data for which the connection conditions are met.

UNION/UNION ALL: UNION ALL only implements the union of two queries, and UNION will remove duplicate data based on UNION ALL. The query efficiency of UNION is lower.

SUM/COUNT: Both are aggregate functions. SUM is a summation, while COUNT is to find the total number of rows that meet the conditions.

IN/EXISTS: Both can often be achieved. Same result.

In the statement, IN should be preceded by a field, followed by a data collection of the same type as the preceding field or variable. This collection can be obtained through SELECT, and EXISTS is followed by a SELECT statement, and the WHERE condition of this statement is passed in The fields of the external main query are used as connections. If the connection is established, then the conditions are met. Otherwise the conditions are not met.

For example, SELECT * FROM STUDENT WHERE ID IN (SELECT ID FROM SCORE)

Equivalent to: SELECT * FROM STUDENT WHERE EXISTS (SELECT 1 FROM SCORE WHERE SCORE.ID = STUDENT.ID)

When executing, if IN is followed by a query, the query needs to be executed. However, EXISTS will automatically exit the query when it encounters the first result that meets the query conditions, so the efficiency is much higher.