数据库基础篇

学习来源：黑马程序员

Mysql

数据库密码 123456
service.msc进入windos的服务管理
net start mysql80 启动mysql服务
net stop mysql80 停止mysql服务

客户端工具连接

直接使用mysql的命令行去链接
mysql -h 127.0.0.1 -P 3306 -u root -p 连接mysql

客户端——》DBMS（数据库管理系统）——》数据库——》表——》数据

• 关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

SQL

SQL语句可以单行或多行书写，以分号结尾
SQL语句不区分大小写，关键字建议使用大写
SQL语句可以使用空格/缩进来增强语句的可读性
注释：

• 单行注释：-- 注释内容
• 多行注释：/* 注释内容 */

分类

DDL：数据定义语言，用于定义数据库的结构——针对的是数据库的操作

• 查询：show databases;
select database();查询当前数据库
• 创建：create if not exists database 数据库名;
• 删除：drop database 数据库名;
• 使用：use 数据库名;

进入数据库后查看表结构

• 创建表：

create table 表名(
    字段名 字段类型 约束 comment `注释`，
    字段名 字段类型 约束 comment `注释`
) comment `注释`;  

• 查询：

  • 查询当前库里面的所有表：show tables;
  • 查询表结构：desc 表名;
  • 查询建表语句：show create table 表名;

• 修改表：

  • 添加字段：alter table 表名 add 字段名 字段类型 约束 comment 注释;
  • 修改字段类型：alter table 表名 modify 字段名 新字段类型 约束 comment 注释;
  • 修改字段名：alter table 表名 change 旧字段名 新字段名 字段类型 约束 comment 注释;
  • 删除字段：alter table 表名 drop 字段名;
  • 修改表名：alter table 表名 rename to 新表名;

• 删除表：drop table 表名;
truncate table 表名; //清空表中的数据

DML：数据操作语言，用于操作数据库中的数据

• 添加数据：insert into 表名(字段名1，字段名2，字段名3) values(值1、值2)，(值1、值2），（值1、值2);

• 查询数据：select 字段名1，字段名2，字段名3 from 表名;

• 修改数据：update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3 where 条件;
  修改条件如果没有就是修改整个表，每个都修改

• 删除数据：delete from 表名 where 条件;
  删除条件如果没有就是修改整个表，每个都修改

DQL：数据查询语言，用于查询数据库中的数据
select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组后条件 order by 排序字段 limit 分页参数
设置别名：select 字段名 as 别名 from 表名;
去除重复记录： select distinct 字段名 from 表名;

select name ,workno ,age from emp 
select * from emp   
select addr  as `地址` from emp  //呈现出表的行变成别名 

select distinct addr from emp //去除重复记录      

条件查询

select * from emp where age = 88  
select * from emp where age <20 
select * from emp age <= 20 
select *  from emp where id is null 
select * from emp where id is not null 
select * from emp where age != 88  
select * from emp where age between 15 and 20 
//大于等于15小于等于20   
select * from emp where gender ='女' and age <25  
select * from emp where age =18 || age =20 || age= 40  
select * from emp where age in (18,20,40)  
select * from emp where name like '__' 
select * from emp where id like '%x'

聚合函数
讲一列数据作为一个整体，进行纵向计算

select count(id) from emp //统计id列的行数,作用于列中  
 select count(*) form emp  
//所有的null值不参与聚合计算  
slelect avg(age) from emp  
select max(age) from emp  
select min(age) from emp 
slelect  sum(age) from emp where addr = '西安'     

分组查询
将一列数据进行分组，然后进行纵向计算
select 字段列表 from 表明 where 条件列表 group by 分组字段列表 having 分组后条件列表
where是分组之前的过滤而having是分组之后的过滤

select gender,count(*) from emp group by gender 

select gender ,avg(age) from emp group by gender    

select  addr  from emp where age <= 45 group by addr having count(*)>=3

排序操作
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2
多字段排序——会根据第一个字段排序，如果第一个相同则会根据第二个再进行排序
ASC：升序排序
DESC：降序排序

select * from emp  order by age ASC   
select * from emp order by time DESC 
select * from emp order by age ASC time DESC 

分页查询
select 字段列表 from 表名 limit 起始索引，查询条数
起始索引从0开始，起始索引=（查询页码-1）*每页显示的条数
分页查询是数据库的方言，不同的数据库有不同的分页查询语句

select * from emp limit 10   //默认的时候从0开始  
select * from emp limit 10 ,10  //数据如果不够10条，则返回实际条数  

select * from emp where gender='女' and age in (20,21,22)  
select * from emp where gender = '男' and  (age between 20 and 40 or name like '___')  
select gender ,count(*) from emp where age <60 group by gender 
select name ,  age from emp where age < 35 order by age ASC time DESC  
select *  from emp gender = '男' and age between 20 and 40  order by age ASC time DESC limit 0 5  

