SQL相关知识学习

文章目录

MySQL

Day01

1、 sql DB DBMS 之间的区别以及区别

DB ： DataBase 数据库，在硬盘中以文件的形式存在
DBMS ： 数据库管理系统
常见的有 ： MYSQL Oracle DB2 …

SQL ： 标准化结构语言，是一门标准的语言，标准的额 sql 适合用于所有的数据库产品
- sql 属于高级语言
- sql 在执行的时候，会先进行编译，然后进行运行，sql 语句的编译由 DBMS 进行完成
- DBMS 负责执行 sql 语句，通过执行 sql 语句进而执行 DB 当中的数据。
- Mysql 是一个数据库管理系统，严格意义上来讲

2、 什么是表

表：table
表：是数据库的基本组成单元，所有的数据都以表格的形式进行存储，目的是可读性比较强
行：数据 记录（data）
列：字段（column）

每个字段拥有什么属性
字段名 数据类型 相关的约束（具体的约束）

对于表的认识

3、学习 sql 语句，sql 进行相关的增删改查，SQL 是如何进行分类的呢

DQL： （数据查询语言）凡是 select 语句都是 DQL
DML： （数据操作语言）insert delete update 对于表中的 * 数据 * 进行 ** 增删改

DDL： （数据定义语言）create drop alter 对表中的结构的增删改
TCL： （事物控制语言）commit 提交事物，rollback 回滚事物
DCL： （数据控制语言）qrant 授权，revoke 撤销权限

表的设计在前期显得十分重要 ， 后期的修改的成本是比较大的

4、导入数据（后期的联系基于此数据）

4.1 mysql -uroot -p

4.2 show databases; 查看当前有哪些数据库

4.3 创建自己的数据库

create database bjpowernode; (创建数据库)

4.4 使用自己的数据路里面的数据

use bjpowernode;

4.5 查看当前数据可中有什么表

show tables;(这个不是 sql 语句 属于 MySql 的命令)

4.6 初始化数据

source \\Mac\Home\Desktop\ 编程实践 \SQL 学习 \ 数据脚本 \bjpowernode.sql

注意：在这里进行初始化之后，有三张表

5、 *.sql 结尾的文件 叫做“SQL 脚本”，什么是脚本？

当一个文件用 .sql 进行结尾，并且里面有大量的 Sql 语句，这就叫做 sql 脚本;
可以使用 source 命令进行执行 SQL 脚本
sql 脚本中的数据量比较大的时候，可以使用 source 命令进行初始化

6、删除数据库

drop database ...(自己的数据库的名字)

desc ... 表的名字 查看表的结构
select * from ... 查看表的内容

7、常用的命令：

- select database（）； 查看当前使用的数据库

- select version();

- \c 进行结束语句的输入
- exit 退出 mysql

8、查看创建表的语句

show create table emp;

9、简单的查询语句

简单的查询语句（DQL）

语法格式：
    select 字段名1，字段名2，字段名3，...from 表名

Tips:
1、SQL 语句使用封号进行结尾
2、SQL 语句不区分大小写

查询员工的年薪(字段可以参与数学运算)

select ename,sal * 12 from emp;

给查询结果的列进行重新命名

select ename,sal * 12 as yearsal from emp;

想要使用中文对字段进行命名，可以使用单引号进行

select ename,sal * 12 as '年薪' from emp;

as 关键字可以进行省略
实际的开发中不建议使用 *，使用起来的效率比较低下

10、 条件查询

语法格式：
    select 
        字段，字段...
    from
        表名
    where
        条件；
执行的顺序： from where select

练习：查询工资等于5000 的员工的姓名？
    select ename from emp where sla = 5000;
    查询某个人的工资
    select sal from emp where ename = 'SMITH';

<> 就是不等于

  找出那些人没有津贴？
数据库中，NUll 不是一个值，代表的是什么也没有，为一个空值
空不是一个值，代表什么也没有，为空；
必须使用 is null ; 或者 is not null 

**练习** ：1、找出工作岗位是MANAGER 和 SALEMAN 的员工
select ename,deptno from emp where job = 'manager' or job = 'saleman';

