发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

Mysql数据库详细笔记(跟随b站韩顺平教程)

影子的影子

5

主题

5

帖子

15

积分

新手上路

Rank: 1

积分
15
显示全部楼层
Mysql数据库

一、数据库
  1. mysql服务启动,在cmd输入net start mysql
复制代码
  1. #创建数据库
  2. CREATE DATABASE hsp_db01;
  3. #创建一个使用 utf8 字符集的 hsp_db02 数据库
  4. CREATE DATABASE hsp_db02 CHARACTER SET utf8
  5. #创建一个使用 utf8 字符集，并带校对规则的 hsp_db03 数据库
  6. CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
  7. #校对规则 utf8_bin 区分大小 默认 utf8_general_ci 不区分大小写
  8. ​
  9. #查看当前数据库服务器中的所有数据库
  10. SHOW DATABASES
  11. #查看创建的 hsp_db01 数据库的定义信息
  12. SHOW CREATE DATABASE `hsp_db01`     #为了规避关键字，可以使用反引号
  13. ​
  14. #删除前面创建的 hsp_db01 数据库
  15. DROP DATABASE hsp_db01
复制代码
数据库的备份、恢复（导入）（必须在dos下运行）

备份恢复数据库

mysqldump -u 用户名 -p 密码 -B 数据库名 > 路径（例：d:\\database.sql）
备份恢复数据库的表

备份数据库的多个表不能带-B，否则会被认为是多个数据库
mysqldump -u 用户名 -p 密码 数据库名 表1 表2 > 路径（例：d:\\database.sql）
使用mysqldump命令来恢复一个叫data01数据库的一个叫table01的表(已有备份文件)。具体步骤如下：

  • 打开cmd窗口，输入mysql -u 用户名 -p，然后输入mysql密码，进入mysql数据库。
  • 输入use data01，选择您要恢复的数据库。
  • 输入source d:/table01.sql，执行您的备份文件，恢复您的表。
恢复数据库（必须在mysql命令行下运行）

先进入mysql命令行：mysql -u 用户名 -p 密码
恢复：source 文件路径.sql（例：d:\\database.sql）

左上角写了mysql -u 用户名 -p 的界面才叫mysql命令行
备份，恢复数据库实践

先用mysqldump -u 用户名 -p 密码 -B 数据库名 > 路径（例：d:\\database.sql）
从数据库里备份一个到E盘里，然后在数据库中用drop指令将其删除
然后通过mysql -u 用户名 -p 密码进入mysql命令行，输入source d:\\database.sql将其备份
二、Mysql基本数据类型


基本数据类型的使用细节

数值型：

decimal（M,D)中的M默认为10（最大65），D默认为0（最大30），M是位数，D是小数点后有几位
如果没有指定 unsinged , 就是有符号
  1. #创建一个无符号的tinyint类型数据，0~255
  2. CREATE TABLE t4 (id TINYINT UNSIGNED)
复制代码
字符串型：


VARCHAR虽然可以有65535个字节，但是首先去掉3个字节用于记录字段大小，所以最大为65532个字节，
如果用的编码是UTF-8的话，一个字符消耗3个字节，最多存放65532 / 3 = 21844个字符，
GBK是2个字节，最多存放65532 / 2 = 32766个字符。
  1. #我们来创建一个数据类型为varchar，编码为GBK的表
  2. CREATE TABLE t10 (`name` VARCHAR(32766)) CHARSET gbk;
复制代码




日期类型：

DATE / DATETIME / TIMESTAMP
  1. CREATE TABLE t14 (
  2.         birthday DATE ,      -- 年月日
  3.         job_time DATETIME,    -- 年月日 时分秒
  4.         login_time TIMESTAMP   -- 如果希望 login_time 列自动更新, 需要配置这两句
  5.         NOT NULL DEFAULT CURRENT_TIMESTAMP      -- 不允许为空，默认为当前时间
  6.         ON UPDATE CURRENT_TIMESTAMP             -- 更新当前时间戳  
  7. );
复制代码
三、表

