MySQL

Linux下配置MySQL：

 

Windows下MySQL老版本对Unicode支持不是很好，可能不能在命令行输入、显示中文，MySQL8.0.15没毛病。

 

SQL

库名，表名，字段名在Win下不区分大小写，在Linux下区分大小写。

设置在Win下也区分大小写：

 

SQLyog

Ctrl S保存为.sql文件，Ctrl O打开.sql文件，选中一条语句，点击“执行查询”，左边面板按F5刷新数据库状态。

SHOW TABLE STATUS FROM 库名 LIKE 表名

添加check约束：

ALTER  TABLE  TB_STU  ADD  CHECK  (STU_AGE  >  0  AND  STU_AGE  <  100)；

重置表：（删除加重建，id归零）

TRUNCATE  TABLE  TB_STU；

（列名，）values（值，）可以省略自增、可空字段，但是values（值，）要写上全部字段。

如果值为null，就替换为0。注意：只是查询结果呈现出来是0，表中存的还是null。

把一个表的查询结果插入到另一个表：

INSERT  INTO  TB1  ( , , , ,)  SELECT  ( , , , ,)  FROM  TB2；

%表示任意个任意字符，_表示一个任意字符。

如求所有商品库存之和。

按照品牌分组就要先查出品牌，然后要查出总金额，按品牌分组。得到的总金额就是每个分组的总金额。

分组查询的应用

分组查询主要和聚合查询一起用，如果用SUM求学校的总人数，结果就只有一条，是全学校的总人数，如果再用GROUP BY学院，结果就是每个学院的总人数。

分页查询

“当前页数”其实是当前页第一行是总行的第几行，从0开始算。

 

查询两Decimal字段相减，报错：

DECIMAL UNSIGNED value is out of range in '(`ssms`.`tb_goods`.`market_price` - `ssms`.`tb_goods`.`shop_price`)'

解决：转成有符号：

SELECT row_id, goods_name, CAST(market_price AS SIGNED) - CAST(shop_price AS SIGNED) AS '节省' FROM tb_goods;

 

注意：删除一个字段的主键自增约束后，该字段主键的主键约束不会被删除，删除主键约束要用drop primary key。

注意：主键自增使用过的值，即使删除了，也不会重新用。id有1，2，3，把id为3的删除了，下一次添加自增为4。

    不能重复。

这两张表的数据是有关联的，因此应该遵循某种规则，比如：

orderlist表增加一行数据时要判断其uid是不是user表中存在的id；

user表中删除一行数据时要考虑是否把orderlist表中对应uid的数据也删除。

一个用户多个订单，一对多，就让多的订单创建外键，引用用户的主键。

ou_fk1是外键约束名，ou是两个表的首字母，fk是foreign key

删除之后uid字段依然存在，但是没有那层约束了。 

就是某用户的id改为10，对应订单表中关联的uid也会改为10。

id为10的用户被删除，对应订单表中uid为10的订单也会被删除。

 

check约束：限定字段值：

stu_age int check age > 5 and age < 80

在Oracle中有用，MySQL中没用。

 

多表操作

多表操作可以在SQLyog中点击工具栏最右边一个，新架构设计器，从左边把表拖进去查看各表之间的关系（1----∞，1----1）。

多表查询

WHERE和INNER JOIN的区别：

换行书写优雅的SQL语句：

隐式内连接：

用户1用户2有订单，用户3没有订单；订单1订单2有用户，订单3没有用户；那么：

用户表左连接订单表，查到用户1、2、3以及各自对应的订单1、2，用户3虽然没有订单，但是也会被查出来。

用户表右连接订单表，查到用户1、2以及各自对应的订单1、2、3，订单3虽然没有用户，但是也会被查出来。

注意：table1 left join table2就相当于table2 right join table1，表名交换位置就可以切换左右连接。

原表：

结果是错的：

 

结果是对的：

这里用的是内连接，没有所属用户的订单不查询。如果也要查询没有用户的订单的话，就用外连接。

（SELECT * FROM orderlist WHERE id > 4）括号里是查询的结果，多行多列，看作一张表，o是别名。

 

EXISTS型子查询

用于：查询所有班级前提是该班级下有学生。

 

    mgr意为上级

 

联合查询UNION

有两个表，stu1和stu2，想从两个表中查询有没有叫‘乔峰’的学生：

SELECT * FROM STU1 WHERE NAME = '乔峰'

UNION

SELECT * FROM STU2 WHERE NAME = '乔峰'；

应用场景：

项目运行了10年，库太大了查询慢了，怎么解决：移库移表，把老数据移到另一个服务器上，如果碰到老用户要查询老数据，就用联合查询，同时查新老库。

 

SQL语句书写思路：

先把from、join、on的内容一个一个写好，最后写select要查什么字段。

    

