Practical SQL tips for 13 mysql Databases

Www.gbtags.comgb4242351.htm MYSQL, as one of the most successful open-source relational databases, has a large number of fans (I am also). In this article, we carefully collected 10 most useful mysql Query Techniques, I hope you will be pleasantly surprised. If you have a very good SQL, please leave a message to share with us! SQL, MYSQ

Http://www.gbtags.com/gb/share/2351.htm MYSQL, as one of the most successful open source relational databases, has a large number of fans (I am also), in this article, we carefully collected 10 most practical mysql Query Techniques, I hope you will be pleasantly surprised. If you have a very good SQL, please leave a message to share with us! SQL, MYSQ

Http://www.gbtags.com/gb/share/2351.htm

As one of the most successful open-source relational databases, MYSQL has a large number of fans (I am also a fan). In this article, we carefully collected 10 most practical mysql Query Techniques, I hope you will be pleasantly surprised. If you have a very good SQL, please leave a message to share with us!

SQL, MYSQL

Use CASE to redefine the Value Type

 
 

  
  
SELECT id,title,
  
  
(CASE date WHEN '0000-00-00' THEN '' ELSE date END) AS date
  
  
 FROM your_table
  
  
SELECT id,title,
  
  
(CASE status WHEN 0 THEN 'open' WHEN 1 THEN 'close' ELSE 'standby'END) AS status
  
  
 FROM your_table
 
 

Search for repeated email record Fields

 
 

  
  
SELECT email, COUNT(email) AS q
  
  
 FROM emails_table GROUP BY email HAVING q >1
  
  
 ORDER BY q DESC
 
 

Obtain records from random order

 
 

  
  
SELECT * FROM your_table ORDER BY RAND()
 
 

Use update to replace characters in a specified field

 
 

  
  
UPDATE your_table
  
  
 SET name=REPLACE(name,'gbin1.com','gbtags.com')
  
  
 WHERE name LIKE '%John%';
 
 

Reset the auto-increment value in the specified table

 
 

  
  
ALTER TABLE your_table AUTO_INCREMENT =2
 
 

The next time you insert data, the id is automatically increased to 2

Add an automatically added field column to the select statement.

 
 

  
  
set@N=0;
  
  
SELECT @N:=@N+1 AS number, name, surname FROM gbtags_users;
 
 

Use concat to connect Fields

 
 

  
  
SELECT CONCAT(name,' ',surname) AS complete_name FROM users
 
 

Use the date method to retrieve the specified part of the date type

 
 

  
  
SELECT id,title, YEAR(date_field) FROM your_table
  
  
SELECT id,title,
  
  
 CONCAT(MONTH(date_field),'/',YEAR(date_field))as new_date
  
  
 FROM your_table
 
 

In the preceding statement, the first select selects the year, and the second selects the combination of the month and the year.

Insert statements for fields of unique key (unique key) Type

 
 

  
  
INSERT IGNORE INTO tags (tag) VALUES ('good');
 
 

This insert statement can be executed multiple times, and no error is reported. Duplicate Inserts will be ignored.

Use full-text index and match search

 
 

  
  
select*from articles where MATCH(content_column) AGAINST ('music')
 
 

You must first add the full-text search index to the specified column (content_column ). Note: If you already have data in the table, no index will be created. Therefore, you need to use an empty table to execute this statement.

If the query is performed one month ago

 
 

  
  
SELECT user, count(*) AS logins
  
  
 FROM stat_log
  
  
 WHERE action='LOGIN' AND dt_when >= DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
  
  
 GROUP BY user
 
 

This statement allows you to query all records of the field dt_when a month ago.

Set the correct Character Set

 
 

  
  
SET NAMES 'utf8';
 
 

Run this statement after the connection starts.

Insert another table from one table

 
 

  
  
INSERT INTO yourtable (field1,field2,field3)
  
  
 SELECT newfield1,newfield2,'fixed value'
  
  
 FROM yourtable2
 
 

This statement can help you quickly add specific content from a table to another table.

We hope that the SQL statements we will introduce here will help you better and faster use of Mysql. If you have a lot of self-saved SQL statements, please leave us a message!