一、创建表：
表名：employee
create table employee
(
id int,
name varchar(40),
sex varchar(4),
birthday date,
entry_date date,
salary decimal(8,2),
resume text
);
二、增删改查
（1）插入数据
insert into employee(id,name,sex,birthday,entry_date,salary,resume) values(1,'zhangsan','male','1993-03-04','2016-11-10','1000','i am a developer');
/*可以省略表字段，但是必须插入全部字段*/
insert into employee values(1,'zhangsan','male','1993-03-04','2016-11-10','1000','i am a developer');
（2）指定某列插入数据
insert into employee(id) values(6);
（3）查看汉字时不乱码
insert into employee(id,name) values(6,'张三');
/*告诉mysql客户采用gb2312编码*/
show variables like 'chara%';
set character_set_client=gb2312;
insert into employee(id,username) values('3','张三');
/*查看汉字时不乱码*/
show variables like 'chara%';
set character_set_results=gb2312;
select * from employee;
（4）删除表数据
/*删除表中名称为’zs’的记录*/
delete from employee where name='zs';
/*删除表中所有记录*/
delete from employee;
/*使用truncate删除表中记录(快速删除表中所有数据，保留表的数据结构)*/
truncate table employee;
（5）修改表中数据
/*将所有员工薪水修改为5000元*/
update employee set salary=5000;
/*将姓名为’zs’的员工薪水修改为3000元*/
update employee set salary = 3000 where name='zs';
/*将姓名为’aaa’的员工薪水修改为4000元,job改为ccc*/
update employee set salary = 4000,job='ccc' where name='aaa';
/*将lisi的薪水在原有基础上增加1000元*/
update employee set salary = salary+1000 where name='lisi';
（6）查询表中数据
/*查询表中所有学生的信息*/
select id,name,chinese,english,math from student;
/*查询表中所有学生的姓名和对应的英语成绩*/
select name,english from student;
/*过滤表中重复数据*/
select distinct english from student;
在所有学生分数上加10分特长分*/
select name,(chinese+english+math)+10 from student;
/*统计每个学生的总分*/
select name,(chinese+english+math) from student;
/*使用别名表示学生分数*/
select name,(chinese+english+math) as 总分 from student;
/*使用别名表示学生分数,可以不用as*/
select name,(chinese+english+math) 总分 from student;
/*查询姓名为张三的学生成绩*/
select * from student where name='张三';
/*查询英语成绩大于90分的同学*/
select * from student where english>'90';
/*查询总分大于200分的所有同学*/
select name,(chinese+english+math) 总分 from student where chinese+english+math>200;
/*查询英语分数在 80－90之间的同学*/
select * from student where english>=80 and english=<90;
select * from student where english between 80 and 90;
/*查询数学分数为89,90,91的同学*/
select * from student where math=89 or math=90 or math=91;
select * from student where math in(89,90,91);
/*查询所有姓李的学生成绩*/
select * from student where name like '李%';
select * from student where name like '李_';
/*查询数学分>80，语文分>80的同学*/
select * from student where math>80 and chinese>80;
select * from student where chinese is null;
/*对数学成绩排序（降序：从高到低，desc）后输出。*/
select name,math from student order by math desc;
/*对数学成绩排序（升序：从低到高，asc）后输出。*/
select name,math from student order by math asc;
/*对总分排序后输出，然后再按从高到低的顺序输出*/
select name,math+english+chinese from student order by math+english+chinese desc;
/*对姓李的学生成绩排序输出*/
select name,math+english+chinese from student where name like '李%' order by math+english+chinese desc;
/*统计一个班级共有多少学生*/
select count(*) from student;
select count(id) from student;
/*统计数学成绩大于80的学生人数*/
select count(*) from student where math>80;
/*统计总分大于250的人数*/
select count(*) from student where math+english+chinese>250;
/*细节 null不能被count*/
select count(chinese) from student;
/*统计一个班级数学总成绩*/
select sum(math) from student;
/*统计一个班级语文、英语、数学各科的总成绩s*/
elect sum(math),sum(english),sum(chinese) from student;
/*统计一个班级语文、英语、数学的成绩总和*/
select sum(math+english+chinese) from student;
/*统计一个班级语文成绩平均分*/
select sum(chinese)/count(*) from student;
/*求一个班级数学平均分*/
select avg(math) from student;
/*求一个班级总分平均分*/
select avg(math+english+chinese) from student;
/*求班级最高分和最低分（数值范围在统计中特别有用）*/
select max(math+english+chinese) from student;
select min(math+english+chinese) from student;
/*对订单表中商品归类后，显示每一类商品的总价*/
select product,sum(price) from orders group by product;
/*查询购买了几类商品，并且每类总价大于100的商品*/
select product,sum(price) from orders group by product having sum(price)>100;
