学习笔记-mysql基础

左戍戎

已于 2023-05-21 19:00:35 修改

阅读量107

点赞数

文章标签： mysql 数据库 sql

于 2023-03-01 10:34:57 首次发布

本文链接：https://blog.csdn.net/weixin_48880019/article/details/129125544

版权

注意事项

用单引号‘’（通用），虽然mysql 支持“”。
实现开发中不建议用" * "代替字段查询，效率低。
关系型数据库，表和表之间存在关系。

（一）DQL（数据查询语句）

1.查询语句执行顺序

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

2.查询语句关键字

desc 【表名】；查看表结构
select database();查看当前数据库
as ；起别名（中文名加‘’，如 as ‘中文名’）可以省略
<>; 不等于
between … and …(闭区间，必须要左小右大) 范围
例：

select name , sal from emp where sal between 1000 and 3000;

in (‘A’, ‘B’) ; not in (‘A’, ‘B’)
"%“代表任意多个字符，”_"代表任意一个字符
distinct ; 查询结果去重，只能出现在所有字段最前面
show create table [表名] 查看建表语句
explain select name from t_user where id =12; 执行计划(mysql独有)

3.分组函数(多行处理函数)

自动忽略null
分组函数不能出现在where 子句中（分组函数一般和group by 联合使用，分组函数都是在group by 语句（先）执行之后执行，group by 语句是在where语句执行之后执行 -》先分组，再对组操作，最后对组的数据条件筛选，最后查出来）

4.单行处理函数

有null参加运算结果都为null
ifnull([可能为null的值], [替代null的值])

5.分组

5.1.group by

按某个字段或某些字段分组

当根据多个字段分组时，可以将多个字段看做一个字段。

当一条语句中有qroup by的话，select 后面只能跟分组函数和参与分组的字段。

5.2.having

对分组后的数据进行过滤
1.having 不能与group by 分开使用
2. 对于分组查询，要注意效率，能用where不要用having
例：

select max(sal), deptno from emp group by deptno having max(sal) >= 2900;
select max(sal), deptno from emp where sal >=2900 group by deptno;

6.连接查询

内外连接对表有主次之分

6.1.内连接

等值连接
特点：条件是等量关系，表与表之间是平等的
语法：

sql(99)标准：将连接条件与筛选条件分开
				...
				A
			inner join			//inner可以省略，写上可读性更好
				B
			on 
				连接条件
			where
				...

非等值连接
特点：条件是非等值关系（范围）

				...
				A
			inner join			//inner可以省略，写上可读性更好
				B
			on 
				...
			between 
				...
			and 
				...
			where
				...

自连接
特点：自己连接自己，一张表看成两张表

6.2.外连接

特点：表与表之间是有主次之分。左右连接可以转换

6.2.1左外连接

		... 
		left outer join		//左边（上边）是主表，outer可以省略
			...
		on
			...
		where 
			...

6.2.2右外连接

		... 
		right outer join	//右边（下边）是主表，outer可以省略
			...
		on
			...
		where 
			...

7.子查询

定义：select 语句中嵌套select 语句，被嵌套的select 语句是子查询
语法：

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

8.查询结果集相加 union

特点：可以将相干或不想干的查询列（列数必须相同）相加

# 相干
select  ename, job from emp where job = 'MANAGER'	//查两列
union
select  ename, job from emp where job = 'SALESMAN';	//查两列

# 不相干
select ename, job from emp where job = 'SALESMAN'
union
select job, sal from emp;

9.分页查询 limit

特点：取结果集部分数据。sql语句最后执行的步骤。
语法：

limit startIndex, length
	#startIndex: 起始位置 （不写，默认为0）
	#length：取几个

int pageNo = x;			//页码
int pageSize = y;		//每页条数
limit (x-1)*y, y;		//页码-1差乘以每页条数 = 起始位置

（二）DDL语句

数据类型

在这里插入图片描述

1.创建表

create table [表名] (
字段名1 数据类型约束（default [默认值]），
字段名2 数据类型约束
…
);

