MySQL How to use a simple tutorial

1. Start the MySQL server
In fact, the previous article has talked about how to start MySQL. Two methods:
One is to use Winmysqladmin, if the machine started automatically run, you can go directly to the next step.
Second, running in DOS mode
D:/mysql/bin/mysqld
2. Enter MySQL Interactive interface
In DOS mode, run:
D:/mysql/bin/mysql-u root-p
A prompt appears, and the interactive mode of MySQL is now entered.
If "Error 2003:can ' t connect to MySQL server on ' localhost ' (10061)" appears, your MySQL has not started yet.
3. Exit the MySQL operator interface
Enter quit at the mysql> prompt to exit the interactive interface at any time:
Mysql> quit
Bye
You can also use control-d to quit.
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)
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 22:59:15 |
+--------------------+-----------------------------+
6. 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)
7. Create a database Abccs
mysql> CREATE DATABASE Abccs;
Note the sensitivity of the different operating systems to the case.
8. Select the database you created
Mysql> Use Abccs
Database changed
At this point you have entered the database Abccs you just created.
9. 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 |
+---------------------+
10, 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 | |
+-------------+-------------+------+-----+---------+-------+
11. 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)
12, 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.
13. 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)
14. 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 | A2 | 2000-12-04 |
| Tom | C1 | 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 | A1 |
| 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 |
+-------+--------+-----------+------------+
15. 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);
16, 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 |
+----------+------+------------+-----------+--------+
17. 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 |
+----------+------+------------+-----------+--------+
18. 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 |
+----------+------+------------+-----------+--------+
19. 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.
20, the deletion of the database:
mysql> drop database name;
Use carefully.
21, the database backup:
Back to Dos:
Mysql> quit
D:mysqlbin
Use the following command to back up the database Abccs:
Mysqldump--opt ABCCS>ABCCS.DBB
ABCCS.DBB is the backup file for your database Abccs.
22. 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
23. Please log in to MySQL with Root first, method:
C:/mysql/bin/mysql-u root-p

24. Create a user

Mysql> GRANT all privileges in javatest.* to [email protected] "%"
-Identified by "Javadude";

Also, attach a picture of MySQL Workbench

MySQL How to use a simple tutorial