MySQL基本操作{记录1}{查看,选择,创建,修改,删除}

1,启动MySQL

终端输入

mysql -u root -p

然后输入密码，启动成功

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2,基本操作

2.1查看MySQL下的所有数据库

SHOW DATABASES;

2.2选择要操作的数据库：use 数据库名；

比如我们要使用数据库work

use work;

mysql> use work;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

此后我们的所有操作都针对work

 

2.3创建数据库：使用create命令

CREATE DATABASE 数据库名;

例如我们要创建一个名为work的数据库

CREATE DATABASE work;

如果work数据库不存在则创建，如果存在则不会创建

work已存在，显示如下信息

mysql> CREATE DATABASE work;
ERROR 1007 (HY000): Can't create database 'work'; database exists

2.4查看某个数据库 使用LIKE从句，他用于匹配指定的数据库名称

SHOW DATABASES LIKE '数据库名';

例如我们要查看数据库work

SHOW DATABASES LIKE 'work';

mysql> SHOW DATABASES LIKE 'work';
+-----------------+
| Database (work) |
+-----------------+
| work            |
+-----------------+
1 row in set (0.00 sec)

2.4查看某数据库的信息（定义声明）

SHOW CREATE DATABASE 数据库名;

查看work的信息（定义声明）

SHOW CREATE DATABASE work;

mysql> SHOW CREATE DATABASE work;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| work     | CREATE DATABASE `work` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.5修改数据库

ALTER DATABASE 数据库名

终端下：

mysql> ALTER DATABASE 数据库名
    -> DEFAULT CHARACTER SET 字符集名
    -> DEFAULT COLLATE 校对规则名;

对work数据库进行修改

查看：

 SHOW CREATE DATABASE work;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| work     | CREATE DATABASE `work` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+

修改：

mysql> ALTER DATABASE work
    -> DEFAULT CHARACTER SET gb2312
    -> DEFAULT COLLATE gb2312_chinese_ci;
Query OK, 1 row affected (0.02 sec)

再次查看：修改成功

mysql> SHOW CREATE DATABASE work;
+----------+----------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                    |
+----------+----------------------------------------------------------------------------------------------------+
| work     | CREATE DATABASE `work` /*!40100 DEFAULT CHARACTER SET gb2312 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------+

2.6删除数据库，使用DORP命令(谨慎使用)不能删除MySQL安装时自己创建的数据库

命令格式：

DROP DATABASE 数据库名;

例：删除work_tb1

DROP DATABASE work_tb1;

mysql> DROP DATABASE work_tb1;
Query OK, 0 rows affected (0.02 sec)