Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the methods of using MySQL". In daily operation, I believe that many people have doubts about the use of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is the use of MySQL?" Next, please follow the editor to study!

1. Start the MySQL server

In fact, the last article talked about how to start MySQL. There are two ways:

One is to use winmysqladmin. If the machine has been running automatically when it starts, it can directly proceed to the next step.

The second is to run in DOS mode.

D:/mysql/bin/mysqld

2. Enter the mysql interactive interface

In DOS mode, run:

D:/mysql/bin/mysql-u root-p

The prompt appears, and you have entered the interoperability mode of mysql.

If "ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)" appears, your MySQL has not been started yet.

3. Exit the MySQL 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 exit.

4. The first order

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 current date. Try to manipulate the above command in different case and see how it turns out.

The results show that the case of the mysql command is consistent.

Practice the following:

Mysql > Select (20,5) * 4

Mysql > Select (20,5) * 4 pi () / 3)

Mysql > Select (205th) * 4 AS Result,sin (pi () / 3); (AS: specify pseudonym as Result)

5. Multi-line statement

A command can be entered in multiple lines until the semicolon ";" appears:

Mysql > select

-> USER ()

->

-> now ()

->

+-- +

| | USER () | now () |

+-- +

| | ODBC@localhost | 2001-05-17 22:59:15 |

+-- +

6. Use the show statement to find out what databases currently exist 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 case sensitivity of different operating systems.

8. Select the database you created

Mysql > USE abccs

Database changed

At this point, you have entered the database abccs that you just created.

9. Create a database table

First of all, let's see what tables exist in your database:

Mysql > SHOW TABLES

Empty set (0.00 sec)

Indicates that there are no database tables in the database you just created. Let's create a database table mytable:

We want to set up a birthday table for your employees, which contains the employee's name, gender, date of birth and city of birth.

Mysql > CREATE TABLE mytable (name VARCHAR (20), sex CHAR (1)

-> birth DATE, birthaddr VARCHAR (20)

Query OK, 0 rows affected (0.00 sec)

Because the column values of name and birthadd vary, the length of VARCHAR is not necessarily 20. You can choose from the

Any length from 1 to 255, if you need to change its word length later, use the ALTER TABLE statement. )

Gender only needs one character to indicate "m" or "f", so choose CHAR (1)

The birth column uses the DATE data type.

After creating a table, we can look at the results we just did and use SHOW TABLES to show which tables are in the database:

Mysql > SHOW TABLES

+-+

| | Tables in menagerie |

+-+

| | mytables |

+-+

10. Display the structure of the 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. Correct the error record:

If there is an error in the date of birth of tom, it should be 1973mur09Mui02, then you can use the update statement to correct it:

Mysql > update mytable set birth = "1973-09-02" where name = "tom"

Then use the statements in 2 to see if they have been corrected.

13. Select specific lines

The date of birth of tom has been modified above, so we can select the line tom to see if it has changed:

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 parameters of the WHERE above specify the retrieval conditions. We can also use combined 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

Now that we are familiar with the basic operations of databases and database tables, let's take a look at how to manipulate multiple tables.

In a database, there may be multiple tables, all of which are interrelated. Let's continue with the previous example. The table established earlier contains some basic information about the employee, such as name, gender, date of birth, and place of birth. We create another table that describes the article published by the employee, including the author's name, article title, and publication date.

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, publication date):

Mysql > create table title (writer varchar (20) not null

-> title varchar (40) 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 combined query:

For example, we need to query the name, gender and article of the author abccs:

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 above example, because the author's name, gender, and article are recorded in two different tables, a combination must be used for the query. You must specify how records in one table match records in other tables.

Note: if the writer column in the second table title is also named name (the same as the name column in the mytable table) instead of writer, it must be represented as mytable.name and title.name to show the difference.

To give another example, it is used to query the author, place of birth and date of birth of 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:

For example, add a column to the mytable table in the previous example to indicate whether you are single single:

Mysql > alter table mytable add column single char (1)

16. Modify the record

Change the single record of abccs to "y":

Mysql > update mytable set single='y' where name='abccs'

Now let's see what happened:

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 records

I've already talked about how to add a record. For ease of viewing, repeat with this:

Mysql > insert into mytable

-> values ('abc','f','1966-08-17)

Query OK, 1 row affected (0.05sec)

Check it out:

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 meets 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 carefully.

20. Deletion of database:

Mysql > drop database database name

Use it carefully.

21. Backup of the database:

Go back to DOS:

Mysql > quit

D:mysqlbin

Back up the database abccs using the following command:

Mysqldump-- opt abccs > abccs.dbb

Abccs.dbb is the backup file of your database abccs.

22. Use MySQL in batch mode:

First, create a batch file, mytest.sql, with the following contents:

Use abccs

Select * from mytable

Select name,sex from mytable where name='abccs'

Run the following command under DOS:

D:mysqlbin mysql

< mytest.sql 在屏幕上会显示执行结果。 如果想看结果，而输出结果很多，则可以用这样的命令： mysql < mytest.sql | more 我们还可以将结果输出到一个文件中： mysql < mytest.sql >

Mytest.out

23. Please log in to mysql using root first, by:

C:/mysql/bin/mysql-u root-p

24. Create a user

Mysql > GRANT ALL PRIVILEGES ON javatest.* TO javauser@ "%"

-> IDENTIFIED BY "javadude"

At this point, the study on "what is the use of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.