数据的DQL操作:数据查询
格式:
select [字段列表] | * from 表名
[where 搜索条件]
[group by 分组字段 [having 子条件]]
[order by 排序 asc|desc]
[limit 分页参数]
基础查询
mysql> select * from stu;
+----+----------+-----+-----+---------+
| id | name | age | sex | classid |
+----+----------+-----+-----+---------+
| 1 | zhangsan | 20 | m | lamp138 |
| 2 | lisi | 20 | m | lamp138 |
| 3 | wangwu | 21 | w | lamp138 |
| 4 | zhaoliu | 25 | w | lamp94 |
| 5 | uu01 | 26 | m | lamp94 |
| 6 | uu02 | 28 | w | lamp92 |
| 7 | qq02 | 24 | m | lamp92 |
| 8 | uu03 | 32 | m | lamp138 |
| 9 | qq03 | 23 | w | lamp94 |
| 10 | aa | 19 | m | lamp138 |
| 11 | sad | 35 | m | lamp94 |
| 12 | tt | 25 | m | lamp92 |
| 13 | wer | 25 | w | lamp94 |
| 14 | xx | 25 | m | lamp92 |
| 15 | kk | 0 | w | lamp94 |
+----+----------+-----+-----+---------+
15 rows in set (0.00 sec)
where条件查询
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据。
1. 查询班级为lamp138期的学生信息
mysql> select * from stu where classid='lamp138';
2. 查询lamp138期的男生信息(sex为m)
mysql> select * from stu where classid='lamp138' and sex='m';
3. 查询id号值在10以上的学生信息
mysql> select * from stu where id>10;
4. 查询年龄在20至25岁的学生信息
mysql> select * from stu where age>=20 and age<=25;
mysql> select * from stu where age between 20 and 25;
5. 查询年龄不在20至25岁的学生信息
mysql> select * from stu where age not between 20 and 25;
mysql> select * from stu where age<20 or age>25;
6. 查询id值为1,8,4,10,14的学生信息
select * from stu where id in(1,8,4,10,14);
mysql> select * from stu where id=1 or id=8 or id=4 or id=10 or id=14;
7. 查询lamp138和lamp94期的女生信息
mysql> select * from stu where classid in('lamp138','lamp94') and sex='w';
mysql> select * from stu where (classid='lamp138' or classid='lamp94') and sex='w
LIKE 子句
WHERE 子句中可以使用等号=来设定获取数据的条件,如 "runoob_author = 'RUNOOB.COM'"。
但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录,
这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
LIKE 子句中使用百分号%字符来表示任意字符,类似于UNIX或正则表达式中的星号*。
如果没有使用百分号%, LIKE 子句与等号=的效果是一样的。
LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
你可以使用 AND 或者 OR 指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。
可以使用regexp正则来代替 like
--9. 查询name字段值是以zh开头的所有信息
mysql> select * from stu where name like "zh%";
mysql> select * from stu where name regexp "^zh"; --正则写法
+----+----------+------+-----+---------+
| id | name | age | sex | classid |
+----+----------+------+-----+---------+
| 14 | zhangle | 29 | m | 5 |
| 1 | zhangsan | 20 | w | 1 |
| 4 | zhaoliu | 21 | m | 4 |
+----+----------+------+-----+---------+
3 rows in set (0.00 sec)
--10.查询姓名name中含有ang子串的所有信息
mysql> select * from stu where name like "%ang%";
mysql> select * from stu where name regexp "ang";
+----+-----------+------+-----+---------+
| id | name | age | sex | classid |
+----+-----------+------+-----+---------+
| 1 | zhangsan | 20 | w | 1 |
| 3 | wangwu | 22 | w | 5 |
| 10 | xiaozhang | 19 | w | 1 |
| 13 | wangwen | 27 | w | 2 |
| 14 | zhangle | 29 | m | 5 |
+----+-----------+------+-----+---------+
5 rows in set (0.01 sec)
--11. 查询姓名是任意四位字符构成的信息。
mysql> select * from stu where name like "____";
mysql> select * from stu where name regexp "^[a-z0-9]{4}$";
+----+------+------+-----+---------+
| id | name | age | sex | classid |
+----+------+------+-----+---------+
| 2 | lisi | 25 | m | 2 |
| 5 | uu01 | 27 | w | 1 |
| 6 | uu02 | 25 | m | 2 |
| 7 | uu03 | 28 | w | 2 |
| 8 | uu05 | 22 | m | 4 |
+----+------+------+-----+---------+
5 rows in set (0.00 sec)
MySQL的统计函数(聚合函数):max() min() count() sum() avg()
-- 获取学生表中最大、最小以及平均年龄是多少?
mysql> select max(age),min(age),avg(age) from stu;
+----------+----------+----------+
| max(age) | min(age) | avg(age) |
+----------+----------+----------+
| 29 | 19 | 24.5714 |
+----------+----------+----------+
1 row in set (0.00 sec)
-- 获取学生表中男生m的数量
mysql> select count(*) from stu where sex='m';
GROUP BY 语句 分组
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
-- 统计班级信息,按性别分组,并统计每组人数;
MySQL> select sex,count(*) from stu group by sex;
-- 统计每个班级的人数
MySQL> select classid,count(*) from stu group by classid;
-- 统计每个班级的,男生和女生各多少人数。
MySQL> select classid,sex,count(*) from stu group by classid,sex;
ORDER BY 排序 -- asc 默认升序 desc 降序
我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如果我们需要对读取的数据进行排序,我们就可以使用 MySQL 的ORDER BY子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。
你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
你可以添加 WHERE...LIKE 子句来设置条件。
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
-- 按年龄升序排序查询学生信息
mysql> select * from stu order by age;
mysql> select * from stu order by age asc; --默认asc升序 可省略
+----+-----------+------+-----+---------+
| id | name | age | sex | classid |
+----+-----------+------+-----+---------+
| 10 | xiaozhang | 19 | w | 1 |
| 1 | zhangsan | 20 | w | 1 |
| 4 | zhaoliu | 21 | m | 4 |
| 11 | xiaoyan | 22 | m | 2 |
| 8 | uu05 | 22 | m | 4 |
| 3 | wangwu | 22 | w | 5 |
| 6 | uu02 | 25 | m | 2 |
| 2 | lisi | 25 | m | 2 |
| 5 | uu01 | 27 | w | 1 |
| 13 | wangwen | 27 | w | 2 |
| 7 | uu03 | 28 | w | 2 |
| 12 | xiaoxin | 28 | w | 4 |
| 9 | xiaoli | 29 | w | 2 |
| 14 | zhangle | 29 | m | 5 |
+----+-----------+------+-----+---------+
14 rows in set (0.01 sec)
-- 年龄降序排序
mysql> select * from stu order by age desc;
+----+-----------+------+-----+---------+
| id | name | age | sex | classid |
+----+-----------+------+-----+---------+
| 14 | zhangle | 29 | m | 5 |
| 9 | xiaoli | 29 | w | 2 |
| 12 | xiaoxin | 28 | w | 4 |
| 7 | uu03 | 28 | w | 2 |
| 13 | wangwen | 27 | w | 2 |
| 5 | uu01 | 27 | w | 1 |
| 2 | lisi | 25 | m | 2 |
| 6 | uu02 | 25 | m | 2 |
| 11 | xiaoyan | 22 | m | 2 |
| 8 | uu05 | 22 | m | 4 |
| 3 | wangwu | 22 | w | 5 |
| 4 | zhaoliu | 21 | m | 4 |
| 1 | zhangsan | 20 | w | 1 |
| 10 | xiaozhang | 19 | w | 1 |
+----+-----------+------+-----+---------+
14 rows in set (0.00 sec)
-- 查询学生信息,按班级做升序排序,相同班级按年龄降序排序
mysql> select * from stu order by classid asc,age desc;
limit 关键字 查询部分数据
-- 例如: .... limit m; 查询数据只显示前m条
-- 例如: .... limit m,n; 排除前m条,然后再查询出前n条
-- 查询前5条信息
mysql> select * from stu limit 5;
+----+----------+------+-----+---------+
| id | name | age | sex | classid |
+----+----------+------+-----+---------+
| 1 | zhangsan | 20 | w | 1 |
| 2 | lisi | 25 | m | 2 |
| 3 | wangwu | 22 | w | 5 |
| 4 | zhaoliu | 21 | m | 4 |
| 5 | uu01 | 27 | w | 1 |
+----+----------+------+-----+---------+
5 rows in set (0.00 sec)
-- 排除前2条后再获取4条信息
mysql> select * from stu limit 2,4;
+----+---------+------+-----+---------+
| id | name | age | sex | classid |
+----+---------+------+-----+---------+
| 3 | wangwu | 22 | w | 5 |
| 4 | zhaoliu | 21 | m | 4 |
| 5 | uu01 | 27 | w | 1 |
| 6 | uu02 | 25 | m | 2 |
+----+---------+------+-----+---------+
4 rows in set (0.00 sec)
-- 以4条数据分一页,取第一页。
mysql> select * from stu limit 0,4;
+----+----------+------+-----+---------+
| id | name | age | sex | classid |
+----+----------+------+-----+---------+
| 1 | zhangsan | 20 | w | 1 |
| 2 | lisi | 25 | m | 2 |
| 3 | wangwu | 22 | w | 5 |
| 4 | zhaoliu | 21 | m | 4 |
+----+----------+------+-----+---------+
4 rows in set (0.00 sec)
-- 以4条数据分一页,取第二页。
mysql> select * from stu limit 4,4;
+----+------+------+-----+---------+
| id | name | age | sex | classid |
+----+------+------+-----+---------+
| 5 | uu01 | 27 | w | 1 |
| 6 | uu02 | 25 | m | 2 |
| 7 | uu03 | 28 | w | 2 |
| 8 | uu05 | 22 | m | 4 |
+----+------+------+-----+---------+
4 rows in set (0.00 sec)
-- 以4条数据分一页,取第三页。
mysql> select * from stu limit 8,4;
+----+-----------+------+-----+---------+
| id | name | age | sex | classid |
+----+-----------+------+-----+---------+
| 9 | xiaoli | 29 | w | 2 |
| 10 | xiaozhang | 19 | w | 1 |
| 11 | xiaoyan | 22 | m | 2 |
| 12 | xiaoxin | 28 | w | 4 |
+----+-----------+------+-----+---------+
4 rows in set (0.00 sec)
-- 以4条数据分一页,取第四页。
mysql> select * from stu limit 12,4;
+----+---------+------+-----+---------+
| id | name | age | sex | classid |
+----+---------+------+-----+---------+
| 13 | wangwen | 27 | w | 2 |
| 14 | zhangle | 29 | m | 5 |
+----+---------+------+-----+---------+
2 rows in set (0.00 sec)
mysql>
分页公式:.... (页号-1)*页大小, 页大小;
页号 limit语句 起始分页码数值是页大小的几倍。
---------------------------------------------------------------------
1 ... limit 0,4; 0
2 ... limit 4,4; 1
3 ... limit 8,4; 2
4 ... limit 12,4; 3
5 ... limit 16,4; 4
嵌套查询 (不推荐)
--1. 嵌套查询
mysql> select max(age) from stu;
+----------+
| max(age) |
+----------+
| 29 |
+----------+
1 row in set (0.00 sec)
mysql> select * from stu where age=29;
+----+---------+------+-----+---------+
| id | name | age | sex | classid |
+----+---------+------+-----+---------+
| 9 | xiaoli | 29 | w | 2 |
| 14 | zhangle | 29 | m | 5 |
+----+---------+------+-----+---------+
2 rows in set (0.01 sec)
-- 查询年龄最大的所有学生信息
mysql> select * from stu where age=(select max(age) from stu);
+----+---------+------+-----+---------+
| id | name | age | sex | classid |
+----+---------+------+-----+---------+
| 9 | xiaoli | 29 | w | 2 |
| 14 | zhangle | 29 | m | 5 |
+----+---------+------+-----+---------+
2 rows in set (0.01 sec)
-- 查询python02期的所有学生信息
mysql> select * from stu where classid=(select id from classes where name='python02');
mysql> select * from stu where classid in(select id from classes where name='python02');
+----+---------+------+-----+---------+
| id | name | age | sex | classid |
+----+---------+------+-----+---------+
| 2 | lisi | 25 | m | 2 |
| 6 | uu02 | 25 | m | 2 |
| 7 | uu03 | 28 | w | 2 |
| 9 | xiaoli | 29 | w | 2 |
| 11 | xiaoyan | 22 | m | 2 |
| 13 | wangwen | 27 | w | 2 |
+----+---------+------+-----+---------+
6 rows in set (0.00 sec)
where关联查询
-- 查询所有学生信息,并跨表显示对应的班级名称信息
mysql> select s.*,c.name cname from stu s,classes c where s.classid=c.id;
+----+-----------+------+-----+---------+----------+
| id | name | age | sex | classid | cname |
+----+-----------+------+-----+---------+----------+
| 1 | zhangsan | 20 | w | 1 | python01 |
| 2 | lisi | 25 | m | 2 | python02 |
| 3 | wangwu | 22 | w | 5 | python04 |
| 4 | zhaoliu | 21 | m | 4 | python03 |
| 5 | uu01 | 27 | w | 1 | python01 |
| 6 | uu02 | 25 | m | 2 | python02 |
| 7 | uu03 | 28 | w | 2 | python02 |
| 8 | uu05 | 22 | m | 4 | python03 |
| 9 | xiaoli | 29 | w | 2 | python02 |
| 10 | xiaozhang | 19 | w | 1 | python01 |
| 11 | xiaoyan | 22 | m | 2 | python02 |
| 12 | xiaoxin | 28 | w | 4 | python03 |
| 13 | wangwen | 27 | w | 2 | python02 |
| 14 | zhangle | 29 | m | 5 | python04 |
+----+-----------+------+-----+---------+----------+
14 rows in set (0.01 sec)
-- 统计每个班级的人数
mysql> select c.name,count(s.id) from classes c,stu s where c.id=s.classid group by c.id;
+----------+-------------+
| name | count(s.id) |
+----------+-------------+
| python01 | 3 |
| python02 | 6 |
| python03 | 3 |
| python04 | 2 |
+----------+-------------+
4 rows in set (0.00 sec)
mysql> select c.name,count(s.id) from classes c,stu s where c.id=s.classid group by c.id order by count(s.id) desc;
+----------+-------------+
| name | count(s.id) |
+----------+-------------+
| python02 | 6 |
| python01 | 3 |
| python03 | 3 |
| python04 | 2 |
+----------+-------------+
4 rows in set (0.00 sec)
JOIN连接查询(左联,右联,内联)
JOIN 按照功能大致分为如下三类:
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接):与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
-- 查询新闻信息,并补齐新闻类别信息
mysql> select n.id,n.title,t.name from news n,ntype t where n.ntid=t.id;
mysql> select n.id,n.title,t.name from news n inner join ntype t on n.ntid=t.id;
+----+-------------------------------------------------------------+--------------+
| id | title | name |
+----+-------------------------------------------------------------+--------------+
| 1 | 骑共享单车违法 将被禁用或冻结账户 | 娱乐新闻 |
| 2 | 武警特战排爆手 | 军事新闻 |
| 3 | 国外武装直升机型号发展与作战能力分析 | 军事新闻 |
| 4 | 俄战机过去一周在边境拦截外国侦察机11次 | 军事新闻 |
| 5 | 中国空军战机进行远洋训练。 | 军事新闻 |
| 6 | 中国驻韩使馆举行中韩建交25周年纪念招待会 | 国际新闻 |
+----+-------------------------------------------------------------+--------------+
6 rows in set (0.00 sec)
-- 同上,但采用的是左联查询left join
mysql> select n.id,n.title,t.name from news n left join ntype t on n.ntid=t.id;
+----+-------------------------------------------------------------+--------------+
| id | title | name |
+----+-------------------------------------------------------------+--------------+
| 1 | 骑共享单车违法 将被禁用或冻结账户 | 娱乐新闻 |
| 2 | 武警特战排爆手 | 军事新闻 |
| 3 | 国外武装直升机型号发展与作战能力分析 | 军事新闻 |
| 4 | 俄战机过去一周在边境拦截外国侦察机11次 | 军事新闻 |
| 5 | 中国空军战机进行远洋训练。 | 军事新闻 |
| 6 | 中国驻韩使馆举行中韩建交25周年纪念招待会 | 国际新闻 |
+----+-------------------------------------------------------------+--------------+
6 rows in set (0.01 sec)
-- 统计每个新闻类别下的新闻数量,采用where关联统计
mysql> select t.name,count(n.id) from ntype t,news n where t.id=n.ntid group by t.id;
+--------------+-------------+
| name | count(n.id) |
+--------------+-------------+
| 娱乐新闻 | 1 |
| 国际新闻 | 1 |
| 军事新闻 | 4 |
+--------------+-------------+
3 rows in set (0.01 sec)
-- 统计每个新闻类别下的新闻数量,采用左联统计
mysql> select t.name,count(n.id) from ntype t left join news n on t.id=n.ntid group by t.id;
+--------------+-------------+
| name | count(n.id) |
+--------------+-------------+
| 娱乐新闻 | 1 |
| 体育新闻 | 0 |
| 国际新闻 | 1 |
| 军事新闻 | 4 |
+--------------+-------------+
4 rows in set (0.01 sec)
数据库的恢复
删库不一定非得跑路,在跑路前你需要做的是数据恢复
前提是开启了bin-log日志,并定期备份
以下模拟了一次删库和数据恢复的过程
-- 刷新 日志
mysql> reset master;
Query OK, 0 rows affected (0.39 sec)
mysql> create database ops;
Query OK, 1 row affected (0.28 sec)
mysql> use ops;
Database changed
create table customers(
id int not null auto_increment,
name char(20) not null,
age int not null,
primary key(id)
)engine=InnoDB;
insert into customers values(1,"wangbo","24"),(2,"guohui","22"),(3,"zhangheng","27");
mysql> select * from customers;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
+----+-----------+-----+
3 rows in set (0.00 sec)
-- (2)现在进行全备份
mysqldump -uroot -p -B -F -R -x --master-data=2 ops >/home/yc/ops.sql
-----------------
参数说明:
-B:指定数据库
-F:刷新日志
-R:备份存储过程等
-x:锁表
--master-data:在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
-- 再添加新的数据
insert into customers values(4,"liupeng","21"),(5,"xiaoda","31"),(6,"fuaiai","26");
mysql> select * from customers;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
| 4 | liupeng | 21 |
| 5 | xiaoda | 31 |
| 6 | fuaiai | 26 |
+----+-----------+-----+
6 rows in set (0.00 sec)
-- (4)此时误操作,删除了test数据库
drop database ops;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| python4 |
| sys |
| wx |
+--------------------+
6 rows in set (0.00 sec)
将binlog文件导出sql文件,并vim编辑它删除其中的drop语句
[root@vm-002 backup]# mysqlbinlog -d ops /var/lib/mysql/mysql-bin.000002> /home/yc/002bin.sql
sudo mysqlbinlog -d ops /var/lib/mysql/mysql-bin.000002> /home/yc/002bin.sql
[root@vm-002 backup]# ls
002bin.sql mysql-bin.000002 ops_2016-09-25.sql
[root@vm-002 backup]# vim 002bin.sql #删除里面的drop语句
-- 首先导入备份的数据文件
mysql -uroot -p < /home/yc/ops.sql
再导入删除 drop语句后的 binlog日志文件
mysql -uroot -p ops < /home/yc/002bin.sql
Python学习交流、资源共享群:563626388 QQ
mysql(2)——mysql基础 数据操作 DQL 数据恢复
最新推荐文章于 2024-10-02 06:14:43 发布