执行的顺序：
from ——>where ——>group by ——>having ——>select ——>order by ——>limit

select age ad eage from emp e where e.age < 35 order by aage

DCL：数据控制语言，用于控制数据库的访问权限,管理用户权限

查询数据库
Use mysql;
select * from user;
//所有用户权限都放在user表中
//Host+User 才能定位一个用户

创建用户
create user '用户名'@'主机名' identified by '密码';

create user itcase@localhost identified by '123456';
//创造了但是没有访问其他数据库的权限
create user itcase@'%' identified by '123456';
//创造了可以任意主机使用这个用户去访问数据库的权限

修改用户
alter user '用户名'@'主机名' identified with 加密方式 by '新密码';

删除用户
drop user '用户名'@'主机名';

ALL ALL PRIVILEGES  //所有权限

SELECT  //查询权限
INSERT  //插入权限
UPDATE  //更新表权限
DELETE  //删除数据权限
DROP  //删除数据库权限

查询用户的权限
SHOW GRANTS FOR '用户名'@'主机名';

授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

SHOW GRANTS FOR 'itcase@%';  
GRANT ALL ON DB.emp TO 'itcase@%';
REVOKE ALL ON DB.emp FROM 'itcase@%';  

函数

是指一段可以直接呗另一段程序调用的程序或代码

字符串函数

select concot('Hello','MYSQL'); 
select lower('Hello')；
select upper('hello');
select lpad('01'，'5','a')  //向左边填充到5位填充字符为a  
select rpad('01'，'5','a')  //向右边填充到5位填充字符为a    
select trim('   hello  mysql   ')//去除两边的空格  
select substring('hello mysql',1,5)  //这个字符串从1 开始的截取5个

可以复合使用如：
update emp set id = lpad(id,5,'0')；

数值函数

select ceil(1.1); //向上取整
select floor(1.9); //向下取整
select round(1.51); //四舍五入
select rand(); //随机数0~1之间
select mod(10,3); //取余  10%3   

select lpan(ceil(rand()*10000),'6','0')

日期函数：

select curdate(); //当前日期
select curtime(); //当前时间
select now(); //当前日期和时间
select year(now()); //年
select month(now()); //月
select day(now()); //日
select hour(now()); //时
select date_add(now(),interval 1 day); //往后推1天  

datediff('2025-05-10','2025-05-1'); //计算两个日期之间的差值    

select name , datediff ( entrydate ,now()) as 'date' from emp order by date DESC

流程控制函数

select if(age<18,'未成年','成年') from emp;

ifnull(nill,'Default')  //如果第一个为空那么就返回第二个，如果不为空就直接使用第一个  

