MySQL syntax, special symbols, and the use of regular expressions

http://blog.csdn.net/pipisorry/article/details/46764495

MySQL Common display command

1. Displays the list of databases in the current database server:

mysql> SHOW DATABASES;

Note: MySQL library has the MySQL system information, we change the password and new users, is actually using this library to operate.

2. Enter the database :

mysql> use library name;

2. Display the data table in the database :

mysql> SHOW TABLES;

3, display the structure of the data table :

mysql> DESCRIBE table name;

4. Establish the database :

mysql> CREATE database name;

5. Set up the data sheet:

mysql> use library name;
mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));

6. Delete the database :

mysql> DROP database name;

7. Delete multiple data tables :

mysql> DROP table name, table name;

8. Empty the records in the table:

Mysql> DELETE from table name;

9. display the records in the table :

Mysql> SELECT * from table name;

10. insert a new field into the table :

mysql> alter tabel table name add column field name varchar (10);

10. Insert a record into the table:

mysql> INSERT into table name VALUES ("HyQ", "M");

11. Modify the field type:

Mysql> ALTER TABLE name modify column field name varchar (12);

11. Update the data in the table:

mysql-> UPDATE table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';

12. Load data into the data table in text mode:

mysql> LOAD DATA LOCAL INFILE "d:/mysql.txt" into table name;

13. Import the. sql File command:

mysql> use database name;
Mysql> SOURCE D:/mysql.sql;

14, the command line to modify the root password:

mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';
mysql> FLUSH privileges;

15. Display the database name of use:

Mysql> SELECT DATABASE ();

16. Display the current User:

Mysql> SELECT USER ();

Note:

1. The operation is performed at the prompt of MySQL, and each command ends with a semicolon.

2. SQL statements are not case sensitive

[Detailed MySQL command]

http://blog.csdn.net/pipisorry/article/details/46764495

MySQL Index usage rules

2. The best alternative data columns for indexing are those that appear in the WHERE clause, join clause, ORDER by, or GROUP BY clause.

4. Under what circumstances should the index not be built or less?
   A. Too few table records
   B. Frequently inserted, deleted, modified tables
   C. table fields that are frequently queried with the main field but with a larger index value than the main field

6. Creation of composite indexes:
   For example, there is a statement like this:

   SELECT * from users where area= ' Beijing ' and age=22;
   If we were to create a single index on area and age, the MySQL query could use only one index at a time, so while the full table scan was a lot more efficient when it was relatively non-indexed, it would be more efficient to create a composite index on the area or age two column.

   If we create a composite index (area, age,salary), then it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix

8. The establishment of composite indexes and the principle of leftmost prefixes:
   If you need to index a string data column, it is best to specify the prefix length in any appropriate case. You can index the prefixes of Char, VARCHAR, BINARY, VARBINARY, blob, and text data columns.
   Suppose you set up a composite index on a table's state, city, and zip data columns. The rows of data in the index are arranged in state/city/zip order, so they are also automatically arranged in State/city/zip order. This means that MySQL can use this index even if you specify only the state value in the query, or specify the state and city values. Therefore, this index can be used to search for a combination of data columns as follows: (State, city, Zip)
   

10. The index does not contain a column with null values
    This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.

12. MySQL queries use only one index.

    Therefore, if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
    

14. It is generally discouraged to use the like operation.

    If non-use is not available, how to use it is also an issue. Like "%a%" does not use the index and like "aaa%" can use the index.

16. Do not perform operations on columns, select * from the users where year (adddate)

18. Do not use not in operation:
    The not in operation does not use the index to perform a full table scan. Not in can be replaced with not exists.
    

[MySQL index Monitor set]

http://blog.csdn.net/pipisorry/article/details/46764495

MySQL Special symbols

% ( percent percent):
An example of a string representing any length (length can be 0): A%b represents a string of any length beginning with a and ending with B. such as Acb,addgb,ab are satisfied with the matching string _ (Lower horizontal line ):
Represents any single character example: A_b represents an arbitrary string of length 3 that begins with a and ends with B. such as ACB,AFB are satisfied with the matching string

String quotation marks:

In SQL, it is strongly recommended to use single quotation marks (') to denote string quotes. Although MySQL (and the best combination of PHP collocation) can also use double quotation marks ("), it is recommended to use single quotes in order to unify with SQL Server (a powerful database platform on the Windows platform) and Oracle (large Web site database platform). If there is also a single quote in the string, you need to replace it with two single quotation marks (' ') in SQL and the DBMS interprets it as a single quote.

Line breaks and string connectors for sql:

There are differences between MySQL (the best combination with PHP), SQL Server (a powerful database platform on the Windows platform) and Oracle (large Web site database platform), which are shown in the list below. MySQL (best combination with PHP) SQL Server (a powerful database platform on the Windows platform) Oracle (large Web site database platform) line break \ n or \ r \ n or char (10) string connector concat ()+|| or concat ()

Macro variable identifier (&):

In Oracle (large Web site database platform) has a special meaning, is a macro variable identifier, in Sqlplus execute select ' AAA BBB ' as STR from DUAL, it will prompt you to enter the macro variable value, all if there is (&) in SQL, It is recommended to enclose it in single quotation marks, such as select ' AAA ' | | ' & ' | | ' nbsp BBB ' as STR from dual it will not prompt.

MySQL Regular expression

Use of Replace and regexp
0 Comments | This entry is posted on APR 08 2010
MySQL Replace usage
1.replace into
Replace into table (Id,name) VALUES (' 1′, ' AA '), (' 2′, ' BB ')
The purpose of this statement is to insert two records into table tables. If the primary key ID is 1 or 2 does not exist
is equivalent to
Insert into table (Id,name) VALUES (' 1′, ' AA '), (' 2′, ' BB ')
Data is not inserted if the same value exists
2.replace (Object,search,replace)
Replace all occurrences of search in object with replace
Select replace (' www.163.com ', ' w ', ' Ww ')->wwwwww.163.com
Example: Replacing AA in the Name field in table tables with BB
Update table Set Name=replace (name, ' AA ', ' BB ')
——————————————————————————–

Extending regular Expressions
Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you test for this type of pattern, use the regexp and not regexp operators (or rlike and not rlike, which are synonyms).
Some of the characters that extend the regular expression are:
· ‘.‘ matches any single character.
· Character class "[...]" Matches any character within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name a range of characters, use a "-". "[A-z]" matches any letter, while "[0-9]" matches any number.
· "*" matches 0 or more characters in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of characters.
If the regexp pattern matches any of the tested values, the pattern matches (this is different from the like pattern match, and the pattern matches only if it matches the entire value).
To locate a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern.
To illustrate how an extended regular expression works, use RegExp to override the like query shown above:
To find the name starting with "B", use "^" to match the beginning of the name:
Mysql> SELECT * from pet WHERE name REGEXP ' ^b ';

[mysql regular expression ]

[Delete all tables in MySQL database that do not start with JP]

from:http://blog.csdn.net/pipisorry/article/details/46764495

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL syntax, special symbols, and the use of regular expressions