MySQL syntax charging

1. The role of keyword delimiter

①delimiter is called "delimiter" and can often see the contents of DELIMITER $$ in statements such as compound SQL statements or stored procedures.

The meaning of ②delimiter $$ is to tell the MySQL interpreter that the end sign of the command is $$, and when the parse SQL statement encounters the $$ symbol, it can execute the SQL statement.

③delimeter defines the delimiter, which can be any symbol (as long as it does not conflict with the SQL statement).

　④ By default, delimiter is a semicolon, and in a command-line client, if a line of commands ends with a semicolon,

Then after the carriage return, MySQL will execute the command. such as entering the following statement
Mysql> select * from test_table;
Then enter, then MySQL will execute the statement immediately.

⑤ changes the default delimiter; for other symbols, remember to change back to the default , because, when executing the SQL statement, the semicolon is already a conventional symbol.

2.mysql5.1, 5.5, and other different versions of data migration errors and solutions,

The definition of the ① stored procedure is stored in the proc table in the MySQL database, and the type of the comment field in proc is of type char in the mysql5.1 version, However, in the mysql5.5 version of the text type, in these two versions of MySQL for data migration, be careful to change the type of comment .

Do not modify the words will be error "cannot load from Mysql.proc. The table is probably corrupted",

For this error message, refer to http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=28212952&id=3407891

Http://wenku.baidu.com/link?url=exER7FiGM7PSzueM4cwJtDR5lz9jO4SQc044_ A5rsbk6axiqxhodqotejl7e9oihxxhx15tqckqqxynbhhamj9ikf2vkncts3vc2qnzp8hc

3. Using \g in theMySQL command-line interface , you can turn the printed content into a columnar display, with the caption in the left column,

4. SQL statement to view stored procedure information underMySQL command-line operation Interface

Show CREATE procedure xxx stored procedure name \g

Nested If, case, when statements in a 5.sql statement:

Reference http://blog.knowsky.com/240220.htm

http://blog.csdn.net/zoohouse/article/details/5800799

6. User access Rights settings

GRANT all privileges the db1.* to ' user1 ' @ ' 192.168.1.1 ' identified by ' 1234556 ' with GRANT OPTION;

Maximum permissions GRANT all privileges on * * to ' user1 ' @ '% ' identified by ' 1234556 ' with GRANT OPTION;

7. Query the SQL that displays the ordinal,

Case study: Query TAB1 based on the field paging order, show the rank of each record, if the number of records per page is size, page number is page, the first page page number is 0, then the starting rank of each page is size*page+1.

The @rownum:=size*page,sql statement is:

Select (@rownum: [email protected]+1) as rankno,t1.* from Tab1 as T1, (select @rownum: =size*page) b ORDER by score LIMIT PA Ge,size

8.having, where is different: where the role is to group the results of the query, will not meet the where condition of the row is removed, the HAVING clause is to filter the group that satisfies the condition,

Select A,count (B) as NUM1 from TAB1 Group by A have c= ' XX ', this is wrong, there is no C column in Select, there is no C column value in the query result set, and have no way to filter C,

Should be changed to select A,count (B) as Num1,c from TAB1 Group by A have c= ' XX '

(The meaning of the Ps:group by statement is that the aggregate function is calculated after the grouping query, if it is not appropriate to display only the column values, before having to execute, group by followed by the having is generally an expression, if the value of the field is judged, Make sure that the value of the field is unique before grouping, so it is generally required to determine that the field is a grouped field.

9.Group by and have, where, order by these keywords are executed in the following order : Where, Group by, have, order by,

Reference: http://www.chengxuyuans.com/MySQL/63991.html

10. Create User: reference http://cache.baiducontent.com/c?m= 9f65cb4a8c8507ed4fece763105392230e54f73260878e482a958448e435061e5a35bfe87969585393d82f2747f41802bdb12b71350527b59ec89f4aa ae1d477719c6269304a891e59925af1dc4755d650964d99a90e97cce74199b9d3a3c82252dd22046df0809c2905&p= aa759a43d48a57f20be296375857&newp=9b6c861f86cc47ad01be9b7c1c6492695d0fc20e39d2db4c&user=baidu&fm= Sc&query=mysql%b4%b4%bd%a8%d3%c3%bb%a7&qid=a5e398780002a9d7&p1=1

Insert into Mysql.user (Host,user,password) VALUES ("localhost", "Phplamp", Password ("1234"));

Refresh System Permissions Table

Flush privileges;

11. Timestamp and date conversion:

From_unixtime (): Convert to Timestamp type time
Unix_timestamp (): Return long Shaping type time

For example:

SELECT from_unixtime (1234567890, '%y-%m-%d%h:%i:%s ')

Mysql>select from_unixtime (1249488000, '%y year%m month%d ')
->2007 Year November 20

mysql> SELECT unix_timestamp (' 2009-08-06 ');
->1249488000

12. Table structure of the same time a table data import B table:

INSERT into Table_b SELECT * from table_a

MySQL syntax charging