MySQL Frequently Used Command (II)

CRUD (Create, Retrieve, Update, Delete)

1. Basic Retrieve Usage

1
2
3
select * from <table_name>;
# assign certain columns
select <col1>, <col2>,... from <table_name>;

2. Create a new data item

1
2
3
4
5
6
7
8
# insert all columns as a data item
insert into <table_name> values(...);
# insert several columns as a data item
insert into <table_name>(col1, col2, col3, ...) values(value1, value2, value3, ...);
# insert more than one data items (all columns) into assigned table
insert into <table_name> values(...), (...), ...;
# insert more than one data items (assigned columns) into assigned table
insert into <table_name>(col1, col2, col3, ...) values(value1, value2, value3, ...), (valueI, valueII, valueIII), ...;

3. Update

1
2
# update the value of data item
update <table_name> set col1=value1, col2=value2, col3=value3, ... where [condition]

4. Delete

1
2
# delete data item 
delete from <table_name> where [condition]

Conditional retrieval

keyword where supports many kinds of operators, such as:

  • comparable operators

    1
    2
    3
    4
    5
    6
    7
    8
    '''
    1. =
    2. <
    3. >
    4. <=
    5. >=
    6. != or <>
    '''

  • logical operators 

    1
    2
    3
    4
    5
    '''
    1. and
    2. or 
    3. not
    '''

  • fuzzy queries

    1
    2
    3
    4
    5
    '''
    1. like
    2. % stands for multiple arbitrary characters
    3. _ stanfs for one arbitraty character
    '''

  • scope queries

    1
    2
    3
    4
    '''
    1. in stands for a inconsecutive scope
    2. between ... and ... stands for a consecutive scope
    '''

  • null judgment

    1
    2
    3
    4
    # judge null
    is null
    # judge not null
    is not null

  • priority
    The order of priority from high to low is : () > not > comparable operators > logical operators.

    and has higher priority than or

Sort

For checking out data easily, and make data ordered.

1
2
3
4
5
select * from <table_name> order by <col1> asc|desc [, col2 asc|desc, ...	];
# Search for undeleted student information, in ascending order by name
select * from students where is_delete=0 order by name;
# Show all students, sort by age first from oldest -> youngest, and when age is the same sort by height from tallest -> shortest
select * from students order by age desc, height desc;

Aggregation functions

In order to obtain statistics quickly, the following five aggregation functions are often used

  1. count (*) 

    1
    2
    # Check the total number of students
    select count(*) from students;

  2. max(col_name)

    1
    2
    # Check the maximum number of girls
    select max(id) from students where gender = 2;

  3. min(col_name)

    1
    2
    # Query the minimum number of students that have not been deleted
    select min(id) from students where is_delete = 0;

  4. sum(col_name)

    1
    2
    # Check the total age of the boys
    select sum(age) from students where gender = 1;

  5. avg(col_name)

    1
    2
    # Query the average number of undeleted girls
    select avg(id) from students where gender = 2 and is_delete = 0;

Group

1. group by

  1. Group by: The query results are grouped according to one or more fields. The query results with the same field value belong to a group
  2. Group BY can be used to group a single field or multiple fields
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1|小明 | 18|180.00|女| 1|
| 2|小月月  | 18|180.00|女| 2|
| 3|彭于晏  | 29|185.00|男| 1|
| 4|刘德华  | 59|175.00|男| 2|
| 5|⻩蓉    | 38|160.00|女| 1｜
| 6|凤姐		| 28|150.00|保密| 2｜
|7|王祖贤	 | 18｜172.00|女| 1|
|8|周杰伦	 | 36|NULL|男| 1|
| 9|程坤		| 27|181.00|男| 2|
|10|刘亦菲	 | 25|166.00|女| 2|
|11|金星 		| 33|162.00｜中性｜3｜
|12|静香 		|	12|180.00｜女| 4|
|13|周杰 		| 34|176.00|女| 5|
|14|郭靖 		| 12|170.00|男| 4|
+----+-----------+------+--------+--------+--------+-----------+
select gender from students group by gender;
+--------+
| gender |
+--------+
|男| 
|女| 
|中性 | 
|保密 | 
+--------
# Group by has little practical meaning when used alone

2. group by + group_concat()

  1. Group_concat (field name) can be used as an output field.

  2. After grouping, use group_concat() to place the set of values of a field in each group based on the grouping result

us

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
select gender from students group by gender;
+--------+
| gender |
+--------+
|男| 
|女| 
|中性 | 
|保密 | 
+--------+
select gender,group_concat(name) from students group by gender; 
+--------+-----------------------------------------------------------+ 
| gender | group_concat(name) | 
+--------+-----------------------------------------------------------+ 
| 男 | 彭于晏,刘德华,周杰伦,程坤,郭靖 | 
| 女 | 小明,小月月,⻩蓉,王祖贤,刘亦菲,静香,周杰 | 
|中性 |金星 | 
|保密 |凤姐 | 
+--------+-----------------------------------------------------------+
select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男 | 3,4,8,9,14 | 
| 女 | 1,2,5,7,10,12,13 | 
|中性 |11 | 
|保密 |6 | 
+--------+------------------+

