【基础篇】
数据库常见概念
一、
1.DB:数据库，存储数据的容器
2.DBMS:数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
3.SQL:结构化查询语言，用于数据库通信的语言，不是某个数据库软件特有，而是几乎所有的主流数据库软件通用的语言
二、
1.数据存放到表中，然后表再放到库中
2.一个库中可以有多张表，每张表具有唯一的表名用来表示自己
3.表中有一个或多个列，列又称为“字段”，相当于Java中“属性”
4.表中的每一行数据，相当于Java中“对象”
三、常见的数据库管理系统
mysql、oracle、db2、sqlserver
四、
所用到的数据库可视化工具sqlyog、数据库mysql
SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。
DQL( Data Query Language 数据查询语言 )
DML（Data manipulation language 数据操纵语言）
DDL(Data definition language 数据定义语言)
DCL(Data control language 数据控制语言 )
结合配套练习视频
MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷_哔哩哔哩_bilibili
Mysql服务的启动和停止
方式一：计算机-右击管理-服务
方式二：通过管理员身份运行
net start 服务名（mysql）[启动服务]
net stop 服务名（mysql）[关闭服务]
Mysql服务登录与退出
登录：
打开命令提示符以管理员身份运行
输入指令:mysql -u root -p（1234567）密码
退出：
exitMysql常见命令
1.查看当前所有数据库
show databases;
2.打开指定库
use test(库名);
3.查看当前库的所有表
show tables;
4.查看其他库的所有表
show tables from test01(库名);
5.创建表
注意多个变量需要
create table 表名(
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
列名 类型，
列名 类型，
..........
);
6.查看表结构
desc test02(表名);
7.查看服务器版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --versionMysql语法规范
1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/*注释文字*/
DQL语言
DQL( Data Query Language 数据查询语言 )
数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件> #进阶1：基础查询
/*语法：
select 查询列表 from 表名：
类似于：System.out.printlt(打印东西);
特点：
1.查询列表中可以是：表中的字段、常量值、表达式、函数
2.查询结果是一个虚拟的表格
*/
#1.查询表中的单个字段
SELECT last_name FROM employees;
#2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;
#3.查询表中的所有字段
SELECT * FROM employees;
#4.查询常量值
SELECT 100;
SELECT 'john';
#5.查询表达式
SELECT 100%98;
#6.查询函数
SELECT VERSION();
#7.起别名
/*
1:便于理解
2.如果查询的字段重名的情况，使用别名可以区分开来
*/
#方式一：
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二：
SELECT last_name 姓,first_name 名 FROM employees;
#案例：查询salary,显示结果为out put是错误的
SELECT salary AS OUT put FROM employees;
#正确形式
SELECT salary AS "out put" FROM employees;
#8.去重
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;
#9.+号的作用
/*
java中的+号
1.运算符，两个操作数都为数值型
2.连接符，只要有一个操作数为字符串
mysql中的+号
仅仅只有一个功能
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90; 其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select 'john'+90; 如果转换失败，则将字符型数值转换成0
select null+10; 只要其中一方为null，则结果肯定为null
*/
#案例1：查询员工名和姓连接成一个字段，并显示为姓名
错误形式
SELECT last_name + first_name AS 姓名 FROM employees ;
#正确形式
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
#案例2：查询员工奖金率出现NULL结果显示全部为NULL
SELECT CONCAT(first_name,last_name,job_id,commission_pct)AS out_put
FROM employees;
#正确形式 SELECT IFNULL(commission_pct,0)如果commission_pct返回Null则返回0【0可已修改为其他返回数】
SELECT CONCAT(first_name,last_name,job_id,IFNULL(commission_pct,0)) AS out_put
FROM employees;
#进阶二：条件查询
语法：
select
查询表
from
表名
where
筛选条件;
分类：
一、按条件表达式筛选
条件运算符：> ,<,=,!=,<>,>=,<=
二、按逻辑表达式筛选
逻辑运算符：&& 、||、 !
and、or、not
三、模糊查询
like、between and、in、is null
#一、按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT *FROM employees WHERE salary>12000;
#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id!=90;
#二、按逻辑表达式筛选
#案例1：查询工资在10000到20000之间的员工名、工资以及奖金
SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;
#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;
三、模糊查询
like
特点：
1.一般和通配符搭配使用
通配符：
%任意多个字符,包含0个字符
_任意单个字符
between and
特点：
1.使用BETWEEN AND可以提高语句的简洁度
2.包含临界值
3.两临界值不可调换顺序
in
含义：判断某字段的值是否属于IN列表中的某一项
特点：
1.使用IN提高语言简洁度
2.IN列表的值类型必须一致或者兼容
3.IN不支持通配符 % _
is null|is not null
#1.LIKE
#案例1：查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
#案例2：查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '__e_a%';
#案例3：查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees
WHERE last_name LIKE '_\_%'; #\为转义符，或者 LIKE '_@_%' ESCAPE '@';
#2.BETWEEN AND
#案例1：查询员工编号在100到120之间的员工信息
SELECT * FROM employees
WHERE employee_id>=100 AND employee_id<=120;
---------------------------------------------------------------------
SELECT * FROM employees
WHERE employee_id BETWEEN 100 AND 120;
#3.IN
#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_RPES中的一个员工和工种编号
SELECT last_name,job_id FROM employees
WHERE job_id='IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES';
---------------------------------------------------------------------
SELECT last_name,job_id FROM employees
WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');
#4.IS NULL 注意：IS NULL或IS NOT NULL可以判断NULL值
#案例1：查询有奖金率的员工名和奖金率
SELECT last_name,commission_pct FROM employees
WHERE commission_pct IS NOT NULL;
#补充内容 安全等于<=>
#案例1：查询没有奖金的员工和奖金率
SELECT last_name,commission_pct FROM employees
WHERE commission_pct <=> NULL;
#案例2：查询工资为12000的员工信息
SELECT last_name,salary FROM employees
WHERE salary <=>12000;#进阶三：排序查询
SELECT 查询表 FROM 表 WHERE 筛选条件 ORDER BY 排序列表 【asc|desc】
#特点：
1.ASC代表是升序，DESC代表的是降序，如果不写默认是升序
2.ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名
3.ORDER BY子句一般是放在查询语句的最后面,limit子句除外
#案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT * FROM employees
WHERE department_id>=90 ORDER BY hiredate ASC;
#案例3：按年薪的高低显示员工信息和年薪【按表达式排序】
SELECT * , salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT * , salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees ORDER BY 年薪 DESC;
#案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) AS 字节长度, last_name,salary
FROM employees ORDER BY LENGTH(last_name)DESC;
#案例6：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;
#题目练习
1.查询员工姓名和部门号和年薪，按年薪降序按姓名升序
SELECT last_name AS 名字,department_id, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees ORDER BY 年薪 DESC, 名字 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;
#进阶四：常见函数
功能：类似于Java的方法，将一组逻辑语句封装在方法中，对外暴露方法名
好处：
1.隐藏了实现细节
2.提高代码的重用性
调用：select 函数名() FROM 表;
特点：
1.叫什么(函数名)
2.干什么(函数功能)
分类：
1、单行函数
如concat、length、ifnull
2、分组函数
功能：做统计使用、又称为统计函数、聚合函数、组函数
语法：
select 分组函数，分组后的字段 步骤五
from 表 步骤一
【筛选条件】 步骤二
group by分组字段 步骤三
【having】 分组后的筛选 步骤四
【order by】 排序列表 步骤六
常见函数：
1.字符函数：length、concat、substr、instr、trim、upper、lower、lpad、rpad、replace
2.数学函数：round、ceil、floor、truncate、mod
3.日期函数：now、curtime、yead、month、monthname、day、hour、minute、second、str_to_date、date_format
其他函数：version、database、if、case
USE myemployees;
一、字符函数
1.length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');
2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
3.upper(转换成大写)、lower(转换成小写)
SELECT UPPER('john'); //转大写
SELECT LOWER('john'); //转小写
#案例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),'&',LOWER(first_name)) AS 姓名
FROM employees;
4.substr、substring
注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTRING('李莫愁爱上了陆展源',7) AS 输出;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展源',1,3) AS 输出;
#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), LOWER(SUBSTR(last_name,2))) 输出
FROM employees;
5.instr 用于返回字串的起始索引
SELECT INSTR ('杨不梅爱上了殷六侠','殷八侠') AS out_put
FROM employees;
6.trim 清除所需字符串的前后元素，默认清除前后空格
SELECT TRIM(' 张翠山 ') AS out_put;
SELECT TRIM('a' FROM 'aaaaaa张aaaaaaa翠山aaaaaaaaa') AS out_put;
7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*') AS out_put;
8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',21,'*') AS out_put;
9.replace 替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','美女') AS out_put;
#二、数学函数
1.round 四舍五入
SELECT ROUND(1,65);
SELECT ROUND(1.567,2);
2.ceil 向上取整,返回大于等于该参数的最小整数
SELECT CEIL(1.52);
3.floor 向下取整，返回小于等于该参数的最大整数
SELECT FLOOR(-9.99);
4.truncate 截断
SELECT TRUNCATE (1.6415155,1);
5.mod取余mod(a,b): a-a/b*b 注意a/b是取整不是四舍五入
SELECT MOD(10,-3);
SELECT 10%3;
#三、日期函数
1.now 返回当前系统日期+时间
SELECT NOW();
2.curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
3.curtime 返回当前时间，不包含日期
SELECT CURTIME();
4.可以获取指定的部分，年、月、日、时、分、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT DISTINCT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW())月;
#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%m-%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');
#data_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期
FROM employees WHERE 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的效果一致
CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1或语句1；
WHEN 常量2 THEN 要显示的值2或语句2；
.........
ELSE 要显示的值n或语句 END
FROM 表;
#案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary END AS 新工资
FROM employees;
3.case函数的使用二：类似与 多重 IF
CASE WHEN 条件1 THEN 要显示的值1或 语句1
WHEN 条件2 THEN 要显示的值2或 语句2
...............
ELSE 要显示的值n或语句n
END
#案例：查询员工的工资的情况
如果工资>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;
题目练习
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 首字母 ASC;
4.做一个查询,产生一下结果
<last_name> earns <salary> monthly but wants<salary*3> Dream Salary
King earns 24000 monthly but wants 72000 xxxxx
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_PRES C
产生下面结果
Last_name job Grade
King AD_PRES A
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'
END AS Grade
FROM employees;
二、分组函数 功能用作统计使用,又称为聚合函数或统计函数或组函数
分类：
sum求和、avg平均值、max最大值、min最小值、count计算个数
参数类型支持哪些类型
特点：
1.sum、avg一般用于处理数值型
2.max、min、count可以处理任何类型
3.以上类型分组函数都忽略null值
4.可以和distinct搭配实现去重
5.一般使用count(*)用作统计行数
6.和分组函数一同查询的字段要求是group by后的字段
1.简单的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary)个数 FROM employees;
2.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary)FROM employees;
3.和分组函数一同查询的字段有限制
错误写法
SELECT AVG(salary),employee_id FROM employees;
题目练习
1.查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary) 最大值,MIN(salary) 最小值, AVG(salary) 平均值,SUM(salary)总和
FROM employees;
2.查询员工表的最大入职时间和最小入职时间的相差天数(diffrence)
SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) diffrence
FROM employees;
3.查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees
WHERE department_id=90;#进阶五：分组查询
语法:SELECT 分组函数,分组列表（要求出现在group by的后面）
FROM 表 WHERE 筛选条件 GROUP BY 分组列表 ORDER BY 子句
注意：查询列表必须特殊,要求是分组函数和group BY 后出现的字段
特点：
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
2、分组函数做条件肯定是放在having子句中
3、能用分组前筛选的,就优先考虑使用分组前筛选
4、group by子句支持单个字段分组,多个字段分组
(多个直接用逗号隔开没有顺序要求,表达式或函数)
5、也可以添加排序（排序放在整个分组查询的最后）
案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees GROUP BY job_id;
案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments GROUP BY location_id;
添加筛选条件
案例1：查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary) ,manager_id
FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
#添加筛选条件
案例1：查询哪个部门的员工个数>2
1.查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees GROUP BY department_id;
2.根据1的结果进行筛选,查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees GROUP BY department_id HAVING COUNT(*)>2 ;
#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
1.查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id FROM employees
WHERE commission_pct IS NOT NULL GROUP BY job_id;
2.根据1结果继续筛选,最高工资>12000
SELECT MAX(salary),job_id FROM employees
WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;
案例3：查询每个领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
1.查询每个领导手下的员工最低工资
SELECT MIN(salary),manager_id
FROM employees GROUP BY manager_id;
2.添加筛选条件：编号>102
SELECT MIN(salary),manager_id
FROM employees WHERE manager_id>102 GROUP BY manager_id;
3. 添加筛选条件：最低工资>5000
SELECT MIN(salary),manager_id
FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;
#按表达式或函数分组
#案例：按员工姓名的长度分组，查询每组的员工个数，筛选分组员工个数>5的有哪些
1.查询每组长度的员工个数
SELECT COUNT(*),LENGTH(last_name)
FROM employees GROUP BY LENGTH(last_name);
2.筛选分组员工个数>5
SELECT COUNT(*),LENGTH(last_name)
FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;
#按多个字段分组
#案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees GROUP BY department_id,job_id;
#添加排序
案例：查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id ORDER BY AVG(salary) ASC ;
题目练习
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 DESC;
2.查询员工最高工资和最低工资的差距
SELECT MAX(salary)-MIN(salary) 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 COUNT(*),AVG(salary),department_id
FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC;
5.选择具有job_id的员工人数
SELECT COUNT(*),job_id FROM employees GROUP BY job_id;
#进阶六：连接查询
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=n*m行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
SELECT * FROM beauty;
SELECT * FROM boys;
分类：
按年代分类：
SQL92标准：仅仅支持内连接
SQL99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左连接
右连接
自连接
交叉连接
笛卡尔集的错误情况;
SELECT NAME,boyName FROM boys,beauty;
1.SELECT COUNT(*) FROM beauty; 假设输出12行
2.SELECT COUNT(*) FROM boys; 假设输出4行
最终结果12*4=48行
#一、SQL 92标准
1.等值连接
多表等值连接的结果为多表的交集部分
n表连接,至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
#案例1：查询女神名和对应的男神名
SELECT NAME,boyName FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;
#案例2：查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments
WHERE employees.department_id=departments.department_id;
2.查询工种号、工种名、员工名(可以为表起别名)提高语句的简洁度区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
SELECT last_name,A.job_id,job_title
FROM employees AS A,jobs AS B WHERE A.`job_id`=B.`job_id`;
3.两个表的顺序是否可以调换[结果是可以]
SELECT last_name,B.job_id,job_title
FROM jobs AS A ,employees AS B WHERE A.`job_id`=B.`job_id`;
4.可以加筛选
#案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees AS A,departments AS B
WHERE A.department_id=B.department_id AND A.commission_pct IS NOT NULL;
#案例2：查询城市名总是第二个字符为o的部门
SELECT department_name,city
FROM departments AS A,locations AS B
WHERE A.`location_id`=B.`location_id` AND city LIKE '_o%';
5.可以加分组查询
#案例1：查询每个城市的部门个数
【注意：此处查询结果是department里面department_id不重复,location_id重复】
SELECT COUNT(*),B.city FROM departments AS A,locations AS B
WHERE A.location_id=B.location_id GROUP BY B.city;
6.可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees AS A ,jobs AS B
WHERE A.job_id=B.job_id GROUP BY job_title
ORDER BY COUNT(*) DESC;
案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees
WHERE commission_pct IS NOT NULL GROUP BY manager_id;
7.可以实现多表连接
#案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees AS A,departments AS B,locations AS C
WHERE A.department_id=B.department_id
AND B.location_id=C.location_id AND city LIKE 's%'
ORDER BY department_name DESC;
首先创建等级表
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
二、非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level FROM employees AS A,job_grades AS B
WHERE A.salary BETWEEN B.lowest_sal AND B.highest_sal
AND B.grade_level='A';
三、SQL 92自连接
#案例：查询员工名和上级名称
SELECT A.last_name AS 员工名,B.last_name AS 上级
FROM employees AS A ,employees AS B
WHERE A.manager_id=B.employee_id;
在案例条件下显示员工编号
SELECT A.employee_id AS 员工ID, A.last_name AS 员工名,
B.employee_id AS 领导ID,B.last_name AS 上级
FROM employees AS A ,employees AS B
WHERE A.manager_id=B.employee_id;
#题目练习
1.显示和所有员工的姓名，部门号和部门名称
SELECT A.last_name,A.department_id,B.department_name
FROM employees AS A,departments AS B
WHERE A.department_id=B.department_id;
2.查询90号部门员工的job_id和90号部门的location_id
SELECT A.job_id,B.location_id
FROM employees AS A,departments AS B
WHERE A.department_id=B.department_id AND A.department_id=90;
3.选择有奖金的员工的last_name,department_name,location_id,city
SELECT A.last_name,B.department_name,B.location_id,C.city
FROM employees AS A,departments AS B,locations AS C
WHERE A.department_id=B.department_id AND B.location_id=C.location_id
AND A.commission_pct IS NOT NULL;
4.选择city在Toronto工作的员工
last_name,job_id,department_id,department_name
SELECT last_name,job_id,A.department_id,B.department_name
FROM employees AS A,departments AS B,locations AS C
WHERE A.department_id=B.department_id AND B.location_id=C.location_id
AND C.city='Toronto';
5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_title, MIN(salary)
FROM employees AS A, departments AS B,jobs AS C
WHERE A.department_id=B.department_id AND A.job_id=C.job_id
GROUP BY department_name,job_title;
6.查询每个国家下的部门个数大于2的国家编号
SELECT COUNT(*),country_id
FROM departments AS A, locations AS B
WHERE A.location_id=B.location_id
GROUP BY country_id HAVING COUNT(*)>2;
7.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp manager Mgr
kochhar 101 king 100
SELECT A.last_name AS 'Employees Name',
A.employee_id AS 'Employees Id',
B.last_name AS 'Manager Name',
B.employee_id AS 'Manager Id'
FROM employees AS A,employees AS B
WHERE B.manager_id=A.employee_id AND A.last_name='kochhar';
四、SQL 99 语法
语法:
SELECT 查询列表
FROM 表 1别名【连接类型】
JOIN 表 2别名
ON 连接条件
【where 筛选条件】
【group BY 分组】
【having 筛选条件】
【order BY 排序列表】
分类：
内连接（※）：inner
外连接
左外（※）:LEFT 【outer】
右外（※）:RIGHT 【outer】
全外：full 【outer】
交叉连接：cross
内连接：select FROM 表1 AS 别名1 INNER JOIN 表2 AS 别名2 ON 连接条件
分类：等值、非等值、自连接
特点：
1.添加排序、分组、筛选
2.inner可以省略
3.筛选条件放在where后面，连接条件放在on后面,提高分离性,便于阅读
4.inner JOIN 连接和sql92语法中连接效果是一样的，都是查询多表的交集
1.等值连接
#案例1：查询员工名、部门名
SELECT last_name,department_name
FROM employees AS A INNER JOIN departments AS B
ON A.department_id=B.department_id;
#案例2：查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees AS A INNER JOIN jobs AS B
ON A.`job_id`=B.`job_id` AND A.last_name LIKE '%e%';
#案例3：查询每个城市的部门个数>3的城市名和部门个数
SELECT city,COUNT(*) AS 部门个数
FROM departments AS A INNER JOIN locations AS B
ON A.`location_id`=B.`location_id`
GROUP BY city HAVING COUNT(*)>3;
#案例4：查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序
SELECT COUNT(*),B.department_name
FROM employees AS A INNER JOIN departments AS B
ON A.`department_id`=B.`department_id`
GROUP BY B.department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;
#案例5：查询员工名、部门名、工种名,并按部门名降序
SELECT last_name,department_name, job_title
FROM employees AS A
INNER JOIN departments AS B
INNER JOIN jobs AS C
ON A.`department_id`=B.`department_id`
AND A.`job_id`=C.`job_id`
ORDER BY B.`department_name` DESC;
2.非等值连接
#案例1：查询员工工资级别
SELECT salary,grade_level
FROM employees AS A INNER JOIN job_grades AS B
ON A.salary BETWEEN B.lowest_sal AND B.highest_sal;
#案例2：查询工资级别的个数>20的个数,并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees AS A INNER JOIN job_grades AS B
ON A.salary BETWEEN B.lowest_sal AND B.highest_sal
GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level DESC;
五、sql99自连接
#案例1：查询姓名中包含字符K的员工的名字、上级的名字
SELECT A.last_name,A.employee_id,B.last_name,B.employee_id
FROM employees AS A INNER JOIN employees AS B
ON A.manager_id=B.employee_id
WHERE A.last_name LIKE '%k%';
六、外连接
特点：
1.外连接查询结果为主表中所有记录
如果从表中和它匹配，则显示匹配的值
如果从表没有和它匹配的，则显示Null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2.左外连接,LEFT join左边的是主表
右外连接,RIGHT join右边的是主表
3.左外和右外交换两个表的顺序,可以实现同样的效果
#案例1：查询女神没有男朋友的名字【左外连接】
SELECT b.name,bo.*
FROM beauty b LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;
右外连接
SELECT b.name,bo.*
FROM boys bo RIGHT JOIN beauty b
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;
#案例2：查询哪个部门没有员工
#左外
SELECT d.department_name,e.employee_id
FROM departments d LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id` WHERE employee_id IS NULL;
七、交叉连接(即笛卡尔乘积，主表与从表全部显示出来n*m)
SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;
SQL 92和 SQL 99
注意：【MYSQL不支持全外连接，正式使用的时候也用不上】
功能： SQL 99支持的较多
可读性：SQL 99实现连接条件和筛选条件的分离，可读性较高
题目练习：
1、查询编号>3的女神的男朋友信息,如果有则列出详情，如果没有,用NULL填充
SELECT A.id,A.name ,B.*
FROM beauty AS A LEFT JOIN boys AS B
ON A.boyfriend_id=B.id WHERE A.id>3;
2、查询哪个城市没有部门
SELECT A.city FROM locations AS A LEFT JOIN departments AS B
ON A.location_id=B.location_id AND B.location_id=NULL;
3、查询部门名为SAL或IT的员工
SELECT B.*,A.department_name
FROM departments AS A LEFT JOIN employees AS B
ON A.department_id=B.department_id
WHERE A.department_name IN('SAL','IT'); #进阶七：子查询
含义：出现在其他语句的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
注意：子查询必须有括号括起来
分类：
按子查询出现的位置:
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：【重点】
支持标量子查询（单行） √
支持列子查询 （多行） √
支持行子查询（用的比较少）
exists后面(相关子查询)：
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
按结果集的行列数不同：
一、Where或having后面
1、标量子查询（单行子查询）
2、列子查询 （多行子查询）
3、行子查询 （多行多列）
特点：
[1]子查询放在小括号内
[2]子查询一般放在条件的右侧
[3]标量子查询,一般搭配着单行操作符使用
>、<、>=、<=、=、<>
[4]子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
列子查询，一般搭配多行操作符使用
IN、ANY/SOME、ALL
1.标量子查询
#案例1：谁的工资比Abel高
[1]先查询Abel的工资
SELECT salary FROM employees WHERE last_name='Abel'
[2]查询员工信息，满足salary>[1]结果
SELECT * FROM employees WHERE
salary>(SELECT salary FROM employees WHERE last_name='Abel');
#案例2：查询job_id与141号员工相同，并且salary比143号员工salary多的员工的姓名、job_id、工资
[1]查询141员工的job_id
SELECT job_id FROM employees WHERE employee_id=141;
[2]查询143号员工号的salary
SELECT salary FROM employees WHERE employee_id=143;
[3]查询员工的员工 姓名、job_id、工资,要求job_id=[1]结果并且salary>[2]结果
SELECT last_name,job_id,salary,employee_id 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
[1]查询公司的最低工资
SELECT MIN(salary) FROM employees;
[2]查询last_name,job_id和salary,要求salary=[1]结果
SELECT last_name,job_id,salary FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);
#案例4：查询求某些部门的最低工资大于50号部门的最低工资，并查询这些部门的最低工资和department_id
[1]查询50号部门的最低工资
SELECT MIN(salary) FROM employees WHERE department_id=50;
[2]SELECT MIN(salary),department_id
FROM employees GROUP BY department_id;
[3]筛选[2]结果,满足min(salary)>[1]结果
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=50);
2.列子查询(多行子查询)
案例1：查询locatoin_id是1400或1700的部门中的所有员工姓名
[1]查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700);
[2]查询员工姓名,要求部门号[1]结果列表中的一个
SELECT last_name FROM employees WHERE department_id
IN(SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));
案例2：查询其他工种比job_id为'IT_PROG'工种中任意员工的工资还低的：工号、姓名、job_id以及salary
相当于：查询其他工种比job_id为'IT_PROG'工种中员工最高工资还低的：工号、姓名、job_id以及salary
[1]查询job_id为'IT_PROG'部门的最高工资
SELECT MAX(salary) FROM employees WHERE job_id='IT_PROG';
[2]SELECT job_id,last_name,salary FROM employees
WHERE salary<(SELECT MAX(salary) FROM employees WHERE job_id='IT_PROG')
AND job_id<>'IT_PROG';
3、行子查询(结果集一行多列或多行多列)【用得较少，了解即可】
案例:查询员工编号最小并且工资最高的员工信息
SELECT * FROM employees
WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)FROM employees);
[1]查询最小的员工编号
SELECT MIN(employee_id) FROM employees;
[2]查询最高工资
SELECT MAX(salary) FROM employees;
[3]员工信息
SELECT * FROM employees
WHERE employee_id=(SELECT MIN(employee_id) FROM employees)
AND salary=(SELECT MAX(salary) FROM employees);
二、select 后面，仅仅支持标量子查询
案例1：查询每个部门的员工个数
SELECT d.*,(SELECT COUNT(*) FROM employees AS e WHERE e.department_id=d.department_id) AS 'num'
FROM departments AS d;
案例2：查询员工号=102的部门名
SELECT (SELECT d.department_name FROM
departments AS d INNER JOIN employees AS e
ON d.department_id=e.department_id WHERE e.employee_id=102) 部门名;
三、from后面,将子查询结果充当一张表，要求必须起别名
案例1：查询每个部门的平均工资的工资等级
[1]查询每个部门的平均工资
SELECT AVG(salary), department_id FROM employees GROUP BY department_id;
[2]连接[1]的结果集和job_grades表,筛选条件平均工资between lowest_sal AND highest_sal
SELECT DISTINCT ag_dep.*,g.grade_level
FROM (SELECT AVG(salary) ag, department_id FROM employees GROUP BY department_id) ag_dep
JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、exists后面（相关子查询）【可用其他语句替换】
exists（完整的查询语句）结果：1或0
案例1：查询员工名和部门名
SELECT department_name FROM departments d
WHERE EXISTS(SELECT * FROM employees e WHERE d.department_id=e.department_id);
案例2：没有女朋友的男神信息
SELECT b.* FROM boys b WHERE
NOT EXISTS(SELECT boyfriend_id FROM beauty g WHERE g.boyfriend_id=b.id);
题目练习：
1、查询和员工姓名为zlotkey相同部门的员工姓名和工资
[1]查询部门名为Zlotkey的部门id
SELECT department_id FROM employees WHERE last_name='Zlotkey';
[2]查询部门号=[1]结果的姓名和工资
SELECT salary,last_name FROM employees
WHERE department_id=(SELECT department_id FROM employees WHERE last_name='zlotkey');
2、查询工资比公司平均工资高的员工的员工号，姓名和工资
[1]查询公司平均工资
SELECT AVG(salary) FROM employees;
[2]查询工资>[1]结果的员工号，姓名和工资
SELECT last_name,employee_id,salary FROM employees
WHERE salary>(SELECT AVG(salary) FROM employees);
3、查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
[1]查询各部门的平均工资
SELECT AVG(salary),department_id FROM employees GROUP BY department_id;
[2]连接[1]结果集饿employees表,进行筛选
SELECT e.employee_id,e.last_name,e.salary,e.department_id FROM employees e
JOIN (SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) ag_dep
ON e.department_id=ag_dep.department_id WHERE e.salary>ag_dep.ag;
4、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
[1]查询姓名中包含字母u的员工部门id
SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%';
[2]查询部门号=[1]中的任意一个的员工号和姓名
SELECT last_name,employee_id,department_id FROM employees WHERE department_id IN(SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%');
5、查询在部门的location_id为1700的部门工作员工的工号
[1]查询部门的location_id为1700的部门department_id
SELECT DISTINCT department_id FROM departments WHERE location_id=1700;
[2]查询部门号=[1]结果中任意一个的员工号
SELECT last_name,employee_id FROM employees WHERE department_id
IN(SELECT DISTINCT department_id FROM departments WHERE location_id=1700);
6、查询管理者是K_ing的员工姓名和工资
[1]查询管理者是K_ing的employee_id
SELECT employee_id FROM employees WHERE last_name='K_ing';
[2]查询哪个员工的manager_id=[1]
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显示为一列,列名为姓.名
[1]查询最高的工资
SELECT MAX(salary) FROM employees;
[2]salary=[1]结果的姓名
SELECT CONCAT(first_name,last_name) '姓.名' FROM employees
WHERE salary=(SELECT MAX(salary) FROM employees);
#进阶八：分页查询
应用场景：当要显示的数据,一页显示不全，需要分页提交sql请求
语法：
SELECT 查询列表 [7]
FROM 表 [1]
[JOIN TYPE JOIN 表 2] [2]
ON 连接条件 [3]
WHERE [4]
GROUP BY 分组字段 [5]
HAVING 分组后的筛选 [6]
ORDER BY 排序的字段 [8]
LIMIT OFFSET,size; [9]
OFFSET 要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数
特点：limit语句放在查询语句的最后
公式：要显示的页数page,每页的条目数size
SELECT 查询列表 FROM 表 LIMIT (page-1)*size,size;
案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,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 0,10;
已知表 stinfo
id 学号
NAME 姓名
email 邮箱 john@126.com
grade_id 年级编号
sex 性别
age 年龄
已知表 grade
id 年级编号
gradename 年级名称
一、查询所有学员的邮箱的用户名(注：邮箱中@前面的字符)
SELECT SUBSTR(email,1,INSTR(email,'@')-1) 用户名 FROM stinfo;
二、查询男生和女生个数
SELECT COUNT(*) FROM stinfo GROUP BY sex;
三、查询年龄>18岁的所有学生的姓名和年级名称
SELECT NAME,gradename FROM stinfo s INNER JOIN grade g
ON s.grade_id=g.id WHERE age>18;
四、查询哪个年级的学生最小年龄>20岁
[1]查询年级的最小年龄
SELECT MIN(age),grade_id FROM stinfo GROUP BY grade_id;
[2]在1的结果上筛选
SELECT MIN(age),grade_id FROM stinfo GROUP BY grade_id
HAVING MIN(age)>20;
题目练习：
1、查询工资最低的员工信息:last_name,salary
[1]查询最低的工资
SELECT MIN(salary) FROM employees;
[2]查询last_name,salary,要求salary=[1]
SELECT last_name,salary FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);
2、查询平均工资最低的部门信息
[1]查询平均工资最低的部门department_id
SELECT department_id FROM employees GROUP BY department_id
ORDER BY AVG(salary) ASC LIMIT 0,1;
[2]查询部门信息department_id=[1]
SELECT * FROM employees WHERE department_id=
(SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) ASC LIMIT 0,1);
3、查询平均工资最低的部门信息和该部门的平均工资
[1]查询平均工资最低的部门department_id
SELECT department_id FROM employees GROUP BY department_id
ORDER BY AVG(salary) ASC LIMIT 0,1;
[2]查询部门信息
SELECT d.*,ag FROM departments d JOIN
(SELECT AVG(salary) ag, department_id
FROM employees GROUP BY department_id
ORDER BY AVG(salary) ASC LIMIT 0,1) ag_dep
ON d.department_id=ag_dep.department_id;
4、查询平均工资最高的job信息
[1]查询每个job的平均工资的
SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 0,1;
[2]查询job_id=[1]的信息
SELECT * FROM employees WHERE job_id=
(SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 0,1);
5、查询出公司中所有manager的详细信息
[1]查询出公司的所有manager
SELECT DISTINCT manager_id FROM employees;
[2]查询详细信息manager_id=[1]结果
SELECT * FROM employees WHERE manager_id
IN(SELECT DISTINCT manager_id FROM employees);
6、查询各部门中最高工资中里面最低的那个部门的最低工资是多少
[1]查询各个部门的最高工资里面的最低工资的部门id
SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) ASC LIMIT 0,1 ;
[2]查询部门的department_id=[1]的最低工资
SELECT MIN(salary) FROM employees WHERE department_id=
(SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) ASC LIMIT 0,1 );
7、查询每个专业的学生人数
SELECT majorid,COUNT(*) FROM student GROUP BY majorid;
8、查询参加考试的学生中,每个学生的平均分、最高分
SELECT AVG(score),MAX(score),studentno FROM result GROUP BY studentno;
9、查询姓张的每个学生的最低分大于60的学号、姓名
SELECT s.studentno,studentname FROM student s JOIN result r
ON s.studentno=r.studentno WHERE s.studentname LIKE '张%'
GROUP BY s.studentnohaving MIN(score)>60;
10、查询生日在'1988-1-1'后的学生姓名、专业名称
SELECT s.studentname,m.majorname FROM student s JOIN major m
ON s.majorid=m.majorid WHERE DATEDIFF(borndate,'1988-1-1')>0;
11、查询每个专业的男生人数和女生人数分别是多少
方式一、
SELECT COUNT(*),majorid,sex FROM student
GROUP BY majorid,sex;
方式二、
SELECT majorid,
(SELECT COUNT(*) FROM student WHERE sex='男' AND majorid=s.majorid) 男,
(SELECT COUNT(*) FROM student WHERE sex='女' AND majorid=s.majorid) 女
FROM student s GROUP BY majorid;
12、查询专业和张翠山一样的学生的最低分
[1]查询张翠山的专业编号
SELECT majorid FROM student WHERE studentname='张翠山';
[2]查询编号=[1]的所有学生编号
SELECT studentno FROM student WHERE majorid
IN(SELECT majorid FROM student WHERE studentname='张翠山');
[3]查询最低分
SELECT MIN(score) FROM result WHERE studentno IN
(SELECT studentno FROM student WHERE majorid
IN(SELECT majorid FROM student WHERE studentname='张翠山'));
13、查询大于60分的学生的姓名、密码、专业名
SELECT studentname,loginpwd,majorname
FROM student s JOIN major m ON s.majorid=m.majorid
JOIN result r ON s.studentno=r.studentno WHERE r.score>60;
14、按邮箱位数分组,查询每组的学生个数
SELECT COUNT(*),LENGTH(email) FROM student GROUP BY LENGTH(email);
15、查询哪个专业没有学生,用左连接实现
SELECT m.majorid,m.majorname,s.studentno
FROM major m LEFT JOIN student s ON m.majorid=s.majorid
WHERE s.studentno IS NULL;
16、查询没有成绩的学生人数
SELECT COUNT(*) FROM student s
LEFT JOIN result r ON r.studentno=s.studentno
WHERE r.id IS NULL;#进阶九：联合查询
#进阶九：联合查询
union联合 合并：将多条查询语句的结果合并成一个结果
UNION具有去重作用
语法：
查询语句1
UNION
查询语句2
UNION
........
应用场景
引入案例：查询部门编号>90或邮箱包含a的员工信息
【普通查询】
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
【联合式查询】
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90
UNION
SELECT * FROM employees WHERE department_id>90;
案例：查询中国用中男性的信息以及外国用户中男性用户的信息
SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';
应用场景：
1、要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重,如果使用union ALL 可以包含重复项
DML语言
DML（Data manipulation language 数据操纵语言）
数据操作语言：
插入：insert
修改：update
删除：delete
一、插入语句
方式一、经典的插入
语法：
INSERT INTO 表名(列名,......) VALUES (值1,.....);
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,boyfriend_id)
VALUES (13,'唐艺昕','女','1990-4-23','1898888888',2);
方式二、
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
VALUES (14,'金星','女','1990-4-23','1398888888',9);
3.列的顺序可以调换,但值与属性要一一对应
INSERT INTO beauty (NAME,sex,id,phone)
VALUES ('蒋欣','女',15,'110');
4.列数和值的个数必须一致
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');
5.可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'199',NULL,NULL);
方式二、
语法：INSERT INTO 表名 SET 列名=值,列名=值.......
案例：
INSERT INTO beauty SET id=19,NAME='刘涛',phone='999';
两种方式PK
1.方式一支持插入多行
INSERT INTO beauty
VALUES(23,'唐艺昕','女','1990-4-23','1898888888',NULL,2),
(24,'唐艺昕','女','1990-4-23','1898888888',NULL,2),
(25,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
2.方式一支持子查询,方式二不支持
INSERT INTO beauty(id,NAME,phone) SELECT 26,'宋茜','11809866';
二、修改语句
1.修改单表的记录【重点】
语法：
UPDATE 表名 SET 列=新值,列=新值....... WHERE 筛选条件;
2.修改多表的记录【补充】
语法：
sql92语法：
UPDATE 表1,别名,表2,别名
SET 列=值,....... WHERE 连接条件 AND 筛选条件
sql99语法：
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
on连接条件
SET 列=值,.......
WHERE 筛选条件;
[1]修改单表的记录
案例1：修改beauty表中姓唐的女神的电话为13899888899
UPDATE beauty SET phone='13899888899' WHERE NAME LIKE '唐%';
案例2:修改boys表中id号为2号的名称为张飞,魅力值为10
UPDATE boys SET boyName='张飞',usercp=10 WHERE id=2;
[2]修改多表的记录
案例1：修改张无忌的女朋友的手机号为114
UPDATE boys bo
JOIN beauty b ON bo.id=b.boyfriend_id
SET b.phone=114 WHERE bo.boyName='张无忌';
案例2：修改没有男朋友的女神的男朋友的编号都为2号
UPDATE beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
SET boyfriend_id=2
WHERE bo.id IS NULL;
三、删除语句
方式一、delete
1.单表删除【重点】
DELETE FROM 表名 WHERE 筛选条件
2.多表删除【补充】
sql92语法
DELETE 表1的别名,表2的别名
FROM 表1 别名,表2 别名
WHERE 连接条件
AND 筛选条件；
sql99语法
DELETE 表1的别名,表2的别名
FROM 表1 别名 INNER|LEFT|RIGHT|join表2 别名 on连接条件
WHERE 筛选条件;
方式二、truncate
语法：truncate table表名;
方式一、delete
1.单表的删除
案例1：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
2.多表删除
案例：删除张无忌的女朋友的信息
DELETE b FROM beauty b JOIN boys bo ON b.boyfriend_id=bo.id
WHERE bo.boyName='张无忌';
案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b JOIN boys bo ON bo.`id`=b.`boyfriend_id`
WHERE bo.boyname='黄晓明';
方式二：truncate语句
案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys;
DELETE PK truncate【面试题】
1.delete 可以加where条件,truncate不能加
2.truncate删除,效率比delete效率高一点
3.假如要删除的表中有自增长列,如果用delete删除后再插入数据,
自增长列的值从断点开始,而truncate删除后,再插入数据,自增长的值从1开始
4.truncate删除没有返回值,而delete删除有返回值
5.truncate删除不能回滚,而delete删除可以回滚
题目练习：
1、运行以下脚本创建表my_employees
CREATE TABLE my_empoyees(
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_employess的结构
DESC my_employees;
3、向my_employees表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
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
方式一、
INSERT INTO my_empoyees
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);
方式二、
INSERT INTO my_empoyees
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表中插入数据
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
INSERT INTO users VALUES
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);
5、将3号员工的last_name修改为'drelxer'
UPDATE my_empoyees SET Last_name='drelxer' WHERE id=3;
6、将所有工资少于900的员工的工资修改为1000
UPDATE my_empoyees SET Salary=1000 WHERE Salary<900;
7、将userid为Bbiri的user表和my_employees表的记录全部删除
DELETE e,u FROM my_employees e
JOIN users u ON e.Userid=u.userid
WHERE u.userid='Bbiri';
8、删除所有数据
DELETE FROM my_employees;
DELETE FROM users;
9、检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;
10、清空表my_employees
TRUNCATE TABLE my_employees;DDL语言
DDL(DATA definition LANGUAGE 数据定义语言)
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
1.库的创建
语法
CREATE DATABASE [IF NOT EXISTS]库名;
案例:创建库Books
CREATE DATABASE IF NOT EXISTS Books;
2.库的修改[一般不修改]
更改库的字符集
ALTER DATABASE books CHARACTER SET GBK;
3.库的删除
DROP DATABASE IF EXISTS Books;
二、表的管理
创建、修改、删除
创建: CREATE
修改: ALTER
删除: DROP
1.表的创建[重点]
语法：
CREATE TABLE 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
........................
);
案例:创建Book表
CREATE TABLE IF NOT EXISTS book(
id INT,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT,#作者
publishDate DATETIME#出版日期
);
案例:创建表author
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);
2.表的修改
ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名 【列类型 约束】;
[1]、修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
[2]、修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pudDate TIMESTAMP;
[3]、添加列
ALTER TABLE author ADD COLUMN annual DOUBLE;
[4]、删除列
ALTER TABLE author DROP COLUMN annual;
[5]、修改表名
ALTER TABLE book_author RENAME TO author ;
3.表的删除
DROP TABLE IF EXISTS book_author;
通用写法:
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
4.表的复制
INSERT INTO author VALUES
(1,"村上春树",'日本'),
(2,"莫言",'中国'),
(3,"冯唐",'中国'),
(4,"金庸",'中国');
[1]仅仅复制表的结构
CREATE TABLE copy LIKE author;
[2]复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
[3]只复制部分数据[根据条件]
CREATE TABLE copy3
SELECT id,au_name
FROM author WHERE nation='中国';
[4]仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name FROM author WHERE 0;
题目练习
1、创建表dept1
NAME NULL? TYPE
id INT(7)
NAME VARCHAR(25)
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
NAME NULL? TYPE
id INT(7)
first_name VARCHAR(25)
last_name VARCHAR(25)
dept_id INT(7)
CREATE TABLE emp5(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);
4、将列Last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);
5、根据表employees创建employees2
CREATE TABLE employees2 LIKE myemployees.`employees`;
6、删除表emp5
DROP TABLE IF EXISTS emp5;
7、将表employees2重命名为emp5;
ALTER TABLE employees2 RENAME TO emp5;
8、在表dept和emp5中添加新列test_column,并检查所作的操作
ALTER TABLE emp5 ADD test_column INT;
9、直接删除表emp5中的列 dept_id
ALTER TABLE emp5 DROP dept_id;
常见的数据类型【了解即可】
数值型：
整型
小数：定点数、浮点数
字符型：
较短文本：char、varchar
较长的文本:text、blob(较长的二进制数据)
日期型：
一、整型
分类：
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 5
特点：
[1]如果不设置无符号还是有符号,默认是有符号,如果想设置无符号
需要添加unsigned关键字
[2]如果插入数值超出了整形的范围
[3]如果不设置长度,会有默认的长度
长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用
1、如何设置无符号和有符号
CREATE TABLE tab_int(
t1 INT
t2 INT UNSIGNED
);
DESC tab_int;
INSERT INTO tab_int VALUES(-21311251251);
二、小数
1.浮点型
FLOAT(M,D)
DOUBLE(M,D)
2.定点型
DEC(M,D)
特点:
[1]
M:代表整数部位+小数部位
D:小数部位
如果超过范围,则插入临界值
[2]M,D都可以省略
如果是decimal,则M默认为10,D默认为0
如果是float和double,则会根据插入数值的精度来决定精度
[3]定点型的精度较高,如果要求插入数值的精度较高如货币运算则考虑使用
原则:所选择的类型越简单越好,能保存数值的类型越小越好【节省空间】
CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DECIMAL(5,2)
);
INSERT INTO tab_float
VALUES(123.45,123.45,123.45);
三、字符型
较短文本:
CHAR
VARCHAR
较长文本:
TEXT
BLOB(较大的二进制)
其他:
binary和varbinary用于保存较短的二进制
enum用于保存枚举型
set用于保存集合
特点:
CHAR CHAR(M) M的意思 特点 空间的耗费 效率
最大的字符数,可以省略,默认为1 固定长度的字符 比较耗费 高
VARCHAR VARCHAR(M) 最大的字符数,不可以省略 可变长度的字符 比较节省空间 低
四、日期型
分类
date只保存日期
time只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间
特点:
字节 范围 时区等影响
DATETIME 8 1000~9999 不受
TIMESTAMP 4 1970-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';#常见约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
[1]、Not NULL:非空,用于保证该字段的值不为空
比如姓名、学号等
[2]、default:默认、用于保证该字段有默认值
比如性别
[3]、primary KEY:主键,用于保证该字段的值具有唯一性,并且非空
比如学号、员工编号等
[4]、unique:唯一,用于保证该字段的值具有唯一性,可以为空
比如座位号
[5]、check:检查约束【Mysql中不支持】
比如年龄、性别
[6]、foreign KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束,用于引用主表中某列的值
比如学生表的专业编号,员工表的部门编号,员工表的工种编号
主键和唯一的大对比
保证唯一性 是否允许为空 一个表可以有多少个 是否允许组合【不推荐使用】
主键 √ ❌ 至多一个 √
唯一 √ √ 可以有多个 √
外键:
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的对应列的类型要求一致或兼容
3.主表的关联列必须是一个key(一般是主键或唯一)
4.插入数据时,先插入主表,再插入从表
5.删除数据时,先删除从表,再删除主表
添加约束的时机:
1.创建表时
2.修改表时
seat INT UNIQUE,#唯一
一、创建表时添加约束
约束的分类:
1.列级约束:六大约束语法上都支持,但外键约束没有效果
语法:直接在字段名和类型后面追加约束类型即可
只支持:默认、非空、主键、唯一
2.表级约束:出了非空、默认、其他的都支持
语法:
[CONSTRAINT 约束名] 约束类型(字段名)
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
);
CREATE DATABASE student;
添加列级约束
`stuName`
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
用来查看表中所有索引
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),#主键,主键名就算改名都是展示的时候都是primary
CONSTRAINT uq UNIQUE(seat), #唯一键
CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键约束
);
#通用写法
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
PRIMARY KEY(id,stuname),#组合唯一键，就是当两个字段出现相同就不能插入
UNIQUE(seat), #唯一键
FOREIGN KEY(majorid) REFERENCES major(id)#外键约束
);
二、修改表时添加约束
添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;
添加表级约束
ALTER TABLE 表名 ADD 约束类型(字段名);
1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3.添加主键约束
[1]列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
[2]表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
4.添加唯一键
[1]列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
[2]表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
5.添加外键约束
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
三、修改表时
1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majarid;
四、级联删除、级联置空【不建议使用】
[1]级联删除
alter table stuinfo add foreign key(majorid) references major(id) on delete cascade;
[2]级联置空
alter table stuinfo add foreign key(majorid) references major(id) on delete set null;
题目练习
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、向表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) REFERENCES dept2(id);
位置 支持的约束类型 是否可以起约束名
列级约束: 列的后面 语法都支持,但外键没有效果 不可以
表级约束: 所有列的下面 默认和非空不支持,其他支持 可以(主键没有效果)
#标识列
又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
一、创建表时设置标识列
特点:
1.标识列必须和主键搭配吗?不一定,但要求是一个key
2.一个表中可以有几个标识列?最多一个
3.标识列的类型只能是数值型
4.标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过手动插入值,设置起始值
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO tab_identity VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;
SHOW VARIABLES LIKE 'auto_increment%';
二、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
#事务控制语言【Transacation Control LANGUAGE】
事务:
一个或者一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行
案例:转账
张三丰 1000
郭襄 1000
事务的ACID(属性)
1.原子性
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生
2.一致性
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3.隔离性
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作
及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰
4.持久性
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他
的操作和数据库不应该对其任何影响
事务的创建
隐式事务:事务没有明显的开启结束的标记
比如insert、update、delete语句
DELETE FROM 表 WHERE id=1;
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用
开启事务的语句:
UPDATE 表 SET 张三丰的余额=500 WHERE NAME='张三丰'
UPDATE 表 SET 郭襄的余额=1500 WHERE NAME='郭襄'
结束事务的语句:
步骤1:开启事务
SET autocommit=0;
START TRANSACTION;可选的
步骤2:编写事务中的sql语句(SELECT INSERT UPDATE DELETE)
语句1;
语句2;
...
步骤3:结束事务
COMMIT;提交事务
ROLLBACK;回滚事务
SAVEPOINT 节点名;设置保存点
演示事务的受用步骤
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);
#开启事务
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
READ UNCOMMITTED(读取未提交数据)
允许事务读取未被其他事务提交的变更、脏读、不可重复度读和幻读的问题都会出现
READ COMMITED(读取已提交数据)
只允许事务读取已经被其他事务的变更,可以避免脏读,但不可重复读和幻读问题仍然可能出现
REPEATABLE READ(可重复读)
确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新
可以避免脏读和不可重复读,但幻读的问题仍然存在
SERIALIZABLE(串行化)确保事务可以从一个表中读取相同的行.
这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作.所有并发问题都可以避免.但性能十分低下
事务隔离级别
脏读 不可重复读 幻读
read uncommitted √ √ √
read committed × √ √
repeatable read × × √
serializable × × ×
MySQL中默认 第三个隔离级别repeatable read
Oracle中默认 第二个隔离级别read committed
命令控制符进入mysql登录个人用户查看数据库隔离级别
SELECT @@transaction_isolation;
设置当前MySQL连接的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;
设置数据库系统的全局隔离级别
SET global transaction isolation level 隔离级别;
delete和truncate在事务中使用时的区别
演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
事务执行中delete可以回滚,truncate不能回滚
演示savepoint的使用(只搭配rollback)
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点#视图
含义:虚拟表,和普通表一样使用
案例:查询姓张的学生名和专业名
SELECT stuname,majorname FROM stuinfo s
INNER JOIN major m ON s.majorid=m.id
WHERE s.stuname LIKE '张%';
CREATE VIEW v1
AS
SELECT stuname,majorname FROM stuinfo s
INNER JOIN major m ON s.majorid=m.id
SELECT * FROM v1 WHERE stuname LIKE '张%';;
一、创建视图
语法:
CREATE VIEW 视图名
AS
查询语句;
题目练习
1.查询邮箱中包含a字符的员工名、部门名和工种信息
[1]创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN jobs j ON j.job_id=e.job_id;
[2]使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
2.查询各部门的平均工资级别
[1]创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ,department_id
FROM employees GROUP BY department_id;
[2]使用
SELECT myv2.`avg(salary)`,g.grade_level FROM myv2
JOIN job_grades g ON myv2.`avg(salary)` BETWEEN g.lowest_sal AND g.highest_sal;
3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY myv2.`avg(salary)` ASC LIMIT 1;
4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY myv2.`avg(salary)` LIMIT 1;
SELECT d.*,m.`avg(salary)` FROM myv3 m JOIN departments d
ON m.`department_id`=d.`department_id`;
二、视图的修改
方式一:
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary), job_id FROM employees GROUP BY job_id;
方式二:
ALTER VIEW myv3
AS
查询语句
ALTER VIEW myv3
AS
SELECT * FROM employees;
三、删除视图
语法:DROP VIEW 视图名,视图名......;
DROP VIEW myv1,myv2,myv3;
四、查看视图
DESC myv3;
SHOW CREATE VIEW myv3;
题目练习
1.创建视图emp_v1,要求电话号码以'001'开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';
2.创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary),department_id
FROM employees GROUP BY department_id
HAVING MAX(salary)>12000;
SELECT d.*,emp_v2.`max(salary)`
FROM departments d JOIN emp_v2 ON emp_v2.`department_id`=d.`department_id`;
五、视图更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
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 ALL
创建语法 是否实际占用物理空间 使用
视图 CREATE VIEW 只是保存了sql逻辑 增删改查,一般不能增删改
表 CREATE TABLE 保存了数据 增删改查
题目练习
1.创建Book表,字段如下；
bid整型,要求主键
bname字符型,要求设置唯一键,并非空
price浮点型,要求有默认值10
btypeId类型编号,要求引用bookType表和id字段
已知bookType表(不用创建),字段如下
id
NAME
CREATE TABLE book(
bid INT PRIMARY KEY,
bname VARCHAR(20) UNIQUE NOT NULL,
price FLOAT DEFAULT 10,
btypeId INT,
FOREIGN KEY(btypeId) REFERENCES bookType(id)
);
2.开启事务向表中插入1行数据,并结束
SET autocommit=0;
INSERT INTO book(bid,bname,price,btypeId) VALUES(1,'向前冲',100,1);
COMMIT;
3.创建视图,实现查询价格大于100的书名和类型名
CREATE VIEW myv1
AS
SELECT bname,NAME
FROM book b JOIN booktype t ON b.typeid=t.id;
WHERE price>100;
4.修改视图,实现查询价格在90-120之间的书名和价格
CREATE OR REPLACE VIEW myv1
AS
SELECT bname,price
FROM book WHERE price BETWEEN 90 AND 120;
5.删除刚才建的视图
DROP VIEW myv1;
#变量
系统变量:
全局变量
作用域:服务器每次启动将为所有全局变量赋初始值
会话变量
自定义变量:
用户变量
局部变量
一、系统变量
说明:变量由系统提供,不是用户定义,属于服务器层面
如果是全局级别,则需要加global,如果是会话级别,则需要加session,如果不写,则默认session
使用的语法:
1、查看所有的系统变量
SHOW SESSION|GLOBAL VARIABLES;
2、查看满足条件的部分系统变量
SHOW SESSION|GLOBAL VARIABLES LIKE '%char%';
3、查看指定的某个系统变量的值
SELECT @@global|session.系统变量名;
4、为某个系统变量赋值
方式一、
SET GLOBAL|session系统变量名=值;
方式二、
SET @@global|session系统变量名=值;
查看指定的全局变量的值
SELECT @@global.autocommit;
SELECT @@transaction_isolation;
为某个指定的全局变量赋值
SET @@global.autocommit=0;
二、会话变量
作用域:仅仅针对于当前会话(连接)有效
【用户变量】
作用域:针对当前会话(连接)有效,同于会话变量的作用域
应用在任何地方,也是begin end里面
1.查看所有的会话变量
SHOW SESSION VARIABLES;
2.查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
3.查看指定的某个会话变量
SELECT @@transaction_isolation;
4.为某个会话变量赋值
方式一:
SET @@session.transaction_isolation='read-uncommitted';
方式二:
SET SESSION transaction_isolation='read-committed';
三、自定义变量
说明:变量是用户自定义的,不是由系统给的
使用步骤
声明
赋值
使用(查看、比较、运算等)
1、用户变量
作用域:针对于当前会话(连接)有效,同于会话变量的作用域
[1]声明并初始化
SET @用户变量名=值;
SELECT @用户变量名:=值;
2、赋值(更新用户变量的值)
方式一、通过SET或SELECT
SET @用户变量名=值;
SELECT @用户变量名:=值;
方式二:通过SELECT INTO
SELECT 字段 INTO 变量名
FROM 表;
案例:
声明并初始化
SET @name='john';
SET @name=100;
SET @count=1;
3.使用(查看用户变量名)
赋值
SELECT COUNT(*) INTO @count FROM employees;
查看
SELECT @count;
四、局部变量
作用域:仅仅在定义它的begin end中有效
应用在 BEGIN end中的第一句话
1.声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
2.赋值
方式一:通过SET或SELECT
SET 局部变量名=值;
SELECT @局部变量名:=值;
方式二:通过SELECT INTO
SELECT 字段 INTO 局部变量名
FROM 表;
3.使用
SELECT 局部变量名;
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何地方 必须加@符号;
局部变量 BEGIN end中 只能在begin END,且为第一句话 一般不用加@符号,需要限定类型
案例:声明两个变量并赋初值,求和,并打印
1.用户变量
SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;
2.局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;#存储过程
存储过程
存储过程:类似于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表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,PASSWORD)
VALUES('john1','0000'), ('lily','0000'),
('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1();
SELECT * FROM admin;
2.创建带in模式参数的存储过程
案例1:创建过程实现 根据女神名,查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
CALL myp2('王语嫣');
案例2：创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR (20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) INTO result FROM admin WHERE admin.username=username
AND admin.password=PASSWORD;
SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp3('张飞','8888');
3.创建带out模式的存储过程
案例1:根据女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName
FROM boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
CALL myp4('王语嫣',@bname);
SELECT @bname;
案例2:根据女神名,返回对应的男神名和男神魅力值
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,usercp
FROM boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
CALL myp5('王语嫣',@boyName,@usercp);
SELECT @boyName,@usercp;
4.创建带inout模式参数的存储过程
案例1:传入a和b两个值,最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE myp6(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=10;
SET @n=20;
CALL myp6(@m,@n);
SELECT @m,@n;
5、删除存储过程
语法:DROP PROCEDURE 存储过程名
DROP PROCEDURE myp3;
6、查看存储过程信息
SHOW CREATE PROCEDURE test_pro3;
题目练习
一、创建存储过程实现传入用户名和密码,插入到admin表中
DELIMITER $
CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginpwd VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,admin.password)
VALUES(username,loginpwd);
END $
CALL test_pro1('admin','0000');
SELECT * FROM admin;
二、创建存储过程实现传入女神编号,返回女神名称和女神电话
DELIMITER $
CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
SELECT b.name,b.phone INTO NAME,phone
FROM beauty b
WHERE b.id=id;
END $
CALL test_pro2(1,@n,@p);
SELECT @n,@p;
三、创建存储过程或函数实现传入两个女神生日,返回大小
DELIMITER $
CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(birth1,birth2) INTO result;
END $
CALL test_pro3('1998-1-1',NOW(),@result);
SELECT @result;
四、创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回
DELIMITER $
CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strdate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%Y年%M月%D日') INTO strdate;
END $
CALL test_pro4(NOW(),@str);
SELECT @str;
五、创建存储过程或函数实现传入女神名称,返回:女神and男神格式的字符串
如传入:李嘉诚
返回:李嘉诚 AND 郭老师
DELIMITER $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(beautyName,'and',boyName) INTO str
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id=bo.id
WHERE b.name=beautyName;
END $
CALL test_pro5('柳岩',@str);
SELECT @str;
六、创建存储过程或函数,根据传入的条目数和起始索引,查询beauty
DELIMITER $
CREATE PROCEDURE test_pro6(IN startindex INT,IN size INT)
BEGIN
SELECT * FROM beauty LIMIT startindex,size;
END $
CALL test_pro6(3,2);#函数
#函数
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器连接次数,提高了效率
与存储过程区别:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果
一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
参数列表 包含两部分:
参数名 参数类型
函数体:肯定有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
RETURN 值;
3.函数体中仅有一句话,则可以省略begin END
4.使用delimiter语句设置结束标记
DELIMITER $;
二、调用语法
SELECT 函数名(参数列表)
1.无参有返回
案例:返回公司的员工个数
SET GLOBAL log_bin_trust_function_creators=1;
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义变量
SELECT COUNT(*) INTO c #为变量赋值
FROM employees;
RETURN c;
END $
SELECT myf1();
2.有参有返回
案例1：根据员工名,返回它的工资
DELIMITER $
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;#定义用户变量
SELECT salary INTO @sal FROM employees WHERE last_name=empName;
RETURN @sal;
END $
SELECT myf2('Kochhar');
案例2:根据部门名,返回部门的平均工资
DELIMITER $
CREATE FUNCTION myp3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE;
SELECT AVG(salary) INTO sal
FROM employees e JOIN departments d ON e.department_id=d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
SELECT myp3('IT');
三、查看函数
SHOW CREATE FUNCTION myp3;
四、删除函数
DROP FUNCTION myf3;
案例
一、创建函数,实现传入两个float,返回二者之和
DELIMITER $
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_fun1(1,3);
#流程控制语句
流程控制结构
顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上,重复执行y一段代码
一、分支结构、
1.if函数
功能:实现简单的双分支
语法
IF(表达式1,表达式2,表达式3)
执行顺序:
如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值
应用:任何地方
2.case结构
情况1:类似于Java的switch语句,一般用于实验的等值判断
语法:
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1;
WHEN 要判断的值 THEN 返回的值2;
.......
ELSE 要返回的值n或语句n;
END CASE;
情况2:类似于Java的多重if语句,一般用于实现区间判断
语法:
CASE
WHEN 要判断的条件1 THEN 返回的值1;
WHEN 要判断的条件2 THEN 返回的值2;
.......
ELSE 要返回的值n或语句n;
END CASE;
特点:
[1]
可以作为表达式,嵌套在其他语句中使用,可以放在任何地方,BEGIN END 中火或begin END 的外面
可以作为独立的语句去使用,只能放在begin END 中
[2]
如果when中的值满足或条件成立,则执行对应的then后面的语句,并且结束case
如果都不满足,则执行else中的语句或值
[3]
ELSE 可以省略,如果else省略了,并且所有when条件都不满足,则返回NULL;
案例:
创建存储过过程,根据传入的成绩,来显示等级,比如传入的成绩
90-100显示A,80-90显示B,60-80显示C,否则显示D
DELIMITER $
CREATE PROCEDURE test_case(IN score INT)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A';
WHEN score>=80 THEN SELECT 'B';
WHEN socre>=60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END $
CALL test_case(55);
3.if结构
功能:实现多重分支
语法:条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
....
END IF;
应用在begin end中
案例1:
根据传入的成绩,来显示等级,比如传入的成绩
90-100返回A,80-90返回B,60-80返回C,否则返回D
DELIMITER $
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END $
SELECT test_if(30);
二、循环结构[只能放在begin end中]
分类:
WHILE[常用、重点] LOOP REPEAT
循环控制:
ITERATE 类似于continue,继续,结束本次循环,继续下一次
LEAVE 类似于 break;跳出,结束当前所在的循环
1.while
语法:
[标签]WHILE 循环条件 DO
循环体;
END WHILE [标签];
语法
[标签]LOOP
循环体;
END LOOP[标签];
3.repeat
语法
[标签]REPEAT
循环体;
UNTIL 结束循环的条件;
END REPEAT[标签];
案例:批量插入,根据依次数插入到admin表中多条记录
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertcount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertcount DO
INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('Rose',i ),'666');
SET i=i+1;
END WHILE;
END $
CALL pro_while1(100);
SELECT * FROM admin;
/*
int i=1;
while(i<=insertcount)
插入
i++;
*/
2.添加leave语句,类似break;
案例:批量插入,根据依次数插入到admin表中多条记录,如果次数>20则停止
DELIMITER $
CREATE PROCEDURE test_while2(IN insertcount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertcount DO
IF i>=20 THEN LEAVE a;
END IF;
INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('小花',i),'00000');
SET i=i+1;
END WHILE a;
END $
CALL test_while2(200);
SELECT * FROM admin;
3.添加iterate语句
案例:批量插入,根据次数插入到admin表中多条记录,只插入偶数次
DELIMITER $
CREATE PROCEDURE test_while3(IN insertcount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertcount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('MOKO',i),'00000');
END WHILE a;
END $
CALL test_while3(100);感谢大家观看,基础篇完结,后续高级篇后续也会更新,谢谢大家的阅览
107
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
