一 MySQL 数据库学习认识数据库 _mysql

一数据库概述 1 数据库介绍（1）什么是数据库
存储数据的仓库
（2）生活中的数据库
视频、音频、图片、文本
（3）常见软件
主流操作系统：Unix、Linux、
（4）数据库类型
关系型数据库(RDBMS)
采用表格结构存储数据，使用SQL语言进行操作和管理例如MysQL、、等
非关系型数据库(NOSQL)
不使用表格结构，而是使用键值对、文档等数据结构存储数据，常用于大数据高并发等场景。例如、Redis、等
图形数据库 (Graph
用于存储图形数据，如社交网络、知识图谱等数据，采用节点和边进行数据建模例如Neo4i、等
内存数据库 (In- )
将数据缓存在内存中，具有高速读写、低延迟的优点，适用于对读写性能要求比较高的应用场景。例如、Redis 等
分布式数据库 ( ）
将数据分散存储在不同的节点上，实现水平扩展，提高整个系统的性能和容错性例如、、TDSQL等
不同的数据库类型适用于不同的应用场景和需求。在进行系统设计和开发时，需要根据具体的情况选择合适的数据库类型。
2 MySQL介绍（1）MySQL简史
MySQL 1.x是MySQL的最初版本，发布于1995年
MySQL 3.x 是该版本系列的最终版本，包括了许多基本的数据库功能。
MySQL 4.0和4.1主要引入了一些新特性，如存储过程、视图、游标等，增强了MySQL的功能。
MySQL 5.x 引入了存储引警、事件调度器、存储过程和视图等新特性。
MysQL 8.0是最新版本，于2018年发布。更新了许多新特性和改进，如函数、 Table(CTE) 和JSON支持等。
（2）特点及应用
主要特点
适用于中小规模、关系型数据库系统(RDBMS)
支持Linux、Unix、等多种操作系统
支持、Java、 Perl、PHP等编程语言
典型应用环境
LAMP平台，与 HTTP 组合
LNMP平台，与Nginx组合
二部署MySQL服务 1 启动服务（1）准备环境
准备2台虚拟机，要求如下：
IP地址
主机名
说明
192.168.88.50
MySQL 50
配置yum源，关闭和防火墙
192.168.88.51
MySQL 51
配置yum源，关闭和防火墙
（2）安装软件
命令操作如下所示：
mysql- 提供服务软件
mysql 提供命令软件
[root@mysql50 ~]# yum -y install mysql-servermysql//安装提供服务和命令软件//软件已安装[root@mysql50 ~]# rpm -q mysql-servermysqlmysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64[root@mysql50 ~]#[root@mysql50 ~]# rpm -qi mysql-server//查看软件信息Name: mysql-serverVersion: 8.0.26Release: 1.module+el8.4.0+652+6de068a7Architecture: x86_64Install Date: 2023年03月13日星期一 12时09分38秒Group: UnspecifiedSize: 126674945License: GPLv2 with exceptions and LGPLv2 and BSDSignature: RSA/SHA256, 2021年09月22日星期三 07时27分14秒, Key ID 15af5dac6d745a60Source RPM: mysql-8.0.26-1.module+el8.4.0+652+6de068a7.src.rpmBuild Date: 2021年09月22日星期三 07时06分32秒Build Host: ord1-prod-x86build005.svc.aws.rockylinux.orgRelocations : (not relocatable)Packager: infrastructure@rockylinux.orgVendor: RockyURL: http://www.mysql.comSummary: The MySQL server and related filesDescription :MySQL is a multi-user, multi-threaded SQL database server. MySQL is aclient/server implementation consisting of a server daemon (mysqld)and many different client programs and libraries. This package containsthe MySQL server and some accompanying files and directories.
（3）启动服务
[root@mysql50 ~]# systemctlstartmysqld//启动服务[root@mysql50 ~]# systemctlenablemysqld//开机运行[root@mysql50 ~]# systemctlenable mysqld//设置服务开机运行Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
【一MySQL 数据库学习认识数据库】（4）查看端口号和进程名
[root@mysql50 ~]# ps -C mysqld//查看进程PID TTYTIME CMD21912 ?00:00:00 mysqld[root@mysql50 ~]#[root@mysql50 ~]# ss -utnlp| grep 3306查看端口tcpLISTEN 070*:33060*:*users:(("mysqld",pid=21912,fd=22))tcpLISTEN 0128*:3306*:*users:(("mysqld",pid=21912,fd=25))[root@mysql50 ~]# 或[root@mysql50 ~]# netstat-utnlp| grepmysqld//仅查看mysqld进程tcp600 :::33060:::*LISTEN21912/mysqldtcp600 :::3306:::*LISTEN21912/mysqld[root@mysql50 ~]#
说明：
MySQL 8中的3306端口是MySQL服务默认使用的端口，主要用于建立客户端与MySQL服务器之间的连接。
MySQL 8中的33060端口是MySQL Shell默认使用的管理端口，主要用于执行各种数据库管理任务。远程管理MySQL服务器：使用MySQL Shell连接到MySQL服务，并在远程管理控制台上执行各种数据库管理操作，例如创建、删除、备份和恢复数据库等。
（5）相关参数
2 连接服务（1）命令格式
mysql -h -P -u -p
选项
mysql- -P3306 -uroot -
（2）基本操作
select version () ;//查看服务软件版本select user() ;//查看登录用户showdatabases;//查看已有库use 库名;//切换库select database();//查看当前所在库show tables;//查看库里已有表exit ;//断开连接
mysql> select version() ;//查看数据库软件版本+-----------+| version() |+-----------+| 8.0.26|+-----------+1 row in set (0.00 sec)mysql> select user() ; //查看登陆的用户和客户端地址 +----------------+| user()|+----------------+| root@localhost | 管理员root本机登陆+----------------+1 row in set (0.00 sec) mysql> show databases; //查看已有的库+--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|+--------------------+4 rows in set (0.00 sec)mysql>
说明：
默认4个库不可以删除，存储的是服务运行时加载的不同功能的程序和数据。
：是MySQL数据库提供的一个虚拟的数据库，存储了MySQL数据库中的相关信息，比如数据库、表、列、索引、权限、角色等信息。它并不存储实际的数据，而是提供了一些视图和存储过程，用于查询和管理数据库的元数据信息。
mysql：存储了MySQL服务器的系统配置、用户、账号和权限信息等。它是MySQL数据库最基本的库，存储了MySQL服务器的核心信息。
：存储了MySQL数据库的性能指标、事件和统计信息等数据，可以用于性能分析和优化。
sys：是MySQL 8.0引入的一个新库，它基于和视图，提供了更方便、更直观的方式来查询和管理MySQL数据库的元数据和性能数据。
mysql> select database();//查看当前在那个库里null 表示没有在任何库里+------------+| database() |+------------+| NULL|+------------+1 row in set (0.00 sec)mysql> use mysql ; //切换到mysql库mysql> select database();// 再次显示所在的库+------------+| database() |+------------+| mysql|+------------+1 row in set (0.00 sec)mysql> show tables;//显示库里已有的表+------------------------------------------------------+| Tables_in_mysql|+------------------------------------------------------+| columns_priv|| component|| db|| default_roles|| engine_cost|| func|| general_log|| global_grants|| gtid_executed|| help_category|| help_keyword|| help_relation|| help_topic|| innodb_index_stats|| innodb_table_stats|| password_history|| plugin|| procs_priv|| proxies_priv|| replication_asynchronous_connection_failover|| replication_asynchronous_connection_failover_managed || replication_group_configuration_version|| replication_group_member_actions|| role_edges|| server_cost|| servers|| slave_master_info|| slave_relay_log_info|| slave_worker_info|| slow_log|| tables_priv|| time_zone|| time_zone_leap_second|| time_zone_name|| time_zone_transition|| time_zone_transition_type|| user|+------------------------------------------------------+37 rows in set (0.00 sec)mysql> exit ;断开连接Bye[root@mysql50 ~]#
（3）连接服务
说明：数据库管理员本机登陆默认没有密码
命令操作如下所示：
[root@mysql50 ~]# mysql//连接服务Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.26 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>登陆后的提示符mysql> exit //断开连接Bye[root@mysql50 ~]#
3 密码管理（1）密码的设置和修改
命令格式
//设置密码]# mysqladmin -hlocalhost -uroot -p password"新密码//修改密码password"新密码]#mysqladmin -hlocalhost -uroot -p旧密码
设置root密码为
命令操作如下所示：（2行输出是警告而已不用关心）
[root@mysql50 ~]# mysqladmin-uroot -ppassword "tarena"Enter password: //敲回车mysqladmin: [Warning] Using a password on the command line interface can be insecure.Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.[root@mysql50 ~]# mysql //无密码连接被拒绝ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@mysql50 ~]#[root@mysql50 ~]# mysql -uroot –ptarena//连接时输入密码mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 14Server version: 8.0.26 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 登陆成功
修改root密码为…A
命令操作如下所示：
[root@mysql50 ~]# mysqladmin-uroot -ptarena password "123qqq...A"//修改密码mysqladmin: [Warning] Using a password on the command line interface can be insecure.Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.[root@mysql50 ~]# mysql -uroot –ptarena //旧密码无法登陆mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@mysql50 ~]# mysql -uroot -p123qqq...A//新密码登陆mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 18Server version: 8.0.26 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 登陆成功
（2）破解密码
破解root密码为…a
[root@mysql50 ~]# mysql -uroot -pNSD2023...a//破解前登陆失败mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@mysql50 ~]# vim /etc/my.cnf.d/mysql-server.cnf//修改主配置文件[mysqld]skip-grant-tables//手动添加此行作用登陆时不验证密码:wq[root@mysql50 ~]# systemctlrestart mysqld //重启服务作用让服务以新配置运行[root@mysql50 ~]# mysql //连接服务Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 7Server version: 8.0.26 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.//把mysql库下user表中用户root的密码设置为无；mysql> update mysql.user set authentication_string="" where user="root";Query OK, 1 row affected (0.05 sec)Rows matched: 1Changed: 1Warnings: 0mysql> exit; 断开连接Bye[root@mysql50 ~]# vim/etc/my.cnf.d/mysql-server.cnf编辑配置文件[mysqld]#skip-grant-tables//注释添加的行:wq[root@mysql50 ~]# systemctlrestart mysqld //重启服务作用让注释生效[root@localhost ~]# mysql无密码登陆Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.26 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.//设置root用户本机登陆密码mysql> alter user root@"localhost" identified by "NSD2023...a";Query OK, 0 rows affected (0.00 sec)mysql> exit 断开连接Bye[root@localhost ~]# mysql不输密码无法登陆ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@localhost ~]# mysql -uroot -pNSD2023...a使用破解的密码登陆mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 10Server version: 8.0.26 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>mysql> 登陆成功mysql> show databases; 查看已有的库+--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|+--------------------+4 rows in set (0.01 sec)
三基础查询 1安装图形软件（1）图形软件
客户端连接MySQL服务的方法
（2）安装
步骤一：部署运行环境LNP
命令操作如下所示：
gcc 源码包编译工具unzip 提供解压.zip 压缩包命令make 源码软件编译命令pcre-devel 支持正则表达式zlib-devel 提供数据压缩命令[root@mysql50 ~]# yum -y install gcc unzip make pcre-devel zlib-devel //安装依赖[root@mysql50 ~]# tar -xf nginx-1.22.1.tar.gz //解压源码[root@mysql50 ~]# cd nginx-1.22.1 //进源码目录[root@mysql50 nginx-1.22.1]# ./configure //配置[root@mysql50 nginx-1.22.1]# make &&makeinstall //编译并安装[root@mysql50 nginx-1.22.1]# ls /usr/local/nginx///查看安装目录confhtmllogssbin[root@mysql50 nginx-1.22.1]# vim /usr/local/nginx/conf/nginx.conf //修改主配置文件43location / {44roothtml;45indexindex.php index.html index.htm; //添加php首页名46}65location ~ \.php$ {//访问.php的请求转给本机的9000端口66roothtml;67fastcgi_pass127.0.0.1:9000;68fastcgi_indexindex.php;69#fastcgi_paramSCRIPT_FILENAME/scripts$fastcgi_script_name;70includefastcgi.conf;//保存nginx变量文件71}:wq[root@mysql50 nginx-1.22.1]# /usr/local/nginx/sbin/nginx //启动服务[root@mysql50 nginx-1.22.1]# netstat-utnlp| grep 80//查看端口tcp00 0.0.0.0:800.0.0.0:*LISTEN42182/nginx: master [root@mysql50 nginx-1.22.1]#php 解释php代码php-devel php扩展包 php-mysqlnd 连接mysql命令包php-json 支持json代码php-fpm 提供fpm服务[root@mysql50 ~]# yum -y install phpphp-devel php-mysqlnd php-json php-fpm //安装php软件[root@mysql50 ~]# vim /etc/php-fpm.d/www.conf //修改主配置文件38 ;listen = /run/php-fpm/www.sock39 listen = 127.0.0.1:9000//非socket方式运行,不是必须的:wq[root@mysql50 ~]# systemctlstart php-fpm//启动服务[root@mysql50 ~]# netstat-utnlp| grep 9000//查看端口tcp00 127.0.0.1:90000.0.0.0:*LISTEN67251/php-fpm: mast [root@mysql50 ~]#[root@mysql50 ~]# vim /usr/local/nginx/html/test.php //编写php脚本:wq[root@mysql50 ~]# curlhttp://localhost/test.php//访问脚本plj[root@mysql50 ~]#
步骤二：安装软件
命令操作如下所示：
[root@mysql50 ~]# unzip phpMyAdmin-5.2.1-all-languages.zip//解压[root@mysql50 ~]# mv phpMyAdmin-5.2.1-all-languages /usr/local/nginx/html/phpmyadmin//移动并改名，为了便于访问[root@mysql50 ~]# cd /usr/local/nginx/html/phpmyadmin///进软件目录[root@mysql50 phpmyadmin]# cp config.sample.inc.php config.inc.php //创建主配置文件[root@mysql50 phpmyadmin]# vim config.inc.php //修改主配置文件//定义cookies验证码16 $cfg['blowfish_secret'] = 'plj123'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! *///管理本机的数据库服务30 $cfg['Servers'][$i]['host'] = 'localhost';:wq[root@mysql50 phpmyadmin]# setenforce 0//关闭selinux[root@mysql50 phpmyadmin]# systemctlstop firewalld //关闭防火墙
（3）登陆软件
打开浏览器输入此网址效果如图所示
说明：输入数据库管理员root 和密码成功后如图所示
2 基础查询（1）环境准备
拷贝.sql文件到主机里，然后使用.sql创建练习使用的数据。
//拷贝tarena.sql 拷贝到 mysql50主机的/root 下[openeuler@server1 ~]$ scp /linux-soft/s3/tarena.sqlroot@192.168.88.50:/root/root@192.168.88.50's password: tarena.sql100%284KB 171.9MB/s00:00 //连接mysql50主机[openeuler@server1 ~]$ ssh root@192.168.88.50root@192.168.88.50's password: Last login: Tue May 23 10:59:57 2023 from 192.168.88.254//恢复数据[root@mysql50 ~]# mysql -uroot-pNSD2023...a < /root/tarena.sqlmysql: [Warning] Using a password on the command line interface can be insecure.//连接服务[root@mysql50 ~]# mysql -uroot-pNSD2023...amysql> show databases; //查看库+--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|| tarena| 恢复的库+--------------------+5 rows in set (0.00 sec)mysql> use tarena; //进入库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;//查看表+------------------+| Tables_in_tarena |+------------------+| departments| 部门表| employees| 员工表| salary| 工资表| user| 用户表+------------------+4 rows in set (0.00 sec)
使用user 表做查询练习
user表里存储的是系统用户信息就是 /etc/ 文件的内容
mysql> desc tarena.user;//查看表头+----------+-------------+------+-----+---------+----------------+| Field| Type| Null | Key | Default | Extra|+----------+-------------+------+-----+---------+----------------+| id| int(11)| NO| PRI | NULL| auto_increment |行号| name| char(20)| YES|| NULL||用户名| password | char(1)| YES|| NULL||密码占位符| uid| int(11)| YES|| NULL|| uid号| gid| int(11)| YES|| NULL|| gid号| comment| varchar(50) | YES|| NULL|| 描述信息| homedir| varchar(80) | YES|| NULL|| 家目录| shell| char(30)| YES|| NULL|| 解释器+----------+-------------+------+-----+---------+----------------+8 rows in set (0.00 sec)
（2）语法
语法格式1：字段列表 FROM 库名.表名；
语法格式2：字段列表 FROM 库名.表名 where 筛选条件
mysql> select name fromtarena.user;//查看一个表头mysql> select name ,uidfrom tarena.user;//查看多个表头mysql> select *fromtarena.user;//查看所有表头
加筛选条件
mysql> select *fromtarena.user wherename = “root”;//查找root用户信息 +----+------+----------+------+------+---------+---------+-----------+| id | name | password | uid| gid| comment | homedir | shell|+----+------+----------+------+------+---------+---------+-----------+|1 | root | x|0 |0 | root| /root| /bin/bash |+----+------+----------+------+------+---------+---------+-----------+1 row in set (0.00 sec)mysql> mysql> select *fromtarena.user whereid = 2 ;//查找第2行用户信息+----+------+----------+------+------+---------+---------+--------------+| id | name | password | uid| gid| comment | homedir | shell|+----+------+----------+------+------+---------+---------+--------------+|2 | bin| x|1 |1| bin| /bin| /sbin/nologin |+----+------+----------+------+------+---------+---------+--------------+1 row in set (0.00 sec)
（3）语法规则
必须遵守，命令才会执行
（4）筛选条件
符号两边必须是数字或数值类型的表头
命令操作如下所示：
//查看第3行的行号、用户名、uid、gid四个表头的值mysql> selectid,name,uid,gidfromtarena.user whereid = 3;+----+--------+------+------+| id | name| uid| gid|+----+--------+------+------+|3 | daemon |2 |2 |+----+--------+------+------+1 row in set (0.00 sec)//查看前2行的行号用户名、uid、gid 四个表头的值mysql> selectid,name,uid,gidfromtarena.user whereid < 3;+----+------+------+------+| id | name | uid| gid|+----+------+------+------+|1 | root |0 |0 ||2 | bin|1 |1 |+----+------+------+------+2 rows in set (0.00 sec)//查看前3行的行号、用户名、uid、gid四个表头的值mysql> selectid,name,uid,gidfromtarena.user whereid <= 3;+----+--------+------+------+| id | name| uid| gid|+----+--------+------+------+|1 | root|0 |0 ||2 | bin|1 |1 ||3 | daemon |2 |2 |+----+--------+------+------+3 rows in set (0.00 sec)//查看前uid号大于6000的行号、用户名、uid、gid四个表头的值mysql> selectid,name,uid,gidfromtarena.user whereuid > 6000;+----+-----------+-------+-------+| id | name| uid| gid|+----+-----------+-------+-------+| 22 | nfsnobody | 65534 | 65534 |+----+-----------+-------+-------+1 row in set (0.00 sec)//查看前uid号大于等于1000的行号、用户名、uid、gid四个表头的值mysql> selectid,name,uid,gidfromtarena.user whereuid >= 1000;+----+-----------+-------+-------+| id | name| uid| gid|+----+-----------+-------+-------+| 22 | nfsnobody | 65534 | 65534 || 24 | plj|1000 |1000 |+----+-----------+-------+-------+2 rows in set (0.00 sec)//查看uid号和gid号相同的行仅显示行号、用户名、uid、gid四个表头的值mysql> selectid,name,uid,gidfromtarena.user whereuid = gid;+----+-----------------+-------+-------+| id | name| uid| gid|+----+-----------------+-------+-------+|1 | root|0 |0 ||2 | bin|1 |1 ||3 | daemon|2 |2 || 13 | nobody|99 |99 || 14 | systemd-network |192 |192 || 15 | dbus|81 |81 || 17 | sshd|74 |74 || 18 | postfix|89 |89 || 20 | rpc|32 |32 || 21 | rpcuser|29 |29 || 22 | nfsnobody| 65534 | 65534 || 23 | haproxy|188 |188 || 24 | plj|1000 |1000 || 25 | apache|48 |48 || 26 | mysql|27 |27 |+----+-----------------+-------+-------+15 rows in set (0.00 sec)//查看uid号和gid号不一样的行仅显示行号、用户名、uid、gid四个表头的值mysql> selectid,name,uid,gidfromtarena.user whereuid != gid;+----+----------+------+------+| id | name| uid| gid|+----+----------+------+------+|4 | adm|3 |4 ||5 | lp|4 |7 ||6 | sync|5 |0 ||7 | shutdown |6 |0 ||8 | halt|7 |0 ||9 | mail|8 |12 || 10 | operator |11 |0 || 11 | games|12 |100 || 12 | ftp|14 |50 || 16 | polkitd|999 |998 || 19 | chrony|998 |996 |+----+----------+------+------+11 rows in set (0.00 sec)mysql>
范围匹配
匹配范围内的任意一个值即可
命令操作如下所示：
//uid号表头的值是 (1 , 3 , 5 , 7) 中的任意一个即可mysql> select name , uidfromtarena.user whereuidin (1 , 3 , 5 , 7);+------+------+| name | uid|+------+------+| bin|1 || adm|3 || sync |5 || halt |7 |+------+------+//shell 表头的的值不是 "/bin/bash"或"/sbin/nologin" 即可mysql> select name , shellfromtarena.user whereshellnot in ("/bin/bash","/sbin/nologin");+----------+----------------+| name| shell|+----------+----------------+| sync| /bin/sync|| shutdown | /sbin/shutdown || halt| /sbin/halt|| mysql| /bin/false|+----------+----------------+//id表头的值在 10到20 之间即可包括 10 和20本身mysql> select id , name , uidfromtarena.user where idbetween 10 and 20 ;+----+-----------------+------+| id | name| uid|+----+-----------------+------+| 10 | operator|11 || 11 | games|12 || 12 | ftp|14 || 13 | nobody|99 || 14 | systemd-network |192 || 15 | dbus|81 || 16 | polkitd|999 || 17 | sshd|74 || 18 | postfix|89 || 19 | chrony|998 || 20 | rpc|32 |+----+-----------------+------+11 rows in set (0.00 sec)mysql>
模糊匹配
用法
where字段名like"表达式";
通配符
命令操作如下所示：
//找名字必须是3个字符的（没有空格挨着敲）mysql> select name fromtarena.user wherename like "___"; +------+| name |+------+| bin|| adm|| ftp|| rpc|| plj|| bob|+------+6 rows in set (0.00 sec)//找名字必须是4个字符的（没有空格挨着敲）mysql> select name fromtarena.user wherename like "_ _ _ _"; +------+| name |+------+| root || sync || halt || mail || dbus || sshd || null |+------+7 rows in set (0.00 sec)//找名字以字母a开头的（没有空格挨着敲）mysql> select name fromtarena.user wherename like "a%";//查找名字至少是4个字符的表达式mysql> select name fromtarena.user wherename like "%_ _ _ _%";（没有空格挨着敲）mysql> select name fromtarena.user where namelike "_ _%_ _";（没有空格挨着敲）mysql> select name fromtarena.user where namelike "_ _ _ _%";（没有空格挨着敲）
正则匹配
格式
select 字段名列表 from 库名.表名 where 字段名 regexp '正则表达式'；
正则符号
命令操作如下所示：
//添加有数字的名字insert intotarena.user(name)values("yaya9");insert intotarena.user(name)values("6yaya");insert intotarena.user(name)values("ya7ya");insert intotarena.user(name)values("yay8a");//查看名字里有数字的mysql> select name fromtarena.user where name regexp "[0-9]"; +-------+| name|+-------+| yaya9 || 6yaya || ya7ya || yay8a |+-------+4 rows in set (0.00 sec)//查看名字以数字开头mysql> select name fromtarena.user where name regexp "^[0-9]"; +-------+| name|+-------+| 6yaya |+-------+1 row in set (0.00 sec)//查看名字以数字结尾mysql> select name fromtarena.user where name regexp "[0-9]$"; +-------+| name|+-------+| yaya9 |+-------+1 row in set (0.00 sec)mysql> //查看名字以r开头mysql> select name fromtarena.user where name regexp "^r"; +---------+| name|+---------+| root|| rpc|| rpcuser |+---------+3 rows in set (0.00 sec)//查看名字以t结尾mysql> select name fromtarena.user where name regexp "t$"; +------+| name |+------+| root || halt |+------+2 rows in set (0.00 sec)mysql> //查看名字以r开头或t结尾mysql> select name fromtarena.user where name regexp "^r|t$"; +---------+| name|+---------+| root|| halt|| rpc|| rpcuser |+---------+4 rows in set (0.00 sec)//名字r开头t结尾mysql> select name fromtarena.user where name regexp "^r.*t$"; +------+| name |+------+| root |+------+1 row in set (0.00 sec)mysql>
逻辑比较
多个判断条件时使用and的优先级高于or
命令操作如下所示：
//逻辑非例子，查看解释器不是/bin/bash 的mysql> select name,shell from tarena.user whereshell != "/bin/bash"; //not 也是取反要放在表达式的前边mysql> select name,shell from tarena.user wherenotshell = "/bin/bash";//id值不在 10 到 20 之间 mysql> select id , name from tarena.user where notid between 10 and 20 ;//逻辑与例子mysql> select name , uid from tarena.user where name="root" and uid = 1;Empty set (0.00 sec)mysql> select name , uid from tarena.user where name="root" and uid = 0;+------+------+| name | uid|+------+------+| root |0 |+------+------+1 row in set (0.00 sec)//逻辑或例子mysql> select name , uid from tarena.user where name = "root"orname = "bin"oruid = 1;+------+------+| name | uid|+------+------+| root |0 || bin|1 |+------+------+mysql>
() 提高优先级
mysql> select2+ 3 * 5 ; //使用默认计算顺序先乘除后加减+------------+| 2+ 3 * 5 |+------------+|17 |+------------+1 row in set (0.00 sec)mysql> select(2+ 3 ) * 5 ; //先加法再乘法+---------------+| (2+ 3 ) * 5 |+---------------+|25 |+---------------+1 row in set (0.00 sec)mysql>
逻辑与and 优先级高于逻辑或 or的时候需要加（）
如果在筛选条件里既有and 又有 or 默认先判断and 再判断or
//没加（）的查询结果select name , uid from tarena.userwhere name = "root" or name = "bin"and uid = 1 ;+------+------+| name | uid|+------+------+| root |0 || bin|1 |+------+------+2 rows in set (0.00 sec)//加（）的查询结果select name , uid from tarena.userwhere (name = "root" or name = "bin")and uid = 1 ;+------+------+| name | uid|+------+------+| bin|1 |+------+------+1 row in set (0.00 sec)mysql>
字符比较/空/非空
符号两边必须是字符或字符类型的表头
命令操作如下所示：
//查看表里是否有名字叫apache的用户mysql> selectnamefromtarena.user where name="apache" ;+--------+| name|+--------+| apache |+--------+1 row in set (0.00 sec)//输出解释器不是/bin/bash的用户名及使用的解释器mysql> selectname , shellfromtarena.user where shell != "/bin/bash";+-----------------+----------------+| name| shell|+-----------------+----------------+| bin| /sbin/nologin|| daemon| /sbin/nologin|| adm| /sbin/nologin|| lp| /sbin/nologin|| sync| /bin/sync|| shutdown| /sbin/shutdown || halt| /sbin/halt|| mail| /sbin/nologin|| operator| /sbin/nologin|| games| /sbin/nologin|| ftp| /sbin/nologin|| nobody| /sbin/nologin|| systemd-network | /sbin/nologin|| dbus| /sbin/nologin|| polkitd| /sbin/nologin|| sshd| /sbin/nologin|| postfix| /sbin/nologin|| chrony| /sbin/nologin|| rpc| /sbin/nologin|| rpcuser| /sbin/nologin|| nfsnobody| /sbin/nologin|| haproxy| /sbin/nologin|| apache| /sbin/nologin|| mysql| /bin/false|+-----------------+----------------+24 rows in set (0.00 sec)mysql>
空 is null 表头下没有数据
非空 is not null 表头下有数据
mysql服务使用关键字 null 或 NULL 表示表头没有数据
//添加新行仅给行中的id 表头和name表头赋值mysql> insert into tarena.user(id,name) values(71,"");//零个字符mysql> insert into tarena.user(id,name) values(72,"null");//普通字母mysql> insert into tarena.user(id,name) values(73,NULL);//表示空mysql> insert into tarena.user(id,name) values(74,null);//表示空//查看id表头值大于等于70的行仅显示行中 id表头和name 表头的值mysql> select id , name fromtarena.user whereid >= 71;+----+------+| id | name |+----+------+| 71 ||| 72 | null || 73 | NULL || 74 | NULL |+----+------+//查看name 表头没有数据的行仅显示行中id表头和naeme 表头的值mysql> select id , name fromtarena.user where name is null;+----+------+| id | name |+----+------+| 28 | NULL || 29 | NULL || 73 | NULL || 74 | NULL |+----+------+//查看name 表头是0个字符的行，仅显示行中id表头和naeme 表头的值mysql> select id , name fromtarena.user where name="";+----+------+| id | name |+----+------+| 71 ||+----+------+1 row in set (0.00 sec)//查看name 表头值是null的行，仅显示行中id表头和naeme 表头的值mysql> select id , name fromtarena.user where name="null";+----+------+| id | name |+----+------+| 72 | null |+----+------+1 row in set (0.00 sec)//查看name 表头有数据的行，仅显示行中id表头和name 表头的值mysql> select id , name fromtarena.user where name is not null;+----+-----------------+| id | name|+----+-----------------+|1 | root||2 | bin||3 | daemon||4 | adm||5 | lp|........| 27 | bob|| 71 ||| 72 | null|+----+-----------------+
别名/去重/合并
改变查询结果的显示效果
命令操作如下所示：
//定义别名使用as或空格mysql> select name , homedirfrom tarena.user;mysql> select name as 用户名 , homedir家目录 from tarena.user;//拼接concat()mysql> select concat(name,"-",uid) as 用户信息 from tarena.user where uid <= 5;+--------------+| 用户信息|+--------------+| root-0|| bin-1|| daemon-2|| adm-3|| lp-4|| sync-5|+--------------+6 rows in set (0.00 sec)//2列拼接mysql>select concat(name , "-" , uid)as用户信息from tarena.user where uid <= 5;//多列拼接mysql>select concat(name , "-" , uid , "-" , gid)as用户信息from tarena.user where uid <= 5;+--------------+| 用户信息|+--------------+| root-0-0|| bin-1-1|| daemon-2-2|| adm-3-4|| lp-4-7|| sync-5-0|+--------------+
去重显示字段名列表
//去重前输出mysql> select shellfromtarena.userwhere shell in ("/bin/bash","/sbin/nologin") ;+---------------+| shell|+---------------+| /bin/bash|| /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /sbin/nologin || /bin/bash|| /sbin/nologin |+---------------+22 rows in set (0.00 sec)//去重后查看mysql> select distinct shellfromtarena.userwhere shell in ("/bin/bash","/sbin/nologin") ;+---------------+| shell|+---------------+| /bin/bash|| /sbin/nologin |+---------------+2 rows in set (0.01 sec)mysql>