MySQL基础教程

安装后修改密码

MySQL连接

使用mysql -u root -p 连接，-u指定用户root,-p 密码选项,如果设置了密码需要加-p选项
连接方式一

[root ~]#mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.34 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>

连接方式二

[root ~]#mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.34 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>

连接方式一种会在历史记录中留下mysql密码，更安全的方式是使用方式二。

确认mysql中字符编码

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

Connection id:      8
Current database:
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.34 MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:         1 day 27 min 38 sec

Threads: 1  Questions: 17  Slow queries: 0  Opens: 113  Flush tables: 1  Open tables: 106  Queries per second avg: 0.000
--------------

可以看到客户端和服务端字符编码都是utf8.也可以通过SHOW VARIABLES LIKE 'char%';来确认

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

创建数据库

CREATE DATABASE 数据库名

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

查询数据库

SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
+--------------------+
2 rows in set (0.00 sec)

指定数据库

mysql> use db1
Database changed

显示当前数据库

SELECT DATABASE();

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

创建表

CREATE TABLE 表名 (列名1 数据类型1，列名2 数据类型2 ……）

mysql> CREATE TABLE tb1 (empid VARCHAR(10),name VARCHAR(10),age INT);
Query OK, 0 rows affected (0.53 sec)

显示所有的表

mysql> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1           |
+---------------+
1 row in set (0.00 sec)

指定字符编码创建表

mysql> CREATE TABLE tb2 (empid VARCHAR(10),name VARCHAR(10),age INT) CHARSET=utf8;
Query OK, 0 rows affected (0.55 sec)

当前由于默认字符编码是utf8，所以创建表不需要按照上面的设置来创建表。

https://dy.wmyun.men/link/TbCrfF1NzBHR6bT7?mu=2

52coder