数据库MySQL

概述

数据库： 按照数据一定结构，存储管理数据的仓库。数据库是在数据库管理系统管理和控制下，在一定介质上的数据集合。

数据库管理系统 ：管理数据库的软件，用于建立和维护数据库。

数据库系统 ： 由数据库和数据库管理系统，开发工具等组成的集合 。

数据库分类：关系型数据库和非关系数据库

开源数据库：MySQL、SQLite、MongoDB

非开源数据库：Oracle、DB2、SQL_Server

MySQL

MySQL的特点：

1. 是开源数据库，使用C和C++编写
2. 能够工作在众多不同的平台上
3. 提供了用于C、C++、Python、Java、Perl、PHP、Ruby众多语言的API
4. 存储结构优良，运行速度快
5. 功能全面丰富

MySQL的安装：

• Ubuntu安装MySQL服务

  • 终端执行: sudo apt install mysql-server
  • 配置文件：/etc/mysql
  • 数据库存储目录 ：/var/lib/mysql

• Windows安装MySQL

  • 下载MySQL安装包(windows) https://dev.mysql.com/downloads/windows/installer/8.0.html
  • 直接运行安装文件安装
  • https://blog.csdn.net/qq_37350706/article/details/81707862?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162607108016780262533011%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=162607108016780262533011&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-2-81707862.first_rank_v2_pc_rank_v29&utm_term=mysql%E5%AE%89%E8%A3%85%E9%85%8D%E7%BD%AE%E6%95%99%E7%A8%8B&spm=1018.2226.3001.4187

启动和连接MySQL服务

服务端启动

• 查看MySQL服务：sudo service mysql status
• 启动/停止/重启服务：sudo service mysql  start/stop/restart

连接数据库

• mysql -h  主机地址  -uroot -p123456

断开连接

• exit

MySQL的数据库结构

数据元素--记录--数据表--数据库

基本概念解析

• 数据表：存放数据的表格
• 字段：每个列，用来表示该列数据的含义
• 记录：每个行，表示一组完整的数据

SQL语言

• 什么是SQL

结构化查询语言(Structured Query Language)，一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

• SQL语言特点

  • SQL语言基本上独立于数据库本身
  • 各种不同的数据库对SQL语言的支持与标准存在着细微的不同
  • 每条命令以 ; 结尾
  • SQL命令（除了数据库名和表名）关键字和字符串可以不区分字母大小写

数据库管理

1、查看已有库

show databases;

2、创建数据库

create database  库名 charset=utf8;

注意：库名的命名

数字、字母、下划线,但不能使用纯数字

库名区分字母大小写

不要使用特殊字符和mysql关键字

3、切换库

use 库名;

4、查看当前所在库

select database();

5、删除库

drop database 库名;

数据表管理

基本思考过程

• 确定存储内容
• 明确字段构成
• 确定字段数据类型

基础数据类型

• 数字类型：INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT
• 浮点类型：FLOAT,DOUBLE,DECIMAL
• 比特值类型：BIT

注意：

1. 对于准确性要求比较高的东西，比如money，用decimal类型减少存储误差。声明语法是DECIMAL(M,D)。M是数字的最大数字位数，D是小数点右侧数字的位数。比如 DECIMAL(6,2)最多存6位数字，小数点后占2位,取值范围-9999.99到9999.99。
2. 比特值类型指0，1值表达2种情况，如真，假

• 字符串类型：

  • 普通字符串： CHAR，VARCHAR
  • 存储文本： text
  • 存储二进制数据： BLOB
  • 存储选项型数据：ENUM，SET

注意：

1. char：定长，即指定存储字节数后，无论实际存储了多少字节数据，最终都占指定的字节大小。默认只能存1字节数据。存取效率高。
2. varchar：不定长，效率偏低 ，但是节省空间，实际占用空间根据实际存储数据大小而定。必须要指定存储大小 varchar(50)
3. enum用来存储给出的多个值中的一个值,即单选，enum('A','B','C')
4. set用来存储给出的多个值中一个或多个值，即多选，set('A','B','C')

数据表的基本操作

创建表

create table 表名(字段名 数据类型 约束，字段名 数据类型 约束...)；

• 字段约束

  • 如果你想设置数字为无符号则加上 unsigned
  • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
  • DEFAULT 表示设置一个字段的默认值
  • AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
  • PRIMARY KEY 关键字用于定义列为主键。主键的值不能重复,且不能为空。

举例子

创建班级表

create table class_1 (
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null,
sex enum('w','m'),
score float default 0.0
);

