Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "mysql basic knowledge Summary". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Collation of basic knowledge of SQL:

Select query results such as: [student number, grade point average: group function avg (score)]

From which table does from look for data such as: [related to grades: score sheet score]

Where query conditions such as: [b. Course number = '0003' and b. Score > 80]

Group by grouping such as: [average of each student: grouped by student number] (non-grouping functions that appear after the select clause in oracle,SQL server must appear after the group by clause), but may not be used in MySQL

Having specifies conditions for grouping results such as: [greater than 60 points]

Order by sorts the query results, such as: [increasing order: score ASC / descending order: score DESC]

Limit uses the limt clause to return topN (the top two scores returned for this question), such as: [limit 2 = = > read 2 from index 0] limit== > from index 0 [0memNmurl]

① select * from table limit 2 offset 1; / / it means to skip 2 items to take out 1 piece of data. Limit is followed by reading from Article 2, reading 1 piece of information, that is, reading the third piece of data ② select * from table limit 2 offset 1; / / it means taking 2 pieces of data from Article 1 (excluding) data, limit is followed by 2 pieces of data, and offset is followed by reading from Article 1, that is, reading articles 2 and 3.

Group function: deduplicated distinct () statistics total number sum () calculate number count () average number avg () maximum max () minimum number min ()

Multi-table join: internal join (omit default inner) join... on.. Left connection left join tableName as b on a.key = = b.key right connection right join connection union (no repetition (filter deduplication) and union all (with repetition [no filter deduplication])

Union union

Union all (with repetition)

Oracle (SQL server) database

Intersect intersection

Minus (except) subtraction (subtraction)

Oracle

Database objects: table (table), view (view), sequence (sequence), index (index) synonym (synonym)

1. View: stored select statements

Create view emp_vwasselect employee_id, last_name, salaryfrom employeeswhere department_id = 90th select * from emp_vw

DML operations can be performed on simple views

Update emp_vwset last_name = 'HelloKitty'where employee_id = 100th select * from employeeswhere employee_id = 100

1)。 Complex view

Create view emp_vw2asselect department_id, avg (salary) avg_salfrom employeesgroup by department_id;select * from emp_vw2

Complex views cannot perform DML operations

Update emp_vw2set avg_sal = 10000where department_id = 100

two。 Sequence: used to generate a regular set of values. (typically used to set values for primary keys)

Create sequence emp_seq1start with 1increment by 1maxvalue 10000minvalue 1 cyclenocachetion select emp_seq1.currval from dual;select emp_seq1.nextval from dual

Problem: cracks. Reason:

When multiple tables share the same sequence.

Rollback

An exception occurred

Create table emp1 (id number (10), name varchar2 (30)); insert into emp1values (emp_seq1.nextval, 'Zhang San'); select * from emp1

3. Indexing: improving query efficiency

Automatic creation: Oracle automatically creates indexes for columns with unique constraints (unique constraints, primary key constraints)

Create table emp2 (id number (10) primary key, name varchar2 (30))

Create manually

Create index emp_idxon emp2 (name); create index emp_idx2on emp2 (id, name)

4. Synonym

Create synonym D1 for departments;select * from D1

5. Table:

DDL: data definition language create table. / drop table. / rename. To..../ truncate table.../alter table...

DML: data manipulation language

Insert into... Values... update... Set... Where... delete from... Where...

[important]

Select... Group function (MIN () / MAX () / SUM () / AVG () / COUNT ())

From... join... On... Left outer link: left join. On... Right outer connection: right join. On...

Where...

Group by... (non-grouping functions that appear after the select clause in oracle,SQL server must appear after the group by clause)

Having... Used to filter group functions

Order by... Asc ascending, desc descending

Limit (0B4) restricts N pieces of data such as topN data

Union union

Union all (with repetition)

Intersect intersection

Minus subtraction

DCL: data control language commit: submit / rollback: rollback / authorization grant...to... / revoke

Indexes

When to create an index:

I.

Select employee_id, last_name, salary, department_idfrom employeeswhere department_id in (70,80)-> 70:1 80:34

Union union

Union all (with duplicates)

Intersect intersection

Minus subtraction

Select employee_id, last_name, salary, department_idfrom employeeswhere department_id in (80,90)-> 90:4 80:34

