MySQL基础

基础操作

1. 链接数据库

mysql -uroot -p

2. 查看所有的数据库

show databases;

3. 创建数据库

CREATE DATABASE IF NOT EXISTS mydatabase;  # if not exists 可选

4. 使用数据库

USE mydatabase   #如果表名或者字段名是特殊字符 用反引号``括起来

5. 查看当前数据库的表

show tables;

6. 删除数据库

DROP DATABASE IF EXISTS  mydatabase;       # if exists 可选

7. 导出/导入

# 在命令行执行 导出
mysqldump -hlocalhost -uroot -p123456 数据库名 [表1 表2] > D:/x.sql
# 导入 登入情况下 切换到指定数据库
source D:/x.sql

数据类型

数值

• tinyint (1 byte)
• smallint (2 bytes)
• mediumint (3 bytes)
• int (4 bytes)
• bigint (8 bytes)
• float (4 bytes)
• double (8 bytes)
• decimal 字符串形式的浮点数–用于金融计算

字符串

• char 固定大小 0-255
• varchar 可变字符串 0-65535
• tinytext 微型文本 0-2^8-1
• text 文本串 0-2^16-1

时间日期

• date : YYYY-MM-DD

• time : HH:MM:SS’

• datatime : YYYY-MM-DD HH:MM:SS

• timestamp 1970-01-01到现在的毫秒数

• year 年

null

• 没有值
• 不要使用null进行运算，否则结果就为null

字段属性

Not_null:

• 设置为该属性，如果不赋值，就会报错

Auto_Increment:

• 每添加一条数据,自动加1(默认)
• 通常用于设置主键,且为整数类型
• 可定义起始值和自增量

数据管理

DML语言：

• insert
• update
• delete

添加

语法：

#单个加入
insert into `表名`(`字段1`,`字段2`,`字段3`) value ('值1','值2',值3);
#多个加入
insert into `表名`(`字段1`,`字段2`,`字段3`) values 
('值1','值2',值3),('值1','值2',值3);

注意：

• 字段可以省略，但默认表示所有字段，值必须一一对应
• Auto_Increment的值不需要写入

修改

语法：

update `表名`
set `字段1` = 'value1',`字段2` = 'value2'  #多个字段用逗号隔开
where id = 4;                   #条件，与或非对应 and or not

删除

语法：

delete from `表名` where id = 5;    

truncate `表名`;      #清空这张表

truncate:

• 自增计数器会清零
• 不会影响事务

数据查询

简单查询

语法：

查询所有

select *
from student;

查询指定字段 ，as 可以起别名

select `studentno` as 学号,`studentname` as 姓名
from student;

distinct 指定字段中重复的记录只显示一条

select distinct `studentno`
from student;

between and 为某一闭区间范围

select `studentno`
from result
where `studentresult` between 80 and 90;

in 查出满足所列选项中的其中一个的数据

select `studentname`
from student
where address in ('北京','上海');

order排序

select `studentname`,`score`
from student
where `score` is not null 
order by `score` desc ;    # desc:降序  asc:升序  order by 不能在where前面

limit限制获取的数量大小

select studentresult
from result
where studentresult is not null
order by `studentresult` desc 
limit 0,5;          # 参数1表示起始值， 参数2表示查询的数量

模糊查询

语法：

SELECT `字段` FROM `表` WHERE `某字段` LIKE 条件;

条件：

一、%：表示零个或多个字符。

select * from `student` where `studentname` like '%星%';
# 查出所有名字中带有‘星’字的记录
select * from `student` where `studentno` like '2018%';
#查出学号为2018开头的所有记录

二、_：表示任意单个字符

select * from `student` where `studentname` like '王_';
#查出所有姓王二字学生的记录

三、[]：表示括号内所列字符中的一个

select * from `student` where `studentname` like '小[明红]';
#查出‘小明’和‘小红’
#若[]内有一系列字符（01234，abcde之类的）则可略写为“0-4”，“a-e”:

四、[^]：表示不在括号所列之内的单个字符。

select * from `student` where `studentname` like '李[^明红]';
#查出不是‘李明’和‘李红’的其他姓李的同学

联表查询

上面介绍了如何在一张表中查找数据，但是在真正的应用中经常需要从多个数据表中读取数据。通过使用join来联合多表查询。

join 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录,交集。
• **LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 获取右表所有记录，即使左表没有对应匹配的记录。

语法：

# 查询 学号 姓名 科目 分数 ----在三张不同的表中 
# on关键字声明连接点
select stu.`studentno`,`studentname`, `subjectname`,`studentresult`
from student stu
right join result r on stu.studentno = r.studentno
inner join subject s on r.subjectno = s.subjectno;

自连接查询

