SQL 练习（MySQL）

SQL

--APS_VERSION 修改column字段长度
SELECT  *  FROM EXPORT_PRODUCTFULLFILLMENT2;
alter table EXPORT_PRODUCTFULLFILLMENT2 modify APS_VERSION VARCHAR2(60);
--ALTER TABLE table_name MODIFY column_name new_data_type(new_length);

练习sql1

select * from hr.employees e;-- hr.employees,hr.departments -- count(*) 总数
select * from hr.departments d;
--练习-计算每个员工的日薪  hr.employees的salary字段表示月薪的意思
select round(e.salary / 30) as "员工日薪" from hr.employees e order by salary desc;
--使用 字符串连接符 || （这就把姓和名连接在一起，并且显示在同一个字段中）  
select first_name || last_name as "姓名" from hr.employees e;
--在姓和名之间加上空格 ''
select first_name || ' ' || last_name as "姓名" from hr.employees;
--消除重复的行 distinct 总数107 消除重复的job_id号
select distinct(job_id) from hr.employees;--19条数据

--条件限定 in 
select * from hr.employees e where e.salary in(3000,6000);
--名字里倒数第三个字母是u的员工
--%表示任意匹配，有或者没有都可以
--_表示有，并且只有一个
select * from hr.employees where first_name like '%u__';
--常用的逻辑判断 与 或 非 and or not
select salary from hr.employees where salary between 3000 and 5000;
select salary from hr.employees where salary>= 3000 and salary<=5000;

--练习
--1查询所有员工的姓名（last_name+' '+first_name）,工资，年终奖金（工资的百分之八十 乘以 commission_pct 在加500）别名（年终奖）。
select last_name|| ' ' || first_name,
salary,
((salary * 0.8) * commission_pct + 500) as "年终奖"
from hr.employees;
--2查询所有有人员的部门编号，并且去掉重复行。
select distinct(department_id) as "部门编号"
from hr.employees e;
--3查询员工的姓名，工资，岗位(JOB_ID)，要求工资为2000~3000并且JOB_ID以‘ERK’结束
select 
 first_name || last_name,
 salary,
 JOB_ID
 from hr.employees e
where salary between 2000 and 3000 and JOB_ID like '%ERK';
--4查询所有有人员的岗位编号，要求岗位(JOB_ID)中包含‘L’同时岗位(JOB_ID)名称以‘N’或‘K’结束，去掉重复行。
select distinct(job_id) as "job_id"
from hr.employees e
where job_id like '%L%' and job_id like '%N' or job_id like '%K';

--1313212
select * 
from hr.employees e left join hr.departments d 
on e.department_id = d.department_id;

select * from hr.employees e;-- hr.employees,hr.departments -- count(*) 总数
select * from hr.departments d;
--1.查询部门编号大于等于50小于等于90的部门中 工资小于5000的员工的编号、部门编号和工资
select e.employee_id,
e.department_id,
e.salary
from hr.employees e
where e.department_id >= 50 and e.department_id <= 90
and salary < 5000;
--2.显示员工姓名加起来一共15个字符的员工
select (first_name || '' || last_name) as "员工姓名"
from hr.employees
where length(first_name|| '' ||last_name)=15;
--3.显示不带有“ R ”的员工的姓名
select (e.first_name || '' || e.last_name) as "员工姓名"
from hr.employees e
where (e.first_name || '' || e.last_name) not like '%R%';
--4.查询所有员工的部门的平均工资，要求显示部门编号，部门名，部门所在地（需要多表关联查询： employees, department, location）
--常见查询 数据不全
select count(*) as "总数",
avg(e.salary) as "平均工资",
d.department_id,
d.department_name,
l.street_address
from hr.employees e,hr.departments d,hr.locations l
where e.department_id = d.department_id and l.location_id = d.location_id
group by d.department_id,d.department_name,l.street_address;

--用关联查询 数据更全
select avg(e.salary), e.department_id,d.department_name,l.street_address  from hr.employees e
left join hr.departments d
on e.department_id = d.department_id
left join hr.locations l
on d.location_id = l.location_id
group by e.department_id ,d.department_name,l.street_address;

--子查询

练习sql2

--子查询
--统计所有月薪高于Bruce的人数（看情况 写子查询）
--1.查询出Bruce的月薪 2.查询出所有高于Bruce月薪人数 
select e.salary from hr.employees e 
where e.first_name like 'Bruce';

select count(*) as "总计" from hr.employees ee
where ee.salary > (
  select e.salary from hr.employees e 
  where e.first_name like 'Bruce'
)