创建表
  1. CREATE TABLE `emp` (
  2. id INT,
  3. `name` VARCHAR(32),
  4. sex CHAR(1),
  5. brithday DATE,
  6. entry_date DATETIME,
  7. job VARCHAR(32),
  8. salary DOUBLE,
  9. `resume` TEXT
  10. )
  11. CHARSET utf8 COLLATE utf8_bin ENGINE INNODB
  12. #charset是character set的简写，即字符集
  13. #字符集，校验规则，存储引擎不写的话就是默认的（默认的话跟随当前数据库）
复制代码
要用cmd在一个叫database的数据库下新建一个表，你需要先用mysql命令登录到数据库，然后用use命令选择database数据库，再用create table命令创建表。具体的步骤如下：

  • 打开cmd，输入mysql -u root -p，回车后输入密码，登录到数据库。
  • 输入use database;，选择database数据库。
  • 输入如上代码完成创建。
删除表
  1. DROP TABLE `emp`
复制代码
修改表

举例演示:
  1. ALTER TABLE emp
  2. -- 员工表 emp 的上增加一个 image 列，varchar 类型(要求在 birthday 后面)。
  3. ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER birthday ;
  4. -- DEFAULT ''默认为''这个字符
  5. ​
  6. -- 修改 job 列，使其长度为 60。
  7. ALTER TABLE emp
  8. MODIFY job VARCHAR(60) NOT NULL DEFAULT '' ;
  9. ​
  10. -- 删除 sex 列。
  11. ALTER TABLE emp
  12. DROP sex;
  13. ​
  14. -- 表名改为 employee。
  15. RENAME TABLE emp TO employee;
  16. ​
  17. -- 修改表的字符集为 utf8
  18. ALTER TABLE emp CHARACTER SET utf8;
  19. ​
  20. -- 列名 name 修改为 user_name
  21. ALTER TABLE emp;
  22. ​
  23. -- 显示表结构，可以查看表的所有列
  24. DESC emp ;
复制代码
四、CRUD增删改查

1. Insert
  1. 用法:
  2. INSERT INTO 表名 (列名1,列名2...)
  3. values (对应列名的值);
复制代码
  1. INSERT INTO `emp` (id, birthday)
  2. VALUES(1, '2003-05-15');        -- 注意date等日期类型一定要按照规则写否则报错或者错位写入
复制代码
insert使用细节


  • 插入的数据应与字段的数据类型相同
  • 数据的长度应在列的规定范围内
  • 在 values 中列出的数据位置必须与被加入的列的排列位置相对应
  • 字符和日期型数据应包含在单引号中
  • 列可以插入空值[前提是该字段允许为空]
  • 可以用 values ( ),( ) ,( ) ; 的形式添加多条记录
    1. INSERT INTO `goods` (id, goods_name, price)
    2. VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800);
    复制代码
  • 如果是给表中的所有字段添加数据，可以不写前面的字段名称
  • 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
    -- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null
    -- 如果我们希望指定某个列的默认值，可以在创建表时通过 not null default xxx 指定
  1. #表里有3个数据,id,goodname,price有默认值10
  2. INSERT INTO `goods` (id, goods_name)
  3. VALUES(80, '格力手机');
  4. #最后会插进去一个(80,格力手机,10)进去
复制代码
 
2. Update
  1. 用法:
  2. UPDATE 表名 SET 列名 = 值 WHERE 布尔表达式，筛选满足条件的行
复制代码
  1. #将所有员工薪水修改为 5000 元。如果不带 where 条件，会修改该列所有的记录，因此要小心
  2. UPDATE employee SET salary = 5000;
  3. <h1 id="将姓名为-小妖怪-的员工薪水修改为-3000-元">将姓名为 小妖怪 的员工薪水修改为 3000 元</h1>
  4. <p>UPDATE employee
  6. SET salary = 3000
  8. WHERE user_name = '小妖怪';</p>
  9. <h1 id="也可以修改多个列的值">也可以修改多个列的值</h1>
  10. <p>UPDATE employee
  12. SET salary = salary + 1000 , job = '出主意的'
  14. WHERE user_name;</p>
复制代码
 
3. Delete
  1. 用法:
  2. DELETE FROM 表名
  3. where 布尔等值式;   -- 如果不带 where 条件，会修改所有记录,小心删库跑路