下面是 and 与 or 混合使用
当优先级不确定的时候，进行加括号处理，可以使得处理的结果输出正确

**练习:** 2、in 的功能等同于 or 
    找出工作岗位是MANAGER 和 SALEMAN 的员工
select ename,deptno from emp where job = 'manager' or job = 'saleman';

// in 
select ename,job from emp where job in('manager','saleman');

10.1 模糊查询 like

**练习：** 找出名字中含有 o 的人  / 找出公司中所有姓李的人 ，用到的就是模糊查询；
    在模糊查询在红需要掌握的两个符号： %  _ 
    % 代表任意多个字符
    _ 代表任意一个字符

select ename from emp where ename like %o%;

**练习：** 找出名字中第二个字母是 A 的
select ename from emp where ename like '_A%';

**练习：** 找出名字中有下划线的(使用转义字符 进行处理)
\具有转义的作用
select ename from emp where ename like '%\_%';

**练习：** 找出名字中最后的一个字母是 T 的
select ename from emp where ename like'%T'

11、排序 （升序 降序）

**练习：** 按照工资进行升序。找出员工的名字以及薪资
select ename,sal from emp order by sal;

注意：默认是升序进行排列的、如何进行指定的升序或者降序呢
asc(升) desc（降低排序）

select ename,sal from emp order by sal asc;
select ename,sal from emp order by sal desc;

按照工资的降序排，工资一样的按照名字的升序排
select ename,sal from emp order by sal desc,ename asc;
越靠前的字段，越能起到主导的作用，只有前面的字段无法 完成排序的时候，才会启用后面的字段

**练习：** 找出工作岗位是saleman 的员工，要求按照工资的降序进行排序
select ename,sal from emp where job = 'saleman' order by sal asc;

    select
        *            3
    from
        tableman     1
    where 
        条件          2
    order by
        ...          4


order by 是最后进行执行的
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| TURNER | SALESMAN | 1500.00 |
| ALLEN  | SALESMAN | 1600.00 |
+--------+----------+---------+

12、分组函数

分组处理函数 只有这五个 另外的名字叫做 多行处理函数;
多行处理函数的额特点：输入多行，输出的结果是一行；
分组函数自动忽略 NULL

count 计数； 自动对于空值进行忽略
sum 求和；
avg 平均值；
max 最大值；
min 最小值；
记住所有的分组函数都是对于“一组”数据进行操作的；

找出工资总和
select sum(sal) from emp；
找出相关的平均，最大，最小工资；

找出总人数：

select count（*） from emp;
select count(ename) from emp;

13、单行处理函数

什么是单行处理函数
输入一行，输出一行

计算每个员工的年薪

select ename,(sal + comm) * 12 as yearssal from emp;

所有：数据库中，只要参数数据的运算有一个是NULL 那么计算的结果就是NULL 需要加以注意

使用 ifnull(可能为NULL的数据，被当作什么处理)处理函数

使用 ifnull 进行判断是否为空 进行相关的计算处理

ifnull() 对空进行处理

**select ename,(sal + ifnull(comm,0)) * 12 as yearssal from emp;**
+--------+----------+
| ename  | yearssal |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

14、分组函数 聚合函数 多行处理函数

分组函数自动忽略 NULL 只有五个
有 NULL 参加的运算 结果一定是 NULL

**练习：** 找出工资高于平均工资的员工    
    select ename,sal from emp where sal > avg(sal);

上面的结果是不对的，为什么呢，为什么错误，无效的使用了分组函数；
原因：sql 语句的规则：分组函数不可以直接出现在 where 语句中 ，where 后面不 能出现五个函数中的任何一个，这是为什么呢
具体的原因是：group by 是在 where 进行执行之后才会执行
分组函数只能在分完组之后才能进行使用，还没有分组 ，当然不能使用分组函数

注意：分组函数中的函数自动排除NULL值 ，不需要进行 is null 进行判断

14.1 count(*) 和 count(具体的某个字段) 有什么区别

