MySQL database basic usage-query

Basic syntax for queries
SELECT * from table name;

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

　

Eliminate duplicate rows

Use distinct before columns in select to eliminate duplicate rows

select distinct gender from students;

　　

Conditions

1. Use the WHERE clause to filter data in a table, and rows that result in true appear in the result set
2. 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 <>
• 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;

　　

logical operators

• 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;

　　

Fuzzy query

• Like keyword
• % 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 _ ';

　　

• Ask a student surnamed Huang or Jing

SELECT * from students where sname like ' yellow ' or sname like '% jing% ';

　　

Scope Query

• 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;

　　

Null judgment

• 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;

　　

Priority level

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

MySQL database basic usage-query