MySQL基础

MySQL基础

1、关系型数据库介绍

1.1 数据结构模型

• 数据结构模型：
  ·层次模型
  ·网状模型
  ·关系模型：二维关系：row，column
• 数据库管理系统：DBMS
• 关系：Relational，ROBMS

1.2 关系型数据库的常见组件

• 数据库：database
• 表：table，由行（row）和列（column）组成
• 索引：index
• 视图：view
• 用户：user
• 权限：privilege
• 储存过程：procedure
• 储存函数：function
• 触发器：trigger
• 事件调度器：event scheduler

1.3 SQL语句

SQL语句有三种类型：

• DDL：Data Defination Language，数据定义语言
• DML：Data Manipulation Language，数据操纵语言
• DCL：Data Control Language，数据控制语言

SQL 语句类型	对应操作
DDL	CREATE：创建 DROP：删除 ALTER：修改
DML	INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据
DCL	GRANT：授权 REVOKE：移除授权

2、MySQL安装与配置

2.1 MySQL安装

mysql安装方式有三种：

• 源代码：编译安装

• 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用

• 程序包管理器管理的程序包：
  ·rpm：有两种

  1. OS Vendor：操作系统发行商提供的
  2. 项目官方提供的

  ·deb

2.1 MySQL语法

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

2.2 MySQL 安装操作

[root@localhost ~]# cd  /etc/yum.repos.d/
**//本地仓库中不要有其他源（有的话请移走）**
[root@localhost yum.repos.d]# ls

[root@localhost yum.repos.d]# rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
……下载过程略
[root@localhost yum.repos.d]# ls
mysql-community.repo  mysql-community-source.repo
**//过滤MySQL安装包**
[root@localhost yum.repos.d]# yum  list all |grep  mysql
**//只需下载以下安装包即可**
[root@localhost yum.repos.d]# yum  -y  install  mysql-community-server.x86_64  mysql-community-client.x86_64  
mysql-community-common.x86_64  mysql-community-devel.x86_64
……下载过程略

**//启动MySQL服务**
[root@localhost ~]# systemctl   start  mysqld
[root@localhost ~]# ss  -antl   **//MySQL服务端口为3306**
State      Recv-Q Send-Q              Local Address:Port                             Peer Address:Port              
LISTEN     0      128                             *:22                                          *:*                  
LISTEN     0      100                     127.0.0.1:25                                          *:*                  
LISTEN     0      32                             :::21                                         :::*                  
LISTEN     0      128                            :::22                                         :::*                  
LISTEN     0      100                           ::1:25                                         :::*                  
LISTEN     0      80                             :::3306                                       :::*                  

2.3 MySQL 配置操作

**//修改mysql登陆密码**
**//首先在日志文件中找出临时密码**
[root@localhost ~]# grep "password" /var/log/mysqld.log
2020-04-03T10:29:08.240430Z 1 [Note] A temporary password is generated for root@localhost: auGLpj4R4Z-I
**//临时密码为（每个人的临时密码都不同，请看清楚。）**：auGLpj4R4Z-I

**//使用临时密码登陆mysql**
[root@localhost ~]# mysql  -uroot  -p
Enter password:          **//此处直接复制临时密码即可**
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 2
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>          **//出现此标识就说明登陆成功**  

**//修改MySQL登陆密码（两种方式）**
**方式一：**
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345';
Query OK, 0 rows affected (0.01 sec)

mysql> quit                 **//修改成功后退出**

[root@localhost ~]# mysql  -uroot  -p12345     **//密码可直接写在-p后**
.......
mysql>          **//修改密码后登陆成功**   

