Skip to content

Latest commit

 

History

History
258 lines (193 loc) · 9.14 KB

03_数据库.md

File metadata and controls

258 lines (193 loc) · 9.14 KB

数据库基础

登录数据库: mysql-uroot -p
退出数据库: quit 或者 exit 或者 ctrl + d
创建数据库: create database 数据库名 charset=utf8 collate utf8_bin
使用数据库: use 数据库名:
删除数据库: drop database 数据库名;
创建表: create table 表名 (字段名字段类型约束 (试情况添加),…);
修改表 - 添加字段: alter table 表名 add 字段名字段类型约束
修改表修改字段类型: alter table 表名 modify 字段名字段类型约束
修改表修改字段名和字段类型: alter table 表名 change 原字段名新字段名字段类型约束
修改表 - 删除字段: alter table 表名 drop 字段名
删除表: drop table 表名;
查询数据: select * from 表名; 或者 select 列 1, 列 2,… From 表名
插入数据: insert into 表名 values (..) 或者 insert into 表名例 1…) values (值 1…
修改数据: update 表名 set 列 1=值 1, 列 2=值 2… Where 条件
删除数据: delete from 表名 where 条件

as 关键字可以给表中字段或者表名起别名
distinct 关键字可以去除重复数据行。

常见的比较运算符有 > , < , > =, < =, !=
逻辑运算符 and 表示多个条件同时成立则为真，or 表示多个条件有一个成立则为真，not 表示对条件取反

like 和% 结合使用表示任意多个任意字符，like 和 _ 结合使用表示一个任意字符

between-and 限制连续性范围 in 限制非连续性范围

判断为空使用: is null 判断非空使用: is not null

  1. 排序使用 order by 关键字
  2. Asc 表示升序
  3. Desc 表示降序

使用 limit 关键字可以限制数据显示数量，通过 limit 关键可以完成分页查询
limit 关键字后面的第一个参数是开始行索引 (默认是 0，不写就是 0)，第二个参数是查询条数

聚合函数

count (col): 表示求指定列的总行数
max (col): 表示求指定列的最大值
min (col): 表示求指定列的最小值
sum (col): 表示求指定列的和
avg (col): 表示求指定列的平均值

Ifnull 函数: 表示判断指定字段的值是否为 null，如果为空使用自己提供的值。

分组查询

group by 根据指定的一个或者多个字段对数据进行分组
group_concat (字段名) 函数是统计每个分组指定字段的信息集合
聚合函数在和 group by 结合使用时, 聚合函数统计和计算的是每个分组的数据
having 是对分组数据进行条件过滤
with rollup 在最后记录后面新增一行，显示 select 查询时聚合函数的统计和计算结果

连接查询

内连接使用 inner join .. On .., on 表示两个表的连接查询条件
内连接根据连接查询条件取出两个表的 “交集”

左连接使用 left join .. On .., on 表示两个表的连接查询条件
左连接以左表为主根据条件查询右表数据，右表数据不存在使用 null 值填充。

右连接使用 right join .. On .., on 表示两个表的连接查询条件
右连接以右表为主根据条件查询左表数据，左表数据不存在使用 null 值填充。

自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
自连接就是一种特殊的连接方式，连接的表还是本身这张表

子查询

在一个 select 语句中, 嵌入了另一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个 select 语句则称为主查询.

  1. 子查询是嵌入到主查询中
  2. 子查询是辅助主查询的, 要么充当条件, 要么充当数据源
  3. 子查询是可以独立存在的语句, 是一条完整的 select 语句

子查询是一个完整的 SQL 语句，子查询被嵌入到一对小括号里面

范式

第一范式（1 NF）: 强调的是列的原子性，即列不能够再分成其他几列。
第二范式（2 NF）: 满足 1 NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式（3 NF）: 满足 2 NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

范式就是设计数据库的一些通用规范。
1 NF 强调字段是最小单元，不可再分
2 NF 强调在 1 NF 基础上必须要有主键和非主键字段必须完全依赖于主键，也就是说不能部分依赖
3 NF 强调在 2 NF 基础上非主键字段必须直接依赖于主键，也就是说不能传递依赖 (间接依赖)。

E-R 模型

E-R 模型由实体、属性、实体之间的关系构成，主要用来描述数据库中表结构。
开发流程是先画出 E-R 模型，然后根据三范式设计数据库中的表结构

外键

外键约束: 对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

-- 为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table teacher;

-- 获取名称之后就可以根据名称来删除外键约束
alter table teacher drop foreign key 外键名;

添加外键约束: alter table 从表 add foreign key (外键字段) references 主表 (主键字段);
删除外键约束: alter table 表名 drop foreign key 外键名;

索引

#索引

-- 查看已有索引
show index from 表名;

-- 创建索引
alter table 表名 add index 索引名[可选](列名);

-- 删除索引
alter table 表名 drop index 索引名;
-- 开启运行时间监测：
set profiling=1;
-- 查找第1万条数据ha-99999
select * from test_index where title='ha-99999';
-- 查看执行的时间：
show profiles;
-- 给title字段创建索引：
alter table test_index add index (title);
-- 再次执行查询语句
select * from test_index where title='ha-99999';
-- 再次查看执行的时间
show profiles;

联合索引

-- 创建teacher表
create table teacher
(
id int not null primary key auto_increment,
name varchar~~(~~10),
age int
);
-- 创建联合索引
alter table teacher add index (name,age);

联合索引的好处:
减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销。

索引是加快数据库的查询速度的一种手段
创建索引使用: alter table 表名 add index 索引名 [可选] (字段名, xxx);
删除索引使用: alter table 表名 drop index 索引名

数据库备份

备份数据库

mysqldump -u用户名 -p密码 --databases 数据库1 数据库2 > xxx.sql

常见选项:

-u: 用户名
-p: 密码
-P: 端口号，不写默认 3306
--all-databases, -A：备份所有数据库
--databases, -B: 用于备份多个数据库，如果没有该选项，mysqldump 把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldump 把每个名字都当作为数据库名。
-d: 只导出数据库的表结构
-t: 只导出数据库的数据
--quick, -q：快速导出
--xml, -X：导出为 xml 文件

mysqldump -uroot -p123456 -A > 0101.sql

还原数据库

  1. 系统命令行：
mysqladmin -uroot -p 123456 create db_name

mysql -uroot -p 123456 db_name < 0101.sql

-- 注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与0101.sql中数据库名是一样的才可以 导入。
  1. Soure 方法
 Mysql > use db
 mysql > source 0101. Sql

explain

-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';

-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

• EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN 不考虑各种 Cache
• EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• Explain 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划

存储过程

思考：如何快速构造 1000 条数据到数据库。
存储过程是一组预先编写好的 SQL 语句集合, 它们按特定的顺序依次执行, 完成各种数据库操作的功能, 并返回执行结果给用户

Create PROCEDURE test ()
	begin
		DECLARE i int default 1; #定义计数器
		while i <=10
			do
				insert into students values (0,'小明','男','一班');
				set i = i+1; #每执行一次计数器加1
		end while;
	end;
call test ();
create PROCEDURE test 1 ()
	begin
		DECLARE i int default 1; #定义计数器
		DECLARE index_i VARCHAR (6) DEFAULT "0"; #定义名字字符串后缀
		while i <=10
			do
				set index_i=CONVERT (i, char (6)); #将计数器值转换为字符串
				insert into student values (0,CONCAT ('小明', index_i),'男','一班'); #进行字符串拼接并插入数据
				set i = i+1;
		end while;
	end;
call test 1 ();