mysql数据库part3
一、查询语法
sql查询语句基本语法:
select .. from .. where ..group by .. having .. order by .. limit..
1、where条件的使用
功能:对表中的数据进行过滤筛选
-
判断的符号:
< > = <= >= != <>(不等于) -
拼接条件的关键字:
and or not -
查询范围区间between
between 小值 and 大值 [小值,大值] 查询两者之间的这个范围所有数据 -
查询某个值在具体某个范围里 in
in(1,2,3,4) -
模糊查询 like “%” “_” 通配符
like “%a” 匹配以a结尾的任意长度字符串
like “a%” 匹配以a开头的任意长度字符串
like “%a%” 匹配含有a字母的任意长度字符串
like “_a” 个数一共是两个字符,必须以a结尾,前面字符随意
like “a__” 个数一共是三个字符,必须以a开头,后面字符随意
(1) 单条件查询
查询部门是sale的所有员工姓名:
select emp_name from employee where post="sale";
(2) 多条件查询
部门是技术部,收入大于10000的所有人员及对应收入
select emp_name,salary from employee where post="技术部" and salary > 10000;
(3) between … and …
收入在10000-20000之间的所有员工姓名和对应收入
select emp_name,salary from employee where salary between 10000 and 20000;
收入不在10000-20000之间的所有员工姓名和对应收入
(4) null关键字,在查询的时候,要使用is进行判断,不能用=
select * from employee where post_comment is null;
(5) in 在…之中
查询收入是3000、4000、5000、8300所有员工的姓名和收入
select emp_name,salary from employee where salary in (3000,4000,5000,8300);
not … in … 不在所列数据之中:
select emp_name,salary from emnployee where salary not in (3000,4000,5000,8300);
写法二:
select emp_name,salary from employee where salary=3000 or salary=4000 or salary=5000 or salary=8300;
(6) 模糊查询
“%”通配符
select emp_name,salary from employee where emp_name like "L%";
“_”通配符
select emp_name,salary from employee where emp_name like "H__sir";
(7) concat数据拼接(as 别名)
select concat("姓名:",emp_name,"收入:",salary) as info from employee;
concat_ws(拼接的符号,参数1,参数2…):
select concat_ws(":",emp_name,salary) as info from employee;