安装和建表

数据库安装链接：https://pan.baidu.com/s/1NpJEW-cqAk3gR6rJ7TjrrA?pwd=lhg5

建表文件：https://pan.baidu.com/s/1kYHwfRreQHRAXrivDKzoKA?pwd=3iry

数据库查询

#查看所有库
show databases;
#选库
use jobs;
#查看库里所有表；
show tables;
#查看表中的内容
select * from jobs;
#查询所有员工薪水+1000后的结果
select salary+1000 from employees;
#别名(如果别名有空格，要使用双引号)
select salary+1000 AS "salary new" from employees;
#虚拟表(伪表)dual 可省略
select 1+1 from DUAL;
#过滤(查询员工薪水大于6000的人)
select first_name,salary from employees where salary>6000;
#查询80号部门的员工
select first_name,department_id from employees where department_id=80;
#查询员工薪水在8000到10000之间
select first_name,salary from employees where salary BETWEEN 8000 AND 10000;
#查询30和50号部门中的员工
select first_name,department_id from employees 
#where department_id in(30,50)
where department_id=30 or department_id=50;
#模糊查询
#查询员工的姓名中包含f的
select first_name from employees where first_name like '%f%';
#查询员工的姓名中第二个字符为d的
select first_name from employees where first_name like '_d%';
#查询员工的姓名中第二个字符为_的
select first_name from employees where first_name like '_\_%';
#指定转义字符(ESCAPE)
select first_name from employees where first_name like '_s_%' ESCAPE 's';
#查询员工的姓名中包含a又包含b的
select * from employees where first_name like '%a%' and first_name like '%e%';
#排序
#查询所有员工的姓名，薪水，按照薪水降序
SELECT first_name,SUM(salary) from employees ORDER BY salary DESC;
#查询80号部门所有员工的姓名，薪水
select first_name,salary,department_id from employees where department_id=80 ORDER BY salary DESC;
#查询所有员工薪水+1000后大于10000的人
SELECT first_name,salary,salary+1000 newsalary FROM employees where salary+1000>10000
ORDER BY newsalary
#查询所有员工名，薪水，部门号 部门号相同的按照薪水排序
SELECT first_name,salary,department_id from employees ORDER BY department_id,salary DESC
#多表查询
#内连接 
#查询所有员工姓名和部门名称
SELECT a.first_name,b.department_name FROM employees a INNER JOIN departments b on a.department_id=b.department_id;
#左外连接(除了匹配的内容，还包括左表不匹配的内容)
#查询所有员工名和部门名称
select a.first_name,b.department_name FROM employees a LEFT JOIN departments b on a.department_id=b.department_id;
#右外连接（除了匹配的内容，还包括右表不匹配的内容）
#查询所有的部门名称和部门中所有员工
select b.department_name,a.first_name from employees a RIGHT JOIN
departments b on a.department_id=b.department_id
#外连接(mysql：左连接 UNION 右连接 hive:FULL JOIN)
#查询所有员工和部门名称（包括没有部门的员工和没有员工的部门）
SELECT a.first_name,b.department_name FROM employees a LEFT JOIN departments b on a.department_id=b.department_id UNION select a.first_name,b.department_name from employees a RIGHT JOIN
departments b on a.department_id=b.department_id
#查询员工姓名部门名称和部门所在城市名
select a.first_name,b.department_name,c.city from employees a join departments b on a.department_id=b.department_id join locations c on c.location_id=b.location_id
#``飘号 功能：区分关键字
#去重 DISTINCT
#查询有员工的部门
select DISTINCT department_id from employees where department_id is not null;

//练习一：过滤查询
# 1.查询工资大于12000的员工姓名和工资
select first_name,salary from employees where salary>12000;
# 2.查询员工号为176的员工的姓名和部门号
select first_name,department_id FROM employees where employee_id=176
# 3.选择工资不在5000到12000的员工的姓名和工资
select first_name,salary from employees where salary<5000 or salary>12000;
# 4.选择在20或50号部门工作的员工姓名和部门号
select first_name,department_id from employees where department_id in(20,50);
# 5.选择公司中没有管理者的员工姓名及job_id
select first_name,job_id from employees where manager_id is null;
#6.选择公司中有奖金的员工姓名，工资和奖金
select first_name,salary,salary*commission_pct as bonus from employees where commission_pct is not null;
# 7.选择员工姓名的第三个字母是a的员工姓名
select first_name from employees where first_name like '__a%';
# 8.选择姓名中有字母a和e的员工姓名
select first_name from employees where first_name like '%a%' and first_name like '%e%'

//练习二：多表查询
# 1.显示所有员工的姓名，部门号和部门名称。
select a.first_name,a.department_id,b.department_name from employees a join departments b on a.department_id=b.department_id
# 2.查询90号部门员工的job_id和90号部门的location_id
select a.job_id,a.first_name,b.location_id from employees a join departments b on a.department_id=b.department_id;
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
select a.last_name , b.department_name , c.location_id , c.city from employees a join departments b on a.department_id=b.department_id JOIN locations c ON b.location_id=c.location_id;
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 
SELECT a.last_name ,a.job_id , b.department_id , b.department_name FROM employees a join departments b on a.department_id=b.department_id join locations c on b.location_id=c.location_id where c.city='TORONTO';
#5.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
SELECT a.first_name,a.employee_id,b.first_name manager_name,b.employee_id from employees a join employees b on a.manager_id=b.employee_id;

#lower()将字符串内容变成小写
select LOWER(first_name) from employees;
#upper()将字符串内容变成大写
select UPPER(first_name) from employees;
#CONCAT()拼接字符串
select CONCAT(first_name,'-',last_name) as `name` from employees;
#SUBSTR()截取字符串（字符串,索引位置,长度）
select SUBSTR('sadadas',1,5) as n from;
#LENGTH()显示字符串长度
select LENGTH(first_name) from employees;
#instr()某个字符在字符串中第一次出现的位置
select INSTR(first_name,'a') from employees;
#lpad(salary,10,'*')向右对其，如果内容长度不够10，前面用*补齐
select LPAD(salary,10,'*') from employees;
#rpad(salary,10,'*')向右对其，如果内容长度不够10，后面用*补齐
select rpad(salary,10,'*') from employees;
#trim()去除字符串两端指定字符
select trim('h','hhhhhhhhshhhhhhhhqh')from dual
#REPLACE()将字符串的内容用指定内容替换
select REPLACE('wwwwasda','w','c')from DUAL
#round()四舍五入
select round(2.9,0) #-->3
#truncate()全部舍去
select truncate(2.9,0) #-->2

#通用函数
#日期时间 now()
select now(); #-->2023-06-15 20:27:35
#ifnull(字段名,默认值)如果字段的内容为null用默认值替换
#查询所有员工的工资(工资+奖金)
SELECT salary+salary*ifnull(commission_pct,0) from employees;
#case表达式
#查询部门号为10,20,30的员工信息，如是部门10 打印工资的1.1倍 如是部门20 打印1.2倍,如果是30 ，打印1.3倍
SELECT
first_name,
salary,
CASE
department_id 
WHEN 10 THEN
salary * 1.1 
WHEN 20 THEN
salary * 1.2 
WHEN 30 THEN
salary * 1.3 ELSE salary * 1 
END AS salary_new 
FROM
employees;
#查询所有员工的薪水如果大于10000显示高等，小于10000显示低等，等于10000显示中等
select first_name,salary,
case when salary>10000 then "高等"
when salary<10000 then "低等"
when salary=10000 then "中等" END as salary_grade
from employees;

#聚合函数
#avg平均值,sum和,max最大,min最小,count统计数量
#求员工数量,员工薪水最大值，最小值，平均值，和
select count(first_name),max(salary),MIN(salary),AVG(salary),SUM(salary)from employees;
#GROUP BY 分组 查询各部门平均薪水 并排序
select department_id,AVG(salary) avg_salary from employees where department_id is not null GROUP BY department_id ORDER BY avg_salary desc;
#查询10,20,30部门的平均薪水大于5000的部门
select department_id,AVG(salary) avg_salary from employees WHERE department_id in(10,20,30) GROUP BY department_id HAVING avg_salary>5000

#谁的工资比Abel高
#自连接
select a.first_name,a.salary from employees a join employees b on a.salary>b.salary and b.last_name='Abel';
#子查询
select first_name,salary from employees where salary>(select salary from employees where last_name='Abel');
#查询最低工资大于50号部门最低工资的部门id和最低工资
select department_id,MIN(salary) min_salary from employees
where department_id is not null
group by department_id
HAVING min_salary>(select MIN(salary) from employees
where department_id=50)

数据库操作

#创建库 if not EXISTS如果没有就创建
CREATE DATABASE if not EXISTS test;
# CHARACTER SET '编码集' 指定库的编码集 默认latin1
CREATE DATABASE if not EXISTS test CHARACTER SET 'gbk';
#查看库创建信息
SHOW CREATE DATABASE test;
#修改库编码集
ALTER DATABASE test CHARACTER SET 'gbk';
#删除库
DROP DATABASE if EXISTS test;
#查看表结构
DESC employees;
#查看表创建信息
SHOW CREATE TABLE employees;
#删除表
DROP TABLE IF EXISTS test;
#创建表
#①
CREATE TABLE IF NOT EXISTS test(
`name` VARCHAR(10),
`score` VARCHAR(10)
)CHARACTER SET 'gbk';
#②带数据
CREATE TABLE IF NOT EXISTS test as SELECT * from employees;
#③不带数据
CREATE TABLE IF NOT EXISTS test like employees;
#添加表字段
alter table test add COLUMN age INT; 
#删除表字段
ALTER table test DROP COLUMN age2;
#修改表字段
alter table test CHANGE COLUMN age age2 int;
#修改表字段类型
alter table test MODIFY COLUMN age age2 VARCHAR(10);
#修改表名
alter TABLE test rename to test2;
#清空表
truncate table test;

数据操作

#插入数据
insert into test(`name`,age) VALUES('杜甫',35);
#如果为全字段可以省略字段名
insert into test VALUES('王维',32);
#插入多行数据
insert into test VALUES('杜牧',31),('白居易',36);
#将查询的结果插入到表中
insert into test(`name`,age) select `name`,age from test;
#修改数据
update test set `name`='李商隐',age=33 where `name`='白居易';
#删除数据
delete from test where `name`='李商隐';

数据库事务

一组逻辑操作单元，使数据从一种状态变换到另一种状态

#事务开启--禁止自动提交
set autocommit=false;
delete from test;
#事务回滚
rollback;
#事务提交--提交就不能回滚
COMMIT;
#允许提交(一定要提交)
Set autocommit=true;

约束

#约束分类：列级:约束一列，表级:约束多列
#非空约束 只有列级
Not NULL
#唯一约束
UNIQUE
#主键 非空且唯一
primary KEY
#外键
FOREIGN KEY
#检查约束(Mysql 不支持)
CHECK
#默认值 只有列级
DEFAULT 
#创建表时添加约束
#列级
CREATE TABLE test(
id INT PRIMARY KEY,#主键
`name` VARCHAR(20) NOT NULL,#非空
sid INT UNIQUE,#唯一
age INT DEFAULT 18#默认值
);
INSERT INTO test(id,`name`,sid) VALUES(2,'杜甫',1200);
#表级
CREATE TABLE if not EXISTS test2(
id int auto_increment,
sid int,
`name` VARCHAR(20),
#约束 CONSTRAINT
CONSTRAINT test2_id_sid PRIMARY KEY(id,sid)
);
#auto_increment 自增
​

索引

b+树

当添加唯一，主键，外键约束时会自动创建索引