mysql查詢表基本操作

標籤：

資料庫表的建立
create table <表名>
(
<列名> <資料類型及長度> [not null],
<列名> <資料類型及長度>,
...
<列名> <資料類型及長度>
)
刪除表
drop table <表名>

匯入匯出資料
把表變成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

保護資料：
獲得資料庫添加許可權
grant insert
on constomers
to Mary

擷取資料庫檢索許可權
grant update,select
on constomers
to Mary
解除許可權
revoke inse rt
on constomers
from mary

mysql查詢表基本操作