select * from 表名;
--语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
基础查询
-- 多个字段查询;全部的可以用*来代替
-- eg:查询姓名和年龄
select
name, -- 姓名
age,-- 年龄
from
student;-- 学生表
-- distinct 去除重复的结果集
select distinct address from student;
/*计算列:
一般可以用四则运算
ifnull(表达式1,表达式2):null参与的运算,计算结果都为null
表达式1:哪个字段需要判断是否为null
如果该字段为null就用表达式2的值替换。
*/
-- 计算math和English分数之和
select name,math,ength,math+english from student;
--如果有null参与的运算,计算都是null
select name,math,ength,math+ IFNULL(english,0) from student;
-- AS:取别名;也可以省略
select name,math,ength,math+ IFNULL(english,0) as 总分 from student;
select name,math 数学,ength 英语,math+ IFNULL(english,0) 总分 from student;
条件查询
1.where子句后跟条件
2.运算符:
>,<,<=,>=,=,<>
between ...and
in(集合)
like:模糊查询 占位符:_ 单个任意字符;% 多个任意字符;
IS NULL
and 或 &&
or 或 ||
not 或 !
select * from stu where age > 20;
select * from stu where age >= 20;
select * from stu where age = 20;
select * from stu where age != 20;
select * from stu where age <> 20;
select * from stu where age >=20 && age <= 30 ;
select * from stu where age >=20 and age <= 30 ;
select * from stu where age between 20 and 30 ;
select * from stu where age = 22 or age = 18 or age = 25 ;
select * from stu where age in(22,19,25);
-- null特别
select * from student where english = null;-- 不可以
select * from student where English is null;
select * from student where English is not null;
select * from student where name like '张%';
select * from student where name like '_化_';
--查询名字里有3个字的人
select * from student where name like '___';
select * from student where name like '%大%';