Detailed explanation of MySQL syntax, special symbols and regular expressions

Mysql commonly used display commands

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

mysql> SHOW DATABASES;

Note: The mysql database contains MYSQL system information. When we change passwords and add new users, we actually use this database to perform operations.

2. Enter the database:

mysql> USE 庫名；

2. Display the data table in the database:

mysql> SHOW TABLES;

3. Display the structure of the data table:

mysql> DESCRIBE 表名;

4. Establish database:

mysql> CREATE DATABASE 庫名;

5. Create a data table:

mysql> USE library name;
mysql> CREATE TABLE table name (field name VARCHAR (20), field name CHAR (1));

6. Delete the database:

mysql> DROP DATABASE 庫名;

7. Delete multiple data tables:

mysql> DROP TABLE 表名,表名；

8. Clear the records in the table:

mysql> DELETE FROM 表名;

9. Display the records in the table:

mysql> SELECT * FROM 表名;

10. Insert new fields into the table:

mysql> alter tabel 表名add column 字段名varchar(10);

10. Insert records into the table:

mysql> INSERT INTO 表名VALUES (”hyq”,”M”);

grammar:

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

Note: When inserting in Django, you need to add an extra id field: INSERT INTO table name VALUES (1, 'aaa', 'bbb');

11. Modify the field type:

mysql> alter table 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 using text:

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE table name;

13. Import .sql file command:

mysql> USE database name;
mysql> SOURCE d:/mysql.sql;

14. Modify the root password via 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 under the MySQL prompt, and each command ends with a semicolon.

2. SQL statements are not case sensitive

MySQL index usage rules

The best candidates for indexing are columns that appear in a WHERE clause, a join clause, an ORDER BY clause, or a GROUP BY clause.

Under what circumstances should we not create or create fewer indexes?

a. Too few records in the table

b. Tables that are frequently inserted, deleted, or modified

c. Table fields that are often queried together with the main field but have more main field index values

Creating a composite index:

For example, there is a sentence like this:

select * from users where area='beijing' and age=22;

If we create a single index on area and age respectively, since MySQL query can only use one index at a time, although this has improved the efficiency a lot compared to the full table scan without indexing, creating a composite index on the area and age columns will bring higher efficiency.

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

The establishment of composite index and the leftmost prefix principle:

If you need to index a column of string data, it is best to specify a prefix length whenever appropriate. You can index prefixes of CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT columns.

Suppose you create a composite index on the state, city, and zip columns of a table. The rows in the index are in state/city/zip order, so they are automatically sorted in state/city/zip order. This means that MySQL can use this index even if you specify only the state value, or both state and city values, in your query. Therefore, this index can be used to search for the following column combinations: (state, city, zip)(state, city)(state)

The index will not include columns with NULL values.

As long as the column contains NULL values, it will not be included in the index. As long as one column in a composite index contains NULL values, this column will be invalid for this composite index. Therefore, we should not set the default value of the field to NULL when designing the database.

The mysql query only uses one index.

Therefore, if the index is already used in the where clause, the columns in the order by will not use the index. Therefore, do not use sorting operations if the default sorting of the database can meet the requirements; try not to include sorting of multiple columns. If necessary, it is best to create a composite index for these columns.

In general, the use of like operations is not encouraged.

If it must be used, how to use it is also a problem. like "%a%" will not use the index but like "aaa%" can use the index.

Do not perform operations on the column, select * from users where YEAR(adddate)

Without using the NOT IN operation:

NOT IN operations will not use indexes and will perform a full table scan. NOT IN can be replaced by NOT EXISTS.

mysql special symbols

% (percent sign):

Represents a string of any length (the length can be 0). For example, a%b represents a string of any length that starts with a and ends with b. For example, acb, addgb, ab, etc. all satisfy the matching string _ (underline):
Represents any single character. For example, a_b represents any string of length 3 that starts with a and ends with b. For example, acb, afb, etc. all meet the matching string

String quotes:

In SQL, single quotes (') are strongly recommended for string quotes. Although MySQL (the best combination with PHP) can also use double quotes ("), in order to be consistent with SQL Server (a powerful database platform on the WINDOWS platform) and Oracle (a large website database platform), it is recommended to use single quotes. If single quotes also appear in the string, they need to be replaced with two single quotes ('') in SQL, and the DBMS will interpret it as a single quote.

SQL line break and string concatenation characters:

There are differences in MySQL (the best combination with PHP), SQL Server (a powerful database platform on the WINDOWS platform) and Oracle (a large website database platform), which are shown in the table below. MySQL (the best combination with PHP) sql server (a powerful database platform on WINDOWS) Oracle (a large website database platform) Line break \n or \r\n or CHAR (10) CHAR (13) CHR (10) String concatenation CONCAT () + || or CONCAT ()

Macro variable identifier (&):

It has a special meaning in Oracle (a large website database platform). It is a macro variable identifier. When you execute SELECT 'AAA BBB' AS STR FROM DUAL in SQLPLUS, it will prompt you to enter the macro variable value. Therefore, if there is (&) in the SQL, it is recommended to enclose it in single quotes, such as SELECT 'AAA'||'&'||'BBB' AS STR FROM DUAL, it will not prompt.

mysql regular expression

Usage of replace and regexp

0 Comments | This entry was 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 the table. 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')

If the same value exists, the data will not be inserted.

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: Replace aa in the name field in the table table with bb

update table set name=replace(name,'aa','bb')

————————————————————————————–

Extended Regular Expressions

Another type of pattern matching provided by MySQL is using extended regular expressions. When you test for a match on such patterns, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some characters for extended regular expressions are:

· '.' matches any single character.

· The 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 "-". "[az]" matches any letter, and "[0-9]" matches any digit.

· “ * ” matches zero or more of the character that precedes it. For example, "x*" matches any number of "x" characters, "[0-9]*" matches any number of digits, and ".*" matches any number of any characters.

If the REGEXP pattern matches anywhere in the value being tested, the pattern matches (this differs from LIKE pattern matching, which matches only if it matches the entire value).

To position 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 extended regular expressions work, here's the LIKE query shown above rewritten using REGEXP:

To find names that begin 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 the mysql database that do not start with JP]

Summarize

The above is what I introduced to you about MySQL syntax, special symbols and the use of regular expressions. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!