文章目录
select * from emp where mgr is null;
查询上级领导为空的员工select * from emp where mgr is not null and comm>0;
如果表中的字段名称太长或者不是很容易直接看懂,那么我们可以使用别名,使用的方式有三种:
select ename "姓名" from emp;
select ename as "姓名" from emp;
select ename 姓名 from emp;
select distinct job from emp;
条件语句支持的运算符: > < = != >= <= !=(<>)
and 并且 多个条件属于与的关系 or 或者
select * from user where id=1 or id=2;
查询id=1的数据或者id=2的数据 ,如果这两个都存在,那么将会全部返回_
代表单个未知字符_a%
%a__
%
代表多个未知字符
a%
%a
%a%
select * from user where name like '_加%';
select * from user where name like '_加_';
此时匹配的名字是三个字符,比如 陈加兵
select * from user where name not like '_加%';
在两个数之间
select * from t_item where price between 10 and 100;
查询价格在10 到 100之间的数据
查询某个字段的值为多个值的时候使用in
select * from t_item where price in(100,200,233);
select price from t_item order by price
select price from t_item where price<100 order by price;
select price from t_item order by price desc
select category_id,price from t_item order by category_id,price desc;
按照分类id升序,价格降序select title,price from t_item where title like "%燃%" order by price;
limit n,m
: n表示跳过的条数,m表示每页显示的条数limit 0,5
查询第一页,每页显示5条limit 10,5
查询第三页,每页显示5条limit 12,3
查询第五页 每页3条select price from t_item order by price limit 10,10;
按照价格升序排列,显示第二页,每页10条
select price from t_item where price <100 limit 0,10;
查询价格小于100的记录,显示第一页,每页10条
concat()函数可以实现多个字符串的拼接
select concat('a','b');
select concat(price,"元") from t_item limit 0,3;
查询商品,并且将查询到的价格和元这个单位拼接。相当于显示的是价格只是每个价格后面添加了单位 比如: 23元
select concat(price,"元",title) from t_item limit 0,5;
支持加减乘除,取余(%)等效mod(n,m)
select price,num ,price*num '总价' from t_item;
select now();
select now(),curdate(),curtime();
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());
select date_format(now(),'%Y年%m月%d日 %h时%i分%s秒');
输出 2018年03月23日 03时44分51秒select title,date_format(created_time,'%Y年%m月%d日 %h时%i分%s秒') from t_item;
select str_to_date("2018年10月23日",'%Y年%m月%d日');
update emp set comm=ifnull(comm,0);
如果奖金comm是null,那么comm=0,如果不是空 comm=comm,还是原来的值select sum(num) from t_item where price<100;
select avg(price) from t_item;
select count(
*) from t_item where price<100;
select max(price) from t_item;
select min(price) from t_item;
select avg(price) "平均单价" from t_item where title like "%DELL%";