【入门】MySQL基础入门笔记

学习资料

https://www.bilibili.com/video/BV1Vy4y1z7EX?spm_id_from=333.999.0.0

MYSQL常用命令

启动/关闭服务

net start mysql
net stop mysql

登录MySQL

mysql -u root -p 123456
mysql -u root -p

退出MySQL

exit

查看数据库支持的存储引擎

show engines \G

查看数据库的版本号

select version();

查看当前使用数据库

select database();

查看数据库

show databases;

使用某个数据库

use <数据库名>;
eg: use Test;

创建数据库

create <数据库名>;
eg: create database sevenlin;

查看数据库下的表名

show tables;

数据导出

// 导出数据库
mysqldump <数据库名> > <sql脚本文件路径> -uroot -p123456

// 导出数据表
mysqldump <数据库名> <数据表名> > <sql脚本文件路径> -uroot -p123456

执行sql脚本

source <sql文件路径>  // 路径不能包含中文

查看表结构

desc <表名>

SQL 分类

• DQL（数据查询语言）：查询表中数据 select
• DML（数据操作语言）：操作表中数据 insert、update、delete
• DDL（数据定义语言）：操作表结构 create、 alter、 drop
• TCL（事务控制语言）：提交事务 commit、撤销事务 rollback
• DCL（数据控制语言）：授权 grant、撤销权限 revoke

DQL数据查询

单表查询

查询单个字段

select <字段名> from <表名>;
eg: select name from students;

查询多个字段

select <字段名1, 字段名2, ...> from <表名>;
eg: select name, age from students;

查询所有字段

select * from <表名>;  // 缺点：效率低、可读性差
eg: select * from students;

查询列起别名

select <字段名> as <别名> from <表名>;  // 如果别名包含空格/中文，需用单引号
select <字段名> <别名> from <表名>;
eg: select name as nam from students;
    select name nam from students;

条件查询

• < 小于
• <= 小于等于
• > 大于
• >= 大于等于
• !=、<> 不等于
• between … and … 、>= and <= 在两者之间
• is null 值为空
• is not null 值不为空
• and 与操作 // and和or同时出现，and 的优先级高于 or
• or 或操作
• in 包含某个值
• like 模糊查询 // %：表示至少一个字符； _：表示占用一个字符

select * from <表名> where <条件>;
eg: select * from students where score > 90;
    select * from students where address is not null;
    select * from students where name like '%lin%';
    select * from srudents where name like '%\_%'  // 使用转义字符

排序

• 默认为升序
• 降序：desc 升序：asc

select * from students order by <字段名>;
eg: select * from students order by name desc;
    select * from students order by score asc, name desc;
    select * from students order by 2;  // 按查询结果的第二列进行排序

函数

单行处理函数

特点：一个输入对应一个输出

• lower 转换小写
• upper 转换大写
• substr 取子串 // substr(被截取的字符串， 起始下标， 截取长度) 起始下标从1开始
• concat 字符串拼接
• length 取长度
• trim 取出空格
• str_to_date 将字符串转化为日期 // str_to_date(‘日期字符串’, ‘日期格式’)
  注意：日期格式：%Y-%m-%d %H:%i:%s
• date_format 日期转化为字符串 // date_format(‘日期类型数据’, ‘日期格式’)
• now() 获取系统当前时间
• format 设置千分位
• round 四舍五入
• rand() 生成随机数
• ifnull 可以将null转化为一个具体的数值

eg: select lower(name) from students;
    select upper(name) from students;
    select substr(name, 1, 1) from srudents;
    select concat(name, id) from students;
    select length(name) from students;

• case … when … then … when … then … else … end // 分支

eg: select id, name, score as oldScore, (case score when 10 then score + 1 when 20 then score + 2 else score end) as newScore
    from students;

分组函数（多行处理函数）

特点： (1)多个输入对应一个输出;
(2)分组函数会自动忽略NULL;
(3) count(具体字段)：表示统计该字段下不为空的元素数量；
count(*)：统计表中的记录数
(4)分组函数不能用于where子句;
(5)分组函数可以组合使用；
(6)分组函数的使用需要先进行分组，没有分组则默认整张表为一个组