复制代码
  1. #删除表中名称为’老妖怪’的记录。
  2. DELETE FROM employee
  3. WHERE user_name = '老妖怪';
复制代码
delete使用细节


  • delete不能删除某一列的值，只能使用update将列设为空
  • 如果不带 where 条件，会修改所有记录,小心删库跑路！！！
     
4. 单表Select

用法1：基础查询
  1. SELECT [DISTINCT] * 或 指定列 FROM 表名;   
  2. -- distinct功能是去重(写在from前的每一个列的值都相同才去)
  3. -- *代表所有列,也可以写多个列(用逗号隔开)
  4. <p>比如:
  6. -- 查询表中所有学生的信息
  8. SELECT * FROM student;</p>
  9. <p>-- 查询表中所有学生的姓名和对应的英语成绩
  11. SELECT name,english FROM student;</p>
  12. <p>-- 过滤表中重复数据
  14. SELECT DISTINCT name,english FROM student;</p>
复制代码
用法2：列名使用表达式进行运算
  1. 比如:
  2. -- 统计每个学生的总分
  3. SELECT `name`, (chinese + english + math ) FROM student;   
  4. #但是使用这种方法系统会自动命名第二列为chinese + english + math,所以我们可以采用下面的方式美化一下
  5. <p>-- 使用AS给列命名
  7. SELECT name AS '名字', (chinese + english + math ) AS total
  9. FROM student;</p>
复制代码
常用运算符表


用法3: 使用where子句运算符,进行过滤查询
  1. SELECT [DISTINCT] * 或 指定列 FROM 表名 WHERE 布尔表达式;        -- select定最后显示多少列,from定从哪个表查询,where定最后显示满足条件的行
  2. <p>比如:
  4. -- 查询姓名为赵云的学生成绩
  6. SELECT * FROM student
  8. WHERE name = '赵云';</p>
  9. <p>-- 查询 math 大于 60 并且(and) id 大于 4 的学生成绩
  11. SELECT * FROM student
  13. WHERE math >60 AND id > 4;</p>
  14. <p>-- 查询英语成绩大于语文成绩的同学
  16. SELECT * FROM student
  18. WHERE english > chinese;</p>
  19. <p>-- 查询总分大于 200 分 并且 数学成绩小于语文成绩 并且 姓赵的学生, 赵% 表示名字以赵开头
  21. SELECT * FROM student
  23. WHERE (chinese + english + math) > 200 AND
  25. math < chinese AND name LIKE '赵%';</p>
  26. <p>-- 查询英语分数在 80－90 之间的同学。
  28. SELECT * FROM student
  30. WHERE english >= 80 AND english <= 90;
  32. SELECT * FROM student
  34. WHERE english BETWEEN 80 AND 90; -- between .. and .. 是 闭区间</p>
  35. <p>-- 查询数学分数为 89,90,91 的同学。
  37. SELECT * FROM student
  39. WHERE math = 89 OR math = 90 OR math = 91;
  41. SELECT * FROM student
  43. WHERE math IN (89,90,91);</p>
复制代码

  • 把子查询当做临时表使用 : 避免mysql重复计算表，提高查询性能，并且可以解决很多复杂的查询
    1. SELECT * FROM 表名 ORDER BY 列名(排序的依据) [DESC];                -- 默认为升序,DESC改为降序
    2. 比如:
    3. -- 对数学成绩排序后输出【升序】。
    4. SELECT *
    5. FROM student
    6. ORDER BY math;
    7. <p>-- 对总分按从高到低的顺序输出 [降序] -- 使用别名排序
    9. SELECT name , (chinese + english + math) AS '总分'
    11. FROM student
    13. ORDER BY total_score DESC;</p>
    14. <p>-- 对姓韩的学生成绩[总分]排序输出(升序) where + order by
    16. SELECT name, (chinese + english + math) AS '总分' FROM student
    18. WHERE name LIKE '韩%'
    20. ORDER BY total_score;</p>
    复制代码
