MySql_1
文章目录
一、数据库的引言
- 概念：数据库系统是用来组织，存储和管理数据的系统。可以对其存储的数据进行增加，删除，修改，查询等等一些列操作，并提供一系列保障数据完整性的事务机制和容灾备份的能力。
- 为什么要使用数据库？
- 内存存储数据：临时存储数据，数据不安全，一旦重启，数据将丢失，没有办法做到持久化保存数据。
- 持久化保存：将内存中的数据转移到硬盘，io操作麻烦 效率 ，数据一旦丢失将不能找回。
- 数据库存储数据
- 提高操作数据的效率
- 数据库存在数据类型，文件存储数据只能是字符串
- 存在权限校验的能力：身份校验等
- 数据库有容灾备份的能力，机器一旦宕机数据可以被找回。
…
- 数据库的分类
企业开发中使用到的数据库可以分为两大类：传统的关系型数据库和新兴的NoSql（Not ONLY SQl）数据库
| 关系型数据库 | 非关系型数据库（NoSql） | |
|---|---|---|
| 存储的数据结构 | 结构化的数据，以表为单位 | 半结构的数据（xml、json） 非结构化的数据（文档、文本、图片） |
| 事务 （保证数据完整性的机制） | 强事务 | 弱事务 |
| 性能 | 高并发的情况效率低 | 高并发的情况效率高 |
实战中对于数据库的选择
1. 关系型数据库：对事务要求比较高的数据，如：金融 银行
2. NoSql数据库：对事务要求比较低，对性能要求比较高：如聊天等
常见的数据库产品
1. 关系型数据：Oracle MySql SqlServer 等
2. NoSql数据库：Redis MongoDB HBase
二、数据库的安装和使用
-
数据库的工作模型 （客户端 - 服务器模型）
单机工作模型：软件在同一时间只能为一个用户提供服务，如果多个用户想要使用，必须在使用软件 的每一天计算机上安装软件
客户端服务器模式：软件在同一时刻可以为多个用户提供服务，用户只需要在自己的计算机上安装用于连接服务器的客户端软件即可。 -
安装数据库
- 复制my.ini文件 修改 mysql的安装目录 和 数据文件夹的位置
- 配置环境变量 path
- 启动cmd 以管理员身份运行
- 执行相关命令 详见笔记 如果出错 需要删除自动生成的数据文件夹
- 服务启动之后 需要修改密码
- 安装数次都失败，需要删除注册表
-
navicat的使用
三、数据库的一些概念
- 数据库
为了方便管理数据的表，MySql通过库(database)来管理，也就是说表存在于库（database）。
创建database
create database 库名;
显示所有的库
show databases;
删除库
drop database 库名;
使用|进入某一个库
use 库名;
- 表
表时数据库组织、存储数据的基本单位，表中保存数据以行和列保存；不同信息保存在不同的表中，如：学生相关的信息保存在学生表中，员工相关的信息保存在员工表中，部门相关的信息保存在部门表中。 - 字段（列）
一个字段用来描述某一个特征，如工号，名字 - 行
行是表中存储数据的基本单位，一行数据通常包含多个字段，向表中添加数据，至少添加一行。 - 主键（特殊的列）
用来唯一标识某一行数据的字段，如工号 ，要求该字段 非空+唯一 - 外键（特殊的列）
用来表示两张表关系的列。
四、查询的SQL（命令）
SQL：Strutctured Query Language 结构化的查询语言 用来访问和处理数据库的标准的计算机语言，核心的功能是提供了对数据库中数据的增删改查操作。
1.简单的查询
查询部分列
select 字段名1,字段名2,字段名3.....字段名n
from 表名;
示例
-- 查询员工表中的员工的编号，名字，邮箱，薪资
select employee_id,first_name,email,salary
from employees;
2.查询所有列
语法
-- 方式1
select * from 表名;
-- 方式2
select 字段名1,字段名2,字段名3.....字段名n
from 表名;
注意：方式2 要包含所有的字段名
-- 查询员工表中所有的列
select * from employees;
-- 第二种写法
select employee_id,first_name,last_name,email,phone_number,job_id,salary,commission_pct,manager_id,department_id,hiredate
from employees;
注意：以后要写第二种
1. 第一种方式可读性不好
2. 效率低 在sql优化中不推荐写*
3.数学运算
select 数学列名+数字,数学列名-数字,数学列名*数字,数学列名/数字,数学列名%数字
from 表名;
-- 查询员工的工资，并基于工资运算 +100 -100 年薪 日薪
select salary,salary+100,salary-100,salary*12,salary/30
from employees;
4.别名
select 字段名1 as 别名1,字段名2 as 别名2....
from 表名;
-- 查询员工的月薪，年薪和日薪 并取别名
select salary as "月薪", salary*12 as "年薪",salary/30 as "日薪"
from employees;
select salary "月薪", salary*12 "年薪",salary/30 "日薪"
from employees;
注意：别名可以使用双引号，单引号或者不加引号；as关键字可以省略。
5. 去重
select distinct 字段名
from 表名;
select distinct job_id,department_id
from employees;
6.case when
case
when 条件1 then 结果1
when 条件2 then 结果2
....
else 其他结果
end 显示的列名
类似于java中的if-else if-else
-- 注意：then后的显示的内容必须加上引号，单引号或者双引号都可以，但是不能不加引号，会报错。
-- 查询薪资，并且对薪资进行等级判定
select salary,
case
when salary >= 12000 then '高薪'
when salary >= 10000 then '中等薪资'
when salary >= 8000 then '一般'
else ' 垃圾'
end 薪资等级
from employees;
7.查询表详情
describe 表名;
-- 简写方式
desc 表名；
示例
describe employees;
desc employees;
单条件查询
select 字段名....
from 表名
where 条件；
-- 条件 比较运算符 > < >= <= != =
-- 查询工资大于8000的员工信息(员工的编号 名字 工资)
select employee_id,first_name,salary
from employees
where salary = 8000;
-- 查询名字为Steven的员工的所有信息
select *
from employees
where binary first_name = 'Steven';
注意：如果筛选条件为字符串，则不区分大小写，如果想要区分大小写，则可以在字段名前加上binary
多条件查询
语法：使用关键字and | or
and：连接条件 所有的条件都必须满足
or：连接条件 只要有一个条件满足即可
-- 1. 查询工资大于8000 并且工资小于12000的员工信息
select employee_id,first_name,salary
from employees
where salary > 8000 and salary < 12000;
-- 2. 查询工资小于5000 或者 工资大于15000的员工信息
select employee_id,first_name,salary
from employees
where salary < 5000 or salary > 15000;
区间查询
select 字段....
from 表名
where 字段名 [not] between 小值 and 大值;
-- 查询在某个范围内的数据 使用between 小值 and 大值， 是一个闭区间
-- 不在某个范围之内的数据 使用 not between 小值 and 大值
-- 1. 查询工资大于等于8000 并且工资小于等于12000的员工信息
select employee_id,first_name,salary
from employees
where salary >= 8000 and salary <= 12000;
select employee_id,first_name,salary
from employees
where salary between 8000 and 12000;
-- 2. 查询工资不在8000~12000的员工信息
select employee_id,first_name,salary
from employees
where salary < 8000 or salary > 12000;
select employee_id,first_name,salary
from employees
where salary not between 8000 and 12000;
枚举查询
语法：列名 [not] in （值1，值2，值3…值n）
-- 查询60 90 100号部门的员工信息
select *
from employees
where department_id = 60 or department_id = 90 or department_id = 100;
select *
from employees
where department_id in (60,90,100);
-- 查询不是60 90 100号部门的员工信息
select *
from employees
where department_id not in (60,90,100);
空值查询
语法: 字段名 is [not] null
注意：空值判断不能使用等号
-- 查询没有提成的员工信息
select *
from employees
where commission_pct is null;
-- 查询有提成的员工信息
select *
from employees
where commission_pct is not null;
模糊查询
由字符串和通配符组成的查询条件
select *
from 表名
where 字段名 like '匹配模式';
匹配模式：
- %：表示n个字符
- _：表示1个字符
以张开头 ： '张%' 张三 张三三 张三三三 张 都可以匹配 李三 不能
以张开头并且有两个字符：'张_' 张1 张2 可以匹配 张三三 张三三三 张 都不可以匹配
手机号必须11位且以186开头： '186________'
-- 1. 查询first_name以A开头的员工信息
select *
from employees
where first_name like 'A%';
-- 2. 查询first_name包含a的员工信息
select *
from employees
where first_name like '%a%';
-- 3. 查询first_name的第三位为a的员工信息
select *
from employees
where first_name like '__a%';
-- 4. 查询first_name的倒数第三位为a且长度为8的员工信息
select *
from employees
where first_name like '_____a__';
排序
select 字段名
from 表名
where 条件
order by 字段名1 [asc(默认的 升序)|desc(降序)],字段名2 [asc(默认的 升序)|desc(降序)];
-- 排序效果：如果是多个字段进行排序，那么先按第一个字段的排序进行排序，如果第一个字段的值相等，再按第二个字段进行排序。
单列排序
-- 根据薪资进行排序
select *
from employees
order by salary desc;
-- 查询工资大于8000，并且按照工资降序的方式排列
select *
from employees
where salary > 8000
order by salary desc;
多列排序
```sql
-- 先按工资降序排列，然后工资相等按工号升序排列
select *
from employees
order by salary desc,employee_id;
-- 思考 如下的sql语句的执行结果
select *
from employees
order by salary asc,employee_id desc;
-- 1. 按照工资降序排列，如果工资相等再按照员工编号降序排列
-- 2. 按照工资升序排列，如果工资相等再按照员工编号降序排列 √
-- 3. 报错
函数
- 单行函数
作用于表中的每一行数据，表中有多少行数据，就可以得到多少个结果。
concat(列名,…)：用于拼接多列的值
-- 将名字和姓拼接到一块
select concat(first_name,last_name) 名字 from employees;
select concat(first_name,'-',last_name) 名字 from employees;
注意：中间拼接的字符必须加上引号mod(m,n)
等同于数学运算中的%，取余数，注意：标准的sql不支持直接使用%
select mod(5,2) from employees;
dual：哑表， 在数据库中不显示出来，一行一列的表，只是为了保证sql语句的完整性的
select mod(5,2) from dual;
注意：mysql中可以省略 from dual
在mysql数据库中可以直接使用%
length(数据|字段)
获取数据或者字段的长度
-- 获取employees表中first_name的长度
select first_name,length(first_name) from employees;
-- 获取first_name长度为6的员工的信息
select * from employees where length(first_name) = 6;
select * from employees where first_name like '______';
now()|sysdate()
用于获取当前的系统时间
select now(),sysdate() from employees;
select now(),sysdate() from dual;
select now(),sysdate();
str_to_date(str,format)
将字符串按照某一个格式转换成日期类型的数据
日期格式：
%Y：代表的4位的年份
%y：代表的2位的年份
%m：代表月份，格式（01~12）
%c：代表月份，格式（1~12）
%d：代表月份中的天数，格式为（01~31）
%e：代表月份中的天数，格式为（1~31）
%H：代表小时 格式为（00~23）
%k：代表小时 格式为（0~23）
%i：代表分钟 格式为（00~59）
%s：代表秒 格式为（00~59）
%w：代表星期 格式为（0~6 星期天：0 星期六：6）
--1 将'2022-04-14 17:20:50' 字符串转换成相应的日期格式
select str_to_date('2022-04-14 17:20:50','%Y-%m-%d %H:%i:%s') from dual;
date_format(date,format)
将日期或者时间数据转换成指定类型的字符串
-- 将系统时间按照 年/月/日 时:分:秒显示出来
select date_format(now(),'%Y/%m/%d %H:%i:%s');
综合案例
-- 获取当前系统时间的年份
select date_format(now(),'%Y');
select date_format(now(),'%m');
-- 查询1998年入职的员工的详细信息
select * from employees where hiredate like '%98%';
select * from employees where date_format(hiredate,'%Y') = 1998;
-- 查询3月份入职的员工信息
select * from employees where date_format(hiredate,'%m') = 3;
-- 查询当月入职的员工信息
select * from employees where date_format(hiredate,'%m') = date_format(now(),'%m');
-- 查询2008年8月8日是星期几
select date_format(str_to_date('2008-08-08','%Y-%m-%d'),'%w');
-- 查询自己生日的星期几
-
组函数
作用于一组（多行）数据，每一组数据都可以得到一个结果，注意：默认在没有分组的情况，将一张表中的数据看作一组。
sum(列)：求一组数据的和
avg(列)：求一组数据的平均值
max(列)：求一组数据的最大值
min(列)：求一组数据的最小值
-- 求employees表中工资总和，平均工资，最大工资，最小工资
select sum(salary),avg(salary),max(salary),min(salary) from employees;
count()：查询结果中非null值的统计
- count(*)
- count(字段)：
-- 统计员工表的员工数量
select count(*) from employees;
select count(employee_id) from employees;
-- 统计员工表中有提成的员工的人数
select count(commission_pct) from employees; -- 35
select count(commission_pct) from employees where commission_pct is not null; -- 35
select count(employee_id) from employees where commission_pct is not null; -- 35
-- 统计员工表中没有提成的员工的人数
select count(employee_id) from employees where commission_pct is null;
select count(*) from employees where commission_pct is null;
count(commission_pct)
遍历表中的所有数据 并与 null 进行判断
for(Employee e:es){
if(e.getCommissionPct() != null)count++;
}
思考：以下sql语句的执行结果
select count(1) from employees; -- 107
for(Employee e:es){
if(1 != null)count++;
}
select count(0) from employees; -- 107
for(Employee e:es){
if(0 != null)count++;
}
select count(null) from employees; -- 0
for(Employee e:es){
if(null != null)count++;
}
MySql_2
一、分组
根据某一个分组依据（字段、函数）对表中的数据进行分组，值相同的为一组，值不同的不再一组中，然后以组为单位进行数据的处理。
- 简单分组
语法
select ...
from 表名
group by 分组的依据
示例
-- 查询各部门的员工的最高工资
-- 1. 确定分组依据 部门编号 department_id
-- 2. 求最高工资 max(salary)
select department_id,max(salary)
from employees
group by department_id;
- 带有条件的查询
语法
select ....
from 表名
where 条件
group by 分组依据
示例
-- 查询60,90,100号部门的员工的平均工资
-- 1. 确定数据来源 from employees
-- 2. 筛选条件 where department_id in (60,90,100)
-- 3. 分组 group by department_id
-- 4. 求平均工资 avg(salary)
select department_id,avg(salary)
from employees
where department_id in (60,90,100)
group by department_id;
【思考】如果想在select后添加一个employee_id，是否可行？
select employee_id,department_id,avg(salary)
from employees
where department_id in (60,90,100)
group by department_id;
-- 结果报错，不是group by后出现的字段或表达式； 分析结果也是不符合逻辑的
select employee_id,department_id,max(salary)
from employees
group by department_id;
-- 依然报错 不是group by后出现的字段或者表达式；分析结果也不合逻辑的 假如一组内有三个工资相同且都是最高工资 显示哪一个呢？
-
group by的使用规则（标准sql）
- 只有出现在group by后的字段才能写在select后
- 如果group by后没有出现的字段必须配合组函数可以出现在select后
- 在group by后出现的单行函数，可以出现select后，但是要求必须完全一致（包括参数）
示例
-- 统计1998年各个月份入职的员工人数
-- 1. 确定来源表 from employees
-- 2. 筛选条件 where date_format(hiredate,'%Y') = 1998
-- 3. 分组依据 group by date_format(hiredate,'%m')
-- 4. 统计人数 count(*)|count(employee_id)
select date_format(hiredate,'%Y-%m'),count(employee_id)
from employees
where date_format(hiredate,'%Y') = 1998
group by date_format(hiredate,'%Y-%m');
案例
-- 统计员工表中各部门的部门编号和人数（只显示人数大于2的部门编号和人数）
-- 1. 确定数据来源 from employees
-- 2. 根据部门进行分组 group by department_id
-- 3. 使用组函数统计人数 count(employee_id)
-- 4. 条件筛选 显示大于2的 where count(employee_id) > 2
select department_id,count(employee_id)
from employees
where count(employee_id) > 2
group by department_id;
--
select date_format(hiredate,'%Y-%m'),count(employee_id)
from employees
where date_format(hiredate,'%Y') = 1998 and count(*) > 3
group by date_format(hiredate,'%Y-%m');
注意：where子句后不能使用组函数，解决此问题需要使用having
二、having子句
在分组后执行的条件判断
语法
select ....
from ....
group by ....
having 条件
示例
-- 查询平均工资大于等于8000的部门编号
-- 1. 确定表来源 employees
-- 2. 分组 group by department_id
-- 3. 对分组后的数据进行过滤 having avg(salary) >= 8000
-- 4. 求平均工资 avg(salary)
select department_id,avg(salary)
from employees
group by department_id
having avg(salary) >= 8000;
having和where的区别：
where在group by之前执行，having在group by后执行
如果组函数作为筛选条件，选择having
当既可以使用where也可以使用having实现时，优先选择where，where效率高
示例
-- 修改错误
select department_id,count(employee_id)
from employees
group by department_id
having count(employee_id) > 2;
-- 修改错误
select date_format(hiredate,'%Y-%m'),count(employee_id)
from employees
where date_format(hiredate,'%Y') = 1998
group by date_format(hiredate,'%Y-%m')
having count(*) > 5;
示例
-- 查询60,90,100号部门的平均工资
-- 1 确定数据来源 from employees
-- 2. 分组 group by department_id
-- 3. 筛选出60，,9,100号部门 having deparment_id in (60,90,100)
-- 4. 展示部门编号和平均工资（组函数）
select department_id,avg(salary)
from employees
group by department_id
having department_id in (60,90,100);
-- 1. 确定数据来源 from employees
-- 2. 找出60，90,100号部门的员工 where department_id in (60,90,100)
-- 3. 分组 group by department_id
-- 4. 显示数据
select department_id,avg(salary)
from employees
where department_id in (60,90,100)
group by department_id;
三、limit关键字
限制查询结果的条目数，通常用于数据分页
语法
select ...
from ....
limit offset,rows;
-- offset：起始的下标 下标从0开始 rows：显式的条数
示例
-- 获取employees前10条数据
select *
from employees
limit 0,10;
-- 显示员工编号110~119条数据
select *
from employees
limit 10,10;
-- 注意 当需要从下标0开始显示时，可以省略0
select *
from employees
limit 10;
-- 注意：当数据不够显示条数时不会报错，只会显示剩余的数据
-- 注意：limit可以和原先讲的任意一个关键字连用，但是limit要放在最后
-- 显示工资大于10000 的第0条到3条数据
select *
from employees
where salary > 10000
limit 20;
四、查询sql关键字的执行顺序
select | from |where| group by | having |order by| limit
| 语法顺序 | 执行顺序 | 代表含义 |
|---|---|---|
| select | 5 | 显示的数据 |
| from | 1 | 确定数据来源 |
| where | 2 | 对原始数据进行过滤 |
| group by | 3 | 分组 |
| having | 4 | 对分组后的进行再次的过滤 |
| order by | 6 | 排序 |
| limit | 7 | 分页展示数据 |
五、子查询
当一个查询的sql的条件需要使用到另一个查询sql的结果时，需要在一个sql语句中嵌套另一个sql语句。
- where子查询
- where单值子查询
被嵌套的sql语句的查询结果是一行一列的结果
- where单值子查询
-- 查询员工编号为150号员工的所在的部门信息
select * from departments;
-- 1. 根据员工编号查询部门编号
select department_id from employees where employee_id = 150; -- 80
-- 2. 查询80号部门所在的部门信息
select * from departments where department_id = 80;
-- 3 整合
select * from departments where department_id = (select department_id from employees where employee_id = 150);
1. where多值子查询
被嵌套的sql语句的查询结果是多行一列的结果
-- 查询last_name为"King"的员工所在的部门信息
-- 1. 查询last_name为"King"的员工所在的部门信息
select department_id from employees where last_name = 'King'; -- 80 90
-- 2. 查询80，90号部门的部门信息
select * from departments where department_id in (80,90);
-- 3. 整合
select * from departments where department_id in (select department_id from employees where last_name = 'King');
-
from子查询
被嵌套的sql语句是多行多行的结果
select employee_id,first_name,last_name,phone_number,email,department_id from employees; --不同的角色 分配不同的权限
select employee_id,first_name,last_name,phone_number,email,department_id from employees; -- t1表
select * from (select employee_id,first_name,last_name,phone_number,email,department_id from employees) t1;
-- 一般用于对数据的预处理
-- 对员工进行降序排列，然后统计出薪资排名前10的员工的总薪资
select * from employees order by salary desc limit 10;
select sum(salary) from (select * from employees order by salary desc limit 10) t1;
六、表连接
当查询的数据需要从多张表中获取时，需要将多张表的数据连接起来进行查询。表与表之间的联系体现在外键上。
-
外键：体现两张表之间联系的列
外键的要求：- 特殊的列，用来记录两张表之间联系的列
- 外键一般来源于另一张表的主键|唯一列
- 外键可以充当表连接的连接条件
-
表连接的分类
- 内连接
- 外连接
- 自连接
-
表连接之后查询结果的行数由表连接的方式控制，列数由两张表的列数决定。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YxDsM1Vx-1656830712773)(https://secure2.wostatic.cn/static/iRWkw9m2S2BTGY8XGRGUKx/smbgvraq5h.jpg)]
-
内连接
语法
select 字段名1，字段名2....字段名n
from 表1
inner join 表2
on 连接条件;
-- 注意：inner可以省略
-- 如果某一个字段只在一张表中存在，可以省略表别名.,直接使用字段名，如果某一个字段在两张表都存在，则必须区分是哪一张表的字段。
示例
-- 查询员工表和部门表的信息
select e.*,d.*
from employees e
inner join departments d
on e.department_id = d.department_id;
select employee_id,first_name,e.email,salary,department_id,department_name
from employees e
inner join departments d
on e.department_id = d.department_id;
特点：两张表中满足连接条件的数据展示出来，不满足连接条件的数据不展示出来。
- 外连接
- 左外连接
语法
- 左外连接
select 字段名......
from 表1 表别名
left outer join 表2
on 连接条件;
注意：outer可以省略
示例
-- 查询员工表和部门表的相关信息
select e.*,d.*
from employees e
left outer join departments d
on e.department_id = d.department_id;
select e.*,d.*
from employees e
left join departments d
on e.department_id = d.department_id;
select e.*,d.*
from departments d
left outer join employees e
on e.department_id = d.department_id;
特点：左表中所有的数据全部展示出来，右表中通过连接条件能够连接上的数据展示出来，不能够连接的数据不展示出来。
1. 右外连接
语法
select 字段名......
from 表1 表别名
right outer join 表2
on 连接条件;
-- 注意：outer可以省略
示例
-- 查询员工表和部门表的相关信息
select e.*,d.*
from employees e
right outer join departments d
on e.department_id = d.department_id;
select e.*,d.*
from employees e
right join departments d
on e.department_id = d.department_id;
特点：右表中所有的数据展示处理，左表中通过连接条件连接数据展示处理，不能通过连接条件连接的数据不展示出来。
- 自连接
是特殊的表连接，参与表连接的两张表为同一张表，可以选择任何一种表连接的方式。
案例
-- 查询员工的信息（工号 名字 直接领导的工号 直接领导的名字）
select e1.employee_id,e1.first_name,e1.manager_id,e2.first_name
from employees e1
left join employees e2
on e1.manager_id = e2.employee_id;
-- 查询工资相同的员工信息（第一人的工号，名字，薪资，第二人的工号，名字，薪资）
select e1.employee_id,e1.first_name,e1.salary,e2.employee_id,e2.first_name,e2.salary
from employees e1
inner join employees e2
on e1.salary = e2.salary
where e1.employee_id < e2.employee_id
order by e1.salary desc;
-- 查询本部门最高薪资的员工信息
-- 1. 按部门分组，求出部门的最高薪资
select department_id,max(salary) maxSalary from employees group by department_id; -- t1
-- 2. 使用employees和t1表做表连接 连接条件：工资相等
select t1.department_id,e.*
from t1
left join employees e
on t1.maxSalary = e.salary;
-- 3. 整合
select t1.department_id,e.*
from (select department_id,max(salary) maxSalary from employees group by department_id) t1
left join employees e
on t1.maxSalary = e.salary and t1.department_id = e.department_id;
- 多表连接
select ....
from 表1 别名
left join 表2 别名
on 连接条件
left join 表3 别名
on 连接条件
示例
-- 查询员工的信息，部门的信息，地址的信息
select e.*,d.*,l.*
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id;
MySql_Day3
一、SQL的分类
-
DQL（Data Query Language）
数据查询语言 对数据库的数据进行查询操作的sql，select
-
DCL（Data Controller Language） （了解）
数据控制语言 对数据用户的设定以及权限更改的SQL grank(授权)|revoke(撤销)
DBA（数据库管理员的权限） -
DDL（Data Define Language）
数据定义语言 执行表、视图、索引的创建（create）、销毁（drop）、修改（alter）的语言
-
DML（Data Manipulation Language）
数据操纵语言 执行对数据的增加（insert）|删除（delete）|修改（update）
-
TCL（Transaction Controller Lamguage）
事务控制语言：保证数据完整性的一种机制 commit(提交)|rollback(回滚)
二、DDL
1. 表的创建和销毁
- 表的创建
create table [if not exists] table_name(
字段名 数据类型 [默认值] [约束]，
字段名 数据类型 [默认值] [约束]，
....
字段名 数据类型 [默认值] [约束]
);
-- 注意
1. []中的内容可以省略
2. 字段名之间使用逗号隔开，最后一个字段后不加逗号
3. 默认值：当添加时如果不指定值，那么存在默认值
4. 约束：对于此字段的要求， 如不能为空，不能重复等等。
5. 当表存在时再创建此表，会报错，但是如果添加if not exists，不会报错，只会报一个警告，不会创建此表。
- 表名和字段名
1. 合法的标识符，由字母，数字，下划线组成，且数字不能开头
2. 表名一般以t_开头，表名或者字段名如果是多个单词建议使用 _ 分隔
3. 表名和字段名不区分大小写
4. 不能使用关键字 如insert group select等
- 数据类型
| 数据类型 | 解释 | 注意事项 |
|---|---|---|
| ****int、integer | 4个字节整数 | |
| bigint | 8个字节整数 | |
| float | 4个字节的小数 | |
| ****double | 8个字节的小数 | |
| ****decimal(m,d) | 小数类型 | m代表总长度，d代表小数的长度 |
| ****char(n) | 定长字符串 | 不管存储的数据的长度是多少，都会分配固定空间 |
| ****varchar(n) | 变长字符串 | 根据存入的字符串分配空间 |
| text | 大文本文件 | 能保存4G大小的文字 |
| ****enum | 枚举类型 | 在指定的范围内取值，比如性别enum(‘男’,‘女’) |
| ****date | 日期类型 | 包含年月日 |
| time | 日期类型 | 包含时分秒 |
| ****datetime | 日期类型 | 包含的年月日 时分秒 |
| timestamp | 日期类型 | 包含年月日时分秒 |
示例
-- 创建一个t_person 主键：非空+唯一 name： age sex phone address birthday salary
create table if not exists t_person(
id int,
name varchar(20),
sex enum('男','女'),
phone char(11),
salary decimal(10,2),
address varchar(50),
birthday datetime
);
- 约束
约束限制字段值的格式和范围
| 约束 | 解释 | 说明 |
|---|---|---|
| primary key | 主键约束 | 用来明确表中的某一个字段为主键列 |
| not null | 非空约束 | 用来明确表中的某一个字段不能为空 |
| unique | 唯一约束 | 用来明确表中某一个字段值不能重复 |
| default | 默认值 | 当表中没有指明具体值时，使用默认值 |
| references | 外键约束 | 明确表中的某一列为外键列，并指明引用哪一张表的哪一个字段的值 |
1. 主键约束：用来唯一标识表中的一行数据，非空+唯一
注意：通常每张表中只能存在一个主键约束，没有主键约束的表是一张垃圾表。比如：学号、工号等，如果在一行数据中不能抽取出一个主键约束，此时应该自己创建一个字段作为主键约束
2. 非空+唯一不等于主键约束
3. 默认值 default
4. 外键约束：一般是另一张表的主键或唯一列
1. 外键列可以为null，可以不唯一
2. 对于一对多关系的表，外键应该建在多的一方 如：员工（外键）和部门 用户和订单（外键） 学生（外键）和班级
3. 建表的顺序：先建没有外键的表，再建有外键的表 如：先建部门表，再建员工表 先建用户表，再建订单表
4. 删表的顺序：先删有外键的表，再删没有外键的表 如：先删员工表，再删部门表 先建订单表，再建用户表
示例
-- 学生表
create table t_student(
id int primary key,
name varchar(20) not null,
age int not null,
sex enum('男','女') default '男',
mobile char(11) unique not null,
address varchar(50),
birthday datetime
);
-- 部门表
create table t_dept(
id int,
dname varchar(20) not null,
location varchar(20),
-- 添加主键约束
-- constraint 随便取的名字 约束的类型(约束名字)
-- 取名字是为了将来删除约束使用的
constraint pk_dept_id primary key(id),
constraint un_dname unique(dname)
);
-- 员工表
create table t_emp(
id int primary key,
name varchar(20) not null,
age int not null,
sex enum('男','女') default '男',
salary decimal(10,2),
birthday datetime,
-- 字段名 类型 references 表名(字段名) 创建一个外键约束 引用自哪张表的哪一个字段（主键|唯一列）
-- did int references t_dept(id)
did int,
-- 添加外键约束
-- constraint 外键名 外键约束(字段名) references 表名(字段)
constraint fk_dept_emp foreign key(did) references t_dept(id)
);
- 表的删除
语法
drop table 表名;
示例
drop table t_person;
drop table t_student;
drop table t_emp; -- 先删有外键的表
drop table t_dept; -- 再删没有外键的表
2. 视图【了解】
一个视图代表了一个复杂对象的查询的sql语句，基于视图操作就是基于这个复杂的sql语句的操作。
- 创建视图
语法
create view 视图名 as 查询sql的语句;
示例
create view v_d_e as (select e.*,d.department_name,d.location_id
from employees e
left join departments d
on e.department_id = d.department_id);
- 使用视图
语法
select ... from 视图名 where 条件;
示例
select * from v_d_e;
- 销毁视图
drop view 视图名;
示例
drop view v_d_e;
- 视图的特点
- 视图本质上就是一个复杂的查询sql语句，为了简化复杂的查询语句
- 视图本身并不能提高查询效率，只能提升开发效率，实际执行的依然是原有的复杂的sql语句。
- 视图不是不表，没有增加操作
- 视图用于屏蔽底层的一些机密的（可以基于视图做权限管理）
3. 索引【了解】
是数据库为了提升查询效率而提供的一种机制。
- 索引的语法
语法
-- 创建索引
create index 索引名 on 表名(列名);
-- 查看索引
show index from 表名;
-- 删除索引
drop index 索引名 on 表名;
示例
create index sname on t_student(name);
show index from t_student;
drop index sname on t_student;
- 索引的使用
当添加了索引，如果需要根据索引作为条件查询数据，会自动的使用索引。
提前准备千万级的数据才能显示效果。 - 索引的特点
- 索引是为了提高查询效率
- 索引会占用磁盘空间
- 索引不是越多越好，一般一张表中不超过5个。（主键列和唯一列会自动的添加索引）
- 索引会影响增删改的效率
- 索引不是何时都有效，模糊查询和空值查询无效
三、DML语句
对数据库的数据进行增加，删除，修改的操作
1. 添加
语法
-- 部分字段插入
insert into 表名 (字段名，字段名.....字段名) values(值，值.... 值)；
-- 全表字段插入
insert into 表名 values(值，值....值);
-- 注意：此种方式必须和表中的字段的个数、类型完全一致
示例
-- 1. 向t_student表中添加一条数据
insert into t_student (id,name,age,sex,mobile,address,birthday) values(1,'cpx',20,'男','15139785182','河南省郑州市','1995-12-12');
insert into t_student (id,name,age,sex,mobile,address,birthday) values(2,'chenpx',32,'男','15139785183','河南省郑州市','1990-10-14');
insert into t_student (id,name,age,mobile,address,birthday) values(3,'zhangsan',32,'15139785184','河南省郑州市','1990-10-14');
insert into t_student (id,name,age,mobile,birthday) values(4,'lisi',32,'15139785185','1990-10-14');
insert into t_dept (id,dname,location) values (1,'研发部','北京海淀区');
insert into t_dept (id,dname,location) values (2,'教学部','郑州市文化路');
insert into t_dept (id,dname,location) values (3,'市场部','郑州市南阳路');
insert into t_emp (id,name,age,sex,salary,birthday,did) values(1,'cpx',25,'男',1000.0,'1993-12-18',1);
insert into t_emp (id,name,age,sex,salary,birthday,did) values(2,'abc',25,'男',1000.0,'1993-12-18',1);
insert into t_emp (id,name,age,sex,salary,birthday,did) values(3,'xyz',25,'男',1000.0,'1993-12-18',2);
insert into t_emp (id,name,age,sex,salary,birthday) values(4,'xxx',25,'男',1000.0,'1993-12-18');
insert into t_dept values(4,'后勤部','郑州市管城区');
主键自动增长策略
-- 学生表
create table t_student(
id int primary key auto_increment,
name varchar(20) not null,
age int not null,
sex enum('男','女') default '男',
mobile char(11) unique not null,
address varchar(50),
birthday datetime
);
insert into t_student values(null,'cpx',22,'男','12345678910','郑州市金水区','1998-12-13');
insert into t_student values(null,'cpx',22,'男','12345678911','郑州市金水区','1998-12-13');
-- 注意：主键必须协商null
insert into t_student values('cpx1',22,'男','12345678911','郑州市金水区','1998-12-13');
2. 删除
语法
delete from 表名 [where 条件];
示例
delete from t_student where id = 5;
3. 修改
语法
update 表名 set 字段名1 = 新值, 字段名2 = 新值 .... [where 条件];
示例
update t_student set name = 'chenpx' where id = 6;
update t_student set name = 'abc',age = 25,sex = '女' where id = 7;
update t_student set age = 28;
四、TCL 事务控制语言
事务：是数据库为保证数据完整性的一种机制。保证了一个业务中对应的多条sql语句要么同时成功，则结果成功，只要有一个sql语句执行失败，则结果失败。事务是数据库中最小的执行单元，包含了多个sql的执行，事务中所有sql语句都执行成功，则事务提交（commit），只要有一个sql语句执行失败，则事务回滚（rollback）
1. 使用事务
-- 1. 开启事务
begin | start transaction
-- 2. 执行sql语句
sql语句
sql语句
sql语句
-- 3. 结束事务
如果结果正确 提交事务 commit
如果结果不正确 回滚事务 rollback
示例
create table t_account(
id int primary key auto_increment,
name varchar(20),
balance decimal(10,2)
);
insert into t_account values(null,'cpx',100.0);
insert into t_account values(null,'chenpx',100.0);
-- 开启事务
begin;
-- 执行sql
update t_account set balance = balance - 1 where id = 1;
update t_account set balance = balance + 1 where id = 2;
2. 事务的大小
事务的大小就是事务由几个sql语句组成的，取决于实际的业务需求。
转账：两个 更新操作
开户：1个sql语句
登录：1个sql语句
3. 事务的边界
开启事务：begin|start transaction
结束事务：
- DML（insert|update|delete）语句，明确指定结束
- 成功commit 失败rollback
- DDL（create |drop）语句，会自动的提交事务
- 正常退出：自动的回滚事务 执行exit
- 非正常退出：自动的回滚事务（直接关闭黑窗口|服务器宕机）
4. 事务的特点 ACID 【理解+面试】
A ：原子性
事务中所有的sql语句是一个整体，如果所有的sql语句执行成功，则提交事务，只要有一个sql语句执行失败则回滚事务。
C : 一致性
事务执行前后，数据的状态是一致的。
I：隔离性
事务与事务是相互独立的，互不干扰的。
D：持久性
事务一旦提交，数据的改变将是永久的。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
