MySQL Database common statements 2

On the collation of common MySQL statements, the previous article involved DDL, DML, and some simple query statements.

1:mysql Paging Query

SELECT * FROM table_name limit 5, 10; From the subscript for 5 element query, query 10 records, note: MySQL subscript starting from 0

2: Correlation Query

Select A.id,a.name,b.id,b.name from table_name A, table_name b where a.id=b.id; Table A and Table B correlate queries with field IDs

3: Compare the following statement

SELECT * FROM table_name where column_name=column_value; Querying table data based on the value of a field

SELECT * FROM table_name where column in (Column_value1,column_value2,.....); Use in to query data when there are multiple values

SELECT * FROM table_name where column in (select Column_value from table_name); Advanced subquery to query the result set of a table as a conditional query

4: Issues to be noted in building tables

Not null: A field with a non-null constraint has this constraint, and when a value is inserted into a table, it cannot be empty.

Default ' m ': defaults to ' m ' after a table is established

Primary key: Set a PRIMARY KEY constraint, which is to set a marker for a field, Auto_increment: Auto increment step is 1

Is null: When the Judge field is empty, use is instead of =

5: Fuzzy query like _%

SELECT * FROM table_name where name is like%user_;

%: Represents any character _: represents a character

6: > < = = = <> <= >= logic-judged symbols, which are often used in combination with any

7:exists whether a field exists in the table, the example is as follows

SELECT * from emp e where empno>0 and exists (SELECT ' x ' from Dept D where E.deptno=d.deptno and d.loc= ' Beijing ');

MySQL Database common statements 2