Mysql个人学习笔记

mysql进阶一-基础#进阶1:基础查询/*语法:select查询列表from表名;类似于:System.out.println(打印东西);特点:1、查询列表可以是:表中的字段、常量值、表达式、函数2、查询的结果是一个虚拟的表格*/USEmyemployees;#使用这个数据库#1.查询表中的单个字段SELECTlast_nameFROMemployees;#2.查询表中的多个字段SELECTl...

Mysql个人学习笔记
mysql进阶一-基础
#进阶1:基础查询/*语法:select 查询列表 from 表名;类似于:System.out.println(打印东西);特点:1、查询列表可以是:表中的字段、常量值、表达式、函数2、查询的结果是一个虚拟的表格*/USE myemployees;#使用这个数据库#1.查询表中的单个字段SELECT last_name FROM employees;#2.查询表中的多个字段SELECT last_name,salary,email FROM employees;#3.查询表中的所有字段#方式一:SELECT  `employee_id`, `first_name`, `last_name`, `phone_number`, `last_name`, `job_id`, `phone_number`, `job_id`, `salary`, `commission_pct`, `manager_id`, `department_id`, `hiredate` FROM employees ;#方式二:SELECT * FROM employees;  #4.查询常量值 SELECT 100; SELECT 'john';  #5.查询表达式 SELECT 100�;  #6.查询函数  SELECT VERSION();#7.起别名 /* ①便于理解 ②如果要查询的字段有重名的情况,使用别名可以区分开来  */ #方式一:使用asSELECT 100� AS 结果;SELECT last_name AS 姓,first_name AS 名 FROM employees;#方式二:使用空格SELECT last_name 姓,first_name 名 FROM employees;#案例:查询salary,显示结果为 out putSELECT salary AS "out put" FROM employees;#8.去重#案例:查询员工表中涉及到的所有的部门编号SELECT DISTINCT department_id FROM employees;#9. 号的作用/*java中的 号:①运算符,两个操作数都为数值型②连接符,只要有一个操作数为字符串mysql中的 号:仅仅只有一个功能:运算符select 100 90; 两个操作数都为数值型,则做加法运算select '123' 90;只要其中一方为字符型,试图将字符型数值转换成数值型如果转换成功,则继续做加法运算select 'john' 90;如果转换失败,则将字符型数值转换成0select null 10; 只要其中一方为null,则结果肯定为null*/#案例:查询员工名和姓连接成一个字段,并显示为 姓名SELECT CONCAT('a','b','c') AS 结果;SELECT CONCAT(last_name,first_name) AS 姓名FROMemployees;
进阶二-条件查询
#进阶2:条件查询/*语法:select 查询列表from表名where筛选条件;分类:一、按条件表达式筛选简单条件运算符:> < = != <> >= <=二、按逻辑表达式筛选逻辑运算符:作用:用于连接条件表达式&& || !and or not&&和and:两个条件都为true,结果为true,反之为false||或or: 只要有一个条件为true,结果为true,反之为false!或not: 如果连接的条件本身为false,结果为true,反之为false三、模糊查询likebetween andinis null*/#一、按条件表达式筛选#案例1:查询工资>12000的员工信息SELECT *FROMemployeesWHEREsalary>12000;#案例2:查询部门编号不等于90号的员工名和部门编号SELECT last_name,department_idFROMemployeesWHEREdepartment_id<>90;#二、按逻辑表达式筛选#案例1:查询工资z在10000到20000之间的员工名、工资以及奖金SELECTlast_name,salary,commission_pctFROMemployeesWHEREsalary>=10000 AND salary<=20000;#案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息SELECT*FROMemployeesWHERENOT(department_id>=90 AND  department_id<=110) OR salary>15000;#三、模糊查询/*likebetween andinis null|is not null*/#1.like/*特点:①一般和通配符搭配使用通配符:% 任意多个字符,包含0个字符_ 任意单个字符*、#案例1:查询员工名中包含字符a的员工信息select *fromemployeeswherelast_name like '%a%';#abc#案例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资selectlast_name,salaryFROMemployeesWHERElast_name LIKE '__n_l%';#案例3:查询员工名中第二个字符为_的员工名SELECTlast_nameFROMemployeesWHERElast_name LIKE '_$_%' ESCAPE '$';#2.between and/*①使用between and 可以提高语句的简洁度②包含临界值③两个临界值不要调换顺序*/#案例1:查询员工编号在100到120之间的员工信息SELECT*FROMemployeesWHEREemployee_id >= 100 AND employee_id<=120;#----------------------SELECT*FROMemployeesWHEREemployee_id BETWEEN 100 AND 120;#3.in/*含义:判断某字段的值是否属于in列表中的某一项特点:①使用in提高语句简洁度②in列表的值类型必须一致或兼容③in列表中不支持通配符*/#案例:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号SELECTlast_name,job_idFROMemployeesWHEREjob_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';#------------------SELECTlast_name,job_idFROMemployeesWHEREjob_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');#4、is null/*=或<>不能用于判断null值is null或is not null 可以判断null值*/#案例1:查询没有奖金的员工名和奖金率SELECTlast_name,commission_pctFROMemployeesWHEREcommission_pct IS NULL;#案例1:查询有奖金的员工名和奖金率SELECTlast_name,commission_pctFROMemployeesWHEREcommission_pct IS NOT NULL;#----------以下为�  错误示范SELECTlast_name,commission_pctFROMemployeesWHERE salary IS 12000;#安全等于  <=>#案例1:查询没有奖金的员工名和奖金率SELECTlast_name,commission_pctFROMemployeesWHEREcommission_pct <=>NULL;#案例2:查询工资为12000的员工信息SELECTlast_name,salaryFROMemployeesWHERE salary <=> 12000;#is null pk <=>IS NULL:仅仅可以判断NULL值,可读性较高,建议使用<=> :既可以判断NULL值,又可以判断普通的数值,可读性较低
进阶三-排序
#进阶3:排序查询/*语法:select 查询列表from 表名【where  筛选条件】order by 排序的字段或表达式;特点:1、asc代表的是升序,可以省略desc代表的是降序2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段3、order by子句在查询语句的最后面,除了limit子句*/#1、按单个字段排序SELECT * FROM employees ORDER BY salary DESC;#2、添加筛选条件再排序#案例:查询部门编号>=90的员工信息,并按员工编号降序SELECT *FROM employeesWHERE department_id>=90ORDER BY employee_id DESC;#3、按表达式排序#案例:查询员工信息 按年薪降序SELECT *,salary*12*(1 IFNULL(commission_pct,0))FROM employeesORDER BY salary*12*(1 IFNULL(commission_pct,0)) DESC;#4、按别名排序#案例:查询员工信息 按年薪升序SELECT *,salary*12*(1 IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY 年薪 ASC;#5、按函数排序#案例:查询员工名,并且按名字的长度降序SELECT LENGTH(last_name),last_name FROM employeesORDER BY LENGTH(last_name) DESC;#6、按多个字段排序#案例:查询员工信息,要求先按工资降序,再按employee_id升序SELECT *FROM employeesORDER BY salary DESC,employee_id ASC;
进阶四-函数
#流程函数if(条件,值1,值2) =======类似三元运算 条件true则值1,否则值2case 字段============ 类似switchwhen 条件  then  结果elseend#分组函数 max、min、count可以处理任何类型 忽略null datediff(now(),date):相差天数 MAX(salary)-MIN(salary) DIFFRENCE:相差工资
#进阶4:常见函数概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名好处:1、隐藏了实现细节  2、提高代码的重用性调用:select 函数名(实参列表) 【from 表】;特点:①叫什么(函数名)②干什么(函数功能)分类:#一、字符函数#1.length 获取参数值的字节个数SELECT LENGTH('john');SELECT LENGTH('张三丰hahaha');#2.concat 拼接字符串SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;#3.upper、lowerSELECT UPPER('john');SELECT LOWER('joHn');#示例:将姓变大写,名变小写,然后拼接SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;#4.substr、substring注意:索引从1开始#截取从指定索引处后面所有字符SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;#截取从指定索引处指定字符长度的字符SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;#案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_putFROM employees;#5.instr 返回子串第一次出现的索引,如果找不到返回0SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;#6.trim  去除空格或者指定的SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;#7.lpad 用指定的字符实现左填充指定长度SELECT LPAD('殷素素',2,'*') AS out_put;#8.rpad 用指定的字符实现右填充指定长度SELECT RPAD('殷素素',12,'ab') AS out_put;#9.replace 替换SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;#二、数学函数#round 四舍五入SELECT ROUND(-1.55);SELECT ROUND(1.567,2);#ceil 向上取整,返回>=该参数的最小整数SELECT CEIL(-1.02);#floor 向下取整,返回<=该参数的最大整数SELECT FLOOR(-9.99);#truncate 截断SELECT TRUNCATE(1.69999,1);#mod取余/*mod(a,b) :  a-(a/b*b) 先除后乘mod(-10,-3):-10- (-10)/(-3)*(-3)=-1*/SELECT MOD(10,-3);SELECT 10%3;#三、日期函数#now 返回当前系统日期 时间SELECT NOW();#curdate 返回当前系统日期,不包含时间SELECT CURDATE();#curtime 返回当前时间,不包含日期SELECT CURTIME();#可以获取指定的部分,年、月、日、小时、分钟、秒SELECT YEAR(NOW()) 年;SELECT YEAR('1998-1-1') 年;SELECT YEAR(hiredate) 年 FROM employees;SELECT MONTH(NOW()) 月;SELECT MONTHNAME(NOW()) 月;#str_to_date 将字符通过指定的格式转换成日期SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;#查询入职日期为1992--4-3的员工信息SELECT * FROM employees WHERE hiredate = '1992-4-3';SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');#date_format 将日期转换成字符SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;#查询有奖金的员工名和入职日期(xx月/xx日 xx年)SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期FROM employeesWHERE commission_pct IS NOT NULL;#四、其他函数SELECT VERSION();SELECT DATABASE();SELECT USER();#五、流程控制函数#1.if函数: if else 的效果SELECT IF(10<5,'大','小');#===========三元运算SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵','有奖金,嘻嘻') 备注FROM employees;#2.case函数的使用一: switch case 的效果/*java中switch(变量或表达式){case 常量1:语句1;break;...default:语句n;break;}mysql中case 要判断的字段或表达式when 常量1 then 要显示的值1或语句1;when 常量2 then 要显示的值2或语句2;...else 要显示的值n或语句n;end*//*案例:查询员工的工资,要求部门号=30,显示的工资为1.1倍部门号=40,显示的工资为1.2倍部门号=50,显示的工资为1.3倍其他部门,显示的工资为原工资*/SELECT salary 原始工资,department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS 新工资FROM employees;#3.case 函数的使用二:类似于 多重if/*java中:if(条件1){语句1;}else if(条件2){语句2;}...else{语句n;}mysql中:case when 条件1 then 要显示的值1或语句1when 条件2 then 要显示的值2或语句2。。。else 要显示的值n或语句nend*/#案例:查询员工的工资的情况如果工资>20000,显示A级别如果工资>15000,显示B级别如果工资>10000,显示C级别否则,显示D级别SELECT salary,CASE WHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别FROM employees;#二、聚合函数/*功能:用作统计使用,又称为聚合函数或统计函数或组函数分类:sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数特点:1、sum、avg一般用于处理数值型max、min、count可以处理任何类型2、以上分组函数都忽略null值3、可以和distinct搭配实现去重的运算4、count函数的单独介绍一般使用count(*)用作统计行数5、和分组函数一同查询的字段要求是group by后的字段*/#1、简单 的使用SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数FROM employees;#2、参数支持哪些类型SELECT SUM(last_name) ,AVG(last_name) FROM employees;SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;SELECT MAX(last_name),MIN(last_name) FROM employees;SELECT MAX(hiredate),MIN(hiredate) FROM employees;SELECT COUNT(commission_pct) FROM employees;SELECT COUNT(last_name) FROM employees;#3、是否忽略null  (忽略)SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;SELECT COUNT(commission_pct) FROM employees;SELECT commission_pct FROM employees;#4、和distinct搭配SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;#5、count函数的详细介绍SELECT COUNT(salary) FROM employees;SELECT COUNT(*) FROM employees;SELECT COUNT(1) FROM employees;效率:MYISAM存储引擎下  ,COUNT(*)的效率高INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些#6、和分组函数一同查询的字段有限制(一般和group by一起)SELECT AVG(salary),employee_id  FROM employees;
进阶五-分组
select 查询列表from 表【where 筛选条件】group by 分组的字段【having 筛选条件】【order by 排序的字段】;
#进阶5:分组查询/*语法:select 查询列表from 表【where 筛选条件】group by 分组的字段【order by 排序的字段】;特点:1、和分组函数一同查询的字段必须是group by后出现的字段问题1:分组函数做筛选能不能放在where后面答:不能问题2:where——group by——having一般来讲,能用分组前筛选的,尽量使用分组前筛选,提高效率3、分组可以按单个字段也可以按多个字段4、可以搭配着排序使用*/#引入:查询每个部门的员工个数SELECT COUNT(*) FROM employees WHERE department_id=90;#1.简单的分组#案例1:查询每个工种的员工平均工资SELECT AVG(salary),job_idFROM employeesGROUP BY job_id;#案例2:查询每个位置的部门个数SELECT COUNT(*),location_idFROM departmentsGROUP BY location_id;#2、可以实现分组前的筛选#案例1:查询邮箱中包含a字符的 每个部门的最高工资SELECT MAX(salary),department_idFROM employeesWHERE email LIKE '%a%'GROUP BY department_id;#案例2:查询有奖金的每个领导手下员工的平均工资SELECT AVG(salary),manager_idFROM employeesWHERE commission_pct IS NOT NULLGROUP BY manager_id;#3、分组后筛选#案例:查询哪个部门的员工个数>5#①查询每个部门的员工个数SELECT COUNT(*),department_idFROM employeesGROUP BY department_id;#② 筛选刚才①结果SELECT COUNT(*),department_idFROM employeesGROUP BY department_idHAVING COUNT(*)>5;#案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资SELECT job_id,MAX(salary)FROM employeesWHERE commission_pct IS NOT NULL #原始表中有啊GROUP BY job_idHAVING MAX(salary)>12000;  #分组后有啊 #案例3:领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资MIN(salary)>5000;manager_id>102 |SELECT manager_id,MIN(salary)FROM employeesGROUP BY manager_id|SELECT manager_id,MIN(salary)FROM employeeswhere manager_id>102GROUP BY manager_idHAVING MIN(salary)>5000;#4.添加排序#案例:每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序SELECT job_id,MAX(salary) mFROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING m>6000ORDER BY m ;#5.按多个字段分组#案例:查询每个工种每个部门的最低工资,并按最低工资降序SELECT MIN(salary),job_id,department_idFROM employeesGROUP BY department_id,job_idORDER BY MIN(salary) DESC;
进阶六-连接查询
#进阶6:连接查询/*含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行发生原因:没有有效的连接条件如何避免:添加有效的连接条件按功能分类:内连接:等值连接非等值连接自连接外连接:左外连接右外连接全外连接*/#案例1:查询女神名和对应的男神名SELECT NAME,boyName FROM boys,beautyWHERE beauty.boyfriend_id= boys.id;#案例2:查询员工名和对应的部门名SELECT last_name,department_nameFROM employees,departmentsWHERE employees.`department_id`=departments.`department_id`;#2、为表起别名#查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM employees  e,jobs jWHERE e.`job_id`=j.`job_id`;#4、可以加筛选#案例:查询有奖金的员工名、部门名SELECT last_name,department_name,commission_pctFROM employees e,departments dWHERE e.`department_id`=d.`department_id`AND e.`commission_pct` IS NOT NULL;#案例2:查询城市名中第二个字符为o的部门名和城市名SELECT department_name,cityFROM departments d,locations lWHERE d.`location_id` = l.`location_id`AND city LIKE '_o%';#5、可以加分组#案例1:查询每个城市的部门个数SELECT COUNT(*) 个数,cityFROM departments d,locations lWHERE d.`location_id`=l.`location_id`GROUP BY city;#案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资SELECT department_name,d.`manager_id`,MIN(salary)FROM departments d,employees eWHERE d.`department_id`=e.`department_id`AND commission_pct IS NOT NULLGROUP BY department_name,d.`manager_id`;#6、可以加排序#案例:查询每个工种的工种名和员工的个数,并且按员工个数降序SELECT job_title,COUNT(*)FROM employees e,jobs jWHERE e.`job_id`=j.`job_id`GROUP BY job_titleORDER BY COUNT(*) DESC;#7、可以实现三表连接?#案例:查询员工名、部门名和所在的城市SELECT last_name,department_name,cityFROM employees e,departments d,locations lWHERE e.`department_id`=d.`department_id`AND d.`location_id`=l.`location_id`AND city LIKE 's%'ORDER BY department_name DESC;#2、非等值连接#案例1:查询员工的工资和工资级别SELECT salary,grade_levelFROM employees e,job_grades gWHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`AND g.`grade_level`='A';#3、自连接#案例:查询 员工名和上级的名称SELECT e.employee_id,e.last_name,m.employee_id,m.last_nameFROM employees e,employees mWHERE e.`manager_id`=m.`employee_id`;#二、sql99语法语法:select 查询列表from 表1 别名 【连接类型】join 表2 别名 on 连接条件【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】分类:内连接(★):inner外连接左外(★):left 【outer】右外(★):right 【outer】全外:full【outer】交叉连接:cross #一)内连接语法:select 查询列表from 表1 别名inner join 表2 别名on 连接条件;特点:①添加排序、分组、筛选②inner可以省略#案例1.查询员工名、部门名SELECT last_name,department_nameFROM departments d JOIN  employees eON e.`department_id` = d.`department_id`;#案例2.查询名字中包含e的员工名和工种名(添加筛选)SELECT last_name,job_titleFROM employees eINNER JOIN jobs jON e.`job_id`=  j.`job_id`WHERE e.`last_name` LIKE '%e%';#3. 查询部门个数>3的城市名和部门个数,(添加分组 筛选)#①查询每个城市的部门个数#②在①结果上筛选满足条件的SELECT city,COUNT(*) 部门个数FROM departments dINNER JOIN locations lON d.`location_id`=l.`location_id`GROUP BY cityHAVING COUNT(*)>3;#案例4.查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(添加排序)#①查询每个部门的员工个数SELECT COUNT(*),department_nameFROM employees eINNER JOIN departments dON e.`department_id`=d.`department_id`GROUP BY department_name#② 在①结果上筛选员工个数>3的记录,并排序SELECT COUNT(*) 个数,department_nameFROM employees eINNER JOIN departments dON e.`department_id`=d.`department_id`GROUP BY department_nameHAVING COUNT(*)>3ORDER BY COUNT(*) DESC;#5.查询员工名、部门名、工种名,并按部门名降序(添加三表连接)SELECT last_name,department_name,job_titleFROM employees eINNER JOIN departments d ON e.`department_id`=d.`department_id`INNER JOIN jobs j ON e.`job_id` = j.`job_id`ORDER BY department_name DESC;#查询员工的工资级别SELECT salary,grade_levelFROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;  #查询工资级别的个数>20的个数,并且按工资级别降序 SELECT COUNT(*),grade_levelFROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level DESC;  #三)自连接  #查询员工的名字、上级的名字 SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`= m.`employee_id`;#查询姓名中包含字符k的员工的名字、上级的名字 SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`= m.`employee_id` WHERE e.`last_name` LIKE '%k%';  #二、外连接  #左外连接 SELECT b.*,bo.* FROM boys bo LEFT OUTER JOIN beauty b ON b.`boyfriend_id` = bo.`id` WHERE b.`id` IS NULL;  #案例1:查询哪个部门没有员工 #左外 SELECT d.*,e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;#右外SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL; #全外 USE girls; SELECT b.*,bo.* FROM beauty b FULL OUTER JOIN boys bo ON b.`boyfriend_id` = bo.id;
进阶七-子查询
select语句中(select)
#进阶7:子查询含义:出现在其他语句中的select语句,称为子查询或内查询外部的查询语句,称为主查询或外查询分类:按子查询出现的位置:select后面:仅仅支持标量子查询from后面:支持表子查询where或having后面:★标量子查询(单行) √列子查询  (多行) √行子查询exists后面(相关子查询)表子查询#案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资#①查询141号员工的job_idSELECT job_idFROM employeesWHERE employee_id = 141#②查询143号员工的salarySELECT salaryFROM employeesWHERE employee_id = 143#③查询员工的姓名,job_id 和工资,要求job_id=①并且salary>②SELECT last_name,job_id,salaryFROM employeesWHERE job_id = (SELECT job_idFROM employeesWHERE employee_id = 141) AND salary>(SELECT salaryFROM employeesWHERE employee_id = 143);#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资#①查询50号部门的最低工资SELECT  MIN(salary)FROM employeesWHERE department_id = 50#②查询每个部门的最低工资SELECT MIN(salary),department_idFROM employeesGROUP BY department_id#③ 在②基础上筛选,满足min(salary)>①SELECT MIN(salary),department_idFROM employeesGROUP BY department_idHAVING MIN(salary)>(SELECT  MIN(salary)FROM employeesWHERE department_id = 50);#2.列子查询(多行子查询)★#案例1:返回location_id是1400或1700的部门中的所有员工姓名#①查询location_id是1400或1700的部门编号SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700)#②查询员工姓名,要求部门号是①列表中的某一个SELECT last_nameFROM employeesWHERE department_id IN(SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700));#案例2:返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary#①查询job_id为‘IT_PROG’部门任一工资SELECT DISTINCT salaryFROM employeesWHERE job_id = 'IT_PROG'#②查询员工号、姓名、job_id 以及salary,salary<(①)的任意一个SELECT last_name,employee_id,job_id,salaryFROM employeesWHERE salary<ANY(SELECT DISTINCT salaryFROM employeesWHERE job_id = 'IT_PROG') AND job_id<>'IT_PROG';#或SELECT last_name,employee_id,job_id,salaryFROM employeesWHERE salary<(SELECT MAX(salary)FROM employeesWHERE job_id = 'IT_PROG') AND job_id<>'IT_PROG';#案例3:返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salarySELECT last_name,employee_id,job_id,salaryFROM employeesWHERE salary<ALL(SELECT DISTINCT salaryFROM employeesWHERE job_id = 'IT_PROG') AND job_id<>'IT_PROG';#或SELECT last_name,employee_id,job_id,salaryFROM employeesWHERE salary<(SELECT MIN( salary)FROM employeesWHERE job_id = 'IT_PROG') AND job_id<>'IT_PROG';#3、行子查询(结果集一行多列或多行多列)#案例:查询员工编号最小并且工资最高的员工信息SELECT * FROM employeesWHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)FROM employees);#①查询最小的员工编号SELECT MIN(employee_id)FROM employees#②查询最高工资SELECT MAX(salary)FROM employees#③查询员工信息SELECT *FROM employeesWHERE employee_id=(SELECT MIN(employee_id)FROM employees)AND salary=(SELECT MAX(salary)FROM employees);#二、select后面#案例:查询每个部门的员工个数SELECT d.*,(SELECT COUNT(*)FROM employees eWHERE e.department_id = d.`department_id` ) 个数 FROM departments d;#案例2:查询员工号=102的部门名 SELECT (SELECT department_name,e.department_idFROM departments dINNER JOIN employees eON d.department_id=e.department_idWHERE e.employee_id=102) 部门名;#三、from后面/*将子查询结果充当一张表,要求必须起别名*/#案例:查询每个部门的平均工资的工资等级#①查询每个部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_idSELECT * FROM job_grades;#②连接①的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_salSELECT  ag_dep.*,g.`grade_level`FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id) ag_depINNER JOIN job_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;#四、exists后面(相关子查询)/*语法:exists(完整的查询语句)结果:1或0 ,true or false*/SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);#案例1:查询有员工的部门名#inSELECT department_nameFROM departments dWHERE d.`department_id` IN(SELECT department_idFROM employees)#existsSELECT department_nameFROM departments dWHERE EXISTS(SELECT *FROM employees eWHERE d.`department_id`=e.`department_id`);#案例2:查询没有女朋友的男神信息#inSELECT bo.*FROM boys boWHERE bo.id NOT IN(SELECT boyfriend_idFROM beauty)#existsSELECT bo.*FROM boys boWHERE NOT EXISTS(SELECT boyfriend_idFROM beauty bWHERE bo.`id`=b.`boyfriend_id`);
进阶八-分页查询
limit 0,1  从第一个开始选1个
#进阶8:分页查询 ★/*应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求语法:select 查询列表from 表【join type join 表2on 连接条件where 筛选条件group by 分组字段having 分组后的筛选order by 排序的字段】limit 【offset,】size;offset要显示条目的起始索引(起始索引从0开始)size 要显示的条目个数特点:①limit语句放在查询语句的最后②公式要显示的页数 page,每页的条目数sizeselect 查询列表from 表limit (page-1)*size,size;size=10page  102  10320*/#案例1:查询前五条员工信息SELECT * FROM  employees LIMIT 0,5;SELECT * FROM  employees LIMIT 5;  #从0开始的可以省略#案例2:查询第11条——第25条SELECT * FROM  employees LIMIT 10,15;#案例3:有奖金的员工信息,并且工资较高的前10名显示出来SELECT  * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10 ;
联合查询
union  union all  #放在查询语句之后
#进阶9:联合查询/*union 联合 合并:将多条查询语句的结果合并成一个结果语法:查询语句1union查询语句2union...应用场景:要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时特点:★1、要求多条查询语句的查询列数是一致的!2、要求多条查询语句的查询的每一列的类型和顺序最好一致3、union关键字默认去重,如果使用union all 可以包含重复项*/#引入的案例:查询部门编号>90或邮箱包含a的员工信息SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;;SELECT * FROM employees  WHERE email LIKE '%a%'UNIONSELECT * FROM employees  WHERE department_id>90;#案例:查询中国用户中男性的信息以及外国用户中年男性的用户信息SELECT id,cname FROM t_ca WHERE csex='男'UNION ALLSELECT t_id,tname FROM t_ua WHERE tGender='male';
库和表DDL
创建: create修改: alter删除: drop查看表结构 desc
#DDL/*数据定义语言库和表的管理一、库的管理创建、修改、删除二、表的管理创建: create修改: alter删除: drop*/#一、库的管理#1、库的创建/*语法:create database  [if not exists]库名;*/#案例:创建库BooksCREATE DATABASE IF NOT EXISTS books ;#2、库的修改RENAME DATABASE books TO 新库名;#更改库的字符集ALTER DATABASE books CHARACTER SET gbk;#3、库的删除DROP DATABASE IF EXISTS books;#二、表的管理#1.表的创建 ★/*语法:create table 表名(列名 列的类型【(长度) 约束】,列名 列的类型【(长度) 约束】,列名 列的类型【(长度) 约束】,...列名 列的类型【(长度) 约束】)*/#案例:创建表BookCREATE TABLE book(id INT,#编号bName VARCHAR(20),#图书名price DOUBLE,#价格authorId  INT,#作者编号publishDate DATETIME#出版日期);DESC book;#案例:创建表authorCREATE TABLE IF NOT EXISTS author(id INT,au_name VARCHAR(20),nation VARCHAR(10))DESC author;#2.表的修改/*语法alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;*/#①修改列名ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;#②修改列的类型或约束ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;#③添加新列ALTER TABLE author ADD COLUMN annual DOUBLE; #④删除列ALTER TABLE book_author DROP COLUMN  annual;#⑤修改表名ALTER TABLE author RENAME TO book_author;DESC book;#3.表的删除DROP TABLE IF EXISTS book_author;SHOW TABLES;#通用的写法:#如果存在 if existsDROP DATABASE IF EXISTS 旧库名;CREATE DATABASE 新库名;DROP TABLE IF EXISTS 旧表名;CREATE TABLE  表名();#4.表的复制SELECT * FROM Author;SELECT * FROM copy2;#1.仅仅复制表的结构CREATE TABLE copy LIKE author;#2.复制表的结构 数据CREATE TABLE copy2 SELECT * FROM author;#只复制部分数据CREATE TABLE copy3SELECT id,au_nameFROM author WHERE nation='中国';#仅仅复制某些字段CREATE TABLE copy4 SELECT id,au_nameFROM authorWHERE 0;
数据的增删改DML
插入:insert修改:update删除:deletetruncate与delete的区别truncate清空表,不支持回滚delete可加条件删除指定
#DML语言#一、插入语句#方式一:经典的插入/*语法:insert into 表名(列名,...) values(值1,...);*/SELECT * FROM beauty;#1.插入的值的类型要与列的类型一致或兼容INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);#2.不可以为null的列必须插入值。可以为null的列如何插入值?#方式一:INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);#方式二:INSERT INTO beauty(id,NAME,sex,phone)VALUES(15,'娜扎','女','1388888888');#3.列的顺序是否可以调换 (不可以)INSERT INTO beauty(NAME,sex,id,phone)VALUES('蒋欣','女',16,'110');#4.列数和值的个数必须一致匹配INSERT INTO beauty(NAME,sex,id,phone)VALUES('关晓彤','女',17,'110');#5.可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致INSERT INTO beautyVALUES(18,'张飞','男',NULL,'119',NULL,NULL);#二、修改语句/*1.修改单表的记录★语法:update 表名set 列=新值,列=新值,...where 筛选条件;2.修改多表的记录【补充】update 表1 别名inner|left|right join 表2 别名on 连接条件set 列=值,...where 筛选条件;*/#1.修改单表的记录#案例1:修改beauty表中姓唐的女神的电话为13899888899UPDATE beauty SET phone = '13899888899'WHERE NAME LIKE '唐%';#案例2:修改boys表中id好为2的名称为张飞,魅力值 10UPDATE boys SET boyname='张飞',usercp=10WHERE id=2;#2.修改多表的记录#案例 1:修改张无忌的女朋友的手机号为114UPDATE boys boINNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`SET b.`phone`='119',bo.`userCP`=1000WHERE bo.`boyName`='张无忌';#案例2:修改没有男朋友的女神的男朋友编号都为2号UPDATE boys boRIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`SET b.`boyfriend_id`=2WHERE bo.`id` IS NULL;SELECT * FROM boys;#三、删除语句/*方式一:delete语法:1、单表的删除【★】delete from 表名 where 筛选条件2、多表的删除【补充】delete 表1的别名,表2的别名from 表1 别名inner|left|right join 表2 别名 on 连接条件where 筛选条件;方式二:truncate语法:truncate table 表名;*/#方式一:delete#1.单表的删除#案例:删除手机号以9结尾的女神信息DELETE FROM beauty WHERE phone LIKE '%9';SELECT * FROM beauty;#2.多表的删除#案例:删除张无忌的女朋友的信息DELETE bFROM beauty bINNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`WHERE bo.`boyName`='张无忌';#案例:删除黄晓明的信息以及他女朋友的信息DELETE b,boFROM beauty bINNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`WHERE bo.`boyName`='黄晓明';#方式二:truncate语句#案例:将魅力值>100的男神信息删除TRUNCATE TABLE boys ;#delete pk truncate【面试题★】/*1.delete 可以加where 条件,truncate不能加2.truncate删除,效率高一丢丢3.假如要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长列的值从断点开始,而truncate删除后,再插入数据,自增长列的值从1开始。4.truncate删除没有返回值,delete删除有返回值5.truncate删除不能回滚,delete删除可以回滚.*/SELECT * FROM boys;DELETE FROM boys;TRUNCATE TABLE boys;INSERT INTO boys (boyname,usercp)VALUES('张飞',100),('刘备',100),('关云长',100);
数据类型
#常见的数据类型#一、整型/*分类:tinyint、smallint、mediumint、int/integer、bigint1 2 348特点:① 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字② 如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值③ 如果不设置长度,会有默认的长度长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用!*/#1.如何设置无符号和有符号DROP TABLE IF EXISTS tab_int;CREATE TABLE tab_int(t1 INT(7) ZEROFILL,t2 INT(7) ZEROFILL );DESC tab_int;INSERT INTO tab_int VALUES(-123456);INSERT INTO tab_int VALUES(-123456,-123456);INSERT INTO tab_int VALUES(2147483648,4294967296);INSERT INTO tab_int VALUES(123,123);SELECT * FROM tab_int;#二、小数/*分类:1.浮点型float(M,D)double(M,D)2.定点型dec(M,D)decimal(M,D)特点:①M:整数部位 小数部位D:小数部位如果超过范围,则插入临界值②M和D都可以省略如果是decimal,则M默认为10,D默认为0如果是float和double,则会根据插入的数值的精度来决定精度③定点型的精确度较高,如果要求插入数值的精度较高如货币运算等则考虑使用*/#测试M和DDROP TABLE tab_float;CREATE TABLE tab_float(f1 FLOAT,f2 DOUBLE,f3 DECIMAL);SELECT * FROM tab_float;DESC tab_float;INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);INSERT INTO tab_float VALUES(123.456,123.456,123.456);INSERT INTO tab_float VALUES(123.4,123.4,123.4);INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);#原则:/*所选择的类型越简单越好,能保存数值的类型越小越好*/CREATE TABLE tab_char(c1 ENUM('a','b','c'));INSERT INTO tab_char VALUES('a');INSERT INTO tab_char VALUES('b');INSERT INTO tab_char VALUES('c');INSERT INTO tab_char VALUES('m');INSERT INTO tab_char VALUES('A');SELECT * FROM tab_set;CREATE TABLE tab_set(s1 SET('a','b','c','d'));INSERT INTO tab_set VALUES('a');INSERT INTO tab_set VALUES('A,B');INSERT INTO tab_set VALUES('a,c,d');#四、日期型/*分类:date只保存日期time 只保存时间year只保存年datetime保存日期 时间timestamp保存日期 时间特点:字节范围时区等的影响datetime81000——9999  不受timestamp41970-2038受*/CREATE TABLE tab_date(t1 DATETIME,t2 TIMESTAMP);INSERT INTO tab_date VALUES(NOW(),NOW());SELECT * FROM tab_date;SHOW VARIABLES LIKE 'time_zone';SET time_zone=' 9:00';
事务TCL
#TCL/*Transaction Control Language 事务控制语言事务:一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。案例:转账张三丰  1000郭襄1000update 表 set 张三丰的余额=500 where name='张三丰'中间出现了意外 (例如数据库挂掉了)update 表 set 郭襄的余额=1500 where name='郭襄'事务的特性:ACID原子性:一个事务不可再分割,要么都执行要么都不执行一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态隔离性:一个事务的执行不受其他事务的干扰持久性:一个事务一旦提交,则会永久的改变数据库的数据.事务的创建隐式事务:事务没有明显的开启和结束的标记比如insert、update、delete语句delete from 表 where id =1;显式事务:事务具有明显的开启和结束的标记前提:必须先设置自动提交功能为禁用set autocommit=0;步骤1:开启事务set autocommit=0;start transaction;可选的步骤2:编写事务中的sql语句(select insert update delete)语句1;语句2;...步骤3:结束事务commit;提交事务rollback;回滚事务savepoint 节点名;设置保存点事务的隔离级别:  脏读不可重复读幻读read uncommitted:√ √  √read committed:  � √ √repeatable read: � � √serializable  ��  �mysql中默认 第三个隔离级别 repeatable readoracle中默认第二个隔离级别 read committed查看隔离级别select @@tx_isolation;设置隔离级别set session|global transaction isolation level 隔离级别;开启事务的语句;update 表 set 张三丰的余额=500 where name='张三丰'update 表 set 郭襄的余额=1500 where name='郭襄' 结束事务的语句;*/SHOW VARIABLES LIKE 'autocommit';SHOW ENGINES;#1.演示事务的使用步骤#开启事务SET autocommit=0;START TRANSACTION;#编写一组事务的语句UPDATE account SET balance = 1000 WHERE username='张无忌';UPDATE account SET balance = 1000 WHERE username='赵敏';#结束事务ROLLBACK;#commit;SELECT * FROM account;#2.演示事务对于delete和truncate的处理的区别SET autocommit=0;START TRANSACTION;DELETE FROM account;ROLLBACK;#3.演示savepoint 的使用SET autocommit=0;START TRANSACTION;DELETE FROM account WHERE id=25;SAVEPOINT a;#设置保存点DELETE FROM account WHERE id=28;ROLLBACK TO a;#回滚到保存点SELECT * FROM account;
视图
#视图/*含义:虚拟表,和普通表一样使用mysql5.1版本出现的新特性,是通过表动态生成的数据比如:舞蹈班和普通班级的对比创建语法的关键字是否实际占用物理空间使用视图create view只是保存了sql逻辑增删改查,只是一般不能增删改表 create table保存了数据 增删改查*/#案例:查询姓张的学生名和专业名SELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.`majorid`= m.`id`WHERE s.`stuname` LIKE '张%';CREATE VIEW v1ASSELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.`majorid`= m.`id`;SELECT * FROM v1 WHERE stuname LIKE '张%';#一、创建视图/*语法:create view 视图名as查询语句;*/#1.查询姓名中包含a字符的员工名、部门名和工种信息#①创建CREATE VIEW myv1ASSELECT last_name,department_name,job_titleFROM employees eJOIN departments d ON e.department_id  = d.department_idJOIN jobs j ON j.job_id  = e.job_id;#②使用SELECT * FROM myv1 WHERE last_name LIKE '%a%';#2.查询各部门的平均工资级别#①创建视图查看每个部门的平均工资CREATE VIEW myv2ASSELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id;#②使用SELECT myv2.`ag`,g.grade_levelFROM myv2JOIN job_grades gON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;#3.查询平均工资最低的部门信息SELECT * FROM myv2 ORDER BY ag LIMIT 1;#4.查询平均工资最低的部门名和工资CREATE VIEW myv3ASSELECT * FROM myv2 ORDER BY ag LIMIT 1;SELECT d.*,m.agFROM myv3 mJOIN departments dON m.`department_id`=d.`department_id`;#二、视图的修改#方式一:/*create or replace view  视图名as查询语句;*/SELECT * FROM myv3 CREATE OR REPLACE VIEW myv3ASSELECT AVG(salary),job_idFROM employeesGROUP BY job_id;#方式二:/*语法:alter view 视图名as 查询语句;*/ALTER VIEW myv3ASSELECT * FROM employees;#三、删除视图/*语法:drop view 视图名,视图名,...;*/DROP VIEW emp_v1,emp_v2,myv3;#四、查看视图DESC myv3;SHOW CREATE VIEW myv3;#五、视图的更新CREATE OR REPLACE VIEW myv1ASSELECT last_name,email,salary*12*(1 IFNULL(commission_pct,0)) "annual salary"FROM employees;CREATE OR REPLACE VIEW myv1ASSELECT last_name,emailFROM employees;SELECT * FROM myv1;SELECT * FROM employees;#1.插入INSERT INTO myv1 VALUES('张飞','zf@qq.com');#2.修改UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';#3.删除DELETE FROM myv1 WHERE last_name = '张无忌';#具备以下特点的视图不允许更新#①包含以下关键字的sql语句:分组函数、distinct、group  by、having、union或者union allCREATE OR REPLACE VIEW myv1ASSELECT MAX(salary) m,department_idFROM employeesGROUP BY department_id;SELECT * FROM myv1;#更新UPDATE myv1 SET m=9000 WHERE department_id=10;#②常量视图CREATE OR REPLACE VIEW myv2ASSELECT 'john' NAME;SELECT * FROM myv2;#更新UPDATE myv2 SET NAME='lucy';#③Select中包含子查询CREATE OR REPLACE VIEW myv3ASSELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资FROM departments;#更新SELECT * FROM myv3;UPDATE myv3 SET 最高工资=100000;#④joinCREATE OR REPLACE VIEW myv4ASSELECT last_name,department_nameFROM employees eJOIN departments dON e.department_id  = d.department_id;#更新SELECT * FROM myv4;UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';INSERT INTO myv4 VALUES('陈真','xxxx');#⑤from一个不能更新的视图CREATE OR REPLACE VIEW myv5ASSELECT * FROM myv3;#更新SELECT * FROM myv5;UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;#⑥where子句的子查询引用了from子句中的表CREATE OR REPLACE VIEW myv6ASSELECT last_name,email,salaryFROM employeesWHERE employee_id IN(SELECT  manager_idFROM employeesWHERE manager_id IS NOT NULL);#更新SELECT * FROM myv6;UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
函数
#函数/*含义:一组预先编译好的SQL语句的集合,理解成批处理语句1、提高代码的重用性2、简化操作3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率区别:存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新函数:有且仅有1 个返回,适合做处理数据后返回一个结果*/#一、创建语法CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型BEGIN函数体END/*注意:1.参数列表 包含两部分:参数名 参数类型2.函数体:肯定会有return语句,如果没有会报错如果return语句没有放在函数体的最后也不报错,但不建议return 值;3.函数体中仅有一句话,则可以省略begin end4.使用 delimiter语句设置结束标记*/#二、调用语法SELECT 函数名(参数列表)#------------------------------案例演示----------------------------#1.无参有返回#案例:返回公司的员工个数CREATE FUNCTION myf1() RETURNS INTBEGINDECLARE c INT DEFAULT 0;#定义局部变量SELECT COUNT(*) INTO c#赋值FROM employees;RETURN c;END $SELECT myf1()$#2.有参有返回#案例1:根据员工名,返回它的工资CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLEBEGINSET @sal=0;#定义用户变量 SELECT salary INTO @sal#赋值FROM employeesWHERE last_name = empName;RETURN @sal;END $SELECT myf2('k_ing') $#案例2:根据部门名,返回该部门的平均工资CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLEBEGINDECLARE sal DOUBLE ;SELECT AVG(salary) INTO salFROM employees eJOIN departments d ON e.department_id = d.department_idWHERE d.department_name=deptName;RETURN sal;END $SELECT myf3('IT')$#三、查看函数SHOW CREATE FUNCTION myf3;#四、删除函数DROP FUNCTION myf3;#案例#一、创建函数,实现传入两个float,返回二者之和CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOATBEGINDECLARE SUM FLOAT DEFAULT 0;SET SUM=num1 num2;RETURN SUM;END $SELECT test_fun1(1,2)$
存储过程
#存储过程和函数/*存储过程和函数:类似于java中的方法好处:1、提高代码的重用性2、简化操作*/#存储过程/*含义:一组预先编译好的SQL语句的集合,理解成批处理语句1、提高代码的重用性2、简化操作3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率*/#一、创建语法CREATE PROCEDURE 存储过程名(参数列表)BEGIN存储过程体(一组合法的SQL语句)END#注意:/*1、参数列表包含三部分参数模式  参数名  参数类型举例:in stuname varchar(20)参数模式:in:该参数可以作为输入,也就是该参数需要调用方传入值out:该参数可以作为输出,也就是该参数可以作为返回值inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值2、如果存储过程体仅仅只有一句话,begin end可以省略存储过程体中的每条sql语句的结尾要求必须加分号。存储过程的结尾可以使用 delimiter 重新设置语法:delimiter 结束标记案例:delimiter $*/#二、调用语法CALL 存储过程名(实参列表);#--------------------------------案例演示-----------------------------------#1.空参列表#案例:插入到admin表中五条记录SELECT * FROM admin;DELIMITER $CREATE PROCEDURE myp1()BEGININSERT INTO admin(username,`password`) VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');END $#调用CALL myp1()$#2.创建带in模式参数的存储过程#案例1:创建存储过程实现 根据女神名,查询对应的男神信息CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))BEGINSELECT bo.*FROM boys boRIGHT JOIN beauty b ON bo.id = b.boyfriend_idWHERE b.name=beautyName;END $#调用CALL myp2('柳岩')$#案例2 :创建存储过程实现,用户是否登录成功CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))BEGINDECLARE result INT DEFAULT 0;#声明并初始化SELECT COUNT(*) INTO result#(into 赋值给result)FROM adminWHERE admin.username = usernameAND admin.password = PASSWORD;SELECT IF(result>0,'成功','失败');#使用END $#调用CALL myp3('张飞','8888')$#3.创建out 模式参数的存储过程#案例1:根据输入的女神名,返回对应的男神名CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))BEGINSELECT bo.boyname INTO boynameFROM boys boRIGHT JOINbeauty b ON b.boyfriend_id = bo.idWHERE b.name=beautyName ;END $#案例2:根据输入的女神名,返回对应的男神名和魅力值CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) BEGINSELECT boys.boyname ,boys.usercp INTO boyname,usercpFROM boys RIGHT JOINbeauty b ON b.boyfriend_id = boys.idWHERE b.name=beautyName ;END $#调用CALL myp7('小昭',@name,@cp)$SELECT @name,@cp$#4.创建带inout模式参数的存储过程#案例1:传入a和b两个值,最终a和b都翻倍并返回CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)BEGINSET a=a*2;SET b=b*2;END $#调用SET @m=10$SET @n=20$CALL myp8(@m,@n)$SELECT @m,@n$#三、删除存储过程#语法:drop procedure 存储过程名DROP PROCEDURE p1;DROP PROCEDURE p2,p3;#�#四、查看存储过程的信息DESC myp2;�SHOW CREATE PROCEDURE  myp2;
源文地址:https://www.guoxiongfei.cn/csdn/7949.html
0