select name, (case addr when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as 'addr' from emp

select name, case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end as 'math', case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end as 'english' from student

约束

作用于表中字段上的规则，用于限制插入到表中的数据
保证数据的完整性、有效性、正确性、一致性

对表进行约束

creat   table  user
{
    Id int  primary key auto_increament comment'主键', 
    name varchar(10) unique  not null comment '姓名' ,
    age int  check(gae>10 && age<=20)  ‘年龄’,
    status char(1) default '1' comment '状态',
    gender char(1) ,
}  comment '用户'；

插入数据
其中如果数据库申请了数据但是因为数据有错误没有执行的话主键依旧已经算自增了，所以会出现主键跳跃的情况
insert into user (name,age,gender) values ('tom1',2,'男');

外键约束

通过对两张表建立连接，保持一致性和完整性
其中拥有主键的称为主表，另一个表中跟随这个的称为从表

方法一：创建的时候添加

creat table 表名  {
    字段名   类型  ，
    ······
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表（主表列表）  
}

方法二：创建后添加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表（主表列表）；

ALTER TABL usr ADD CONSTRAINT dempt_emp_id FOREIGN (emp_id) REFERENCES dept(id)
其中外键名称自己设定

删除外键ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
ALTER TABLE usr DROP FOREIGN KEY dempt_emp_id

删除/更新的约束

NO ACTION表示在父表中删除\更新对应记录的时候会先检查是否有外键索引如果有则不允许与 RESTRICT一致
CASCADE 当在父表中删除\更新对应的记录，会先检查然后进行一致性的更新\删除
SET NULL 同上不过是会把从表改成NULL（表约束允许为NULL）
SET DEFAULT 同上不过是会把从表改成默认值

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 约束条件 ON DELETE 约束条件 这个就表示删除和更新都会进行这个操作

多表查询

多表关系

表的关系：
一对多（多对一）：在多的一方建立外援，指向一的一方主键，即一个部门对应多个
多对多：并且通过建立中间表（至少包含两个外键）。即一个学生可以选多门课程，一个门课程可以选多个学生
一对一：用于单表查分，将一张表的基础字段放在一张表，其他详细信息放到另外一张表，从表通过加入UNIQUE的外键关联主键

多表查询

select * from emp,dept 这样就会形成两个集合的所有组合数量——笛卡尔积
select * from emp,dept where emp.dep_id = dept.id;

连接查询
内连接：相当于查询A、B交集部分数据
外连接：
左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名

内连接

查询的是两张表的交集部分，当为表起了别名，我们就无法再使用表的本来名称了
隐式内连接：SELECT 字段列表 FROM 表1、表2 WHERE 条件....;
显式内连接：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...
例：select * from emp e inner join dempt d on e.dept_id=d.id

外连接

左外连接：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...
完全包含左表的数据，并且包含表1和表2交集部分的数据
右外连接：SELECT 字段列表 FROM 表2 RIGHT [OUTER] JOIN 表2 ON 条件 ....
完全包含右表的数据，并且包含表1和表2交集部分的数据

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...
SELECT u.name ,u1.name FROM user u JOIN user u1 on u.managerid = u.id

联合查询

会讲两次查询的结果合并起来，形成一个新的查询结果集

SELECT 字段列表 FROM 表A  ...
UNIO [ALL]  
SELECT 字段列表 FROM 表B  ...

进行联合查询必须确保多张表的列数和字段类型都保持一致

select * from age<20 union all select * from salary > 5000 unio all 会对查询结果进行去重

子查询

嵌套select语句称为嵌套查询——子查询
可分为：标量子查询、列子查询、行子查询、表子查询

标量子查询

返回的结果是个单个值，最简单的形式。
常用的操作符： = <> > >= <=

select username from emp where after data > (select data form emp where name ='房东白')

列子查询

子查询返回的结果是一列
常用的操作符号： IN(在指定集合范围内) 、 NOT IN（不在指定集合范围内） 、 ANY （子查询返回列表中，有任意一个满足即可） 、SOME同any 、ALL（子查询返回的条件全部都得满足）

select * from emp where emp_id in (select id from demp where name ='销售部');

select usrname from emp where salary > all (select salary from emp where dept_id=（select id from dept where name = '财务部'）)

select usrname from emp where salary > any/some (select salary from emp where dept_id=（select id from dept where name = '财务部'）)

行子查询

常用操作符号： = 、<>、in 、NOT IN
select * from emp where (salary , managerid) = (select salary , manangerid from emp where name= '张无忌');

表子查询

子查询返回的结果是多行多列（类似表），这种子查询称为表子查询
操作符号：IN

案例：
select name ,age,job , d.name from user u join dept d where u.dept = d.id
select name ,age,job , d.name from user u , dept d where u.dept = d.id

select nu.*,d.name from user u left join dept d on u.dept_id =d.id where u.age>40

事务

方式一：
select @@autocommit就能查询是否能够自动提交
使用 @@autocommit = 0 ;进行手动设置提交这只对于当前界面有效果
最后要使用commit 才能提交成功

如果不设置自动提交的话他就是属于没有对数据库操作只是临时修改了
使用不自动提交的时候中间出错他并不会提交到数据库，只是临时修改了，程序出错了可以执行rollback实现回滚食事务的功能（就是撤回所有当前执行的临时操作）

方式二 ：
START TRANSACTION 或 BEGIN; 表示开启事务
COMMIT 提交事务
ROLLBACK 回滚事务

整合模式

BRGIN ; 
·········
COMMMIT ;
ROLLBACK;

事务的四大特性

原子性：事务是不可分的最小操作单元，要么全部成功，要么全部失败
一致性:事务完成时，必须所有数据都保持一致状态。
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作的影响下独立环境下运行。两个不同事务操作同一个数据库的时候不会相互影响
持久性：事务一旦提交或回滚，他对数据库中的数据改变是永久的——存储在磁盘当中

并发事务引发的问题

关于隔离性引发的问题：

1. 脏读：一个事务读到另一个事务还没有提交的数据（都还没有提交）
   

2. 不可重复度：一个事务先后读取同一条记录，单两次读取的数据不同。解决之后两次查询的都是同样的了 （事务B提交了 ）
   

3. 幻读 ：一个事务按照条件语句查询数据的时候，没有对应的数据行。但是在插入数据时，又发现这行数据已经存在好像出现幻影 （上个锁）
   

幻读就类似事务A想要插入一个数据的时候，数据B已经插入了，但是事务A无法查询到此时就勒烯幻影

事务的隔离级别

解决并发事务引起的隔离级别
select @@TRANSACTION_ISOLATION; 查看事务的隔离级别
SET [SESSION|GLOABL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITED| READ COMMITTED|REPEDATABLE READ | SERIALIZABLE ] session表示的是当前界面
SERIALITZABLE：串行化就保证这个表一次性只能被一个事务操作所以级别最高