MySQL总结

最新推荐文章于 2024-11-01 11:00:05 发布

呆小呆啊

最新推荐文章于 2024-11-01 11:00:05 发布

阅读量165

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/m0_47073109/article/details/119882554

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1. MySQL总结

1.1 SQL的分类

数据查询语言: (DQL)select
数据库操作语言: (DML)insert,delete,update
数据定义语言: (DDL)create,drop,alter
事务操作语言: (TCL)commit,rollback
数据控制语言:(DCL)grant,revoke

1.2 命令导入数据

source <sql脚本路径>  # source D://a.sql

1.3 简单命令

1.3.1 查看数据库版本

mysql --version
mysql -V

1.3.2 创建数据库

cerate datebase 数据库名;  #create database test;

使用数据库

use 数据库名; #use test

1.3.3 查看使用的数据库

select database();
# 查看数据库版本
select version();

1.3.4 查看所有数据库和查看当前库中所有的表

show databases; #查看所有数据库
show table; #查看库中所有的表

1.3.6 查看表结构

desc 表名;

1.3.7 查看创建表的语句

show create table 表名;

1.4 查询(DQL)

1.4.1 条件查询
条件查询需要用到where 语句,where 必须放到from 语句表的后面支持如下运算符
distinct 去除重复行

运算符	说明
=	等于
<>或者!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between…and,	两者之间,等同于>=and <=
is null	为 null (is not null不为空
and	并且
or	或者
in	包含,相当于多个or(not in 不在这个范围中)
not	not 可以取非,主要用在is或in中
like	like 称为模糊查询,支持%或下划线匹配 ,%匹配任意个字符下划线, 一个下划线匹配一个字符

1.4.2 排除数据
单一字段排序: 排序采用order by 子句,order by后面跟上排序字段,排序字段可以放多个,多个采用逗号间隔,order by 默认采用升序,如果存在where 子句那么order by 必须放到where 语句的后面
多个字段排序: order by 后面紧跟哪个字段,哪个字段就先排,依次向下排序
1.4.3 数据处理函数/单行处理函数

Lower	转换小写
upper	转换大写
substr	取子串(substr被截取的字符串,起始下标,截取的长度)
length	取长度
trim	去空格

str_to_date	将字符串转化为日期
date_format	格式化日期
format	设置前分位
round	四舍五入
rand()	生成随机数
Ifnull	可以将null转化为一个具体的值

select lower(字段) from 表名;#将该字段查出来的数据全部转换为小写

1.4.4 case…when…then…else…end
当员工的工作岗位是manager 的时候,工资上调10%,当工作岗位是salesman的时候,工资上调50%,其他不变

select 
  ename,job,sal as oldsal,
  (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 
from 
	emp;

1.4.5 ifnull

select ifnull(字段名,0) from 表名;  #表示该字段如果为空就替换为0

在SQL 语句当中若有NULL值参与数学运算,计算结果一定是NULL为了防止计算结果出现NULL,建议先使用ifnull空值处理函数预先处理.
1.4.6 分组函数/聚合函数/多行处理函数

函数	使用
max	取某个字段的最大值
min	取某个字段的最小值
sum	取某个字段的总和
count	取得记录数
avg	娶某个字段的平均数

1.4.7 分组查询

分组查询主要涉及两个子句:group by 和 having

原则:能在where 中过滤的数据,尽量在where 中过滤,效率较高.having的过滤是专门对分组之后的数据进行过滤的.

select 字段
from 表名
where ....
group by ....
having ....(就是为了过滤分组后的数据而存在的-不可单独出现)
order by ...

1.4.8 连接查询
连接分类: 连接分为内连接和外连接
内连接: 表 1 inner join 表 2 on 关联条件做连接查询的时候一定要写上关联条件 inner可以省略
**外连接:**外连接分为左外连接和右外连接
左外连接: 表 1 left outer join 表 2 on 关联条件做连接查询的时候一定要写上关联条件 outer 可以省略
右外连接: 表 1 right outer join 表 2 on 关联条件做连接查询的时候一定要写上关联条件outer 可以省略
左外连接和右外连接的区别: 左外连接以左表的表为准和右边的表进行比较,和左边的表不相等都会显示出来,右表符合条件的显示,不符合条件的就不显示; 右外连接正好相反.

1.4.9子查询
子查询就是嵌套的select语句,可以理解为子查询就是一张表
where 子查询: 在where 语句中加入select 语句

select * from 表名 where id ={select id from 表名}

from 子查询: 在from语句中使用子查询

select * from 表名 join (子查询) on 条件

select 子查询: 在select语句中使用子查询

select 字段 ,(子查询) from 表名 字段

1.4.10 union和limit
union: 表示可以合并的集合(相加)

select * from student where hight > 170 union select * from student where hight < 155
# 查询hight大于170和hinght小于155的学生信息

limit: 主要用于提取前几条或者中间某几条数据

select * from 表名 where 条件 limit 0 5   # 拿取从第一条数据为始的5条数据

表

1.5.1 创建表
创建表的时候,表中有字段,每一个字段有:

字段名
字段数据类型
字段长度限制
字段约束

mysql常用数据类型：

类型	描述
char(长度)	定长字符串,存储空间大小固定,适合作为主键或外键
varchar(长度)	变长字符串,存储空间等于实际数据空间
double(有效数字位数,小数位)	数值型
Float(有效数字位,小数位)	数值型
int(长度)	整型
bigint(长度)	长整型
Date	日期型
biob	Binary Large Object(二进制大对象)
其他类型…

1.5.2 增加/删除/修改表结构
添加字段

alter table 表名 add 字段名 字段类型(长度);

修改字段

alter table 表名 modify 字段名 字段类型(长度);

删除字段

alter table 表名 drop 字段名;

1.5.3 数据库的增删改查
插入数据 (insert)

insert into 表名(字段名,字段名,....,字段名) value (字段值,字段值,....,字段值)

更新数据(update)

update 表名 set 字段名=字段值,...字段名=字段值 where 条件

删除数据(delete)

delete from 表名 where 条件

1.5.4 创建表加入约束

常见的约束

非空约束 not null
唯一约束 unique
主键约束 primary key
外键约束 foreign key
自定义检查约束,check (不建议使用) (在mysql中现在还不支持)

建立学生表

create table student(
	id int primary key,   #int类型的长度限制可以省略不写  id作为主键
	student_id  char(11) not null unique,  #学生学号，唯一且不为空
	name varchar(50)  not null,   #学生姓名，不为空
	cno int, #外键 班级id
	foreign key(cno) references class(id),  #将班级表class的主键id作为该表的外键
	unique(student_id,name)  #学生学号和姓名联合起来不唯一 约束没有添加在列的后面，这种约束被称为表级约束。
)

1.5.5 增加/删除/修改表约束
1.5.5.1 增加约束
添加外键约束

alter table 从表 add constraint 约束名称 foreign key 从表(外键字段) references 主表(主键字段);

添加主键约束

alter table 表 add constraint 约束名称 primary key 表(主键字段);

添加唯一性约束

alter table 表 add constraint 约束名称 unique 表(字段);

1.5.5.2 删除约束
删除外键约束

alter table 表名 drop foreign 外键字段;

删除主键约束

alter table 表名 drop primary key;

1.5.5.3 修改约束,其实就是修改字段

alter table 表名 modify 字段名 字段类型(长度) 约束;
# mysql 对有些约束的修改时不支持,所以我们可以先删除,再添加

存储引擎

1.6.1 存储引擎的使用

数据库中的各表均被(在创建表时)指定的存储引擎来处理
服务器可用引擎依赖于以下因素
- MySQL 的版本
- 服务器在开发时如何配置
- 启动选项
为了解当前服务器中有哪些存储引擎可用,可用使用SHOW ENGINES 语句查看

 SHOW ENGINES\G

在创建表时,可使用ENGINE 选项为CREATE TABLE 语句显示指定存储引擎

CREATE TABLE 表名(NOINT) ENGINE =MyISAM;

如果在创建表时没有显示指定存储引擎,则该表使用当前默认的存储引擎
默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定
现有表的存储引擎可使用ALTER TABLE语句来改变

ALTER TABLE 表名 ENGINE = INNODB;

为确定某表所使用的存储引擎,可以使用SHOW CREATE TABLE 或 SHOW TABLE STATUS语句

SHOW CREATE TABLE 表名\G
SHOW TABLE STATUS LIKE '表名'\G

1.6.2 常见的存储引擎
1.6.2.1 MyISAM 存储引擎

MyISAM是Mysql最常见的引擎
它管理表具有以下三个特征:
- 使用三个文件表示每个表
  - 格式文件 :存储表结构的定义(mytable.frm)
  - 数据文件: 存储表行的内容(mytable.MYD)
  - 索引文件: 存储表表上的索引(mytable.MYI)
- 灵活的AUTO_INCREMENT 字段处理
- 可被转化为压缩,只读来节省空间

1.6.2.2 InnoDB 存储引擎

InnoDB存储引擎是MySQL的缺省引擎
它管理的表具有下列主要特征:
- 每个InnDB 表在数据库目录中以.frm 格式文件表示
- InnoDB 表空间tablespace 被用于存储表的内容
- 提供一组用来记录事务性活动的日志文件
- 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
- 提供全 ACID 兼容
- 在 MySQL 服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新

1.6.2.3 Memory存储引擎

使用MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得MENORY存储引擎非常快
MEMORY 存储引擎管理的表具有下列的特征
- 在数据库目录内,每个表均以.frm格式的文件表示
- 表数据及索引被存在内存中
- 表级锁机制
- 不能包括TEXT和BLOB字段
Memory 存储引擎以前被称为HEAP引擎

1.6.3 如何选择存储引擎

MyISAM:适合大量数据读而少量数据更新的混合操作.MyISAM表的另一种适用情形是使用压缩的只读表
InnoDB:如果包含较多的数据更新操作,那么就一个使用InnoDB.其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制
Memory:存储非永久性需要的数据,或者是能够从基于磁盘表中重新生成的数据

1.7事务

1.7.1 概述
事务可以保证多个操作的原子性,要么全部成功,要么全部失败.对应数据库来说事务保证批量的DML操作要么全部成功要么全部失败,事务有四个特征ACID

原子性(Atomicity): 整个事务中的全部操作,必须作为一个单元全部完成(或者全部取消)
一致性(Consistency):在事务开始之前和结束之后,数据库都保持一致
隔离性(Isolation):一个事务不会影响其他事物的运行
持久性(Durability):在事务完成后,该事务对数据库所有的更改都会被持久的保存在数据库中,不会被回滚

事务中存在一些概念
事务(Transaction):一批操作(一组DML操作)
开启事务(start transaction 或者 begin)
回滚事务(rollback)
提交事务(commit)

**SET AUTOCOMMIT： **禁用事务或者启用事务的自动提交模式
执行DML语句就是开启一个事务
关于事务的回滚: 只能回滚insert,update和delete语句,不能回滚select(回滚select没有意义),也不能回滚,create,drop和alter语句
事务只对DML语句有效

注意：rollback，或者commit后事务就结束了