MySQL Statement summary

The previous period of time and students received the project was finally completed, the project is mainly to do reports, involving a lot of SQL statements, so take this summary.

I. BASIC 1. Database-related commands

A> Create a database

CREATE database name

B>: Creating a database and setting the default character set

CREATE database name DEFAULT CHARACTER SET UTF8;

C>: Deleting a database

DROP database name

2. Data Sheet related commands

A> Delete a table

DROP table name;

B> new Table

DROP table IF EXISTS name; CREATE Table table name (' ID '  INT NOT NULL auto_increment, ' username ' VARCHAR ' = ' not  null ' COMMENT ' login ', ' Passwo Rd '  CHAR (+) not NULL DEFAULT ' COMMENT ' login password ', PRIMARY key (' id '),   --primary key index (' username ')    --index) Engine=in Nodb DEFAULT Charset=utf8 comment= ' new table ';

C>. modifying table names

D> Add a column

E> Delete a column

F> a few simple SQL statements

Select: SELECT * from table name where scope; Insert: INSERT INTO table name (field name 1, field name 2) VALUES (field value 1, field value 2); Delete: Delete from table name where scope; update: The updated table name SET field  name = field value where range; Lookup: 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: 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; Max: select max (field name) as MaxValue from table name; min: SELECT MIN (field name) as MinValue from table name;

G> Give database user permissions (each database creates a separate user)

CREATE user ' project users ' @ ' localhost ' identified by ' Project user password ';  

Or

GRANT all privileges the on database name. * To user name @ '% ' identified by ' user password ';(with '% ' means 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 a view

DROP view name;

M> Union query (this block has a lot of content, I only present the basic)

Result set Column Union: Join, here I used to use the 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, formatted as follows (note that the name and number of columns must be the same):

SELECT a.a as col1, a.b as col2 from Aunion allselect B.C as col1, b.d as col2 from B;

N> grouping: Group by generally used in conjunction with COUNT,SUM,MAX,MIN,AVG

SELECT SUM (field name 1) from table name GROUP by field name 2;

O> Top 10 records

SELECT * FROM table name limit 0,9; (MySQL does not have a top syntax, limit is usually used to page pagination)

P>. sort (desc descending; ASC ascending; default ascending)

SELECT * FROM table name ORDER by column name DESC;

Second, advanced

A> to go heavy, generally used DISTINCT

SELECT DISTINCT * from table name; SELECT DISTINCT (column name) from table name;

b> Copy table

CREATE Table table Name 2 as   (     SELECT * FROM table name 1   )        

C> Query Analyzer: Add explain or desc in front of SQL to view the statement execution

EXPLAIN  SELECT * from table name;

Third, paste the report when you write some of the disgusting SQL

1. Query the eligible item number according to the query criteria

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.--The total amount of all budget accounts corresponding to the query 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,&NBSP;&NBSP;&NBSP;&Nbsp;    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.--Check the control amount and expenditure information of each department under the specific account (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 (' All ', ' ') ' GROUP by XCS1.DEPT,XCS1. Detailid;

4. A list of messages similar to QQ space

--like me. Select tf.id,1 as Typeid,c.avatar as Avatar, c.name,ct.catalog as catalog, ct.id as MessageId, ct.content as TRENDSC  Ontent,null as Replycontent, tf.create_time as createtime,tf.read as Isreadfrom Trends_fork TF left joins Cust_trends CT on  tf.trends_id = Ct.id Left JOIN customer C on c.id = Tf.customer_idwhere tf.master_id = 1UNION all--give 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.cre Ate_time as createtime,cb.read as Isreadfrom Cust_board CB left JOIN customer C on cb.customer_id = C.id WHERE Cb.master_i D = 1UNION all--comment My dynamic select tr.id,3 as TypeId, C.avatar as Avatar, C.name, ct.catalog as catalog, ct.id as MessageId, C  T.content as Trendscontent, tr.content as replycontent,tr.create_time as createtime,tr.read as IsReadFROM trends_reply tr  Left join Cust_trends ct in tr.trends_id = Ct.id left JOIN customer C on c.id = Tr.customer_idwhere tr.master_id = 1UNION all--replied to my comments (including a direct reply to myComment, comment on my dynamic responses) SELECT tra.id,4 as typeid,c.avatar as Avatar, C.name, ct.catalog as catalog, tra.trends_id as Messageid,c T.content as Trendscontent, tra.content as Replycontent, tra.create_time as createtime,tra.read as IsReadFROM Trends_repl Yat tra left join trends_reply tr in tra.reply_id = Tr.id left JOIN customer c in c.id = tra.customer_id LEFT Join cust_t Rends ct on ct.id = tr.trends_idwhere tra.at = 1 OR (tra.master_id = 1 and tra.customer_id! = 1) UNION all--Message Board reply to my message Sele  CT cbr.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

MySQL Statement summary