Mariadb基础

it2023-09-21 64

Mariadb基础

关系型数据库介绍

数据结构模型

数据结构模型主要有：层次模型网状模型关系模型二维关系：row（行），column（列）数据库管理系统：DBMS（DataBase Management System）关系型数据库管理系统：RDBMS（Relational DataBase Management System）

注：数据库管理系统是由DateBase服务+DateBase+table+row、column组成，这些东西可以存在多个，而不是只能存在一个

RDBMS专业名词

常见的关系型数据库管理系统

MySQL：MySQL，MariaDB，Percona-ServerPostgreSQL：简称为pgsqlOracleMSSQL：Windows平台的数据库

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

约束：constraint，向数据表提供的数据要遵守的限制

主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。一个表中只能存在一个惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）一个表可以存在多个外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据检查性约束

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

关系型数据库的常见组件

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

SQL语句

SQL语句分为三类 DDL：Data Defination Language，数据定义语言DML：Data Manipulation Language，数据操纵语言DCL：Data Control Language，数据控制语言 SQL语句类型对应操作DDLCREATE：创建DROP：删除ALTER：修改DMLINSERT：向表中插入数据DELETE：删除表中数据UPDATE：更新表中数据SELECT：查询表中数据DCLGRANT：授权REVOKE：移除授权

MariaDB安装与配置

MariaDB安装

MariaDB和MySQL操作语法是通用的，但是现在MySQL是收费的，所以这里我们安装MariaDBMariaDB安装的方式有三种：源代码：编译安装二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用程序包管理器管理的程序包 //在Redhat 8中本地源中已经存在mariadb安装包，所以可以直接使用yum命令安装 [root@server ~]# yum -y install mariadb mariadb-common mariadb-devel mariadb-server 在Redhat 7 中要安装MySQL #配置mysql的yum源 wget -O /usr/src/mysql57-community-release-el7-10.noarch.rpm \ http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm rpm -Uvh /usr/src/mysql57-community-release-el7-10.noarch.rpm #安装mysql5.7 yum -y install mysql-community-server mysql-community-client \ mysql-community-common mysql-community-devel

Mariadb配置

//启动数据库 [root@server ~]# systemctl enable --now mariadb //确保3306端口已经处于监听状态 [root@server ~]# ss -antl|grep 3306 LISTEN 0 80 *:3306 *:* //登录mariadb数据库 [root@server ~]# mysql -uroot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.3.17-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('redhat123+'); //重新使用密码登，以下两种方法都可以登录 [root@server ~]# mysql -uroot -p'redhat123+' [root@server ~]# mysql -uroot -p Enter password:

Mariadb的程序组成

客户端 mysql：CLI交互式客户端程序mysql_secure_installation：安全初始化mysqldump：mysql备份工具mysqladmin：mysql备份工具服务端 Mariadb

Mariadb工具使用

语法：mysql [OPTIONS] [database]

常用选项：

-uUSERNAME #指定用户名，默认为root-hHOST #指定服务器主机，默认为localhost，推荐使用ip地址-pPASSWORD #指定用户的密码 [root@server ~]# mysql -uroot -p'redhat123+' -h127.0.0.1 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.3.17-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)]> -P# #指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307-V #查看当前使用的mysql版本 [root@server ~]# mysql -V mysql Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1 -e #不登录mysql执行sql语句后退出，常用于脚本 [root@server ~]# mysql -uroot -p'redhat123+' -e 'SHOW DATABASES;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+

服务端监听的两种socket地址

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

Mariadb数据库操作

DDL操作

数据库操作

创建数据库 //语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME'; MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS CWT; #如果CWT数据库不存在则创建CWT数据库，这样做的好处是，如果要创建的数据库存在也不会报错 Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | CWT | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) 删除数据库 //语法：DROP DATABASE [IF EXISTS] 'DB_NAME'; MariaDB [(none)]> DROP DATABASE IF EXISTS CWT; #如果CWT数据库存在则删除它，这样做的好处是，如果要删除的数据库不存在也不会报错 Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec)

表操作

