第1章 MySQL安装和卸载
1.1 MySQL卸载
/*
1.关闭MySQL服务
此电脑 -> 右键 -> 管理 -> 服务和应程序 -> 服务 -> MYSQL80(不同mysql版本名字不一样)
---> 右键 --> 停止
2.卸载MySQL
2.1 打开控制面板 -> 程序 -> 卸载程序 -> MySQL(双击 或 右键-卸载)
2.2 将MySql安装目录中的配置文件和数据全部删除(可选)
3.再次打开服务查找MySQL服务是否还在 - 如果还在必须通过命令提示符删除
右键以管理员身份打开命令提示符 -> 写命令: sc delete 服务名
=========================================================================
4.清理注册表(自己百度-不同的版本都不一样)
5.重装系统
*/
1.2 MySQL安装
详见安装文档
第2章 MySQL概述
/*
1.MySQL作用:①持久化保存数据 ②对数据可以更方便的进行增,删,改,查的操作
2.概念
DB : 数据库
DBMS :数据库管理系统(MySQL,Oracle,SQLServer,......)
SQL:结构化查询语言(用来操作数据库)
3.常见的数据库管理系统 :MySQL,Oracle,SQLServer,......
4.SQL的优点:①简单易学 ②所有的关系性数据库全都支持 ③虽然简单但是一种强有力的语言
5.SQL(Structural query language)语句分为以下三种类型:
DML: 数据操纵语言
DDL: 数据定义语言
DCL: 数据控制语言
*/
第3章 DML
3.1 查询语句
#查询所有的库
show databases;
#查询所有的表
show tables;
#选库
use 库名;
#查表中的内容
select 字段名1,字段名2,.....
from 表名;
#查看所有的字段
select *
from 表名;
#理解成输出语句
select 1+1;
#dual是一张虚拟表(伪表)可以省略不写
select 1+1
from dual;
select 3 >> 1;
#注意:任意数据类型和null做运算结果为null
select 1+null;
#注意:不要理解成字符串拼接-要想字符串拼接要用函数
select "a"+1;#结果是1 - 因为a无法转换成具体的数值所以用0替代
3.2 字段的别名
/*
格式:
select 字段名1 as 别名1,字段名2 别名2,字段名3 "别 名 3",字段名3 '别 名 3',字段名3 `别 名 3`
from 表名;
注意:如果别名中间有空格那么需要使用双引号,单引号或飘号引起来。
一般别名中间不会有空格。
*/
#给字段起别名
SELECT salary+1000 AS new_salary,
salary+2000 new_salaray2,
salary+1 "new salary", #别名中间不要出现空格
salary+2 'new salary2',
salary+3 `new salary3`,
salary+4 薪水
FROM employees;
3.3 注意
/*
注意:
1.SQL 语言大小写不敏感。
2.SQL 可以写在一行或者多行
3.关键字不能被缩写也不能分行
4.各子句一般要分行写。
5.使用缩进提高语句的可读性。
6.每条sql语句最后以分号结尾
7.库名不能相同,同一个库中表名不能相同。
*/
3.4 过滤
/*
select 字段名1,字段名2,....
from 表名
where 过滤条件
*/
#需求:查询部门号为50的员工有哪些
SELECT first_name,department_id
FROM employees
WHERE department_id=50; #注意:=不是赋值符号 是等于
#需求:查询员工Steven的信息
SELECT *
FROM employees
WHERE first_name="Steven";
#需求:查询员工StevenKing的信息
SELECT *
FROM employees
#where first_name='Steven' && last_name='King';
WHERE first_name='Steven' AND last_name='King'; #推荐and
#需求:查询除90号部门外其它部门的员工信息
SELECT *
FROM employees
#where department_id != 90;
WHERE department_id <> 90; #推荐 <>
#需求:查询奖金率是null的员工信息
SELECT first_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
#需求:查询奖金率不是null的员工信息
SELECT first_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#需求:查询薪水在5000到8000之间的员工信息(包含边界)
SELECT first_name,salary
FROM employees
#where salary>=5000 and salary<=8000;
#BETWEEN...and...是包含边界
WHERE salary BETWEEN 5000 AND 8000;#相当于salary>=5000 and salary<=8000
/*
使用between...and...要注意的点
1.BETWEEN...and...是包含边界
2.between后面是小的值 and后面是大的值
*/
#下面的写法是错误的
SELECT first_name,salary
FROM employees
WHERE salary BETWEEN 8000 AND 5000;##相当于salary>=8000 and salary<=5000
#需求:查询薪水不在5000到8000之间的员工信息
SELECT first_name,salary
FROM employees
#where salary < 5000 || salary > 8000;
#where salary<5000 or salary>8000;#推荐使用or
WHERE salary NOT BETWEEN 5000 AND 8000;
#需求:查询部门号90和80号部门的员工信息
SELECT first_name,department_id
FROM employees
#where department_id=90 or department_id=80;
WHERE department_id IN(80,90);#department_id=90 or department_id=80
#需求:查询部门号除90和80号部门的员工信息
#注意:not in 不包含null值
SELECT first_name,department_id
FROM employees
WHERE department_id NOT IN(80,90);
3.5 运算符
3.6 模糊查询
#需求:查询姓名中包含f的员工信息
SELECT first_name,last_name
FROM employees
WHERE first_name LIKE '%f%'; #%表示任意个数的任意字符
#需求:查询名字中第二个字符为d的员工信息
SELECT first_name
FROM employees
WHERE first_name LIKE '_d%';#_表示任意一个字符
#需求:查询名字中第二个字符为_的员工信息
SELECT first_name
FROM employees
WHERE first_name LIKE '_\_%';# \_转义字符表示内容为_
#替换转义字符
SELECT first_name
FROM employees
WHERE first_name LIKE '_$_%' ESCAPE '$'; #ESCAPE 指定某个字符为转义字符
#需求:查询名字中包含a又包含e的员工信息
#写法一
SELECT first_name
FROM employees
WHERE first_name LIKE '%a%e%' OR first_name LIKE '%e%a%';
#写法二
SELECT first_name
FROM employees
WHERE first_name LIKE '%a%' AND first_name LIKE '%e%';
3.7 排序
/*
select 字段名1,字段名2,.....
from 表名
where 过滤条件
order by 字段名1 asc/desc,字段名2 asc/desc,.......
说明:
1.asc表示升序,desc表示降序。
2.如果字段名后面没有指定升序还是降序默认是升序。
*/
#需求:查询所有员工的姓名和薪水并对薪水进行排序-升序
SELECT first_name,salary
FROM employees
ORDER BY salary ASC;
SELECT first_name,salary
FROM employees
ORDER BY salary;-- 注意:如果order by后面的字段没有指定排序方式默认是升序
#需求:查询50号部门所有员工的姓名,工种和薪水 并按照薪水排序-降序
SELECT department_id,first_name,job_id,salary
FROM employees
WHERE department_id=50
ORDER BY salary DESC;
#需求:对所有员工的薪水加1000 并对新的薪水进行排序-降序
SELECT salary,salary+1000 new_sal
FROM employees
ORDER BY new_sal DESC; -- order by后面可以使用别名
SELECT salary,salary+1000
FROM employees
ORDER BY `salary+1000` DESC; -- salary+1000是字段名
#需求:查询所有员工的姓名,薪水及部门号。要求按照部门号排序-降序 如果部门号相同再按照薪水排序-升序
SELECT first_name,department_id,salary
FROM employees
ORDER BY department_id DESC,salary ASC;
3.8 去重
/*
distinct : 去重
格式:
distinct 字段名1,字段名2,.......
*/
#需求:查询员工所在的部门有哪些
SELECT DISTINCT department_id
FROM employees
WHERE department_id IS NOT NULL
SELECT DISTINCT department_id, job_id #两个字段都一样再去重
FROM employees
WHERE department_id IS NOT NULL
#语法上错误
SELECT DISTINCT department_id,DISTINCT job_id
FROM employees
WHERE department_id IS NOT NULL
3.9 多表查询
/*
多表查询:当查询的多个字段不在同一张表时就需要用到多表查询。
连接条件分类:
自连接 vs 非自连接
内连接 vs 外连接
等值连接 vs 非等值连接
连接语句分类 :sql92语法和sql99语法
sql92语法
select 表名.字段名1,表名.字段名2,...
from 表1 表别名1,表2 表别名2,......
where 连接条件
*/
#sql92语法
#需求:查询每个员工的姓名和所在的部门名称
SELECT first_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;#连接条件
#sql92语法
#需求:查询每个员工的姓名,部门号和所在的部门名称
/*
在多表查询中如果某一个字段只出现在某一张表中那么该字段前的表名可加可不加。
如果某一个字段出现在多张表中那么该字段前面的表名必须加
结论:在多表查询中字段名前最好加上表名因为效率高
*/
SELECT employees.first_name,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;
#给表起别名
SELECT e.first_name,d.department_name,e.department_id
FROM employees e,departments d
WHERE e.department_id=d.department_id;
#缺少连接条件 发生了笛卡尔集错误
SELECT e.first_name,d.department_name
FROM employees e,departments d
#需求:查询每个员工的姓名,部门名称和部门所在城市的名称
SELECT e.first_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.location_id;
/*
sql99语法
select 字段名1,字段名2,.....
from 表名1 join/left join/right join/full join 表名2
on 连接条件
join/left join/right join/full join 表名3
on 连接条件
......
where 过滤条件
order by 字段名1 asc/desc,字段名2 asc/desc,......
*/
#内连接:获取两张表中匹配的内容
#等值连接:连接的条件用的是等号
#非自连接:连接的表不是同一张表
#sql99语法
#需求:查询每个员工的姓名和所在的部门名称
SELECT e.`first_name`,d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#sql99语法
#需求:查询每个员工的姓名和所在的部门名称及部门所在的城市名称
SELECT e.`first_name`,d.`department_name`,l.`city`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;
#自连接:连接的表是同一张表
#需求:查询员工的名字及领导的名字
SELECT e1.`first_name` 员工名字,e2.`first_name` 领导的名字
FROM employees e1 JOIN employees e2 #e1当成员工表 e2当成领导表
ON e1.`manager_id`= e2.`employee_id`;
SELECT e2.`first_name` 员工的名字,e1.`first_name` 领导的姓名
FROM employees e1 JOIN employees e2 #e1当成领导表 e2当成员工表
ON e1.`employee_id` = e2.`manager_id`;
#非等值连接:连接条件用的不是等号
#需求:查询员工的姓名,薪水和薪水等级
SELECT e.`first_name`,e.`salary`,j.`GRADE`
FROM employees e JOIN job_grades j
#on e.`salary` >= j.`LOWEST_SAL` and e.`salary` <= j.`HIGHEST_SAL`;
ON e.`salary` BETWEEN j.`LOWEST_SAL` AND j.`HIGHEST_SAL`;
#左外连接:除了两张表中匹配的内容外还包括左表中不匹配的内容
#需求:查询所有员工的姓名和他们的部门名称
SELECT e.`first_name`,e.`department_id`,d.`department_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右外连接:除了两张表中匹配的内容外还包括右表中不匹配的内容
#需求:查询所有的部门名称及部门中的员工姓名
#注意:右外连接完全可以通过左外连接实现(只需要交换两张表的位置即可)
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#满外连接:除了两张表中匹配的内容还包括左表和右表中不匹配的内容
#mysql不支持(oracle支持)
/*
实现full join:
左外连接
union
右外连接;
union(去重):将两张表的结果合成一张表。
union all(不去重):将两张表的结果合成一张表。
*/
#需求:查询所有的员工姓名及所有的部门名称(除了匹配的 还包括 没有部门的员工 及 没有员工的部门)
/*
使用union时要注意的点:
1.union去重
2.两张表合成一张表那么这两张表的字段的个数和类型要保持一致。
*/
SELECT e.`employee_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION
SELECT e.`employee_id`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#union all:不去重
SELECT e.`employee_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT e.`employee_id`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
3.10 函数
单行函数
/*
LOWER('SQL Course') :将内容全部转成小写
UPPER('SQL Course') :将内容全部转成大写
*/
SELECT LOWER('aAbBcC'),UPPER('aAbBcC');
SELECT LOWER(first_name),UPPER(last_name)
FROM employees;
/*
CONCAT('Hello', 'World') : 字符串拼接
SUBSTR('HelloWorld',1,5) : 截取子串 1是索引位置 5是内容的长度
注意:索引是从1开始
LENGTH('HelloWorld') : 字符串的长度
INSTR('HelloWorld', 'W') : w在字符串中首次出现的位置-从左向右查找
LPAD(salary,10,'*') :
当字段的内容的长度不够10的时候用*补在左边补
RPAD(salary, 10, '*'):
当字段的内容的长度不够10的时候用*补在右边补
TRIM('H' FROM 'HelloWorld') :去除字符串两端指定的字符
REPLACE('abcd','b','m') : 将当前字符串中b替换成m
*/
SELECT CONCAT(first_name,last_name)
FROM employees;
SELECT SUBSTR('abcdef',2,2);
SELECT first_name,LENGTH(first_name)
FROM employees;
SELECT INSTR('abwccwc','w');
SELECT LPAD(first_name,10,"*"),RPAD(last_name,10,"*")
FROM employees;
SELECT TRIM('#' FROM '####a####c####');
SELECT REPLACE('abcdaaa','a','A');#mysql不区分大小写-关键字(sql语句) 数据(内容)是区分大小写的
/*
ROUND: 四舍五入
ROUND(45.926, 2) 45.93
TRUNCATE: 截断
TRUNCATE(45.926,0) 45
MOD: 求余
MOD(1600, 300) 100
*/
SELECT ROUND(45.926, 2),ROUND(45.926, 1),ROUND(45.926, 0),ROUND(45.926, -1);
SELECT TRUNCATE(45.926,2), TRUNCATE(45.926,1), TRUNCATE(45.926,0), TRUNCATE(45.926,-1);
#结果的正负和第一个参数的正负有关
SELECT MOD(3,2),MOD(-3,2),MOD(3,-2),MOD(-3,-2);
SELECT NOW();#当前日期和时间
SELECT VERSION(); #查看mysql版本
/*
ifnull(字段名,默认值):如果字段的内容为null就用默认值替换
*/
#需求:查询所有员工的薪水(薪水+奖金)
SELECT first_name,salary,commission_pct,salary+salary*commission_pct#null参与运算
FROM employees;
SELECT first_name,salary,commission_pct,salary+salary*IFNULL(commission_pct,0)
FROM employees;
case语句
/*
case表达式:
格式1:
case 字段名
when 值1 then 返回值1
when 值2 then 返回值2
when 值3 then 返回值3
......
else 返回值n
end
格式2:
case
when 表达式1 then 返回值1
when 表达式2 then 返回值1
when 表达式3 then 返回值1
......
else 返回值n
end
*/
#需求:练习:查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍,
# 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
SELECT first_name,department_id,salary,CASE department_id
WHEN 10 THEN salary*1.1
WHEN 20 THEN salary*1.2
WHEN 30 THEN salary*1.3
ELSE salary
END "别名1"
FROM employees
WHERE department_id IN(10,20,30);
#需求:查询所有员工的姓名,薪水及描述信息(如果薪水大于10000输出会所嫩模,
# 小于10000输出下海干活 等于10000输出再接再厉)
SELECT first_name,salary,CASE
WHEN salary>10000 THEN "会所嫩模"
WHEN salary<10000 THEN "下海干活"
ELSE "再接再厉"
END "别名"
FROM employees;
多行函数(组函数-聚合函数)
/*
AVG():求平均数
SUM():求和
注意:只能对数值类型(整型,浮点型)的数据操作
MAX():求最大值
MIN() :求最小值
COUNT() :求数据的条数
*/
#需求:求员工薪水的总和,平均值,最高薪水,最低薪水
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary)
FROM employees;
#注意:select后面一旦出现组函数(聚合函数,分组函数)就不能再出现其它字段。
# 除非该字段在group by的后面
/*
下面的写法都是错的
select first_name,avg(salary)
from employees;
select first_name,min(salary)
from employees;
SELECT first_name,max(salary)
FROM employees;
*/
/*
count(*) : 用来统计查询结果的数据的总条数。
count(字段名) :用来统计查询结果中该字段不为null的有多少条数据
count(数值) : 理解成count(*)
count(*):如果一条数据中所有字段全为null 不统计该条数据
count(1):如果一条数据中所有字段全为null 统计该条数据
*/
#查询employees表中有多少条数据
SELECT COUNT(*)
FROM employees;
#查询50号部门有多少人
SELECT COUNT(*)
FROM employees
WHERE department_id=50;
SELECT COUNT(employee_id),COUNT(commission_pct)
FROM employees;
#奖金率不为null的有多少人
SELECT COUNT(*)
FROM employees
WHERE commission_pct IS NOT NULL;
SELECT COUNT(salary),COUNT(commission_pct)
FROM employees
WHERE department_id=60
SELECT first_name,salary,COUNT(2)
FROM employees;
#注意:avg在求平均值时有没有包括null? 不包括null
SELECT AVG(commission_pct),SUM(commission_pct)/107,SUM(commission_pct)/35
FROM employees;
3.11 分组和过滤
/*
select 字段名1,字段名2,.......
from 表名
where 过滤条件
group by 字段名1,字段名2,...
having 过滤条件
order by 字段名1 asc/desc,字段名2 asc/desc,.....
select 字段名1,字段名2,.......
from 表名1 join 表名2
on 连接条件
join 表名3
on 连接条件
.......
where 过滤条件
group by 字段名1,字段名2,...
having 过滤条件
order by 字段名1 asc/desc,字段名2 asc/desc,.....
where和having的区别?
1.where在分组前过滤。having是在分组后过滤。
2.where后面不可以用组函数。having后面可以用组函数。
3.如果没有分组(group by)不要使用having进行过滤用where
*/
#需求:查询各部门平均薪水-没有部门的不要。
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id; #按照部门分组
#需求:查询50号部门不同工种的最高薪水
SELECT job_id,MAX(salary)
FROM employees
WHERE department_id = 50
GROUP BY job_id;
#需求:查询不同部门不同工种的最低薪水是多少
SELECT department_id,job_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id;
#需求:查询不同部门不同工种的最低薪水是多少并按照最低薪水排序-升序
SELECT department_id,job_id,MIN(salary) min_salary
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
ORDER BY min_salary ASC;
#需求:查询各部门的平均薪水 并且只要平均薪水大于5000的部门
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > 5000; #最好不要用别名-和hive有关系
SELECT department_id,AVG(salary) avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING avg_sal > 5000;
#需求:查询各部门最高薪水 部门为null的不要
#方式一:效率高-能放在where后面过滤的尽量放在where后面过滤掉
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
#方式二:
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING department_id IS NOT NULL;
#需求:查询各部门的平均薪水 并且只要平均薪水大于5000的部门
#该方式效率高
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > 5000; #注意:AVG(salary)是在后组后得到的 所以要对此字段过滤只能在分组后过滤
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id IS NOT NULL AND AVG(salary) > 5000;
3.12 子查询
概述
/*
子查询:在一条查询语句a中再嵌套一条查询语句b。那么b语句叫作子查询(内查询)a语句叫作主查询(外查询)
子查询的分类:单行子查询 vs 多行子查询
单行子查询:子查询返回的结果只有一条。
多行子查询:子查询返回的结果有多条
单行子查询所使用运算符 :> >= < <= = <>
多行子查询所使用运算符: in any all
子查询是由内向外去写(先这与内查询再写外查询)。
*/
单行子查询
#需求:谁的工资比 Abel 高?
#方式一:
#1.先查询Abel工资
SELECT salary
FROM employees
WHERE last_name='Abel'; #11000
#2.查询比11000高的薪水的员工信息
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式二:多表查询
SELECT e1.`last_name`,e1.`salary`
FROM employees e1 JOIN employees e2
ON e1.`salary` > e2.`salary` AND e2.`last_name`='Abel';
#方式三:子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
#单行子查询:子查询结回的结果只有一条
#注意:子查询查询的结果只能有一个字段
SELECT salary
FROM employees
WHERE last_name='Abel'
);
#===================================================
#题目:返回job_id与141号员工相同,salary比143号员工多的员工
# 姓名,job_id 和工资
#方式一:
#1.查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141;#ST_CLERK
#2.查询143号员工的薪水
SELECT salary
FROM employees
WHERE employee_id=143;#2600
#3.查询job_id为ST_CLERK薪水比2600高的员工信息
SELECT first_name,job_id,salary
FROM employees
WHERE job_id='ST_CLERK' AND salary>2600;
#方式二:子查询
SELECT first_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
);
#==================================================
#题目:返回公司工资最少的员工的last_name,job_id和salary
#方式一
#1.最少工资是多少
SELECT MIN(salary)
FROM employees; #2100
#2.查询工资为2100的员工信息
SELECT last_name,job_id,salary
FROM employees
WHERE salary=2100;
#方式二:
#2.查找最低薪水的员工信息
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
#1.先查找最低薪水
SELECT MIN(salary)
FROM employees
)
/*
#注意:下面的写法不对
SELECT last_name,job_id,salary
FROM employees
WHERE salary=min(salary) #where后面不要使用组函数
*/
#===========================================================
#题目:查询最低工资大于50号部门最低工资的部门id和其最低工资
#2.查询各部门最低工资比2100低的
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
#1.查询50号部门最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);
#注意:子查询返回的是一个空值(不是null)不是错误只不过是没有查询到匹配的内容。
SELECT first_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=1410
)
#================================================================
#下面的sql语句会报错
SELECT first_name
FROM employees
WHERE salary > ( #子查询返回的结果为多条数据 但是使用的运算符是单行子查询使用的运算符
SELECT salary
FROM employees
WHERE department_id = 50
)
多行子查询
#需求:返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员
# 工号、姓名、job_id 以及salary
#2.查询其它工种员工的薪水比工种为IT_PROG的员工的薪水任意一个低
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary <ANY(
#1.查询工种为IT_PROG的员工的薪水
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
#题目:返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
# 的员工号、姓名、job_id 以及salary
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary <ALL(
#1.查询工种为IT_PROG的员工的薪水
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
#7. 查询工资比80号部门平均工资高的员工的员工号,姓名和工资。
SELECT employee_id,first_name,salary
FROM employees
WHERE salary > (
#80号部门平均工资
SELECT AVG(salary)
FROM employees
WHERE department_id=80
);
#10.查询姓名中包含f的员工的相同部门的员工有哪些
SELECT first_name,department_id
FROM employees
WHERE department_id IN(
#查找姓名中包含f的员工所在的部门
SELECT department_id
FROM employees
WHERE first_name LIKE '%f%'
);
#11.查询各工种最低员工薪水的薪水等级
SELECT t1.*,t2.grade
FROM(
#查询各工种最低薪水
SELECT job_id,MIN(salary) min_salary
FROM employees
GROUP BY job_id
) t1 JOIN job_grades t2
ON t1.min_salary BETWEEN t2.lowest_sal AND t2.Highest_sal;
#12.查询各部门(没有部门不要)员工最高薪水的员工的信息。
SELECT t1.*,t2.`department_id`,t2.`salary`,t2.`first_name`
FROM(
#查询各部门最高薪水
SELECT department_id,MAX(salary) max_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
) t1 JOIN employees t2
ON t1.department_id = t2.`department_id` AND t1.max_sal = t2.`salary`;
#13.查询30号部门薪水最低的员工的邮箱是多少
SELECT t2.*
FROM(
#查询号部门最低薪水
SELECT MIN(salary) min_salary
FROM employees
WHERE department_id=30 #2500
) t1 JOIN employees t2
ON t1.min_salary=t2.`salary` AND t2.`department_id`= 30;
SELECT *
FROM employees
WHERE salary = (
SELECT MIN(salary) min_salary
FROM employees
WHERE department_id=30
) AND department_id = 30;
3.13 向表中插入数据
#插入单条数据
insert into 表名(字段名1,字段名2,.....) values(值1,值2,......);
#注意:如果插入的是全字段那么表名后面的字段名可以不写
insert into 表名 values(值1,值2,......);
#插入多条数据
insert into 表名(字段名1,字段名2,.....) values(值1,值2,......),(值1,值2,......),......;
#将查询的结果插入到表中
#要插入的字段和查询的字段的个数和类型要匹配。
insert into 表名(字段名1,字段名2,.......)
select 字段名1,字段名2,.......
from xxxx
xxxx;
3.14 删除表中的数据
#删除数据:
delete from 表名 [where 过滤条件]
#删除表中所有内容
delete from 表名
3.15 修改表中的数据
#修改表中的数据
update 表名 set 字段名1=值1,字段名2=值2,..... [where 过滤条件]
#修改的表中所有的内容
update 表名 set 字段名1=值1,字段名2=值2,.....
第4章 DDL
4.1 库的操作
#查看所有的库
show databases;
#选库
use 库名
#创建库
create database [if not exists] 库名 [character set '编码集'];
#[if not exists] :如果库不存在则创建 存在则不创建。如果没有该字段库存在则报错
create database [if not exists] 库名;
# [character set '编码集']:设置库的编码集
#注意:utf8默认是utf8mb3
create database 库名 [character set '编码集'];
#修改库的编码集
alter database 库名 character set '编码集';
#删除库
#[if exists]:如果库存在则删除 不存在则不删。如果没有该字段库不存在则报错。
drop database [if exists] 库名:
4.2 表的操作
#查看所有的表
show tables;
#查看表中的字段的信息
desc 表名;
#查看表的信息
show create table 表名;
#创建表的方式一:
create table [if not exists] 表名(
字段名 字段类型 [约束],
字段名2 字段类型 [约束],
字段名3 字段类型 [约束],#如果字段下面没有其它内容那么逗号就要省略
[表级约束]
) [character set '编码集'] #表默认的编码集和库的编码集一致
#创建表的方式二:基于现有的表创建新表(没有数据)
create table [if not exists] 表名(不存在-新表) like 表名(已经存在的表的表名)
#创建表的方式三:基于查询结果创建新表
create table [if not exists] 表名
select xxx
xxxx;
#删除表
# [if exists] : 如果表存在则删除不存在则不删。如果没有该字段表不存在则报错。
drop table [if exists] 表名;
4.3 字段的操作
#添加一个字段
alter table 表名 add [column] 字段名 字段类型;
#删除一个字段
alter table 表名 drop [column] 字段名;
#修改字段的名字
alter table 表名 change [column] 原字段名 新字段名 字段的类型;
#修改字段的类型
alter table 表名 change [column] 字段名 字段名 字段的新类型;
alter table 表名 modify [column] 字段名 字段的新类型;
#修改表的名字
alter table 原表名 rename to 新表名;
4.4 数据类型
4.5 在配置文件中修改编码集
在mysql的安装目录中有一个文件my.ini - 配置文件
/*
1.找到安装目录下的my.ini文件
2.在[mysqld]下面添加:
character-set-server=utf8
collation-server=utf8_general_ci
3.在[client]下面添加:(如果没有[client]字段,先添加该字段)
default-character-set=utf8
4.重启mysql服务
*/
4.6 mysql中的命名规则
/*
1.数据库名不得超过30个字符,变量名限制为29个
2.必须只能包含 A–Z, a–z, 0–9, _共63个字符
3.不能在对象名的字符间留空格
4.必须不能和用户定义的其他对象重名
5.必须保证你的字段没有和保留字、数据库系统或常用方法冲突
6.保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。
假如数据类型在一个表里是整数,那在另一个表里就不要变成字符型了
7.各单词之间有用下划线分隔开
*/
第5章 DCL
5.1 事务
事务:事务可以将多个操作绑定在一起变成一个操作单元
-在执行时要么都成功要么都失败不允许有的成功有的失败
/*
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
案例:AA给CC转账1000
try{
开启事务
aa -= 1000;
System.out.println(1/0);
cc += 1000;
事务提交 - 一旦提交就不能回滚了
}catch(Exception e){
事务回滚
}
发生的问题:一个成功 一个失败。
解决思路:将多个操作看成一个整体要么都成功要么都失败-不允许有成功有失败的。
注意:对数据的操作才可以使用事务
*/
#开启事务-方式一
SET autocommit=FALSE;
#开启事务-方式二(一旦commit或rollback事务自动结束)
START TRANSACTION;
#具体的操作
DELETE FROM account;
#事务提交-一旦提交将不能再回滚
COMMIT;
#事务回滚
ROLLBACK;
#关闭事务
SET autocommit=TRUE;
5.2 truncate table
#清空表中的内容
truncate table 表名;
delete from 表名;
/*
delete from 和 truncate table的区别?
1.delete from可以使用事务。truncate table不能使用事务。
2.如果可以确认删除的数据不需要回滚用truncate table效率高。
*/
START TRANSACTION;
#清空表中的内容
TRUNCATE TABLE account;
ROLLBACK;
第6章约束
6.1约束的分类
六种约束:
NOT NULL 非空约束,规定某个字段不能为空
UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)
FOREIGN KEY 外键
CHECK 检查约束
DEFAULT 默认值
约束分类:列级约束 vs 表级约束
列级约束:同时只能约束一列
表级约束:同时可以约束多列
6.2在创建表时添加列级约束
#创建表时添加列级约束
CREATE TABLE stu(
id INT PRIMARY KEY,#主键约束 = 非空且唯一
sid INT UNIQUE, #唯一约束
NAME VARCHAR(20) NOT NULL, #非空约束
score INT CHECK(score > 60),#check约束
age INT DEFAULT 18 #默认约束
);
DESC stu;
INSERT INTO stu(id,sid,NAME,score,age) VALUES(1,1000,'a',70,20);
INSERT INTO stu(id,sid,NAME,score,age) VALUES(2,1001,'b',71,21);
INSERT INTO stu(id,sid,NAME,score,age) VALUES(3,1002,'c',72,22);
INSERT INTO stu(id,sid,NAME,score,age) VALUES(4,1003,'d',73,23);
INSERT INTO stu(id,sid,NAME,score,age) VALUES(5,1004,'e',61,25);
INSERT INTO stu(id,sid,NAME,score) VALUES(6,1005,'f',62);
6.3在创建表时添加表级约束
/*
注意:default 和not null只有列级约束
*/
#在创建表时添加表级约束
#主键
CREATE TABLE stu2(
id INT,
sid INT,
NAME VARCHAR(20),
#添加主键的表级约束 :CONSTRAINT 索引名 PRIMARY KEY(字段名1,字段名2,......)
CONSTRAINT stu2_id_sid PRIMARY KEY(id,sid)
);
DESC stu2;
INSERT INTO stu2(id,sid) VALUES(1,2);
INSERT INTO stu2(id,sid) VALUES(1,3);
#唯一
CREATE TABLE stu3(
#
id INT,
sid INT,
NAME VARCHAR(20),
#添加unique的表级约束 :CONSTRAINT 索引名 UNIQUE(字段名1,字段名2,......)
CONSTRAINT stu3_id_sid UNIQUE(id,sid)
);
DESC stu3;
INSERT INTO stu3(id,sid) VALUES(1,2);
INSERT INTO stu3(id,sid) VALUES(1,3);
#check
CREATE TABLE stu4(
id INT,
sid INT,
CHECK(id > 10 AND sid > 20)
);
INSERT INTO stu4(id,sid) VALUES(11,21);
INSERT INTO stu4(id,sid) VALUES(13,20);
6.4 在创建表后添加约束
#创建表后添加约束
CREATE TABLE stu5(
id INT,
sid INT,
NAME VARCHAR(20),
age INT,
score DOUBLE(5,2)
);
/*
primary key
添加约束 : alter table 表名 add primary key (字段名)
修改约束 : alter table 表名 modify 字段名 类型 primary key
删除约束 : alter table 表名 drop primary key
*/
ALTER TABLE stu5 ADD PRIMARY KEY(id);
ALTER TABLE stu5 DROP PRIMARY KEY;
ALTER TABLE stu5 MODIFY id INT PRIMARY KEY;
DESC stu5;
/*
unique:
添加约束 : alter table 表名 add unique(字段名)
添加约束 : alter table 表名 add constraint 索引名 unique(字段名)
修改约束 :alter table 表名 modify 字段名 类型 unique
删除约束 :alter table 表名 drop index 索引名
*/
ALTER TABLE stu5 ADD UNIQUE(sid); #sid就是索引名
ALTER TABLE stu5 ADD CONSTRAINT stu5_age_name UNIQUE(age,NAME);
ALTER TABLE stu5 DROP INDEX stu5_age_name;
ALTER TABLE stu5 DROP INDEX sid;
DESC stu5;
/*
not null
添加约束:alter table 表名 modify 字段名 字段类型 not null
删除约束 :alter table 表名 modify 字段名 字段类型 null
*/
/*
default:
设置默认约束 : alter table tb_name alter 字段名 set default value;
删除默认约束 : alter table tb_name alter 字段名 drop default;
*/
/*
foreign key:
添加约束 : ALTER TABLE 表名 ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);
删除约束 :alter table 表名 drop foreign key 索引名
*/
6.5外键约束
AUTO_INCREMENT
CREATE TABLE emp(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20)
);
INSERT INTO emp(NAME) VALUES('a'); #不需要向主键插入数据因为主键设置成了自增-自动添加数据
案例
/*
外键约束
1.先创建主表还是从表?先创建主表再创建从表
2.插入数据时先往主表还是从表?先往主表中插入数据再往从表中插入数据
3.删除数据时先删除主表还是从表?先删除从表再删除主表
*/
CREATE DATABASE d1;
#主表
CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);
#从表
CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
#外键约束:该员工的部门号必须是dept表中有的部门号
#格式:CONSTRAINT 索引名 FOREIGN KEY(本表中的字段名) REFERENCES 主表的表名(主表的字段名)
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);
#插入数据:先往主表中插入数据再往从表中插入数据
INSERT INTO dept(dept_id,dept_name) VALUES(10,'IT');
INSERT INTO emp(last_name,dept_id) VALUES('longge',10);
#删除数据
DELETE FROM emp;
DELETE FROM dept;
级联删除
#级联删除 :当我们删除部门表中的数据时该部门中所有的员工将被直接删除
#主表
CREATE TABLE dept2(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);
#从表
CREATE TABLE emp2(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
#ON DELETE CASCADE: 级联删除
CONSTRAINT emp2_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept2(dept_id) ON DELETE CASCADE
);
#级联删除--当我们删除10号部门时 10号部门中所有的员工也将被删除掉
DELETE FROM dept2 WHERE dept_id=10;
第7章 分页
/*
limit 索引值,数据的条数
分页公式 :(页数-1)*数据的条数,数据的条数
*/
#需求:查询表中的前十条数据
SELECT *
FROM employees
LIMIT 0,10;
#再拉取十条
SELECT *
FROM employees
LIMIT 10,10;
#再拉取十条
SELECT *
FROM employees
LIMIT 20,10;