select from
-
别名
SELECT id, loginid, loginpwd, 'abc' as '额外的一列' from `user`; -
*
-- 查询全部字段，并新增一列 abc 别名为 extra SELECT *, 'abc' as 'extra' from `employee`; -
case
-- 查询员工 -- ismale 等于 1 转成 男，否则转成女 -- salary 大于等于 10000 为高，大于等于 5000 为 中，5000 以下为 低 SELECT id, `name`, CASE WHEN ismale = 1 THEN '男' ELSE '女' END sex, CASE WHEN salary >= 10000 THEN '高' WHEN salary >= 5000 THEN '中' ELSE '低' END `level`, salary FROM employee; -
distinct
-- 对 location, NAME 都相同的去重 SELECT DISTINCT location, NAME FROM employee;
where
=、in、is、is not、>、<、>=、<=、between and、like、and、or
SELECT * FROM employee WHERE ismale = 1;
SELECT * FROM department WHERE companyId in (1, 2);
SELECT * from employee WHERE location is not null;
SELECT * from employee WHERE location is null;
SELECT * from employee WHERE salary>=10000;
SELECT * from employee WHERE salary BETWEEN 10000 and 12000;
SELECT * from employee WHERE `name` like '%张%';
-- 查询名字是 张X 的员工
SELECT * from employee WHERE `name` like '张_';
SELECT * from employee WHERE `name` like '张%' and ismale=0 and salary>=12000;
SELECT * from employee WHERE `name` like '张%' and (ismale=0 and salary>=12000 or birthday>='1996-1-1');
order by
-- 先按 sex 升序排序，再对 sex 相同的 salary 降序排序
SELECT *,
CASE ismale
WHEN 1 THEN '男'
ELSE '女'
END sex
FROM
employee
ORDER BY
sex ASC,
salary DESC;
limit
分页公式：
LIMIT (pageInx -1)*pageSize, pageSize
-- 跳过前两条数据，取 3 条数据
SELECT * FROM employee LIMIT 2, 3;
运行顺序
from -> where -> select -> order by -> limit
练习
-- 查询 user 表，得到账号为 admin，密码为 123456 的用户
SELECT * from `user` WHERE loginid = 'admin' and loginpwd = '123123';
-- 查询员工表，按照员工的入职时间降序排序，并且使用分页查询，查询第 3 页，每页 5 条数据
SELECT * FROM employee ORDER BY employee.joinDate desc LIMIT 10, 5
-- 查询工资最高的女员工
SELECT * FROM employee WHERE ismale = 0 ORDER BY salary desc limit 0,1;
打赏作者
您的打赏是我前进的动力
微信
支付宝
MySql👉 表记录的增删改
上一篇
评论
