Mysql-SQL advanced application, mysql-SQL application

Mysql-SQL advanced application, mysql-SQL application
Advanced SQL languageTypical operations

  Order

- select * from play_list order by createtime;- select * from play_list order by bookedcount desc,createtime asc;

The order by statement is used to sort the result set based on the specified column. by default, the order by statement sorts records in ascending order. If desc is used, the order by statement can also sort multiple fields in descending order, while desc only acts on one field; Distinct 

Select distinct userid from play_list; select distinct userid, play_name from play_list;
(Userid and play_named are both de-duplicated)

Distinct is used to remove duplicates and can return a unique combination of multiple columns. distinct performs sorting in the background, which consumes a lot of CPU resources; Group by havingScenario: Count the list of users who create a music ticket and the number of tickets created by each user.

mysql> select userid,count(*) AS play_num from play_list group by userid having count(*)>=2;

Group keyword userid, which must appear in the query. Generally, an aggregate function is required after the grouping keyword is queried; Like

Select * from play_list where play_name like '% boys % ';

 

Wildcard	Description
%	Replace one or more characters
_	Replace a single character
[Charlist]	Any single character in brackets
[^ Charlist] or [! Charlist]	No single character in brackets

Large tables use like with caution. Except for the % number on the rightmost side, all records in the table are queried and matched; Limit offsetScenario 4: query the tickets created in a month (10 records are displayed starting from line 1)

select * from play_list where (createtime between 1427701323 and 1430383307) limit 10 offset 6

Note: The value after offset should not be too large. If offset1000 is used, it will scan the first 1000 records, so the IO overhead will be high. Case whenCase when implements the if else function similar to the programming language to select and judge the SQL output result. Scenario 5: for unrecorded tickets (trackcount = null ), returns 0 for the number of songs in the output result.

mysql> select play_name,case when trackcount is null then 0 else trackcount end from play_list;

  JoinUse an SQL statement to query the data associated with multiple tables. Scenario 6: query the user list of the favorite "Old Boy" song

Mysql> SELECT play_fav.userid FROM play_fav inner join play_list ON play_fav.play_id = play_list.id where play_list.play_name = 'old kid ';

Another method:

Mysql> select f. userid from play_list lst, play_fav f where lst. id = f. play_id and lst. play_name = 'old kid'

 

Subqueries and Problems  Subquery Syntax:

Select userid from play_fav where play_id = (select id from play_list where play_name = 'old kid ');

Aliases do not need to use the AS keyword. You can simply add aliases with spaces. subqueries have certain performance disadvantages, which is not conducive to the optimization of the mysql performance optimizer; because the users of the inner table and the driver table are locked, the performance Optimizer of the connected driver table and the inner table is determined based on the actual situation. Why is the subquery not conducive to optimization: join is a nested loop query implementation. For example, select * from play_list, play_fav where play_list.id = play_fav.play_id; play_list driver table (left of where equal sign); play_fav of inner table (right of where equal sign ); traverse to play_list.id in northeast China, find an id, and then find it in play_fav.play_id; loop down in sequence; the inner table can be queried once or several times at this time IndexSo the basic optimization is to use a small table volume as the driving table, which reduces the number of cycles; UnionPurpose: aggregate the same fields in different tables and return them to the user in a single result set. Scenario 8: The boss wants to view all users who have created and added song lists, and query all userids in play_list and play_fav tables;

mysql> select userid from play_list         -> union         -> select userid from play_fav;

The default union operation will deduplicate the result set and does not want to deduplicate the union all operation; DML advanced syntax

• Multi-value insert: insert into table values (.....),(.....)
• Overwrite insert: replace into table values (...)
• Ignore insert: insert ignore into table values (...)
• Insert query: insert into table_a select * from table_ B

Multi-value insertion: reduces the number of database accesses and improves efficiency. Overwrite insertion and ignore insertion: simplifies the judgment of business logic; avoids primary key duplication; queries and inserts: In the table structure; insert primary key duplication, update: insert into table tb1 values (id, col1, col2) on duplicate key update col2 = ....;

mysql> insert into  a values(1,100) on duplicate key update age=100;

If id = 1 exists, the key age is changed to 100. Note that the primary key is used. If no primary key is set in the table, a new record is added; adding a primary key to Table a is:

mysql> alter table order add primary key （id）;

 

Join table updateUpdate the table age to Table A using the age in Table B:

mysql> select * from a;+----+------+| id | age  |+----+------+|  1 |  100 ||  2 |   34 ||  3 |   23 ||  4 |   29 |+----+------+mysql> select * from b;+------+------+------+| id   | name | age  |+------+------+------+|    1 | pw   |   20 ||    2 | ljb  |   30 |+------+------+------+mysql> update a,b set a.age=b.age where a.id = b.id;mysql> select * from a;+----+------+| id | age  |+----+------+|  1 |   20 ||  2 |   30 ||  3 |   23 ||  4 |   29 |+----+------+

Join table deleteUse Conditions in Table B to delete data in table;

mysql> select * from a;+----+------+| id | age  |+----+------+|  1 |   20 ||  2 |   30 ||  3 |   23 ||  4 |   29 |+----+------+mysql> select * from b;+------+------+------+| id   | name | age  |+------+------+------+|    1 | pw   |   20 ||    2 | ljb  |   30 |+------+------+------+mysql> delete a from a,b where a.id=b.id and b.name='pw';mysql> select * from a;+----+------+| id | age  |+----+------+|  2 |   30 ||  3 |   23 ||  4 |   29 |+----+------+

 

Delete Syntax:

DELETE FROM CustomersWHERE cust_id = '1000000006';

After a table is deleted, the table to be deleted must be placed after the delete operation; Built-in functions

• Objective: To master common mysql Aggregate functions and predefined Functions
• Use the group by and order by functions in SQL query statements to complete various statistical functions.

Aggregate functions

• An aggregate function is oriented to a group of data and returns a single value after aggregation.
• Basic Syntax of mysql Aggregate functions: select function (column) from table

Common Aggregate functions: Scenario: query the songs with the most playbacks

Mysql> select song_name, max (playcount) from song_list; // error query method # select song_name, no corresponding playcount; # note that the aggregate function is used to process the returned column, put back all the songs in this column; mysql> select song_name, playcount from song_list order by playcount desc limit1; // The correct subquery method: select song_name from song_list where playcount = (select max (playcount) from song_list );

 

Scenario: displays the list of songs for each album. For example:

Mysql> select album, group_concat (song_name) from song_list group by album; + ------------------ + upper + | album | group_concat (song_name) | + ------------------ + lower + | 1701 | elephant, dingxi | Straight Shooter | Good Lovin 'gone Bad, Weep No More, Shooting Star | works by Li zongsheng | from the Air Cabinet | red snow lotus | + ------------------ + ghost +

The maximum character of the group_concat connection is 1024. You can adjust the parameters. Use the aggregate function to convert the row and column of the database:

 

Predefined functions:

• Predefined functions are designed for Single-value data and return one-to-one processing results (Aggregate functions can be understood as multiple-to-one)
• Basic Syntax of predefined functions: select function (column) from table; select * from table where column = function (value)

 

 

Time Processing functions:

 

Summary