创建兴趣班表

create table interest (
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null.
hobby set('sing','dance','draw'),
level char not null,
price decimal(6,2),
remark text
);

查看数据表

show tables;

查看表结构

desc 表名;

查看数据表创建信息

show create table 表名;

删除表

drop table 表名

表内数据的基本操作

插入数据

insert into 表名 values(值1),(值2);
insert into 表名(字段1，字段2) values(值1，值2);

例子

insert into class_1 values(2,'Dema',10,'m',91),(3,'Jame','w',80);
insert into class_1(name,age,sex,score) values('Lucy',17,'w',100);

查询数据

select * from 表名 where 条件;
select 字段 from 表名 where 条件;

例子

select * from class_1;
select name,age from class_1;

where子句

主要是通过各种运算符来筛选

where子句在sql语句中扮演了重要角色，主要通过一定的运算条件进行数据的筛选，在查询，删除，修改中都有使用。

• 算数运算符

e.g.
select * from class_1 where age % 2 = 0;

• 比较运算符

e.g.
select * from class_1 where age > 8;
select * from class_1 where between 8 and 10;
select * from class_1 where age in (8,9);

• 逻辑运算符

e.g.
select * from class_1 where sex='m' and age>9;

 更新表记录

update 表名 set 字段1=值1，字段2=值2  where 条件;
注意：update语句需要加where条件，否则整个表会被更新

例子

update class_1 set age=11 where name='Lucy';

删除表记录

delete from 表名 where 条件;
注意：delete语句需要加where条件，否则所有记录都被删除

例子

delete from class_1 where name='Ruby';

表字段的操作

语法 ：alter table 表名 执行动作;

* 添加字段(add)
    alter table 表名 add 字段名 数据类型;
    alter table 表名 add 字段名 数据类型 first;
    alter table 表名 add 字段名 数据类型 after 字段名;
* 删除字段(drop)
    alter table 表名 drop 字段名;
* 修改数据类型(modify)
    alter table 表名 modify 字段名 新数据类型;
* 修改字段名(change)
    alter table 表名 change 旧字段名 新字段名 新数据类型;
* 表重命名(rename)
    alter table 表名 rename 新表名;

例子

alter table interest add tel char(11) after name;

时间类型数据

• 日期 ： DATE
• 日期时间： DATETIME，TIMESTAMP
• 时间： TIME
• 年份 ：YEAR

时间格式

date ："YYYY-MM-DD"
time ："HH:MM:SS"
datetime ："YYYY-MM-DD HH:MM:SS"
timestamp ："YYYY-MM-DD HH:MM:SS"

注意:

1. datetime ：以系统时间存储
2. timestamp ：以标准时间存储但是查看时转换为系统时区，所以表现形式和datetime相同

create table marathon (
id int primary key auto_increment,
athlete varchar(32),
birthday date,
registration_time datetime,
performance time
);

• 日期时间函数

  • now() 返回服务器当前日期时间,格式对应datetime类型
  • curdate() 返回当前日期，格式对应date类型
  • curtime() 返回当前时间，格式对应time类型

• 时间操作

  时间类型数据可以进行比较和排序等操作，在写时间字符串时尽量按照标准格式书写。

  select * from marathon where birthday>='2000-01-01';
  select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

高级查询语句

模糊查询like

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1

select * from class_1 where name like 'A%';

正则查询

只支持部分元字符

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 REGEXP condition1

select * from class_1 where name regexp '^B.+';

as用法

一般使用as给字段或者表取个别名

 select name as 姓名,age as 年龄 from class_1;
 select * from class_1 as c where c.age > 17;

排序 order by

SELECT field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]

默认asc升序排列，desc是降序

select * from class_1 where sex='m' order by age desc;

复合排序

当第一排序字段相同时，以第二排序字段排序

select * from class_1 order by score desc,age;

限制 limit

查询结果后限制数量

SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num]

联合查询 union

连接2个或多个select语句的结果，组合到一个结果集合中

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

默认union后为distinct表示删除结果中的重复数据，all表示返回所有结果包含重复数据

select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;

子查询

• 定义 ： 当一个select语句中包含另一个select 查询语句，则称之为有子查询的语句

• 子查询出现的位置：

  1. from 之后 ，此时子查询的内容作为一个新的表内容，再进行外层select查询

  select name from (select * from class_1 where sex='m') as s where s.score > 90;
  

  注意： 需要将子查询结果集重命名一下，方便where子句中的引用操作

  1. where字句中，此时select查询到的内容作为外层查询的条件值

   	select *  from class_1 where age = (select age from class_1 where name='Tom');
  

  注意：

  1. 子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列。
  2. 如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。

