Mysql command

Mysql command
Use <dbname> [LIKE wild];
Show tables;
Show databases;
? /Help;
Exit/quit;
Mysqladmin version;
Mysql mysqladmin version;
Net start mysql;
Mysqladmin variables;
Mysqladmin-h hostname -- port = port_number variables;
By default, the port is 3306 and the socket name is MySQL.
Mysqladmin shutdown;
Mysqladmin reload;
Mysqladmin-help;
Mysqladmin-u root-p shutdown;
If the-p option is omitted, mysql considers that you do not need a password and do not prompt
Shell> mysql-h mysql.domain.net-u tom-p;
Create database db_name;
Drop database [if exists] db_name;
SHOW/DESCRIBE;

Source E: \ Tomcat \ webapps \ 2richshop1. 1 \ doc \ SQL .txt;

GRANT privileges (columns) ON what TO user identified by "password" WITH GRANT OPTION

Modify the root user password:
Mysql> UPDATE user SET password = PASSWORD ("new password") WHERE user = 'hunte ';
Mysql> flush privileges;
Mysql> QUIT

 

The SELECT statement syntax is as follows:

SELECT selection_list

FROM table_list where to select a row

WHERE primary_constraint

How does group by grouping_columns GROUP results?

HAVING secondary_constraint must meet the second condition

Order by sorting_columns how to sort results

LIMIT on LIMIT count results

Note: All keywords used must be given exactly in the above order. For example, a HAVING clause must be followed BY the group by clause and before the order by clause.

COUNT () function COUNT the number of non-NULL results

 

The TIMESTAMP column type is provided. The TIMESTAMP value can start from a certain time of 1970 until January 1, 2037. The precision is one second, and its value is displayed as a number. You can use it to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is automatically updated.

The first TIMESTAMP column is automatically updated under any of the following conditions:

The l column is not explicitly specified in an INSERT or load data infile statement.

L columns are not explicitly specified in an UPDATE statement and some other columns change values. (Note that setting an UPDATE column as its existing value does not cause the TIMESTAMP column to be updated, because if you set a column as its current value, MySQL ignores the changes for efficiency .)

L You explicitly set the TIMESTAMP column as NULL.

The TIMESTAMP column except the first one can also be set to the current date and time, as long as the column is set to NULL, or NOW ().

For example, create the following table:

Mysql> create table student

-> (

-> Id int,

-> Name char (16 ),

-> English tinyint,

-> Chinese tinyint,

-> History tinyint,

-> Time timestamp

-> );

Insert records into the table to view the results:

Mysql> INSERT student (id, name, englisht, Chinese, history) VALUES (11, "Tom", 67 );

View the storage status of the record:

Mysql> SELECT * FROM student;

 

+ ------ + --------- + ---------------- +
| Id | name | english | chinese | history | time |
+ ------ + --------- + ---------------- +
| 11 | Tom | 66 | 93 | 67 | 20010220123335 |
+ ------ + --------- + ---------------- +
 

You can see that the time column records the time value of data input. If you update and modify the record, view the operation result:

Mysql> UPDATE student SET english = 76 WHERE id = 11;

Mysql> SELECT * FROM student;

+ ------ + --------- + ---------------- +
| Id | name | english | chinese | history | time |
+ ------ + --------- + ---------------- +
| 11 | Tom | 76 | 93 | 67 | 20010220125736 |
+ ------ + --------- + ---------------- +
We can clearly see that the time of the time column is automatically changed to the time when the record is modified.

Sometimes you want to modify the value of the TIMESTAMP column without changing any value. MySQL can automatically update the value of the TIMESTAMP column as long as the value of this column is set to NULL:

Mysql> UPDATE student SET time = null where id = 11;

Mysql> select * from student where id = 11;

+ ------ + --------- + ---------------- +
| Id | name | english | chinese | history | time |
+ ------ + --------- + ---------------- +
| 11 | Tom | 76 | 93 | 67 | 20010220130517 |
+ ------ + --------- + ---------------- +
By explicitly setting the expected value, you can set any TIMESTAMP column as a value different from the current date and time, even for the first TIMESTAMP column. For example, if you want a TIMESTAMP to be set to the current date and time when you create a row, but it will not change at any time after the row is updated, you can use this method:

L let MySQL set the column when the row is created, which will initialize it as the current date and time.

L when you perform subsequent changes to other columns in the row, set the current value of the TIMESTAMP column.

For example, when you modify a column, you can pay the original value to the TIMESTAMP column:

Mysql> UPDATE student SET english = 66, time = time WHERE id = 11;

Mysql> select * from student where id = 11;

+ ------ + --------- + ---------------- +
| Id | name | english | chinese | history | time |
+ ------ + --------- + ---------------- +
| 11 | Tom | 66 | 93 | 67 | 20010220130517 |
+ ------ + --------- + ---------------- +
On the other hand, you may find that when you want to implement the above effect, it is easy to use a DATETIME column that you initialize with NOW () and then no longer change it. This may be more direct. However, the advantage of the TIMESTAMP column is that the storage requirements are relatively small, saving space. The storage requirement of TIMESTAMP is 4 bytes, while that of DATETIME columns is 8 bytes.

 

