标题: SQL练习 [打印本页] 作者: Lcca 时间: 2018-5-15 09:38 标题: SQL练习 1.向USER表中插入三个员工信息
insert into USER (ID,USERNAME,GENDER,BIRTHDAY,ENTRY_DATE,JOB,SALARY,RESUME) values (1,'yangmaolin','female','1994-05-23','2017-10-30','CEO',10000,'strong man');
insert into USER values(2, 'qinqilin', 'male', '1999-09-19', '2017-10-30', 'COO', 10000, 'humor man');
insert into USER values(3, '春哥', 'male', '1997-04-01', '2017-10-30', 'UFO', 10000, '帅锅');
插入和查询时发生的中文乱码问题，解决：
character_set_client:指示客户端使用的编码
set character_set_client=gbk：通知数据库，客户端使用的编码是gbk(临时性)
character_set_results:指示返回的结果集的编码
set character_set_results=gbk：通知数据库，客户端只能接受的结果集为gbk(临时性)
2.查看员工信息
select * from USER;
3.查看库中所有编码
show variables like 'character_set%';
4.通知服务器，客户端使用的是gbk
set character_set_client=gbk：通知数据库，客户端使用的编码是gbk(临时性)
5.通知服务器，客户端接受的结果使用gbk
set character_set_results=gbk：通知数据库，客户端只能接受的结果集为gbk(临时性)
6.将所有员工薪水改为5000元
update USER set SALARY=5000;
7.将姓名为yangmaolin的员工薪水修改为3000元
update USER set SALARY=3000 where USERNAME='yangmaolin';
8.将姓名为qinqilin的员工薪水修改为4000元，job改为CEO
update USER set SALARY=4000,JOB='CEO' where USERNAME='qinqilin';
9.将姓名为'春哥'的薪水在原有基础上增加1000元
update USER set SALARY=SALARY+1000 where USERNAME='春哥';
10.删除表中名为'qinqilin'的记录
delete from USER where USERNAME='qinqilin';
11.删除表中所有记录
delete from USER;
truncate table USER;
12.查看表中所有学生信息
select * from USER;
13.查询表中所有学生的姓名和对应的英语成绩
select NAME, ENGLISH from STUDENT;
14.过滤表中的重复数据
select distinct ENGLISH from STUDENT;
15.在所有学生分数上加10分特长分
select NAME,ENGLISH+10,CHINESE+10,MATH+10 from STUDENT;
16.统计每个学生的总分
select NAME,ENGLISH+CHINESE+MATH from STUDENT;
17.统计每个学生的总分，使用别名
select NAME 姓名,ENGLISH+CHINESE+MATH 总分 from STUDENT;
18.查询姓名为XX的学生成绩
select * from STUDENT where NAME='王五';
19.查询英语成绩大于90分的同学
select * from STUDENT where ENGLISH > 90;
20.查询总分大于250分的所有同学
select * from STUDENT where ENGLISH+MATH+CHINESE>250;
21.查询英语分数在80-90之间的同学
select * from STUDENT where ENGLISH between 80 and 90;
22.查询数学分数为89,90,91的同学
select * from STUDENT where MATH in(89, 90, 91);
23.查询所有姓李的学生成绩
select * from STUDENT where NAME like '李%';
24.查询数学分>80，语文分>80的同学
select * from STUDENT where MATH>80 and CHINESE>80;
25.对数学进行排序后输出
select * from STUDENT order by MATH asc;
26.对总分排序后输出，然后再按照从高到低的顺序输出
select NAME, ENGLISH+CHINESE+MATH from STUDENT order by ENGLISH+CHINESE+MATH desc;
27.对姓李的学生的数学成绩排序输出
select * from STUDENT where NAME like '李%' order by ENGLISH desc;
28.统计一个班级共有多少学生
select count(*) from STUDENT;
29.统计数学成绩大于90的学生有多少个
select count(*) from STUDENT where MATH>90;
select count(ID) from STUDENT where MATH>90;
select count(100) from STUDENT where MATH>90;
30.统计总分数大于250的人数有多少
select count(*) from STUDENT where (ENGLISH+MATH+CHINESE)>250;
31.统计一个班级的数学总成绩
select sum(MATH) from STUDENT;
32.统计一个班级语文，英语，数学各科的总成绩
select sum(CHINESE),sum(ENGLISH),sum(MATH) from STUDENT;
33.统计一个班级语文，英语，数学的成绩总和
select sum(CHINESE+MATH+ENGLISH) from STUDENT;
34.统计一个班级语文成绩平均分
select avg(CHINESE) from STUDENT;
select sum(CHINESE)/count(*) from STUDENT;
35.求一个班级数学平均分
select avg(MATH) from STUDENT;
36.求一个班级总分平均分
select avg(ENGLISH+CHINESE+MATH) from STUDENT;
37.对订单表中商品归类后，显示每一类商品的总价
select sum(price),product from orders group by product;
38.查询购买了几类商品，并且每类商品总价大于100的商品
select sum(price),product from orders group by product having sum(price)>100;
----------------------------------------------------------------------------------------
create table student(
id int,
name varchar(200),
english int,
chinese int,
math int
);
insert into student values (1,'张三',85,74,91);
insert into student values (2,'李四',95,90,83);
insert into student values (3,'王五',85,84,59);
insert into student values (4,'赵六',75,79,76);
insert into student values (5,'田七',69,63,98);
insert into student values (6,'李四',89,90,83);
----------------------------------------------------------------------------------------
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'橘子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
----------------------------------------------------------------------------------------
create table BUYER (
ID int primary key,
NAME varchar(20),
CITY varchar(20)
);
create table COMMODITY (
ID int primary key,
NAME varchar(20),
BUYER_ID int,
constraint BUYER_ID_FK foreign key (BUYER_ID) references BUYER(ID)
);
insert into BUYER (ID,NAME,CITY) values (1,'李宗瑞','台湾');
insert into BUYER (ID,NAME,CITY) values (2,'苍井空','日本');
insert into BUYER (ID,NAME,CITY) values (3,'小泽','日本');
insert into BUYER (ID,NAME,CITY) values (4,'吉泽','日本');
insert into BUYER (ID,NAME,CITY) values (5,'泷泽','null');
insert into COMMODITY (ID, NAME,BUYER_ID) values (1,'摄像头',1);
insert into COMMODITY (ID, NAME,BUYER_ID) values (2,'电视机',1);
insert into COMMODITY (ID, NAME,BUYER_ID) values (3,'照相机',2);
insert into COMMODITY (ID, NAME,BUYER_ID) values (4,'护士服',3);
insert into COMMODITY (ID, NAME,BUYER_ID) values (5,'警察服',4);
insert into COMMODITY (ID, NAME,BUYER_ID) values (6,'0.01',null);
查看购买了商品的买家的姓名，城市和所购买的商品信息
隐式内连接：
select b.name,b.city,c.name from buyer b, commodity c where b.id=c.buyer_id;
显示内连接：
select b.name,b.city,c.name from BUYER b inner join COMMODITY c on b.ID=c.BUYER_ID;
查看所有买家以及买家所购买的商品信息
左外连接：
select b.name,b.city,c.name from buyer b left join commodity c on b.id=c.buyer_id;
查看所有商品以及购买商品的买家信息
右外连接：
select b.name,b.city,c.name from buyer b right join commodity c on b.id=c.buyer_id;
查看李宗瑞买的所有商品信息
子查询：select * from commodity where buyer_id=(select id from buyer where name='李宗瑞');