MySQL数据库简单练习题

简单巩固：

create table student(

id int(10) primary key auto_increment,
name varchar(20) unique,
age int(5) default 16,
sex char(2) not null


);
show databases;
create database libry;
use libry;
show tables;
create table books(
b_id int(11) primary key,
b_name varchar(50),
Authers varchar(100),
Price Float,
pubdate Year,
Note varchar(100),
Num int(11)
);
desc libry;
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(1,'Tal of AAA','Dickes',23,1995,'novel',11);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(2,'EmmaT','Jane lura',35,1993,'Joke',22);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(3,'Story of Jane','Jane Tim',40,2001,'novel',0);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(4,'Lovey Day','George Byron',20,2005,'novel',30);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(5,'Old land','Honore Blade',30,2010,'Law',0);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(6,'The Battle','Upton Sara',30,1999,'medicine',40);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);
select * from books where Note='novel'like update books set Price+5;




create table books(
b_id int(11) primary key,
b_name varchar(50),
Authers varchar(100),
Price Float,
pubdate Year,
Note varchar(100) not null,
Num int(11)
);






show databases;
create database employee;
use employee;
show tables;
create table einfo(
id int(11) not null primary key auto_increment,
name varchar(50) not null,
sex char(2) default '女',
tel varchar(50),
addr varchar(100),
salary Float
);
insert into einfo(id,name,sex,tel,addr,salary) values(10001,'张一一','男','13456789000','广东韶关',1001.58);
insert into einfo values(10002,'刘小红','女','13454319000','广东江门',1201.21);
insert into einfo values(10003,'李四','男','0751-1234567','广东佛山',1004.11),(10004,'刘强','男','0755-5555555','广东深圳',1501.23),(10005,'王艳','女','020-1232133','广东广州',1405.16);




三、按照以下题目要求实现操作，并将sql语句记录在记事本中。
create table customers(
c_num int(11) primary key not null unique auto_increment,
c_name varchar(50),
c_contact varchar(50),
c_city varchar(50),
c_birth datetime not null


);
alter table customers modify c_contact varchar(50) after c_birth;
alter table customers change c_name c_name varchar(70);
alter table customers change c_contact c_phone varchar(50);
alter table customers add c_gender char(1) not null;
alter table customers rename customers_info;


alter table customers_info drop c_city;




六、 按照以下题目要求实现操作，并将sql语句记录在记事本中。
show databases;
create database wangchuanlu;
use wangchuanlu;
show tables;


create table department(
depid int(10) primary key,
depname varchar(20),
depinfo varchar(100)
);


create table employee(
empid int(10) primary key,
name varchar(5),
sex char(2),
title varchar(20),
birthday date,
depid int(10)  
);


create table salary(
empid int(10) primary key,
basesalary  Float,
titlesalary Float,
deduction Float
);


insert into department(depid,depname) values(111,'生产部');
insert into department(depid,depname) values(222,'销售部');
insert into department(depid,depname) values(333,'人事部');




insert into employee(empid,name,sex,title,birthday,depid) values(1001,'张三','男','高级程师','1975-01-01',111);
insert into employee(empid,name,sex,title,birthday,depid) values(1002,'李四','女','助工','1985-01-01',111),(1003,'王五','男','工程师','1978-11-11',222),(1004,'赵六','男','工程师','1979-01-01',222);




insert into salary(empid,basesalary,titlesalary,deduction) values(1001,2200,1100,200),(1002,1200,200,100),(1003,1900,700,200),(1004,1950,700,150);


alter table department change depinfo deincl varchar(100);
alter table department add deincl varchar(100);
alter table employee add constraint foreign key(depid) references department(depid);
update salary set basesalary=1700,titlesalary=600 where empid=(select empid from employee where name='李四');
delete from department where depname='人事部';
select empid,basesalary+titlesalary-deduction as 实发工资,basesalary+titlesalary as 应发工资 from salary;
select * from employee where name like '张%' and (year(curdate())-year(birthday))<40;


