Mysql_Note2

1.表中的数据增删改查

1.1添加

插入记录

insert into 表名(字段名1,字段名2) values(值1,值2)

插入查询结果

insert into 表名 (字段名)
select 字段名或值
from 表名 where 条件

1.2 删除

delete form 表名 where 条件

1.3修改

update 表名 set 字段名=值 where 条件

注意不要轻易修改主键的值

1.4查询

SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，行数

1.5思考

1.如果我们把查询的结果插入到表中时,导致主键约束或者唯一性约束被破坏了 如何解决

insert into demo.goodmaster 
select * from demo.goodsmaster1 as a
on duplicate key update barcode = a.barcode,goodsname=a.goodsname

2.使用INSERT...ON DUPLICATE语句可能存在死锁,什么原因导致的,如何解决

第二个事务的区间锁导致第一个事务无法获得插入权限,导致两个事务互相等待，最后锁表。解决的办法是，用update代替这个语句

3.A表有商品唯一id，价格。B表也有关联的商品唯一id，价格为空，实现从A表相同的Id的价格更新到B表

表A：create table demo.a (id int primary key,price decimal(10,2));
表B：create table demo.b (id int primary key,myprice decimal(10,2));
可以尝试用下面的语句更新：
update demo.a as a, demo.b as b
set b.myprice = a.price
where a.id=b.id;

4.商品表 demo.goodsmaster 中,字段“itemnumber”是主键,而且满足自增约束,如果我删除了一条记录,再次插入数据的时候,就会出现字段“itemnumber”的值不连续的情况.请你想一想,如何插入数据,才能防止这种情况的发生呢？

添加商品表中记录的时候，可以判断一下，如果发现itemnumber不连续，可以通过显式指定itemnumber值的办法插入数据，而不是省略itemnumber让它自增。

2.如何正确设置主键

2.1场景

设置主键	含义
业务字段做主键	尽量不要使用和业务相关的字段做主键
自增字段做主键	适用于单机
手动赋值做主键	使用

2.2 思考题

1.重新设置自增字段

alter table demo.membermaster drop primary key
alter table demo.mebermaster add id int primary key auto_increment

2.把销售流水表 demo.trans 中，所有单位是“包”的商品的价格改成原来价格的 80%，该怎么实现呢

update demo.trans as a ,demo.goodsmaster as b set price=price*0.8 where a.itermnumber=b.itermnumber and b.unit='包'

3.外键和连接

3.1 如何创建外键

create table 从表名(
字段名1 类型1,
-- 定义外键约束,指出外键字段和参照的主表字段
constraint  外键约束名
foreign key (字段名1) references 主表名 (字段名)    
)

查看外键约束

SELECT
    constraint_name, -- 表示外键约束名称
    table_name, -- 表示外键约束所属数据表的名称
    column_name, -- 表示外键约束的字段名称
    referenced_table_name, -- 表示外键约束所参照的数据表名称
    referenced_column_name -- 表示外键约束所参照的字段名称
    FROM
    information_schema.KEY_COLUMN_USAGE
    WHERE constraint_name = 'fk_importdetails_importhead';

+-----------------------------+---------------+-------------+-----------------------+------------------------+
| CONSTRAINT_NAME             | TABLE_NAME    | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-----------------------------+---------------+-------------+-----------------------+------------------------+
| fk_importdetails_importhead | importdetails | listnumber  | importhead            | listnumber             |
+-----------------------------+---------------+-------------+-----------------------+------------------------+
1 row in set (0.05 sec)

外键的优缺点

1.建议尽量养成在关联表中定义外键约束的习惯

2.如果你的业务场景因为高并发等原因,无法承担外键约束的成本,也可以不定义外键约束,但是一定要在应用层面实现外键约束的逻辑功能,这样才能确保系统的正确可靠

3.2 连接

内连接

SELECT 字段名FROM 表名 AS aJOIN 表名 AS bON (a.字段名称=b.字段名称);

外连接

SELECT 字段名FROM 表名 AS aLEFT JOIN 表名 AS bON (a.字段名称=b.字段名称); 
SELECT 字段名FROM 表名 AS aRIGHT JOIN 表名 AS bON (a.字段名称=b.字段名称);

3.3 思考

1.如果你的业务场景因高并发等原因,不能使用外键约束,在这种情况下,你怎么在应用层面确保数据的一致性呢

如果不能使用外键约束，你可以在应用层增加确保数据完整性的功能模块，比如删除主表记录时，增加检查从表中是否应用了这条记录的功能，如果应用了，就不允许删除