MYSQL database operations (add, delete, modify, query)

Mysql has become one of the most popular relational databases. The latest mysql version is now 8.0. In addition, it is considered a good friend of PHP. The lamp architecture was once all the rage. Today we will take a look at some operations of the database.

First of all, let me state that the version of mysql I demonstrated is version 5.7.28.

Create a new database

First of all, I will teach you a trick. What to do if you forget the database creation syntax? MYSQL provides help to help you. For example, you forget the database creation statement and only know that the starting command is create. Then you can enter help craete;

mysql> help create
……
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   ……

on the MySQL client. Now that we know the first two words of the database creation statement, let's continue with help and see what the specific syntax is.

mysql> help create database
……
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...
create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
……

Now we know the specific syntax.

Grammar:

create database [if not exists] 数据库名 
[
    [charset 字符集名]
    [collate 校对集名]
]

• if not exists means that the database will only be created when it does not exist

• Character set This and html characters Set means one thing, usually utf8 or utf8mb4

• is chosen. This proofing set is related to comparison and sorting between characters.

Let’s complete the establishment of a job library

mysql> CREATE DATABASE IF NOT EXISTS jobs
    -> CHARSET utf8
    -> COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

View the database

When a library After it is established, you need to check it to see if it is really established.

The syntax for viewing the database is as follows:

show databases [like patter]

This command can display all databases and search for databases by mode.

Now let’s take a look at how many libraries there are currently

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jobs               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

If I want to find which libraries end with s, how to check

mysql> SHOW DATABASES LIKE '%s';
+---------------+
| Database (%s) |
+---------------+
| jobs          |
| sys           |
+---------------+
2 rows in set (0.00 sec)

Select database

If we want to create a table in the database, or add or delete data, etc., the first operation we must do is to select the database. These operations are meaningful and can be performed correctly only if the database is selected.

The operation of selecting a database is very simple. The syntax is as follows:

use 数据库名

mysql> USE jobs;
Database changed

In this way, the jobs library is selected.

Modify the library

When I find that the character set or proofing set is incorrect when I create it, how should I change it?

Syntax:

ALTER DATABASES 库名
CHASET 字符集名
COLLATE 校对集名

As you can see, the modification and new operations are basically the same, except that CREATE is changed to ALTER.

Delete the library

The library name created before identifies jobs, but now I think the last s can be omitted. So can the library name be changed? Sorry, the library name cannot be modified. You can only delete the library jobs first and then re-create the library jobs.

Delete library syntax:

DROP DATABASE [IF EXISTS] 库名

mysql> DROP DATABASE IF EXISTS jobs;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE DATABASE job
    -> CHARSET utf8mb4
    -> COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

Note: Deletion is an extremely dangerous operation. It would be terrible if you really delete the database and run away!

The above is the detailed content of MYSQL database operations (add, delete, modify, query). For more information, please follow other related articles on the PHP Chinese website!