mysql--4 Query

#查询的基本语法

SELECT * from table name;
The FROM keyword is followed by a table name, indicating that the data originates from this table
The column name in the table after the SELECT, if * * indicates that all columns in the table are displayed in the results
In the Column Name section following the Select, you can use as as a column alias, which appears in the result set
If you want to query multiple columns, use commas to separate

Cases:
Select Id,name from students;

Eliminate duplicate rows

Use distinct before columns in select to eliminate duplicate rows
Select distinct gender from students;

#条件

Use the WHERE clause to filter data in a table, and rows that result in true appear in the result set
The syntax is as follows:
SELECT * FROM table name where condition;
Comparison operators

equals =
Greater than >
Greater than or equal to >=
Less than <
Less than or equal to <=
Not equal to! = or <>

Cases:
Students with a query number greater than 3
SELECT * FROM students where id>3;

Account with no more than 4 query number
SELECT * FROM subjects where id<=4;

Query students whose names are not "Huang Rong"
SELECT * from students where sname!= ' Huang Rong ';

Query for students who have not been deleted
SELECT * from students where isdelete=0;

#逻辑运算符

and
Or
Not

Female students with a query number greater than 3
SELECT * from students where id>3 and gender=0;

Students with a query number less than 4 or not deleted
SELECT * from students where id<4 or isdelete=0;

#模糊查询

Like
% means any number of any characters
_ Denotes an arbitrary character

Find a student surnamed Huang
SELECT * from students where sname like "yellow";

A student surnamed Huang and whose name is a word
SELECT * from students where sname like "yellow _";

A student surnamed Huang and whose name is two words
SELECT * from students where sname like "yellow __";

Ask a student surnamed Huang or Jing
SELECT * from students where name like "yellow" or sname like "% jing%";

Query students with Dragons in their names
SELECT * from students where name is like "% dragon";

#范围查询

In means within a discontinuous range

Students with a query number of 1 or 3 or 8
SELECT * from students where ID in (1,3,8);

Between ... Expressed in a contiguous range

Students are 3 to 8 students.
SELECT * from students where ID between 3 and 8;

Students are 3 to 8 boys.
SELECT * from students where ID between 3 and 8 and gender=1;

PS: When there are multiple and in a query statement, the first match between and as a set, where statement after the left-to-right match, encountered a between with the following and first match into a group, the last is the logical operator and, Logical operators have lower precedence.

#空判断

Note: null is different from '
Empty is null

Query for students who do not fill in the address
SELECT * from students where hometown is null;

A non-null was NOT NULL

The student who filled in the address was queried
SELECT * from students where hometown are not null;

Query the girl who filled in the address
SELECT * from students where hometown are not null and gender=0;

#优先级

Parentheses, not, comparison operator (sibling to between), logical operator
and than or, if it appears at the same time and you want to count or, you need to combine ()

mysql--4 Query