Question: query the information of employees whose salary is greater than 149

Select * from employeeswhere salary > (select salary from employeeswhere employee_id = 149)

Question: inquire about other employees who have the same manager_id and department_id as employee 141or 174,

Employee_id, manager_id, department_id select employee_id, manager_id, department_idfrom employeeswhere manager_id in (select manager_id from employeeswhere employee_id in (141174)) and department_id in (select department_idfrom employeeswhere employee_id in (141174)) and employee_id not in (141174) Select employee_id, manager_id, department_idfrom employeeswhere (manager_id, department_id) in (select manager_id, department_idfrom employeeswhere employee_id in (141174)) and employee_id not in (141174)

1. Use subqueries in the from clause

Select max (avg (salary)) from employeesgroup by department_id;select max (avg_sal) from (select avg (salary) avg_sal from employeesgroup by department_id) e

Problem: return the last_name, department_id, salary and average salary of employees who earn more than the average salary in this department

Select last_name, department_id, salary, (select avg (salary) from employees where department_id = e1.department_id) from employees e1where salary > (select avg (salary) from employees e2 where e1.department_id = e2.department_id) select last_name, e1.department_id, salary, avg_salfrom employees E1, (select department_id Avg (salary) avg_sal from employees group by department_id) e2where e1.department_id = e2.department_idand e1.salary > e2.avg_sal

Case...when... Then... When... Then... Else... End

Query: if the department is 1.1 times the salary of 10, the department number is 1.2 times the salary of 20, and the remaining 1.3 times

Select employee_id, last_name, salary, case department_id when 10 then salary * 1.1 when 20 then salary * 1.2 else salary * 1.3 end "new_salary" from employees Select employee_id, last_name, salary, decode (department_id, 10, salary * 1.1,20, salary * 1.2, salary * 1.3) "new_salary" from employees

Problem: employee_id,last_name and location of explicit employees. Among them, if the employee department_id is the same as the department_id with a location_id of 1800, then the location is' Canada', and the rest is' USA'.

Select employee_id, last_name, case department_id when (select department_id from departments where location_id = 1800) then 'Canada' else' USA' end "location" from employees

Question: query the employee's employee_id,last_name, which is required to be sorted by employee's department_name

Select employee_id, last_namefrom employees e1order by (select department_name from departments D1 where e1.department_id = d1.department_id)

SQL optimization: do not use IN if you can use EXISTS

Problem: query the employee_id,last_name,job_id,department_id information of the company's managers

Select employee_id, last_name, job_id, department_idfrom employeeswhere employee_id in (select manager_id from employees) select employee_id, last_name, job_id, department_idfrom employees e1where exists (select 'x'from employees e2 where e1.employee_id = e2.manager_id)

Problem: query the department_id and department_name of departments in the departments table that do not exist in the employees table

Select department_id, department_namefrom departments d1where not exists (select 'x' from employees E1 where e1.department_id = d1.department_id)

Change the information of 108employees: make their salary the highest wage in their department, and job the lowest average wage job in the company.

Update employees e1set salary = (select max (salary) from employees e2 where e1.department_id = e2.department_id), job_id = (select job_id from employees group by job_id having avg (salary) = (select min (avg (salary)) from employees group by job_id)) where employee_id = 108

fifty-six。 Delete the lowest-paid employee in employee 108's department.

Delete from employees e1where salary = (select min (salary) from employees where department_id = (select department_id from employees where employee_id = 108)) select * from employees where employee_id = 108 role select * from employees where department_id = 100order by salary;rollback; Common SQL interview questions: classic 50

The following four tables are known:

Student form: student (student number, student name, date of birth, gender)

Transcript: score (student number, course number, grade)

Course schedule: course (course number, course name, teacher number)

Teacher list: teacher (teacher's number, teacher's name)

According to the above information, write the corresponding SQL statement according to the following requirements.

Ps: these questions examine the writing ability of SQL. For this type of problem, you need to figure out the relationship between the four tables first. The best way is to draw the correlation diagram on the draft paper, and then write the corresponding SQL statement. The following figure is a diagram of the four tables I have drawn, and you can see which foreign keys are associated between them:

I. create databases and tables

To demonstrate the running process of the topic, let's first create the database and table in the client-side navicat by using the following statement.

If you don't know what a database is and what a client-side navicat is, you can learn this first:

