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;
The case of default, the port is 3306, the name of the socket MySQL
mysqladmin shutdown;
mysqladmin reload;
mysqladmin-help;
mysqladmin-u root-p shutdown;
If you omit the-p option, mysql think you do not need the password does not prompt for
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

To change the root user password:
mysql> UPDATE user SET password = PASSWORD (“new password”) WHERE user = ‘hunte’;
mysql> FLUSH PRIVILEGES;
mysql> QUIT

SELECT statement syntax is as follows:

SELECT selection_list to     Select which columns

FROM table_list      From where to select the row

WHERE primary_constraint    Line must meet the conditions

GROUP BY grouping_columns are       How to group results

HAVING secondary_constraint      Rows must satisfy the second condition

ORDER BY sorting_columns How to sort the results

LIMIT count   Restricts results

Note: All keywords must be precisely at the order given. For example, a HAVING clause must follow the GROUP BY clause and before the ORDER BY clause.

COUNT () function counts the number of non-NULLresults

TIMESTAMP column type provides a type timestamp value can be from sometime in 1970 until the beginning of 2037, the accuracy of one second, its value as a digital display. 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 updated automatically.

Automatically update the first TIMESTAMP column in any of the following conditions occur:

l          Column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.

l          The column is not explicitly specified in an UPDATE statement and some other column changes value. (Note that an UPDATE to set a as it has some value, which will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL for efficiency and ignore the changes.)

l          You explicitly set the TIMESTAMP column is NULL.

Except the first TIMESTAMP column can be set to the current date and time, as long as the column is set to NULL, 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 a record to the table, you can see the effect:

mysql> INSERT student (id, name, englisht, Chinese, history) VALUES (11, “Tom”, 66,93,67);

View record storage:

mysql> SELECT * FROM student;

+ —— + ——— + ——— + ——— + ——— + – ————– + | Id    | Name     | English | chinese | History | time            | + —— + ——— + ——— + ——— + ——— + – ————– + |    11 | Tom      |       66 |       93 |       67 | 20010220123335 | + —— + ——— + ——— + ——— + ——— + – ————– +

You can see the time column record the data entry time. If you update the changed records, view the result of the operation:

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

mysql> SELECT * FROM student;

+ —— + —— + ——— + ——— + ——— + —– ———– + | Id    | Name | english | english | History | time            | + —— + —— + ——— + ——— + ——— + —– ———– + |    11 | Tom   |       76 |       93 |       67 | 20010220125736 | + —— + —— + ——— + ——— + ——— + —– ———– +

You can clearly see that the time is time column is automatically changed to modify the record at the time.

Sometimes you do not want to change any values, modify the value of TIMESTAMP column can hit, this time as long as the set of the column value is NULL, MySQL on can automatically update TIMESTAMP column values:

mysql> UPDATE student SET time = NULL WHERE id = 11;

mysql> select * from student where id = 11;

+ —— + —— + ——— + ——— + ——— + —– ———– + | Id    | Name | english | english | History | time            | + —— + —— + ——— + ——— + ——— + —– ———– + |    11 | Tom   |       76 |       93 |       67 | 20010220130517 | + —— + —— + ——— + ——— + ——— + —– ———– +

By explicitly set the desired value, you can set any TIMESTAMP column to a value different from the current date and time, even for the first TIMESTAMP column. For example, if, when you create a row, you want a TIMESTAMP is set to the current date and time, but in the future whenever the row is updated does not change, you can use:

l          MySQL row is created to set the column, which will initialize it to the current date and time.

l          When you run the other columns change subsequent to the line, explicitly set the TIMESTAMP column to its current value.

For example, when you modify the column can be paid to the original value of TIMESTAMP column:

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

mysql> select * from student where id = 11;

+ —— + —— + ——— + ——— + ——— + —– ———– + | Id    | Name | english | english | History | time            | + —— + —— + ——— + ——— + ——— + —– ———– + |    11 | Tom   |       66 |       93 |       67 | 20010220130517 | + —— + —— + ——— + ——— + ——— + —– ———– +

On the other hand, you may find that you want to achieve this effect, it is easy for a NOW () to initialize the DATETIME column and then change it no longer, it may be direct. The TIMESTAMP column after storage requirements are relatively small, space-saving benefits. TIMESTAMP storage requirements is 4 bytes, while the the DATETIME column of storage requirements is 8bytes.

In MySQL, SQL mode the default is to ignore case.