创建表 //语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型'; MariaDB [(none)]> CREATE DATABASE XX_DATA; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | XX_DATA | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) //进入数据库 MariaDB [(none)]> USE XX_DATA Database changed MariaDB [XX_DATA]> SHOW TABLES; //创建表 MariaDB [XX_DATA]> CREATE TABLE xx(id int not null,name varchar(10),age tinyint); Query OK, 0 rows affected (0.004 sec) //查看表 MariaDB [XX_DATA]> SHOW TABLES; +-------------------+ | Tables_in_XX_DATA | +-------------------+ | xx | +-------------------+ 1 row in set (0.001 sec) 删除表 MariaDB [XX_DATA]> DROP TABLE xx; Query OK, 0 rows affected (0.003 sec) MariaDB [XX_DATA]> SHOW TABLES; Empty set (0.000 sec)

用户操作

mysql用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录，HOST的值可以为： IP地址，如：192.168.86.132通配符 %：匹配任意长度的任意字符，常用于设置允许从任何主机登录_：匹配任意单个字符

注： HOST应该为客户端的IP地址

//创建用于登录数据库的用户语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password']; MariaDB [(none)]> CREATE USER 'tom'@'192.168.86.132' IDENTIFIED BY 'redhat456+'; Query OK, 0 rows affected (0.000 sec) //使用新创建的用户和密码在客户端上登录 [root@client ~]# mysql -utom -p'redhat456+' -h192.168.86.129 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16 Server version: 10.3.17-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'@'192.168.86.132'; Query OK, 0 rows affected (0.000 sec)

查看命令SHOW

获取帮助

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

DML操作

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

INSERT语句

//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),... MariaDB [XX_DATA]> DESC xx; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(15) | YES | | NULL | | | age | tinyint(4) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.001 sec) //插入一条数据 MariaDB [XX_DATA]> INSERT INTO xx VALUE(1,'cwt',18); Query OK, 1 row affected (0.001 sec) //插入多条数据 MariaDB [XX_DATA]> INSERT INTO xx VALUES (2,'tom',20),(3,'xx',25),(4,'lisi',20); Query OK, 3 rows affected (0.001 sec) Records: 3 Duplicates: 0 Warnings: 0

SELECT语句

字段column表示法表示符含义*所有字段as字段别名条件判断语句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个结果 //语法：SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n]; //查看表中的所有数据 MariaDB [cwt]> SELECT * FROM student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhaangshan | 20 | | 7 | lisi | 50 | | 8 | chenshou | 10 | | 9 | wangwu | 100 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ //查看表中指定的字段的数据 MariaDB [cwt]> SELECT name FROM student; +-------------+ | name | +-------------+ | tom | | jerry | | wangqing | | sean | | zhangshan | | zhaangshan | | lisi | | chenshou | | wangwu | | qiuyi | | qiuxiaotian | +-------------+ //定义别名 MariaDB [cwt]> SELECT id AS a,name AS b,age AS c FROM student; +----+-------------+------+ | a | b | c | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | ... //查找年龄大于20的数据 MariaDB [cwt]> SELECT * FROM student WHERE age > 20; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 7 | lisi | 50 | | 9 | wangwu | 100 | +----+-----------+------+ //查找年龄在20到30岁之间的数据 MariaDB [cwt]> 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 | zhaangshan | 20 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ //查找名字以n结尾的数据 MariaDB [cwt]> SELECT * FROM student WHERE name LIKE '%n'; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhaangshan | 20 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ //使用正则表达式进行匹配 MariaDB [cwt]> SELECT * FROM student WHERE name RLIKE '^z.*'; +----+------------+------+ | id | name | age | +----+------------+------+ | 5 | zhangshan | 26 | | 6 | zhaangshan | 20 | +----+------------+------+ //匹配年龄为空的数据 MariaDB [cwt]> SELECT * FROM student WHERE age is NULL; +----+------+------+ | id | name | age | +----+------+------+ | 12 | cwt | NULL | +----+------+------+ //以age字段进行升序排序，去除age为NULL的行 MariaDB [cwt]> SELECT * FROM student WHERE age is NOT NULL ORDER BY age; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 8 | chenshou | 10 | | 10 | qiuyi | 15 | | 1 | tom | 20 | | 6 | zhaangshan | 20 | ... //以age字段进行降序排序，去除age为NULL的行 MariaDB [cwt]> SELECT * FROM student WHERE age is NOT NULL ORDER BY age DESC; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 9 | wangwu | 100 | | 7 | lisi | 50 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 3 | wangqing | 25 | ... //以age字段进行排序，并取前3个结果 MariaDB [cwt]> SELECT * FROM student WHERE age is NOT NULL ORDER BY age LIMIT 2; +----+----------+------+ | id | name | age | +----+----------+------+ | 8 | chenshou | 10 | | 10 | qiuyi | 15 | //以age字段进行排序，并忽略结果的第一个数据取后面的两个数据 MariaDB [cwt]> SELECT * FROM student WHERE age is NOT NULL ORDER BY age LIMIT 1,2; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+

