文章目录
数据库MySQL
一、数据库的概念
1.1 现阶段数据存放方式
瞬时状态:指数据存储到内存中
持久化状态:使用文件存储(IO流、对象序列化)
1.2 现有方式的缺点
- 数据类型
- 数据量级
- 安全机制
- 备份、恢复机制
- 查询搜索
1.3 新的文件类型
能否使用excel之类的表格来存储?
可以,但是excel表格能够清晰的描述数据的结构,却难以进行复杂的查询,而且excel用代码操作也极为不便利。
1.4 数据库
数据库是按照一定的数据结构来组织、存储、管理的仓库。
1.4 分类
网状结构:
层次结构:
关系结构:以Table存储,多表之间建立关联关系,通过分类、合并、连接、选取等运算实现访问。是目前主流的数据库结构。
- Oracle:Oracle公司产品,企业级的专业性极强的大型数据库产品。服务收费。
- DB2:IBM公司产品,企业级的专业性极强的大型数据库产品。服务收费。
- MySQL:免费的数据库产品,目前仍旧是中小型企业和个人的首先数据库,也是个人学习的首选。
- SQL Server:微软公司的产品,早期只能应用于windows系统。
非关系结构:全称为Not only SQL,简写为no SQL,在处理高并发、高性能、海量数据时才使用,一般采用key-value方式存储。一般作为关系型数据库的补充。例如:MongoDB、Redis、ES等。
1.5 数据库管理系统
操作和管理数据库的软件,用来建立、管理和维护数据库。
用来对数据库的统一管理和控制。用户通过数据库管理系统来访问数据库中的数据。
二、MySQL
2.1 简介
是一个关系型数据库。
RDBMS:relation database management system关系型数据库管理系统
2.2 目录
bin:可执行文件
data:系统数据库
include:头文件
lib:支持的库
2.3 核心文件
my.ini:对数据库的核心配置的设置。
port=3306 # 端口号
default-character-set=utf8 # 默认字符集
default-storage-engine=INNODB # 默认存储引擎
max_connections=100 # 最大连接数
三、SQL语言
SQL:structured query language,结构化查询语言。
用来对数据库进行操作。
对数据的操作一般有增删改查。简称CRUD(create、read、update、delete)
四、MySQL操作
4.1 连接数据库
如果已经配置了环境变量:
输入 mysql -u root -p回车
输入root用户密码,然后回车,登录结束
如果没有配置环境变量,windows系统中,可以在开始菜单找到mysql的客户端命令行工具。打开输入密码即可。
4.2 基本命令
4.2.1 查看所有数据库
show databases; 查看MySQL中有哪些数据库
4.2.2 创建数据库
create database 数据库名称; 创建一个默认字符集的数据库。
create database 数据库名称 character set GBK; 创建一个字符集为GBK的数据库。
create database if not exists 数据库名称; 如果没有某个数据库就创建一个。
注意:在创建数据库时一定要指定字符集。
create database java2106 character set utf8;
4.2.3 查看创建的数据库信息
show create database 数据库名称;
4.2.4 修改数据库
alter database 数据库名称 character set 字符集; 修改字符集
alter database java2106 character set gbk;
4.2.5 查看当前所使用的数据库
select database(); 注意后面的括号,意味着此处是调用函数。
4.2.6 切换使用数据库
use 数据库名称;
4.2.7 删除数据库
drop database 数据库名称;
数据库结构的创建、修改、删除、查看等,使用的命令是:create、alter、drop、show
五、数据查询
数据库是由表构成,表又包含行和列。数据查询即对表的查询。查询结果以行和列的形式显示,可以看作为一张虚拟的表。
5.1 数据导入
show tables; 查看所有的表信息。
source sql文件地址; 导入一个sql文件。
create database companydb character set utf8; -- 创建数据库
use companydb; -- 切换使用数据库
source d:\companydb.sql; -- 导入一个sql文件内容到数据库。
show tables; -- 查询所有的表名
5.2 基本查询
select 列名 from 表名; 查询一个表中的数据,只查询相应的列。
select * from t_employees; -- 查询所有的列,所有的行。
select first_name, email from t_employees; -- 查询first_name, email两列。
对查询中的列进行运算:可以使用+、-、*、/、运算,但是不能使用%,%在sql中不是求余运算。
注意:+不能做拼接字符串操作。
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY * 12 FROM t_employees;
别名操作,可以给列起别名,语法结构是
select EMPLOYEE_ID AS 编号 from t_employees;
注意: AS 关键字可以省略。别名加不加单引号没有区别。
SELECT EMPLOYEE_ID id, FIRST_NAME '名', SALARY * 12 as 年薪 FROM t_employees;
distinct 对查询结果去重。
注意:如果有多列,需要多列的内容都相同才能去重。
SELECT DISTINCT MANAGER_ID FROM t_employees;
5.3 排序查询
将查询结果按照指定规则排序。
select 列名 from 表名 order by 排序列 排序规则;
排序规则有两种:
- ASC 默认值,升序(由小到大)
- DESC 降序(由大到小)
-- 按照单列排序
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees ORDER BY SALARY DESC;
多列排序:
先按照第一排序列的规则排序,如果有相同的列,则再按第二排序列的规则,以此类推。
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees ORDER BY SALARY DESC, EMPLOYEE_ID DESC;
上面的案例是表示先按照员工薪资排序,如果薪资一样,则将员工比较大的排在前面。
5.4 条件查询
语法:
select * from 表名 where 条件;
5.4.1 等值判断
SELECT * FROM t_employees WHERE SALARY = 11000;
SELECT * FROM t_employees WHERE FIRST_NAME = 'Steven';
注意:如果条件中比较的值是数字,可以不加单引号,也可以加,但是如果不是数字,是字符串或者时间等,必须加单引号。
等值比较时只需要一个等号即可。
5.4.2 逻辑判断
当有多个条件时,可以使用AND、OR来进行逻辑处理,可以使用NOT来取反。
-- 条件并列
SELECT * FROM t_employees WHERE SALARY = 11000 AND COMMISSION_PCT = 0.3;
-- 条件或者
SELECT * FROM t_employees WHERE FIRST_NAME = 'Steven' OR FIRST_NAME = 'Den';
-- 条件取反
SELECT * FROM t_employees WHERE NOT SALARY = 11000 ORDER BY SALARY DESC;
5.4.3 不等值判断
可以使用>、 < 、>=、 <=、 !=、 <>来进行不等值判断。
注意: !=与<>效果一样。
注意:字符串也可以使用不等值判断。先判断第一个字符,然后判断第二个。例如 ‘ac’ 大于’ab’
注意: 日期也可以使用不等值判断。后面的日期大于前面的日期,如果使用字符串的方式表示,也可以按照字符串的方式判断。例如:date > '2021’表示得到2021年后所有的数据。
SELECT * FROM t_employees WHERE SALARY <> 11000 ORDER BY SALARY DESC;
5.4.4 区间判断
使用between…and来实现区间判断。
相当于 列名 >= 较小值 AND 列名 <= 较大值
SELECT * FROM t_employees WHERE SALARY BETWEEN 11000 AND 12000;
-- 相当于
SELECT * FROM t_employees WHERE SALARY >= 11000 AND SALARY <= 12000;
注意:BETWEEN… AND区间必须先写小值,后面写大值,否则查询没有结果。
5.4.5 空值判断
列名 is NULL
列名 is not NULL
SELECT * FROM t_employees WHERE COMMISSION_PCT IS NULL; -- 不能用 = NULL
SELECT * FROM t_employees WHERE COMMISSION_PCT IS NOT NULL;
5.4.6 枚举查询
将要匹配的值列举出来,使用in关键字
语法:列名 IN (值1,值2, 值3…)
SELECT * FROM t_employees WHERE SALARY IN (8000,10000,6000);
-- 等同于
SELECT * FROM t_employees WHERE SALARY = 8000 OR SALARY = 10000 OR SALARY = 6000;
5.4.7 模糊查询
当条件不明晰时,可以使用模糊查询。
语法:列名 LIKE ‘A%’
使用
%
和_
作为通配符。%
表示0到多个字符,_
表示一个字符。注意: 列名 LIKE ‘%%’ 查询所有。
-- 表示名以Al开头,后面有3个字符的
SELECT * FROM t_employees WHERE FIRST_NAME LIKE 'Al___' ORDER BY FIRST_NAME;
-- 表示名以Al开头
SELECT * FROM t_employees WHERE FIRST_NAME LIKE 'Al%' ORDER BY FIRST_NAME;
-- 表示名包含Al
SELECT * FROM t_employees WHERE FIRST_NAME LIKE '%Al%' ORDER BY FIRST_NAME;
注意:如果在LIKE后面的条件中没有通配符,等同于=
5.4.8 分支查询
一些sql的含义:
SELECT 1 FROM t_employees; -- 表里有多少条记录,就会显示多少行数字1
SELECT `EMPLOYEE_ID` FROM t_employees; -- 表示查询EMPLOYEE_ID列的数据,`号一般用来区分关键字
SELECT name, `DESC` from users; -- 区分关键字
SELECT 'EMPLOYEE_ID' FROM t_employees; -- 表里有多少条记录,就会显示多少行字符串EMPLOYEE_ID
分支结构查询:
语法:
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
WHEN 条件2 THEN 结果2
ELSE 结果4
END
-- 根据薪资查询级别
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
CASE
WHEN SALARY >= 20000 THEN '高级'
WHEN SALARY >= 10000 THEN '中级'
WHEN SALARY >= 5000 THEN '初级'
ELSE '见习'
END 级别
FROM t_employees ORDER BY SALARY DESC;
5.4.9 时间函数
在MySQL中的时间函数,可以单独使用,也可以用在其他SQL语句中。
SELECT SYSDATE(); -- 得到当前的日期时间
SELECT CURDATE(); -- 得到当前的日期
SELECT CURTIME(); -- 得到当前的时间
SELECT NOW(); -- 得到当前的日期时间
SELECT WEEK('2021-08-02'); -- 得到指定日期是当年的第几周
SELECT WEEK(CURDATE()); -- 得到当前日期是当年的第几周(函数可以嵌套)
-- 得到日期时间中的年月日时分秒
SELECT YEAR(CURDATE());
SELECT MONTH(CURDATE());
SELECT DAY(CURDATE());
SELECT HOUR(NOW());
SELECT DATEDIFF('2021-06-03','2021-08-02'); -- 得到两个日期中间间隔的天数,结果是-60
SELECT ADDDATE(CURDATE(),80); -- 得到今天的80天后是哪一天
SELECT * FROM t_employees WHERE HIRE_DATE = ADDDATE(CURDATE(),-8200); -- 判断HIRE_DATE日期为今天的8200天前
5.4.10 字符串函数
在MySQL中的字符串函数,可以单独使用,也可以用在其他SQL语句中。
SELECT CONCAT(FIRST_NAME,',',LAST_NAME) 全名 FROM t_employees; -- 拼接字符串
-- 第一个参数是要改变的字符串,第二个参数是从哪个位置开始(下标从1开始),第三个参数是长度,最后一个参数是替换的字符串
SELECT INSERT(FIRST_NAME,3,3,'Hello') FROM t_employees WHERE EMPLOYEE_ID = 100; -- 将字符串Steven变成StHellon
SELECT LOWER(FIRST_NAME) FROM t_employees WHERE EMPLOYEE_ID = 100; -- 转小写
SELECT UPPER(FIRST_NAME) FROM t_employees WHERE EMPLOYEE_ID = 100; -- 转大写
SELECT SUBSTRING(FIRST_NAME,2) FROM t_employees WHERE EMPLOYEE_ID = 100; -- 截取字符串
5.5 聚合函数
类似于Excel中的公式。
sum():求和
avg():求平均数
max():求最大值
min():求最小值
count():计算行数
SELECT SUM(SALARY) FROM t_employees; -- 统计当月员工总工资和
SELECT avg(SALARY) FROM t_employees; -- 计算平均薪资
SELECT max(SALARY) FROM t_employees; -- 最高工资
SELECT min(SALARY) FROM t_employees; -- 最低工资
SELECT count(1) FROM t_employees; -- 查询行数,可以使用*或列名,使用数字1性能最好
SELECT count(1) FROM t_employees WHERE SALARY > 10000; -- 查询薪资大于10000的人数
5.6 分组查询
5.6.1 分组基础查询
分组查询是将数据按照指定列分成多组,然后按组进行数据统计。所以一般情况下,分组意味着需要聚合统计。
语法:
Group by 分组列
注意: 如果语句中有分组,那么前面查询的列,必须是聚合函数或者与分组列有一一对应关系的列。否则只会显示第一行记录,是无意义记录。
SELECT DEPARTMENT_ID,COUNT(1),AVG(SALARY),MAX(SALARY), MIN(SALARY), SUM(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID;
5.6.2 分组过滤查询
在分组后,如果还需要添加过滤条件,使用having关键字。
-- 先分组,再写条件使用having关键字
SELECT DEPARTMENT_ID, COUNT(1), MAX(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID HAVING DEPARTMENT_ID IN (30, 50, 80);
但是,上面的语句明显可以先写条件,再分组
-- 先写条件,再分组
SELECT DEPARTMENT_ID, COUNT(1), MAX(SALARY) FROM t_employees WHERE DEPARTMENT_ID IN (30, 50, 80) GROUP BY DEPARTMENT_ID ;
在项目中,必须要用having关键字的场景应该是使用聚合函数当条件。
查询平均薪资高于8000的部门。
-- 此时,将条件放到group by的前面,并使用where是不行的
SELECT DEPARTMENT_ID, AVG(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID HAVING AVG(SALARY) > 8000;
注意:having后面的聚合函数只能跟常量比较,不能写列名等,例如,下面的代码是错误的:
-- 查询部门大于平均薪资的人数,不能这么写
SELECT DEPARTMENT_ID, COUNT(1) FROM t_employees GROUP BY DEPARTMENT_ID HAVING SALARY > AVG(SALARY);
5.7 限定查询
显示一部分满足条件的结果。项目中通常用来进行分页显示。
语法:limit 跳过条数, 显示条数
-- 跳过5条,显示5条,即显示第6-10条
SELECT * FROM t_employees LIMIT 5, 5;
-- 如果是跳过0条,显示5条
SELECT * FROM t_employees LIMIT 0, 5;
-- 可以简写为:
SELECT * FROM t_employees LIMIT 5;
在项目中,如果有分页的业务,计算规则如下:
// page是页数
// size是每页显示的条数
public void findAll(int page, int size){
int skip = (page - 1) * size;
String sql = "select * from t1 limit " + skip + ", " + size;
}
5.8 基础查询顺序总结
SELECT 列名
FROM 表名
WHERE 条件
GROUP BY 分组列
HAVING 分组后条件
ORDER BY 排序列 排序规则
LIMIT 跳过条数, 显示条数
5.9 子查询
在查询语句中包含查询语句。
5.9.1 将查询结果当条件
例如:查询工资大于Bruce的员工信息。
-- 查询Bruce的工资信息,得到单行单列的数据(6000)
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';
-- 查询工资大于6000的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
-- 将两条语句组合,需要先查询的部分应该加上括号
SELECT * FROM t_employees WHERE SALARY > (SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce');
注意:需要结果能够作为查询条件。此处结果必须是单行单列。
5.9.2 查询结果为多行单列(枚举)
查询与’King’同一个部门的员工信息。
-- 查询King对应的部门
SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = 'King';
-- 结果有两个,不能直接用等号,需要使用in
SELECT * FROM t_employees WHERE DEPARTMENT_ID in (80, 90) ;
-- 将两条语句组合,需要先查询的部分应该加上括号
SELECT * FROM t_employees WHERE DEPARTMENT_ID in (SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = 'King') ;
5.9.3 ALL和ANY用法
查询大于60部门所有员工工资的员工信息。
-- 查询60部门的员工工资,结果是多行单列
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60;
-- 如果要大于60部门所有员工工资,其实只需要大于最高工资即可
SELECT * FROM t_employees WHERE SALARY > (SELECT max(SALARY) FROM t_employees WHERE DEPARTMENT_ID = 60);
-- 上面的写法可以实现结果,但是是人为分析的(算法),按照原始要求应该写为
SELECT * FROM t_employees WHERE SALARY > ALL (SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
查询大于60部门任意员工工资的员工信息。
-- 查询60部门的员工工资,结果是多行单列
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60;
-- 如果要大于60部门任意员工工资,其实只需要大于最低工资即可
SELECT * FROM t_employees WHERE SALARY > (SELECT min(SALARY) FROM t_employees WHERE DEPARTMENT_ID = 60);
-- 上面的写法可以实现结果,但是是人为分析的(算法),按照原始要求应该写为
SELECT * FROM t_employees WHERE SALARY > ANY (SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
ALL和ANY的区别:
ALL需要判断结果中多行是否全部满足条件,ANY只要结果中多行有一行满足条件即可。
5.9.4 将子查询作为条件再次查询
查询所有级别为高级的员工信息。(参考5.4.8)
先将员工级别使用分支查询的方式显示:
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
CASE
WHEN SALARY >= 20000 THEN '高级'
WHEN SALARY >= 10000 THEN '中级'
WHEN SALARY >= 5000 THEN '初级'
ELSE '见习'
END lv
FROM t_employees
尝试直接在后面加条件 WHERE lv = ‘高级’,结果报错,原因是别名是在查询出结果后添加的,而条件是作为查询过程中使用的,但是此时结果还没出来,根本就没有别名。
于是,需要将查询结果作为一个类似于虚拟表的方式再次查询。
SELECT * FROM
(SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
CASE
WHEN SALARY >= 20000 THEN '高级'
WHEN SALARY >= 10000 THEN '中级'
WHEN SALARY >= 5000 THEN '初级'
ELSE '见习'
END lv
FROM t_employees) as t1 WHERE lv = '中级';
注意:将查询结果作为虚拟表查询时,一定要给查询结果起别名,否则会报错。
5.10 合并查询
将两次查询结果合并,变成一个结果。
前面讲到in效率不高,应该使用其他办法。例如:
查询部门号为90和60的,如果使用in,如下:
-- 使用in
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN (90, 60);
可以将90和60拆分查询,并将结果合并:
SELECT * FROM t_employees WHERE DEPARTMENT_ID = 90
UNION ALL
SELECT * FROM t_employees WHERE DEPARTMENT_ID = 60;
-- 也可以使用
SELECT * FROM t_employees WHERE DEPARTMENT_ID = 90
UNION
SELECT * FROM t_employees WHERE DEPARTMENT_ID = 60;
UNION和UNION ALL的区别:
UNION会去掉重复的行。而UNION ALL会保留所有的行。
注意:必须要查询的列数一致才能合并,必须要一行中所有的列内容与另一行完全一致才算重复。
5.11 连接查询
5.11.1 交叉连接(了解)
两张表完全交叉,形成笛卡尔集,即A表中如果有3条数据,B表中有5条数据,会形成15条记录的结果。
SELECT * FROM t_employees CROSS JOIN t_departments
-- 也可以写作
SELECT * FROM t_employees, t_departments
5.11.2 内连接
两张表根据某个关联条件进行连接。只有当有关联关系时才会有结果。
-- 查询所有的员工信息,表内有107条记录,但是有一个员工没有部门id
SELECT * FROM t_employees;
-- 查询所有的部门信息,表内有32条记录
SELECT * FROM t_departments;
-- 关联查询部门和员工,共有106条记录,因为没有部门id的员工无法显示(没有关联)
SELECT * FROM t_employees e, t_departments d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
-- 另一种使用内连接的写法
SELECT * FROM t_employees e INNER JOIN t_departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
注意:INNER JOIN在MySQL中是后面的版本才添加的写法,所以之前都是使用where加条件的写法。
5.11.3 外连接
两张表根据某个关联条件进行连接。以连接的方向对应的表为基准(会显示该表的所有记录)。
左外连接:以左边的表为基准。
右外连接:以右边的表为基准。
-- left outer join 可以简写为 left join,以左边的表为准,会先显示内连接的所有106条记录,然后显示左边表中没有关联的数据1条,一共107条记录,没有关联的部门信息显示为null
SELECT * FROM t_employees e left OUTER JOIN t_departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID ORDER BY e.DEPARTMENT_ID;
-- 右外连接,以右边的表为基准,先显示内连接所有的数据106条,然后显示没有与员工关联的部门信息,员工部分显示为null
SELECT * FROM t_employees e right JOIN t_departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID ORDER BY e.DEPARTMENT_ID;
5.12 复杂查询
– 查询每个部门大于平均薪资的人数
1、查询每个部门的平均薪资
SELECT DEPARTMENT_ID, AVG(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID;
2、查询部门大于平均薪资的信息
SELECT * FROM t_employees e INNER JOIN
(SELECT DEPARTMENT_ID, AVG(SALARY) v FROM t_employees GROUP BY DEPARTMENT_ID) t1
ON e.DEPARTMENT_ID = t1.DEPARTMENT_ID WHERE SALARY > v;
3、将上面的结果分组,求数量。
SELECT e.DEPARTMENT_ID, COUNT(1) FROM t_employees e INNER JOIN
(SELECT DEPARTMENT_ID, AVG(SALARY) v FROM t_employees GROUP BY DEPARTMENT_ID) t1
ON e.DEPARTMENT_ID = t1.DEPARTMENT_ID WHERE SALARY > v
GROUP BY e.DEPARTMENT_ID;
5.13 行转列[经典面试题]
默认的表数据显示方式如下:表名为:score1
姓名stu_name | 课程co_name | 成绩score |
---|---|---|
张三 | Java | 90 |
张三 | SQL | 95 |
张三 | HTML | 88 |
需要查询后显示为以列的形式:
姓名 | Java | SQL | HTML |
---|---|---|---|
张三 | 90 | 95 | 88 |
1、先使用单表操作,创建一个单表,如上面的第一个表格样式。
查询所有数据
SELECT * FROM score1;
2、由于需要使用多个新列(Java、HTML、SQL)来显示数据,所以使用case when的方式来条件显示。
SELECT *,
CASE WHEN co_name = 'Java' THEN scores ELSE 0 END AS 'Java',
CASE WHEN co_name = 'HTML' THEN scores ELSE 0 END AS 'HTML',
CASE WHEN co_name = 'SQL' THEN scores ELSE 0 END AS 'SQL'
FROM score1;
3、去掉其他多余的列
SELECT stu_name,
CASE WHEN co_name = 'Java' THEN scores ELSE 0 END AS 'Java',
CASE WHEN co_name = 'HTML' THEN scores ELSE 0 END AS 'HTML',
CASE WHEN co_name = 'SQL' THEN scores ELSE 0 END AS 'SQL'
FROM score1;
4、按照学生姓名分组显示
SELECT stu_name,
CASE WHEN co_name = 'Java' THEN scores ELSE 0 END AS 'Java',
CASE WHEN co_name = 'HTML' THEN scores ELSE 0 END AS 'HTML',
CASE WHEN co_name = 'SQL' THEN scores ELSE 0 END AS 'SQL'
FROM score1 group by stu_name;
5、分组之后,与分组列不相关的列只会显示第一行数据,为了显示正确的数据,可以使用max函数来求当列的最大值。
SELECT stu_name,
max(CASE WHEN co_name = 'Java' THEN scores ELSE 0 END) AS 'Java',
max(CASE WHEN co_name = 'HTML' THEN scores ELSE 0 END) AS 'HTML',
max(CASE WHEN co_name = 'SQL' THEN scores ELSE 0 END) AS 'SQL'
FROM score1 GROUP BY stu_name;
练习题中数据进行行转列:
1、先把数据以前面的表格1(score1)的形式显示出来。
(SELECT
s.STU_NAME,
sc.DEGREE,
c.COU_NAME
FROM
student AS s
INNER JOIN score AS sc ON s.STU_ID = sc.STU_ID
INNER JOIN course AS c ON sc.COU_ID = c.COU_ID);
2、把上面的显示结果当作虚拟表t1,根据其进行查询显示行转列。
SELECT STU_NAME,
MAX(CASE WHEN COU_NAME = '计算机导论' THEN DEGREE ELSE 0 END) AS '计算机导论',
MAX(CASE WHEN COU_NAME = '操作系统' THEN DEGREE ELSE 0 END) AS '操作系统',
MAX(CASE WHEN COU_NAME = '数字电路' THEN DEGREE ELSE 0 END) AS '数字电路'
FROM
(SELECT
s.STU_NAME,
sc.DEGREE,
c.COU_NAME
FROM
student AS s
INNER JOIN score AS sc ON s.STU_ID = sc.STU_ID
INNER JOIN course AS c ON sc.COU_ID = c.COU_ID) t1 GROUP BY STU_NAME;
5.14 查询某个列重复的行[经典面试题]
去重的方式比较简单,使用distinct关键字即可。
如果要查询某个列重复的行,先根据该列分组,求每组的数量,如果该组的数量大于1,说明该列是重复的。
SELECT
sc.COU_ID
FROM
score AS sc
GROUP BY COU_ID
HAVING COUNT(1) > 1;
六、DML操作
数据操作语言,对数据库中的数据进行增删改操作。
6.1 新增
添加数据。
语法:
添加一行记录:
INSERT INTO 表名(列名1, 列名2, 列名3) VALUES (值1, 值2, 值3)
当添加所有列对应的值时,也可以如下操作:
INSERT INTO 表名 VALUES (值1, 值2, 值3)
一次添加多行记录:
INSERT INTO 表名(列名1, 列名2, 列名3) VALUES (值1, 值2, 值3), (值1, 值2, 值3), (值1, 值2, 值3), (值1, 值2, 值3);
-- 添加一行,指定部分列
INSERT INTO t_jobs(JOB_ID, MIN_SALARY, MAX_SALARY) VALUES('AAA', 50000, 100000);
-- 添加一行,不指定任何列,全列添加
INSERT INTO t_jobs VALUES('BBB', 'BBBBBBB', 50000, 100000);
-- 添加多行
INSERT INTO t_jobs(JOB_ID, MIN_SALARY, MAX_SALARY) VALUES('AAAA', 50000, 100000), ('AAAAA', 50000, 100000), ('AAAAAA', 50000, 100000);
注意:值和列名要对应。
6.2 修改
修改数据:
语法:
UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, 列名3 = 值3 WHERE 条件;
注意:修改时如果没有加条件,会修改所有的行。
UPDATE t_jobs SET JOB_TITLE = 'AAAAAAAAAA', MIN_SALARY = 2000 WHERE JOB_ID = 'AAA';
6.3 删除和清空表
删除数据:
语法:
DELETE FROM 表名 WHERE 条件;
注意:如果没有加条件,会删除表中所有内容。
DELETE FROM t_jobs WHERE Job_id = 'AAA';
清空表:将整个表数据清空。
TRUNCATE TABLE 表名;
TRUNCATE TABLE t_locations;
TRUNCATE 和 DELETE在删除所有数据时的区别?
- DELETE是逐条删除。而TRUNCATE直接删除表本身,然后重新建立一次。
- DELETE删除表会保留自增数字,而TRUNCATE自增重新开始计算。
七、MySQL字段数据类型
7.1 数据类型概述
字段数据类型大体分为两类:
- 不带单引号:数字
- 带单引号:
- 日期时间,带单引号,但是格式必须按要求
- 字符串,带单引号,格式没有要求
7.2 数值类型
int:整数,也有类似于Java中的,此处叫tinyint,smallint,int,bigint
double:小数
decimal:小数
注意:double和decimal也可以限制长度和小数位的长度,例如double(5,2)表示长度5位,小数位2位,范围-999.99~999.99
7.3 日期类型
date:日期,例如:2021-10-10
time:时间,例如:13:14:15
datetime:日期时间,例如:2021-10-10 13:14:15
timestamp:时间戳
7.4 字符串类型
char:定长字符串,例如:char(4)
varchar:变长字符串,例如:varchar(4)
blob:二进制
text:长文本时
注意:在数据库中,char和varchar使用时一定要指定长度。添加数据时不能超过该长度,超过后会截断。
char和varchar区别在于,如果使用char,但是添加的内容没有达到长度,后面会自动使用空格填充,varchar则不会。例如上面的长度为4,如果添加内容tom,char类型时,显示tom空格,varchar类型时,显示tom
所以在项目中,除非知道字段一定会长度固定,才会使用char(性别列),大多数都使用varchar
八、表结构操作
8.1 表创建
表创建的几个要素:
1、表名
2、每一个列的名称,类型
3、规则(约束)
基本语法:
CREATE TABLE 表名(
列名 类型 约束,
列名 类型 约束,
列名 类型 约束
) 字符集;
CREATE TABLE product(
id INT NOT NULL,
pro_name VARCHAR(20) NULL,
create_time datetime
) charset=utf8;
8.2 表修改
语法:
ALTER TABLE 表名 操作;
8.2.1 添加列
ALTER TABLE product ADD price INT NOT NULL;
8.2.2 修改列
ALTER TABLE product MODIFY price DOUBLE;
8.2.3 修改列名
ALTER TABLE product CHANGE price pro_price DOUBLE NOT NULL;
8.2.4 删除列
ALTER TABLE product DROP pro_price;
注意:对表的修改操作会受到数据的影响,可能修改不成功。
例如:一个表中列类型为varchar(20),并且表中有一条记录’abc’,如果修改表,要把该列类型修改成int,会报错。
建议:先导出表数据,再清空表,然后修改表,最后导入数据。
8.2.5 修改表名
ALTER TABLE product RENAME pro;
8.3 删除表
删除表结构。
语法:DROP TABLE 表名;
DROP TABLE pro;
九、表设计过程中的三范式
范式:是指表设计的规则。
在理论上来说,表设计可以有五范式,但是实际上达不到,所以一般设计表只需要满足三范式即可。
三范式:
第一范式:域(字段)完整性。列应该是不可再分的。
第二范式:实体完整性,主键约束。每一行所有的列应该完全依赖于主键。
第三范式:引用完整性,(外键约束)。只能将另一个表的主键当作外键来引用。
注意:所有的后面的范式是基于前面的范式的基础上,即第二范式必须先满足第一范式。
十、约束
10.1 实体完整性约束
10.1.1 主键
使用primary key,标识该列为主键,不能重复,不能为空。
CREATE TABLE product(
id INT PRIMARY KEY,
pro_name VARCHAR(20),
create_time datetime
);
CREATE TABLE product1(
id INT ,
pro_name VARCHAR(20),
create_time datetime,
PRIMARY KEY (id)
);
当使用多个列同时一起做主键时,还是一个主键,但是必须多列一起做是否重复的判定。
-- 指定多列同时做主键
CREATE TABLE score1(
stu_id INT,
cou_id INT,
socre INT,
PRIMARY KEY(stu_id, cou_id)
);
10.1.2 唯一键
当一个表定义时,除了主键外,还有其他的列需要设置唯一性,可以使用关键字UNIQUE。
注意:主键不仅需要唯一,而且不能为空,但是UNIQUE只管唯一,可以为空,而且可以多行为空,此时需要设置为NOT NULL。
CREATE TABLE product(
id INT PRIMARY KEY,
pro_name VARCHAR(20) UNIQUE NOT NULL,
create_time datetime
);
10.1.3 自动增长
在mysql中,int类型可以设置自增。而且需要与主键一起使用,不能单独使用,其他数据库与mysql使用方式不一样。
CREATE TABLE product(
id INT PRIMARY KEY auto_increment,
pro_name VARCHAR(20) UNIQUE NOT NULL,
create_time datetime
);
10.2 域完整性
设置单元格的正确性。
- 非空, NOT NULL
- 默认值,DEFAULT
CREATE TABLE product(
id INT PRIMARY KEY auto_increment,
pro_name VARCHAR(20) UNIQUE NOT NULL,
create_time datetime,
price int DEFAULT 1
);
注意:即使是int类型,在允许为空的前提下,如果没有设置值,会默认为null,当设置了默认值后,才会使用默认值。
10.3 引用完整性
一个表中外键列必须要引用另一个表中的主键列。
语法:
CONSTRAINT fk_t FOREIGN KEY (type_id) REFERENCES pro_type(id)
type_id是当前表中的外键列
pro_type(id)是引用的表的名称和对应的主键列
fk_t是当前表中外键的名称(随意取的,但是不同的表中名称不能重复)
CREATE TABLE product(
id INT PRIMARY KEY auto_increment,
pro_name VARCHAR(20) NOT NULL,
type_id INT NOT NULL,
CONSTRAINT fk_t FOREIGN KEY (type_id) REFERENCES pro_type(id)
);
注意:如果表中已经有数据,再想通过修改表的方式建立引用外键关系,可能会报错,建议先删除数据再建立。
十一、权限管理
11.1 创建用户
语法:
CREATE USER 用户名 IDENTIFIED by 密码;
CREATE USER fenghua IDENTIFIED by '123456';
11.2 授权
语法:GRANT 权限 ON 数据库名.表名 TO 用户名
SELECT\DELETE\UPDATE\INSERT
-- 给与fenghua用户对于companydb数据库所有的表查询权限
GRANT SELECT ON companydb.* TO fenghua;
-- 给与fenghua用户对于所有库所有的表所有权限
GRANT ALL ON *.* TO fenghua;
11.3 撤销权限
REVOKE 权限 ON 数据库名.表名 FROM 用户名;
-- 撤销给与的fenghua用户对于companydb数据库所有的表查询权限
REVOKE SELECT ON companydb.* FROM fenghua;
11.4 删除用户
语法:DROP USER 用户名;
DROP USER fenghua;
十二、视图
视图可以理解为一个虚拟的表,实际上就是将一个复杂的查询语句当作一个变量来使用。
语法结构:
创建视图:
方式一:CREATE VIEW 视图名 AS 查询语句
方式二:CREATE OR REPLACE VIEW 视图名 AS 查询语句,此方式的特点是如果该视图已经存在,则替换,如果不存在,则创建。
修改视图:
ALTER VIEW 视图名 AS 查询语句
删除视图:
DROP VIEW 视图名
注意:视图仅仅只是一个虚拟的变量(表),对视图的删除(DROP)和修改(ALTER)不影响视图所对应的表。
将5.12的复杂查询使用视图完成:
-- 查询每个部门大于平均薪资的人数,原来的写法如下
SELECT e.DEPARTMENT_ID, COUNT(1) FROM t_employees e INNER JOIN
(SELECT DEPARTMENT_ID, AVG(SALARY) v FROM t_employees GROUP BY DEPARTMENT_ID) t1
ON e.DEPARTMENT_ID = t1.DEPARTMENT_ID WHERE SALARY > v
GROUP BY e.DEPARTMENT_ID;
-- 将代码中的子查询使用视图
CREATE VIEW v1
AS
SELECT DEPARTMENT_ID, AVG(SALARY) v FROM t_employees GROUP BY DEPARTMENT_ID;
-- 修改原来的代码,使用视图替换
SELECT e.DEPARTMENT_ID, COUNT(1) FROM t_employees e INNER JOIN
v1
ON e.DEPARTMENT_ID = v1.DEPARTMENT_ID WHERE SALARY > v
GROUP BY e.DEPARTMENT_ID;
将原来的行转列使用视图完成:
-- 原来的sql如下,参考5.13
SELECT STU_NAME,
MAX(CASE WHEN COU_NAME = '计算机导论' THEN DEGREE ELSE 0 END) AS '计算机导论',
MAX(CASE WHEN COU_NAME = '操作系统' THEN DEGREE ELSE 0 END) AS '操作系统',
MAX(CASE WHEN COU_NAME = '数字电路' THEN DEGREE ELSE 0 END) AS '数字电路'
FROM
(SELECT
s.STU_NAME,
sc.DEGREE,
c.COU_NAME
FROM
student AS s
INNER JOIN score AS sc ON s.STU_ID = sc.STU_ID
INNER JOIN course AS c ON sc.COU_ID = c.COU_ID) t1 GROUP BY STU_NAME;
-- 将上面的3表关联查询创建为一个视图
CREATE VIEW v2 AS
SELECT
s.STU_NAME,
sc.DEGREE,
c.COU_NAME
FROM
student AS s
INNER JOIN score AS sc ON s.STU_ID = sc.STU_ID
INNER JOIN course AS c ON sc.COU_ID = c.COU_ID;
-- 将上面的语句使用视图来完成
SELECT STU_NAME,
MAX(CASE WHEN COU_NAME = '计算机导论' THEN DEGREE ELSE 0 END) AS '计算机导论',
MAX(CASE WHEN COU_NAME = '操作系统' THEN DEGREE ELSE 0 END) AS '操作系统',
MAX(CASE WHEN COU_NAME = '数字电路' THEN DEGREE ELSE 0 END) AS '数字电路'
FROM
v2 GROUP BY STU_NAME;
- 视图相当于一个临时的表(查询结果),但是使用时还是会将原来的查询语句代入运行,所以视图不会存储数据,原表的数据改变了,视图的结果也会改变。
- 对查询性能没有任何优化,仅仅是将查询语句变得简单
- 视图是可以update修改数据(修改视图数据等同于修改原表数据)的,但是,如果视图的创建语句包含下面的任意一种,则不能update数据:
- 有聚合函数
- 使用了distinct
- 使用了group by
- 使用了having
- 使用了union或union all的
十三、SQL的分类
DDL:数据定义语言,创建表、数据库等 CREATE\ALTER\DROP
DCL:数据控制语言,权限,GRANT\REVOKE
DML:数据操纵语言,增删改,INSERT\UPDATE\DELETE
DQL:数据查询语言,查询数据,SELECT\GROUP BY\ORDER BY\HAVING
TPL(TCL):事务处理语言,处理事务的,COMMIT\ROLLBACK
十四、事务
14.1 事务的场景
转账。这在项目中应该是一个业务操作(方法)。
实际上,该业务操作会在数据库中形成两个修改语句,即修改A账户减钱,修改B账户加钱。
如果在修改A账户结束后,在修改B账户时出现了异常,没有成功,那么就形成了A账户减钱了,但是B账户没有加钱的局面,是不合理的。
此时,需要使用到事务。
14.2 事务的概念
事务是由一个或多个sql语句组成的一个原子操作。
在一个事务中,只有所有的SQL都执行成功,才成功,如果有一个sql执行失败,则全部失败。
14.3 简单原理
上面的案例在数据库中执行,如果使用了事务(即不自动提交),此时所有的修改操作都是在缓存中执行,如果全部执行成功,则会将结果写入到数据库(同步数据)(提交事务),如果有任何一个不能执行成功,则不写入到数据库,撤销操作(回顾事务)。
14.4 具体操作
注意:在数据库中,默认是自动提交事务,即每一条sql语句都默认为是一个独立的事务,所以该sql执行成功后,会自动提交到数据库。
所以,如果需要使用事务,首先应该关闭事务的自动提交,注意:事务执行完毕后需要还原自动提交的设置。
事务提交:
COMMIT,提交事务,将缓存数据同步(写入)到数据库。
事务回顾:(撤销)
ROLLBACK,回滚事务,撤销之前的数据库操作。
设置自动提交参数:
SET AUTOCOMMIT = 0; 关闭自动提交
SET AUTOCOMMIT = 1; 开启自动提交
SELECT @@autocommit; -- 查看系统是否自动提交 1:自动提交 0:不自动提交
set autocommit=0; -- 修改为不自动提交
-- 减钱
UPDATE account SET money = money - 500 WHERE account = '1001';
-- 加钱
UPDATE account SET money = money + 500 WHERE account = '1002';
COMMIT; -- 提交事务
set autocommit=1; -- 还原设置,修改为自动提交
set autocommit=0; -- 修改为不自动提交
-- 减钱
UPDATE account SET money = money - 500 WHERE account = '1001';
-- 加钱
UPDATE account SET money = money + 500 WHERE account = '1002';
ROLLBACK; -- 回滚事务
set autocommit=1; -- 还原设置,修改为自动提交
14.5 事务的四大特征
A(原子性):事务是一个整体,要么全部成功,要么全部失败。
C(一致性):表示事务操作过程中,数据的状态必须是一致的。有一条语句失败,其他的全部回滚。
I(隔离性):事务操作之间是相互隔离的,只能看到事务操作之前的状态或者之后的状态,不能看到事务操作过程中的状态。
D(持久性):事务执行完成后,结果就持久的写入了数据库。
记忆的小技巧:一元九个。(一原久隔)
14.6 事务的隔离性的理解
事务与事务之间应该相互隔离。
安全和性能:如果涉及到同一个数据时,事务与事务之间安全和性能该如何取舍。
14.7 事务的隔离级别
- 读未提交,一个事务能够读取另一个事务的未提交的数据。会引发脏读、虚读(不可重复读)、幻读问题
- 读已提交,一个事务能够读取另一个事务中的已提交的数据。会引发虚度、幻读问题
- 可重复读,只会出现幻读问题
- 可串行化,事务一个一个执行,性能会降到极低,安全性上升到极高
注意:上面的内容从上往下,性能越来越差,安全性越来越高,在项目中需要在性能和安全性上做一个取舍。所以基本不可能设置级别为读未提交或者可串行化。
在数据库中的事务的隔离级别的设置上:
主流的数据库都默认设置为读已提交。但是唯独MySQL默认设置的可重复读。
14.8 脏读、不可重复读、幻读
脏读:一个事务能读取另一个事务未提交的数据。脏读不会被任何数据提供方允许,所以出现脏读一般会认为是系统的BUG,需要改动。
不可重复读,也叫虚读。一个事务中,两次读取的数据不一致。此时,对于用户来说,可能会产生一些影响,但是对于数据提供方来说,安全没有影响。所以介于安全性和性能上的考虑,大多数主流数据库选择的就是此隔离级别。
幻读。一个事务中,两次读取的数据的条数不一致(增加或者减少)。此时,对于用户来说,可能会产生一些影响,但是对于数据提供方来说,安全没有影响。此隔离级别也可以选择,与上一种隔离级别不管是性能还是安全相差不大。