MySQL Query 1

1. Copy the following statements to the SQLYOG query bar

/*!40101 SET NAMES UTF8 */;

CREATE TABLE ' T_student ' (
' ID ' double,
' Stuname ' varchar (60),
' Age ' double,
' Sex ' varchar (30),
' Gradename ' varchar (60)
);
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 1 ', ' Zhang San ', ' 23 ', ' Male ', ' first grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 2 ', ' Zhang Sanfeng ', ' 25 ', ' Male ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 3 ', ' John Doe ', ' 23 ', ' Male ', ' first grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 4 ', ' Harry ', ' 22 ', ' Male ', ' third grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 5 ', ' Jenny ', ' 21 ', ' Female ', ' first grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 6 ', ' Li Na ', ' 26 ', ' Female ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 7 ', ' Wang Feng ', ' 20 ', ' Male ', ' third grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 8 ', ' Mona ', ' 21 ', ' Female ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 9 ', ' small black ', ' 22 ', ' Male ', ' first grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 10 ', ' herd ', ' 25 ', ' Male ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 11 ', ' Little Zhang San ', ' + ', NULL, ' second grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 12 ', ' small Zhang San ', ' 23 ', ' Male ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 13 ', ' Zhang Sanfeng ', ' "'", ' ", ') ', ' the ' second Grade ');

This will create a t_student table in the selected database Db_student.

Note: After the query statement is written in the inquiry bar, select all or the cursor in the statement, and then click the Execute Query (F9) button, you can see the results in the result column below.

2. Query all fields

SELECT id,stuname,age,sex,gradename from T_student;

SELECT * from T_student;

3. Query the specified field

SELECT id,stuname from T_student;

4. Where Condition query

SELECT * from T_student WHERE id=1;

SELECT * from T_student WHERE age>22;

5, with in keyword query

SELECT * from T_student WHERE age in (21,23);

SELECT * from T_student WHERE age is not in (21,23);

6. Range query with between and

SELECT * from T_student WHERE age between and 24;

SELECT * from T_student WHERE age is not between and 24;

SELECT * from T_student WHERE age is not between and the ORDER by ID DESC;

7. Fuzzy query with Like

SELECT * from t_student WHERE stuname like ' Jenny ';

SELECT * from t_student WHERE stuname like ' Zhang San% ';

SELECT * from t_student WHERE stuname like ' Zhang San _ ';

SELECT * from t_student WHERE stuname like ' Zhang San __ ';

SELECT * from t_student WHERE stuname like '% Zhang San% ';

8. Null value Query

SELECT * from t_student WHERE sex is NULL;

SELECT * from t_student WHERE sex is not NULL;

9. Multi-condition query with and

SELECT * from T_student WHERE gradename= ' first grade ' and age=23;

10. Multi-condition query with OR

SELECT * from T_student WHERE gradename= ' first grade ' OR age=23;

11, distinct to repeat the query

SELECT DISTINCT gradename from T_student;

12. Sort the query results

Default ascending:

SELECT * from T_student ORDER by age ASC;

SELECT * from T_student ORDER by age DESC;

13. Group BY group Query

SELECT Gradename, Group_concat (stuname) from T_student GROUP by Gradename;

SELECT Gradename,count (stuname) from T_student GROUP by Gradename;

SELECT Gradename,count (stuname) from T_student GROUP by Gradename have COUNT (stuname) >3;

SELECT Gradename,count (stuname) from T_student GROUP by Gradename with ROLLUP;

SELECT Gradename,group_concat (stuname) from T_student GROUP by Gradename with ROLLUP;

Note: Group_concat (), manual description: This function returns a string result with a non-null value from a join from a group

14, Limit paging query

SELECT * from T_student LIMIT 0, 5;

SELECT * from T_student LIMIT 5, 5;

SELECT * from T_student LIMIT 10, 5;

MySQL Query 1