Linux服务管理-mysql基础-CSDN博客

本文链接：https://blog.csdn.net/Mzh_CSDN/article/details/106239216

mysql基础

文章目录

mysql基础

1. 关系型数据库介绍

1.1 数据结构模型

数据结构模型主要有：

层次模型
网状结构
关系模型

关系模型：
二维关系：row，column

数据库管理系统：DBMS
关系：Relational，RDBMS

1.2 RDBMS专业名词

SQL：Structure Query Language，结构化查询语言

SQL 约束：constraint，用于限制加入表的数据的类型。

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

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

关系型数据库的常见组件有：

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

1.4 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安装与配置

由于MySQL和MariaDB是同一个开发团队制作的软件，所以操作方式差不多，而且MariaDB是免费的，这里就用MariaDB作为演示。

2.1 mariadb安装

[root@localhost ~]# yum -y install mariadb*

2.2 mariadb配置

//设置mariadb开机自动启动
[root@localhost ~]# systemctl enable --now mariadb

//确保3306端口已经监听起来
[root@localhost ~]# ss -antl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      100    127.0.0.1:25                        *:*                  
LISTEN      0      50            *:3306                      *:*                  
LISTEN      0      128           *:22                        *:*                  
LISTEN      0      100       [::1]:25                     [::]:*                  
LISTEN      0      128        [::]:22                     [::]:*     

//登录mariadb
[root@localhost ~]# mysql   
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>         //看到有这样的标识符则表示成功登录了

//设置mariadb登录密码
MariaDB [(none)]> set password = password('12321');
Query OK, 0 rows affected (0.00 sec)

//修改密码后登录
[root@localhost ~]# mysql -uroot -p12321
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

3. mysql的程序组成

客户端
- mysql：CLI交互式客户端程序
- mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
- 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 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1

//指定服务器主机（必须先授权才能连接）
//如：客户机IP：192.168.118.133  服务器IP：192.168.118.134
//服务器操作：
[root@localhost ~]# mysql -uroot -p12321
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all on *.* to 'root'@192.168.118.133 identified by '12321';Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//现在客户机可以连接服务器了：
[root@localhost ~]# mysql -uroot -p12321 -h192.168.118.134
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

//使用交互式输入密码
[root@localhost ~]# mysql -u root -p
Enter password:    //输入密码
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

//不登录mysql执行sql语句
[root@localhost ~]# mysql -uroot -p12321 -e'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

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

ip socket：默认监听在tcp的3306端口，支持远程通信

unix sock

监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）
仅支持本地通信

//通过sock文件连接
[root@localhost ~]# mysql -uroot -p12321 -S /var/lib/mysql/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

server地址只能是：localhost，127.0.0.1

4. mysql数据库操作

4.1 DDL操作

4.1.1 数据库操作

//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库school
MariaDB [(none)]> create database if not exists school;
Query OK, 1 row affected (0.00 sec)

//查看当前实例有哪些数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库school
MariaDB [(none)]> drop database if exists school;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

4.1.2 表操作

//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//在数据库school里创建表20200520
MariaDB [(none)]> create database school;     //创建数据库school
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use school;            //进入school数据库
Database changed
MariaDB [school]> create table Linux0520(id int not null,name varchar(50) not null,age tinyint not null);
Query OK, 0 rows affected (0.01 sec)

//查看当前数据库有哪些表
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| Linux0520        |
+------------------+
1 row in set (0.00 sec)

//删除表
//语法：DROP TABLE [ IF EXISTS ] 'table_name';
//删除表school
MariaDB [school]> drop table Linux0520;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> show tables;
Empty set (0.00 sec)

4.1.3 用户操作

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

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

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

//数据库用户创建
//语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//创建数据库用户tom
MariaDB [(none)]> create user 'tom'@'127.0.0.1' identified by '12321';
Query OK, 0 rows affected (0.05 sec)

//使用新创建的用户和密码登录
[root@localhost ~]# mysql -utom -p12321 -h127.0.0.1 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

