mysql—练习
之前我们博客所讲的mysql数据库到这里就告一段落，我们来做一些mysql数据库的练习结束mysql数据库。
- 练习中表的数据：
CREATE TABLE dept(
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT "");
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
CREATE TABLE emp(
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
ename VARCHAR(20) NOT NULL DEFAULT "",
job VARCHAR(9) NOT NULL DEFAULT "",
mgr MEDIUMINT UNSIGNED,
hiredate DATE NOT NULL,
sal DECIMAL(7,2) NOT NULL,
comm DECIMAL(7,2),
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0);
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1991-2-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1991-9-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1997-4-19',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
(7900,'MILLER','CLERK',7782,'1991-1-23',1300.00,NULL,10);
CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
);
INSERT INTO salgrade VALUES(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
- 写出查看dept表和emp表的结构 的sql语句。
DESC dept;
DESC emp;
-
使用简单查询语句完成：
- 显示所有部门的名称。
SELECT dname FROM dept;- 显示所有雇员名及其全年收入 13月(工资+补助)，并指定列别名"年收入"。
SELECT ename,(sal+ IFNULL(comm,0)) * 13 年收入 FROM emp; -
限制查询数据：
- 显示工资超过2850的雇员姓名和工资。
SELECT ename,sal FROM emp WHERE sal > 2850;- 显示工资不在1500到2850之间的所有雇员名及工资。
SELECT ename,sal FROM emp WHERE sal <1500 OR sal>2850;- 显示编号为7566的雇员姓名及所在部门编号。
SELECT ename,deptno FROM emp WHERE empno = 7566;- 显示部门10和30中工资超过1500的雇员名及工资。
SELECT ename,sal FROM emp WHERE (deptno=10 OR deptno=30) AND sal>1500;- 显示无管理者的雇员名及岗位。
SELECT ename,sal FROM emp WHERE mgr IS NULL; -
排序数据
- 显示在1991年2月1日到1991年5月1日之间雇用的雇员名，岗位及雇用日期，并以雇用日期进行排序。
SELECT ename,job,hiredate FROM emp WHERE hiredate > '1991-02-01' AND hiredate < '1991-05-01' ORDER BY hiredate;- 显示获得补助的所有雇员名，工资及补助，并以工资降序排序。
SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL ORDER BY sal DESC; -
根据：emp员工表
- 选择部门30中的所有员工。
SELECT * FROM emp WHERE deptno = 30;- 列出所有办事员(CLERK)的姓名，编号和部门编号。
SELECT ename,empno,deptno FROM emp WHERE job='CLERK';- 找出佣金高于薪金的员工。
SELECT * FROM emp WHERE IFNULL(comm,0) > sal;- 找出佣金高于薪金60%的员工。
SELECT * FROM emp WHERE IFNULL(comm,0) > sal * 0.6;- 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料。
SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job='CLERK');- 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)，还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。
SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job='CLERK') OR(job != 'MANAGER' AND job != 'CLERK' AND sal >=2000);- 找出收取佣金的员工的不同工作。
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;- 找出不收取佣金或收取的佣金低于100的员工。
SELECT * FROM emp WHERE IFNULL(comm,0) <100;- 找出各月倒数第三天受雇的所有员工。
SELECT * FROM emp WHERE LAST_DAY(hiredate) - 2 = hiredate;- 找出早于12年受雇的员工。
SELECT * FROM emp WHERE DATE_ADD(hiredate, INTERVAL 12 YEAR) <NOW();- 以首字母小写的方式显示所有员工的姓名。
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) FROM emp;- 显示正好为5个字符的员工的姓名。
SELECT * FROM emp WHERE LENGTH(ename) = 5;- 显示不带有"R"的员工的姓名。
SELECT * FROM emp WHERE ename NOT LIKE '%R%';- 显示所有员工姓名的前三个字符。
SELECT LEFT(ename,3) FROM emp;- 显示所有员工的姓名，用a替换所有"A"。
SELECT REPLACE(ename,'A','a') FROM emp;- 显示满10年服务年限的员工的姓名和受雇日期。
SELECT ename,hiredate FROM emp WHERE DATE_ADD(hiredate, INTERVAL 10 YEAR) <NOW();- 显示员工的详细资料，按姓名排序。
SELECT * FROM emp ORDER BY ename;- 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。
SELECT ename,hiredate FROM emp ORDER BY hiredate;- 显示所有员工的姓名、工作和薪金，按工作降序排序，若工作相同则按薪金排序。
SELECT ename,job,sal FROM emp ORDER BY job DESC,sal;- 显示所有员工的姓名、加入公司的年份和月份，接受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。
SELECT ename,CONCAT(YEAR(hiredate),'-',MONTH(hiredate)) FROM emp ORDER BY MONTH(hiredate),YEAR(hiredate);- 显示在一个月为30天的情况所有员工的日薪金，忽略余数。
SELECT ename,FLOOR(sal/30),sal/30 FROM emp;- 找出在(任何年份的)2月受聘的所有员工。
SELECT * FROM emp WHERE MONTH(hiredate) = 2;- 对于每个员工，显示其加入公司的天数。
SELECT ename,DATEDIFF(NOW(),hiredate) FROM emp;- 显示姓名字段的任何位置包含"A"的所有员工的姓名。
SELECT * FROM emp WHERE ename LIKE '%A%';- 以年月日的方式显示所有员工的服务年限。
SELECT ename, FLOOR(DATEDIFF(NOW(),hiredate) /365) AS '工作年', FLOOR(DATEDIFF(NOW(),hiredate) % 365 /31) AS '工作月', FLOOR(DATEDIFF(NOW(),hiredate) %31) AS '工作日' FROM emp; -
根据：emp员工表，dept部门表，工资 = 薪金sal +佣金 comm。
- 列出至少有一个员工的所有部门。
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) >1;- 列出薪金比"SMITH"多的所有员工。
SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');- 列出受雇日期晚于其直接上级的所有的员工。
SELECT worker.ename '员工名',worker.hiredate '员工入职时间', leader.ename '上级名',leader.hiredate '上级入职时间' FROM emp worker,emp leader WHERE worker.hiredate > leader.hiredate AND worker.mgr = leader.empno;- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT dname, emp.* FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;- 列出所有"CLERK"(办事员) 的姓名及其部门名称。
SELECT ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno AND job = 'CLERK';- 列出最低薪金大于1500的各种工作。
SELECT MIN(sal) AS min_sal ,job FROM emp GROUP BY job HAVING min_sal > 1500;- 列出在部门"SALES"(销售部)工作的员工的姓名。
SELECT ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno AND dname = 'SALES';- 列出薪金高于公司平均薪金的所有员工。
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);- 列出与"SCOTT"的从事相同工作的所有员工。
SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT');- 列出薪金高于所在部门30的工作的所有员工的薪金的员工姓名和薪金。
SELECT ename,sal FROM emp WHERE sal > ( SELECT MAX(sal) FROM emp WHERE deptno = 30);- 列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT deptno,COUNT(*) '部门员工数量',FORMAT(AVG(sal),2) '部门平均工资', FORMAT(AVG(DATEDIFF(NOW(),hiredate) / 365),2) '部门平均服务期限' FROM emp GROUP BY deptno;- 列出所有员工的姓名、部门名称和工资。
SELECT ename,dname,sal FROM emp,dept WHERE emp.deptno = dept.deptno;- 列出所有部门的详细信息和部门人数。
SELECT dept.*,COUNT(*)'部门人数' FROM emp,dept WHERE emp.deptno = dept.deptno GROUP BY emp.deptno;- 列出各种工作的最低工资。
SELECT MIN(sal),job FROM emp GROUP BY job;15.列出MANAGER(经理)的最低薪金。
SELECT MIN(sal),job FROM emp WHERE job = 'MANAGER';- 列出所有员工的年工资，按年薪从低到高排序。
SELECT ename,(sal+IFNULL(comm,0)) *12 year_sal FROM emp ORDER BY year_sal ; -
设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。
现要建立关于系、学生、班级的数据库，关系模式为：
班 CLASS (班号 classid，专业名 subject，系名 deptname，入学年份 enrolltime，人数 num)
学生 STUDENT (学号 studentid，姓名 name，年龄 age，班号 classid)
系 DEPARTMENT (系号 departmentid，系名 deptname)
试用SQL语言完成以下功能：
- 建表，在定义中要求声明：
- 每个表的主外码。
- deptname是唯一约束。
- 学生姓名不能为空。
- 插入如下数据：
DEPARTMENT(001,数学;002,计算机;003,化学;004,中文;005,经济;)
CLASS(101,软件,计算机,1995,20;
102,微电子,计算机,1996,30;
111,无机化学,化学,1995,29;
112,高分子化学,化学,1996,25;
121,统计数学,数学,1995,20;
131,现代语言,中文,1996,20;
141,国际贸易,经济,1997,30;
142,国际金融,经济,1996,14;
)
STUDENT(8101,张三,18,101;
8102,钱四,16,121;
8103,王玲,17,131;
8105,李飞,19,102;
8109,赵四,18,141;
8110,李可,20,142;
8201,张飞,18,111;
8302,周瑜,16,112;
8203,王亮,17,111;
8305,董庆,19,102;
8409,赵龙,18,101;
8510,李丽,20,142;
)
- 完成以下查询功能
- 找出所有姓李的学生。
- 列出所有开设超过1个专业的系的名字。
- 列出人数大于等于30的系的编号和名字。
- 学校又新增加了一个物理系，编号为006。
- 学生张三退学，请更新关系表。
-- 问题一：
CREATE TABLE DEPARTMENT(
departmentid VARCHAR(32) PRIMARY KEY,
deptname VARCHAR(32) UNIQUE NOT NULL) ENGINE = INNODB;
CREATE TABLE class(
classid INT PRIMARY KEY,
`subject` VARCHAR(32) NOT NULL DEFAULT '',
deptname VARCHAR(32),
enrolltime INT NOT NULL DEFAULT 2000,
num INT NOT NULL DEFAULT 0,
FOREIGN KEY (deptname) REFERENCES DEPARTMENT(deptname)) ENGINE = INNODB;
DROP TABLE class;
CREATE TABLE student1(
studentid INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
classid INT,
FOREIGN KEY (classid) REFERENCES class(classid)) ENGINE = INNODB;
-- 问题二：
INSERT INTO DEPARTMENT VALUES ('001','数学'),
('002','计算机'),('003','化学'),('004','中文'),('005','经济');
INSERT INTO class VALUES (101,'软件','计算机',1995,20);
INSERT INTO class VALUES (102,'微电子','计算机',1996,30);
INSERT INTO class VALUES (111,'无机化学','化学',1995,29);
INSERT INTO class VALUES (112,'高分子化学','化学',1996,25);
INSERT INTO class VALUES (121,'统计数学','数学',1995,20);
INSERT INTO class VALUES (131,'现代语言','中文',1996,20);
INSERT INTO class VALUES (141,'国际贸易','经济',1997,30);
INSERT INTO class VALUES (142,'国际金融','经济',1996,14);
INSERT INTO student1 VALUES (8101,'张三',18,101);
INSERT INTO student1 VALUES (8102,'钱四',16,121);
INSERT INTO student1 VALUES (8103,'王玲',17,131);
INSERT INTO student1 VALUES (8105,'李飞',19,102);
INSERT INTO student1 VALUES (8109,'赵四',18,141);
INSERT INTO student1 VALUES (8110,'李可',20,142);
INSERT INTO student1 VALUES (8201,'张飞',18,111);
INSERT INTO student1 VALUES (8302,'周瑜',16,112);
INSERT INTO student1 VALUES (8203,'王亮',17,111);
INSERT INTO student1 VALUES (8409,'赵龙',18,101);
INSERT INTO student1 VALUES (8510,'李丽',20,142);
INSERT INTO student1 VALUES (8305,'董庆',19,102);
SELECT * FROM DEPARTMENT;
SELECT * FROM class;
SELECT * FROM student1;
-- 问题三：
-- 1.
SELECT * FROM student1 WHERE `name` LIKE '李%';
-- 2.
SELECT COUNT(*) nums,deptname FROM class GROUP BY deptname HAVING nums>1;
-- 3.
SELECT tmp.*,DEPARTMENT.departmentid FROM DEPARTMENT,
(SELECT SUM(num) nums,deptname FROM class
GROUP BY deptname HAVING nums>=30) tmp
WHERE DEPARTMENT.deptname = tmp.deptname;
-- 问题四：
INSERT INTO DEPARTMENT VALUES ('006','物理系');
-- 问题五：
START TRANSACTION;
UPDATE class SET num = num - 1
WHERE classid = (
SELECT classid FROM student1 WHERE `name`='张三');
DELETE FROM student1 WHERE `name`='张三';
COMMIT;
SELECT * FROM student1;
SELECT * FROM class;
扫一扫
1413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
