mysql基础

it2026-03-07 4

1.关系型数据库介绍

1.1 数据结构模型

数据结构模型主要有：

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

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

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

1.2 RDBMS专业名词

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

MySQL：MySQL，MariaDB，Percona-ServerPostgreSQL：简称为pgsqlOracleMSSQL

SQ: Structure Query Language，结构化查询语言

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

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

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

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语句类型对应操作DDLCREATE：创建 DROP：删除 ALTER：修改DMLINSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据DCLGRANT：授权 REVOKE：移除授权

2. mariadb 安装与配置

2.1 mariadb 安装

配置yum本地源 [root@linux131 yum.repos.d]# pwd /etc/yum.repos.d [root@linux131 yum.repos.d]# ls redhat.repo xx.repo [root@linux131 yum.repos.d]# cat xx.repo [BaseOS] name=baseos baseurl=file:///mnt/BaseOS gpgcheck=0 enabled=1 [AppStream] name=appstream baseurl=file:///mnt/AppStream gpgcheck=0 enabled=1 下载安装4个包 mariadbmariadb-commonmariadb-develmariadb-server [root@linux131 yum.repos.d]# yum -y install mariadb mariadb-common mariadb-devel mariadb-server 略... mariadb-connector-c-3.0.7-1.el8.x86_64 mariadb-connector-c-config-3.0.7-1.el8.noarch mariadb-connector-c-devel-3.0.7-1.el8.x86_64 mariadb-errmsg-3:10.3.17-1.module+el8.1.0+3974+90eded84.x86_64 perl-DBD-MySQL-4.046-3.module+el8.1.0+2938+301254e2.x86_64 perl-DBI-1.641-3.module+el8.1.0+2928+fafc4afc.x86_64 perl-Digest-1.17-395.el8.noarch perl-Digest-MD5-2.55-396.el8.x86_64 perl-Net-SSLeay-1.88-1.el8.x86_64 perl-URI-1.73-3.el8.noarch perl-libnet-3.11-3.el8.noarch Complete!

2.2 mariadb配置

启动mariadb并设置开机自动启动，确保3306端口已经监听起来 [root@linux131 ~]# systemctl enable --now mariadb [root@linux131 ~]# systemctl status mariadb [root@linux131 ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 80 *:3306 *:* 登录数据库，设置密码 [root@linux131 ~]# mysql -uroot //此root指的是数据库的管理员 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)]> set password = password('lp123456'); //设置加密密码的格式 Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> quit //退出 Bye

3. mysql的程序组成

客户端 mysql：CLI交互式客户端程序mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令mysqldump：mysql备份工具mysqladmin 服务器端 mysqld

3.1 mariadb工具使用

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

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

4. mysql数据库操作

4.1 DDL操作

4.1.1 数据库操作

//创建数据库 //语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME'; 创建数据库school MariaDB [(none)]> create database school; Query OK, 1 row affected (0.001 sec) show databases查看当前有哪些数据库 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | +--------------------+ 4 rows in set (0.001 sec) //删除数据库 //语法：DROP DATABASE [IF EXISTS] 'DB_NAME'; //删除数据库school MariaDB [(none)]> drop database school; Query OK, 0 rows affected (0.006 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.001 sec) if not exists执行不会报错 MariaDB [(none)]> create database if not exists school; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]>

4.1.2 表操作

//创建表 //语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型'; MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | +--------------------+ 4 rows in set (0.001 sec) MariaDB [(none)]> use school //use进入某个数据库 Database changed //create创建 table表格 stusent名字(id int not null整列数字不为空,name varchar(10)数字长度为10,age tinyint时长不超过200); MariaDB [school]> create table student(id int not null,name varchar(10),age tinyint); Query OK, 0 rows affected (0.016 sec) MariaDB [school]> show tables; //查看当前数据库有哪些表格 +------------------+ | Tables_in_school | +------------------+ | student | +------------------+ 1 row in set (0.001 sec) MariaDB [school]> drop table student; //删除表格 Query OK, 0 rows affected (0.013 sec) MariaDB [school]> show tables; Empty set (0.001 sec)

4.1.3 用户操作

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

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

