任何人都可以给我正确的方法来编写这个函数的查询吗？

Question

I'm trying to implement search function where the user can search either of the following: name, age, address, gender, course and year?

SELECT *
FROM student
WHERE name LIKE '%name%'
  OR age BETWEEN 'age' AND 'age'
  OR address LIKE '%address%'
  OR gender = 'gender'
  OR course LIKE '%course%'
  OR YEAR = 'year'


Note: each of the search criteria is in different input field

but i can't get the correct results whenever the user input either 1 or more of the search criteria.

i know when to use the OR and AND but I just can't get the correct way of writing query for that.

example is: i have table student.

id   name   address   age   gender  course  year

2    juan   New York   19    male   IT      2
4    whitney New Jersey 23    female BA     4
7    manny   Canada     20    male   Law    2

Note: age is a select input field. example is 
15-20
20-25
25-30 and etc.

however when I'm trying to search for example is course which is IT, it returns all records which is wrong. the correct result should only be related to IT like the first record.

Answer 1

I think this is what you want

SELECT * FROM student WHERE name LIKE '%name%'  OR (age > 23 AND age < 30)
OR address LIKE '%address%'  OR gender = 'gender' OR course LIKE '%course%' 
OR YEAR = 'year'

Demo