In MySQL, the SQL Mode is case-insensitive by default.

In MySQL, databases and tables correspond to directories and files under those directories. Therefore, the sensitivity of the internal operating system determines the case sensitivity of databases and tables. This means that the database and table names are case-sensitive on Unix, while the case sensitivity is ignored on Win32.

Note: On Win32, although the database and table names are case-insensitive, you should not reference a given database or table in the same query using different cases. The following query will not work because it references a table as my_table and MY_TABLE:

Mysql> SELECT * FROM my_table WHERE MY_TABLE.col = 1;

2. Column name
The column names are case-insensitive in all cases.

3. Table alias
Table aliases are case sensitive. The following query will not work because it uses a and A to reference aliases:

Mysql> SELECT col_name FROM tbl_name AS

WHERE a. col_name = 1 or a. col_name = 2;

4. Column alias
The column alias is case-insensitive.

5. String comparison and pattern matching
By default, MySQL search is case-insensitive (although some character sets are never case-insensitive, such as Czech ). This means that if you search by col_name LIKE 'a % ', you will get all column values starting with a or. If you want to make this search case sensitive, use INDEX (col_name, "A") = 0 to check A prefix. Or if the column value must be "A", use STRCMP (col_name, "A") = 0.

Simple comparison operations (> =,>, =, <, <=, sort, and aggregation) are based on the "sort value" of each character ". Characters with the same sorting value (such as E and e) are considered to be the same character!

LIKE is compared to the upper-case values of each character ("E" = "e ").

If you want a column to always be case sensitive, declare it as BINARY.

For example:

Mysql> SELECT "E" = "e", "E" = BINARY "e ";

+ --------- + ---------------- +
| "E" = "e" | "E" = BINARY "e" |
+ --------- + ---------------- +
| 1 | 0 |
+ --------- + ---------------- +

 

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.

A character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name a range of characters, use a hyphen (-). "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.

"*" Matches zero or multiple items before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". * "matches any number of things.

Regular expressions are case-sensitive, but if you want to, you can use one character class matching method. For example, "[aA]" matches lowercase or upper-case "a", and "[a-zA-Z]" matches any letter in either of the two statements.

If it appears anywhere in the tested value, the schema matches (as long as they match the entire value, the SQL schema matches ).

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, the LIKE query shown above is rewritten using REGEXP below:

To find the name starting with "B", use "^" to match the start of the name and use "[bB]" to match "B" in lower case or upper case ":

 

Mysql> SELECT * FROM pet WHERE name REGEXP "^ [bB]";

+ -------- + --------- + ------ + ------------ +
| Name | owner | species | sex | birth | death |
+ -------- + --------- + ------ + ------------ +
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Boane | Diane | dog | m |
+ -------- + --------- + ------ + ------------ +
 

To find the name ending with "fy", use "$" to match the end of the name:

 

Mysql> SELECT * FROM pet WHERE name REGEXP "fy $ ";

+ -------- + --------- + ------ + ------------ + ------- +
| Name | owner | species | sex | birth | death |
+ -------- + --------- + ------ + ------------ + ------- +
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+ -------- + --------- + ------ + ------------ + ------- +
 

To locate the name containing a "w", use "[wW]" to match the "w" in lower case or upper case ":

 

Mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";

+ ---------- + ------- + --------- + ------ + ------------ +
| Name | owner | species | sex | birth | death |
+ ---------- + ------- + --------- + ------ + ------------ +
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Boane | Diane | dog | m |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+ ---------- + ------- + --------- + ------ + ------------ +
 

Since a regular expression appears anywhere in the value and its pattern matches, there is no need to place a wildcard in the two aspects of the pattern in the previous query so that it matches the entire value, just like if you use an SQL mode.

To locate a name that contains exactly five characters, use "^" and "$" to match the start and end of the name, and the five "." instances are in the range:

Mysql> SELECT * FROM pet WHERE name REGEXP "^... $ ";

+ ------- + -------- + --------- + ------ + ------------ + ------- +
| Name | owner | species | sex | birth | death |
+ ------- + -------- + --------- + ------ + ------------ + ------- +
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+ ------- + -------- + --------- + ------ + ------------ + ------- +
 

You can also use the "{n}" "repeat n times" operator to rewrite the previous query:

 

Mysql> SELECT * FROM pet WHERE name REGEXP "^. {5} $ ";

+ ------- + -------- + --------- + ------ + ------------ + ------- +
| Name | owner | species | sex | birth | death |
+ ------- + -------- + --------- + ------ + ------------ + ------- +
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+ ------- + -------- + --------- + ------ + ------------ + ------- +

 

 

Syntax: select from table_reference left join table_reference ON conditional_expr