数据库的概念
一、数据库的好处
1、可以持久化数据到本地
2、结构化查询
二、数据库的常见概念 ★
1、DB：数据库，存储数据的容器
2、DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
3、SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言
三、数据库存储数据的特点
1、数据存放到表中，然后表再放到库中
2、一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3、表中有一个或多个列，列又称为“字段”，相当于java中“属性”
4、表中的每一行数据，相当于java中“对象”
四、常见的数据库管理系统
mysql、oracle、db2、sqlserver
一、MySQL的背景
前身属于瑞典的一家公司，MySQL AB
08年被sun公司收购
09年sun被oracle收购
二、MySQL的优点
1、开源、免费、成本低
2、性能高、移植性也好
3、体积小，便于安装
三、MySQL的安装
属于c/s架构的软件，一般来讲安装服务端
企业版
社区版
5.5
5.6
5.7
8.0
配置文件介绍：C:\Program Files (x86)\MySQL\MySQL Server 5.5 里面的 my.ini可以看配置文件介绍
mysql的启动和停止有两种方式：一：开始---------计算机右击找管理---------服务和应用程序------打开服务------找mysql（进行调节）
二：通过管理员打开cmd（命令提示符）
MySQL服务的登录和退出
方式1. 进入mysql自带的用户端（直接输入用户密码）
方式2. 登录：mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码 (如果是本机括号内可以省略)
Microsoft Windows [版本 10.0.19041.1083]
(c) Microsoft Corporation。保留所有权利。
C:\Windows\system32>mysql -h localhost -P3306 -u root -p1724
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
C:\Windows\system32>mysql -h localhost -P3306 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
退出：exit或ctrl+C
数据库的命令
1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(
列名 列类型,
列名 列类型，
。。。
);
6.查看表结构
desc 表名;
7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V
MySQL的语法规范
1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进 或换行
4.注释
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */
myemployees库的四张表介绍（18）
DQL语言的学习开始（19）
SQL的语言分类
DQL（Data Query Language）：数据查询语言
select
DML(Data Manipulate Language):数据操作语言
insert 、update、delete
DDL（Data Define Languge）：数据定义语言
create、drop、alter
TCL（Transaction Control Language）：事务控制语言
commit、rollback
进阶一 ：基础查询
#进阶1：基础查询
/*语法：
select 查询列表 from 表名；
类似于：System.out.println(打印的东西);
特点：
1.查询列表可以是：表中字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格。
注意事项
1.首先打开库
2.加入着重号避免被系统误认为是关键字而大写
例：如果字段名为name，那么系统会认为它是关键字，改为大写，这时我们需要加入着重号“·”
SELECT NAME FROM employees; 改为 SELECT `name` FROM employees;
3.程序运行时选中那个程序运行哪一个；
*/
#开始前先打开库
USE myemployees;
#1.查询表中的单个字段
SELECT last_name FROM employees;
#2.查询表中的段个字段
SELECT last_name,salary,job_id FROM employees;
#3.查询表中的所有字段（这里通过双击字段自动添加,F12自动格式化（标准化）选中的代码的格式）
#方法一
SELECT
`employee_id`,
`first_name`,
`last_name`,
`email`,
`phone_number`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees ;
#方法二
SELECT * FROM employees;
#4.查询常量值
SELECT 120;
SELECT 'juoo';
#注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
#5.查询表达式
SELECT 100*98;
#6.查询函数
SELECT VERSION();
#7.起别名
/*
1.便于理解
2.如果要查询的字段有重名的情况，使用别名可以进行区分
*/
#方式一：使用as
SELECT 100*87 AS 结果;
SELECT last_name AS 性,first_name AS 名 FROM employees;
#方式二：使用空格
SELECT salary 薪水 FROM employees;
#特例：查询hiredate，显示结果为out put 因为中间有空格，而且out为关键字，所以有双引号也可以单引号。建议双引号
SELECT hiredate "out put" FROM employees;
#8.去重
# 查询员工表中涉及到的所有部门的编号
SELECT DISTINCT department_id FROM employees;
#+号的作用
/*
java中的+号
1.运算符：两个操作数都为数值型
2.连接符：只要有一个操作书为字符串
mysql语句中的+号
唯一的作用时运算符
select 100+90; 两个操作数都为数值型，做加法运算
select '123'+90; 只要其中一方为字符型，试图将字符型转成数值型
如果转成功就继续做加法运算
select 'john'+90; 如果转化失败就将字符型转化为零；
select null+10； 如果有一方为null，则结果就对为null；
*/
#查询员工姓和名连接为一个字段，并显示为姓名（这里连接需要用到函数）
SELECT CONCAT('a','b','c') AS 姓名;
SELECT
CONCAT(last_name, first_name) AS 姓名
FROM
employees ;
#显示出employees的部分列，各个列之间用逗号隔开，列头显示out_put
#这里用了IFNULL(commission_pct,0))函数，如果数据为null就变为0，避免经过拼接后大部分为null。
SELECT
CONCAT(`employee_id`,',',`first_name`,',',`last_name`,',',IFNULL(commission_pct,0))AS out_put
FROM
employees;
进阶二：条件查询
#进阶二：条件查询
/*
语法：
select
查询列表
from
表名
where
筛选条件;
分类：
一、按条件表达式筛选
条件运算符：> < = != <> >= <=
二、按照逻辑表达式筛选
逻辑运算符：
&& || ！ （不建议使用）
and or not
and和&&：两个条件都为true，结果为true，否则为flase。
or和||：只要有一个为true，结果为true，否则为flase。
not和！：如果连接的条件本身为flase，结果为true，否则为flase。
三、模糊查询
like
between and
in
in null
*/
#一、按条件表达式筛选
#1.查询工资大于12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000;
#2.查询部门编号不等于90的员工名和部门编号
SELECT
first_name,
department_id
FROM
employees
WHERE
department_id>90;
#二、按逻辑表达式筛选
#工资在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个字符）
_任意单个字符
2.既可以判断字符型也可以判断数据型
between and
in
is null is not nul
*/
#1.like
#查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
#查询员工名字第三个字符为m，第五个字符为n的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__m_n%';
#查询员工名中第二个字符为_的员工名(ESCAPE '&' 意味这将&设置为转义字符)
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_&_%' ESCAPE '&';
#2.between and
/*
1.使用between and 可以提高语句的简洁度
2.包含临界值
3.两个临界值不要调换顺序
*/
#查询员工编号在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
/*
含义：判断某字段的值是否属于in列表中的某一项
特点： 1.使用in提高语言简洁度
2.in列表的值类型必须统一或兼容（兼容就是可以转型，例如：‘123’转成123）
3.in当中不支持%等
*/
#查询员工的工种编号是 IT_PROG、AD_VP、AD_PREA中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id='IT_PROG' OR job_id='AD_vp' OR job_id='AD_PREA';
#-------------------------------------
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN('IT_PROG','AD_VP','AD_PREA');
#4.is null
#查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
#查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
#安全等于 <=>
#查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#查询工资为12000的员工信息
SELECT
*
FROM
employees
WHERE
salary<=>12000;
# is null PK <=>
/*
is null:仅仅可以判断null值，可读性较高，建议使用
<=> :既可以判断null值，又可以判断普通的数值，可读性较低
*/
#查询员工号为176的员工的姓名和部门和年薪
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
WHERE
employee_id=176;
#查看部门departments表的结构
DESC departments;
#查询部门departments表中涉及到了哪些位置编号
SELECT DISTINCT location_id
FROM departments;
/*
五、经典面试题：
问：select * from employees;
和select *from employees where commission_pct like '%%' and last_name like ‘%%’;
结果是否一样？并说明原因
不一样！因为判断的字段有 null值。
进阶三：排序查询
#进阶三：排序查询
/*
语法：
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc | desc】
特点： 1.asc:代表升序 desc：代表降序
如果都不写，默认是升序
2.order by 子句中可以支持：单个字段、多个字段、表达式、函数、别名
3.order by子句一般放在查询语句的最后面，limit子句除外
*/
#查询员工信息，要求工资从高到低排序
USE myemployees;
SELECT * FROM employees ORDER BY salary DESC;
#查询部门编号>=90的员工信息，按入职时间的先后顺序进行排序【添加筛选条件】
SELECT *
FROM
employees
WHERE
department_id>=90
ORDER BY
hiredate ASC;
#按年薪的高低显示员工的信息和年薪【按表达式查询】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#按年薪的高低显示员工的信息和年薪【按别名查询】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
#按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) AS 姓名长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;
#选择工资不在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;
进阶四：常见函数
一、单行函数
#进阶4：常见函数
/*
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）
分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
常见函数：
一、单行函数
字符函数：
length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
concat
substr
instr
trim
upper
lower
lpad
rpad
replace
数学函数：
round
ceil
floor
truncate
mod
日期函数：
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date_format
其他函数：
version
database
user
控制函数
if
case
*/
#一：字符函数
#1.length 获取参数值的字节个数
SELECT LENGTH('junan');
SELECT LENGTH('吴方童wufang');
SHOW VARIABLES LIKE '%char%'; #查看字符集
#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) FROM employees;
#3.upper 变大写 lower 变小写
SELECT UPPER('wufangtong');
SELECT LOWER('WUFANGTONG');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名 FROM employees;
#4.substr、substring
注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS 姓名
FROM employees;
#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('我喜欢你渣我的样子宝儿','渣') out_put;
#6.trim 在字符串的前后去除指定的东西，不指定的话去除空格
SELECT TRIM(' 我好难啊 ') AS out_put;
SELECT TRIM('aa'FROM'aaaaaaaa吴aaaaaaaaaaa方aaaaaaaa') AS out_put;
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('吴方童',10,'*') AS out_put;
SELECT LPAD('吴方童',2,'&') AS out_put;
#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('吴方童',10,'*') AS out_put;
#9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
SELECT REPLACE('超级野兽喜欢吴方童','超级野兽','超级小可爱') AS 真理;
#二、数学函数
#round 四舍五入
SELECT ROUND(-1.55); #对绝对值取整之后四舍五入
#四舍五入保留2位小数
SELECT ROUND(1.567,2);
#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.002);
#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.002);
SELECT FLOOR(-9.99);
#truncate 截断
SELECT TRUNCATE(1.699994,2);
#mod取余
/*
mod(a,b) ： a-a/b*b 如果a是负数那么取余得到的数是负数否则是正数
mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(-10,3);
SELECT -10%3;
#三、日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#curtime 返回当前时间，不包含日期
SELECT CURTIME();
#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) AS 年;
SELECT YEAR('1999-5-21') AS 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) AS 月;
SELECT MONTHNAME(NOW()) AS 月; #英文
#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('2021-5-21','%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年%d月%d日') AS out_put;
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月%d日%Y年')
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 的效果
/*
java中
switch(变量或表达式){
case 常量1：语句1;break;
...
default:语句n;break;
}
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*/
/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_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
/*
java中：
if(条件1){
语句1；
}else if(条件2){
语句2；
}
...
else{
语句n;
}
mysql中：
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;
#查询员工号，姓名，工资，以及工资提高20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary*1.2 AS "new salary"
FROM employees;
#将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name),SUBSTR(last_name,1,1) AS 首字符
FROM employees
ORDER BY
首字符;
#使用case-when，按照下面的条件：
/*
job grade
AD_PRES A
ST_MAN B
IY_PROG C
SA_REP D
*/
SELECT last_name,job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IY_PROG' THEN 'C'
WHEN 'SA_PRE' THEN 'D'
ELSE 'E'
END AS Grade
FROM employees;
二、分组函数
#二、分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段
*/
USE myemployees;
#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;
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最大,MIN(salary) 最小,COUNT(salary) 个数
FROM employees;
#2、参数支持哪些类型
SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate) FROM employees;
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;
#3、是否忽略null
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107
FROM employees;
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;
#4、和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT(salary)),COUNT(salary) FROM employees;
#5、count函数的详细介绍(下面的都是求行数)
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees; #数字也可以是别的
效率：
#MYISAM存储引擎下 ，COUNT(*)的效率高
#INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
#6、和分组函数一同查询的字段有限制
SELECT AVG(salary),last_name FROM employees;
#2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）用到了新函数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees;
SELECT DATEDIFF(NOW(),'2001-10-18');
SELECT ROUND(7211/365,2);
#3.查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id=90;
进阶五：分组查询
#进阶5：分组查询
/*
语法：
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;
特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
针对的表 位置 连接的关键字
分组前筛选 原始表 group by前 where
分组后筛选 group by后的结果集 group by后 having
问题1：分组函数做筛选能不能放在where后面
答：不能
问题2：where——group by——having
一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用
*/
#1.简单的分组
USE myemployees;
#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#案例2：查询每个位置的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#2、可以实现分组前的筛选
#案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2：查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary) ,manager_id a
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY a;
#3、分组后筛选
#案例：查询哪个部门的员工个数>5
#①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#② 筛选刚才①结果
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
#案例2：每个工种有奖金的员工的最高工资>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的领导编号和最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#4.添加排序
#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary);
#5.按多个字段分组
#案例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;
#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;
进阶6：连接查询
按年代分类：
sql92：
等值
非等值
自连接
也支持一部分外连接（用于oracle、sqlserver，mysql不支持）
sql99【推荐使用】
内连接
等值
非等值
自连接
外连接
左外
右外
全外（mysql不支持）
交叉连接
三、SQL92语法
1、等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点：
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
2、非等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3、自连接
语法：
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
SQL92语法
#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
*/
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id`=boys.`id`;
#一、sql92标准
#1、等值连接
/*
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
#案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
#3、两个表的顺序是否可以调换
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;
#4、可以加筛选
#案例：查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT city,department_id
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id`
AND city LIKE '_o%';
#5、可以加分组
#案例1：查询每个城市的部门个数
SELECT city,COUNT(*) 个数
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY department_name;
#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT j.`job_title`,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY j.`job_title`
ORDER BY COUNT(*)DESC;
#7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市 而且公司名以s开头
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;
AND city LIKE 's%';
#2、非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;
/*
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);
*/
#3、自连接
#案例：查询 员工名和上级的名称
SELECT e.last_name,m.last_name
FROM employees e, employees m
WHERE e.`manager_id`=m.`employee_id`;
#3. 选择所有有奖金的员工的
#last_name , department_name , location_id , city
SELECT last_name , department_name ,l.location_id , city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND 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 l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city='Toronto';
#6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*)
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING COUNT(*)>2;
#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
SELECT e.last_name,e.employee_id "Emp#",m.last_name,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id=m.employee_id
AND e.last_name='kochhar';
sql99语法
#二、sql99语法
/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类：
内连接（★）：inner
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
全外：full【outer】
交叉连接：cross
*/
#一）内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
分类：
等值
非等值
自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/
#1、等值连接
#案例1.查询员工名、部门名
SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
ON e.`department_id`=d.`department_id`;
#案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM employees e
JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';
#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT COUNT(*),city
FROM locations l
JOIN departments d
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_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
ORDER BY d.`department_name` DESC;
#二）非等值连接
#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM 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%';
#二、外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
*/
#引入：查询男朋友 不在男神表的的女神名
SELECT *FROM beauty;
SELECT *FROM boys;
#左外连接
SELECT b.*,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.id
WHERE bo.`id` IS NULL;
#案例1：查询哪个部门没有员工
#左外
SELECT d.*,employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL;
#右外
SELECT d.*,employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`department_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
/*
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
*/
#一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.id,b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3;
#二、查询哪个城市没有部门
SELECT city,department_id
FROM locations l
LEFT OUTER JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`department_id` IS NULL;
#三、查询部门名为SAL或IT的员工信息
SELECT e.*,d.department_name,d.`department_id`
FROM departments d
LEFT OUTER JOIN employees e
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN('SAL','IT');
进阶7：子查询
#进阶7：子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询 （多行） √
行子查询
exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列，多行多列）
表子查询（结果集一般为多行多列）
*/
#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
列子查询，一般搭配着多行操作符使用
in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/
#1.标量子查询★
#案例1：谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name='Able';
#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Able'
);
#案例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
);
#2.列子查询（多行子查询）★
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700);
#②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’部门任一工资
SELECT salary
FROM employees
WHERE job_id='IT_PROG';
#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT 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<(
SELECT MIN(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<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#3、行子查询（结果集一行多列或多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (salary,employee_id)=(
SELECT MAX(salary),MIN(employee_id)
FROM employees
);
#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees
#②查询最高工资
SELECT MAX(salary)
FROM employees
#③查询员工信息
SELECT *
FROM employees
WHERE salary=(
SELECT MAX(salary)
FROM employees
)
AND employee_id=(
SELECT MIN(employee_id)
FROM employees
);
#二、select后面
/*
仅仅支持标量子查询
*/
#案例：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
) 个数
FROM departments d;
#案例2：查询员工号=102的部门名
SELECT(
SELECT department_name
FROM departments d
INNER JOIN employees e
ON e.department_id=d.department_id
WHERE e.employee_id=102
) 部门名;
#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employyes e
GROUP BY department_id;
SELECT * FROM job_grades;
#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level`
FROM(
SELECT AVG(salary) ag,department_id
FROM employees e
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
#四、exists后面（相关子查询）
/*
语法：
exists(完整的查询语句)
结果：
1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
#案例1：查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
);
#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
#案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.`id` NOT IN(
SELECT b.boyfriend_id
FROM beauty b
);
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`
);
#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;
#5. 查询在部门的location_id为1700的部门工作的员工的员工号
#①查询location_id为1700的部门
SELECT 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
);
进阶八：分页查询
/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
size=10
page
1 0
2 10
3 20
*/
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 1,5;
SELECT * FROM employees LIMIT 5; #如果是一的话可以省略
#案例2：查询第11条——第25条
SELECT * FROM employees LIMIT 11,15;
#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
子查询经典案例
# 1. 查询工资最低的员工信息: last_name, salary
#①查询最低的工资
SELECT MIN(salary)
FROM employees
#②查询last_name,salary，要求salary=①
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
# 2. 查询平均工资最低的部门信息
#方式一：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
#③查询部门信息
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id d
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
# 3. 查询平均工资最低的部门信息和该部门的平均工资
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#③查询部门信息
SELECT d.*,
FROM departments d,ag
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
) ag_dep
ON d.`department_id`=ag_dep.department_id;
# 4. 查询平均工资最高的 job 信息
#①查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;
#②查询job信息
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
# 5. 查询平均工资高于公司平均工资的部门有哪些?
#①查询平均工资
SELECT AVG(salary)
FROM employees
#②查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
);
# 6. 查询出公司中所有 manager 的详细信息.
#①查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees;
#②查询详细信息，满足employee_id=①
SELECT *
FROM employees
WHERE employee_id =ANY(
SELECT DISTINCT manager_id
FROM employees
);
# 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
#①查询各部门的最高工资中最低的部门编号
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;
#②查询①结果的那个部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER 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) ;
进阶九：联合查询
#进阶9：联合查询
/*
union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
...
应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：★
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项
*/
#引入的案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id>90 OR email LIKE '%a%';
SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';
DML语言
#DML语言
/*
数据操作语言：
插入：insert
修改：update
删除：delete
*/
#一、插入语句
#方式一：经典的插入
/*
语法：
insert into 表名(列名,...) values(值1,...);
*/
SELECT * FROM beauty;
#1.插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty (id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUE(13,'唐艺昕','女','1998-3-5','18391787298',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,id,sex,phone)
VALUES('蒋欣',16,'女','110');
#4.列数和值的个数必须一致
INSERT INTO beauty(NAME,id,sex,phone)
VALUE('关晓彤',17,'女',119);
#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUE(18,'张飞','男',NULL,119,NULL,NULL);
#方式二：
/*
语法：
insert into 表名
set 列名=值,列名=值,...
*/
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';
#两种方式大pk ★
#1、方式一支持插入多行,方式二不支持
INSERT INTO beauty
VALUE (23,'唐艺昕1','女','1990-4-23',189888888,NULL,2),
(24,'唐艺昕2','女','1990-4-23',189888888,NULL,2),
(25,'唐艺昕3','女','1990-4-23',189888888,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.修改单表的记录★
语法：
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 #这里表名没有主次之分
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';
#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON b.`boyfriend_id`=bo.`id`
SET b.`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.单表的删除
#案例：删除手机号以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 b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';
#方式二：truncate语句
#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys;
#delete pk truncate【面试题★】
/*
1.delete 可以加where 条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚.
*/
DELETE FROM boys;
TRUNCATE TABLE boys;
INSERT INTO boys (boyname,usercp)
VALUES('张飞',100),('刘备',100),('关云长',100);
#1. 运行以下脚本创建表my_employees
USE 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表中插入下列数据
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_employees
VALUE(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表中插入数据
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
INSERT INTO users
VALUE(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的员工的工资修改为1000
UPDATE 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 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
/*
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除
创建： create
修改： alter
删除： drop
*/
#一、库的管理
#1、库的创建
/*
语法：
create database [if not exists]库名;
*/
#案例：创建库Books
CREATE DATABASE IF NOT EXISTS books;
#2、库的修改
RENAME DATABASE books TO 新库名;#库名不建议修改，如果非要修改就到c盘找mysql--data--直接重命名，然后对MySQL进行重新启动。
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
#3、库的删除
DROP DATABASE IF EXISTS books;
#二、表的管理
#1.表的创建 ★
/*
语法：
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
*/
#案例：创建表Book
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorid INT,#作者编号
publihDate DATETIME #出版日期
);
DESC book;
#创建表author
CREATE TABLE author(
id INT,
an_name VARCHAR(20),
nation VARCHAR(10)
);
DESC author;
#2.表的修改
/*
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/
#①修改列名
ALTER TABLE book CHANGE COLUMN publihDate pubDate DATETIME;
#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
#④删除列
ALTER TABLE author DROP COLUMN annual;
#⑤修改表名
ALTER TABLE AUTHORS RENAME TO author;
DESC book_author;
#3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
#通用的写法：
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;
SELECT * FROM copy;
#2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
SELECT * FROM copy2;
#只复制部分数据
CREATE TABLE copy3
SELECT id,an_name
FROM author
WHERE nation='中国';
SELECT * FROM copy3;
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,an_name
FROM author
WHERE 0;
SELECT * FROM copy4;
#1. 创建表dept1
NAME NULL? TYPE
id INT(7)
NAME VARCHAR(25)
USE test;
CREATE TABLE dept1(
id INT,
NAME VARCHAR(20)
);
#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);
DESC emp5;
#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 COLUMN test_column INT;
#9.直接删除表emp5中的列 test_column
ALTER TABLE emp5 DROP COLUMN test_column;
DESC emp5;
常见的数据类型
#常见的数据类型
/*
数值型：
整型
小数：
定点数
浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）
日期型：
*/
#一、整型
/*
分类：
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
*/
#1.如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT(7) ZEROFILL,
t2 INT(7) ZEROFILL
);
DESC tab_int;
INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);
INSERT INTO tab_int VALUES(123,123);
SELECT * FROM tab_int;
#二、小数
/*
分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(M,D)
特点：
①
M：整数部位+小数部位
D：小数部位
如果超过范围，则插入临界值
②
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
*/
#测试M和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);
#原则：
/*
所选择的类型越简单越好，能保存数值的类型越小越好
*/
#三、字符型
/*
较短的文本：
char
varchar
其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：
text
blob(较大的二进制)
特点：
写法 M的意思 特点 空间的耗费 效率
char char(M) 最大的字符数，可以省略，默认为1 固定长度的字符 比较耗费 高
varchar varchar(M) 最大的字符数，不可以省略 可变长度的字符 比较节省 低
*/
CREATE TABLE tab_char(
c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');
INSERT INTO tab_char VALUES('A');
SELECT * FROM tab_set;
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');
#四、日期型
/*
分类：
date只保存日期
time 只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间
特点：
字节 范围 时区等的影响
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';
常见约束
#常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：
1.创建表时
2.修改表时
约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
表级约束：
除了非空、默认，其他的都支持
主键和唯一的大对比：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐
外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
*/
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
CREATE DATABASE students;
#一、创建表时添加约束
#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, #默认条件
majorld INT REFERENCES major(id) #外键
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
#2.添加表级约束
/*
语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
*/
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 up UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender='男'OR gender='女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);
SHOW INDEX FROM stuinfo;
#通用的写法：★
DROP TABLE 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)
);
#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
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;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo MODIFY COLUMN 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 fk_stuinfo_major;
SHOW INDEX FROM stuinfo;
#1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）
ALTER TABLE emp2 MODIFY COLUMN id PRIMARY KEY;
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);
#3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE stuinfo ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) 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,
NAME FLOAT UNIQUE AUTO_INCREMENT,
seat INT
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity (id,NAME) VALUE(NULL,'john');
INSERT INTO tab_identity (NAME)VALUE('lucy');
SELECT * FROM tab_identity;
SHOW VARIABLES LIKE '%auto_increment%';#每一步自增长的值，和起始值
SET auto_increment_increment=3;#设置每一步自增长的值位3；
事务（有点难）
#TCL
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account(username,balance)
VALUE('张无忌',1000),('赵敏',1000);
/*
Transaction Control Language 事务控制语言
事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
案例：转账
张三丰 1000
郭襄 1000
update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'
事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.
事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id =1;
显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
set autocommit=0;
步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤3：结束事务
commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点
事务的隔离级别：
脏读 不可重复读 幻读
read uncommitted：√ √ √
read committed： × √ √
repeatable read： × × √
serializable × × ×
mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
开启事务的语句;
update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name='郭襄'
结束事务的语句;
*/
SHOW VARIABLES LIKE 'autocommit'; #查看自动提交功能是否开启 如果开启位 on；
SHOW ENGINES;
#1.演示事务的使用步骤
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
#结束事务
ROLLBACK;#回滚事务
#commit;上交事务
SELECT * FROM account;
#3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=1;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=2;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;
视图
#视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
比如：舞蹈班和普通班级的对比
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改
表 create table 保存了数据 增删改查
*/
#案例：查询姓张的学生名和专业名
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
查询语句;
*/
USE myemployees;
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.employee_id =d.department_id
JOIN jobs j ON e.job_id =j.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#2.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees e
GROUP BY department_id;
#②使用
SELECT myv2.ag,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 myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.ag
FROM departments d
JOIN myv3 m ON m.`department_id`=d.`department_id`;
#二、视图的修改
#方式一：
/*
create or replace view 视图名
as
查询语句;
*/
SELECT * FROM myv3;
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees e
GROUP BY job_id;
#方式二:
/*
语法：
alter view 视图名
as
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;
#三、删除视图
/*
语法：drop view 视图名,视图名,...;
*/
DROP VIEW myv3;
#四、查看视图
DESC myv3;
SHOW CREATE VIEW myv3;
#一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees e
WHERE phone_number LIKE '011%';
#二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
#老方法
SELECT MAX(salary),department_id
FROM employees e
GROUP BY department_id
HAVING MAX(salary)>12000;
SELECT *
FROM departments d
JOIN (
SELECT MAX(salary) ag,department_id
FROM employees e
GROUP BY department_id
HAVING MAX(salary)>12000
) ag ON ag.department_id=d.department_id;
#新方法
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary),department_id
FROM employees e
GROUP BY department_id
HAVING MAX(salary)>12000;
SELECT * FROM departments d
JOIN emp_v2 m
ON d.`department_id`=m.department_id;
1416
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