select e.name,s.basesalary from employee e,salary s where depid=(select depid from department where depname='销售部') and e.empid=s.empid;


select count(title) as '人数',title as '职称' from employee group by title;


七、 按照以下题目要求实现操作，并将sql语句记录在记事本中。
create table cus(
顾客编号 varchar(10) primary key,
顾客名 varchar(20),
地址 varchar(50),
余额 Float
);


insert into cus(顾客编号,顾客名,地址,余额) values('001','Zhang','Usa',5000);
insert into cus(顾客编号,顾客名,地址,余额) values('002','Liu','China',10000),('003','Liu','Frech',50000),('004','Wen','Jap',250000);
alter table cus change 商品编号 商品编号 varchar(10);


create table pro(
供应厂家编号 int(10) primary key,
供应厂家名称 varchar(20),
商品编号  int(10),
商品价格 Float
);


insert into pro(供应厂家编号,供应厂家名称,商品编号,商品价格) values(1001,'Oracle',100023,1500),(1002,'Ibm',100024,3000),(1003,'Baidu',100025,2900),(1004,'Google',100026,5000);


create table goods(
商品编号  int(10) primary key,
商品名称 varchar(20),
商品数量 int(10)
);


insert into goods(商品编号,商品名称,商品数量) values(100023,'Nokia',5),(100024,'Htc',7),(100025,'Mi',10),(100026,'Asus',3);


create table ord(
商品编号  int(10) primary key,
顾客编号 varchar(10),
订单日期 date
);
insert into ord(商品编号,顾客编号,订单日期) values(100023,'001','1998-2-1'),(100024,'002','1999-3-12'),(100025,'003','2000-10-21'),(100026,'004','2000-11-02');


select 供应厂家名称 from pro;
select 顾客编号,顾客姓名,余额 from cus where 地址='Usa'; 




sql4作业数据代码
teacher:
insert into teacher(tno,tname) values(1, '张老师');
insert into teacher(tno,tname) values(2, '王老师');
insert into teacher(tno,tname) values(3, '李老师');
insert into teacher(tno,tname) values(4, '赵老师');
insert into teacher(tno,tname) values(5, '刘老师');
insert into teacher(tno,tname) values(6, '向老师');
insert into teacher(tno,tname) values(7, '李文静');
insert into teacher(tno,tname) values(8, '叶平');


student:
insert into student(sno,sname,sage,ssex) values(1, '张三', '1980-01-23', '男');
insert into student(sno,sname,sage,ssex) values(2, '李四', '1982-12-12', '男');
insert into student(sno,sname,sage,ssex) values(3, '张飒', '1981-09-09', '男');
insert into student(sno,sname,sage,ssex) values(4, '莉莉', '1983-03-23', '女');
insert into student(sno,sname,sage,ssex) values(5, '王弼', '1982-06-21', '男');
insert into student(sno,sname,sage,ssex) values(6, '王丽', '1984-10-10', '女');
insert into student(sno,sname,sage,ssex) values(7, '刘香', '1980-12-22', '女');


course:
insert into course(cno,cname,tno) values(1, '企业管理', 3);
insert into course(cno,cname,tno) values(2, '马克思', 1);
insert into course(cno,cname,tno) values(3, 'UML', 2);
insert into course(cno,cname,tno) values(4, '数据库', 5);
insert into course(cno,cname,tno) values(5, '物理', 8);