GROUP BY语句

将结果集中的数据行根据选择列的值进行逻辑分组，以便能汇总表内容的子集，实现对每个组而不是对整个结果集进行整合 //语法GROUP BY {col_name | expr | position} [ASC|DESC] MariaDB [cwt]> SELECT * FROM info; +----+-----------+------------+--------+ | id | name | department | salary | +----+-----------+------------+--------+ | 1 | zhangshan | sales | 5000 | | 2 | wangwu | dev | 20000 | | 3 | lisi | sales | 15000 | | 4 | cwt | dev | 15000 | | 5 | tom | operation | 25000 | | 6 | jerry | operation | 20000 | +----+-----------+------------+--------+ //取出表中每个部门中工资最高的人 MariaDB [cwt]> SELECT name,department,MAX(salary) as salary FROM info GROUP BY department; +-----------+------------+--------+ | name | department | salary | +-----------+------------+--------+ | wangwu | dev | 20000 | | tom | operation | 25000 | | zhangshan | sales | 15000 | +-----------+------------+--------+

UPDATE语句

//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n]; MariaDB [XX_DATA]> SELECT * FROM xx; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | cwt | 18 | | 2 | tom | 20 | | 3 | xx | 25 | | 4 | lisi | 20 | +----+------+-----+ 4 rows in set (0.000 sec) MariaDB [XX_DATA]> UPDATE xx SET age = 21 where name = 'cwt'; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [XX_DATA]> SELECT * FROM xx; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | cwt | 21 | | 2 | tom | 20 | | 3 | xx | 25 | | 4 | lisi | 20 | +----+------+-----+ 4 rows in set (0.000 sec)

DELETE语句

//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n]; MariaDB [XX_DATA]> SELECT * FROM xx; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | cwt | 21 | | 2 | tom | 20 | | 3 | xx | 25 | | 4 | lisi | 20 | +----+------+-----+ 4 rows in set (0.000 sec) //删除表中的某一条记录 MariaDB [XX_DATA]> DELETE FROM xx where name = 'xx'; Query OK, 1 row affected (0.002 sec) MariaDB [XX_DATA]> SELECT * FROM xx; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | cwt | 21 | | 2 | tom | 20 | | 4 | lisi | 20 | +----+------+-----+ 3 rows in set (0.000 sec) //删除整张表的内容，但会保留表本身 MariaDB [XX_DATA]> DELETE FROM xx; Query OK, 3 rows affected (0.001 sec) MariaDB [XX_DATA]> SELECT * FROM xx; Empty set (0.000 sec) MariaDB [XX_DATA]> SHOW TABLES; +-------------------+ | Tables_in_XX_DATA | +-------------------+ | xx | +-------------------+ 1 row in set (0.000 sec)

TRUNCATE语句

