Mysql练习题

create database mydb1 charset utf8;

use mydb1;

create table emp(
 id int primary key auto_increment,   
 name varchar(20)not null, 
 gender char(1) not null, 
 birthday date not null, 
 job varchar(20) not null, 
 salary double not null, 
 bouns double not null 
)charset=utf8;

INSERT INTO emp VALUES (DEFAULT,'张飞','男','1981-1-1','保安',3500,600);
INSERT INTO emp VALUES (DEFAULT,'赵云','男','1983-2-6','战士',8500,900);
INSERT INTO emp VALUES (DEFAULT,'孙尚香','女','1986-4-1','射手',1500,7700);
INSERT INTO emp VALUES (DEFAULT,'小乔','女','1999-3-6','法师',2500,900);

UPDATe emp SET salary = salary + 9000;

SELECT * FROM emp;

create database mydb2 charset utf8;

use mydb2;

create table emp(
id int primary key auto_increment, 
name varchar(20) not null, 
gender char(1) not null, 
birthday date not null, 
dept varchar(20) not null,
job varchar(20) not null,
salary double not null,
bonus double not null
)charset=utf8;

insert into emp values(default ,'王海涛','男','1995-03-24','培优部','讲师',3400,100);
insert into emp values(default,'齐雷','男','1994-04-06','培优部','讲师',2500,100);
insert into emp values(default,'刘沛霞','女','1996-06-14','培优部','讲师',1400,800);
insert into emp values(default,'陈子枢','男','1991-05-18','培优部','总监',4500,700);
insert into emp values(default,'刘昱江','男','1993-11-18','培优部','讲师',2600,200);
insert into emp values(default,'王非','女','1998-07-18','就业部','讲师',3700,200);
insert into emp values(default,'范齐','男','1999-09-18','就业部','讲师',3200,900);
insert into emp values(default,'苍老师','男','1995-09-18','就业部','总监',4850,300);
insert into emp values(default,'刘涛','男','1990-10-18','就业部','讲师',2700,700);
insert into emp values(default,'韩少云','男','1980-12-18','NULL','CEO',5000,700);
insert into emp values(default,'懂长春','男','1988-02-05','培优部','讲师',3200,800);
insert into emp values(default,'张久军','男','1989-01-11','培优部','讲师',4200,900);

SELECt  * FROM emp;

SELECt  name'姓名',gender'性别' from emp where gender='男';

SELECt  name'姓名',dept'性别' from emp where dept='培优部';

select distinct bonus'奖金' from emp;

select name'姓名',bonus'奖金',job'职位' from emp where bonus > 500;

select name'姓名',salary'薪资' from emp where salary between 1000 and 2000;

select name'姓名',bonus'奖金' from emp where bonus in (300,500,700);

select name'姓名' from emp where name like '王%';


select name'姓名' from emp where name like '%涛';


select gender'性别', count(*) from emp where gender='男';


select job'职位',count(*) from emp group by job;


select sum(salary) + sum(bonus) '总薪资' from emp;


select avg(bonus) from emp;


select name'姓名',birthday'出生日期',sum(salary+ifnull(bonus,0))'总薪资' from emp group by id order by  birthday desc ;


select name,birthday from emp where month(birthday)=month(now())+1;


select * from emp where year(birthday)=1995;


select name,salary from emp order by salary desc limit 0,3;