Day07_MySQLLearning

2023-08-02 08:48:10

Day07_MySQLLearning

33_MySQL函数使用注意事项

如果mysql函数和PHP函数都实现某个功能，优先使用哪一个？
1. mysql 的函数肯定是要降低查询速度的。应该在建表时，通过合理的表结构减少函数的使用。比如email ，按 “@” 前后拆分
2. 如果确实要用函数，比如时间的格式化，在mysql里用date_format，在PHP里用date可以实现，此时优先放在业务逻辑层处理，即PHP层
3. 在查询时使用了函数，最大的坏处是该列的索引将无法使用。以date_format（A） 为例，则A列的索引将无法使用。
  举例：select name , email from table where right(email , length(email) - position(’@’ in email)) = ‘qq.com’ email列是有索引的，可以加快查询速度，但是因为使用的并不是email列，而是函数处理后的email的返回值，因此email列的查询速度就非常缓慢了。
  总结： where条件中，对某列使用了函数，则此列的索引不发挥作用

34_视图及与表的关系

视图 view
- 在查询中我们经常把查询结果，当成临时表来看
- view 是什么？
  view可以看成一张虚拟表，是表通过某种运算得到的一个投影
创建视图的语法
```
create view 视图名
as
select 语句
```
视图的作用
1. 可以简化查询
  比如，复杂的统计时，先用视图生成一个中间结果，再查询视图
2. 实现更精细的权限控制
  比如某张表，用户表为例，现在有两个网站搞合作，可以查询对方网站的用户，需要向对方开放用户表的权限，但是又不想开放用户表中的密码字段
```
create view vuser
as
select user_id , username , email from user;
```
  开放这个视图的权限给对方
3. 数据多，分表时可以用到
  比如小说站，novel表，1000多万篇，分成novel1 ， novel2 ， novel3…novel5 这5张表，每张表放200万条，查询小说时不知在哪张表，这时可以建立视图：
```
create view novel as
select title from novel1 union select title from novel2 ... union select title novel5; 
```
表与视图数据变化时的相互影响问题
视图是表的一个影子，表的数据变化要影响到视图的变化，视图如果变了，表如何变？

以这个例子而言，平均价来自于多行的shop_price的计算结果
如果pj列的值变了，映射过去，到底修改那几行shop_price?
视图某种情况下，是可以修改的
要求： 视图的数据和表的数据一一对应，就像函数的映射：表–>推出视图对应的数据，视图–>推出表对应的数据

代码：

mysql> set names gbk;
Query OK, 0 rows affected (0.04 sec)
mysql> use worker;
Database changed
mysql> #视图的学习
mysql> #查询每个栏目下商品的平均价格，并取平均价前3高的额栏目
mysql> select cat_id , avg(shop_price) as pj
    -> from goods
    -> group by cat_id
    -> order by pj
    -> limit 3;
mysql> select cat_id , avg(shop_price) as pj
    -> from goods
    -> group by cat_id ;
mysql> select cat_id , avg(shop_price) as pj
    -> from goods
    -> group by cat_id
    -> order by shop_price asc
    -> limit3;
mysql> #让你取平均价格前3低的栏目
mysql> select cat_id , avg(shop_price) as pj
    -> from goods
    -> group by cat_id
    -> order by shop_price asc;
mysql> select cat_id , avg(shop_price) as pj
    -> from goods
    -> group by cat_id
    -> order by shop_price asc
    -> limit 3;
mysql> #平均价格由高到低，第三到第五名的栏目
mysql> #group by cat_id , 查询出每个栏目下的平均价格，设为结果集A
mysql> #无论是想查询前三高，还是前三低，都要用到结果集A
mysql> #结果集A频繁用到，因此，可以m把
mysql> #结果集A频繁用到，因此，可以把结果保存到一张表，下次来查这张表
mysql> #但是，如果goods表又添加了商品，A结果集就与你保存的临时表不一样了
mysql> #这是，我们可以用视图来解决
mysql> create view stats
    -> as 
    -> select cat_id , avg(shop_price) as pj
    -> from goods
    -> group by cat_id ;
