Introduction to MySQL (3)-Database Operation (Linux and Windows graphical tools)

table of Contents

1. Operate the database on Linux

One, view the database

instruction:

SHOW DATABASES;

This command returns a list of available databases.
Included in this list may be databases used internally by MySQL.

Insert picture description here


View the character encoding of the database:SHOW CREATE DATABASE 数据库名;

SHOW CREATE DATABASE mydb;
SHOW CREATE DATABASE mydb2;
Insert picture description here

Second, select the database

Use the USE keyword, which is not case-sensitive in MySQL, but for greater readability, you can capitalize the keyword.

USE mysql;

View current database

SELECT DATABASE();
Insert picture description here

Three, query the table in the database

SHOW TABLES ;
Insert picture description here

Fourth, create a database

Method 1: Use the specified character encoding table to create a database.
grammar:CREATE DATABASE 数据库名 CHARACTER SET 字符编码;

CREATE DATABASE mydb CHARACTER SET utf8mb4;
Insert picture description here

Tip: There are two encodings of utf8 and utf8mb4 in mysql. Please forget utf8 in mysql and always use utf8mb4. This is a legacy of mysql. The utf8 in mysql can only support character encoding up to 3bytes in length. For some text that needs to occupy 4bytes, utf8 of mysql does not support it, and utf8mb4 should be used.

Method 2: Use the default character encoding table to create a database
Format:CREATE DATABASE 数据库名;

CREATE DATABASE mydb2;
Insert picture description here

Five, delete the database

DROP DATABASE mydb;
Insert picture description here

In the same way, in order to prevent errors, you can also add a judgment to delete the database, and delete it if it exists.

DROP DATABASE IF EXISTS mydb;
Insert picture description here

Sixth, modify the database code

ALTER DATABASE mydb CHARACTER SET gbk;

Note: MySQL once provided a command to rename the database: RENAME DATABASE, but this command was later cancelled, so the database cannot be renamed directly.

2. Operate the database on graphical tools

Take DataGrip as an example

One, view the database

Enter the following [1] instruction, click the green triangle at [2] to display the content information below,
or a green box will appear after inputting the instruction, click Ctrl+Enter to run the same

Insert picture description here

Two, select the database, query the table in the database

Insert picture description here


If you want to execute several instructions together, you can select all of them and then click the small green triangle above

Insert picture description here

Three, create a database

And the above instructions are the same

Insert picture description here

Fourth, delete the database

Insert picture description here

3. Summary

Create database

– Method 1: Use the specified character encoding table to create a database
Format: create database 数据库名 character set 字符编码;
– Method 2: Use the default character encoding table to create a database.
Format: create database 数据库名;
– Method 3: First judge whether it exists, and then create it
Format:create database if not exists 数据库名 character set 字符编码;

View database

– View all databases
Format: show databases;
– View the character encoding of the specified database
Format:show create database 数据库名;

Delete database

– Delete the database
Format:drop database 数据库名;

Use database

– View the currently used database
Format: select database();
– Set the currently used database
Format:use 数据库名;