自连接查询其实等同于连接查询，需要两张表，只不过它的左表（父表）和右表（子表）都是自己。做自连接查询的时候，是自己和自己连接，分别给父表和子表取两个不同的别名，然后附上连接条件。看下面的例子：

在这个表中，通过一个父ID来标识一个层级的关系，我们希望将父级和对应的子级罗列出来，例如ps技术的父级为美术设计。

代码实现：

select parent.categoryname as 父级,child.categoryname as 子级   -- 取别名看作两张表
from category as parent, category as child
where child.parent_id = parent.categoryid;    

结果：

子查询

有时条件判定中需要其余表中的信息，除了使用链表查询的方式外，还可以使用子查询----在where中嵌套一个select语句即可

-- 分数>80的学生的姓名学号
select `studentname`,`studentno`
from `student`
where `studentno` in (select distinct result.`studentno` from  result where studentresult < 80);

分组查询

group by分组时，可以使用使用下面的聚合函数

-- 查询各个科目的平均分
select `subjectname`,avg(studentresult) as 平均分
from result r
inner join subject s on r.subjectno = s.subjectno
group by `subjectname`

当需要进一步对分组内容进行筛选时，使用having关键字：

-- 删选出平均分大于80的数据
select `subjectname`,avg(studentresult) as 平均分
from result r
inner join subject s on r.subjectno = s.subjectno
group by `subjectname`
having 平均分 > 80;

MySQL函数

**官方文档：**https://dev.mysql.com/doc/refman/8.0/en/function-reference.html

聚合函数

函数名称	描述
COUNT()	Return a count of the number of rows returned
SUM()	Return the sum
AVG()	Return the average value of the argument
MAX()	Return the maximum value
MIN()	Return the minimum value

事务

事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。

ACID

事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID

原子性

事务的原子性是指事务必须是一个原子的操作序列单元。事务中包含的各项操作在一次执行过程中，只允许出现两种状态之一：

• 全部执行成功
• 全部执行失败

任何一项操作失败都会导致整个事务的失败，同时其它已经被执行的操作都将被撤销并回滚，只有所有的操作全部成功，整个事务才算是成功完成。

一致性

事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。

比如：如果从A账户转账到B账户，不可能因为A账户扣了钱，而B账户没有加钱。

隔离性

事务的隔离性是指在并发环境中，并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。

持久性

事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

事务隔离问题

脏读

指一个事务读取了另一个事务未提交的数据

不可重复读

一个事务内读取表中某一行的数据，多次读取的结果不同（不一定是错误导致的，可能是其他事务进行了修改）

幻读

一个事务内读取到其他事务插入的数据，导致前后读取不一致

事务隔离级别

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高

一般数据默认级别是读已提交或可重复读

事务隔离级别	脏 读	不可重复读	幻 读
读未提及（READ UNCOMMITTED）	允许	允许	允许
读已提交（READ COMMITTED）	禁止	允许	允许
可重复读（REPEATABLE READ）	禁止	禁止	允许
顺序读（SERIALIZABLE）	禁止	禁止	禁止

SQL操作事务

-- 可以设置一个事务的隔离级别
set transaction isolation level repeatable read ;

-- 显式地开启一个事务
start transaction ;
...

-- 回滚到处理前的状态 ，提交后就无法回滚
rollback ;

-- 提交事务
commit ;

索引

简介

索引是对数据库表中一列或多列的值进行排序的一种结构，对于大量数据的查询，索引可以大大提高MySQL的检索速度，简单来说就是以空间换时间。

优点

• 保证行的唯一性
• 缩短数据检索时间
• 加快分组和排序

缺点

• 创建和维护需要时间成本和空间成本
• 会降低表的增删改的效率

使用原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 数据量少的表不需要加索引
• 索引一般加在常用来查询的数据上

算法原理

参考文章：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

类别

普通索引

这是最基本的索引，它没有任何限制

直接创建

create index index_name on table_name(column_name)

修改表结构添加

alter table table_name add index index_name on (column_name)

删除索引

drop index index_name on table_name

组合索引

在多个字段上创建的索引。遵守“最左前缀”原则**，**即在查询条件中要使用组合索引的第一个字段，索引才会被使用。因此，在组合索引中索引列的顺序至关重要。

直接创建

create index index_name on table_name(col_name1,col_name2,...);

修改表结构添加

alter table table_name add index index_name(col_name,col_name2,...);

唯一索引

索引列的值必须唯一，但允许有空值(唯一空值)。如果是组合索引形式，则列值的组合必须唯一，创建方法和上述类似，关键字为unique index

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)    -- 主键索引
);

全文索引

在一般情况下，模糊查询都是通过 like的方式进行查询,但是对于海量数据， like 进行模糊匹配性能很差。需要考虑使用全文搜索的方式进行优化。关键字fulltext index