1. Create a tabl

1) create a student table (student)

Click the following figure to create a student table in the client navicat

The "student number" column of the student table is set as the primary key constraint. The following figure shows the data types and constraints set for each column.

When you have finished creating the table, click Save

2) create a transcript (score)

In the same step, create a "grade sheet". The "student number" and "course number" of the course schedule are set together as primary key constraints (joint primary keys), and the "grades" column is set to numeric types (float, floating point values).

3) create a course schedule (course)

The course number of the course schedule is set as the primary key constraint

4) teacher list (teacher)

The teacher number column of the teacher table is set as the primary key constraint

The teacher's name column is constrained to "null" (unchecked in the red box), indicating that this column is allowed to contain null values (null).

two。 Add data to the table

1) add data to the student table

Add sql for data

Insert into student (student number, name, date of birth, sex) values ('0001', 'monkey', '1989-01-01', 'male'); insert into student (school number, name, date of birth, sex) values ('0002', 'monkey', '1990-12-21', 'female') Insert into student (student number, name, date of birth, gender) values ('0003', 'Jack Ma', '1991-12-21', 'male'); insert into student (student number, name, date of birth, sex) values ('0004', 'Wang Sicong', '1990-05-20', 'male')

Operations in client-side navicat

2) report sheet (score)

Add sql for data

Insert into score (student number, course number, grade) values ('0001',' 0001', 80); insert into score (student number, course number, grade) values ('0001',' 0002', 90); insert into score (student number, course number, grade) values ('0001',' 0003', 99); insert into score (student number, course number, grade) values ('0002',' 0002', 60) Insert into score (student number, course number, grade) values ('0002',' 0003', 80); insert into score (student number, course number, grade) values ('0003',' 0001', 80); insert into score (student number, course number, grade) values ('0003',' 0002', 80); insert into score (student number, course number, grade) values ('0003',' 0003', 80)

Operations in client-side navicat

3) course schedule

Add sql for data

Insert into course (course number, course name, teacher number) values ('0001',' Chinese', '0002'); insert into course (course number, course name, teacher number) values (' 0002', 'Mathematics', '0001'); insert into course (course number, course name, teacher number) values (' 0003', 'English', '0003')

Operations in client-side navicat

4) add data to the teacher's table

Add sql for data

