MySQL Statement summary

Source: Internet
Author: User

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

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.