Python Learning (18)--Database (iii)

Reprinted from Http://www.cnblogs.com/linhaifeng/articles/7356064.html

I. Data manipulation 1. Inserting data Insert

1. Insert full data (sequential insert)    syntax One:    insert into table name (Field 1, Field 2, Field 3 ...) field N) VALUES (value 1, value 2, value 3 ...) Value n);    Syntax two:    INSERT into table name values (value 1, value 2, value 3 ...) Value n); 2. Specify field Insert data    syntax:    insert INTO table name (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...); 3. Insert multiple record    syntax:    insert INTO table name values        (value 1, value 2, value 3 ...) Value n),        (value 1, value 2, value 3 ...) Value n),        (value 1, value 2, value 3 ...) Value n);         4. Insert Query result    syntax:    insert INTO table name (Field 1, Field 2, Field 3 ...) Field N)                     SELECT (Field 1, Field 2, Field 3 ...) field N) from table 2                    WHERE ...;

View Code2. Updating data

Syntax:    Update table name set        field 1= value 1,        field 2= value 2,        where CONDITION; Example:    UPDATE mysql.user Set Password =password (' 123')         where userand host= ' localhost ';

View Code3. Delete data

Syntax:    Delete from table name         where conition; Example:    DELETE from Mysql.user         where password="; Practice:    Update the MySQL root user password for mysql123    remove all users except the root user logged on locally

View Code4. Querying Data Select

　1) Single-table query

SELECT Field 1, Field 2 ... From table name                  WHERE condition                  GROUP by field have                  filter                  ORDER by field limit                  number of bars

Grammar

Priorities in Focus: Priority of keyword execution  from wheregroup Byhavingselectdistinctorder bylimit 1. Find the table: from 2. Take the constraints specified in where, go to the File/table to take out a record 3. Group BY, if no group by, is grouped 4. To filter the results of a group 5. Perform select6. Go to weight 7. Sorts the results by criteria: ORDER BY8. Limit the number of display bars for the result

execution Priority (emphasis)

See more: http://www.cnblogs.com/linhaifeng/articles/7372774.html

#Simple QuerySELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee; SELECT*from employee; SELECT name,salary from employee;#avoid repeating distinctSELECT DISTINCT post from employee; #Query by ArithmeticSELECT name, salary*12from employee; SELECT Name, Salary*12As annual_salary from employee; SELECT Name, Salary*12annual_salary from employee;#Defining display FormatsThe CONCAT () function is used for connection strings SELECT CONCAT ('Name:', Name,'Annual Salary:', salary*12) as annual_salary from employee; Concat_ws () The first parameter is a delimiter SELECT concat_ws (':', name,salary*12) as annual_salary from employee;

Simple Query

the WHERE clause can be used:  and the value between 10 and 20 in (80,90,100) value is 10 or 20 or'egon%'     pattern can be% or _,    % means any number of    characters and or not  

where constraint

#1: Single condition querySELECT name from employee WHERE Post='Sale'; #2: Multi-criteria QuerySELECT name,salary from employee WHERE Post='Teacher'and salary>10000;#3: keyword between andSELECT name,salary from employee WHERE salary between10000 and 20000; SELECT name,salary from employee WHERE salary not between10000 and 20000; #4: keyword is null (determines if a field is null cannot be used as an equals sign, need to be)SELECT name,post_comment from the employee WHERE post_comment is NULL;            SELECT name,post_comment from employee WHERE post_comment are not NULL; SELECT name,post_comment from employee WHERE post_comment="'; Attention"'is an empty string, NOT NULL PS: Perform update employee set post_comment="'where id=2; And then check it out, there's going to be a result.#5: Keyword in collection querySELECT name,salary from employee WHERE salary=3000 or salary=3500 or salary=4000 or salary=9000 ; SELECT name,salary from Employee WHERE salary in (3000,3500,4000,9000) ; SELECT name,salary from employee WHERE salary not in (3000,3500,4000,9000) ;#6: Keywords like fuzzy queryWildcard characters '%' SELECT*From employee WHERE name is like'eg%'; Wildcard ' _ ' SELECT*From employee WHERE name is like'al__';

View Code

# 1. First make it clear: The grouping occurs after the where, that is, the grouping is based on the records obtained after the Where # 2. Grouping refers to classifying all records according to one of the same fields, such as grouping positions on employee information sheets, or grouping by gender # 3. Why should we group them? take     the maximum wage per department to get the number of    men and women in each department tips: The field behind the word ' every ' is the basis of our group #4, the premise:    can be grouped by any field, but after grouping, such as group by post, can only view the post field, if you want to view the information in the group, need to use the aggregation function