TRUNCATE与DELETE的区别：语句特点DELETEDELETE删除表内容时仅删除内容，但会保留表结构DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间TRUNCATE删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表语法：TRUNCATE table_name; MariaDB [cwt]> DESC student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ MariaDB [cwt]> 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 | chenshou | 10 | | 9 | wangwu | 100 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ MariaDB [cwt]> TRUNCATE student; Query OK, 0 rows affected (0.307 sec) MariaDB [cwt]> INSERT student(name,age) VALUE('cwt',20); Query OK, 1 row affected (0.001 sec) MariaDB [cwt]> SELECT * FROM student; #如果使用DELETE删除表的内容，则重新插入数据时新添加的行计数值不会重置为初始值，也就是说id的值为12 +----+------+------+ | id | name | age | +----+------+------+ | 1 | cwt | 20 | +----+------+------+ 1 row in set (0.000 sec)

DCL操作

创建授权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 | +--------------------+ | XX_DATA | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) //授权tom用户在192.168.86.132上远程登录数据库，且对XX_DATA数据库有SELECT权限 MariaDB [(none)]> GRANT SELECT ON XX_DATA.* TO 'tom'@'192.168.86.132' IDENTIFIED BY 'redhat456+'; Query OK, 0 rows affected (0.000 sec) //重读授权表 MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.000 sec) //远程登录数据库，并测试权限是否正确设置 [root@client ~]# mysql -utom -p'redhat456+' -h192.168.86.129 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 18 Server version: 10.3.17-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)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | XX_DATA | | information_schema | +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> USE XX_DATA 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 [XX_DATA]> CREATE TABLE cwt(id int not null); ERROR 1142 (42000): CREATE command denied to user 'tom'@'192.168.86.132' for table 'cwt'

查看授权

查看当前登录用户的授权信息 MariaDB [(none)]> SHOW GRANTS; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*AACA4ED26B7AFC0A081A787B8C607B4262252DE5' WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION ... 查看指定用户的授权信息 MariaDB [(none)]> SHOW GRANTS FOR 'tom'@'192.168.86.132'; +-----------------------------------------------------------------------------------------------------------------+ | Grants for tom@192.168.86.132 | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tom'@'192.168.86.132' IDENTIFIED BY PASSWORD '*0AF6C9DF2CBB59D18182D5C4AFD2E069F93F7DA3' | | GRANT SELECT ON `XX_DATA`.* TO 'tom'@'192.168.86.132' ...

取消授权REVOKE

//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host'; MariaDB [(none)]> REVOKE SELECT ON XX_DATA.* FROM 'tom'@'192.168.86.132'; Query OK, 0 rows affected (0.000 sec) //重读授权表 MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> SHOW GRANTS FOR tom@192.168.86.132; +-----------------------------------------------------------------------------------------------------------------+ | Grants for tom@192.168.86.132 | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tom'@'192.168.86.132' IDENTIFIED BY PASSWORD '*0AF6C9DF2CBB59D18182D5C4AFD2E069F93F7DA3' | mysql服务进程启动时会读取mysql库中的所有授权表至内存中： GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表 //重读授权表 MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.000 sec)

内连接、左连接以及右连接

示例表说明： MariaDB [cwt]> SELECT * FROM info; +----+-----------+------------+--------+ | id | name | department | salary | +----+-----------+------------+--------+ | 1 | zhangshan | sales | 5000 | | 2 | wangwu | dev | 20000 | | 3 | lisi | sales | 15000 | | 4 | cwt | dev | 15000 | | 5 | tom | operation | 25000 | | 6 | jerry | operation | 20000 | +----+-----------+------------+--------+ 6 rows in set (0.000 sec) MariaDB [cwt]> SELECT * FROM age_info; +----+------+ | id | age | +----+------+ | 1 | 30 | | 2 | 20 | | 3 | 45 | | 4 | 30 | | 5 | 25 | | 6 | 23 | | 7 | 28 | | 8 | 30 | | 9 | 25 | | 10 | 23 | | 11 | 28 | | 12 | 30 | +----+------+ 12 rows in set (0.000 sec) MariaDB [cwt]> SELECT * FROM job_number; +----+----+ | id | jn | +----+----+ | 1 | 1 | | 2 | 3 | | 3 | 5 | | 4 | 6 | | 5 | 9 | +----+----+

