Top
NSD DBA DAY01
- 案例1:構建MySQL服務器
- 案例2:密碼管理
- 案例3:安裝圖形軟件
- 案例4:篩選條件
1 案例1:構建MySQL服務器
1.1 問題
- 在IP地址192.168.88.50主機和192.168.88.51主機上部署mysql服務
- 練習必備命令的使用
1.2 方案
準備2臺虛擬機,要求如下:
表-1
?
配置yum源、關閉selinux和防火墻,如果忘記了請自行補習前邊課程的知識或查看今天講課的PPT,謝謝!!!
1.3 步驟
實現此案例需要按照如下步驟進行。
步驟一:安裝軟件
命令操作如下所示:
mysql-server 提供服務軟件
mysql 提供命令軟件
- [root@mysql50 ~]# yum -y install mysql-server mysql //安裝提供服務和命令軟件
- //軟件已安裝
- [root@mysql50 ~]# rpm -q mysql-server mysql
- mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64
- mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64
- [root@mysql50 ~]#
- [root@mysql50 ~]# rpm -qi mysql-server //查看軟件信息
- Name : mysql-server
- Version : 8.0.26
- Release : 1.module+el8.4.0+652+6de068a7
- Architecture: x86_64
- Install Date: 2023年03月13日 星期一 12時09分38秒
- Group : Unspecified
- Size : 126674945
- License : GPLv2 with exceptions and LGPLv2 and BSD
- Signature : RSA/SHA256, 2021年09月22日 星期三 07時27分14秒, Key ID 15af5dac6d745a60
- Source RPM : mysql-8.0.26-1.module+el8.4.0+652+6de068a7.src.rpm
- Build Date : 2021年09月22日 星期三 07時06分32秒
- Build Host : ord1-prod-x86build005.svc.aws.rockylinux.org
- Relocations : (not relocatable)
- Packager : infrastructure@rockylinux.org
- Vendor : Rocky
- URL : http://www.mysql.com
- Summary : The MySQL server and related files
- Description :
- MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
- client/server implementation consisting of a server daemon (mysqld)
- and many different client programs and libraries. This package contains
- the MySQL server and some accompanying files and directories.
- [root@mysql50 ~]# systemctl start mysqld //啟動服務
- [root@mysql50 ~]# systemctl enable mysqld //開機運行
- [root@mysql50 ~]# systemctl enable mysqld //設置服務開機運行
- Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
步驟二:查看端口號和進程名
命令操作如下所示:
- [root@mysql50 ~]# ps -C mysqld //查看進程
- PID TTY TIME CMD
- 21912 ? 00:00:00 mysqld
- [root@mysql50 ~]#
- [root@mysql50 ~]# ss -utnlp | grep 3306 查看端口
- tcp LISTEN 0 70 *:33060 *:* users:(("mysqld",pid=21912,fd=22))
- tcp LISTEN 0 128 *:3306 *:* users:(("mysqld",pid=21912,fd=25))
- [root@mysql50 ~]#
- 或
- [root@mysql50 ~]# netstat -utnlp | grep mysqld //僅查看mysqld進程
- tcp6 0 0 :::33060 :::* LISTEN 21912/mysqld
- tcp6 0 0 :::3306 :::* LISTEN 21912/mysqld
- [root@mysql50 ~]#
說明:
MySQL 8中的3306端口是MySQL服務默認使用的端口,主要用于建立客戶端與MySQL服務器之間的連接。
MySQL 8中的33060端口是MySQL Shell默認使用的管理端口,主要用于執行各種數據庫管理任務。遠程管理MySQL服務器:使用MySQL Shell連接到MySQL服務,并在遠程管理控制臺上執行各種數據庫管理操作,例如創建、刪除、備份和恢復數據庫等。
步驟三:連接服務。
說明: 數據庫管理員本機登陸默認沒有密碼
命令操作如下所示:
- [root@mysql50 ~]# mysql //連接服務
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 8
- Server version: 8.0.26 Source distribution
- Copyright (c) 2000, 2021, Oracle and/or its affiliates.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> 登陸后的提示符
- mysql> exit //斷開連接
- Bye
- [root@mysql50 ~]#
步驟四:配置第2臺數據庫服務器MySQL51。
命令操作如下所示:
- [root@mysql51 ~]# yum -y install mysql-server mysql
- [root@mysql51 ~]# systemctl start mysqld
- [root@mysql51 ~]# systemctl enable mysqld
- [root@mysql51 ~]# mysql
- mysql> exit
- Bye
- [root@mysql51 ~]#
步驟五:練習必備命令的使用(在mysql50主機完成練習)
命令操作如下所示:
- 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個庫 不可以刪除,存儲的是 服務運行時加載的不同功能的程序和數據。
information_schema:是MySQL數據庫提供的一個虛擬的數據庫,存儲了MySQL數據庫中的相關信息,比如數據庫、表、列、索引、權限、角色等信息。它并不存儲實際的數據,而是提供了一些視圖和存儲過程,用于查詢和管理數據庫的元數據信息。
mysql:存儲了MySQL服務器的系統配置、用戶、賬號和權限信息等。它是MySQL數據庫最基本的庫,存儲了MySQL服務器的核心信息。
performance_schema:存儲了MySQL數據庫的性能指標、事件和統計信息等數據,可以用于性能分析和優化。
sys:是MySQL 8.0引入的一個新庫,它基于information_schema和performance_schema視圖,提供了更方便、更直觀的方式來查詢和管理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 ~]#
2 案例2:密碼管理
2.1 問題
1) 在192.168.88.50主機做如下練習:
- 設置root密碼為tarena
- 修改root密碼為123qqq…A
- 破解root密碼為NSD2023…a
2.2 步驟
實現此案例需要按照如下步驟進行。
步驟一:設置root密碼為tarena
命令操作如下所示:
2行輸出是警告而已不用關心
- [root@mysql50 ~]# mysqladmin -uroot -p password "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 14
- Server version: 8.0.26 Source distribution
- Copyright (c) 2000, 2021, Oracle and/or its affiliates.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> 登陸成功
步驟二:修改root密碼為123qqq…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 18
- Server version: 8.0.26 Source distribution
- Copyright (c) 2000, 2021, Oracle and/or its affiliates.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> 登陸成功
步驟三:破解root密碼為NSD2023…a
說明:在mysql50主機做此練習
命令操作如下所示:
- [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 ~]# systemctl restart mysqld //重啟服務 作用讓服務以新配置運行
- [root@mysql50 ~]# mysql //連接服務
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 7
- Server version: 8.0.26 Source distribution
- Copyright (c) 2000, 2021, Oracle and/or its affiliates.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- //把mysql庫下user表中 用戶root的密碼設置為無;
- mysql> update mysql.user set authentication_string="" where user="root";
- Query OK, 1 row affected (0.05 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> exit; 斷開連接
- Bye
- [root@mysql50 ~]# vim /etc/my.cnf.d/mysql-server.cnf 編輯配置文件
- [mysqld]
- #skip-grant-tables //注釋添加的行
- :wq
- [root@mysql50 ~]# systemctl restart mysqld //重啟服務 作用讓注釋生效
- [root@localhost ~]# mysql 無密碼登陸
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 8
- Server version: 8.0.26 Source distribution
- Copyright (c) 2000, 2021, Oracle and/or its affiliates.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- //設置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 10
- Server version: 8.0.26 Source distribution
- Copyright (c) 2000, 2021, Oracle and/or its affiliates.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql>
- mysql> 登陸成功
- mysql> show databases; 查看已有的庫
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- +--------------------+
- 4 rows in set (0.01 sec)
3 案例3:安裝圖形軟件
3.1 問題
- 在IP地址192.168.88.50主機安裝phpmyadmin軟件
- 客戶端通過訪問phpmyadmin軟件管理數據庫
3.2 方案
把用到的軟件拷貝的虛擬機mysql50里
在mysql50主機,首先配置運行環境LNP,然后安裝phpmyadmin軟件,最后打開真機的瀏覽器輸入phpmyadmin的網址訪問。
3.3 步驟
實現此案例需要按照如下步驟進行。
步驟一:部署運行環境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 && make install //編譯并安裝
- [root@mysql50 nginx-1.22.1]# ls /usr/local/nginx/ //查看安裝目錄
- conf html logs sbin
- [root@mysql50 nginx-1.22.1]# vim /usr/local/nginx/conf/nginx.conf //修改主配置文件
- 43 location / {
- 44 root html;
- 45 index index.php index.html index.htm; //添加php首頁名
- 46 }
- 65 location ~ \.php$ { //訪問.php的請求轉給本機的9000端口
- 66 root html;
- 67 fastcgi_pass 127.0.0.1:9000;
- 68 fastcgi_index index.php;
- 69 #fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
- 70 include fastcgi.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 //查看端口
- tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 42182/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 php php-devel php-mysqlnd php-json php-fpm //安裝php軟件
- [root@mysql50 ~]# vim /etc/php-fpm.d/www.conf //修改主配置文件
- 38 ;listen = /run/php-fpm/www.sock
- 39 listen = 127.0.0.1:9000 //非socket方式運行,不是必須的
- :wq
- [root@mysql50 ~]# systemctl start php-fpm //啟動服務
- [root@mysql50 ~]# netstat -utnlp | grep 9000 //查看端口
- tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 67251/php-fpm: mast
- [root@mysql50 ~]#
- [root@mysql50 ~]# vim /usr/local/nginx/html/test.php //編寫php腳本
- <?php
- $name = "plj" ;
- echo $name ;
- echo "\n" ;
- ?>
- :wq
- [root@mysql50 ~]# curl http://localhost/test.php //訪問腳本
- plj
- [root@mysql50 ~]#
步驟二:安裝phpmyadmin軟件
命令操作如下所示:
- [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]# systemctl stop firewalld //關閉防火墻
步驟三:客戶端訪問
命令操作如下所示:
- http://192.168.88.50/phpmyadmin 打開瀏覽器輸入此網址 效果如圖-1所示
?
圖-1
說明:輸入數據庫管理員root 和 密碼 成功后如圖-2所示
?
4 案例4:篩選條件
4.1 問題
- 準備練習環境
- 練習數值比較
- 練習范圍匹配
- 練習模糊匹配
- 練習正則匹配
- 練習邏輯比較
- 練習字符比較/空/非空
- 練習別名/去重/合并
4.2 方案
拷貝tarena.sql文件到mysql50主機里,然后使用tarena.sql創建練習使用的數據。
4.3 步驟
實現此案例需要按照如下步驟進行。
步驟一:準備練習環境
- //拷貝tarena.sql 拷貝到 mysql50主機的/root 下
- [openeuler@server1 ~]$ scp /linux-soft/s3/tarena.sql root@192.168.88.50:/root/
- root@192.168.88.50's password:
- tarena.sql 100% 284KB 171.9MB/s 00:00
- //連接mysql50主機
- [openeuler@server1 ~]$ ssh root@192.168.88.50
- root@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.sql
- mysql: [Warning] Using a password on the command line interface can be insecure.
- //連接服務
- [root@mysql50 ~]# mysql -uroot -pNSD2023...a
- mysql> 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 names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> show tables; //查看表
- +------------------+
- | Tables_in_tarena |
- +------------------+
- | departments | 部門表
- | employees | 員工表
- | salary | 工資表
- | user | 用戶表
- +------------------+
- 4 rows in set (0.00 sec)
使用user 表做查詢練習
user表里存儲的是 系統用戶信息 就是 /etc/passwd 文件的內容
- 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)
select命令格式演示
語法格式1 SELECT 字段列表 FROM 庫名.表名;
語法格式2 SELECT 字段列表 FROM 庫名.表名 where 篩選條件;
- mysql> select name from tarena.user;????????//查看一個表頭
- mysql> select name ,uid from tarena.user;????//查看多個表頭
- mysql> select * from tarena.user;????????//查看所有表頭
加篩選條件
- mysql> select * from tarena.user where name = “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 * from tarena.user where id = 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行的行號、用戶名、uid、gid 四個表頭的值
- mysql> select id,name,uid,gid from tarena.user where id = 3;
- +----+--------+------+------+
- | id | name | uid | gid |
- +----+--------+------+------+
- | 3 | daemon | 2 | 2 |
- +----+--------+------+------+
- 1 row in set (0.00 sec)
- //查看前2行的行號用戶名、uid、gid 四個表頭的值
- mysql> select id,name,uid,gid from tarena.user where id < 3;
- +----+------+------+------+
- | id | name | uid | gid |
- +----+------+------+------+
- | 1 | root | 0 | 0 |
- | 2 | bin | 1 | 1 |
- +----+------+------+------+
- 2 rows in set (0.00 sec)
- //查看前3行的行號、用戶名、uid、gid 四個表頭的值
- mysql> select id,name,uid,gid from tarena.user where id <= 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> select id,name,uid,gid from tarena.user where uid > 6000;
- +----+-----------+-------+-------+
- | id | name | uid | gid |
- +----+-----------+-------+-------+
- | 22 | nfsnobody | 65534 | 65534 |
- +----+-----------+-------+-------+
- 1 row in set (0.00 sec)
- //查看前uid號大于等于1000的行號、用戶名、uid、gid 四個表頭的值
- mysql> select id,name,uid,gid from tarena.user where uid >= 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> select id,name,uid,gid from tarena.user where uid = 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> select id,name,uid,gid from tarena.user where uid != 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>
步驟三:練習范圍匹配
in (值列表) //在…里
not in (值列表) //不在…里
between 數字1 and 數字2 //在…之間
命令操作如下所示:
- //uid號表頭的值 是 (1 , 3 , 5 , 7) 中的任意一個即可
- mysql> select name , uid from tarena.user where uid in (1 , 3 , 5 , 7);
- +------+------+
- | name | uid |
- +------+------+
- | bin | 1 |
- | adm | 3 |
- | sync | 5 |
- | halt | 7 |
- +------+------+
- //shell 表頭的的值 不是 "/bin/bash"或"/sbin/nologin" 即可
- mysql> select name , shell from tarena.user where shell not 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 , uid from tarena.user where id between 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 "表達式";
通配符
_ 表示 1個字符
% 表示零個或多個字符
命令操作如下所示:
- //找名字必須是3個字符的 (沒有空格挨著敲)
- mysql> select name from tarena.user where name like "___";
- +------+
- | name |
- +------+
- | bin |
- | adm |
- | ftp |
- | rpc |
- | plj |
- | bob |
- +------+
- 6 rows in set (0.00 sec)
- //找名字必須是4個字符的(沒有空格挨著敲)
- mysql> select name from tarena.user where name like "_ _ _ _";
- +------+
- | name |
- +------+
- | root |
- | sync |
- | halt |
- | mail |
- | dbus |
- | sshd |
- | null |
- +------+
- 7 rows in set (0.00 sec)
- //找名字以字母a開頭的(沒有空格挨著敲)
- mysql> select name from tarena.user where name like "a%";
- //查找名字至少是4個字符的表達式
- mysql> select name from tarena.user where name like "%_ _ _ _%";(沒有空格挨著敲)
- mysql> select name from tarena.user where name like "_ _%_ _";(沒有空格挨著敲)
- mysql> select name from tarena.user where name like "_ _ _ _%";(沒有空格挨著敲)
步驟五:練習正則匹配
格式:select 字段名列表 from 庫名.表名 where字段名 regexp '正則表達式';
回顧shell課程學過的元字符(正則符號)
^ 匹配行首
$ 匹配行尾
[] 匹配范圍內任意一個
* 前邊的表達式出現零次或多次
| 或者
. 任意一個字符
命令操作如下所示:
- //添加有數字的名字
- insert into tarena.user(name)values("yaya9");
- insert into tarena.user(name)values("6yaya");
- insert into tarena.user(name)values("ya7ya");
- insert into tarena.user(name)values("yay8a");
- //查看名字里有數字的
- mysql> select name from tarena.user where name regexp "[0-9]";
- +-------+
- | name |
- +-------+
- | yaya9 |
- | 6yaya |
- | ya7ya |
- | yay8a |
- +-------+
- 4 rows in set (0.00 sec)
- //查看名字以數字開頭
- mysql> select name from tarena.user where name regexp "^[0-9]";
- +-------+
- | name |
- +-------+
- | 6yaya |
- +-------+
- 1 row in set (0.00 sec)
- //查看名字以數字結尾
- mysql> select name from tarena.user where name regexp "[0-9]$";
- +-------+
- | name |
- +-------+
- | yaya9 |
- +-------+
- 1 row in set (0.00 sec)
- mysql>
- //查看名字以r開頭
- mysql> select name from tarena.user where name regexp "^r";
- +---------+
- | name |
- +---------+
- | root |
- | rpc |
- | rpcuser |
- +---------+
- 3 rows in set (0.00 sec)
- //查看名字以t結尾
- mysql> select name from tarena.user where name regexp "t$";
- +------+
- | name |
- +------+
- | root |
- | halt |
- +------+
- 2 rows in set (0.00 sec)
- mysql>
- //查看名字以r開頭或t結尾
- mysql> select name from tarena.user where name regexp "^r|t$";
- +---------+
- | name |
- +---------+
- | root |
- | halt |
- | rpc |
- | rpcuser |
- +---------+
- 4 rows in set (0.00 sec)
- //名字r開頭t結尾
- mysql> select name from tarena.user where name regexp "^r.*t$";
- +------+
- | name |
- +------+
- | root |
- +------+
- 1 row in set (0.00 sec)
- mysql>
步驟六:練習邏輯比較
多個判斷條件
邏輯與 and (&&) 多個判斷條件必須同時成立
邏輯或 or (||) 多個判斷條件其中某個條件成立即可
邏輯非 not (!) 取反
命令操作如下所示:
- //邏輯非例子,查看解釋器不是/bin/bash 的
- mysql> select name,shell from tarena.user where shell != "/bin/bash";
- //not 也是取反 要放在表達式的前邊
- mysql> select name,shell from tarena.user where not shell = "/bin/bash";
- //id值不在 10 到 20 之間
- mysql> select id , name from tarena.user where not id 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" or name = "bin" or uid = 1;
- +------+------+
- | name | uid |
- +------+------+
- | root | 0 |
- | bin | 1 |
- +------+------+
- mysql>
() 提高優先級
- mysql> select 2 + 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.user
- where 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.user
- where (name = "root" or name = "bin") and uid = 1 ;
- +------+------+
- | name | uid |
- +------+------+
- | bin | 1 |
- +------+------+
- 1 row in set (0.00 sec)
- mysql>
步驟七:練習字符比較/空/非空
符號兩邊必須是字符 或字符類型的表頭
= 相等比較
!= 不相等比較。
命令操作如下所示:
- //查看表里是否有名字叫apache的用戶
- mysql> select name from tarena.user where name="apache" ;
- +--------+
- | name |
- +--------+
- | apache |
- +--------+
- 1 row in set (0.00 sec)
- //輸出解釋器不是/bin/bash的用戶名 及使用的解釋器
- mysql> select name , shell from tarena.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 from tarena.user where id >= 71;
- +----+------+
- | id | name |
- +----+------+
- | 71 | |
- | 72 | null |
- | 73 | NULL |
- | 74 | NULL |
- +----+------+
- //查看name 表頭沒有數據的行 僅顯示行中id表頭 和 naeme 表頭的值
- mysql> select id , name from tarena.user where name is null;
- +----+------+
- | id | name |
- +----+------+
- | 28 | NULL |
- | 29 | NULL |
- | 73 | NULL |
- | 74 | NULL |
- +----+------+
- //查看name 表頭是0個字符的行, 僅顯示行中id表頭 和 naeme 表頭的值
- mysql> select id , name from tarena.user where name="";
- +----+------+
- | id | name |
- +----+------+
- | 71 | |
- +----+------+
- 1 row in set (0.00 sec)
- //查看name 表頭值是null的行, 僅顯示行中id表頭 和 naeme 表頭的值
- mysql> select id , name from tarena.user where name="null";
- +----+------+
- | id | name |
- +----+------+
- | 72 | null |
- +----+------+
- 1 row in set (0.00 sec)
- //查看name 表頭有數據的行, 僅顯示行中id表頭 和 name 表頭的值
- mysql> select id , name from tarena.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 , homedir from 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 |
- +--------------+
去重顯示 distinct 字段名列表
- //去重前輸出
- mysql> select shell from tarena.user where 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 shell from tarena.user where shell in ("/bin/bash","/sbin/nologin") ;
- +---------------+
- | shell |
- +---------------+
- | /bin/bash |
- | /sbin/nologin |
- +---------------+
- 2 rows in set (0.01 sec)
- mysql>