MySQL常用命令
- MySQL常用命令汇总
- 一、查看MySQL的版本：`mysql --version`和`select version();`
- 二、连接MySQL服务：`mysql -h服务器IP -P端口号 -u用户名 -p密码`
- 三、退出MySQL服务：`exit`或者`quit`
- 四、停止MySQL服务：`net stop MySQL服务名`
- 五、启动MySQL服务：`net start MySQL服务名`
- 六、查看有哪些数据库：`show databases;`
- 七、使用某个据库：`use 数据库名;`
- 八、查看当前数据库有哪些表：`show tables;`
- 九、创建数据库：`create database 数据库名;`
- 十、查看创建某个数据库的语句（编码信息）：`show create database 数据库名;`
- 十一、创建表：`create table 表名(字段名1 字段1数据类型, 字段名2 字段2数据类型……);`
- 十二、查看创建某个表的语句（搜索引擎、编码信息）：`show create table 表名;`
- 十三、查询某个表中所有的数据：`select * from 表名;`
- 十四、向某个表中插入数据：`insert into 表名 values(字段1的值, 字段2的值, ……);`
- 十五、查看以`character_`开头的变量（字符编码）：`show variables like 'character_%';`
- 十六、查看以`collation_`开头的变量（查看编码信息）：`show variables like 'collation_%';`
- 十七、修改'root'@'localhost'用户的密码规则和密码：`ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';`
- 十八、刷新权限：`FLUSH PRIVILEGES;`
- 十九、修改表信息（编码格式）：`alter table 表名 charset utf8;`
- 十七、删除数据库：`drop database if exists 数据库名;`
- 十八、导入现有的数据表、表的数据：`source .sql文件完整路径;`
- 十九、备份数据库：`mysqldump -h 数据库服务器IP -u 用户名 -p密码 数据库名 >D:\bakdbtest1.sql`
- 二十、显示表结构：`DESCRIBE 表名`或者`DESC 表名`
- 二十一、MySQL信息函数：`VERSION()`、`CONNECTION_ID()`、`DATABASE()`、`SCHEMA()`、`USER()`、`CURRENT_USER()`、`CHARSET('字符串')`、`COLLATION('字符串')`
MySQL常用命令汇总
C:\Users\song>mysql --version # 查看MySQL的版本
mysql> select version(); # 查看MySQL的版本
C:\Users\song>mysql -hlocalhost -P3306 -uroot -p123456 # 连接MySQL服务
C:\Users\song>mysql -h 127.0.0.1 -P 3306 -u root -p # 连接MySQL服务
mysql> exit # 退出MySQL服务
mysql> quit # 退出MySQL服务
C:\Windows\System32>net stop MySQL80 # 停止MySQL服务
C:\Windows\System32>net start MySQL80 # 启动MySQL服务
mysql> show databases; # 显示所有的数据库
mysql> use mysql; # 使用mysql数据库
mysql> show tables; # 显示当前数据库的所有表
mysql> create database atguigu; # 创建atguigu数据库
mysql> show create database atguigu; # 查看创建数据库atguigu
mysql> create table students(id int, name varchar(15)); # 创建表 students
mysql> show create table students; # 查看创建students表的语句（搜索引擎、编码信息）
mysql> select * from students; # 查询students表中所有的数据
mysql> insert into students values(1001, '张三'); # 向students表中插入数据
mysql> show variables like 'character_%'; # 查看以 character_ 开头的变量
mysql> show variables like 'collation_%'; # 查看以 collation_ 开头的变量
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; # 修改'root'@'localhost'用户的密码规则和密码
mysql> FLUSH PRIVILEGES; # 刷新权限
mysql> alter table teachers charset utf8; # 修改 teachers 表的字符编码为utf8
mysql> drop database if exists atguigu; # 删除数据库atguigu
mysql> source D:\database\atguigudb.sql # 导入D:\database\atguigudb.sql文件中的数据表、表的数据
C:\Users\song>mysqldump -h 数据库服务器IP -u 用户名 -p密码 数据库名 >D:\bakdbtest1.sql # 备份数据库到 D:\bakdbtest1.sql
mysql> DESCRIBE employees; # 显示了表中字段的详细信息
mysql> DESC employees; # 显示了表中字段的详细信息
mysql> SELECT VERSION(), CONNECTION_ID(), DATABASE(), SCHEMA(), USER(), CURRENT_USER(), CHARSET('尚硅谷'), COLLATION('尚硅谷') FROM DUAL; # MySQL信息函数
一、查看MySQL的版本：mysql --version和select version();
C:\Users\song>mysql --version # 查看MySQL的版本
mysql Ver 8.0.26 for Win64 on x86_64 (MySQL Community Server - GPL)
C:\Users\song>mysql -hlocalhost -P3306 -uroot -p # 连接MySQL
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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 version(); # 查看MySQL的版本
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
mysql> quit # 退出
Bye
C:\Users\song>
二、连接MySQL服务：mysql -h服务器IP -P端口号 -u用户名 -p密码
C:\Users\song>mysql -hlocalhost -P3306 -uroot -p123456 # 连接MySQL服务
C:\Users\song>mysql -h 127.0.0.1 -P 3306 -u root -p # 连接MySQL服务
说明：参数后面可以加空格，也可以不加空格
-h：mysql服务器名或者IP。-P：mysql服务器端口号。大写的P，默认端口号是3306。-u：用户名-p：密码。小写的p，后面不可以加空格！！！，会默认为是密码中的空格。
三、退出MySQL服务：exit或者quit
四、停止MySQL服务：net stop MySQL服务名
1、搜索框输入cmd，在出现的命令提示符处，右键，选择以管理员身份运行。
2、输入命令：net stop MySQL服务名，停止服务。注：windows不区分服务名的大小写。
如果不以管理员身份运行，会报错“发生系统错误 5。 拒绝访问。”
3、刷新后，可以看到MySQL80服务已停止。
五、启动MySQL服务：net start MySQL服务名
1、搜索框输入cmd，在出现的命令提示符处，右键，选择以管理员身份运行。
2、输入命令：net start MySQL服务名，启动服务。注：windows不区分服务名的大小写。
如果不以管理员身份运行，会报错“发生系统错误 5。 拒绝访问。”
3、刷新后，可以看到MySQL80服务已启动。
六、查看有哪些数据库：show databases;
mysql> show databases; # 查看有哪些数据库
+--------------------+
| Database |
+--------------------+
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.04 sec)
七、使用某个据库：use 数据库名;
mysql> show databases; # 查看有哪些数据库
+--------------------+
| Database |
+--------------------+
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.04 sec)
mysql> use dbtest1; # 使用dbtest1数据库
Database changed
mysql>
八、查看当前数据库有哪些表：show tables;
mysql> show databases; # 查看有哪些数据库
+--------------------+
| Database |
+--------------------+
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.04 sec)
mysql> use dbtest1; # 使用dbtest1数据库
Database changed
mysql>show tables; # 查看当前数据库有哪些表
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| employees |
+-------------------+
1 row in set (0.02 sec)
mysql>
九、创建数据库：create database 数据库名;
mysql> show databases; # 查看有哪些数据库
+--------------------+
| Database |
+--------------------+
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.04 sec)
mysql> create database atguigu; # 创建atguigu数据库
Query OK, 1 row affected (0.11 sec)
mysql> show databases; # 查看有哪些数据库
+--------------------+
| Database |
+--------------------+
| atguigu |
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
十、查看创建某个数据库的语句（编码信息）：show create database 数据库名;
mysql> show create database atguigu; # 查看创建数据库atguigu
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database
|
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| atguigu | CREATE DATABASE `atguigu` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
十一、创建表：create table 表名(字段名1 字段1数据类型, 字段名2 字段2数据类型……);
mysql> use dbtest1; # 使用数据库 dbtest1
Database changed
mysql> show tables; # 查看当前数据库的所有表
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| employees |
+-------------------+
1 row in set (0.00 sec)
mysql> create table students(id int, name varchar(15)); # 创建表 students
Query OK, 0 rows affected (0.93 sec)
mysql> show tables; # 查看当前数据库的所有表
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| employees |
| students |
+-------------------+
2 rows in set (0.00 sec)
十二、查看创建某个表的语句（搜索引擎、编码信息）：show create table 表名;
mysql> show create table students; # 查看创建students表的语句（搜索引擎、编码信息）
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int DEFAULT NULL,
`name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
十三、查询某个表中所有的数据：select * from 表名;
mysql> select * from students; # 查询students表中所有的数据
Empty set (0.00 sec)
十四、向某个表中插入数据：insert into 表名 values(字段1的值, 字段2的值, ……);
mysql> select * from students; # 查询students表中所有的数据
Empty set (0.00 sec)
mysql> insert into students values(1001, '张三'); # 向students表中插入数据
Query OK, 1 row affected (0.07 sec)
mysql> select * from students; # 查询students表中所有的数据
+------+------+
| id | name |
+------+------+
| 1001 | 张三 |
+------+------+
1 row in set (0.01 sec)
十五、查看以character_开头的变量（字符编码）：show variables like 'character_%';
mysql> show variables like 'character_%'; # 查看以 character_ 开头的变量
+--------------------------+----------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | D:\Environment\MySQL\MySQL Server 8.0.26\share\charsets\ |
+--------------------------+----------------------------------------------------------+
8 rows in set, 1 warning (0.07 sec)
十六、查看以collation_开头的变量（查看编码信息）：show variables like 'collation_%';
mysql> show variables like 'collation_%'; # 查看以 collation_ 开头的变量
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | gbk_chinese_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set, 1 warning (0.00 sec)
十七、修改’root’@'localhost’用户的密码规则和密码：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> select * from user; # 查询user表信息
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string
| password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N
| N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2023-07-21 10:10:45 | NULL | Y
| N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N
| Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2023-07-21 10:10:43 | NULL | Y
| N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N
| N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2023-07-21 10:10:46 | NULL | Y
| N | N | NULL | NULL | NULL | NULL |
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$`KDKOx<wS
HBR0?@iLazmZ2gS1YwIs3GUYn4MDSFWrelhohlfmD2UefatlrA | N | 2023-07-21 10:11:09 | NULL | N | Y | Y | NULL | NULL | NULL
| NULL |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
4 rows in set (0.05 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; # 修改'root'@'localhost'用户的密码规则和密码
Query OK, 0 rows affected (0.35 sec)
mysql> select * from user; # 查询user表信息
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string
| password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N
| N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2023-07-21 10:10:45 | NULL | Y
| N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N
| Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2023-07-21 10:10:43 | NULL | Y
| N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N
| N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2023-07-21 10:10:46 | NULL | Y
| N | N | NULL | NULL | NULL | NULL |
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | 2023-08-09 11:03:40 | NULL | N
| Y | Y | NULL | NULL | NULL | NULL |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
4 rows in set (0.00 sec)
十八、刷新权限：FLUSH PRIVILEGES;
mysql> FLUSH PRIVILEGES; # 刷新权限
Query OK, 0 rows affected (0.09 sec)
十九、修改表信息（编码格式）：alter table 表名 charset utf8;
mysql> use dbtest1; # 使用数据库dbtest1
Database changed
mysql> create table teachers(id int, name varchar(15)) charset Latin1; # 创建表teachers，编码格式为Latin1
Query OK, 0 rows affected (0.60 sec)
mysql> show tables; # 查看所有表
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| employees |
| students |
| teachers |
+-------------------+
3 rows in set (0.06 sec)
mysql> show create table teachers; # 查看创建表teachers的语句
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| teachers | CREATE TABLE `teachers` (
`id` int DEFAULT NULL,
`name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table teachers charset utf8; # 修改表teachers的编码格式为utf8
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show create table teachers; # 查看创建表teachers的语句
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| teachers | CREATE TABLE `teachers` (
`id` int DEFAULT NULL,
`name` varchar(15) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table teachers\G # 查看创建表teachers的语句
*************************** 1. row ***************************
Table: teachers
Create Table: CREATE TABLE `teachers` (
`id` int DEFAULT NULL,
`name` varchar(15) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
mysql> show create table teachers\g # 查看创建表teachers的语句
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| teachers | CREATE TABLE `teachers` (
`id` int DEFAULT NULL,
`name` varchar(15) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
十七、删除数据库：drop database if exists 数据库名;
mysql> show databases; # 查看所有的数据库
+--------------------+
| Database |
+--------------------+
| atguigu |
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.03 sec)
mysql> drop database if exists atguigu; # 删除数据库atguigu
Query OK, 0 rows affected (0.14 sec)
mysql> show databases; # 查看所有的数据库
+--------------------+
| Database |
+--------------------+
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
十八、导入现有的数据表、表的数据：source .sql文件完整路径;
mysql> show databases; # 查看所有的数据库
+--------------------+
| Database |
+--------------------+
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> source D:\database\atguigudb.sql # 导入D:\database\atguigudb.sql文件中的数据表、表的数据
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected, 1 warning (0.08 sec)
Database changed
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected, 2 warnings (1.17 sec)
Query OK, 25 rows affected (0.20 sec)
Records: 25 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected, 4 warnings (0.63 sec)
Query OK, 27 rows affected (0.06 sec)
Records: 27 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.13 sec)
Query OK, 0 rows affected, 6 warnings (0.90 sec)
Query OK, 107 rows affected (0.09 sec)
Records: 107 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected, 3 warnings (0.74 sec)
Query OK, 6 rows affected (0.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected, 3 warnings (0.66 sec)
Query OK, 10 rows affected (0.12 sec)
Records: 10 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected, 3 warnings (0.73 sec)
Query OK, 19 rows affected (0.25 sec)
Records: 19 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.10 sec)
Query OK, 0 rows affected, 2 warnings (0.74 sec)
Query OK, 23 rows affected (0.15 sec)
Records: 23 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected, 2 warnings (0.27 sec)
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected, 2 warnings (0.66 sec)
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected, 6 warnings (0.56 sec)
Query OK, 0 rows affected (0.43 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.15 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show databases; # 查看所有的数据库
+--------------------+
| Database |
+--------------------+
| atguigudb |
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.03 sec)
mysql> use atguigudb; # 使用atguigudb数据库
Database changed
mysql> show tables; # 查看所有的表
+---------------------+
| Tables_in_atguigudb |
+---------------------+
| countries |
| departments |
| emp_details_view |
| employees |
| job_grades |
| job_history |
| jobs |
| locations |
| order |
| regions |
+---------------------+
10 rows in set (0.02 sec)
mysql>
十九、备份数据库：mysqldump -h 数据库服务器IP -u 用户名 -p密码 数据库名 >D:\bakdbtest1.sql
C:\Users\song>mysqldump -h localhost -u root -p123456 dbtest1 >D:\bakdbtest1.sql # 备份 dbtest1 数据库 到 D盘的 bakdbtest1.sql 文件
mysqldump: [Warning] Using a password on the command line interface can be insecure.
C:\Users\song>
二十、显示表结构：DESCRIBE 表名或者DESC 表名
mysql> show tables; # 查看有哪些表
+---------------------+
| Tables_in_atguigudb |
+---------------------+
| countries |
| departments |
| emp_details_view |
| employees |
| job_grades |
| job_history |
| jobs |
| locations |
| order |
| regions |
+---------------------+
10 rows in set (0.00 sec)
mysql> DESCRIBE employees; # 显示了表中字段的详细信息
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int | YES | MUL | NULL | |
| department_id | int | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
mysql> DESC employees; # 显示了表中字段的详细信息
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int | YES | MUL | NULL | |
| department_id | int | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
mysql>
二十一、MySQL信息函数：VERSION()、CONNECTION_ID()、DATABASE()、SCHEMA()、USER()、CURRENT_USER()、CHARSET('字符串')、COLLATION('字符串')
mysql> use atguigudb;
Database changed
mysql> SELECT VERSION(), CONNECTION_ID(), DATABASE(), SCHEMA(), USER(), CURRENT_USER(), CHARSET('尚硅谷'), COLLATION('尚硅谷') FROM DUAL;
+-----------+-----------------+------------+-----------+----------------+----------------+-------------------+---------------------+
| VERSION() | CONNECTION_ID() | DATABASE() | SCHEMA() | USER() | CURRENT_USER() | CHARSET('尚硅谷') | COLLATION('尚硅谷') |
+-----------+-----------------+------------+-----------+----------------+----------------+-------------------+---------------------+
| 8.0.26 | 27 | atguigudb | atguigudb | root@localhost | root@localhost | gbk | gbk_chinese_ci |
+-----------+-----------------+------------+-----------+----------------+----------------+-------------------+---------------------+
1 row in set (0.00 sec)
mysql>
95
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