sc:
insert into sc(sno,cno,score) values(1, 1, 80);
insert into sc(sno,cno,score) values(1, 2, 86);
insert into sc(sno,cno,score) values(1, 3, 83);
insert into sc(sno,cno,score) values(1, 4, 89);
insert into sc(sno,cno,score) values(2, 1, 50);
insert into sc(sno,cno,score) values(2, 2, 36);
insert into sc(sno,cno,score) values(2, 4, 59);
insert into sc(sno,cno,score) values(3, 1, 50);
insert into sc(sno,cno,score) values(3, 2, 96);
insert into sc(sno,cno,score) values(3, 4, 69);
insert into sc(sno,cno,score) values(4, 1, 90);
insert into sc(sno,cno,score) values(4, 2, 36);
insert into sc(sno,cno,score) values(4, 3, 88);
insert into sc(sno,cno,score) values(5, 1, 90);
insert into sc(sno,cno,score) values(5, 2, 96);
insert into sc(sno,cno,score) values(5, 3, 98);
insert into sc(sno,cno,score) values(5, 4, 99);
insert into sc(sno,cno,score) values(6, 1, 70);
insert into sc(sno,cno,score) values(6, 2, 66);
insert into sc(sno,cno,score) values(6, 3, 58);
insert into sc(sno,cno,score) values(6, 4, 79);
insert into sc(sno,cno,score) values(7, 1, 80);
insert into sc(sno,cno,score) values(7, 2, 76);
insert into sc(sno,cno,score) values(7, 3, 68);
insert into sc(sno,cno,score) values(7, 4, 59);
insert into sc(sno,cno,score) values(7, 5, 89);

#1.查询课程1的成绩 比 课程2的成绩高 的所有学生的学号.
select a.sno from (select sno,score from sc where cno = 1) a,(select sno,score from sc where cno = 2) b
where a.sno = b.sno and a.score>b.score;




#2.查询平均成绩大于60分的同学的学号和平均成绩；
select sno,avg(score) from sc group by sno  having avg(score)>60




#3.查询所有同学的学号、姓名、选课数、总成绩
select s.sno,s.sname,count(sc.cno),sum(sc.score) from student s,sc  where s.sno = sc.sno
group by s.sno




#4.查询姓“李”的老师的个数
select count(1) from teacher where tname like '李%'




#5.查询没学过“叶平”老师课的同学的学号、姓名；
select sno,sname from student where sno not in  
(select sc.sno from teacher t,sc,course c where t.tno = c.tno  and c.cno = sc.cno and  t.tname = '叶平');


#7.查询学过“叶平”老师所教所有课程的所有同学的学号、姓名
select sno,sname from student where sno in  
(select sc.sno from teacher t,sc,course c where t.tno = c.tno  and c.cno = sc.cno and  t.tname = '叶平');




#6.查询同时学过课程1和课程2的同学的学号、姓名
select sno,sname from student where sno in(select sno from sc where cno = 1)  and sno in (select sno from sc where cno =2)


#8.1查询所有课程成绩小于60分的同学的学号、姓名  1
select sno,sname from student where sno  in (select sno from sc where score<60)


#8.2查询所有课程成绩小于60分的同学的学号、姓名  2
select sno,sname from student where sno  in(
select sno from sc where score <60  group by sno  having count(1)>2)




#9查询没有学全所有课的同学的学号、姓名
#select s.sno,s.sname,count(sc.cno) from student s,sc where s.sno = sc.sno  group by sno


select s.sno,s.sname from student s,sc where s.sno = sc.sno  group by sno having count(sc.cno) <(select count(1) from course)


#10.查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名
select distinct s.sno,s.sname from student s,sc where s.sno = sc.sno  and s.sno !=1  and sc.cno in (select cno from sc where sno = 1)




#11.查询各科成绩最高分和最低分：以如下形式显示：课程号，最高分，最低分
select cno,max(score),min(score) from sc group by cno




#12.查询不同老师所教不同课程平均分, 从高到低显示


#select avg(sc.score),t.tname from teacher t,sc,course c where t.tno = c.tno and c.cno = sc.cno




select avg(sc.score),t.tname from teacher t,sc,course c where t.tno = c.tno and c.cno = sc.cno  group by t.tno  order by avg(sc.score) desc




#13.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 


#select avg(score) from sc group by cno


select avg(score) from sc group by cno  order by avg(score) ,cno desc



sql5作业代码：
student:
sno,sname,ssex,sage
course:
cno,cname,hours
sc:
sno,cno,grade


