Collection of common dedecms SQL statements

This article has sorted out some SQL statements that DEDECMS commonly uses to help you. in the DedeCMSV5.3 system, we need to use SQL statements in many places, such as batch modification (replacement) content and data content calling, there is a template tag {dede: SQL/} specifically used to call data in the system Template. for details, refer to the template tag description in the Help Center. This article has sorted out some frequently-used SQL statements required for DEDECMS, hope to help you.
In the DedeCMSV5.3 system, we need to use SQL statements in many places, such as batch modification (replacement) content and data content calling, there is a template tag {dede: SQL/} specifically used to call data in the system Template. for details, refer to the template tag description in the help center to learn how to use this tag.
Of course, before using SQL statements and Learning SQL statements, we need to have a general understanding of the database structure of Zhimeng. we can simply understand these contents through the database description in the Help Center.

Tag name:
SQL function description: used to obtain the returned content from the template using an SQL query. applicability:
Global Basic syntax: {dede: SQL = ""} underlying Template {/dede: SQL}
Parameter description: SQL = "" complete SQL query statement underlying template field:
All fields found in the SQL statement can use [field: field name/] to call the application instance:

1. call the content of an article published by a specific member.
{Dede: SQL = 'select * from dede_archives where mid = 1 '}
[Field: title/]
{/Dede: SQL}
If mid is the user ID, see dede_archives data table fields in secondary development.
Templates help you better use the DedeCMS system with some commonly used SQL statements.
We divide SQL statements into two types: Functional and content calling. the functional type mainly performs regular operations on the database, such as {insert, update }, data Call-type operations (select), the use of the two types of SQL statements is also very simple, if it is a functional model, you only need to use it in the system background [system]-[SQL command running tool]. if it is the data calling type of the template tag, you only need to add tags in the corresponding position of the template.
Function example

Clear all registered Member Content
Delete from 'dede _ member' WHERE 'dede _ member'. 'mid '! = 1;
Truncate table 'dede _ member_flink ';
Truncate table 'dede _ member_person ';
Truncate table 'dede _ member_space ';
Truncate table 'dede _ member_tj ';
Data Call type example

Total number of published articles
{Dede: SQL = 'SELECT count (*) as cc from dede_archives '}
[Field: cc/]
{/Dede: SQL}

DEDECMS functional SQL statement sorting: 1. add custom attributes
Insert into 'dede _ arcatt' (sortid, att, attname) values (9, 'D', 'comment ');
Alter table 'dede _ Archives' modify 'flag' set ('C', 'H', 'P', 'F', 'S', 'J ', 'A', 'B', 'D') defaultNULL;

2. assign values to authors and sources in batches
UPDATE dede_archives SET writer = 'value to be assigned 'Where writer = '';
UPDATE dede_archives SET source = 'Where source = '';

3. delete comments from a specified IP address

Delete from 'dede _ feedback' WHERE 'dede _ feedback'. 'IP' = '000. 000.000.000'
000.000.000.000 is the IP address of the spam comment publisher.

4. clear keyword fields in the document
Update dede_archives set keywords =''

5. batch replace the release time, warehouse receiving time, and update timeStep 1. Add an article in the background.
Get a time, such as 14:13:32, which can be seen through the management article.
Step 2: run the SQL statement SELECT * FROM dede_archives order by id DESC limit1 in the background.
In this way, you can see all the field values in article 1 that you just added.
Observe the following data:
Pubdate: 1231846313
Senddate: 1231846313
Sortrank: 1231846313
1231846313 is the time data.
Then it is replaced.
Updated dede_archives SET sortrank = 1231846313;
UPDATE dede_archives SET senddate = 1231846313;
UPDATE dede_archives SET pubdate = 1231846313;

6. modify columns in batches to dynamic or static
UPDATE 'dede _ arctype 'set' isdefault' = '-1' dynamic
UPDATE 'dede _ arctype 'set' isdefault' = '1' static

7. replacing SQL statements in batchesUpdate 'dede _ addonarticle' set body = REPLACE (body, 'bbs ', 'Community') where body like "% Forum %"
The preceding SQL statement is used to find the phrase "Forum" in all articles and replace the forum with "community"

8. add custom attributes
Insert into 'dede _ arcatt' (sortid, att, attname) values (9, 'D', 'comment ');
Alter table 'dede _ Archives' modify 'flag' set ('C', 'H', 'P', 'F', 'S', 'J ', 'A', 'B', 'D') defaultNULL;

9. assign values to authors and sources in batchesUPDATE dede_archives SET writer = 'value to be assigned 'Where writer = '';
UPDATE dede_archives SET source = 'Where source = '';

10. change the thumbnail Directory