//删除数据库用户
//语法：DROP USER 'username'@'host'; 
MariaDB [(none)]> drop user 'tom'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

4.1.4 查看命令SHOW

查看支持的所有字符集

MariaDB [(none)]> 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 West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
......
......

查看当前数据库支持的所有存储引擎

//第一种方法
MariaDB [(none)]> show engines;        
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys       | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
......
......

//第二种方法
MariaDB [(none)]> show engines\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
......
......

查看数据库信息

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
5 rows in set (0.07 sec)

不进入某数据库而列出其包含的所有表

MariaDB [(none)]> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| Linux0520        |
+------------------+
1 row in set (0.00 sec)

查看某表的创建命令

MariaDB [(none)]> show create table school.Linux0520;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                          |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Linux0520 | CREATE TABLE `Linux0520` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看某表的状态

MariaDB [school]> use school;         //进入数据库school
Database changed
MariaDB [school]> show table status like 'Linux0520'\G
*************************** 1. row ***************************
           Name: Linux0520
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 10485760
 Auto_increment: NULL
    Create_time: 2020-05-21 02:17:42
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

查看表结构

MariaDB [(none)]> desc school.Linux0520;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

4.1.5 获取帮助

//获取命令使用帮助
//语法：HELP keyword;
MariaDB [(none)]> help create table;     //获取创建表的帮助
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement
......
......

4.2 DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

4.2.1 INSERT语句

//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...

一次插入一条记录

MariaDB [(none)]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [school]> insert into Linux0520 (id,name,age) value (1,'tom',20);
Query OK, 1 row affected (0.00 sec)

一次插入多条记录