合并查询


  • union all 单纯取并集不去重
  • union 去重
    1. 比如：
    2. -- %  表示 0 到多个的任意字符
    3. -- _  表示单个任意字符  其中GBK中需要两个_ _来表达一个字符
    4. <p>-- 显示首字符为 S 的员工姓名和工资
    6. SELECT name, salary FROM emp
    8. WHERE name LIKE 'S%'
    10. -- 显示第三个字符为大写 O 的所有员工的姓名和工资
    12. SELECT name, salary FROM emp
    14. WHERE name LIKE '__O%'</p>
    复制代码
6.连接

自然连接

通常用于将两个关系表中具有相同属性的行组合在一起，以便我们可以从两个表中同时获取信息。
自然连接是一种特殊的等值连接（区别在于等值连接需要指定值），他要求两个表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。
natural表1：

natural表2：
  1. DESC 表名
复制代码
说人话，直接先把两张表的列名去重后列出来

然后开始对比他们两相同的列（id）的值，我们就找到id = 2时相同，直接把左表和右表连接起来，结果：

内连接

内连接基本与自然连接相同，不同之处在于自然连接的是同名属性列的连接，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。
  1. select * from table limit 参数1,参数2;          -- 参数1 : 代表偏移量offset（从第offset + 1数据开始查），注意是从0开始，所以应该+1代表第一个数据
  2.                                                                                 -- 参数2 : 取出的数据条数rows
  3. /* 查询第1-10条数据 */
  4. SELECT * FROM Student LIMIT 0,10;
  5. /* 查询第11-20条数据 */
  6. SELECT * FROM Student LIMIT 10 , 10;        -- 讲到这我们会想知道如何快速算出 参数1，接下来我们看一下计算公式
复制代码
外连接

通常用于查询在一张表中存在但在另一张表中不存在的记录

  • 左外：只保留左表的所有元素，右表没有则置null
  • 右外：同理
  • 全外连接 ：mysql不支持
    1. #获得数据总条数
    2. SELECT COUNT(*) FROM Student;
    3. #假设每页显示10条，则直接进行除法运算，然后向上取整，这样我们就获得了总页数，在实际操作中，要注意一下是否超出了最大页数
    4. SELECT CEIL(COUNT(*) / 10) AS '总页数' FROM Student;
    复制代码
     
五、函数

1.聚簇函数（合计、统计）

count：
  1. 自连接的特点
  2. 1. 把同一张表当做两张表使用
  3. 2. 需要给表取别名 如果from同一张表两次，会报错，所以需要对表重命名，无需使用 AS，直接表加别名就好
  4. 3. 列名有时不明确，可以通过 AS 指定列的别名
  5. <p>举例：</p>
  6. <h1 id="显示公司员工名字和他上级的名字员工名字和上级的名字都在emp表">显示公司员工名字和他上级的名字，员工名字和上级的名字都在emp表</h1>
  7. <h1 id="其中员工和上级是通过-emp-表的-manager列-关联">其中员工和上级是通过 emp 表的 manager列 关联</h1>
  8. <p>SELECT worker.name AS '职员名' ,
  10. boss.name AS '上级名'
  12. FROM emp worker, emp boss
  14. WHERE worker.manager = boss.empno</p>
复制代码
sum:
  1. #多行子查询中使用in运算符
  2. #查询和10号部门的工作相同的雇员的 名字、岗位、工资、部门号, 但是不含10号部门的员工
  3. select ename, job, sal, deptno
  4. from emp
  5. where job in (
  6. SELECT DISTINCT job
  7. FROM emp
  8. WHERE deptno = 10
  9. ) and deptno != 10
  10. <h1 id="多行子查询中使用-all-操作符">多行子查询中使用 all 操作符</h1>
  11. <h1 id="查询工资比部门-30-的所有员工的工资高的员工的姓名工资和部门号">查询工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号</h1>
  12. <p>SELECT ename, sal, deptno
  14. FROM emp
  16. WHERE sal > ALL(
  18. SELECT sal
  20. FROM emp
  22. WHERE deptno = 30
  24. )</p>
  25. <h1 id="多行子查询中使用-any-操作符">多行子查询中使用 any 操作符</h1>
  26. <h1 id="查询工资比部门-30-的任意其中一个员工的工资高的员工的姓名工资和部门号">查询工资比部门 30 的任意/其中一个员工的工资高的员工的姓名、工资和部门号</h1>
  27. <p>SELECT ename, sal, deptno
  29. FROM emp
  31. WHERE sal > any(
  33. SELECT sal
  35. FROM emp
  37. WHERE deptno = 30
  39. )</p>
