LINUX summary article 4th: installing and using mysql_MySQL in LINUX

LINUX summary article 4th: installing and using mysql bitsCN.com in LINUX

To install mysql in LINUX, you must install both the client and server.

Because the mysql system is distributed, both the server and client systems exist.

The server system includes a set of programs and related files running on the server host. The client system connects to the database server and is used to query, modify, and manage data in the database.

1. what is SQL

SQL generally refers to the structured query language.

Structured Query Language (SQL) is a database Query and programming Language used to access data and Query, update, and manage relational database systems; it is also the extension of the database script file. Structured Query Language is an advanced non-procedural programming language that allows users to work on high-level data structures. It does not require users to specify the data storage method, nor require users to understand the specific data storage method. Therefore, different database systems with completely different underlying structures are required, the same structured query language can be used as an interface for data input and management. Structured Query Language statements can be nested, which gives it great flexibility and powerful functions.

II. what is mysql?

Mysql is a relational database management system. It is a program that can store a large variety of data.

Mysql uses the structured SQL query language.

Use DBMS to manage databases. A database management system (dbms) is a large-scale software used to manipulate and manage databases. it is used to establish, use, and maintain databases.

3. install mysql1. common networking diagram

1) the mysql server and client are installed on one machine at the same time.

----- --------

2) the mysql client is installed on server A, and the server is installed on another connected server B.

A-------B

3) A Terminal A, which does not run any SQL-related software, is connected to server B (running mysql client), and B is connected to server C (running mysql server.

A---------B--------------C

In the 3rd mode, the mysql client is not on its own machine. you need to log on to another machine to use the mysql client, and use telnet to perform the operation.

This article uses the first mode.

2. install mysql

1) install the mysql client using yum install mysql

2) yum install mysql-server install mysql server

3) service mysqld start mysql server

4. log on to and exit the mysql server

Mysql users include common users and root users.

Root users are super administrators with all permissions, such as creating users, deleting users, changing passwords of all users, and managing permissions.

A common user only has the permissions granted to the user when the user is created.

1. log on to the mysql server

Mysql-u root

Enter mysql. After mysql is installed, the default user root and default password are empty.

-U is used to specify the user name.

Of course, you can also do this:

Mysql-h localhost-u root-p

-H is used to specify the server name, such as www.xxx.com or 192.168.59.130. localhost indicates the local machine, and 127.0.0.1 also indicates the local machine.

Of course, if you log on to the local machine,-h can be ignored.

-U same as above

-P indicates the password. If this parameter is left blank, the default password is null. Therefore, if the password is not blank and you do not add-u, an error is prompted.

After you press enter, you will be prompted to enter the password.

Mysql-u root-p

Log on to the root account.

Of course, you can also do this:

Mysql-u root-pxxx

This xxx is my login password, in the text format. if you don't want others to see your password, use the previous method.

Mysql-u root-p mysql

Not only the mysql server, but also the mysql database. If you do not specify a mysql database, it will be logged into the mysql database by default.

2. Disconnect

QUIT

Disconnect. Of course, EXIT or CTRL + D is also acceptable.

5. create and delete common users 1. create users

Method 1: Use the create user statement

Create user 'user1' @ 'localhost' identified by 'user1'

Create a user named user1 and the password is user1.

Method 2: INSERT statements

Directly add user information to the mysql. user table.

The specific example is not given.

Method 3: use the GRANT statement

Grant select on *. * TO 'user3' @ 'localhost' identified by 'user3 ';

Create a user named user3 and the m password is user3.

The GREAT statement can be authorized to users. for example, *. * indicates all tables in the database. User3 has the query permission on all tables.

2. delete a user

Method 1: Use the drop user statement

Drop user 'user3' @ 'localhost ';

Delete a user whose username is user3

Method 2: use the DELETE statement

Delete user information directly from mysql. user

The specific example is not given.

6. Change Password 1. the root user changes the root password

Method 1: Use the mysqladmin command

Mysqladmin-u root-p password "root"

Change password to root.

Method 2: UPDATE

Use mysql
Update user set Password = PASSWORD ('Hello') where user = 'root ';

Flush privileges;

First select the database, and then set the password for a user (here is root) (here is hello)

Finally, use flush privileges to refresh the mysql system permission table.

Method 3: Use SET

Set password = PASSWORD ('root ');

Change the root password to root.

2. change the password of a common user as the root user

Method 1: Use the SET statement

Set password for 'user2' @ 'localhost' = PASSWORD ('user1 ');

Method 2: Use UPDATE

Directly modify the user table in the mysql database.

No specific example is written.

Method 3: use the GRANT statement

Grant select on *. * to 'user2' @ 'localhost' identified by 'user2 ';

3. change the password of a common user

Set password = PASSWORD ('test ');

7. operate the database 1. create a database

Create database example;

2. delete a database

Drop database example;

3. display the database

Show databases;

4. select a database

Use mysql;

8. operation table 1. create a table

Create table name (attribute name data type [integrity constraints ],

Attribute name data type [integrity constraints]

.

.

.

Attribute name data type );

For example:

Create table text1

(

Id INT,

Name VARCHAR (20 ),

SEX BOOLEAN

);

2. display tables in the database

Show tables;

3. view the table structure

DESCRIBE text1;

4. view the detailed table structure

Show create table text1;

5. basic query

Select id, name from text1;

Query the information of the field id and name from the table text.

6. Insert data

Insert into text1 (id, name, sex) values (2000, 'xiaosan', 0 );

7. display table data

SELECT * FROM text1;

8. modify records

Update name set id = 3000 where name = 'xiaosan ';

9. delete records

Delete from name where id = 3000;

10. delete a table

Drop table name

9. database selection

Oracle, DB2, and SQL Server: mainly used in large management systems. SQL Server runs in windows.

Access, MysSQL, PostgreSQL: small and medium databases, mainly used in small and medium management systems, Access can only run in windows

Complexity:

Oracle, DB2> MySQL, PostgreSQL

Performance:

Oracle, DB2> MySQL, PostgreSQL

Please refer to the following link for more information: