select * from employees;
select * from employees where salary = 10000;
select *from employeeswhere first_name like 'D%' or first_name like 'E%';
select * from employees where first_name like '%a%';
select * from employees where salary between 10000 and 20000;
select first_name || ' ' || last_name 姓名from employeeswhere salary between 10000 and 20000;
select first_name || ' ' || last_namefrom employeeswhere salary between 10000 and 20000;
select * from employees here salary between 10000 and 20000;
select *from employeeswhere first_name = 'Pat'unionselect *from employeeswhere first_name = 'Lex'unionselect *from employeeswhere first_name = 'Lisa';
select emp.*from employees emp, departments dep, locations locwhere emp.department_id = dep.department_id and dep.location_id = loc.location_id and loc.city = 'Seattle';
select first_name || last_name from employees where salary > 12000;
select first_name || ' ' || last_name from employees where salary > 12000;
select *from employeeswhere salary < 20000 and salary > 10000;
select * from employees where salary between 10000 and 20000;
select first_name, last_name, emp.salary, dep.department_namefrom employees emp, departments depwhere emp.department_id = dep.department_id and emp.salary > 10000;
select emp.*from employees emp, departments dep, locations loc, countries couwhere emp.department_id = dep.department_id and dep.location_id = loc.location_id and loc.country_id = cou.country_id and cou.country_name = 'United States of America';
select * from countries;select * from employees;select * from locations;select * from departments;
drop user lhq;create user lhq identified by lhq123456;grant dba to lhq;grant connect, resource to lhq;alter user sys identified by sysdba;grant sysdba to lhq;
select emp.first_name userName, man.first_name Managerfrom hr.employees emp, hr.employees manwhere emp.employee_id = man.manager_idorder by Manager asc;
select emp.first_name userName, dep.department_name Departmentfrom hr.departments depleft join hr.employees empon emp.department_id = dep.department_id;
select * from hr.countries;select * from hr.employees;select * from hr.locations;select * from hr.departments;select * from hr.jobs;
--1、查询所有员工的员工 姓名、部门名称、工作国家、工作地区、工作名称 select emp.first_name || ' ' || emp.last_name 姓名, dep.department_name 部门名称, loc.city 工作地区, cou.country_name 工作国家, job.job_title 工作名称from hr.employees empfull join hr.departments depon emp.department_id = dep.department_idfull join hr.locations locon dep.location_id = loc.location_idfull join hr.countries couon loc.country_id = cou.country_idfull join hr.jobs jobon emp.job_id = job.job_idwhere emp.first_name is not nullorder by 姓名;
select emp.first_name || ' ' || emp.last_name 姓名, dep.department_name 部门名称, loc.city 工作地区, cou.country_name 工作国家, job.job_title 工作名称from hr.employees empleft join hr.departments depon emp.department_id = dep.department_idleft join hr.locations locon dep.location_id = loc.location_idleft join hr.countries couon loc.country_id = cou.country_idleft join hr.jobs jobon emp.job_id = job.job_idorder by 姓名;--2、查询所有薪资大于一万的员工以及其领导和部门select emp.first_name || ' ' || emp.last_name 员工, man.first_name || ' ' || man.last_name 领导, dep.department_name 部门, emp.salary 薪水from hr.employees empleft join hr.employees manon emp.manager_id = man.employee_idleft join hr.departments depon emp.department_id = dep.department_idwhere emp.salary >= 10000order by 领导 asc;
select hr.emp.*, man.first_name 领导, dep.department_namefrom hr.employees empleft join hr.employees manon emp.manager_id = man.employee_idleft join hr.departments depon emp.department_id = dep.department_idwhere emp.salary >= 10000;--3、查询所有有员工的部门select emp.first_name || ' ' || emp.last_name 员工姓名, dep.department_name 部门from hr.employees empright join hr.departments depon dep.department_id = emp.department_idwhere emp.department_id is not nullorder by 部门;
select dep.department_id, dep.department_name, count(*)from hr.employees emp, hr.departments depwhere emp.department_id = dep.department_idgroup by dep.department_id, dep.department_name;
select dep.department_id, dep.department_name, count(emp.employee_id)from hr.departments depleft join hr.employees empon dep.department_id = emp.department_idgroup by dep.department_id, dep.department_namehaving count(emp.employee_id) > 0;--4、查询所有没有员工的部门select emp.first_name || ' ' || emp.last_name 员工姓名, dep.department_name 部门from hr.employees empright join hr.departments depon dep.department_id = emp.department_idwhere emp.first_name is nullorder by 部门 asc;
select dep.department_id, dep.department_name, count(emp.employee_id)from hr.departments depleft join hr.employees empon dep.department_id = emp.department_idgroup by dep.department_id, dep.department_namehaving count(emp.employee_id) = 0;--5、查询所有在98~2000年入职的员工select emp.first_name || ' ' || emp.last_name 姓名, emp.hire_date 入职日期from hr.employees empwhere emp.hire_date < to_date('2000-1-1', 'yyyy-mm-dd') and emp.hire_date > to_date('1998-1-1', 'yyyy-mm-dd')order by 入职日期 asc;
select *from hr.employeeswhere hire_date between '1-1月-1998' and '1-1月-2000';--6、查询所有部门以及部门领导 select emp.first_name || ' ' || emp.last_name 领导姓名, dep.department_name 部门名称from hr.employees empright join hr.departments depon dep.manager_id = emp.employee_idorder by 领导姓名 desc;
select *from hr.departments depleft join hr.employees empon dep.manager_id = emp.employee_id;--7、查询所有平均工资大于8000的部门select dep.department_name 部门, avg(emp.salary) 部门平均工资from hr.employees empright join hr.departments depon emp.department_id = dep.department_idgroup by dep.department_namehaving avg(emp.salary) >= 8000;--8、查询每个部门最高工资和最低工资是多少select dep.department_name 部门, max(emp.salary) 部门最高工资from hr.employees empright join hr.departments depon emp.department_id = dep.department_idgroup by dep.department_namehaving avg(emp.salary) > 0;--9、查询每个国家有多少个员工select cou.country_name 国家, count(*) 员工数from hr.employees empleft join hr.departments depon emp.department_id = dep.department_idleft join hr.locations locon dep.location_id = loc.location_idright join hr.countries couon loc.country_id = cou.country_idgroup by cou.country_nameorder by 国家;--10、查询每个部门总工资是多少select dep.department_name 部门, sum(emp.salary) 部门平均工资from hr.employees empright join hr.departments depon emp.department_id = dep.department_idgroup by dep.department_namehaving avg(emp.salary) > 0;--11、查询工资大于12000的员工姓名和工资select emp.first_name || ' ' || emp.last_name 员工姓名, emp.salary 工资from hr.employees empwhere salary > 12000;--12、查询员工号为176的员工的姓名和部门号select emp.first_name || ' ' || emp.last_name 员工姓名, emp.department_idfrom hr.employees empwhere emp.employee_id = 176;--13、选择工资不在5000到12000的员工的姓名和工资select emp.first_name || ' ' || emp.last_name 员工姓名, emp.salary 工资from hr.employees empwhere salary < 5000 or salary > 12000;--14、选择雇用时间在1908-02-01到1908-05-01之间的员工姓名，job_id和雇用时间select emp.first_name || ' ' || emp.last_name 员工姓名, emp.job_id 员工号, emp.hire_date 雇用时间from hr.employees empwhere emp.hire_date between '31-1月-1998' and '2-5月-1998';--15、选择在20、50号部门工作的员工姓名和部门号select emp.first_name || ' ' || emp.last_name 员工姓名, emp.department_idfrom hr.employees empwhere emp.department_id = 20 or emp.department_id = 50;--16、选择在1908年雇用的员工的姓名和雇用时间select emp.first_name || ' ' || emp.last_name 员工姓名, emp.hire_date 雇用时间from hr.employees empwhere emp.hire_date between '31-12月-1997' and '1-1月-1999';--17、选择公司中没有管理者的员工姓名及job_idselect emp.first_name || ' ' || emp.last_name 员工姓名, emp.job_id 员工号from hr.employees empwhere emp.manager_id is null;--18、查询员工姓名的第三个字母是a的员工姓名select emp.first_name || ' ' || emp.last_name 员工姓名from hr.employees empwhere emp.first_name like '__a%';--19、查询每一个工种的人数select emp.job_id 工作类别, count(*) 员工数from hr.employees empgroup by emp.job_idhaving count(*) > 0;--20、查询每一个领导手下的员工数量并排序select emp.manager_id 领导, count(*) 员工数from hr.employees empgroup by emp.manager_idhaving count(*) > 0order by 领导;
select * from hr.jobs;select * from hr.countries;select * from hr.employees;select * from hr.locations;select * from hr.departments;
create table customers(customers_id number(10) primary key, customers_account varchar2(20) not null unique, customers_password varchar2(18) not null, customers_name varchar2(30), customers_gender varchar2(6), customers_birth date not null, customers_phone number(11) not null, customers_mail varchar2(30), customers_address varchar2(18) not null);
create table orderdetail( orderdetail_id number(10) primary key, goods_serialnum number(10) not null unique, goods_num number(10) not null, goods_price number(10, 2) not null, customers_id number(10), foreign key(customers_id) references customers(customers_id));
create SEQUENCE test_sequence INCREMENT by 1 start with 10000 NOMAXVALUE NOCYCLE CACHE 10;
insert into customersvalues (test_sequence.nextval, 'Dream68686868', '8888-6666', 'ycr', 'female', to_date('21-08-2003', 'DD-MM-YYYY'), 13886868987, '123456@qq.mail', 'ChinaJiangSu');
select * from orderdetail;select * from customers;select test_sequence.Currval from dual;
drop table orderdetail;drop table customers;drop SEQUENCE test_sequence;
alter table orderdetail rename column orderdetail to orderdetail_id;
------------------------------------------------------------------------------------------------------
create table emp(emp_id number(10), first_name varchar2(20), last_name varchar2(20), salary number(10));
create table interest(interestId number(10) primary key, interest varchar2(20) unique);
create table users(userId number(10) primary key, USERNAME varchar2(20), PASSWORD varchar2(20), GENDER varchar2(20), CITY varchar2(20), TEXT varchar2(20));
create table users_interest(interestId number(10), userId number(10), primary key(interestId, userId), --联合主键，当联合主键不同时和其让数据相同时，那么就不算主键重复 foreign key(interestId) references interest(interestId), foreign key(userId) references users(userId));
create SEQUENCE user_users INCREMENT by 1 start with 10000 NOMAXVALUE NOCYCLE CACHE 10;
select * from interest;select * from users;select * from users_interest;select * from emp;
drop table emp;drop table users_interest;drop table users;drop table interest;drop SEQUENCE user_users;
insert into emp value (select emp.employee_id, emp.first_name, emp.last_name, emp.salary from hr.employees emp);
insert into usersvalues (user_users.nextval, 'lu', '123', 'male', 'nanjing', 'aaaa');insert into usersvalues (user_users.nextval, 'zhao', '123', 'female', 'guizhou', 'bbbb');insert into usersvalues (user_users.nextval, 'qian', '123', 'female', 'beijing', 'cccc');insert into usersvalues (user_users.nextval, 'sun', '123', 'male', 'changzhou', 'dddd');insert into usersvalues (user_users.nextval, 'li', '123', 'female', 'suzhou', 'eeee');
insert into interest values (1, '哭');insert into interest values (2, '玩');insert into interest values (3, '闹');insert into interest values (4, '蹦');insert into interest values (5, '跳');insert into interest values (6, '跑');insert into interest values (7, '睡');insert into interest values (8, '吃');commit;rollback;
drop user lu;create user lu identified by lu123456;grant sysdba to lu;grant dba to lu;
转载于:https://www.cnblogs.com/Dream-Lasting/p/4184381.html
