第十三章 MySQL-SQL语句进阶

本节所讲内容：
13.1 数据类型
13.2 常用select命令
13.3 导出、导入数据库
13.4 SQL查询语句进阶

以下使用Yum方式快速安装MySQL 5.7 版本（如果已经安装则不需求重复安装）：
提前下载好mysql的rpm包压缩并上传到Linux主机上，并解压：
注：mysql-5.7.tar.gz 中包括了安装mysql5.7主要的软件包。 这样部署起来更方便
[root@xuegod63 ~]# tar xvf mysql-5.7.tar.gz
[root@xuegod63 ~]# yum -y install ./mysql*.rpm
[root@xuegod63 ~]# systemctl start mysqld #启动MySQL会生成临时密码。

在MySQL的配置文件/etc/my.cnf 中关闭密码强度审计插件，并重启MySQl服务。
[root@xuegod63 ~]# vim /etc/my.cnf #修改MySQL的配置文件，在[myqld]标签处末行添加以下项：
validate-password=OFF #不使用密码强度审计插件

[root@xuegod63 ~]# systemctl restart mysqld #重启MySQL服务

[root@xuegod63 ~]# grep ‘password’ /var/log/mysqld.log #获取临时密码。
2018-08-01T09:59:33.918961Z 1 [Note] A temporary password is generated for root@localhost: buL.UJp!T2Od #临时密码

[root@xuegod63 ~]# mysql -u root -p’buL.UJp!T2Od’ #使用临时密码登录MySQl，注意临时密码要引号
mysql> set password for root@localhost = password(‘123456’); #修改root用户密码为123456
mysql> flush privileges;
mysql> exit
—————————————————以上为MySQL 5.7版本安装———————————————————

13.1 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）0类型。

13.1.1 数值类型

13.1.2 日期和时间类型

13.1.3 字符串类型

整型
tinyint,占1字节，有符号：-138~137，无符号位：0~255
smallint,占2字节，有符号：-32768~32767，无符号位：0~65535
mediumint,占3字节，有符号：-8388608~8388607，无符号位：0~16777215
int,占4字节，有符号：-2147483648~2147483647，无符号位：0~4284967295
bigint,占8字节
bool 等价于tinyint(1) 布尔型

浮点型
float([m[,d]]) 占4字节，1.17E-38~3.4E+38
double([m[,d]]) 占8字节
decimal([m[,d]]) 以字符串形式表示的浮点数

字符型
char([m])：固定长度的字符,占用m字节
varchar[(m)]：可变长度的字符,占用m+1字节，大于255个字符：占用m+2
tinytext,255个字符(2的8次方)
text,65535个字符(2的16次方)
mediumtext,16777215字符(2的24次方)
longtext,(2的32次方)
enum(value,value,…)占1/2个字节 最多可以有65535个成员
set(value,value,…)占1/2/3/4/8个字节，最多可以有64个成员

http://www.runoob.com/mysql/mysql-select-query.html

13.2 常用select命令

使用select命令查看mysql数据库系统信息：
— 打印当前的日期和时间
mysql> select now();

— 打印当前的日期
mysql> select curdate();

— 打印当前的时间
mysql> select curtime();

— 打印当前数据库
mysql> select database();

— 打印MySQL版本
mysql> select version();

— 打印当前用户
mysql> select user();

—查看系统信息
mysql> show variables; #显示变量
mysql> show global variables; #显示全局变量
mysql> show global variables like ‘%version%’;
mysql> show variables like ‘%storageengine%’; #默认的存储引擎
like和””模糊搜索还可用户where字句。
MySQL提供两个通配符，用于与LIKE运算符一起使用，它们分别是：百分比符号 %和下划线 。
百分比（%）表示通配符允许匹配任何字符串的零个或多个字符。
下划线（）表示通配符允许匹配任何单个字符。

例如：
mysql> create database xuegod；
mysql> use xuegod;
mysql> create table students(id int(11),stname char(20));
mysql> insert into students values(1,’a1’),(1,’b2’),(3,’c3’),(4,’b4’);
mysql> select from students where stname like’_1’；

mysql> select from students where stname like’%_’;

除了like 还有not like

举例:
mysql> select * from students where stname not like’_1’;

mysql> show engines; #查看支持哪些存储引擎

查看系统运行状态信息：
mysql> show status; #查看运行状态
mysql> show global status like ‘Thread%’; #显示全局状态

13.3 导出、导入数据库

13.3.1 导入数据库

导入数据库前必须创建一个空数据库
[root@xuegod63 ~]# mysql -e ‘create database book’ -u root -p123456
或者登陆 MySQL
mysql> create database book;

