Mysql syntax, special symbols, and regular expressions

Mysql syntax, special symbols, and regular expressions
Http://blog.csdn.net/pipisorry/article/details/46773545

Common Mysql display commands

1. display the list of databases on the current database server:

Mysql>Show databases;

Note: the mysql database contains the MYSQL system information. We change the password and add new users to use this database for operations.

2,Enter Database:

Mysql>USE Database Name;

2,Display data tables in the database:

Mysql>Show tables;

3,Display the data table structure:

Mysql> DESCRIBE table name;

4,Create a database:

Mysql> create database name;

5. Create a data table:

Mysql> USE Database Name;
Mysql> create table Name (field name VARCHAR (20), field name CHAR (1 ));

6,Delete Database:

Mysql> drop database name;

7,Delete multiple data tables:

Mysql> drop table Name, TABLE name;

8. Clear records in the table:

Mysql> delete from table name;

9,Displays records in a table.:

Mysql> SELECT * FROM table name;

10,Insert new fields into the table:

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

10,Insert records into the table:

Mysql> insert into table name VALUES ("hyq", "M ");

Syntax: INSERT [INTO] tbl_name [(col_name,...)] VALUES (PRession ,...),...
INSERT [INTO] tbl_name SET col_name = expression ,...

Note: Add an id field when inserting data in django: insert into table name VALUES (1, 'aaa', 'bbb ');

11. Modify the field type:

Mysql> alter table name modify column field name varchar (12 );

11. Update table data:

Mysql-> UPDATE table name: SET field name: 1 = 'a'; field name: 2 = 'B' WHERE field name: 3 = 'C ';

12. load data into a 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. Change the root password on the command line:

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. All operations are performed at the MySQL prompt, and each command ends with a semicolon.

2. SQL statements are case insensitive.

[MySQL Command details]

Http://blog.csdn.net/pipisorry/article/details/46773545

MySQL index usage rules

1. The best alternative data columns for indexing are those that appear in the WHERE clause, join clause, order by, or group by clause.

2. Under what circumstances should I create no or fewer indexes?
   A. Too few table records
   B. frequently inserted, deleted, and modified tables
   C. Table fields that are frequently queried with the primary field but have a large index value

3. Create a composite index:
   For example, a statement is as follows:

   Select * from users where area = 'beijing' and age = 22;
   If we create a single index on the area and age respectively, mysql queries can only use one index at a time. Therefore, although full table scan improves the efficiency compared with no index, however, creating a composite index on the "area" and "age" columns will increase the efficiency.

   If we create a composite index (area, age, salary), it is equivalent to creating (area, age, salary), (area, age), (area) three indexes. This is called the best left prefix.

4. Create a composite index and the leftmost prefix principle:
   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 have created a composite index on the table's state, city, and zip data columns. Data rows in the index are arranged in the state/city/zip order, so they are automatically arranged in the state/city/zip order. This means that MySQL can use this index even if you specify only the state value or the state and city value in the query. Therefore, this index can be used to search for the following data column combinations: (state, city, zip) (state, city) (state)
   

5. The index does not contain columns with NULL values.
   As long as a column contains a NULL value, it will not be included in the index. If a column in the composite index contains a NULL value, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL during database design.

6. Mysql queries only use one index.

   Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.
   

7. Generally, like operations are not encouraged.

   If it is not usable, how to use it is also a problem. Like "% a %" does not use indexes, but like "aaa %" can use indexes.

8. Do not perform operations on columns. select * from users where YEAR (adddate)

9. Do NOT use the not in operation:
   The not in operation does NOT use indexes to scan the entire table. Not in can be replaced by not exists.
   

[Mysql index proverbs set]

Http://blog.csdn.net/pipisorry/article/details/46773545

Special mysql symbols

% (Percent):
For example, a % B indicates a string of any length starting with a and ending with B. Such as acb, addgb, and AB all match strings. _ (Lower horizontal line ):
Represents any single character. For example, a_ B indicates any string starting with a and ending with B with a length of 3. Such as acb and afb all match strings

String quotation marks:

In SQL, single quotes (') are strongly recommended for string quotes ('). Although MySQL (the best combination with PHP) can also use double quotation marks ("), but to and SQL server (WINDOWS powerful database platform) and Oracle (large website database platform) uniform. We recommend that you use single quotes. If single quotes also appear in the string, you must replace them with two single quotes ('') in SQL. DBMS will interpret it as a single quotation mark.

SQL line breaks and string connectors:

MySQL (the best combination with PHP), SQL server (a powerful database platform on WINDOWS), and Oracle (a large website database platform) are different. The list is shown below. MySQL (best combination with PHP) SQL server (powerful database platform on WINDOWS) Oracle (large website database platform) line feed \ n or \ r \ n or CHAR (10) CHAR (13) CHR (10) string connector CONCAT () + | or CONCAT ()

Macro variable identifier (&):

It has a special meaning in Oracle (a large-scale website database platform) and 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. If there is (&) in SQL, it is recommended to enclose it in single quotes, such as SELECT 'aaa' | '&' | 'nbsp; BBB 'as str from dual it won't prompt.

Mysql Regular Expression

Replace and regexp usage
0 Comments | This entry was posted on Apr 08 2010.
Mysql replace usage
1. replace
Replace into table (id, name) values ('1', 'A'), ('2', 'bb ')
This statement inserts two records into the table. If the primary key id is 1 or 2 does not exist
It is equivalent
Insert into table (id, name) values ('1', 'A'), ('2', 'bb ')
Data is not inserted if the same value exists.
2. replace (object, search, replace)
Replace all search objects with replace
Select replace ('www .163.com ', 'w', 'ww')-> WwWwWw.163.com
For example, replace aa in the name field of the table with bb.
Update table set name = replace (name, 'AA', 'bb ')
---------------------------

Extended Regular Expression
Other types of pattern matching provided by MySQL use extended regular expressions. When you perform a match test on this type of pattern, use the REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms ).
Some Characters of the extended regular expression are:
· '.' Matches any single character.
· The character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name the character range, use a hyphen (-). "[A-z]" matches any letter, and "[0-9]" matches any number.
· "*" Matches zero or multiple characters before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number, and ". * "matches any number of characters.
If the REGEXP pattern matches any part of the tested value, the pattern matches (different from the LIKE pattern match. The pattern matches only the entire value ).
To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ".
To demonstrate how the extended regular expression works, use REGEXP to rewrite the LIKE Query shown above:
To find the name starting with "B", use "^" to match the start of the name:
Mysql> SELECT * FROM pet WHERE name REGEXP '^ B ';

[Mysql regular expression]

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

From: http://blog.csdn.net/pipisorry/article/details/46773545

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.