一，MYSQL入门
1.数据库相关概念
DB：数据库：存储数据的仓库，保存了一系列有组织的数据。
DBMS：数据库管理系统：数据库是通过DBMS创建和操作的容器。
SQL：结构化查询语言：专门用来与数据库通信的语言。
2.数据库的好处
1.可以持久化数据到本地
2.可以实现结构化查询，方便管理
3.数据库存储数据特点
1.将数据放到表中，表放到库中。
2.一个数据库有多张表，每个表都有一个名字，用来标识自己。
表名具有唯一性。
3.表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中类的设计。
4.表有列组成，我们也称为字段。所有表都是由一个列或多个列组成的，
每一列类似Java中的属性。
5.表中的数据按照行来存储，每一行类似于Java中的对象。
4.mysql的安装与使用
参照mysql安装文档
5.Mysql常用命令
显示数据库----->show Databases;
使用数据库----->use 数据库名；
显示表---->show tables;
显式指定数据库的表---->show tables from 数据库名；
查看位于那个数据库---->select database();
显示表结构--->desc 表名；
查看数据库版本：--->select version();
查看数据库版本2:----->Dos:mysql --version;
查看数据库信息----->show CREATE DATABASE mydb1;
查看服务器中的数据库，并把mydb1的字符集修改为utf-8----->ALTER DATABASE mydb1character set utf8;
删除数据库----->drop database mydb1;
表中增加一栏信息----->alter table student add image blob;
删除表----->drop table student;
修改地址----->alter table student modify address varchar(100);
删除一个属性-----> alter table student drop image;
修改表名----->rename table student to students;
查看表的创建细节----->show create table students;
修改表的字符集为 gbk----->alter table students character set gbk;
列名name修改为studentname----->alter table students change name studentname varchar(100);
6.mysql语法规范
1.不区分大小写，建议关键字大写，表名列名小写。
2.每条命令最好用分号结尾。
3.每条语句可以缩进，换行。
4.注释
单行注释：#注释文字
-- 注释文字
多行注释：/* */
二，DQL查询语言
1.基础查询
**语法： select 查询列表 from 表名**
**查询列表：表中的字段，常量，表达式，函数**
**查询的结果是张虚拟的表格**
1.查询表中的单个字段
select last_name from employee;
2.查询表中的多个字段
select last_name,salary,email from employee;
3.查询表中的所有字段
select * from employee;
4.查询常量值
select 100;
select 'john';
5.查询表达式
select 100*98;
6.查询函数
select version();
7.起别名
select last_name as name from employee;
select last_name name from employee;
8.去重
查询员工表中涉及到的所有的部门编号
select distinct department_id from employee;
9.+的作用
#运算符：两个操作数都为数值型，则做加法运算；
#其中一方为字符型，试图将字符型数值转换成数值型，
#如果转换成功，继续做加法运算；否则，将字符型数值
#转换为0；
10.使用concat实现连接
#案例：查询员工名和性连接成一个字段
SELECT CONCAT(username,PASSWORD) FROM USER;
#任何数与null做运算结果都为null
2.条件查询
语法：
select 查询列表 from 表名 where 筛选条件
分类：
①按照条件表达式筛选
条件运算符：>,<,=,!=,>=,<=
查询员工工资>1w2的员工信息
select * from employee where salary >12000;
查询部门编号！=90号的员工名和部门编号
select name, dep_id from employee where dep_id 1=90；
②按照逻辑表达式筛选
逻辑运算符：&&,||,!,AND,OR,NOT
查询工资在一万到两万之见的员工名，工资以及奖金。
select name,salary ,jiangjin where salary between 10000 and 20000;
查询部门编号不在90-110之间，或者工资高于15000的员工信息。
select * from employee where department<90||department>110 ||salary >15000;
③模糊查询
like：一般和通配符搭配使用
通配符：
%任意多个字符，包含0个字符
_任意单个字符
BETWEEN AND:包含临界值
IN:判断某个字段的值是否属于in列表中的某一项
IS NULL,IS NOT NULL:=或者！=不能用来判断null
安全等于<=>可以判断null
查询员工名中包含a的员工信息
select * from emp where name like %a%;
查询员工名中第三个字符为e第五个字符为a的员工名和工资
select name ,salary from emp where name like %__e_a%;
员工名中第二个字符为_的员工名
select name from emp where name like %_\_%;
查询员工编号在100到120之间的所有员工信息
select * from emp where id between 100 and 120;
查询员工的工种编号是IT_PRIG,AD_PRES,AD_VP中的一个员
工名和工种编号；
select name , id from emp where id in(IT_PRIG,AD_PRES,AD_VP);
查询没有奖金的员工名和奖金率
select salary , jjl from emp where salary is Null;
查询有奖金的员工名和奖金率
select salary ,jjl from emp where salary is not null;
④IF null的使用：
查询员工号为176的员工的姓名和部门号和年薪
SELECT last_name ,department_id , salary*(1+IFNULL(commission_pct,0))*12 '年薪'
FROM employees WHERE employee_id =176;
3.排序查询
语法：
select 查询列表
from 表
where 筛选条件
order by 排序列表 asc 或desc （升序或者降序，默认为升序）
查询员工信息，要求工资从高到低排序
select * from emp order by salary desc;
查询部门编号大于等于90的员工信息，按照入职时间先后排序
select * from emp where dep_id >=90 order by createtime asc;
按照员工年薪的高低显示员工的信息和年薪
select * ,年薪 from emp order by salary*(1+if null(jjl,0))*12 as 年薪 desc;
按姓名长度显示员工的姓名和工资
select name ,salary from emp order by length(name) asc;
查询员工信息，先按照工资排序，再按照员工编号排序
select * from emp order by salary asc,id asc;
4.常见函数
功能：类似Java中的方法
分类：单行函数
分组函数
1.单行函数
1.字符函数
1.length 获取参数值的字节个数
select * from emp order by length(name);
2.concat 拼接字符串
select concat(last_name,first_name) as 姓名 from emp;
3.upper，lower 大小写转换函数
案例：将姓变大写，名字变小写，然后拼接
SELECT
CONCAT(UPPER(last_name),LOWER(first_name))
FROM employees;
4.substr,SUBSTRING 截取字符串
SELECT SUBSTR('李莫愁',2);
SELECT SUBSTR('李莫愁',2,3);
案例：姓名中首字符大写，其他的小写然后用_拼接显示出来
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
) output
FROM
employees ;
5.instr:返回字串第一次出现的索引，如果找不到返回0
SELECT INSTR('风急天高猿啸哀','天') AS out_put;
6.trim :去掉前后空格或前后指定字符
SELECT LENGTH(TRIM(' 张三丰 ')) AS out_put;
SELECT TRIM('a' FROM 'aaaa1aa2aaa3aaa') AS out_put;
7.lpad :用指定字符填满指定长度（左填充）
SELECT LPAD('苍老师',10,'*');
8.rpad:用指定字符填满指定长度（右填充）
SELECT RPAD('苍老师',10,'*');
9.replace 替换
SELECT REPLACE('千锋培训机构','千锋','尚硅谷');
2.数学函数
1.round:四舍五入
SELECT ROUND(1.666);
SELECT ROUND(1.567,2);
2.ceil 向上取整
SELECT CEIL(1.52);
3.floor 向下取整
SELECT FLOOR(1.52);
4.truncate:截断（小数点后保留几位）
SELECT TRUNCATE(1.65,2);
5.mod:取余
SELECT MOD(10,3);
3.日期函数
1.now:返回当前系统日期时间
SELECT NOW();
2.curdate:返回当前系统日期
SELECT CURDATE();
3.curtime:返回当前时间
SELECT CURTIME();
4.获取指定部分的年月日时分秒
SELECT YEAR(NOW());
SELECT YEAR(hiredate) FROM employees;
5.str_to_date将字符通过指定的格式转化成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
案例：查询入职时间为1992-4-3的员工信息
SELECT * FROM employees
WHERE hiredate=STR_TO_DATE('2016-3-3','%Y-%c-%d');
6.date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 日期;
案例：查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT
last_name,
DATE_FORMAT(hiredate, '%c月/%d日 %y')
FROM
employees
WHERE commission_pct IS NOT NULL ;
4.其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
5.流程控制函数
1.if:IF else效果
SELECT IF(10>5,'true','false');
案例：查询如果有奖金就备注有，没有就备注没有。
SELECT
last_name,
commission_pct,
IF(
commission_pct IS NULL,
'没奖金',
'有奖金'
) AS 备注
FROM
employees ;
2.case函数
1)switch-CASE
语法:
CASE 要判断的字段或者表达式
WHEN 常量1 THEN 要显示的值1或者语句1
WHEN 常量2 THEN 要显示的值2或者语句2
...
ELSE 要显示的值n或者语句n；
案例：查询员工的工资，要求
部门号==30，显示的工资为1.1倍，
部门号==40，显示的工资为1.2倍，
部门号==50，显示的工资为1.3倍，
其他部门，显示原有工资。
SELECT
salary AS 原始工资,
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 ;
2)CASE 使用2：
语法：
CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件2 THEN 要显示的值2或语句2
...
ELSE 要显示的值n或语句n
END
案例：查询员工的工资情况
如果>2w，显示A
如果>1.5w，显示B
如果>1w，显示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 ;
2.分组函数
功能：用作统计使用
1.sum :求和
SELECT SUM(salary) FROM employees;
2.avg：平均值
SELECT AVG(salary) FROM employees;
3.max：最大值
SELECT MAX(salary) FROM employees;
4.min：最小值
SELECT MIN(salary) FROM employees;
5.count：计算个数
SELECT COUNT(salary) FROM employees;
总结
①.sum,avg一般用于处理数值类型
②.max，min，count用来处理任何类型
③.以上分组函数都忽略null值
④.可以和distinct搭配
SELECT SUM(DISTINCT salary) 纯净,SUM(salary) FROM employees;
6.count的详细介绍
①select COUNT(*) FROM employees;
②select COUNT(1) FROM employees;
③和分组函数一同查询的字段要求是group by后的字段。
5.分组查询
GROUP BY 和分组函数对应
分组查询中分组条件分为两类
| 数据源 | 位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | GROUP BY 子句的前面 | WHERE |
| 分组后筛选 | 分组后的结果集 | GROUP BY 子句的后面 | HAVING |
分组函数做条件肯定是放在having子句中。
group BY 子句支持单个字段分组，多个字段分组
（多个字段之间用逗号隔开没有顺序要求），表达式或函数。
也可以添加排序，放在整个分组查询的最后。
案例：查询每个工种的最高工资
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY job_id
ORDER BY MAX(salary) ASC ;
案例：查询邮箱中包含a字符的，每个部门的平均工资
SELECT
AVG(salary),
department_id
FROM
employees
WHERE email LIKE '%a%'
GROUP BY department_id ;
#select Avg(salary),dep_id from employee where email like %a% group by dep_id ;
案例：查询有奖金的每个领导手下员工的最高工资
SELECT
MAX(salary),
manager_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id ;
#select max(salary) ,manage_id from employees where commission_pct is not null group by manager_id;
案例：哪个部门的员工个数大于二？
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_id
HAVING COUNT(*) > 2 ;
#select dep_id from emp group by dep_id having count(*)>2;
案例：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
MAX(salary),
job_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000 ;
#select job_id ,max(salary) from emp where commission_pct IS NOT NULL group by job_id having max(salary)>12000;
案例：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个？
SELECT manager_id ,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#select manager_id from emp where manager_id>102 group by manager_id having min(salary)>5000;
#按照员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些？
SELECT COUNT(*) AS c
FROM employees
GROUP BY LENGTH(last_name)
HAVING c>5;
# select count(*) from emp group by length(name) having count(*)>5;
#查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY department_id,job_id;
#select avg(salary) from emp group by dep_id,job_id;
#查询每个部门每个工种的员工的平均工资并且按照平均工资的高低显示
SELECT AVG(salary),job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) ASC;
#select avg(salary) from emp group by dep_id,job_id order by avg(salary) asc;
6.连接查询
又称为多表查询，当查询的字段来自多个表时，就会用到连接查询。
笛卡尔乘积现象：表1有m行，表2有n行，结果：m*n行
发生原因：没有有效的连接条件
分类
①按年代分类
sql92:仅仅支持内连接
sql99：不支持全外连接
②按功能分类
| 内连接 | 外连接 | 交叉连接 |
|---|---|---|
| 等值连接 | 左外连接 | |
| 非等值连接 | 右外连接 | |
| 自连接 | 全外连接 |
1.等值连接
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序，分组，筛选
#案例一：查询女优名对应的男优名
SELECT
NAME,
boyName
FROM
beauty,
boys
WHERE beauty.boyfriend_id = boys.`id` ;
#select name, boyname from girl ,boy where girl.boyfriend_id=boy.id;
#案例：查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE employees.`department_id` = departments.`department_id` ;
#select name ,dep_name from emp e,dep d where e.dep.id= d.id;
#案例：查询员工名，工种号，工种名。
SELECT
last_name,
emp.`job_id`,
job_title
FROM
employees emp,
jobs job
WHERE emp.`job_id` = job.`job_id` ;
#select name , e.job_id,job_title from emp e,job j where e.job_id=j.id;
#案例：查询有奖金的员工名和部门名
SELECT
last_name,
department_name
FROM
employees emp,
departments dep
WHERE commission_pct IS NOT NULL && emp.`department_id` = dep.`department_id` ;
#select name ,dep_name from emp e ,dep d where e.dep_id =d.id &&e.salary_pct is not null;
#案例：查询城市名第二个字符为o的部门
SELECT
department_name
FROM
locations l,
departments d
WHERE l.`location_id` = d.`location_id`
AND l.`city` LIKE '_o%' ;
#select dep_name from location l , dep d where l.city like %_o% && l.id =d.location_id;
#案例：查询每个城市的部门个数
SELECT
COUNT(*),
city
FROM
locations l,
departments d
WHERE l.`location_id` = d.`location_id`
GROUP BY l.`city` ;
#select count(*),city from loca l,dep d where l.loc_id=d.loc_id group by count(*) asc;
#案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT
d.`department_name`,
d.manager_id,
MIN(salary)
FROM
employees e,
departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.`department_id`,
d.`department_name` ;
#select dep_name ,d.manager_id ,min(salary) from emp e ,dep d where e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL GROUP BY d.`department_id`,d.`department_name` ;
#案例：查询每个工种的工种名和员工的个数，并且按照员工个数降序排序
SELECT
j.job_title,
COUNT(*)
FROM
jobs j,
employees e
WHERE j.`job_id` = e.`job_id`
GROUP BY e.`job_id`,
j.`job_title`
ORDER BY COUNT(*) DESC ;
#案例：查询员工名，部门名和所在城市
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` ;
2.非等值连接
#案例：查询员工的工资和工资级别
SELECT DISTINCT
salary,
grade_level
FROM
employees e,
job_grades j
WHERE e.salary >= j.lowest_sal && e.salary <= j.highest_sal
ORDER BY salary ASC ;
3.自连接
#案例：查询员工名和上级的名称
SELECT
e.last_name,
m.last_name
FROM
employees e,
employees m
WHERE e.manager_id = m.employee_id ;
4.内连接
INNER 可以省略
#查询员工名，部门名
SELECT
last_name,
department_name
FROM
employees e
INNER JOIN departments d
ON e.department_id = d.department_id ;
#查询名字中包含e的员工名和工种名
SELECT
last_name,
job_title
FROM
employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE last_name LIKE '%e%' ;
#查询部门个数>3的城市名和部门个数
SELECT
city,
COUNT(*)
FROM
departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3 ;
#查询哪个部门的部门员工个数>3的部门名和员工个数，并按照个数降序排序
SELECT
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY e.department_id
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC ;
#查询员工名，部门名，工种名，并按照部门名降序排序
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 department_name DESC ;
#查询员工工资级别
SELECT
grade_level,
salary
FROM
job_grades j
INNER JOIN employees e
ON e.`salary` BETWEEN j.`lowest_sal`
AND j.`highest_sal` ;
#查询每个工资级别的个数，并且降序排序
SELECT grade_level,COUNT(*)
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal`
AND j.`highest_sal`
GROUP BY grade_level
ORDER BY COUNT(*) DESC;
#查询员工的名字和上级的名字
SELECT e1.last_name, e2.last_name
FROM employees e1
INNER JOIN employees e2
ON e1.`employee_id`=e2.`manager_id`;
5.左外连接
语法：
SELECT 查询列表
FROM 表1 【连接类型】
JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 筛选条件
ORDER BY 排序条件
连接类型：
内连接：inner
左外连接：left
右外连接：right
全外连接：full
交叉连接：cross
外连接
用于查询一个表中有，另一个表中没有的数据
左外连接，left左边是主表
右外连接，right右边是主表
Mysql不支持全外连接
#没有男朋友的女生
SELECT
g.`name`,b.`boyName`
FROM beauty g
LEFT JOIN boys b
ON g.`boyfriend_id`=b.`id`
WHERE b.`boyName` IS NULL;
6.交叉连接
笛卡尔乘积
7.子查询
出现在其它语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
①按照子查询出现的位置：
| select后面 | from后面 | where或having后面 | exists后面 |
|---|---|---|---|
| 仅仅支持标量子查询 | 支持表子查询 | 标量子查询，列子查询 | 表子查询 |
②按照结果集的行列数不同：
| 标量子查询 | 列子查询 | 行子查询 | 表子查询 |
|---|---|---|---|
| 结果只有一行一列 | 结果一列多行 | 一行多列 | 多行多列 |
1）where或having后面
特点：
子查询一般放在小括号内
子查询一般放在条件的右边
标量子查询，一般搭配着单行操作符
列子查询：一般搭配多行操作符使用
1.标量子查询
#谁的工资比Abel高
SELECT
last_name
FROM
employees
WHERE salary >
(SELECT
salary
FROM
employees
WHERE last_name = 'Abel') ;
#返回job_id于141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
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)
#返回公司工资工资最少的员工的姓名，job_id,salary
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE salary =
(SELECT
MIN(salary)
FROM
employees);
#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT
department_id,
MIN(salary)
FROM
employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT
MIN(salary)
FROM
employees
WHERE department_id = 50) ;
2.列子查询
多行操作符：
IN / NOT in：等于列表中的任意一个
ANY / SOME ：和子查询返回的某一个值比较
ALL ：和子查询返回的所有值比较
#返回location_id是1400或者1700的部门中的所有员工姓名
SELECT
last_name
FROM
employees
WHERE department_id IN
(SELECT DISTINCT
department_id
FROM
departments
WHERE location_id IN (1400, 1700)) ;
#返回其他工种中比job_id为IT_PROG部门任意工资低的员工
#工号，姓名，job_id以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary <
(SELECT
MAX(salary)
FROM
employees
WHERE job_id = 'IT_PROG')
AND job_id !='IT_PROG';
#返回其他工种中比job_id为IT_PROG部门所有工资低的员工
#工号，姓名，job_id以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary <
(SELECT
MIN(salary)
FROM
employees
WHERE job_id = 'IT_PROG')
AND job_id !='IT_PROG';
*********************************
3.行子查询
#查询员工编号最小并且工资最高的员工信息
SELECT
*
FROM
employees
WHERE employee_id =
(SELECT
MIN(employee_id)
FROM
employees)
AND salary =
(SELECT
MAX(salary)
FROM
employees)
2）SELECT 后面
#查询每个部门的员工个数
SELECT
d.*,
(SELECT
COUNT(*)
FROM
employees e
WHERE e.department_id = d.department_id)
FROM
departments d ;
#查询员工号等于102的部门名
SELECT
department_name
FROM
departments
WHERE department_id =
(SELECT
department_id
FROM
employees
WHERE employee_id = 102) ;
3）FROM 后面
#查询每个部门平均工资的工资等级
SELECT
grade_level ,aa.department_id
FROM
(SELECT
AVG(salary) ag,
department_id
FROM
employees
GROUP BY department_id) aa
INNER JOIN job_grades j
ON aa.ag BETWEEN lowest_sal
AND highest_sal ;
4）exists后面（相关子查询）
#查询有员工的部门名
SELECT department_name FROM departments d
WHERE EXISTS(
SELECT * FROM employees e WHERE d.department_id=e.department_id
);
#查询没有女朋友的男生信息
SELECT bo.* FROM boys bo WHERE
bo.`id` NOT IN(SELECT boyfriend_id FROM beauty);
5）子查询经典案例祥讲
1.查询工资最低的员工信息：last_name,salary
SELECT last_name,salary FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);
2.查询平均工资最低的部门信息
SELECT * FROM departments WHERE department_id=
(SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary)
LIMIT 1)
3.查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*,a1.ag FROM departments d JOIN
(SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ORDER BY AVG(salary)
LIMIT 1) a1
ON d.department_id=a1.department_id
4.查询平均工资最高的job信息
SELECT j.* FROM jobs j WHERE j.job_id=
(SELECT job_id FROM employees
GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1)
5.查询平均工资高于公司平均工资的部门有哪些
SELECT department_id FROM
(SELECT department_id ,AVG(salary) AS avg1 FROM employees GROUP BY department_id) e1
WHERE e1.avg1>(SELECT AVG(salary) AS avg2 FROM employees)
6.查询出公司中所有manager的详细信息
SELECT * FROM employees
WHERE employee_id IN(SELECT DISTINCT manager_id FROM employees);
7.各个部门中，最高工资中最低的那个部门的最低工资是多少
SELECT MIN(salary) FROM employees GROUP BY department_id
HAVING 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 last_name,department_id,email,salary FROM employees WHERE employee_id=(
SELECT manager_id FROM departments WHERE department_id=(
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary)
DESC LIMIT 1))
8.分页查询
*语法：limit(currentPage-1)size,size
#查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
#查询第11-25条员工信息
SELECT * FROM employees LIMIT 10,15;
#查询有奖金的员工，并且工资最高的前十名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0 ,10;
9.联合查询
要查询的结果来自于多个表，且多个表没有直接的连接关系，单查询的信息一致时
特点：
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.union关键字默认去重，如果使用union all 可以不去除重复项
案例：查询员工部门编号大于90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
三，DML数据操作语言
插入insert
一：插入语句
#插入beauty一行数据
INSERT INTO beauty(NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES('波多野吉依','女','1998-11-11','13342969497',
NULL,10)
#可以为null的列如何不插入值
直接写null，或列名少写一列
INSERT INTO beauty(NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES('小泽玛利亚','女','1999-11-11','13342456497',
NULL,11)
INSERT INTO beauty VALUES(15,'马蓉','女','1989-11-11','13342456123',
NULL,12);
INSERT INTO beauty SET id=16,NAME='刘亦菲', sex='女',borndate='1989-10-01',
phone='15945231056',boyfriend_id=16;
#insert 嵌套子查询，将一个表的数据插入另一张表
INSERT INTO beauty (NAME,sex,borndate,phone,boyfriend_id)
SELECT '妲己','女','1111-11-11','13146587954',0;
修改update
二，修改
UPDATE beauty SET phone='110' WHERE id=16;
多表修改：sql99
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列=值
WHERE 筛选条件
#修改张无忌的女朋友手机号为114
UPDATE beauty g
INNER JOIN boys b
ON g.boyfriend_id=b.id
SET g.phone='114'
WHERE b.boyName='张无忌';
#修改没有男朋友的女生的男朋友编号都为4号
UPDATE beauty g
LEFT JOIN boys b
ON g.`boyfriend_id`=b.id
SET g.`boyfriend_id`=4
WHERE b.id=NULL;
删除delete
三，删除
DELETE 和 TRUNCATE 的区别：
1.delete可以加where条件，truncate不行
2.truncate删除效率高
3.加入要删除的表中有自增列，
用delete删除整个表后在插入数据，从断点处开始插入
用truncate删除后在插入数据，从1开始。
4.truncate删除没有返回值，delete有返回值
5.truncate删除不能回滚，delete删除可以回滚
DELETE FROM beauty WHERE id=17;
语法：truncate TABLE 表名;
#删除张无忌的女朋友的信息
DELETE g FROM beauty g
INNER JOIN boys b
ON g.boyfriend_id=b.id
WHERE b.id=1;
#删除黄晓明以及他女朋友的信息
DELETE b,g FROM beauty g
INNER JOIN boys b
ON b.`id`=g.`boyfriend_id`
WHERE b.`boyName`='黄晓明';
多表删除 :TRUNCATE
TRUNCATE TABLE boys
四，DDL数据定义语言
1.库和表的管理
一，库的管理
创建 CREATE
CREATE DATABASE IF NOT EXISTS mydb1 ;
修改 ALTER
1.更改字符集
ALTER DATABASE mydb1 CHARACTER SET utf8;
删除 DROP
DROP DATABASE IF EXISTS school;
二，表的管理
创建 CREATE
CREATE TABLE book(
id INT PRIMARY KEY,
b_name VARCHAR(30),
price DOUBLE,
author_id INT ,
publishDate DATE
);
DESC book ;
CREATE TABLE author(
id INT PRIMARY KEY ,
au_name VARCHAR(20),
nation VARCHAR(10)
);
DESC author;
修改 ALTER
1.修改列名
ALTER TABLE book CHANGE COLUMN publishDate pub_date DATETIME;
2.修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pub_date DATE;
3.添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
4.删除新列
ALTER TABLE author DROP COLUMN annual;
5.修改表名
ALTER TABLE author RENAME TO book_author;
删除 DROP
DROP TABLE IF EXISTS my_employee;
SHOW TABLES;
复制
1.仅仅复制表的结构
CREATE TABLE copy LIKE book_author;
2.复制表的结构加数据
CREATE TABLE copy2
SELECT * FROM book_author;
3.复制部分结构
CREATE TABLE copy3
SELECT id,au_name
FROM book_author
WHERE id=0;
2.数据类型
数值型
1.整型
| TINYINT | SMALLINT | MEDIUMINT | INT/INTEGER | BIGINT |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 8 |
如何设置无符号和有符号(默认有符号)
DROP TABLE tab_int;
CREATE TABLE tab_int(t1 INT,t2 INT UNSIGNED);
INSERT INTO tab_int(t1,t2) VALUES(-1,1);
DESC tab_int;
1）如果插入的数值超出了整形的范围，会报out of range异常，并且插入
临界值。
2）如果不设置长度，会有默认的长度。
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配
zerofill使用。
2.小数
①定点数
dec（M,D）
②浮点数
float（4） ，double（8）
M，D的意思：M指定一共多少位，D指定小数几位，超出会四舍五入。
MD都可以省略，
如果是dec，则M默认为10，D默认为0
如果是浮点数，则会根据插入数值的精度改变精度
定点型精度相对较高。
3.字符型
①较短的文本
CHAR(M)默认为1,VARCHAR(M)
M:字符数
char：固定长度字符，比较耗费空间，但是效率高。
varchar：可变长度字符
ENUM 枚举类
CREATE TABLE tab_char(
t1 ENUM('a','c','b')
);
SET 集合
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set(s1) VALUES('a,b');
BINARY:保存较短的二进制。
②较长的文本
text（文本）,BLOB(较大的二进制)
4.日期型
DATE:日期
DATETIME:日期加时间，8字节
timestamp：跟时区有关系，建议使用，4字节
time：时间
year：年
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date(t1,t2)
VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SET time_zone='+9:00';
#设置时区为东9区
3.常见约束
含义：一种限制，用于限制表中的数据，保证数据的一致性。
-
NOT NULL
-
DEFAULT
-
PRIMARY KEY 唯一，且不为空
-
UNIQUE 唯一，可以为空
-
CHECK Mysql不支持
-
FOREIGN KEY 外键约束，用于限制两个表的关系，
用于保证该字段的值必须来自于主表的关联列的值。
约束的分类：
列级约束：除外键约束
表级约束：除了非空，默认。
CREATE TABLE 表名(
字段1 字段类型 列级约束,
字段2 字段类型 列级约束,
表级约束
);
#创建表时添加列级约束
DROP TABLE tab_test;
CREATE TABLE tab_test(
id INT PRIMARY KEY,
stu_name VARCHAR(20) NOT NULL,
gender CHAR DEFAULT '男',
seat_id INT UNIQUE,
major_id INT REFERENCES tab_major(id)
);
CREATE TABLE tab_major(
id INT PRIMARY KEY ,
major_name VARCHAR(20) NOT NULL
);
DESC tab_test;
SHOW INDEX FROM tab_test;
#查看索引信息
#添加表级约束
CREATE TABLE tab_test(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(20) NOT NULL,
gender CHAR DEFAULT '男',
seat_id INT UNIQUE,
major_id INT ,
CONSTRAINT m_id FOREIGN KEY(major_id) REFERENCES tab_major(id)
);
CONSTRAINT m_id 可以省略
面试题：主键约束和唯一约束的区别：
都可以保证唯一性，
主键不能为空 ，unique 能为空，但是只能有一个null。
主键只能有1个，unique可以有多个。
都允许两个列组合成一个约束。
面试题：外键：
要求在从表设置外键关系
从表的外键列类型和主表的关联列类型一致，名称无要求
要求主表的关联列必须是主键或者唯一键
插入数据应该先插入主表再插入从表
删除数据应该先删除从表，在删除主表
二，修改表时添加约束
CREATE TABLE tab_test2(
id INT ,
stu_name VARCHAR(20) ,
gender CHAR ,
seat_id INT ,
major_id INT
);
ALTER TABLE tab_test2 MODIFY COLUMN stu_name VARCHAR(20) NOT NULL ;
ALTER TABLE tab_test2 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#添加外键
ALTER TABLE tab_test2 ADD FOREIGN KEY(major_id) REFERENCES tab_major(id);
4.标识列
自增长列 AUTO_INCREMENT
特点：
1.表示必须和一个key搭配
2.一个表最多一个标识列
3.标识列类型只能是数值型
4.标识列可以通过set auto_increment_increment=3;设置步长
CREATE tab_auto(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL
);
五，TCL语言：事务控制语言
事务：一个或一组sql语句组成的执行单元，
要么全部执行,要么都不执行。
存储引擎:在MySQL中的数据用各种不同的技术存储在文件中。
通过show ENGINES;来查看mysql支持的存储引擎。
innodb引擎支持事务。
事务的ACID属性：
1.原子性:事务是一个不可分割的工作单位，要么都发生，要么都不发生。
2.一致性：事务必须使数据库从一个一致性状态变为另一个一致性状态。
3.隔离性：一个事务的执行不能被另一个事务干扰。
4.持久性：事务一旦被提交，对数据库事务的改变就是永久性的。
DELETE 和 TRUNCATE 在事务中的区别：
演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM tab_teacher;
ROLLBACK;
演示 TRUNCATE
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE tab_teacher;
ROLLBACK;
DELETE 是直接删除表中数据，truncate是江表删除，创建一张与原来一样的空表。
六，视图
含义：虚拟表，和普通表格一样使用
通过表动态生成的数据
1.创建视图
语法：
CREATE VIEW 视图名
AS
查询语句 ;
# 案例：查询姓名中包含a字符的员工名，部门名和工种信息
create view view1 as
select e.last_name,d.department_name ,j.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
where e.last_name like '%a%';
select * from view1;
# 案例：查询各个部门的平均工资级别
create view view2 as
select j.grade_level ,aa.department_id
from job_grades j
inner join (select avg(salary) avg_s,department_id from employees group by department_id) aa
on aa.avg_s between j.lowest_sal and j.highest_sal;
select * from view2;
# 案例：查询平均工资最低的部门信息
create view view3 as
select avg(salary) avg_s ,department_id
from employees
group by department_id
order by avg_s asc
limit 1;
select * from view3;
2.视图修改
①create OR REPLACE VIEW 视图名 AS 查询语句;
②alter VIEW 视图名 AS 查询语句;
3.删除视图
DROP VIEW v1,v2;
4.查看视图
DESC v1;
#创建视图emp_v1，要求查询电话号码以011开头的员工姓名和工资，邮箱
CREATE VIEW emp_v1 AS
SELECT last_name ,salary,email FROM employees WHERE
phone_number LIKE '%011';
#创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE VIEW v4 AS
SELECT department_id FROM employees GROUP BY department_id
HAVING MAX(salary)> 12000;
CREATE VIEW emp_v2 AS
SELECT * FROM departments WHERE department_id IN(SELECT * FROM v4);
5.视图的更新
视图的可更新性和视图中查询的定义有关，以下类型的视图是不能更新的。
1.包含以下关键字的sql语句：分组函数，distinct，group by，having，union
2.常量视图
3.select中包含子查询的
4.join
5.from 一个不能更新的视图
6.where子句的子查询引用了from子句的表
6.视图和表的对比：
| 创建语法的关键字 | 是否实际占用物理空间 | 使用 | |
|---|---|---|---|
| 视图 | CREATE VIEW | 只是保存了sql逻辑 | 增删改查，一般不能增删改 |
| 表 | CREATE TABLE | 占用 | 增删改查 |
七，变量
系统变量 ：变量由系统提供，不是用户自定义，属于服务器层面。
查看系统所有变量：show GLOBAL VARIABLES;
查看满足条件的部分系统变量： SHOW GLOBAL VARIABLES LIKE ‘%char%’;
查看指定的某个系统变量的值： SELECT @@global.autocommit;
为某个系统变量赋值：set @@global.系统变量名=值;
全局变量:GLOBAL
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话有效，但不能
跨重启。
会话变量:SESSION
作用域：针对当前的会话有效。
用户自定义变量
用户变量
声明： SET/SELECT @用户变量名 :=值;
赋值：通过 SELECT 字段 INTO 变量名;或 SET/SELECT @用户变量名 :=值;
使用：select @用户变量名;
应用在任何地方。
作用域：针对当前会话和连接有效。
局部变量
作用域：作用在定义它的begin END 块中。
声明： DECLARE 变量名 类型 （default 值）;
赋值：通过 SELECT 字段 INTO 变量名;或 SET/SELECT @变量名 :=值;
使用：select @变量名;
只能放在begin END 中的第一句话
八，存储过程和函数
存储过程：
一组预先定义好的sql语句集合，理解成批处理语句。
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。
1.创建语法：
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
一组合法的sql语句;
END
参数列表：参数模式 参数名 参数类型
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
OUT ：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出
如果存储过程只有一句话，begin END 可以省略
存储过程体中的每条sql语句的结尾需要必须加分号，
存储过程的结尾可以使用 DELIMITER 重新设置。
2.调用
CALL 存储过程名（实参列表）;
3.案例
#插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE my_a()
BEGIN
INSERT INTO admin(username,PASSWORD) VALUES('yin','666');
INSERT INTO admin(username,PASSWORD) VALUES('aa','123');
INSERT INTO admin(username,PASSWORD) VALUES('bb','666');
INSERT INTO admin(username,PASSWORD) VALUES('cc','123');
INSERT INTO admin(username,PASSWORD) VALUES('dd','666');
END $
#创建存储过程实现 根据女生名查询对应的男生信息
DELIMITER $
CREATE PROCEDURE my_b(IN beauty_name VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beauty_name;
END $
CALL my_b('热巴');
#根据女生名返回他的男朋友名
DELIMITER $
CREATE PROCEDURE my_d(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END $
CALL my_d('小昭',@b_name);
SELECT @b_name;
#传入两个值a，b，最终翻倍返回a和b
DELIMITER $
CREATE PROCEDURE my_e(INOUT a INT ,INOUT b INT )
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=10;
SET @n=20;
CALL my_e(@m,@n);
SELECT @m,@n;
4.删除存储过程
DROP PROCEDURE 存储过程名
DROP PROCEDURE my_a;
5.查看存储过程的信息
SHOW CREATE PROCEDURE my_b;
函数
存储过程可以有0/n个返回值：适合批量增删改
函数有且仅有一个返回值：适合查询
1.创建
DELIMITER $
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
END
注意：
参数列表：参数名，参数类型
一定会有return语句
2.使用
SELECT 函数名(参数列表)
#返回公司员工个数
DELIMITER $
CREATE FUNCTION my_f1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0 ;
SELECT COUNT(*) INTO c FROM employees;
RETURN c;
END $
SELECT my_f1();
#根据员工名返回他的工资
DELIMITER $
CREATE FUNCTION my_f2(NAME VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE c DOUBLE;
SELECT salary INTO c FROM employees WHERE last_name=NAME;
RETURN c;
END $
SET @a='Hunold';
SELECT my_f2(@a);
3.查看
SHOW CREATE FUNCTION my_f2;
4.删除
DROP FUNCTION my_f2;
九，流程控制
分支结构
1.if （表达式1，表达式2，表达式3）
如果表达式1成立，就返回表达式2的值，否则返回表达式3的值。
应用在任何地方
2.case
1)switch-CASE
语法:
CASE 要判断的字段或者表达式
WHEN 常量1 THEN 要显示的值1或者语句1
WHEN 常量2 THEN 要显示的值2或者语句2
…
ELSE 要显示的值n或者语句n；
案例：查询员工的工资，要求
部门号==30，显示的工资为1.1倍，
部门号==40，显示的工资为1.2倍，
部门号==50，显示的工资为1.3倍，
其他部门，显示原有工资。
SELECT
salary AS 原始工资,
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 ;
2)CASE 使用2：
语法：
CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件2 THEN 要显示的值2或语句2
…
ELSE 要显示的值n或语句n
END
案例：查询员工的工资情况
如果>2w，显示A
如果>1.5w，显示B
如果>1w，显示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
可以放在任何地方
#创建存储过程，根据传入的成绩，显示等级，90A,80B，70C，60D ，F
DELIMITER $
CREATE PROCEDURE my_1(IN score INT)
BEGIN
CASE
WHEN score BETWEEN 90 AND 100
THEN SELECT 'A';
WHEN score BETWEEN 80 AND 90
THEN SELECT 'B';
WHEN score BETWEEN 70 AND 80
THEN SELECT 'C';
WHEN score BETWEEN 70 AND 60
THEN SELECT 'D';
ELSE SELECT 'E';
END CASE;
END $
CALL my_1(95);
3.if
语法：
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
…
ELSE 语句n;
END IF;
只能用在begin end中
#创建存储过程，根据传入的成绩，返回等级，90A,80B，70C，60D ，F
DELIMITER $
CREATE FUNCTION my_2( score INT) RETURNS CHAR
BEGIN
IF score >=90 THEN RETURN'A';
ELSEIF score >=80 THEN RETURN'B';
ELSEIF score >=70 THEN RETURN'C';
ELSEIF score >=60 THEN RETURN'D';
ELSE RETURN'E';
END IF;
END $
SELECT my_2(85);
循环结构
在存储过程或函数里面使用
1.while
语法：
标签:WHILE 循环条件 DO
循环体;
END WHILE 标签;
循环控制和标签搭配使用
2.loop
语法：
标签： LOOP
循环体;
END LOOP 标签;
3.repeat
语法：
标签： REPEAT
循环体;
UNTIL 结束循环的条件
END REPEAT 标签;
循环控制
ITERATE 类似continue
LEAVE 类似break
left join==left outer join
a left join b 就是取a和b的交集加a剩下的部分
inner join
a inner join b就是取交集
8万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
