MySQL face test

1. According to the department number from high to low, wages from low to high list of employee information

SELECT * FROM Employee ORDER by dept_id Desc,salary

The difference between the 2.union and union ALL

After the table is joined with union, the duplicate records are deleted and the records are completely preserved after the Union ALL join table

3. Paging statements (direct paging with SQL statements, high efficiency)

PageSize =5* from Tbname limit (pageNo-1) *pagesize,pagesize

4. The three paradigms of the database
The first Paradigm field is atomic and cannot be divided
Each column in the second Paradigm table is associated with the primary key
The third paradigm each column is directly related to the primary key column, not the indirect correlation

5. Use an SQL statement to find out the names of students who have more than 80 points per course
Name Kecheng Fenshu
Zhang San language 81
Zhang San Mathematics 75
John Doe Language 76
John Doe Mathematics 90
Harry Language 81
Harry Mathematics 100
Harry English 90

Select name, ' Subject ', score, SUM (Score >) as Jgfrom resultgroup by ' name ' have JG = (SELECT COUNT (DISTINCT rs.sub ject) from result rs)

Another way of writing, the Tall Man wrote

Select Ttt.namefrom (select COUNT (r.name) as C, r.namefrom result rwhere r.score > 80GROUP by R. ' Name ') Tttwhere ttt.c = (SELECT COUNT (DISTINCT ' subject ') from result)

6. AutoNumber Number Name Course number Course name score

1 2005001 Zhang 30,001 Math 69
2 2005002 li 40,001 Math 89
3 2005001 Zhang 30,001 Math 69
Delete the same student redundancy information except for the automatic numbering

Create TABLE tmp as SELECT S.code from student s GROUP by S. ' Name ', S.kname,s.scode,s.kcode,s.score;D elete from studentwhe Re code not in (select Code from TMP);d ROP table tmp;

7. A table called department, there is only one field name, a total of 4 records, respectively, is a,b,c,d, corresponding to four ball pairs, now four ball pairs to play, with an SQL statement to show all possible match combinations.

SELECT * FROM Department INNER JOIN Department as aondepartment. ' Name ' <> (A. ' Name ')

8. List the number of employees and department numbers in each department with wages higher than the department's average salary, sorted by department number

Select Name, DeptID, Salaryfrom employeewhere salary > (select AVG (Salary) from employee) GROUP by DeptID ASC

After you write your own practice SQL export a copy to come up

Mysql> SELECT * FROM table LIMIT 5, 10; Retrieving record rows 6-15//in order to retrieve all the record rows from an offset to the end of the recordset, you can specify a second parameter of -1:mysql> SELECT * from table LIMIT 95,-1; Retrieving record rows 96-last.//If only one parameter is given, it represents the maximum number of record rows returned: mysql> SELECT * from table LIMIT 5; Retrieving the first 5 record lines//In other words, limit n is equivalent to limit 0,n. SELECT * FROM (select Id,realname,row_number () over (ORDER by ID ASC) RN from T_user) WHERE RN between and 20SELECT * from (select ROWNUM rn,id,realname from (select Id,realname from T_user) where rownum<=20) T2 where T2.rn >=10;

8. When the database returns records by connecting two or more tables, an intermediate temporary table is generated, and then the temporary table is returned to the user.
When using left Jion, the difference between on and where conditions is as follows:
1. On condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true.
2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

MySQL face test