复制代码
avg:
  1. #查找每个部门工资 高于 他们自己部门平均工资的人的资料
  2. -- 1. 先得到每个部门的 部门号和 对应的平均工资
  3. SELECT deptno, AVG(sal) AS avg_sal
  4.         FROM emp
  5.         GROUP BY deptno  -- 当聚合列和非聚合列出现在一起时必须使用group by语句
  6. <p>-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
  8. SELECT ename, sal, temp.avg_sal, emp.deptno
  10. FROM emp,（
  12. SELECT deptno, AVG(sal) AS avg_sal
  14. FROM emp
  16. GROUP BY deptno
  18. ) temp        -- 把子查询当作一个临时表使用，重命名为temp
  20. WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal</p>
复制代码
使用group by 对列进行分组 ：

注意：当聚合列和非聚合列出现在一起时必须使用group by语句
  1. 举例：
  2. -- union all 就是将两个查询结果合并，不会去重
  3. SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5条
  4. UNION ALL
  5. SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3条
  6. -- 最后有8条
复制代码
使用 having 对group by 分组后的结果进行过滤
  1. select 列名 from 左表 natural join 右表
  2. -- 将2个表自然连接一下
  3. SELECT * FROM `natural` NATURAL JOIN `natural2`
复制代码
2.字符串函数

3.数学函数

4.日期函数

5.加密函数

MySQL有一些加密函数，可以用来对字符串进行加密和解密。常用的有以下几种：
  1. select 列名 from 左表 inner join 右表 on 条件  -- inner可以省略
复制代码
 
六、约束

MySQL约束语句是用来限制表中数据的条件。MySQL支持以下几种约束：

  • 主键（PRIMARY KEY）：唯一标识表中的每一行。
  • 外键（FOREIGN KEY）：引用另一个表中的主键或唯一键。
  • 唯一（UNIQUE）：保证列中的值不重复。
  • 非空（NOT NULL）：保证列中的值不为空。
  • 检查（CHECK）：保证列中的值满足指定的条件。
  • 默认（DEFAULT）：为列提供默认值。
可以在创建表或修改表时添加或删除约束。
我来介绍一下这6种约束的用法。
主键（PRIMARY KEY）

不能重复而且不能为 null
在创建表时，可以使用PRIMARY KEY关键字来指定一个或多个列作为主键，例如：
  1. 用法：
  2. select 列名 from 左表 left join 右表 on 条件
  3. select 列名 from 左表 right join 右表 on 条件
  4. <h1 id="下面是一个简单的例子演示如何使用外连接查询两张表中的数据">下面是一个简单的例子，演示如何使用外连接查询两张表中的数据。</h1>
  5. <p>假设我们有两张表：员工表和部门表。员工表包含员工的姓名和所属部门，部门表包含部门名称和部门编号。</p>
  6. <h1 id="员工表">员工表：</h1>
  7. <p>姓名          部门编号
  9. 张三                1
  11. 李四                2
  13. 王五                3
  15. 赵六                NULL</p>
  16. <h1 id="部门表">部门表：</h1>
  17. <p>部门编号        部门名称
  19. 1                        销售部
  21. 2                        人事部
  23. 如果我们想要查询所有员工及其所属部门，可以使用以下SQL语句：</p>
  24. <p>SELECT 员工姓名, 部门名称 FROM 员工表 LEFT JOIN 部门表 ON 员工的部门编号 = 部门的编号;</p>
  25. <p>执行上述语句后，将得到以下结果：</p>
  26. <p>姓名        部门名称
  28. 张三        销售部
  30. 李四        人事部
  32. 王五        NULL
  34. 赵六        NULL
  36. 可以看到，上述查询结果中包含了员工表中所有记录，并且显示了每个员工所属的部门。对于那些在部门表中没有匹配记录的员工（即王五和赵六），其所属部门显示为NULL。</p>