多表查询练习

理解题意：查出每个商品分类下的商品，如果某个商品没有所属分类也要查出来。

    

 

视图

视图跟子查询比较类似，但是子查询产生的结果只能在当前SQL语句中使用，而视图把查询结果封装成一个表，以后随时都可以使用。

 

数据库备份和恢复

备份不需要进入MySQL，回复需要进入MySQL。这里说的MySQL服务器指的是部署了MySQL的Linux服务器。

 

 

存储过程和存储函数

PL/SQL：Procedural Language/SQL过程化SQL语言，用SQL编写过程化代码。

DELIMITER $ 是修改结束符为$，因为存储过程里面有多条SQL，每条都有分号；，我们不想让他在；结束，而是在END处结束，因此修改结束符。

定义完存储过程后，要把结束符再改回来。

DELIMITER $

BEGIN

END

DELIMITER ；

存储函数和存储过程区别：函数FUNCTION必须有返回值，用SELECT调用；过程PROCEDURE可以没有返回值，用CALL调用。

    

 

 

CONCAT用于拼接字符串。

UPDATE型触发器也类似：

 

事务

事务操作

因为没出错，所以可以提交了。

    

假如出现了错误：

就要回滚：

    

事务在提交或回滚之前所做的操作都是临时操作，只有提交或回滚才会真正持久化。

事务的提交方式

改成手动提交后：

面试题：

脏读：

隔离级别：read-uncommitted。

事务1：开启事务，转账：

    

事务2：开启事务，查询：

    

事务1：回滚：

    

事务2：再查询，发现跟上次查询不一样：

    

隔离级别改成read-committed，则事务2第一次查询就是1000，1000。直到事务1提交事务之后，事务2才会查询到500，1500。

不可重复读：

隔离级别：read-committed。

事务1修改，没提交；

事务2读第一次，是原数据；

事务1提交；

事务2读第二次，是新数据；

这样的结果就是不会读到不确定的数据（脏读），但是对于事务2来说，重复读两次，结果不一样。

这样的应用场景是：老板让员工查一下公司盈利情况，员工查到50万，过一会财务更新了，老板自己又查，查到80万，这样老板对员工就会产生信任问题。

这里的需求是：应该让这段时间内我只要查就是50万，就算那边更新了，也不要影响我这边的查看。

解决：隔离级别改成repeatable-read。

repeatable-read级别下怎样看到别的事务提交的数据：自己也提交，结束事务，不处在事务当中就能看到那边提交的，我这边还在事务中就看不到那边提交的。

幻读：

隔离级别：repeatable-read。

事务1：开启事务，添加数据：

    

事务2：开启事务，查询表，没有事务1添加的数据：

    

事务2：添加数据：

事务2：添加后没有结果，处于卡死状态；

事务1：提交事务：

事务2：报错：

事务2：

事务2：查询数据，发现已经存在：

此时对事务2来说，我查询，没有，于是我添加，结果添加不了，我提交事务后再查询，又有了。就像出现了幻觉。

解决：隔离级别改成serializable。

SERIALIZABLE级别下的事务操作：

事务1：添加，未提交；

事务2：查询，无结果，卡死；

事务1：提交；

事务2：得到查询结果，是添加后的。

即：serializable级别下，一个事务没有提交，另一个事务无法进行任何操作，包括查询。

 

 

会列出表的所有配置，包括存储引擎。

添加索引

 

索引不是越多越好，索引是独立于表单独存储的，索引越多消耗的空间也越多，而且维护那么多的数据结构也会增加开销（增删数据要在每个索引里增删元素）。

 

锁

数据库的锁是为了保证数据的一致性，采取的一种规则，使得共享的资源被并发访问时具有安全性。

跟多线程的同步类似，作用是保证数据的安全性和一致性。

事务1：开启事务，查询id为1的行并加共享锁：

事务2：开启事务，查询，没问题：

事务2：查询id为1的行并加共享锁，没问题：

事务2：修改，完不成，一直在转圈：

事务1：提交事务：

事务2：修改成功：

事务2：修改另一行，没问题：

原因：按索引列查询时加锁，加的是行锁，不会锁住其他行。

事务1：查询分数为99的行，并加共享锁：

事务2：修改另一行，完不成，一直在转圈：

原因：按非索引列查询时加锁，加的是表锁，整个表都会锁住。

事务1：开启事务，查询id为1的行，并加排他锁：

事务2：普通查询，没问题：

事务2：枷锁查询，完不成，一直在转圈：

原因：排他锁与任意锁不兼容。

事务1：提交事务；事务2：查询成功：

事务1：开启事务；事务2：修改数据，完不成，一直在转圈；事务1：提交事务；事务2：修改成功。

自己当前连接也不能修改。

只有自己能查询和修改。