jinquan26 发表于 2018-10-6 07:16:31

mysql四-1:单表查询

  一、单表查询的语法
  SELECT 字段1,字段2... FROM 表名
  WHERE 条件
  GROUP BY field
  HAVING 筛选
  ORDER BY field
  LIMIT 限制条数
  二、关键字的执行优先级(重点)
  关键字的执行优先级重点中的重点!!!
  from--->where--->group by--->having--->select--->distinct--->order by--->limit
  1.找到表:from
  2.拿着where指定的约束条件,去文件/表中取出一条条记录
  3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
  4.将分组的结果进行having过滤
  5.执行select
  6.去重
  7.将结果按条件排序:order by
  8.限制结果的显示条
  三、简单查询
  1、造表
  创建表
  mysql> create table employee(

  >  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;
  插入记录(三个部门:教学,销售,运营)
  mysql> insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
  ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
  ('alex','male',78,'20150302','teacher',1000000.31,401,1),
  ('wupeiqi','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)
  ;
  2、查询
  简单查询

  SELECT>  SELECT * FROM employee;
  SELECT name,salary FROM employee;
  避免重复DISTINCT
  SELECT DISTINCT post FROM employee;#distinct意思是输出后面 列 去重后的内容;
  通过四则运算查询
  SELECT name, salary*12 FROM employee;
  SELECT name, salary*12 AS Annual_salary FROM employee;
  SELECT name, salary*12 Annual_salary FROM employee;
  定义显示格式
  #CONCAT() 函数用于连接字符串
  SELECT CONCAT('姓名: ',name,'年薪: ', salary*12)AS Annual_salary FROM employee;
  #CONCAT_WS() 第一个参数为分隔符
  SELECT CONCAT_WS(':',name,salary*12)AS Annual_salary FROM employee;
  结合CASE语句:
  SELECT
  (
  CASE
  WHEN NAME = 'egon' THEN
  NAME
  WHEN NAME = 'alex' THEN
  CONCAT(name,'_BIGSB')
  ELSE
  concat(NAME, 'SB')
  END
  ) as new_name
  FROM employee;
  3、小练习
  1)查出所有员工的名字,薪资,格式为
     
  SELECT CONCAT('','') AS annual_year FROM employee;
  2)查出所有的岗位(去掉重复)
  SELECT DISTINCT depart_id FROM employee;
  3)查出所有员工名字,以及他们的年薪,年薪的字段名为annual_year
  SELECT name, salary*12 AS annual_year FROM employee;
  四、WHERE约束
  where字句中可以使用:
  1. 比较运算符:> < >= 10000;
  关键字BETWEEN AND:
  SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;
  SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;
  关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS):
  SELECT name,post_comment FROM employee WHERE post_comment IS NULL;
  SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL;
  SELECT name,post_comment FROM employee WHERE post_comment=''; 注意''是空字符串,不是null
  ps:

  执行update employee set post_comment='' where>  关键字IN集合查询:
  SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
  SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;
  SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;
  关键字LIKE模糊查询:
  通配符’%’:SELECT * FROM employee WHERE name LIKE 'eg%';
  通配符’_’:SELECT * FROM employee WHERE name LIKE 'al__';
  小练习:
  1. 查看岗位是teacher的员工姓名、年龄
  SELECT name,age FROM employee WHERE post='teacher';
  2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
  SELECT name,age FROM employee WHERE post='teacher' AND age>30;
  3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
  SELECT name,age,salary FROM employee WHERE post='teacher' AND salary between 9000 and 10000;
  4. 查看岗位描述不为NULL的员工信息
  select * from employee where post_comment is not null;
  5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
  SELECT name,age,salary FROM employee WHERE post='teacher' AND salary in (10000,9000,30000);
  6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
  SELECT name,age,salary FROM employee WHERE post='teacher' AND salary not in (10000,9000,30000);
  7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
  SELECT name,salary FROM employee WHERE post='teacher' AND age like 'jin%';
  五、分组查询:GROUP BY
  1、什么是分组?为什么要分组?
  1)首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的
  2)分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等
  3)为何要分组呢?
  取每个部门的最高工资
  取每个部门的员工数
  取男人数和女人数
  小窍门:‘每’这个字后面的字段,就是我们分组的依据
  4)大前提:
  可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数
  2、ONLY_FULL_GROUP_BY
  查看MySQL 5.7默认的sql_mode如下:
  mysql> select @@global.sql_mode;
  ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  !!!注意
  ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。
  设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式):
  mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  3、GROUP BY
  单独使用GROUP BY关键字分组
  SELECT post FROM employee GROUP BY post;
  注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数
  GROUP BY关键字和GROUP_CONCAT()函数一起使用
  SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组,并查看组内成员名
  SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
  GROUP BY与聚合函数一起使用
  select post,count(id) as count from employee group by post;#按照岗位分组,并查看每个组有多少人
  4、聚合函数
  强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
  示例:
  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;
  5、小练习:
  1)查询岗位名以及岗位包含的所有员工名字
  SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;
  2)查询岗位名以及各岗位内包含的员工个数
  SELECT post,count(id) FROM employee GROUP BY post;
  3)查询公司内男员工和女员工的个数
  SELECT sex,count(id) FROM employee GROUP BY sex;
  4)查询岗位名以及各岗位的平均薪资
  SELECT post,AVG(salary) FROM employee GROUP BY post;
  5)查询岗位名以及各岗位的最高薪资
  SELECT post,MAX(salary) FROM employee GROUP BY post;
  6)查询岗位名以及各岗位的最低薪资
  SELECT post,MIN(salary) FROM employee GROUP BY post;
  7)查询男员工与男员工的平均薪资,女员工与女员工的平均薪资
  SELECT sex,AVG(salary) FROM employee GROUP BY sex;
  六、HAVING过滤
  HAVING与WHERE不一样的地方在于!!!!!!
  !!!执行优先级从高到低:where > group by > having
  1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
  2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
  小练习:
  1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
  SELECT post,group_concat(name),count(id) FROM employee GROUP BY post having count(id)10000;
  4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
  SELECT post,AVG(salary) FROM employee GROUP BY post having AVG(salary)>10000 and AVG(salary)10000 ORDER BY avg(salary);
  3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
  SELECT post,avg(salary) from employee group by post having avg(salary)>10000 ORDER BY avg(salary) DESC;
  八、限制查询的记录数:LIMIT
  示例:
  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条
  小练习:
  1. 分页显示,每页5条
  SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5;
  SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5;
  SELECT * FROM employee ORDER BY salary DESC LIMIT 10,5;
  九、使用正则表达式查询
  SELECT * FROM employee WHERE name REGEXP '^ale';
  SELECT * FROM employee WHERE name REGEXP 'on$';
  SELECT * FROM employee WHERE name REGEXP 'm{2}';
  小结:对字符串匹配的方式
  WHERE name = 'egon';
  WHERE name LIKE 'yua%';
  WHERE name REGEXP 'on$';
  小练习:
  查看所有员工中名字是jin开头,n或者g结果的员工信息
  SELECT * FROM employee WHERE name REGEXP '^jin.*';

页: [1]
查看完整版本: mysql四-1:单表查询