MySQL入门学习

MySQL入门学习

MySQL常用命令

查看MySQL版本 mysql --version
创建数据库 created database 库名
查看所有数据库 show database
进入数据库 use 库名
查询当前使用的数据库 show database()
查看当前库中所有表 show tables
查看其他库中的表 show tables from <database name>
查看表结构 desc 表名
查看表创建语句 show created table <table name>
终止一条语句 ctrl + c
退出MySQL exit

MySQL常用关键字

关键字	说明	例句
distinct	将查询结果去重	select distinct name from user查询名字，不保留重复项
union	将多个sql语句的查询结果合并，其中列名和列数要相同	select name from user union select name from student
limit 起始位置,数量	将查询结果分页	select * from user limit 0,10查询从0开始的十条数据

MySQL查询语句

select 字段 from 表名 where 条件 group by 分组字段 order by 排序字段

简单查询

查询表中所有数据select 字段名 from 表名

条件查询

select 字段名 from 表名 where 条件
where关键字必须放在表名后面，条件支持如下运算符

运算符	说明	示例
=	等于	select * from user where name = 'jack'
<> 或 !=	不等于	select * from user where name <> 'lucy'
<	小于	select * from user where age <15
<=	小于等于	select * from user where age <=15
>	大于	select * from user where age >15
>=	大于等于	select * from user where age >=15
between…and…	两个值之间	select * from user where age between 10 and 15
is null	为null（is not null不为null)	select * from user where job is null
and	并且	select * from user where age >15 and job = 'coding'
or	或者	select * from user where name = 'jack' and name = 'lucy'
in	包含，相当于多个or	select * from user where name in ('jack','lucy')
not	not取非，可以用在is null 和in中	select * from user where job is not null
like	模糊查询，支持% _,%匹配任意一个字符，下划线，一个下划线匹配一个字符	select * from user where name like ‘_a%'匹配名字中第二个字母为a的

排序查询

使用字段名排序

select * from user order by age desc
通过年龄降序排序
将需要排序的字段放在order by后面可以实现排序查询
在字段后面添加desc表示降序，不写默认为升序，可以写asc表示升序
可以写多个字段，表示通过第一个字段排序，如果第一个字段相同，则使用第二个字段排序

使用字段所在位置排序（不常用）

select name,age,job from user order by 1
表示使用第一个字段进行排序，但是如果前面的字段换了位置，或者换一个字段，这个排序就不是按照我们所想的方式排序了。

分组查询

select * from user group by job
通过工作分组
将字段放在group by后面可以实现分组查询，

单行处理函数

单行处理函数表示对每一行的数据都使用这个函数来处理其中的数据，
可以使用在select 后面，表示返回的结果需要通过函数处理
也可以使用在 where后面，表示通过该函数处理后的结果需要匹配某些条件

lower(字段)

将括号里的字段转换成小写

upper(字段)

将括号里的字段转换成大写

