ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL知识点大全！！ [打印本页]

作者: 兜兜零元 时间: 2022-8-31 19:45
标题: MySQL知识点大全！！
1.初识数据库

数据库（DB，database）
概念：数据仓库，安装在操作系统上的一款软件
作用：存储管理数据
分类：
- 关系型数据库（SQL）：
  - MySQL，Oracle，Sql Server，DB2，SQLlite
  - 通过表和表之间，行和列之间的关系进行数据的存储
- 非关系型数据库(NoSQL not only) ：
  - Redis，MongDB
  - 非关系数据库，对象存储，通过对象自身的属性决定
DBMS（数据库管理系统）：
- 数据库的管理软件，科学有效的管理数据，维护和获取数据
- MySQL，数据库管理系统
下载安装MySQL数据库
下载安装数据库可视化工具Navicat
连接数据库（命令行）：
- 打开cmd
- 跳转到mysql的bin目录
- 输入mysql -uroot -p
- 输入密码
- exit退出
数据库一些命令：
1. --注：每个sql语句结束都要用分号结尾
3. flush privilege; --刷新权限
5. show databases; --查看所有数据库
7. use 数据库名; --使用数据库
8. Database changed
11. show tables; --查看数据库中所有的表
13. describe student; --查看表的信息
15. create database test_db; --创建数据库
复制代码

2. 操作数据库

操作数据库》操作数据库中的表》操作表中的信息

操作数据库：
- 操作数据库：
  1. CREATE DATABASE [if not exists] shcool;
  复制代码
- 删除数据库：
  1. drop database [if exists] lpjsql;
  复制代码
- 使用数据库：
  1. use db_yq;
  复制代码
- 查看数据库：
  1. show DATABASES;
  复制代码
操作数据库表：
- 数据库列的类型：
  - 数值：
    tinyint --十分小的整数|1个字节
    smallint --较小的整数|2个字节
    mediumint --中等大小的整数|3个字节
    int --标准的整数|4个字节|常用
    bigint --较大的整数|8个字节
    float --单精度浮点数|4个字节
    double --多精度浮点数|8个字节
    decimal --字符串形式的浮点数|用于金融计算
    复制代码
  - 字符串：
    char --固定大小的字符串|0~255
    varchar --可变长字符串|0~65535|常用
    tinytext --微型文本|0~2^8-1
    text --文本串|0~2^16-1|保存大文本
    复制代码
  - 时间和日期：
    date --YYYY-MM-DD|日期
    time --HH:mm:ss|时间
    datetime --YYYY-MM-DD HH:mm:ss|最常用的时间格式
    timestamp --时间戳|1970.1.1到现在的毫秒数|较为常用
    year --年份
    复制代码
  - null：
    --没有值，未知，注意不要使用null进行运算，因为结果一定为null
    复制代码