• count 技术
• sum 求和
• avg 求平均值
• max 求最大值
• min 求最小值

eg: select min(score) from srudents;
    select * from students where score > min(score);  // 错误

问题点：select * from students where score > min(score); // 错误 ？？？
sql语句的执行顺序：
select … from … where … group by … having … order by …
第一步: 执行 from 语句；
第二步: 执行 where 语句；
第三步：执行 group by 语句；
第四步：执行 having 语句
第五步：执行 select 语句；
第六步：执行 order by 语句。
错误原因：分组函数的使用需先进行分组，执行where语句是还未分组，故报错。

分组查询

select ... from ... group by ...
eg: select name , sum(score) as total from students group by name;

分组查询–having语句

• 不能独立使用
• 需要结合 group by 语句使用
• 可以对使用分组函数后的记录进行过滤

eg: select name, sum(score) as total from students group by name having sum(score) > 70;

去重

select distinct <字段名, ...> from <表名>
eg: select distinct name from students;
    select distinct name, score from students;  // 两个字段联合去重
    select name, distinct score from students;  // 错误

连接查询（多表查询）

连接方式

• 内连接 （inner join … on …）
  （1）等值连接
  （2）非等值连接
  （3）自连接
• 外连接
  （1）左外连接
  （2）右外连接
• 全连接

等值连接

sql92语法： // 缺点： 结构不清晰，表的连接条件与进一步的筛选条件都在where语句中
select e.name, d.name from emp e, dept d where e.deptno = d.deptno;

sql99语法：  // 优点： 结构清晰，表的连接条件是独立的
select e.name, d.name from emp e inner join dept d on e.deptno = d.deptno;  // inner可缺省

非等值连接

select e.name, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;  // 条件不是一个等量关系

自连接

select a.empno, a.ename, b.empno as mgrno, b.name as mgrname from emp a join emp b on a.mgr = b.empno;  // 一张表看成两张表

右外连接

右边的表为主表，将右边的表中的数据完全查询出来

select e.ename, d.dname from emp e right outer join dept d on e.deptno = d.deptno;  // outer可缺省

左外链接

左边的表为主表，将左边的表中的数据完全查询出来

select e.ename, d.dname from emp e left join dept d on e.deptno = d.deptno;

注意：外连接的查询结果的条数一定是大于等于自连接的查询结果条数的

三张表以上联合查询

select
    ...
from
    a
join
    b
on
    a与b的连接条件
left join
    c
on
    a与c的连接条件
right join
    d
on
    a与d的连接条件

子查询

where语句中的子查询

select ename, sal from emp where sal > (select min(sal) from emp);

from语句中的子查询

select t.*, s.grade from (select job, avg(asl) as avgsal from emp group by job) as t join salgrade s on t.avgsal between s.losal anf s.hisal;

select语句中的子查询

select e.ename, e.deptno, (select d.name from dept d where e.deptno = d.deptno) as dname from emp e;

union 合并

select e.ename, job from emp e where job = 'aaa'
union
select e.ename, jon from emp e where job = 'bbb';

注意： 使用union的效率会比较高一下。在表连接的情况下，每一次表连接的匹配次数都会满足笛卡尔积；但union可以减少匹配的次数，在减少匹配次数的情况下还可以完成两个结果集的拼接
例子：表a、b、c 均为10条记录
a 连接 b 连接 c 的情况下，匹配次数为 10 * 10 * 10；
a 连接 b union a 连接 c 的情况下，匹配次数为 10 * 10 + 10 * 10

limit

limit 可以将查询结果的一部分结果取出，通常使用于分页查询

select ... from ... limit <起始下标>, <长度>  // 起始下标从0开始，为0时可缺省
eg: select * from students limit 1, 5;
    select * from students limit 5;

sql查询语句

select ... from ... where ... group by ... having ... order by ... limit ...