2.删除表

drop table if exists [表名] 如果表存在则删除表

3.复制表

create table [表名] as [DQL语句（select …）] 将查询结果集当做表创建出来

(三)DML语句

1.插入语句

1.1.条插入

insert into [表名] (字段1，字段2，…) values(值1，值2，…)，values(值1，值2，…)，values(值1，值2，…)；
要求：字段数量要和值的数量相同，且数据类型要对应。

1.2.批量插入

insert into [表名] DQL语句将查询结果集批量插入到表中

2.更新语句

update [表名] set 字段名=值, 字段名=值 where …
无条件全部更新

3.删除语句

delete from [表名] where… #效率低，用橡皮擦，可回滚
无条件全部删除
truncate table [表名] #效率高，用剪刀剪，只留表头，不可回滚

（四）约束

创建表时，可以给表的字段值添加相应的条件进行约束，目的是保证数据的合法性，有效性，完整性。

1. 常见的约束

unique 唯一约束
not null 非空约束
primary key 主键约束(pk)
foreign key 外键约束(fk)
检查约束 (oracle数据库有，mysql没有）

unique
特点：字段不能重复，但可以为null（null不是值，因此不可以判断是否相同）。

# 列级约束
create table t_user(
	id int ,
	usercode varchar(255) unique,
	username varchar(255) unique
);
# 多个字段添加多个约束，多个字段后加unique。



#表级约束
create table t_user(
	id int ,
	usercode varchar(255) ,
	username varchar(255) ,
	unique(usercode, username)
);
# 多个字段添加一个约束，unique将多个字段看做一个字段，是一个约束。

not null
特点：字段不能为空。只有列级约束，没有表级约束。

3.primary key
特点：字段不能重复也不能为空。一张表的主键约束只能有一个
作用：该表中某条记录的唯一标识。

分类：
- 数量划分：
  - 单一主键。（常用）
  - 复合主键。（多字段联合添加一个主键约束）
- 性质划分：
  - 自然主键。（常用）
  - 业务主键。（如：身份证号。不推荐，业务与主键最好不挂钩）

主键约束前的字段是主键字段，主键字段的值是主键值
主键值自增 auto_increment：从1开始以1 递增。
例：id int primary key auto_increment,

4.foreign key
特点：外键来自某张表的某个字段，可以不是该表主键，但必须有unique,可以为null。
例：foreign key(classcode) references t_class(cid)。classcode字段引用 t_class表中的cid字段，被引用的表是父表。

#父子表删除创建有顺序
#先删子，后删父
drop table if exists t_student;
drop table if exists t_class;
#先建父，再建子
create table t_class(
	cid int auto_increment,
	cnum int ,
	cname varchar(255),
	primary key(cid)
);
create table t_student(
	sid int auto_increment,
	snum int,
	sname varchar(255),
	classcode int,
	primary key(sid),
	foreign key(classcode) references t_class(cid)
);
insert into t_class values(1, 1, '高三一班'),(2, 2, '高三二班');
insert into t_student values(1, 1, 'zs', 1),(2, 2, 'zs', 2);
select * from t_class;
select * from t_student;

(五）存储引擎（了解）

存储引擎就是存储方式，不同存储引擎对应不同的存储方式。

mysql特有，默认是InnoDB。
- 特点：支持事务，行级锁，外键。支持级联删除（删除父表，子表也删除）和级联更新。
mysql最常用的引擎：MyISAM存储引擎。
- 特点：
  - 使用三个文件表示一张表：
    - 1.格式文件 ——存储表结构的定义（mytable.frm)
    - 2.数据文件 ——存储表行的内容 (mytable.MYD)
    - 3.索引文件 ——存储表上索引 (mytable.MYI)
  - 灵活的AUTO_INCREMENT字段处理
  - 可被转换为压缩，只读表来节省空间
  - 不支持事务！

（六）事务

一个事务是一个完整的业务逻辑单元，不可再分。DML语句才需要事务。
多条DML语句同时成功或者同时失败。
特性：ACID

原子性( A )
- 事务最小工作单元
一致性( C )
- 多条DML语句同时成功或者同时失败
隔离性( I )
- 事务与事务之间具有隔离
持久性( D )
- 最终数据必须持久化到硬盘中，事务才算成功结束

1.事务隔离

1.1 隔离级别

第一级别：读未提交(read uncommitted)
- 对方事务还没提交，当前事务可以读取到对方未提交的数据，存在脏读现象，读了脏数据。
第二级别：读已提交（read committed)
- 对方事务提交之后的数据我方可以读取到。解决了脏读现象。存在的问题是：不可重复读（对于最早查询的数据来说）。
第三级别：可重复读（repeatable read)
- 解决了不可重复问题。存在的问题是读取到的数据是幻象。
第四级别：序列化读\串行化读（serializable）
- 解决了所有问题。存在问题是效率低，需要事务排队。

