SQL语句进阶

导出、导入数据库

导出数据库（备份）

导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名

[root@haha ~]#  mysqldump -u root -p123456 haha>haha.sql
最好使用-B选项导出的时候，把数据库结构也导出

导入数据库（还原）

导入前先模拟将要还原的数据库删除
[root@haha ~]# mysql -e 'drop database haha' -uroot -p123456
导入数据库（还原数据库）
[root@haha ~]# mysql -e 'create database haha' -uroot -p123456

方法一
在shell命令行执行导入命令，不推荐,暴露了密码,一般这种方法用于脚本自动化执行比较多
[root@haha ~]# mysql -uroot -p123456 haha < haha.sql

方法二
进入到mysql数据库，添加sql文件，推荐使用此方法,安全
mysql> source /root/haha.sql

导出数据库结构

Mysqldump -uroot -p123456 -B 库名>文件.sql
##-B参数在导出数据库时，同时导出数据库结构（建库语句）
-B : 导出整个库包含建库语句
-A：导出全部数据库

导出数据库不能导出数据库结构，需要我们导入数据库时新建数据库，使用-B参数在导出数据库时，同时导出数据库结构，这样我们在导入数据库时不用先创建要导入的数据库的库结构
省略一步创建库操作

把一个select结果导出到文本

select * into outfile ‘/tmp/test.txt’ from 库名.表名;
此处有个文件访问权限问题，mysql用户是可以访问/tmp路径的,所以这里放到tmp下

mysql> select * into outfile '/tmp/test.txt'from haha.test;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
是因为secure-file-priv 这个选项规定了导出的目录,查看导出目录

mysql>  show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。
secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出

[root@haha ~]# sed -i '$i secure_file_priv='/tmp''
/etc/init.d/mysqld restart
[root@haha ~]# mysql -uroot -p123456 -A
mysql> select * into outfile '/tmp/test.txt' from haha.test;
Query OK, 1 row affected (0.00 sec)

逻辑运算符

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\G
************** 1. row ****************
     bName: Illustrator 10完全手册
publishing: 科学出版社
     price: 50
************** 2.row ****************
     bName: FreeHand 10基础教程
publishing: 北京希望电子出版
     price: 50
*************** 3. row ****************
     bName: 网站设计全程教程
publishing: 科学出版社
     price: 50
*************** 4. row ****************
     bName: ASP数据库系统开发实例导航
publishing: 人民邮电出版社
     price: 60
*************** 5. row ****************
     bName: Delphi 5程序设计与控件参考
publishing: 电子工业出版社
     price: 60
*************** 6. row ****************
     bName: ASP数据库系统开发实例导航
publishing: 人民邮电出版社
     price: 60
6 rows in set (0.00 sec)

算数运算符

普通

= 等于
<> 不等于 !=

大于
< 小于
= 大于等于
<= 小于等于

in运算符

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

找出大于某条件的内容

mysql> select bName,price from books where price>100\G
************ 1.row ***************
bName: Javascript与Jscript从入门到精通
price: 7500
************ 2.row ***************
bName: ASP 3初级教程
price: 104
************ 3.row ***************
bName: XML 完全探索
price: 104
3 rows in set (0.00 sec)
##找出价格大于100的书籍，显示名称和价格

找出等于某条件的内容

mysql> select bName,price from books where price=60\G
************1.row***************
bName: ASP数据库系统开发实例导航
price: 60
************2.row***************
bName: Delphi 5程序设计与控件参考
price: 60
************3.row***************
bName: ASP数据库系统开发实例导航
price: 60
3 rows in set (0.00 sec)
##找出价格等于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 in (50,60,70);
##找出价格是50、60、70的记录

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

排序

升序和降序

升序：order by “排序的字段” asc 默认
降序：oredr by “排序的字段” desc

mysql> select bName,price from books where price  in (50,60,70) order by price asc;
##查看价格是50、60、70的记录，显示名称和价格，显示时以价格升序的方式显示
mysql> select bName,price from books where price in (50,60,70) order by price desc;
##查看价格是50、60、70的记录，显示名称和价格，显示时以价格降序的方式显示

多个字段排序

mysql> select bName,price from books where price  in (50,60,70) order by price desc,bName desc;
##排序一个字段后，再按照另一个字段进行排序

范围运算

between …and…

mysql> select bname,price from books where price between 30 and 60;
##查找价格在30到60之间（包含30和60）的书名和价格

mysql> select bname,price from books where price >=30 and price <=60;
##Between and 可以使用>=和<=的方式来代替,并且使用大于小于意义表述更明确

mysql> select bName,price from books where price not between 30 and 60 order by price desc;
mysql> select bName,price from books where price <30 or price>60 order by price desc;
##查找价格不在30到60之间（包含30和60）的书名和价格

模糊匹配查询

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

mysql> select bName from books where bName like '%程序%';
##查找书名中包括"程序"字样记录

mysql> select bName from books where bName not like '%程序%';
##查找书名中不包括"程序"字样记录

MYSQL子查询

在select 的where条件中又出现了select查询，查询中嵌套着查询，这就是子查询

查询类型名称是“网络技术”的书

普通查询

mysql> select bTypeId from category where bTypeName='网络技术';
mysql> select bName,bTypeId from books where bTypeId=7;

嵌套查询

mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术');

查询类型名称是“黑客”的书

mysql> select bname,btypeid from books where bTypeId=(select bTypeId from category where bTypeName='黑客');

