数据库基础

目录

2. DQL语言的学习

2.1 基础查询

2.2 条件查询

2.3 排序查询

2.4 常见函数

2.5 分组函数

2.6 分组查询

2.7 连接查询

2.8 子查询

2.9 分页查询

2.10union联合查询

---

数据库的好处

1. 持续化数据到本地
2. 可以实现结构化查询，方便管理

数据库的相关概念

1. DB: DateBase, 数据库。它保存了一系列有组织的数据。
2. DBMS(Datebase Management System) :数据库管理系统，数据库时通过DBMS创建的操作的容器。
3. SQL(Structure Query Language): 结构化查询语言，专门用来与数据库通信的语言。

MySQL服务器的登录和退出

启动和停止

1. 启动：net start mysql
2. 停止：net stop mysql

进入：mysql -h localhost -P 3306 -u root -p

退出：exit 或 ctrl + c

---

2. DQL语言的学习

# 查询语法以及执行顺序
select 查询列表    ⑦
from 表1 别名       ①
连接类型 join 表2   ②
on 连接条件         ③
where 筛选          ④
group by 分组列表   ⑤
having 筛选         ⑥
order by排序列表    ⑧
limit 起始条目索引，条目数;  ⑨

2.1 基础查询

use myemployees;
select last_name, first_name from employees;
select * from employees;

# 着重号 一般用于“关键词”作为字段

4. 查询常量值；
5. 查询表达式； select 100%99；
6. 查询函数 select version()；
7. 起别名 （AS / 空格）
	select 100%99 as 结果；
	select last_name 姓 from employees;
8. 去重 distinct
	select distinct(department_id) from employees;
9. "+"号的作用
	仅存在一个功能：就是运算符	
10. select concat(last_name,first_name) from employees;
 

2.2 条件查询

—————————————————————————条件查询————————————————
案例1：select * from employees where salary>12000;
案例2：select first_name,department_id from employees 
	   where department_id <> 90;
案例3：select 
			first_name,salary as s, commission_pct
       from employees
	   where s>=10000 and s<=20000;

select * from employees where last_name like '%a%';

select 
	salary,last_name from employees 
where 
	commission_pct is null 
AND 
	salary < 180000;
	
select *
from employees
where job_id <> 'IT'
or salary=12000;

desc departments;

select distinct location_id
from departments;

 

2.3 排序查询

select * from employees order by salary desc;

select * 
from employees
where department_id>=90
order by hiredate ASC;


select *, salary*(1+ifnull(commission_pct,0))*12 AS 年薪
from employees
order by 年薪 DESC; 

select last_name, salary 
from employees
order by length(last_name) DESC;


select *
from employees
order by salary ASC, employees_id DESC;


select last_name, department_id, salary*12*(1+ifnull(commission_pct,0)) AS 年薪
from employees 
order by 年薪 DESC, last_name ASC;


select last_name, salary
from employees
where salary < 8000
and salary > 17000
order by salary DeSC;

select *
from employees
where email like '%e%'
order by length(email) DESC, department_id ASC;

2.4 常见函数

 一个中文三个字节 一个英文1个字节
索引从1开始；

 # substr 截取指定索引的字符
 # instr 返回子串在主串中第一次出现时的索引，如果找不到为0

2.5 分组函数

 

2.7 连接查询

 

# 连接查询
SELECT last_name, employees.job_id, job_title
FROM employees , jobs
WHERE employees.job_id = jobs.job_id;

SELECT last_name, department_name
FROM employees AS e, departments AS d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;


SELECT department_name, city
FROM departments AS d, location AS l
WHERE d.location_id = l.location_id
AND city = '_o%';

# 查询每个城市的部门个数
SELECT COUNT(*) 个数, city
FROM departments AS d, location AS l 
WHERE d.location_id = l.location_id
GROUP BY city;

# 查询有奖金的每个部门的部门名和部门领导的编号
# 和该部门的最低工资
SELECT employees.department_name, d.manager_id, MIN(salary)
FROM employees AS e, departments AS d
WHERE e.department_id = d. department_id
AND commission_pct IS NOT NULL
GROUP BY e.department_name, d.manager_id  # 这个地方重点看一下

# 
SELECT job_title, COUNT(*) AS 个数
FROM jobs AS j,  employees AS e
WHERE j.job_id = e.job_id
GROUP BY job_title
ORDER BY 个数 DESC;

可以实现三表连接
查询员工名、部门名和所在的城市

SELECT last_name, department_name, city
FROM employees e, departments d, location l
WHERE e.department_id=d.department_id
AND d.location_id =l.location_id;

2.8 子查询

 

 # 子查询
 
 #标量子查询：结果只有一行一列
 ①查询最低工资的员工姓名和工资
 select last_name, salary
 from employees
 where salary= (
	select min(salary) from employees
)

#列子查询：结果集只有一列多行
②查询所有是领导的员工姓名
	查询所有员工的manager_id
	select manager_id from employees
	查询姓名，employee_id属于步骤一中的一个
	select last_name
	from employees
	where employee_id in(
		select manager_id from employees
	)

# 行子查询：结果集有一行多列
③查询员工编号最小并且工资最高的员工信息
select *
from employees
where employee_id=(
	select min(employee_id)
	from employees
	)
AND salary =(
	select max(salary)
	from employees
)

2.9 分页查询

2.10union联合查询