复制代码
也可以在创建表后，使用ALTER TABLE语句来添加或删除主键，例如：
  1. SELECT COUNT(*) 或 (列)FROM 表名 WHERE 表达式;
  2. -- count(*) 和 count(列) 的区别
  3. -- count(*) 返回满足条件的行数
  4. -- count(列): 统计满足条件的某列个数，会排除为 null 的
复制代码
外键（FOREIGN KEY）

外键是用于建立和加强两个表数据之间的链接的列。 外键的作用是保持数据的一致性、完整性，主要体现在下面两个方面

  • 约束作用：外键可以防止在外键所在表中插入无效的数据，或者删除外键指向表中引用的数据。
  • 连接作用：外键可以通过连接操作实现两个表之间的联合查询。
在创建表时，可以使用FOREIGN KEY关键字来指定一个或多个列作为外键，并引用主表中的主键(primary key)或唯一键(unique)
  1. SELECT SUM(列) FROM 表名 WHERE 表达式;
  2. 比如:
  3. -- 统计一个班级语文、英语、数学各科的总成绩并使用别名
  4. SELECT SUM(math) AS math_total_score,
  5. SUM(english) AS english_total_score,
  6. SUM(chinese) AS chinese_total_score
  7. FROM student;
  8. <p>-- 统计一个班级语文成绩平均分
  10. SELECT SUM(chinese)/ COUNT(*) FROM student;</p>
  11. <p>-- 注意有些数据类型不能统计比如字符串之类
  13. SELECT SUM(name) FROM student;  #不报错但无意义</p>
复制代码

唯一（UNIQUE）


  • 在创建表时，可以使用UNIQUE关键字来指定一个或多个列的值不重复，例如：
  1. SELECT AVG(列) FROM 表名 WHERE 表达式;
  2. 比如:
  3. -- 求一个班级数学平均分？
  4. SELECT AVG(math) FROM student;
  5. <p>-- 求一个班级总分平均分
  7. SELECT AVG(math + english + chinese) FROM student;</p>
  8. <p>-- 求班级最高分和最低分
  10. SELECT MAX(math + english + chinese), MIN(math + english + chinese)
  12. FROM student;</p>
  13. <p>-- 求出班级数学最高分和最低分并使用别名
  15. SELECT MAX(math) AS math_high_socre,
  17. MIN(math) AS math_low_socre
  19. FROM student;</p>
复制代码
非空（NOT NULL）

在创建表时，可以使用NOT NULL关键字来指定一个列的值不为空，例如：
  1. SELECT 列名 FROM 表名 group by 列名(从select后面的几个列名之中挑);
  2. 比如:
  3. -- 显示每个部门的每种岗位的平均工资和最低工资
  4. SELECT AVG(salary), MIN(salary) ,  department, job
  5. FROM emp GROUP BY department, job;
  6. <p>-- GROUP BY也可以对查询结果进行去重</p>
复制代码
检查（CHECK）


  • 在创建表时，可以使用CHECK关键字来强制指定一个列的值满足指定的条件，（注意MySQL目前不支持检查约束）
  1. SELECT 列名 FROM 表名 group by 列名 having 布尔表达式;
  2. 比如:
  3. -- 显示平均工资低于 2000 的部门编号和它的平均工资 并使用别名
  4. SELECT AVG(salary) AS '平均工资',
  5. deptno AS '部门编号'
  6. FROM emp
  7. GROUP BY deptno
  8. HAVING AVG(salary) < 2000;        # 注意HAVING 子句中不能使用别名，只能使用原始的表达式