IP地址，如：192.168.152.131通配符 %：匹配任意长度的任意字符，常用于设置允许从任何主机登录_：匹配任意单个字符 //语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password']; 创建数据库用户tom [root@linux131 ~]# mysql -uroot -plp123456 MariaDB [(none)]> create user tom@127.0.0.1 identified by '123456'; Query OK, 0 rows affected (0.003 sec) 使用新创建的用户和密码登录 [root@linux131 ~]# mysql -utom -p123456 -h127.0.0.1 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 17 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. 删除数据库用户 //语法：DROP USER 'username'@'host'; MariaDB [(none)]> drop user tom@127.0.0.1; Query OK, 0 rows affected (0.001 sec) 创建可以远程登录131数据库的用户 MariaDB [(none)]> create user lisi@192.168.152.133 identified by '123456'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> grant all on *.* to 'lisi'@'192.168.152.133'; //给133主机权限 Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> flush privileges; //刷新 Query OK, 0 rows affected (0.001 sec) [root@ansible ~]# mysql -ulisi -p123456 -h192.168.152.131 //lisi账户从133主机就能登录131主机 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 20 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. 创建远程登录账户zhangsan，不设置密码，只有查看的权限 MariaDB [(none)]> create user zhangsan@192.168.152.133; Query OK, 0 rows affected (0.001 sec) [root@ansible ~]# mysql -uzhangsan -h192.168.152.131 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 21 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)]>

4.1.4 查看命令SHOW

4.1.5 获取帮助

//获取命令使用帮助 //语法：HELP keyword; MariaDB [school]> 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 略... MariaDB [school]> help create database; //获取创建数据库的帮助 Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. URL: https://mariadb.com/kb/en/create-database/

