mysql：操作记录

插入

/*同时插入多条的写法*/insert into student2(student_id, sdudent_name, class_name, area)values(11, 'zhang5', 'ios0208', 'hunan'),(12, 'zhang6', 'php0318', 'beijing'),(13, 'zhang7', 'java0307', 'tianjin');/*在发生唯一索引冲突时，插入自动变成替换*/replace into table_name (字段，字段) values (值，值); /*蠕虫复制*/insert into table_name (字段，字段) select (字段，字段) from table_name2 [where 条件];

更新数据

update table_name set 字段 = 值,set 字段 = 值 where 条件;　　#单表更新

删除数据

delete from table_name where 条件 ;

记录的查找（80%的操作都是select）

select 字段，字段　from table_name where 条件;＃最简单的查询update pet aset birth = (             select date             from event b             where a.name=b.name and b.type='birthday'         )where a.name in (               select name               from event               where type='birthday'            )；#子查询，作为条件

where条件

where sex is not null ；  ＃sex 字段不是nullwhere (birth>'1980' and species='dog') or species='bird' ；＃或与操作where owner like '___n%'；　#模糊查询where owner in ('gwen' , 'benny')；#所有主人名叫gwen 或benny 的宠物where birth between '1990' and '1999';＃查询出生日期在90 年代是宠物，相当与 >= and <=

As 别名

SELECT po.OrderID, p.LastName, p.FirstNameFROM Persons AS p, Product_Orders AS poWHERE p.LastName='Adams' AND p.FirstName='John'；　＃表的别名SELECT LastName AS Family, FirstName AS NameFROM Persons；＃字段的别名select name, year(birth) as year, month(birth) as month from pet  where species in('dog','cat') ;＃函数运算后别名

分组group by

select * from STAFF group by dept desc; ＃分组查询结果：   id  name  dept  salary  edlevel  hiredate      1 张三 开发部 2000 3 2009-10-11      2 李四 开发部 2500 3 2009-10-01      3 王五 设计部 2600 5 2010-10-02      4 王六 设计部 2300 4 2010-10-03      5 马七 设计部 2100 4 2010-10-06      6 赵八 销售部 3000 5 2010-10-05      7 钱九 销售部 3100 7 2010-10-07      8 孙十 销售部 3500 7 2010-10-06SELECT DEPT, MAX(SALARY) AS MAXIMUM　FROM STAFF　GROUP BY DEPT　；　＃分组查询　+  聚合函数结果：      DEPT  MAXIMUM      开发部 2500      设计部 2600      销售部 3500聚合函数：对多条记录操作，永远只有一个返回值select age,sex from users group by sex having age > 20; ＃只取　age 超过20的分组，select 必需要 age

Limit

limit  3；//取出三条limit  3，4；从查询到的数据的第三条开始取，取4条；

order by

order by owner, name desc//按主人姓名排序，相同的按宠物姓名倒序排列

子查询

/*由比较运算符引发的子查询*/select * from goods where price >= (select round(avg(price),2) from goods);＃查询价格在平均价格之上的产品/* ANY SOME ALL　关键字：配合子查询返回的不是一个值使用*/select * from goods where price >= ANY (select price from goods where type = "超级本");#ANY 和SOME 是一致的，只要大于等于子查询返回一个值就好，ALL是大于子查询返回的所有值/*由　in ,not in ,exists 引发的子查询*/select * from goods where price　in (select price from goods_detail where type = "超级本"); ＃返回　price 在子查询的返回的值里面的记录