SQL数据库 CRUD
2016-08-01 07:26 天疯狂石 阅读(233) 评论(0) 编辑 收藏 举报1.删除表
drop table +表名
2.修改表
alter table+表名+ add(添加)+列名+ int(类型)
alter table+表名+ drop(删除)+column(列)+列名
3.删除数据库
Drop datebase +库名
CRUD操作
C代表create 添加数据
R代表 read 读取数据
U代表update 修改数据
D代表delete 删除数据
1.添加数据
Insert(插入/添加) into+ 表名+ values(添加的值) ,‘ ’转换字符串
Insert into Nation values(‘n002’,‘回族’)
Insert into Nation values(‘n003’,‘’)
多列添加:Insert into Nation(code,name) values(‘n003’,‘维吾尔族’)
Insert into friends values(‘p003’,‘p007’)
2.删除数据
delete from Nation 删除所有
delete from friends where ids=5
3.修改数据
update Friends set fcode=‘’p016’ 修改所有
update Friends set fcode=‘p006’,mcode=‘p002’where ids=4
查询
1.简单查询
select * from info(表名) --查所有数据
select code,name from info --查指定列的数据
select code as ‘代号’,name as ‘姓名’ from info (改变列名) --给列指定别名
2.条件查询
select * from info where code=‘p001’
select * from info where sex=‘true’,and nation=‘n001’ --多条件并的关系
select * from info where sex=‘true’,or nation=‘n001’ --多条件或的关系
3.范围查询
select * from car where price>40 and price<50
select * from car where price between 40 and 50
4.离散查询
select * from car where code in(‘c001’,‘c005’,‘c010’,‘c015’)
select * from car where code not in(‘c001’,‘c005’,‘c010’,‘c015’)
5.模糊查询
select * from car where name like ‘%宝马%’ --查包含宝马的
select * from car where name like ‘宝马%’ --查以宝马开头的
select * from car where name like ‘%宝马 --查以宝马结尾的’
select * from car where name like ‘宝马’ --查等与宝马的
select * from car where name like‘__E%’ --查第三个字符是E的
%代表是任意多个字符
_代表是一个字符
6.排序查询
select * from car order by price asc --以价格升序排列
select * from car order by price desc –以价格降序排列
select * from car order by 0il desc, price asc --以两个字段排序,前面的是主条件后面的是次要条件
7.分页查询
select top 5 * from car
select top 5 * from car where code not in (select top 5 code from car)
当前页:page = 2 row= 10
select top row * from car where code not in (select top (page-1) * row code from car)
8.去重查询
select distinct brand from car
9.分组查询
select Brand from car group by Brand having count(*)>2
10.聚合函数(统计查询)
select count(*)查询完以后到底有多少数据 from car –查询所有数据条数
select count(code) from car --查询所有数据条数
select sum(price) from car(表名) 求和
select sum(price) from car 求和
select sum(price) from car 求和
select sum(price) from car 求和