MySQL Common statements

First, MySQL commonly used statements

Create, delete, and most basic queries:
Display database mysql->show databases;
Create DATABASE Mysql->create db;
Delete database Mysql->drop db;
Select Database Mysql->use db
Create Tables Mysql->create table MyTable (name varchar), sex (char (1), birth date);
Delete tables Mysql->drop table mytable;
Display the contents of the table mysql->show tables;
The structure of the display table Mysql->describe mytable;

Update:
1, the operation of the column:
Add a field to a Table Mysql->alter table yourtable add name varchar () not NULL;
Delete a field mysql->alter table yourtable drop name;
2, the operation of the line:
Insert a record mysql->insert into mytable values (' Summer ', ' m ', ' 1983-08-24 ');
Delete a record mysql->delete from mytable where name= ' summer ';
Modify a record mysql->update mytable set sex= ' VM ' where name= ' summer ';
Insert multiple records Mysql->insert into mytable select *from yourtable;
In this form of an INSERT statement, the data value of the new row is not explicitly specified in the body of the statement. Instead, a database query specified in the statement. Logical limits for this query:
The query cannot contain an ORDER BY clause. The query result should contain the same columns as the number of columns in the INSERT statement, and the data type must be column-compatible. )

Simple query:
1. Display column names in query results
A. With AS keyword: select name as ' name ' from students order by age
B. Direct representation: Select name ' name ' from students order by age
. Exact search:
A. With in bounds: SELECT * from students where native in (' Hunan ', ' Sichuan ')
B.between...and:select * from students where age between and 30
C. Comparison test: (including =,<>,<,<=,>,>=) SELECT * from students where name = ' Li Shan '
D.like:select * from students where the name like ' Li% ' (note that there is "%" in the query condition, then the description is partially matched, and there is also a message in the inside, that is, to find a match with "Li". Therefore, if the query has "Li" all objects, should command: '% li% '; If the second word is Li, it should be ' _ Li% ' or ' _ Li ' or ' _ li _ '. )
E.[] Match checker: SELECT * from courses where CNO like ' [ac]% ' (representation or relationship, with "in (...)" Similarly, and "[]" can represent a range, such as: SELECT * from courses where CNO like ' [a-c]% ') Note: about this character I used in MySQL when MySQL took it as two ordinary self-character processing.
[^]stockname like ' [^f-m]% '---------(^ exclude specified range)
A.count (), such as: SELECT COUNT (*) from students (total number of students)
B.avg (column) averaging, such as: Select AVG (Mark) from grades where cno= ' B2 '
C.max (column) and min (column) for maximum and minimum

--------------------------------------------------------------------------------------------------------------- ------------------------------
Second, some common MySQL statements
Php+mysql+linux is now becoming a classic combination of small Web servers. Building and debugging MySQL databases in a Windows environment is a top choice for many Web developers.

Here are some common MySQL statements:

First, connect MySQL.
Format: mysql-h host address-u user name-P user Password
1. Example 1: Connect to MySQL on this machine.
First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is:mysql>
2. Example 2: Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: You and root can be used without spaces, others are the same)
3. Exit MySQL command: Exit (enter)

Second, change the password.
Format: Mysqladmin-u username-P Old password password new password
1, Example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command
Mysqladmin-uroot-password AB12
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
2, Example 2: Then change the root password to djg345.
MYSQLADMIN-UROOT-PAB12 Password djg345

Third, add new users. (Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)
Format: Grant Select on database. * To User name @ login host identified by "password" Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:
Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";
But example 1 increases the user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on your MySQL database and to your data can do whatever, workaround see Example 2.
Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";


4, the first command
Mysql> Select Version (), current_date ();
+----------------+-----------------+
| Version () | Current_date () |
+----------------+-----------------+
| 3.23.25a-debug | 2001-05-17 |
+----------------+-----------------+
1 row in Set (0.01 sec)
Mysql>

This command requires the MySQL server to tell you its version number and the current date. Try the above command with a different case, and see what the result is.
The result shows that the case of the MySQL command is consistent.
Here's how to practice:
Mysql>select (20+5);
Mysql>select (20+5) *4,sin (Pi ()/3);
Mysql>select (20+5) as Result,sin (Pi ()/3); (as: Specify Kana as result)

5, multi-line statements
A command can be divided into multiple lines of input until a semicolon ";" is present:
Mysql> Select
-USER ()
,
Now ()
->;
+--------------------+---------------------+
| USER () | Now () |
+--------------------+---------------------+
| [Email protected] | 2001-05-17 2215 |
+--------------------+---------------------+
1 row in Set (0.06 sec)
Mysql>
Notice the middle of the comma and the end of the semicolon using the method.