复制代码
默认（DEFAULT）


  • 在创建表时，可以使用DEFAULT关键字来为列提供默认值，在插入数据时如果没有指定该列的值，则会自动填充默认值。默认值必须是常量。如果要设置当前日期时间作为默认值，则需要用CURRENT_TIMESTAMP函数。例如：
  1. AES_ENCRYPT(str,key)：返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储。
  2. <p>AES_DECRYPT(crypt_str,key)：返回用密钥key对字符串crypt_str利用高级加密标准算法解密后的结果1。</p>
  3. <p>DECODE(str,key)：使用key作为密钥解密加密字符串str2。</p>
  4. <p>ENCODE(str,key)：使用key作为密钥加密字符串str2。</p>
  5. <p>MD5(str)：返回str的MD5校验和，是一个32位十六进制数2。</p>
  6. <h1 id="演示md5使用">演示MD5使用</h1>
  7. <p>-- 插入
  9. INSERT INTO users (username, password) VALUES ('admin', MD5('123456'));
  11. -- 这样就可以把用户名和密码加密后存储在users表中。你也可以使用MD5函数来验证用户输入的密码是否正确，例如：
  13. SELECT * FROM users WHERE username = 'admin' AND password = MD5('123456');</p>
  14. <p>这样就可以根据用户名和密码查询users表中是否有匹配的记录。你明白了吗？</p>
复制代码
七、自增长

目的：当我们希望一列每插一个数据进去会自动增长时可以使用自增长,这样可以避免手动输入主键值，也可以保证主键值的唯一性
要实现MySQL自增长，需要满足以下条件：

  • 只能有一个字段使用AUTO_INCREMENT约束
  • 该字段必须有唯一索引（主键或主键的一部分）
  • 该字段必须具备NOT NULL属性（要么直接primary key 要么就加 not null ）
  • 该字段只能是整型（TINYINT、SMALLINT、INT、BIGINT)
  1. CREATE TABLE student (
  2.   id INT PRIMARY KEY,
  3.   name VARCHAR(20),
  4.   age INT
  5. );
  6. #复合主键
  7. CREATE TABLE t(
  8.     id INT ,
  9.     `name` VARCHAR(32),
  10.         PRIMARY KEY (id, `name`)         -- 这里就是复合主键
  11. );
复制代码
八、索引

MySQL索引是一种帮助MySQL高效获取数据的数据结构。它可以根据一列或多列的值进行排序，从而加快数据库的查询速度
MySQL中常用的索引结构有B-TREE，B+TREE，HASH等。不同的索引结构适用于不同的查询场景，例如：

  • B-TREE索引适用于全键值、键值范围和前缀查找
  • B+TREE索引是B-TREE索引的改进版，具有更高的查询效率和空间利用率
  • HASH索引适用于等值查找，但不支持范围查找和排序
  1. ALTER TABLE student ADD PRIMARY KEY (id);
  2. ALTER TABLE student DROP PRIMARY KEY;
复制代码
数据库索引的优点与缺点


  • 优点：

    • 索引可以提高数据检索的效率，降低数据库的I/O成本
    • 索引可以通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
    • 索引可以加快表与表之间的连接，提高关联查询的性能
    • 索引可以保证行或列的唯一性，生成唯一的row

  • 缺点：

    • 索引需要占用额外的存储空间，增加数据库的负担
    • 索引会降低数据更新（插入、删除、修改）的速度，因为每次更新都需要维护索引结构
    • 不恰当地使用索引可能导致查询性能下降或者无法使用索引

创建索引的条件


九、事务

事务是指一组操作量大，复杂度高的数据操作，它们要么都执行成功，要么都执行失败，不会出现中间状态。

  • 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成，不会停滞在中间某个环节。
  • 一致性（Consistency）：事务在完成时，必须使所有数据恢复到一致性状态。在一致性状态下，所有相关的数据规则都得到满足。
  • 隔离性（Isolation）：事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间要相互隔离。
  • 持久性（Durability）：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。
MySQL默认每条DML（增删改）操作都是一个事务。可以通过 SHOW VARIABLES LIKE 'autocommit’ 查看自动提交模式是否开启。如果关闭自动提交模式，则需要手动使用COMMIT或ROLLBACK来提交或回滚事务
MySQL只有使用了InnoDB数据库引擎的数据库或表才支持事务。
  1. 用法:FOREIGN KEY (外键所在表的外键列列名) REFERENCES 主表名(主表中的主键或唯一键)
  2. <p>比如:
  4. -- 创建 主表 my_class  (外键指向表)
  6. CREATE TABLE my_class (
  8. id INT PRIMARY KEY ,         -- 班级编号
  10. name VARCHAR(32) NOT NULL DEFAULT ''
  12. );</p>
  13. <p>-- 创建 从表 my_stu          (外键所在表)
  15. CREATE TABLE my_stu (
  17. id INT PRIMARY KEY , -- 学生编号
  19. name VARCHAR(32) NOT NULL DEFAULT '',
  21. class_id INT , -- 学生所在班级的编号
  23. FOREIGN KEY (class_id) REFERENCES my_class(id)        -- 外键
  25. );</p>
