sql增删查改总结

INSERT INTO  patient (id,name,age,gender,phone)VALUES(250,'json',18,1,'15623720881')                插入数据

DELETE FROM patient WHERE id=250                                                                    删除指定数据
DELETE FROM patient                                                                                 删除所有数据（不包括表结构和属性）
DROP FROM patient                                                                                   删除所有数据（包括表结构和属性）

SELECT count(age) from patient;                                                                     统计
SELECT *FROM patient ORDER BY age ASC;                                                              升序
SELECT *FROM patient ORDER BY age DESC;                                                             降序
SELECT max(age) FROM patient;                                                                       最大值
SELECT min(age) FROM patient;                                                                       最小值
SELECT avg(age) FROM patient;                                                                       平均数
SELECT sum(age) FROM patient;                                                                       求和
SELECT DISTINCT(age) FROM patient;                                                                  去重
SELECT age FROM patient WHERE age BETWEEN 20 AND 30;                                                在···之间
SELECT * FROM patient  LEFT JOIN topic_exam ON topic_exam.user_id= patient.id;                      左关联，左表所有数据和右表匹配数据
SELECT * FROM patient  RIGHT JOIN topic_exam ON topic_exam.user_id= patient.id;                     右关联，右表所有数据和左表匹配数据
SELECT * FROM patient INNER JOIN topic_exam ON topic_exam.user_id= patient.id;                      内关联，两个表相匹配的数据
SELECT *FROM patient ORDER BY age ASC LIMIT 5                                                       升序取前五的数据


SELECT gender,sum(gender) AS 姓名统计 from patient  GROUP BY gender ORDER BY sum(gender) DESC;      
SELECT age,sum(age) AS nums FROM patient GROUP BY age                                               分组

UPDATE patient set name='邹彭云' where id=88                                                       改数据


begin;                                                                                              开始事务
START transaction ;                                                                                 开始事务（开起一次事务必须要结束才能开启下一次，也就是begin->commit或begin->rollback之后再进行第二次事务，begin->begin是会报错的）

UPDATE patient set name='彭云' where id=88        执行语句
SELECT *FROM patient WHERE id=88

rollback;                                                                                           回滚  
COMMIT;                                                                                             提交