Shulou(Shulou.com)06/01 Report--

This article mainly introduces the installation of MySQL and basic commands, the contents of the article are carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the installation of MySQL and basic commands bar.

Mysql installation mode

There are three ways to install MySQL, one is RPM installation, one is source code compilation and installation, and the other is general binary format package installation.

RPM installation, you can directly use the yum command to install or download the RPM package before installing

Yum installation commands:

Yum-y install mysql-server

The system automatically resolves the dependencies and installs the mysql client.

Command categories in mysql interaction mode:

Client commands: commands executed on the client

CVM command: execute on the CVM and return the result to the client. You must use a statement Terminator, which is sealed by default ";"

MySQL database:

After the mysql installation is complete, there are 3 databases by default

Information_schema: the information that is located in memory when mysql is running. It saves mysql runtime data, which is available only when mysql is started, and is usually empty.

Test: a database used only for testing

Mysql:mysql 's database

MySQL database directory:

[root@host2 ~] # ls / var/lib/mysql/ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock test

You can see that there is no information_schema database because it exists in memory.

Common MySQL commands:

DDL: define database objects:

CREATE:

ALTER:

DROP:

DML: data manipulation language

INSERT

UPDATE

DELETE

DCL data control language

GRANT:

REVOKE:

Create the database:

CREATE DATABASE db_name

CREATE DATABASES IF NOT EXISTS testdb

Mysql > CREATE DATABASE test_db;Query OK, 1 row affected (0.00 sec) mysql > SHOW DATABASES +-+ | Database | +-+ | information_schema | | mysql | | test | | test_db | +-+ 4 rows in set (0.01sec)

Delete the database:

DROP DATABASE db_name

Mysql > DROP DATABASE test_db;Query OK, 0 rows affected (0.00 sec) mysql > SHOW DATABASES;+-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec)

Create a table:

CREATE TABLE tb_name (col1,col2,...) Col1,col2 is the field name

Note: tables are database dependent, so before creating tables, be sure to specify the default database and use the command

USER DATABASE; to specify the database

Create a table named student that contains three fields, Name, Age, and Gender