SQL command: update dede_archives setlitpic = replace (litpic, 'original characters', 'replacement characters ')
Example: update dede_archives setlitpic = replace (litpic, 'uplimg, 'tupiany ')
Update dede_addonarticle setbody = replace (body, 'src = "inline" http://upload.server110.com/image/20131010/10120I2Y-1.jpg') where typeid = 90;

DEDECMS data calling SQL statement sorting:
1. common Statistics Code
· A total of articles: **
{Dede: SQL = "select count (*) as c from dedede_archives where channel = 1"}. articles in total: [field: c/] {/dede: SQL}
· A total of Atlas: **
{Dede: SQL = "select count (*) as c from dedede_archives where channel = 2"} · total Gallery: [field: c/] {/dede: SQL}
· A total of software: **
{Dede: SQL = "select count (*) as c from dedede_archives where channel = 3"} a total of software: [field: c/] {/dede: SQL}
· Comments: **
{Dede: SQL = "select count (*) as c from dede_feedback"}. comments: [field: c/] {/dede: SQL}
· Total members: ** name
{Dede: SQL = "select count (mid) as c from dedede_member"}. Common member: [field: c/] name {/dede: SQL}
· Article reading: ** number of people
{Dede: SQL = "select sum (click) as c from dedede_archives"} reading: [field: c/] count {/dede: SQL}
· Updated today: **
{Dede: SQL = "SELECT count (*) AS c FROM dede_archives WHERE pubdate> UNIX_TIMESTAMP (CURDATE ()"} updated today: [field: c/] {/dede: SQL}
Total messages: {dede: SQL = "select count (*) as cc From dede_guestbook"} [field: cc/] {/dede: SQL}

2. call the posts with images attached to the Discuz Forum.
{Dede: SQL = "SELECT 'cdb_p_w_uploads '. 'aid ', 'cdb _ p_w_uploads '. 'P _ w_upload ', 'cdb _ threads '. 'tid', 'cdb _ threads '. 'fid', 'cdb _ threads '. 'subobject' FROM 'cdb _ p_w_uploads 'left JOIN 'cdb _ Threads' ON 'cdb _ threads '. 'tid' = 'cdb _ p_w_uploads '. 'tid' WHERE 'cdb _ p_w_uploads '. 'readperm' = '0' AND 'displayorder'> = '0' AND 'filetype' = 'p _ w_picpath/pjpeg 'group BY tid LIMIT 0, 2 "}

[Field: subject function = "cn_substr ('@ me', 30)"/]

{/Dede: SQL}

3. call the latest UCHOME log{Dede: SQL = "Select subject, viewnum, blogid, uid Fromuchome_blog order by blogid desc limit 0, 8 "}

[Field: subject function = "cn_substr ('@ me', 24)"/]

{/Dede: SQL}

4. ranking of member points{Dede: SQL = "Select id, userid, uname, scores From dede_member order by scores desc limit 0, 10 "}

[Field: uname/]
Points [field: scores/]

{/Dede: SQL}

5. call method of the latest enterprise product (image + title)
{Dede: SQL = "SELECT. id,. litpic,. title FROM dede_addonproduct p left join dede_archives a on. id = p. aid order by. id desc LIMIT 0, 4 "}

[Field: title/]

{/Dede: SQL} 6. call the latest code for joining the enterprise and the industry to go to the homepage.{Dede: SQL = "SELECTm. mid, m. mtype, m. userid, m. matt, c. mid, c. company, c. comface, d. ename, d. evalue, d. egroupFROM dede_sys_enum as d, dede_member as m left joinde_member_company c on m. mid = c. mid where m. mtype = 'Enterprises' andm. matt = 1 and c. vocation = d. evalue and d. egroup = 'vocation' LIMIT 0, 10 "}
[Field: company/]
[Field: ename/]
{/Dede: SQL}

7. recommended members (with user portraits){Dede: SQL = "SELECTID, type, userid, uname, matt, spacep_w_picpath
FROM dede_member where matt = 1 and type = 0
LIMIT 0, 10 "}
Avatar: if (! @ Me) @ me = 'http: // bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif ';
[/Field: spacep_w_picpath] "/>
Username: [field: uname/]
{/Dede: SQL}
[Field: spacep_w_picpath runphp = 'yes']
If (! @ Me) @ me = 'http: // bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif ';
[/Field: spacep_w_picpath]
User profile picture
[Img] http://bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif#/img]
If your profile picture is empty, you need to display the image.

8. recommendation enterprises{Dede: SQL = "SELECTm. ID, m. type, m. userid, m. matt, m. spacep_w_picpath, c. id, c. comname
FROM dede_member m left join dede_member_cominfo c on m. ID = c. id
Where m. type = 1 and m. matt = 1
LIMIT 0, 10 "}
Avatar: if (! @ Me) @ me = 'http: // bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif ';
[/Field: spacep_w_picpath] "/>
Username: [field: comname/]
{/Dede: SQL}

9. UCenter Home member call (with profile picture)
{Dede: SQL = "SELECT * FROM 'uchome _ space' WHERE 'Avatar '= 1 LIMIT 0, 10 "}




[Field: username/]
{/Dede: SQL}