6, one line multi-command
Enter the following command:
Mysql> Select USER (); Select now ();
+------------------+
| USER () |
+------------------+
| [Email protected] |
+------------------+
1 row in Set (0.00 sec)

+---------------------+
| Now () |
+---------------------+
| 2001-05-17 23:06:15 |
+---------------------+
1 row in Set (0.00 sec)
Mysql>
Notice the middle semicolon, separating the commands with semicolons.

7. Display the database that currently exists
mysql> show databases;
+----------+
| Database |
+----------+
| mysql|
| Test |
+----------+
2 row in Set (0.06 sec)
Mysql>

8. Select the database and display the currently selected database
mysql> use MySQL
Database changed
Mysql>
(The use and QUIT commands do not require a semicolon to end.) ）
Mysql> Select Database ();
+---------------+
| Database () |
+---------------+
| MySQL |
+---------------+
1 row in Set (0.00 sec)

9. Displays the tables that exist in the current database
Mysql> SHOW TABLES;

10, display the contents of the table (db)
Mysql>select * from DB;

11. Cancellation of the Order
When a command is entered incorrectly and cannot be changed (multi-line statement case), you can use C to cancel the command as long as it appears before the semicolon occurs.
Mysql> Select
-User ()
C
Mysql>

These are some of the most basic operational commands that are most commonly used, and can be tightly covered with many exercises.

Study article

Having learned some of the most basic operational commands, let's learn how to create a database and database table.

1. Use the show statement to find out what database currently exists on the server:

Mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql|
| Test |
+----------+
3 Rows in Set (0.00 sec)

2. Create a database Abccs
mysql> Create DATABASE Abccs;
Note the sensitivity of the different operating systems to the case.

3. Select the database you created
Mysql> Use Abccs
Database changed
At this point you have entered the database Abccs you just created.

4. Create a database table
First look at what tables are present in your database:
Mysql> SHOW TABLES;
Empty Set (0.00 sec)
Indicates that there are no database tables in the database that you just created. Next, create a database table MyTable:

We are going to create a birthday table for your employees whose contents include the employee's name, gender, date of birth, and city of birth.
Mysql> Create TABLE mytable (name VARCHAR), sex CHAR (1),
Birth DATE, Birthaddr VARCHAR (20));
Query OK, 0 rows Affected (0.00 sec)

Because the column values of name and Birthadd are variable, you choose varchar, whose length is not necessarily 20. You can choose from
1 to 255 of any length, if you need to change its word size later, you can use the ALTER TABLE statement. ）;
The gender can be represented by a single character: "M" or "F", so a char (1) is chosen;
The birth column uses the date data type.

Once we have created a table, we can look at the results we just made, and show tables which tables are in the database:
Mysql> SHOW TABLES;
+---------------------+
| Tables in Menagerie |
+---------------------+
| mytables|
+---------------------+

5, the structure of the display table:
Mysql> DESCRIBE mytable;
+-------------+-------------+------+-----+---------+-------+
| Field | type| Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| name| varchar (20) | YES | | null| |
| sex | char (1) | YES | | null| |
| Birth | date| YES | | null| |
| deathaddr | varchar (20) | YES | | null| |
+-------------+-------------+------+-----+---------+-------+
4 rows in Set (0.00 sec)

6. Add a record to the table
Let's first use the Select command to view the data in the table:
Mysql> select * FROM MyTable;
Empty Set (0.00 sec)
This means that the table you just created is not yet documented.

Add a new record:
mysql> INSERT INTO MyTable
VALUES (' Abccs ', ' f ', ' 1977-07-07 ', ' China ');
Query OK, 1 row affected (0.05 sec)
Then use the Select command above to see what has changed.

We can add the records of all employees to the table in this way, one at a line.

7. Load data into a database table in text mode
If you enter it one by one, it is troublesome. We can add all the records to your database table in the form of a text file.
Create a text file "Mysql.txt", each line containing a record, using the Locator (tab) to separate the values, and to
The order of the columns listed in the Create table statement is given, for example:

Abccs F 1977-07-07 China
Mary F 1978-12-12 USA
Tom M 1970-09-02 USA

Use the following command to load the text file "Mytable.txt" into the MyTable table:
mysql> LOAD DATA LOCAL INFILE "Mytable.txt" into TABLE pet;

Then use the following command to see if the data has been entered into the database table:
Mysql> select * FROM MyTable;