复制代码
十、事务隔离级别

MySQL事务的隔离级别。事务的隔离级别是指多个并发事务之间如何相互影响的问题，不同的隔离级别会导致不同的并发问题。
四个事务隔离级别


  • 读未提交（read uncommitted）：最低的隔离级别，一个事务可以读取另一个未提交事务的修改数据，可能会出现脏读，不可重复读，幻读
  • 读已提交（read committed）：一个事务只能读取另一个已提交事务的修改数据，可以避免脏读，但可能会出现不可重复读、幻读
  • 可重复读（repeatable read）：MySQL默认隔离级别，一个事务在执行期间看到的数据始终和这个事务在启动时看到的数据是一致的，可以避免脏读和不可重复读，但可能会出现幻读，不过InnoDB实现的RR通过mvcc机制避免了这种幻读现象。对于MVCC以及如何在RR上解决幻读的理解 - 知乎 (zhihu.com)面试重点之一，核心就是采用了一个间隙锁（Next-key lock），行锁只能锁一行，所以对于幻读这种范围查询的数据不能避免数据的插入，
  • 可串行化（serializable）：最高的隔离级别，所有事务都必须按照顺序执行，可以避免所有并发问题，但性能最差。
那么脏读、不可重复读、幻读都是什么意思呢?

  • 脏读（dirty read）：一个事务读取了另一个还未提交事务的修改数据，如果后者回滚了操作，则前者所读取的数据就是不正确的
  • 不可重复读（non-repeatable read）：一个事务在执行期间多次读取同一份数据(比如某行某列的一个值)，但由于另一个事务对该数据进行了update并提交，导致前者两次读取的结果不一致
  • 幻读（phantom read）：一个事务在执行期间多次查询某段数据(比如一张表)，但由于另一个事务对该段数据进行了delete，insert并提交，导致前者查询的结果不一致
  • 不可重复读的重点是修改:
    同样的条件, 你读取过的数据, 再次读取出来发现值不一样了
    幻读的重点在于新增或者删除
    同样的条件, 第1次和第2次读出来的记录数不一样
    当然, 从总的结果来看, 似乎两者都表现为两次读取的结果不一致.
    但如果你从控制的角度来看, 两者的区别就比较大

    • 对于前者, 只需要锁住满足条件的记录
    • 对于后者, 要锁住满足条件及其相近的记录


设置事务隔离级别
  1. CREATE TABLE user (
  2.   id INT PRIMARY KEY,
  3.   username VARCHAR(20) UNIQUE NOT NULL,                -- unique + not null = primary key
  4.   password VARCHAR(20) NOT NULL
  5. );
复制代码
十一、视图
  1. CREATE TABLE book (
  2.   id INT PRIMARY KEY,
  3.   title VARCHAR(50) NOT NULL,
  4. );
复制代码

十二、用户管理
  1. CREATE TABLE employee (
  2.   id INT PRIMARY KEY,
  3.   name VARCHAR(20) NOT NULL,
  4.   salary DECIMAL(10,2),
  5.   CHECK (salary >0)
  6. );
复制代码
细节说明



  1. CREATE TABLE post (
  2.   id INT PRIMARY KEY,
  3.   title VARCHAR(50) NOT NULL,
  4.   content TEXT,
  5.   create_time DATETIME DEFAULT CURRENT_TIMESTAMP
  6. );
复制代码

来源:https://www.cnblogs.com/hanlinyuan/p/17259881.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x

上一篇: 支持10倍订单增长，携程数据库架构升级实践

下一篇: P3 创建数据库

发表于 2023-3-27 02:06:40

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4