导入（方法一）
上传book.sql文件到Linux主机：
[root@xuegod63 ~]# mysql -uroot -p123456 book < book.sql
mysql> use book;
mysql> show tables;
+—————————-+
| Tables_in_book |
+—————————-+
| books |
| category |
+—————————-+
2 rows in set (0.00 sec)

导入（方法二）
mysql> create database book;
mysql> use book;
mysql> source /tmp/book.sql #sql脚本的路径
mysql> show tables;
+—————————-+
| Tables_in_book |
+—————————-+
| books |
| category |
+—————————-+

13.3.2 导出数据库

导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名
[root@xuegod63 ~]# mysqldump -u root -p123456 book > /tmp/book2.sql
[root@xuegod63 tmp]# ls
book2.sql
book.sql

扩展知识
mysqldump - u root -p123456 -B 库名 >文件.sql
-B : 导出整个库包含建库语句
-A：导出全部数据库

如何把一个select的结果导出到文本，其实就是备份数据库
mysql> use book;
mysql> select into outfile ‘/tmp/133.txt’ from books; 此处有个文件访问权限问题，mysql用户是可以访问/tmp路径的，所以这里放到tmp下
或：
mysql> select from books into outfile ‘/tmp/456.txt’;
注意：mysql 默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作，否则会出现报错如下。
mysql> select into outfile ‘/tmp/133.txt’ from books;
ERROR 1290 (HY000): The MySQL server is running with the —secure-file-priv option so it cannot execute this statement
扩展：
解决 5.7版本导出报错，可以设置my.cnf 加上secure-file-priv=”/ “
[root@xuegod63 ~]# vim /etc/my.cnf #修改MySQL配置文件，在未行添加如下。

[root@xuegod63 ~]# systemctl restart mysqld
[root@xuegod63 ~]# mysql -u root -p123456
mysql> select into outfile ‘/tmp/133.txt’ from books;

13.4 SQL查询语句进阶

在我们刚导入的book数据库进行测试

13.4.1 查看表的内容

mysql> select * from category;

mysql> select from books;

mysql> select from books\G
* 1. row *
bId: 1
bName: 网站制作直通车
bTypeId: 2
publishing: 电脑爱好者杂志社
price: 34
pubDate: 2004-10-01
author: 苗壮
ISBN: 7505380796
bId: 43
bName: ASP 3初级教程
bTypeId: 2
publishing: 机械工业出版社
price: 104
pubDate: 2003-11-01
author: 韩旭日
ISBN: 7505375458
。。。
* 44. row *
bId: 44
bName: XML 完全探索
bTypeId: 2
publishing: 中国青年出版社
price: 104
pubDate: 2004-01-01
author: 齐鹏
ISBN: 7505357778
44 rows in set (0.00 sec)

13.4.2 查看字段类型

desc 表名
mysql> desc books;

13.4.3 逻辑运算符

and or not
and 且
or 或
not 非
选择出书籍价格为（30,40,50,60）的记录,只显示书籍名称，出版社，价格
mysql> select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;
+——————————————————————+——————————————-+———-+
| bName | publishing | price |
+——————————————————————+——————————————-+———-+
| Illustrator 10完全手册 | 科学出版社 | 50 |
| FreeHand 10基础教程 | 北京希望电子出版 | 50 |
| 网站设计全程教程 | 科学出版社 | 50 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
| Delphi 5程序设计与控件参考 | 电子工业出版社 | 60 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
+——————————————————————+——————————————-+———-+

13.4.4 算术运算符

= 等于
<> 不等于 !=
> 大于
< 小于
>= 大于等于
<= 小于等于

in 运算符
IN 运算符用于 WHERE 表达式中，以列表项的形式支持多个选择，语法如下：
WHERE column IN (value1,value2,…)
WHERE column NOT IN (value1,value2,…)

Not in 与in相反
当 IN 前面加上 NOT 运算符时，表示与 IN 相反的意思，即不在这些列表项内选择。

找出价格大于60的记录
mysql> select bName,price from books where price>60;

mysql> select bName,price from books where price=60; #找出价格等于60的。

mysql> select bName,price from books where price<>60; #找出价格不等于60的

mysql> select bName,price from books where price in (50,60,70); #找出价格是60，50，70的记录。

mysql> select bName,price from books where price not in (50,60,70); #找出价格不是60，50，70的记录
+—————————————————————————-+————-+
| bName |price |
+—————————————————————————-+————-+
| 网站制作直通车 |34 |
。。。
| XML 完全探索 |104 |
+——————————————————————————+————+
38 rows in set (0.00 sec)

13.4.5 排序

升序：order by “排序的字段” asc 默认
降序：oredr by “排序的字段” desc
mysql> select bName,price from books where price in (50,60,70) order by price asc;
+———————————————————+————+
| bName | price |
+———————————————————+————+
| Illustrator 10完全手册 | 50 |
| FreeHand 10基础教程 | 50 |
| 网站设计全程教程 | 50 |
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |

mysql> select bName,price from books where price in (50,60,70) order by price desc;
+————————————————————-+————-+
| bName | price |
+————————————————————-+————-+
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |
| Illustrator 10完全手册 | 50 |
| FreeHand 10基础教程 | 50 |
| 网站设计全程教程 | 50 |

mysql> select bName,price from books where price in (50,60,70) order by price desc,bName desc; #多个字段排序。

13.4.6 范围运算

[not]between ….and….
Between and 可以使用大于小于的方式来代替,并且使用大于小于意义表述更明确
查找价格不在30到60之间的书名和价格
mysql> select bName,price from books where price not between 30 and 60 order by price desc;
注：
这里的查询条件有三种：between。。。and，or 和 in
(30,60) >30 and <60
[30,60] >=30 and <=60

13.4.7 模糊匹配查询

字段名 [not]like ‘通配符’ ——》% 表示任意多个字符

查找书名中包括”程序”字样记录
mysql> select bName from books where bName like ‘%程序%’;

13.4.8 查找书名中不包括“程序”字样记录

mysql> select bName from books where bName not like ‘%程序%’;
| bName |
+————————————————————————————-+
| 网站制作直通车
| 黑客与网络安全 |
。。。
| XML 完全探索 |
+————————————————————————————-+
42 rows in set (0.00 sec)

MySQL子查询：
概念：在select 的where条件中又出现了select，查询中嵌套着查询

选择 类型名为“网络技术”的图书：
mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName=’网络技术’);
+————————————-+————-+
| bName | bTypeId|
+————————————-+————-+
| Internet操作技术 | 7 |
+————————————-+————-+
1 row in set (0.01 sec)

选择类型名称为“黑客”的图书；
mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName=’黑客’);
+—————————————+————-+
| bName | bTypeId |
+—————————————+————-+
| 黑客与网络安全 | 6 |
| 黑客攻击防范秘笈 | 6 |
+—————————————+————-+
2 rows in set (0.00 sec)

13.4.9 Limit限定显示的条目

SELECT * FROM table LIMIT [offset,] rows
偏移量 行数
　　LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0（而不是 1）。

比如select * from table limit m,n语句
表示其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。

查出category表中第2条到第6行的记录。
首先2到6行有2，3，4，5，6总共有5个数字，从2开始，偏移量为1
mysql> select * from category limit 1,5;

查看所有书籍中价格中最低的三条记录：
我们对所有记录排序以升序排列，取出前面3个来
mysql> select bName,price from books order by price asc limit 0,3;

我们将子查询和限制条目，算术运算结合起来查询
显示字段bName ,price；条件：找出价格比电子工业出版社出版的书中最便宜还要便宜的书。
针对这种查询，我们一步步的来，先找出电子工业出版社出版中最便宜的书。
mysql> select bName,price from books where publishing=”电子工业出版社” order by price asc limit 0,1;
+——————————————————-+————+
| bName | price |
+——————————————————-+————+
| Delphi 5程序设计与控件参考 | 60 |
+——————————————————-+————+
1 row in set (0.00 sec)

mysql> select bName,price from books where price<(select price from books where publishing=”电子工业出版社” order by price asc limit 0,1);
+————————————————————————————+———-+
| bName | price |
+————————————————————————————+———-+
| 网站制作直通车 | 34 |
。。。
| Access 2000应用及实例基集锦 | 59 |
+————————————————————————————+———-+
25 rows in set (0.01 sec)
或者：
多行子查询： all表示小于子查询中返回全部值中的最小值
mysql> select bName,price from books where price+————————————————————————————+———-+
| bName |price |
+————————————————————————————+———-+
| 网站制作直通车 | 34 |
。。。
| Access 2000应用及实例基集锦 | 59 |
+————————————————————————————+———-+
25 rows in set (0.00 sec)

13.4.10 连接查询

以一个共同的字段，求两张表当中符合条件的并集。通过共同字段把这两张表连接起来。
常用的连接：
内连接：根据表中的共同字段进行匹配
外连接分两种：左外连接、右外链接。

内连接
语法：
select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段
内连接：根据表中的共同字段进行匹配
测试
mysql> select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;
+————————————————————————————-+———-+———————-+
| bname | price | btypename |
+————————————————————————————-+———-+———————-+
| 网站制作直通车 | 34 | 网站 |
。。。
| XML 完全探索 | 104 | 网站 |
+————————————————————————————-+———-+———————-+
实际使用中inner可省略掉，跟WHERE 子句结果一样
mysql> select a.bname,a.price,b.btypename from books a, category b where a.btypeid=b.btypeid;

外连接 (分为左外连接；右外连接)

1. 左连接： select 字段 from a表 left join b表 on 连接条件