**方式二：**
[root@localhost ~]# mysql_secure_installation
Enter password for user root:               **//此处直接复制临时密码或已有的密码**
Change the password for root ? ((Press y|Y for Yes, any other key for No) : yes    //此处回答yes，修改根密码
New password:                   **//输入新密码**
Re-enter new password:      **//再次输入新密码**
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : yes    **//继续使用提供的密码yes**
Remove anonymous users? (Press y|Y for Yes, any other key for No) : yes          **//删除匿名用户yes**
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : yes         **//不允许root用户登录**
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : yes         **//删除测试数据库并访问它**
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : yes     **//立即重新加载特权表**
Success.

All done!                **//修改成功**
[root@localhost ~]# mysql  -uroot  -p12345     **//密码可直接写在-p后**
.......
mysql>          **//修改密码后登陆成功** 

**//为避免mysql自动升级，这里需要卸载最开始安装的yum源**
**//如果担心以后还会用，可先备份一份。**
[root@100 ~]# rpm -qa|grep mysql
mysql57-community-release-el7-10.noarch
mysql-community-client-5.7.26-1.el7.x86_64
mysql-community-libs-5.7.26-1.el7.x86_64
mysql-community-server-5.7.26-1.el7.x86_64
mysql-community-devel-5.7.26-1.el7.x86_64
mysql-community-common-5.7.26-1.el7.x86_64
mysql-community-libs-compat-5.7.26-1.el7.x86_64
[root@100 ~]# yum -y remove mysql57-community-release-el7-10.noarch     //卸载主包

3、MySQL数据库操作

3.1 DDL 操作

3.1.1 数据库操作

**// 创建数据库lol**
mysql> create database lol;
Query OK, 1 row affected (0.00 sec)

**// 查看当前有哪些数据库**
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| lol                |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

**// 删除数据库lol**
mysql> drop database lol;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

3.1.2 表操作

**// 查看有哪些数据库**
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| lol                |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

**// 进入lol数据库**
mysql> use whell          
Database changed

**// 在数据库lol里创建lpl表**
mysql> create table lpl (id int not null,name varchar(50) not null,age tinyint);        
Query OK, 0 rows affected (0.02 sec

**// 查看当前数据库有哪些表**
mysql> show tables;
+-----------------+
| Tables_in_whell |
+-----------------+
| lpl             |
+-----------------+
1 row in set (0.00 sec)

**// 查看表结构**
mysql> desc lpl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

**// 删除表lpl**
mysql> drop table lpl;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
Empty set (0.00 sec)

3.1.3 用户操作

• mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
• 这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
  ·IP地址
  ·通配符：
  1. %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
  2. _ ：匹配任意单个字符

**// 创建数据库用户scl**
mysql> create user 'spider'@'localhost' identified by '12345';
Query OK, 0 rows affected (0.01 sec)

**// 使用新创建的用户和密码登录**
[root@localhost ~]# mysql -uspider -p12345
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 14
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>           

**// 删除数据库用户** 
mysql> drop user 'spider'@'localhost';       **//需要在root身份下**
Query OK, 0 rows affected (0.00 sec)

3.1.4 查看命令 show

**//查看支持的所有字符集**
mysql> show character set;     
.........
**//查看当前数据库支持的所有存储引擎**
mysql> show engines;         
.........
**//查看数据库信息**
mysql> show databases;        
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| lol                |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

**// 不进入某数据库而列出其包含的所有表**
mysql> use mysql;    **//退出当前数据库**
mysql> show tables from lol;
+-----------------+
| Tables_in_whell |
+-----------------+
| lpl             |
+-----------------+
1 row in set (0.00 sec)

**// 查看某表的创建命令**
mysql> show create table lol.lpl;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                           |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| lpl | CREATE TABLE `lpl` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

**// 查看某表的状态**
mysql> use lol        
Database changed
**// 查看lpl这个表的状态**
mysql> show table status like 'lpl'\G      
*************************** 1. row ***************************
           Name: lpl
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-07-05 23:43:35
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

**// 获取命令使用帮助**
**//获取创建表的帮助**
mysql> help create table;          
……过程略

3.2 DML 操作

• DML操作包括增(insert)、删(delete)、改(update)、查(select)，均属针对表的操作。

3.2.1 insert 语句

**//进入lol数据库**
mysql> use lol;         
Database changed

**//插入信息**
**//value：一次插入一条信息；values：一次插入多条信息；**
mysql> insert  into lpl (id,name,age) values (1,'tom',20);          
Query OK, 1 row affected (0.01 sec)

mysql> insert  into lpl (id,name,age) values (2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'list',20); 
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

**// 查看插入的表信息**
mysql> select * from lpl;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | list        |   20 |
+----+-------------+------+
6 rows in set (0.06 sec)

3.2.2 select 语句

• 字段column表示法

表示符	代表
*	所有字段
as	字段别名，如col1 AS alias1当表名很长时用别名代替

• 判断语句 where

操作类型	常用操作符
操作符	>，<，>=，<=，=，!= between column# and column# like：模糊匹配 rlike：基于正则表达式进行模式匹配 is not null：非空 is null：空
条件逻辑操作	and or not

• ORDER BY：排序，默认为升序

order by 语句	意义
order by ‘column_name’	根据column_name进行升序排序
order by ‘column_name’ desc	根据column_name进行降序排序
order by ’column_name’ limit 2	根据column_name进行升序排序，并只取前2个结果
order by ‘column_name’ limit 1,2	根据column_name进行升序排序并且略过第1个结果取后面的2个结果

**//进入lol数据库**
mysql> use lol;         
Database changed

**// 查看lpl这个表中的所有信息**
mysql> select * from lpl;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | list        |   20 |
+----+-------------+------+
6 rows in set (0.06 sec)

**// 只看name这个字段**
mysql> select name from lpl;       
+--------------+ 
| name         |
+--------------+
| tom         | 
| jerry       |  
| wangqing    | 
| sean        |   
| zhangshan   |  
| list        |
+--------------+
6 rows in set (0.00 sec)

**// as别名**
**// 将name用别名n代替**
mysql> select name as n,age from lpl;   
+--------------+------+
| n            | age  |
+--------------+------+
| tom         |   20 |
| jerry       |   23 |
| wangqing    |   25 |
| sean        |   28 |
| zhangshan   |   26 |
| list        |   20 |
+--------------+------+
6 rows in set (0.00 sec)

**// 只看age大于等于30的**
mysql> select * from lpl where age >=25;  
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
+----+-------------+------+
3 rows in set (0.06 sec)

**// 找age在10到30之间的**
mysql> select * from lpl where age between 25 and 30; 
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
+----+-------------+------+
3 rows in set (0.06 sec)

**// order升序排序**
**// 按id字段升序排序**
mysql> select * from lpl order by id;   
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | list        |   20 |
+----+-------------+------+
6 rows in set (0.06 sec)

**// 倒序排序**
**// 按id字段倒序排序**
mysql> select * from lpl order by id desc;  
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  6 | list        |   20 |
|  5 | zhangshan   |   26 |
|  4 | sean        |   28 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
+----+-------------+------+
6 rows in set (0.06 sec)

**// 根据id升序排序取出前两个**
mysql> select * from lpl order by id limit 2;  
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
2 rows in set (0.00 sec)

**// 根据id进行升序排序,并且略过第1个结果取后面的2个结果**
mysql> select * from lpl order by id limit 1,2;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
+----+-----------+------+
2 rows in set (0.00 sec)

**// and用法**
mysql> select * from lpl where age=28 and name='sean';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  4 | sean      |   28 |
+----+-----------+------+
1 row in set (0.00 sec)

**// or用法**
mysql> select * from lpl where age=28 or name='wangqing';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  3 | wangqing  |  25  |
|  4 | sean      |  28  |
+----+-----------+------+
2 rows in set (0.00 sec)

**// not用法**
mysql> select * from lpl where age is not null;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | list        |   20 |
+----+-------------+------+
6 rows in set (0.06 sec)

3.2.3 update 语句

**// 把name=sean的用户的age改为50**
mysql> update lpl set age = 50 where name = 'sean';     
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from lpl;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   50 |    **//此处age改为50**
|  5 | zhangshan   |   26 |
|  6 | list        |   20 |
+----+-------------+------+
6 rows in set (0.06 sec))

3.2.4 delete 语句

**// 删除表中id=5的一条内容**
mysql> delete from lpl where id=5;     
Query OK, 1 row affected (0.01 sec)

mysql> select * from lpl;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  6 | list        |   20 |
+----+-------------+------+
5 rows in set (0.06 sec)

**// 删除整张表的内容**
mysql> delete from lpl;        
mysql> select * from lpl; 
Empty set (0.00 sec)

3.2.4.1 truncate 语句

• truncate与delete的区别

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据 非常占用空间
truncate	删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表

mysql> select * from lpl;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  6 | list        |   20 |
+----+-------------+------+
5 rows in set (0.06 sec)

**// 删除lpl这张表**
mysql> truncate lpl;                
Query OK, 0 rows affected (0.07 sec)

mysql> select * from lpl; 
Empty set (0.00 sec)               

**// 查看表结构，表结构还在**
mysql> desc lpl;        
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.3 DCL 操作

3.3.1 创建授权 grant

• 权限类型

权限类型	代表
all	所有权限
select	读取内容权限
update	更新内容权限
insert	插入内容权限
delete	删除内容权限

• 指定要操作的对象db_name.table_name

表示方式	意义
.	所有库的所有表
db_name	指定库的所有表
db_name.table_name	指定库的指定表

**// 授权spider用户在数据库本机上登录访问所有数据库**
mysql> grant all on *.* to 'spider'@'localhost' identified by '12345';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all on *.* to 'spider'@'127.0.0.1' identified by '12345';
Query OK, 0 rows affected, 1 warning (0.00 sec)

**// 授权spider用户在192.168.206.130上远程登录访问lol数据库**
mysql> grant all on lol.* to 'spider'@'192.168.206.130' identified by '12345';
Query OK, 0 rows affected, 1 warning (0.00 sec)

**// 授权spider用户在所有位置上远程登录访问whell数据库**
mysql> grant all on *.* to 'spider'@'%' identified by '12345';
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.3.2 查看授权

**// 查看当前登录用户的授权信息**
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

**// 查看指定用户scl的授权信息**
mysql> show grants for spider;
+-----------------------------------------------+
| Grants for spider@%                         |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'spider'@'%' |
+-----------------------------------------------+
1 row in set (0.00 sec)

3.3.3 取消授权revoke

**//取消授权**
mysql> revoke all on *.* from 'spider'@'192.168.206.130';
Query OK, 0 rows affected (0.00 sec)

**// 刷新权限**
mysql> flush privileges;