数据库的字段属性（重点）：
- unsigned （无符号的整数声名该列不能为负数）
- zerofill（0填充不足的位数用0填充 1 001）
- auto_increment（自增，自动在上一条记录的基础上+1（默认）通常用来设计唯一的主键必须是整数可自定义设计主键的自增起始值和步长）
- NULL NOT NULL（非空，假设不赋值就会报错）
- default '默认值'（默认，设置默认值）（navicat设置字符默认值时需要加引号）
拓展：做项目时，每个表设计时都应该具有的五个字段：
- id 主键
- ‘version’ 乐观锁版本号
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
注：在数据库中int的长度并不影响数据的存储精度，长度只是和显示有关
创建数据库表：
1. create table if not exists ‘studnet’ (
2. `id` int(4) not null auto_increment COMMENT '学号',
3. `name` VARCHAR(20) not NULL DEFAULT '匿名' COMMENT '姓名',
4. `psd` VARCHAR(20) not NULL DEFAULT '123456' COMMENT '密码',
5. `sex` VARCHAR(2) not NULL DEFAULT '男' COMMENT '性别',
6. `birthda` datetime DEFAULT null COMMENT '出生日期',
7. `address` VARCHAR(100) DEFAULT null COMMENT '家庭住址',
8. `email` VARCHAR(18) DEFAULT null COMMENT '邮箱',
9. PRIMARY key(`id`)
10. );
13. show create table studnet; --查看表的创建语句
14. desc studnet;--查看表的结构
15. --注：use 数据库之后，相关操作比如创建表是在该数据库下创建
复制代码
数据表的类型：
- 数据引擎：
  MyISAMInnoDB事务支持不支持支持数据行锁定不支持支持外键约束不支持支持全文索引支持不支持表空间大小较小较大，约为倍常规使用操作：
  - MyISAM：节约空间，速度较快
  - InnoDB：安全性高，支持事务，多表多用户操作
  物理存储位置：所有数据库文件都存在data目录下，本质上还是文件存储
  - MySQL引擎在物理上文件的区别而：
    - .MySQL（server）创建并管理的数据库文件：
      - .frm文件：存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，与操作系统和存储引擎无关，即不管MySQL运行在何种操作系统上，使用何种存储引擎，都有这个文件。
      - 除了必有的.frm文件，根据MySQL所使用的存储引擎的不同（MySQL常用的两个存储引擎是MyISAM和InnoDB），存储引擎会创建各自不同的数据库文件。
        
        MyISAM数据库表文件：
        
        .MYD文件：即MY Data，表数据文件
        
        .MYI文件：即MY Index，索引文件
        
        InnoDB数据库文件（即InnoDB文件集，ib-file set）：
        
        ibdata1等：系统表空间文件，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用
        
        .ibd文件：单表表空间文件，每个表使用一个表空间文件（file per table），存放用户数据库表数据和索引
        
        日志文件： ib_logfile0、ib_logfile1
数据库表的字符集编码：不设置的话会使用默认的编码
修改删除表：
- 修改表：
  1. ALTER table studnet RENAME as student --修改表名
  3. --增加表的字段
  4. ALTER TABLE test1 add age int(10) not null COMMENT '年龄'
  6. --修改约束和字段类型，不能重命名
  7. ALTER TABLE test1 MODIFY age VARCHAR(11);
  9. --不能单独修改字段类型和约束，但是可以在重命名时设置字段类型约束
  10. ALTER TABLE test1 CHANGE age age1 int(2);
  12. --删除表的字段
  13. ALTER TABLE test1 drop age;
  复制代码
- 删除表：
  1. drop table if exists test1;
  复制代码
  注：所有的删除或者创建操作尽量加上判断条件

3.MySQL数据管理

<ol>外键（了解）：数据库中直接设立外键都是数据库级别的外键，不建议使用（避免数据库过多造成困扰）。

最佳实现：数据库就是单纯的表，只用来存储数据，当有多张表进行操作时或者想使用外键建议使用程序实现。

DML语言（掌握）：

insert：
1. --方法一：直接插入一整条记录
2. insert into 表名 values(全部列值，且一一对应);
3. --方法二：对应列插入值
4. insert into 表名(字段1，字段2...) values(字段值1,字段值2,...);
5. --一次性插入多个记录
6. INSERT into 表名(字段1，字段2...) VALUES (字段值1,字段值2,...)，(字段值1,字段值2,...),...;
复制代码
update：
1. update 表名 set 字段名=字段值[,字段名=字段值,...] [where ...]
2. --注1：如果没有条件限定，会直接修改全部记录的对应字段值
3. --注2：字段值不仅可以是常量，也可以是变量，比如常用的时间函数
复制代码
delete：
1. delete from 表名;--删除整个表所有数据
2. delete from 表名 where ...;--删除指定数据
4. --补充
5. --完全清空一个数据库表，表的结构和索引约束不会变
6. truncate table 表名;
8. --两种方式的区别
9. --相同点：都能删除数据且不会删除表
10. --不同点：
11. --truncate：自增会归零
12. --delete：不影响自增
14. --拓展：delete 删除数据时，如果重启数据库，那么
15. --InnoDB：自增列会从1开始(存在内存中的，断电即失)
16. --MySAM：继续从上一个自增量开始(存在文件中的，不会丢失)
复制代码

