mysql的常见sql问题

女爷i 2022-04-12 07:24 245阅读 0赞

查找emp表

select \* from emp;

查找emp表的sal

select a.SAL from emp a;

查找emp表的ename

select a.ename from emp a;

emp表的sal\*10

select a.SAL\*10 from emp a;

emp表的sal的平均值

select avg(a.sal) from emp a;

emp表的sal的总和

select sum(a.sal) from emp a;

emp表的sal的max

select max(a.sal) from emp a;

emp表的sal的min

select min(a.sal) from emp a;

emp表中sal<1000的信息

select \* from emp where sal<1000;

ename中含有A的信息

select ename from emp where ename like’%A%’;

emp中ename不含有A的信息

select \* from emp where ename not like’%A%’;

查询系统时间

select sysdate from dual;

计算薪资小于5000的员工的工资总和

select sum(sal) from emp where sal<5000 ;

计算工资不高于平均工资的工资总和

select sum(sal) from emp where sal<(select avg(sal) from emp);

计算工资小于4000的员工的平均工资

select avg((select sum(sal) from emp where sal<4000)) from emp;

查询薪水低于100000的员工姓名和sal

select ename,sal from emp where sal<100000;

计算20号部门的员工的最高工资和最低工资

select max(sal),min(sal) from emp where deptno=20;

查询工资大于1000，并且是20号部门的员工的全部信息

select \* from emp where sal>1000 and deptno=20;

求最高工资的员工的员工姓名和部门名称

select ename,deptno,sal from emp where sal=(select max(sal) from emp);

将员工薪水小于5000且部门平均薪水大于1000的部门标号列出，按部门平均薪水降序排序

select deptno from emp where sal<5000 group by deptno having avg(sal) >1000;

select sal from emp order by sal desc;

Order by \*\*\* desc

查找表emp

select \* from emp ;

根据用户名“Smiths”查找他所在的部门

select deptno from emp where ename=‘SMITH’;

查询每个部门的最高工资的人员名称

select e.ename,e.deptno,e.sal from (select deptno as did ,max(sal) as m from emp group by deptno ) s,emp e,dept d where e.sal=s.m and s.did=e.deptno and d.deptno=e.deptno;

查询“Jones”之后第一个进入公司的人

select \* from emp where hiredate=(select min(hiredate) from emp where hiredate>(select hiredate from emp where ename=‘JONES’)) ;

5.查找工资最高的部门名称和工资最低的部门名称及工资

select d.dname,e.sal from emp e,dept d where e.deptno=d.deptno and sal=(select max(m) from (select deptno,max(sal) as m from emp e group by deptno) s)

union

select d.dname,e.sal from emp e,dept d where e.deptno=d.deptno and sal=(select min(m) from (select deptno,min(sal) as m from emp e group by deptno) s)

创建表

create table student(
    
    ```
       StudentId number (6),--学号
    
       LoginPwd varchar(20),--密码
    
       StudentName varchar(50),--姓名
    
       Sex char(2),--性别
    
       Gradeld Number(6),--所在年级
    
       Phone number(15),--联系电话
    
       Address varchar2(255),--现住址
    
       BornDate Date,--出生日期
    
       Emile varchar2(50)--电子邮件
    ```
    
    );

添加数据

insert into student values(1001,'123456','赵六','男',1507,120,'北京','23-5月-1995','@10422');
    
    insert into student values(1002,'123456','王五','女',1507,110,'北京','23-5月-1995','@10422');
    
    insert into student values(1003,'123456','张三','男',1507,120,'北京','23-5月-1995','@10422');
    
    insert into student values(1004,'123456','李四','女',1507,110,'北京','23-5月-1995','@10422');

提交

commit;

查询此表

select \* from student;

根据条件修改

update student set studentname=‘孙七’,loginpwd=‘666666’ where studentid=1001;

select \* from student;

根据条件删除

delete from student where studentid=1002;

select \* from student;

增加字段

ALTER TABLE 表名称 ADD(列名称 数据类型 \[DEFAULT 默认值\],列名称 数据类型 \[DEFAULT 默认值\],…)