Mysql > CREATE TABLE students (Name CHAR (20) NOT NULL, Age TINYINT UNSIGNED,Gender CHAR (1) NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql > SHOW TABLES;+-+ | Tables_in_test_db | +-+ | students | +-+ 1 row in set (0.00 sec)

View a table in a database:

SHOW TABLES FROM db_name

View the table structure:

DESC table_name

Mysql > DESC students +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | Name | char (20) | NO | | NULL | | Age | tinyint (3) unsigned | YES | | NULL | | Gender | char (1) | NO | | NULL | | +-+- -+ 3 rows in set (0.00 sec)

Delete the table:

DROP TABLE tb_name

Modify the table:

ALTER TABLE tb_name

MODIFY: modify a field, modify the field properties, and keep the field name unchanged

CHANGE: change a field, change the field name

ADD: adding field

DROP: deleting field

You can query help information through the help command:

Help CREATE TABLE

Add a field

For example: modify the student table created earlier and add a field course course

Mysql > ALTER TABLE students ADD course VARCHAR; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC students +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | Name | char (20) | NO | | NULL | | Age | tinyint (3) unsigned | YES | | NULL | | Gender | char (1) | NO | | NULL | | course | varchar (100) | YES | | NULL | | | +-+-+ 4 rows in set (0.00 sec) |

Modify the field name, such as changing the name of the course field added above to Course

Mysql > ALTER TABLE students CHANGE course Course VARCHAR; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC students +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | Name | char (20) | NO | | NULL | | Age | tinyint (3) unsigned | YES | | NULL | | Gender | char (1) | NO | | NULL | | Course | varchar (100) | YES | | NULL | | | +-+-+ 4 rows in set (0.00 sec) |

Delete a field:

DROP col_name

Insert data:

INSERT INTO tb_name (col1,col2,...) VALUES | VALUE ('STRING',NUM...)

The above command means: insert data into a table with the field name col1,col2,... If the inserted value is a string, it is enclosed in quotation marks, and if it is a numeric value, a number is used directly. If each field is given a value, you do not have to write the field name

For example: insert two pieces of data into the students table, Zhang San and Li Si

Mysql > INSERT INTO students (Name,Gender) VALUE ('ZhangSan','M'), (' LiSi','F')

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

View data

Mysql > SELECT * FROM students +-+ | Name | Age | Gender | Course | +-+ | ZhangSan | NULL | M | NULL | | LiSi | NULL | F | NULL | +-- -+ 2 rows in set (0.01sec)

Insert a user with values for all fields:

Note that all fields have values, so there is no need to specify a field name

Mysql > INSERT INTO students VALUES ('ZengChengpeng',28,'M','IT'); Query OK, 1 row affected (0.00 sec) mysql > SELECT * FROM students WHERE Name='ZengChengpeng' +-+ | Name | Age | Gender | Course | +-+ | ZengChengpeng | 28 | M | IT | +- -+ 1 row in set (0.00 sec)

Modify the data:

UPDATE tb_name SET column=value WHERE condition

For example: change the name of the Course course in ZengChengpeng to Develop

Mysql > UPDATE students SET Course='Develop' WHERE Name='ZengChengpeng';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT * FROM students WHERE Name='ZengChengpeng' +-+ | Name | Age | Gender | Course | +-+ | ZengChengpeng | 28 | M | Develop | +- -+ 1 row in set (0.00 sec)

SELETE statement:

Selete statements are divided into two cases.

Select: specify a field as the search code, make a logical comparison, and filter the rows that meet the criteria. WHERE specifies selection conditional projection: specify a field as the search code, make a logical comparison, and filter the fields that meet the criteria. Mysql > SELECT Name,Course FROM students WHERE Gender='M'; +-+ | Name | Course | +-+-+ | ZhangSan | NULL | | ZengChengpeng | Develop | +-+-+

Delete data:

DELECT FROM tb_name WHERE condition

Create a user:

CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY' PASSWORD'

Delete a user:

DROP USER 'USERNAME'@'HOST'

HOST:

IP:

HOSTNAME:

NETWORK:

Wildcards: wildcards are enclosed in quotation marks

_: the underscore matches any single character: for example, 172.16.0.0._

%: match any character:

Jerry@'%'

User permissions:

Add permissions:

GRANT pri1,pri2,... ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' [IDENTIFIED BY' PASSWORD']

Pri1 pri2 represents the permission name, and all permissions are represented by ALL PRIVILEGES

Revoke permissions:

REVOKE pri1,pri2,... ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST'

Create a user example:

Mysql > CREATE USER 'jerry'@'%' IDENTIFIED BY' jerry'

View user authorization:

SHOW GRANTS FOR 'USERNAME'@'HOST'

Mysql > SHOW GRANTS FOR jerry@'%' +-+ | Grants for jerry@% | +- -+ | GRANT USAGE ON *. * TO 'jerry'@'%' IDENTIFIED BY PASSWORD' * 09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' | + -+

Example: give the jerry user all permissions to test_db all tables in this database

Mysql > GRANT ALL PRIVILEGES ON test_db.* TO 'jerry'@'%';Query OK, 0 rows affected (0.00 sec) mysql > SHOW GRANTS FOR' jerry'@'%' +-+ | Grants for jerry@% | +- -+ | GRANT USAGE ON *. * TO 'jerry'@'%' IDENTIFIED BY PASSWORD' * 09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' | GRANT ALL PRIVILEGES ON `test_ db`.* TO 'jerry'@'%' | +- -+ 2 rows in set (0.00 sec)

Revoke all permissions:

Mysql > REVOKE ALL PRIVILEGES ON test_db.* FROM jerry@'%';Query OK, 0 rows affected (0.00 sec) mysql > SHOW GRANTS FOR 'jerry'@'%' +-+ | Grants for jerry@% | | +- -- + | GRANT USAGE ON *. * TO 'jerry'@'%' IDENTIFIED BY PASSWORD' * 09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' | + -- + 1 row in set (0.00 sec)

After reading the above about the installation mode and basic commands of MySQL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.