In this article we learned how to create a database and database table and how to add records to a database table.
So how do we retrieve data from a database table?

1. Retrieving information from database tables
In fact, we have used the SELECT statement earlier, which is used to retrieve information from a database table.
The SELECT statement format is generally:

Select retrieves the keyword from the retrieved table Where the search condition (optional)

The "*" used previously indicates that all columns are selected.
The following continues with the table mytable we created in the previous article:

2. Query all data:
Mysql> select * FROM MyTable;
+----------+------+------------+----------+
| name | sex | Birth | birthaddr |
+----------+------+------------+--------+
| Abccs|f| 1977-07-07 | China |
| Mary |f| 1978-12-12 | USA |
| Tom |m| 1970-09-02 | USA |
+----------+------+------------+----------+
3 row in Set (0.00 sec)

3, correction Error Record:
If Tom's birth date is wrong, it should be 1973-09-02, you can use the UPDATE statement to fix:
mysql> Update mytable Set birth = "1973-09-02" WHERE name = "Tom";
Then use the 2 statement to see if it has been corrected.

4. Select a specific line
The above changes Tom's date of birth, we can choose Tom's line to see if there has been a change:
Mysql> SELECT * FROM mytable WHERE name = "Tom";
+--------+------+------------+------------+
| Name |sex | Birth | birthaddr |
+--------+------+------------+------------+
| tom|m| 1973-09-02 | usa|
+--------+------+------------+------------+
1 row in Set (0.06 sec)

The Where parameter above specifies the search criteria. We can also use the combination of conditions to query:
Mysql> SELECT * from mytable Where sex = "F" and birthaddr = "China";
+--------+------+------------+------------+
| Name |sex | Birth | birthaddr |
+--------+------+------------+------------+
| Abccs |f| 1977-07-07 | China |
+--------+------+------------+------------+
1 row in Set (0.06 sec)

5. Select a specific column
If you want to see the names of everyone in the table, you can do this:
mysql> Select name from MyTable;
+----------+
| name |
+----------+
| Abccs |
| Mary |
| Tom |
+----------+
3 row in Set (0.00 sec)
If you want to list name and gender columns, you can separate the keyword name and birth with commas:
Myaql> select Name,birth from MyTable;

6. Sort the rows
We can sort the records in the table by birthday size:
mysql> Select name, birth from MyTable OrDER by birth;
+----------+------------+
| name | Birth |
+----------+------------+
| Tom | 1973-09-02 |
| abccs| 1977-07-07 |
| Mary | 1978-12-12 |
+----------+------------+
3 row in Set (0.00 sec)

We can use DESC to sort the reverse order:
mysql> Select name, birth from MyTable OrDER by birth DESC;
+----------+------------+
| name | Birth |
+----------+------------+
| Mary | 1978-12-12 |
| abccs| 1977-07-07 |
| Tom | 1973-09-02 |
+----------+------------+
3 row in Set (0.00 sec)

7, Row Count
Databases often have to count data, such as the number of employees in a table, we need to use the row Count function count ().
The count () function is used to count records for non-null results:
Mysql> Select COUNT (*) from mytable;
+----------+
| COUNT (*) |
+----------+
|3 |
+----------+
1 row in Set (0.06 sec)

Number of men and women in the workforce:
mysql> Select Sex, COUNT (*) from mytable GROUP by sex;
+------+----------+
| sex | COUNT (*) |
+------+----------+
| f|2 |
| m|1 |
+------+----------+
2 row in Set (0.00 sec)

Note that we used group by to group sex.

Multi-table operation

We are familiar with the basic operations of database and database tables, and now let's look at how to manipulate multiple tables.

In a database, there may be more than one table, and the tables are interrelated. Let's continue with the previous example. The table that was created earlier contains some basic information about the employee, such as name, gender, date of birth, place of birth. We then create a table that describes the articles that the employee publishes, including the author's name, the title of the article, and the date of publication.

1. View the contents of the first table mytable:
Mysql> select * FROM MyTable;
+----------+------+------------+-----------+
| name | sex | Birth | birthaddr |
+----------+------+------------+-----------+
| abccs|f | 1977-07-07 | China |
| Mary |f | 1978-12-12 | USA |
| Tom |m | 1970-09-02 | USA |
+----------+------+------------+-----------+

2. Create a second table title (including author, article title, date of publication):
Mysql> CREATE TABLE title (writer varchar) NOT NULL,
Title varchar (+) NOT NULL,
Senddate date);