4.2 DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(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操作之增操作insert //语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),... MariaDB [school]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | score | float | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.002 sec) MariaDB [school]> insert student value(1,'tom',20); //增加一个表格，名字tom，20岁 Query OK, 1 row affected (0.003 sec) //DML操作之查操作select //语法：SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n]; MariaDB [school]> select * from student; //查看表格所有信息 +----+------+-------+ | id | name | age | +----+------+-------+ | 1 | tom | 20 | +----+------+-------+ 1 row in set (0.002 sec) MariaDB [school]> select name from student; //查看name这一行信息 +------+ | name | +------+ | tom | +------+ 1 row in set (0.001 sec) 同时增加多个用户信息 MariaDB [school]> insert student value(2,'xiaozhan',18),(3,'lixian',20),(4,'wangyibo',19),(5,'zhuyilong',22),(6,'denglun',23); Query OK, 5 rows affected (0.024 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [school]> select * from student; //查看表格所有信息 +----+-----------+-------+ | id | name | age | +----+-----------+-------+ | 1 | tom | 20 | | 2 | xiaozhan | 18 | | 3 | lixian | 20 | | 4 | wangyibo | 19 | | 5 | zhuyilong | 22 | | 6 | denglun | 23 | +----+-----------+-------+ 6 rows in set (0.001 sec) MariaDB [school]> insert student(id) value(7); //增加指定id一行为7 Query OK, 1 row affected (0.004 sec) MariaDB [school]> select * from student; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | tom | 20 | | 2 | xiaozhan | 18 | | 3 | lixian | 20 | | 4 | wangyibo | 19 | | 5 | zhuyilong | 22 | | 6 | denglun | 23 | | 7 | NULL | NULL | +----+-----------+------+ 7 rows in set (0.001 sec) //DML操作之改操作update //语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n]; 更改name为laowang,指定条件为age为NULL那一行 MariaDB [school]> update student set name = 'laowang' where age is NULL; Query OK, 1 row affected (0.004 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [school]> select * from student; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | tom | 20 | | 2 | xiaozhan | 18 | | 3 | lixian | 20 | | 4 | wangyibo | 19 | | 5 | zhuyilong | 22 | | 6 | denglun | 23 | | 7 | laowang | NULL | +----+-----------+------+ 7 rows in set (0.001 sec) 更改age等于100岁，条件是age等于NULL这一行 MariaDB [school]> update student set age = 100 where age is NULL; Query OK, 1 row affected (0.003 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [school]> select * from student; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | tom | 20 | | 2 | xiaozhan | 18 | | 3 | lixian | 20 | | 4 | wangyibo | 19 | | 5 | zhuyilong | 22 | | 6 | denglun | 23 | | 7 | laowang | 100 | +----+-----------+------+ 7 rows in set (0.001 sec) //DML操作之删操作delete //语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n]; 指定删除某一行信息，指定id等于1这一行 MariaDB [school]> delete from student where id = 1; Query OK, 0 rows affected (0.001 sec) MariaDB [school]> select * from student; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 2 | xiaozhan | 18 | | 3 | lixian | 20 | | 4 | wangyibo | 19 | | 5 | zhuyilong | 22 | | 6 | denglun | 23 | | 7 | laowang | 100 | +----+-----------+------+ 6 rows in set (0.001 sec) MariaDB [school]> delete from student; //删除这个表的内容 Query OK, 6 rows affected (0.004 sec) MariaDB [school]> select * from student; //查看表里面的内容没有 Empty set (0.001 sec) MariaDB [school]> show tables; //表格还存在 +------------------+ | Tables_in_school | +------------------+ | student | +------------------+ 1 row in set (0.001 sec) 创建表格 MariaDB [school]> create table student(id int not null,name varchar(11)age tinyint); Query OK, 0 rows affected (0.009 sec) 表格内插入信息 MariaDB [school]> insert student values(1,'tom',11),(2,'jerry',22),(3,'iaoming',18); Query OK, 3 rows affected (0.002 sec) Records: 3 Duplicates: 0 Warnings: 0 查看 MariaDB [school]> select * from student; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | tom | 11 | | 2 | jerry | 22 | | 3 | xiaoming | 18 | +----+----------+------+ 3 rows in set (0.001 sec) 当表名很长时用别名代替//as MariaDB [school]> select id as 编号,name as 名字,age as 年龄 from student; +--------+----------+--------+ | 编号 | 名字 | 年龄 | +--------+----------+--------+ | 1 | tom | 11 | | 2 | jerry | 22 | | 3 | xiaoming | 18 | +--------+----------+--------+ 3 rows in set (0.001 sec) 取出名字小于20的id编号 MariaDB [school]> select id from student where age <20; +----+ | id | +----+ | 1 | | 3 | +----+ 2 rows in set (0.001 sec) 用like匹配某个名字，用正则表达式的方式 MariaDB [school]> select * from student where name like 'x%'; +----+----------+------+ | id | name | age | +----+----------+------+ | 3 | xiaoming | 18 | +----+----------+------+ 1 row in set (0.001 sec) 指定age一列，desc降序排序 MariaDB [school]> select * from student order by age desc; +----+----------+------+ | id | name | age | +----+----------+------+ | 2 | jerry | 22 | | 3 | xiaoming | 18 | | 1 | tom | 11 | +----+----------+------+ 3 rows in set (0.001 sec) 指定age一列，asc升序排序，默认为升序 MariaDB [school]> select * from student order by age asc; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | tom | 11 | | 3 | xiaoming | 18 | | 2 | jerry | 22 | +----+----------+------+ 3 rows in set (0.001 sec) limit取出年纪最小的两个 MariaDB [school]> select * from student order by age limit 2; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | tom | 11 | | 3 | xiaoming | 18 | +----+----------+------+ 2 rows in set (0.001 sec) 先用desc降序，limit取出年纪最大的两个 MariaDB [school]> select * from student order by age desc limit 2; +----+----------+------+ | id | name | age | +----+----------+------+ | 2 | jerry | 22 | | 3 | xiaoming | 18 | +----+----------+------+ 2 rows in set (0.001 sec) limit取出年纪最小的两个，跳过第一个 MariaDB [school]> select * from student order by age limit 1,2; +----+----------+------+ | id | name | age | +----+----------+------+ | 3 | xiaoming | 18 | | 2 | jerry | 22 | +----+----------+------+ 2 rows in set (0.001 sec) 创建表格info，设置主键，自动增长 MariaDB [school]> create table info(id int not null primary key auto_increment,name varchar(50) not null,department varchar(100),salary int); Query OK, 0 rows affected (0.006 sec) 查看表格 MariaDB [school]> desc info; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | department | varchar(100) | YES | | NULL | | | salary | int(11) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.004 sec) 表格内插入名字，部门，工资 MariaDB [school]> insert info(name,department,salary) values('tom','sales',5000),('jerry','dev',20000),('zhangshan','dev',10000),('lisi','sales',30000),('wangwu','office',8000),('qianliu','financial',10000),('zhaokai','financial',20000),('sunba','operation',25000),('zhoujiu','operation',9000),('wutian','dev',15000); Query OK, 10 rows affected (0.004 sec) Records: 10 Duplicates: 0 Warnings: 0 查看建好的表格内容 MariaDB [school]> select * from info; +----+-----------+------------+--------+ | id | name | department | salary | +----+-----------+------------+--------+ | 1 | tom | sales | 5000 | | 2 | jerry | dev | 20000 | | 3 | zhangshan | dev | 10000 | | 4 | lisi | sales | 30000 | | 5 | wangwu | office | 8000 | | 6 | qianliu | financial | 10000 | | 7 | zhaokai | financial | 20000 | | 8 | sunba | operation | 25000 | | 9 | zhoujiu | operation | 9000 | | 10 | wutian | dev | 15000 | +----+-----------+------------+--------+ 10 rows in set (0.001 sec) 查出每个部门department工资最高max人的名字，group分组 MariaDB [school]> select name,department,max(salary) as salary from info group by department; +---------+------------+--------+ | name | department | salary | +---------+------------+--------+ | jerry | dev | 20000 | | qianliu | financial | 20000 | | wangwu | office | 8000 | | sunba | operation | 25000 | | tom | sales | 30000 | +---------+------------+--------+ 5 rows in set (0.002 sec) 添加一个新的字段jn MariaDB [school]> alter table info add jn int not null; Query OK, 0 rows affected (0.005 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [school]> desc info; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | department | varchar(100) | YES | | NULL | | | salary | int(11) | YES | | NULL | | | jn | int(11) | NO | | NULL | | +------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.002 sec) MariaDB [school]> select * from info; +----+-----------+------------+--------+----+ | id | name | department | salary | jn | +----+-----------+------------+--------+----+ | 1 | tom | sales | 5000 | 0 | | 2 | jerry | dev | 20000 | 0 | | 3 | zhangshan | dev | 10000 | 0 | | 4 | lisi | sales | 30000 | 0 | | 5 | wangwu | office | 8000 | 0 | | 6 | qianliu | financial | 10000 | 0 | | 7 | zhaokai | financial | 20000 | 0 | | 8 | sunba | operation | 25000 | 0 | | 9 | zhoujiu | operation | 9000 | 0 | | 10 | wutian | dev | 15000 | 0 | +----+-----------+------------+--------+----+ 10 rows in set (0.001 sec) 修改编号 MariaDB [school]> update info set jn = 10 where id = 10; Query OK, 1 row affected (0.003 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [school]> select * from info; +----+-----------+------------+--------+----+ | id | name | department | salary | jn | +----+-----------+------------+--------+----+ | 1 | tom | sales | 5000 | 1 | | 2 | jerry | dev | 20000 | 2 | | 3 | zhangshan | dev | 10000 | 3 | | 4 | lisi | sales | 30000 | 4 | | 5 | wangwu | office | 8000 | 5 | | 6 | qianliu | financial | 10000 | 6 | | 7 | zhaokai | financial | 20000 | 7 | | 8 | sunba | operation | 25000 | 8 | | 9 | zhoujiu | operation | 9000 | 9 | | 10 | wutian | dev | 15000 | 10 | +----+-----------+------------+--------+----+ 10 rows in set (0.001 sec) 新建表格basic_info MariaDB [school]> create table basic_info(job_number int not null,age int not null); Query OK, 0 rows affected (0.007 sec) MariaDB [school]> desc basic_info; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | job_number | int(11) | NO | | NULL | | | age | int(11) | NO | | NULL | | +------------+---------+------+-----+---------+-------+ 2 rows in set (0.003 sec) 表格内插入信息 MariaDB [school]> insert basic_info values(1,21),(3,25),(5,30),(7,26),(9,28),(10,30),(8,22); Query OK, 7 rows affected (0.002 sec) Records: 7 Duplicates: 0 Warnings: 0 MariaDB [school]> select * from basic_info; +------------+-----+ | job_number | age | +------------+-----+ | 1 | 21 | | 3 | 25 | | 5 | 30 | | 7 | 26 | | 9 | 28 | | 10 | 30 | | 8 | 22 | +------------+-----+ 7 rows in set (0.001 sec)

内连接、左连接、右连接以及全连接查询

一、内连接查询 inner join

语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;

说明：组合两个表中的记录，查询两个表有交集的部分

查询info和basic_info这两个表的内连接 MariaDB [school]> select * from info; +----+-----------+------------+--------+----+ | id | name | department | salary | jn | +----+-----------+------------+--------+----+ | 1 | tom | sales | 5000 | 1 | | 2 | jerry | dev | 20000 | 2 | | 3 | zhangshan | dev | 10000 | 3 | | 4 | lisi | sales | 30000 | 4 | | 5 | wangwu | office | 8000 | 5 | | 6 | qianliu | financial | 10000 | 6 | | 7 | zhaokai | financial | 20000 | 7 | | 8 | sunba | operation | 25000 | 8 | | 9 | zhoujiu | operation | 9000 | 9 | | 10 | wutian | dev | 15000 | 10 | +----+-----------+------------+--------+----+ MariaDB [school]> select * from basic_info; +------------+-----+ | job_number | age | +------------+-----+ | 1 | 21 | | 3 | 25 | | 5 | 30 | | 7 | 26 | | 9 | 28 | | 10 | 30 | | 8 | 22 | +------------+-----+ 7 rows in set (0.001 sec) 用as为选项更改别名为ab，条件为a=b，取出有交集的信息 MariaDB [school]> select * from info as a inner join basic_info as b on a.jn = b.job_number; +----+-----------+------------+--------+----+------------+-----+ | id | name | department | salary | jn | job_number | age | +----+-----------+------------+--------+----+------------+-----+ | 1 | tom | sales | 5000 | 1 | 1 | 21 | | 3 | zhangshan | dev | 10000 | 3 | 3 | 25 | | 5 | wangwu | office | 8000 | 5 | 5 | 30 | | 7 | zhaokai | financial | 20000 | 7 | 7 | 26 | | 8 | sunba | operation | 25000 | 8 | 8 | 22 | | 9 | zhoujiu | operation | 9000 | 9 | 9 | 28 | | 10 | wutian | dev | 15000 | 10 | 10 | 30 | +----+-----------+------------+--------+----+------------+-----+ 7 rows in set (0.001 sec)

二、左连接查询 left join

语句：SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;

说明： left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

查询info和basic_info这两个表的左连接 MariaDB [school]> select * from info as a left join basic_info as b on a.jn = b.job_number; +----+-----------+------------+--------+----+------------+------+ | id | name | department | salary | jn | job_number | age | +----+-----------+------------+--------+----+------------+------+ | 1 | tom | sales | 5000 | 1 | 1 | 21 | | 3 | zhangshan | dev | 10000 | 3 | 3 | 25 | | 5 | wangwu | office | 8000 | 5 | 5 | 30 | | 7 | zhaokai | financial | 20000 | 7 | 7 | 26 | | 9 | zhoujiu | operation | 9000 | 9 | 9 | 28 | | 10 | wutian | dev | 15000 | 10 | 10 | 30 | | 8 | sunba | operation | 25000 | 8 | 8 | 22 | | 2 | jerry | dev | 20000 | 2 | NULL | NULL | | 4 | lisi | sales | 30000 | 4 | NULL | NULL | | 6 | qianliu | financial | 10000 | 6 | NULL | NULL | +----+-----------+------------+--------+----+------------+------+ 10 rows in set (0.002 sec)

三、右连接 right join

语句：SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;

说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

查询info和basic_info这两个表的右连接 MariaDB [school]> select * from info as a right join basic_info as b on a.jn = b.job_number; +------+-----------+------------+--------+------+------------+-----+ | id | name | department | salary | jn | job_number | age | +------+-----------+------------+--------+------+------------+-----+ | 1 | tom | sales | 5000 | 1 | 1 | 21 | | 3 | zhangshan | dev | 10000 | 3 | 3 | 25 | | 5 | wangwu | office | 8000 | 5 | 5 | 30 | | 7 | zhaokai | financial | 20000 | 7 | 7 | 26 | | 8 | sunba | operation | 25000 | 8 | 8 | 22 | | 9 | zhoujiu | operation | 9000 | 9 | 9 | 28 | | 10 | wutian | dev | 15000 | 10 | 10 | 30 | +------+-----------+------------+--------+------+------------+-----+ 7 rows in set (0.001 sec) 取出info表里名字叫tom的，条件是两个表的jn和job_number要一致 MariaDB [school]> select * from info,basic_info where info.name = 'tom' and info.jn = basic_info.job_number; +----+------+------------+--------+----+------------+-----+ | id | name | department | salary | jn | job_number | age | +----+------+------------+--------+----+------------+-----+ | 1 | tom | sales | 5000 | 1 | 1 | 21 | +----+------+------------+--------+----+------------+-----+ 1 row in set (0.001 sec) 取出两张表里名字一致的信息 MariaDB [school]> select info.name,basic_info.age from info,basic_info where info.name = 'tom' and info.jn = basic_info.job_number; +------+-----+ | name | age | +------+-----+ | tom | 21 | +------+-----+ 1 row in set (0.001 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：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。

设置所有权限给tom,允许在133主机登录，查看指定数据库的表，密码为123456 MariaDB [school]> grant all on school.student to tom@192.168.152.133 identified by '123456'; Query OK, 0 rows affected (0.001 sec) MariaDB [school]> flush privileges; //设置好权限必须刷新 Query OK, 0 rows affected (0.001 sec) 设置所有权限给lisi,允许在133主机登录，查看所有数据库 MariaDB [(none)]> grant all on *.* to 'lisi'@'192.168.152.133'; MariaDB [(none)]> flush privileges;

4.3.2 查看授权

查看当前登录用户的授权信息 MariaDB [school]> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*DD9FDDDE84D0834EC1E75A9E5BC4A2A1E41C210C' WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec) 查看指定用户tom的授权信息 MariaDB [(none)]> show grants for tom@192.168.152.133; +------------------------------------------------------------------------------------------------------------------+ | Grants for tom@192.168.152.133 | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tom'@'192.168.152.133' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `school`.`student` TO 'tom'@'192.168.152.133' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec)

4.3.3 取消授权REVOKE

语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host'; 删除select读取内容的权限 MariaDB [(none)]> revoke select on school.student from tom@192.168.152.133; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> show grants for tom@192.168.152.133; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for tom@192.168.152.133 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tom'@'192.168.152.133' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, DELETE HISTORY ON `school`.`student` TO 'tom'@'192.168.152.133' | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec)

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

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