count(具体的某个字段) 记录某个字段中不为空（NULL）的字段的个数
count(*) 不是记录某个字段中数据的个数，而是统计总记录条数，与某个字段是无关的

分组函数的组合使用

 select count(*),sum(sal),avg(sal),sum(sal),max(sal),min(sal) from emp;

+----------+----------+-------------+----------+----------+----------+
| count(*) | sum(sal) | avg(sal)    | sum(sal) | max(sal) | min(sal) |
+----------+----------+-------------+----------+----------+----------+
|       14 | 29025.00 | 2073.214286 | 29025.00 |  5000.00 |   800.00 |
+----------+----------+-------------+----------+----------+----------+

单行数据处理函数，多行数据处理函数
单行 输入一行 输出一行
多行 输入多行 输出一行

15 group by 和 having

group by 按照某个字段或者某些字段进行分组；（先分成不同的组，然后对组中的数据进行处理）
having 对分组之后的数据进行再次的过滤；

案例：

    找出每个工作岗位的最高薪资；
    select * from emp order by job; //寻找有哪些岗位
    select max(sal) from emp group by job;//进行最高薪资的寻找

分组函数一般都会和 group by 进行联合使用，这是其叫做分组函数的原因，并且任何一个分组函数都是在（count sum avg max min）group by 执行结束之后才会执行；
没有进行group by 的时候，整张表自动成为一组（整张表就是一个整体的数据）；

select   5
...
from     1
...
where    2   （此处后面不能进行分组函数的使用，此时不能使用分组函数，因为还没有尽        心分组目前）
...
group by 3    (此处进行分组)（where 执行完之后才会进行分组）
...
having   4   （分完组进行二次过滤）
...
order by 6
...

**练习：** 找出高于平均工资的员工：
 第一步：找出平均工资；
 select avg(sal) feom emp;
 第二步：找出高于平均工资的人（分两步进行执行查询）
 select ename,sal from emp where sal >= ...

(where 后面不能直接使用分组函数)

Day02

1、关于查询结果集的去重？

select distinct job from emp;

| job |
±----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
±----------+
select ename,distinct job from emp;
// 以上的sql 语句是错误的，前面是14 条语句，后面是 4 条语句，是不正确的；
记住：distinct 只能出现在所有字段的最前面；
distinct 出现在所有高字段的最前面，表示的是：所有的字段进行联合，去除重复性，并不是针对某一个字段进行重复性的去除；

2、连接查询

2.1、什么是连接查询？

在实际开发中，大部分的查询不是在单表中查询，一般都是多张表进行查询，进行联合    查询，一个业务会对应多张表，比如学生和班级，，起码两张表，
    stuno       stuname     calssno     calssname
    ---------------------------------------------
    
    ...

学生与班级存储在一张表中，不存在大量的数据的冗余；
关系型数据库，多张表之间进行建立联系；想

2.2 连接查询的分类？

根据语法出现的年代：
SQL 92（DEA 数据库管理员 Database Administer） ；
SQL 99

根据表的连接方式进行划分，包括：
内连接
等值连接
非等值连接
自连接

连接
左外连接：（左连接）
右外连接：（右链接）

全连接（少用）

2.3 在表的连接查询方便，有一种现象被叫做笛卡尔积现象（笛卡尔乘积现象）

**案例分析：**找出每一个员工的部门名称，要求显示员工名字与部门名字
select ename,deptno from emp;
// 员工表
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+

// 部门表

select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
// 运行的结果将会是 16 * 4 共有 56 条查询的记录；

select ename,dname from emp,dept;
两张表之间进行连接查询，没有任何条件的限制，结果将会是两张表的记录条数的乘积

关于表的别名：
    select e.ename,d.dname from emp e,dept d;

给予表的别名的好处
第一：执行的效率比较高；
第二：可读性比较好；

2.4 怎么进行笛卡尔现象的避免？进行加条件进行过滤；

思考：避免了笛卡尔乘积，会减少匹配的次数吗？
不会，次数不变，显示的是有效记录；

**案例分析：**找出每一个员工的部门名称，要求显示员工名字与部门名字
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

