Mysql-2 adding various conditions when querying data for a single table

First create a table

CREATE TABLE classmate (   ID int,   CNAME VARCHAR (5), age   INT,   math  double,   中文版 double,   Nature DOUBLE);

Fill in the table with the data

INSERT into classmate values (1, ' Wang San ', 12,34,55,12), insert into classmate values (2, ' John Doe ', 15,67,45,15); INSERT INTO Classmate values (3, ' xiaoming ', 20,84,75,50); INSERT into classmate values (4, ' Gubei ', 23,44,65,52); INSERT into classmate values (5, ' Iron brother ', 29,88,65,90);

The table shows the following

Query all math and change ' math ' to ' math ' in query results

SELECT math as ' math ' from classmate;

Find out the total number of students

SELECT cname, (math+english) as ' count out ' from classmate;

Remove repetitive English scores when searching

SELECT DISTINCT 中文版 from classmate;

Query for all cases named Xiaoming, age 20

SELECT * FROM classmate WHERE cname = ' xiaoming ' and age = 20;

Query math score greater than 50 points or English score greater than 60 points in all cases

SELECT * from classmate WHERE math >50 OR 中文版 > 60;

Query for all cases where math scores are greater than or equal to 50 and less than or equal to 70 points (using between and)

SELECT * from classmate WHERE math between and 70;

Check all cases where natural results are not equal to 15 points

SELECT * from classmate WHERE Nature <> 15;

Querying for all cases where natural results are NOT NULL

SELECT * FROM classmate WHERE nature are not NULL;

Query for all cases where the natural result is neither null nor an empty string

<> ‘‘;

Find out all about all the students surnamed Wang

SELECT * FROM classmate WHERE the cname like ' King% ';

Query all cases of students with a white name

SELECT * FROM classmate WHERE cname like '% White% ';

Query two words of the first name, the surname is Gu's all cases

SELECT * FROM classmate WHERE cname like ' gu _ ';

The total of English in the query list

SELECT SUM (中文版) from classmate;

The average score of mathematics in a query list

SELECT AVG (math) from classmate;

Query math is the name of the student with the highest score

SELECT Cname,max (math) from classmate;

Query nature is the lowest score of the classmate's name

SELECT cname,min (nature) from classmate;

Find out how many students there are in the class

SELECT COUNT (ID) from classmate;

Starting with line No. 0, query for three rows of records (line No. 0 is the actual first row, and so on)

SELECT * from classmate LIMIT 0, 3;

Mysql-2 adding various conditions when querying data for a single table