案例：

创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age） [root@linux131 ~]# mysql -uroot -plp123456 MariaDB [(none)]> create database liaopan; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> use liaopan; Database changed MariaDB [liaopan]> create table student(id int not null,name varchar(11),age tinyint); Query OK, 0 rows affected (0.007 sec) MariaDB [liaopan]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(11) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.001 sec) 或者以下 MariaDB [xiaozhan]> create table student (id int null primary key auto_increment,name varchar(100) not null,age tinyint(4)); Query OK, 0 rows affected (0.044 sec) MariaDB [xiaozhan]> 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.038 sec) 查看下该新建的表有无内容（用select语句） MariaDB [liaopan]> select * from student; Empty set (0.001 sec) 往新建的student表中插入数据（用insert语句） MariaDB [liaopan]> insert into student (id,name,age) values(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); Query OK, 11 rows affected (0.001 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [liaopan]> 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.000 sec) 修改lisi的年龄为50 MariaDB [liaopan]> update student set age = 50 where name = 'lisi'; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [liaopan]> 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 | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 11 rows in set (0.000 sec) 以age字段降序排序 MariaDB [liaopan]> 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.001 sec) 查询student表中年龄最小的3位同学跳过前2位 MariaDB [liaopan]> select * from student order by age limit 2,3; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 11 | qiuxiaotian | 20 | | 2 | jerry | 23 | +----+-------------+------+ 3 rows in set (0.001 sec) 查询student表中年龄最大的4位同学 MariaDB [liaopan]> 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.000 sec) 查询student表中名字叫zhangshan的记录 MariaDB [liaopan]> select * from student where name = 'zhangshan'; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | +----+-----------+------+ 2 rows in set (0.001 sec) 查询student表中名字叫zhangshan且年龄大于20岁的记录 MariaDB [liaopan]> select * from student where name = 'zhangshan'and age>20; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 5 | zhangshan | 26 | +----+-----------+------+ 1 row in set (0.001 sec) 查询student表中年龄在23到30之间的记录 MariaDB [liaopan]> select * from student where age between 23 and 30; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | +----+-----------+------+ 4 rows in set (0.000 sec) 修改wangwu的年龄为100 MariaDB [liaopan]> update student set age = 100 where name = 'wangwu'; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [liaopan]> 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 | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 100 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 11 rows in set (0.001 sec) 删除student中名字叫zhangshan且年龄小于等于20的记录 MariaDB [liaopan]> delete from student where name = 'zhangshan' and age <=20; Query OK, 1 row affected (0.001 sec) MariaDB [liaopan]> 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.001 sec)

最新回复(0)