2.5 内连接之等值连接：最大的特点是：等量关系

select(太老 不进行使用)(语法不清晰)
    e.name,d.name
from
    emp e,dept d
where
    e.deptno = d.deptno;

常用的：            （SQL 99 结构清晰，表的连接条件和后来的 where 条件分离了）
select
    e.ename,d.dname
from
    emp e
inner join         （可以带着 inner 目的是让其可读性进行增强）
    dept d
on(后面加上表的连接条件)
    e.deptno = d.deptno;

…
A
join
B
on
连接条件
where
…

2.6 内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系

**练习：**找出每个员工的工资等级，显示员工名字、工资、工资的等级； 

select 
    e.ename,e.sal,s.grade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal;
    

+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

2.7 自连接 最大的特点是：一张表看作是两张表，自己连接自己

**练习：**找出每个员工自己的上级领导，员工名字与领导的名字相互对应

员工的领导编号的 等于 领导的员工编号（一张表看成两张表，就是自连接）

员工的领导编号 等于 领导的员工编号（逻辑上的等量关系需要找到）（等值连接） 

select
    a.ename,b.ename
from
    emp a
inner join
    emp b
on
    a.mgr = b.empno;
    
-------
select a.ename as '员工名字',b.ename as '领导名字' from emp a inner join emp b on a.mgr = b.empno;

2.8 外连接

什么是外连接？与内连接有什么区别？
内连接：假设 A 和 B 进行连接，使用内连接的话，凡是 A 表和 B 表能够匹配上记录查询出来，此为内连接，两张表之间没有主副之分，两张表是平等的；

外连接：两种表中，一张是主表，一张是副表，主要查询主表中的数据，附带查询副表中的数据，副表中的数据没有与主表中的数据匹配上的时候，副表中自动模拟出 NULL 与之匹配；

外连接的分类：
左外连接：表示左边的表是主表
右外连接：表示右边的表是主表
做连接有右链接的写法，右链接也会有左连接的写法

**练习：** 找出每个员工的上级领导？（所有的员工都需要进行查询）
内连接：

select
    a.ename '员工'，b.ename '领导'
from
    emp a
join 
    emp b
on
    a.mgr = b.empno;

外连接：

select
    a.ename '员工名字'，b.ename '领导名字'
from
    emp a
left join 
    emp b
on
    a.mgr = b.empno;

3 子查询

3.1 什么是子查询？子查询可以出现在哪儿位置？

select 语句中进行 （select 语句的嵌套）被嵌套的句子就是自查询

select
...(select)
from
...(select)
where
...(select)

3.2 where 子句中使用子查询

** 练习：** 找出高于平均工资的员工的信息
错误的使用，分组函数不能使用在 where 之后
// select * from emp where sal > avg(sal);
分组函数的使用条件，

3.3 from 后面进行嵌套查询语句

练习 ： 找出每个部门平均薪水的薪资等级
第一步： 找出每个部门的平均薪水（按照部门的编号进行分组，求sal 的平均值）
select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

第二步：将以上的查询结果当作是临时表 t ，让t 和salgrade 表进行连接，条件是:
t.avgsal between s.losal and s.hisal(最低以及最高工资之间)

select
    t.*,s.grade
from
    (select deptno,avg(sal) from emp group by deptno) t 
join 
    salgrade s
on
    t.avgsal between s.losal and s.hisal;
from 后面 (加上空格表示的是起的别名)

4、分页查询（十分重要）（叫做分页查询）

4.1 limit 是 mysql 特有的，其他的数据库是没有的，不是通用的

4.2 limit 取结果集中的部分数据，这是他的作用

4.3 语法机制：

limit startindex , length
startIndex 表示起始的位置
length 表示取出几个

练习：
    取出工资前五名的员工
    （思路：降序，取前五个）
select ename, sal from emp order by sal desc;
select ename, sal from emp order by sal desc limit 0,5; (取出来的前五个)
select ename, sal from emp order by sal desc limit 5; 


