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查询表基本操作

标签：