首页 > 代码库 > 2014.9.3数据库CRUD

2014.9.3数据库CRUD

CRUD 增删改查

 

DCL 数据控制语言:备份,grant

DML 数据操作语言: CRUD

DDL 数据定义语言:create drop alter 

 

自增长列不能赋值

增:

Insert into 表名 values‘’,’’,’’,’’,’’)--into可以省略

删:

Delete from 表名(有日志)(慢)   truncate table 表名(不写日志,下次执行自增长列从1开始)(快)

Delete from 表 where 列名 关系运算符 值

改:

Update 表 from set 列名=值,列名=值,......where 列名 关系运算符 值

查:

Select * from 

Select 列名,列名,......  From 

Select * from 表 where 列名 关系运算符 值 and 列名 关系运算符 值

Select * from 表 where 列 between 10 and 20 (范围查询)

Select * from 列 where 列 in 3,4,5

Select  distinct 列 from 表  (列去重)

Select * from car where name like  %5%     %任意多个任意字符;_一个任意字符

对列的筛选——投影

对行的筛选——筛选

行——记录(元组) 列——字段(属性)

 

 1 select * from Fruit 2 select * from Fruit where Stack in (3,4,5) 3 Select * from car where name like  %5%--通配符   %  _ 4 select * from car where name like %型 5 select * from car where name like 宝% 6 select * from car where name like %型 7 select * from car where name like __[3,5]%--中括号表示选其一 8 --排序 9 select * from car order by price     --升序10 select * from car order by price desc  --降序11 select * from car order by price,oil asc,exhaust desc12 select * from car where price>30 order by price13 --统计   5个14 select COUNT(*) from car where name like 奥迪%--有多少个奥迪开头的记录15 select AVG(price) from car--统计car所有记录price的平均值16 select SUM(price/oil) from car17 select * ,(price*0.9) jiu折 from car18 select code 代号,name 名称 from car19 select MAX(price) from car20 select * from car where price in (select MIN(price) from car)--找出最小值所在的记录(行)21 22 --分组23 select oil,COUNT(*) from car group by oil order by COUNT(*)24 select oil,COUNT(*) from car where price>50 group by oil order by COUNT(*)25 select brand,MAX(price) from car group by brand26 select brand,Min(price) from car group by brand27 28 select oil,COUNT(*) from car group by oil having COUNT(*)>=2   --having分组后筛选29 --根据身份证号分组统计;筛选个数大于130 select stack from Fruit group by Stack having COUNT(*)>1

 

 

 

2014.9.3数据库CRUD