knex 单表查询_数据库查询之单表查询

单表查询的语法以及关键字执行的优先级

单表查询语句

select distinct 字段, 字段2... from 表名

where 条件

group by field

having 筛选

order by field

limit 限制条数

关键字执行的优先级

from# 找到表

where# 利用where后的指定约束条件, 去文件/表中取出一条记录

group by# 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

select# 执行

distinct# 去重

having# 将分组的结果进行having过滤

order by# 将结果按条件排序

limit# 限制结果的显示条数

简单查询

1. 简单查询

select id, name, age from t1; # 根据字段查询

select * from t1;# 查询所有字段

2. 避免重复 (distinct)

select distinct post from t1;

3. 通过四则运算查询

select name, salary*5 from t1; # 显示薪水的5倍

select name, salary*5 as s from t1; # 以别名的方式显示薪水 (字段 as 别名)

select name, salary*5 s from t1; # 以别名的方式显示薪水

4. 定义显示格式

CONCAT() 函数用于连接字符串

select concat('姓名: ', name, ' 年薪: ', salary) as 临时字段名 from t1;

CONCAT_WS() 第一个参数为分隔符

select concat_ws(':', name, salary) as 临时字段名 from t1;

结合CASE语句

select

(

case# 判断开始的标志

when name = 'zhansan' then# if 语句

name# 输出符合条件的值

when name = 'lisi' then# elif 语句

concat(name, '_cool')

else# else 语句

concat(name, 'cool')

end# 判断结束的标志

) as new_name from t1;

注意: 当使用 concat 或 concat_ws 连接字符串是最好设置别名, 否则就会拿语句作为字段名

设置别名是的字段名: message

不设置别名的字段名: concat_ws(':', name, salary)

字段显示: (姓名: zhansan 年薪: 87603.96) 或 (zhansan : 36501.65)

where (约束)

where字句中可以使用:

1. 比较运算符：> < >= <= <> !=

2. between ... and ... # 值在80到100之间

3. in(80,90,100) # 值是80或90或100

4. like 'e%'

% 表示任意多字符

_ 表示一个字符

5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

1. 单条件查询

select name from t1 where id=5;

2. 多条件查询

select name, salary from t1 where post='teacher' and salary>10000;

3. 关键字 between... and...

select name, salary from t1 where salary between 9000 and 15000;

select name, salary from t1 where salary not between 9000 and 15000;

4. 关键字 is null (判断某个字段是否为 NULL不能用等号, 需要用 is)

select name, post_comment from t1 where post_comment IS NULL;

select name, post_comment from t1 where post_comment IS NOT NULL;

# 注意post_comment='', ''是空字符串, 不是null

5. 关键字 in 集合查询

select name, salary from t1 where salary IN (3000, 4000, 5000, 9000);

select name, salary from t1 where salary NOT IN (3000, 4000, 5000, 9000);

6. 关键字 like 模糊查询

1) 通配符 '%'

select * from t1 where name like 'zhang%';

2) 通配符 '_'

select * from t1 where name like '张_';

group by (分组)

1. 单独使用 GROUP BY 关键字进行分组

select post from t1 group by post;

注意: 按照post字段分组, select查询字段只能是post, 想获取组内其他信息, 要借助函数.

2. GROUP BY 关键字和 GROUP_CONCAT() 函数一起使用.

select post, group_concat(name) from t1 group by post; # 按岗位分组, 并查看组内名字.

select post, group_concat(name) as name from t1 group by post;

3. GROUP BY 和聚合函数一起使用

select post, count(id) as count from t1 group by post; # 按岗位分组, 并查看人数

注意:

如果用 unique 的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义

多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

聚合函数

# 聚合函数聚合的是组的内容, 若没有分组, 则默认一组.

1. 统计计数: COUNT()

select count(*) from t1;

select count(*) from t1 where depart_id=1;

2. 最大值: MAX()

select max(salary) from t1;

3. 最小值: min()

select min(salary) from t1;

4. 平均值: AVG()

select avg(salary) from t1;

5. 求和: SUM()

select sum(salary) from t1;

select sum(salary) from t1 where depart_id=2;

having (过滤)

# 执行优先级从高到低: where > group by > having

1. where 发生在分组 group by之前, 因而 where中可以有任意字段, 但绝不能使用聚合函数.

2. Having 发生在 group by之后, 因而 Having中可使用分组字段, 无法直接取其他字段, 可使用聚合函数.

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

select post, GROUP_CONCAT(name) as name, count(id) as total from t1

GROUP BY post HAVING COUNT(id) > 5;

2. 查询各岗位平均薪资大于10000的岗位名、平均工资

select post, AVG(salary) as avg from t1 group by post HAVING AVG(salary)>10000;

3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

select post, avg(salary) as avg from t5

GROUP BY post HAVING AVG(salary)>10000 and AVG(salary)<20000;

order by (查询排序)

1. 按单列排序 (降序: desc 升序: asc)

select * from t1 order by salary; # 默认升序

select * from t1 order by salary desc; # 降序

2. 按多列排序: 按照字段顺序先后排序, 先按年纪排序, 如果年纪相同, 则按照薪资排序.

select * from t1 order by age, salary desc;

limit (限制)

1. 限制查询的记录数: limit 值1(查询起始位置, 默认为0), 值2(查询条数)

1) 按照薪资查询前五条数据

select * from t1 order by salary desc limit 5; # 默认初始位置为0

select * from t1 order by salary desc limit 0, 5;

2) 从第5条开始，即先查询出第6条，然后包含这一条在内往后查5条

select * from t1 order by salary desc limit 5, 5;

正则表达式查询

SELECT * FROM t1 WHERE name REGEXP '^li';

SELECT * FROM t1 WHERE name REGEXP 'si$';

SELECT * FROM t1 WHERE name REGEXP 'm{2}';

小结：对字符串匹配的方式

WHERE name = 'egon';

WHERE name LIKE 'yua%';

WHERE name REGEXP 'on$';

测试数据

company.employee

员工id id int

姓名 name varchar

性别 sex enum

年龄 age int

入职日期 hire_date date

岗位 post varchar

职位描述 post_comment varchar

薪水 salary double

办公室 office int

部门编号 depart_id int

# 创建表

create table t1(

id int not null unique auto_increment,

name varchar(20) not null,

sex enum('male','female') not null default 'male',

age int(3) unsigned not null default 28,

hire_date date not null,

post varchar(50),

post_comment varchar(100),

salary double(15,2),

office int,

depart_id int

);

# 查看表结构

mysql> desc employee;

+--------------+-----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| sex | enum('male','female') | NO | | male | |

| age | int(3) unsigned | NO | | 28 | |

| hire_date | date | NO | | NULL | |

| post | varchar(50) | YES | | NULL | |

| post_comment | varchar(100) | YES | | NULL | |

| salary | double(15,2) | YES | | NULL | |

| office | int(11) | YES | | NULL | |

| depart_id | int(11) | YES | | NULL | |

+--------------+-----------------------+------+-----+---------+----------------+

# 插入记录: 三个部门 -> 教学，销售，运营

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values

('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部

('zhangsan','male',78,'20150302','teacher',1000000.31,401,1),

('lisi','male',81,'20130305','teacher',8300,401,1),

('yuanhao','male',73,'20140701','teacher',3500,401,1),

('liwenzhou','male',28,'20121101','teacher',2100,401,1),

('jingliyang','female',18,'20110211','teacher',9000,401,1),

('jinxin','male',18,'19000301','teacher',30000,401,1),

('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门

('丫丫','female',38,'20101101','sale',2000.35,402,2),

('丁丁','female',18,'20110312','sale',1000.37,402,2),

('星星','female',18,'20160513','sale',3000.29,402,2),

('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门

('程咬金','male',18,'19970312','operation',20000,403,3),

('程咬银','female',18,'20130311','operation',19000,403,3),

('程咬铜','male',18,'20150411','operation',18000,403,3),

('程咬铁','female',18,'20140512','operation',17000,403,3);