select * from student,sc where student.sno=sc.sno;
select student.sno,course.cno,sc.grade from student,sc,course where grade between 70 and 80;
#select cname,cno from course;
select sdept,count(sno) from student group by sdept;
select sc.sno,sum(grade) from sc group by sno having sum(grade)>200;
update sc set grade=grade+10 where cno='c01';
select s.sname,s.sdept from student s,sc where sc.sno=s.sno and cno='c02';


delete from sc where grade<50 or grade=null;


sql6作业代码：
select * from user where name='李%';
select u.id,u.name,l.info,l.createdate from user u,log l where u.id=l.uid and name='李四';


select info from log;
select u.name from log l,user u where u.id=l.uid group by uid having count(uid)>2;
select info from log limit 1,2;

select u.name,l.info from user u,log l where u.id=l.id and dept='测试部';

三、按照以下题目要求实现操作，并将sql语句记录在记事本中。
*********************************
create database Market;
*********************************
create table customers(
c_num int(11) primary key  auto_increment,
c_name varchar(50) not null,
c_contact varchar(50) not null,
c_city varchar(50) not null,
c_birth datetime 


);
*********************************
alter table customers modify c_contact varchar(50) after c_birth;
*********************************
alter table customers change c_name c_name varchar(70);
*********************************
alter table customers change c_contact c_phone varchar(50);
*********************************
alter table customers add c_gender char(1);
*********************************
alter table customers rename customers_info;
*********************************
alter table customers_info drop c_city;
*********************************
四、按照以下题目要求实现操作，并将sql语句记录在记事本中。
*********************************
create database company;
*********************************
create table office(
officeCode int primary key unique not null,
City varchar(30) not null,
address varchar(50),
country varchar(50) not null,
postalCode varchar(25) unique
);
*********************************
create table employees(
empNum int(11) primary key not null unique auto_increment,
lastName varchar(50) not null,
firstName varchar(50) not null,
mobile varchar(25),
Code int not null ,
jobTutle varchar(50) not null,
birth Datetime not null,
Note varchar(255),
Sex varchar(5)
);
*********************************
alter table employees modify mobile varchar(25) after Code;
*********************************
alter table employees change birth employee_birth Datetime;
*********************************
alter table employees change sex sex varchar(5) not null;
*********************************
alter table employees drop Note;
*********************************
alter table employees add favoriate_activity varchar(100);
*********************************
alter table employees rename employees_info;
*********************************


六、按照以下题目要求实现操作，并将sql语句记录在记事本中。
*********************************
create table department(
depid int(10) primary key,
depname varchar(10)
);


create table emoloyee(
empid int(10) primary key,
name varchar(10),
sex varchar(10),
title varchar(10),
birthday Datetime,
depid int(10),
foreign key(depid) references department(depid)
);


create table salary(
empid int(10) primary key,
basesalary Float,
titlesalary Float,
deduction Float
);
*********************************
insert into department values(111,'生产部',null),(222,'销售部',null),(333,'人事部',null);
---------------------------------
insert into emoloyee values(1001,'张三','男','高级工程师','1975-1-1',111),(1002,'李四','女','助工','1985-1-1',111),(1003,'王五','男','工程师','1978-11-11',222),(1004,'赵六','男','工程师','1979-1-1',222);
---------------------------------
insert into salary values(1001,2200,1100,200),(1002,1200,200,100),(1003,1900,700,200),(1004,1950,700,150);
*********************************
update salary set basesalary=1700,titlesalary=600 where empid=(select empid from emoloyee where name='李四');
*********************************
delete from department where depid=333;
*********************************
select empid as '雇员编号',basesalary+titlesalary-deduction as '实发工资',basesalary+titlesalary as '应发工资' from salary;
*********************************
select * from employee where name like '张%' and (year(curdate())-year(birthday))<40;
select e.name,s.basesalary from employee e,salary s where depid=(select depid from department where depname='销售部') and e.empid=s.empid;
select count(title) as '人数',title as '职称' from employee group by title;

五：

#将李四的基本工资改为1700元，职务工资为600
update salary set  basesalary=1700,titlesalary=600 where empid = (select empid from emoloyee where name = '李四')




