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.如果你的业务场景因高并发等原因,不能使用外键约束,在这种情况下,你怎么在应用层面确保数据的一致性呢
如果不能使用外键约束，你可以在应用层增加确保数据完整性的功能模块，比如删除主表记录时，增加检查从表中是否应用了这条记录的功能，如果应用了，就不允许删除
|