MySQL的应用

linux mysql基础

3. mysql的程序组成

客户端

• mysql：CLI交互式客户端程序
• mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令

[root@localhost ~]# mysql_secure_installation
密码强度  是否修改root密码？
Estimated strength of the password: 50 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
是否安装匿名用户？
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
是否禁止远程登录？
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
是否删除测试数据库？
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
是否现在重新加载特权表？
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

• mysqldump：mysql备份工具
• mysqladmin
• 服务器端
• mysqld

3.1 mysql工具使用

//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本

[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper

[root@localhost ~]# mysql -uroot -pyyds123!
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 13
Server version: 5.7.38 MySQL Community Server (GPL)

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> 

//注意，不推荐直接在命令行里直接用-pPASSWORD的方式登录，而是使用-p选项，然后交互式输入密码

[root@localhost ~]# mysql -uroot -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.38 MySQL Community Server (GPL)

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> exit

[root@localhost ~]# mysql -uroot -pyyds123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3.2 服务器监听的两种socket地址

socket类型	说明
ip socket	默认监听在tcp的3306端口，支持远程通信
unix sock	监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）仅支持本地通信 server地址只能是：localhost，127.0.0.1

[root@localhost ~]# mysql -uroot -pyyds123! -S /var/lib/mysql/mysql.sock
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 9
Server version: 5.7.38 MySQL Community Server (GPL)

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> exit

4. mysql数据库操作

4.1 DDL操作

4.1.1 数据库操作

//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] ‘DB_NAME’;
大小写均可执行
//创建数据库

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

//查看当前实例有哪些数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yyds               |
+--------------------+
5 rows in set (0.00 sec)

//删除数据库
//语法：DROP DATABASE [IF EXISTS] ‘DB_NAME’;
//删除数据库

mysql> drop database yyds;
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)

4.1.2 表操作

//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE=‘存储引擎类型’;
//在数据库yyds里创建表yyds

mysql> CREATE DATABASE yyds;   //创建数据库yyds
Query OK, 1 row affected (0.00 sec)

mysql> use yyds;      //进入yyds数据库
Database changed
mysql> CREATE TABLE yyds (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint(3));      //创建yyds表
Query OK, 0 rows affected (0.01 sec)

//查看当前数据库有哪些表

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

//删除表
//语法：DROP TABLE [ IF EXISTS ] ‘table_name’;
//删除表yyds

mysql> drop table yyds;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

int(大整数值)

date(日期值xx-xx-xx )
datetime（混合日期和时间值 xx-xx-xx xx:xx:xx）

varchar （变长字符串）

4.1.3 用户操作

mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录

这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

• IP地址，如：172.16.12.129
• 通配符
• %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
• _：匹配任意单个字符

查看命令show

//数据库用户创建
//语法：CREATE USER ‘username’@‘host’ [IDENTIFIED BY ‘password’];
//创建数据库用户yyds

mysql> CREATE USER 'yyds'@'127.0.0.1' IDENTIFIED BY 'yyds123!';
Query OK, 0 rows affected (0.00 sec)

//使用新创建的用户和密码登录

[root@localhost ~]# mysql -uyyds -pyyds123! -h127.0.0.1
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 6
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> 

//删除数据库用户
//语法：DROP USER ‘username’@‘host’;

mysql> DROP USER 'yyds'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

4.1.4 查看命令SHOW

1.mysql> SHOW CHARACTER SET; //查看支持的所有字符集

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS