Some simple MySQL commands

MySQL operation command:
Run cmd to enter the DOS environment
Query the basic information about MySQL -- help and the selection project table provided by MySQL
Connection: mysql-H ***-u ***-P and enter the password to launch the quit command.
Query version and time: Select version (), current_time, current_date;
MySQL indicates the clock time instead of the CPU or machine time. These values are not accurate.
MySQL is case-insensitive and can be used as a simple calculator.
For example, select sin (PI ()/4), (4 + 1) * 5;
Multiple statements can be entered in one row, separated by semicolons (;).
Select version (); select now ();
For a long command, you can enter it into multiple lines. MySQL determines where the statement ends by looking for a termination semicolon instead of the end of the input line, for example:
Select
User (),
Current_time;
Mysql> will change to->, indicating that the remaining part is waiting. If an error is found, enter "\ c" to end. It will switch back to MySQL> for example:
Select
Er (),
Now () \ c
Prompt description
Mysql> prepare to receive new commands
-> Wait for the next line of the multi-line command
'> Wait for the next line and wait for the end of the string starting with single quotes
"> Wait for the next row and wait for the end of the identifier starting with double quotation marks
/*> Wait for the next line and wait for the end of comments starting /*
For example:
Mysql> select * from table where name = 'Tom;
'>
At this time, because the ending character of single quotes is missing behind Tom, the simplest way is to end it, but not just enter \ c, because MySQL is interpreted as a part of the string being collected, it is reflected that the input closes the quotation mark character, and then in the input \ c, that is, '\ c, this is also true for double quotation marks.

Use Database
Show databases; view the current database on the server
MySQL and test are mandatory. MySQL describes user access permissions. The test database is often used as the workspace for user testing.
Use Database Name; you can switch to the corresponding database. The use command is similar to the quit command. You do not need to end with a semicolon, but you must keep the command on a single row.
Show tables; you can view tables in the current database
Administrator authorization: grant all on dbname. * To 'username' @ 'hostname ';
Create Database dbname; the database name in UNIX is case sensitive.
Then use dbname to switch to this database
You can also connect to the database when you connect to it,
For example, MySQL-H hostname-u username-P dbname and then enter the password
Create a pet table:
Create Table PET (name varchar (20), owner varchar (20 ),
Species varchar (20), sex char (1), birth date, death date );
Describe tablename; you can get the basic information of a data table. If you forget the table information, you can use this statement to get the table information.
After creating a table, you can use the load data and insert statements to fill the table.
You can create an example file named pet.txt. Each line contains a record. The values are separated by a tab and given in the order of the columns listed in the create table statement. You can use NULL for missing values (such as unknown gender, or dead dates of living animals. To represent the content in your text file, use \ n (backslash, letter N ),
Load data local infile ''c:/Documents and Settings/Administrator/desktop/pet.txt 'into table PET;
Note that if you use the editor in Windows (use \ r \ n as the line terminator) to create a file, use:
Mysql> load data local infile '/path/pet.txt' into Table pet
-> Lines terminated by '\ r \ n ';
(The line terminator '\ R' should be used on an apple machine running OS X '.)
If you want to, you can explicitly specify the delimiter and the end mark of the row for the column value in the load data statement, but the default mark is the location and line break. This is enough to read the program pet.txt statement.
You can also add a new record using the insert statement:
Insert into pet values ('none', 'dinae ', 'cat', 'F', '2017-05-28', null );
Select data
Select * from PET;
Data correction:
Method 1: delete from pettings first, then modify the pet.txt file, and re-import the file through load data.
Method 2: Update pet set birth = '2017-08-31 'Where name = 'shanghai ';
Update pet set sex = 'M' where name in ('jia ', 'hai', 'fan ');
Update pet set sex = 'M' where name = 'zhang 'or name = 'yong ';
Search for Qualified Data
Select * From Pet where birth> '2017-10-25 ';
And or can be mixed, but and has a higher priority than or. If you use two operators, it is a good idea to use parentheses to indicate how to group conditions:
Mysql> select * From Pet where (species = 'Dog' and sex = 'M') or (species = 'bird' and sex = 'F ');
Select a special column:
Select owner from PET; some of the results may appear multiple times
Select distinct owner from PET; the output is minimized. The distinct keyword is added to retrieve each unique output record.
Select name, birth from pet order by birth; sort by date of birth, ascending by default
Select name, birth from pet order by birth DESC; in descending order
You can sort multiple columns in different directions. For example, sort the types of animals in ascending order, and sort the types of animals in descending order based on their birthdays (the youngest animals are at the beginning ),
For example: Select name, species, birth from pet order by species, birth DESC;
==========================================
Date calculation:
Select name, birth, curdate (), (Year (curdate ()-year (birth ));
To determine the size of each pet, you can calculate the difference between the year of the current date and the date of birth. If the calendar year of the current date is earlier than the date of birth, a year is subtracted. The following query displays the number of years of birth date, current date, and age for each pet.
Mysql> select name, birth, curdate (),
-> (Year (curdate ()-year (birth ))
->-(Right (curdate (), 5) <right (birth, 5 ))
-> As age
-> From pet order by name;
+ ---------- + ------------ + ------ +
| Name | birth | curdate () | age |
+ ---------- + ------------ + ------ +
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bo8-| 1989-08-31 | 2003-08-19 | 13 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Puffball | 4 |
+ ---------- + ------------ + ------ +

Here, year () extracts the year portion of the date, similar functions include month (), dayofmonth (), right () extract the MM-DD of the date (calendar year) the rightmost five characters of the part. The value of the expression part that compares the MM-DD value is generally 1 or 0, if the year of curdate () is earlier than the year of birth, the year should be subtracted 1. The entire expression is somewhat difficult to understand. Using alias (AGE) makes the output column mark more meaningful.
Query the age of dead animals:
Mysql> select name, birth, death, (Year (death)-year (birth)-(right (birth, 5)> right (death, 5 )) as age from pet where death> '2017-00-00 'order by age;
Note that in MySQL, 0 or null means false while other values mean true. The default true value of Boolean operations is 1.
The special processing of null is in the previous chapter. To determine which animal is no longer alive, use death is not null instead of death! = NULL.
Search for animals whose birthdays are next month (provided that they are not killed ):
Select name, birth, month (birth) from PET;
It is also easy to find the animals for the next month's birthday. Assume that the current month is January 1, April, and the monthly value is 4. You can find the animal born on January 1, May (January 1, May):
If the current month is December, it will be a bit complicated. You can't just add 1 to the number of months (12) and search for animals born in 13 months because there is no such month. Instead, you should look for animals born in January (January ).
You can even write a query, regardless of the current month. In this way, you do not need to use a specific month in the query. date_add () allows you to add time intervals on a given date. If you add the last month to the now () value and extract the month with month (), the result shows the month of the birthday:
Mysql> select name, birth from pet
-> Where month (birth) = month (date_add (curdate (), interval 1 month ));
Another way to complete this task is to add 1 to get the next month of the current month (after using the MOD function, if the current month value is 12, then "rollback" to 0 ):
Mysql> select name, birth from pet
-> Where month (birth) = Mod (month (curdate (), 12) + 1;
------------------------------------------------------------
Null Value operation
Null indicates no value or unknown value.
You can only use ** is null or ** is not null, rather than ** = NULL or **! = NULL. in MySQL, 0 or null indicates false while other values mean true. The default value of Boolean operation is 1.
In group by, two null values are considered to be the same.
When order by is executed, if order by * ASC is run, the null value appears at the beginning, and desc is placed at the end.
--------------------------------------
Pattern Matching
SQL mode matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters ). In MySQL, the SQL mode is case-insensitive by default. The following are some examples. Note that = or! =; Instead, use the like or not like comparison operator.

The correct method to restart MySql in Linux:

1. MySQL installed through the RPM package

Service mysqld restart

2. Install MySQL from the source code package

// Linux Command to disable MySQL

$ Mysql_dir/bin/mysqladmin-uroot-P Shutdown

// Linux Command to start MySQL

$ Mysql_dir/bin/mysqld_safe &

Mysql_dir is the installation directory of MySQL, and mysqladmin and mysqld_safe are located in the bin directory of the installation directory of MySQL, which is easy to find.

3. If none of the above methods are valid, you can use the Force Command "killall MySQL" to close mysql. However, this method is not recommended, this brutal method forcibly terminates the MySQL database service and may cause table corruption.

Step or method: RedHat Linux (Fedora Core/cent OS)

1. Start:/etc/init. d/mysqld start

2. Stop:/etc/init. d/mysqld stop

3. Restart:/etc/init. d/mysqld restart

Debian/Ubuntu Linux

1. Start:/etc/init. d/MySQL start

2. Stop:/etc/init. d/MySQL stop

3. Restart:/etc/init. d/MySQL restart

Windows

1. Click Start> Run (shortcut: Win + r ).

2. Start: Enter net stop MySQL

3. Stop: Enter Net start MySQL

Prompt * RedHat Linux also supports service command, start: # service mysqld start stop: # service mysqld stop restart: # service mysqld restart

* In Windows, you cannot directly restart (restart). You can only stop the instance and start it again.

MySQL start, stop, restart method:

I. Startup Mode

1. Start with service: Service mysqld start

2. Use the mysqld script to start:/etc/inint. d/mysqld start

3. Start with safe_mysqld: safe_mysqld &

Ii. Stop

1. Start with service: Service mysqld stop

2. Use the mysqld script to start:/etc/inint. d/mysqld stop

3. mysqladmin Shutdown

3. Restart

1. Start with service: Service mysqld restart

2. Use the mysqld script to start:/etc/inint. d/mysqld restart