Group query: Group by

use GROUP BY keyword grouping    alone SELECT post from the employee GROUP by post;    Note: We group by post field, then the field of the select query can only be post, want to get other related information within the group, need to use the function GROUP by keyword and group_concat () function with    SELECT post,group_ CONCAT (name) from the employee GROUP by post; # Group by post and view member names within a group     SELECT Post,group_concat (name) as Emp_members from the employee GROUP by post; Group BY is used    with the aggregation function from the employee group by post;  # GROUP by Post and see how many people are in each group Stress: If we use unique fields as the basis for grouping, then each record becomes a group, and this grouping does not make sense for a field value between multiple records, which is usually used as the basis for grouping

View Code

# emphasis: Aggregation functions aggregate the contents of a group, and if there is no grouping, the default set of Example:    SELECT COUNT (*) from employee;    SELECT COUNT (*) from employee WHERE depart_id=1;    SELECT MAX (salary) from employee;    SELECT MIN (salary) from employee;    SELECT AVG (salary) from employee;    SELECT SUM (salary) from employee;    SELECT SUM (Salary) from employee WHERE depart_id= 3;

Aggregation Functions

having the same place as where is!!!!!! #  #1. Where occurs before grouping group by, so there can be any field in the where, but the aggregate function must never be used.  #2. Having a group by, and having the ability to use grouped fields, cannot be taken directly to other fields, you can use aggregate functions

Having filter

sort    by single column * From the employee ORDER by salary    ; * From the employee ORDER by salary ASC    ; * From the employee order by salary DESC; Sort by multiple columns: Sort by age first, and if you are older, sort    by salary from   employee        ORDER by age,        salary DESC;

Query Sort: Order by

Example:     * From the employee ORDER by salary         DESC3;                    # * From the employee ORDER by salary DESC        LIMIT 0,# starting from the No. 0, the first one is queried first,            And then include this one and look      back. 5 * from the employee ORDER by salary        DESC# starting from the 5th one, That is, the 6th is queried first, and then included in the article 5

limit the number of records for a query: limit

SELECT * FROM employee WHERE name REGEXP'^ale'; SELECT* FROM employee WHERE name REGEXP'on$'; SELECT* FROM employee WHERE name REGEXP'm{2}'; summary: How to match a string where name='Egon'; WHERE name like'yua%'; WHERE name REGEXP'on$';

querying using regular Expressions

　　2) Multi-table query

# Focus: External link syntax SELECT Field List    From table 1 INNER| Left| Right JOIN table 2     = Table 2. Fields;

Multi-table connection query

1Cross Connect: No matching criteria are applied. Generate Cartesian product2Inner Connection: Only matching rows are connected3external link Left connection: Show all records of left table first4the right connection of the outside link: first display all records of the right table5full outer connection: show left and right two tables all record full outer joins: On the basis of the internal connection to increase the left side has no right and left side has no results#Note: MySQL does not support full out -of-band join#emphasis: MySQL can use this method to indirectly implement a full-outer connectionSELECT * fromEmployee left JOIN Department on employee.dep_id =Department.idunionselect* fromEmployee right Join department on employee.dep_id =department.id;#View Results+------+------------+--------+------+--------+------+--------------+| ID | name | sex | Age | dep_id | ID |    Name |+------+------------+--------+------+--------+------+--------------+| 1 | Egon |   Male |    18 |  200 | 200 |    Technology | | 5 | Liwenzhou |   Male |    18 |  200 | 200 |    Technology | | 2 | Alex |   Female |    48 |  201 | 201 |    Human Resources | | 3 | Wupeiqi |   Male |    38 |  201 | 201 |    Human Resources | | 4 | Yuanhao |   Female |    28 |  202 | 202 |    Sales | | 6 | Jingliyang |   Female |    18 | 204 | NULL | NULL | | NULL | NULL | NULL |   NULL |  NULL | 203 | Operating |+------+------------+--------+------+--------+------+--------------+#Note the difference between Union and Union: Union will remove the same record

View Code

# 1: A subquery is a query statement that is nested within another query statement.  #2: Query result of inner query statement, can provide query condition for outer query statement.  #3: Subqueries can contain: In, not in, any, all, EXISTS, and not EXISTS keywords #4: You can also include comparison operators: =,! =, >, <, etc.

Sub-query

Python Learning (18)--Database (iii)