substr(字段，起始下标，截取长度）

截取字符串，起始下标从1开始

length(字段)

获取当前字段中数据的长度

trim(字段名)

去除前后空格

str_to_date（字段）

将字符串转换成日期类型（注：该字符串的格式需要匹配到某一种日期格式）

dateformat(字段，表达式）

将日期通过表达式转换成字符串
常见表达式%Y-%m-%d %H:%i:%s可以任意排列组合
%Y年
%m月
%d日
%H时
%i:分
%s秒

now()

获取当前时间

format（字段名，小数位)

格式化数字，保留几位小数

round(字段名)

将该字段四舍五入

rand()

生成 0到1的随机数

case 字段名 when 条件 then 结果 else 没有匹配到条件返回的结果 end

匹配该字段，如果满足条件，然后then中的结果，如果没有匹配到，则返回else中的结果
when … then … 可以写多个
语句必须有end结束符

ifnull(字段，默认值）

判断该字段中的数据是否为null，如果时null则返回默认值

多行处理函数

多行处理函数又叫做聚合函数，如果在没有分组的情况下使用，所得到的数据将只有一条
需要group by之后才能，否则得到得数据将没有意义
多行处理函数不能放在where后面使用，因为sql语句的执行顺序为，from --> where --> group by --> select
可以将函数放在having后面使用

常用的多行处理函数

函数	说明
count	取得记录数
sum	求和
avg	平均值
max	最大值
min	最小值

select语句的执行顺序

select 字段 
from 表名 
where 条件
group by 字段 
having 条件(聚合函数条件，需要个group by 同时出现)
order by 字段

以上语句的执行舒徐

1. 首先执行 where语句过滤的原始数据
2. 执行group by进行分组
3. 执行having对分组数据进行操作
4. 执行select选出数据
5. 执行order by排序
   原则：能使用where中过滤的数据，尽量在where中过滤，效率较高，having专门用来过滤分组之后的数据

连接查询

通常数据库中的一张表无法完美的存放我们想要的数据，所以数据会在A表中存一部分，在B表中存一部分。
这样做一方面可以将数据存放完成；
另一方面可以将多条数据中相同的部分存放在同一张表中，从而节省数据库的空间
此时我们想要同时获取两张表的数据，就需要用到连接查询

SQL92

select * from user,class
在from关键字后面写需要关联的两个或多个表可以实现连接查询，但是这样查询出来的结果会是两个表数据量的乘积（笛卡尔积查询）
所以需要在where后面增加条件，表示两张表的关联条件（这里的条件可以是等于，也可以是范围）
select * from user,class where user.class_id = class.id

SQL99（推荐使用）

上面是sql92中的做法，这样有个弊端，
如果我们想在连接查询的结果再进行条件过滤的话，所有的查询条件都会出现在where后面，这是的sql语句阅读起来会相对困难
所有在SQL99中使用了join关键字来进行分离，使用 on关键字来表示两张表的关联关系，如果需要在后面增加条件字节添加where即可

select * 
from user 
join class 
on user.class_id = class.id
where name = 'jack'

连接查询之内连接

内连接，只显示匹配条件的数据

select * 
from user 
inner join class
on user.class_id = class.id

其中的inner关键字可以省略

连接查询之外连接

业务场景：显示全部学生信息，如果有班级的显示班级

select * 
from student
left outer join class
on student.class_id = class.id

其中outer关键字可以省略
外连接可以为左外连接和右外连接
简单的理解就是，在如果join关键字左边表的信息要全部显示，就使用left join
如果join关键字右边表的信息要全部显示，就使用right join

子查询

将一组查询结果当作表，在这个查询结果的基础上进行第二次的筛选查询，这种查询称之为子查询

select * 
from (select * from user where sex = '男') a
where name like 'j%'
-- 查询所有名字以j开头的男性

可以在 from后面使用子查询，表示将查询结果进行再一次的筛选，

select * 
from user
where name not in (select name from user where sex = '男' and name like 'j%')
-- 查询所有名字不以j开头的男性

可以在where后面使用子查询，表示查询查询条件要匹配子查询

MySQL创建表

create table tableName(
	columnName dataType(length),
	.....
	columnNameN dataType(length)
);
set character_set_results = 'gbk'

创建表时，每一个字段都有‘字段名’，‘字段数据类型’，‘字段长度限制’，‘字段约束’

MySQL常用数据类型

类型	描述
char(长度)	定长字符串，存储空间大小固定，适合字段长度固定时使用
varchar(长度)	可变长度字符串，存储空间等于实际数据空间，当数据长度不固定时使用
double(有效数字位数，小数位）	数值类型
float（有效数字位数，小数位）	数字类型
int（长度）	整数
bigint（长度）	长整型
date	日期类型
blob	二进制大对象，一般用来存储视频，图片等文件的二进制代码，Java中通过io流转换
clob	字符大对象，存储长字符串（例如一篇作文）
其他	…

创建学生表

create table t_student(
	student_id int(30),
	student_name varchar(20),
	sex car(2),
	birthday date,
	email varchar(30),
	class_id int(30)
);

数据增删改

新增数据

关键字 insert
新增语句格式
insert into tableName (columnName,...)values ()
新增语句示例
insert into t_student (student_id,student_name,sex,birthday,email,class_id) values (1,'jack','男',1996-08-29,'jack@email.com',10);
新增语句中的列明和字段值顺序和数量必须完全对应，
字段名可以少写，表示插入语句中只用对应列的一些数据
insert into t_student(student_name,sex) values ('lucy','女');
字段名也可以不写，当字段名为空时后面的值表示查询的数据是所有字段，values中的字段数量和顺序和创建表时的数量顺序相同，否则数据会出现错误
insert into t_student values(2,'lala','女',1998-09-12,'lala@email.com',20);

修改数据

update 表名 set 字段1 = 修改的值，字段2 = 修改的值 where 条件
将匹配条件的数据修改，如果where后面不写任何条件，表示整个表进行修改
修改时可以增量修改，例如，将所有员工的工资加100
update emp set sal = 100+sal

删除数据

delete from 表名 where 条件
将匹配条件的数据删除，如果where后面不写任何条件，表示删除整个表的数据

表字段的增删改

表创建好之后，如果相对字段进行一下修改或者删除，可以使用关键字alter

增加字段

在学生表中增加电话字段，数据类型为varchar(11)
alter table t_student add phone_number varchar(11);

修改表字段

业务表更，学生表中的性别要保存英文，char(2)不够用了
将学生表中的性别字段更改为varchar(10)
alter table t_student modify sex varchar(10)

删除字段

不需要表中的Email字段了，将字段删除
alter table t_student drop email;

添加约束

表约束可以在创建表的时候添加，也可以在之后修改表字段时添加表约束

常见的表约束

1. 非空约束：not null
2. 唯一约束：unipue
3. 主键约束：primary key
4. 外键约束：foreign key
5. 自定义检查约束：check（不建议使用，在MySQL中不支持）

添加非空约束

在创建表学生表时给id字段添加非空约束

-- 先删除表（如果表存在的话）
drop table if exists t_student;
create table t_student(
	id int(30) not null,
	name varchar(20),
	sex varchar(10),
	brithday date
)

添加唯一约束

alter table t_student modify id int(30) unipue;

添加主键约束

当一个字段即有唯一约束，又有非空约束时表示该字段为主键

-- 先删除表（如果表存在的话）
drop table if exists t_student;
create table t_student(
	id int(30) primary key,
	name varchar(20),
	sex varchar(10),
	brithday date
)

添加外键约束

两张表有关联时可以添加外键约束，这样可以在添加数据时校验，如果另一张表中没有可以关联的主键添加数据就会报错
外键约束是和另一张表中的主键进行关联，所以需要确定另一张表的主键

drop table if exists t_classes;
create table t_classes(
	classes_id int(3),
	classes_name varchar(40),
	-- 通过表级约束创建约束
	constraint pk_classes_id primary key(classes_id)
)

drop table if exists t_student;
create table t_student(
	student_id int(10),
	student_name varchar(20),
	sex char(2),
	birthday date,
	email varchar(30),
	classes_id int(3),
	-- 为学生表添加主键
	constraint student_id_pk primary key(student_id),
	-- 为学生表添加外键约束，关联班级表中的班级id
	constraint fk_classes_id foreign key(classes_id) references 	t_classes(classes_id) 
)

添加完外键约束的两张表，在创建表，删除表，添加数据，删除数据时必须遵循一定的顺序，否则会报错

1. 创建班级表—父表
2. 创建学生表—子表
3. 添加班级表中数据—父表数据
4. 添加学生表中数据—子表数据
5. 删除学生表中数据—子表数据
6. 删除班级表中数据—父表数据
7. 删除学生表—子表
8. 删除班级表—父表

MySQL存储引擎

查询存储引擎
>SHOW ENGINES\G
可以在创建表时添加存储引擎
create table table_name (id int(10)) ENDINE = MyISAM

MyISAM存储引擎（了解）

MyISAM 存储引擎是 MySQL 最常用的引擎。
它管理的表具有以下特征：
使用三个文件表示每个表：

• 格式文件 — 存储表结构的定义（mytable.frm）
• 数据文件 — 存储表行的内容（mytable.MYD）
• 索引文件 — 存储表上索引（mytable.MYI）
  灵活的 AUTO_INCREMENT(字段值自增) 字段处理
  可被转换为压缩、只读表来节省空间

InnoDB存储引擎

MySQL中默认的存储引擎，最重要的一点是，这种存储引擎支持事务控制，

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

MySQL事务控制

事务的四个特性

A: 原子性
说明事务的最小的工作单元。不可再分
C：一致性
所有事务要求，在同一个事务当中，所有惭怍必须同时成功，或者同时失败，以保证数据的一致性。
I：隔离性
A事务和B事务之间具有一定的隔离。
D：持久性
事务最终结束的一个保证。事务提交，就相当于将没有保存到硬盘上的数据保存到银盘上！

事务隔离性

事务和事务之间的隔离级别有哪些？从低到高依次是

• 读未提交：read uncommitted
  事务A可以读取事务B未提交的数据
  问题：可能出现脏读现象—读取到另一个事务未提交的数据，当作结果返回后，另一个事务将数据回滚了。此时的结果数据不是数据库中的正确数据
• 读已提交：read committed
  事务A只能读取事务B中已提交的数据，事务B中的提交的数据可以查询
  问题：不可重复读----事务B从早上就开启事务，一直到晚上后没有关，其中会有很多增删操作，此时的事务A查询的数据只能是事务B开启事务时的数据，不会实时更新
• 可重复读：repeatable read
  确保事务A中两次查询语句都返回相同的结果，无论其他事务是否有提交
  问题：幻读—事务A开始时间很长，其中有其他事务对数据进行了多次增删，而事务A只能读取事务开启时的数据。
  如果事务A在晚上的时候返回一个查询结果，这个时候的数据和数据库中此时的数据已经不同了
• 序列化：serializable
  将一个事务与其他事务隔离，就是说同时只能存在一个事务对数据库进行操作

设置事务的隔离级别

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
隔离级别可选项

• READ-UNCOMMITTED
• READ-COMMITTED
• REPEATABLE-READ
• SERIALIZABLE
  例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

设置事务隔离范围

global为全局级：对所有的会话有效
session回话级：只对当前的会话有效
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；

查看隔离级别

查询全局隔离级别
select @@global.tx_isolation
查询会话级别
select @@session.tx_isolation

常用命令

开启事务控制 start transaction
提交事务 commit
回滚 rollback

测试隔离级别

开启两个命令行窗口

read uncommitted(未提交读) --脏读(Drity Read)

会话1	会话2
mysql> prompt s1>	mysql> use bjpowernode
s1>use bjpowernode	mysql> prompt s2>
s1>create table tx (id int(11),num int (10));
s1>set global transaction isolation level read uncommitted;
s1>start transaction;
	s2>start transaction;
s1>insert into tx values (1,10);
	s2>select * from tx;
s1>rollback;
	s2>select * from tx

read committed(已提交读)

会话1	会话2
s1> set global transaction isolation level read committed;
s1>start transaction;
	s2>start transaction;
s1>insert into tx values (1,10);
s1>select * from tx;
	s2>select * from tx;
s1>commit;
	s2>select * from tx

repeatable read(可重复读)

会话1	会话2
s1> set global transaction isolation level repeatable read;
s1>start transaction;
	s2>start transaction;
s1>select * from tx;
s1>insert into tx values (1,10);
	s2>select * from tx;
s1>commit;
	s2>select * from tx;

索引

索引的原理

索引被用来快速找出在一个列商用一特定值的行，简单来说：就是在每一个数据上添加了一个标签，通过这个标签可以快速的找到对应的数据。
没有索引的情况下，要查询一条数据需要扫描全表才能找到数据
如果加了索引，可以通过这个标签（或者说目录）快速的找到数据
举例：例如生活中的字典，如果没有目录的话要找到一个字需要翻阅整本书，在有目录的情况下如果要找到 ‘兰’这个字，可以先找到L的字母所在的大概页再找la等等，可以大大缩减查询时间。
MySQL会在主键字段上自动添加索引

创建索引

创建索引语句格式：再哪个表的哪个字段上创建索引
create index 索引名 on 表名（字段名）

查看索引

show index from 表名

使用索引

explain select * from user where name = 'jack'
使用上面的SQL语句可以看到查询这条结果的时候共扫描了多少条数据

删除索引

drop index index_name on table_name
alter table table_name drop index index_name

视图

什么是视图

• 视图时一种根据查询语句定义的数据库对象，用于获取想要看到和使用的局部数据
• 视图有时也被称之为“虚拟表”
• 视图可以被永凯从常规表或者其他视图中查询数据
• 相对于从基表中直接获取数据，视图可以使访问数据变得简单，可被用来对不同用户显示不同的表的内容

视图的作用

• 提高检索效率
• 隐藏表的实现细节

创建视图

create view 视图名 as select语句

修改视图

alter view 视图名 as select语句

删除视图

drop view if exists 视图名

数据库权限

数据导入导出

数据库三范式

三范式总结

1. 必须有主键，具有原子性，字段不可分割
2. 完全依赖，没有部分依赖
3. 没有传递依赖

具体需要根据业务进行取舍

---

未完