mysql数据库之单表查询
单标查询
SELECT DISTINCT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
from where group by select distinct having order by limit
1.找到表 :from
2.拿着where指定的约束条件,去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
4.这行select(去重)
5.将分组的结果进行having过滤
6.将结果按条件排序:order by
7.限制结果的显示条数
#简单查询 select 字段名字,字段名字 ,字段名字 from 表名 ----名字查询 select * from 表名 -----查询所有 #避免重复DISTINCT select distinct 字段名字 from 表名; #定义显示格式 concat() 函数用于连接字符串 select concat('姓名:',字段名) from 表名; concat_ws() 第一个参数为分隔线 select concat_ws(':',字段名) from 表名; #通过四则运算查询 select 字段名, salary*12(月薪*12) as annual_salary(年薪) from 表名#运算加重新定义名字
where字句中可以使用:
1.比较运算符:>＜ ＞＝ ＜＝ ＜＞ ！＝
2.between 80 and 100 #值在80到100之间
3.in(80,10) #值是80或10
4.like 'e%'
通配符可以是%或_,
%表示任意多字符
_表示一个字符
5.逻辑运算符 and or not
#单条件查询 select 字段名 from 表名 where 条件; 例:select emp_name from employee where post='sale'; #多条件查询 select 字段名 from employee where 条件 and 条件 例:SELECT emp_name,salary FROM employee WHERE post='teacher' AND salary>10000; #关键字between and 例: SELECT emp_name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; SELECT emp_name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000; #关键字 IS NULL(判断某个字段是否为null不能用等号,需要用IS) 例:SELECT emp_name,post_comment FROM employee WHERE post_comment IS NULL; SELECT emp_name,post_comment FROM employee WHERE post_comment IS NOT NULL; 注意是空字符串,不是null #关键字IN集合查询 例:SELECT emp_name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; SELECT emp_name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ; SELECT emp_name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ; #关键字like模糊查询 例: 通配符’%’ SELECT * FROM employee WHERE emp_name LIKE 'eg%'; 通配符’_’ SELECT * FROM employee WHERE emp_name LIKE 'al__';
#单独使用group by 关键字分组 select post from employee group by post; #group by关键字和group_concat()函数一起使用 select post,group_concat(emp_name) from employee group by post; #按照岗位分组,并查询组内成员名字 #group by 与聚合函数一起使用 select post,count(id) from employee group by post; #按照岗位分组,并查看每个有多少人 ''' 强调: 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据 '''
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 示例： SELECT COUNT(*) FROM employee; SELECT COUNT(*) FROM employee WHERE depart_id=1; SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee; SELECT SUM(salary) FROM employee WHERE depart_id=3;
HAVING与WHERE不一样的地方在于!!!!!! #！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
mysql> select @@sql_mode; +--------------------+ | @@sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ row in set (0.00 sec) mysql> select * from emp where salary > 100000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ row in set (0.00 sec) mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' mysql> select post,group_concat(emp_name) from emp group by post having avg(salary) > 10000; +-----------+-------------------------------------------------------+ | post | group_concat(emp_name) | +-----------+-------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | +-----------+-------------------------------------------------------+ rows in set (0.00 sec) 验证
按单列排序 SELECT * FROM employee ORDER BY salary; SELECT * FROM employee ORDER BY salary ASC; #升序 SELECT * FROM employee ORDER BY salary DESC;#降序 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序 SELECT * from employee ORDER BY age, salary DESC;
示例： SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默认初始位置为0 SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
SELECT * FROM employee WHERE emp_name REGEXP '^ale'; #以ale开头 SELECT * FROM employee WHERE emp_name REGEXP 'on$'; #以on结尾 SELECT * FROM employee WHERE emp_name REGEXP 'm{2}'; #连续的两个m
