Mysql statement Summary

Source: Internet
Author: User

Mysql statement Summary

Some time ago, my project was finally completed. The project mainly involves reports and a lot of SQL statements, so I would like to summarize it.

I. Basics 1. Database-related commands

A>. Create a database

Create database Name

B>. Create a database and set the default Character Set

Create database name default character set utf8;

C>. delete a database.

Drop database Name

2. Data Table commands

A>. delete a table

Drop table name;

B>. Create a table.

Drop table if exists table name; create table Name ('id' int not null AUTO_INCREMENT, 'username' VARCHAR (60) not null default '''comment' ', 'Password' CHAR (32) not null default ''comment' login password', primary key ('id'), -- primary key index ('username') -- INDEX) ENGINE = innodb default charset = utf8 COMMENT = 'create table ';

C>. Modify the table name.

Alter table name 1 rename to table name 2;

D>. add columns.

Alter table name add column name type (for example, VARCHAR (30 ));

E>. Delete columns.

Alter table Name drop column name;

F>. Several Simple SQL statements

SELECT: SELECT * FROM table name WHERE range; INSERT: insert into Table Name (field name 1, field name 2) VALUES (field value 1, field value 2); Delete: delete from table name WHERE range; UPDATE: UPDATE table name SET field name = field value WHERE range; Search: SELECT * FROM table name WHERE field name LIKE '% Field Value %'; sort: SELECT * FROM table name order by field name 1, field name 2 [DESC]; Total number: select count as totalcount FROM table name; SUM: select sum (Field Value) AS sumvalue FROM table name; average: select avg (field name) AS avgvalue FROM table name; maximum: select max (field name) AS maxvalue FROM table name; minimum: select min (field name) AS minvalue FROM table name;

G>. Grant Database User Permissions (each database creates a separate user)

Create user 'Project USER '@ 'localhost' identified by 'Project USER password'; grant all privileges on database name. * TO project USER @ 'localhost ';

Or

Grant all privileges on database name. * TO username @ '%' identified by 'user password'; (use '%' TO connect from any address)

H>. Add a primary key.

Alter table name add primary key (column name );

I>. Create an index

Create index field name ON table name;

J>. delete an index

Drop index field name;

K>. Create a view

Create view name ASSELECT statement;

L>. Delete View

Drop view name;

M>. Joint query (there is a lot of content here, I only propose basic)

Result set column Union: JOIN. Here I usually use LEFT JOIN. The format is as follows:

SELECT a.a, a.b, b.c, b.d FROM a LEFT OUT JOIN b ON a.a = b.c;
Result set row UNION: UNION or union all, in the following format (note that the column names and numbers must be consistent ):
SELECT a.a as col1, a.b as col2 from aUNION ALLSELECT b.c as col1, b.d as col2 from b;

N>. GROUP: group by is generally used in combination with count, sum, max, min, and avg.

Select sum (field name 1) FROM table name group by field name 2;

O>. The first 10 records

SELECT * FROM table name LIMIT; (mysql does not have top syntax, limit is generally used for paging)

P>. Sort (desc in descending order; asc in ascending order; ascending by default)

SELECT * FROM table name order by column name DESC;

Ii. Advanced

A>. deduplication, commonly used DISTINCT

Select distinct * FROM table name; select distinct (column name) FROM table name;

B>. Copy a table

Create table name 2 AS (SELECT * from table name 1)

C>. query Analyzer: Add EXPLAIN or DESC to the front of the SQL statement to view the statement execution status.

Explain select * FROM table name;

Iii. Post some disgusting SQL statements written during report preparation

1. query the project numbers that meet the query conditions.

SELECT DISTINCT(xp.Id)FROM xmgl_finance_plan xfp LEFT JOIN xmgl_project xp ON xfp.proId = xp.Id WHERE 1=1AND xp.proCategory IN ('ITER973')AND xp.Id IN(0,1,2);

2. -- query the total amount of all budget accounts in the Project Plan table

SELECT  t.Pid,  xsb2.subjectName,  SUM(t.planTotalAmount)    planTotalAmount,  SUM(t.planCurrentAmount)    planCurrentAmount,  SUM(t.total_amount)    total_amount,  SUM(t.payedMoney)    payedMoney,  SUM(t.finalPayment)    finalPayment,  SUM(t.CurYearPayedMoney)    CurYearPayedMoneyFROM (SELECT        xsb.Id,        xsb.subjectName,        xsb.Pid,        SUM(xfp.planTotalAmount)    planTotalAmount,        SUM(xfp.planCurrentAmount)    planCurrentAmount,        SUM(xfp.total_amount)    total_amount,        SUM(xfp.payedMoney)    payedMoney,        SUM(xfp.finalPayment)    finalPayment,        SUM(xfp.CurYearPayedMoney)    CurYearPayedMoney      FROM xmgl_subject_budget xsb        LEFT JOIN xmgl_project xp          ON xsb.type = xp.subjectType        LEFT JOIN xmgl_finance_plan xfp          ON xsb.Id = xfp.subjectId      WHERE xp.Id = 1      GROUP BY xfp.subjectId,xsb.Id      ORDER BY xsb.Id) AS t  LEFT JOIN xmgl_subject_budget xsb2    ON t.Pid = xsb2.IdWHERE t.Pid <> 0GROUP BY t.Pid;

3. -- query the control quota and expenditure information of each department under a specific subject (optimization)

SELECT DISTINCT(xcs1.dept),xsc.subjectName,xcs2.amount,COALESCE(SUM(xfa.amount),0) payedMoney,xcs2.amount-SUM(xfa.amount) leftAmountFROM xmgl_control_subject xcs1LEFT JOIN xmgl_control_subject xcs2 ON xcs1.dept = xcs2.dept AND xcs2.detailId = 3LEFT JOIN xmgl_subject_control xsc ON xcs2.detailId = xsc.IdLEFT JOIN xmgl_finance_activity xfa ON xcs2.dept = xfa.department AND xfa.detailId IN('22','23')GROUP BY xcs1.dept,xcs1.detailId;

4. Similar to the message list in the QQ space

-- Liked me SELECT tf. id, 1 AS typeId, c. avatar AS avatar, c. name, ct. catalog AS catalog, ct. id AS messageId, ct. content AS trendsContent, null as replyContent, tf. create_time AS createTime, tf. read AS isReadFROM trends_fork tf left join cust_trends ct ON tf. trends_id = ct. id left join customer c ON c. id = tf. customer_idWHERE tf. master_id = 1 union all -- leave me a message SELECT cb. id, 2 AS typeId, c. avatar AS avatar, c. name, 3 AS catalog, cb. id AS messageId, null as trendsContent, cb. content AS replyContent, cb. create_time AS createTime, cb. read AS isReadFROM cust_board cb left join customer c ON cb. customer_id = c. id WHERE cb. master_id = 1 union all -- Comment on my dynamic SELECT tr. id, 3 AS typeId, c. avatar AS avatar, c. name, ct. catalog AS catalog, ct. id AS messageId, ct. content AS trendsContent, tr. content AS replyContent, tr. create_time AS createTime, tr. read AS isReadFROM trends_reply tr left join cust_trends ct ON tr. trends_id = ct. id left join customer c ON c. id = tr. customer_idWHERE tr. master_id = 1 union all -- reply to my comments (including directly replying to my comments and commenting on others' replies under my dynamics) SELECT tra. id, 4 AS typeId, c. avatar AS avatar, c. name, ct. catalog AS catalog, tra. trends_id AS messageId, ct. content AS trendsContent, tra. content AS replyContent, tra. create_time AS crea TeTime, tra. read AS isReadFROM trends_replyat tra left join trends_reply tr ON tra. reply_id = tr. id left join customer c ON c. id = tra. customer_id left join cust_trends ct ON ct. id = tr. trends_idWHERE tra. at = 1 OR (tra. master_id = 1 AND tra. customer_id! = 1) union all -- the SELECT cbr message returned from the message board. id, 5 AS typeId, c. avatar AS avatar, c. name, 3 AS catalog, cb. id AS messageId, cb. content AS trendsContent, cbr. content AS replyContent, cbr. create_time AS createTime, cbr. read AS isReadFROM cust_board_reply cbr left join cust_board cb ON cbr. board_id = cb. id left join customer c ON cbr. customer_id = c. idWHERE (cbr. board_customer_id = 1 AND cbr. customer_id! = 1) OR (cbr. master_id = 1 AND cbr. customer_id! = 1) order by createTime DESCLIMIT 2, 10


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.