mysql> show tables;
+------------------+
| Tables_in_worker |
+------------------+
| a                |
| account          |
| b                |
| boy              |
| class            |
| girl             |
| grades           |
| interview        |
| jiaban           |
| m                |
| member           |
| n1               |
| salary           |
| t                |
| test             |
| test10           |
| test11           |
| test12           |
| test13           |
| test14           |
| test15           |
| test2            |
| test3            |
| test4            |
| test5            |
| test6            |
| test7            |
| test9            |
+------------------+
28 rows in set (0.47 sec)
mysql> #多了一张stats"表"
mysql> select * from stats;
mysql> #查栏目平均价格前三高
mysql> select * from stats order by pj limit 3;
mysql> #视图一旦创建完毕，就可以像表一样查询
mysql> select * from stats;
mysql> select goods_id , cat_id , shop_price from goods where cat_id = 15;
mysql> update goods set shop_price = 65 where goods_id = 28;
mysql> select goods_id , cat_id , shop_price from goods where cat_id = 15;
mysql> select * from stats;
mysql> #视图到底能否增删改呢？
mysql> create view goods4
    -> as 
    -> select * from goods where cat_id = 4;
mysql> select goods_id , shop_price , goods_name from goods4;
mysql> delete from good4 where goods_id = 1;
mysql> select goods_id , shop_price , goods_name from goods4;
mysql> select goods_id , shop_price , goods_name from goods;
mysql> drop view goods4;
mysql> exit

35_视图algorithm的概念

了解
对于一些简单的视图，它在返回作用的过程中，并没有建立临时表，而只是把条件存起来，下次来查询，把条件一合并，直接去查表
algorithm的用法
create algorithm = merge(合并查询语句) / temptable(临时表) / undefined(未定义，由系统判断) view 视图名

代码：

mysql> set names gbk;
Query OK, 0 rows affected (0.03 sec)
mysql> use worker;
Database changed
mysql> #建一张简单的查询视图，不用临时表，只用条件合并
mysql> create view v1
    -> as
    -> select * from goods where shop_price > 300;
mysql> #查询视图
mysql> select goods_id , goods_name , shop_price from v1 where shop_price < 500;
mysql> #总的条件，就是>300, <500
mysql> #这个简单的查询还建临时表的话，开销有点大
mysql> #这时我们可以指定algorithm选项为merge
mysql> create algorithm = merge view v2
    -> as
    -> select * from goods where shop_price > 300;
mysql> select goods_id , goods_name , shop_price from v2 where shop_price <500;
mysql> #虽然从结果上看不出区别，但是这个v2视图，并没有建立临时表
mysql> #有的时候，必须建立临时表
mysql> #比如，每个栏目的平均商品价格
mysql> create algorithm = temptable view v3
    -> as 
    -> select goods_id , cat_id , goods_name , shop_price
    -> from goods
    -> order by cat_id asc , shop_price desc;
mysql> #这张表，明确指定了生成临时表
mysql> #如果我拿不准用什么，algorithm = undefined ， 让系统为我们做决定。
mysql> exit

36_GB2312与UTF8编码

Unicode与utf-8的关系

就像源文件 --> 压缩文件的关系
UTF-8与Unicode的关系
如何截取utf8（各国语言都有）,无乱码？
1. 从头开始，取1个字节
2. 通过位运算，计算连续的1的个数
3. 个数为0，则再往后截取1个字节
  个数为N，则再往后截取N个字节
乱码原因

37_MySQL字符集参数

连接器的特性：
- 连接客户端与服务器
- 客户端的字符先发给连接器
- 连接器选择一种编码将其转换，临时存储
- 再次转换成，服务器需要的编码，并存储在服务器
数据库防止乱码的方法
1. 正确指定客户端的编码
2. 合理选择连接器的编码
3. 正确指定返回内容的编码

