mysql查询新建的一列_mysql表创建查询等基本操作

数据库表的创建

create table

(

[not null],

,

...

)

删除表

drop table

导入导出数据

把表变成sql代码

备份与还原

增，删，改，查 CRUD

添加：

insert into [(列1,列2....)] values(，['值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