数据库里面的数据库sql文件：https://blog.csdn.net/GongmissYan/article/details/102937816
#基础查询/* 语法：、 select 查询列表 from 表名； 查询列表可以是，表中的字段，常量值 表达式，函数查询的表格是一个虚拟的表*/# 查询单个字段select last_name from employees# 查询多个字段select last_name ,salary,email from employees#查询表中的所有字段(尽量不要使用*如果字段明确使用字段)使用*号的顺序是和原始表的顺序是一样的select * from employees#查询常量值SELECT 100 as 这个是100#查询函数select VERSION()#起别名（在查询的时候如果有重名的情况，可以通过别名来区分）select 100 as 这里是别名select 100 这里是别名> 如果别名里面包含了关键字最好通过引号引用起来#去重:通过DISTINCT来去重SELECT DISTINCT department_id from employees# +号的作用#sql中的+号的作用只是做为运算符/** select 100+90两个操作数都是数值型，则做加法运算* select '123' + 90 如果一方为字符串类型，字符数值转换成数值类型* 如果转换成功就进行加法运算* 如果转换失败就将字符型数值置为0* select null + 90 在加法运算中不管那一方为Null结果都是null*/SELECT FROM employees#字符串拼接(在字符串拼接的时候使用的是CONCAT)select CONCAT(last_name,first_name) from employees# 条件查询/*select 查询列表 From 表名 where 筛选条件*/# 按条件表达式筛选select * from employees where salary > 12000#查询员工部门编号不是90的员工我姓名和部门的IDselect CONCAT(last_name,first_name) as 员工名,e.department_id from employees e WHERE department_id <> 90#查询工资在10000到20000之间的员工名，工资以及奖金SELECT CONCAT( last_name, first_name ) AS 员工名, salary AS 工资, commission_pct * salary AS 奖金 FROM employees WHERE salary > 10000 AND salary < 20000 # 查询部门编号不是在90到110之间，或者工资高于15000的员工信息SELECT * FROM employees WHERE department_id < 90 AND department_id > 110 OR salary > 15000# 模糊查询/** like* between and* in* is null |is not null*/# LIKE/** %任意多的字符包含0个* _ 任意单个字符*/SELECT * FROM employees WHERE last_name LIKE ( '%a%' )#查询员工名字第三个字符为e每5个字符为a的SELECT * from employees WHERE last_name LIKE '__e_a%'#查询第二个字符是_的SELECT last_name FROM employees WHERE last_name LIKE '_\_%';SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';#between and# 包含两个临界值的# 两个临界值不可以互换的#查询员工编号在100 到120之间的员工信息SELECT * FROM employees WHERE department_id BETWEEN 100 and 120# in# 查询员工的工种编号SELECT last_name, job_id FROM employees WHERE job_id IN ( 'AD_PRES', 'AD_VP', 'IT_PROT' )# is null# 案例：查询没有奖金的员工名和奖金率SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL#案例：查询有奖金的员工名和奖金率SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL # 案例：查询员工号为176的员工的姓名和部门号和年薪SELECT last_name, salary * 12 *(1+IFNULL(commission_pct,0)) FROM employees WHERE employee_id = '176' # 查询没有奖金，且工资小于18000的salay,last_nameSELECT salary, last_name FROM employees WHERE commission_pct IS NULL AND salary < 18000 # 查询employees表中，job_id不为'IT'或者工资为12000的员工信息SELECT * FROM employees WHERE job_id <> 'IT' OR salary = 12000 # 查看Departments结构DESC departments # 查询departments表中涉及到了哪些位置的编号SELECT DISTINCT location_id FROM departments WHERE location_id IS NOT NULL # 排序查询/** select 查询列表 from 表 【where筛选】 order by 排序列表[asc 升序|desc 降序]* 默认是asc asc可以不写*/# 案例：查询员工的信息，要求工资从高到低排序SELECT * FROM employees ORDER BY salary DESC # 案例：查询部门编号 >= 90 的员工信息，按入职时间SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate DESC # 案例：按年薪的高低显示员工的信息和年薪【按表达式】SELECT *, salary * 12 + ( 1+ IFNULL( commission_pct, 0 ) ) a FROM employees ORDER BY a DESC # 查询姓名的长度来显示员工的姓名和工资【按函数排序】SELECT LENGTH(last_name) a,salary FROM employees ORDER BY a DESC# 查询员工信息，要求先按员工资排序，再按员工编号排序SELECT * FROM employees ORDER BY salary ASC, employee_id DESC # 查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序SELECT last_name, department_id, 12 * salary + ( 1+ IFNULL( commission_pct, 0 ) ) 年薪FROM employeesORDER BY 年薪 DESC,last_name ASC# 选择工资不在8000到17000的员工的姓名和工资，按工资降序SELECT last_name, salary FROM employees WHERE !(salary BETWEEN 8000 AND 17000)ORDER BY salary DESC # 查询邮箱中包含e的员工信息，并按邮箱的字节数降序，再按部门号升序SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH( email ) DESC, department_id ASC # 常见函数/** 单行函数：* 字符函数* length、concat、upper、lower、substr、instr、trim、lpad、rpad、replace* 数学函数* round、ceil、floor、truncate* 日期函数* now、curdate、curtime、year、day、month、monthname、str_to_date、date_format* 其它函数* version、database、user* 流程控制函数* if、case**/# 案例：将姓变大写，名就能小写，然后拼接SELECT CONCAT(UPPER(first_name),'_',LOWER(last_name)) FROM employees; # 姓名中首字母大写，其它的字符小写，然后通过_拼接，显示出来SELECT CONCAT( UPPER( SUBSTR( last_name, 1, 1 ) ), '_', LOWER( SUBSTR( last_name, 1 ) ) ) FROM employees; # instr用于返回子串在父串里面的起始索引(第一次出现的索引) # trim 去掉前后面的空格# 案例去掉字符串里面的前后的aSELECT TRIM('a' FROM 'aaaaaaaBaaaa') # lpad 如果字符串的长度不为二参值，就会通过&来填满左侧SELECT lpad('兔兔',10,'&')# rpad 如果字符串的长度不为二参值，就会通过&来填满左侧SELECT rpad('兔兔',10,'&')#把下面字符串中的cc替换成zzSELECT replace('bbccaa','cc','zz')#四舍五入SELECT ROUND(-1.5)#小数点后面保留两位SELECT ROUND(-1.5456,2)#向上取整SELECT ceil(1.10)#向下取整SELECT FLOOR(1.1)# truncate 截断SELECT TRUNCATE(1.8999,1)# mod取余SELECT MOD(10,3)# now返回当前系统日期+时间SELECT now();# curdate返回当彰系统的日期 不包含时间SELECT CURDATE()#curtime 返回当前的时间，不包含日期SELECT CURTIME()#可以获取指定的部分SELECT year(NOW())SELECT MONTH(NOW()SELECT DAY(now())SELECT MONTHNAME(NOW())# 将字符串的日期转换成date格式SELECT STR_TO_DATE('2020-07-9','%Y-%c-%d')#将日期转换成字符SELECT DATE_FORMAT(NOW(),'%y年%c月%d日')#查询有奖金的员工名和入职日期(xx月/xx日 xx年) SELECT last_name, DATE_FORMAT( hiredate, '%m月/%d日 %Y年' ) FROM employees WHERE commission_pct IS NOT NULL # 查询员工是否有奖金有就显示有没有就显示没有 SELECT last_name, commission_pct, IF ( commission_pct, '有', '没有' ) FROM employees # 查询员工名，姓名，工资，以及工资提高百分之20%后的结果SELECT last_name,salary,salary * 1.2 FROM employees # 将员工的姓名按首字符排序，并写出姓名的长度(length) SELECT last_name FROM employees ORDER BY SUBSTR( last_name, 1, 1 ) ASC#分组函数/** 功能：用作统计使用，又称为聚合函数或统计函数或组函数* 分类：* sum求和 avg 平均 Max min count* sum avg 不可以放字符类型 日期也不可以* max min 是可以使用字符的 日期也支持* count 计算不为null个数，支持所有的类型 myisam存储引擎下效率最高，因为它有一个内部的计数器*/# 查询员工表中的最大入职时间和最小入职时间，并计算出它们的相差的天数SELECT DATEDIFF( MAX( hiredate ), MIN( hiredate ) ) FROM employees;# 查询员工编号为90的员工个数SELECT COUNT( * ) FROM employees WHERE department_id = 90; # 分组查询#查询每个部门的平均工资SELECT AVG( salary ) 平均工资, department_id 部门编号 FROM employees GROUP BY department_id;# 查询每个工种的最高工资SELECT MAX( salary ) 最高工资, job_id FROM employees GROUP BY job_id#查询邮箱中包含a字符的，每个部门的平均工资SELECT AVG( salary ), department_idFROM employees WHERE email LIKE '%a%'GROUP BY department_id#查询有奖金的每个领导手下员工的最高工资SELECT MAX( salary ), manager_idFROM employees WHERE commission_pct IS NOT NULLGROUP BY manager_id # 查询那个部门的员工个数大于2SELECT count( * ) a, department_id FROM employees GROUP BY department_id HAVING a > 2# 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资# 1. 查询每个工种有奖金的员工的最高工资SELECT MAX( salary ), job_id FROM employees WHEREcommission_pct is not NULLGROUP BY job_id# 在1的基础上结果上筛选 最高工资大于12000的SELECT MAX( salary ) a, job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING a > 12000# 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，#以及其最低工资SELECT MIN( salary ) a, manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING a > 5000 # 案例：按员工姓名的长度分组，查询每一组的员工个数，# 筛选员工个数大于5的有那些SELECT count(*) c,LENGTH(last_name)FROM employees GROUP BY LENGTH( last_name )HAVING c > 5 # 查询每个部门每个工种的员工的平均工资SELECT AVG(salary) a,department_id,job_idFROM employeesGROUP BY department_id,job_id# 查询每个部门每个工种的员工的平均工资 根据平均工资的高低来排序SELECT AVG(salary) a,department_id,job_idFROM employeesGROUP BY department_id,job_idORDER BY a DESC#查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序SELECT MAX( salary ), MIN( salary ), AVG( salary ), SUM( salary ), job_idFROM employees GROUP BY job_idORDER BY job_id # 查询员工最高工资和最低工资的差距SELECT MAX(salary) - MIN(salary) FROM employees;#查询各管理者手下员工的最低工资，其中最低工资不能低于6000# 没有管理者的员工不能计算在内SELECT MIN( salary ) m, manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING m > 6000 # 查询所有部门的编号，员工数量和工资平均值，并按平均工资降序SELECT department_id, COUNT( * ) , AVG( salary ) aFROM employees GROUP BY department_id ORDER BY a DESC# 选择具有各个job_id 的员工人数SELECT COUNT(*) FROM employees GROUP BY job_id 
No comments:
Post a Comment