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

Today, I will talk to you about the commonly used SQL sentences in Mysql, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Query time, friendly hint

Timestamp date type

$sql = "select date_format (create_time,'% Ymuri% mme% d') as day from table_name"

Int timestamp type

$sql = "select from_unixtime (create_time,'% Ymuri% mme% d') as day from table_name"

A sql returns multiple totals

$sql = "select count (*) all,"

Sql. = "count (case when status = 1 then status end) status_1_num,"

$sql. = "count (case when status = 2 then status end) status_2_num"

$sql. = "from table_name"

Update Join / Delete Join

$sql = "update table_name_1"

$sql. = "inner join table_name_2 on table_name_1.id = table_name_2.uid"

$sql. = "inner join table_name_3 on table_name_3.id = table_name_1.tid"

$sql. = "set *"

$sql. = "where *"

/ / delete join ditto.

A statement that replaces the contents of a field

$sql = "update table_name set content = REPLACE (content, 'aaa',' bbb')"

$sql. = "where (content like'% aaa%')"

Get the data of a field in a table that contains a string

$sql = "SELECT * FROM `Table name `WHERE LOCATE ('keyword', field name)"

Look up redundant duplicate records in the table

Single field

$sql = "select * from table name where field name in"

$sql. = "(select field name from table name group by field name having count (field name) > 1)"

Multiple field

$sql = "select * from table name alias where (alias. Field 1, alias. Field 2) in"

$sql. = "(select field 1, field 2 from table name group by field 1, field 2 having count (*) > 1)"

Delete redundant duplicate records in the table (keep id minimum)

Single field

$sql = "delete from table name where field name in"

$sql. = "(select field name from table name group by field name having count (field name) > 1)"

Sql. = "and primary key ID not in"

Sql. = "(select min (primary key ID) from table name group by field name having count (field name) > 1)"

Multiple field

$sql = "delete from table name alias where (alias. Field 1, alias. Field 2) in"

$sql. = "(select field 1, field 2 from table name group by field 1, field 2 having count (*) > 1)"

Sql. = "and primary key ID not in"

$sql. = "(select min (primary key ID) from table name group by field 1, field 2 having count (*) > 1)"

After reading the above, do you have any further understanding of the commonly used SQL statements in Mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.