解释：优先显示左表全部记录,此时左表主表，右表为从表
主表内容全都有，从表内没有的显示null。
mysql> select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid;
+————————————————————————————-+———-+———————-+
| bname | price | btypename |
+————————————————————————————-+———-+———————-+
| 网站制作直通车 | 34 | 网站 |
| HTML设计实务 | 72 | 网站 |
| XML 完全探索 | 104 | 网站 |
。。。
+————————————————————————————-+———-+———————-+
44 rows in set (0.00 sec)

1. 右连接：select 字段 from a表 right join b表 on 条件

解释：优先显示右表全部记录,此时右表主表，左表为从表
主表内容全都有，从表内没有的显示null。

mysql> select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;
+————————————————————————————-+————-+———————-+
| bname | bTypeId | bTypeName |
+————————————————————————————-+————-+———————-+
| 网站制作直通车 | 2 | 网站 |
| ASP 3初级教程 | 2 | 网站 |
| XML 完全探索 | 2 | 网站 |
。。。
+————————————————————————————-+————-+———————-+
44 rows in set (0.00 sec)

13.4.11 聚合函数

函数：执行特定功能的代码块。

1. 算数运算函数

2. Sum()求和

   显示所有图书单价的总合
   mysql> select sum(price) from books;
   或：
   select sum(price) as 图书总价 from books;
   +——————+
   | sum(price) |
   +——————+
   | 10048 |
   +——————+

3. avg()平均值

求书籍Id小于3的所有书籍的平均价格
mysql> select avg(price) from books where bId<=3;
+——————+
| avg(price) |
+——————+
| 39.3333 |
+——————+

1. max() 最大值

求所有图书中价格最贵的书籍
mysql> select bName,max(price) from books; 这种方法是错误的

我们来查一下最贵的图书是哪本？
Select bname,price from books order by price asc limit 0,3;
可见最贵书是Javascript与Jscript从入门到精通，而不是网站制作直通车
Select bName,price from books where price=(select max(price) from books);
+————————————————————+———-+
| bName | price |
+————————————————————+———-+
| Javascript与Jscript从入门到精通 | 7500 |
+————————————————————+———-+

1. min()最小值

求所有图书中价格便宜的书籍？
mysql> select bName,price from books where price=(select min(price) from books);
+———————————-+———-+
| bName | price |
+———————————-+———-+
| 网站制作直通车 | 34 |
+———————————-+———-+

1. count()统计记录数

统计价格大于40的书籍数量
mysql> select count() from books where price>40;
+—————+
| count() |
+—————+
| 43 |
+—————+
Count（）中还可以增加你需要的内容，比如增加distinct来配合使用
mysql> select count(distinct price) from books where price>40;
+————————————+
| count(distinct price) |
+————————————+
| 26 |
+————————————+
1 row in set (0.00 sec)

1. 算数运算

   • • • /
         给所有价格小于40元的书籍，涨价5元
         mysql> update books set price=price+5 where price<40;
         给所有价格高于70元的书籍打8折
         mysql> update books set price=price*0.8 where price>70;

2. 字符串函数

   substr(string ,start,len) 截取：从start开始，截取len长.start 从1开始算起。
   mysql> select substr(bTypeName,1,7) from category where bTypeId=10;
   +—————————————-+
   | substr(bTypeName,1,7) |
   +—————————————-+
   | AutoCAD | 本来是AutoCAD技术
   +—————————————-+
   mysql> select substr(bTypeName,8,2)from category where bTypeId=10;
   +—————————————-+
   | substr(bTypeName,8,2) |
   +—————————————-+
   | 技术 | 只截取汉字
   +—————————————-+
   1 row in set (0.00 sec)

concat(str1,str2,str3…..) 拼接，把多个字段拼成一个字段输出
mysql> select concat(bName,publishing) from books;
+———————————————————————————————————————+
| concat(bName,publishing) |
+———————————————————————————————————————+
| 网站制作直通车电脑爱好者杂志社 |
| XML 完全探索中国青年出版社 |
。。。
+———————————————————————————————————————+
44 rows in set (0.00 sec)

mysql> select concat(bName,”——-“,publishing) from books; #同上边的结果是一样的

1. 大小写转换

   upper()大写 : 转为大写输出
   mysql> select upper(bname) from books where bId=9;
   +———————————————+
   | upper(bname) |
   +———————————————+
   | DREAMWEAVER 4ǽɡµň¶Ľ |
   +———————————————+
   这样转换中文有可能会出现乱码

   lower()小写：转为小写输出
   mysql> select lower(bName) from books where bId=10;
   +———————————————+
   | lower(bName) |
   +———————————————+
   | 3d max 3.0 创作效果百例 |
   +———————————————+

总结：
13.1 数据类型
13.2 常用select命令
13.3 导出，导入数据库
13.4 Sql查询语句进阶