#删除人事部门的部门记录。
delete from  department where depname = '人事部'




#查询出每个雇员的雇员编号，实发工资（基本工资+职务工资-扣除），应发工资（基本工资+职务工
资）。


select empid '雇员编号',(basesalary+titlesalary-deduction)  '实发工资',(basesalary+titlesalary)  '应发工资'
from salary;




#查询姓“张”且年龄小于40的员工的记录。
select empid,name,sex,title,birthday from emoloyee where name like '张%' and (year(sysdate())-year(birthday))<40




select  year(sysdate())-year(birthday)  from emoloyee




#查询销售部门的雇员姓名及其基本工资。
select d.depname,e.name,s.basesalary from department d,emoloyee e,salary s 
where d.depid = e.depid  and s.empid = e.empid and  d.depname ='销售部'




#统计各职称的人数
select count(1) from emoloyee group by title


#从商品表中查询商品名称倒数第二个字母为i的商品名称及数量，并按数量降序排序
select name,num from goods where name like '%i_'  order by num desc




#从商品表中查询最多商品数量，最小商品数量及商品总数量的记录信息


select max(num),min(num),sum(num) from goods




#找出所有其家庭地址中含有“北京”的教师的教工号及部门号，要求显示结果中各列标题用中文表示。
select number '教工号',depno '部门号' from teacher where address like '%北京%'




#获得Teacher表中工资最高的教工号和姓名。
select number,name from teacher where salary = (select max(salary) from teacher)




#找出所有收入在2500～4000之间的教工号。
select number from teacher where salary between 2500 and 4000




#查找在网络技术系工作的教师的姓名、性别和工资。
select t.name,t.sex,t.salary from teacher t,department d where d.depno = t.depno  and d.depname='网络技术系'




#计算男性教师和女性教师的平均工资。
select avg(salary),sex from teacher group by sex


#视图Teacher_view，视图包含教工号、姓名、部门号和工资。
create view teacher_view as select number,name,depno,salary from teacher;


select salary from teacher_view where name = 'lucy'






update teacher set salary= salary+300 where depno = 603


#求所有男员工的姓名和部门名称。


select t.name,d.depname from teacher t,department d where d.depno = t.depno and t.sex = '男'

三：

#查询student表中，名字里有龙的学生
select sname from student where sname like '%龙%';

#查询student表中，birthday大于2016-02-01的学生，并按id正序排序
select sname from student where birthday < '2016-02-01' order by id




#8）查询student表中，birthday小于2016-02-02的学生的数量
select count(id) from student where  birthday > '2016-02-02'






#9）查询出所有学生总共分几个班级


select count(m.id) from myclass m ,student s where s.cid = m.id  group by m.id
select count(id) from myclass where id in (select  distinct m.id from myclass m ,student s where s.cid = m.id )




select count(id),cname from myclass where id in (select  distinct m.id from myclass m ,student s where s.cid = m.id ) group by id




#10）查询出班级的学生人数大于2的有哪些
select count(s.cid),m.cname from myclass m ,student s where s.cid = m.id  group by m.id  having   count(s.cid)>2


#只显示班级
select m.cname from myclass m ,student s where s.cid = m.id  group by m.id  having   count(s.cid)>2




#11）查询student表中，班级id为1,2的学生，通过in实现
select sname from student where id in (1,2);




#12）查询和观世音同班的同学
select sname from student where cid = (select distinct m.id from student s,myclass m where s.cid = m.id and s.sname = '观世音')  and sname !='观世音'


#学生对应的班级id
select m.id from student s,myclass m where s.cid = m.id


#13）全查student表，按分页处理，每页2条，查出第三页的学生
select id,sname,birthday from student limit 4,2




#14）查询显示所有学生的两个信息：学生姓名，班级名字和教师名字
select s.sname,m.cname,m.cteacher from student s,myclass m where s.cid = m.id


select s.sname,m.cname,m.cteacher from student s right join myclass m  on s.cid = m.id