2014.9.3数据库CRUD

浏览数：30 / 时间：2015年06月12日

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 desc
12 select * from car where price>30 order by price
13 --统计   5个
14 select COUNT(*) from car where name like ‘奥迪%‘--有多少个奥迪开头的记录
15 select AVG(price) from car--统计car所有记录price的平均值
16 select SUM(price/oil) from car
17 select * ,(price*0.9) jiu折 from car
18 select code 代号,name 名称 from car
19 select MAX(price) from car
20 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 brand
26 select brand,Min(price) from car group by brand
27 
28 select oil,COUNT(*) from car group by oil having COUNT(*)>=2   --having分组后筛选
29 --根据身份证号分组统计；筛选个数大于1
30 select stack from Fruit group by Stack having COUNT(*)>1