2021-8-2 Mysql个人练习题
创建学生表
CREATE TABLE student( id int, uname VARCHAR(20), chinese FLOAT, english FLOAT, math FLOAT ); INSERT INTO student(id,uname,chinese,english,math) VALUES(1,'张小明',89,78,90); INSERT INTO student(id,uname,chinese,english,math) VALUES(2,'李进',67,53,95); INSERT INTO student(id,uname,chinese,english,math) VALUES(3,'王五',87,78,77); INSERT INTO student(id,uname,chinese,english,math) VALUES(4,'李一',88,98,92); INSERT INTO student(id,uname,chinese,english,math) VALUES(5,'李来财',82,84,67); INSERT INTO student(id,uname,chinese,english,math) VALUES(6,'张进宝',55,85,45); INSERT INTO student(id,uname,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
答案与题目
SELECT * from student;#查询所有学生 SELECT uname,english from student;#查询学生以及对应的英语成绩 SELECT (chinese+english+math) as 总分 from student;#查询学生总分 SELECT (chinese+english+math+10) as 总分加附加分 from student;#查询学生总分加101分 SELECT chinese 中文,english 英语,math 数学 from student;#给列增加名字 SELECT * from student where uname='李一';#姓名为李一的学生成绩 SELECT uname from student where english>=90;#查询英语成绩大于等于90的学生姓名 SELECT uname as 用户名,(chinese+english+math) as 总分 from student HAVING 总分>240;#总分大于240的学生 SELECT uname 用户名,chinese 中文 from student having 中文 BETWEEN 80 and 90;#分数在80到90之间的学生
创建员工表，部门表，员工等级表
-- 部门表 CREATE TABLE DEPT( DEPTNO INT PRIMARY KEY, DNAME VARCHAR(14), -- 部门名称 LOC VARCHAR(13)-- 部门地址 ) ; INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); -- 员工表 CREATE TABLE EMP( EMPNO INT PRIMARY KEY, -- 员工编号 ENAME VARCHAR(10), -- 员工姓名 JOB VARCHAR(9), -- 员工工作 MGR INT, -- 员工直属领导编号 HIREDATE DATE, -- 入职时间 SAL DOUBLE, -- 工资 COMM DOUBLE, -- 奖金 DEPTNO INT, -- 所在部门 FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)); -- 关联dept表 -- ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO); INSERT INTO EMP VALUES(7369,'SMITH','职员',7566,"1980-12-17",800,NULL,20); INSERT INTO EMP VALUES(7499,'ALLEN','销售员',7698,'1981-02-20',1600,300,30); INSERT INTO EMP VALUES(7521,'WARD','销售员',7698,'1981-02-22',1250,500,30); INSERT INTO EMP VALUES(7566,'JONES','经理',7839,'1981-04-02',2975,NULL,20); INSERT INTO EMP VALUES(7654,'MARTIN','销售员',7698,'1981-09-28',1250,1400,30); INSERT INTO EMP VALUES(7698,'BLAKE','经理',7839,'1981-05-01',2850,NULL,30); INSERT INTO EMP VALUES(7782,'CLARK','经理',7839,'1981-06-09',2450,NULL,10); INSERT INTO EMP VALUES(7788,'SCOTT','职员',7566,'1987-07-03',3000,2000,20); INSERT INTO EMP VALUES(7839,'KING','董事长',NULL,'1981-11-17',5000,NULL,10); INSERT INTO EMP VALUES(7844,'TURNERS','销售员',7698,'1981-09-08',1500,50,30); INSERT INTO EMP VALUES(7876,'ADAMS','职员',7566,'1987-07-13',1100,NULL,20); INSERT INTO EMP VALUES(7900,'JAMES','职员',7698,'1981-12-03',1250,NULL,30); INSERT INTO EMP VALUES(7902,'FORD','销售员',7566,'1981-12-03',3000,NULL,20); INSERT INTO EMP VALUES(7934,'MILLER','职员',7782,'1981-01-23',1300,NULL,10); -- 工资等级表 CREATE TABLE SALGRADE( GRADE INT,-- 等级 LOSAL DOUBLE, -- 最低工资 HISAL DOUBLE ); -- 最高工资 INSERT INTO SALGRADE VALUES (1,500,1000); INSERT INTO SALGRADE VALUES (2,1001,1500); INSERT INTO SALGRADE VALUES (3,1501,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999);
SELECT * from emp where DEPTNO=30;#查询部门30的员工 SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE JOB='职员';#查询是职员的编号，姓名，部门编号 SELECT * from emp where COMM>SAL;#查询奖金大于工资的员工信息 SELECT * from emp where COMM>0.6*SAL;#查询奖金大于60%的工资的员工信息 SELECT * from emp where ENAME like '%A%';#查询员工姓名包含A的员工信息 SELECT * from emp where ENAME LIKE 'A%' OR ENAME LIKE 'B%' OR ENAME LIKE 'S%' ORDER BY ENAME;#查询姓名以A、B、S开头的员工信息 SELECT * from emp where ENAME LIKE '_______';#查询名字为7个字符的员工信息（7个下划线） SELECT * from emp where ENAME not in(SELECT ENAME from emp where ENAME LIKE '%E%');#查询名字不包含E字符的员工信息 SELECT * from emp where ENAME not LIKE '%E%';#查询名字不包含E字符的员工信息 SELECT * from emp where !(ENAME LIKE '%E%');#查询名字不包含E字符的员工信息
SELECT * from emp ORDER BY ENAME asc;#查询员工信息并根据姓名升序 SELECT * from emp ORDER BY ENAME DESC,SAL asc;#查询员工信息并根据姓名降序，工资升序 select ENAME,ROUND(SAL/30,2) FROM emp;#查询员工的日薪资，并保留两位小数 SELECT JOB from emp where COMM>0;#找出有奖金的工作 SELECT * from emp where COMM<100 || ISNULL(COMM);#查询奖金少于100或者没有奖金的员工信息 SELECT * from emp where (JOB='职员' AND DEPTNO=10)||(JOB='经理' AND DEPTNO=20);#查询10部门职员和20部门经理 SELECT * from emp where (JOB='职员' AND DEPTNO=10)||(JOB='经理' AND DEPTNO=20)||(JOB!='职员' and JOB!='经理' and SAL>=2000);#查询10部门职员和20部门经理和不是经理、职员，但工资高于2000
创建职员表
CREATE TABLE employee( id INT, NAME VARCHAR(20), sex VARCHAR(10), birthday DATE, salary FLOAT, RESUME TEXT ); INSERT INTO employee VALUES(1,"zhangsan","male","1980-11-25",2000,"good body"); INSERT INTO employee VALUES(2,"lisi","male","1980-04-25",1000,"good body"); INSERT INTO employee VALUES(3,"xiaohong","female","1978-11-25",4000,"good girl");
UPDATE employee set salary=5000;#把所有员工信息修改为5000 UPDATE employee set salary=3000 where NAME='zhangsan';#把zhangsan的工资改为3000 UPDATE employee set salary=4000,sex='female' where NAME='lisi';#把lisi的工资改为4000，性别改为female UPDATE employee set salary=salary+1000 where NAME='xiaohong';#把xiaohong的工资加1000
创建职员表
create table emp( id int(11) , name varchar(20), sex varchar(10), birthday date , salary float(10,2) , -- 薪水 bonus float(10,2) , -- 奖金 department varchar(20), -- 部门 resume varchar(200) -- 简介 ); -- 插入员工数据 insert into emp values (1,'zhangsan','male','1980-11-25',2000,100,'总裁办','good body'), (2,'lisi','male','1980-04-25',1000,200,'研发部','good body'), (3,'xiaohong','female','1978-11-25',4000,100,'财务部','good girl'), (4,'wangwu','male','1981-01-25',4000,400,'人事部','good body'), (5,'zhaoliu','male','1978-12-25',2000,NULL,'人事部','good body'), (6,'tianqi','female','1998-05-25',2000,100,'人事部','good girl'), (7,'renba','male','1968-10-25',3000,500,'财务部','good body');
SELECT * from emp;#查询所有员工信息 SELECT name,salary from emp;#查询员工姓名和薪水 SELECT id 编号,name 姓名,sex 性别,birthday 生日,salary 薪水,bonus 奖金,department 部门,resume 备注 from emp;#使用中文列名 SELECT * from emp where (salary+bonus)>3000;#查询总收入大于3000的员工信息 SELECT name,department,salary,bonus from emp where(salary+bonus)>4000;#查询总收入大于4000的员工姓名、部门、薪水和奖金 select * from emp where birthday>='1980-1-1';#查询80后员工 SELECT * from emp where salary>=4000 and sex='female';#查询工资大于等于4000且为女性的员工信息 SELECT department,count(*) from emp GROUP BY department;#查询各个部门的员工数量 SELECT department,count(*),sum(salary) from emp GROUP BY department;#查询各个部门的员工数量和薪水支出 SELECT department,count(*),sum(salary)+sum(bonus) from emp GROUP BY department;#查询各个部门的员工数量和总支出
SELECT department,sum(salary+IFNULL(bonus,0) ) as 总支出 from emp GROUP BY department HAVING 总支出>=7000; #查询总支出大于7000的同事 SELECT avg(salary) from emp where name REGEXP('i');#查询名字包含i的员工平均工资 SELECT sum(salary) from emp where name regexp('i');#查询名字包含i的员工工资总和
创建部门表和员工表
-- 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'); -- 创建员工表 CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), gender CHAR(1), -- 性别 salary DOUBLE, -- 工资 join_date DATE, -- 入职日期 dept_id INT, FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键) ); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
SELECT * from emp as t1,dept as t2 where t1.dept_id=t2.id;#查询员工表和对应的部门 SELECT t1.NAME,t1.gender,t2.NAME as 部门名称 from emp as t1,dept as t2 where t1.dept_id=t2.id;#查询姓名，性别，部门名称 SELECT * from emp as e inner join dept as d on e.dept_id=d.id;#内连接 DELETE from emp where NAME like '沙悟净'; INSERT into emp VALUES(0,'沙悟净','男',8000,NOW(),null);#插入一行新语句 SELECT * from emp as e left join dept as d on d.id=e.dept_id;#左连接，左边必须要有 SELECT * from emp as e right join dept as d on d.id=e.dept_id;#右连接，右边必须要有 #INSERT into dept VALUES(0,'公关部'); SELECT * from emp as e where e.salary>=(SELECT MAX(salary) from emp);# 查询工资最高的同事信息 SELECT * from emp as e where e.salary<=(SELECT min(salary) from emp);#查询工资最低的同事信息 SELECT * from emp as e where e.salary<=(SELECT avg(salary) from emp);#查询低于平均工资的同事信息 SELECT * from emp as e where e.dept_id in (SELECT id from dept where NAME LIKE "财务部");#查询财务部同事的信息 SELECT * from emp as e where e.dept_id in (SELECT id from dept where NAME LIKE "财务部" or NAME LIKE "市场部");#查询财务部和市场部同事的信息
SELECT * from emp e left join dept d on e.dept_id=d.id where e.join_date>='2011-11-11';#查询在2011-11-11之后入职的员工
创建借书表
CREATE TABLE book ( BID CHAR(10) NOT NULL, title CHAR(50) DEFAULT NULL, author CHAR(20) DEFAULT NULL, PRIMARY KEY (BID) ); INSERT INTO book VALUES ('B001', '人生若只如初见', '安意如'); INSERT INTO book VALUES ('B002', '入学那天遇见你', '晴空'); INSERT INTO book VALUES ('B003', '感谢折磨你的人', '如娜'); INSERT INTO book VALUES ('B004', '我不是教你诈', '刘庸'); INSERT INTO book VALUES ('B005', '英语四级', '白雪'); CREATE TABLE borrow ( borrowID CHAR(10) NOT NULL, stuID CHAR(10) DEFAULT NULL, BID CHAR(10) DEFAULT NULL, T_time VARCHAR(50) DEFAULT NULL, B_time VARCHAR(50) DEFAULT NULL, PRIMARY KEY (borrowID) ) ; INSERT INTO borrow VALUES ('T001', '1001', 'B001', '2007-12-26 00:00:00', NULL); INSERT INTO borrow VALUES ('T002', '1004', 'B003', '2008-01-05 00:00:00', NULL); INSERT INTO borrow VALUES ('T003', '1005', 'B001', '2007-10-08 00:00:00', '2007-12-25 00:00:00'); INSERT INTO borrow VALUES ('T004', '1005', 'B002', '2007-12-16 00:00:00', '2008-01-07 00:00:00'); INSERT INTO borrow VALUES ('T005', '1002', 'B004', '2007-12-22 00:00:00', NULL); INSERT INTO borrow VALUES ('T006', '1005', 'B005', '2008-01-06 00:00:00', NULL); INSERT INTO borrow VALUES ('T007', '1002', 'B001', '2007-09-11 00:00:00', NULL); INSERT INTO borrow VALUES ('T008', '1005', 'B004', '2007-12-10 00:00:00', NULL); INSERT INTO borrow VALUES ('T009', '1004', 'B005', '2007-10-16 00:00:00', '2007-12-18 00:00:00'); INSERT INTO borrow VALUES ('T010', '1002', 'B002', '2007-09-15 00:00:00', '2008-01-05 00:00:00'); INSERT INTO borrow VALUES ('T011', '1004', 'B003', '2007-12-28 00:00:00', NULL); INSERT INTO borrow VALUES ('T012', '1002', 'B003', '2007-12-30 00:00:00', NULL); CREATE TABLE student ( stuID CHAR(10) NOT NULL, stuName VARCHAR(10) DEFAULT NULL, major VARCHAR(50) DEFAULT NULL, PRIMARY KEY (stuID) ); INSERT INTO student VALUES ('1001', '林林', '计算机'); INSERT INTO student VALUES ('1002', '白杨', '计算机'); INSERT INTO student VALUES ('1003', '虎子', '英语'); INSERT INTO student VALUES ('1004', '北漂的雪', '工商管理'); INSERT INTO student VALUES ('1005', '五月', '数学');
SELECT b2.stuID 学生编号,s1.stuName 学生姓名,b1.BID 图书编号,b1.title 图书名称,b2.T_time 借出日期 from borrow b2 left join student s1 on b2.stuID=s1.stuID left join book b1 on b1.BID=b2.BID where b2.T_time BETWEEN '2007-12-15' and '2008-1-8' ORDER BY s1.stuName;#查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期； SELECT s1.stuName as 学生名称,s1.stuID 学生编号,s1.major 专业 from student s1 where s1.stuID in (SELECT stuId from borrow);#查询所有借过图书的学生编号、学生名称、专业； SELECT s1.stuName as 学生名称,s1.stuID 学生编号,s1.major 专业 from student s1 where s1.stuID not in (SELECT stuId from borrow);#查询没有借过图书的学生编号、学生名称、专业； SELECT s1.stuName,b1.title,b2.T_time,b2.B_time from borrow b2 inner join student s1 on b2.stuID=s1.stuID inner join book b1 on b1.BID=b2.BID where b1.author like '安意如';#查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期； SELECT b2.stuID 学生编号,s1.stuName 学生姓名,b1.BID 图书编号,b1.title 图书名称,b2.T_time 借出日期 from borrow b2 left join student s1 on b2.stuID=s1.stuID left join book b1 on b1.BID=b2.BID where !ISNULL(b2.T_time) and ISNULL(b2.B_time);#查询借过书但有书未归还的学生编号、学生名称、图书编号、图书名称、借出日期 SELECT s1.stuName 学生姓名,count(*) from borrow b2 inner join student s1 on b2.stuID=s1.stuID inner join book b1 on b1.BID=b2.BID where ISNULL(b2.B_time) GROUP BY s1.stuName;#查询目前借书但未归还图书的学生名称及未还图书数量；
转载：https://www.cnblogs.com/battlecry/p/9573858.html
浙公网安备 33010602011771号