语句执行顺序：from -> where -> group by -> having -> select -> order by -> limit

DDL数据定义

数据类型

• varchar
  可变长度的字符串，会根据实际数据长度动态分配空间
  优点：节省空间
  缺点：需要动态分配空间，速度慢
• char
  定长字符串
  分配固定的长度空间来存储数据，使用不恰当时会造成空间浪费
  优点：不需要动态分配空间，速度快
  缺点：使用不当会造成空间浪费
• int
  整数型
• bigint
  长整型
• float
  单精度浮点型
• double
  双精度浮点型
• date
  短日期类型，包括年月日
• datetime
  长日期类型，包括年月日时分秒
• clog
  字符大对象，最多存储4G的字符串
• blob
  二进制大对象，专门用来存储图片、声音和视频等流媒体数据

约束

在创建表的时候，可以给表中的数据添加一些约束，来保证表中数据的完整性和有效性。

分类

• 非空约束 not null
  字段值不能为空
• 唯一性约束 unique
  字段值不能重复
• 主键约束 primary key（简称 PK）
  字段值不能为空且不能重复
  一张表，主键只能有一个
  不建议使用varchar做为主键，主键值一般都是数字，为定长的
  （1）分类方式一：单一主键和复合主键
  （2）分类方式二：自然主键和业务主键(建议使用自然主键)
     自然主键：主键值是一个自然数，和业务没关系
     业务主键：主键值和业务紧密关联，如银行卡号作为主键
• 外键约束 foreign key（简称 FK）
  （1）外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性
  （2）对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）
  （3）主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。
  注意：被引用的外键不一定需要为主键，但要有唯一性
     外键值可以为null
• 检查约束 check（mysql不支持 oracle支持）

// unique
create table students{
    id int,
    name varchar(255),
    email varchar(255) unique  // 列级约束，单字段约束
}

create table students{
    id int,
    name varchar(255),
    email varchar(255)，
    unique(name, email)  // 表级约束，多个字段联合约束
}

// primary key
create table students{
    id int,
    name varchar(255),
    email varchar(255) primary key  // 列级约束，单字段约束
}

create table students{
    id int,
    name varchar(255),
    email varchar(255)，
    primary key(name, email)  // 表级约束，多个字段联合约束
}

create table students{
    id int primary key auto_increment,  // 主键自增，从1开始，自动维护主键
    name varchar(255),
    email varchar(255)
}

// foreign key
drop table if exists studebts;
drop table if exists class;

create table class{
    classno int primary key,
    classname varchar(255) not null
}

create table students{
    id int primary key auto_increment,
    name varchar(255) not null,
    cno int,
    foreign key(con) references class(classno)  // 给con字段增加外键约束，外键引用这class表中的classno字段
}

注意：在MySQL中，当一个字段被同时约束为not null和unique时，该字段默认成为主键字段。在oracle中就不是。

建表语句

create table <表名>(
    字段名1 数据类型 (defailt 默认值), 
    字段名2 数据类型,
    字段名3 数据类型,
    ......
);

create table if not exists <表名>(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型,
    ......
);

查看已建表的建表语句

show create table <表名>;

查看已建数据库创建语句

show create database <数据库名>;

快速创建表（复制表结构及表中数据）

将一个查询结果当成一张新表创建

create table emp2 as select * from emp;

删表语句

drop table <表名>;
drop table if exists <表名>；

truncate 删除表中数据

原理：物理删除，表被一次截断，删除后不可恢复
优点：效率高
缺点：不支持回滚

truncate table <表名>;

注意：当表中数据量很大时，删除表中数据使用delete语句会耗时较长，使用truncate语句则是快速完成的

DML数据操作

insert 插入

insert into <表名>(字段名1, 字段名2, ...) values(值1, 值2, ...);
insert into <表名>(字段名1, 字段名2, ...) values(), (), ();  // 一次性插入多条记录
insert into <表名> select * from <表名>;  // 将查询结果插入到表中

update 更新

