自学内容网 自学内容网
自学内容网 > 正文

MySQL(三)

🕗 发布于 2024-07-18 08:29 mysql  数据库  

一、mysql多表查询

  多表查询是指两个或两个以上的表查询。假设有以下几张表：

//查找量表中的所有记录当Student表的sno=SC表的sno时
select * from Student,SC
where Student.sno=SC.sno;

自连接是指在同一张表的连接查询【将同一张表看作两张表】

mysql表子查询：子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

//查询和李勇性别相同的学生的所有信息
select*from Student
where gender={
select gender from Student
where sname='李勇'
}
//找出性别为女的学生选择的所有科目
select distinct major
from Student
where gender='女';
//显示所有女生选择的专业的所有科目
select*from Student
where major in {
select distinct major
from Student
where gender='女'
}

在多行子查询中使用all操作符：

//找出SC表中学生21012101比学生21012102所有成绩都高的科目
select*from Student
where sno='21012101' and grade>all(
select grade from Student
where sno='21012102'
)

select*from Student
where sno='21012101' and grade>(
select max(grade) from Student
where sno='21012102'
)

在多行子查询中使用any操作符:

如何显示成绩比21012102的其中一个成绩高的记录
select*from Student
where sno='21012101' and grade>any(
select grade from Student
where sno='21012102'
)

select*from Student
where sno='21012101' and grade>(
select min(grade) from Student
where sno='21012102'
)

多列子查询：多列子查询是指查询返回多个列数据的子查询语句。

//请查询所有与李勇性别和选课完全相同的学生。
select*from Student
where(gender,major)={
select gender,major
from Student
where sname='李勇'
}

二、合并查询

为了合并多个select语句的结果，有时候可用集合操作符号union、union all

1.union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。

2.union：此操作符与union all类似，但会自动去掉结果集中的重复行。

三、表外连接

1.左外连接（如果左侧的完全显示就是左外连接）

显示所有学生的学号、姓名以及他们的选课情况（左侧完全显示，右侧没有信息则为null）

select Student.sno,Student.sname,SC.cno
from Student left join SC
on Student.sno=Sc.sno;

2.右外连接（如果右侧的完全显示就是右外连接）

select Student.sno,Student.sname,SC.cno
from SC right join Student
on Student.sno=Sc.sno;

四、mysql约束

         约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null、unique、primary key、foreign key和check五种。

1.primary key(主键)

primary key不能重复且不能为null

一张表最多只能有一个主键，但可以是复合主键

逐渐的指定方式有两种

直接在字段名后制定：字段名 primary key

在表定义最后写primary key(列名)

使用desc表名，可以看到primary key地情况

在实际开发中，每个表往往都设计一个主键。
2.not null（非空）

如果在列上定义了not null，那么当插入数据时，必须为列提供数据。

3.unique（唯一）

当定义了唯一约束后，该列值是不能重复的。

如果没有指定not null，则unique字段可以有多个null

一张表可以有多个unique字段

4.foreign key（外键）

用于定义主表和从表之间的关系，外键约束要定义在从表上，主表必须具有主键约束或是unique约束，当定义外键约束后，要求外键数据必须在主表的主键列存在或是为null

foreign key(本表字段名)references

主表名(主键名或unique字段名)

细节：外键指向的表的字段，要求是primary key或者是unique

表的类型是innodb，这样的表才支持外键

外键字段的类型要和主键字段的类型一致（长度可以不同）

外键字段的值，必须在主键字段中出现过，或者为null

一旦建立主外键的关系，数据不能随意删除。

5.check

用于强制行数据必须满足的条件

sex varchar(6) check(sex in('man','woman'))

五、自增长

在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始，自动的增长。

字段名 整型 primary key auto_increment

添加自增长的字段方式：

insert into xxx（字段1，字段2......）values(null,'值'......);

自增长默认从1开始，你也可以通过如下命令修改：alter table 表名 auto_increment=新的开始值。

六、索引

1.添加索引：

create [unique] index index_name on tabl_name(col_name[(length)] [asc|desc],......);

alter table table_name add index [index_name] (index_col_name,......)

2.添加主键（索引）alter table 表名 add primary key(列名,...);

3.删除索引

drop index index_name on tbl_name;

alter table table_name drop index index_name;

4.删除主键索引：alter table t_b drop primary key;

5.查询索引（三种方式）

show index(es) from table_name;

show keys from table_name;

desc table_name;

七、mysql事务

        事务是用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。

        事务和锁：当执行事务操作时（dml语句），mysql会在表上加锁，防止其他用户修改表的数据。

mysql数据控制台事务的几个重要操作

  • start transaction  --开始一个事务
  • savepoint 保存点名   --设置保存点
  • rollback to 保存点名  --回退事务
  • rollback  --回退全部事务
  • commit  --提交事务，所有操作生效，不能回退

细节：

  • 如果不开始事务，默认情况下，dml是自动提交的，不能回滚
  • 可以在事务没有提交前，选择回退到哪个保存点

事务隔离级别

介绍：

  1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
  2. 如果不考虑隔离性，可能会引发：
  • 脏读：当一个事务读取另一个事务尚未提交的改变时产生脏读；
  • 不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读；
  • 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

事务的acid特性

  1. 原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  2. 一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态。
  3. 隔离性：事物的隔离性是多个用户并发访问数据库时，数据为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
  4. 持久性：持久性指一个事务一旦被提交，它对数据库中数据的改变就是持久性的，接下来即使数据库发生故障也不应该对其有任何影响。

八、mysql表类型和存储引擎

  1. mysql的表类型由存储引擎决定，主要包括MyISAM、innoBDB、Memory。
  2. mysql数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB。
  3. 这六种又分为两类，一类是“事务安全型”，如：InnoDB；其余都属于第二类，称为“非事务安全型”；

细节：

  • MyISAM不支持事务，也不支持外键，但其访问速度快，对事务完整性没有要求
  • InnoDB存储引擎提供了具有提交，回滚和崩溃恢复能力的事务安全。但比起MyISAM存储引擎，InnoDB效率差一些并且会占用更多的磁盘空间以保留数据和索引。
  • MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦mysql服务关闭，表中的数据就会丢失掉，表的结构还在。

九、视图

基本概念

  1. 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表。
  2. 通过视图可以修改基表的数据。
  3. 基表的改变也会影响到视图的数据。

视图基本使用

  1. create view 视图名 as select语句
  2. alter view 视图名 as select 语句  --更新成新的视图
  3. show create view 视图名
  4. drop view 视图名1，视图名2 

十、mysql管理

mysql中的用户，都存储在系统数据库mysql中user表中

其中user表的重要字段说明：

1.host：允许登陆的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址

2.user：用户名

3.authentication_string：密码，是通过mysql的password()函数加密之后的密码。

创建用户：

create user '用户名' @ '允许登陆位置' identified by '密码';

删除用户：

drop user '用户名' @ '允许登陆位置';

用户修改密码：

set password=password('密码')；（修改自己的密码）

set password for '用户名'@'登陆位置'=password('密码')；

给用户授权：

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置'；

回收用户授权：

revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

权限生效指令:

flush privileges;


原文地址：https://blog.csdn.net/weixin_73975059/article/details/139774508

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024