--使用rownum进行 分页查询
--查出薪水最高的5-10条数据
select * from --3.
(
   select ee.salary,rownum r from --2. ****
  (
    select * from hr.employees e order by e.salary desc --1.
  )ee
)eee where r >= 5 and r <= 10;

 select ee.salary,rownum r from --2. ****
  (
    select * from hr.employees e order by e.salary desc --1.
  )ee
where ee.r >= 5 and ee.r <= 10;  
--select * from hr.employees where rownum <= 5;
--select rownum r, e1.* from e1 // rownum是oracle的伪列，rounum不能查询>=的数据，而且rownum默认就是从1开始排序的，所以不能直接写
--select rownum r ,e1.* from e1 where r > 5 and r< 10，因为这样r>5会报错的
--那么办呢，将select rownum r, e1.* from e1的结果当做一张表，这样就行了，而且多了一列rownum此时就可以按rownum来分页了。
/*
为什么不是
select rownum r, e1.* from
  (
   select e.* from hr.employees e  order by e.salary desc
  ) e1
 where e1.r >=5 and e1.r<=10

因为rownum得运行机制问题，所以呢，你当前查得是当前生成得e1的rownum,所以查看出来的第一条数据其给它附的id为1，
然后你后面有执行e1.r的判断，所以那个e1的第一条数据（id为1）不符合大于等于5小于等于10，
第一条不符合，那么它就会执行销毁，所以本来的第二条也会成为id为1，然后继续执行，
所以第二条也不符合，如果想使它符合，那么只能让e1.r大于0即可，否则其它条件都是不可以的，
如果你想让其大于1或者及以上，就必须多包涵一层，让其变成一个数据集，再从数据集里面取就可以取到（这就是为什么要多取一层的原因），
不然你执行这个语句，一条数据都没有而且还会报错
*/

/*
查询所在部门所在城市为'South San Francisco'的员工
查找和143在同一个部门和同一个岗位的比他工资高的员工。
查询总工资最多的部门，显示部门编号，部门名
*/
select * from hr.employees;
select * from hr.departments;
select * from hr.locations;
--查询所在部门所在城市为'South San Francisco'的员工
select first_name || '' || last_name as "员工"
from hr.employees e
left join hr.departments d on e.department_id = d.department_id
left join hr.locations l on l.location_id = d.location_id
where l.city like '%South San Francisco%';
--查找和143在同一个部门和同一个岗位的比他工资高的员工
/*
select first_name || '' || last_name as "员工"
from hr.employees e
where e.salary >
(
 --查出薪资 2600
 select ee.salary
 from hr.employees ee
 left join hr.departments d on ee.department_id = d.department_id
 and ee.manager_id = d.manager_id
 where ee.employee_id = '143'
)
*/
select *
  from hr.employees e2
 where e2.job_id =
       (select e.job_id from hr.employees e where e.employee_id = 143)
   and e2.salary >
       (select e.salary from hr.employees e where e.employee_id = 143)
--select e.salary from hr.employees e where first_name || '' || last_name like '%DanielFaviet%'

--查询总工资最多的部门，显示部门编号，部门名
select d.department_id,d.department_name 
from hr.departments d 
where d.department_id in
(
 --第三层求出外层条件 department_id **
  select xx.department_id from
  (
    --第二层求出rownum 所有的数据 ***
    select rownum r,aa.* from
      (
        --先求和 求出总工资 第一层
        select e.department_id,sum(e.salary) as totalSalary from hr.employees e 
        left join hr.departments dd on e.department_id = dd.department_id
        group by e.department_id 
        order by totalSalary desc  
      )aa
  )xx where xx.r = 1
)     
--order by d.department_id desc
select d.department_id, d.department_name
  from hr.departments d
 where d.department_id =
       (select x2.department_id
          from (select rownum r, x.*
                  from (select sum(e.salary) as sumSalary, e.department_id
                          from hr.employees e
                         group by e.department_id
                         order by sumSalary desc) x) x2
         where x2.r = 1)
/*
select sum(e.salary) from hr.employees e 
  left join hr.departments dd on e.department_id = dd.department_id
where dd.department_id = '30'
*/
/*
--SUBSTR(string，start， [length])
select first_name || '' || last_name as "姓名",substr('sundar',1,3) as "截取str"
 from hr.employees ee
*/

select * from 
(
  select sum_salary, e.department_id, d.department_name from
        (
            select sum(salary) sum_salary , department_id from hr.employees group by department_id
        ) e 
        left join hr.departments d on e.department_id = d.department_id order by sum_salary desc
) 
where rownum = 1