3. group by + aggregation functions

  1. Inspired by group_concat(), we can count the set of values of a field for each group. We can also operate on the set of values using the set function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
select gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age)    |
+--------+----------------------+
| 男 | 29,59,36,27,12 | 
| 女 | 18,18,38,18,25,12,34 | 
|中性 |33 | |保密 |28 | 
+--------+----------------------+
-- 分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender; 
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男
| 女
| 中性
| 保密 
+--------+----------+
-- 分别统计性别为男/女的人的个数
select gender,count(*) from students group by gender; 
+--------+----------+
| gender | count(*) |
+--------+----------+
|男			|5					|
|女			|7					|
|中性		  | 1				 |
|保密			| 1				 |
+--------+----------+

4. group by + having

  1. Condition expression: used to specify conditions after a group query to output the query results

  2. Having acts the same as where, but having can only be used with group by

1
2
3
4
5
6
7
select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+ 
| gender | count(*) | 
+--------+----------+
|男			 |5					| 
|女			 |7					| 
+--------+----------+

Get partial rows

When the volume of data is too large, looking at the data on a page can be a hassle

1
2
3
4
5
6
7
select * from <table_name> limit <start>,<count>
# Query the first 3 lines of male information
select * from students where gender = 1 limit 0, 3;
''' Known: each page displays m data, the current display of the n page to find the total number of pages: this section of logic will be implemented in Python laterQuery the total number of items P1 by dividing P1 by m to get P2 if divided evenly P2 is the total number of pages if not P2 +1 is the total number of pages Find the data on page n
'''
 select * from students where is_delete=0 limit (n-1)*m,m

Join queries

If the query result comes from multiple tables, you need to join the tables into a large data set and select the appropriate column to return the query result. Mysql supports three types of join query:

  1. The result of an inner-join query is the data that is matched by the two tables
  2. Right join query The query result is the data matched by the two tables. The unique data of the right table is filled with null for the data that does not exist in the left table
  3. The result of the query is the data that matches the two tables, the data that is unique to the left table, and the data that does not exist in the right table are filled with null
1
2
3
4
5
6
7
8
9
select * from <table1> inner|left|right join <table2> on <table1.col> = <table2.col>;
# Use the left join to query the class table with the student table
select * from students inner join classes on students.cls_id=classes.id;
# Use the left join to query the class table with the student table
select * from students left join classes on students.cls_id=classes.id;
# Use the right join to query the class table and the student table
select * from students right join classes on students.cls_id=classes.id;
# Search for student name and class name
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;

Self-correlation

Two tables waste storage space, thus, there is a method to combine two tables into one table.

Subquery

  1. Subquery concepts

When a SELECT statement is embedded with another SELECT statement, the embedded SELECT statement is called a subquery statement

  1. Main Query Indicates the primary query object, the first SELECT statement

  2. Relationship between main query and sub-query

Subqueries are embedded in the main query and subqueries are secondary to the main query, either as conditions or as data sources and a subquery is a statement that can stand on its own, a complete SELECT statement

  1. Subquery categories

Standard quantum query: The result returned by the subquery is a single data (one row and one column) column Subquery: the result returned by the subquery is a column (one column and many rows)

Row subquery: Returns one row of results (one row with multiple columns)

4.1 Standard quantum query

  1. Check the average age of students in the class

  2. Search for students older than the average age

1
select * from students where age = (select avg(age) from students);

4.2 Column level subquery

Query also has the student in the class of all class names

1
select name from classes where id in (select cls_id from students);

4.3 Row-level subquery

Needs: Find the oldest and tallest student in the class

Row element: Multiple fields are combined into a row element that is used in row-level subqueries

4.4 Use specific keywords in subqueries

in the scope of

Format: main query WHERE condition in (column subquery)

Three normal form

1NF

  • After studying and summarizing the problems in use, some specifications for designing databases are proposed, which are called Normal forms.
  • At present, there are a total of 8 normal forms that can be traced.
  • Generally, the first normal form (1NF) needs to follow the three normal forms: the atomicity of columns is emphasized, that is, columns cannot be divided into other columns.

2NF

Second normal form (2NF): The first is 1NF, and contains two other parts. The first is that the table must have a primary key; The second is that columns that are not included in the primary key must depend entirely on the primary key, not just part of it.

3NF

Third normal form (3NF): The first is 2NF. In addition, non-primary key columns must depend directly on the primary key, and there can be no passing dependencies. Non-primary key column A depends on non-primary key column B, and non-primary key column B depends on primary key condition.

