导出、导入数据库
导出数据库(备份)
导出数据库: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)