内连接

组合两个表中的记录，返回关联字段相符的记录，也就是返回两张表中都包含的内容 MariaDB [cwt]> SELECT * FROM info as a INNER JOIN age_info as b ON a.id = b.id INNER JOIN job_number as c ON a.id = c.jn; +----+-----------+------------+--------+----+------+----+----+ | id | name | department | salary | id | age | id | jn | +----+-----------+------------+--------+----+------+----+----+ | 1 | zhangshan | sales | 5000 | 1 | 30 | 1 | 1 | | 3 | lisi | sales | 15000 | 3 | 45 | 2 | 3 | | 5 | tom | operation | 25000 | 5 | 25 | 3 | 5 | | 6 | jerry | operation | 20000 | 6 | 23 | 4 | 6 | +----+-----------+------------+--------+----+------+----+----+

左连接

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。 MariaDB [cwt]> SELECT * FROM info as a LEFT JOIN age_info as b ON a.id = b.id LEFT JOIN job_number as c ON a.id = c.jn; +----+-----------+------------+--------+------+------+------+------+ | id | name | department | salary | id | age | id | jn | +----+-----------+------------+--------+------+------+------+------+ | 1 | zhangshan | sales | 5000 | 1 | 30 | 1 | 1 | | 3 | lisi | sales | 15000 | 3 | 45 | 2 | 3 | | 5 | tom | operation | 25000 | 5 | 25 | 3 | 5 | | 6 | jerry | operation | 20000 | 6 | 23 | 4 | 6 | | 2 | wangwu | dev | 20000 | 2 | 20 | NULL | NULL | | 4 | cwt | dev | 15000 | 4 | 30 | NULL | NULL | +----+-----------+------------+--------+------+------+------+------+

右连接

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。 MariaDB [cwt]> SELECT * FROM info as a RIGHT JOIN age_info as b ON a.id = b.id RIGHT JOIN job_number as c ON a.id = c.jn; +------+-----------+------------+--------+------+------+----+----+ | id | name | department | salary | id | age | id | jn | +------+-----------+------------+--------+------+------+----+----+ | 1 | zhangshan | sales | 5000 | 1 | 30 | 1 | 1 | | 3 | lisi | sales | 15000 | 3 | 45 | 2 | 3 | | 5 | tom | operation | 25000 | 5 | 25 | 3 | 5 | | 6 | jerry | operation | 20000 | 6 | 23 | 4 | 6 | | NULL | NULL | NULL | NULL | NULL | NULL | 5 | 9 | +------+-----------+------------+--------+------+------+----+----+

查询多张表，组合结果 MariaDB [cwt]> SELECT * FROM info; +----+-----------+------------+--------+ | id | name | department | salary | +----+-----------+------------+--------+ | 1 | zhangshan | sales | 5000 | | 2 | wangwu | dev | 20000 | | 3 | lisi | sales | 15000 | | 4 | cwt | dev | 15000 | | 5 | tom | operation | 25000 | | 6 | jerry | operation | 20000 | +----+-----------+------------+--------+ 6 rows in set (0.000 sec) MariaDB [cwt]> SELECT * FROM age_info; +----+------+ | id | age | +----+------+ | 1 | 30 | | 2 | 20 | | 3 | 45 | | 4 | 30 | | 5 | 25 | | 6 | 23 | | 7 | 28 | | 8 | 30 | +----+------+ MariaDB [cwt]> SELECT * FROM info,age_info WHERE info.name = 'cwt' AND info.id = age_info.id; +----+------+------------+--------+----+------+ | id | name | department | salary | id | age | +----+------+------------+--------+----+------+ | 4 | cwt | dev | 15000 | 4 | 30 | +----+------+------------+--------+----+------+ 1 row in set (0.001 sec) //指定字段 MariaDB [cwt]> SELECT info.name,age_info.age FROM info,age_info WHERE info.name = 'cwt' AND info.id = age_info.id; +------+------+ | name | age | +------+------+ | cwt | 30 | +------+------+

最新回复(0)