找个SQL脚本，练练SQL语句

1、复制到小海豚，然后执行，刷新。

Can't connect to MySQL server on 'localhost' (10061)？？？

灵魂拷问：朋友，你的mysql服务启动了吗？

CREATE DATABASE scott default charset utf8;

USE scott;

CREATE TABLE dept(
    deptno      INT UNSIGNED PRIMARY KEY COMMENT '部门编号',
    dname       VARCHAR(15) COMMENT '部门名称',
    loc         VARCHAR(50) COMMENT '部门所在位置'
) COMMENT='部门表';

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');
commit;

CREATE TABLE emp(
    empno           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '雇员编号',
    ename           VARCHAR(15) COMMENT '雇员姓名',
    job             VARCHAR(10) COMMENT '雇员职位',
    mgr             INT UNSIGNED COMMENT '雇员对应的领导的编号',
    hiredate        DATE COMMENT '雇员的雇佣日期',
    sal             DECIMAL(7,2) COMMENT '雇员的基本工资',
    comm            DECIMAL(7,2) COMMENT '奖金',
    deptno          INT UNSIGNED COMMENT '所在部门',
    FOREIGN KEY(deptno) REFERENCES dept(deptno)
) COMMENT='雇员表';

INSERT INTO emp VALUES    (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES    (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO emp VALUES    (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO emp VALUES    (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO emp VALUES    (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO emp VALUES    (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO emp VALUES    (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO emp VALUES    (7788,'SCOTT','ANALYST',7566,'87-7-13',3000,NULL,20);
INSERT INTO emp VALUES    (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES    (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO emp VALUES    (7876,'ADAMS','CLERK',7788,'87-7-13',1100,NULL,20);
INSERT INTO emp VALUES    (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO emp VALUES    (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
commit;

CREATE TABLE salgrade(
    grade       INT UNSIGNED COMMENT '工资等级',
    losal       INT UNSIGNED COMMENT '此等级的最低工资',
    hisal       INT UNSIGNED COMMENT '此等级的最高工资'  
) COMMENT='工资等级表';

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
commit;

CREATE TABLE bonus(
    ename       VARCHAR(10) COMMENT '雇员姓名',
    job         VARCHAR(9) COMMENT '雇员职位',
    sal         DECIMAL(7,2) COMMENT '雇员工资',
    comm        DECIMAL(7,2) COMMENT '雇员资金'  
) COMMENT='工资表';

2、

• 1）查询SCOTT的信息

SELECT * FROM emp e WHERE e.`ename` = 'SCOTT';

• 2）查询20号部门的员工信息

SELECT * FROM emp e WHERE e.`deptno` = 20;

• 3）查询工资超过1500的员工名字和工资

SELECT e.`ename`,e.`sal` FROM emp e WHERE e.`sal` > 1500;

• 4）查询所有的职位,要求查询结果没有重复值

SELECT DISTINCT e.`job` FROM emp e ;
SELECT e.`job` FROM emp e GROUP BY e.`job` ;

• 5）查询工资在1600到3000之间的员工信息

SELECT * FROM emp e WHERE e.`sal` >= 1600 AND e.`sal` <= 3000;
SELECT * FROM emp e WHERE e.`sal` BETWEEN 1600 AND 3000;

• 6）查询工资是1600,3000的员工信息

SELECT * FROM emp e WHERE e.`sal`=1600 OR e.`sal` = 3000;
SELECT * FROM emp e WHERE e.`sal` IN(1600, 3000);

 

• 7） 查询名字是四个字符长度的员工名字

SELECT e.`ename` FROM emp e WHERE LENGTH(e.`ename`) = 4;
SELECT e.`ename` FROM emp e WHERE e.`ename` LIKE '____';

• 8）查询名字里包含M的员工名字

SELECT e.`ename` FROM emp e WHERE e.`ename` LIKE '%M%';

• 9）查询名字以S结尾的员工名字

SELECT e.`ename` FROM emp e WHERE e.`ename` LIKE '%S';

• 10）查询SMITH的主管的名字

# 方法1 子查询
SELECT a.`ename` FROM emp a WHERE a.`empno` = (SELECT e.`mgr` FROM emp e WHERE e.`ename` = 'SMITH');
# 方法2 表连接
SELECT b.`ename` FROM emp a, emp b WHERE a.`mgr`=b.`empno` AND a.`ename`='SMITH';

• 11）查询入职日期早于其主管的员工名字

SELECT a.`ename` FROM emp a, emp b WHERE a.`mgr`=b.`empno` AND a.`hiredate` < b.`hiredate`;

• 12）查询SMITH的上班地点

# 方法1 子查询
SELECT d.`loc` FROM dept d WHERE d.`deptno`=(SELECT e.`deptno` FROM emp e WHERE e.`ename`='SMITH');
# 方法2 表连接
SELECT d.`loc` FROM emp e, dept d WHERE e.`deptno`=d.`deptno` AND e.`ename`='SMITH';

 

• 13）查询20号部门的员工数目

SELECT COUNT(e.`empno`) FROM emp e WHERE e.`deptno`=20;

• 14）查询提成为空值的员工信息

SELECT * FROM emp e WHERE e.`comm` IS NULL;

• 15）查询提成不为空值的员工信息

SELECT * FROM emp e WHERE e.`comm` IS NOT NULL;
SELECT * FROM emp e WHERE NOT e.`comm` IS NULL;

• 16）查询工资高于30号部门平均值的员工信息

SELECT * FROM emp e WHERE e.`sal` > (SELECT AVG(a.`sal`) FROM emp a WHERE a.`deptno`=30);

• 17）查询所有员工的最高工资

SELECT MAX(e.`sal`) FROM emp e;

• 18）查询20号部门的最高工资

SELECT MAX(e.`sal`) FROM emp e WHERE e.`deptno`=20;

• 19）查询每个部门的最高工资,显示部门编号和最高工资

SELECT e.`deptno`,MAX(e.`sal`) FROM emp e GROUP BY e.`deptno`;

 

• 20）查询每个部门的最高工资,显示部门名字和最高工资

SELECT d.`dname`,MAX(e.`sal`) FROM emp e, dept d WHERE e.`deptno`=d.`deptno` GROUP BY d.`dname`;

• 21）查询工资高于本部门平均工资的员工信息

# 方法1
SELECT e.* FROM emp e, (SELECT a.`deptno`,AVG(a.`sal`) av FROM emp a GROUP BY a.`deptno`) b WHERE e.`deptno`=b.deptno AND e.`sal` > b.av;
# 方法2
SELECT * FROM emp e WHERE e.`sal` > (SELECT AVG(a.`sal`) FROM emp a WHERE a.`deptno`=e.`deptno`);

• 22）查询每个部门工资最高的员工信息

# 方法1
SELECT e.* FROM emp e, (SELECT b.deptno,MAX(b.sal) m FROM emp b GROUP BY b.deptno) a WHERE e.deptno=a.deptno AND e.sal=a.m;
# 方法2
SELECT * FROM emp e WHERE e.`sal` = (SELECT MAX(b.sal) FROM emp b WHERE b.deptno=e.deptno);