学习前准备： 1,sqlyog 数据库客户端工具（其他客户端也可以，比如Mysql workbench 或者navicat）2,需要导入两个库（我已转备好，可以留言，我分享给你） 下面是数据库表导入教
1,sqlyog 数据库客户端工具（其他客户端也可以，比如Mysql workbench 或者navicat）2,需要导入两个库（我已转备好，可以留言，我分享给你）下面是数据库表导入教程，以SQLyog为例
找到数据库表存放位置，点击执行即可
# DB：数据库，他保存了一系列有组织的数据；# DBMS：数据库管理系统；# SQL：结构化查询语言，用来与数据库进行沟通；# 1，通过 windows 自带的客户端 # 登录：MySQL -h 主机名 -p 端口号 -u 用户名 -p 密码（-p 和密码之间不能有空格） # 退出：exit## 1，Windows 客户端进入 MySQL,写 SQL 命令 select version();## 2，Windows 客户端写 MySQL --version*（结尾没有分号）## 1. 不区分大小写，建议关键字大写，表名，列名小写## 2. 每条命令最好分号结尾；## 3. 每条命令根据需要进行缩进，换行；## 4. 注释 1,单行注释：# 注释内容； 2,单行注释：-- 注释内容（-- 后面必须有空格） 3,多行注释：## 1. DQL：数据查询语言；select## 2. DML：数据操作语言 增删改## 3. DDL：数据定义语言## 4. TCL：事务控制语言## F12 可以对代码进行格式化；# 查询单个字段select last_name from employees;# 查询表中的多个字段select last_name , salary,email from employees;# 查询表中的所有字段select * from employees;# 查询常量表达式select 100;select 'aini';select 100*98;select version();## as 关键字 ## 方式一 select 98 * 100 as 运算结果 select last_name as 姓, first_name as 名 from employees; ## 方式二 select last_name 姓,first_name 名 from employees; ## 注意：特殊字符如#,关键字 建议加上双引号; select salaty as "out put" from employess;# 去重 distinct## 查询员工表中涉及到的所有部门编号select distinct department_id from employees;## 两个操作数是数值，则做加法运算；## 其中一个为字符串时，试图将其转换为数值，转换成功继续做数值的加法运算；如果转换失败，则将此字符串转换为 0；如果其中一方为 null，则结果已经为 null；select "aini" + 20 # 20select 50 + 30 # 80select "a" + "b" # 0## Null 与任何字段，任何数据拼接结果都是 nullselect concat('a','b','c') as 字符连接;select concat(last_name,first_name) as 姓名 from employees;## 1，IFNULL(x,y) x 为判断的值，y 为返回的值 ，意思就是假如 x 是 nullselect ifnull(commission_puc,0) as 奖金率,commission-puc from employees;## null 的地方会返回函数里面设置的值，不是null 的地方会返回本身的值# 按条件表达式进行筛选> < = != <> >= <=# 按逻辑表达式进行筛选&& || ! and not or# 模糊查询like , between and , in , null# 查询工资>12000的员工信息；select * from employees where salaty > 12000;# 查询部门编号不等于90号的员工名和部门编号select last_anme ,department_id from employees where department_id <> 90;# 查询员工工资在10000 到 20000 之间的员工名，工资，奖金select last_name,salary commIstion_put from employees where salary >= 10000 and salary <= 20000;# 查询部门编号不是在90 到 110 之间的，或者工资高于15000的员工信息；select * from employees where department_id < 90 or department_id > 110 or salary > 15000;select * from employees where not (department_id >= 90 and department_id <= 110) or salary > 15000;# %表示任意多个字符，也包含零个字符；# _表示任意单个字符# 查询员工名中包含字符a的员工信息select * from employees where last_name like '%a%';# 查询员工名中第三个字符为e的，第五个字符为a的员工名select last_name 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 '$';select last_name from employees where last_name like '_z_%' escape 'z';# 包含临界值，临界值不能颠倒# 查询员工编号在100到120之间的员工信息select * from employees where employee_id between 100 and 120;## 判断值是否等于in列表中的某一项## 不支持括号里使用通配符## in 列表的值类型必须统一或兼容# 查询员工的工种编号是IT_PROG,AD_VP,AD_PRES 中的一个工种名和工种编号；select last_name ,job_id from employees where job_id in ('IT_PROG','AD_VP','AD_PRES')## 不能用来判断字段值是否为空## 查询没有奖金的员工和奖金率select last_name,commistion_pct from employees where commisstion_pct is null## = 不能判断字段值是否为Null，所以使用is null## 查询有奖金的员工和奖金率select last_name,commistion_pct from employees where commission-pct is not null#案例1：查询没有奖金的员工名和奖金率SELECTlast_name,commission_pctFROMemployeesWHEREcommission_pct <=>NULL;#案例2：查询工资为12000的员工信息SELECTlast_name,salaryFROMemployeesWHERE salary <=> 12000;## 功能：判断一个字段值是否为 null；## Isnull（字段名），就一个参数，值为 null 返回 1，值不是为空则返回 0；# IS NULL:仅仅可以判断NULL值，可读性较高，建议使用## <=> :既可以判断NULL值，又可以判断普通的数值，可读性较低#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;
## Order by 语句一般放在最后## 分类：## 单行函数：## Concat() ; length() ; ifnull() ; isnull() 等；## 分组函数：## 功能：做统计使用，统计函数；##单行函数： ## 1. 字符函数： ## 2. 数学函数： ## 3. 日期函数： ## 4. 其他函数： ## 5. 流程控制函数：## 注一个汉字占三个字节SELECT LENGTH('john');SELECT LENGTH('张三丰hahaha');SHOW VARIABLES LIKE '%char%'SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;SELECT UPPER('john');SELECT LOWER('joHn');#示例：将姓变大写，名变小写，然后拼接SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;# 注意：索引从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_put FROM employees;## 可以放三个参数，第一个为字段或要截取的字符串，第二个是开始位置的索引，第三个是长度## Instr 返回一个小字符串在大字符串里面的第一次出现的起始索引找不到就返回 0SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;# 只去掉前后的空格或者前后的某个指定字符SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;## lpad 在左边填充指定字符，到指定长度（指定长度指的是总长度）SELECT LPAD('殷素素',2,'*') AS out_put;## 8，rpad 右填充，跟lpad同理SELECT RPAD('殷素素',12,'ab') AS out_put;## replace 替换 ，可以把所有被替换的内容都替换掉## 三个参数(字符串,’被替换的内容’,’替换的新内容’)SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;## 一个参数，直接四舍五入到整数，第二个参数可以设置保留的小数位数SELECT ROUND(1.567,2);SELECT ROUND(-1.55);SELECT FLOOR(-9.99);SELECT CEIL(-1.02);SELECT TRUNCATE(1.69999,1);## 结果是 1.6SELECT 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;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#案例：查询员工的工资的情况SELECT salary,CASE WHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别FROM employees;SELECT SUM(salary) FROM employees;SELECT AVG(salary) FROM employees;SELECT MIN(salary) FROM employees;SELECT MAX(salary) FROM employees;SELECT COUNT(salary) FROM employees;SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数FROM employees;SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 低,COUNT(salary) 个数FROM employees;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;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;SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;SELECT COUNT(salary) FROM employees;SELECT COUNT(*) FROM employees;SELECT COUNT(1) FROM employees;效率：## MYISAM存储引擎下 ，COUNT(*)的效率高## INNODB 存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些SELECT AVG(salary),employee_id FROM employees;## 返回两个日期相差的天数select datediff('2017-10-1','2017-05-18');select datediff(max(hiredate),min(hiredate)) from employees;#案例1：查询每个工种的员工平均工资SELECT AVG(salary),job_idFROM employeesGROUP BY job_id;#案例2：查询每个位置的部门个数SELECT COUNT(*),location_idFROM departmentsGROUP BY location_id;#案例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;#案例：查询哪个部门的员工个数>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 NULLGROUP BY job_idHAVING MAX(salary)>12000;#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资manager_id>102SELECT manager_id,MIN(salary)FROM employeesGROUP BY manager_idHAVING MIN(salary)>5000;#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序SELECT job_id,MAX(salary) mFROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING m>6000ORDER BY m ;#案例：查询每个工种每个部门的最低工资,并按最低工资降序SELECT MIN(salary),job_id,department_idFROM employeesGROUP BY department_id,job_idORDER BY MIN(salary) DESC;#案例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`;#查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM employees e,jobs jWHERE e.`job_id`=j.`job_id`;#查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM jobs j,employees eWHERE e.`job_id`=j.`job_id`;#案例：查询有奖金的员工名、部门名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%';#案例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`;#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序SELECT job_title,COUNT(*)FROM employees e,jobs jWHERE e.`job_id`=j.`job_id`GROUP BY job_titleORDER BY COUNT(*) DESC;#案例：查询员工名、部门名和所在的城市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;#案例1：查询员工的工资和工资级别SELECT salary,grade_levelFROM employees e,job_grades gWHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`AND g.`grade_level`='A';#案例：查询 员工名和上级的名称SELECT e.employee_id,e.last_name,m.employee_id,m.last_nameFROM employees e,employees mWHERE e.`manager_id`=m.`employee_id`;## 自连接就是把一张表用两次来查询数据## 单行函数 #1.显示系统时间(注：日期+时间) SELECT NOW(); #2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary） SELECT employee_id,last_name,salary,salary*1.2 "new salary" FROM employees; #3.将员工的姓名按首字母排序，并写出姓名的长度（length） SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符,last_name FROM employees ORDER BY 首字符; #4.做一个查询，产生下面的结果 <last_name> earns <salary> monthly but wants <salary*3> Dream Salary King earns 24000 monthly but wants 72000 SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary" FROM employees WHERE salary=24000; #5.使用case-when，按照下面的条件： job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 产生下面的结果 Last_nameJob_idGrade kingAD_PRESA SELECT last_name,job_id AS job, CASE job_id WHEN 'AD_PRES' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' WHEN 'SA_PRE' THEN 'D' WHEN 'ST_CLERK' THEN 'E' END AS Grade FROM employees WHERE job_id = 'AD_PRES';#分组查询 #1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序 SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROM employees GROUP BY job_id ORDER BY job_id; #2.查询员工最高工资和最低工资的差距（DIFFERENCE） SELECT MAX(salary)-MIN(salary) DIFFRENCE FROM employees; #3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary)>=6000; #4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序 SELECT department_id,COUNT(*),AVG(salary) a FROM employees GROUP BY department_id ORDER BY a DESC; #5.选择具有各个job_id的员工人数 SELECT COUNT(*) 个数,job_id FROM employees GROUP BY job_id; ## 排序查询 #1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序 SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC; #2.选择工资不在8000到17000的员工的姓名和工资，按工资降序 SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC; #3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序 SELECT *,LENGTH(email) FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC; # 分组函数 #1.查询公司员工工资的最大值，最小值，平均值，总和 SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和 FROM employees; #2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE） SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE FROM employees; SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE FROM employees; SELECT DATEDIFF('1995-2-7','1995-2-6'); #3.查询部门编号为90的员工个数 SELECT COUNT(*) FROM employees WHERE department_id = 90;#案例1.查询员工名、部门名SELECT last_name,department_nameFROM departments d JOIN employees eON e.`department_id` = d.`department_id`;#案例2.查询名字中包含e的员工名和工种名（添加筛选） SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e.`job_id`= j.`job_id` WHERE e.`last_name` LIKE '%e%';#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选） #①查询每个城市的部门个数 #②在①结果上筛选满足条件的 SELECT city,COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id`=l.`location_id` GROUP BY city HAVING COUNT(*)>3;#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序） #①查询每个部门的员工个数 SELECT COUNT(*),department_name FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id` GROUP BY department_name #② 在①结果上筛选员工个数>3的记录，并排序 SELECT COUNT(*) 个数,department_name FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id` GROUP BY department_name HAVING COUNT(*)>3 ORDER 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 * FROM beauty; SELECT * FROM boys; 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 b.*,bo.* FROM beauty b CROSS JOIN boys bo; #sql92和 sql99pk ## 连接查询#1.显示所有员工的姓名，部门号和部门名称。USE myemployees;SELECT last_name,d.department_id,department_nameFROM employees e,departments dWHERE e.`department_id` = d.`department_id`;#2.查询90号部门员工的job_id和90号部门的location_idSELECT job_id,location_idFROM employees e,departments dWHERE e.`department_id`=d.`department_id`AND e.`department_id`=90;#3.选择所有有奖金的员工的last_name , department_name , location_id , citySELECT last_name , department_name , l.location_id , cityFROM employees e,departments d,locations lWHERE e.department_id = d.department_idAND d.location_id=l.location_idAND e.commission_pct IS NOT NULL;#4.选择city在Toronto工作的员工的last_name , job_id , department_id , department_name SELECT last_name , job_id , d.department_id , department_name FROM employees e,departments d ,locations lWHERE e.department_id = d.department_idAND d.location_id=l.location_idAND city = 'Toronto';#5.查询每个工种、每个部门的部门名、工种名和最低工资SELECT department_name,job_title,MIN(salary) 最低工资FROM employees e,departments d,jobs jWHERE e.`department_id`=d.`department_id`AND e.`job_id`=j.`job_id`GROUP BY department_name,job_title;#6.查询每个国家下的部门个数大于2的国家编号SELECT country_id,COUNT(*) 部门个数FROM departments d,locations lWHERE d.`location_id`=l.`location_id`GROUP BY country_idHAVING 部门个数>2;#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employeesEmp#managerMgr#kochhar101king100SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"FROM employees e,employees mWHERE e.manager_id = m.employee_idAND e.last_name='kochhar';## 外连接#一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充SELECT b.id,b.name,bo.*FROM beauty bLEFT OUTER JOIN boys boON b.`boyfriend_id` = bo.`id`WHERE b.`id`>3;#二、查询哪个城市没有部门SELECT cityFROM departments dRIGHT OUTER JOIN locations l ON d.`location_id`=l.`location_id`WHERE d.`department_id` IS NULL;#三、查询部门名为SAL或IT的员工信息SELECT e.*,d.department_name,d.`department_id`FROM departments dLEFT JOIN employees eON d.`department_id` = e.`department_id`WHERE d.`department_name` IN('SAL','IT');SELECT * FROM departmentsWHERE `department_name` IN('SAL','IT');## 子查询#1.查询和ZloTKEy相同部门的员工姓名和工资 #①查询Zlotkey的部门 SELECT department_id FROM employees WHERE last_name = 'Zlotkey' #②查询部门号=①的姓名和工资 SELECT last_name,salary FROM employees WHERE department_id = ( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' )#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。 #①查询平均工资 SELECT AVG(salary) FROM employees #②查询工资>①的员工号，姓名和工资。 SELECT last_name,employee_id,salary FROM employees WHERE salary>( SELECT AVG(salary) FROM employees );#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资 #①查询各部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id #②连接①结果集和employees表，进行筛选 SELECT employee_id,last_name,salary,e.department_id FROM employees e INNER JOIN ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep ON e.department_id = ag_dep.department_id WHERE salary>ag_dep.ag ;#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 #①查询姓名中包含字母u的员工的部门 SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' #②查询部门号=①中的任意一个的员工号和姓名 SELECT last_name,employee_id FROM employees WHERE department_id IN( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' );#5. 查询在部门的location_id为1700的部门工作的员工的员工号 #①查询location_id为1700的部门 SELECT DISTINCT department_id FROM departments WHERE location_id = 1700 #②查询部门号=①中的任意一个的员工号 SELECT employee_id FROM employees WHERE department_id =ANY( SELECT DISTINCT department_id FROM departments WHERE location_id = 1700 );#6.查询管理者是King的员工姓名和工资 #①查询姓名为king的员工编号 SELECT employee_id FROM employees WHERE last_name = 'K_ing' #②查询哪个员工的manager_id = ① SELECT last_name,salary FROM employees WHERE manager_id IN( SELECT employee_id FROM employees WHERE last_name = 'K_ing' );#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名 #①查询最高工资 SELECT MAX(salary) FROM employees #②查询工资=①的姓.名 SELECT CONCAT(first_name,last_name) "姓.名" FROM employees WHERE salary=( SELECT MAX(salary) FROM employees );#案例1：谁的工资比 Abel 高? #①查询Abel的工资 SELECT salary FROM employees WHERE last_name = 'Abel' #②查询员工的信息，满足 salary>①结果 SELECT * FROM employees WHERE salary>( SELECT salary FROM employees WHERE last_name = 'Abel' );#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资 #①查询141号员工的job_id SELECT job_id FROM employees WHERE employee_id = 141 #②查询143号员工的salary SELECT salary FROM employees WHERE employee_id = 143 #③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>② SELECT last_name,job_id,salary FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 ) AND salary>( SELECT salary FROM employees WHERE employee_id = 143 );#案例3：返回公司工资最少的员工的last_name,job_id和salary #①查询公司的 最低工资 SELECT MIN(salary) FROM employees #②查询last_name,job_id和salary，要求salary=① SELECT last_name,job_id,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees );#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资 #①查询50号部门的最低工资 SELECT MIN(salary) FROM employees WHERE department_id = 50 #②查询每个部门的最低工资 SELECT MIN(salary),department_id FROM employees GROUP BY department_id#③ 在②基础上筛选，满足min(salary)>① SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>( SELECT MIN(salary) FROM employees WHERE department_id = 50 );#非法使用标量子查询 SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>( SELECT salary FROM employees WHERE department_id = 250 );#案例1：返回location_id是1400或1700的部门中的所有员工姓名 #①查询location_id是1400或1700的部门编号 SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) #②查询员工姓名，要求部门号是①列表中的某一个 SELECT last_name FROM employees WHERE department_id <>ALL( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) );#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary #①查询job_id为‘IT_PROG’部门任一工资 SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' #②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个 SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ANY( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG';#或 SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<( SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG';#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ALL( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG';#或 SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<( SELECT MIN( salary) FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG';##行子查询（结果集一行多列或多行多列）#案例：查询员工编号最小并且工资最高的员工信息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 employees WHERE employee_id=( SELECT MIN(employee_id) FROM employees )AND salary=( SELECT MAX(salary) FROM employees );#案例：查询每个部门的员工个数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) 部门名;#案例：查询每个部门的平均工资的工资等级#①查询每个部门的平均工资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后面（相关子查询）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`);#案例1：查询前五条员工信息SELECT * FROM employees LIMIT 0,5;SELECT * FROM employees LIMIT 5;#案例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 ;#引入的案例：查询部门编号>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';# 1. 查询工资最低的员工信息: last_name, salary#①查询最低的工资SELECT MIN(salary)FROM employees#②查询last_name,salary，要求salary=①SELECT last_name,salaryFROM employeesWHERE salary=(SELECT MIN(salary)FROM employees);# 2. 查询平均工资最低的部门信息#方式一：#①各部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_id#②查询①结果上的最低平均工资SELECT MIN(ag)FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id) ag_dep#③查询哪个部门的平均工资=②SELECT AVG(salary),department_idFROM employeesGROUP BY department_idHAVING AVG(salary)=(SELECT MIN(ag)FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id) ag_dep);#④查询部门信息SELECT d.*FROM departments dWHERE d.`department_id`=(SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary)=(SELECT MIN(ag)FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id) ag_dep));#方式二：#①各部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_id#②求出最低平均工资的部门编号SELECT department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) LIMIT 1;#③查询部门信息SELECT *FROM departmentsWHERE department_id=(SELECT department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) LIMIT 1);# 3. 查询平均工资最低的部门信息和该部门的平均工资#①各部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_id#②求出最低平均工资的部门编号SELECT AVG(salary),department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) LIMIT 1;#③查询部门信息SELECT d.*,agFROM departments dJOIN (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) LIMIT 1) ag_depON d.`department_id`=ag_dep.department_id;# 4. 查询平均工资最高的 job 信息#①查询最高的job的平均工资SELECT AVG(salary),job_idFROM employeesGROUP BY job_idORDER BY AVG(salary) DESCLIMIT 1#②查询job信息SELECT * FROM jobsWHERE job_id=(SELECT job_idFROM employeesGROUP BY job_idORDER BY AVG(salary) DESCLIMIT 1);# 5. 查询平均工资高于公司平均工资的部门有哪些?#①查询平均工资SELECT AVG(salary)FROM employees#②查询每个部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_id#③筛选②结果集，满足平均工资>①SELECT AVG(salary),department_idFROM employeesGROUP BY department_idHAVING AVG(salary)>(SELECT AVG(salary)FROM employees);# 6. 查询出公司中所有 manager 的详细信息.#①查询所有manager的员工编号SELECT DISTINCT manager_idFROM employees#②查询详细信息，满足employee_id=①SELECT *FROM employeesWHERE employee_id =ANY(SELECT DISTINCT manager_idFROM employees);# 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少#①查询各部门的最高工资中最低的部门编号SELECT department_idFROM employeesGROUP BY department_idORDER BY MAX(salary)LIMIT 1#②查询①结果的那个部门的最低工资SELECT MIN(salary) ,department_idFROM employeesWHERE department_id=(SELECT department_idFROM employeesGROUP BY department_idORDER BY MAX(salary)LIMIT 1);# 8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary#①查询平均工资最高的部门编号SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1 #②将employees和departments连接查询，筛选条件是① SELECT last_name, d.department_id, email, salary FROM employees e INNER JOIN departments d ON d.manager_id = e.employee_id WHERE d.department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1) ;#DDL#案例：创建库BooksCREATE DATABASE IF NOT EXISTS books ;RENAME DATABASE books TO 新库名;#更改库的字符集ALTER DATABASE books CHARACTER SET gbk;DROP DATABASE IF EXISTS books;#案例：创建表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;#①修改列名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;DROP TABLE IF EXISTS book_author;SHOW TABLES;#通用的写法：DROP DATABASE IF EXISTS 旧库名;CREATE DATABASE 新库名;DROP TABLE IF EXISTS 旧表名;CREATE TABLE 表名();INSERT INTO author VALUES(1,'村上春树','日本'),(2,'莫言','中国'),(3,'冯唐','中国'),(4,'金庸','中国');SELECT * FROM Author;SELECT * FROM copy2;CREATE TABLE copy LIKE author;CREATE TABLE copy2 SELECT * FROM author;CREATE TABLE copy3SELECT id,au_nameFROM author WHERE nation='中国';CREATE TABLE copy4 SELECT id,au_nameFROM authorWHERE 0;其他： 1，binary 和 varbinary 用于保存较短的二进制； 2，Enum 用于保存枚举 3，Set 用于保存集合日期型 1，date (4 字节) 1000-01-01 ~ 9999-12-31； 2，datetime (8 字节) 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59； 3，timestamp (4 字节) 1970~2038 年的某个时刻 （受时区的影响） 4，time 只有时间 5，year 只有年份 1901 ~ 2155*/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;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');DROP 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');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';#1.创建表dept1 NAMENULL?TYPE idINT(7) NAMEVARCHAR(25) USE test; CREATE TABLE dept1( id INT(7), NAME VARCHAR(25) );#2.将表departments中的数据插入新表dept2中 CREATE TABLE dept2 SELECT department_id,department_name FROM myemployees.departments;#3.创建表emp5 NAMENULL?TYPE idINT(7) First_nameVARCHAR (25) Last_nameVARCHAR(25) Dept_idINT(7) CREATE TABLE emp5( id INT(7), first_name VARCHAR(25), last_name VARCHAR(25), dept_id INT(7) );#4.将列Last_name的长度增加到50ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);#5.根据表employees创建employees2CREATE TABLE employees2 LIKE myemployees.employees;#6.删除表emp5DROP TABLE IF EXISTS emp5;#7.将表employees2重命名为emp5ALTER TABLE employees2 RENAME TO emp5;#8.在表dept和emp5中添加新列test_column，并检查所作的操作ALTER TABLE emp5 ADD COLUMN test_column INT;#9.直接删除表emp5中的列 dept_id DESC emp5; ALTER TABLE emp5 DROP COLUMN test_column;## 1，列级约束：6 大约束都支持，但外键约束没有效果；## 2，表级约束：除了非空，默认值约束，其余的都支持；## 1.创建表时## 2.修改表时#1.添加列级约束USE students;DROP TABLE stuinfo;CREATE TABLE stuinfo(id INT PRIMARY KEY,#主键stuName VARCHAR(20) NOT NULL UNIQUE,#非空gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查seat INT UNIQUE,#唯一age INT DEFAULT 18,#默认约束majorId INT REFERENCES major(id)#外键);CREATE TABLE major(id INT PRIMARY KEY,majorName VARCHAR(20));#查看stuinfo中的所有索引，包括主键、外键、唯一SHOW INDEX FROM stuinfo;DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo(id INT,stuname VARCHAR(20),gender CHAR(1),seat INT,age INT,majorid INT,CONSTRAINT pk PRIMARY KEY(id),#主键CONSTRAINT uq UNIQUE(seat),#唯一键CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键);SHOW INDEX FROM stuinfo;#通用的写法：★CREATE TABLE IF NOT EXISTS stuinfo(id INT PRIMARY KEY,stuname VARCHAR(20),sex CHAR(1),age INT DEFAULT 18,seat INT UNIQUE,majorid INT,CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id));alter table 表名 modify column 字段名 字段类型 新约束;alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo(id INT,stuname VARCHAR(20),gender CHAR(1),seat INT,age INT,majorid INT)DESC stuinfo;#1.添加非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;#2.添加默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;#①列级约束ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;#②表级约束ALTER TABLE stuinfo ADD PRIMARY KEY(id);#①列级约束ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;#②表级约束ALTER TABLE stuinfo ADD UNIQUE(seat);ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;ALTER TABLE stuinfo MODIFY COLUMN age INT ;ALTER TABLE stuinfo DROP PRIMARY KEY;ALTER TABLE stuinfo DROP INDEX seat;ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity(id INT ,NAME FLOAT UNIQUE AUTO_INCREMENT,seat INT );TRUNCATE TABLE tab_identity;INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');INSERT INTO tab_identity(NAME) VALUES('lucy');SELECT * FROM tab_identity;SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment=3;#1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);#2.向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）#3.向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。ALTER TABLE emp2 ADD COLUMN dept_id INT;ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id); 位置 支持的约束类型 是否可以起约束名列级约束：列的后面 语法都支持，但外键没有效果 不可以表级约束：所有列的下面默认和非空不支持，其他支持 可以（主键没有效果）*语法：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);INSERT INTO beautySET id=19,NAME='刘涛',phone='999';#1、方式一支持插入多行,方式二不支持INSERT INTO beautyVALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2),(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2),(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);#2、方式一支持子查询，方式二不支持INSERT INTO beauty(id,NAME,phone)SELECT 26,'宋茜','11809866';INSERT INTO beauty(id,NAME,phone)SELECT id,boyname,'1234567'FROM boys WHERE id<3;#案例1：修改beauty表中姓唐的女神的电话为13899888899UPDATE beauty SET phone = '13899888899'WHERE NAME LIKE '唐%';#案例2：修改boys表中id好为2的名称为张飞，魅力值 10UPDATE boys SET boyname='张飞',usercp=10WHERE id=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;#案例：删除手机号以9结尾的女神信息DELETE FROM beauty WHERE phone LIKE '%9';SELECT * FROM beauty;#案例：删除张无忌的女朋友的信息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`='黄晓明';#案例：将魅力值>100的男神信息删除TRUNCATE TABLE boys SELECT * FROM boys;DELETE FROM boys;TRUNCATE TABLE boys;INSERT INTO boys (boyname,usercp)VALUES('张飞',100),('刘备',100),('关云长',100);#1.运行以下脚本创建表my_employeesUSE myemployees;CREATE TABLE my_employees(Id INT(10),First_name VARCHAR(10),Last_name VARCHAR(10),Userid VARCHAR(10),Salary DOUBLE(10,2));CREATE TABLE users(id INT,userid VARCHAR(10),department_id INT);#2.显示表my_employees的结构DESC my_employees;#3.向my_employees表中插入下列数据 IDFIRST_NAMELAST_NAMEUSERIDSALARY 1patelRalphRpatel895 2DancsBettyBdancs860 3BiriBenBbiri1100 4NewmanChadCnewman750 5RopeburnAudreyAropebur1550#方式一： INSERT INTO my_employees VALUES(1,'patel','Ralph','Rpatel',895), (2,'Dancs','Betty','Bdancs',860), (3,'Biri','Ben','Bbiri',1100), (4,'Newman','Chad','Cnewman',750), (5,'Ropeburn','Audrey','Aropebur',1550); DELETE FROM my_employees; #方式二： INSERT INTO my_employees SELECT 1,'patel','Ralph','Rpatel',895 UNION SELECT 2,'Dancs','Betty','Bdancs',860 UNION SELECT 3,'Biri','Ben','Bbiri',1100 UNION SELECT 4,'Newman','Chad','Cnewman',750 UNION SELECT 5,'Ropeburn','Audrey','Aropebur',1550;#4. 向users表中插入数据 1Rpatel10 2Bdancs10 3Bbiri20 4Cnewman30 5Aropebur40 INSERT INTO users VALUES(1,'Rpatel',10), (2,'Bdancs',10), (3,'Bbiri',20);#5.将3号员工的last_name修改为“drelxer”UPDATE my_employees SET last_name='drelxer' WHERE id = 3;#6.将所有工资少于900的员工的工资修改为1000UPDATE my_employees SET salary=1000 WHERE salary<900;#7.将userid 为Bbiri的user表和my_employees表的记录全部删除 DELETE u,e FROM users u JOIN my_employees e ON u.`userid`=e.`Userid` WHERE u.`userid`='Bbiri';#8.删除所有数据 DELETE FROM my_employees; DELETE FROM users; #9.检查所作的修正 SELECT * FROM my_employees; SELECT * FROM users;#10.清空表my_employeesTRUNCATE TABLE my_employees;1. 原子性## 事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生2. 一致性## 事务必须使数据库从一个形状状态变换到另外一个一致性状态3. 隔离性## 一个事务不能被其他事务干扰，事务之间是隔离的，互不干扰4. 持久性## 事务一旦被提交，它对数据库中数据的改变就是永久性的## 隐式事务：事务没有明显的开启和结束标记，比如 insert，update，delete 语句；## 显示事务：具有明显的开启和结束标志。先设置自动提交功能为禁用，SET autocommit = 0;
## 步骤一：开启事务 1，Set autocommit = 0; 2，Start transaction;(可选的，可以不写)； ## 步骤二：编写事务中的 SQL 语句 3，语句 1;（select，insert，update，delete） 4，语句 2； ## 步骤三：结束事务（提交事务或回滚事务）commit or rollback#开启事务 SET autocommit=0; START TRANSACTION; #编写一组事务的语句 UPDATE account SET balance = 1000 WHERE username='张无忌'; UPDATE account SET balance = 1000 WHERE username='赵敏';#结束事务 ROLLBACK; #commit;## 对于同时运行的多个事务，当这些事务访问数据库中的相同数据时，如果没有采取必要的隔离机制，就会导致各种并发问题；## 脏读：对于两个事务 T1，T2，T1 读取了已经被 T2 更新但还没被提交的字段之后，若 T2 回滚，T1 读取的内容就是临时无效的；## 不可重复读：对于两个事务 T1，T2，T1 读取了一个字段，然后 T2 更新了该字段之后，T1 再次读取该字段，值就不一样了；## 幻读：对于两个事务 T1，T2，T1 从一个表中读取了一个字段，然后 T2 在该表中插入了一些新的行之后，如果 T1 再次读取同一个表，就会多出几行；## MySQL 支持 4 种事务隔离级别。MySQL 默认的事务隔离级别为：REPEATABLE READ
## 1，5.7.2 之前 使用 show variables like 'tx_isoation' 或者 select @@tx_isolation;## 2 ， 5.7.2 之 后 使 用 show variables like 'transaction_isoation' 或 者 select @@transaction_isoation;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;SET autocommit=0;START TRANSACTION;DELETE FROM account;ROLLBACK;USE myemployees;#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 myv2 AS SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id; #②使用 SELECT myv2.`ag`,g.grade_level FROM myv2 JOIN job_grades g ON 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`;#方式一：SELECT * FROM myv3 CREATE OR REPLACE VIEW myv3ASSELECT AVG(salary),job_idFROM employeesGROUP BY job_id;ALTER VIEW myv3ASSELECT * FROM employees;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;INSERT INTO myv1 VALUES('张飞','zf@qq.com');UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';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';#一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱CREATE OR REPLACE VIEW emp_v1ASSELECT last_name,salary,emailFROM employeesWHERE phone_number LIKE '011%';#二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息CREATE OR REPLACE VIEW emp_v2ASSELECT MAX(salary) mx_dep,department_idFROM employeesGROUP BY department_idHAVING MAX(salary)>12000;SELECT d.*,m.mx_depFROM departments dJOIN emp_v2 mON m.department_id = d.`department_id`;## 系统变量：变量由系统提供，不是用户定义，属于服务器层面## 1，查看所有的系统变量 SHOW VARIABLES; ## 2，查看满足条件的部分系统变量；SHOW VARIABLES LIKE '%char%';## 3，查看指定的某个系统的变量值；SELECT @@character_set_client;@@global.变量名 @@session.变量名 @@变量名## 4，为系统变量名赋值 1，set 变量名 = 值（global.变量名||session.变量名） 2，Set @@global.变量名 = 值 (session.变量名) ## 如果是全局级别加 global，如果是会话级别加 session 关键字## 全局变量 SHOW GLOBAL VARIABLES; (1) 查看部分全局变量： SHOW GLOBAL VARIABLES LIKE '%char%'； (2) 查看指定的全局变量的值： SELECT @@global.autocommit; (3) 为某个指定的全局变量赋值 SET @@global.autocommit=0; ## 作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接有效），但不能跨重启SHOW SESSION VARIABLES;## 作用域：仅仅针对于当前的会话（连接有效）(1) 查看部分会话变量：SHOW SESSION VARIABLES LIKE '%char%'；(2) 查看指定的全局变量的值：SELECT @@SESSION.autocommit;(3) 为某个指定的全局变量赋值 SET @@SESSION.变量名=值;## 自定义变量: 用户自定义的变量，不是系统提供的## 作用域：仅仅针对于当前的会话（连接有效）可以应用在任何地方1 声明并初始化 ## （1）SET @用户变量名:=值；(为了不跟等号起冲突); ## （2）SET @用户变量名=值； ## （3）SELECT @用户变量名:=值； 2 更新用户变量的值（跟声明初始化一样） ## (1) SET @用户变量名:=值；(为了不跟等号起冲突); ## (2) SET @用户变量名=值； ## (3) SELECT @用户变量名:=值； ## (4) SELECT 字段 INTO @变量名 FROM 表（查询出来的值必须是一个值才能赋给这个变量）
3 查看用户变量的值## （1）SELECT @用户变量名## 作用域：仅仅在定义它的 begin end 中有效；1) 声明：DECLARE 变量名 类型 || DECLARE 变量名 类型 DEFAULT 值；2) 赋值： ## （1）SET 用户变量名:=值；(为了不跟等号起冲突); ## （2）SET 用户变量名=值； ## （3）SELECT 用户变量名:=值； ## （4）SELECT 字段 INTO 变量名 FROM 表3) 使用：SELECT 局部变量名；#案例：声明两个变量，求和并打印#用户变量SET @m=1;SET @n=1;SET @sum=@m+@n;SELECT @sum;#局部变量DECLARE m INT DEFAULT 1;DECLARE n INT DEFAULT 1;DECLARE SUM INT;SET SUM=m+n;SELECT SUM;#用户变量和局部变量的对比 作用域 定义位置 语法用户变量当前会话 会话的任何地方 加@符号，不用指定类型局部变量定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型CREATE PROCEDURE 存储过程名(参数列表)BEGIN存储过程体（一组合法的SQL语句）END#注意：CALL 存储过程名(实参列表);#案例：插入到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()$#案例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#赋值FROM adminWHERE admin.username = usernameAND admin.password = PASSWORD;SELECT IF(result>0,'成功','失败');#使用END $#调用CALL myp3('张飞','8888')$#案例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$#案例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;#一、创建存储过程实现传入用户名和密码，插入到admin表中CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))BEGININSERT INTO admin(admin.username,PASSWORD)VALUES(username,loginpwd);END $#二、创建存储过程实现传入女神编号，返回女神名称和女神电话CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))BEGINSELECT b.name ,b.phone INTO NAME,phoneFROM beauty bWHERE b.id = id;END $#三、创建存储存储过程或函数实现传入两个女神生日，返回大小CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)BEGINSELECT DATEDIFF(birth1,birth2) INTO result;END $#四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))BEGINSELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;END $CALL test_pro4(NOW(),@str)$SELECT @str $#五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串如 传入 ：小昭返回： 小昭 AND 张无忌DROP PROCEDURE test_pro5 $CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))BEGINSELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO strFROM boys boRIGHT JOIN beauty b ON b.boyfriend_id = bo.idWHERE b.name=beautyName;SET str=END $CALL test_pro5('柳岩',@str)$SELECT @str $#六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录DROP PROCEDURE test_pro6$CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT)BEGINSELECT * FROM beauty LIMIT startIndex,size;END $CALL test_pro6(3,5)$create function 函数名(函数列表) returns 返回类型 begin 函数体 end含义：一组预先编译好的SQL语句的集合，理解成批处理语句1、提高代码的重用性2、简化操作3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率区别：存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新函数：有且仅有1 个返回，适合做处理数据后返回一个结果1.参数列表包含两个部分，参数名和参数类型；2.函数体肯定会有return语句，如果没有会报错；如果return语句没有放在函数体的最后也不报错但不建议3.函数体中仅有一句话则可以省略begin end；4.使用delimiter语句设置结束标记；SELECT 函数名(参数列表)#1.无参有返回#案例：返回公司的员工个数CREATE FUNCTION myf1() RETURNS INTBEGINDECLARE c INT DEFAULT 0;#定义局部变量SELECT COUNT(*) INTO c#赋值FROM employees;RETURN c;END $SELECT myf1()$#案例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)$## 语法： ## IF(表达式1，表达式2，表达式3)； ## 如果表达式1成立，则返回表达式2的值，不成立则返回表达式3的值；① 一般用于实现等值判断；② 一般用于实现区间判断语法1： CASE 变量|表达式|字段 WHEN 要判断的值1 THEN 返回的值1WHEN 要判断的值2 THEN 返回的值2WHEN 要判断的值3 THEN 返回的值3...ELSE 要返回的值nEND CASE;语法2： CASE WHEN 要判断的条件1 THEN 返回的语句1或值；WHEN 要判断的条件2 THEN 返回的值2或语句；WHEN 要判断的条件3 THEN 返回的值3或语句；...ELSE 要返回的条件nEND CASE;## 特点：可以作为表达式，嵌套在其他语句中使用；也可以作为独立的语句使用（可以放在任何地方）如果作为独立的语句只用，只能放在begin end中#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回DCREATE FUNCTION test_case(score FLOAT) RETURNS CHARBEGIN DECLARE ch CHAR DEFAULT 'A';CASE WHEN score>90 THEN SET ch='A';WHEN score>80 THEN SET ch='B';WHEN score>60 THEN SET ch='C';ELSE SET ch='D';END CASE;RETURN ch;END $SELECT test_case(56)$## 语法：IF 条件1 THEN 语句1；ELSEIF 条件2 THEN 语句2；,,,ELSE 条件n;END IF;## 应用在begin end 中；#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回DCREATE FUNCTION test_if(score FLOAT) RETURNS CHARBEGINDECLARE ch CHAR DEFAULT 'A';IF score>90 THEN SET ch='A';ELSEIF score>80 THEN SET ch='B';ELSEIF score>60 THEN SET ch='C';ELSE SET ch='D';END IF;RETURN ch;END $SELECT test_if(87)$#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500CREATE PROCEDURE test_if_pro(IN sal DOUBLE)BEGINIF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;END IF;END $CALL test_if_pro(2100)$WHILE, LOOP, REPEAT,1.ITERATE 类似于continue，结束本次循环，继续下次循环；2.LEAVE 类似于 break，跳出，结束当前所在的循环；#1.while#2.loop#3.repeat#1.没有添加循环控制语句#案例：批量插入，根据次数插入到admin表中多条记录DROP PROCEDURE pro_while1$CREATE PROCEDURE pro_while1(IN insertCount INT)BEGINDECLARE i INT DEFAULT 1;WHILE i<=insertCount DOINSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');SET i=i+1;END WHILE;END $CALL pro_while1(100)$#2.添加leave语句#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止TRUNCATE TABLE admin$DROP PROCEDURE test_while1$CREATE PROCEDURE test_while1(IN insertCount INT)BEGINDECLARE i INT DEFAULT 1;a:WHILE i<=insertCount DOINSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');IF i>=20 THEN LEAVE a;END IF;SET i=i+1;END WHILE a;END $CALL test_while1(100)$#3.添加iterate语句#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次TRUNCATE TABLE admin$DROP PROCEDURE test_while1$CREATE PROCEDURE test_while1(IN insertCount INT)BEGINDECLARE i INT DEFAULT 0;a:WHILE i<=insertCount DOSET i=i+1;IF MOD(i,2)!=0 THEN ITERATE a;END IF;INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');END WHILE a;END $CALL test_while1(100)$DROP TABLE IF EXISTS strinGContent;CREATE TABLE stringcontent(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(20));DELIMITER $CREATE PROCEDURE test_randstr_insert(IN insertCount INT)BEGINDECLARE i INT DEFAULT 1;DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';DECLARE startIndex INT;#代表初始索引DECLARE len INT;#代表截取的字符长度WHILE i<=insertcount DOSET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1-（20-startIndex+1）INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));SET i=i+1;END WHILE;END $CALL test_randstr_insert(10)$import pymysql# 连接MySQL数据库db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='test',port=3306)# 设置字符集 防止乱码db.set_charset('utf8')# 创建游标对象cursor = db.cursor()# 准备SQLsql = 'select * from user'# 执行SQL语句cursor.execute(sql)# 获取所有print(cursor.fetchall())print(cursor.fetchone())# 关闭数据库连接db.close()import pymysql# 连接MySQL数据库db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='test',port=3306)# 设置字符集 防止乱码db.set_charset('utf8')# 创建游标对象cursor = db.cursor()try: # 准备插入SQL语句 sql = 'insert into user values(4,"刘强东", 50, "男")' # 执行SQL语句 cursor.execute(sql) # 提交事务 保存到数据库中 db.commit()except Exception as e: print(e) # 回滚 db.rollback()# 对于插入获取受影响的行数print(cursor.rowcount)# 关闭数据库连接db.close()来源地址：https://blog.csdn.net/m0_67844671/article/details/133246379
--结束END--
本文标题: 【后端开发---MySQL】mysql数据库基础（增删改查，函数，事务控制）特详细，保姆级教程
本文链接: https://www.lsjlt.com/news/423964.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0