use
Source E: /tomcat/webapps/2richshop1.1/doc/sql.txt;
Grant Privileges (Column) On What To User Identified by "Password" with grant option
Modify root user password: mysql> update user set password = password ("new password") where user = 'hunte'; mysql> flush privileges; mysql> quit
The syntax of the SELECT statement is as follows:
SELECT SELECTION_LIST which columns selected
Where does from Table_List choose a line?
Where primary_constraint must meet what conditions
GROUP BY Grouping_COLUMNS How to Packet
The second condition that the Having Secondary_Constraint must satisfy
ORDER BY SORTING_COLUMNS How to Sortature
Limit count result
Note: All keywords use must be given in the order of the above. For example, a HAVING clause must follow before the group By clause and the ORDER BY clause.
COUNT () function count Number of non-null results
The TimeStAMP column type provides a type, and the timestamp value can be from 1970 to 2037, the accuracy is one second, and its value is a digital display. You can use it to automatically use the current date and time to mark the operation of INSERT or UPDATE. If you have multiple TimeStAMP columns, only the first automatic update.
Automatic Update First TimeStAMP column happens under any of the following:
The L column is not specified in an Insert or Load Data INFILE statement.
The L column is not explicitly specified in a UPDATE statement and some additional column changes value. (Note that a UPDATE sets a value listed as it already, this will not cause the TimeStAMP column being updated because if you set a current value, MySQL ignores changes for efficiency.)
l You explicitly set TimeStamp as NULL.
In addition to the first TimeStAMP column can also be set to the current date and time, as long as NULL, or now ().
For example, create the table below:
Mysql> Create Table Student
-> (-> ID INT,
-> Name Char (16),
-> ENGLISH TINYINT,
-> Chinese tinyint,
-> History Tinyint,
-> Time TimeStamp
->);
Insert records into the table, you can view the effect:
Mysql> Insert Student (ID, Name, Englisht, Chinese, History) VALUES (11, "Tom", 66, 93, 67);
View recorded storage:
MySQL> Select * from student;
---- -------- -------- --------- --------- - --------------
| ID | NAME | ENGLISH | Chinese | History | TIME |
---- -------- -------- --------- --------- - --------------
| 11 | Tom | 66 | 93 | 67 | 20010220123335 |
---- -------- -------- --------- --------- - --------------
You can see the time value of the data entry when the TIME column records. If you update the record, check the results of the view:
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 |
---- ------ ------- ------- ------- ----- -----------
It can be clearly seen that the time of the Time column is automatically changed to modify the time.
Sometimes you want to change any value, you can also hit the value of the TimeStAMP column. At this time, as long as the value of the column is null, MySQL can automatically update the value of the TimeStAMP column:
MySQL> Update Student Set Time = NULL Where ID = 11;
MySQL> SELECT * from stay at d = 11;
---- ------ ------- ------- ------- ----- -----------
| ID | NAME | ENGLISH | Chinese | History | TIME |
---- ------ ------- ------- ------- ----- ----------- | 11 | Tom | 76 | 93 | 67 | 20010220130517 |
---- ------ ------- ------- ------- ----- -----------
By explicitly set the value of the desired value, you can set any TimeStamp columns as a value different from the current date and time, even if the first TimeStAMP column is also the case. For example, if you create a row, you want a TimeSTAMP to be set to the current date and time, but you can use this when the row is updated, you can use this:
l Let MySQL set the column when the row is created, which will initialize it for the current date and time.
l When you perform the subsequent changes to the other columns in the row, it is clearly set to the current value of TimeStamp as its current value.
For example, when you modify the 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 stay at d = 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 use now () and then no longer change it, so maybe it is directly. However, the benefits of the TimeStAMP column is that the storage requirements are relatively small, saving space. TimeStamp's storage requirements are 4 bytes, while the storage requirements of the DateTime column are 8 bytes.
In MySQL, SQL moderately default is ignored.
In MySQL, databases and tables correspond to directory and files in those directories, thus the sensitivity of the inherent operating system determines the case sensitivity of the database and table named. This means that the database and the table name are case sensitive on UNIX, and case in Win32 is ignored.
Note: On Win32, although the database and table names are ignored, you should not use different cases in the same query to reference a given database and table. The following query will not work because it uses a table as my_table and as my_table:
MySQL> SELECT * from my_table where my_table.col = 1;
2, column name
The column name is ignored in all cases.
3, the alias of the table
The alias of the table is case sensitive. The following query will not work, because it uses A and A reference alias: mysql> select col_name from TBL_NAME AS A
WHERE A.COL_NAME = 1 or a.col_name = 2;
4, column alias
The alias of the column is ignored.
5, string comparison and pattern matching
By default, MySQL search is case sensitive (although some character sets never ignore cases, such as Czech). This means that if you search with col_name like 'a%', you will get all column values starting with A or A. If you want to write this search case, use INDEX (col_name, "a") = 0 to check a prefix. Or if the column value must be exactly "A", use strcmp (col_name, "a") = 0.
Simple comparison operations (> =,>, =, <, <=, sorting, and polymerization) are based on the "sort value" of each character. A character (like E, E) with the same order value is considered the same character!
The LIKE is compared to the uppercase value of each character ("e" = "e").
If you want a column always being used as a case sensitive way, declare it for binary.
E.g:
MySQL> SELECT "E" = "e", "e" = binary "e";
------- --------------
| "E" = "e" | "e" = binary "e" |
------- --------------
| 1 | 0 |
------- --------------
Other types that match the mode provided by MySQL use expansion regular expressions. When you match this type of mode, use the regexp and not regexp operators (or rlike and not rlike, they are synonymous).
Some characters of extended regular expressions are:
"." Match any single character.
A character class "[...]" matches any character in square brackets. For example, "[ABC]" matches "A", "B" or "C". In order to name a range of characters, use a "-". "[A-Z]" matches any lowercase letters, and "[0-9]" matches any number.
"*" Matches zero or more things in front. For example, "x *" matches any number of "X" characters, "[0-9] *" matching any number of numbers, and ". *" Matches any number of anything.
Regular expressions are case sensitive, but if you want, you can use a character class to match two ways. For example, "[AA]" matches "A" "A" "[[A-ZA-Z]" matches any letters of two ways of writing.
If it appears anywhere in the test value, the mode matches (as long as they match the entire value, SQL mode matches).
In order to locate a mode so that it must match the start or end of the test value, use "^" or "$" at the beginning of the mode at the beginning.
To illustrate how the extended regular expression works, the LIKE query shown above is overridden below:
In order to find the name starting with "B", use the "^" matching name and "[bb]" matches "B" of lowercase or uppercase:
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 the name of "FY", use the "$" matching the end:
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 a "W" name, "[WW]" matches "W" of lowercase or uppercase:
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 appears anywhere in the value, its pattern matches, there is no need to place a wildcard in the two aspects of the previous query so that it matches the entire value, just like if you use a SQL mode. . In order to find the name of the name of the five characters, use "^" and "$" match the beginning and end, and 5 "." The instance is 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 the "{n}" "Repeat N times" operator to override 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
