mysql命令回顾

CREATE TABLE emp(
    empno        INT,
    ename        VARCHAR(50),
    job        VARCHAR(50), 
    mgr        INT, -- 上级领导编号
    hiredate    DATE,-- 入职日期
    sal        INT,
    comm        INT, -- 奖金
    deptno        INT  --  部门编号
) ;

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

-- 1、按员工编号升序排列不在10号部门工作的员工信息
SELECT *
FROM emp
WHERE deptno NOT IN(10)
ORDER BY empno ASC
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息,按年薪降序排列
SELECT *
FROM emp
WHERE NAME NOT LIKE '_A%' AND sal > 800
ORDER BY sal DESC
-- 3、求每个部门的平均薪水
SELECT AVG(sal)
FROM emp
GROUP BY deptno
-- 4、求各个部门的最高薪水
SELECT MAX(sal)
FROM emp
GROUP BY deptno
-- 5、求每个部门每个岗位的最高薪水
SELECT MAX(sal)
FROM emp
GROUP BY deptno,job
-- 6、求平均薪水大于2000的部门编号
SELECT deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000
-- 7、将部门平均薪水大于1500的部门编号列出来,按部门平均薪水降序排列
SELECT deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000
ORDER BY AVG(sal) DESC
-- 8、求最高薪水的员工信息
SELECT *
FROM emp
HAVING sal = MAX(sal)
-- 9、求大于平均薪水的员工信息
SELECT *
FROM emp
HAVING sal > AVG(sal)1

第一题:

  1. 创建学生students表信息如下:(创建表格+插入数据)

studentNonamesexhometownageclass_idcard
1王昭君北京201340322199001247654
2诸葛亮上海182340322199002242354
3张飞南京243340322199003247654
4白起安徽224340322199005247654
5大乔天津193340322199004247654
6孙尚香河北181340322199006247654
7百里玄策山西202340322199007247654
8小乔河南153NULL
9百里守约湖南211
10妲己广东262340322199607247654
11李白北京304340322199005267754
12孙膑新疆263340322199000297655

库表创建:

create table students (
  studentNo int primary key auto_increment not null,
  name varchar(10),
  sex varchar(10),
  hometown varchar(20),
  age tinyint(4),
  class_id int not null,
  card varchar(20)
);
​
insert into students (name,sex,hometown,age,class_id,card)values
('王昭君', '女', '北京', 20, 1, '340322199001247654'),
('诸葛亮', '男', '上海', 18, 2, '340322199002242354'),
('张飞', '男', '南京', 24, 3, '340322199003247654'),
('白起', '男', '安徽', 22, 4, '340322199005247654'),
('大乔', '女', '天津', 19, 3, '340322199004247654'),
('孙尚香', '女', '河北', 18, 1, '340322199006247654'),
('百里玄策', '男', '山西', 20, 2, '340322199007247654'),
('小乔', '女', '河南', 15, 3, null),
('百里守约', '男', '湖南', 21, 1, ''),
('妲己', '女', '广东', 26, 2, '340322199607247654'),
('李白', '男', '北京', 30, 4, '340322199005267754'),
('孙膑', '男', '新疆', 26, 3, '340322199000297655');

1.查询学生"百里守约"或”百里玄策”的基本信息

SELECT *
FROM students
WHERE name IN ('百里守约','百里玄策')

2.查询姓"张"学生的姓名,年龄,班级

SELCET name,age,class_id
FROM students
WHERE name LIKE '张%'

3.查询姓"百"并且家乡是"山西"的学生信息

SELECT *
FROM students
WHERE name LIKE '百%' AND hometown IS '山西'

4.查询姓"孙",但是家乡不是"河北"的学生信息

SELECT *
FROM students
WHERE name LIKE '孙%' AND hometown NOT IS '河北'

5.查询全部学生信息,并按照“性别”排序

SELECT *
FROM students
GROUP BY sex

6.查询1班学生中的最大年龄是多少

SELECT MAX(age)
FROM students
WHERE class_id = 1

7.统计2班男女生各有多少人

SELECT COUNT(*)
FROM students
WHERE class_id = 2
GROUP BY sex

8.统计年龄大于平均年龄的学生有多少个

SELECT COUNT(*)
FROM students
HAVING age > AVG(age)

1.部门表DEPT
1.1建表语句
CREATE TABLE dept(
    DEPTNO FLOAT(2) PRIMARY KEY comment '部门号',
    DNAME VARCHAR(14) comment '部门名称',
    LOC VARCHAR(13) comment '部门地址'
);
1.2数据
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'); 
2.员工表 EMP
2.1 建表语句
CREATE TABLE emp (
    EMPNO float(4)  PRIMARY KEY comment '员工编号',
    ENAME VARCHAR(10) comment '员工姓名',  --
    JOB VARCHAR(9) comment '员工职位',
    MGR float(4) comment '员工上级工号',
    HIREDATE DATE comment '生日',
    SAL float(7,2) comment '薪水',
    COMM float(7,2) comment '年终奖',
    DEPTNO float(2) comment '部门号' REFERENCES dept
);
2.2数据
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 7566,'1981-12-02', 3000, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
3.基础查询 (25分)
  • 3.1 查询年终奖不为空的所有员工的信息

    select *
    from emp
    where COMM NOT IS null
  • 3.2 查询部门名称为“RESEARCH”的所有员工的全部信息 (连接查询)

    select *
    from dept,emp
    where dept.DEPTNO = emp.DEPTNO AND DNAME = 'RESEARCH'
  • 3.3 查询每个部门的员工的平均薪资(连接查询)

    select AVG(SAL)
    from dept,emp
    where dept.DEPTNO = emp.DEPTNO
    GROUP BY emp.DEPTNO
4.复杂子查询 (25分)
  • 4.1 列出薪资高于公司平均薪资但是没有年终奖的员工的全部信息

    select *
    from emp
    where COMM IS null AND sal > (
    select AVG(SAL)
    from emp)
  • 4.2 列出薪资低于公司平均薪资的所有员工姓名、薪资。

    select ENAME,SAL
    from emp
    where sal < (
    select AVG(SAL)
    from emp)
  • 4.3 列出与“FORD”员工上级相同的所有员工姓名、工作职位名称 。 (5分)

    select ENAME,JOB
    from emp
    where MGR IN(
    select MGR
    from emp
    where ENAME = 'FORD')
  • 4.4 查询上级是“FORD”的所有员工的信息

    select *
    from emp
    where MGR IN(
    select EMPNO
    from emp
    where ENAME = 'FORD')
  • 14
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值