-- teacher table: add data insert into teacher (teacher number, teacher name) values ('0001',' Monza'); insert into teacher (teacher number, teacher name) values ('0002',' Ma Huateng');-- teacher name here is null value (null) insert into teacher (teacher number, teacher name) values ('0003', null) -- the name of the teacher here is the empty string ('') insert into teacher (teacher number, teacher's name) values ('0004','')

Operation in client navicat

Add result

Thirty or 50 interview questions

In order to facilitate my study, I classified 50 interview questions.

Query the list of students surnamed "Monkey"

Inquire the number of teachers surnamed "Meng"

Select count (teacher number) from teacherwhere teacher's name like 'Meng%'; 2. Summary statistics and grouping analysis

Interview questions: inquire about the total score of the course number "0002"

/ * Analysis idea select query results [Total score: summary function sum] from which table to find data [transcript score] where query condition [course number is 0002] * / select sum (grade) from scorewhere course number = '0002'

Inquire about the number of students who have taken the course

/ * this topic is translated into vernacular: find out how many candidates have selected the course select student number, and there is a duplicate value in the grade table. You need to remove from from the course schedule to find score;*/select count (distinct student number) as student number from score.

Query the highest and lowest scores in each subject, and show them in the following form: course number, highest score, lowest score

/ * Analysis ideas select query results [course ID: alias for course number, highest score: max (grade), lowest score: min (grade)] from which table to find data [transcript score] where query conditions [none] group by grouping [grades: that is, grades of each course, need to be grouped by course number] * / select course number, max (grade) as highest score, min (grade) as minimum score from scoregroup by course number

Query the number of students who are enrolled in each course

/ * Analysis ideas select query results [course number, number of students taking the course: summary function count] from which table to find data [transcript score] where query conditions [none] group by grouping [each course number: grouped by course number]; * / select course number, count (course number) from scoregroup by course number

Inquire about the number of boys and girls

/ * Analysis idea select query results [gender, number of gender corresponding to gender: summary function count] from which table from looks for data [gender is in the student table, so it is looking for student table student] where query conditions [none] group by grouping [number of boys and girls: grouped by gender] having specifies criteria for grouping results [no] order by sorts the query results [none] * / select gender, count (*) from studentgroup by gender

Inquire about the student number and grade point average of students with an average score of more than 60

/ * title translated into vernacular: average score: in expansion, it means to calculate the average score of each student. Here, when it comes to "each", it means grouping the average score of more than 60 points, that is, specifying the conditional analysis train of thought for the grouping result. Select query result [student number, average grade: summary function avg (grade)] from looks up the data from which table [score is in the grade table. So look for the score sheet score] where query condition [no] group by grouping [average score: first grouped by student number, then calculate average grade] having specifies the conditions for the grouping result [average score greater than 60] * / select student number, avg (grade) from scoregroup by student number having avg (grade) > 60

Check the student numbers of at least two courses

/ * translated into vernacular: step 1, you need to calculate the data of each student's elective courses first, and you need to group each student into two groups according to the student number, and take at least two courses: that is, the number of electives per student > = 2 Specify conditional analysis ideas for grouping results select query results [student number, number of elective courses per student: summary function count] from from which table to find data [course student number: course schedule score] where query conditions [at least two courses: need to calculate how many courses each student has taken, need to use grouping So there is no where clause] group by grouping [number of elective courses per student: grouping by course number, and then using the aggregate function count to calculate the number of elective courses] having specifies the conditions for the grouping result [at least two elective courses: number of elective courses per student > = 2] * / select number, number of elective courses count (course number) as number from scoregroup by (course number) > = 2

Query the list of same-sex students with the same name and count the number of students with the same name

/ * translated into vernacular, question resolution: 1) find out who the students with the same name are and the number of students with the same name. Query results: name, number condition: how to calculate the same name? The number of people grouped by name is greater than or equal to 2. Because the number of people with the same name is greater than or equal to 2 Analysis idea select query results [name, number: summary function count (*)] from from which table to find data [student table student] where query conditions [none] group by grouping [same name: grouped by name] having specifies conditions for grouping results [same name: count (*) > = 2] order by sorts the query results [none] * / select name, count (*) as number from studentgroup by name having count (*) > = 2

Query the failed courses and sort them by course number from big to small

/ * Analysis ideas select query results [course number] from from which table to find data [transcript score] where query conditions [failing: grade 2 grade 2 prime prime * step 1: get each student's average grade and display the student number. Average grade select query results [student number, average grade: summary function avg] from which table from looks for data [related to grades: grade sheet score] where query conditions [none] group by grouping [average of each student: grouping by student number] having specifies criteria for grouping results [none] order by sorts the query results [none] * / select student number, avg (grade) as grade point average from scoregroup by student number / * step 2: add restrictions: 1) fail courses 2) select query results for more than two [failed courses] [student number, grade average: summary function avg (grade)] from which table does from find data [related to grades: grade sheet score] where query conditions [restrictions: failed courses] Average score 2, summary function count (course number) > 2] order by sorts the query results [none] * / select student number, avg (grade) as grade point average (from scorewhere) = 2

If you can't do the above topic, you can review the sql knowledge involved in this section:

3. Complex query

Check the student numbers and names of all students whose course scores are less than 60 points.

[knowledge points] Sub-query

1. Translate into vernacular

1) query result: student number, name

2) query conditions: all course scores

< 60 的学生，需要从成绩表里查找，用到子查询 第1步，写子查询（所有课程成绩 < 60 的学生） select 查询结果[学号] from 从哪张表中查找数据[成绩表：score] where 查询条件[成绩 < 60] group by 分组[没有] having 对分组结果指定条件[没有] order by 对查询结果排序[没有] limit 从查询结果中取出指定行[没有]; select 学号 from scorewhere 成绩 < 60; 第2步，查询结果：学生学号，姓名，条件是前面1步查到的学号 select 查询结果[学号,姓名] from 从哪张表中查找数据[学生表:student] where 查询条件[用到运算符in] group by 分组[没有] having 对分组结果指定条件[没有] order by 对查询结果排序[没有] limit 从查询结果中取出指定行[没有]; select 学号,姓名from studentwhere 学号 in (select 学号 from scorewhere 成绩 < 60); 查询没有学全所有课的学生的学号、姓名| /*查找出学号，条件：没有学全所有课，也就是该学生选修的课程数 < 总的课程数【考察知识点】in，子查询*/select 学号,姓名from studentwhere 学号 in(select 学号 from scoregroup by 学号having count(课程号) < (select count(课程号) from course)); 查询出只选修了两门课程的全部学生的学号和姓名| select 学号,姓名from studentwhere 学号 in(select 学号from scoregroup by 学号having count(课程号)=2); 1990年出生的学生名单