DQL语言（查询数据）：

别名：字段名 as 新名字；表名 as 新名字；或者直接：字段名新名字；表名新名字；
拼接字符串：Concat(a,b,c,...)
1. SELECT CONCAT('名字：',number,'寝室') as '寝室号' , CONCAT('楼：',building_name) as '所属楼层' from room;
复制代码
去重：distinct（将查询中的重复结果只显示一条）
1. SELECT DISTINCT room_id FROM student;
复制代码
特殊作用：
1. SELECT VERSION();--查询版本号
2. SELECT 123*223;--查询计算结果
复制代码

注：数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量，而
select 表达式 from 表
<ol start="5">where条件语句：

常用运算符：
- 逻辑运算符：
  运算符含义AND 或者 &&逻辑与，含义为“并且”。当所有操作数均为非零值且不为 null 时，返回值为 1；当一个或多个操作数为 0 时，返回值为 0；其余情况返回值为 nullOR 或者 ||逻辑或，含义为“或者”。当所有操作数均不为 null 且任意一个操作数为非零时，返回值为 1，否则为 0；当有操作数为 null，且存在另一个操作数为非零时，返回值为 1，否则为 null；当所有操作数均为 null 时，返回值为 nullNOT 或者 !逻辑非，含义为“取反”。当操作数为 0 时，返回值为 1；当操作数为非零时，返回值为 0；当操作数为 null 时，返回值为 null