update <表名> set 字段名1 = 值, 字段名2 = 值, 字段名3 = 值 .... where 条件 

delete 删除

原理：逻辑删除，表中数据被删除了，但数据在硬盘上的真实存储空间不会被释放
优点：支持回滚
缺点：删除效率较低

delete from <表名> where 条件 

TCL事务控制

事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。
例子：从A账户想B账户转账100，将A账户的钱减去100，将B账户的钱加上100.
注意：只有DML语句（insert、update、delete）才会有事务

• 事务是如何实现一组DML语句同时执行成功和同时执行失败的？
  答：InnoDB存储引擎：提供了一组用来记录事务性活动的日志文件，在事务的执行过程中，每一条DML语句的操作都会被记录到事务性活动的日志文件中，随后可以提交事务或回滚事务。
  （1）提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着事务的结束，并且是一种全部成功的结束。
  （2）回滚事务：将之前所有的DML语句操作全部撤销，并且清空事务性活动的日志文件。回滚事务标志着事务的结束，并且是一种全部失败的结束。

查看数据库当前事务隔离级别

select @@transaction_isolation

设置数据库的事务隔离级别

set global transaction isolation level read committed;

开始事务

start transaction;

提交事务

commit;

回滚事务

rollback;

特性

• A：原子性 Atomicity
  事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

• C：一致性 Consistency
  当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中.数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。

• I：隔离性 Isolation
  对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

• D：持久性 Durability
  事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。

事务的隔离级别

• 读未提交 read uncommitted
  隔离级别：最低
  解释：事务A可以读取到事务B未提交的数据，这种现象为脏读现象（dirty read），一般不会采用这种隔离级别。

例子：事务A  事务B  数据库表students 
// 事务B回滚之前，事务A读到了事务B回滚前插入的数据，出现脏读现象
事务A：start transaction  
事务B：start transaction
事务A：select * from students  //读到初始表格数据  
事务B：insert into students values('sevenlin')  //插入新数据  
事务A：select * from students  //能读到事务B插入的数据  
事务B：rollback  //事务回滚  
事务A：select * from students  //读到初始表格数据

• 读已提交 read committed
  解释：事务A只能读取到事务B提交后的数据，解决了脏读现象，但会导致不可重复读取数据的情况。

例子：事务A  事务B  数据库表students 
// 在事务A中，在事务B提交前后读到的数据不一致
事务A：start transaction  
事务B：start transaction
事务A：select * from students  //读到初始表格数据  
事务B：insert into students values('sevenlin')  //插入新数据  
事务A：select * from students  //读到初始表格数据   
事务B：commit  //事务提交   
事务A：select * from students  //读到事务B提交后的数据

• 可重复读 repeatable read
  解释：事务A不会读到其他事务对已有数据的修改，即使其他事务已提交，也就是说，事务A开始时读到的已有数据是什么，在事务A提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务C新插入的数据是可以读到的，这也就引发了幻读问题。（MySQL默认隔离级别）

例子：事务A  事务B  数据库表students 
// 在事务A未提交之前，读到的数据都是事务A开启时的数据，即使事务B修改了数据并提交。
事务A：start transaction  
事务B：start transaction
事务A：select * from students  //读到初始表格数据  
事务B：insert into students values('sevenlin')  //插入新数据  
事务A：select * from students  //读到初始表格数据   
事务B：commit  //事务提交   
事务A：select * from students  //读到初始表格数据 

• 序列化 serializable
  隔离级别：最高
  解释：序列化使得事务是按顺序执行，只有当前事务执行完成才能进行下一个事务。很好的解决了脏读、不可重复读和幻读的问题。但执行效率很差。

例子：事务A  事务B  数据库表students 
// 在事务A未提交之前，读到的数据都是事务A开启时的数据，即使事务B修改了数据并提交。
事务B：start transaction
事务A：start transaction  
事务A：select * from students  //阻塞，需等到事务B完成
事务B：insert into students values('sevenlin')  //插入新数据  
事务B：commit  //事务提交  事务A的select开始执行

索引

