sql语句进阶
关于基本的sql语句使用，我放在了这篇博客，你们可以参考
https://blog.csdn.net/weixin_45308292/article/details/103613559
我这章只将进阶知识
如果你发现有些命令输入后无法使用，可能是mysql版本过低
使用如下命令，查看mysql的版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26 |
+-----------+
1 row in set (0.03 sec)
1）查看系统信息
关于那个%是模糊查询，我后面会详细讲，这里先这么打就行
mysql> show variables\G;
mysql> show global variables\G;
mysql> show global variables like "%version%";
如下命令，可以查看默认的存储引擎
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)
查看支持的存储引擎
mysql> show engines;
2.查看系统运行状态信息
如下查看mysql系统运行状态信息
信息过多，我这里省略了
mysql> show status;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0
...
**还有如下 **
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 2 |
| Threads_created | 2 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
如下，查看MySQL的进程连接数
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 2 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
解释一些这里
Id:mysql的进程id，每次都是随机的
User:登陆用户
Host:登陆位置，上面表示是本地登陆
db:当前用户位于的数据库,NULL表示没有在任何数据库中
Command: 表示命令，上图因为使用这条命令，是使用中，只有运行此命令时才为Query, 其他都是sleep
time:时间，在这里作用不大
state:状态，只有运行次命令式时，才为starting
info:执行的命令，只有运行此命令时才会有显示
这里解释一下为什么有的运行此命令才会显示，因为那些选项都是秒级检测运行的命令的，而这条命令本身也是一个命令，才会显示出来，而其他命令就不会了，
当然如果你还不理解，就可以过掉，作用不大
2)导出，导入数据库或其中的表mysqldup
使用mysql提供的mysqldump工具来导入导出数据库，可以实现数据库的备份和还原
1.导出指定数据库中所有表
语法如下
-p参数是mysql的用户密码,你们可能和我不一样
mysqldump -u 用户名 -p密码 数据库名 > 导出的文件名
先随意创建一个数据库，并在其中随意创建一个’up’表,如下
mysql> create database backup;
Query OK, 1 row affected (0.00 sec)
mysql> use backup;
Database changed
mysql> create table up(id int,name char(10),age int);
Query OK, 0 rows affected (0.13 sec)
mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| up |
+------------------+
1 row in set (0.00 sec)
导出backup库中的所有表，如下，在linux命令行界面
[root@localhost ~]# mysqldump -u root backup>backup.sql -p123456
mysqldump: [Warning] Using a password on the command line interface can be insecure.
(下面的这个不是报错，但是如果输出的不是这个信息，说明没有导出成功）
默认导出文件，在当前目录
[root@localhost ~]# ls | grep backup.sql
backup.sql
被导出的表与库，原来的数据不会发生任何变化，
2.导入数据库中的表
方法1
这种导出方式不可以导出数据库，而是导出其中的表
所以还需要新创建一个新的数据库backup1，将刚刚的表导入到其中
[root@localhost ~]# mysql -u root -e 'create database backup1' -p123456
（下面的不是报错）
mysql: [Warning] Using a password on the command line interface can be insecure.
将导出的backup数据库中的所有表导入到backup1数据库中，如下
[root@localhost ~]# mysql -u root -p123456 backup1<backup.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
已经导入进来了,如下
mysql> use backup1;
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_backup1 |
+-------------------+
| up |
+-------------------+
1 row in set (0.00 sec)
这种方式，密码被暴露在终端上，不是很安全，普遍用在脚本当中
方法2
这种方法，比较安全，适用于手动还原
需要指定，刚刚导出的文件的路径，我的在/root/backup.sql
mysql> create database backup3;
Query OK, 1 row affected (0.00 sec)
mysql> use backup3;
Database changed
mysql> source /root/backup.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
...
mysql> show tables;
+-------------------+
| Tables_in_backup3 |
+-------------------+
| up |
+-------------------+
1 row in set (0.00 sec)
如上已经导入进来了
3.导出指定数据库，包含其中的所有表
语法如下 -p后面跟的是mysql的root用户密码
mysqldump -uroot -p123456 -B 库名>文件.sql
导出bakcup数据库，包括其中的所有表
[root@localhost ~]# mysqldump -uroot -p123456 -B backup>backupdate.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
恢复指定数据库
首先删除刚刚的backup数据库
mysql> drop database backup;
Query OK, 1 row affected (0.02 sec)
这里就和上面不一样了，直接恢复就行，不需要创建一个额外的数据库
恢复的数据库还是叫backup,还是指定导出文件的位置
mysql> source /root/backupdate.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
...
mysql> use backup;
Database changed
mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| up |
+------------------+
1 row in set (0.00 sec)
3)把一个select的结果导出到文本
在数据backup表中创建一些数据
mysql> use backup;
Database changed
mysql> insert into up values(1,'zhangsan',10),(2,'lisi',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from up;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 10 |
| 2 | lisi | 20 |
+------+----------+------+
2 rows in set (0.00 sec)
语法如下
select * into outfile '/tmp/123.txt' from bakcup.up;
(此处有个文件权限问题，因为导出使用的系统的mysql用户，需要mysql用户对指定目录有写入权限，避免麻烦，本例直接使用/tmp目录,上面mysqldump因为是一个工具所以没有这个问题)
实施
将backup数据库下up表的数据导出到/tmp目录下
直接导出会有问题如下
mysql> select * into outfile '/tmp/123.txt' from backup.up;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
新版本的5.7的会报错，是为了安全性，不让随意导出
是因为secure-file-prive这个选项规定了导出的目录
查看规则如下,注意看最后一行
mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | NULL |
+--------------------------+-------+
3 rows in set (0.00 sec)
secure_file_priv 为NULL时，不允许导入或导出数据记录
secure_file_prive 为 /tmp时，表示只能在/tmp目录中执行导入，导出操作， 其他目录不行
secure_file_priv 没有值时，表示不限制在任意目录的导出导入
解决方法如下
在配置文件末尾加入以下语句后重启mysql
[root@localhost ~]# vim /etc/my.cnf
secure_file_priv='/tmp'
[root@localhost ~]# systemctl restart mysqld
再次查看已经改变了，最后一行
mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /tmp/ |
+--------------------------+-------+
3 rows in set (0.00 sec)
再次执行
mysql> select * into outfile '/tmp/123.txt' from backup.up;
Query OK, 2 rows affected (0.00 sec)
查看已经导出了
[root@localhost ~]# cat /tmp/123.txt
1 zhangsan 10
2 lisi 20
高级进阶
1)导入我提供的senior-sql表文件
后面的实验都是基于我提供的这个文件的
百度云如下，拖到linux中
链接：https://pan.baidu.com/s/1_Eud5UN05YP_nAS6oJmM7A
提取码：64dp
先创建一个额外的数据库senior
mysql> create database senior;
Query OK, 1 row affected (0.00 sec)
把我提供的文件托入linux中，并导入
[root@localhost ~]# mysql -uroot -p123456 senior<senior.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
应该有如下两张表
mysql> use senior;
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_senior |
+------------------+
| books |
| category |
+------------------+
2 rows in set (0.00 sec)
2）分别查看两种表的字段类型与内容
首先字段
mysql> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId | int(4) | NO | PRI | NULL | auto_increment |
| bName | varchar(255) | YES | | NULL | |
| bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | YES | | NULL | |
| publishing | varchar(255) | YES | | NULL | |
| price | int(4) | YES | | NULL | |
| pubDate | date | YES | | NULL | |
| author | varchar(30) | YES | | NULL | |
| ISBN | varchar(255) | YES | | NULL | |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> desc category;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| bTypeId | int(4) | NO | PRI | NULL | auto_increment |
| bTypeName | varchar(40) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
然后是内容,第二张表内容过多，我这里省略了
mysql> select * from category;
+---------+---------------+
| bTypeId | bTypeName |
+---------+---------------+
| 1 | windows应用 |
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
| 9 | 平面 |
| 10 | AutoCAD技术 |
+---------+---------------+
10 rows in set (0.00 sec)
mysql> select * from books;
+-----+---------------------------------------------------------+---------+-----------------------------------+-------+------------+-----------+-------------+
| bId | bName | bTypeId | publishing | price | pubDate | author | ISBN |
+-----+---------------------------------------------------------+---------+-----------------------------------+-------+------------+-----------+-------------+
| 1 | 网站制作直通车 | 2 | 电脑爱好者杂志社 | 34 | 2004-10-01 | 苗壮 | 7505380796 |
| 2 | 黑客与网络安全 | 6 | 航空工业出版社 | 41 | 2002-07-01 | 白立超 | 7121010925 |
| 3 | 网络程序与设计－asp | 2 | 北方交通大学出版社 | 43 | 2005-02-01 | 王玥 | 75053815x |
| 4 | pagemaker 7.0短期培训教程 | 9 | 中国电力出版社 | 43 | 2005-01-01 | 孙利英 | 7121008947 |
| 5 | 黑客攻击防范秘笈 | 6 | 北京腾图电子出版社 | 44 | 2003-06-29 | 赵雷雨 | 7120000233 |
| 6 | Dreamweaver 4入门与提高 | 2 | 清华大学出版社 | 44 | 2004-06-01 | 岳玉博 | 7505397699 |
| 7 | 网页样式设计－CSS | 2 | 人民邮电出版社 | 45 | 2002-03-01 | 张晓阳 | 7505383663 |
| 8 | Internet操作技术 | 7 | 清华大学出版社 | 45 | 2002-02-01 | 肖铭 | 7121003023 |
| 9 | Dreamweaver 4网页制作 | 2 | 清华大学出版社 | 45 | 2004-04-01 | 黄宇 | 7505380796 |
| 10 | 3D MAX 3.0 创作效果百例 | 3 | 北京万水电子信息出版社 | 45 | 2002-09-01 | 耿影 | 7505380796 |
| 11 | Auto CAD职业技能培训教程 | 10 | 北京希望电子出版社 | 47 | 2004-06-01 | 张晓阳 | 7505380796 |
| 12 | Fireworks 4网页图形制作 | 2 | 清华大学出版社 | 48 | 2004-04-01 | 白立超 | 7505380796 |
| 13 | 自己动手建立企业局域网 | 8 | 清华大学出版社 | 48 | 2003-08-30 | 郭刚 | 7505380796 |
| 14 | 页面特效精彩实例制作 | 2 | 人民邮电出版社 | 49 | 2004-09-01 | 白宇 | 7505380796 |
| 15 | 平面设计制作整合案例详解－页面设计卷 | 2 | 人民邮电出版社 | 49 | 2004-04-01 | 陈继云 | 7505380796 |
...
44 rows in set (0.00 sec)
上面这张表内容过多，查看的时候可以加\G,如下
mysql> select * from books\G;
3)逻辑运算符
and ,or,not
and 且
or 或
not 非
选择出数据价格为(50,60,104)的书籍，显示书籍名称(bName)，价格(price),出版社(publishing)
mysq不区分大小写，可以写大写，or是或者的意思
mysql> select bname,publishing,price from books where price=50 or price=60 or price=104;
+--------------------------------------+--------------------------+-------+
| bname | publishing | price |
+--------------------------------------+--------------------------+-------+
| Illustrator 10完全手册 | 科学出版社 | 50 |
| FreeHand 10基础教程 | 北京希望电子出版 | 50 |
| 网站设计全程教程 | 科学出版社 | 50 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
| Delphi 5程序设计与控件参考 | 电子工业出版社 | 60 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
| ASP 3初级教程 | 机械工业出版社 | 104 |
| XML 完全探索 | 中国青年出版社 | 104 |
+--------------------------------------+--------------------------+-------+
8 rows in set (0.00 sec)
4)算术运算符
= :等于
<>或者!= :不等于
>:大于
< :小于
>= :大于等于
<= :小于等于
1. 找出价格大于100的书籍
mysql> select bname,price from books where price>100;
+----------------------------------------+-------+
| bname | price |
+----------------------------------------+-------+
| Javascript与Jscript从入门到精通 | 7500 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
+----------------------------------------+-------+
3 rows in set (0.00 sec)
2.找出价格等于104的书籍
mysql> select bname,price from books where price=104;
+-------------------+-------+
| bname | price |
+-------------------+-------+
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
+-------------------+-------+
2 rows in set (0.00 sec)
3.找出价格不等于100的书籍
数据较多，我省略了
mysql> select bname,price from books where price!=100;
+---------------------------------------------------------+-------+
| bname | price |
+---------------------------------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
| pagemaker 7.0短期培训教程 | 43 |
| 黑客攻击防范秘笈 | 44 |
| Dreamweaver 4入门与提高 | 44 |
| 网页样式设计－CSS | 45 |
| Internet操作技术 | 45 |
| Dreamweaver 4网页制作 | 45 |
| 3D MAX 3.0 创作效果百例 | 45 |
| Auto CAD职业技能培训教程 | 47 |
| Fireworks 4网页图形制作 | 48 |
...
5)in运算符
in 运算符用于 where表达式中，已列表的形式支持多个选项，语法如下:
where column in (value1,value2,...)
where column not in (value1,value2,...)
not in 表示与in相反的意思，即不在这些列表项内选择
1.找出价格是 50，60，104的记录
mysql> select bname,price from books where price in (60,50,104);
+--------------------------------------+-------+
| bname | price |
+--------------------------------------+-------+
| Illustrator 10完全手册 | 50 |
| FreeHand 10基础教程 | 50 |
| 网站设计全程教程 | 50 |
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
+--------------------------------------+-------+
8 rows in set (0.00 sec)
2.找出价格不是 50，60，104的记录
数据较多，我省略了
mysql> select bname,price from books where price not in (60,50,104);
+---------------------------------------------------------+-------+
| bname | price |
+---------------------------------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
| pagemaker 7.0短期培训教程 | 43 |
| 黑客攻击防范秘笈 | 44 |
| Dreamweaver 4入门与提高 | 44 |
| 网页样式设计－CSS | 45 |
| Internet操作技术 | 45 |
| Dreamweaver 4网页制作 | 45 |
| 3D MAX 3.0 创作效果百例 | 45 |
| Auto CAD职业技能培训教程 | 47 |
| Fireworks 4网页图形制作 | 48 |
| 自己动手建立企业局域网 | 48 |
| 页面特效精彩实例制作 | 49 |
...
36 rows in set (0.00 sec)
6)排序
1.升序与降序
语法如下
升序: orderby "排序的字段" asc 默认
降序: orderby "排序的字段" desc
如下以升序排序 price字段 从小到大asc
mysql> select bname,price from books where price in (50,60,104) order by price asc;
+--------------------------------------+-------+
| bname | price |
+--------------------------------------+-------+
| Illustrator 10完全手册 | 50 |
| FreeHand 10基础教程 | 50 |
| 网站设计全程教程 | 50 |
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
+--------------------------------------+-------+
8 rows in set (0.00 sec)
如下以降序排列price字段，从大到小desc
mysql> select bname,price from books where price in (50,60,104) order by price desc;
+--------------------------------------+-------+
| bname | price |
+--------------------------------------+-------+
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |
| Illustrator 10完全手册 | 50 |
| FreeHand 10基础教程 | 50 |
| 网站设计全程教程 | 50 |
+--------------------------------------+-------+
8 rows in set (0.00 sec)
2.多个字段排序
因为 price字段在前面，所以先会以降序排列price字段，然后再以降序排列bname字段
意思就是，右边的price字段会先被排序，不会动了
而如，3个同样的price值50，把右边的bname字段排序，规则如下
中文字最大，其次降序A B C往后排，然后降序小写abc往后排
注意会先排右边，只有左边三个数值相同时，才会用如上规则排序
因为price字段在前面，先排列前面的字段
ysql> select bname,price from books where price in (50,60,104) order by price asc,bname asc;
+--------------------------------------+-------+
| bname | price |
+--------------------------------------+-------+
| FreeHand 10基础教程 | 50 |
| Illustrator 10完全手册 | 50 |
| 网站设计全程教程 | 50 |
| ASP数据库系统开发实例导航 | 60 |
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
+--------------------------------------+-------+
8 rows in set (0.00 sec)
如下可以是单字段排序，可以对比一下
mysql> select bname,price from books where price in (50,60,104) order by price asc;
+--------------------------------------+-------+
| bname | price |
+--------------------------------------+-------+
| Illustrator 10完全手册 | 50 |
| FreeHand 10基础教程 | 50 |
| 网站设计全程教程 | 50 |
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
+--------------------------------------+-------+
8 rows in set (0.00 sec)
7) 范围运算
语法如下
[not] between ...and...
查找价格不在 10 到70之间的书籍，显示书籍名称与价格并以降序排列
不包括10于70
mysql> select bname,price from books where price not between 10 and 60 order by price desc;
+---------------------------------------------------------+-------+
| bname | price |
+---------------------------------------------------------+-------+
| Javascript与Jscript从入门到精通 | 7500 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
| SQL Server 7.0数据库系统管理与应用开发 | 95 |
| SQL Server 2000 从入门到精通 | 93 |
| 3D Studio Max 3综合使用 | 91 |
| lllustrator 9宝典 | 83 |
| 3D MAX R3动画制作与培训教程 | 73 |
| HTML设计实务 | 72 |
| Frontpage 2000＆ ASP 网页设计技巧与网站维护 | 71 |
| 深入Flash 5教程 | 64 |
| Auto CAD R14 中文版实用教程 | 64 |
| Auto CAD 2002 中文版实用教程 | 63 |
| 3DS MAX 4横空出世 | 63 |
| 精通Javascript | 63 |
| 活学活用Delphi5 | 62 |
+---------------------------------------------------------+-------+
16 rows in set (0.00 sec)
去掉前面的not表示去在哪个范围内的
如下查找在价格在30到45之间的书，并以降序排列
mysql> select bname,price from books where price between 30 and 45 order by price desc;
+---------------------------------+-------+
| bname | price |
+---------------------------------+-------+
| 网页样式设计－CSS | 45 |
| Internet操作技术 | 45 |
| Dreamweaver 4网页制作 | 45 |
| 3D MAX 3.0 创作效果百例 | 45 |
| 黑客攻击防范秘笈 | 44 |
| Dreamweaver 4入门与提高 | 44 |
| 网络程序与设计－asp | 43 |
| pagemaker 7.0短期培训教程 | 43 |
| 黑客与网络安全 | 41 |
| 网站制作直通车 | 34 |
+---------------------------------+-------+
10 rows in set (0.00 sec)
也可以使用符号显示，效果一样，如查询价格不在40 至100的书
不包括40和100
mysql> select bname,price from books where price <40 or price >100 order by price desc;
+----------------------------------------+-------+
| bname | price |
+----------------------------------------+-------+
| Javascript与Jscript从入门到精通 | 7500 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
| 网站制作直通车 | 34 |
+----------------------------------------+-------+
4 rows in set (0.00 sec)
8）模糊匹配查询
语法如下
字段名 [not]like '通配符' --->>% 任意多个字符
要' '号包裹
1.查找带有某关键词或者其他
如下查找书籍名称带有 ’黑客‘ 的书籍，并打印出书籍的名称
使用% %包裹
mysql> select bname from books where bname like '%黑客%';
+--------------------------+
| bname |
+--------------------------+
| 黑客与网络安全 |
| 黑客攻击防范秘笈 |
+--------------------------+
2 rows in set (0.00 sec)
2.查找以什么开头的
如下查找已ASP开头的书籍，并输出
在%前面加想要加的
mysql> select bname from books where bname like 'ASP%';
+--------------------------------------+
| bname |
+--------------------------------------+
| ASP数据库系统开发实例导航 |
| ASP数据库系统开发实例导航 |
| ASP 3初级教程 |
+--------------------------------------+
3 rows in set (0.00 sec)
mysql>
3.查找以什么结尾的
如下查找以 制作,结尾的书籍
在%后面添加即可
mysql> select bname from books where bname like '%制作';
+--------------------------------+
| bname |
+--------------------------------+
| Dreamweaver 4网页制作 |
| Fireworks 4网页图形制作 |
| 页面特效精彩实例制作 |
+--------------------------------+
3 rows in set (0.00 sec)
9) mysql子查询
在 select的where条件中有出现了select查询，查询中嵌套着查询，这就是子查询
刚刚上面有两种表，这两张表其实是有关联的
第一张表
记录类型id对应什么书籍类型，
而另二张表也有这个字段，这就是其中的关系，如下
mysql> select * from category;
+---------+---------------+
| bTypeId | bTypeName |
+---------+---------------+
| 1 | windows应用 |
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
| 9 | 平面 |
| 10 | AutoCAD技术 |
+---------+---------------+
mysql> select btypeid from books;
+---------+
| btypeid |
+---------+
| 2 |
| 6 |
| 2 |
| 9 |
| 6 |
| 2 |
| 2 |
| 7 |
| 2 |
| 3 |
| 10 |
| 2 |
| 8 |
...
44 rows in set (0.00 sec)
1.查询类型名称是"平面" 的图书
1) 普通查询方式
先查寻category表中网站 技术类型的 btypeid
mysql> select btypeid from category where btypename="平面";
+---------+
| btypeid |
+---------+
| 9 |
+---------+
1 row in set (0.00 sec)
再在books表中查看 类型id（btypeid）为 9的 是哪本书
mysql> select bname,btypeid from books where btypeid=9;
+---------------------------------+---------+
| bname | btypeid |
+---------------------------------+---------+
| pagemaker 7.0短期培训教程 | 9 |
| Illustrator 10完全手册 | 9 |
| FreeHand 10基础教程 | 9 |
| lllustrator 9宝典 | 9 |
+---------------------------------+---------+
4 rows in set (0.00 sec)
2)嵌套查询
就是把两个select语句结合起来
把后面括号中select查询结果，作为外面的select查询条件
mysql> select bname,btypeid from books where btypeid=(select btypeid from category where btypename="平面");
+---------------------------------+---------+
| bname | btypeid |
+---------------------------------+---------+
| pagemaker 7.0短期培训教程 | 9 |
| Illustrator 10完全手册 | 9 |
| FreeHand 10基础教程 | 9 |
| lllustrator 9宝典 | 9 |
+---------------------------------+---------+
4 rows in set (0.00 sec)
结果和上面普通查询的，结果一致
3)查询类型名称为“安全”的书籍
mysql> select bname,btypeid from books where btypeid=(select btypeid from category where btypename="安全");
+-----------------------------------+---------+
| bname | btypeid |
+-----------------------------------+---------+
| 自己动手建立企业局域网 | 8 |
+-----------------------------------+---------+
1 row in set (0.00 sec)
10) limt限定显示的条目
语法如下
select * from table limit [offset,] rows;
偏移量 行数
例如，如下2就是偏移量，7就是行数
mysql> select * from category limit 2,7;
可以这么计算，把 2加上1是3，而要显示的可以用偏移量2加上后面的行数7，
然后显示的就是 3到9行
如下
mysql> select * from category limit 2,7;
+---------+--------------+
| bTypeId | bTypeName |
+---------+--------------+
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
| 9 | 平面 |
+---------+--------------+
7 rows in set (0.00 sec)
注意这个表当中一共只有10个字段，就是10行
1)如查询category表中3行到第8行的记录
可以这样计算
前面的3，先减去1 ，是2，2就是偏移量
让后用后面的到8行减去上面的2，就是6 ，6就是行数
如下这么写
mysql> select * from category limit 2,6;
+---------+--------------+
| bTypeId | bTypeName |
+---------+--------------+
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
+---------+--------------+
6 rows in set (0.00 sec)
2.不加偏移量查询
可以不加前面的偏移量，后面的行数填几就显示前几行
如下，填5，就是输出前5行
mysql> select * from category limit 5;
+---------+---------------+
| bTypeId | bTypeName |
+---------+---------------+
| 1 | windows应用 |
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
+---------+---------------+
5 rows in set (0.00 sec)
3.查看书籍中价格最低的三条记录
可以结合上面的排序，升序就是从小到大排序，再提前三行
mysql> select bname,price from books order by price asc limit 3;
+-----------------------------+-------+
| bname | price |
+-----------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
+-----------------------------+-------+
3 rows in set (0.00 sec)
11)将子查询和限制条目，算术运算结合起来查询
需求：找出价格比“人民邮电出版社” 最贵，还要贵的图书
1.先查询出人们邮电出版社最贵的书
mysql> select bname,price from books where publishing="人民邮电出版社" order by price desc limit 1 ;
+----------------------------------------------------+-------+
| bname | price |
+----------------------------------------------------+-------+
| SQL Server 7.0数据库系统管理与应用开发 | 95 |
+----------------------------------------------------+-------+
1 row in set (0.00 sec)
2.使用嵌套子查询计算
mysql> select bname,price from books where price>(select price from books where publishing="人民邮电出版社" order by price desc limit 1);
+----------------------------------------+-------+
| bname | price |
+----------------------------------------+-------+
| Javascript与Jscript从入门到精通 | 7500 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
+----------------------------------------+-------+
3 rows in set (0.00 sec)
12）多表连接查询
以一个共同的字段，求两种表当中符合条件的并集。
通过共同字段把这两种表连接起来
常用的连接
内连接，
外连接：左外连接，右外连接
简单的说就是，两个表中的字段拥有相同的值，字段名可随意，字段类型要一致，
可以通过这个条件，使两个表来进行连接
1.内连接
语法：
select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段
内连接的特点是，如果有不匹配的值，则左右的值（就是两种表相同的值），就都不会显示出来
使用方法如下，显示内容过多，我有省略
如下右面的btypename是另一张表的，现在显示在一起了，这就是多表查询
mysql> select books.bname,books.price,category.btypename from books inner join category on books.btypeid=category.btypeid;
+---------------------------------------------------------+-------+---------------+
| bname | price | btypename |
+---------------------------------------------------------+-------+---------------+
| 网站制作直通车 | 134 | 网站 |
| 黑客与网络安全 | 41 | 黑客 |
| 网络程序与设计－asp | 43 | 网站 |
| pagemaker 7.0短期培训教程 | 43 | 平面 |
| 黑客攻击防范秘笈 | 44 | 黑客 |
| Dreamweaver 4入门与提高 | 44 | 网站 |
| 网页样式设计－CSS | 45 | 网站 |
| Internet操作技术 | 45 | 网络技术 |
| Dreamweaver 4网页制作 | 45 | 网站 |
| 3D MAX 3.0 创作效果百例 | 45 | 3D动画 |
| Auto CAD职业技能培训教程 | 47 | AutoCAD技术 |
| Fireworks 4网页图形制作 | 48 | 网站
44 rows in set (0.00 sec)
...
还可以使用给表起一个别名的方式，这样更简单，在下面的sql语句中如下这是起别名的意思，不用单独执行，仅限在这个sql语句使用
books f #给books表起一个别名叫f
category t #给category表起一个别名叫t
mysql> select f.bname,f.price,t.btypename from books f inner join category t on f.btypeid=t.btypeid;
+---------------------------------------------------------+-------+---------------+
| bname | price | btypename |
+---------------------------------------------------------+-------+---------------+
| 网站制作直通车 | 134 | 网站 |
| 黑客与网络安全 | 41 | 黑客 |
| 网络程序与设计－asp | 43 | 网站 |
| pagemaker 7.0短期培训教程 | 43 | 平面 |
| 黑客攻击防范秘笈 | 44 | 黑客 |
| Dreamweaver 4入门与提高 | 44 | 网站 |
| 网页样式设计－CSS | 45 | 网站 |
| Internet操作技术 | 45 | 网络技术 |
| Dreamweaver 4网页制作 | 45 | 网站 |
| 3D MAX 3.0 创作效果百例 | 45 | 3D动画 |
| Auto CAD职业技能培训教程 | 47 | AutoCAD技术 |
| Fireworks 4网页图形制作 | 48 | 网站 |
| 自己动手建立企业局域网 | 48 | 安全
...
也可以使用where的方式实现内连接
mysql> select f.bname,f.price,t.btypename from books f inner join category t where f.btypeid=t.btypeid;
+---------------------------------------------------------+-------+---------------+
| bname | price | btypename |
+---------------------------------------------------------+-------+---------------+
| 网站制作直通车 | 134 | 网站 |
| 黑客与网络安全 | 41 | 黑客 |
| 网络程序与设计－asp | 43 | 网站 |
| pagemaker 7.0短期培训教程 | 43 | 平面 |
| 黑客攻击防范秘笈 | 44 | 黑客 |
| Dreamweaver 4入门与提高 | 44 | 网站 |
| 网页样式设计－CSS | 45 | 网站 |
...
2.左连接
语法如下
select 字段 from f 表 left join b表 on 连接条件
f (books)表是主表，有连接的所有的字段都会显示
t (category)表是从表，有连接的没有的内容会显示null
意思就是有相同（有连接）的部分，如果主表有，而从表没有，从表会显示成null
首先先删除从表的几个字段
mysql> delete from category where btypeid in (1,2);
Query OK, 2 rows affected (0.04 sec)
mysql> select * from category;
+---------+---------------+
| bTypeId | bTypeName |
+---------+---------------+
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
| 9 | 平面 |
| 10 | AutoCAD技术 |
+---------+---------------+
8 rows in set (0.00 sec)
进行左连接
会发现右边会有很多 NULL，这是因为我删除了category表的两个值（1，2）， 导致左边表和右边的表数值对不上,左边数值会多出（对应右边1，2值的）又是主表，因为右边我删了两个 然后右边原来的（1，2）数值就会显示 NULL
mysql> select f.bname,f.price,t.btypename from books f left join category t on f.btypeid=t.btypeid;
+---------------------------------------------------------+-------+---------------+
| bname | price | btypename |
+---------------------------------------------------------+-------+---------------+
| 网站制作直通车 | 134 | NULL |
| 黑客与网络安全 | 41 | 黑客 |
| 网络程序与设计－asp | 43 | NULL |
| pagemaker 7.0短期培训教程 | 43 | 平面 |
| 黑客攻击防范秘笈 | 44 | 黑客 |
| Dreamweaver 4入门与提高 | 44 | NULL |
| 网页样式设计－CSS | 45 | NULL |
| Internet操作技术 | 45 | 网络技术 |
| Dreamweaver 4网页制作 | 45 | NULL |
| 3D MAX 3.0 创作效果百例 | 45 | 3D动画 |
| Auto CAD职业技能培训教程 | 47 | AutoCAD技术 |
| Fireworks 4网页图形制作 | 48 | NULL |
| 自己动手建立企业局域网 | 48 | 安全 |
| 页面特效精彩实例制作 | 49 | NULL
...
3.右连接
select 字段 from f表 rght join t表 on 连接条件
f是从表，t是主表都显示
这次删除f（books）表中的，让左边（books）显示NULL，让右边t表（category）全部显示
mysql> delete from books where btypeid in (4,5);
Query OK, 3 rows affected (0.00 sec)
使用右连接，注意看下面最后两个记录，books表的bname字段为null了，这就是右连接
mysql> select f.bname,t.* from books f right join category t on f.btypeid=t.btypeid;
+----------------------------------------+---------+---------------+
| bname | bTypeId | bTypeName |
+----------------------------------------+---------+---------------+
| 黑客与网络安全 | 6 | 黑客 |
| pagemaker 7.0短期培训教程 | 9 | 平面 |
| 黑客攻击防范秘笈 | 6 | 黑客 |
| Internet操作技术 | 7 | 网络技术 |
| 3D MAX 3.0 创作效果百例 | 3 | 3D动画 |
| Auto CAD职业技能培训教程 | 10 | AutoCAD技术 |
| 自己动手建立企业局域网 | 8 | 安全 |
| Illustrator 10完全手册 | 9 | 平面 |
| FreeHand 10基础教程 | 9 | 平面 |
| Auto CAD 3D模型大师 | 10 | AutoCAD技术 |
| Auto CAD 2000 应用及实例基集锦 | 10 | AutoCAD技术 |
| 3DS MAX 4横空出世 | 3 | 3D动画 |
| Auto CAD R14 中文版实用教程 | 10 | AutoCAD技术 |
| 3D MAX R3动画制作与培训教程 | 3 | 3D动画 |
| lllustrator 9宝典 | 9 | 平面 |
| 3D Studio Max 3综合使用 | 3 | 3D动画 |
| NULL | 4 | linux学习 |
| NULL | 5 | Delphi学习 |
+----------------------------------------+---------+---------------+
18 rows in set (0.00 sec)
聚合函数
函数：执行特定功能的代码块
因为上面删掉了，好多数据，从新创建一个数据库，并导入我提供的那个文件
mysql> create database ansible;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123456 ansible<senior.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use ansible;
mysql> show tables;
+-------------------+
| Tables_in_ansible |
+-------------------+
| books |
| category |
+-------------------+
2 rows in set (0.00 sec)
1.算数运算函数
1）sum()求和
求所有图书的总价，price字段是图示的价格，算这个字段的和的值即可
mysql> select sum(price)from books;
+------------+
| sum(price) |
+------------+
| 10048 |
+------------+
1 row in set (0.00 sec)
可以看到上面 sum(price)不太友好，可以用as，显示成自己显示的
mysql> select sum(price) as 总价格 from books;
+-----------+
| 总价格 |
+-----------+
| 10048 |
+-----------+
1 row in set (0.00 sec)
2) age()平均值
求书籍id大于10的所有书籍的平均价格
mysql> select avg(price) as 平均价 from books where bid>10;
+-----------+
| 平均价 |
+-----------+
| 282.9118 |
+-----------+
1 row in set (0.00 sec)
3) max()最大值
求所有图书中价格最贵的书
mysql> select bname as 名字,price as 价钱 from books where price=(select max(price)from books);
+----------------------------------------+--------+
| 名字 | 价钱 |
+----------------------------------------+--------+
| Javascript与Jscript从入门到精通 | 7500 |
+----------------------------------------+--------+
1 row in set (0.00 sec)
4) min()最小值
mysql> select bname as 名字,price as 价钱 from books where price=(select min(price) from books);
+-----------------------+--------+
| 名字 | 价钱 |
+-----------------------+--------+
| 网站制作直通车 | 34 |
+-----------------------+--------+
1 row in set (0.00 sec)
5) count()统计
统计价格大于60的书籍数量
就是记录行数，因为每行一个书籍名称，就是记录书籍数量了
mysql> select count(*) as 有哪些 from books where price>60;
+-----------+
| 有哪些 |
+-----------+
| 16 |
+-----------+
1 row in set (0.00 sec)
然后通过去重distinct，去重操作，可以记录大于60价格上，有几个不同价位的书，其实也是记录行数，几行就是几个价位，因为去重了，价格都是不同的
mysql> select count(distinct price) as 还有如下个价位 from books where price>60;
+-----------------------+
| 还有如下个价位 |
+-----------------------+
| 12 |
+-----------------------+
1 row in set (0.00 sec)
2.算术运算
+ - * /
例如给所有价格等于60的书籍，降价20元
首先查看有哪些书籍等于60元
mysql> select bname,price from books where price=60;
+--------------------------------------+-------+
| bname | price |
+--------------------------------------+-------+
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |
+--------------------------------------+-------+
3 rows in set (0.00 sec)
降价20元操作，再次查看40元的书籍
mysql> update books set price=price-20 where price=60;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select bname,price from books where price=40;
+--------------------------------------+-------+
| bname | price |
+--------------------------------------+-------+
| ASP数据库系统开发实例导航 | 40 |
| Delphi 5程序设计与控件参考 | 40 |
| ASP数据库系统开发实例导航 | 40 |
+--------------------------------------+-------+
3 rows in set (0.00 sec)
也可进行打折操作，例如给价格大于100的书籍打折%80
mysql> update books set price=price*0.8 where price>100;
3.字符串函数
1)substr(string,start,len)截取
这种截取不会改变原来的值，只是在select输出的时候截取
从start开始，截取len长，start从1开始算起
先正常查看一会要截取输出的字段
mysql> select btypename from category;
+---------------+
| btypename |
+---------------+
| windows应用 |
| 网站 |
| 3D动画 |
| linux学习 |
| Delphi学习 |
| 黑客 |
| 网络技术 |
| 安全 |
| 平面 |
| AutoCAD技术 |
+---------------+
10 rows in set (0.00 sec)
截取输出，如下的意思是，前面的1是截取第1个字符，2就是截取第二个字符，加上第一个字符往后截取3个字符，然后加上前面截取的第一个字符和后面的3个字符就是4个字符，后面4就是这个意思，
不管是字母还是中文均占一个字符
mysql> select substr(btypename,1,4) from category;
+-----------------------+
| substr(btypename,1,4) |
+-----------------------+
| wind |
| 网站 |
| 3D动画 |
| linu |
| Delp |
| 黑客 |
| 网络技术 |
| 安全 |
| 平面 |
| Auto |
+-----------------------+
10 rows in set (0.00 sec)
如下截取第2到第4个字符,应该显示3个字符，有些不是3个字符，是它字符原来不够长
mysql> select substr(btypename,2,3) from category;
+-----------------------+
| substr(btypename,2,3) |
+-----------------------+
| ind |
| 站 |
| D动画 |
| inu |
| elp |
| 客 |
| 络技术 |
| 全 |
| 面 |
| uto |
+-----------------------+
10 rows in set (0.00 sec)
2.conat(str1,st2,str3....)拼接
把多个字段拼接一个字段输出
把书的名称和书的价格，拼接输出
如下' '包裹的就是中间要拼接的，前后要有逗号
mysql> select concat(bname,'----书的价格是',price) as 书对应的价格是 from books;
+-------------------------------------------------------------------------+
| 书对应的价格是 |
+-------------------------------------------------------------------------+
| 黑客与网络安全----书的价格是41 |
| pagemaker 7.0短期培训教程----书的价格是43 |
| 黑客攻击防范秘笈----书的价格是44 |
| Internet操作技术----书的价格是45 |
| Auto CAD职业技能培训教程----书的价格是47 |
| 自己动手建立企业局域网----书的价格是48 |
| Illustrator 10完全手册----书的价格是50 |
| FreeHand 10基础教程----书的价格是50 |
| Auto CAD 3D模型大师----书的价格是53 |
| Linux傻瓜书----书的价格是54 |
| Auto CAD 2000 应用及实例基集锦----书的价格是58 |
...
4.输出大小写转换
原本数据不会变，只是select查询的时候变了
1) upper()转为大写输出
mysql> select upper(bname) from books where bid=4;;
+---------------------------------+
| upper(bname) |
+---------------------------------+
| PAGEMAKER 7.0短期培训教程 |
+---------------------------------+
1 row in set (0.00 sec)
2) lower()输出为小写
mysql> select lower(bname) from books where bid=4;;
+---------------------------------+
| lower(bname) |
+---------------------------------+
| pagemaker 7.0短期培训教程 |
+---------------------------------+
1 row in set (0.00 sec)
200
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
