1.创建数据库
1.1.1语法
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
}
1.1.2案例
[root@localhost ~]# ps -ef | grep mysql
mysql 1812 1 1 21:33 ? 00:00:06 /usr/sbin/mysqld
root 2291 2246 0 21:40 pts/0 00:00:00 grep --color=auto mysql
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE testdatabase;
Query OK, 1 row affected (0.01 sec)
mysql> use testdatabase;
Database changed
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p testdatabase
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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> select database();
+--------------+
| database() |
+--------------+
| testdatabase |
+--------------+
1 row in set (0.00 sec)
查看当前连接的数据库
SELECT DATABASE();
查看数据库版本
SELECT VERSION();
查看当前用户
SELECT USER();
查看所有用户
SELECT User,Host,Password FROM mysql.user;
执行对应系统命令：
SYSTEM <命令>
system cls | clear
system date 等
2.修改数据库
2.1.1语法
ALTER {DATABASE | SCHEMA} [db_name]
alter_option ...
alter_option: {
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
| READ ONLY [=] {DEFAULT | 0 | 1}
}
2.1.2案例
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.01 sec)
mysql> show create database teset;
ERROR 1049 (42000): Unknown database 'teset'
mysql> show create database test;
+----------+---------------------------------------------------------------------
-----------------------------------------------------------+
| Database | Create Database
|
+----------+---------------------------------------------------------------------
-----------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------
-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database test read only = 1;
Query OK, 1 row affected (0.01 sec)
mysql> show create database test;
+----------+---------------------------------------------------------------------
-------------------------------------------------------------------------------+
| Database | Create Database
|
+----------+---------------------------------------------------------------------
-------------------------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1
*/ |
+----------+---------------------------------------------------------------------
-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop table tb_emp1;
ERROR 3989 (HY000): Schema 'test' is in read only mode.
mysql> alter database test read only = 0;
Query OK, 1 row affected (0.00 sec)
mysql> drop table tb_emp1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
3.删除数据库
3.1.1语法
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
3.1.2案例
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdatabase |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database testdatabase;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)