MYSQL的条件查询

查询(SQL中最复杂的)

1)全列查询

select*from table_name;

2)指定列查询

select column_1_name,column_2_name form table_name;

3)查询带有表达式

select column_1+column_2.. as sum from table_name;
//as也可以不写,后面是别名,写上增加可读性,另外,查询操作不会动硬盘上的东西

4)去重查询

select distinct cloumn_name from table_name;

5)排序

select column_name from table_name order by column_1_name asc/desc..,...;
//按照先后顺序设置优先级
//asc表示升序,一般不写,desc表示降序,一般要写,放在列名后面

6)条件查询

select column_name from table_name where 条件;

条件运算符

 

 select * from exam_result where chinese >80 or math>70 and english >70;

可以看出and的优先级是要大于or的优先级,但是我们建议一般情况下,进行括号覆盖.

select * from exam_result where chinese between 80 and 90;

between and是一个闭区间的条件.

 select * from exam_result where math in (58,59,98,99);

 select*from exam_result where math in (78,59,98.5,99,98);

 select*from exam_result where math =78 or math =59 or math=98.5 or math=99 or math=98;

        用in或者or都能够达到上述效果.

like

使用like关键词时,可以进行一个模糊匹配

_该符号'_'只能表示一个字符

select*from exam_result where name like '_孙';
select*from exam_result where name like '孙_';

如上图所示

select*from exam_result where name like '孙%';
//表示以孙开头
select*from exam_result where name like '%孙';
//表示以孙结尾
select*from exam_result where name like '%孙%';
//表示带孙字的字符
select*from exam_result where name like '%';
//表示所有字符

select*from exam_result where id is null;
//查询id为null
select*from exam_result where id = null;
//此处认为null是不相等的,为false
select*from exam_result where id <=> null;
//此处认为null是相等的,为true;

分页查询

select* 比较危险,一次查询出来的内容太多了

分页查询可以限制一次查询,解决上述这个问题

另外我们还引入了偏移量来进行处理

select *from exam_result limit 3 offset 0;
select *from exam_result limit 3 offset 3;
select *from exam_result limit 3 offset 6;

update table_name set column_name_1=xxx where 条件;

update exam_result set math=60,english=70 where name='曹孟德';
 select*from exam_result where name='曹孟德';

另外在这里,要注意一点,就是null值在排序的时候默认为最小

select name,chinese +math +english from exam_result order by  chinese +math+english limit 3;
 select name,math from exam_result where name in('宋公明','刘玄德','曹孟德');

\给上述三位同学的数学分数加10;

 update exam_result set math=math+10 where  name in('宋公明','刘玄德','曹孟德');
 select name,math from exam_result where name in('宋公明','刘玄德','曹孟德');

delete 相关操作

delete from table_name where (条件) limit /order by;

删除想删除的;