select * from tableName limit i,n
# tableName：表名
# i：为查询结果的索引值(默认从0开始)，当i=0时可省略i
# n：为查询结果返回的数量
# i与n之间使用英文逗号","隔开

# 
limit n 等同于 limit 0,n

4.4 limit s是sql 语句执行的最后一个环节

select      5
...
from        1
...
where       2
...
group by    3
...
having      4
...
order by    6
...
limit       7
...

4.5 练习： 找出排名在第四到第九的人

select ename,sal from emp order by sal desc limit 4,6;
limit 后面的参数 第一个是索引值，从 0 开始，后面的是取几个；此处为取六个；

4.6 SQL 通用的标准分页？

每页显示三条记录：
第一页：0，3
第二页：3，3
第三页：6，3
第一个参数表示开始的索引，第二个表示显示几条记录；
第pageNo页 ： （pageNo - 1） * pageNo,pageSize
pageSize: 每页显示的记录的个数
lageNo: 显示第几页

5、创建表：

建表的语句格式：

create table 表名{
    字段名1 数据类型，
    字段名2 数据类型，
    字段名3 数据类型，
    ...
}

关于mysql 中的常见的数据类型：
int
bigint 长整型
float
double
char 定长字符串
varchar 不定长字符转
date 对应的日期（对应java.sql.Date类型）
BLOB 二进制大对象（存储视频，图片等流媒体信息）
CLOB 字符大对象（存储较大的文本，可以存储4G 的字符串）

表名在数据库中，一般使用：t_ 或者 tb1_ 进行开头

创建一张学生信息表：
        学号（int），
        姓名（varchar）
        性别(char)，
        班级编号(varchar)，
        生日(char)
       
create table t_student(
    no bigint,
    name varchar(255),
    sex char(1) default 1,
    classNo varchar(255),
    birth char(10)
);

        

# 7、使用 insert 进行插入数据
        语法格式:
            insert into 表名（字段名1，字段名2，字段名3，...） values (值1，值2，值3)

    要求：字段的数量和值的数量相同，并且数据类型是相同的