In MySQL, databases and tables correspond to the directories and files in those directories, therefore, depends on the sensitivity of the internal operating system database and table names case sensitivity. This means database and table names are case-sensitive on Unix, Win32 ignore case.

Note: on Win32, although the database and table names are case insensitive, you should not be in the same query using a different case to refer to a given database and table. The following query will not work, because it is as my_table and asMY_TABLE to refer to a table:

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

2, column names

Column names are ignored in all cases sensitive.

3, the table alias

The table alias is case-sensitive. The following query will not work: alias because it references a and A:

mysql> SELECT col_name FROM tbl_name AS a

           WHERE a.col_name = 1 OR A.col_name = 2;

4, column aliases

Column aliases are case insensitive.

5 string comparisons and pattern matching

By default, MySQL searches are case-insensitive (although there are some character set has never been ignoring case, for example, in Czech). This means that if you search with col_name LIKE ‘a%’, you will get all of column A or a value. If you want to make this search case sensitive, use something like INDEX (col_name, “A”) = 0 check a prefix. Or if the column value must be exactly “A”, using the strcmp (col_name, “A”) = 0.

Simple comparison operations (> =,> =, <, <=, sorting and aggregation) is based on each character’s “sort value. The characters have the same sort value (like E, e) is regarded as the same character!

LIKE comparison in uppercase value of each character (“E” = “e”).

If you want a column always treated as a case-sensitive manner, declare it as BINARY.

For example:

mysql> SELECT “E” = “e”, “E” = BINARY “e”;

+ ——— + —————- + | “E” = “e” | “E” = BINARY “e” | + ——— + —————- + |        1 |               0 | + ——— + —————- +

Other types of matching provided by MySQL mode is to use extended regular expressions. When you test for a match of such mode, use the REGEXP and NOT the regexp operator (or RLIKE and NOT RLIKE, they are synonyms).

Extended regular expression characters are:

“.” Matches any single character.

A character class “[…]” matches in square brackets any character. For example, “[abc]” match “a”, “b” or “c”. To name a range of characters, use a “-“. “[Az]” matches any lowercase letters, “[0-9]” matches any digit.

“*” Matches zero or more things in front of it. E.g., “x *” matches any number of characters of “x”, “[0-9] *” matches any number of numeric, while “*” matches any number of anything.

Regular expressions are case-sensitive, but if you want to, you can use a character class to match two ways for writing. For example, “[aA] matches lowercase or uppercase” a “and” [a-zA-Z] “matches two written any letter.

If it appears anywhere in the value being tested, pattern matching (as long as they match the entire value, SQL pattern matching).

In order to locate a pattern so that it must match the beginning or end of the test values ??in the mode at the beginning of use “^” or at the end of the pattern with a “$”.

To demonstrate how extended regular expressions work, the LIKE queries shown above regexp rewrite below:

In order to find out the names beginning with “b”, use the “^” matches the beginning of the name and [bB] matches lowercase or uppercase “b”:

mysql> SELECT * FROM pet WHERE name REGEXP “^ [bB]”;

+ ——– + ——– + ——— + —— + ———— + – ———– + | Name    | Owner   | Species | sex   | BIRTH       | Death       | + ——– + ——– + ——— + —— + ———— + – ———– + | Buffy   | Harold | dog      | F     | 1989-05-13 | NULL        | | Bowser | Diane   | Dog      | M     | 1989-08-31 | 1995-07-29 | + ——– + ——– + ——— + —— + ———— + – ———– +

In order to find out to the “Fy end of the name, use” $ “matches 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   | + ——– + ——– + ——— + —— + ———— + – —— +

In order to find out the name contains a “w”, the use of the [WW] “matches lowercase or uppercase” w “:

mysql> SELECT * FROM pet WHERE name REGEXP “[wW]”;

+ ———- + ——- + ——— + —— + ———— + ———— + | Name      | Owner | species | sex   | BIRTH       | Death       | + ———- + ——- + ——— + —— + ———— + ———— + | Claws     | Gwen   | Cat      | M     | 1994-03-17 | NULL         | | Bowser    | Diane | dog      | M     | 1989-08-31 | 1995-07-29 | | Whistler | Gwen   | Bird     | NULL | 1997-12-09 | NULL        | + ———- + ——- + ——— + —— + ———— + ———— +

Since a regular expression anywhere in the value, the pattern matching, you no longer need the previous query mode by placing a wildcard so it matches the entire value, like if you are using a SQL mode .

To find names containing exactly five characters of the name, use the “^” and “$” to match the beginning and end of the name, and five. “Instance between the two:

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 “{n}” repeated n times “operator 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