WEB安全之数据库mysql(一):数据的条件查询模糊查询分组查询、表的子查询
- 1.数据的查询
- 2.mysql的子查询
- where型子查询
- from型子查询
- exists型子查询
- 联合查询(两个表的查询)
1.数据的查询
select * from users;
- 星号代表所有的字段
- 查询指定的字段
select username,password from users;
- 按条件调节查询
- 按关系来查询
语法:SELECT 字段名1,字段名2,…FROM 表名WHERE 条件表达式
- in 查询
SELECT * FROM student2 WHERE id IN (1,2,3);
- 带 BETWEEN AND 关键字的查询
select * from users where id not between 1 and 10;
- 带 DISTINCT 关键字的查询
select distinct username from users
- like查询 一般都会给跟着%
select * from users where username like "%m%" ; //包含m字母的
- 链接:
- and查询,满足多个条件
select * from users where id=1 and username='moon';
- or查询,满足任意一个条件
select * from users where id=1 or username='moon';
- OR 和 AND 一起使用的情况
OR 和 AND 一起使用的时候,AND 的优先级高于 OR,因此二者一起使用时,会先运算 AND 两边的表达式,再运算 OR 两边的表达式。
mysql> select * from users where id >5 and password='123456c' or username='moon1';
- 聚合查询
- count 返回行数
select count(*) from users;select count(id) from users;
- COUNT() 返回某列的行数
SUM() 返回某列值的和
AVG() 返回某列的平均值
MAX() 返回某列的最大值
MIN() 返回某列的最小值 - 分组查询
- 如果报错请在 my.ini添加
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
- 后重启
- GROUP BY
mysql> SELECT * FROM users GROUP BY password;+----+----------+----------+| id | username | password |+----+----------+----------+| 3 | moon1 | 123456 || 1 | moon | 456789 |+----+----------+----------+2 rows in set (0.01 sec)mysql> SELECT * FROM users GROUP BY username;+----+----------+----------+| id | username | password |+----+----------+----------+| 2 | alex1 | 456789 || 1 | moon | 456789 || 3 | moon1 | 123456 |+----+----------+----------+3 rows in set (0.01 sec)
- 使用 LIMIT 限制查询结果的数量
select * from users limit 2,10;select * from users as u where u.id=1;
- 为表和字段取别名
select username as myname from users;
2.mysql的子查询
where型子查询
(把内层查询结果当作外层查询的比较条件) select * from users where id in (select id from users where id>10);
from型子查询
(把内层的查询结果供外层再次查询)select * from (select username,age from users) as agev_a where age>20select * from (select * from users where id>=10) as age_10;(select * from users where id>=10)查询出来的是一个集合 别名为age_10select * from age_10
exists型子查询
(把外层查询结果拿到内层,看内层的查询是否成立)10select * from users where EXISTS (select * from users where id>1)
联合查询(两个表的查询)
- 注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行 - UNION ALL 查询全部 而且不会消除重复的行
union - SQL UNION ALL 语法
- union的用法及注意事项
两次查询的列数必须一致
select * from users union select *,1 from news;
CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) not NULL, `content` varchar(255) not null, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; insert into news (title,content)values('a1','a1');
- 联合查询