数据库相关(mysql)

谁借莪１个温暖的怀抱￠ 2021-11-22 03:22 408阅读 0赞

1.表A inner join(内连接) 表B 无连接条件  
遵循笛卡尔乘积，交叉相乘，如果A表5条记录，B表6条记录，结果为30记录  
表A left join(左外连接) 表B on 连接条件  
表A为主表，表B为从表，结果为表A的所有数据以及其交集部分  
表A left join(右外连接) 表B on 连接条件  
表A为从表，表B为主表，结果为表B的所有数据以及其交集部分  
2.分页查询 limit offset,length  
offset从0开始计算  
返回结果为从索引为offset的记录开始的length条记录  
3.distinct关键字 去除结果集中重复的记录  
4.ifnull(a,b)函数  
如果a为null，返回b  
题目：编写一个 SQL 查询，获取 Employee 表中第二高的薪水SecondHighestSalary，如果不存在第二高的薪水，那么查询应返回 null。  
select ifnull((select distinct salary from employee order by salary desc limit 1,1),null) as SecondHighestSalary ;  
5.select xxx from xxx where xxx not in (select xxx); 子查询  
6.如果要删除一个表的所有记录  
delete from 表名; 不推荐使用，有多少条记录就会执行多少次删除操作，效率低  
truncate table 表名; 先删除表，然后再创建结构一样的空表，推荐使用  
delete 可以回滚，truncate不能回滚  
7.count(\*):总记录数 count(字段): 字段不为null的值的数目  
一般写count(主键)  
8.分组查询的字段：分组字段、聚合函数  
where后面不能跟聚合函数，having后面可以进行聚合函数的判断  
9.通配符  
\_ ：仅替代一个字符  
%：替代零个或多个字符

10.alter  
修改表名　　　　　　 alter table 表名 rename to 新表名;  
修改表的字符集 　　　alter table 表名 character set 字符集;  
添加列 　　　　　　　 alter table 　　表名 add 列 类型 约束；  
修改列名+类型+约束 　alter table 表名 change 列名 新类名 新类型 新束；  
修改列类型+约束 　　alter table 表名 modify 列名 新类型 新约束;  
删除列 　　　　　　　alter table 表名 drop 列名;

11.表外键  
constraint 外键名称 foreign key (外键列名称) references 主表名(主表列名称);  
删除外键  
alter table 表名 drop foreign key 外键名称;  
添加外键  
alter table 表名 add constraint 外键名称 (外键列名称) references 主表名(主表列名称);  
添加级联更新，级联删除  
alter table 表名 add constraint 外键名称 (外键列名称) references 主表名(主表列名称) **on update cascade on delete cascade**;  
如果添加了级联更新/级联删除之后，更新/删除主表中的数据，从表中与之相关的数据会被自动删除

12.数据库表关系以及设计  
一对一(1:1)：合成一张表，或者可以在任意一方添加外键指向另一方的主键，并设置外键唯一(unique)  
一对多(1:n)：在n的一方建立外键，指向1的一方的主键  
多对多(m:n)：借助第三张中间表，中间表至少包含两个字段(两张表的主键) 联合主键

13.数据库备份与还原  
备份：mysqldump -u账号 -p密码 数据库名 > 路径(d://a.sql)  
还原：登陆–>建库–>使用库–>执行文件 source 路径(d://a.sql)

14.子查询  
单行单列：子查询可以作为运算符右边的单值进行判断 (salary > (子查询) )  
多行单列：子查询可以与 IN/NOT IN 、ANY/SOME、ALL进行搭配使用 (salary in (子查询))  
多行多列：此时子查询可以作为一张虚拟表使用 select \* from 表A inner join (子查询) on 连接条件

15.**事务是由一条或多条SQL语句组成，要么全都执行，要么全都不执行**  
START TRANSACTION;  
多条SQL语句；  
COMMIT / ROLLBACK；  
mysql 默认自动提交(autocommit=1) ，oracle 手动提交  
例如一条更新语句末尾自动加了commit -->自动提交,不然数据的改变都是临时的

--------------------

**事务的四大特征**  
原子性：不可分割的最小单位， 事务中的操作要么都发生，要么都不发生  
一致性：事务操作前后，数据的总量不变，类似于守恒定律  
隔离性：并发执行的各个事务不能相互干扰  
持久性：一个事务一旦提交，它对数据库中的数据的改变是永久性的

--------------------

**当多个事务访问数据库的相同数据时，没有采取必要的隔离机制，就会导致并发问题**  
脏读：一个事务，读取了另一个事务没有提交的数据  
不可重复读：在同一个事务中，两次读取到的数据不一样(第一个事务回滚了)  
幻读：一个事务操作数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

--------------------

四种隔离机制　　　　　　　　　　出现问题  
read uncommitted　　　　　　脏读，不可重复读，幻读  
read committed　　　　　　　　不可重复读，幻读  
repeatable read　　　　　　　　　　幻读  
serializable　　　　　　　　　　无问题，但性能低下  
MySQL默认为repeatable read,Oracle默认为read committed

16.DCL：管理用户，授权

#### 管理用户(基于mysql数据库的user表) ####

**查询用户**  
USE mysql ;  
SELECT \* FROM USER;  
**添加用户**  
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;  
本机:主机名localhost  
所有主机:主机名%  
测试 mysql -u用户名 -p密码;  
**删除用户**  
DROP USER ‘用户名’@‘主机名’;  
**修改用户密码**  
方式一  
UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;  
方式二  
SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);

#### 授权 ####

**查询用户权限**  
SHOW GRANTS FOR ‘用户名’@‘主机名’；  
**授予权限**  
（1）授予用户对某个数据库的某个表的某个操作权限  
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;  
权限列表有SELECT、 INSERT、 UPDATE、 DELETE 等等  
（2）给用户授予所有权限  
grant all on \*.\* to ‘用户名’@‘主机名’  
**撤销权限**  
revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;