MySQL常见的查询语句的运用

数据库基本查询语句：
insert into 表名 values (数据1，数据2，数据3.。。。。。。),(数据1，数据2，数据3.。。。。。。)
INSERT into student (stuid,name,sex,phone,birthday,cid) VALUES (1,"王一",'男',13888888888,'20010301',1)
修改
update 表名 set 字段= 新值 where 条件
UPDATE student SET cid=2 WHERE `name`="王一"
删除
delete from 表名//表里所有的数据删除
delete from 表名 where 条件
DELETE from student WHERE NAME='王八'
查询
select 需要的结果 from 表名 
SELECT name FROM student
select 需要的结果 from 表名 where 条件
SELECT cid from student WHERE name='王一'
SELECT name,score from student
SELECT name,score+10 from student
SELECT name as "姓名",score+10 '成绩'from student
SELECT score  from student WHERE sex<>'女'
SELECT name from student WHERE score>60
SELECT * from student WHERE score BETWEEN 60 AND 80
SELECT * from student WHERE score in (10,60,80)
SELECT * from student WHERE name LIKE "王%"//% 匹配一个及多个字符
SELECT * from student WHERE name LIKE "王_"//  _  匹配一个字符
SELECT * from student WHERE score>70 and cid=1     //and 与    or  或    NOT 非
SELECT avg(score) FROM student WHERE cid=1
SELECT sum(score) FROM student WHERE cid=1
SELECT max(score) FROM student WHERE cid=1
SELECT min(score) FROM student WHERE cid=1
SELECT COUNT(name) from student WHERE score>60
SELECT cid,avg(score) from student GROUP BY cid 
SELECT sex,cid,avg(score) from student GROUP BY cid,sex
SELECT stuid ,score+5 as sa from student HAVING sa>60
SELECT * from student ORDER BY score ASC//从小到大
SELECT * from student ORDER BY score DESC//从大到小
SELECT cid,avg(score) from student  GROUP BY cid HAVING avg(score)>60 ORDER BY avg(score) DESC
SELECT * from student ORDER BY score DESC LIMIT 1,3
SELECT * from student,classinfo WHERE student.cid=classinfo.cid
SELECT name,cname FROM student,classinfo WHERE student.cid=classinfo.cid
SELECT student.name '姓名',classinfo.cname '班级名' FROM student,classinfo WHERE student.cid=classinfo.cid
SELECT student.stuid,student.name,student.cid,classinfo.cname from student,classinfo WHERE student.cid=classinfo.cid AND student.name='王一'