select *
from (select d.department_id,d.department_name,sum(e.salary) as salary
from hr.employees e,hr.departments d
where e.department_id=d.department_id
group by d.department_id,d.department_name
order by salary desc)
where rownum=1

练习sql3

create table hero(
  id number,
  name varchar2(30),
  hp number,
  mp number,
  damage number,
  armor number,
  speed number
)

select * from hero;
insert into hero(id,name,hp,mp,damage,armor,speed) values(1,'盖伦',1000,20,2000,2000,10);
insert into hero(id,name,hp,mp,damage,armor,speed) values(2,'石头人',1000,20,2000,2000,10);
insert into hero(name,hp,mp,damage,armor,speed) values('剑圣',1000,20,2000,2000,10);
commit;
--序列即sequence通常被用来作为id插入到表中
--创建sequence
create sequence hero_seq
  increment by 1 
  start with 1 
  maxvalue 9999999

--使用sequence 
select hero_seq.nextval from dual
--获取hero_seq的下一个值
select hero_seq.currval from dual
--获取hero_seq的当前值 

--作为id插入到表中
insert into hero (id,name,hp,mp,damage,armor,speed) values(hero_seq.nextval,'炸弹人',450,200,45,3,300);
--执行上述代码多次，再执行查询语句
select * from hero
--可以看到后面再增加的数据的id就是自动增加的

delete from hero;
truncate table hero;

--增加新的一列
alter table hero add(kill number)

--修改列
alter table hero modify(kill varchar2(255))

--删除列 管理员赋予权限
alter table hero drop column kill;

------------------------------------------------
/*
40为hr方案创建表“USERS”，要求有列：USER_ID,USER_NAME,BIRTHDATE,AGE,SALARY,photo,USER_COMMENT(个人履历)。

41为“USERS” 表添加一列 “DEPARTMENT_ID”;

42删除表“USERS”中的age列

43删除表“users”
*/
create table users(
  USER_ID number,
  USER_NAME varchar2(255),
  BIRTHDATE date,
  AGE number,
  SALARY number,
  photo varchar2(255),
  USER_COMMENT varchar2(255)
  --constraint uk_hero_photo unique
);
select * from users;
alter table users add(DEPARTMENT_ID number);
alter table users drop column age;
drop table users;

insert into users(USER_ID,USER_NAME,BIRTHDATE,AGE,SALARY,photo,USER_COMMENT)values(1,'kaka',TO_DATE ('2022-01-01','YYYY-MM-DD'),20,2800,1,2);
insert into users(USER_ID,USER_NAME,BIRTHDATE,AGE,SALARY,photo,USER_COMMENT)values(1,'kaka',TO_DATE ('2022-01-01','YYYY-MM-DD'),20,2800,1,2);

--给已经存在的表增加约束
alter table users add constraint pk_hero_ID primary key(user_ID);

alter table users drop constraint fk_killer_hero;

--外键约束
create table kill_record(
   id number,
   killerid number,
   killedid number,
   number_ number,
   constraint fk_killer_hero foreign key(killerid) references hero(id),
   constraint fk_killed_hero foreign key(killedid) references hero(id)
)

alter table kill_record add constraint pk_kill_record_ID primary key(id);
alter table kill_record add constraint uk_kill_killer unique(id);
alter table kill_record add constraint uk_kill_killed unique(id);

drop table kill_record;
select * from kill_record;
update hero set name = 'kakaka' where id = '16';
--创建视图
create view herosimpleView as(
  select id,name from hero
)

select * from herosimpleView;
update herosimpleView set name = 'kakaka111' where id = '17';
select * from hero;

-------------------------------------------------------------------
/*
为hr.users表的user_id添加主键约束。

为hr.users表的user_name添加非空和唯一约束。

创建一个表hr.department( department_id, department_name)

为hr.users表的DEPARTMENT_ID添加外键约束

删除表hr.users后重新创建它，并在一条SQL语句中一并添加上述的约束条件。
*/
select * from users for update;

drop table users;
alter table users add constraint pk_users_userId primary key(user_id);
alter table users drop constraint constraint_users_username;

ALTER TABLE users MODIFY (user_name NOT NULL); 

alter table users add constraint username_unique unique(user_name);

select * from departments;
create table departments(
  department_id number,
  department_name varchar2(255)
);

insert into departments(department_id,department_name)values(1,'IT');
alter table departments add constraint department_id_unique unique(department_id);



select * from users;
select * from departments for update;

alter table users add constraint fk_users_department foreign key (department_id) references departments(department_id)

练习sql4

alter user bigsword quota unlimited on users

[mysql系列教材 （一）- 安装mysql-server (how2j.cn)](