E-R model

  • Entity describes a class and its attributes.
  • Relationship describe corresponding rules between two entities, including 1 to 1, 1 to many, many to many.
  • A relationship is also A type of data that needs to be stored in A table through A field.
  • If entity A is 1-to-1 for entity B, create A field in Table A or table B to store the primary key value of another table

Logical deletion

  • For important data, physical deletion is not desired.
  • Once the data is deleted, the data cannot be retrieved.
  • Delete solution: Set the column of isDelete.
  • The type is bit, indicating logical deletion

Foreign key constraints

  • Foreign key constraints: Validation of data
  • Keyword: foreign key, only InnoDB database engine supports foreign key constraint
  • How do you update foreign key constraints on existing tables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
alter table goods add foreign key (cate_id) references goods_cates(id);
# while creating table, set foreign key
# note: foreign keys' type should be same as associated tables.
create table goods(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);
# how to cancel foreign key constraints
show create table goods;
alter table goods drop foreign key <foreign_key_name>;
# In real process of development, foreign key constraints are less used due to reducing efficiency of updating tables.

Extension

  1. Primary Key Super Key Candidate Key Foreign key

Primary key: A combination of data columns or attributes in a database table that uniquely and completely identify the stored data object. A data column can have only one primary key, and the value of the primary key cannot be missing, that is, it cannot be Null. Hyperkey: The set of attributes that uniquely identify a tuple in a relation is called the hyperkey of a relational schema. A single attribute can act as a superkey, and multiple attributes can be grouped together as a superkey. A hyperkey contains a candidate key and a primary key. Candidate key: is the minimum super key, that is, the super key with no redundant elements. Foreign key: The primary key of another table that exists in one table is called the foreign key of this table.

  1. Four characteristics and meanings of database transactions

Four essential elements of proper execution of database transaction transanction. Fixed color: ACID, Atomicity, Correspondence, Isolation, Durability. Atomicity: All the operations in the whole transaction are either complete or incomplete, and cannot be stalled somewhere in between. If an error occurs during the execution of a transaction, it will be rolled back to the state before the transaction started, as if the transaction had never been executed. Consistency: The integrity constraints of the database are not broken before and after the transaction. Isolation: The isolated state performs transactions as if they were the only operation performed by the system at a given time. If there are two transactions, running at the same time and performing the same function, the isolation of the transactions ensures that each transaction is recognized in the system as only that transaction is using the system. This property is sometimes called serialization, and in order to prevent confusion between transaction operations, it is necessary to serialize or serialize requests so that only one request is used for the same data at a time. Persistence: After a transaction is completed, changes made by the transaction to the database are persisted in the database and cannot be rolled back.

  1. Database Optimization 3.1 SQL Statement Optimization

    1. Try to avoid using it in WHERE clauses! = or & lt; > Operator, otherwise the engine will abandon the use of indexes and do a full table scan.

    2. Try to avoid null value judgment in the WHERE clause, otherwise the engine will give up using the index and carry out the full table scan, such as: Select id from t where num is null You can set the default value 0 on num, make sure that the num column in the table has no null value, and then query as follows: Select id from t where num=0

    3. Replacing in with exists is a good choice in many cases.

    4. Replacing the HAVING clause with the WHERE clause because HAVING will only filter the result set after all records have been retrieved

3.2 Index Optimization

Recommendation: http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

3.3. Database structure optimization

  1. Paradigm optimization: such as eliminating redundancy (saving space).
  2. Anti-normal pattern optimization: for example, add redundancy (reduce JOIN).
  3. Partition table: Partition separates data physically, and data from different partitions can be stored in data files on different disks. So, when the query on the table, only need to brush stroke in the table partitioning, rather than a full table scan, significantly shorten the query time, the other in a different disk partition will also be on the table data scattered in different disk I/O, an elaborate set of partition of disk I/O data transmission can be evenly spread out. This method can be used for time tables with large amount of data. Table partition can be automatically created by month.
  4. Split is actually divided into vertical split and horizontal split: Case: simple shopping system temporarily involves the following table: 1. Product table (data volume: 10W, stable) 2. Order table (data volume: 200W, increasing trend) 3. Take mysql as an example to describe horizontal and vertical splitting. The order of magnitude that mysql can tolerate is from millions of static data to tens of millions of static data. Vertical splitting: Solve the problem: The IO competition between tables does not solve the problem: the pressure of increasing data volume in a single table. Put the product table and user table on the same server and the order form on the same server Horizontally split: Solve the problem: The pressure of data volume growth in a single table does not solve the problem: table to table I/O contention solution: The user table is split into male user table and female user table by gender the order table is split into completed order and unfinished order by completed and completed product table the unfinished order is placed on one server the completed order table box the male user table is placed on one server the female user table is placed on one server (women love shopping ha ha)

3.4. Server hardware optimization