Shulou(Shulou.com)06/01 Report--

I. Addition, deletion and correction of data

1.1 insert records

1.1.1 insert single data

INSERT INTO table name (field name 1, field name 2,……, field name N)

VALUES (field 1 value, field 2 value,..., field N value);

1 This is to bring all the fields

INSERT INTO student (id,`name`,pwd,age,flush_time,record)

VALUES

(1,'Li Rong','2212o1uwi2ui 2', 23,'2017-09-13 20:03: 45',

Hello, everyone. I am Li Rong, graduated from Class 1014, City College. My hobbies are basketball, badminton, football, tennis and so on.

② If all fields are present and the order of the corresponding data table fields is correct, then (field name 1, field name 2,..., field name N) can be omitted, such as:

3 If the fields are not in order and not all fields are to be filled in, list the fields you want to fill in and their order:

INSERT INTO table name (field name n, field name m,……, field name x)

VALUES (field n value, field m value,…, field x value);

We query the data inserted in the three cases just now, and the results are as follows:

Because I set id as primary key, and self-growing, and flush_time is also if there is no value written, then write the default value of 2000-01-01 00:00:00.

1.1.2 Insert multiple data

When listing fields and their order, the order of subsequent record fields must be consistent.

1.2 Single table query

1.2.1 Query all fields and all records

SELECT * FROM table name;

1.2.2 Query desired fields

SELECT field 1, field 2, field 3… FROM table name;

1.2.3 Conditional Inquiry

SELECT * FROM table name WHERE conditional expression [AND| OR conditional expression 1……];

Such as the following cases:

#Query with relational expression (equal, not equal, greater than, less than, greater than or equal to, less than or equal to)

SELECT * FROM table name WHERE field value = 'a qualified value';

#queries with IN keyword

SELECT * FROM table name WHERE field value [NOT] IN (field value 1, field value 2,…);

NOT IN:

#Range query with BETWEEN

SELECT field 1, field 2,…… FROM table name WHERE a field [NOT] BETWEEN left AND right;

#Query with LIKE

SELECT field 1, field 2,…… FROM table name WHERE field LIKE 'string';

where the string can be such: (_: means there is and only one digit, %: means 0 or more digits)

a_: represents a two-digit string beginning with the letter a;

a%: represents a string of one or more digits beginning with a;

a_%: represents a string of two or more characters starting with a, at least two characters. And so on.

_a: two digits ending in a;

%a: one or more digits ending in a:

_a_:..................

#null query

SELECT field 1, field 2,… FROM table name WHERE field IS [NOT] NULL;

#with AND| Multiple conditional queries for OR:

Repeat the query

SELECT DISTINCT field FROM table name;

It was originally like this:

#Group queries GROUP BY are generally used with aggregate functions.

Commonly used:

SELECT field n,GROUP_CONCAT(field m) FROM table name GROUP BY field n;

#LIMIT Query Pagination

SELECT field 1, field 2,... FROM table name LIMIT starting bit (starting from 0), query the number of records.

Aggregate functions in sql statements

For example: COUNT(field name'): count the total number according to a certain field

Sums can also be calculated separately according to grouping:

#SUM ('field name') Sum function

Group by age, and calculate the sum of the number of people and age in each group (and the sum of age and number):

#AVG (Field) Mean Value Function

#MAX (field) maximum

Find the maximum, but I also want to know the name of the oldest person here, sql statement is as follows:

#Min ('field name')

Multi-table query we write next blog.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.