orcale默认隔离级别是：读已提交。
mysql默认隔离级别是：可重复读。

1.2 设置事务隔离级别

设置全局事务隔离级别

 set global transaction isolation level [隔离级别];

查看全局事务隔离级别

 select @@global.transaction_isolation;(新版本)
 select @@global.tx_isolation;（旧版本）

2.mysql事务

特点：默认自动提交
关闭自动提交机制

start transaction；

(七）索引（给某一或某些 !字段！添加索引）

作用：相当于书的目录，可快速找到资源。
特点：提高检索效率，但不能随意添加，因为索引也是数据库中的对象，也要维护(表中数据经常被修改时不适合添加索引，因为数据修改索引要重新排序)。

1.原理

通过B Tree缩小扫描范围
当执行创建索引语句后，会创建出一个索引对象（可能是内存可能是硬盘中，看存储引擎），索引对象会自动排序分区，当执行查询语句时，首先会查该字段是否有索引，通过条件进行分区定位，缩小检索扫描的范围，定位到具体记录，并且带有该记录在磁盘中的物理地址XXX，然后执行select …where 物理地址=XXX，即可快速检索。

2.添加索引条件:

数据量庞大
该字段DML（修改）操作少
该字段经常出现在where子句中
主键和unique约束的字段会自动添加索引。

3.创建索引

create index t_user_name_index on t_user(name);
创建         索引              在  表     字段

4.删除索引

drop index [索引名] on [索引所在表名]

5.索引失效

select id, name from t_user where name like ‘%A%’;
模糊查询时，第一个通配符使用%，这时索引失效。

(八）视图

什么是视图？
用不同的角度来看待数据。
个人理解，将表结构再次抽象出来重新命名（表，字段）并可视化，对于表中的数据隐藏，增加了数据的安全性

1.创建视图

只有DQL语句才可以将视图创建出来

create view myview as select name, id from t_user;

2.删除视图

drop view myview;

3.crud

视图也可以crud，但对视图操作会影响到原表数据

DBA命令

1.导出

1.1 导出整个库

在windows的dos命令窗口中执行

mysqldump [数据库名]>[导出文件路径] [用户名] [密码]

1.2 导出指定表

mysqldump [数据库名] [表名]>[导出文件路径] [用户名] [密码]

2. 导入

create database [数据库名];
use [数据库名];
source [路径]

（九）数据库操作

1. 新建数据库

create database [数据库名];

2. 删除数据库

drop database [数据库名];

（十）数据库设计三范式

设计表的依据。如此设计不会出现冗余。

第一范式
- 表有主键，每个字段不可再分
第二范式
- 建立在第一范式，非主键字段完全依赖主键，不可部分依赖
第三范式
- 建立在第二范式，非主键字段直接依赖主键，不可传递依赖

表设计口决

一对多，两张表，多的加外键
多对多，三张表，关系表两外键
一对一，若两张表，1主键共享，2外键唯一

实现开发中客户是上帝，有时拿冗余换执行速度

左戍戎

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
学习笔记-mysql基础

mysql基础
复制链接

扫一扫