小驴不开心要学java -- MySQL入门篇

mysql -hip地址 -u用户名 -p密码 -p端口号
    ip地址：MySQL服务器所在的电脑的地址 如果就是一台电脑上 localhost 127.0.0.1
    用户名：当前需要登录的账号的名字 默认 root
    密码：安装MySQL的时候，确定的密码，或者是 别人给定的密码
    端口号：MySQL开放的端口号，在安装的时候可以确定 默认：3306

可以省略的部分：
   1.如果MySQL就装在当前电脑上，-h可以直接不写

mysql -u用户名 -p密码 -P端口号

    2.如果端口号没有修改，-P可以直接不写

mysq1 -hip地址 -u用户名 -p密码

    3.如果不希望直接书写明文密码，-p后面可以不写任何字符，直接回车

mysql -hip地址 -u用户名 -p -P端口号

最简单的登录命令：

mysql -u用户名 -p

退出命令：
1.exit 
2.quit 
3.\q

查看数据库：

注意：*需要语句末尾添加分号，如果不添加会识别不了*

展示当前所有可用的数据库：

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mdb                |
| mysql              |
| mysqldb            |
| performance_schema |
| sys                |
| webbd              |
+--------------------+
7 rows in set (0.00 sec)

使用数据库：

use 数据库名称：

mysql> use mysqldb;
Database changed

查看数据表：

show tables;

mysql> show tables;
+-------------------+
| Tables_in_mysqldb |
+-------------------+
| dept              |
| emp               |
| salgrade          |
+-------------------+
3 rows in set (0.00 sec)

查看数据库服务的版本

select version();

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

查看数据库默认的存储引擎(了解即可 不重要)

show engines;

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

查看当前数据库支持的字符集

show variables like '%char%';

mysql> show variables like '%char%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | gbk                               |
| character_set_connection | gbk                               |
| character_set_database   | utf8mb3                           |
| character_set_filesystem | binary                            |
| character_set_results    | gbk                               |
| character_set_server     | utf8mb4                           |
| character_set_system     | utf8mb3                           |
| character_sets_dir       | E:\mysql\MySQL8.0\share\charsets\ |
+--------------------------+-----------------------------------+
8 rows in set, 6 warnings (0.01 sec)

设置字符集

set character_set_client = utf8;

mysql> set character_set_client = utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

一次性设置client、connection、result的字符集(效果同上)

set names utf8;

-----------------------------------------------------------------------------------------------------------------------

向MySQL中导入SQL脚本文件（很重要的脚本导入方法）

先将脚本文件放置在指定文件夹下，再进行导入

source 脚本位置

mysql> source f:\init1.sql;
ERROR:
Unknown command '\i'.   // 这里的error不用担心 是识别文件的问题 脚本已经全部导入
                               使用‘\’会报错，使用‘/’就不会报错了
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.00 sec)

Database changed
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 6 warnings (0.02 sec)

Query OK, 14 rows affected (0.00 sec)
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 4 warnings (0.01 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

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入门篇到此结束，小驴继续更新做学习笔记