- 比较运算符：
  [table][tr]运算符含义[/tr][tr][td]=[/td][td]等于[/td][/tr][tr][td][/td][td]安全等于，可以比较 null[/td][/tr][tr][td] 或 !=[/td][td]不等于[/td][/tr][tr][td]>=[/td][td]大于等于[/td][/tr][tr][td][/td][td]大于[/td][/tr][tr][td]</tdtd小于/td/tr/tbody/table/lilip算术运算符：/ptabletheadtrthstrong运算符/strong/ththstrong含义/strong/th/tr/theadtbodytrtd+/tdtd加法运算/td/trtrtd-/tdtd减法运算/td/trtrtd*/tdtd乘法运算/td/trtrtd//tdtd除法运算，返回商/td/trtrtd%/tdtd求余运算，返回余数/td/tr/tbody/table/lilip模糊查询：/ptabletheadtrth运算符/thth含义/th/tr/theadtbodytrtdIS NULL/tdtd判断一个值是否为 null/td/trtrtdIS NOT NULL/tdtd判断一个值是否不为 null/td/trtrtdBETWEEN AND/tdtd在什么和什么之间/td/trtrtdLIKE/tdtd匹配/td/trtrtdin/tdtda in (a1,a2,a3,...),如果a在a1,a2,a3之间存在，那么为真/td/tr/tbody/tablep注1：like与%（0~任意个字符）和_（一个字符）结合使用，且不一定要是字符类型才可以使用，数字类型也可以加上引号使用/p
  1. select id from student where id like '%2%';
  复制代码
  p注2：in()必须指定的是确定的值，使用%或者_不行，确定值的表达式也可以/p
  1. select id from student where id in (7,8,9,10,1+11);
  复制代码
  /li/ol/li/ol/lilip联表查询：/pollipimg src="https://img2022.cnblogs.com/blog/2935570/202207/2935570-20220721170514497-117876205.png" alt="" loading="lazy"/p/lilip两种连接表的查询：/polli等值查询：where/lili联表查询：join...on/li/ol/lilip联表查询：/pollipinner join：内连接，特点是查询出来的结果是两个表都有的部分/p
  1. --如a表有1,2,3,4，b表有2,3,4,5,那么查询的结果包含2,3,4
  2. SELECT s.room_id,r.id,s.name,r.building_name from student s INNER JOIN room r on s.room_id=r.id;
  复制代码
  /lilipleft join：/pollip左连接：/p
  1. --如a表有1,2,3,4，b表有2,3,4,5,那么查询的结果包含1,2,3,4
  2. SELECT s.room_id,r.id,s.name,r.building_name from student s LEFT JOIN room r on s.room_id=r.id;
  复制代码
  /lilip左外连接：/p
  1. --如a表有1,2,3,4，b表有2,3,4,5,那么查询的结果包含1
  2. SELECT s.room_id,r.id,s.name,r.building_name from student s LEFT JOIN room r on s.room_id=r.id where r.building_name is null;
  复制代码
  /li/ol/lilipright join：/pollip右连接：/p
  1. --如a表有1,2,3,4，b表有2,3,4,5,那么查询的结果包含2,3,4,5
  2. SELECT s.room_id,r.id,s.name,r.building_name from student s RIGHT JOIN room r on s.room_id=r.id
  复制代码
  /lilip右外连接:/p
  1. --如a表有1,2,3,4，b表有2,3,4,5,那么查询的结果包含5
  2. SELECT s.room_id,r.id,s.name,r.building_name from student s RIGHT JOIN room r on s.room_id=r.id where s.room_id is null;
  复制代码
  /li/ol/lilip全连接（mysql不支持全连接）/p/lilip三个及以上的多表查询：/p
  1. select r.*,s.`寝室人数`,e.* from room r left join (SELECT count(*) as '寝室人数',room_id as id from student GROUP BY room_id) as s on r.id=s.id RIGHT JOIN `repair` e on r.id=e.room_id
  2. --注：直接先两表查询，然后接着两表查询的结果后面再连接表
  复制代码
  /lilip联表查询+子查询;/p
  1. select r.*,s.`寝室人数` from room r left join (SELECT count(*) as '寝室人数',room_id as id from student GROUP BY room_id) as s on r.id=s.id
  2. --注：在s.`寝室人数`处使用字符时应该使用`而不是引号
  复制代码
  /lilip自连接（了解）：将一张表看成两张表进行连接查询/p/li/ol/li/ol/lilip分页和排序：/polliporder by(排序)：/pollipdesc（降序）：/p
  1. order by 字段 DESC
  复制代码
  /lilipasc（升序）：/p
  1. order by 字段 ASC
  复制代码
  /li/ol/liliplimit（分页）：/p
  1. LIMIT 数据起始位置,页面数量
  复制代码
  /li/ol/lilip子查询：/p
  1. select room.id,`repair`.`describe`,student.`name` from student,room,`repair` WHERE student.room_id=(SELECT id FROM room WHERE number=2624) and `repair`.room_id=(SELECT id FROM room WHERE number=2624) and room.number=2624
  复制代码
  /lilip分组和过滤（当分组后还有条件时，可以使用having）：/p
  1. select room_id FROM student GROUP BY room_id having COUNT(*) = 2
  复制代码
  p注：（select ...）可以当做一个表，可以跟在from后面，也可以跟在连接查询后面，同时在查询的时候新生成的表都要设置别名。/p/li/ol/li/olh1 id="4mysql函数"4.mysql函数：/h1p参考网站：a href="https://www.jb51.net/article/226393.htm" target="_blank" rel="noopener"https://www.jb51.net/article/226393.htm/a/pollip常用函数：/pollipcodelength(str)/code：获取参数值的字节个数；/pp对于codeutf8/code字符集来说，一个英文占1个字节；一个中文占3个字节；/pp对于codegbk/code字符集来说，一个英文占1个字节；一个中文占2个字节；/p/lilipcodeconcat(str1,str2,…)/code：拼接字符串；/p/lilipcodeupper(str)/code：将字符中的所有字母变为大写；/p/lilipcodelower(str)/code ：将字符中所有字母变为小写；/p/lilipcodeinstr(str,要查找的子串)/code ：返回子串第一次出现的索引，如果找不到，返回0；当查找的子串存在于字符串中：返回该子串在字符串中【第一次】出现的索引。当查找的子串不在字符串中：返回0。/p/lilipcodetrim(str)/code：去掉字符串前后的空格；该函数只能去掉字符串前后的空格，不能去掉字符串中间的空格。/p/lilipcodelpad(str,len,填充字符)/code：用指定的字符，实现对字符串左填充至指定长度；/p/lilipcoderpad(str,len,填充字符)/code：用指定的字符，实现对字符串右填充至指定长度/p/lilipcodereplace(str,子串,另一个字符串)/code：将字符串str中的字串，替换为另一个字符串；/p/lilipcoderound(x,[保留的位数])/code ：四舍五入/p/lilipcodeceil(x)/code：向上取整/p/lilipcodefloor(x)/code：向下取整/p/lilipcodemod(被除数,除数)/code ：取余/p/lilipcodenow()/code ：返回系统当前的日期和时间；/p/lilipcodecurdate()/code ：只返回系统当前的日期，不包含时间；/p/lilipcodecurtime()/code ：只返回系统当前的时间，不包含日期；/p/lilip获取日期和时间中年、月、日、时、分、秒；/pp获取年份：year()；/pp获取月份：month()；/pp获取日：day()；/pp获取小时：hour()；/pp获取分钟：minute()；/pp获取秒数：second()；/p/lilipcodeweekofyear()/code ：获取当前时刻所属的周数；/p/lilipcodequarter()/code ：获取当前时刻所属的季度；/p/li/ol/lilip聚合函数：/pollicount（）：计数/lilisum（）：求和/liliavg（）：平均值/lilimax（）：最大值/lilimin（）：最小值/li/olp注：/pollipsum()函数和avg()函数对于字符串类型、日期/时间类型的计算都没有太大意义。因此，sum()函数和avg()函数，我们只用来对小数类型和整型进行求和/p/lilipmax()和min()中传入的是"整型/小数类型"，计算的是数值的code最大值/code和code最小值/code。max()和min()中传入的是"日期类型"，max()计算的最大值是离我们code最近的那个日期/code，min()计算的最小值是离我们code最远的那个日期/code，这个可以记一下。max()和min()中传入的是code字符串类型/code，max()计算的最大值是按照英文字母顺序显示的，min()计算的最小值也是按照英文字母顺序显示的，意义不太大。/p/lilip当某个字段列中没有null值，则"count(列字段)=count()。"br当某个字段列中有null值，则"count(列字段)count()。"br因此，假如你想统计的是整张表的行数，请用count(*)。/pp另可参考：a href="https://blog.csdn.net/qq_41711758/article/details/116258290" target="_blank" rel="noopener"https://blog.csdn.net/qq_41711758/article/details/116258290/a/p/li/ol/lilip拓展（MD5加密）：/p
  1. --数据库中使用MD5函数加密
  2. INSERT into `repair` (id,`describe`,room_id) VALUES (15,md5('2624'),2)
  复制代码
  /li/olh1 id="5事务"5.事务/h1ollip什么是事务;/pp经典例子：/pp转账：/pp操作: 张三和李四各自的账号都是1000元；张三向李四转账100元/pp组成单元: 张三钱-100, 李四钱+100/pp操作成功: 张三钱900,李四钱1100/pp操作失败: 张三钱1000,李四钱1000/pp不可能发生: 张三钱900,李四钱1000;或者战三钱1000,李四钱1100/p/lilip事务特性：/polli原子性：要么都成功，要么都失败,即不会出现钱转出去了但是没有收到的情况，即转钱和收钱两个操组合作为一个不可拆分的操作/lili一致性:事务前后数据完整性一致，比如转钱转钱一共2000，那么转钱之后一共也是2000/lili持久性-----事务提交：事务提交后不可逆，被持久化保存到数据库中/lili隔离性：多个事务之前是相互隔离的，互不影响。数据库允许多个a href="https://so.csdn.net/so/search?q=%E5%B9%B6%E5%8F%91&spm=1001.2101.3001.7020" target="_blank" rel="noopener"并发/a同时对其数据进行读写和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致的数据不一致。br比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。/lili隔离造成的一些问题：olli脏读：一个事务读取了另外一个事务未提交的数据/lili不可重复读：在一个事务内读取表中某一行数据多次读取结果不一致（不一定是错误，可能是场合不对）/lili虚读（幻读）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致/li/ol/li/ol/lilip数据库实现事务：/pollipmysql是默认开启事务自动提交的/p
  1. set autocommit =0; --关闭事务
  2. set autocommit =1; --开启事务，默认
  复制代码
  /lilip手动实现事务步骤：/p
  1. --第一步：关闭事务
  2. set autocommit =0;
  4. --第二步：开始一个事务
  5. START TRANSACTION; --标记一个事务的开始，这个之后的sql语句都算一个事务
  7. --第三步：sql语句执行
  9. --第四步：提交或者回滚
  10. commit --提交：持久化，事务成功
  11. ROLLBACK --回滚：回到之前的状态，一旦被提交就持久化了无法回滚
  13. --第五步：事务结束
  14. set autocommit =1; --开启事务提交
  16. --了解：
  17. SAVEPOINT 保存点名 --设置一个事务保存点
  18. ROLLBACK to SAVEPOINT 保存点名 --回滚到保存点名
  19. RELEASE SAVEPOINT 保存点名 --撤销保存点，一个事务可以有多个保存点
  复制代码
  /li/ol/li/olh1 id="6索引"6.索引/h1ollip定义：索引是帮助mysql高效获取数据的一种数据结构/p/lilip分类：/polli主键索引（primary）：ulli唯一标识，不可重复，只能存在一个主键/li/ul/lili唯一索引（unique）：ulli避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引/li/ul/lili常规索引（key/index）：ulli默认的，使用index或者key设置/li/ul/lili全文索引（FullText）：ulli快速定位数据/li/ul/li/ol/lilip使用：/pollip在创建表的时候增加索引/p/lilip创建完毕后，增加索引：/pollipALTER TABLE 表名 add 索引类型索引名 (列名1,列名2，列名3，...)/p
  1. --查看对应表的索引
  2. show index from student
  3. --增加一个全文索引
  4. ALTER TABLE student add FULLTEXT index `student_name` (`name`)
  5. --EXPLAIN:分析sql执行的状况
  6. EXPLAIN SELECT * FROM student --非全文索引
  复制代码
  /lilipcreate 索引类型索引名 on 表（列名）/p
  1. CREATE UNIQUE INDEX `student_name` on student (name)
  复制代码
  /li/ol/lilip删除索引：DROP INDEX index_name ON table_name/p
  1. drop index `student_name` on student
  复制代码
  /li/ol/lilip索引原则：/polli索引不是越多越好/lili不要对经常变动的数据加索引/lili小数据量的表不需要加索引/lili索引一般加在常用来查询的字段/li/ol/lilip索引的数据结构和原理：/pp参考链接：a href="http://blog.codinglabs.org/articles/theory-of-mysql-index.html" target="_blank" rel="noopener"http://blog.codinglabs.org/articles/theory-of-mysql-index.html/a/p/lilipmysql优化：参考链接：参考链接;a href="https://blog.csdn.net/jiadajing267/article/details/81269067" target="_blank" rel="noopener"https://blog.csdn.net/jiadajing267/article/details/81269067/a/p/li/olh1 id="7数据库的备份和用户管理"7.数据库的备份和用户管理/h1ollip备份：/pollip方式：/pullip直接拷贝物理文件（data）/p/lilip直接在可视化工具里面导出导入/p/lilip使用命令行导出:在Bin目录下/p
  1. --导出
  2. --mysqldump -h主机 -u用户名 -p密码数据库 [表1 表2 表3] >位置
  3. mysqldump -hlocalhost -uroot -p18227022334a mysql user >D:/mysql_user.sql
  5. --导入
  6. --登录的情况下，切换到对应数据库
  7. --source 文件
  8. source D:/mysql_user.sql
  9. --未登录的情况下
  10. --mysql -u用户名 -p密码库名 [表名]< 文件
  11. mysql -uroot -p18227022334a mysql user< D:/mysql_user.sql
  复制代码
  </ul>
用户管理：
- navicat操作：
- sql命令操作：
  用户表：mysql数据库下的user表
  1. --创建用户:
  2. CREATE user 用户名 IDENTIFIED by 密码
  4. --修改密码
  5. ALTER USER 用户名@Host IDENTIFIED WITH mysql_native_password BY 密码
  7. --用户重命名
  8. RENAME user 用户名@Host to 新用户名@新Host
  10. --用户授权
  11. grant all privileges on *.* to 用户名@Host with grant option;
  13. --查询权限
  14. --撤销权限
  16. --删除用户
  17. DROP user 用户名@Host
  复制代码

8.规范数据库设计

三大范式：
- 第一范式（1NF）：原子性：保证每一列不可再分
- 第二范式（2NF）：满足第一范式前提下，每张表只集中于一件事（比如姓名，性别，年龄，课程名称，课程成绩这张表就不满足第二范式，应该拆分为姓名，性别，年龄和姓名，课程名称，课程成绩两张表）
- 第三范式（3NF）：满足第二范式的前提下，确保数据表中每一列数据都跟主键直接相关，不能间接相关
规范性和性能问题：
阿里内部规定关联查询的表表的超过三张
- 从成本和用户体验方面来说，性能更加重要
- 在考虑性能问题的同时应该适当考虑下规范性
- 可以故意增加一些冗余字段（从多表查询变成单表查询）
- 故意增加一些计算列（从大数据量变为小数据量的查询：索引）

9.JDBC

导入数据库驱动
jdbc：Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口应用程序接口，提供了诸如查询和更新数据库中数据的方法，我们通常说的JDBC是面向关系型数据库的。
操作数据库步骤：
- 加载驱动:
  1. Class.forName("com.mysql.jdbc.cj.Driver");
  复制代码
- 用户信息和密码：
  1. String url="jdbc.url=jdbc:mysql://localhost:3306/dormitory_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
  2. String username="root";
  3. String password="18227022334a";
  复制代码
- 连接数据库：
  1. Connection conn= DriverManager.getConnection(url,username,password);
  复制代码
- 创建执行sql语句的Statement对象：
  1. Statement statement=conn.createStatement();
  复制代码
- 执行sql语句：
  1. String sql="SELECT * from room;";
  2. ResultSet resultSet = statement.executeQuery(sql);
  3. //遍历结果集获得数据
  4. while (resultSet.next()){
  5. System.out.println(resultSet.getObject("id"));
  6. System.out.println(resultSet.getObject("number"));
  7. System.out.println(resultSet.getObject("building_name"));
  8. System.out.println(resultSet.getObject("max_capacity"));
  9. }
  复制代码
- 释放连接：
  1. resultSet.close();
  2. statement.close();
  3. conn.close();
  复制代码
连接数据库各对象：
- DriverManager：
  1. Class.forName("com.mysql.jdbc.cj.Driver"); //加载数据库驱动
  2. DriverManager.getConnection(url,username,password);//获得一个数据库的连接对象
  3. //url成分分析
  4. // jdbc:mysql://主机名:端口号/数据库名?参数
  复制代码
- connection：
  1. //相当于数据库
  2. connection.commit();//事务提交
  3. connection.rollback();//事务回滚
  4. connection.setAutoCommit();//设置自事务动提交
  5. connection.createStatement();//创建一个执行sql语句的
  复制代码
- Statement：
  1. statement.executeQuery(sql);//执行查询语句，返回结果集
  2. statement.execute(sql);//执行任何语句
  3. statement.executeUpdate(sql);//执行更新，插入，删除语句，返回受影响的行数
  复制代码
- resulSet：
  1. //遍历结果集
  2. resultSet.next();//光标移动到下一行，注：光标一开始是在第一行前面
  3. //获得数据
  4. resultSet.getObject();//不知道类型情况下使用
  5. resultSet.getXXX();//XXX为基本数据类型，根据指定的数据类型获得数据
  复制代码
注：在最后都应该释放资源，释放的原则遵循：假如创建时顺序为abc，那么释放是cba
Statement对象存在的问题：sql注入问题且效率低
1. //比如：模拟一个用户登录的场景
2. //假设存在一个login(username,password)
3. //由于使用statement执行sql语句时，传入了参数，则需要使用字符串的拼接
4. String sql="select * from student where name ='"+name+"' and password = '"+"';";
5. //当用户非法传入进行字符串的拼接时，就会导致数据库的泄露，比如
6. login("' or '1=1'","' or '1=1'");
7. /*此时会查询出数据库student表所有信息，因为参数传进来进行拼接后成为
8. select * from student where name ="" or '1=1' and password="" or '1=1';
9. */
复制代码
使用PreStatement对象：
1. public int execUpdate(String sql, Object[] parms) {
2. int count = 0;
3. try {
4. this.getConn();
5. ps = conn.prepareStatement(sql);
6. if (parms != null) {
7. for (int i = 0; i < parms.length; i++) {
8. ps.setObject(i + 1, parms[i]);
9. }
10. }
11. count = ps.executeUpdate();
12. return count;
13. } catch (SQLException e) {
14. e.printStackTrace();
15. } finally {
16. //关闭数据库连接
17. this.closeConn(conn, resultSet, ps);
18. }
19. return count;
20. }
复制代码
注：PreparedStatement 防止 SQL 注入的原理就是把用户非法输入的单引号进行转义，最终传入参数作为一个整体执行，从而防止 SQL 注入，而 Statement 对象不会进行此操作。
idea操作数据库
jdbc操作事务：
1、获得connection对象
2、设置conn.setAutoCommit(false);
3、只有执行conn.commit();才会一起提交，否则不会一起提交 conn.rollback(); 回滚方法
1. public void test(){
2. try {
3. this.getConn();
4. String sql1="update user set money=money-500 where user_name='张三'";
5. String sql2="update user set money=money+500 where user_name='李四'";
6. conn.setAutoCommit(false);//关闭自动提交，开启事务
7. conn.prepareStatement(sql1).executeUpdate();
8. int x=1/0;//模拟中途出错
9. conn.prepareStatement(sql2).executeUpdate();
10. conn.commit();//事务提交
11. } catch (SQLException e) {
12. try {
13. conn.rollback();//执行失败，事务回滚
14. } catch (SQLException ex) {
15. ex.printStackTrace();
16. }
17. e.printStackTrace();
18. }finally {
19. closeConn(conn,null,null);
20. }
21. }
复制代码
数据库连接池：
- 数据库连接--执行完毕--释放
  由于连接--释放十分浪费系统资源，故而开发出池化技术
  池化技术：准备一些预先的资源，连接时连接预先准备好的
  比如：
  数据库最大资源数：20
  假设常用连接数：10
  那么最大连接数：20
  小于10的连接数直接使用，超过10小于20则需要使用备用的资源
  超过20那么进行一个等待
  如果等待时间过长，那么等待超时
- 编写连接池:实现一个接口DataSource
- 开源数据源：DBCP，C3P0，Druid：阿里巴巴，当使用了数据库连接池之后就不用我们自己去实现数据库的连接的代码的编写了
  - DBCP：dataSource= BasicDataSourceFactory.createDataSource()，该数据源的相关信息是通过一个propertiee文件保存的，在配置数据源时直接读取该属性文件信息。
  - C3P0：ComboPooledDataSource dataSource = new ComboPooledDataSource();该数据源信息是通过一个xml文件进行配置的，并且它可以配备多个数据源，在使用的时候可以指定使用哪个数据源。
  注：使用数据库连接池都是需要去实现一个dataSource接口。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)