```sql
进行数据的插入，想数据库中的表进行修改；
insert into t_student(no,name,sex,classNo,birth) values(1,'Jack','1','Gride 3','11111')

前面进行列的指定，后面进行行中的数据的插入

列的数量需要和表中的列进行匹配，否则会报错
字段可以省略不写，但是对于后面值的顺序以及值的类型都具有要求；

5.1 进行表的删除

drop table if exists t_student;

需要注意的是：当一条 insert 进行执行的时候，表格中必然会多出来一行记录；

6 表的复制以及批量插入

表的复制
将结果使用表进行创建出来

create table emp1 as select * from emp;

7 将查询结果导入表中

将选择的结果批量插入到新建立的表中

create table dept1 as select * from dept;

8 修改数据

语法格式：
update 表名 set 字段名1 = 值1，字段名2 = 值2… where 条件；

注意：没有条件的时候，整张表的数据全部进行更新；
练习 将部门的LOC 修改为：shanghai,将部门名称修改为：人事部

update dept1 set loc = ''SHANGHAI',dname = 'RENSHIBU' where deptno = 10;

在后面进行条件的限制；

9 删除数据

delete from dept1 where deptno = 10;
truncate table emp1;    // 表与数据进行隔断，删除的速度非常快

10 DQL （select）

DML （insert delete update）
DDL （create drop alter）

对于表的结构数据，使用工具进行修改即可，一般在实际的项目中，一般不会对于表中的结构进行过大的改动，对于表的结构的额修改就是对于之前的设计的否定，即使需要修改，使用工具修改即可，不需要使用语句进行修改；修改表的结构的代码不会出现子啊Java的代码中；出现在Java中的有增删改查，主要是进行数据进行操作；
CRUD 增删改查的专业术语；
Create(z增) Retrive（检索） Update（修改） Delete（删除 ）

基本的 insert update delete 进行Java 程序运行即可

11 约束 constraint

11.1 什么是约束

insert into
创建表的时候，可以给表中的字段添加相应的约束，保证表格中的数据的合法性，完整性
常见的约束？
非空约束；（not null） 约束的字段不能为NULL
唯一约束；(umique) 约束的字段不能重复
主键约束；(primary key) 约束的字段不能为 NULL 不能重复 (PK)
外键约束；(foreign key) :…(FK)
检查约束；(check) mysql 目前没有
## 11.2 非空约束 not null

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255) not null,
	password varchar(255)
);

insert into t_user(id , username , password) values(1,'Jack','123456')

11.2 唯一性约束

唯一性约束修饰的字段具有唯一性，不能重复，但是可以为 NULL；
案例：

drop table if exists t_user;
create table t_user(
		id int,
		username varchar(255) unique
);

insert into t_user values(1,'Jack');
// 下面的语句会报错 因为违反了 唯一性约束；出现了两次
insert into t_user values(2,'Jack');

案例：设置两个字段联合起来的时候，同时不能重复

drop table if exists t_user;
create table t_user(
		id int,
		usercode varchar(255),
		username varchar(255),
		unique(username,usercode)
);
insert into t_user values(1,'Hello WOrld','Jack');
如果两个字段的后面分别加上了 unique 结果就是，每个独立的字段中的值是不能重复的；
注意：not null 约束只有列级别的约束，没有表级别的约束

11.3 主键约束

怎么给一张表添加主键约束？

drop table if exists t_user;
create table t_user(
	id int primary key,
	username varchar(255),
	email varchar(255)
);

insert into t_user(id,username,email) values (1,'jack','@qq');
insert into t_user(id,username,email) values (2,'Tom','@163');

select * from t_user;
主键相关的术语：
	主键约束：	primary
	主键字段：	id
	主键值：		1

上面的代码中，id 是主键，因为添加了主键约束，主键字段的数据不能为 null ，也不能重复，此为主键的特点；
添加主键约束，需要在 字段的后面加上 primary key 即可；

主键的作用？
SQL表的设计三范式中要求：
任何一张表中都需要有主键；
主键的作用就是：
主键字段上面的每一个值，是该行的唯一标识；
相当于每一行的身份证号码，不能为空，不能重复

主键的分类：
	根据主键字段的字段数量进行划分：
		- 单一键；
		- 复合主键；（不建议使用，违背三范式）
	根据之间额性质进行划分：
		- 自然主键；
		- 业务主键；（主键值与系统的业务进行挂钩：比如使用银行卡号作为主键，使用身份证号码作为主键）
一张表的主键约束只能有一个，牢记；
可以联合起来添加主键，但是不能给多个字段后面都加上主键约束；

使用表级约束方式定义主键： 使用关键字进行约束即可；

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255),
	primary key(id)
);
insert into t_user(id,username) values(1,'Jack');
insert into t_user(id,username) values(2,'Jac');
insert into t_user(id,username) values(3,'Ja');
insert into t_user(id,username) values(4,'J');

mysql 提供的主键值的自增：

drop table if exists t_user;
create table t_user(
	# id 字段自动维护一个自增的数字，从 1 开始，以 1 进行递增；
	# id 会自动进行增加的操作；
	# 使用关键字 auto_increment
	id int primary key auto_increment,
	username varchar(255)
);

insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
insert into t_user(username) values('f');
select * from t_user;

11.4 外键约束

  • 关于外键约束的相关术语
    外键约束：foreign
    外键字段：添加有外键约束的字段
    外键值：外键字段中的每一个值
  • 外键约束的作用？

案例分析：设计数据库表，维护学生和班级的信息
第一种方案：一张表存储所有的数据
no name classno classname
----------------------------------------
1 jack 1212 海淀区人大附中

导致数据大量的冗余，

第二种方案：两张表（班级和学生表）
t_class 班级表
cno caname

t_student 学生表
sno sname

将上表中的建标语句书写出来：

t_student 中的classno 字段 引用 t_class 表中的 cno 字段，此时的t_student 表叫做子表，t_class 表叫做父表；

删除的时候，先删除子表，再删除父表，因为父表使用子表中的东西，不能进行强制的删除；

创建的时候，现有父表再有子表；

12 存储引擎

表在底层存储的时候，采用了不同的存储方式；

下面是完整的建表语句
在mysql 中不建议使用飘号；因为不通用
建表的时候，使用存储引擎是InnoDB,utf

CREATE TABLE t_x (
id int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Oracle 叫做表的存储方式；

mysql 可以支持多种存储引擎，合适的时机，合适的引擎；

查看当前支持的存储引擎
show engines \G

13 事务（Transaction）

13.1 什么是事务？

一个事务是一个完整的业务逻辑单元，不可以再分，
银行账务转账， A 到 B 转账；
一个账户进行转入，一个进行转出，两条语句，必须同时进行，不能只成功一个;

 update t_act set balance = balance - 1000 where actno = 'act-001';

 update t_act set balance = balance + 1000 where actno = 'act-002';

保证两条语句 同时执行，此时需要使用数据库的事务机制；

13.2 与事务有关的只有（DML 语句）

（insert delete update）操作不会先直接影响到具体的文件，记录的数据操作，放到缓存当中

为什么？
因为这三个操作都是和数据库中的 “数据” 相关的
事务的存在是为了保证数据的完整性，安全性；

13.3 假设所有的业务可以使用 一条DML 搞定，还需要使用事务机制吗？

此时就不需要，业务的完成是需要多个语句进行执行的；
实际情况中，需要多条语句进行联合完成；

事务的简单理解，将所有需要执行的语句，进行捆绑，然后进行一起的操作执行，完成某一项工作；

13.4 事务的特性

 A 原子性 事务是最小的额工作单元，不可以进行再分；
 C 一致性 事务必须保证多条DML 语句同时成功或者失败；
 I 隔离性 不同的事务之间具有隔离性；
	隔离级别
 D 持久性 最终数据持久到硬盘中，事务才算成功的结束；

13.5 关于事务之间的隔离性

事务之间的隔离存在隔离级别，理论上存在四个隔离级别：
	第一级别：读未提交（read uncommited）
		对方的事务还没有提交，我们当前的事务可以读取到对方未提交的数据
		读未提交存在脏读现象（Dirty data）表示读取到了脏的数据
		
	第二级别：读已提交（read commited）（Oracle）
		对方事务提交之后的数据，我方可以读取到；
		解决了脏读现象；
		读已提交存在的问题是：不可重复读												
		
	第三级别：可以重复读（repeated read） （mysql）
		解决了不可重复的问题；
		读取到的东西是幻像
	
	第四级别：序列化读，解决了所有的问题；
		效率低，需要排队

13.6 演示事务

mysql 中的事务是默认提交的（什么是自动提交？只要执行任意一条DML语句则提交一次，怎么关闭自动提交？）

14 索引

14.1 什么是索引，有什么作用

方便快速的寻找到需要的资源；
第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）

索引可以缩小扫描范围，索引需要进行维护，不能随意的添加，有一定的维护成本，表中的数据经常修改，不适合添加索引

14.2 怎么创建索引对象，怎么删除索引对象

创建索引
create index 索引名称 on 表名（字段名）;

删除索引
drop index 索引名称 on 表名；

14.3 什么时候考虑字段上加索引 满足什么条件

  • 数据量庞大
  • 很少进行DML 操作（字段操作，索引需要维护）
  • 添加索引是给一个或者某些字段进行添加索引
  • 字段经常出现在 where 字段当中，不出现，没有必要进行添加索引

14.4 主键和具有 unique 约束的字段自动会添加索引（自动添加）

查看SQL 的执行情况：

 explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

给 sal 字段 添加索引
create index emp_index on emp(sal);

14.5 索引实现的底层原理

15 视图

站在不同的角度查看数据（同一张表，在不同的视角进行查看）

posted @   YIMENG-0  阅读(60)  评论(0)  编辑  收藏  举报
相关博文：
· Java 八股文 持续修改更新...
· Java IDEA 进行数据库的连接 （MYSQL）实现数据库交互 及其配置 （基于Mac M1）
· MySQL学习笔记
· mysql笔记
· MySQL
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示