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