语法

select ...
from ...
where ...
order by ...
limit ...

select

别名

SELECT
    `name` 名字 
FROM
    employee

*：所有

SELECT
    * 
FROM
    employee

case：判断语句相当于if

SELECT
    `name` 名字,
CASE
        ismale 
        WHEN 1 THEN
        '男' ELSE '女' 
    END 性别 
FROM
    employee

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：去重

SELECT DISTINCT
    e.location 居住地址 
FROM
    employee AS e

from

where

= 相当于js中 ==

SELECT
    * 
FROM
    employee AS e 
WHERE
    e.`name` = '张云'

in：在什么之中

SELECT
    * 
FROM
    department AS d 
WHERE
-- 在公司id 1 或 在公司id 3 的部门
d.companyId IN(1,3)

is：为null值

SELECT
    * 
FROM
 employee
--  查询那些员工地址为null 空值 与 null 不相等
 WHERE location IS NULL

is not：不是null值

SELECT
    * 
FROM
 employee
--  查询那些员工地址为null 空值 与 null 不相等
 WHERE location IS NOT NULL

< > <= >= ：小于 大于 小于或等于 大于或等于

between：在什么之间

SELECT
    * 
FROM
    department AS d 
WHERE
-- 在公司id 1 到 公司id3 之间的部门
d.companyId BETWEEN 1 AND 3

like：模糊搜索

% 和_

SELECT
    * 
FROM
 employee
--  %任意字符
-- 匹配袁前面或后面任意字符
 WHERE `name` LIKE '%袁%'

SELECT
    * 
FROM
 employee
--  '_'一个字符
-- 匹配性袁且名为一个字
 WHERE `name` LIKE '袁_'

and：并且 相当于 &&

and 优先级比or高

or：或者

SELECT * from employee
WHERE `name` like '张%' and (ismale=0 and salary>=12000
or
birthday>='1996-1-1');

order by：排序

asc 升序

SELECT * 
FROM employee
-- 升序排列
ORDER BY birthday ASC

desc 降序

SELECT * 
FROM employee
-- 降序排列
ORDER BY birthday DESC

limit：n,m跳过n条数据，取出m条数据

SELECT * 
FROM employee
-- 降序排列
ORDER BY birthday DESC
-- 分页
LIMIT 0, 5

运行顺序

• 先从from开始
• where
• select
• order by
• limit
• select

  练习
  

  ```sql — 查询user表，得到账号为admin，密码为123456的用户 — 登录

SELECT * from user WHERE loginid = ‘admin’ and loginpwd = ‘123123’;

```sql
-- 查询员工表，按照员工的入职时间降序排序，并且使用分页查询
-- 查询第3页，每页5条数据
-- limit (page-1)*pagesize, pagesize
SELECT * FROM employee
ORDER BY employee.joinDate desc
LIMIT 10,5

-- 查询工资最高的女员工
SELECT * FROM employee
WHERE ismale = 0
ORDER BY salary desc
limit 0,1;

-- 查询女性2000年以后入职或出生日期在1998年以后的工资情况
SELECT
    `name`,
    location,
    joinDate,
    birthday,
    ismale,
CASE
        WHEN salary >= '8000' THEN
        '高工资' 
        WHEN salary >= '5000' THEN
        '中等工资' ELSE '低工资' 
    END salary 
FROM
    employee
WHERE ismale = 0 AND (joinDate > '2000-1-1' OR birthday>'1998-1-1')

资料

companydb.sqlstaff.sql