Python--mysql the operation of a table record

Table record Add and delete change---Insert table record inserts

--Inserting a piece of data

INSERT [INTO] table_name (field name,...) Values (value,...);

---inserting multiple data

INSERT [INTO] table_name (field name,...) Values (value,...),

(Value,...),

...

(value,...);

---modify table record UPDATE

UPDATE table_name SET field = value, field = value ... WHERE sentence;

---delete table records

DELETE from table_name [WHERE clause];

/*    
            Delete data from the entire table if you do not follow the WHERE statement deletes            can only be used to delete a row of records delete            statement can only remove the contents of the table, cannot delete the table itself, want to delete the table, with drop            TRUNCATE Table can also delete all the data in the table, which first destroys the table and then creates a new table. Data that is deleted in this manner cannot be recovered in a            transaction. */

TRUNCATE TABLE table_name;

Query table records (focus) preparation tables and records:

CREATE TABLE table_name (

Field Name field data type [constraint],

Field Name field data type [constraint],

...

Field Name field data type [constraint],

);

INSERT [INTO] table_name (field name, ...) VALUES (value, ...),

(Value, ...),

...

(value, ...);

--Query syntax: SELECT *|field1,filed2 ... From table_name WHERE condition GROUP by field have filter ORDER by field limit number of simple queries

-- (1) SELECT [DISTINCT] *|field1,field2, ...   From table_name            --where from specifies which table to filter from, * means to find all columns, or to specify a column

-- The table explicitly specifies the column to find, and distinct is used to reject duplicate rows.

-- (2) Select can also use expressions, and can use: field as alias or: field Alias

Note: The consequences of not adding commas:

from Examresult; (√)

-What would happen?----> Remember to add commas

Use the WHERE clause to filter queries

Where clause can be used:

                    --comparison operator:
                          > < >= <= <>! = (The following two bars are not equal)
                         between and 100 values between 80 and 100
                  The        in (80,90,100) value is 80 or 90 or
                         like ' yuan% '   

Like ' Li ___ '
/ *   % means any number of characters    _ means a character Li _, two _ means two characters: __ */

　　　　　　　

-- logical operators

logical operators and or not can be used between multiple conditions

Order by sort

Specifies the sorted column, which is either the column name in the table or the alias specified after the SELECT statement.
Select *|field1,field2 ... from tab_name order by field [asc| DESC]

ASC Ascending, desc Descending, where ASC is the default value the ORDER BY clause should be at the end of the SELECT statement. 

GROUP BY group Query

-- Note that each group will only show the first record when grouped by grouping criteria

The group by sentence can then be followed by multiple column names, or with the HAVING clause, to filter the results of GROUP by. 

/* have                   and where both can be further filtered query results, the difference is:                     <1>where statement can only be used in the filter before grouping, having can be used in the filter after grouping;                     <2 > where the WHERE statement can be replaced with the                     <3>having can be used in the aggregation function, where it does not work.                   */

-- Group_concat () function

SELECT Id,group_concat (name), Group_concat (JS) from the Examresult GROUP by ID;

Aggregation functions

--all records in the <1> statistics-            - count (column name): Count rows  

-- SUM (column name): Statistics The contents of rows that meet the criteria and

-- AVG (column name): Statistics the column average

-- Max, min: Statistical maximum and minimum values

Limit record Bar number limits

SELECT * from Examresult limit 1; SELECT * from Examresult limit 2,5;        --  Skip the first two entries to show the next five records SELECT * from Examresult limit 2, 2;

Regular expressions

SELECT * FROM employee WHERE emp_name REGEXP ' ^yu ';　　SELECT * FROM employee WHERE emp_name REGEXP ' yun$ '; SELECT * FROM employee WHERE emp_name REGEXP ' m{2} ';

Python--mysql the operation of a table record