/ * find the type of birth date column in the student table of the list of students born in 1990 is datetime*/select student number, and the name from student where year (date of birth) = 1990

Check the records of the top two grades in each subject.

This kind of problem is actually common: grouping each group of maximum and minimum values, each group of maximum N (top N) records.

Sql interview question: topN question

Business problems like this are often encountered at work:

How to find out which product users like best under each category?

What are the five items that users click on most under each category?

This kind of problem is actually common: grouping each group of maximum and minimum values, each group of maximum N (top N) records.

Faced with this kind of problem, how to solve it?

Let's give the answer through the example of the score sheet.

The transcript is the student's grade, which includes the student number (the student's student number), the course number (the course number of the student's elective course), and the grade (the student's achievement in taking the course).

Group to take the maximum value of each group

Case: the data in which the maximum score is obtained by course number.

We can use group by and summary functions to get a value in each group (maximum, minimum, average, etc.). However, the data of the line in which the maximum score is not available.

Select course number, max (grade) as maximum score from score group by course number

We can use an associated subquery to do this:

Select * from score as a where score = (select max) from score as b where b. Course number = a. Course number)

The above query result course number "0001" has 2 rows of data, because there are 2 maximum scores of 80.

Group to take the minimum value of each group

Case: the data in which the minimum score is grouped by course number.

The same use of associated subqueries to implement

Select * from score as a where score = (select min) from score as b where b. Course number = a. Course number)

The largest N records in each group

Case study: query the records of the top two grades in each subject.

Step 1, find out which groups

We can group by course number and find out which groups there are. What are the course numbers in this question?

Select course number, max (grade) as maximum score from score group by course number

Step 2: first use the order by clause to sort by grade descending (desc), and then use the limt clause to return topN (the top two scores returned for this question)

-- Top 2 select * from score where course number = '0001' order by desc limit 2 in the group' 0001'

Similarly, you can write out the sql of other groups (other course numbers) and take out the top 2 scores.

Step 3, use union all to merge the selected data from each group

-- swipe left and right to get all sql (select * from score where course number = '0001' order by grade desc limit 2) union all (select * from score where course number =' 0002' order by grade desc limit 2) union all (select * from score where course number = '0003' order by grade desc limit 2)

Previously, we used the order by clause to sort by a column in descending order (desc) to get the largest N records per group. If you want to reach the minimum of N records in each group, sort the order by clause in ascending order of a column (asc).

The problem of finding topN can also be implemented using custom variables, which will be discussed later.

If you don't know anything about multi-table merging, you can take a look at the "multi-table query" that I talked about "learning SQL from scratch".

Summary

Common interview questions: take the maximum and minimum values of each group, and the largest N (top N) records of each group.

4. Multi-table query

Inquire about the student number, name, number of courses and total scores of all students.

Selecta. Student number, a. Name, count (b. Course number) number of courses selected by as, sum (b. As total score from student as a left join score as bon a. Student number = b. Student number group by a. Student number

Inquire about the student numbers, names and grade points of all students whose GPA is greater than 85

Select a. Student number, a. Name, avg (b. Grade point average (as) from student as a left join score as bon a. Student number = b. Student number group by a. Student number having avg (b. Grades) > 85

Inquire about the students' course selection: student number, name, course number, course name

Select a. Student number, a. Name, c. Course number, c. Course title: from student an inner join score b on a. Student number = b. Student number inner join course c on b. Course number = c. Course number

Find out the number of passes and failures in each course

-- examine case expression select course number, sum (case when score > = 60 then 1 else 0 end) as pass number, sum (case when score < 60 then 1 else 0 end) as failure number from scoregroup by course number

Use subparagraphs [100-85], [85-70], [70-60], [= 70 and = 60 and

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.