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