LIMIT限定显示条目

SELECT * FROM table LIMIT [offset,] rows；
偏移量 行数
LIMIT语句可以强制让 SELECT 查询返回我们指定的记录条数。LIMIT 接受一个或两个数字参数。参数必须是一个正整数。如果给定两个参数，第一个参数指定偏移量，偏移量就是我要隔几行显示我需要查询的第一条记录，初始记录行的偏移量是 0(而不是 1)，第二个参数指定要显示几行

select * from haha limit m,n
m是指开始记录行的数值，0表示表中的第一条记录
n是指从第m+1条开始，取n条记录

查出category表中第二行到第六行的记录
mysql> select * from category limit 1,5;

查出category表中第三行到第六行的记录
mysql> select * from category limit 2,4;

查看所有书籍中价格最低的三条记录
mysql> select bName,price from books order by price asc limit 0,3;

将子查询、限制条目、算数运算结合查询

需求：显示字段bName ,price ；条件：找出价格比电子工业出版社出版的书中最便宜还要便宜的书

mysql> select bName,price from books where price<(select price from books where publishing="电子工业出版社" order by price asc limit 0,1);

或者
mysql> select bName,price from books where price < all(select price from books where publishing="电子工业出版社");
all表示子查询中返回全部值中的最小值

多表连接查询

以一个共同的字段，求两张表当中符合条件的并集。 通过共同字段把这两张表连接起来。

常用的连接：
内连接：根据表中的共同字段进行匹配
外连接：左外连接、右外链接

内连接

根据表中的共同字段进行匹配，将两个表中符合条件的记录进行拼接

select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段

查询每个学生的学号、姓名以及各科成绩

mysql> create table stu_info(xh int primary key,xm varchar(30) not null,sex enum('M','W')) engine=innodb default charset=utf8;
mysql> create table stu_chj(xh int,yw float(5,2),shx float(5,2));
mysql> insert into stu_info values(1,'lisi','M'),(2,'lier','W'),(3,'zhs','M'),(4,'liming','W');
mysql> insert into stu_chj values(1,99,77),(2,89,78),(3,100,67);
mysql> select stu_info.xh,xm,yw,shx from stu_info inner join stu_chj on stu_info.xh=stu_chj.xh;
##stu_info.xh，因为xh字段在两个表中都存在，所以需要声明该字段来自哪个表

mysql> select i.xh,i.xm,c.yw,c.shx from stu_info i inner join stu_chj c on i.xh=c.xh;
##可以给表起个别名，其中i.xh中的i是stu_info表的别名，需要在from后面定义

把书的名字，价格和所属类型显示出来
mysql> select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;

实际使用中可以省略掉inner，使用where子句代替
mysql> select a.bname,a.price,b.btypename from books a, category b where a.btypeid=b.btypeid;

外连接（左外连接，右外连接）

左连接
select 字段 from a表 left join b表 on 连接条件
a表是主表，指定的字段内容都显示。
b表从表，没有的内容显示null

mysql> select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid;

右连接
select 字段 from a表 right join b表 on 连接条件
a表是从表，
b表主表，都显示。

mysql> select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;

聚合函数

算数运算函数

sum()求和：

显示所有图书单价的总和
		mysql> select sum(price) from books;
+------------+
| sum(price) |
+------------+
|      10048 |
+------------+
1 row in set (0.01 sec)
 	或
select sum(price)  as 图书总价 from books;
##as给字段设置别名，as可以省略
+--------------+
| 图书总价     |
+--------------+
|        10048 |
+--------------+
1 row in set (0.00 sec)

avg()平均值：

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

max()最大值：

求所有图书中价格最贵的书籍
mysql> select bName,price from books where price=(select max(price) from books);

min()最小值：

求所有图书中价格便宜的书籍
mysql> select bName,price from books where price=(select min(price) from books);

count()统计记录数：

统计价格大于40的书籍数量
mysql> select count(*) from books where price>40;

Count（）中还可以增加你需要的内容，比如增加distinct来配合使用
mysql> select count(distinct price) from books where price>40;

算数运算

+ - * / 

给所有价格小于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;

字符串函数

substr(string ,start,len) 截取：
从start开始，截取len长.start 从1开始算起

mysql> select bTypeName from category where bTypeId=10;
+---------------+
| bTypeName     |
+---------------+
| AutoCAD技术   |
+---------------+
1 row in set (0.01 sec)

mysql> select substr(bTypeName,1,7) from category where bTypeId=10;
+-----------------------+
| substr(bTypeName,1,7) |
+-----------------------+
| AutoCAD               |
+-----------------------+
1 row in set (0.00 sec)

mysql> select substr(bTypeName,2,7) from category where bTypeId=10;
##从第二个字符串截取

concat(str1,str2,str3.....) 拼接：
把多个字段拼成一个字段输出
mysql> select concat(bName,"-----",publishing) from books;

大小写转换

不是查找大小写，而是将输出结果以大写或者小写显示

upper()大写：

转为大写输出
mysql> select upper(bname) from books where bId=9;
+-------------------------------+
| upper(bname)                  |
+-------------------------------+
| 3D MAX 3.0 创作效果百例       |
+-------------------------------+
1 row in set (0.00 sec)

lower()小写：

转为小写输出
mysql> select lower(bName) from books where bId=9;
+-------------------------------+
| lower(bname)                  |
+-------------------------------+
| 3d max 3.0 创作效果百例       |
+-------------------------------+
1 row in set (0.00 sec)