Add a record to the table, and the final table reads as follows:
Mysql> select * from title;
+--------+-------+------------+
| Writer | Title | Senddate |
+--------+-------+------------+
| Abccs | a1| 2000-01-23 |
| Mary | b1| 1998-03-21 |
| Abccs | c2> 2000-12-04 |
| Tom| d1[ 1992-05-16 |
| Tom| c2| 1999-12-12 |
+--------+-------+------------+
5 rows in Set (0.00SEC)

3. Multi-Table Query
Now we have two tables: MyTable and title. Using these two tables we can make a combination query:
For example, we want to query the author Abccs's name, gender, and article:
Mysql> Select Name,sex,title from Mytable,title
, Where Name=writer and Name= ' Abccs ';
+-------+------+-------+
| name | sex | Title |
+-------+------+-------+
| Abccs | F| c1>
| Abccs | F| a2|
+-------+------+-------+

In the example above, because the author's name, gender, and article are recorded in two different tables, a combination must be used for querying. You must specify how records in one table match records in other tables.
Note: If the writer column in the title of the second table is also named name (same as the Name column in the MyTable table) instead of writer, you must use Mytable.name and title.name to indicate the difference.

For an example, find the author, place of birth, and date of birth of the article A2:
Mysql> Select Title,writer,birthaddr,birth from Mytable,title
, where Mytable.name=title.writer and title= ' A2 ';
+-------+--------+-----------+------------+
| Title | Writer | birthaddr | Birth |
+-------+--------+-----------+------------+
| a2| Abccs | China | 1977-07-07 |
+-------+--------+-----------+------------+

Modification and backup, batch processing

Sometimes we want to modify and delete database tables and databases, which can be implemented as follows:

1. Add a column:
As in the previous example, add a column in the MyTable table to indicate whether you are single:
Mysql> ALTER TABLE mytable add column single char (1);

2, change the record
Modify the single record of Abccs to "Y":
mysql> Update mytable set single= ' y ' where name= ' Abccs ';

Now let's see what happens:
Mysql> select * FROM MyTable;
+----------+------+------------+-----------+--------+
| name | sex | Birth | birthaddr | Single |
+----------+------+------------+-----------+--------+
| abccs|f | 1977-07-07 | China | y |
| Mary |f | 1978-12-12 | USA | NULL |
| Tom |m | 1970-09-02 | USA | NULL |
+----------+------+------------+-----------+--------+

3. Add record
I've already talked about how to add a record to make it easier to see and repeat with this:
mysql> INSERT INTO MyTable
VALUES (' abc ', ' F ', ' 1966-08-17 ', ' China ', ' n ');
Query OK, 1 row affected (0.05 sec)
Take a look:
Mysql> select * FROM MyTable;
+----------+------+------------+-----------+--------+
| name | sex | Birth | birthaddr | Single |
+----------+------+------------+-----------+--------+
| abccs|f | 1977-07-07 | China | y |
| Mary |f | 1978-12-12 | USA | NULL |
| Tom |m | 1970-09-02 | USA | NULL |
| ABC |f | 1966-08-17 | China | n |
+----------+------+------------+-----------+--------+


3. Delete records
Delete a record in the table with the following command:
Mysql> Delete from mytable where name= ' abc ';
Delete Deletes a record from the table that satisfies the conditions given by where.

Show the results again:
Mysql> select * FROM MyTable;
+----------+------+------------+-----------+--------+
| name | sex | Birth | birthaddr | Single |
+----------+------+------------+-----------+--------+
| abccs|f | 1977-07-07 | China | y |
| Mary |f | 1978-12-12 | USA | NULL |
| Tom |m | 1970-09-02 | USA | NULL |
+----------+------+------------+-----------+--------+

4. Delete the table:
mysql> DROP TABLE * * * (Name of table 1), * * * name of table 2;
You can delete one or more tables and use them with care.

5, the deletion of the database:
mysql> drop database name;
Use carefully.

6, the database backup:
Back to Dos:
Mysql> quit
D:mysqlbin
Use the following command to back up the database Abccs:
Mysqldump--opt ABCCS&GT;ABCCS.DBB
ABCCS.DBB is the backup file for your database Abccs.

7. Use MySQL in batch mode:

First create a batch file Mytest.sql, which reads as follows:
Use Abccs;
SELECT * FROM MyTable;
Select Name,sex from mytable where name= ' Abccs ';

Run the following command under DOS:
D:mysqlbin MySQL < Mytest.sql
The execution results are displayed on the screen.

If you want to see the results and output a lot of results, you can use this command:
MySQL < Mytest.sql | More

We can also output the results to a file:
MySQL < mytest.sql > mytest.out

MySQL Common statements