概述
数据库: 按照数据一定结构,存储管理数据的仓库。数据库是在数据库管理系统管理和控制下,在一定介质上的数据集合。
数据库管理系统 :管理数据库的软件,用于建立和维护数据库。
数据库系统 : 由数据库和数据库管理系统,开发工具等组成的集合 。
数据库分类:关系型数据库和非关系数据库
开源数据库:MySQL、SQLite、MongoDB
非开源数据库:Oracle、DB2、SQL_Server
MySQL
MySQL的特点:
- 是开源数据库,使用C和C++编写
- 能够工作在众多不同的平台上
- 提供了用于C、C++、Python、Java、Perl、PHP、Ruby众多语言的API
- 存储结构优良,运行速度快
- 功能全面丰富
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
注意:
- 对于准确性要求比较高的东西,比如money,用decimal类型减少存储误差。声明语法是DECIMAL(M,D)。M是数字的最大数字位数,D是小数点右侧数字的位数。比如 DECIMAL(6,2)最多存6位数字,小数点后占2位,取值范围-9999.99到9999.99。
- 比特值类型指0,1值表达2种情况,如真,假
-
字符串类型:
- 普通字符串: CHAR,VARCHAR
- 存储文本: text
- 存储二进制数据: BLOB
- 存储选项型数据:ENUM,SET
注意:
- char:定长,即指定存储字节数后,无论实际存储了多少字节数据,最终都占指定的字节大小。默认只能存1字节数据。存取效率高。
- varchar:不定长,效率偏低 ,但是节省空间,实际占用空间根据实际存储数据大小而定。必须要指定存储大小 varchar(50)
- enum用来存储给出的多个值中的一个值,即单选,enum('A','B','C')
- 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"
注意:
- datetime :以系统时间存储
- 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 查询语句,则称之为有子查询的语句
-
子查询出现的位置:
- from 之后 ,此时子查询的内容作为一个新的表内容,再进行外层select查询
select name from (select * from class_1 where sex='m') as s where s.score > 90;
注意: 需要将子查询结果集重命名一下,方便where子句中的引用操作
- where字句中,此时select查询到的内容作为外层查询的条件值
select * from class_1 where age = (select age from class_1 where name='Tom');
注意:
- 子句结果作为一个值使用时,返回的结果需要一个明确值,不能是多行或者多列。
- 如果子句结果作为一个集合使用,即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;