MariaDB [school]> insert into Linux0520 (id,name,age) values (2,'jerry',23),(3,'jack',18),(4,'zhangsan',25),(5,'lisi',26),(6,'wangwu',NULL);
Query OK, 5 rows affected, 1 warning (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 1

4.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：排序，默认为升序（ASC）

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个结果

//DML操作之查操作select
//语法：SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

查询表内所有内容

MariaDB [school]> select * from Linux0520
    -> ;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | tom      |  20 |
|  2 | jerry    |  23 |
|  3 | jack     |  18 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  26 |
|  6 | wangwu   |   0 |
+----+----------+-----+
6 rows in set (0.00 sec)

将id临时别名为学生编号

MariaDB [school]> select id as 学生编号,name from Linux0520;
+--------------+----------+
| 学生编号     | name     |
+--------------+----------+
|            1 | tom      |
|            2 | jerry    |
|            3 | jack     |
|            4 | zhangsan |
|            5 | lisi     |
|            6 | wangwu   |
+--------------+----------+
6 rows in set (0.00 sec)

按年龄大小排序

MariaDB [school]> select * from Linux0520 where age is not null order by age;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  6 | wangwu   |   0 |
|  3 | jack     |  18 |
|  1 | tom      |  20 |
|  2 | jerry    |  23 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  26 |
+----+----------+-----+
6 rows in set (0.00 sec)

按年龄大小倒序排序

MariaDB [school]> select * from Linux0520 order by age desc;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  5 | lisi     |  26 |
|  4 | zhangsan |  25 |
|  2 | jerry    |  23 |
|  1 | tom      |  20 |
|  3 | jack     |  18 |
|  6 | wangwu   |   0 |
+----+----------+-----+
6 rows in set (0.00 sec)

按年龄大小排序并只取前2个结果

MariaDB [school]> select * from Linux0520 order by age limit 2;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  6 | wangwu |   0 |
|  3 | jack   |  18 |
+----+--------+-----+
2 rows in set (0.00 sec)

按年龄排序并跳过1位取2位

MariaDB [school]> select * from Linux0520 order by age limit 1,2;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  3 | jack |  18 |
|  1 | tom  |  20 |
+----+------+-----+
2 rows in set (0.00 sec)

查询年龄最小的

MariaDB [school]> select * from Linux0520 order by age limit 1;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  6 | wangwu |   0 |
+----+--------+-----+
1 row in set (0.00 sec)

查询年龄最大的

MariaDB [school]> select * from Linux0520 order by age desc limit 1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  5 | lisi |  26 |
+----+------+-----+
1 row in set (0.00 sec)

查询年龄大于20岁的

MariaDB [school]> select * from Linux0520 where age > 20;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  2 | jerry    |  23 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  26 |
+----+----------+-----+
3 rows in set (0.01 sec)

查询年龄大于20岁并且名字叫zahngsan的

MariaDB [school]> select * from Linux0520 where age > 20 and name = 'zhangsan';
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  4 | zhangsan |  25 |
+----+----------+-----+
1 row in set (0.00 sec)

查询年龄在23到26的

MariaDB [school]> select * from Linux0520 where age between 23 and 26;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  2 | jerry    |  23 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  26 |
+----+----------+-----+
3 rows in set (0.00 sec)

查询年龄不是空的

MariaDB [school]> select * from Linux0520 where age is not null;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | tom      |  20 |
|  2 | jerry    |  23 |
|  3 | jack     |  18 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  26 |
|  6 | wangwu   |   0 |
+----+----------+-----+
6 rows in set (0.00 sec)

查询年龄是23和名字是lisi的

MariaDB [school]> select * from Linux0520 where age = 23 or name = 'lisi';
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  2 | jerry |  23 |
|  5 | lisi  |  26 |
+----+-------+-----+
2 rows in set (0.00 sec)

模糊匹配年龄是2开头的

MariaDB [school]> select * from Linux0520 where age like '2%';
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | tom      |  20 |
|  2 | jerry    |  23 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  26 |
+----+----------+-----+
4 rows in set (0.01 sec)

基于正则表达式进行模式匹配

MariaDB [school]> select * from Linux0520 where age rlike '1[0-9]';     //查询年龄是1开头的
+----+------+-----+
| id | name | age |
+----+------+-----+
|  3 | jack |  18 |
+----+------+-----+
1 row in set (0.00 sec)

MariaDB [school]> select * from Linux0520 where name rlike '^[a-z]{3}$';    //查名字是3个字母的
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |  20 |
+----+------+-----+
1 row in set (0.00 sec)

GROUP BY：分组，用于结合聚合函数，根据一个或多个列对结果集进行分组

//语法：SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

//表websites的数据
MariaDB [RUNOOB]> use RUNOOB;
Database changed

MariaDB [RUNOOB]> select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
|  1 | Google       | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝         | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟教程     | http://www.runoob.com/    |  4689 | CN      |
|  4 | 微博         | http://weibo.com/         |    20 | CN      |
|  5 | Facebook     | https://www.facebook.com/ |     3 | USA     |
+----+--------------+---------------------------+-------+---------+
5 rows in set (0.00 sec)

//表access_log的数据
MariaDB [RUNOOB]> select * from access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

统计 access_log 各个 site_id 的访问量

MariaDB [RUNOOB]> select site_id, sum(access_log.count) as num from access_log group by site_id;
+---------+------+
| site_id | num  |
+---------+------+
|       1 |  275 |
|       2 |   10 |
|       3 |  521 |
|       4 |   13 |
|       5 |  750 |
+---------+------+
5 rows in set (0.00 sec)

统计有记录的网站的记录数量（多表连接）

MariaDB [RUNOOB]> select websites.name,count(access_log.aid) as nums from access_log left join websites on access_log.site_id=websites.id group by websites.name;
+--------------+------+
| name         | nums |
+--------------+------+
| Facebook     |    2 |
| Google       |    2 |
| 微博         |    1 |
| 淘宝         |    1 |
| 菜鸟教程     |    3 |
+--------------+------+
5 rows in set (0.00 sec)

4.2.3 update语句

//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

MariaDB [school]> select * from Linux0520;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | tom      |  20 |
|  2 | jerry    |  23 |
|  3 | jack     |  18 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  26 |
|  6 | wangwu   |   0 |
+----+----------+-----+
6 rows in set (0.00 sec)

MariaDB [school]> update Linux0520 set age = 30 where name = 'lisi';    //将lisi的年龄修改为30岁
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [school]> select * from Linux0520 where name = 'lisi';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  5 | lisi |  30 |
+----+------+-----+
1 row in set (0.00 sec)

4.2.4 delete语句

//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

MariaDB [school]> select * from Linux0520;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | tom      |  20 |
|  2 | jerry    |  23 |
|  3 | jack     |  18 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  30 |
|  6 | wangwu   |   0 |
+----+----------+-----+
6 rows in set (0.00 sec)

删除某条记录

MariaDB [school]> delete from Linux0520 where id = 6;
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from Linux0520;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | tom      |  20 |
|  2 | jerry    |  23 |
|  3 | jack     |  18 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  30 |
+----+----------+-----+
5 rows in set (0.00 sec)

删除整张表的内容

MariaDB [school]> delete from Linux0520;
Query OK, 5 rows affected (0.00 sec)

MariaDB [school]> select * from Linux0520;
Empty set (0.00 sec)

MariaDB [school]> desc Linux0520;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

4.2.5 truncate语句

truncate与delete的区别：

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

语句类型

特点

delete

DELETE删除表内容时仅删除内容，但会保留表结构
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据非常占用空间

truncate

删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表

//语法：TRUNCATE table_name;
MariaDB [school]> select * from Linux0520; 
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | tom      |  20 |
|  2 | jerry    |  23 |
|  3 | jack     |  18 |
|  4 | zhangsan |  25 |
|  5 | lisi     |  26 |
|  6 | wangwu   |   0 |
+----+----------+-----+
6 rows in set (0.00 sec)

MariaDB [school]> truncate Linux0520;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> select * from Linux0520; 
Empty set (0.00 sec)

MariaDB [school]> desc Linux0520;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4.3 DCL操作

4.3.1 创建授权grant

权限类型(priv_type)

权限类型	代表什么？
ALL	所有权限
SELECT	读取内容的权限
INSERT	插入内容的权限
UPDATE	更新内容的权限
DELETE	删除内容的权限

指定要操作的对象db_name.table_name

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

WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。

//语法：GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
5 rows in set (0.05 sec)

//授权tom用户在数据库本机上登录访问所有数据库
MariaDB [(none)]> grant all on *.* to 'tom'@'localhost' identified by '12321';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant all on *.* to 'tom'@'127.0.0.1' identified by '12321';
Query OK, 0 rows affected (0.00 sec)

//授权tom用户在192.168.118.133上远程登录访问school数据库
MariaDB [(none)]> grant all on school.* to 'tom'@'192.168.118.133' identified by '12321';
Query OK, 0 rows affected (0.00 sec)

//授权tom用户在所有位置上远程登录访问school数据库
MariaDB [(none)]> grant all on *.* to 'tom'@'%' identified by '12321';
Query OK, 0 rows affected (0.00 sec)

//授权tom用户school数据库的Linux0520表的插入权限
MariaDB [school]> grant insert on school.Linux0520 to 'tom'@'localhost' identified by '12321';
Query OK, 0 rows affected (0.00 sec)

4.3.2 查看授权

查看当前登录用户的授权信息

MariaDB [school]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*045C8A3B828B8D7674941C5D03E5961BAB546F9A' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看指定用户tom的授权信息

MariaDB [school]> show grants for tom;
+-------------------------------------------------------------------------------------------------------------+
| Grants for tom@%                                                                                            |
+-------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'tom'@'%' IDENTIFIED BY PASSWORD '*045C8A3B828B8D7674941C5D03E5961BAB546F9A' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [school]> show grants for 'tom'@'localhost';
+---------------------------------------------------------------------------------------------------------------------+
| Grants for tom@localhost                                                                                            |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'tom'@'localhost' IDENTIFIED BY PASSWORD '*045C8A3B828B8D7674941C5D03E5961BAB546F9A' |
| GRANT INSERT ON `school`.`Linux0520` TO 'tom'@'localhost'                                                           |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4.3.3 取消授权REVOKE

//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';

MariaDB [school]> revoke all on *.* from 'tom'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表

mysql> FLUSH PRIVILEGES;

4.3.4 MySQL密码破解

//mysql -uroot -p 输入密码回车后，出现如下图错误。这时候需要我们破解密码。
[root@localhost ~]# mysql -uroot -p'hello'
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

停止mysql服务

[root@localhost ~]# systemctl stop mariadb

编辑mysql配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables    //添加该行代码开机跳过授权表
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
......
......

重启mysql数据库并进入数据库

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -uroot -p
Enter password:     //直接回车
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>     //成功进入数据库

选择mysql数据库并更改密码

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set password=password('123456') where user="root";    //更改密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

退出数据库并把刚在配置文件里添加的代码删除，然后重启服务

MariaDB [mysql]> quit
Bye
[root@localhost ~]# vim /etc/my.cnf      //删除skip-grant-tables
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
......
......
[root@localhost ~]# systemctl restart mariadb

使用更改后的密码连接数据库

[root@localhost ~]# mysql -uroot -p'123456'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>     //成功连接说明密码破解成功

5. 实例

5.1 搭建mysql服务

[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl enable --now mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.                
[root@localhost ~]# ss -antl | grep 3306    
LISTEN     0      50           *:3306                     *:* 
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> set password = password('123456');    //设置mysql密码
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -uroot -p123456    //用密码登录mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

5.2 创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

MariaDB [(none)]> create database linux;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use linux;
Database changed
MariaDB [linux]> create table student(id int primary key auto_increment not null,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 sec)

MariaDB [linux]> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

5.3 查看下该新建的表有无内容（用select语句）

MariaDB [linux]> select * from student;
Empty set (0.00 sec)

5.4 往新建的student表中插入数据（用insert语句），结果应如下所示：

+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

MariaDB [linux]> insert into student(name,age) values('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangshan',20);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [linux]> insert into student(name) value('lisi');
Query OK, 1 row affected (0.00 sec)

MariaDB [linux]> insert into student(name,age) values('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [linux]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

5.5 修改lisi的年龄为50

MariaDB [linux]> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [linux]> select * from student where name = 'lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi |   50 |
+----+------+------+
1 row in set (0.00 sec)

5.6 以age字段降序排序

MariaDB [linux]> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangshan   |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.00 sec)

5.7 查询student表中年龄最小的3位同学跳过前2位

MariaDB [linux]> select * from student order by age limit 2,3;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
| 10 | qiuyi     |   15 |
|  1 | tom       |   20 |
|  6 | zhangshan |   20 |
+----+-----------+------+
3 rows in set (0.00 sec)

5.8 查询student表中年龄最大的4位同学

MariaDB [linux]> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | lisi      |   50 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  3 | wangqing  |   25 |
+----+-----------+------+
4 rows in set (0.00 sec)

5.9 查询student表中名字叫zhangshan的记录

MariaDB [linux]> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.00 sec)

5.10 查询student表中名字叫zhangshan且年龄大于20岁的记录

MariaDB [linux]> select * from student where name = 'zhangshan' and age > 20; 
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.00 sec)

5.11 查询student表中年龄在23到30之间的记录

MariaDB [linux]> select * from student where age between 20 and 30;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
7 rows in set (0.00 sec)

5.12 修改wangwu的年龄为100

MariaDB [linux]> update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [linux]> select * from student where name = 'wangwu'; 
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  9 | wangwu |  100 |
+----+--------+------+
1 row in set (0.00 sec)

5.13 删除student中名字叫zhangshan且年龄小于等于20的记录

MariaDB [linux]> delete from student where name = 'zhangshan' and age <= 20;
Query OK, 1 row affected (0.00 sec)

MariaDB [linux]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.00 sec)