MySQL增刪改查（CRUD）

標籤：

匯入匯出資料
把表變成sql代碼
備份與還原

增，刪，改，查 CRUD

添加：
insert into <表名>[(列1,列2....)] values(<‘值1‘>[,‘值2‘])
注意：
1.列與值要匹配（數量，類型，次序）
2.列可以省掉，但值必須與表中的總列數和列的次序完全對應。
3.自增長列，不能省掉自增列，給自增列賦個‘‘

 

delete from car where code=‘c001‘
delete from car where brand=‘b001‘ or brand=‘b004‘
delete from car where brand=‘b001‘ || brand=‘b004‘
delete from car where brand=‘b007‘ && price>50
delete from car where brand=‘b007‘ and price>50

<> !=

更新
update <表名> set <列=值>[,列=值...] where .....
update info set sex=‘1‘ where code=‘p003‘
update info set sex=‘0‘,nation=‘n004‘,birthday=‘1999-9-9‘ where code=‘p001‘
update car set price=price * 0.9 where price > 30
update car set price =price * 0.95 where (brand=‘b006‘ || brand=‘b005‘)&&price>30

 

查詢
select * from 表名
select 列名1,列名2... from 表名--投影
select * from 表名 where 條件--篩選

1.等值與不等值
select * from car where code=‘c001‘;
select * from car where code != ‘c001‘;
select * from car where price > 30;
--下面的都是範圍
select * from car where price >=30 && price <=50;
select * from car where price between 30 and 50
select * from car where brand=‘b002‘ || brand=‘b004‘ || brand=‘b006‘
select * from car where brand in (‘b002‘,‘b004‘,‘b006‘)

2.模糊查
select * from car where name like ‘寶馬%‘%--任意多個任一字元
select * from car where name like ‘%5%‘
select * from car where name like ‘%型‘
select * from car where name like ‘__5%‘_ -- 一個任一字元

3.排序
select * from 表名 where .... order by 列名 [ASC/DESC],列名[asc/desc]....

select * from car order by price desc
select * from car order by brand desc,price asc

MySQL增刪改查（CRUD）