一、創(chuàng)建表：
表名：employee

create table employee  
(  
    id int,  
    name varchar(40),  
    sex varchar(4),  
    birthday date,  
    entry_date date,  
    salary decimal(8,2),  
    resume text  
);

二、增刪改查
（1）插入數(shù)據(jù)

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）指定某列插入數(shù)據(jù)

insert into employee(id) values(6);  

（3）查看漢字時(shí)不亂碼

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','張三');  
  
     /*查看漢字時(shí)不亂碼*/  
  
       show variables like 'chara%';  
    set character_set_results=gb2312;  
    select * from employee;  

（4）刪除表數(shù)據(jù)

/*刪除表中名稱為’zs’的記錄*/  
delete from employee where name='zs';  
  
/*刪除表中所有記錄*/  
delete from employee;  
  
/*使用truncate刪除表中記錄(快速刪除表中所有數(shù)據(jù)，保留表的數(shù)據(jù)結(jié)構(gòu))*/  
truncate table employee;   

（5）修改表中數(shù)據(jù)

/*將所有員工薪水修改為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的薪水在原有基礎(chǔ)上增加1000元*/  
update employee set salary = salary+1000 where name='lisi';  

（6）查詢表中數(shù)據(jù)

/*查詢表中所有學(xué)生的信息*/  
select id,name,chinese,english,math from student;  
  
/*查詢表中所有學(xué)生的姓名和對應(yīng)的英語成績*/  
select name,english from student;  
  
/*過濾表中重復(fù)數(shù)據(jù)*/  
select distinct english from student;  
  
在所有學(xué)生分?jǐn)?shù)上加10分特長分*/  
select name,(chinese+english+math)+10 from student;  
  
/*統(tǒng)計(jì)每個(gè)學(xué)生的總分*/  
select name,(chinese+english+math) from student;  
  
/*使用別名表示學(xué)生分?jǐn)?shù)*/  
select name,(chinese+english+math) as 總分 from student;  
  
/*使用別名表示學(xué)生分?jǐn)?shù),可以不用as*/  
select name,(chinese+english+math) 總分 from  student;  
  
/*查詢姓名為張三的學(xué)生成績*/  
select * from student where name='張三';  
  
/*查詢英語成績大于90分的同學(xué)*/  
select * from student where english>'90';  
  
/*查詢總分大于200分的所有同學(xué)*/  
select name,(chinese+english+math) 總分 from student where chinese+english+math>200;  
  
/*查詢英語分?jǐn)?shù)在 80－90之間的同學(xué)*/  
select * from student where english>=80 and english=<90;  
select * from student where english between 80 and 90;  
  
/*查詢數(shù)學(xué)分?jǐn)?shù)為89,90,91的同學(xué)*/  
select * from student where math=89 or math=90 or math=91;  
select * from student where math in(89,90,91);  
  
/*查詢所有姓李的學(xué)生成績*/  
select * from student where name like '李%';  
select * from student where name like '李_';  
  
/*查詢數(shù)學(xué)分>80，語文分>80的同學(xué)*/  
select * from student where math>80 and chinese>80;  
select * from student where chinese is null;  
  
/*對數(shù)學(xué)成績排序（降序：從高到低，desc）后輸出。*/  
select name,math from student order by math desc;  
  
/*對數(shù)學(xué)成績排序（升序：從低到高，asc）后輸出。*/  
select name,math from student order by math asc;  
  
/*對總分排序后輸出，然后再按從高到低的順序輸出*/  
select name,math+english+chinese from student order  by math+english+chinese desc;  
  
/*對姓李的學(xué)生成績排序輸出*/  
select name,math+english+chinese from student where name like '李%' order by math+english+chinese desc;  
  
/*統(tǒng)計(jì)一個(gè)班級共有多少學(xué)生*/  
select count(*) from student;  
select count(id) from student;  
  
/*統(tǒng)計(jì)數(shù)學(xué)成績大于80的學(xué)生人數(shù)*/  
select count(*) from student where math>80;  
  
/*統(tǒng)計(jì)總分大于250的人數(shù)*/  
select count(*) from student where math+english+chinese>250;  
  
/*細(xì)節(jié) null不能被count*/  
select count(chinese) from student;  
  
/*統(tǒng)計(jì)一個(gè)班級數(shù)學(xué)總成績*/  
select sum(math) from student;  
  
/*統(tǒng)計(jì)一個(gè)班級語文、英語、數(shù)學(xué)各科的總成績s*/  
elect sum(math),sum(english),sum(chinese) from student;  
  
/*統(tǒng)計(jì)一個(gè)班級語文、英語、數(shù)學(xué)的成績總和*/  
select sum(math+english+chinese) from student;  
  
/*統(tǒng)計(jì)一個(gè)班級語文成績平均分*/  
select sum(chinese)/count(*) from student;  
  
/*求一個(gè)班級數(shù)學(xué)平均分*/  
select avg(math) from student;  
  
/*求一個(gè)班級總分平均分*/  
select avg(math+english+chinese) from student;  
  
/*求班級最高分和最低分（數(shù)值范圍在統(tǒng)計(jì)中特別有用）*/  
select max(math+english+chinese) from student;  
select min(math+english+chinese) from student;  
  
/*對訂單表中商品歸類后，顯示每一類商品的總價(jià)*/  
select product,sum(price) from orders group by product;  
  
/*查詢購買了幾類商品，并且每類總價(jià)大于100的商品*/  
select product,sum(price) from orders group by product having sum(price)>100;