#1.查询单个字段#2.查询表中的多个字段#3.查询所有的字段select*from employee;--这个顺序是固定的select`last_name`,`email`,....from employee;--可以排顺序 ` `符号可以加可以不加 , 强调是个字段名 ,与关键字区分 ,提高可读性#4.查询常量值select100;-- 直接获得常量值select'聂捷';--字符串常量#5.查询表达式select100%98;#6.查询函数select VERSION();--可以查询自己创建或者数据库自带的函数#7.起别名 (为字段名起别名 如改sex 为性别)--作用 一是便于理解 二是连接查询有重名可以用于区分select sex AS 性别 from employees;select sex 性别 ,last_name 姓 from employees;--当别名有特殊符号时要加双引号select salary AS"out put"From empoyees;#8.去重--查询所有的部门编号selectDISTINCT department_id from employees;#9. +号的作用 --+ 号在sql中只作为运算符 '123'+23 会转换为数字计算--转换不成功则将字符串转换为0select ‘john’+90;--结果为o--一方为null全为nullselectnull+10;--结果为null;#10.字符串拼接 concat函数select Concat('a','b','c')as 结果;select Concat(last_name,first_name)as 姓名 from employees;#11.
条件查询
-- 一、条件查询-- 1.按条件表达式筛选#员工工资大于12000的员工信息SELECT*FROM
employees
WHERE
salary>12000;#查询部门编号不等于90 的员工和部门编号SELECT
last_name,
department_id
FROM
employees
WHERE
department_id<>90;-- <>为不等于-- 2、按逻辑表达式筛选 and or not && || !-- &&两个表达式都为true 这个是java的长路与 能判断时只运行左边 即当左边为false右边不计算#查询工资在10000到20000之间的员工名以及奖金SELECT first_name,salary,commission_pct from employees where salary>=10000AND salary<=20000;#查询部门编号不是在90到110之间的,或者工资高于15000的员工信息SELECT*from employees wherenot(department_id>=90and department_id<=110)or salary>15000;-- 3、模糊查询-- like BETWEEN and in is null is not NULL# like 和通配符一起使用 -- % 任意多个字符 ,包含0个字符-- _ 下划线表示一个字符# 查询员工名中包含字母a的员工信息SELECT*FROM employees WHERE last_name like'%a%';-- 字符型用单引号 %为通配符select*from employees where last_name like'____a%';-- _ 占一个字符# 查询员工名第二个字符为_ 的员工名SELECT last_name from employees where last_name like'_\_%';-- \为转义字符select last_name from employees WHERE last_name like'_$_%'ESCAPE'$';-- 定义$为转义字符#查询员工编号 在100到120之间的员工信息SELECT*from employees WHERE employee_id BETWEEN100and120;-- ba 包含临界值#查询员工的工种编号是 IT_PPOT AD_VP 使用in匹配 ()里的列表select last_name,job_id FROM employees WHERE job_id in(IT_PROT,AD_VP,AD_PRES);-- in关键字匹配字符-- in列表的类型必须兼容 不可以使用通配符 like 才可以#查询没有奖金的员工和奖金率select last_name,commission_pct from employees WHERE commission_pct isnull;-- 判断null要用这个SELECT last_name,commission_pct from employees WHERE commission_pct isnotnull;#安全等于 <=> 可以判断null 和 普通值select last_name,commission_pct from employees where commission_pct <=>null;select last_name,salary from employees WHERE salary <=>12000;select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0))as 年薪
from employees;-- 阶段练习select*from employees WHERE job_id <>'IT'and salary =12000;desc departments;selectdistinct location_id from departments ;-- 以下存在null值 结果是不一样的 后面一句匹配不到null值SELECT*from employees ;select*from employees WHERE commission_pct like'%%'and last_name like'%%';
排序查询
-- 二、排序查询-- 语法 select 查询列表 from 表 where 条件 order by 排序列表 asc desc -- asc 升序 (默认) desc 降序# 查询员工信息,要求工资从高到低SELECT*from employees orderby salary asc;#查询部门编号>=90的员工信息 按入职先后排序select*from employees WHERE department_id>=90ORDERBY hiredate DESC;# 按表达式排序select*,salary*12*(1+commission_pct)as 年薪 from employees ORDERBY 年薪 desc;# 按名字长度排序select LENGTH(last_name) 字节长度,last_name FROM employees ORDERBY 字节长度 desc;# 多个字段排序 按第一个字段 重复的按第二个字段select*from employees ORDERBY salary asc,last_name desc;-- sql执行顺序 from -> WHERE -> select ->order by 先锁定表 再筛选行 再筛选列 再排序-- 练习# 选择工资不在8000到17000 的员工的姓名和工资 再按照部门编号升序排列select last_name,salary ,department_id from employees WHERE salary <8000or salary>17000orderby department_id asc;-- 也可以使用 not BETWEEN and select last_name,department_id,salary from employees WHERE salary notBETWEEN8000and15000ORDERBY salary desc;# 查询员工名字里包含‘a'的员工的薪水 并且按降序排列select last_name ,salary from employees WHERE last_name like'%a%'ORDERBY salary desc;select email ,LENGTH(email) 邮件长度 from employees ORDERBY LENGTH(email);
-- 分组函数 聚合函数 组函数 统计函数 一组值得到一个值-- 分类 sum avg max min count -- 1. 简单函数 sum selectsum(salary)from employees;selectavg(salary)from employees;selectround(avg(salary),2)from employees;selectcount(salary)from employees;selectmax(salary) 最大工资,min(salary)最小工资 from employees;-- 2.参数类型支持哪些类型-- avg sum 处理数值类型 max min count 可以用在非数值类型selectsum(last_name)from employees;-- 已经没有意义 处理数值类型 -- 3.是否忽略null 值 是忽略null值的select commission_pct from employees;selectcount(*),count(commission_pct)from employees;selectsum(commission_pct),avg(commission_pct),sum(commission_pct)/107,sum(commission_pct)/35from employees;-- 4.和distinct 搭配selectsum(distinct salary),sum(salary)from employees;selectcount(distinct salary)from employees;-- 计算有几种工资-- 5. count 函数详细-- 支持四种重载selectcount(*)from employees ;# 每行只要有一个不为null就统计selectcount(1)from employees;# 相当于加了一行全为1 统计1的行数-- 以上两种效率 myisam 由内核计数器 count(*) 的效率高-- innodb 效率差不多 都是统计行数 比count(字段) 效率高-- 6.和分组函数一同查询的字段有限制 要求是group by 的字段selectavg(salary),employee_id from employees;-- 这个虽然没报错但是不对-- 练习1 查询员工工资的最大值,最小值,平均值,总和selectmax(salary),avg(salary),count(salary)from employees;-- 练习2 查询员工表中的最大入职天数和最小入职天数的差值selectmax(hiredate)-min(hiredate)from employees;-- datediff -- 求两个日期的差
· select datediff(max(hiredate),min(hiredate))from employees;-- 练习员工号为90 的 员工个数selectcount(*)from employees where department_id =90;
分组查询
-- 分组查询 group by /*
(1) FROM:对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积,产生虚拟表VT1;
(2) ON: 对虚拟表VT1进行ON筛选,只有那些符合<join_condition>的行才被插入虚拟表VT2;
(3) JOIN: 如果指定了OUTER JOIN(如LEFT OUTER JOIN、RIGHT OUTER JOIN),那么保留表中未匹配的行作为外部行添加到虚拟表VT2,产生虚拟表VT3。如果FROM子句包含两个以上的表,则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3,直到处理完所有的表;
(4) WHERE: 对虚拟表VT3应用WHERE过滤条件,只有符合<where_condition>的记录才会被插入虚拟表VT4;
(5) GROUP By: 根据GROUP BY子句中的列,对VT4中的记录进行分组操作,产生VT5;
(6) CUBE|ROllUP: 对VT5进行CUBE或ROLLUP操作,产生表VT6;
(7) HAVING: 对虚拟表VT6应用HAVING过滤器,只有符合<having_condition>的记录才会被插入到VT7;
(8) SELECT: 第二次执行SELECT操作,选择指定的列,插入到虚拟表VT8中;
(9) DISTINCT: 去除重复,产生虚拟表VT9;
(10) ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作,产生虚拟表VT10;
(11) LIMIT: 取出指定街行的记录,产生虚拟表VT11,并返回给查询用户
*/-- 1. select 分组函数 ,列 from 表 where 条件 GROUP BY 分组列表,order by 子句-- 执行顺序 from WHERE GROUP BY SELECT ORDER BY-- 查询每个工种的最高工资selectmax(salary),job_id from employees GROUPBY job_id;-- 查询每个位置的部门个数selectcount(*),location_id from departments GROUPBY location_id;-- 2.添加筛选条件selectavg(salary),department_id from employees where email like'%a%'GROUPBY department_id;-- 3.查询复杂筛选条件-- 查询哪个部门的员工个数大于2selectcount(*),department_id from employees GROUPBY department_id
HAVINGcount(*)>2;-- having 进行分组后的筛选-- 4.查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资-- 查询每个工种有奖金的员工的最高工资-- 原始表可以筛选用where 分组后用 HAVING selectmax(salary),job_id from employees WHERE commission_pct isnotNULLGROUPBY job_id havingmax(salary)>12000;-- 按工种分类-- 添加复杂筛选条件 having 子句在分组后筛选 where 分组前筛选-- 分组函数做条件一定放在having子句中-- 1. 查询那个部门的员工个数大于2select department_id ,count(employee_id)from employees GROUPBY department_id havingcount(employee_id)>2;select department_id ,employee_id from employees;-- 2. 查询每个部门有奖金的员工的最高工资大于12000的工种编号和最高工资select department_id ,max(salary)from employees WHERE commission_pct isnotnullgroupby department_id HAVINGmax(salary)>12000;select job_id ,max(salary)from employees WHERE commission_pct isnotnullgroupby job_id HAVINGmax(salary)>12000;-- 3.查询领导编号大于102的每个领导的手下最低工资>5000的领导编号是哪个,以及最低工资selectmin(salary),manager_id from employees where manager_id >102GROUPBY manager_id havingmin(salary)>5000;-- 按表达式分组-- 1.按照员工的姓名长度分组,查询每一组员工的个数,筛选员工个数大于5的有哪些selectcount(*),CONCAT(first_name,last_name)from employees GROUPBY LENGTH(concat(last_name,first_name))havingcount(*)>5;-- GROUP BY 和 having 支持使用select 中定义的别名-- where 子句中是不支持的selectcount(*) c,LENGTH(CONCAT(first_name,last_name)) len from employees GROUPBY len having c>5;-- 2. 查询每个部门每个工种的平均工资 (多个条件进行分组,两个条件相同的分组)selectavg(salary),department_id,job_id from employees GROUPBY department_id,job_id;-- 添加排序-- 1. 查询每个部门每个工种的平均工资并按工资的降序排列selectavg(salary),department_id,job_id from employees GROUPBY department_id,job_id ORDERBYavg(salary)desc;-- order by 也支持别名 selectavg(salary) pj,department_id di,job_id from employees WHERE department_id isnotnullGROUPBY department_id,job_id ORDERBY pj desc;# 总结: GROUP BY 子句 可以根据单个或者多个字段分组#还可以根据函数分组#可以添加排序# 筛选时候 在分组前的条件用where where 不支持别名的# 筛选在分组后使用的是having子句 having支持别名
连接查询
sql 92
-- 连接查询 多表连接 -- 当查询字段来自多个表的时候-- 笛卡尔乘积 : 在没有条件的连接时select*from beauty;select*from boys;select name , boyname from boys,beauty where beauty.boyfriend_id=boys.id;-- 连接查询的分类-- 按年代分类 -- sql 92 仅支持内连接-- sql 99 (推荐) 除外连接的全外连接不支持其他都支持-- 按功能分类-- 1. 内连接-- 等值连接-- 非等值连接-- 自连接-- 2. 外连接-- 左外连接-- 右外连接-- 全外连接-- 3. 交叉连接-- 一、sql 92标准-- 等值连接 实现:全部匹配后筛选 where可以为表取别名,为select语句提高便利-- 1. 当from语句起了别名 那么select语句必须使用别名类限定-- 查询女神名和对应的男神名 select name,boyname from boys ,beauty where beauty.boyfriend_id=boys.id;-- 查询员工名和对应的部门mingselect last_name,department_name from employees,departments WHERE employees.department_id=departments.department_id;-- 查询员工名、工种号、工种名 select last_name ,employees.job_id,job_title from employees,jobs where employees.job_id = jobs.job_id;-- 2. 添加筛选条件 连接筛选用的是and-- 查询有奖金的员工名,部门名select last_name ,department_name,commission_pct from employees e ,departments d WHERE e.department_id=d.department_id and e.commission_pct isnotnull;-- select last_name ,e.job_id,job_title from employees as e,jobs where e.job_id = jobs.job_id;-- 3. 查询城市名中第二个字符为o的部门名和城市名select d.department_name,l.city from departments d ,locations l WHERE d.location_id=l.location_id and subStr(l.city ,2,1)='o';select substr(city,2,1)from locations;-- 4.加分组 -- 查询每个城市的部门个数selectcount(*), city from locations l ,departments d where l.location_id = d.location_id GROUPBY city;-- 查询出有奖金的每个部门的部门名和部门领导标号的和该部门的最低工资selectmin(salary),d.department_name,d.manager_id from employees e ,departments d WHERE e.department_id=d.department_id and commission_pct isnotnullGROUPBY e.department_id;-- 6.加排序-- 查询每个工种的工种名和员工的个数,并按员工个数降序select j.job_title ,count(*)from employees e,jobs j WHERE e.job_id=j.job_id GROUPBY e.job_id ORDERBYcount(*)desc;-- 7.可以三表连接-- 查询员工名,部门名和所在城市select e.first_name ,d.department_name ,l.city from employees e, departments d,locations l WHERE e.department_id=d.department_id and d.location_id=l.location_id;-- 总结 -- ① 多表等值连接的结果是多表的交集部分-- ② n表连接,至少需要n-1个连接条件-- ③ 多表的顺序没有要求-- ④ 一般需要为表起别名-- ⑤ 可以搭配其他的子句 排序 分组等-- 二、非等值连接 把条件改为非等的条件-- 查询员工的工资和工资级别select*from job_grades;select salary,grade_level from employees e , job_grades g WHERE salary BETWEEN g.lowest_sal and g.highest_sal ORDERBY e.salary;-- 三、自连接 连接自己-- 查询 员工名和领导编号 领导也在自己的员工表中select e1.first_name ,e2.employee_id from employees e1 ,employees e2 WHERE e1.employee_id=e2.employee_id;-- 取别名select e.first_name ename,m.first_name manager from employees e,employees m WHERE e.manager_id=m.employee_id and e.first_name like'S%';-- 练习-- 显示员工表的最大工资 平均工资selectmax(salary),avg(salary)from employees;-- 查询员工表的job_id 中包含a和c 并且 a在c的前面select job_id from employees WHERE job_id like'%a%e%';-- 获取当前函数 去掉前后空格 select CURDATE();selectNOW();select trim(" sd fsf ");-- trim('字符' from '要去的 ')select trim("_"from'_djsk_djf___');
sql99
-- sql 99 连接查询/*
语法
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
[WHERE 筛选条件]
【group by】
[having 筛选条件]
【order by 排序列表】
*/-- 内连接 √ : INNER JOIN-- 外连接-- 左外连接 √ :left 【outer】 JOIN-- 右外连接 √ :right 【outer】join-- 全外连接 :full 【outer】 JOIN-- 交叉连接 : CROSS-- 内连接-- 等值连接 -- 非等值连接 -- 自连接-- 等值连接 比92语法可读性更高 inner 可以省略 -- 1. 查询员工名,部门名select last_name ,department_name from employees e
innerjoin departments d on e.department_id = d.department_id;-- 2. 查询名字中包含e的员工名和工种名select last_name , job_title from employees e
innerjoin jobs j on e.job_id=j.job_id WHERE e.last_name like'%e%';-- 3. 添加分组和筛选-- 查询部门个数大于3的城市名和部门个数-- 先查询每个城市的部门个数-- 子筛选满足条件的selectcount(department_id),city from departments d INNERJOIN locations l on d.location_id = l.location_id
GROUPBY city havingcount(*)>3;-- 4. 添加排序---- 5. 查询员工名,部门名,工种名,安照部门名降序select last_name ,department_name,job_title from employees e
innerjoin departments d on d.department_id = e.department_id
innerjoin jobs j on j.job_id = e.job_id
ORDERBY department_name desc;-- 非等值连接-- 查询工资级别select salary ,grade_level from employees e join job_grades g
ON e.salary BETWEEN g.lowest_sal and g.highest_sal;-- 自连接-- 查询员工名字,上级名字select e.last_name ,m.last_name from employees e
INNERJOIN employees m on e.manager_id = m.employee_id;-- 筛选以上 中 员工名包含e的select e.last_name ,m.last_name from employees e
INNERJOIN employees m on e.manager_id = m.employee_id
WHERE e.last_name like'%z%';-- 外连接 查询一个表中有,一个表中没有的 也是笛卡尔积的运用 主表全显示 -- 从表没有显示null 结果分为两部分 一部分是连接条件成立 显示匹配的行-- 没有匹配的 主表显示, 从表显示null -- 在sql 中 左外连接 左边的是主表,右外连接右边的是主表 主表显示在前-- 左外连接-- 右外连接-- 引入:查询没有男朋友在男神表的女神名 select*from beauty ;select*from boys ;select name,boys.*from beauty LEFTJOIN
boys on beauty.boyfriend_id = boys.id
where boys.id isnulland name ='柳岩';-- 使用右外连接select b.name , bo.*from boys bo RIGHTJOIN
beauty b on bo.id=b.boyfriend_id;-- -- 查询哪个部门没有员工 先把有和没有的都查出来再筛选select d.*,e.employee_id from departments d LEFTJOIN
employees e on e.department_id = d.department_id
WHERE employee_id isnull;-- 全外 mysql 不支持-- 交叉连接select b.*,bo.*from beauty b crossjoin boys bo;
联合查询
-- 语法 查询语句一 union 查询语句二;-- 查询多个表 多个表中没有连接关系 但查询结果一致的情况select*from employees where email like"%a%"unionselect*from employees where department_id >90;select*from employees where email like"%a%"or department_id>90;-- 应用场景: 查询中国用户中年龄大于12的信息 以及外国用户中年邻大于-- 这个是两个表的信息select id,cname,csex from t_ca where csex='男'unionselect t_id ,tname ,tgender from t_ua WHERE t_gender ='male';-- 整个数据库表的数据 合并成一个结果 搜索 热点数据查询-- -- 注意事项 :如果查询的列数不同的情况是不行的 多条查询的列数是一致的-- :查询信息的数据类型 和数据的顺序最好一致-- :两个表的数据重复会自动去重 不希望去重要加上 union all