代码：

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use worker;
Database changed
mysql> #要想不乱码，需要指定客户端的编码
mysql> #让连接器不理解错误
mysql> #这样就不会存入错误数据
mysql> #往回取的时候，我们还要告诉连接器，如果你从服务器返回，应该给我转什么格式
mysql> #一共是3个参数，客户端的发送编码
mysql> #连接器使用的编码
mysql> #获取的返回数据的编码
mysql> 
mysql> 
mysql> #当前的情况是，客户端GBK，服务器最终存UTF8
mysql> #明确的告诉服务器
mysql> #我的客户端是GBK的
mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.10 sec)
mysql> #再告诉连接器，使用utf8
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> #再告诉，如果返回值，请返回GBK结果
mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> desc test15;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| sname  | varchar(5) | YES  |     | NULL    |       |
| gender | tinyint(4) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.61 sec)
mysql> insert into test15 values('编码不乱' , 1);
Query OK, 1 row affected (0.08 sec)
mysql> select * from test15;
+----------+--------+
| sname    | gender |
+----------+--------+
| 张三     |      1 |
| 韩梅梅   |      0 |
| 李宇春   |      2 |
| 编码不乱 |      1 |
+----------+--------+
4 rows in set (0.03 sec)
mysql> #但是，我客户端传的是GBK，但我骗对方，是utf8
mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test15 values('编码乱不乱' , 1);
ERROR 1366 (HY000): Incorrect string value: '\xB1\xE0\xC2\xEB\xC2\xD2...' for column 'sname' at row 1
mysql> #此时，“编码不乱”对应的GBK的内码，转成utf8压根就出错，不允许插件入
mysql> #我们服务器如果检测不严格，可以插入，但会丢失数据
mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> #我偏要对方还给我的数据是utf8
mysql> select * from test15;
+----------+--------+
| sname    | gender |
+----------+--------+
| 张三     |      1 |
| 韩梅梅   |      0 |
| 李宇春   |      2 |
| 编码不乱 |      1 |
+----------+--------+
4 rows in set (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test15;
+--------------+--------+
| sname        | gender |
+--------------+--------+
| 寮犱笁       |      1 |
| 闊╂姊?   |      0 |
| 鏉庡畤鏄?   |      2 |
| 缂栫爜涓嶄贡 |      1 |
+--------------+--------+
4 rows in set (0.00 sec)
mysql> #再看另一种情况
mysql> #声明客户端是GBK
mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> #在声明连接器是GBK
mysql> set character_set_connection = gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> #再声明返回值是GBK
mysql> set character_set_results = gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> #如上操作，会不会乱码？
mysql> insert into test15 values ('真不乱？' , 1);
Query OK, 1 row affected (0.02 sec)
mysql> select * from test15;
+----------+--------+
| sname    | gender |
+----------+--------+
| 张三     |      1 |
| 韩梅梅   |      0 |
| 李宇春   |      2 |
| 编码不乱 |      1 |
| 真不乱？ |      1 |
+----------+--------+
5 rows in set (0.00 sec)
mysql> set character_set_connection = latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test15 values('还不乱？' , 1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test15;
+----------+--------+
| sname    | gender |
+----------+--------+
| 张三     |      1 |
| 韩梅梅   |      0 |
| 李宇春   |      2 |
| 编码不乱 |      1 |
| 真不乱？ |      1 |
| ????     |      1 |
+----------+--------+
6 rows in set (0.00 sec)
mysql> #latin1小，GBK大，就像大鱼过小鱼网的时候，丢了块肉
mysql> #这次的乱码能否修复？
mysql> #不能
mysql> #服务器 >= connection >= client
mysql> #再回头看：太巧了，刚才，client , connection , results都是GBK
mysql> #如果3者，都是GBK，可以简写
mysql> #简写成set names gbk;
mysql> exit

38_utf8的BOM问题

小知识：
- 在XP下，用记事本创建utf8文件的时候，前面多了3个字节
- 这3个字节（EF BB BF）不用来显示，是用来辨识编码用的，即告诉记事本，这是utf8编码的
utf8和UTF-8的区别：
- UTF-8 是一种编码，世界上UTF-8只有一个
- utf8是这种编码方式的一种表示方式，不同的环境有不同的表示方式（mysql中为utf8）
可能遇到的问题：
- 用记事本编写的PHP代码：
- 在网页打开时可能出现的错误：
- 错误原因是，记事本编写代码 使用的utf8编码方式有BOM头（即utf8+）

39_存储引擎与事务

存储引擎与其特点：

代码：

mysql> set names gbk;
Query OK, 0 rows affected (0.01 sec)
mysql> use worker;
Database changed
mysql> #建立两张一样结构的表，但是
mysql> #引擎不一样
mysql> create table a1
    -> (uname varchar(20) ,
    -> money int)
    -> engine myisam charset utf8;
Query OK, 0 rows affected (0.22 sec)
mysql> create table a2
    -> (uname varchar(20) ,
    -> money int)
    -> engine innodb charset utf8;
Query OK, 0 rows affected (0.38 sec)
mysql> insert into a1 values 
    -> ('张三' , 3000),
    -> ('李四' , 2000);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> insert into a2 values 
    -> ('张三' , 3000),
    -> ('李四' , 2000);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from a1;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  3000 |
| 李四  |  2000 |
+-------+-------+
2 rows in set (0.03 sec)
mysql> select * from a2;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  3000 |
| 李四  |  2000 |
+-------+-------+
2 rows in set (0.02 sec)
mysql> #存储数据上是一样的
mysql> select * from a1 where uname = '张三' for update;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  3000 |
+-------+-------+
1 row in set (0.06 sec)
mysql> #演示事物的原子性
mysql> #如何使用事物呢？
mysql> #start transaction 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a2;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  3000 |
| 李四  |  2000 |
+-------+-------+
2 rows in set (0.00 sec)
mysql> update a2 set money = money + 1000 where uname = '张三' ;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> #减掉李四1000块钱，转账完成
mysql>  update a2 set money = money - 1000 where uname = '李四';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> #2步都完成了，这个事务完成了
mysql> #提交整个事务
mysql> commit;
Query OK, 0 rows affected (0.05 sec)
mysql> #再次开启事务，体现事务的原子特性
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> #先给张三加500
mysql> update a2 set money = money + 500 where uname = '张三' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> #接下来，扣李四500，但失败了
mysql> #我故意把表名打错，模拟网络故障等失败情景
mysql> update a2 set moneys = money - 500 where uname = '李四' ;
ERROR 1054 (42S22): Unknown column 'moneys' in 'field list'
mysql> #扣李四的钱失败了
mysql> #整体的转账操作，从逻辑上讲，应该是失败，即张三的钱，不能多500
mysql> #部分失败，则之前的成功操作怎么处理？答：回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> #一致性
mysql> #是指操作前后，值的变化，逻辑上成立
mysql> #-500 ， +300  ，这样不行
mysql> #比如，tinyint 来存钱，+300溢出了，也是失败了
mysql> #隔离性是指：
mysql> #事务结束前，每一步的操作带来的影响，别的绘画都开不见
mysql> #别的会话都看不见
mysql>
mysql> #黑窗口是柜台，查看事务的隔离性
mysql> #开启事务
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> update a2 set money = money + 500 where uname = '张三' ;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> #现在李四的钱还没减少，电话张三，让他收到的500元全取出来
mysql> #这个事务还没结束，ATM机上，张三根本看不到自己的500块
mysql> update a2 set money = money - 500 where uname = '李四';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> #提交事务，同时事务也就完成并结束了
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a2;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  4500 |
| 李四  |   500 |
+-------+-------+
2 rows in set (0.00 sec)
mysql> #持久性是指：
mysql> #事务一旦完成，无法撤销
mysql> #事务不能撤销，但确实是一次错误的交易
mysql> #只能再做一次“补偿性事务”
mysql> #流水1：多加了500
mysql> #流水2：追回500[补偿性事务]
mysql> #但是不能通过把流水1给删掉这种操作
mysql> #再来看a1 , myisam表
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> update a1 set money = money + 500 where uname = '张三';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from a1;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  3500 |
| 李四  |  2000 |
+-------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a1;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  3500 |
| 李四  |  2000 |
+-------+-------+
2 rows in set (0.00 sec)
mysql> #myisam 表，隔离性就没达到
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update a1 set money = money + 500 where uname = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from a1;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  4000 |
| 李四  |  2000 |
+-------+-------+
2 rows in set (0.02 sec)
mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from a1;
+-------+-------+
| uname | money |
+-------+-------+
| 张三  |  4000 |
| 李四  |  2000 |
+-------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> exit

码农公寓

Day07_MySQLLearning

Day07_MySQLLearning

33_MySQL函数使用注意事项

如果mysql函数和PHP函数都实现某个功能，优先使用哪一个？

34_视图及与表的关系

视图 view

创建视图的语法

视图的作用

表与视图数据变化时的相互影响问题

视图某种情况下，是可以修改的

代码：

35_视图algorithm的概念

了解

algorithm的用法

代码：

36_GB2312与UTF8编码

Unicode与utf-8的关系

UTF-8与Unicode的关系

如何截取utf8（各国语言都有）,无乱码？

乱码原因

37_MySQL字符集参数

连接器的特性：

数据库防止乱码的方法

代码：

38_utf8的BOM问题

小知识：

utf8和UTF-8的区别：

可能遇到的问题：

39_存储引擎与事务

存储引擎与其特点：

代码：

码农公寓

Day07_MySQLLearning

33_MySQL函数使用注意事项

如果mysql函数和PHP函数都实现某个功能，优先使用哪一个？

34_视图及与表的关系

视图 view

创建视图的语法

视图的作用

表与视图数据变化时的相互影响问题

视图某种情况下，是可以修改的

代码：

35_视图algorithm的概念

了解

algorithm的用法

代码：

36_GB2312与UTF8编码

Unicode与utf-8的关系

UTF-8与Unicode的关系

如何截取utf8（各国语言都有）,无乱码？

乱码原因

37_MySQL字符集参数

连接器的特性：

数据库防止乱码的方法

代码：

38_utf8的BOM问题

小知识：

utf8和UTF-8的区别：

可能遇到的问题：

39_存储引擎与事务

存储引擎与其特点：

代码：

相关文章