Mysql statement Summary

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