hq金水

愿你是阳光,明媚不忧伤~
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

MySQL增删改查(CRUD)

Posted on 2016-05-25 21:18  hq金水  阅读(310)  评论(0编辑  收藏  举报

导入导出数据
把表变成sql代码
备份与还原


增,删,改,查 CRUD

#添加:
insert into <表名>[(列1,列2....)] values(<'值1'>[,'值2'])
注意:
1.列与值要匹配(数量,类型,次序)
2.列可以省掉,但值必须与表中的总列数和列的次序完全对应。
3.自增长列,不能省掉自增列,给自增列赋个''

#删

delete from car 不同于 drop table xx
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 car set brand='b008' where code='c001' or code='c002';
update car set name='奔驰', brand='b009', time='2016-2-2' where code='c002'
update car set price=price*0.9
update car set price=price*0.95 where price>30
update car set price=price*0.1 where (brand='b002' or brand='b003')&&price>40

 

#查询

一、简单查询
select * from 表名
select 列名1,列名2... from 表名 --投影
select * from 表名 where 条件 --筛选

select Code as '代号',Name as '姓名' from Info

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%'   #查找包含5的
select * from car where name like '%型'  #查找以型结尾的
select * from car where name like '__5%' _ -- 一个任意字符     #查找第三个字符是5的行

select * from car where name like'__5'   #查找第三个字符是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

select * from car where brand in('b002','b004','b006')  order by brand asc,price desc   #先找出来再排序

4.范围查询

select * from Car where Price>=40 and Price<=60
select * from Car where Price between 40 and 50

5.聚合函数,统计查询
select sum(Price) from Car #查询所有价格之和 sum()求和
select count(Code) from Car #查询数据条数
select max(Code) from Car #求最大值
select min(Brand) from Car #求最小值
select avg(Price) from Car #求平均值

6.分页查询
#每页显示5条数据,取第2页的数据
select * from Car limit (n-1)*5,5

7.去重查询
select distinct Brand from Car

8.分组查询
select count(*),Brand from Car group by Brand
select Brand as '系列号' from Car group by Brand having count(*)>3 #分组之后根据条件查询使用having 不使用where