查询过程的顺序

(5)SELECT DISTINCT <select_list>                     

(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>

(2)WHERE <where_predicate>

(3)GROUP BY <group_by_specification>

(4)HAVING <having_predicate>

(6)ORDER BY <order_by_list>

(7)LIMIT <limit_number>

聚合操作

聚合函数

聚合分组

聚合筛选

去重语句distinct

聚合运算

 数据库索引

什么是索引

对表中一列或者多列值进行排序的一种数据结构

优点：更快的查询和更新数据

缺点：占用物理内存，需要动态维护，读写的效率降低

索引分类

普通索引

唯一索引

主键索引

数据库的存储过程

存储过程的特点

        无返回值

        in out inout  多个参数类型

        功能丰富，可以实现复杂的业务逻辑，相当于按照固定步骤执行

        调用存储过程 call

数据库的函数

有且只有一个返回值

只能输入一个参数

实现针对性功能，不能显示结果集，只能返回工作后的一个结果

查询的时候调用一部分

表连接

简单多表查询

select  字段1,字段2... from 表1,表2... [where 条件]

内连接查询 inner join

SELECT 字段列表
    FROM 表1  INNER JOIN  表2
ON 表1.字段 = 表2.字段;

select * from table_1 
inner join table_2 
on table_1.id=table_2.id;

左连接 left join

SELECT 字段列表
    FROM 表1  LEFT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person left join  dept  on  person.dept_id =dept.id;

右连接 right join

SELECT 字段列表
    FROM 表1  RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person right join  dept  on  person.dept_id =dept.id;

数据库的事务四大特性 ACID

事务

指的是一件事情从开始到结束的全过程

事务的作用

保证数据库操作的安全

事务使用场景

主要用于处理数据多，操作复杂，要求的安全性的事情

ACID

原子性：事务不能被分割，要么全部执行成功，要么全部执行失败

一致性：事务开始到结束，数据库的数据保存完整性约束

隔离性：事务之间相互独立，但是事务可以并发执行

持久性：如果事务成功，修改的数据会永久保存

事务隔离级别

 读未提交：事务a提交，事务b可以读取

读已提交：事务a提交，事务b才能服务

可重复读：事务a提交，事务b读不到

串行化：事务a提交，事务b等待

数据库优化

数据库设计的三大范式

第一范式：每一列不可分割

第二范式：所有的行记录或者实例是唯一的，或者有唯一的字段如id，且依赖主属性

第三范式：合理使用外键，不同表中不能有重复的字段

MySQL的存储引擎

什么是存储引擎

MySQL数据库管理系统中用来处理表的处理器

基本操作

1、查看所有存储引擎
   mysql> show engines;
2、查看已有表的存储引擎
   mysql> show create table 表名;
3、创建表指定
   create table 表名(...)engine=MyISAM;
4、已有表指定
   alter table 表名 engine=InnoDB;

innodb特点

支持行级锁，对指定的行记录加锁，其他进程还是可以操作表内的其他行

支持外键、事务、事务回滚

表字段和索引同存储在一个文件中

不保存表的行数

使用场景：写操作多的表

myisam特点

支持表级锁，对指定表进行加锁，其他进程无法对表进行写操作

不支持事务、外键

表字段和索引分开存储

保存表的行数

使用场景：读操作多的表

sql优化

• 尽量选择数据类型占空间少，在where ，group by，order by中出现的频率高的字段建立索引

• 尽量避免使用 select * ...;用具体字段代替 * ,不要返回用不到的任何字段

• 少使用like %查询，否则会全表扫描

• 控制使用自定义函数

• 单条查询最后添加 LIMIT 1，停止全表扫描

• where子句中不使用 != ,否则放弃索引全表扫描

• 尽量避免 NULL 值判断,否则放弃索引全表扫描

  优化前：select number from t1 where number is null;

  优化后：select number from t1 where number=0;

  • 在number列上设置默认值0,确保number列无NULL值

• 尽量避免 or 连接条件,否则会放弃索引进行全表扫描，可以用union代替

  优化前：select id from t1 where id=10 or id=20;

  优化后： select id from t1 where id=10 union all select id from t1 where id=20;

• 尽量避免使用 in 和 not in,否则会全表扫描

  优化前：select id from t1 where id in(1,2,3,4);

  优化后：select id from t1 where id between 1 and 4;