（1）索引是帮助数据库高效获取数据的数据结构，为了提高查询效率的一种机制。
（2）一张表的一个自选可以创建一个索引，也可以多个字段联合创建索引。
（3）索引相当于是一本书的目录，可以起到查询数据是缩小扫描范围的作用。

MySQL在查询数据时主要的两种方式：

• 全表扫描
• 根据索引检索

注意：索引是需要排序的，索引的底层是一个B-Tree数据结构，遵循左小右大原则，采用中序遍历方式读取数据

• 在任何数据库当中主键上都会自动添加索引对象。在MySQL中，一个字段如果有unique约束，也会自动创建索引对象。
• 在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
• 在MySQL中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。在MyISAM存储引擎中，索引存储在一个.MYI文件中；在InnoDB存储引擎中，索引存储叜一个逻辑名称叫做tablespace当中；在MEMORY存储引擎中，索引存储在内存中。

注意：什么情况下，会考虑给字段添加索引？

• 数据量庞大；
• 该字段经常出现在where语句中，以查询条件的形式存在，也就是这个字段需要经常被扫描；
• 该字段很少涉及到DML(insert, update, delete)语句，因为在执行操作后，索引需要重新排序。
• 建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能；
• 建议通过主键查询或unique字段进行查询，效率是比较高的。

创建索引

create index <索引名> on <表名>(字段名)；
eg: create index name_index on students(name);
    create index name_score_index on students(name, score);

删除索引

drop index <索引名> on <表名>;
eg：drop index name_index on students;

查看一个SQL语句是否使用了索引

explain <select 语句>
eg: explain select * from students;

索引失效

• 模糊查询当中以 “%” 开头
  eg：select * from students where name like ‘%123’;
  尽量避免在模糊查询中以 “%” 开头，这是一种优化方法

• 使用 or 的时候有可能会失效，使用 or 的话要求两边的条件字段都要有索引。在这中情况下可以使用 union 语句

• 使用符合索引时，没有使用左侧的列进行查找，会导致索引失效
  eg：create index name_score_index on students(name, score);
  select * from students where score = 100;

• 在where语句中索引列参与了运算，会导致索引失效
  eg：select * from students where score + 1 = 99;

• 在索引列使用了函数，会导致索引失效
  eg：select * from students where lower(name) = ‘sevenlin’;

注意：索引是各种数据库进行优化的重要手段；优化的时候优先考虑得就是索引。

视图

视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

创建视图

create view <视图名>(列1, 列2, ...) as <select语句>;
eg：create view students_view as select * from students;

删除视图

drop view <视图名>;

视图的意义

• 视图是一张虚拟表
• 可以面向视图进行增删改查，对视图对象的增删改会导致原表被操作，进而影响原表的数据
• 假设有一条很复杂的select SQL语句，而且这条语句在不同位置上反复使用，每一次在使用到这条SQL语句的时候需要重新编写一遍。这个时候就可以把这条select语句以视图对象的形式创建，在下次使用时可以直接使用视图，简化了开发，并且利于后期维护。

数据库设计

三大范式

• 表格必须有主键，并且每个字段都具有原子性，不可再分；
• 在第一范式的基础上，所有非主键字段必须完全依赖于主键，不要产生部分依赖；
  eg：主键是复合主键，由字段A和字段B组成，其中字段C依赖于字段A，而字段D依赖于字段B。
• 在第二范式的基础上，所有非主键字段必须直接依赖主键，不要产生传递依赖。
  eg：主键是字段A，其中字段B依赖于字段A，而字段C依赖于字段B。

设计小技巧

• 在设计 “多对多” 的数据关系时：“多对多，三张表，关系表连个外键”
• 在设计 “一对多” 的数据关系时：“一对多，两张表，多的表加外键”
• 在设计 “一对一” 的数据关系时，可能存在一张表字段太多，这个时候可以考虑拆表：“一对一，外键唯一”
• 客户就是爸爸，客户说啥就是啥。冗余（减少表的连接次数）换执行速度也是没问题的。